Design a payments ledger

45 minutes. "Design the ledger behind a payments product. It must never lose money and must reconcile with the bank."

Step 1: clarify (3 minutes, out loud)

The questions that change the design, and the answers I will assume:

• What is the ledger the source of truth for? Internal balances. The card network and the bank are external systems we reconcile against; we do not attempt to be authoritative about their state.
• Scale? 5,000 transactions per second at peak, 200 million accounts, seven-year retention for audit.
• Consistency requirement? A balance read must never show money that does not exist. Slight staleness on a display balance is acceptable; an authorisation decision must not be.
• Multi-currency? Yes, and currencies never mix within a single entry.
• Who reads it? The product (balance display, transaction history), risk (velocity checks), finance (reconciliation, reporting), and auditors.

Then say the non-functional requirement that governs everything: the ledger is append-only and every entry is immutable. Corrections are new entries, never edits. That single decision determines the schema, the API, the audit story and half the follow-up answers.

Step 2: capacity math (2 minutes)

Writes
  5,000 transactions/sec at peak
  Double-entry: every transaction writes >= 2 rows -> 10,000 rows/sec
  Average 2.4 legs (fees, FX) -> ~12,000 rows/sec peak

Storage
  Row: id(16) + txn_id(16) + account_id(16) + amount(8) + currency(3)
       + direction(1) + created_at(8) + metadata(~120) ~= 190 bytes
  Indexes roughly double it -> ~400 bytes/row effective
  12,000 rows/sec x 400 B = 4.8 MB/sec at peak
  Average is ~30% of peak -> ~1.4 MB/sec -> ~44 TB/year
  Seven years -> ~310 TB. Partitioned by month, cold tiers to object storage.

Reads
  Balance reads: ~50,000/sec (every page view)
  History reads: ~5,000/sec
  Read:write ratio ~5:1 on transactions, but balance reads dominate
  -> balance must NOT be computed by summing history

That last line is the design conclusion the math produces, and it is worth deriving in front of the interviewer rather than asserting.

Step 3: the data model

Double-entry, and why

Every movement of money is recorded twice: a debit somewhere and a credit somewhere else, and the sum of all entries in a transaction is exactly zero.

CREATE TABLE accounts (
    id            uuid PRIMARY KEY,
    type          text NOT NULL,      -- user_wallet, fee_revenue, bank_settlement,
                                      -- card_network_receivable, fx_position
    currency      char(3) NOT NULL,
    normal_side   text NOT NULL,      -- 'debit' or 'credit'
    created_at    timestamptz NOT NULL DEFAULT now()
);

