02. Payment System — Low-Level Design¶
~22 min read · Part 3 of 4 (Overview → HLD → LLD → Q&A)
The HLD named the boxes. This file opens the four that carry the design's weight — the idempotent payment API, the double-entry ledger, the PSP integration, and reconciliation — and pins down the data, the algorithms, and the concurrency corners where a payment system actually breaks. These are the four things an interviewer probes hardest, because they are the four places money leaks.
Data models¶
Three stores matter: the payment/intent record, the idempotency record, and the ledger itself.
CREATE TABLE payment_intent (
id VARCHAR(32) PRIMARY KEY, -- 'pi_...'
merchant_id BIGINT NOT NULL,
amount BIGINT NOT NULL, -- minor units: 5000 = $50.00, never a float
currency CHAR(3) NOT NULL,
status SMALLINT NOT NULL, -- state-machine enum (see below)
capture_method SMALLINT NOT NULL, -- 0=automatic, 1=manual
psp_ref VARCHAR(64) NULL, -- processor's own id, once known
idempotency_key VARCHAR(64) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
version INT NOT NULL DEFAULT 0 -- optimistic-lock guard for state transitions
);
CREATE INDEX idx_pi_merchant ON payment_intent (merchant_id, created_at);
Two deliberate choices. amount is a BIGINT of minor units, never a decimal or float, so $50.00 is the exact integer 5000 and no rounding error can ever creep into money arithmetic. version exists so that every state transition is a compare-and-set (UPDATE ... WHERE id = ? AND version = ?), which turns two workers racing to advance the same payment into one winner and one no-op rather than a lost update.
CREATE TABLE idempotency_key (
key VARCHAR(64) PRIMARY KEY, -- client-supplied, per logical operation
request_hash CHAR(64) NOT NULL, -- SHA-256 of the request body
status SMALLINT NOT NULL, -- 0=in_progress, 1=completed
response_code SMALLINT NULL,
response_body JSONB NULL, -- the stored result, replayed on retry
intent_id VARCHAR(32) NULL,
created_at TIMESTAMP NOT NULL,
expires_at TIMESTAMP NOT NULL -- TTL, typically created_at + 24h
);
The PRIMARY KEY on key is the whole game: it is the atomic guard that lets exactly one of the scenario's four requests claim the slot. request_hash catches a client that reuses a key with a different body — a bug we must reject (409), not silently serve the wrong stored result for. status = in_progress records that a request holds the key but has not finished, so a concurrent retry can be told "still processing" rather than being allowed to start a second charge.
-- The double-entry ledger: append-only, immutable, never UPDATEd.
CREATE TABLE ledger_entry (
id BIGINT PRIMARY KEY, -- monotonic
transaction_id VARCHAR(32) NOT NULL, -- groups the balanced legs of one movement
account_id BIGINT NOT NULL, -- which account this leg debits/credits
direction SMALLINT NOT NULL, -- +1 = credit, -1 = debit
amount BIGINT NOT NULL, -- minor units, always positive
currency CHAR(3) NOT NULL,
created_at TIMESTAMP NOT NULL
);
CREATE INDEX idx_le_txn ON ledger_entry (transaction_id);
CREATE INDEX idx_le_account ON ledger_entry (account_id, id); -- for balance derivation
The invariant lives here: for any transaction_id, SUM(direction * amount) = 0 — debits equal credits. There is no balance column anywhere; a balance is SUM(direction * amount) over an account's entries. The table is insert-only, so there is no update contention on a hot account and no way for a bug to corrupt a running total, because no running total exists to corrupt.
-- Balance snapshot so a balance read isn't a full-history scan.
CREATE TABLE balance_snapshot (
account_id BIGINT PRIMARY KEY,
balance BIGINT NOT NULL, -- sum up to last_entry_id
last_entry_id BIGINT NOT NULL, -- watermark
updated_at TIMESTAMP NOT NULL
);
A balance read becomes snapshot.balance + SUM(entries WHERE id > last_entry_id) — the checkpoint plus a small tail — rather than summing millions of rows. The snapshot is derived and disposable: if it is ever wrong, it is recomputed from the immutable ledger, which is the true source of record.
Component internals¶
Component 1 — The idempotent payment API¶
Responsibility: ensure that N requests carrying the same idempotency key produce exactly one charge and N identical responses.
The mechanism is a two-phase handshake against the idempotency_key table: claim the key atomically, do the work, then finalize the stored result. Everything hinges on the atomic claim.
def create_payment(key: str, body: dict) -> Response:
req_hash = sha256(canonical(body))
# Phase 1 — atomic claim. INSERT ... ON CONFLICT is the single arbiter.
claimed = db.execute("""
INSERT INTO idempotency_key (key, request_hash, status, created_at, expires_at)
VALUES (:k, :h, 0 /* in_progress */, now(), now() + interval '24 hours')
ON CONFLICT (key) DO NOTHING
RETURNING key
""", k=key, h=req_hash)
if not claimed: # someone already owns this key
rec = db.get_idempotency(key)
if rec.request_hash != req_hash:
return Response(409, "idempotency key reused with different body")
if rec.status == COMPLETED:
return Response(rec.response_code, rec.response_body) # replay — the retry path
return Response(409, "request with this key still in progress")
# Phase 2 — we won the slot; do the work exactly once.
intent = create_intent_and_open_ledger(body) # one DB transaction
enqueue_psp_job(intent.id)
resp = Response(200, serialize(intent))
# Phase 3 — finalize the stored result so future retries replay it.
db.execute("""UPDATE idempotency_key
SET status = 1, response_code = :c, response_body = :b, intent_id = :i
WHERE key = :k""", c=resp.code, b=resp.body, i=intent.id, k=key)
return resp
The subtle part is what happens if the process crashes after the claim but before finalize — the key is stuck in_progress. A retry then gets "still in progress," which is safe (no double charge) but stuck. A sweeper reconciles orphaned in_progress keys older than a threshold against the payment record: if the intent committed, it finalizes the key from the intent; if not, it releases the key. The claim is never lost, only occasionally slow to resolve — the correct failure direction.
Component 2 — The double-entry ledger¶
Responsibility: record every money movement as balanced, append-only entries, and refuse to commit an unbalanced one.
def post_transaction(txn_id: str, legs: list[Leg]) -> None:
# legs = [Leg(account, direction, amount), ...]
assert sum(leg.direction * leg.amount for leg in legs) == 0, "unbalanced transaction"
assert all(leg.amount > 0 for leg in legs)
with db.transaction(): # all legs commit or none do
for leg in legs:
db.insert_ledger_entry(txn_id, leg.account, leg.direction, leg.amount)
# no balance UPDATE — balances are derived, snapshots refreshed asynchronously
The balance-equals-zero assertion runs before the write, so an unbalanced transaction can never be persisted — the invariant is enforced at the door, not audited after. The whole set of legs commits in one database transaction, so a reader never sees a half-posted movement where debits and credits don't tie out.
def balance(account_id: int) -> int:
snap = db.get_snapshot(account_id) # checkpoint
tail = db.execute("""SELECT COALESCE(SUM(direction * amount), 0)
FROM ledger_entry
WHERE account_id = :a AND id > :w""",
a=account_id, w=snap.last_entry_id)
return snap.balance + tail # O(1) + small tail, not O(history)
Component 3 — PSP integration (the unreliable-network boundary)¶
Responsibility: call the external processor exactly-once-in-effect over a network that offers no such guarantee.
def drive_payment(intent_id: str) -> None:
intent = load(intent_id)
if intent.status in TERMINAL: # queue is at-least-once; this job may be a dup
return # idempotent worker: already done, no-op
# Forward OUR idempotency key to the PSP so the retry is safe at their end too.
try:
result = psp.charge(amount=intent.amount, method=intent.pm,
idempotency_key=intent.idempotency_key, timeout=8)
except PspTimeout:
# Ambiguous: card may or may not have been charged. Do NOT guess.
# Leave status = processing; retry with SAME key, then defer to reconciliation.
schedule_retry(intent_id, backoff=True)
return
if result.status == "succeeded":
with db.transaction():
# settle: debit pending, credit merchant, credit fee — one balanced txn
post_transaction(txn(intent), legs=[
Leg(PENDING_ACCT, -1, 5000), # debit the pending/receivable account
Leg(intent.merchant, +1, 4855), # credit merchant net of fee
Leg(PLATFORM_FEE_ACCT, +1, 145), # credit platform fee ($1.45)
])
advance_state(intent, to="succeeded", expect_version=intent.version)
else:
advance_state(intent, to="failed", expect_version=intent.version)
Three things make this safe. The worker is idempotent — a redelivered queue job for a terminal payment is a no-op, so at-least-once delivery does not become at-least-once charging. The idempotency key is forwarded to the PSP, so even our own retry after a timeout is deduplicated at the processor. And a timeout is treated as unknown, not failed — the payment stays processing and reconciliation, not a guess, resolves it.
Component 4 — Reconciliation¶
Responsibility: compare the ledger against the PSP's authoritative settlement file and open a "break" for every mismatch.
def reconcile(settlement_file, date) -> list[Break]:
breaks = []
settled = index_by_psp_ref(parse(settlement_file)) # processor's truth
ledgered = index_by_psp_ref(ledger_movements_for(date))
for ref, s in settled.items():
l = ledgered.get(ref)
if l is None:
breaks.append(Break("MISSING_INTERNALLY", ref, s.amount)) # PSP charged, we didn't book
elif l.amount != s.amount:
breaks.append(Break("AMOUNT_MISMATCH", ref, s.amount, l.amount))
for ref, l in ledgered.items():
if ref not in settled:
breaks.append(Break("MISSING_EXTERNALLY", ref, l.amount)) # we booked, PSP didn't settle
return breaks # each routed to auto-repair (post corrective entry) or manual review
MISSING_INTERNALLY is the dangerous one and the reason reconciliation exists: it is the timeout case made visible — the PSP charged the card, our worker never learned the outcome, and now the settlement file proves the money moved. The repair posts the settling ledger entries that the real-time path missed, converting an at-least-once-with-a-gap into eventually-correct books.
Core algorithm — the $50 charge, timed out and retried three times¶
Walk the scenario end to end with numbers. The client sends POST /v1/payment_intents with amount = 5000, Idempotency-Key = 3f9c, and the network is slow.
- Request 1 arrives.
INSERT ... ON CONFLICT DO NOTHINGon key3f9csucceeds — request 1 owns the slot, statusin_progress. It createspi_123, opens a ledger entry moving $50 into the pending account, enqueues the PSP job, and begins forming the response. But the response is slow to reach the client. - Client times out at 8 seconds and fires Request 2 with the same key
3f9c. ItsINSERT ... ON CONFLICTfinds the key already present,status = in_progress. Request 2 does not start a second charge; it returns409 / still processing. No second intent, no second ledger entry. - Meanwhile the PSP Worker picks up
pi_123, calls the processor with idempotency key3f9cforwarded, and the card is charged once — $50. The worker posts the settling transaction (debit pending $50, credit merchant $48.55, credit fee $1.45), advancespi_123tosucceeded, and finalizes idempotency key3f9cwith the full success body. - Request 3 (second retry) arrives after finalize.
INSERT ... ON CONFLICTfinds key3f9cpresent,status = completed. It replays the stored response — the exact success body forpi_123— without touching the PSP or the ledger. - Request 4 (third retry) does the same: pure replay.
Tally: four requests, one atomic claim, one PSP charge of $50, one balanced ledger transaction, four identical success responses. The card was charged exactly once. Now scale it: at the 10,000 payments/second Black Friday peak, this same claim-once-replay-rest logic runs 10,000 times a second across the partitioned idempotency store, each key on its own partition, so there is no global lock — the exactly-once guarantee is per-key and therefore embarrassingly parallel. And if the PSP had timed out in step 3 instead of succeeding, pi_123 would stay processing, the worker would retry with key 3f9c (safe at the PSP), and if the outcome stayed unknown, the next day's reconciliation would find the charge in the settlement file and post the settling entries — exactly-once achieved not by the network but by idempotency plus reconciliation.
Sequence diagram — timeout, retry, and the winning claim¶
Client API/Payment Svc Idempotency Store Ledger DB PSP Worker PSP/Bank
│ POST amount=5000, key=3f9c │ │ │ │
├──── Request 1 ───────▶│ │ │ │ │
│ ├─ INSERT key 3f9c (ON CONFLICT)▶│ claimed ✓ │ │
│ ├─ create pi_123 + open ledger ─────────────────▶│ (pending $50)│
│ ├─ enqueue drive(pi_123) ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─▶│ │
│ (slow response, client waiting...) │ │ │
│ │ │ │ ├─ charge(5000, key=3f9c) ▶│
│ ── timeout @8s ── │ │ │ │◀── succeeded ─┤ charge card ONCE
├──── Request 2 (retry, key=3f9c) ──▶│ │ │ │
│ ├─ INSERT key 3f9c ─────────────▶│ CONFLICT: │ │
│ │ │ in_progress │ │ │
│◀── 409 still processing┤ │ │ │ │
│ │ │ │ ├─ settle: dr pending 5000,│
│ │ │ │◀─────────────┤ cr merch 4855, fee 145 │
│ │ │ │ ├─ advance pi_123→succeeded│
│ ├─ finalize key 3f9c = success ─▶│ completed │ │
├──── Request 3 (retry, key=3f9c) ──▶│ │ │ │
│ ├─ lookup key 3f9c ─────────────▶│ completed │ │
│◀── 200 replay pi_123 ─┤ │ (no PSP, no ledger touch) │ │
One claim, one charge, one balanced settlement; every retry after completion is a pure replay.
Concurrency and edge cases¶
- Concurrent retries racing the original: resolved by the primary-key
ON CONFLICTon the idempotency table — the database, not application logic, decides who owns the key. A retry that arrives duringin_progressgets a safe "still processing," never a second charge. - Key reused with a different body: rejected with
409via therequest_hashcheck, because replaying the stored response for a different request would be a silent correctness bug worse than an error. - Crash between claim and finalize: the key sticks
in_progress; a sweeper reconciles it against the payment record — finalizing if the intent committed, releasing if not — so the guarantee is never lost, only occasionally delayed. - Duplicate queue delivery: the PSP worker is idempotent — a redelivered job for a
succeeded/failedpayment is a no-op, checked via the payment's terminal status and version, so at-least-once delivery does not become double charging. - Two workers advancing one payment: the optimistic
versionguard on the state transition (UPDATE ... WHERE version = :v) makes one the winner and the other a no-op, preventing a lost update on the state machine. - PSP timeout with unknown outcome: treated as
processing, retried with the same forwarded key, and ultimately resolved by reconciliation against the settlement file — never guessed as success or failure. - Partial ledger write: impossible — all legs of a transaction commit atomically and the balance-to-zero assertion runs before the write, so an unbalanced or half-posted movement can never be persisted.
- Stale balance snapshot: harmless — it is derived and recomputed from the immutable ledger; a money-moving operation re-derives authoritatively rather than trusting the snapshot.
- Refund of an already-refunded charge: guarded by the refund's own idempotency key plus a ledger check that cumulative refunds never exceed the captured amount, so a double-submitted refund refunds once.