Why the database refuses to let me fix a mistake

The row that could not explain itself

A village savings and loan association is a community bank with no building. Fifteen to thirty neighbours meet every week, save, borrow from the pooled fund, repay with interest, pay penalties when they are late and share everything out at the end of the cycle. The whole operating capital of that group is the trust between its members. If the numbers stop making sense, the group is finished, whatever the software says.

The first version of our loan model did what most of us do without thinking. A loan was a row with a balance column. Every event updated that column. Disbursement sets it. A repayment reduces it. Interest and late charges push it back up. Simple to write, simple to read, one row per loan.

Then a member asks the question that model cannot answer. Why do I owe this much? Not in the abstract. She is standing in a meeting, in front of thirty people who all know her. The number on the screen is bigger than the number in her head. A mutable balance column can tell her what she owes. It cannot tell her how she got there, because every step that produced it has been written over by the next one.

You can bolt an audit log onto the side. Plenty of systems do. The trouble is that you then have two sources of truth that are free to disagree. When they do, the log is the thing nobody trusts, because the balance is the thing the software actually uses.

Append only is easy to say

So the loan stopped being a balance and became a ledger. One row per money movement: disbursement, interest, late charge, repayment, share-out settlement, reversal. Each row carries a monotonic sequence number and the balance after it, both assigned under a row lock on the loan so that concurrent appends stay totally ordered. Current state is a database view that folds the events. The balance is derived, never stored twice.

That much is ordinary event sourcing and I would not write an article about it. The interesting part is the next question. Where does the rule that says these rows never change actually live?

The obvious answer is the application. Put the appends behind a context module, do not write an update function, review each other's pull requests. In Elixir this is comfortable: no changeset for updates, no repo call that touches an existing row. Anybody who adds one has to explain themselves in review.

It is also a convention. Conventions lose. They lose to the data-fix script somebody runs from an IEx session because a group is meeting in the morning and the number is wrong now. They lose to the migration written at 2am to unblock a deploy. They lose to the new engineer who does not know that this table is special, because nothing about the table says so. Every one of those people is doing their best. That is exactly why the rule cannot depend on them remembering.

Where the rule actually lives

It lives in Postgres. A trigger rejects every update and every delete on the loan events table. The shape of it is this small:

create or replace function loan_events_are_immutable()
returns trigger as $$
begin
  raise exception
    'loan_events is append only: % rejected. Append a reversal instead.',
    tg_op;
end;
$$ language plpgsql;

create trigger loan_events_no_update_or_delete
  before update or delete on loan_events
  for each row execute function loan_events_are_immutable();

Ten lines. They change what kind of statement the invariant is. Before, append only was something the codebase intended. Now it is something the database enforces against every client that will ever connect to it: the application, the console, a migration, a psql session, me at 2am. There is no privileged path around it, because the check does not live in the path. It lives in the table.

A correction is therefore an append. You post a reversal event, the fold takes it into account and the history shows both what was recorded and what was done about it. The wrong entry stays visible, which is the point. In a paper ledger you cross an error out in front of the group and initial it. Nobody tears out the page. The trigger is that same discipline, made impossible to skip.

What it cost

I would not be honest if I stopped at the part that sounds clever. The bill comes due in four places.

Every event type needs a way to be undone. Once update is off the table, a reversal is not a convenience, it is the only correction that exists. Each kind of event has to be reversible in a way the fold understands, including the awkward ones like a late penalty that has already accrued against a period that has since closed. That thinking has to happen up front rather than the day somebody makes a mistake.

Backfills stop being casual. Add a column to that table and there is no quiet update to populate it. You either derive it on read or you disable the trigger deliberately, do the work, then put it back. That is a heavier operation than a normal migration, on purpose. If it ever becomes routine, the protection is gone.

Fixtures get longer. A test that needs a loan sitting at a particular balance cannot simply set one. It has to build the history that produces it. This is slower to write and, once you have lived with it a while, better: the test now exercises the same path production does, so a fixture that is impossible to construct is telling you something true about the domain.

Reads and writes both pay a little. State comes from folding events, so a balance costs more than reading a column, which is why each row carries the balance after it. Ordering costs a row lock on the loan, so appends to one loan serialise. In this domain that is nothing. A loan is touched a handful of times a month by thirty people in one room. On a table taking thousands of writes a second to the same aggregate, that lock would be the first thing to hurt. I would be making a different trade.

There is a fifth cost, which is that the migration is not finished. The old mutable balance and the new ledger are still double-written, with the legacy path authoritative for the interface and a parity gate that permits a known offset on outstanding penalties. Retiring the legacy path is the next real piece of work on that system. Moving a live money model takes longer than writing the better one.

What it bought

The member's question has an answer that does not depend on trusting the software. Every shilling of her loan is the sum of the events that produced it, the database will not let any of them be edited and a separate audit trail records who entered each one. The history is not something kept beside the balance. It is the balance.

A second benefit arrived that I did not plan for. Groups join with years of paper behind them, so history gets imported. Imports then get re-run. Because events are immutable, each imported event can carry a fingerprint and a repeat import becomes idempotent instead of quietly doubling a group's history. That property only works because nothing is allowed to mutate underneath it.

The third is about me. I am the person most likely to be trusted with a quick fix at a bad hour, on a system whose users cannot inspect the arithmetic and have no support desk to call. Taking that power away from myself, in the one place where nobody can hand it back informally, is the strongest guarantee I can offer a group whose capital is trust.

When I would not do this

This is not a rule for every table. Most rows are current state and nothing else: a profile, a setting, a draft. Making those immutable buys you nothing and costs you the four things above.

The test I use is whether the row is a claim about money, identity or consent that somebody may need to dispute later. If it is, ask where the invariant lives, then push it down to the lowest layer every path must cross. Application code is the first answer people give, database constraints are usually the honest one. The distance between those two is where most data trouble is born.

A convention holds until the night it matters. A constraint holds that night too.