AltSql Core: The Alpha

The Alpha is the working prototype of AltSql Core, the engine the AltSql family is built around: one C file, its tests, a few tools and a demo that runs one engine on three simulated devices. It stores key-value pairs and time-series readings in flash on a microcontroller, answers SQL on a gateway, and copies records from device to gateway byte for byte.

Everything so far runs on a PC. The flash chips and radio links are simulated, carefully, but simulated. The code also compiles for four microcontroller families and its size was measured on each, but it has not yet run on any of them. That is the job of the Beta. The six prototypes built around Core have their own demos, listed on the engines page.

Where it stands. A working engine, heavily tested in simulation. Next it has to prove itself on real chips.

• About 15 KB: the sensor build, with key-value, time-series and sync, compiled for a Cortex-M4 and not yet run on one.
• Under 1.1 KB: engine RAM on each simulated demo device (672 to 1,048 bytes), plus up to 1.4 KB of stack.
• 400,000: simulated power cuts in one run, with nothing saved lost.
• 16 of 16: deliberately planted bugs caught by the tests.

On this page: In brief · How it is built · How it stores data · Size, memory, wear · Tests · Speed · Features and limits · The demo · Where it stands

The Alpha in Brief

How It Is Built

• One file. A project adds altsql.h and defines ALTSQL_IMPLEMENTATION in one of its C files. There is nothing else to link.
• C99, no heap. The engine never calls malloc (only the optional file-based flash for gateways does). The caller hands it one block of memory at start-up and the engine carves everything out of that block. Nothing is allocated later, so a device cannot run out of memory halfway through the day.
• Few dependencies. A device build calls five C library functions (memcpy, memmove, memset, memcmp and strlen) plus the compiler’s floating-point helpers. The gateway build adds a few more for SQL and text, such as snprintf and strtod.
• Compile-time switches. Four switches turn the time-series, SQL, sync and text layers on or off. Code that is switched off is not compiled at all. That is how one code base gives a 7 KB key-value build and a 38 KB gateway build.
• Clean builds. No warnings with -Wall -Wextra -Wshadow -pedantic, from gcc 13 and clang 18 on the PC and in every cross-build.
• License. Not chosen yet. The prototype is marked “all rights reserved” until its first public release.

The engine reaches flash through four small functions: read, write, erase and an optional map. The Alpha ships two sets of them. One keeps simulated flash in RAM and can cut the power on demand, for the tests and the demo. The other keeps the flash in a file, for gateways and tools. Drivers for real flash chips are Beta work.

How the Engine Stores Data

The engine is built in layers. Each layer uses only the one below it, and every layer above storage can be switched off:

A Log That Only Grows at One End

Flash memory has an awkward rule. A write can only turn 1 bits into 0 bits, and only erasing a whole sector, typically 4 KB, turns them back into 1s. So AltSql never changes a record in place. Every change is appended as a new record at the head of a log that runs around the flash sectors in a circle. When free sectors run low, the engine reclaims the oldest one: it copies the key-value records that are still current to the head, drops the old time-series rows (this is rollover) and retires the sector, which is erased only when the log needs it again. Every sector takes its turn, so wear spreads evenly.

Each sector starts with a 32-byte header: a magic number, the format version, the write alignment, a sequence number, the flash geometry, a reclaim marker and its own checksum. All numbers are stored little-endian, one byte at a time, so the format is identical on every chip. That is what lets a gateway keep a device’s records without converting them.

Surviving a Power Cut

Battery devices lose power at any moment, often in the middle of a write. Four rules keep the data safe:

• A record counts only once its checksum is complete. A write torn by a power cut leaves bytes that fail the check, and the engine steps over them.
• Records are never written over. New records always go after the last byte ever programmed, so a torn write is never overwritten. The one deliberate overwrite is retiring a sector, which zeroes its first bytes.
• Reclaiming finishes before it retires anything. A sector is retired only after its live records are safe at the head. If power fails in between, the partial copy is discarded at start-up and reclaiming simply runs again.
• Erase late. A sector is erased only when it is about to be reused, so a half-finished erase can never leave a damaged sector inside the log.

At start-up the engine rebuilds its view by scanning the log. After a power cut every acknowledged write is there, and the write that was in progress is either fully there or not there at all.

Memory

The caller gives AltSql one block of RAM. The engine takes a fixed part of it: 232 bytes, plus 16 per flash sector, 8 per key slot, 44 per series and one record buffer. A gateway gives it a larger block, and the rest becomes working memory for SQL.

Key-Value, Time-Series, SQL and Sync

