Module P-24·40 min read

Idempotency end to end, a double-entry ledger, explicit state machines, reconciliation as a first-class service, and UPI-scale throughput.

JJS

Written by Jatin Jain Saraf · Senior Software Engineer

Module P-24 — Capstone: Design a Payment System

Q: A payment service records balances as `UPDATE accounts SET balance = balance + $1 WHERE id = $2`, inside the same transaction as the payment record. During a flash sale, throughput collapses, and separately a finance review finds a handful of accounts whose balances cannot be explained by any list of payments. What does the capstone prescribe, and which single mechanism fixes the duplicate-application problem?

Replace the mutable balance with an append-only double-entry ledger where balances are derived sums — which removes the contended row entirely, preserves the history that makes discrepancies findable, and makes duplicate application impossible via `UNIQUE (source_event_id)` on the ledger transaction. Add periodic balance snapshots so a current balance is a snapshot plus a short delta sum. — The module lists exactly three defects of the mutable balance — not idempotent because the operation is relative (Module P-16), history destroyed so a discrepancy is unexplainable, and every payment serialised on one row lock while generating a dead tuple per update — and the append-only ledger answers all three at once, with `UNIQUE (source_event_id)` named as the entire duplicate defence and snapshots as the answer to summing a large partition. Option C is the strongest near-miss and is a real technique: a `last_applied_event_id` does make one application idempotent, but it only remembers the *most recent* event, so an older duplicate arriving after a newer event is applied again — and it fixes neither the lock contention nor the missing history. Option B addresses contention only, and cannot shard a platform-level account that every payment touches by definition; an advisory lock makes the serialisation explicit rather than removing it. Option D relocates the contention without reducing it and widens the window in which the balance disagrees with the payments, while still leaving a relative operation behind a retrying job — which is the double-application bug with extra steps.

Q: A team's payment flow calls the PSP first and writes the payment row only after receiving a response, generating a fresh UUID as the PSP idempotency key on each attempt. They see three symptoms: occasional duplicate charges after timeouts, webhook handlers returning `404` for events they later learn about, and a small daily residue of PSP charges that appear in the settlement report but nowhere in their database. What single ordering change addresses all three, and what else is required?

Write the payment row and idempotency claim in a committed local transaction *before* calling the PSP, and derive the PSP key deterministically from tenant, payment and attempt so a retry reproduces it — plus a sweeper that resolves attempts left in `unknown` by querying the PSP with that key, since nothing retries an attempt whose process died. — The module calls writing the row first the most important line in the flow diagram and ties all three symptoms to it: a claim makes client retries replayable, an existing row gives the early webhook something to attach to, and a durable pre-call record is what lets the sweeper resolve the abandoned path. It also insists the derived key must be deterministic, since a fresh UUID inside the retry path defeats the only mechanism that closes the atomicity gap across a boundary you do not control. Option A is genuinely good design for delayed payment methods and the module endorses asynchronous handling — but on its own it does not fix the duplicate charge, because the background job still needs a durable pre-call record and a reproducible key, and the residue of unrecorded charges persists. Option C inverts the module's rule that the webhook is authoritative and the synchronous response optimistic; it also fails outright for 3-D Secure and bank transfers, where no synchronous outcome exists at any delay. Option D is unavailable in practice — a third-party HTTP API is not a 2PC participant — and Module A-3 covers why 2PC would be the wrong reach even where it is possible.

Q: During a PSP outage lasting 25 minutes, an engineer proposes queueing failed charge attempts and replaying them once the PSP recovers, to avoid losing revenue. Separately, they propose that the nightly reconciliation job auto-correct amount mismatches by adjusting the ledger to match the PSP's figure. What does the capstone say about each?

Neither. New charges must fail fast behind a circuit breaker, because charging a customer twenty minutes after they saw a failure produces disputes — only already-authorised captures are safe to replay, since the customer consented to that amount. And auto-correction destroys the evidence of the bug that caused the break: reconciliation must produce a break list with alerts on count and age, resolved by a human writing a reversing transaction with a reason. — Both halves are the module's stated positions, and both follow from the correctness-over-availability requirement that opens the design. The charge case is the rule in action: a customer who saw a failure has usually paid another way or left, so a later charge against an expired intent generates disputes, while an existing authorisation is a record of consent and is therefore safe to capture. The reconciliation case is stated just as directly — auto-correction destroys the evidence of the bug and can compound an error, so the job classifies breaks and a human writes a reversing transaction with a reason. Option B is the plausible split and it is where the idempotency argument misleads: the keys guarantee the charge is not *duplicated*, which is a different question from whether it should happen at all. Option A accepts both. Option D misapplies a real point — reconciling on the PSP's settlement boundaries rather than your calendar day — which addresses timing breaks specifically and does nothing for an amount mismatch or an unwanted late charge.

