Double-entry as a data model, not an accounting concept

15 min read

Somewhere in your codebase there is a function that moves money, and inside it there are two statements:

update accounts set balance = balance - 100 where id = $1;
update accounts set balance = balance + 100 where id = $2;

Wrapped in a transaction, so it's atomic. Both happen or neither does. People stop worrying at this point, and the code ships, and for a year it's fine.

It isn't fine. It's just that the failures are quiet and they arrive later. That pair of statements has three problems, and only one of them is about correctness.

The balance is a claim with no evidence behind it. If the number is wrong, nothing in the database tells you when it went wrong, or by how much, or which code path did it. There is no wrong-ness to detect: the column says what it says. A support ticket about a customer being nine pence short is now an archaeology project across application logs, if you still have them.

The second problem is that half the movements in a real system aren't two-sided. A fee comes out of the middle. A payment goes to a provider you don't have a row for. So somebody adds a third update, and somebody else adds one with no counterparty at all, because the money went "out". Money that goes out goes somewhere, and if your schema has no name for that somewhere, the money leaves your books and the books stop adding up.

Third: the code is the only thing enforcing any of this. The invariant lives in whoever remembers to write both statements.

Double entry fixes all three, and it isn't an accounting concept you have to translate into engineering. It's already a data model. It has been one since 1494, which makes it older than every idea in your stack and better tested than most of them.

One invariant, not a vocabulary

The reason double entry reads as accountancy is the vocabulary: debits, credits, T-accounts, journals, ledgers. None of that is the idea. Strip it and one sentence remains:

Every transaction consists of entries against named accounts, and those entries sum to zero.

That's it. It's an invariant over a table, of the kind you'd happily write a check constraint for in any other domain. What you get from it is what it forbids: money cannot be created, destroyed or made to vanish in transit, because there is no way to write a legal transaction that does any of those things. An amount that leaves one account is, by construction, in another one, and the schema will not let you skip naming it.

Two corrections to the usual mental model follow immediately.

Double entry does not mean two rows. It means balanced rows. A payment with a fee has three legs and is perfectly ordinary:

Customer                 -100.00
Merchant payable          +97.50
Fee income                 +2.50

Sums to zero, so it's valid. Systems that hardcode a from_account and to_account column pair have quietly assumed two, and every fee, tax, split and FX conversion after that gets modelled as several separate transfers that are only related by convention.

The entries are the data. The balance is not. A balance is a sum() over entries. The moment you store it as an independently writable column, you have two sources of truth and no way to say which one is lying.

Name the outside world

The step that decides whether this works is boring and gets skipped: you need accounts for things that are not yours.

The customer's card, the acquiring bank, the tax authority, your own corporate account at a real bank, the fee revenue you just earned. If money moves between your system and any of them, they need to be accounts, or you will write a single-sided entry and the invariant dies on the first real feature.

This feels wrong the first time. You don't control Stripe's balance sheet, so why is there a row called provider_clearing? Because your books are a model of your position, not of theirs. provider_clearing doesn't record what the provider has. It records what you believe the provider owes you, which is a fact about you, and which you will later prove or disprove against their statement. That is exactly the number reconciliation compares, and a system without such an account has nothing to reconcile with.

So account types split into two families, and the difference is operational rather than philosophical:

  • Internal accounts hold value you can inspect directly. Customer wallets, your fee income, the suspense account holding money in flight.
  • External accounts hold your claim on somebody else. Their true value is only knowable from a statement, which arrives later and sometimes disagrees.

Once the outside world is on the chart of accounts, every movement has two ends, and "where did the money go" stops being a question you answer from memory.

Two tables and a constraint

The schema is smaller than the discussion around it.

create table accounts (
  id        uuid primary key,
  name      text not null unique,      -- 'customer:1234:GBP', 'fee_income'
  kind      account_kind not null,     -- asset | liability | income | expense | equity
  currency  char(3) not null
);

create table transactions (
  id           uuid primary key,
  occurred_at  timestamptz not null,   -- when the event happened
  recorded_at  timestamptz not null default now(),
  external_ref text unique,            -- the idempotency handle
  description  text not null
);

