The ledger you wish you had built first

Almost every payments system starts with a balance column. It is the obvious model: an account has money, money is a number, store the number. Deposits add to it, withdrawals subtract, and the code reads exactly like the mental model everyone already has.

It works until the first dispute. Someone asks why an account shows a figure that does not match what they expect, and the answer has to be reconstructed from application logs, because the balance column records the current state and nothing about how it got there.

A balance is a conclusion, not a fact

The correction is to stop storing the balance and start storing the movements. Entries are immutable. A balance is their sum. Nothing ever updates a balance because there is no balance to update.

This inverts the failure mode in a useful direction. With a mutable balance, a bug corrupts the truth and the history is gone. With immutable entries, a bug produces a wrong entry that is visible, attributable, and correctable by appending a reversal — which is what every accounting system built in the last five centuries does.

CREATE TABLE ledger_entries (
  id            bigserial PRIMARY KEY,
  transfer_id   uuid        NOT NULL,
  account_id    uuid        NOT NULL REFERENCES accounts(id),
  -- Minor units, always. Never a float: 0.1 + 0.2 is not 0.3, and a
  -- rounding error in a ledger is indistinguishable from a theft.
  amount        bigint      NOT NULL,
  currency      char(3)     NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT amount_non_zero CHECK (amount <> 0)
);

-- Every transfer must net to zero across its entries. Money is moved
-- between accounts, never created at one end of a write.
CREATE CONSTRAINT TRIGGER transfer_balances
  AFTER INSERT ON ledger_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_transfer_sums_to_zero();

REVOKE UPDATE, DELETE ON ledger_entries FROM application_role;

Every movement has two sides

The discipline that makes this work is that money is never created or destroyed within the system — it only moves. A customer deposit is not an increase in their balance; it is a movement from an external settlement account into theirs. A fee is a movement from the customer to revenue.

Because every transfer nets to zero, the sum of every entry in the system is always zero. That single invariant is checkable at any moment, and when it does not hold you have found a bug without needing anyone to report a symptom.

Note that the chargeback is not a deletion of the deposit. The original entries stand and a new pair reverses them. The history says a payment arrived and was later reversed, which is what happened. A system that deletes the original says the payment never occurred, which is false and, in a regulated context, is a finding.

But summing every entry is slow

This is the objection that sends teams back to a mutable balance, and it is solved the same way every accounting system has solved it: periodic closing balances. A materialised snapshot per account per day, with the current balance computed as the most recent snapshot plus entries since.

• Snapshots are derived, so they can be rebuilt from entries at any time.
• A rebuild that disagrees with the stored snapshot is a bug alarm, not a data loss event.
• Index on (account_id, id) so the tail read after a snapshot is a narrow range scan.
• Keep snapshots off the write path — a nightly job is enough, and it never blocks a transfer.

Reconciliation becomes a query

The real dividend arrives when the provider's settlement file does. With a ledger, reconciliation is a comparison between two sets of movements over the same window, and a discrepancy points at a specific transfer on a specific day.

Without one, reconciliation is an investigation. The provider says a figure, the system says another, and closing the gap means reading application logs to reconstruct a history that was never recorded as history. We have watched teams spend a week per month on this. It is the single most expensive consequence of the balance column, and it is entirely avoidable.