Design a Payment System: Double-Entry Ledger and Idempotency

"Design a payment system" can drift into card networks, fraud models and payouts. The heart of it, and the part interviewers dig into, is the ledger: the record of every movement of money, from which every balance can be explained line by line. Get the ledger right and the rest of the system can retry, crash and be audited without anyone losing a cent.

The problem it solves

The lesson's illustrative numbers: 2,000 transfers a second, and one rule that shapes everything else: no row is ever updated in place. The requirements that follow:

• Every balance is explainable. An auditor can ask why an account holds 212.40 and get the list of entries that sum to it.
• Money is conserved. A transfer never exists on one side only.
• Retries are safe. Networks time out and clients retry; a retry must never become a second payment.
• Corrections are visible. A mistake is fixed by a new entry, not by quietly changing history.

A naive design keeps a balance column and runs UPDATE accounts SET balance = balance - 40. It is fast, cannot answer "why", and turns every retry into a double charge.

The intuition

Four mechanisms answer them:

1. Double entry. Every transaction is a set of legs, debits negative and credits positive, that must sum to zero. Moving 40.00 from A to B is two rows: A −40.00, B +40.00. Money is conserved by construction, and the whole ledger always sums to zero, a cheap global check.
2. One transaction for all legs. Both rows commit together or neither does, which is exactly what ACID transactions provide. Any balance checks, such as "no overdraft", run inside the same transaction under a lock or a strict isolation level.
3. Idempotency keys. The client sends a unique key with each payment, stored with the result in the same transaction; a retry with that key gets the original answer and writes nothing. The same key with a different body is refused: it is a bug, not a retry. The HTTP side of this is in REST API idempotency.
4. Derived balances with snapshots. A balance is the sum of an account's entries. That is always correct and gets slower every year, so a periodic job stores a snapshot at a known entry position; today's balance is the snapshot plus the short tail after it.

Store amounts as integer minor units (cents), never floats. And keep a link from each ledger transaction to the external payment it mirrors, so a daily reconciliation against the processor's or bank's records can catch disagreements.

Watch it run

The animation follows one transfer: move 40.00 from A to B, under the rule that no row is ever updated. The request carries an idempotency key, 9f2, so a retry cannot become a second payment. The key has never been seen, so the transfer is allowed to proceed. Double entry: the money leaves one account and arrives in another, as two rows. If the two sides do not sum to zero, as in a debit of 40.00 against a credit of 39.00, the write is refused rather than fixed later. Balanced, so both rows commit in one transaction, or neither does. The caller retries after a timeout; the key is recognised and nothing is written, still two rows. A mistake is never edited out: a reversing pair is appended underneath, B −40.00 and A +40.00, and the history stays intact. A balance is then the sum of every entry, correct and slower every year: 18 million rows after five years. So a periodic job fixes each balance at a point in time and stores it, and today's balance is that snapshot plus whatever arrived after it, a short sum of 2,100 rows. The last frame is the price: one transaction across two accounts is exactly what does not shard easily.

The code

A toy model on Python's built-in SQLite, so the transaction is a real one. First, why cents: floats cannot even add 0.1 and 0.2 exactly:

import sqlite3

print(0.1 + 0.2 == 0.3, 10 + 20 == 30)          # False True  -> store integer cents

SCHEMA = """
CREATE TABLE entries (id INTEGER PRIMARY KEY, txn TEXT NOT NULL,
                      account TEXT NOT NULL, cents INTEGER NOT NULL);
CREATE TABLE idem (key TEXT PRIMARY KEY, txn TEXT NOT NULL, request TEXT NOT NULL);
CREATE TABLE snapshots (account TEXT PRIMARY KEY, cents INTEGER NOT NULL,
                        upto INTEGER NOT NULL);
"""

def new_ledger():
    db = sqlite3.connect(":memory:", isolation_level=None)   # we issue BEGIN/COMMIT
    db.executescript(SCHEMA)
    return db

class Refused(Exception):
    pass

def post(db, key, legs):
    """Append every leg of one transaction, or nothing. legs: [(account, cents)]."""
    request = repr(sorted(legs))
    db.execute("BEGIN IMMEDIATE")
    try:
        seen = db.execute("SELECT txn, request FROM idem WHERE key = ?", (key,)).fetchone()
        if seen and seen[1] != request:
            raise Refused("key reused for a different request")
        if seen:
            db.execute("ROLLBACK")
            return seen[0]                               # a retry: same answer, no new rows
        if not legs or sum(c for _, c in legs) != 0:
            raise Refused("legs do not sum to zero")
        txn = "t%d" % (db.execute("SELECT COUNT(*) FROM idem").fetchone()[0] + 1)
        db.executemany("INSERT INTO entries (txn, account, cents) VALUES (?, ?, ?)",
                       [(txn, a, c) for a, c in legs])
        db.execute("INSERT INTO idem VALUES (?, ?, ?)", (key, txn, request))
        db.execute("COMMIT")
        return txn
    except Exception:
        db.execute("ROLLBACK")
        raise

def attempt(db, key, legs):
    try:
        return post(db, key, legs)
    except Refused as e:
        return str(e)

def transfer(db, key, src, dst, cents):
    return attempt(db, key, [(src, -cents), (dst, cents)])

def reverse(db, key, txn):                               # a correction is one more txn
    legs = db.execute("SELECT account, -cents FROM entries WHERE txn = ?", (txn,)).fetchall()
    return attempt(db, key, legs)