• Key-value. A small hash index in RAM points each key at its newest record. It is rebuilt from the log at start-up. If a device has more keys than its index was sized for, lookups fall back to scanning the log: slower, never wrong.
• Time-series. A series is declared once, with a layout such as time:time,temp:float. A timestamped reading with one value takes 26 bytes, 28 in flash with 4-byte alignment. Rules on the device use a window call that returns count, minimum, maximum, sum and average since a given time. A float reads back as the decimal that was stored: 21.53, not 21.530000686645508.
• SQL. The gateway build answers SELECT, INSERT and CREATE TABLE. Each series is a table, and the key-value store is a table called kv. Queries stream rows straight from flash. There are no indexes, but a filter on time skips every sector whose newest row is too old.
• Sync. The device hands out its records exactly as stored, in messages of whatever size the link allows, from 51 bytes to 1 KB or more. The gateway checks them and appends them unchanged. Sequence numbers make a resend harmless, so a lost message is simply sent again. A filter chooses what travels, for example only summaries and alerts. A delete stays on the device until the gateway confirms it, so a gateway that was offline still learns about it.

Size, Memory and Flash Wear

The same engine compiled with clang 18 at -Os for five instruction sets. The figures are AltSql’s own code plus its constant data, in bytes:

What these numbers leave out. A KB of code here is 1,000 bytes; flash sizes are binary, so 64 KB of flash is 65,536 bytes. The figures count only AltSql’s own code. Finished firmware also needs the C library functions listed above and the compiler’s floating-point helpers. The device build does some of its math in 64-bit floating point, which these Arm chips do in software. The ESP32’s Xtensa cores were not measured, because the compiler used has no Xtensa target. The Beta measures whole firmware images on real boards.

Engine RAM on the Demo Devices

The stack comes on top. Every function’s stack frame was measured for a Cortex-M4 and the deepest chain of calls behind each API function added up. A sensor build needs at most 1,376 bytes, when it creates a series; appending a row or computing a window needs 1,352, and reading a key 696. SQL on the gateway needs about 4.3 KB for ordinary statements and more for deeply nested expressions, one more reason SQL lives on the gateway. All told, a sensor should budget about 2.5 KB of RAM for the engine.

Storage per Reading

A timestamped reading with one value takes 26 bytes: 12 for the record header and 14 for the series number, the timestamp and the value. In a benchmark with three-column rows AltSql used 32.3 bytes per row, 41% more than SQLite’s 22.9. The difference is mostly the record header. It pays for the power-cut safety and for the sequence numbers that sync relies on.

Flash Wear

Flash cells survive a limited number of erases. Separate NOR flash chips are typically rated for about 100,000 erase cycles. The flash built into a microcontroller is often rated for 10,000, as on ST’s STM32H5. The demo counted every byte written and every erase:

The machine sensor is the warning. One reading a second into 64 KB of 10,000-cycle flash would wear it out in about nine months. That is a set-up problem, and the set-up can fix it:

Machine sensor set-up	Flash life (computed)
64 KB of 10,000-cycle internal flash, every reading stored	About 9 months
The same flash, storing 10-second summaries instead	About 4.5 years
64 KB of 100,000-cycle external NOR flash, every reading stored	About 7 years
1 MB of 100,000-cycle external NOR flash in 64 KB blocks, every reading stored	Over 100 years

Computed from the bytes written in the demo, assuming the flash lasts exactly its rated cycles. Real chips vary; the Beta measures wear on hardware.

Tests

Every test runs on a PC against simulated flash that behaves like NOR flash, including what a power cut does to it in the middle of a write or an erase. All suites pass.

The Power-Cut Test

This is the test that matters most for a device on a battery. A random workload runs until its budget of bytes runs out and the power is cut: the byte being written is left partly programmed, or the sector being erased half erased, as on a real chip. After the restart the database must open and match a model of everything acknowledged.

Long run	Result
Power cuts (of them during an erase)	400,000 (27,211)
Operations between the cuts	27,829,547
Sectors reclaimed / rows rolled over	1,038,578 / 13,805,625
Checks against the model / failures / databases that would not open	31,686,624 / 0 / 0

The same test, in a smaller size, runs in the live demo: its power-cut lab cuts the power 1,000 to 100,000 times in your browser and shows the checks as they happen.

Defects Found and Fixed

The last review added coverage-guided fuzzing and the planted-bug check, and read the code again with fresh eyes. That found seven defects. All are fixed, and each is now covered by a test:

All seven sat on the gateway’s input paths (SQL, import and sync), and none could lose data on a device. Earlier, the long power-cut runs had found and fixed one storage defect: repeated cuts while reclaiming could make the store report itself full.

What the Tests Do Not Cover Yet

