Payment Processing Pipeline

The design in one paragraph: an asynchronous pipeline with PostgreSQL as the system of record, a durable, partitioned event log — Kafka — as the event backbone, and a double-entry ledger at the center. The marketplace-facing API accepts a payment, durably records it, and returns immediately; workers submit it to the external processor with strict idempotency and a circuit breaker; approval writes ledger entries that reserve merchant funds; a per-timezone settlement job nets each merchant's ledger daily and pays out through the bank. Every ambiguous outcome — and with flaky external processors there will be thousands a day — lands in an explicit UNKNOWN state that a reconciliation loop resolves. Money is never inferred; it's always a ledger entry.

Assumptions

Stating these rather than asking, as instructed:

The external processor handles authorization and capture together (auth-capture in one call). Split auth/capture changes the state machine but not the architecture.
The processor accepts an idempotency key per request and offers a status-inquiry API plus webhooks. Every serious processor (Stripe, Adyen, Chase Paymentech) does. If a processor lacked idempotency keys, I'd treat every timeout as "must inquire before retry."
We are a payment platform, not the merchant of record's bank: we instruct payouts through a partner bank, we don't move money ourselves.
Refund volume is roughly 2% of payments (~2M/day) — real marketplaces sit between 1% and 5%.
"Customer-facing status within 2 seconds" means status of our pipeline is visible within 2s; if the processor takes 30s to answer, the customer sees "processing" within 2s, not the final verdict.
We don't store PANs. Card data is tokenized at the edge (processor-hosted fields or a vault like VGS/Basis Theory), which keeps most of the platform out of PCI DSS scope. This is the single highest-leverage security decision available, so I'm making it an assumption rather than a feature.

Requirements

Functional: accept payment requests from 5,000 marketplaces; submit to external processors; on approval, reserve merchant funds; daily settlement per merchant timezone; refunds tied to original payments; status views for customers, merchants, and ops; multi-currency amounts.

Non-functional, in priority order:

Correctness of money. No double charges, no lost payments, no merchant paid twice. This dominates everything — a payment platform that loses 0.01% of 100M payments loses 10,000 payments a day.
Durability and auditability. Seven-year retention, tamper-evident.
Availability of intake. Accepting payments must survive processor outages. 99.95% on the write path.
Latency. 2s customer-visible status; API accept in <100ms p99.
Consistency of reads can lag by a second or two. That's the trade I'm spending everywhere.

Scale estimates (these drive the architecture)

100M payments/day = 1,160/s average; peak 10× = **11,600 requests/s**.
Each payment touches the database 6 times (payment row, outbox event, 2–4 ledger entries, status updates): **70k row writes/s at peak**. One Postgres box won't do that; 16 shards at ~4–5k writes/s each will, comfortably, on NVMe.
Processor calls: 11,600/s at peak with p50 around 1–2s means ~20–25k concurrent outstanding calls. That rules out thread-per-request submission workers; the processor gateway must be async I/O.
Storage: ~~3KB per payment across payment + ledger + events ≈ 300GB/day, ~110TB/year, ~750TB over 7 years. So: hot store keeps ~90 days (~~27TB across shards — fine), everything older goes to object storage (S3) as Parquet/Iceberg.
Ledger entries: 4 per payment ≈ 400M/day. Append-only inserts, never updates — this is what makes the volume tractable.
Settlement skew: 5,000 merchants averaging 20k payments/day, but the top merchant might do 10M/day. Settlement must aggregate in SQL/streams, never load a merchant's day into memory.

Architecture

flowchart LR
    subgraph Edge
        MP[Marketplace clients] --> GW[API Gateway<br/>authn, rate limits]
        GW --> PAY[Payment API<br/>stateless]
    end

    PAY -->|"txn: payment row + outbox"| PDB[(Payments DB<br/>Postgres, 16 shards<br/>by merchant_id)]
    PDB -->|Debezium CDC| K[(Kafka<br/>payment-events)]

    K --> PGW[Processor Gateway<br/>async workers, circuit breaker]
    PGW <-->|"idempotent auth calls"| EXT[External Processor]
    EXT -.->|webhooks| WH[Webhook Ingest] --> K

    K --> LED[Ledger Service<br/>single writer per account]
    LED --> LDB[(Ledger DB<br/>Postgres, append-only,<br/>partitioned by month)]

    SCHED[Settlement Scheduler<br/>per merchant-timezone cutoff] --> SET[Settlement Worker]
    SET --> LDB
    SET -->|payout instructions| BANK[Partner Bank]

    K --> PROJ[Projector] --> RED[(Redis<br/>status by payment_id)]
    PROJ --> ES[(Elasticsearch<br/>ops search)]
    K --> WD[Webhook Dispatcher] --> MP
    STAT[Status API] --> RED
    STAT --> PDB

    REC[Reconciliation Worker] <--> EXT
    REC --> PDB
    LDB -->|nightly export| S3[(S3 / Iceberg<br/>7-year archive)]

Why asynchronous intake

