The obvious way to handle a fact that changes is to overwrite it — the leaving date was 1 June, now it’s 15 June, update the row. mem-local won’t do that anywhere in the store. Nothing is ever mutated in place. A correction closes nothing; it inserts a new assertion, and “what’s true now” gets answered by replaying the log, not by checking a flag. The idea is as old as the first design conversation, where the write path got named as the place these systems actually die. But I didn’t just decide it and move on — I checked it, and the checking found a real bug in a design I’d already called settled.
A worked example of this mechanism, traced against a real decision, is diagrammed in bitemporal-timeline-diagram.
Checking it against a real standard, not my own notes
I’d held a rule since the start of this project: validate the storage model against something outside my own head before building on top of it, because reasoning from your own prior notes just regenerates your own mistakes. That mattered here specifically because part of the “settled” design had originally come out of an AI-generated reference nobody had actually checked against a real spec.
Checking it against SQL:2011 and how XTDB implements the same idea turned up a genuine bug in a design I’d already signed off on. The model I had described supersession as closing the old row’s transaction-time and inserting a new one — which quietly treats two different things as one axis: valid time (when a fact was actually true) and transaction time (when the system found out about it). They’re independent, and folding them together is the classic bi-temporal mistake. It’s the kind of bug that doesn’t announce itself. It corrupts history silently, the first time anyone corrects a fact about the past, and it would only have surfaced months later, on data I’d already be relying on.
Two ways to fix it, and why the harder-looking one won
XTDB frames the fix as a straight choice between two designs:
- Explicit-close (XTDB calls this Model A). Four temporal columns, and every write has to close out an old row before inserting the new one — a close-plus-insert-plus-split, done correctly, every single time. Even XTDB’s own engineer admits it’s fiddly to get right by hand.
- Event-log (Model B). Only ever record when a fact was asserted, never when it stopped being true. Every write is a pure append. “What’s current” gets worked out at read time, by replaying the log.
I picked Model B, and the reasoning matters more than the specific bug it fixed: it makes the one irreversible operation — the write — trivially correct, because it’s always just “append a row.” All the genuinely hard bi-temporal logic moves to the read side, where a bug gives you a wrong answer you can rerun, not a corrupted log you can’t undo. Given that “irreversible if wrong” was the entire fear behind this exercise, putting the hard part on the side where mistakes are recoverable was the whole point. The fast reads that the explicit-close model gets for free still show up here too, just as a separate pointer table that’s explicitly a cache, not the truth — if it’s ever wrong, it gets rebuilt from the log, never patched by hand.
That pass produced six corrections in total, none of them reopening anything — all refinements inside the same architecture: splitting the single time column into two independent axes; a non-null “forever” marker instead of a nullable open end; separating an entity’s stable ID from the ID of each individual version of it; rewriting merge as a sequence of plain appends; deriving “status” instead of storing it; and adding the pointer-cache table on top.
A second, smaller lesson: a gap that argued with its own code
The build spec that came out of this had its own mistake, on a smaller scale. It correctly flagged a real bug in its own list of known gaps — the search fuses keyword and vector results using a ranking formula that needs each result’s position (1st, 2nd, 3rd), and SQLite’s own full-text ranking function returns a relevance score, not a position. That was written down as a known issue. Several pages later, the actual query still used the wrong value anyway. So the document contained a broken query and, separately, a note correctly saying it was broken — and nobody had noticed the two disagreed.
Writing a bug down and then shipping it unfixed is worse than never writing it down, because it makes a live defect look handled. The fix wasn’t to edit the note — it was to fix the query: wrap each half of the search in a step that assigns a real position before combining them, flag the SQLite version this needs, and only then delete the gap from the list.
What supersession actually buys, plainly
Overwrite-and-replace only answers one question well: what’s true right now. Bi-temporal supersession is built to answer three:
- What’s true now?
- What did I believe was true at some point in the past?
- When did my belief about something actually change, separately from when the underlying fact changed?
A flat, overwrite store collapses the second and third questions completely, and answers the first one with false confidence — it’ll happily assert something as current that stopped being true months ago, with nothing to flag that it’s gone stale. That’s exactly failure mode one from 01-origin-and-requirements: a stale fact asserted with full confidence. Supersession doesn’t just dodge that failure — it turns the history of a belief into something you can actually query, which an overwrite store can never do, however carefully it’s built.
What I’d keep from this
Both bugs here were the same mistake in different clothes: something drifting out of agreement with itself, unnoticed. Reasoning from my own prose invented one bug; a spec that disagreed with its own query hid the other. Neither would have survived being checked against something outside the document itself — an external standard the first time, a plain read-through the second. “Be careful” isn’t advice that actually helps here. What helps is checking a design against something you didn’t write, and making sure the thing you built and the notes describing it still agree. Catching the first bug while it was still one sentence in a design doc, rather than a schema full of quietly wrong timestamps, was the whole value of doing this before building instead of after.
Sources: earlier, unpublished notes bitemporal-model-validation-and-build-spec and memory-system-design-decisions; mem-local e8 (settled-architecture cluster, Model B); Bitemporal personal memory system design review, 19 Jun 2026 (“the bitemporal Model-B choice… correct call”).