What this module covers: A complete worked design for taking money, from the API contract down to the reconciliation job — the system where every idea in Practitioner is load-bearing at once. Requirements and the non-functional constraint that reorders everything (correctness above availability). Money as integers, never floats, and always paired with a currency. A double-entry, append-only ledger, and why a mutable balance column is the wrong data structure for reasons that are half correctness and half concurrency. Payment state as an explicit machine advanced only by compare-and-set. The end-to-end flow annotated with the ambiguous failure at every hop, including the webhook that arrives before your own API call returns. Then the failure scenarios worked one by one, three-way reconciliation as the required backstop rather than a nice-to-have, the hot-account problem that appears at scale, and the compliance constraint where statutory retention beats the right to erasure.

What We Are Building, and What We Are Not

Scope. A payment service inside your product that charges customers using an external payment service provider — Stripe, Adyen, Razorpay — records every movement of money in a ledger you own, and can prove at any time that its numbers match the PSP's and the bank's.

Explicitly out of scope, and each exclusion is a decision:

We never touch a card number. The client collects card details directly with the PSP's hosted fields or SDK and hands us a token. This keeps us in the smallest PCI DSS scope available (SAQ-A) instead of the most expensive one, and it is the single highest-leverage architectural decision in the whole design. Any design that proxies a PAN through your servers has changed the project from "build a payment service" to "build a PCI-audited environment."
We are not a card network or an acquirer. Authorisation, clearing and settlement between banks are the PSP's problem.
No fraud-scoring model, no multi-PSP routing, no marketplace payouts, no subscription billing. Each is a real system; the last section says where they attach.

Functional requirements

Charge a customer a specified amount in a specified currency, for an order.
Be safe to retry: a repeated request must never charge twice.
Support authorise-now/capture-later, because physical goods ship after the order.
Support full and partial refunds, never exceeding what was captured.
Handle asynchronous outcomes: 3-D Secure challenges, bank transfers that settle days later, and PSP webhooks as the authoritative notification.
Handle disputes and chargebacks as money movements, not as support tickets.
Produce a complete, immutable audit trail: for any penny, who moved it, when, and why.

Non-functional requirements, and the one that reorders everything

Requirement	Target
Correctness	absolute. No double charge, no lost charge, no unaccounted penny.
Availability	99.95% for payment initiation
Latency	p99 < 800 ms for our own work, excluding PSP round trip
Throughput	200 payments/sec sustained, 5,000/sec peak (a flash sale, or UPI-scale festival traffic)
Auditability	every state change attributable and immutable for 7 years
Freshness of reporting	reconciliation daily; internal dashboards hourly

The first row changes the design's priorities in a way worth stating explicitly, because it inverts the default instinct of this course. Everywhere else, an ambiguous failure is resolved in favour of availability — retry, accept the duplicate, deduplicate later. Here, when correctness and availability conflict, we fail the payment. A customer seeing "we couldn't process that, please try again" has had a bad minute. A customer charged twice has had a bad week, and we have a financial liability, a support cost, and in aggregate a regulatory problem.

That single sentence is what justifies the expensive parts below: the ledger, the compare-and-set transitions, and the reconciliation job.

Analogy: double-entry bookkeeping, which is a 700-year-old solution to precisely this problem. A Venetian merchant could not ask the ship's captain what had happened, could not undo an entry once the ink dried, and could not trust that any single ledger was complete. So every transaction was written twice, in two places, and the books had to balance — not because balancing was the goal, but because failing to balance is how you find the error you did not know you had made. Our system has the same three constraints. The PSP is the ship's captain, the append-only ledger is the ink, and reconciliation is the balancing.

Estimation: What the Numbers Demand

Rough sizing, in the style of Module F-3, because it decides which problems are real.

Write throughput. 5,000 payments/sec peak. Each payment produces, over its life: 1 intent row, 1–3 attempt rows, 4–8 ledger entries, 3–6 webhook inbox rows, and a handful of outbox events. Call it ~20 row writes per payment, so 100,000 row writes/sec at peak.

That number immediately tells us something: a single Postgres primary handles low tens of thousands of small writes per second with fsync-on-commit, so peak traffic requires either sharding (Module P-6) or the observation that peak is short. Both are true in practice: shard by tenant or payment ID, and note that the 5,000/sec peak lasts minutes, so a queue in front of non-critical work absorbs much of it.

