A database that remembers what changed — 2026-10-02

The change clock (a-resource-over-a-table) says that something was committed. This round adds what: m0-sqlite stamps each row a watched table writes, so "what changed since N" is a query. It lands as SPEC O26, O27 and N49 and DECISIONS D63 and D64, with apps/table_notes answering the query over a GET.

Three places to keep the past

A feed sends a client what changed since it last looked, so something has to remember what the client had. There are three places for it.

The first is how Meteor's live queries began, and what it left: every write re-ran every watched query. It is also state per view, which becomes state per connection once views are per user. The second is fine for fifty numbers and cannot say a row is gone. The third is this round.

What was built

install_stamps(db) creates two tables and watch(db, "notes") puts four triggers on one:

m0_changes(tbl, row, seq, born, gone)   PRIMARY KEY (tbl, row)
m0_stamp(id = 0, seq, floor)

One entry per row touched, not per change: seq is the stamp of its last write, born the stamp of the insert that made it, gone whether it is a tombstone. The counter is a row, incremented in the trigger. A feed is then one statement, and its memory one number:

SELECT c.row, c.seq, c.born, c.gone, n.title FROM m0_changes c
LEFT JOIN notes n ON n.id = c.row
WHERE c.tbl = 'notes' AND c.seq > ?1 ORDER BY c.seq

Five properties carry it. The first three and the last are tests in test_stamps.mojo; the fourth is the wire gate's, whose other program is CPython's SQLite:

• Stamps are in commit order, and a rollback takes its own. The counter is in the database and SQLite has one writer. On PostgreSQL the same query misses rows, a sequence value being taken before the commit that publishes it.
• A statement sees whole commits, so what is above a stamp is a whole number of transactions and one stamp per row is enough.
• born tells an insert from an update. A client at N holds a row iff born <= N. That is what lets a feed send an element for a new row and one key for a changed one while keeping nothing.
• A trigger fires for every program. The write path does not publish, so it cannot forget to, and a writer in another process and another language is stamped like this one.
• stamp_of(db, table) is the highest stamp among one table's rows: a clock for a table, where data_version is the database's.

prune_stamps(db, below) forgets tombstones and raises a floor; a client below the floor is told to start over.

The counter is a row

The first version numbered a change max(seq) + 1 over the stamp table. It needed a rule (never prune the row that holds the highest stamp, or the number is given twice) and an index that made the per-table clock a scan. Measured on an M4 through CPython's SQLite, WAL, 10,000 rows:

The counter costs 4 µs a commit and removes the rule.

Both of those forms wrote the entry with an upsert. What shipped does not, for two reasons the review of this round found. A SQLite older than 3.24 cannot parse an upsert, so it could no longer open a watched file. And the plain alternative, INSERT OR IGNORE, is wrong inside a trigger: SQLite applies the conflict clause of the statement that FIRED the trigger to the statements in it, so UPDATE OR REPLACE replaced the entry and lost born, and INSERT OR FAIL would have failed the write. The entry is written by an insert that cannot conflict (INSERT ... SELECT ... WHERE NOT EXISTS), then an update. As shipped, on apps/table_notes' own table, same machine and client:

A watched commit is about 21 µs more than an unwatched one. The upsert form measured 29 µs a commit and 1.2 µs a bulk row on the same table.

The application

apps/table_notes watches its table and answers GET /notes/changes?since=N with the rows stamped above N: the live ones with their titles and born, the dead ones as gone, and head, the stamp to ask from next. smoke-table-notes holds it at one loop and at two: a write through the server, one by another program and a delete arrive as three rows in commit order; a client that has everything is sent nothing; a commit to another table moves the clock and no stamp; a client below the floor, or ahead of the head, is answered reset; a title with a byte that is not UTF-8 still decodes; and under M0_THREADS=2 the two loops answer one stamp identically, neither having kept the client's place.

The list's cache stays on the clock. A stamp can be got past (the two traps below) and data_version cannot, so a rendering keyed on stamps would be as fragile as the triggers, to spare one rendering when another table commits.

What a stamp does not see

Pinned as tests too, so a fix or a change in SQLite shows up as a failure.

• A REPLACE through a UNIQUE column (INSERT OR REPLACE, UPDATE OR REPLACE) deletes without a tombstone unless the writing connection has PRAGMA recursive_triggers = ON. SQLite fires no delete trigger for the displaced row otherwise, and the pragma is per connection, so every program that writes with REPLACE has to set it.
• A table rebuilt by a migration loses its triggers, silently. watched reports it; watch again restores them, and what was written in between was not stamped.
• An update that changes nothing is stamped. A program that recomputes more than changed compares before it writes.
• Only a rowid alias names a row durably. watch refuses a table with any other key, WITHOUT ROWID among them: its triggers would have resolved new.rowid only when they fired, and failed every write to the table from every program. INTEGER PRIMARY KEY DESC is refused too, a spelling SQLite keeps as a key that is not the rowid.
• A row older than the watch is in no delta until it is written, so the changes above 0 are the table only for one watched from its first row. Nothing backfills.
• A window and an aggregate are not rows. A "top 50" loses a row that did not change, and a count has no row. Either stays on the clock and a hash, or is written down as a row by a stage that computes it.