create table entries (
  id             bigserial primary key,
  transaction_id uuid   not null references transactions(id),
  account_id     uuid   not null references accounts(id),
  amount         bigint not null check (amount <> 0),   -- signed minor units
  currency       char(3) not null,
  created_at     timestamptz not null default now()
);

A few of those columns are doing more work than they look like they are.

amount is a signed integer in minor units, and the sign convention is into the account is positive. It is never a float, for the reasons that need their own post, and it is never zero, because a zero entry is either a bug or a comment.

occurred_at and recorded_at are separate on purpose. A transaction booked on Monday for a card authorisation from Saturday has two different times, and every question about "what did we think the balance was at midnight" needs the second one. Collapsing them into a single created_at is a decision you can't reverse without a data migration, because the information was never captured.

external_ref carries the unique constraint that makes writing a transaction idempotent. The contract around it matters more than the column does, but this is where the guarantee is enforced.

And the invariant itself:

create function assert_transaction_balances() returns trigger as $$
declare
  offending record;
begin
  select currency, sum(amount) as total
    into offending
    from entries
   where transaction_id = new.transaction_id
   group by currency
  having sum(amount) <> 0
   limit 1;

  if found then
    raise exception 'transaction % is unbalanced: % %',
      new.transaction_id, offending.total, offending.currency;
  end if;

  return null;
end;
$$ language plpgsql;

create constraint trigger entries_must_balance
  after insert on entries
  deferrable initially deferred
  for each row execute function assert_transaction_balances();

deferrable initially deferred is the whole trick. The check runs at commit, not per row, so you can insert legs one at a time and still be told off if the set of them doesn't balance. Without deferral the constraint is unsatisfiable: the first leg of any transaction is unbalanced by definition.

Note that it groups by currency. That matters later.

Enforce it in the database, or don't claim it

There's a version of this design where the balancing check lives in a Ledger service class, and every write goes through it, and the class is well tested. It's a reasonable-looking argument and I think it's wrong, for a reason that has nothing to do with trusting your own code.

The database is the only component that sees every write. The service layer sees the writes that went through the service layer. Over a few years a ledger acquires: a data migration, a backfill script, an admin console with a "fix balance" button written during an incident, a second service in a different language, an analytics job that turned out to write, and one afternoon where somebody ran an INSERT in a psql session to unblock a customer. Every one of those bypasses your class. None of them bypasses a constraint trigger.

So the rule is that the invariant lives where it cannot be routed around. The application layer can validate early for better error messages, which is a UX decision. The trigger is the one that's load-bearing.

The cost is real and worth stating plainly. Balance checking in a trigger costs you a query per transaction commit, you now have business logic in PL/pgSQL that your test suite has to cover through the database rather than in memory, and the failure surfaces as an exception at COMMIT in a place your ORM's error handling probably doesn't expect. In exchange, "the ledger is unbalanced" becomes a sentence that cannot be true. That trade is worth it in every system I've worked on where the numbers were money, and in approximately none where they weren't.

Append-only is the expensive half

The second constraint is that entries are never updated and never deleted.

revoke update, delete on entries, transactions from app_user;

This is the part that people agree with in principle and then find genuinely painful in practice, so it's worth being honest about what it costs before recommending it.

You get a call: an entry was booked against the wrong account. Under a mutable schema this is a thirty second UPDATE. Under append-only it's a reversing transaction that puts the money back, then a second transaction that books it correctly, and both of them are permanently visible in the customer's history. Six entries where one edit would have done, and a statement that shows the mistake happening and being fixed.

That visible mistake is the feature. An UPDATE on a ledger destroys the evidence of what you previously believed, and what you previously believed is the thing an auditor, a dispute, or a bug investigation is asking about. A ledger you can edit answers "what is true now". A ledger you can only append to answers "what did we know, and when did we know it", which is the question that gets asked when something has gone wrong.

There's a smaller and more practical benefit: append-only means the table is safe to stream. Every downstream consumer, the analytics warehouse, the balance cache, the fraud model, can follow the entries table forward and never has to handle a row changing underneath it.