Storage. At 200/sec sustained: ~6.3 billion payments over a year is wrong by an order of magnitude — 200 × 86,400 × 365 ≈ 6.3 billion? No: 200 × 86,400 = 17.3M/day, ≈ 6.3 billion/year. That is genuinely the arithmetic, and it tells you 200/sec sustained is an enormous business (Stripe-scale), not a mid-sized one. For a more typical product, sustained is 5–20/sec — 160M–630M payments/year — and 5,000/sec is the flash-sale peak. Design for the peak, size storage for the sustained rate, which is a distinction worth making explicitly because conflating them is how teams over-build.

At 20/sec sustained: 630M payments/year × ~20 rows × ~250 bytes ≈ 3 TB/year of ledger and payment data, before indexes. That is fine on one machine for a few years if it is partitioned by month so old partitions can be moved to cheaper storage and never scanned (Module P-9, Module O-8).

The number that matters most is not throughput. It is that 7 years of immutable retention is a hard requirement, so nothing is ever deleted, the ledger only grows, and every design decision must assume the table is large. That is why partitioning appears in the schema below rather than as a later optimisation.

Money: Integers in Minor Units, Always With a Currency

Two rules, both absolute, both violated constantly.

Never use a floating-point type for money. 0.1 + 0.2 !== 0.3 in binary floating point, and a payment system that accumulates rounding errors will fail reconciliation in ways that take days to trace. Store integer minor units — pence, cents, paise — as bigint.

sql

amount_minor  bigint NOT NULL CHECK (amount_minor > 0),
currency      char(3) NOT NULL,     -- ISO 4217

An amount without a currency is not an amount. They travel together, always, in the same row and the same function signature. And currencies have different scales: USD and GBP have 2 decimal places, JPY has 0, KWD and BHD have 3. So amount_minor = 1000 is £10.00, ¥1000, or 1.000 KWD depending entirely on the currency column — which is why a helper that formats or compares money must take both, and why a CHECK that amounts are positive plus a separate direction column is safer than allowing signed amounts (a sign flip is a silent reversal of meaning).

Never mix currencies in one account or one ledger transaction. A single sum(amount_minor) across mixed currencies is meaningless, and no constraint will catch it if the currency lives only in the payment row. Every ledger account is denominated in exactly one currency, so cross-currency movement is modelled explicitly as two transactions plus an FX position — which forces the conversion rate to be recorded rather than implied.

For FX rates themselves, NUMERIC with explicit scale, never a float, and store the rate used at the time on the transaction. A rate looked up later is a different number and will not reproduce the historical figure — the same "record what was true then" principle as Module P-22's Type 2 dimensions.

The Ledger: Append-Only, Double-Entry, and Balances Are Derived

Here is the design most teams get wrong on the first attempt:

sql

-- WRONG, in three separate ways.
UPDATE accounts SET balance = balance + 4200 WHERE id = $1;

It is a relative operation, so it is not idempotent (Module P-16). A retried message adds £42 twice, and there is no record that would let you notice.

It destroys history. The balance is 8,300. Why? Nobody can say. There is no audit trail, no way to answer "what did this look like on 3 March," and no way to find which of ten thousand movements was wrong.

It serialises every payment for that account on one row lock. The platform's clearing account is touched by every payment, so at 5,000/sec every transaction queues behind the same row — and each update produces a dead tuple, making it simultaneously a lock bottleneck and the worst vacuum target in the database (Module F-13).

The correct structure is an append-only, double-entry ledger. Every movement of money is a transaction containing two or more entries whose signed amounts sum to zero. Balances are derived.

sql

CREATE TABLE ledger_accounts (
  id        bigserial PRIMARY KEY,
  code      text NOT NULL,           -- 'psp_clearing', 'customer_receivable', 'revenue', 'psp_fees'
  tenant_id bigint,                  -- NULL for platform-level accounts
  currency  char(3) NOT NULL,
  kind      text NOT NULL,           -- 'asset' | 'liability' | 'revenue' | 'expense'
  UNIQUE (code, tenant_id, currency)
);

CREATE TABLE ledger_transactions (
  id          bigserial PRIMARY KEY,
  occurred_at timestamptz NOT NULL,          -- when the money moved
  recorded_at timestamptz NOT NULL DEFAULT now(),  -- when we learned; these differ
  kind        text NOT NULL,                 -- 'capture' | 'refund' | 'fee' | 'chargeback' | 'reversal'
  payment_id  bigint,
  source_event_id text NOT NULL,             -- the PSP event / our idempotency key
  UNIQUE (source_event_id)                   -- ← the entire duplicate defence
) PARTITION BY RANGE (occurred_at);

