How to design a payment system

Throughput is trivial (300 req/s, 74 TB over 7 years, one box); correctness is everything, because half the state lives at a PSP you do not control and cannot roll back. Three mechanisms each catch a class the others cannot: double-entry, idempotency, reconciliation.

Double-entry ledger

• Invariant: every transaction’s signed entries sum to zero, so the whole ledger sums to zero, per currency, at every instant. Checking it is one aggregate query, no joins.
• No balance column: an account’s balance is SUM of its entries. ledger_entries is append-only (REVOKE UPDATE, DELETE); corrections are new reversing transactions.
• Debit positive (money in), credit negative (money out). $50.00 is 5000 minor units, never 50.00.
• Authorization is a promise, not a movement, so it writes no ledger entries (only a hold with expiry). Booking it is the classic modelling error.
• Catches asymmetry (crash mid-transfer, missing fee credit, wrong currency). Does NOT catch duplication (a doubled txn still sums to zero) — that is idempotency’s job.
• Cost: full history is 6.8 h (quarterly); the open daily partition is 9.6 GB ≈ 9.6 s, run every 5 min against a stored checkpoint. Monthly full recompute validates the checkpoints.

Money is never a float

• 0.1 + 0.2 != 0.3; the error survives into storage. Store/transmit integer minor units (amount_minor BIGINT) end to end — JSON, client lib, and app code must never touch it as a double.
• Use Decimal (exact base-10) only for genuine sub-unit intermediates (FX rates, interest), then round to minor units before posting.
• Division loses money: split base then hand out the remainder one unit at a time so parts sum to the whole. 1000 split 3 ways = [334, 333, 333]. The naive split (999) also fails the zero-sum constraint — caught twice.

Idempotency across the PSP boundary

• Key committed BEFORE the network call, in its own transaction. Crash after key: leaves an IN_FLIGHT row you can recover. Crash without key: recovery re-charges the card.
• Four rules: R1 key = canonical content hash (never uuid4() per attempt); R2 namespace per operation (key:auth, key:capture, key:refund); R3 claim with one atomic INSERT ... ON CONFLICT DO NOTHING; R4 on every mutating endpoint (refund too).
• SELECT then INSERT opens a round-trip gap: 8 concurrent retries all see no row → 8 charges. One statement admits exactly one.
• Same key + different body → 409, never a cached result.
• Unknown outcome → look it up (GET /charges?idempotency_key=...), never re-send. 202 is a valid response, UNKNOWN is a real state.
• Key lifetime = auth expiry (~7 days), not an HTTP timeout.
• Double-charge budget: 1-in-10,000 × 10 M/day × $50 = $50k/day ≈ $18.25 M/yr — what the idempotency work is spent against.

psp_calls state machine

IN_FLIGHT --30s sweep--> UNKNOWN --lookup--> DONE
                                     |
                                     +------> MANUAL (human queue)

• IN_FLIGHT: sent, no reply. DONE: outcome known. UNKNOWN: waited too long, must ask. MANUAL: asking failed, a person owns it.
• Recovery worker query: WHERE state='IN_FLIGHT' AND started_at < now()-30s, served by the (state, started_at) index. A state no code produces is decoration.

Payment timescales and settlement

• Fastest-to-slowest span ~5.2 million× → a state machine, not a boolean; change state by naming a transition.
• CLOSED = 120 days after settle → sets the hot-data retention floor (“30 days hot” is wrong here).

Reconciliation

• Compares your books against the PSP settlement file daily — the only check on your books vs. the outside world. Single-pass hash join on psp_ref, ~238 MB build side, seconds.
• Five classes: missing at PSP, missing locally (a real customer really charged — the dangerous one), amount mismatch (usually fee netting), status mismatch (the file wins), duplicate psp_ref.
• Age unmatched rows 3 cycles before alerting. Day-0 has ~6,000 cutoff-noise rows (100/s × 60 s straddling the file cutoff); “unmatched > 0” alerts get muted in week one. Alertable condition is “zero after aging.”

Availability, retries, human queue

• Own availability is capped by the PSP’s. Two independent providers: 0.001 × 0.001 both-down → 99.9% becomes 99.9999%. Correlated failures (shared upstream network) break the math.
• One external call (2 s p99) costs ~4,000 internal round trips (0.5 ms).
• One in-request retry only; 4 attempts of backoff = 47 s, not a checkout. After that → UNKNOWN + 202, async retries against the lookup endpoint. Use full jitter ([0, backoff]) or a PSP outage synchronizes all workers.
• Human queue: 0.01% of 10 M = 1,000 cases/day × 3 min = 6.25 FTE. Drive it to 0.001% → 0.625 FTE — the business case for automated lookup, in headcount.

Gotchas

• “Just retry the payment” re-charges the card — the most expensive instinct in the design.
• Never book an authorization into the ledger.
• Don’t treat the PSP as system of record; its file is an input to reconciliation, not the truth.
• 2PC with the PSP is impossible — they will not enlist in your transaction. That is why the async design exists.