Payment Ledger Design

A ledger is not a record of balances. It is a record of movements, from which balances are a consequence. Every design decision on this page follows from taking that literally, and the shortest way to state it is the rule the rest of the page justifies: there is no statement in the system that updates an amount. Money is recorded by inserting rows. Mistakes are recorded by inserting more rows.

The reason is evidential, not aesthetic. UPDATE accounts SET balance = balance - 100 produces the right number and destroys the only copy of the previous one. When somebody asks in three months why a balance was what it was on a particular afternoon - a customer, a colleague in finance, an auditor, or you at two in the morning - the answer has been overwritten. An append-only ledger answers it by replaying entries, and answers every variant of the question with the same mechanism.

Double entry as a schema

Double entry is a constraint, not a convention. Every movement is recorded twice, once as a debit and once as a credit, and the two must net to zero. That single invariant means an error cannot create or destroy value; it can only misplace it, and misplaced value is findable.

Two tables carry it.

-- The unit of intent. One row per business event, whatever number of legs it needs.
CREATE TABLE ledger_transaction (
    txn_id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    idempotency_key text        NOT NULL,
    txn_type        text        NOT NULL,
    external_ref    text,            -- scheme or bank reference, e.g. an end-to-end id
    occurred_at     timestamptz NOT NULL,   -- when the money moved
    recorded_at     timestamptz NOT NULL DEFAULT clock_timestamp(),  -- when we learned
    reverses_txn_id bigint REFERENCES ledger_transaction (txn_id),
    CONSTRAINT ledger_txn_idempotent UNIQUE (txn_type, idempotency_key)
);

-- The legs. Append-only: no UPDATE, no DELETE, enforced by privilege and by trigger.
CREATE TABLE ledger_entry (
    entry_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    txn_id     bigint      NOT NULL REFERENCES ledger_transaction (txn_id),
    account_id bigint      NOT NULL REFERENCES ledger_account (account_id),
    -- Signed minor units. One column, not a debit column and a credit column:
    -- two nullable columns admit a row that is neither, and a sign cannot be forgotten.
    amount     bigint      NOT NULL,
    currency   char(3)     NOT NULL,
    CONSTRAINT ledger_entry_nonzero CHECK (amount <> 0)
);

CREATE INDEX ledger_entry_account ON ledger_entry (account_id, entry_id);

Three decisions in there are worth defending.

Integer minor units, never floating point. An amount is a count of the smallest unit the currency defines. Floating-point arithmetic on money produces sums that differ depending on the order of addition, which in a ledger is a reconciliation failure with no cause. Currency is stored per entry, and no constraint permits entries of different currencies to net against each other. Foreign exchange is two transactions and a rate, recorded explicitly.

Both timestamps. occurred_at is when the money moved in the outside world; recorded_at is when this system learned about it. They differ routinely, by seconds for a card authorisation and by days for a returned direct debit, and every interesting reporting question depends on knowing which one is meant. A ledger with one timestamp cannot answer “what did we believe the balance was yesterday” and “what was the balance yesterday” as separate questions, and both get asked.

Reversal by reference, not by mutation. A correction is a new transaction whose entries are the negation of the original, linked by reverses_txn_id. The original remains, wrong, in the record. That is the point. A ledger that can be silently corrected is a ledger whose history means nothing.

Balances are derived

The balance of an account is SUM(amount) over its entries. That is the definition, and it should be the implementation until measurement says otherwise.

SELECT coalesce(sum(amount), 0) AS balance_minor
FROM   ledger_entry
WHERE  account_id = $1
  AND  currency   = $2;

This scales further than people expect, because the index above makes it an ordered index scan, and because the interesting accounts are usually not the ones with a hundred million entries. When it does stop scaling, the correct next step is a snapshot, not a mutable column: a periodic row recording the balance as at a specific entry_id, with current balance computed as the snapshot plus the entries after it. Snapshots are themselves append-only and derivable, so a bug in snapshotting is recoverable by discarding them and recomputing.

The alternative (a running-total column on the entry itself, written in the same transaction) is also acceptable, because it is still append-only: each row records the balance after that entry. It has the pleasant property that any historical balance is a single row lookup, and the unpleasant one that it serialises all writes to an account, which is the contention issue described below.

What is not acceptable is a balance column that is updated. It cannot be verified against anything, it drifts, and the drift is discovered by a customer.

Idempotency is a schema property