CREATE TABLE ledger_entries (
  id             bigserial,
  transaction_id bigint  NOT NULL REFERENCES ledger_transactions(id),
  account_id     bigint  NOT NULL REFERENCES ledger_accounts(id),
  amount_minor   bigint  NOT NULL,           -- signed: debit positive, credit negative
  currency       char(3) NOT NULL,
  occurred_at    timestamptz NOT NULL
) PARTITION BY RANGE (occurred_at);

Four properties do the work:

UNIQUE (source_event_id) is the whole idempotency mechanism. A duplicated webhook, a replayed queue message, an operator re-running a job — each attempts to insert a transaction with a source_event_id it has already used, the constraint rejects it, and the ledger is unchanged. This is Module P-16's "restructure the effect so it has a natural unique key" strategy, and it is why a ledger is the easy case for idempotency while a balance column is the hard one.

Entries must sum to zero per transaction, checked at write time — the invariant that catches programming errors before they become accounting errors:

sql

-- Verified inside the same transaction that writes the entries.
SELECT CASE WHEN sum(amount_minor) <> 0
  THEN raise_unbalanced(transaction_id) END
FROM ledger_entries WHERE transaction_id = $1;

Nothing is ever updated or deleted. A mistake is corrected by a reversing transaction — equal and opposite entries, referencing the original. This means the ledger is a complete history, an auditor can be given read access without risk, and "who changed this and when" is answerable by construction. It is also what makes the 7-year retention requirement free rather than a chore.

Balances are SUM(amount_minor), which is exact and never contended — the hot-row problem disappears because there is no row to contend on. The cost is that summing a large partition per read is expensive, which the scale section addresses with snapshots.

A worked transaction

A customer pays £42.00; the PSP keeps £1.05 in fees.

Account	Amount (minor)	Meaning
`psp_clearing` (asset)	+4200	money the PSP owes us, awaiting payout
`customer_receivable` (asset)	−4200	the customer no longer owes us
—	sums to 0

Then the fee, as its own transaction because it is a different event with a different source_event_id:

Account	Amount (minor)
`psp_fees` (expense)	+105
`psp_clearing` (asset)	−105

And when the PSP pays out to the bank:

Account	Amount (minor)
`bank` (asset)	+4095
`psp_clearing` (asset)	−4095

psp_clearing now nets to zero for this payment, which is exactly what reconciliation checks. Note that fees and payouts are separate transactions arriving at different times — treating a charge and its fee as one atomic event is a modelling error that makes reconciliation impossible, because the PSP reports them separately too.

Payment State Is an Explicit Machine, Advanced Only by Compare-and-Set

A payment has many states and several terminal ones, and modelling it as a boolean paid column is how systems end up with orders that shipped without payment.

text

                    ┌──────────────► canceled (terminal)
                    │
created ──► requires_action ──► authorized ──► captured ──► settled (terminal)
   │              │                 │             │
   │              └──► failed       └──► expired  ├──► refunded (terminal)
   └──► failed (terminal)              (terminal) ├──► partially_refunded
                                                  └──► disputed ──► charged_back (terminal)
                                                          └──────► dispute_won

Encode the allowed transitions as data, not as if statements scattered across handlers:

sql

CREATE TABLE payment_transitions (from_state text, to_state text, PRIMARY KEY (from_state, to_state));

And advance state with a guarded update — a compare-and-set that names the state you believe you are in:

sql

UPDATE payments
   SET state = 'captured', version = version + 1, updated_at = now()
 WHERE id = $1
   AND state = 'authorized'        -- ← the guard. Concurrency handled without a lock.
RETURNING *;
-- Zero rows means: someone else already advanced it, or it is in an unexpected state.
--   Not an error to retry blindly — an outcome to interpret.

Why this specific mechanism rather than a lock: duplicates and races are normal here, not exceptional. Two webhook deliveries of the same event, a webhook racing your own API response, an operator retrying a capture — all arrive concurrently. A compare-and-set makes the second one a no-op that returns zero rows, deterministically, without any worker holding a lock across a network call. It is Module P-16's insert-as-claim idea applied to a transition instead of a creation, and Module P-17's WHERE version < $new predicate applied to state.

Two schema details that matter:

An intent has many attempts. A payment may be tried three times — declined, 3-D Secure challenge, success — and each attempt is a separate PSP call with its own reference and outcome. Collapsing them into the payment row loses the history you need to answer "why did this customer see three declines," and it makes the derived idempotency key for the PSP ambiguous.

sql