Three applications outside the tree

The design was run as three probes before it was lifted, each with a wire gate and a browser check (ideas/changefeed, private). What they measured, on an M4, one run each:

• A page of fifty bars, values bound to Datastar signals, fed by a per-loop poll of the clock and the query above. An outside write reached every subscriber on two loops in one tick; one changed row was 86 bytes against a 6 KB list; 1,000 commits in a burst were 2 events.
• A graph laid out by the server. A thread reads nodes and edges when their stamp moves, runs a force-directed step with the repulsion across cores, and writes positions to a watched table; pages draw them with registered custom properties. 2.5 times faster than one core at 2,000 nodes inside the server.
• Notes and their nearest notes, over this project's dogfood corpus: a Python process embeds the notes stamped above its cursor, a Mojo thread recomputes neighbours for the vectors stamped above its own, and an open page is told when its row of the result is stamped. An edit to one note reached another note's open page in 89 ms, with one embedding and 14 of 535 lists recomputed.

So a stamp is a cursor, and a feed is one consumer of it. A stage that derives something expensive does only the work above its cursor, keeps the cursor in the database where the work is costly to repeat, and writes its result to a table that is watched in turn.

Two rules came from getting them wrong:

• A delta is never cut. Split at a stamp to fit a buffer, a row born below the cut and changed above it was never sent as new. born is true of a client only at the head of a snapshot it has all of.
• Fan-out is per subscriber, from its own stamp. A broadcast assumes everyone stands together, and one refused frame leaves a gap. Asked again from where each stands, a slow client gets fewer and larger deltas.

A stage's place (added the same day)

The embedder kept its cursor in a table of its own making, in the transaction that wrote its vectors. That is the one part of a stage the package can hold without holding the stage: m0_cursors(name, seq), cursor, advance (inside the caller's transaction, so outputs and place hold together; never backwards, never past the head, the check in the statement that writes) and slowest_cursor, the stamp pruning may reach without stranding a registered stage (O28). Registered is the word: a stage takes its row before its first read and gives it back when it retires, or pruning waits on it for good, and the lag query says which one. Two things become queries: a stage's lag, head - seq, and the safe prune point. It was lifted ahead of a Mojo asker, which D64 records.

The stream (added the day after)

The handler the three probes wrote by hand is m0_http.Feed (N51): a registry of subscribers each at a number the application owns, and nothing else. open places a client at its Last-Event-ID, behind(head) names who is not at the head, send queues a delta whole and places the client where the application says, skip moves one with nothing of its own. There is no counter and no journal: a reconnect is the application's own query from the client's number. ViewState gained the stream hooks and the tick, with defaults, so a views application can hold one.

apps/table_notes is the asker (N50): GET /notes/events keeps the list live from either loop. The page's side is eight lines of script, an EventSource and htmx.swap, because htmx 4.0.0 has no SSE in core and its sse extension targets htmx 2's API — checked against both bundles. Two facts about htmx 4 cost a round each in Chromium: an out-of-band element with a positional swap (beforeend) inserts its CHILDREN into the element of the same id, so new rows travel inside a <ul id="notes-list" hx-swap-oob="beforeend">; and htmx.swap({text, target, swap: 'none'}) applies the out-of-band parts of text and nothing else, which is exactly the call an event wants.

Three more things the gate found:

• A client above the head is not behind. The first behind named only subscribers below the head; a client from another incarnation, or a database restored from backup, stood above it and was never served. behind now names both, and send places a subscriber down as well as up.
• A delta of a bulk change is the size of the change. One UPDATE touching 400 rows is one event of 400 rows; the gate uses that to fill a slow subscriber's slot and watch the merging, since the sender's own socket buffer absorbs megabytes before the loop feels anything.
• A refused subscriber must not cost a rendering a tick. The first behind named a stuck subscriber every tick, and the loop rendered its delta every tick to refuse it again (measured: 16 refusals a second with no commits). Feed now remembers how much a slot held when it was refused and leaves it out until that changes.
• A fragment rendered again must reopen the feed from its own stamp. The DELETE's answer is the list at the head; a feed left at its old place then appended a row the fragment already held. The page's script closes the old EventSource and opens one at the fragment's since.
• A view that raises after opening a stream leaves a slot subscribed that the loop never drains. open now clears what a slot held, and the view opens and returns, leaving the sending to the next tick.
• A probe reading a stream reads chunked transfer encoding. The first reader split raw bytes at blank lines; a chunk boundary inside an event corrupted it, and the symptom (an id of 176 at a head of 76) looked like a server bug for an hour.

Not built

• Repairing a rebuilt table. watched reports; the application decides.
• History. One entry per row is the present and when it last changed. "As of stamp 42" is a log, which grows.
• A PostgreSQL form. Commit order is not sequence order there.