Design a Payment Reconciliation System
Scenario prompt
A payments platform processes transactions through its own ledger, then settles funds through one or more external processors/banks (which have their own separate ledgers). Occasionally the two disagree — a payment succeeds internally but the processor reports it failed (or vice versa), a webhook is missed, or a retry creates a near-duplicate. Design a reconciliation system that:
- Regularly compares the internal ledger against each external processor's records and surfaces discrepancies
- Distinguishes real problems (money genuinely missing or duplicated) from benign timing differences (a transaction settled at the processor a few minutes before the internal system's webhook processed it)
- Never silently "fixes" a financial discrepancy automatically without an audit trail
- Scales to millions of transactions/day without becoming the bottleneck itself
Mihir's attempt
[!todo] Write your own attempt here before reading the model solution below — how you'd structure the comparison job, what counts as a "real" discrepancy vs. noise, and how you'd handle the fix.
Model solution
Batch comparison job, not real-time matching. Reconciliation doesn't need to happen inline with the payment flow — it's fundamentally a batch problem: pull the internal ledger's transactions for a time window, pull the processor's settlement report/API for the same window, and diff them on a stable shared key (the processor's transaction/charge ID, stored on the internal record at the time the payment was initiated). Running this as a scheduled job (e.g., hourly or daily depending on volume and how time-sensitive discrepancies are) keeps it decoupled from the hot payment path entirely — it never adds latency or risk to an actual transaction.
Three-way bucket, not a binary match/mismatch. Every transaction lands in one of: (1) matched — present and agreeing on both sides, no action; (2) timing difference — present on both sides but one hasn't propagated yet (e.g., internal record exists but processor webhook hasn't landed, or vice versa) — these resolve themselves within a defined grace window (e.g., 24-48 hours) and shouldn't page anyone; (3) genuine discrepancy — still unmatched or actively conflicting (different amounts, one side missing entirely) after the grace window has passed. Only bucket 3 is a real problem. Collapsing timing differences and genuine discrepancies into one "mismatch" bucket is the single most common mistake here — it either trains the team to ignore alerts (too much noise) or hides real problems in the noise.
Discrepancies become audit-trailed cases, not silent auto-corrections. When money is involved, an automated system silently adjusting a ledger entry to make the numbers match is a serious anti-pattern — it destroys the audit trail exactly where regulators, auditors, and the finance team need one most. Instead, a genuine discrepancy should open a case (in a dedicated reconciliation-case table or ticketing system) with both sides' data captured verbatim, and the resolution — whether it's "processor was right, adjust internal record," "internal was right, dispute with processor," or "this was a legitimate duplicate refund" — is a deliberate, logged action taken by a person or a well-tested automated remediation path, never a blind sync.
Idempotency keys prevent the duplicate-transaction class of discrepancy at the source. A large fraction of "discrepancies" in practice are actually retries creating near-duplicate charges rather than genuine data disagreement. Requiring an idempotency key on every payment-initiating request (see Stripe Idempotency Keys) prevents this whole category before reconciliation ever needs to catch it — the cheapest discrepancy to resolve is the one that never gets created.
Scale via partitioned, incremental comparison. At millions of transactions/day, re-diffing the entire ledger against the entire processor history every run doesn't scale. Partition the comparison by time window (only reconcile the window since the last successful run) and, if needed, by processor/account/merchant so batches can run in parallel. The comparison job's own state (what's been checked, what's pending the grace window) needs to be durable and resumable — a crash mid-run shouldn't force a full re-scan.
Gaps to revisit
- What's the alerting/SLA policy on unresolved cases — how long can a genuine discrepancy sit open before it needs to escalate, and to whom?
- How do you reconcile against a processor that doesn't expose a clean settlement API — some legacy banking rails only offer file-based batch reports (e.g., nightly SFTP drops), which changes the ingestion design significantly.
- At what point does reconciliation itself need its own reconciliation — i.e., how do you verify the comparison job isn't the thing that's wrong (a bug in the diff logic silently marking real discrepancies as timing noise)?
Principal Engineer Lens
Reconciliation is a great example of a problem that looks like "just write a diff script" on the surface but is actually about designing for the failure modes of two systems that don't share a transaction boundary — the internal ledger and the external processor can never be atomically consistent with each other, so the design has to assume divergence is normal and build a process around detecting and resolving it deliberately, rather than pretending it won't happen. The "never silently auto-fix money" principle generalizes well beyond payments — any system reconciling two sources of truth (inventory counts, access-control state across identity providers, config drift across environments) benefits from the same discipline: auto-detect, but keep a human or an audited process in the loop for anything with real consequences. This maps directly onto Capital Markets and Fintech domains Mihir is targeting — trade settlement reconciliation against a clearing house is structurally the identical problem.
Reel Script
Setup: Say your payments platform keeps its own ledger, but money also has to settle through an external bank or processor that keeps a completely separate ledger — what happens when the two disagree?
Concept walkthrough: Explain why this has to be a batch comparison job rather than something checked in real time, and walk through the three-way bucketing — matched, timing difference, and genuine discrepancy — as the key idea that keeps the system from either drowning the team in noise or hiding a real problem inside it.
Real example tie-in: Walk through a concrete case: a transaction is missing on the internal side because a webhook got dropped. Show how the grace window catches this as a timing difference first, and only escalates to a real discrepancy case if it's still unresolved after the window passes — with the case capturing both sides' data for an audit trail, never a silent auto-fix.
Tradeoffs & alternatives: Contrast silent auto-correction (fast, but destroys the audit trail auditors and regulators need) against case-based human/automated-but-logged remediation (slower, but defensible). Mention idempotency keys as the cheaper upstream fix that prevents a whole class of discrepancies from ever needing reconciliation in the first place.
Principal Engineer takeaway: Two systems that can't share a transaction boundary will always drift eventually — the mark of a mature design is treating that as an expected condition to detect and resolve deliberately, not an edge case to paper over, and that discipline applies just as much to trade settlement in Capital Markets as it does to payments.