CREATE TABLE payment_attempts (
  id bigserial PRIMARY KEY,
  payment_id bigint NOT NULL REFERENCES payments(id),
  attempt_no int NOT NULL,
  psp_idempotency_key text NOT NULL UNIQUE,   -- deterministic: derived, not random
  psp_reference text,                          -- filled once the PSP responds
  outcome text,                                -- 'succeeded'|'declined'|'errored'|'unknown'
  decline_code text,
  UNIQUE (payment_id, attempt_no)
);

outcome = 'unknown' is a real, required state. A timeout is not a failure (Module P-16). An attempt whose result we never learned must be recorded as unknown so a sweeper can resolve it, rather than being silently treated as failed — because "treated as failed" plus a retry is exactly how a customer gets charged twice.

The End-to-End Flow, With the Ambiguous Failure Marked at Every Hop

text

 CLIENT            OUR API                      PSP                      BANK
   │                  │                          │                        │
   │ 1 token + amount │                          │                        │
   │  + Idempotency-Key                          │                        │
   ├─────────────────►│                          │                        │
   │                  │ 2 CLAIM the key + INSERT payment(created)         │
   │                  │    ── one local transaction; row exists BEFORE    │
   │                  │       we call anyone. This is load-bearing.       │
   │                  │ 3 INSERT attempt(psp_idempotency_key = derived)   │
   │                  │                          │                        │
   │                  │ 4 authorize(token, amount, idem-key)              │
   │                  ├─────────────────────────►│ 5 auth request         │
   │                  │                          ├───────────────────────►│
   │                  │      ⚠ AMBIGUOUS ────────│◄───── approved ────────┤
   │                  │◄─────── 200 auth ok ─────┤                        │
   │                  │ 6 CAS payment → authorized; attempt → succeeded   │
   │◄─── 200 ─────────┤                          │                        │
   │                  │                          │                        │
   │                  │◄──── 7 webhook: authorization.succeeded ──────────┤
   │                  │    (may arrive BEFORE step 6, or before step 5    │
   │                  │     even returns — see below)                     │
   │                  │ 8 verify sig → inbox INSERT → 200 in <50 ms       │
   │                  │ 9 worker: ledger transaction + CAS state          │
   │                  │10 outbox → receipt email, order fulfilment        │

Step 2 is the most important line in the diagram. The payment row and the idempotency claim are written, and committed, before we call the PSP. Three things depend on this:

A retry of the client's request finds the claim and replays rather than re-charging (Module P-16).
If we crash at step 5, a durable row exists saying "we were about to charge, with this derived key" — which is what makes the sweeper below able to resolve it.
The webhook can arrive before our own HTTP call returns. This is not hypothetical; it is routine, because the PSP fires the webhook when the authorisation completes and our response is still in flight. If the payment row does not exist yet, the webhook handler has nothing to attach the event to, and a handler that responds 404 teaches the PSP to retry a webhook for an event we will shortly know about. Writing the row first turns a race into a non-event.

Step 4 uses a deterministic derived key, "{tenant}:{payment_id}:{attempt_no}", never a fresh UUID. If we time out and retry the same attempt, the PSP recognises the key and returns the original result instead of authorising again. This is the one mechanism that closes the atomicity gap across a boundary we do not control, and it only works if the key is reproducible.

Steps 7–9 encode the rule that surprises people: the webhook is authoritative, our synchronous response is optimistic. For 3-D Secure, bank transfers, or any delayed method, there is no synchronous outcome. So the design must treat the webhook as the primary path and the HTTP response as an optimisation — which means the state machine has to accept the same transition arriving from either direction, idempotently. Systems built the other way round (HTTP response is truth, webhook is a backup) fail the first time a customer pays by a method that settles tomorrow.

Step 8 is Module P-17's queue-then-ack, non-negotiable here. Verify the signature over raw bytes, insert into an inbox table with UNIQUE (psp_event_id), return 200 in tens of milliseconds. Doing ledger work inline makes our processing time the PSP's timeout, and their retry then duplicates work while we are already slow.

The Failure Scenarios, Worked

