The Only Table You Write

A user asks why the invoice says $340 when yesterday it said $290, and you have no answer. The row says 340. Whatever it said yesterday is gone, because UPDATE destroyed the evidence.

Most of the expensive problems in application data are this problem wearing different costumes. The audit trail you can’t produce, the undo you can’t offer, the cache that disagrees with the table it caches, the two reports that can’t both be right — each one traces back to writes that mutate state in place, leaving rows that remember only their latest value.

The fix is old enough that your bank already runs on it: your balance isn’t a number somebody edits, it’s the sum of a transaction log. Event sourcing — Martin Fowler’s essay is the canonical writeup — turns that into a rule: store what happened, not what is. You keep an append-only journal of immutable events, and the journal is the only thing you ever write. Everything else is a projection — a read model derived from the journal purely to make queries fast — and I mean derived in the strong sense: you could drop every projection and rebuild the entire application state by replaying the journal from the first event.

I’ve lived with this pattern for years in GoatBook, the herd-records app I built for our ranch, and I’ll use it as the running example because goats make everything concrete. The journal there is goat_events — weighings, breedings, kiddings, sales, vet visits, each a row with a timestamp, an event type, and a JSON payload, inserted once and never touched again. The same shape fits anywhere an app keeps entities with histories — orders, patients, machines, accounts.

Two kinds of projection

Projections come in two kinds, because there are only two things you can do to a sequence: reduce it or map it. Folds reduce — history compressed into a current state. Extractions map — typed rows pulled out of each event to query. Anything fancier, a search index or a monthly rollup, is one of the two or stacked on top of one. Keeping the pair distinct is most of the design.

The first is the snapshot: one row per entity holding its current state, maintained by a fold — each new event combines with the previous state to produce the next one. GoatBook’s is goats.state, where a doe is pregnant, lactating, dry, sold, or deceased, and a state machine folds events in as they arrive: pregnant plus KIDDING becomes lactating. Alongside the incremental fold you keep a pure replay function that computes the same answer the slow way — start from nothing and walk the whole history.

Two paths to the same answer give you an invariant: incremental fold and full replay must always agree. That’s not a design nicety, it’s a tested property, and I learned why the hard way. GoatBook’s ABORT_PREGNANCY event updated the snapshot correctly when it arrived, but the replay function handled it differently, so a goat’s state depended on whether you’d computed it live or rebuilt it — two sources of truth, which is the disease this whole architecture exists to cure. The test suite now folds and replays every entity and diffs the results. When those disagree, the bug is always real.

The second kind is the fact table — a name borrowed accurately from Kimball’s dimensional modeling, where a fact table is measures keyed by dimensions. For each event family you query often, project a narrow typed table holding just the columns you aggregate. GoatBook has goat_weight_fact (goat id, timestamp, kilograms) and goat_milk_fact (goat id, timestamp, litres), extracted from the JSON payload by a projector at write time, so the blob in the journal never gets parsed at query time — charting a doe’s weight over two years is a plain indexed query, no JSON operators in sight.

Each fact row keeps the event_id of the journal entry that produced it, and that one column earns its keep twice. It’s provenance — every number on every chart traces back to the exact moment it entered the system — and it’s idempotency, because with event_id as the key, re-running the projector over events it has already seen is a no-op. Rebuilding a projection can’t double-count.

The twist: synchronous projectors

The write/read split so far is CQRS in spirit — commands insert events, queries only ever touch projections. In most event-sourced systems the projectors are asynchronous: events land on a bus, handlers consume them eventually, read models lag the journal by milliseconds or minutes. You accept eventual consistency because it buys you scale.

Here’s where I cut against the orthodoxy: make the projectors database triggers. The fold into the snapshot and the split into fact tables happen in the same transaction as the event insert. Either the event and all of its projections commit together, or none of them do. The read models cannot lag and cannot half-apply, ever, by construction.

This trades scalability for a guarantee, and I made the trade with my eyes open: GoatBook has a few hundred goats and one barn. But web scale probably isn’t your problem either. What you get back is the entire category of bugs that makes people afraid of event sourcing — the UI that shows stale state, the projection that died mid-stream and silently fell behind, the retry logic, the reconciliation job — gone, not handled but absent, because the database’s transaction machinery is doing the coordination that an async system rebuilds by hand. Most software is closer to a few hundred goats than to Kafka, and most of it is paying an eventual-consistency tax on a guarantee it never needed to give up.