The tempting design is synchronous: hold the HTTP request, call the processor, return approved/declined. It's simpler and most auths finish in under 2s, so it even meets the latency budget. But the prompt says processors "sometimes respond slowly or not at all," and a synchronous design converts a slow processor into your own outage: 11,600 req/s × 30s hangs = 350k held connections, thread pools exhausted, intake dead. I've seen this exact failure shape in every synchronous-to-a-flaky-dependency system; it's not a maybe.

So: the Payment API validates, writes the payment in state RECEIVED plus an outbox event in one Postgres transaction, and returns 202 with a payment_id in ~30ms. A change-data-capture pipeline (Debezium) tails the WAL and publishes to Kafka — the outbox pattern, because writing to Postgres and Kafka as two separate operations is a dual-write and one of them will eventually fail alone. For the common fast case, the client can long-poll GET /payments/{id}?wait=1500ms and usually get the final answer in a single round trip anyway.

What I rejected: making Kafka the system of record (event sourcing everything). Rebuilding balances from a 400M-event/day stream to answer "what does merchant X owe" is a bad time, and auditors want tables, not topics. Kafka here is transport and fan-out; Postgres is truth.

Why sharded Postgres, not DynamoDB or Cassandra

Payments need multi-row ACID transactions (payment + outbox + ledger entries atomically), and settlement needs real relational aggregation (SUM(amount) GROUP BY merchant, currency over bounded ranges). Postgres gives both, plus the operational ecosystem auditors and DBAs already trust. DynamoDB has transactions but caps them at 100 items and makes ad-hoc audit queries painful; Cassandra's eventual consistency is disqualifying for a ledger. 70k writes/s is squarely in "shard Postgres" territory, not "abandon SQL" territory.

Sharding key: merchant_id, because settlement, merchant dashboards, and balance queries are all merchant-scoped, and 5,000 merchants spread across 16 shards keeps any single merchant's traffic on one shard (a 10M/day merchant is ~1,200 writes/s peak on its shard — fine). Cross-merchant ops queries go to a search index (Elasticsearch), not the shards.

Data model

Amounts are integer minor units + ISO 4217 code, everywhere, forever. No floats, no decimals in transit.

-- Payments DB (sharded by merchant_id)
CREATE TABLE payments (
  payment_id       UUID PRIMARY KEY,          -- UUIDv7, time-ordered
  merchant_id      BIGINT NOT NULL,
  idempotency_key  TEXT NOT NULL,             -- supplied by marketplace
  amount_minor     BIGINT NOT NULL,
  currency         CHAR(3) NOT NULL,
  state            TEXT NOT NULL,             -- see state machine
  processor        TEXT,
  processor_ref    TEXT,                      -- processor's id, once known
  customer_ref     TEXT,                      -- opaque marketplace customer id
  card_token       TEXT,                      -- vault token, never a PAN
  created_at       TIMESTAMPTZ NOT NULL,
  updated_at       TIMESTAMPTZ NOT NULL,
  UNIQUE (merchant_id, idempotency_key)       -- the dedupe backbone
);

CREATE TABLE payment_attempts (               -- one per processor call
  attempt_id       UUID PRIMARY KEY,
  payment_id       UUID NOT NULL,
  processor_idem_key TEXT NOT NULL UNIQUE,    -- OUR key sent to processor
  state            TEXT NOT NULL,             -- SENT | APPROVED | DECLINED | TIMEOUT
  request_at       TIMESTAMPTZ, response_at TIMESTAMPTZ,
  response_code    TEXT
);

CREATE TABLE outbox (
  event_id BIGSERIAL PRIMARY KEY,
  aggregate_id UUID, event_type TEXT, payload JSONB,
  created_at TIMESTAMPTZ NOT NULL
);

-- Ledger DB: double-entry, append-only, partitioned by month
CREATE TABLE ledger_entries (
  entry_id     BIGINT,                        -- per-account monotonic sequence
  account_id   BIGINT NOT NULL,               -- e.g. merchant:42:payable:USD
  journal_id   UUID NOT NULL,                 -- groups the balanced entry set
  payment_id   UUID,
  direction    CHAR(2) NOT NULL,              -- DR | CR
  amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
  currency     CHAR(3) NOT NULL,
  entry_type   TEXT NOT NULL,                 -- CAPTURE | REFUND | FEE | PAYOUT | FX
  prev_hash    BYTEA, entry_hash BYTEA,       -- per-account hash chain
  created_at   TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (account_id, entry_id)
) PARTITION BY RANGE (created_at);

CREATE TABLE account_snapshots (              -- balance = snapshot + tail
  account_id BIGINT, as_of_entry_id BIGINT,
  balance_minor BIGINT, currency CHAR(3),
  PRIMARY KEY (account_id, as_of_entry_id)
);

CREATE TABLE settlement_batches (
  batch_id UUID PRIMARY KEY,
  merchant_id BIGINT, currency CHAR(3),
  cutoff_local_date DATE,
  from_entry_id BIGINT, to_entry_id BIGINT,   -- high-water marks, not wall clock
  gross_minor BIGINT, refunds_minor BIGINT, fees_minor BIGINT, net_minor BIGINT,
  state TEXT,                                  -- PENDING | SENT | PAID | FAILED | RETURNED
  UNIQUE (merchant_id, currency, cutoff_local_date)
);