#	Failure	Mechanism that saves us
1	PSP call times out; charge may or may not have happened	Retry the same attempt with the same derived key. The PSP returns the original result. Attempt stays `unknown` until resolved.
2	We crash after the PSP succeeded, before recording it	Sweeper finds attempts in `unknown` older than N minutes, queries the PSP by our idempotency key, and records the true outcome. The PSP is the recovery source.
3	Duplicate webhook delivery	`UNIQUE (psp_event_id)` on the inbox; the second insert is a no-op and we still return 200.
4	Webhooks arrive out of order (`captured` before `authorized`)	The CAS guard returns zero rows for an impossible transition. Buffer the event and re-drive it after the missing one, or fetch current state from the PSP. Never force the transition.
5	Two workers process the same capture concurrently	CAS: one wins, one gets zero rows and exits. No lock held across a network call.
6	Refund request exceeds what was captured	Compute `captured − refunded` from the ledger inside the transaction that inserts the refund, and reject if insufficient. Never trust a client-supplied "remaining" figure.
7	Duplicate refund submitted twice	Refund carries its own idempotency key; the ledger transaction's `source_event_id` makes the second insert fail.
8	PSP is down	Circuit-break per PSP (Module A-7) and fail the payment fast rather than queueing it. Queue notifications and captures of already-authorised payments, never a new charge — a payment the customer thinks failed must not be charged twenty minutes later.
9	Chargeback arrives 60 days later	A dispute transition plus a ledger transaction moving money out of `psp_clearing`. It is a normal money movement, not an exception.
10	Reconciliation break: PSP has a charge we do not	An orphan charge. The money exists, so this is not a "delete it" case — investigate and either attach it to a payment or refund it. This is why `unknown` attempts must be greppable.

Two of those deserve elaboration, because they are the ones teams get wrong.

Scenario 2, the sweeper, is not optional. Module P-16 established there is no ordering of two non-transactional writes that survives a crash between them. The derived idempotency key handles the retry path; the sweeper handles the abandoned path, where nothing retries because the process that would have died. Without it, a small number of payments per day sit permanently in unknown — money taken from a customer and not recorded, which is both a support problem and, at any volume, a regulatory one.

Scenario 8 is the correctness-over-availability rule in action. The instinct is to queue failed charges and retry when the PSP recovers, because that preserves revenue. Do not. A customer who saw a failure has usually paid another way or left, and charging them later — when their intent has expired and they are not looking — produces disputes. Authorised-but-uncaptured payments are safe to retry, because the customer already consented to that amount and the authorisation is the record of it.

Reconciliation: Three Sources of Truth That Must Be Made to Agree

You have three records of the same money, none of which is complete:

Your ledger — what you believe happened.
The PSP's settlement report — what they processed, with their fees.
The bank statement — what actually arrived.

They will differ, every single day, for legitimate reasons as well as bugs. A daily reconciliation job that classifies the differences is the only mechanism that turns silent divergence into a bounded, worked list. It is the last line of defence for every atomicity gap in this design.

Break type	Typical cause	Action
In PSP, not in our ledger	crash between charge and record (scenario 2)	sweeper resolves; investigate the residue
In our ledger, not in PSP	recorded optimistically without confirmation	a design bug — never record before the PSP confirms
Amount mismatch	partial capture, partial refund applied on one side only	correct with a reversing transaction
Fee mismatch	fee schedule changed, or fees modelled as one event with the charge	fix the model, not the numbers
Timing / cutoff	PSP's day ends at a different hour or timezone than ours	expected; match on the PSP's settlement batch, not on our calendar day
FX difference	rate applied at a different moment	record the rate used at transaction time and compare against it
Payout mismatch	a payout batch includes refunds and chargebacks netted	reconcile at the batch level, not per payment

Three rules that make reconciliation useful rather than ceremonial:

Never auto-correct. A job that silently "fixes" differences destroys the evidence of the bug that caused them and can compound an error. Produce a break list, alert on the count and the aggregate value, and require a human decision that writes a reversing transaction with a reason.

Alert on unreconciled count and age. A break that is three days old is a different problem from one that appeared this morning, exactly as Module P-19's oldest-job age is more informative than a count.

Reconcile on the PSP's boundaries, not yours. Matching your midnight-to-midnight against their settlement batch guarantees a permanent, meaningless population of "breaks" that trains everyone to ignore the report — which is the actual failure mode of most reconciliation processes.

Scale: Where This Design Bends, and What Bends It

The hot account problem, and why balances need snapshots. Every payment touches psp_clearing. With an append-only ledger there is no row to contend on, so writes scale — but SELECT sum(amount_minor) over a billion entries is not a query you run per request. The standard answer is a balance snapshot: a periodic materialised row per account per period, and a current balance computed as latest_snapshot + sum(entries since). The sum then covers minutes of data instead of years. Snapshots are derived and rebuildable, so they can be wrong without the ledger being wrong — which is the property that makes them safe to cache.

Partition the ledger by month (Module P-9). It only grows, retention is 7 years, and no query legitimately scans the whole thing. Partitioning makes old data cheap to store, cheap to exclude, and possible to move to colder storage (Module O-8) without touching the schema.

