Store Changes Instead of Current Rows: An Embedded Database Built Around History
A support ticket asks two plain questions: which plan was customer 4411 on when the August invoice went out, and who moved them off it? The customers table can’t answer either. It holds today’s row. The audit table someone added two years ago holds most of the story, apart from the week a data fix ran a bulk UPDATE in raw SQL and skipped the ORM callbacks that write the audit rows.
That’s the usual arc. Databases keep the current row and throw the story away, so teams bolt history on afterwards, with an audit gem (PaperTrail in Rails, django-simple-history in Django) or with hand-written triggers. Callback-based libraries miss every write that goes around the model layer. Trigger-based audit catches every write but has no idea who made it unless the application passes a user ID down into the database session, and sooner or later someone forgets to.
The tool worth building turns the storage around. The change record is the primary object: actor, time, transaction, key, path, old value, new value. Current state becomes a view the database maintains from those records, the way it maintains an index from a table. Nobody ever “adds auditing”, because a write without a change record can’t happen. A second way in matters just as much. Hand it whole JSON snapshots from a scraper or an API poller and it stores only the structural difference from the previous one.
History-First Databases Already Exist
Several serious systems got here first. Datomic stores immutable facts and lets you query the whole database as of any past transaction; transactions are entities in their own right, so “who did this and why” is just more facts attached to the transaction. XTDB is bitemporal: it separates when something was true from when the database learned it, which is the right model for backdated corrections. Dolt gives SQL tables Git semantics, with commits, branches, merges and diffs. TerminusDB versions graph and document data in a similar spirit.
The SQL standard has an answer too. SQL:2011 system-versioned temporal tables, in MariaDB since 10.3 and SQL Server since 2016, keep every old version of a row along with the period it was valid, and FOR SYSTEM_TIME AS OF reads the table as it stood at a given moment. They record what changed and when, at row granularity. They don’t record who, and they don’t say which field moved; you diff row versions yourself.
SQLite’s session extension is the closest in spirit. It records changes to attached tables as changesets or patchsets you can apply to another database, with a conflict handler for rows that disagree. It’s built to capture and ship changes, though. Keeping them and querying them later is your problem.
So the gap is narrower than “nobody keeps history”. Datomic and XTDB live on the JVM and bring their own query models, which is a lot to adopt for a config file’s history or a price tracker. Dolt’s history is only as fine-grained as your commits. What’s missing is a store small enough to link into a CLI or a Go service, where the field-level change is the thing you write and query, and the actor is a required argument.
Changes In, State Out
Underneath, one file with three tables is enough: an append-only changes log, a current table holding each key’s latest document, and checkpoints holding a full copy of a key’s document now and then. Every write appends change records and updates current in the same transaction, so the two can never disagree.
Storing the old value in each change pays off twice. Any change can be inverted, so an as-of read for last Tuesday starts from current state and walks backwards, undoing changes until it passes the target time. Reads far in the past start from the nearest checkpoint before the target and replay forwards. Checkpoint every 50 to 100 changes per key, or whenever the chain since the last checkpoint outweighs the document itself, and no as-of read has to replay more than a bounded number of steps.
The API stays small. The names below sketch the proposal; no such library exists yet:
db = open_history("shop.db", array_keys={"/items": "id"}, set_paths=["/tags"])
with db.tx(actor="user:maria", reason="ticket 1182") as tx:
tx.set("customer/4411", "/plan", "team")
db.ingest("feed/acme", snapshot, actor="poller:acme") # a whole JSON document
db.as_of("customer/4411", "2026-08-01")["plan"]
# 'pro'
for c in db.history("customer/4411", "/plan"):
print(c.at, c.actor, c.old, "->", c.new)
# 2026-03-02 10:14 user:sam starter -> pro
# 2026-08-19 16:40 user:maria pro -> team
Snapshot ingest is where the design earns its keep. Canonicalize the incoming JSON first (sorted keys, normalized numbers) and hash it; if the hash matches the last snapshot, record only that you checked. Otherwise compute a structural diff and store it as JSON Patch operations (RFC 6902), which for history purposes means add, remove and replace at a path. Arrays get matched by a declared key field such as id. Without one, a feed that inserts a product at the top of a list shows up as a change to every element below it. That’s the index-shift problem, and it makes positional diffs useless as history.
JSON Patch brings a wrinkle of its own. Its paths are JSON Pointers, and JSON Pointer addresses array elements by position, the exact thing you just worked around. So the internal path format carries the key (/items[id=88]/price), and only patches exported for other tools get translated back to positions.
Once changes are rows, the questions people actually ask are plain SQL:
SELECT path, count(*) AS changes, count(DISTINCT key) AS objects
FROM changes
WHERE key GLOB 'feed/acme*' AND at >= '2026-09-01'
GROUP BY path
ORDER BY changes DESC
LIMIT 3;
Point that at a vendor’s API and you get a dated record of every time they changed something without notice. The polling side is its own small project, covered in giving any API a history. If you already keep third-party API responses in SQLite, snapshot ingest turns those stored copies into deltas.
Where It Gets Hard
Arrays without a stable identity come first. Some arrays have no id, and no heuristic suits all of them. A longest-common-subsequence diff over element hashes handles inserts and deletes, but it can’t tell an edited element from a removal plus an addition. Tag lists are worse: plenty of APIs return the same tags in a different order on every call, which produces a change per poll unless the path is declared as a set. The sane default is to diff keyed arrays by key, treat declared sets as sets, and store every other array as one opaque value.
Storage grows forever unless you decide otherwise. Retention has to be a rule per path: keep every change to /plan indefinitely, keep price changes for 90 days, then compact them to one value per day. Compaction changes which questions the history can answer, so it should be explicit, and it should be logged as a change of its own.
The actor is easier in an embedded library than in a server, because the write API can simply refuse a transaction that doesn’t name one. It still has to come from somewhere real. Background jobs need names (“job:nightly-reprice”), and a default of “system” quietly defeats the point. Server databases show how fragile the plumbing gets. A common Postgres pattern has audit triggers read a custom setting set per transaction with SET LOCAL app.user_id = '...'; use a plain SET by mistake and, on a pooled connection, one user’s identity leaks into whichever request borrows the connection next.
Old records outlive their schema. Rename plan to tier in March and the history of /tier starts in March, unless the store knows about the rename. Record migrations as entries in the log, so history() can follow /tier back to /plan. Rewriting old records instead would break the one promise the log makes.
Then there’s the right to erasure, which collides head-on with a log that never forgets. Crypto-shredding is the standard answer: encrypt each person’s fields with a key of their own, kept in a separate key table, and delete that key when they ask to be forgotten. Every old value for that person turns into unreadable ciphertext while the rest of the history stays intact. The costs are real. Personal paths have to be declared before the first write, and encrypted values can’t be indexed or filtered. Backups of the key table have to forget as well, or the key survives in last month’s dump.
What Version 0.1 Should Refuse
Build the first version on SQLite. Transactions and a crash-safe file format come free, and your code is the change model on top. One writer, one file: the three queries above plus raw SQL over changes, snapshot ingest with keyed arrays and set paths, retention rules, and crypto-shredding for declared paths.
It should refuse sync between replicas, which is a different problem with its own conflict rules. It should refuse valid time, too: v0.1 records when the database learned something, and a backdated correction is a new change with a note attached. And if what you need is a record of intent (“order placed”, “refund approved”) instead of effect, you want an embedded event log. The two coexist happily. A change record also has exactly the shape of a state transition event for debugging, so the same file can feed a timeline view later.
Keep the story. The current row is only its last page.