Chart of accounts per payment capture (all entries in one journal, debits = credits enforced at write time):

DR processor_receivable:USD / CR merchant:42:payable:USD (gross)
DR merchant:42:payable:USD / CR platform:fee_revenue:USD (our fee)

A refund reverses: DR merchant:payable / CR processor_receivable. A payout: DR merchant:payable / CR bank:clearing. The merchant's "reserved funds" are simply their payable-account balance — there is no separate mutable balance column to drift out of sync, which is the classic ledger bug.

Ledger writes are serialized per account by partitioning the Kafka ledger-commands topic on account_id: one writer per account at a time, so the monotonic entry_id and hash chain need no locking and hot merchants create no row contention. The hash chain (entry_hash = H(prev_hash || entry)) plus nightly anchoring of chain heads to S3 with Object Lock makes the 7-year archive tamper-evident — an auditor can verify nobody rewrote March 2027.

APIs

POST /v1/payments                    Idempotency-Key: <key>   → 202 {payment_id, state}
GET  /v1/payments/{id}?wait=1500ms                            → 200 {state, ...}
POST /v1/payments/{id}/refunds       Idempotency-Key: <key>   → 202 {refund_id}
GET  /v1/merchants/{id}/balance                               → per-currency balances
GET  /v1/merchants/{id}/settlements?date=                     → batch details + line items
GET  /v1/ops/payments?merchant=&state=&processor_ref=...      → ES-backed search (RBAC)
POST <marketplace webhook>           payment.approved | payment.declined | refund.* 
                                     (signed, retried with backoff, at-least-once)

Idempotency semantics: same key + same body → same payment_id returned; same key + different body → 409. Keys are scoped per merchant and retained 30 days.

The hard part: the processor timeout

This is the two-generals problem with money attached, and it deserves the most interview time. You send an auth, the connection dies after 8 seconds. Did the customer get charged? You don't know, and both guesses are wrong: retry blindly and you double-charge; give up and you may have taken money for nothing.

The state machine makes the ambiguity a first-class state:

stateDiagram-v2
    [*] --> RECEIVED
    RECEIVED --> SUBMITTED : gateway sends auth
    SUBMITTED --> APPROVED : processor approves
    SUBMITTED --> DECLINED : processor declines
    SUBMITTED --> UNKNOWN : timeout / connection error
    UNKNOWN --> APPROVED : inquiry or webhook confirms
    UNKNOWN --> DECLINED : inquiry confirms decline
    UNKNOWN --> REVERSED : unresolved past SLA, void issued
    APPROVED --> REFUND_PENDING : refund requested
    REFUND_PENDING --> REFUNDED

The rules:

Every processor call carries our processor_idem_key from payment_attempts, recorded before the call is made. A retry of the same attempt reuses the same key, so the processor dedupes on their side.
On timeout → UNKNOWN. The reconciliation worker polls the processor's status-inquiry API at 30s / 2m / 10m / 1h. Webhooks from the processor also resolve it (webhook ingest is idempotent — dedupe on processor_ref + event type, because processors love sending webhooks twice).
If still unresolved after 6 hours, we send a void/reversal for that idempotency key (also idempotent — voiding a nonexistent auth is a no-op at every major processor) and mark the payment REVERSED. We fail closed: the customer is never silently charged for a payment we reported as failed.
Ledger entries are written only on confirmed APPROVED. UNKNOWN money doesn't exist yet.
Daily file-level reconciliation against the processor's settlement report catches anything the online loop missed. At 100M/day, even a 0.05% timeout rate is 50k UNKNOWN payments daily — this loop is core infrastructure, not an edge-case script.

The processor gateway itself: async workers consuming Kafka, per-processor adapters, 8s timeout, circuit breaker per processor (open at >20% failures over 30s), token-bucket rate limiting to each processor's contracted TPS. When the breaker opens, payments simply wait in Kafka in RECEIVED — intake never stops, customers see "processing," and the backlog drains when the processor recovers. No hedged requests, ever: hedging duplicates side effects, and the side effect here is charging a card.

Settlement

"Once per day in each merchant's timezone" hides two traps.

Trap one: wall-clock windows. "All payments between midnight and midnight" breaks when a payment's approval lands late or a clock skews — entries get missed or double-counted across batch boundaries. Instead, each batch is defined by ledger sequence high-water marks: batch N covers entry_id from (batch N−1's to_entry_id + 1) to the latest entry at cutoff time. Every entry lands in exactly one batch by construction, even if it was written late. The UNIQUE (merchant_id, currency, cutoff_local_date) constraint makes batch creation idempotent, so a crashed settlement job just reruns.

Trap two: timezones. 5,000 merchants across ~40 offsets, DST included. The scheduler stores each merchant's IANA timezone (America/Sao_Paulo, not UTC-3) and computes the next cutoff instant per merchant; a distributed cron (Temporal — see below) fires each merchant's job individually. Load is trivial: worst case a few hundred merchant jobs per hour.