Corrections compensate; they never mutate

An immutable journal raises the obvious question: what about mistakes? Somebody fat-fingers a measurement; a sale falls through after it’s recorded. You don’t fix history — you can’t, that’s the point — so there are two mechanisms instead.

For the journal itself, soft revocation. A revoked_at column marks an event invalid without deleting it, the audit trail stays intact, and then you repair the projections: delete the fact rows that event produced, recompute the snapshot by replaying what’s left. The replay function stops being just a testing tool and becomes the repair path.

For anything ledger-like, compensating entries. GoatBook’s accounting tables are double-entry and also append-only, so undoing a sale doesn’t delete the SALE — it appends a SALE_REVERSAL with the amounts negated, and if the sale turns out to be real after all, a reinstating entry follows that. The books balance at every point in time and every intermediate state is auditable. Accountants have worked this way for five hundred years; reversing entries are just compensating transactions that predate the computer, and it’s a little embarrassing how recently software noticed.

One journal per aggregate

How many journals? One per aggregate — per unit that can answer for its own history — and your domain will show you where the boundaries are. In GoatBook, buying a goat can’t be a goat event: at the moment of purchase there’s no goat for it to happen to — she has no id until the system records her. So acquisitions, and anything else that happens to the herd rather than to one animal, land in herd_events, a second journal run under the same discipline. Keep the count low and the entry points few. Every table you write is a journal.

The rules, if you want them

Stated plainly, for your domain, whatever it herds:

1. One append-only events table per aggregate type. Events are immutable. Payloads are schema-validated at the edge (Zod, in my case) but stored as JSON.
2. Writes insert events and never touch derived tables. Reads never touch the journal, except for history views.
3. For each event family you query often, project a narrow typed fact table keyed by event_id — provenance and idempotency in one column.
4. Maintain one current-state snapshot per entity via a fold. Keep a pure replay function, and treat “incremental fold equals full replay” as a tested invariant, not an assumption.
5. Corrections are compensations: soft-revoke journal entries, append reversal entries in anything ledger-like, then re-derive the projections.

The pattern pairs unreasonably well with a zero-sync frontend — the client works against a local replica and the server reconciles, which sounds hard until you notice that an append-only journal of immutable events is the easiest possible thing to reconcile. Rows are only ever added, so syncing is a union, and the only conflicts left are domain ones — two events that contradict each other — which the fold has to answer anyway.

All of it together — the journal, the synchronous projections, the zero-sync client — wanted a name. An earlier version was called ledger-core, which is a metal name and I was sorry to lose it. But the bookkeepers’ own vocabulary settles the question. In accounting, the journal is the book of original entry — the chronological record where every transaction lands first — and the ledger is what you get by posting those entries into accounts: organized, queryable, derived. The ledger is a projection. The old name pointed at the derived artifact when the whole discipline is about protecting the source.

So: journal-first. One log you write, many views you read, every correction a new entry.

A journal that is never pruned only grows, and the accountants have the answer here too: close the books. On a schedule, roll the journal up into an archived copy — a year-end, opening balances carried forward — and replay from the last close instead of from the first event. The journal stays immutable, and the close is just another projection you can rebuild.

Even schema evolution — the edge I expected to hurt — yields to the same move. When an event type needs to change shape after ten thousand rows already exist in the old one, you don’t migrate those rows; you introduce the new shape as a new event type and keep appending. Projections that want new columns are cheap, because projections are disposable: define the new table, replay the journal through a new projector, drop the old one when nothing reads it any more. Append, project, replay — the pattern’s answer to everything, including its own growing pains.

What never goes away is the reading. The fold has to understand every event shape ever written — old vocabulary and new, revoked and current — because replay walks all of it. The journal is immutable, and the code that interprets it inherits a little of that immutability. How heavy that gets after twenty years I don’t know yet; GoatBook and the herd are both young. If you’ve kept a system like this running for decades, I’d like to hear what your fold looks like now.