Design a Payment Ledger: Idempotent, Audit-Compliant, Reconciles to the Cent

• Book: System Design Pocket Guide: Interviews — 15 Real System Designs, Step by Step
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

---

"Design a payment ledger" is the prompt that ends interview rounds. Candidates draw a payments table, an update balance arrow, and a Redis cache. Twenty minutes later the interviewer has asked about duplicate charges, partial refunds, and what happens when the processor times out. The whiteboard is a mess.

A payment ledger isn't a CRUD app. It's an append-only event store with two-sided arithmetic, a reconciliation job that runs at 03:00, and a regulator who wants to see every byte you ever wrote. Get four properties right and the rest of the design draws itself.

Why a ledger isn't a CRUD app

The instinct is wrong from the first table. CRUD systems mutate rows. Ledgers don't mutate anything. Every event (charge, refund, chargeback, fee, FX adjustment) is a new entry. The current balance is a projection over the entries, not a value you store and update.

The reason isn't ideology. It's audit. A regulator (or an angry customer, or your CFO) eventually asks: "what was account 7218's balance at 14:32 on March 4th?" If you've been updating a balance column, you can't answer. If you've been appending entries, you replay events up to that timestamp and the answer falls out.

The four properties that separate ledgers from CRUD:

1. Double-entry: every transaction touches at least two accounts, and the sum is zero.
2. Idempotency per attempt: the same payment attempt, retried, doesn't double-charge.
3. Append-only: entries are never updated or deleted, only reversed by new entries.
4. Reconcilable: your numbers match the payment processor's numbers, to the cent, every day.

Miss any one and you're shipping a bug factory.

Double-entry bookkeeping in code

Double-entry has been around since the 15th century. Pacioli wrote it down in 1494. Every accountant on Earth uses it. Software engineers keep reinventing single-entry ledgers and discovering, three years later, why that's bad.

The rule: every transaction has two sides, and they sum to zero. A customer pays you $50. The customer's liability account goes down by $50; your cash account goes up by $50. Net change: zero. You haven't created or destroyed money. You've moved it.

In SQL, that looks like this:

CREATE TABLE accounts (
    id              BIGSERIAL PRIMARY KEY,
    owner_type      TEXT NOT NULL,     -- 'customer', 'platform', 'processor'
    owner_id        TEXT NOT NULL,
    currency        CHAR(3) NOT NULL,  -- ISO 4217
    account_type    TEXT NOT NULL,     -- 'asset', 'liability', 'revenue', 'expense'
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (owner_type, owner_id, currency, account_type)
);