Shard by tenant or payment ID when one primary is not enough (Module P-6). Payments shard cleanly because a payment's lifecycle touches only its own rows plus platform accounts — but note that platform-level ledger accounts are inherently cross-shard, which is why they are append-only entries rather than a balance anyone updates. If you must shard, keep each payment's entries and its transactions co-located, or an unbalanced transaction becomes possible.

Never run analytics on the ledger (Module P-22). Revenue by region by month is a warehouse question, and running it against the payment database reproduces that module's opening incident on the most latency-sensitive system you own. Stream the ledger out with CDC (Module P-23) — and here the append-only design pays off again, since an insert-only table is the simplest possible CDC source with no before-image or TOAST concerns.

Read models via CDC, not via joins. The customer-facing "your payments" list, the internal support view, and the finance export are three different read models. Building them from a CDC stream keeps the write path narrow and lets each one be reshaped without touching the payment schema.

Rate-limit and bound concurrency per tenant (Module P-18, Module P-8). A single merchant running a batch of 200,000 charges must not consume the PSP connection budget or the worker pool that everyone else's payments use.

Security, Compliance, and the Conflict Worth Knowing

PCI scope is an architecture decision, made once. Tokenise at the client, never let a PAN reach your servers, never log a request body from the payment path, and the compliance burden stays small. It is worth re-stating because a single "let's just proxy it for a better UX" decision moves you into a different regulatory category with an annual audit attached.

Least privilege on the ledger. The application role should be able to INSERT into ledger tables and SELECT, and should have no UPDATE or DELETE grant at all. Enforcing immutability with a database grant rather than with code review is the difference between an invariant and an intention.

Audit everything, including reads. Who viewed which customer's payment data is itself a compliance question in most regimes.

And the conflict: a customer exercises their right to erasure (Module O-9) over data you are statutorily required to retain for 7 years as financial records. These do not cancel out, and the resolution is a scoping exercise, not a choice: financial-record obligations override erasure for the transaction data itself — amounts, dates, references — while personal data not required for that purpose (marketing preferences, device fingerprints, support chat history) must go. The practical mechanism is to separate the two from the beginning: keep the ledger free of personal data beyond a customer key, hold the personal data in a separately-encrypted store, and satisfy erasure by crypto-shredding that store's per-subject key. Designing this in on day one is cheap; retrofitting it means rewriting the ledger, which the immutability requirement forbids.

Why this matters in production: payment bugs are found by customers and by month-end finance, not by monitoring. Error rate can be zero, latency fine, and every dashboard green while a subset of payments sits in unknown, a fee model quietly diverges, or a duplicated refund path pays out twice. The three things that actually catch it are the unreconciled break list, the count of unknown attempts older than a threshold, and a ledger balance assertion — sum(amount_minor) = 0 across all accounts per currency — run as a scheduled check. That last one is a single query, it must always be exactly zero, and it is the cheapest correctness alarm in the entire system.

What We Deliberately Did Not Build

Multi-PSP routing. A second provider for redundancy and cost is a real requirement at scale, and it attaches at the attempt layer: an attempt already carries a provider, so routing is a policy above it. The hard part is that each PSP has its own idempotency semantics and window.
Fraud scoring. A synchronous decision point before authorisation, with a strict latency budget and a fallback (Module A-7) — because a fraud service being down must not stop payments, and "fail open" is a business decision, not an engineering one.
Marketplace payouts and splits. More ledger accounts, and a payout lifecycle with its own state machine. The ledger built here extends to it without redesign, which is the main argument for double-entry over a balance column.
Subscription billing. A scheduling problem (Module P-19) plus dunning and retries against declined cards, layered on top of this service rather than inside it.
Real-time fraud/velocity limits. Module P-18's counters, keyed on card fingerprint and customer, with the abuse-limiter posture rather than the fairness one.

Summary