• Real hardware. Real chips add effects the simulation does not model, such as weak bits that read differently from one read to the next. Real radios, timing and batteries are untested too.
• Firmware with several threads, or interrupt handlers using the database at once.
• Time. The demo covers 30 simulated days at most; fuzzing ran for minutes per entry point.
• A big-endian processor. The format is defined byte by byte and should be identical, but no test has run on one.
• Independent review. Every test was written by the people who wrote the code.

Speed on a PC

There are speed figures for a PC only. On a microcontroller the pace will usually be set by flash writes and erases, and that has not been measured yet.

One million sensor rows (timestamp, machine number, temperature) were loaded into AltSql’s file store and into SQLite 3.45.1, and the same five queries run on both. Each time is the best of nine runs. SQLite ran without a journal or disk syncing, and neither side had an index unless stated.

The pattern makes sense. AltSql wins where its layout helps: grouping over a log kept in time order, and skipping whole sectors to reach recent data. It loses where SQLite’s mature query engine and compact pages help, on filters and sorts that touch every row.

Most of AltSql’s scan time goes into checking each record’s checksum on every read. Dropping the check is out of the question, because it is what makes power cuts safe. A checksum computed from a 1 KB table made scans about 1.5 times faster in an experiment, and many microcontrollers have a hardware CRC unit that could do better still. Both are Beta items.

Other operations on the PC	Result
Append a row	5.4 million a second
Put a key (1,000 keys, 10,000 writes)	3.5 million a second
Get a key	11 million a second
Window statistics over the last 60 seconds	0.05 ms
Start-up with one million rows: scan the log, rebuild the key index	90 ms

Features and Limits

The Demo

The demo runs one unchanged engine on three simulated devices, one per industry. Each device’s flash is simulated as the engine sees real NOR flash, and each link is simulated with lost messages. What changes between the devices is the set-up: the flash layout, the memory, the data layout, the rule the device applies by itself, and what it sends over which link.

One run of the Alpha demo. Most of the saving comes from deciding and summarizing on the device; the compact format adds about a factor of two over lean JSON.

The JSON baseline is lean on purpose, one compact object per reading with no MQTT, HTTP or TLS overhead, so real savings would be larger. The machine sensor’s constrained link is the one to gateway B, which takes summaries only; gateway A received every record. The soil sensor’s “every record” figure is computed: 2,880 readings of 30 bytes.

Each device has its own case study: machine monitoring, cold chain and agriculture. Or run it yourself in the live demo.

Where It Stands

Maturity by Part

Readiness Level

On the European Commission’s technology readiness scale, which runs from TRL 1 to TRL 9, the Alpha sits at TRL 4, “technology validated in lab”. The engine works, and it has been validated in a simulated lab. The Beta aims at TRL 5, “technology validated in relevant environment”: real boards with real flash and radios. Its week-long run of three working devices gives a first taste of TRL 6.

How Far From a First Release

An estimate, and only an estimate: the Alpha is perhaps a quarter of the way to a first production release. The hardest design questions have answers, and the engine is small, reasonably fast and well tested in simulation. Most of the remaining work is the unglamorous part: drivers and tests on real chips, security, documentation, tooling and pilots in the field. The Beta should take about 16 weeks with two to three engineers, and a production release about 6 to 9 months after that. Hardware surprises could stretch both.

Against the Alternatives

The fair reading: FlashDB is smaller and already proven on chips, and SQLite is far more capable and far more tested. AltSql’s case rests on doing both jobs with one format and one sync, so far shown in simulation only. Sources: the FlashDB README and About SQLite.

Technical Risks the Alpha Revealed

• Flash wear at high data rates. As shown above, the set-up has to match the chip’s endurance.
• Error-correcting flash. Some chips refuse to program the same flash word twice. AltSql does that in one place, when it zeroes the first bytes of a retired sector. Each Beta chip must accept it, or the engine must retire sectors another way.
• Program and data in one flash chip. On the RP2040, the RP2350 and ESP32 chips the program runs from the flash chip that holds the data, so it must not run from flash during a write. The vendors’ flash functions handle this; the drivers must use them correctly.
• Encrypted flash on ESP32. Encrypted writes must be 16 bytes, which the engine supports. Encryption may also change how erased flash reads back, and the engine relies on erased flash reading as 0xFF to find the end of its log. The Beta checks this on the chip.
• 64-bit math on small chips. The time-series layer computes in 64-bit floating point, which most small chips do in software: slower, and more code.
• Deletes with several gateways. The device remembers only the most advanced gateway’s position, so a gateway that lags behind can miss a delete once its sector is reclaimed. The Beta will track each gateway.
• No authentication on sync. A gateway today trusts any correctly formed record. That is fine on a test bench and not in a product.

Every figure on this page was measured on the Alpha code in September 2026 unless it is marked as computed or estimated. The Alpha package includes the commands that reproduce each one.