Each batch: aggregate the entry range in SQL (streaming, because the top merchant's day is millions of entries), net gross − refunds − fees per currency, write the batch row and the payout journal entry atomically, then instruct the partner bank (ISO 20022 pain.001 or the bank's API, idempotent on batch_id). Payout states track the bank's async lifecycle, including RETURNED days later — a returned payout writes a reversing journal entry against the merchant's payable account. If a merchant's net is negative (refund-heavy day), we don't pay out; the negative balance carries into the next batch, and past a threshold ops gets an alert.

Multi-currency: ledger accounts are per-currency and the ledger never converts implicitly. A EUR payment settles from the merchant's EUR payable account. If the merchant wants USD payouts, conversion is an explicit pair of FX journal entries at settlement time using that day's booked rate, so the audit trail shows exactly when and at what rate money changed denomination.

I'm running settlement, reconciliation, and refund orchestration on Temporal: these are long-lived, multi-step workflows with retries and human-visible state, and hand-rolling that as cron + state columns is how you get a settlement job that crashed at step 3 of 5 and nobody noticed. I rejected plain cron + DB flags for exactly that reason; it works until the first partial failure at 2 a.m.

Refunds

POST /payments/{id}/refunds validates against the ledger — cumulative refunds ≤ captured amount, enforced by the per-account single writer so two concurrent refunds can't both pass the check — then follows the same pipeline: state row, outbox, processor call with idempotency key, UNKNOWN handling, ledger entries on confirmation. A refund is a first-class object linked to its payment, not a negative payment. If the merchant's balance can't cover a refund, policy decides: we allow it into negative balance up to a per-merchant limit (we're extending the merchant credit), beyond that it's rejected and ops is paged.

Status reads (the 2-second promise)

CQRS: the projector consumes payment-events and updates an in-memory key-value cache (Redis: payment_id → {state, updated_at}, 48h TTL) and Elasticsearch (ops search across merchants, states, processor refs, date ranges). Customer status checks hit Redis; misses fall through to the payments shard, so a lagging projector degrades latency, never correctness. Merchants get the same API plus webhook pushes (signed, at-least-once, exponential backoff, dead-letter after 24h with a dashboard for replays). Ops gets ES-backed search plus a manual-resolution queue for payments stuck in UNKNOWN or settlement batches in FAILED.

End-to-end event lag budget: outbox poll/CDC ~200ms, Kafka ~50ms, projector ~100ms — well under 2s at p99, and it's monitored as an SLO with the Redis fall-through as the safety net.

Failure modes

Processor down or slow: circuit breaker opens, payments queue in Kafka, intake unaffected. Backlog math: a 1-hour outage at average load is ~4M queued payments; drain rate is capped by the processor's TPS ceiling, so ops can prioritize by merchant tier. Customers see "processing"; marketplaces can set a per-payment expiry after which we auto-fail queued payments (a payment authed 4 hours late is often worse than a failed one).
Kafka down: the outbox is in Postgres; events buffer in the source of truth and replay when Kafka returns. Nothing is lost, reads go stale, intake continues. This is the payoff for refusing the dual-write.
Postgres shard failure: synchronous replica in another AZ, automated failover (RDS Multi-AZ or Patroni), ~30s of write unavailability for 1/16th of merchants. The API returns 503 for those merchants; marketplaces retry with the same idempotency key and nothing duplicates.
Duplicate deliveries everywhere: Kafka is at-least-once, webhooks repeat, marketplaces retry. Every consumer is idempotent — unique keys on (merchant_id, idempotency_key), processor_idem_key, journal_id, batch_id. Exactly-once is a property we construct at each boundary, not a setting we enable.
Settlement job crash: Temporal resumes the workflow; high-water marks and the unique batch constraint make every step re-runnable.
Reconciliation catches the rest: daily processor-file and bank-statement reconciliation with an ops break queue. Target: automated match rate >99.9%, breaks resolved within one business day.

Security and compliance

PCI scope: PANs never touch our systems (tokenized at the edge); we store vault tokens. This turns a Level 1 PCI audit of the whole platform into an audit of a thin edge component.
mTLS between services; merchant API auth via scoped keys with rotation; webhook signatures (HMAC, timestamped, replay-window checked).
Encryption at rest (KMS, per-shard keys); field-level encryption for anything customer-identifying.
RBAC for ops with step-up auth for money-moving actions (manual settlement release, forced state transitions), and every ops action written to the same append-only audit trail.
7-year retention via monthly Parquet exports to S3 with Object Lock (WORM) + the anchored hash chains; hot stores keep 90 days.

Tradeoffs I'm consciously making, and evolution