The non-functional requirement that reorders the design is correctness over availability. Everywhere else in this course an ambiguous failure is resolved by retrying and deduplicating later; here, when the two conflict, fail the payment. A retry is a bad minute for the customer, a double charge is a liability.
Tokenise at the client and never let a card number reach your servers. It is one decision that determines whether you have a payment service or a PCI-audited environment.
Money is an integer in minor units, always paired with a currency, never a float, and never mixed within one account or one ledger transaction. Currency scale differs (JPY 0, GBP 2, KWD 3), and FX rates are NUMERIC recorded at transaction time — a rate looked up later will not reproduce the historical figure.
A mutable balance column is wrong three times over: it is a relative operation and therefore not idempotent, it destroys the history that makes errors findable, and it serialises every payment on one row lock while producing a dead tuple per update.
The ledger is append-only and double-entry: entries sum to zero per transaction and that invariant is asserted at write time, UNIQUE (source_event_id) is the entire duplicate defence, corrections are reversing transactions rather than updates, and balances are derived sums. Enforce immutability with a database grant, not a code review.
Fees, captures and payouts are separate transactions arriving at different times, because the PSP reports them separately. Modelling a charge and its fee as one atomic event makes reconciliation impossible.
Payment state is an explicit machine with several terminal states, advanced only by compare-and-set. A guarded UPDATE … WHERE state = $expected makes a duplicate or out-of-order transition a deterministic no-op returning zero rows, with no lock held across a network call. Zero rows is an outcome to interpret, not an error to retry.
An intent has many attempts, and outcome = 'unknown' is a required state. A timeout is not a failure; recording it as one and retrying is precisely how a customer is charged twice.
Write the payment row and the idempotency claim before calling the PSP. It makes client retries replayable, gives the sweeper something durable to resolve, and — critically — means a webhook that arrives before your own HTTP response has a row to attach to, which is routine rather than hypothetical.
Derive the PSP's idempotency key deterministically from tenant, payment and attempt. A fresh UUID inside the retry path defeats the one mechanism that closes the atomicity gap across a boundary you do not control.
The webhook is authoritative; the synchronous response is optimistic. For 3-D Secure or bank transfers there is no synchronous outcome at all, so the state machine must accept the same transition from either direction, idempotently. Verify, insert to an inbox with UNIQUE (psp_event_id), and ack in tens of milliseconds.
The sweeper for unknown attempts is not optional — the derived key handles retries, but nothing retries an abandoned attempt whose process died, so the PSP becomes the recovery source, queried by the key you chose.
When the PSP is down, fail new charges fast and queue only what is already consented to. Charging a customer twenty minutes after they saw a failure produces disputes; capturing an existing authorisation is safe.
Three-way daily reconciliation is the required backstop, classifying breaks by cause rather than auto-correcting them — auto-correction destroys the evidence of the bug. Alert on unreconciled count and age, and reconcile against the PSP's settlement batch rather than your calendar day, or you generate permanent meaningless breaks and train everyone to ignore the report.
At scale: balance snapshots plus a short delta sum, monthly ledger partitions for 7-year retention, shard by payment or tenant while keeping a transaction's entries co-located, stream to the warehouse with CDC rather than running analytics on the ledger, and bound per-tenant concurrency so one merchant's batch cannot consume everyone's capacity.
Statutory retention beats the right to erasure for financial records, and does not extend to everything else — so separate ledger data from personal data on day one and satisfy erasure by crypto-shredding the personal store's per-subject key. Retrofitting this means rewriting an append-only ledger, which its own immutability forbids.
The cheapest correctness alarm in the system is one query: sum(amount_minor) across all accounts per currency must be exactly zero. Run it on a schedule, alongside the unreconciled break list and the count of stale unknown attempts — because payment bugs are found by customers and by month-end finance, not by error rates.

That closes Phase 2. Practitioner has been about a single system's data: keeping it correct under concurrency, distributing it, moving it asynchronously, and getting it back out. Phase 3, Architect, changes the subject to coordination between systems — what happens when there is no single database to be the arbiter, when agreement itself must be constructed from unreliable messages, and when the failure you must design for is not a slow query but a service that has become unreachable while remaining alive. It opens with consensus, because every distributed lock, leader election and replicated log later in the phase is built on it.

Knowledge Check

A payment service records balances as UPDATE accounts SET balance = balance + $1 WHERE id = $2, inside the same transaction as the payment record. During a flash sale, throughput collapses, and separately a finance review finds a handful of accounts whose balances cannot be explained by any list of payments. What does the capstone prescribe, and which single mechanism fixes the duplicate-application problem?

A team's payment flow calls the PSP first and writes the payment row only after receiving a response, generating a fresh UUID as the PSP idempotency key on each attempt. They see three symptoms: occasional duplicate charges after timeouts, webhook handlers returning 404 for events they later learn about, and a small daily residue of PSP charges that appear in the settlement report but nowhere in their database. What single ordering change addresses all three, and what else is required?

During a PSP outage lasting 25 minutes, an engineer proposes queueing failed charge attempts and replaying them once the PSP recovers, to avoid losing revenue. Separately, they propose that the nightly reconciliation job auto-correct amount mismatches by adjusting the ledger to match the PSP's figure. What does the capstone say about each?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule P-23: Change Data Capture Next Module A-1: Consensus, Clocks, and the Ordering Problem

Discussion

Join the discussion

Loading comments...