Two rules keep the append-only story honest:

  • A reversal is a new transaction, not a flag on the old one. Give it a reverses_transaction_id so the pair is queryable, and let both stand.
  • Nothing is backdated. A correction discovered today is recorded today, with occurred_at pointing at the original event. That pair of timestamps is why they're separate columns.

The balance becomes a query

With a mutable column gone, a balance is derived:

select sum(amount) from entries
 where account_id = $1 and currency = $2;

The objection is immediate and correct: that's a full scan of an account's history, forever, on the hottest read path you have. A three year old high-volume account will not serve this in single digit milliseconds.

The answer is not to bring back the column. The answer is that a derived value can be cached, and a cache can be checked, which is the property the column never had. Two approaches, and I'd pick between them on write contention rather than read speed.

Checkpoints. Periodically write a row saying "as of entry 4,812,006 this account held 812.40". The balance is that snapshot plus everything after it.

create table balance_checkpoints (
  account_id     uuid   not null,
  currency       char(3) not null,
  as_of_entry_id bigint not null,
  balance        bigint not null,
  primary key (account_id, currency, as_of_entry_id)
);

Reads sum a bounded tail. Checkpoints are derived, so a wrong one is repaired by deleting it and recomputing, which is not a sentence you can say about accounts.balance. This is my default: nothing on the write path changes, and the cache is disposable.

Running balance on the entry. Each entry carries the account's balance immediately after it, with a (account_id, sequence) unique index to force serialisation. Reads are a single indexed row and statements come out right without any arithmetic. The cost is that every write to an account now contends on that account's latest row, which concentrates the contention on precisely the accounts everyone touches: the fee account, the merchant settlement account, the treasury account.

Either way, the reconciliation between cache and truth is a query you can run continuously:

-- A checkpoint claims the sum of every entry up to as_of_entry_id.
-- Recompute it and the drift must be zero.
select c.account_id,
       c.balance - coalesce(sum(e.amount), 0) as drift
  from balance_checkpoints c
  left join entries e
    on e.account_id = c.account_id
   and e.currency   = c.currency
   and e.id        <= c.as_of_entry_id
 group by c.account_id, c.balance
having c.balance - coalesce(sum(e.amount), 0) <> 0;

The exact shape matters less than the fact that it exists. A cached balance you can verify against the entries is a performance optimisation. A stored balance you can't is a liability with a nice index on it.

Signs, and the debit/credit conversation

At some point a finance person will look at your amount column and ask where the debits and credits are, and there's a real decision behind that.

Store one signed column. Two nullable debit and credit columns give you a state space with three illegal configurations (both null, both set, both zero) and no arithmetic without a coalesce. Debit and credit are a presentation of the sign, so present them:

select account_name,
       case when amount > 0 then amount end as debit,
       case when amount < 0 then -amount end as credit
  from entry_view where transaction_id = $1;

The part that catches engineers is that the sign of a good balance depends on the account's kind. A customer's wallet is your liability: you owe them that money. Money arriving from a customer increases what you owe them, so on your books it's a credit, even though the customer's app shows a bigger number and everyone involved is happy. Your bank account is an asset and works the intuitive way round.

This is why kind is on the accounts table. The sign is stored raw and consistent; the interpretation of the sign, whether a balance is "normal" or worrying, is a function of the account's kind, and it belongs in the reporting layer where a human is reading it. Don't push it into the entry rows and don't flip signs on the way in to make one screen read nicely, because the moment two screens disagree about the convention, the invariant reduces to noise that happens to sum to zero.

Currencies don't add up

Sum-to-zero is per currency, not per transaction. This is the constraint people relax when the first FX feature lands, and relaxing it is how you get a ledger that balances in a currency that doesn't exist.

A GBP-to-USD conversion has four legs, not two:

Customer GBP wallet      -100.00 GBP
FX position GBP          +100.00 GBP
FX position USD          -126.40 USD
Customer USD wallet      +126.40 USD

GBP sums to zero. USD sums to zero. Nothing pretends the two amounts are equal, because they aren't: they're equal at one rate, at one instant, which is a fact about the market rather than about the money.