Chosen: async intake over sync simplicity (availability beats one round trip); Postgres shards over NoSQL scale-out (transactions and auditability beat elastic writes we don't need); ledger-derived balances over a balance column (correctness beats a cheap read, and snapshots make the read cheap anyway); fail-closed on UNKNOWN payments (a voided good auth costs a retry; a silent double charge costs trust and chargebacks).

Deferred, deliberately: multiple processor routing with failover (the adapter seam exists; smart routing is a v2 revenue optimization), instant payouts (needs a prefunded float model — a treasury problem more than an engineering one), fraud scoring (a synchronous pre-processor hook slots in front of the gateway when needed), and active-active multi-region (v1 is single-region multi-AZ; the ledger's per-account single-writer discipline is what will make region failover tractable later, which is part of why I insisted on it now).

The first thing I'd build after the walking skeleton is the reconciliation loop, not more features. In payments, the pipeline's job is to be right; reconciliation is how you prove it's right, 100 million times a day.

Interview follow-ups (Codex as interviewer)

The original design above is unchanged. Codex read the prompt and the design, then asked the follow-ups below; answers and any design revisions follow each question.

Round 1

Q1 (Codex): A journal spans several accounts, yet you partition ledger commands by account_id, sending its legs to different Kafka partitions and writers. What exact atomic commit and idempotency protocol prevents an unbalanced half-journal or duplicate leg after crashes and replays, given that the schema shows neither a unique journal_id constraint nor a database-enforced balance invariant?

You're right, and this is the biggest hole in the doc as written. Partitioning ledger-commands by account_id sends the legs of one journal to different partitions and different writers, so nothing commits debits = credits atomically. A crash between legs leaves an unbalanced half-journal, and nothing in the schema stops it.

The fix is to make the journal, not the account, the unit of atomicity. Repartition ledger-commands by merchant_id, and require that every account a journal touches lives on the same ledger shard. The merchant-scoped accounts already do; the global accounts (processor_receivable:USD, platform:fee_revenue:USD) don't, so they get split into per-shard subaccounts — processor_receivable:USD:shard7 — the standard omnibus-account split, with the true balance a read-time sum across shards. Now one writer per merchant inserts all legs of a journal plus a journals header row in a single Postgres transaction, with a constraint trigger asserting per-currency sum(DR) = sum(CR) at commit, and journal_id as the header's primary key so a Kafka replay hits the PK and no-ops. Per-account entry_id sequences and hash chains still work because each account (including each subaccount) is written by exactly one writer.

Design change: ledger-commands partitioned by merchant_id (was account_id); global accounts become per-shard subaccounts; add a journals header table (journal_id PK = idempotency) and a deferred constraint trigger enforcing balance per currency inside the commit.

Q2 (Codex): You assume auth-and-capture, but after an ambiguous timeout you send a void and immediately mark the payment REVERSED; the void itself can time out, fail, or arrive after capture. How does the state machine handle a late approval or settlement-file match after REVERSED without leaving the customer charged, omitting the ledger entry, or refunding twice?

Partially concede. The doc jumps from "send void" to "mark REVERSED" and skips the fact that the void is a processor call with the same two-generals problem as the auth. The fix: the void is a payment_attempts row with its own processor_idem_key and its own UNKNOWN handling, and the payment sits in a new REVERSAL_PENDING state until the void is confirmed (response or inquiry). Only then does it become REVERSED. Until confirmation the customer still sees "processing" — we never tell anyone "this payment failed" while the charge might stand. Same fail-closed discipline, applied one level down.

"Late approval after REVERSED" can't arrive through the front door: at the processor, a confirmed void kills the auth, so a subsequent approval of that idempotency key is a processor-side contradiction. Where it can show up is the daily settlement file — a capture we voided appears as settled money. That's a reconciliation break: entries go to a suspense account (not the merchant's payable), we refund the customer exactly once — enforced by a partial unique index allowing one active reversal-or-refund object per payment — and the discrepancy becomes a dispute with the processor. It's in the break queue's job description; the design already routes settlement-file mismatches there. (Codex pushed harder on the fencing itself in round 2 — see Q7.)

Design change: add REVERSAL_PENDING between UNKNOWN and REVERSED; void modeled as a first-class attempt with its own idempotency key; suspense account for post-void settlement-file captures; partial unique index guaranteeing at most one active reversal/refund per payment.

Q3 (Codex): Payments and ledger entries live in separate databases connected through Kafka, despite the claim that payment, outbox, and ledger writes are atomic. At precisely what durable point may a payment become externally visible as APPROVED, and how do you recover if that state is committed but its ledger command is permanently malformed, repeatedly fails, or misses that day's settlement?

The durable point is the payments-DB transaction that flips the payment row to APPROVED and writes the outbox event. After that commit, the status API and merchant webhooks may say APPROVED; the ledger journal follows asynchronously via Kafka. That gap is deliberate — it's the read-lag tolerance I said I'd spend — but the doc doesn't say what guards it, and "the ledger command fails repeatedly" needs a real answer, because merchant balance and settlement come from the ledger.

Two mechanisms. First, a continuous completeness check: every APPROVED payment must have its capture journal within 15 minutes (estimate; the point is minutes, not days), monitored as an SLO — a single anti-join between the payments shard and the ledger shard, both keyed by merchant. Second, settlement gets a blocking pre-check: a merchant's batch won't cut while that merchant has APPROVED payments older than the cutoff with no journal. The batch is delayed and ops paged, not silently short. A "permanently malformed" command is a poison message from our own producer, generated from our own committed row — that's a code bug, so it goes to a DLQ with a page, and the merchant's settlement holds until it's fixed. The failure mode is money delayed, never money lost: the payment row is truth and the journal is deterministically derivable from it, so recovery is replay, not archaeology.

Design change: APPROVED-without-journal completeness monitor; settlement pre-check that blocks a merchant's batch on missing journals; ledger-command DLQ pages rather than skips.

Q4 (Codex): Settlement batches use from_entry_id and to_entry_id, but entry_id is only monotonic per account while a merchant has multiple accounts and concurrent captures, refunds, fees, FX entries, and payouts. What snapshot or fencing mechanism defines one consistent merchant-wide cutoff and prevents entries from being skipped, counted twice, or included in a batch whose own payout journal changes the scanned range?

Concede the schema as written: a scalar from_entry_id/to_entry_id pair only makes sense for one account, and a merchant has several (per-currency payable, plus fee interactions). The fix is a cutoff vector: a batch_cutoffs table — (batch_id, account_id, from_entry_id, to_entry_id) — one row per account in the batch. And the Q1 fix is what makes the vector consistent: all of a merchant's accounts live on one shard behind one writer, so the settlement worker reads all head entry_ids in a single repeatable-read snapshot — one MVCC snapshot on one Postgres instance is a consistent cut, no fencing protocol needed beyond that.

The batch's own payout journal doesn't contaminate the scanned range: it's written by the same single writer after the cutoff read, so its entry_ids land above every to_entry_id and fall into the next batch's range by construction. And in that next batch it can't be double-counted as revenue because the netting query aggregates by entry_type — a PAYOUT debit in the range reduces the payable balance, it never adds to gross.

Design change: replace scalar high-water marks with a per-account batch_cutoffs vector taken in one MVCC snapshot; depends on the Q1 co-location fix.

Q5 (Codex): After recording the payout journal, the worker calls the bank, but a timeout leaves it unknown whether the bank accepted the instruction. Without an explicit assumption that every bank supports durable idempotency and status lookup, how do retries avoid paying twice while also preventing the ledger from claiming that money left when it did not?

Concede the ledger-timing point: as written, the payout journal says "money left" before the bank confirmed anything, and if the pain.001 timed out and was never processed, the ledger lies. Fix with the same pending/settled split banks themselves use: at instruction time the journal is DR merchant:payable / CR bank:payout_pending — the merchant is no longer owed, but the money hasn't provably left. On confirmation (bank ack, status API, or the camt.053 statement) a second journal moves it: DR bank:payout_pending / CR bank:clearing. The ledger never claims settled funds until the bank's own record says so, and payout_pending's balance is exactly the money in flight — which is also the number ops should be staring at.

On idempotency: I won't assume every bank has a durable idempotency key, but ISO 20022 gives me MsgId/EndToEndId, which I set to batch_id, and banks reject duplicate MsgIds within the dedupe window — that's the rail's native dedupe. A timed-out instruction puts the batch in SENT_UNKNOWN and is never blindly retried: it's resolved against the bank's status API if there is one, or the next statement if there isn't, before any resend. Worst case with a bank offering neither ack nor inquiry: the payout waits for the next day's statement — a day late, never doubled. Late beats double for a payout, every time.

Design change: payout becomes two journals (pending at instruction, settled at confirmation) through a bank:payout_pending account; batch state gains SENT_UNKNOWN; retries gated on statement/status resolution, dedupe via EndToEndId = batch_id.

Q6 (Codex): The capacity argument shards payments by merchant_id, but the ledger appears to be one PostgreSQL system ingesting roughly 400 million entries per day, and the largest merchant is an unsplittable hot key whose estimated peak write load already exceeds your stated per-shard comfort range. What concrete ledger-sharding, hot-merchant splitting, and rebalancing design sustains peak load without breaking per-account ordering or multi-account journal atomicity?

Run the numbers: 400M entries/day is ~4.6k/s average, ~46k/s at the 10× peak. The diagram shows one Ledger DB box and the doc never says it's sharded — that's a fair catch. It is sharded, the same way: 16 shards by merchant_id gives ~2.9k/s per shard at peak, inside the 4–5k comfort band, and these are pure appends with no read-modify-write contention, so the band is conservative.

The whale is the real question. 10M payments/day × 4 legs = 40M entries/day, ~4.6k/s at peak on whatever shard it lands on — at the top of the band before counting neighbors. Three mechanisms. First, the shard map is directory-based, not hashed, so whales get pinned to dedicated shards with no noisy neighbors — a 4.6k/s append-only workload alone on NVMe is fine. Second, if one merchant outgrows a whole shard, split its payable account into subaccounts — merchant:42:payable:USD:00..07, legs hashed by payment_id — each with its own sequence and hash chain, balance = sum of eight. Per-subaccount ordering holds, and journal atomicity holds because the subaccounts stay on the whale's shard; this is the same omnibus split the global accounts already got in Q1. Third, rebalancing: moving a merchant is a chain cut at a settlement-batch boundary — freeze at cutoff, anchor the old chain head to S3, start the new shard's chain with prev_hash = old head — so the tamper-evidence survives the move and there's no dual-write window. (Codex caught a contradiction in the subaccount claim — see Q8.)

Design change: ledger DB explicitly sharded by merchant_id with a directory-based shard map; whale merchants pinned to dedicated shards; per-merchant payable subaccount fan-out; merchant moves as hash-chain cuts at settlement boundaries.

Round 2 — pushback

Q7 (Codex): A "successful" void of an auth the processor has not yet recorded may only confirm a no-op; what processor guarantee or fencing protocol prevents the original in-flight auth from completing afterward and charging the customer after you have marked the payment REVERSED?

Honest answer first: no protocol I control can fence a request that's still in flight inside the processor's network. If our auth is crawling through a partition and hasn't been recorded when the void arrives, "void succeeded" can mean "voided nothing," and the auth can land afterward. The guarantee has to come from the processor's contract, so I'm making it an explicit contract requirement instead of an implicit hope: the processor must support idempotency-key tombstoning — a cancel against key K both voids any recorded auth and poisons K, so a late-arriving original request carrying K is rejected, not processed. Stripe-class processors give you this shape (the key is durable at intake and a cancel is ordered against it); it goes in the processor integration checklist next to "has status inquiry."

For a processor that can't tombstone, I change the timing rather than pretend: REVERSAL_PENDING doesn't resolve to REVERSED at void-confirmation alone — it waits out a quiet period longer than the processor's documented maximum request lifetime (their server-side timeout bounds how late a request can complete; if they'll never process a request older than 60s, a 6-hour-old auth can't materialize through the front door), with a final status inquiry at the end. And the backstop stays: if a charge slips through anyway, it surfaces in webhooks or the settlement file, lands in suspense, and the customer is refunded once via the Q2 path. So the precise claim is: with tombstoning, "REVERSED means never charged" holds outright; without it, the residual tail degrades to "charged, then refunded within a day" — and I'd say that out loud in the SLA rather than promise fencing I can't build.

Design change: idempotency-key tombstoning added as an explicit processor contract requirement; for processors without it, REVERSAL_PENDING → REVERSED additionally requires a quiet period exceeding the processor's max request lifetime plus a final inquiry.

Q8 (Codex): You claim subaccount fan-out handles a merchant that outgrows one shard, but then keep every subaccount on that same shard; how does that add write capacity, and if you distribute them across shards, how will you preserve atomic balanced journals and the single-snapshot settlement cutoff without distributed transactions?

Fair catch — I conflated two ceilings. Same-shard subaccount fan-out adds zero disk or CPU capacity; what it relieves is the serialization ceiling: per-account monotonic entry_id plus a hash chain makes each account a serial dependency, and eight subaccounts turn one serial chain into eight parallel ones on the same box. That's the ceiling today's whale hits first — 4.6k/s of appends is nowhere near a dedicated NVMe box's insert capacity, so for the stated scale, dedicated shard + subaccounts is the whole answer.

If a merchant genuinely outgrows one box (roughly 3–4× today's whale, as an estimate), the escape is to split the merchant, not the journal: partition at the payments layer into logical sub-merchants — payment_id hashes to sub-merchant 0..7 — where each sub-merchant is a complete, self-contained ledger scope (its own payable, fee, and processor-receivable subaccounts) living on its own shard. Every capture journal balances within one sub-merchant, so it's still one single-shard Postgres transaction; a refund routes to its original payment's sub-merchant deterministically from payment_id, so it also stays local. No journal ever spans sub-merchants, which means no distributed transaction exists to need.

Settlement composes the same way: each sub-merchant gets its own cutoff vector in its own MVCC snapshot, and the merchant's batch is the sum of eight sub-batches. That sum is consistent without a global snapshot precisely because no journal crosses a sub-merchant boundary — each sub-cut is internally balanced, so the sum is too. The payout is one bank instruction for the total (idempotent on the merchant-level batch_id), backed by eight per-sub-merchant payout_pending journals that the bank confirmation clears. The one thing that gets worse is the merchant-facing balance read — now a sum of eight accounts across shards — which is an eventually-consistent read I already accepted everywhere else.

Design change: subaccount fan-out reclassified as a serialization fix, not a capacity fix; beyond one shard, whales split into self-contained sub-merchant ledger scopes (journals never cross sub-merchants → single-shard transactions and per-scope settlement cutoffs compose without distributed transactions).

What changed, summarized

Ledger commands repartitioned by merchant_id with journal-level atomicity: all legs + a journals header row (journal_id PK) in one Postgres transaction, balance enforced by constraint trigger; global accounts split into per-shard subaccounts. (Q1)
New REVERSAL_PENDING state; the void is a first-class attempt with its own idempotency key and UNKNOWN handling; suspense account + one-active-reversal partial unique index for post-void settlement-file captures. (Q2)
APPROVED-without-journal completeness monitor and a settlement pre-check that blocks a merchant's batch on missing journals; ledger-command DLQ pages. (Q3)
Settlement cutoffs become a per-account batch_cutoffs vector taken in one MVCC snapshot on the merchant's shard. (Q4)
Payouts split into pending/settled journal pairs through bank:payout_pending; SENT_UNKNOWN batch state; retries gated on bank status or statement, dedupe via EndToEndId = batch_id. (Q5)
Ledger DB explicitly sharded by merchant_id with a directory-based shard map; whales pinned to dedicated shards; merchant moves as hash-chain cuts at settlement boundaries. (Q6)
Idempotency-key tombstoning made an explicit processor contract requirement; without it, REVERSED requires a quiet period past the processor's max request lifetime, and the residual tail is disclosed as "charged, then refunded within a day." (Q7)
Whale overflow beyond one shard handled by self-contained sub-merchant ledger scopes so journals never cross shards; sub-merchant settlement cutoffs compose without distributed transactions. (Q8)

Industry practice and further reading

Added after the interview rounds: how real systems handle the hard parts above, with verified sources (checked 2026-08-19).

How industry does it

The idempotency discipline in this design — record processor_idem_key in payment_attempts before the call, reuse it on every retry — is the published Stripe contract, almost word for word. Their 2017 post Designing robust and predictable APIs with idempotency lays out client-generated keys, retries with exponential backoff, and the reason it matters: calling a charge endpoint twice "would lead to the customer being double-charged." The same author later published Implementing Stripe-like idempotency keys in Postgres, which formalizes what the payment_attempts table does implicitly: split each request into "atomic phases" of local transactions separated by "foreign state mutations," with named recovery points so a crashed request resumes instead of re-executing. If I were writing the gateway worker's loop, I'd start from that post.

On the ledger, the convergence across companies is striking and it's on exactly the invariants Codex pushed on in Q1. Uber's Gulfstream platform enforces zero-sum at write time — "the sum of all the entries is zero (the system cannot create or destroy money)" — per Money movement at scale with strong data consistency, the same job my deferred constraint trigger does. Square's Books requires all transactions to balance to zero and fixes errors with correcting entries, never updates. TigerBeetle takes the argument to its endpoint: debit/credit is the schema for transaction processing, and invariants like balance limits belong inside the database, not the application — my constraint trigger is the sharded-Postgres version of that claim. And Modern Treasury's How to Scale a Ledger, Part II describes the posted/pending/available balance split that banks use — which is the same move as the Q5 fix, where a payout sits in bank:payout_pending until the bank's own record confirms it.

Where the published systems diverge from this design is the storage engine, and it's worth being honest about. I picked sharded Postgres and dismissed NoSQL; Square picked Spanner specifically to avoid application-level sharding (their hot-account answer is cursor sharding in the schema, not a directory map); Uber runs money-order balances on DynamoDB with strongly consistent reads. Nobody converged on an engine. What everyone converged on is the properties: append-only entries, balances derived from entries rather than trusted in a mutable column, and some serialization discipline per account. Square does keep one mutable current-balance row — the thing I refused — but guards it with a "Pending Balance book" so the merchant payout amount is a single pre-balanced row instead of an aggregation over millions of entries; that's their version of my account_snapshots, with the same double-entry safety net underneath.

The Q3 completeness monitor — every APPROVED payment must have its journal within minutes, checked by anti-join — turns out to be core infrastructure at Stripe, not an afterthought. Their Ledger post describes a data quality platform over five billion daily events scoring three dimensions: clearing (do accounts that should zero out actually zero out), timeliness, and completeness (does every upstream database ID have matching ledger events), with 99.99% of money movement verified within four days. That's the design's reconciliation loop and completeness check, productized. On tamper evidence, Uber's LedgerStore (April 2024) does what my hash chain plus S3 anchoring does — it "seals" closed time ranges so recorded transactions can't be silently altered — and also tiers old ledgers to cold storage in deterministic time-range batches, the same 90-days-hot/archive split I sized in the estimates.

Two smaller confirmations. Stripe's payments API retrospective shows their PaymentIntents state machine has an explicit processing state and no terminal failed — consistent with this design's stance that the customer sees "processing" within 2 seconds while the real outcome (including UNKNOWN) resolves behind it. And the Temporal choice for settlement and refund orchestration is the pattern their own saga writeup describes: each service commits local transactions while the workflow records progress and runs explicit compensations — which is precisely the "crashed at step 3 of 5" failure I rejected cron for.

Updates from post-training information

One source here postdates my training data: Uber published Zero-Sum by Design: 10 Years of Uber's Payments Platform on August 6, 2026. It's a confirmation, not a contradiction — after a decade and hundreds of billions in annual volume, the two things Gulfstream kept are immutable money orders (adjustments spawn new orders, never edits) and zero-sum accounting, the same two invariants the Q1/Q2 fixes hardened. The one adjustment I'd make to the original text: the "Why sharded Postgres, not DynamoDB" section reads as if DynamoDB is disqualified for ledgers. Uber's retrospective says they run strongly consistent ledger balances on DynamoDB at larger scale than this problem, enforcing the balance invariant in the application layer with deterministic IDs. So the honest claim is narrower: Postgres is the right call for this team's transaction and audit-query needs, not the only engine that can hold a ledger. (The dismissal also aged fine in one direction — Uber's own LedgerStore moved off DynamoDB, citing cost.)