A payment API will be called more than once for the same payment. Not occasionally: routinely, and by systems you do not control. A client times out after the write committed but before the response arrived. A queue redelivers. A retry policy fires. An operator replays a batch. In each case the second request is a duplicate of the first, and the only thing that distinguishes “charge this customer twice” from “charge them once, and tell me about it twice” is whether the request carried a key and whether the schema enforced it.

The enforcement point is the unique constraint on (txn_type, idempotency_key) above, and the sequence has to be exactly this:

1. Client generates the key. It must be derived from the client’s intent, not from the request payload or a timestamp. A payment retried with a regenerated key is a new payment by definition, and no server-side logic can recover the difference.
2. Server begins one transaction.
3. Server inserts the ledger_transaction row and all of its ledger_entry rows.
4. Server commits. If the insert raised a unique violation, the payment was already recorded: load the existing transaction and return its result, with the same status code as the original.

The failure mode to avoid is the check-then-insert: SELECT for an existing key, and insert if absent. Two concurrent retries both see nothing and both insert, and no isolation level below serializable prevents it because there is no row to conflict on until one of them writes it. Let the constraint do the work: a unique violation is a successful outcome here, and the handler should treat it as one.

The response also has to be stored, or at least reproducible. A duplicate request must get the same answer as the original, including the identifiers it will use for its own reconciliation. Returning a different reference on the retry pushes the ambiguity downstream to a system with less context.

Where the payment is instructed over an ISO 20022 message chain, the message already carries a mandatory end-to-end identification and, increasingly, a unique end-to-end transaction reference (BIS CPMI, 2026). Store those on the transaction and use them as the correlation key against statements and returns. Do not invent a parallel identifier scheme when the message defines one; the whole value of the reference is that every party in the chain uses the same one.

Reconciliation is the control that makes the rest true

An internal ledger records what this system believes happened. The authoritative record of what actually happened is held by a bank, a scheme, or a processor. Those two records will differ, and a ledger design that does not include the process for comparing them is not finished.

The daily loop:

The suspense account is the part people leave out and the part that does the work. Its balance is a direct measure of how much of your ledger you cannot currently explain. If it is zero at the end of each cycle, reconciliation is genuinely closing. If it grows steadily, some category of event has no handling and the ledger is quietly diverging from reality. It belongs on a dashboard next to the error rate.

Match on the external reference first and only fall back to heuristics. Amount-and-date matching finds pairs that are not pairs, particularly for round-number amounts and repeated subscription charges, and a false match is worse than an open item because it removes the evidence that something is wrong.

Concurrency, and the overdraft question

The invariant “this account may not go negative” is where a ledger meets contention. Checking it requires knowing the balance, which requires that no other transaction is concurrently changing it, which serialises writes to that account.

Three approaches, in increasing order of complexity:

Start with the first. It is correct, it is one line, and for the overwhelming majority of accounts the contention never materialises because a given customer is not transacting concurrently with themselves. It becomes a problem on shared accounts - a merchant settlement account, a fee income account, a float - and the fix for those is specific: fee and income accounts do not need an overdraft check at all, because they are not constrained, so they can take unlocked inserts and be summed later.

Do not reach for the third pattern until the first has been measured and found wanting on a specific account. It is the correct answer for card authorisation flows, where the reservation is inherent to the business process anyway, and it is unnecessary machinery everywhere else.

Durability and audit

Two operational requirements attach to a ledger that do not attach to an ordinary application database.

A commit that has been acknowledged must not be lost. This is the case where synchronous replication is the right trade: block writes when no synchronous standby is available rather than accept a commit that exists on one node only. The high-availability topology describes the mechanism; the ledger is the workload that justifies its strict variant, accepting a write outage in exchange for durability.

The audit trail is part of the system, not a byproduct. PCI DSS requires audit logs of access to system components and cardholder data, retained for at least 12 months with the most recent three months immediately available for analysis (PCI DSS v4.0.1, requirement 10.5.1). Two consequences for the design. First, log retention has to be planned as storage with a hot tier, not left to whatever the log shipper defaults to. Second, and more usefully, keep card data out of the ledger entirely: store a token and a scheme reference, never the primary account number and never authentication data, which requirement 3 prohibits retaining after authorisation. A ledger that contains no cardholder data is a ledger whose retention obligations are set by accounting rules rather than by the cardholder data environment, and that is a materially simpler system to operate for the next decade, which is roughly how long the entries will need to be readable.