What you gain is that the FX position accounts now hold a real number: your exposure in each currency, live, from the ledger rather than from a spreadsheet. If GBP and USD positions don't offset at the current rate, the difference is your FX gain or loss, and it was always going to exist. The choice is only whether it's a line item or a mystery.

The rule that keeps this clean: an account has exactly one currency, and it's in its name. customer:1234:GBP and customer:1234:USD are two accounts. Multi-currency accounts are how you end up with a sum(amount) that means nothing.

The invariant you can run in production

Here is the payoff, and it's a single query:

select currency, sum(amount) from entries group by currency;

Every row must be zero. Not "should be", must: it's the same statement as the trigger, evaluated over the whole table instead of one transaction. Run it on a schedule against production. If it ever returns a non-zero row, something has bypassed the constraint, and you want to hear that from a monitor rather than from a customer.

There's a family of these, and they're worth writing down as the ledger's actual specification:

  • Every currency sums to zero across all entries.
  • Every transaction sums to zero, per currency (the trigger, re-checked in bulk).
  • No account whose kind forbids it is negative. Customer wallets, usually. Suspense accounts, usually not.
  • Every checkpoint equals the recomputed sum at its entry ID.
  • Every entry belongs to a transaction that exists, and no transaction has one leg.

Each is one query and none takes more than a few minutes to write. The set of them is a stronger correctness statement than most financial systems have, and unlike unit tests it's a claim about the real data.

The single-leg check is worth calling out. Under the deferred trigger it should be impossible, but it catches the specific incident where somebody disabled the trigger for a bulk load and the load half-failed. That has happened to me. The check found it in eleven minutes.

What this doesn't buy you

  • Not correct amounts. A perfectly balanced transaction can move £10,000 instead of £100.00. Sum-to-zero has no opinion about magnitude, and a decimal shift passes every constraint in this post.
  • Not the right accounts. Money moved from the wrong customer to the wrong merchant balances beautifully.
  • Not reconciliation. The ledger being internally consistent says nothing about whether it matches the bank. Internal consistency is a precondition for that comparison, not a substitute.
  • Not idempotency. The unique external_ref is what stops a duplicate transaction. Double entry will happily record the same payment twice, in perfect balance, both times.
  • Not performance for free. You've traded a cheap read and an unverifiable number for an expensive read and a checkable one, and you'll spend real work on the caching.
  • Not an accounting system. You have a ledger, which is the substrate. Period ends, trial balances, revenue recognition and a chart of accounts a finance team has signed off on are all still ahead of you.

What it does buy is a floor. Below a certain kind of catastrophe you cannot go, and the class of bug where money quietly evaporates between two UPDATE statements has been designed out rather than tested for.

The short version

  • The mutable balance column is the bug. It's a claim with no evidence and nothing to check it against.
  • Double entry is one invariant: entries against named accounts, summing to zero. The vocabulary is optional.
  • Balanced does not mean two rows. Fees, splits and FX have three or four legs and are perfectly normal.
  • Give the outside world accounts, or you'll write a single-sided entry in week one.
  • Enforce sum-to-zero with a deferred constraint trigger, because the database sees every write and your service layer doesn't.
  • Never update or delete an entry. Corrections are new transactions, and the visible mistake is the point.
  • Separate occurred_at from recorded_at at the start. You cannot recover the distinction later.
  • Balances are derived. Cache them with checkpoints you can recompute, never with a column you can't verify.
  • Store a signed amount; derive debit and credit for display. The sign of a good balance depends on the account's kind.
  • Sum-to-zero is per currency. One currency per account, and FX gets a position account on each side.
  • select currency, sum(amount) from entries group by currency must return zeros in production, on a schedule, forever.

Two things to go and look at today. Find every place in your codebase that writes to a balance column and count them; if there's more than one, you already have two definitions of what a balance is, and the reason nobody has noticed is that nothing in the system is able to. Then take last month's data and try to answer, in SQL alone, where a specific £50 went. If the answer requires application logs, the ledger isn't a record of what happened, it's a summary of what you meant.

← All writing