-- The immutable fact table. No UPDATE, no DELETE, ever.
CREATE TABLE entries (
    id            bigserial PRIMARY KEY,
    transaction_id uuid NOT NULL,
    account_id    uuid NOT NULL REFERENCES accounts(id),
    -- Minor units as an integer. NEVER a float; 0.1 + 0.2 != 0.3 and a
    -- payments system that uses floats will eventually be off by a cent
    -- in a way nobody can explain.
    amount        bigint NOT NULL CHECK (amount > 0),
    direction     text NOT NULL CHECK (direction IN ('debit','credit')),
    currency      char(3) NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE transactions (
    id              uuid PRIMARY KEY,
    idempotency_key text UNIQUE NOT NULL,   -- the whole safety story, see below
    kind            text NOT NULL,          -- payment, refund, fee, payout, adjustment
    external_ref    text,                   -- card network / bank reference
    created_at      timestamptz NOT NULL DEFAULT now()
);

A card payment of £10.00 with a 30p fee is one transaction with four entries:

Debits 1000, credits 1000. Balanced.

Why double-entry rather than a balance column you increment: it makes the invariant checkable. At any moment you can sum every entry in the system and it must be zero; if it is not, you have a bug and you know it within one reconciliation cycle rather than at year end. A single-entry design has no such property, so an error is undetectable until somebody complains.

Enforce the invariant in the database, not in application code:

-- Balanced-transaction check, per currency, at commit time.
CREATE CONSTRAINT TRIGGER entries_balance
AFTER INSERT ON entries DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION assert_transaction_balances();
-- The function sums signed amounts grouped by (transaction_id, currency)
-- and raises unless every group is zero.

Deferred is essential: the check must run at commit, after all legs are inserted, not after each row.

Balance as a projection

Balance is derived, never authoritative. Two representations, and you need both:

-- Fast path: a materialised balance, updated in the same transaction as the
-- entries. This is the CQRS read model, and it is a cache with a proof.
CREATE TABLE account_balances (
    account_id     uuid PRIMARY KEY REFERENCES accounts(id),
    balance        bigint NOT NULL,
    last_entry_id  bigint NOT NULL,   -- the watermark that makes it verifiable
    version        bigint NOT NULL    -- optimistic concurrency
);

The last_entry_id watermark is what makes this defensible rather than a duplicate source of truth: a background job can recompute the balance from entries up to that id and assert equality. If it ever disagrees, you have found a bug and you know exactly which entries to examine.

Step 4: the write path

def post_transaction(idempotency_key, legs):
    """One database transaction. Either every leg lands or none does.

    The ordering matters: idempotency check first, because a retried request
    must return the original result rather than doing the work twice.
    """
    with db.transaction(isolation="repeatable read"):
        existing = db.query(
            "SELECT id FROM transactions WHERE idempotency_key = %s",
            idempotency_key)
        if existing:
            return existing.id                    # replay: same answer, no work

        txn_id = uuid4()
        db.execute("INSERT INTO transactions (id, idempotency_key, kind) "
                   "VALUES (%s,%s,%s)", txn_id, idempotency_key, kind)

        for leg in legs:
            db.execute("INSERT INTO entries (transaction_id, account_id, amount, "
                       "direction, currency) VALUES (%s,%s,%s,%s,%s)",
                       txn_id, leg.account, leg.amount, leg.direction, leg.currency)

        # Update the projection in the SAME transaction. Two properties follow:
        # the balance is never stale for the account that just moved, and the
        # conditional UPDATE is where the overdraft invariant is enforced.
        for account, delta in net_deltas(legs):
            updated = db.execute(
                "UPDATE account_balances SET balance = balance + %s, "
                "  last_entry_id = %s, version = version + 1 "
                "WHERE account_id = %s AND balance + %s >= 0",   # <- the invariant
                delta, max_entry_id, account, delta)
            if updated.rowcount == 0:
                raise InsufficientFunds(account)   # rolls the whole thing back
    return txn_id

Three things to say about this code, because they are the design:

Idempotency is the API contract, not a retry helper. Every write endpoint takes a client-supplied key, the key is unique-constrained, and a replay returns the original result. Without it, a client timeout on a successful request produces a double charge, and clients time out constantly. The key must be scoped (per merchant, per endpoint) so two merchants cannot collide, and stored with the result rather than just the fact of having seen it.

The conditional UPDATE is the concurrency control. WHERE balance + delta >= 0 makes the overdraft check and the write a single atomic operation. The read-check-then-write alternative is a textbook write skew: two concurrent withdrawals each read a sufficient balance, each write, and the account goes negative with no error. Making the check part of the write removes the race by construction.

Everything is in one transaction. No sagas, no eventual consistency, no compensations. This is deliberate and it is the reason to keep the ledger in a single relational database for as long as possible: the invariant "money is conserved" is exactly what ACID transactions are for, and every distributed alternative is strictly harder.

Step 5: hot accounts, and where this design breaks

Every payment credits fee_revenue. At 5,000 transactions per second, that is one row taking 5,000 updates per second, and row-level lock contention makes it the bottleneck long before the database is otherwise stressed.

Two fixes:

Sharded counters for accounts that only accumulate:

-- 64 shards. Writers pick one at random; readers sum. Contention drops 64x.
CREATE TABLE account_balance_shards (
    account_id uuid, shard smallint, balance bigint,
    PRIMARY KEY (account_id, shard)
);

No projection at all for accounts nobody needs a real-time balance for. fee_revenue is read by finance once a day; compute it by summing entries in the batch job and skip the hot row entirely.

The general rule: the projection exists for accounts whose balance gates a decision. A user wallet needs it because an authorisation depends on it. A revenue account does not.

Step 6: reconciliation

The ledger is internally consistent by construction. It still has to agree with the outside world, and that is a separate daily process:

1. Internal invariant. SELECT sum(signed_amount) FROM entries grouped by currency must be zero. Runs continuously; a non-zero result pages immediately, because it means the balanced-transaction constraint has been bypassed.
2. Projection check. Recompute balances from entries up to each account's last_entry_id and compare. Any disagreement identifies a bug and bounds it.
3. External reconciliation. Match settlement files from the bank and card network against external_ref. Three outcomes: matched, in-ledger-not-in-bank (usually timing, sometimes a real loss), in-bank-not-in-ledger (always investigate, it means money moved that we did not record).
4. Break resolution. Unmatched items age into a queue with an owner and an SLA. The metric that matters is aged breaks, not break count: a break found and resolved same-day is normal operations, a break older than five days is a control failure and an audit finding.

Corrections are new balancing entries, never edits. If we credited the wrong account, we post a reversing pair plus the correct pair, with the original transaction referenced. The history shows the mistake and the fix, which is what an auditor requires and what makes the ledger trustworthy.

Step 7: failure modes and degradation

The thing to say out loud: in a ledger, the correct failure mode is to refuse, not to guess. Availability is worth less than correctness here, and that is the opposite of the default answer in most system design questions. Saying so explicitly signals that you have calibrated the tradeoff to the domain rather than reaching for a template.

Step 8: what changes at ten times the scale

50,000 transactions per second exceeds a single Postgres primary. The migration path, in order:

1. Partition by time, already done. Old partitions become read-only and tier to cheaper storage.
2. Shard by account. Account id is the partition key, and the awkward case is a transaction touching accounts in two shards. Options: co-locate accounts that transact together (a merchant and its sub-accounts in one shard), accept a two-phase commit for the minority of cross-shard transactions, or restructure so cross-shard movements go through a clearing account in each shard, turning one distributed transaction into two local ones.
3. Consider a purpose-built engine. TigerBeetle is a database designed specifically for double-entry accounting, with the balance invariants built in and throughput orders of magnitude above a general-purpose relational database for this workload. Naming it shows awareness that this problem has specialised tooling.

The thing I would not do is move to an eventually consistent store and reconcile the invariant asynchronously. That converts "money is conserved" from a property the database enforces into a property a batch job hopes for.

Production evidence

Stripe made idempotency keys a first-class part of their public API, documented as the mechanism for safely retrying requests, and their engineering writing on rate limiting and idempotency is the standard reference for how a payments API should behave under client retries.

Square/Block published on their ledger architecture and the double-entry model behind it; Uber's LedgerStore write-up describes their move to an immutable, append-only ledger with strong idempotency guarantees for their payments platform, at a scale where the sharding questions above are real.

TigerBeetle is a purpose-built distributed financial accounting database whose entire design premise is that general-purpose databases are the wrong shape for double-entry at high throughput; its documentation is a good source on why contention on hot accounts is the binding constraint.

Double-entry bookkeeping itself dates to Pacioli in 1494 and is the oldest piece of engineering in this design. That is not a joke: the reason it survives is that the balance invariant makes errors detectable, and no subsequent scheme has improved on that property.

The debate

The alternative is a single-entry balance column updated per transaction. It is simpler, faster, and adequate for a system where money never leaves (loyalty points, in-game currency, credits with no cash value).

It fails the moment you must answer "where did this money come from" or reconcile with an external party, because there is no audit trail and no invariant. An error is invisible until someone notices a discrepancy, and then it is unbounded: you cannot tell when it started or how much is affected.

My position: double-entry, append-only, integers in minor units, idempotency keys on every write, and balances as a verifiable projection with a watermark. Keep it in one relational database for as long as possible, because the invariant you care about is exactly what a transaction gives you, and every distributed alternative makes it harder rather than easier.

This design is wrong when the "money" has no external counterparty and no audit requirement, where the complexity buys nothing; and it is the wrong first system when the product has not proven it needs a ledger at all, because a ledger you cannot change is expensive to get wrong early.

Follow-up Q&A

"Why not just store a balance?" Because a balance alone has no invariant to check. Double-entry gives you one: the sum of every entry in the system is zero, per currency, always. That turns a class of bugs from silent and unbounded into detectable within one reconciliation cycle. It also gives you the audit trail regulators require, and it means a correction is a new entry rather than an edit, so history is never rewritten.

"How do you prevent double-charging on a client retry?" An idempotency key supplied by the client, unique-constrained in the database, checked inside the same transaction that writes the entries, and returning the original result on replay. The key must be scoped per client so two clients cannot collide, and the stored record must include the result, not just the fact of having seen the key, so the replay returns the same transaction id. Client timeouts on successful requests are routine, so this is the primary safety mechanism, not a nicety.

"Two withdrawals arrive at once and the account goes negative. What happened?" Read-check-then-write. Both read a sufficient balance, both decide to proceed, both write. Under snapshot isolation there is no write-write conflict if they touch different rows, and even on the same row a read outside the update is not protected. The fix is to make the check part of the write: UPDATE balances SET balance = balance + :delta WHERE account = :id AND balance + :delta >= 0, and treat zero affected rows as insufficient funds. The check and the write are then a single atomic operation.

"How do you handle multi-currency?" Never mix currencies within an entry, and never sum across currencies. A currency conversion is a transaction with four entries: debit the source currency account, credit an FX position account in the source currency, debit the FX position in the target currency, credit the destination. Each currency balances independently, and the FX position accounts hold the exchange gain or loss, which is exactly what finance needs to see. The balanced-transaction constraint therefore groups by (transaction_id, currency), not by transaction alone.

"Where does eventual consistency show up, and how do you handle it?" In the display balance if you ever move the projection out of the write transaction, and in downstream systems (analytics, risk, notifications) reading a change stream. For the originating user, keep the projection in the write transaction so they always see their own money immediately. For everyone else, publish entries via a transactional outbox so downstream consumers get an ordered, exactly-once-effective stream without a dual-write. Authorisation decisions always read the transactional path, never a derived store.

"How do you correct a mistake?" Post reversing entries plus the correct entries, in a new transaction that references the original. Never update or delete an entry. The invariant stays intact, the audit trail shows both the error and the remedy, and the balance ends up correct. Operationally, adjustments need a separate authorisation path (maker-checker, with the approver recorded) because the ability to post arbitrary entries is the ability to create money.

Common misconceptions

The most damaging is representing money as a floating-point number. Use integers in the currency's minor unit. Every payments engineer learns this once, and learning it in production is expensive.

The second is treating the balance as the source of truth. Entries are the truth; the balance is a projection with a watermark that lets you verify it. Teams that invert this end up unable to explain a discrepancy.

The third is that idempotency is about retries. It is about the fact that the client and server can disagree about whether a request succeeded, which is unavoidable over a network, and the key is what makes the disagreement harmless.

Interview delivery note

Open with the invariant, because it frames everything else: "The ledger is append-only and every entry is immutable. Corrections are new entries, never edits. That gives me an invariant I can check continuously: the sum of all entries is zero, per currency."

Then the three design decisions, quickly: "Double entry, so errors are detectable. Integers in minor units, because floats lose cents. Idempotency keys on every write, because clients time out on successful requests and a double charge is worse than a failed one."

The depth signals, in order of impact: the conditional UPDATE as the overdraft check, because it shows you know why read-check-write is a write-skew bug; the hot revenue account as the real bottleneck, because it shows you have thought about contention rather than throughput; and the degradation stance, that a ledger should refuse rather than guess, because it shows you calibrated availability against correctness for this domain rather than reaching for the default answer.

Further reading

• Stripe's API documentation on idempotent requests, and their engineering blog on designing robust APIs.
• Uber Engineering, "LedgerStore" and the surrounding payments-platform posts, for the sharding and immutability decisions at scale.
• TigerBeetle's documentation on why double-entry accounting is a poor fit for general-purpose databases, particularly on hot-account contention.
• Martin Fowler's Analysis Patterns, the accounting chapters, for the account-and-entry model as a domain pattern.