A Payments Ledger: Why It's Append-Only, and What Idempotency Keys Actually Protect

The obvious design for money is a balance column you add to and subtract from. It is also the one design that is unambiguously wrong, and the reason is not performance or concurrency. It is that UPDATE accounts SET balance = balance - 50 destroys the only thing anyone will ever ask you for.

When a customer disputes a charge, when an auditor asks why an account is short, when a bug corrupts a number and you have to work out which numbers it touched — the question is never “what is the balance?”. It is “how did it get to be this?”. A column that has been overwritten 4,000 times cannot answer that. It has one value and no memory.

Entries, not balances

A ledger stores immutable entries. Nothing is ever updated; nothing is ever deleted. A balance is not stored at all — it is derived, by summing the entries.

Every movement of money is at least two entries that sum to zero: one account is debited, another is credited. This is double-entry bookkeeping, it is about six hundred years old, and its useful property is that the sum of every entry in the system is always zero. That single invariant catches an entire class of bug at the point it happens, because a transaction that doesn’t balance is rejected rather than written.

Look at what a refund does. It does not modify the original charge. It cannot: the charge happened, and pretending otherwise makes the history a lie. A refund is a new pair of entries moving money the other way, and the account’s history now correctly says “charged £50, then refunded £50”, which is what actually occurred — not “£0, and nothing to see”.

The same applies to mistakes. You never delete a bad entry; you write a reversal, an equal and opposite pair that references the original. The account nets to the right number and the record still shows what went wrong and when it was corrected. Auditors want the second part. So does the engineer debugging it eighteen months from now.

A few things follow from immutability that are worth stating outright:

Store money as integers, in the smallest unit. 4999 minor units, not 49.99. Binary floating point cannot represent 0.10 exactly, and money is one of the very few domains where the error is not merely inelegant but wrong — it accumulates across millions of rows and the total stops reconciling. Use BIGINT, or your database’s exact DECIMAL. Never a float.

Carry the currency on every entry, and refuse to sum across currencies. A ledger that adds 100 JPY to 100 USD produces a number that means nothing.

Keep a snapshot, but derive the truth. Summing four million entries on every balance check is a real cost. Cache a running balance in a separate table, computed incrementally — but treat the entries as the source of truth and reconcile the snapshot against a full recomputation on a schedule. If they ever disagree, the entries are right and the snapshot is a bug.

The retry problem

Now the second half, which is what actually causes real money to be lost.

A client sends POST /payments. Your server processes it and returns 200. The response is lost in transit — a load balancer resets, a mobile network drops. The client saw a timeout, knows only that it does not know, and does the sensible thing: retries.

You now have two identical requests, and no way to tell whether the second one is a retry of the first or a customer genuinely paying twice. There is no way to distinguish them from the request alone, and this is not an edge case — at scale it happens constantly.

The fix is that the client supplies a unique idempotency key with the request, and you promise: for the same key, the first request is executed and every subsequent one returns the original result without executing anything.

The details are where implementations go wrong:

The key must be generated per logical operation, not per attempt. A client that generates a fresh UUID on each retry has an idempotency key that idempotency-protects nothing. Generate it when the user presses the button, reuse it for every retry of that press.

Store the key in the same transaction as the ledger entries. If you write the key to Redis and the entries to Postgres, a crash between them leaves you with a key that says “done” and no money moved — or entries with no key, and the retry charges again. One transaction, one atomic commit. A UNIQUE constraint on the key column is what actually enforces this: the second concurrent insert fails on the constraint rather than racing. This is the same lesson as Part 1’s check-then-act race, in a different costume — a SELECT to see whether a key exists, followed by an INSERT, has a gap in it, and here the gap costs real money.

Store the response, not just the fact of completion. The retry must receive the same body and status the original did, or the client cannot tell what happened.

Detect key reuse with different parameters. If a key arrives with a payload that doesn’t match the one it was created with, that is a client bug, and the safe answer is 422 rather than either executing it or returning someone else’s result.

Handle the concurrent in-flight case. Two retries can arrive while the original is still running. The second must not proceed and must not return a wrong answer: mark the key as in-progress on insert and return 409 Conflict to anything that arrives while it is, so the client retries in a moment.

Expire keys, and say for how long. Keeping every key forever is unbounded storage; 24 hours is typical and is long enough to cover any retry that a client would sanely make. Put the window in your API documentation, because it is part of the contract.

What this buys at the boundary

The awkward truth of payments is that the money moves in someone else’s system. Your ledger records an intention; a card network or a bank decides what actually happened, sometimes seconds later, sometimes days later.

So model the states explicitly rather than pretending the operation is instantaneous: pending, then settled or failed. Each transition is new entries, never an update to old ones — a pending authorization credits a holding account, and settlement moves it from there. Reconcile daily against the provider’s own report, and treat a mismatch as a page, not a warning, because a ledger that has silently diverged from the money is worse than one that is obviously broken.

Webhooks from the provider arrive out of order, more than once, and occasionally never. That is not a defect of any particular provider; it is what at-least-once delivery means. The ledger’s immutability is what makes it survivable: a duplicate settlement webhook, keyed on the provider’s event id, is a no-op instead of a second credit.

The short version

Money is a sequence of events, so store the sequence. Immutable double-entry entries that sum to zero, integers in the smallest currency unit, currency on every row, balances derived rather than stored, and corrections written as reversals instead of edits — because the question you will actually be asked is “how did this happen?”, and only the history can answer it.

Then make every write safely retryable, because the network guarantees you will get duplicates. A client-generated idempotency key, one per logical operation rather than per attempt, stored under a UNIQUE constraint in the same transaction as the entries it protects, returning the original response verbatim on replay and 409 while the original is still in flight.

Neither half is difficult. Both are extremely hard to retrofit onto a balance column and a POST endpoint that assumed the response always arrives, which is the actual reason to get them right at the start.