def balance(db, account):
    """The latest snapshot plus whatever arrived after it."""
    cents, upto = db.execute("SELECT cents, upto FROM snapshots WHERE account = ?",
                             (account,)).fetchone() or (0, 0)
    tail = db.execute("SELECT COALESCE(SUM(cents), 0) FROM entries "
                      "WHERE account = ? AND id > ?", (account, upto)).fetchone()[0]
    return cents + tail

def snapshot(db, account):
    db.execute("BEGIN IMMEDIATE")
    upto = db.execute("SELECT COALESCE(MAX(id), 0) FROM entries").fetchone()[0]
    db.execute("INSERT OR REPLACE INTO snapshots VALUES (?, ?, ?)",
               (account, balance(db, account), upto))
    db.execute("COMMIT")

db = new_ledger()
transfer(db, "fund-A", "world", "A", 25000)              # A is funded with 250.00
t = transfer(db, "9f2", "A", "B", 4000)                  # move 40.00 from A to B
print(t, transfer(db, "9f2", "A", "B", 4000))            # t2 t2  (the retry wrote nothing)
print(transfer(db, "9f2", "A", "B", 5000))               # key reused for a different request
print(attempt(db, "x1", [("A", -4000), ("B", 3900)]))    # legs do not sum to zero
print(balance(db, "A"), balance(db, "B"))                # 21000 4000
print(reverse(db, "undo-9f2", t))                        # t3
print(balance(db, "A"), balance(db, "B"))                # 25000 0
print(db.execute("SELECT COUNT(*), SUM(cents) FROM entries").fetchone())   # (6, 0)

A world account stands for the outside banking system, so even deposits are balanced pairs. The reversal left six rows that still sum to zero. Now a snapshot, then a check against a hand-written reference on 2,000 seeded operations with a small key space, so retries and key collisions are common, 10% of requests unbalanced, random reversals and random snapshots:

snapshot(db, "A")
transfer(db, "k7", "A", "B", 1250)
tail = db.execute("SELECT COUNT(*) FROM entries WHERE account = 'A' AND id > "
                  "(SELECT upto FROM snapshots WHERE account = 'A')").fetchone()[0]
print(balance(db, "A"), tail)                            # 23750 1

import random

rng = random.Random(35)
db, accounts = new_ledger(), ["world", "A", "B", "C", "D"]
ref_bal = dict.fromkeys(accounts, 0)
ref_keys, ref_legs = {}, {}                  # key -> (request, txn); txn -> legs
ok = True
for _ in range(2000):
    op, key = rng.random(), "k%d" % rng.randrange(400)   # small key space: many retries
    if op < 0.6:
        a, b = rng.sample(accounts, 2)
        c = rng.randint(1, 9999)
        legs = [(a, -c), (b, c + (rng.random() < 0.1))]   # 10% unbalanced
    elif op < 0.8 and ref_legs:
        legs = [(a, -c) for a, c in ref_legs[rng.choice(sorted(ref_legs))]]
    else:
        snapshot(db, rng.choice(accounts))
        continue
    request = repr(sorted(legs))             # the reference, by hand:
    if key in ref_keys:
        same = ref_keys[key][0] == request
        want = ref_keys[key][1] if same else "key reused for a different request"
    elif sum(c for _, c in legs) != 0:
        want = "legs do not sum to zero"
    else:
        want = "t%d" % (len(ref_keys) + 1)
        ref_keys[key], ref_legs[want] = (request, want), legs
        for a, c in legs:
            ref_bal[a] += c
    ok &= attempt(db, key, legs) == want
    ok &= all(balance(db, a) == ref_bal[a] for a in accounts)
ok &= db.execute("SELECT SUM(cents) FROM entries").fetchone()[0] == 0
print(ok, len(ref_keys))                     # True 391

Every answer and every balance, read through snapshots, matches the reference after every step. The SQL used here (SUM, COALESCE, subqueries) is summarised on the SQL cheat sheet.

The complexity

• A transfer: one transaction, one key lookup and k inserts for k legs, usually two.
• A balance: one snapshot read plus a sum over the tail since it, bounded by snapshot frequency rather than account age.
• Storage: grows forever by design; old entries move to cheaper storage, never to deletion.

Where it goes wrong

• A mutable balance column as the source of truth. It can be a cache of the derived balance, never the record.
• Storing the key outside the transaction. A crash between the write and the key leaves a payment that a retry repeats.
• Floats. Rounding drift makes the ledger stop summing to zero.
• Sharding naively. Legs on two shards need a distributed transaction. Common answers are to shard by account group so most transfers stay local, or to route cross-shard transfers through a clearing account as two local, idempotent transactions coordinated by a saga; as of October 2026 both are standard patterns (from memory).

When it shows up in interviews

As "design a payment system", "design a wallet" or "design a ledger", and inside e-commerce and booking designs at the checkout step. Follow-ups: retries, refunds, overdrafts under concurrency, fast balances and sharding. Message queues usually come up too, because at-least-once delivery is why idempotency is not optional.

How to say it in an interview

"The ledger is append-only and double-entry: every transaction is a set of legs in integer cents that must sum to zero, written in one database transaction, so money is conserved and every balance is explainable. Each request carries an idempotency key stored in that same transaction, so a retry returns the original result and writes nothing. Mistakes are fixed with a reversing transaction, never an update. Balances are derived, so I keep periodic snapshots and sum only the tail. The cost is that cross-account transactions resist sharding, so I shard by account group and route cross-shard transfers through a clearing account."