How It Works

The idea. Put a very small database on the device that reads the sensors, and store its data so that the gateway’s database can use it as it is, byte for byte, with no translation step.

Before and After

Today a device and its gateway keep data in two different formats. A converter has to sit between them and turn the device’s key-value data into rows for the gateway’s SQL database. AltSql uses one format on both sides and takes the converter out:

Before: the device keeps readings in its own key-value store, sends messages to a converter (glue code) that reshapes them into rows for a SQL database with a different format. After: the device runs the small AltSql build and the same records go byte for byte to the gateway, which runs SQL over them, with no converter.

Why take the converter out:

  • Less code to own. The converter is written again for every product and has to keep working for the ten or more years the device is in service. With AltSql there is none to write.
  • Fewer quiet errors. Every conversion is a chance to drop, round or mislabel a reading. Records that arrive exactly as stored leave no such gap, and their checksums show if anything was damaged on the way.
  • Less data on the link. A reading stored as an AltSql record takes 26 bytes. The same reading as JSON takes 53.
  • A device that can decide. The device keeps its data in a form it can read itself, so it can act on the spot and send only what matters.

The Whole Chain at a Glance

From the sensor to the cloud: where each part sits, and which way the data moves.

Sensors wired to a device; the device runs the small AltSql build with time-series, key-value and flash storage; records sync byte for byte to a gateway running the full build with SQL; the gateway serves dashboards and the cloud. The Learned Query Optimizer, drawn dashed, learns on the gateway and decides on the device.

Solid boxes exist in the Alpha today. Dashed boxes are the Learned Query Optimizer, planned for the Advanced Beta.

Where Each Part Lives

The database runs in two places. The same AltSql engine has a small build for the device and a full build for the gateway, and both hold the same records.

Part Where it lives What it does
Sensor A probe wired to the device Measures temperature, vibration, moisture or a door opening. It stores nothing
Device A microcontroller: an ESP32, an STM32, a Raspberry Pi Pico or a newer Arduino board Runs the small AltSql build (about 15 KB of code, about 1 KB of memory), keeps its records and decides on the spot
Time-series On the device, then copied to the gateway Readings: a time and one or more values, such as 10:00 and 61.5 °C
Key-value On the device, then copied to the gateway Settings and state, such as a calibration value or “fan on”
Gateway A small Linux computer: a Raspberry Pi 4 or 5, an industrial PC, a box on the wall Runs the full AltSql build, keeps many devices’ records unchanged and answers questions in SQL. A Raspberry Pi with sensors wired to it can be device and gateway at once
SQL On the gateway; a larger chip can carry it too The language for asking questions. Each time-series appears as a table, and key-value as a table called kv
Server, cloud and apps Beyond the gateway Dashboards, alerts and reports that read answers from the gateway
Learned Query Optimizer (later) Split: it learns on the gateway and decides on the device Adjusts what the device keeps, sends and measures, within rules the application sets

One Set of Records, Two Views

The device saves readings and settings as records. The gateway keeps the same records and shows them as SQL tables:

Time-series readings and key-value settings on the device are saved as records; the same records are copied as they are to the gateway, where they appear as the SQL tables raw and kv.

A common picture is that the device writes key-value data and the gateway turns it into SQL. Two corrections:

  • Readings go into time-series. Key-value holds settings and state. Both are stored as the same kind of record, in one log in the device’s flash.
  • Nothing is converted. The gateway keeps the records exactly as they arrived. SQL is only the language for asking questions about them.

The device can also read its own recent records without SQL, for example the average of the last ten seconds. That is how it decides on the spot, even with no connection.

What “Byte for Byte” Means

In the Alpha, a reading with one value is stored as a record of 26 bytes. The first 12 bytes are the header and the last 14 are the reading itself. The gateway receives exactly these bytes and stores them as they are:

A 26-byte AltSql record: start marker, type, length, sequence number and checksum make a 12-byte header; series number, time and value make 14 bytes of data. Below it, the same reading as a 53-byte line of JSON.

Nobody has to turn those 26 bytes into a line of JSON, which would take 53. In the Alpha demo, JSON would have taken 1.8 to 2.4 times the bytes of the stored records, and that is before any converter runs. You can check the bytes yourself: at the end of a run, the live demo finds the newest record in the device’s flash and the same bytes in the gateway’s flash.

A Sensor and Its Gateway in a Few Lines

On the device, the engine gets one block of memory and a flash driver. A rule reads the device’s own recent data:

static uint8_t mem[2048];                  /* all the RAM the engine gets */
altsql_config cfg = { .mem = mem, .mem_size = sizeof mem, .create = 1 };
altsql_open(&db, &flash, &cfg);            /* flash: the chip's driver */
altsql_ts_create(db, "raw", "time:time,temp:float");

altsql_append(db, "raw", (int64_t)now, 61.5);
altsql_ts_window(db, "raw", "temp", now - 9, &st);
if (st.count == 10 && st.min > 60.0) fan_on();   /* decided on the device */

On the gateway, the same records answer SQL:

SELECT time / 3600 AS hour, AVG(temp) FROM raw GROUP BY hour;

The engine itself, its layers and its builds are described on the Alpha page.

The Hard Part

Identical bytes help only if they stay trustworthy. The Alpha handles the difficult cases like this:

  • A power cut in the middle of a write. Every record carries a checksum. A half-written record fails the check and is skipped. In one test run the simulated power was cut 400,000 times. After every cut, everything the engine had already reported as saved was still there. Only the record being written at that exact moment could be missing, and it was either saved in full or not at all.
  • Different chips. Numbers are stored the same way on every chip, so a gateway reads exactly what a small Arm or RISC-V chip wrote.
  • Lost or repeated messages. Each record has a sequence number. A resent record is recognized, and a lost one is simply sent again.
  • Deletes. A delete is a record too, and it stays on the device until the gateway confirms it.
  • Format changes. Each storage sector records its format version, and the format will be frozen at v1.0 so old records stay readable.

What AltSql Leaves to Others

AltSql is small on purpose. It has no joins, no secondary indexes and no multi-statement transactions. A gateway that needs them can copy the data into a larger database. It stays out of analytics over large datasets, hosted cloud databases and mobile app databases, and it does not try to be compatible with all of SQLite. Its job is narrower: the data layer from the sensor to the gateway, in one format.

Related