CREATE TABLE transactions (
    id              UUID PRIMARY KEY,
    idempotency_key TEXT NOT NULL UNIQUE,
    description     TEXT NOT NULL,
    occurred_at     TIMESTAMPTZ NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE entries (
    id              BIGSERIAL PRIMARY KEY,
    transaction_id  UUID NOT NULL REFERENCES transactions(id),
    account_id      BIGINT NOT NULL REFERENCES accounts(id),
    direction       CHAR(1) NOT NULL CHECK (direction IN ('D','C')), -- Debit / Credit
    amount_minor    BIGINT NOT NULL CHECK (amount_minor > 0),         -- cents, always positive
    currency        CHAR(3) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- The invariant that every code review should check for:
-- SUM(D) - SUM(C) = 0 for every transaction_id, per currency.

Two things to notice. amount_minor is always positive; direction lives in its own column. And entries carries no updated_at. The table is append-only; there's no UPDATE entries SET ... anywhere in the codebase. If you find one in code review, that's a blocker.

The balance projection is a query, not a stored value:

SELECT
    a.id,
    a.currency,
    COALESCE(SUM(CASE WHEN e.direction = 'D' THEN  e.amount_minor
                       WHEN e.direction = 'C' THEN -e.amount_minor END), 0) AS balance_minor
FROM accounts a
LEFT JOIN entries e ON e.account_id = a.id
WHERE a.id = $1
GROUP BY a.id, a.currency;

For hot accounts this gets too slow to run on every request. You materialize the projection into a balances table with last_entry_id, and a worker incrementally folds new entries in. The projection is a cache. If it goes corrupt, you drop it and rebuild from entries. The append-only log is the source of truth.

A single payment writes one transaction and two entries:

BEGIN;

INSERT INTO transactions (id, idempotency_key, description, occurred_at)
VALUES ('a3f...', 'pay_2026_05_24_attempt_1_user_7218', 'Order 9921 capture', now());

INSERT INTO entries (transaction_id, account_id, direction, amount_minor, currency) VALUES
  ('a3f...', 4501, 'D', 5000, 'USD'),  -- cash (asset) up
  ('a3f...', 7218, 'C', 5000, 'USD');  -- customer liability up (we owe them goods/refund-right)

COMMIT;

The BEGIN/COMMIT matters. The two entries must land atomically, or you have a half-applied transaction and the invariant is broken. Postgres gives you this for free. If you're sharding across databases, you need a two-phase commit, or better, keep both legs of any transaction in the same shard. Sharding a ledger is its own essay; the short version is: shard by owner_id, route both legs to the same shard, and accept that some cross-account transfers will need a saga.

Idempotency keys: per attempt, not per customer

The most common bug in payment systems: idempotency keyed on the wrong thing.

A customer clicks "pay" and the request times out. The mobile app retries. Without idempotency, you charge twice. With idempotency keyed on customer_id + order_id, the second attempt sees the existing transaction and returns the old result. Perfect.

Now the customer cancels and pays again for the same order. Different attempt, same key. You return the cached failure. The new payment never happens.

The fix is in the title: idempotency keys are per attempt, not per customer or per order. The client generates a fresh UUID for each payment attempt, sends it on every retry of that attempt, and discards it once the attempt completes.

import uuid

def charge_customer(client, order_id: str, amount_minor: int) -> str:
    # ONE attempt = ONE key. Retries of THIS attempt reuse it.
    # If the user clicks "pay" again later, the client generates a new one.
    idempotency_key = f"pay_{order_id}_{uuid.uuid4()}"

    for retry in range(3):
        try:
            return client.post(
                "/v1/payments",
                json={"order_id": order_id, "amount_minor": amount_minor},
                headers={"Idempotency-Key": idempotency_key},
                timeout=10,
            )
        except (TimeoutError, ConnectionError):
            # Same key, server returns the previously committed result if it landed
            continue
    raise PaymentFailed("3 retries exhausted")

Server side, the key is enforced by the UNIQUE constraint on transactions.idempotency_key. Two requests with the same key race; one wins the insert, the other gets a unique-violation and reads back the existing transaction.

def create_payment(db, idempotency_key: str, payload: dict) -> Transaction:
    try:
        with db.begin():
            txn = db.execute(
                "INSERT INTO transactions (id, idempotency_key, description, occurred_at) "
                "VALUES (%s, %s, %s, %s) RETURNING id",
                (uuid.uuid4(), idempotency_key, payload["description"], payload["occurred_at"]),
            ).fetchone()
            insert_entries(db, txn.id, payload["entries"])
            return txn
    except UniqueViolation:
        # Another request with the same key already committed
        return db.execute(
            "SELECT * FROM transactions WHERE idempotency_key = %s", (idempotency_key,)
        ).fetchone()

The UniqueViolation branch is the idempotency contract. It says: "I already did this exact thing, here's the result." That's why the key must scope to one attempt. If it scoped to the order, the retry of a second legitimate attempt would silently return the first attempt's result and the user would never get charged.

One more wrinkle: store the request body's hash alongside the key. If a client reuses an idempotency key with a different body (different amount, different currency), reject it with 409 Conflict. Stripe does this. It catches integration bugs early.

The append-only event log

The entries table is your event log. Treat it like Kafka. New events are appended, existing events are immutable, downstream projections are rebuilt from the log.

This buys you three things:

1. Time-travel: you can answer "what was the balance at any past moment" by replaying entries up to that timestamp.
2. Disaster recovery: if your balances materialized table is corrupted (a bad migration, a bug in the projector), you drop it and rebuild from entries.
3. Audit: the log is what regulators read. They don't want your balances table; they want the chronological record of every event.

The projector worker is straightforward:

def project_balances(db, batch_size: int = 1000):
    while True:
        new_entries = db.execute(
            "SELECT * FROM entries WHERE id > (SELECT COALESCE(MAX(last_entry_id), 0) FROM balances) "
            "ORDER BY id LIMIT %s",
            (batch_size,),
        ).fetchall()

        if not new_entries:
            time.sleep(1)
            continue

        with db.begin():
            for e in new_entries:
                delta = e.amount_minor if e.direction == "D" else -e.amount_minor
                db.execute(
                    "INSERT INTO balances (account_id, balance_minor, last_entry_id) "
                    "VALUES (%s, %s, %s) "
                    "ON CONFLICT (account_id) DO UPDATE SET "
                    "  balance_minor = balances.balance_minor + EXCLUDED.balance_minor, "
                    "  last_entry_id = EXCLUDED.last_entry_id",
                    (e.account_id, delta, e.id),
                )

If this worker falls behind, balances are stale but never wrong. If the worker dies and you restart it, it picks up from last_entry_id and catches up. If you find a bug in projection logic, you TRUNCATE balances and restart; the log rebuilds it.

Reconciliation: daily checksum against the processor

Every payment processor (Stripe, Adyen, PayPal, your acquiring bank) sends a settlement report. It lists what they think they collected, what they took in fees, and what they're going to deposit. Your job is to make their report match your ledger, to the cent.

The reconciliation job runs nightly:

def reconcile_day(db, processor, date: datetime.date):
    # Pull processor's view
    processor_charges = processor.list_charges(date=date)   # external truth

    # Pull our ledger's view of charges from that processor account
    our_charges = db.execute("""
        SELECT t.id, t.idempotency_key, e.amount_minor, e.currency
        FROM transactions t
        JOIN entries e ON e.transaction_id = t.id
        WHERE e.account_id = (SELECT id FROM accounts WHERE owner_id = %s)
          AND t.occurred_at::date = %s
          AND e.direction = 'D'
    """, (processor.account_id, date)).fetchall()

    # Match on processor's transaction reference, which we store in transactions.description or a side table
    discrepancies = compare(processor_charges, our_charges)

    if discrepancies:
        # Don't auto-fix. File a discrepancy record, page on-call.
        for d in discrepancies:
            db.execute(
                "INSERT INTO reconciliation_discrepancies "
                "(date, kind, processor_ref, processor_amount, our_amount, details) "
                "VALUES (%s, %s, %s, %s, %s, %s)",
                (date, d.kind, d.processor_ref, d.processor_amount, d.our_amount, d.details),
            )
        alert.page("Reconciliation failed", count=len(discrepancies), date=date)

The discrepancies are usually one of four shapes:

• Processor has, we don't: a charge succeeded at the processor but the success webhook never reached us. Reach out, replay it.
• We have, processor doesn't: we recorded a charge that never actually settled. Bug in our state machine.
• Amount mismatch: usually rounding (more on that), or a fee we recorded against the wrong account.
• Currency mismatch: an FX conversion happened differently on the processor side than we modeled.

Auto-fixing reconciliation discrepancies is a trap. You think you're cleaning up; you're actually papering over the bug that produced the discrepancy. File the discrepancy, page a human, fix the root cause. That's the only way the gap doesn't widen.

Dispute handling: chargebacks as reverse entries

A customer disputes a charge two months after it happened. The processor reverses the $50 and takes a $15 fee for their trouble. You do not go back and delete the original transaction. You write three new entries:

-- The chargeback reversal (mirrors the original charge, opposite direction)
INSERT INTO transactions (id, idempotency_key, description, occurred_at)
VALUES ('b88...', 'chargeback_a3f', 'Chargeback of txn a3f', '2026-07-20 10:00:00+00');

INSERT INTO entries (transaction_id, account_id, direction, amount_minor, currency) VALUES
  ('b88...', 4501, 'C', 5000, 'USD'),  -- cash down
  ('b88...', 7218, 'D', 5000, 'USD');  -- customer liability down

-- The processor's chargeback fee
INSERT INTO transactions (id, idempotency_key, description, occurred_at)
VALUES ('b89...', 'chargeback_fee_a3f', 'Chargeback fee for txn a3f', '2026-07-20 10:00:01+00');

INSERT INTO entries (transaction_id, account_id, direction, amount_minor, currency) VALUES
  ('b89...', 4501, 'C', 1500, 'USD'),  -- cash down
  ('b89...', 9100, 'D', 1500, 'USD');  -- 'dispute fees' expense account up

The original transaction a3f is still there. Its idempotency key (chargeback_a3f) refers back to it. The history reads: charged on March 4th, reversed on July 20th, fee posted on July 20th. A regulator or auditor can see the whole story. A DELETE FROM transactions WHERE id = 'a3f' would have destroyed it.

The rule is brutal and simple: never delete, always reverse. If you find code that runs DELETE against transactions or entries, it's a bug. Add a CI check.

Multi-currency, FX, and the precision problem

Money is not a float. This is the single most expensive bug in payment systems, and you find it in year 2 when you reconcile $0.03 against the processor and have no idea where it came from.

Floats lose precision. 0.1 + 0.2 != 0.3 in IEEE 754. Run it in any language:

>>> 0.1 + 0.2
0.30000000000000004

Multiply that across a million transactions and the rounding error becomes real money. Worse, the rounding is non-deterministic across operations: sum-then-multiply gives a different answer from multiply-then-sum. You can't reconcile against a processor that does the math in integers when your code does it in floats.

The fix is the boring one. Store money as integer minor units (cents for USD, satoshis for BTC, the smallest denomination your currency supports). The schema above already does this: amount_minor BIGINT. A $50.00 charge is 5000. A €19.99 charge is 1999. A ¥500 charge is 500 (JPY has no minor unit, so the conversion factor is 1 instead of 100).

# Wrong
amount_usd = 49.99
total = amount_usd * 1.07  # tax. Hello, float drift.

# Right
amount_minor = 4999          # $49.99
tax_minor = round(amount_minor * 7 / 100)   # 350 cents = $3.50
total_minor = amount_minor + tax_minor
# Bankers' rounding for half-cent cases:
from decimal import Decimal, ROUND_HALF_EVEN
tax_minor = int(
    (Decimal(amount_minor) * Decimal("0.07")).quantize(Decimal("1"), rounding=ROUND_HALF_EVEN)
)

FX is its own pit. When you accept a payment in EUR and settle in USD, you do the conversion once, store both legs in their native currencies, and record the FX rate as metadata. Don't average rates, don't recompute historical FX with today's rate, don't lose the original currency. A regulator will ask, and "we converted everything to USD at ingest" is the wrong answer.

ALTER TABLE transactions ADD COLUMN fx_rate NUMERIC(20, 10);
ALTER TABLE transactions ADD COLUMN fx_source_currency CHAR(3);
ALTER TABLE transactions ADD COLUMN fx_source_amount_minor BIGINT;

The cardinal sin is rounding at the wrong place. Round when the customer sees a number. Don't round inside the ledger. Every intermediate calculation runs at full precision; the rounding happens once, at the boundary, with ROUND_HALF_EVEN (bankers' rounding) to keep long-run drift symmetric.

The 90-second answer

When the interviewer asks "design a payment ledger," the answer that wins the round:

A payment ledger is an append-only event store with double-entry bookkeeping. Every transaction has two or more entries that sum to zero, stored in an immutable entries table. Current balances are a projection over the log, materialized into a balances table by a worker that folds new entries in incrementally.

Idempotency is per payment attempt, not per customer or order. The client generates a UUID for each attempt and reuses it on retries. The server enforces uniqueness with a UNIQUE constraint on transactions.idempotency_key, and stores a hash of the request body to reject reuse with a different payload.

Refunds and chargebacks never delete entries; they write reversing entries that point back to the original transaction by idempotency key. This preserves the audit trail that regulators require.

A nightly reconciliation job compares our ledger against the payment processor's settlement report, files discrepancies, and pages on-call. Reconciliation never auto-fixes; humans investigate.

Money is stored as integer minor units (cents). Never floating point. FX conversions store both source and target currency plus the rate, computed once at ingest and never recomputed.

The properties that make this different from CRUD: double-entry invariant per transaction, idempotency per attempt, append-only log as source of truth, daily reconciliation to the cent.

That's the 90 seconds. The interviewer asks follow-ups (sharding, partial refunds, multi-currency settlement, what happens during a processor outage), and the design above gives you a vocabulary to answer them without contradiction.

The gotcha: floating-point money is the bug you find at year 2

Every ledger that uses float or double for money eventually fails a reconciliation. Usually around year 2, when you have enough volume that a tenth of a cent of drift per transaction shows up as a dollar a day, and a few months of those add up to a number your CFO notices.

The fix is to use integers (cents) from day one. The refactor at year 2, when you have millions of rows in entries, is doable but expensive. You convert column types, rewrite every query, recompute every historical balance, and run for months in parallel comparing old and new sums. It works. It's also six months of work you didn't need.

If you take one thing from this post: amount_minor BIGINT, on every monetary column, from day one. The interview answer mentions it. The codebase enforces it. The CI check rejects any new NUMERIC or DOUBLE PRECISION column on a financial table.

The other lessons are reversible. This one isn't.

What's the worst ledger bug you've shipped to prod? Float drift, a missing idempotency key, a DELETE that should've been a reverse entry? Drop the story in the comments; I want to read it.

---

If this was useful

The full version of this design (sharding strategy, saga compensation for cross-shard transfers, the projection table schema in detail, and the regulator export format) is the payment-ledger chapter in the System Design Pocket Guide: Interviews. The book walks 15 system designs at this depth, each ending with a 90-second answer template. If you've got an interview coming up where "design Stripe" or "design Robinhood" might land, the ledger chapter is the one to read first.