Payment and Transaction Processing Systems Questions

Direct answer

PCI DSS (Payment Card Industry Data Security Standard, currently v4.0.1) has 12 requirements grouped under six goals: secure networks, protect account data, manage vulnerabilities, control access, monitor and test, and maintain a security policy. The cheapest control is not needing most of them: design the payment flow so card numbers go straight from the customer's browser or device to the payment provider and never touch the merchant's systems. Scope reduction shrinks the audit, but it never removes all responsibility: the merchant still owns the page that loads the payment form, its vendors, its policies and its incident response.

Key terms

• Cardholder data: the PAN (primary account number, the long card number), plus cardholder name, expiry date and service code when stored with it.
• SAD (sensitive authentication data): the CVV security code, full track or chip data, and PINs. It must never be stored after authorization.
• CDE (cardholder data environment): every system that stores, processes or transmits cardholder data, plus systems connected to them. PCI scope is the CDE.
• PSP (payment service provider): the company that processes card payments on the merchant's behalf.
• SAQ (self-assessment questionnaire): the form smaller merchants complete to validate compliance. Different SAQs apply to different payment designs, and the shortest ones apply when the merchant never handles card data.

The main PCI DSS control areas

In a cloud deployment, the provider covers part of this (physical security, hypervisor (the virtualization layer that isolates one customer's virtual machines from another's on the same physical hardware), some managed services) and publishes a responsibility matrix saying which requirements it meets, which you meet, and which are shared. You still own your configuration of everything the provider hands you.

Architecture approaches that reduce scope

Ordered from least to most merchant scope:

1. Redirect to a PSP-hosted payment page. The customer leaves the merchant site to pay on the PSP's page, then returns. The merchant never sees card data.
2. Embedded iframe or hosted fields. (an iframe, inline frame, is a small embedded webpage inside the merchant's page that is actually served by, and under the control of, a different origin, here the PSP) The card inputs are rendered inside frames served by the PSP, so the PAN goes from the browser to the PSP even though the page looks like the merchant's. The merchant receives a token (a random substitute for the card number).
3. PSP JavaScript that posts directly to the PSP from a merchant-served page, or client-side encryption of the card in the browser with the PSP's public key. The merchant's servers never receive a readable PAN, but the merchant's page itself can be tampered with to steal it, so more controls apply than in options 1 and 2.
4. Tokenization for storage and recurring billing. After the first payment, the merchant stores only the PSP's token and charges it later, so no PAN is at rest in merchant systems.
5. P2PE (point-to-point encryption) for card-present terminals: a validated terminal encrypts the card at swipe or tap and only the provider can decrypt it.
6. Network segmentation for anything that must remain in scope: isolate the CDE into its own account and network so the rest of the estate is out of scope, and prove the isolation with testing.

Worked example

An online retailer accepts 400,000 card payments a year on its website. That number matters for what follows: under the card networks' volume-based merchant levels, a merchant processing roughly 20,000 to 1,000,000 e-commerce transactions a year falls in the tier that validates PCI compliance by completing a Self-Assessment Questionnaire (SAQ), not the highest-volume tier (Level 1, over roughly six million transactions a year), which faces a mandatory annual on-site audit by a Qualified Security Assessor (QSA). At 400,000 payments, this retailer is squarely in SAQ territory, which is exactly why the SAQ A eligibility question below is the one that decides its validation path.

• Design A: its checkout page posts the card form to its own API, which calls the PSP. Its web servers, API servers, load balancers, logs and the admins of all of them are in the CDE. It must satisfy essentially all 12 requirements across that estate.
• Design B: it switches to PSP hosted fields and stores only tokens. Its servers never see a PAN. Its validation shrinks to a short questionnaire (for fully outsourced e-commerce this is SAQ A). What remains is protecting the checkout page that embeds the PSP frames, managing the PSP as a vendor, and its policies and incident response.

The SAQ A revision published in January 2025 (effective 31 March 2025) removed three payment-page requirements from SAQ A's own checklist (6.4.3: keep an inventory of every script that runs on the payment page, justify why each one is needed, and verify none has been tampered with; 11.6.1: run a change- and tamper-detection mechanism, checked at least weekly, that alerts on unauthorized changes to the payment page's security-relevant HTTP headers and script contents as the browser actually receives them; and 12.3.1: the targeted risk analysis that had backed how often 6.4.3 and 11.6.1 needed to run, no longer needed on SAQ A once those two are gone) and replaced them with two eligibility criteria: every payment-page element must be sourced only from a PCI DSS-compliant third-party service provider, and the merchant must confirm its site is not susceptible to attacks from scripts that could affect its e-commerce systems. In practice that still means controlling which scripts run on the checkout page and vetting who those scripts come from. The underlying requirements 6.4.3, 11.6.1 and 12.3.1 still exist in PCI DSS itself and still apply to merchants on SAQ D or a full on-site assessment; only SAQ A's own paperwork changed for the merchants eligible for it.

Trade-offs

Tokens also create PSP lock-in: a token from one provider does not work at another, which matters if you want a backup processor.

Residual responsibilities after scope reduction

• The page that hosts the payment form. A compromised checkout page can swap the PSP frame for a fake one. Script control, a content security policy (a browser header listing which script sources may run) and change monitoring on that page stay the merchant's job.
• Vendor management. Keep the PSP's attestation of compliance (a formal document, typically signed by a PCI-approved assessor, stating that the PSP's own systems were found compliant) on file, know which requirements it covers, and review it yearly.
• Policies, training, incident response. A breach plan and staff awareness are required regardless of design.
• Anything that drifts back into scope. A support agent who takes a card number over the phone and types it into a CRM (customer relationship management system, the software used to track customer interactions) pulls that CRM and its users back in. Scans for PAN patterns in logs, tickets and data warehouses catch this.
• Annual validation. Scope reduction lowers the effort; it does not remove the obligation to validate and to keep the evidence.

Direct answer

An operation is idempotent if doing it twice has the same effect as doing it once. In payments that property has to be engineered, because "charge this card 50 USD" is naturally not idempotent: sending it twice charges twice. Duplicates are unavoidable (networks drop responses, clients retry, users double-click, message queues redeliver), so the system must recognise a repeat of the same intent and return the first result instead of acting again. In a horizontally scaled service that recognition cannot live in any one server's memory; it has to live in shared, durable state, usually a unique idempotency key stored alongside the payment and passed on to the processor.

Where duplicates come from

Problems without idempotency

• Duplicate charges. Two charge requests, two captures, one angry customer, a refund, and quite possibly a dispute.
• Duplicate fulfilment. A "payment succeeded" event processed twice ships two parcels or issues two gift-card codes. This one is often worse than a double charge because the goods are gone.
• Duplicate refunds or payouts. Money leaves the platform twice and must be clawed back.
• Corrupted ledger (the running record of every debit and credit). The same transaction posted twice means balances, revenue reports and reconciliation (matching two independent records of the same money to confirm they agree) are all wrong, and the error is discovered days later.

Strategies for stateless, horizontally scaled services

"Stateless" means any request can land on any of, say, 40 identical instances. So the memory of "I have seen this before" must be in a shared store, and the check-and-act must be atomic.

1. Client-generated idempotency key. The client creates a random unique ID (for example a UUID, a universally unique identifier drawn from 122 random bits) once per payment attempt, stores it, and sends the same value on every retry. The server treats the key as the identity of the intent.
2. Unique constraint, not check-then-insert. "SELECT to see if the key exists, then INSERT" lets two instances both see "not found" at the same moment. A database unique index on the key (scoped per merchant or per user) makes exactly one insert win.
3. Store and replay the result. When the first request finishes, save its status code and body against the key. A retry gets the saved response, byte for byte, instead of a fresh execution.
4. Fingerprint the request. Store a hash of the request body with the key. The same key with a different amount is a client bug, and should be rejected rather than silently replayed.
5. Propagate the key downstream. When calling the processor, pass a key derived from yours. Then even if your service crashes between calling the processor and recording the result, the re-driven call cannot charge twice.
6. Idempotent consumers for events. A service consuming "payment succeeded" records the event ID in a processed-events table in the same database transaction as its side effect. A redelivered event hits the unique constraint and is skipped.
7. Make state transitions conditional. "Mark order shipped where status = 'paid'" is naturally idempotent: the second attempt matches zero rows. Modelling a payment as a state machine with legal transitions gets you a lot of idempotency for free.

Worked example

Maya taps Pay for a 50.00 USD order on a train.

Without a key: request 1 reaches the server, the card is charged, the response is lost in a tunnel. The app shows "Something went wrong", Maya taps Pay again, request 2 charges another 50.00. Her statement shows 100.00; support refunds 50.00; she may also have filed a dispute with her bank.

With a key: the app generated key 7f3c... when the order was created. Request 1 inserts the key, charges the card through the processor using the same key, stores 201 {payment_id: pay_881}. The retry with 7f3c... hits the unique constraint, finds the stored response and returns pay_881. One charge, and Maya sees "Paid".

And if the retry arrived while request 1 was still talking to the processor, it would find the key in an "in progress" state and get a "try again shortly" answer rather than starting a second charge.

Trade-offs and pitfalls

• Idempotency is not exactly-once. The request may still arrive and be checked several times; idempotency makes the effect happen once. People call this "effectively once".
• A key generated on each retry is useless. The key must be created once per intent and persisted on the client before the first attempt.
• Keys expire. Stores keep keys for a bounded window (Stripe documents that keys can be pruned once they are at least 24 hours old), so a retry after that window is treated as new.
• Scope matters. A key unique per merchant is fine; a key unique only per server instance is not.
• Do not cache failures you should retry. Decide deliberately whether a stored error is replayed or whether a retry is allowed to try again; validation errors and "conflict, still in progress" are usually not stored.

Direct answer

First stop the bleeding: halt or roll back the deployment and confirm with data that duplicates have stopped. Then scope exactly who was charged twice by comparing the processor's charges against our orders, refund every duplicate (or void it if still uncaptured), record each correction as a reversing ledger entry, and tell affected customers before they notice on their statements. The root-cause work has to explain a subtle point: a key collision on its own usually makes a payment get skipped (a different customer's request is answered with someone else's stored result), not charged twice. A double charge means one purchase ended up with two keys. So the investigation is about how the library change and the rolling deploy together turned one intent into two keys, and the long-term fix is keys that are random, generated once per intent, persisted before the first attempt, and verified by a duplicate-charge detector that can stop a deploy automatically.

Terms

• Idempotency key: a unique ID per payment intent, sent with every retry so the server can return the first result instead of charging again.
• Rolling deployment: replacing instances a few at a time, so for a while old and new versions serve traffic side by side.
• Compensating transaction: a new transaction that undoes the effect of an earlier one (a refund for a duplicate charge), because the original cannot be deleted.
• Entropy (of a key): how many effectively random, unpredictable bits it actually carries. A key built from fewer random bits, or from input data that repeats across requests, has low entropy and collides with another key far more often than its printed length would suggest.

1. Immediate containment (first hour)

1. Declare an incident, assign an incident commander, open a timeline document.
2. Stop the change: pause the rollout and roll back to the last known-good version, so the fleet is no longer mixed.
3. Stop amplifiers: temporarily disable automatic payment retries in clients and background jobs that could keep creating new keys.
4. Measure that it stopped: a query that counts orders with more than one successful charge in the last N minutes, grouped by service version. The incident is contained when that count stops growing, not when the rollback finishes.

2. Scoping the impact

Pull the processor's successful charges for the window (from its API or settlement report, not only our database, because our records may be exactly what is wrong) and group by order:

SELECT order_id, customer_id, count(*) AS charges, sum(amount_minor) AS total_minor,
       array_agg(psp_charge_id ORDER BY created_at) AS charge_ids
FROM psp_charges
WHERE created_at BETWEEN :deploy_start AND :rollback_done + interval '1 hour'
  AND status = 'succeeded'
GROUP BY order_id, customer_id
HAVING count(*) > 1;

Also check the opposite failure: orders with no charge but marked paid. If keys collided across different customers, some customers received someone else's cached success response and were never charged at all. That is a revenue and fulfilment problem, and it is the tell-tale sign of a true collision.

3. Root-cause analysis

Reconstruct, for a handful of affected orders, every request with its key, instance, service version and outcome. Then test hypotheses against that evidence:

Put numbers on the collision hypothesis. With $n$ keys in the retention window and $b$ random bits per key, the expected number of colliding pairs is approximately

which is just (number of distinct pairs of keys) times (the chance any one given pair matches): $n(n-1)/2$ is how many pairs you can form from $n$ keys, and $1/2^{b}$ is the chance two independent $b$-bit random keys happen to be equal, so their product is the expected count of colliding pairs.

If the library truncated keys to a 32-bit hash and the service handles 1,000,000 payments in a 24-hour window, that is about 116 expected colliding pairs per day (1,000,000 × 999,999 / 2 ÷ 2³² ≈ 116); at 100,000 payments, about 1.2. A version 4 UUID (universally unique identifier) has 122 random bits, giving about 9.4 × 10⁻²⁶ expected pairs at 1,000,000 keys, effectively zero. So "collisions were possible" is plausible only if entropy dropped by orders of magnitude, which the diff will show.

Finish with the causal chain in plain language, and the contributing factors that let it ship: no test pinned key derivation across versions, no canary metric (a small, closely watched signal, checked automatically during a rollout, that halts it the moment the signal turns bad, named after canaries once carried into mines to give early warning of gas) for duplicate charges, key generation delegated to a general-purpose library whose change was treated as a routine upgrade.

4. Remediation for customers

• Refund each duplicate (the later charge, keeping the one the order references), using a deterministic idempotency key per refund such as refund:<duplicate_charge_id> so the remediation script can be re-run safely. If a duplicate is still only authorized and not captured, void it instead: the hold disappears faster and no money moves.
• Customers never charged because of collisions: do not silently charge them later. Contact them, and decide per business policy whether to request payment or absorb the loss.
• Check for disputes already filed on duplicates before refunding, so a customer is not refunded twice (refund plus chargeback).

5. Compensating entries in the ledger

Never delete the duplicate charge from the ledger. Post a reversing entry linked to the incident. Two accounts do the work: processor_receivable is an asset account, money the processor owes us for charges it has taken but not yet paid out, and a debit increases an asset. sales_clearing is the account that holds a sale's proceeds until they clear, and a credit increases it. So the first row below records the duplicate exactly as if it were a real sale (receivable goes up, clearing goes up); the second row reverses both sides precisely (receivable back down, clearing back down), which is what a debit to sales_clearing and a credit to processor_receivable do:

The net effect is zero, the history shows what happened and why, and reconciliation will match the processor's refund line to the refund entry.

6. Notifying customers

Communicate proactively, within a day of confirming scope: what happened (charged twice for order X), what we did (refunded Y on date Z), when it will appear (refunds typically take several business days to show on a statement), and a support contact. Give support staff a script and a lookup tool. For large incidents, merchants and partners who need to know under their contracts get a summary too.

7. Long-term fixes

Architecture

• Keys are random (a version 4 UUID, universally unique identifier, or equivalent from a cryptographically secure generator), generated once per payment intent, stored with the order before the first request, and reused on every retry. Never derived from request contents, never regenerated in a retry loop.
• A second line of defence independent of keys: a unique constraint on (order_id, attempt_number) for successful charges, so one order cannot hold two successful charges without an explicit new attempt.
• Server-side fingerprint check: same key, different body is rejected, never retried with a new key automatically.

Process

• Contract tests (automated tests that pin the exact shape of an interface, here the key format and derivation logic, so either side changing it without the other breaks the build instead of production) that pin key format and derivation, run against both the old and new version in CI (continuous integration), so a mixed fleet cannot disagree.
• A canary metric, orders with more than one successful charge, that automatically halts a rollout when it rises above zero.
• Dependency upgrades in the money path are reviewed like code changes, with changelogs read, not auto-merged.
• Daily reconciliation that flags multiple charges per order, so any recurrence is caught within a day even if every other guard fails.
• A blameless post-mortem (a written review of the incident that focuses on what in the system and process allowed it to happen, not on blaming whoever wrote the code) with owners and dates for each action.

Trade-offs and pitfalls

• Refunding from our own records only misses charges our system does not know about; scope from the processor's data.
• Refunding without idempotency in the remediation script creates a second incident.
• Stopping at "the library did it" leaves the real gap (no cross-version test, no duplicate metric) open for the next library.
• Waiting for customers to complain multiplies support load and disputes; proactive refunds are cheaper than chargebacks.

Direct answer

I would build reconciliation as a pipeline with four stages: ingest each settlement file immutably and normalise it into a canonical settlement-line table; match lines to ledger entries by the processor's reference first and by fuzzy keys only as a fallback; classify every difference into a named break type (timing, partial, fee, FX (foreign exchange), duplicate, missing, unexpected) with a tolerance rule; and resolve automatically where the rule is unambiguous (post the fee or FX difference, carry a timing break to tomorrow) while routing everything else to an ops queue with ageing and ownership. The ledger is never edited to "make it match"; resolutions are new ledger entries with a link to the break they close.

Terms

• Settlement file: the processor's report of what it is paying you for a period: each transaction's gross amount, fees, currency and net, plus refunds, chargebacks and adjustments. Intra-day files arrive several times a day; daily files once.
• Ledger: your internal double-entry record of what should have happened (every entry's debits equal its credits; balances are derived, never edited).
• Break: any ledger entry or settlement line that does not match its counterpart exactly.
• Two-way vs three-way reconciliation: two-way matches ledger to settlement file; three-way adds the actual bank deposit, proving the net money arrived.
• Basis points (bps): a unit for small percentages. 1 bp = 0.01% of an amount, so 50 bps = 0.50%. Reconciliation tolerances are usually stated in bps because "within 0.5%" is easy to misread as 5% or 0.05%.
• FX gain/loss account: the ledger account that absorbs small, expected differences between the exchange rate quoted to the customer at checkout and the rate the processor actually settled at hours or days later. A variance inside tolerance is booked here as a realised gain or loss, not left open as an unexplained break.

Architecture

flowchart LR
    SFTP[Processor files and APIs] --> RAW[(Raw file store, immutable)]
    RAW --> PARSE[Parser per processor format]
    PARSE --> LINES[(Settlement lines)]
    LEDGER[(Ledger entries)] --> MATCH[Matcher]
    LINES --> MATCH
    MATCH --> BREAKS[(Breaks with type and age)]
    MATCH --> OK[Matched pairs]
    BREAKS --> AUTO[Auto-resolution rules]
    AUTO --> POST[New ledger entries]
    BREAKS --> QUEUE[Ops review queue]
    BANK[Bank statement] --> MATCH3[Payout match]
    LINES --> MATCH3

1. Ingest

• Pull files from the processor's SFTP (SSH file transfer protocol) drop or reporting API on a schedule; store the raw bytes with a content hash so a re-delivered file is detected and the same file is never loaded twice.
• One parser per processor format, producing canonical lines: psp, psp_ref, line_type (capture, refund, chargeback, fee, adjustment, payout), gross_minor, fee_minor, settlement_currency, original_currency, original_amount_minor, fx_rate, settlement_date, payout_id.
• Amounts are integers in minor units (cents) to avoid floating-point drift across millions of lines.
• A file that fails to parse or whose control totals (the file's own sum and line count) do not add up is rejected as a whole and alerted on. Half-loaded files create phantom breaks.

2. Match

• Primary key: the processor reference (charge ID, refund ID) stored on the ledger entry when the payment was made. This matches the vast majority of lines deterministically.
• Fallback key for lines without a clean reference (some adjustments, bank-originated items): amount + currency + date window + last four digits or merchant order ID, and a fallback match is always marked as lower confidence.
• Many-to-one: one ledger capture may settle as several lines (split shipments, partial settlement); one payout covers thousands of lines. Match at line level, then roll up to the payout for the three-way check.

3. Classify

4. Resolve

Automated resolution is allowed only where the right entry is unambiguous and bounded: posting an FX difference within tolerance, posting a contract-rate fee, carrying a timing break forward, creating a dispute record for a chargeback line. Each automatic resolution writes a ledger entry tagged with the rule that produced it, so auditors can see why the books moved. Everything else goes to a queue with an owner, an age, an amount, and a severity driven by amount × age.

Runnable matcher sketch

A small, deterministic version of the classify step: ledger entries in minor units, one settlement file in USD, a contract fee rule and an FX tolerance.

ch_3 is a capture taken in EUR: at checkout we quote the customer an exchange rate and record the USD amount we expect to receive at that rate, in expected_fx. Here 80.00 EUR was quoted at 1.0875 USD per EUR (8,700 ÷ 8,000), so we expect 87.00 USD; the settlement file below actually pays 86.83 USD because the rate moved slightly between quote and settlement.

The FX tolerance check, abs(got - exp) * 10000 <= FX_TOLERANCE_BPS * abs(exp), is the relative-difference test abs(got - exp) / abs(exp) <= FX_TOLERANCE_BPS / 10000 (is the gap, as a fraction of what we expected, within the allowed basis points) rewritten without division: multiplying both sides by abs(exp) avoids doing integer division on cent amounts, and multiplying by 10000 is exactly the basis-point conversion (1 bp = 1/10,000).

from collections import defaultdict

# Internal ledger: what we believe the PSP owes us, amounts in minor units (cents).
ledger = [
  # (psp_ref, type, currency, gross)
  ("ch_1", "capture", "USD", 10000),
  ("ch_2", "capture", "USD", 5000),
  ("ch_3", "capture", "EUR", 8000),     # settles to us in USD
  ("ch_4", "capture", "USD", 12000),    # only partly captured-and-settled so far
  ("ch_5", "capture", "USD", 3000),     # captured late on day 1, settles tomorrow
  ("re_1", "refund",  "USD", -2000),
]
expected_fx = {"ch_3": 8700}            # USD we expected at the rate we quoted

# Processor settlement file for the same day, settlement currency USD.
settlement = [
  # (psp_ref, type, gross_usd, fee_usd)
  ("ch_1", "capture",    10000, 350),   # contract says 320
  ("ch_2", "capture",     5000, 175),
  ("ch_2", "capture",     5000, 175),   # the processor listed it twice
  ("ch_3", "capture",     8683, 282),   # FX rate moved slightly
  ("ch_4", "capture",     7000, 233),   # partial settlement
  ("re_1", "refund",     -2000,   0),
  ("dp_9", "chargeback", -4500, 1500),  # dispute we never recorded
]

FX_TOLERANCE_BPS = 50                   # accept FX drift up to 0.50%
fee_schedule = lambda gross: round(gross * 0.029) + 30   # contract: 2.9% + 30c

by_ref = defaultdict(list)
for row in settlement: by_ref[row[0]].append(row)

results = []
for ref, typ, ccy, gross in ledger:
    rows = by_ref.pop(ref, [])
    exp = expected_fx.get(ref, gross)
    if not rows:
        results.append((ref, "MISSING_IN_SETTLEMENT", "timing break: hold until next file, age it"))
        continue
    if len(rows) > 1:
        results.append((ref, "DUPLICATE_IN_SETTLEMENT", f"{len(rows)} rows; raise with processor"))
        continue
    _, _, got, fee = rows[0]
    fee_ok = typ != "capture" or fee == fee_schedule(got)
    if got == exp:
        status = "MATCHED" if fee_ok else "FEE_MISMATCH"
        results.append((ref, status, f"net {got - fee}"))
    elif ccy != "USD" and abs(got - exp) * 10000 <= FX_TOLERANCE_BPS * abs(exp):
        results.append((ref, "MATCHED_FX_VARIANCE", f"post {got - exp} to fx_gain_loss"))
    elif 0 < got < exp:
        results.append((ref, "PARTIAL_SETTLEMENT", f"{exp - got} still outstanding"))
    else:
        results.append((ref, "AMOUNT_MISMATCH", f"expected {exp}, got {got}"))

for ref, rows in by_ref.items():         # rows the processor has but we do not
    results.append((ref, "UNEXPECTED_IN_SETTLEMENT",
                     f"{rows[0][1]} {rows[0][2]}, fee {rows[0][3]}; create dispute case"))   # fee is reported, not dropped

for r in results: print(f"{r[0]:<6} {r[1]:<25} {r[2]}")

Output:

ch_1   FEE_MISMATCH              net 9650
ch_2   DUPLICATE_IN_SETTLEMENT   2 rows; raise with processor
ch_3   MATCHED_FX_VARIANCE       post -17 to fx_gain_loss
ch_4   PARTIAL_SETTLEMENT        5000 still outstanding
ch_5   MISSING_IN_SETTLEMENT     timing break: hold until next file, age it
re_1   MATCHED                   net -2000
dp_9   UNEXPECTED_IN_SETTLEMENT  chargeback -4500, fee 1500; create dispute case

Reading the output: one fee overcharge detected against the contract rate (350 charged, 320 expected), one duplicate line caught before it could be booked twice, a 17-cent FX difference on the EUR payment within the 0.50% tolerance (17 ÷ 8,700 ≈ 0.20%, well under 50 bps) posted to the FX gain/loss account, a partial settlement with 50.00 still open, a late capture parked as a timing break, the refund matched, and a chargeback the ledger did not know about turned into a dispute case that carries both the disputed 45.00 and its 15.00 processor fee, so neither amount is silently dropped from what gets posted.

Operational concerns

• Continuous, not only nightly: matching intra-day files as they arrive shortens the time to detect a problem from a day to hours.
• Ageing and SLAs (service-level agreements for how fast breaks are cleared): a timing break older than the processor's settlement schedule becomes a real break; dashboards show breaks by type and age bucket, and total unreconciled value.
• Three-way check: sum the matched net lines for each payout and compare to the bank deposit. A payout that does not match its lines means money is missing between the processor and the bank.
• Re-runs are idempotent: the matcher can re-process a day and produce the same result; resolutions are linked to breaks so a re-run does not post them twice.
• Scale: matching is a join on psp_ref, easily batched per day and per processor; the difficult part is the long tail of unmatched lines, not the volume.

Trade-offs and pitfalls

• Editing ledger entries to force a match destroys the audit trail; always post a new, linked entry.
• Loose fuzzy matching creates false matches that hide real losses; prefer a clean reference captured at payment time, and flag every fuzzy match.
• Tolerances that are too wide turn reconciliation into a rubber stamp. Tolerances should be per break type and reviewed.
• Ignoring missing-in-settlement because "it is probably timing" is how a capture job that silently failed goes unnoticed for weeks.

Direct answer

True exactly-once is not achievable across a network and an external processor, so I design for effectively-once: the client sends an Idempotency-Key header, the API atomically claims that key in the same relational database that holds the payment, forwards a derived key to the processor, and stores the final response so every retry gets it replayed. Concurrent requests with the same key are resolved by a unique constraint (one wins, the rest get 409 Conflict and retry). Keys are kept for at least 24 hours and then expired by a background job. If the dedup store is unavailable the API refuses with a retryable 503 rather than processing unprotected, which is cheap to guarantee because the dedup store is the payments database.

Requirements and terms

• Exactly-once would mean the charge happens once no matter what fails. Across a network you cannot guarantee delivery exactly once, but you can guarantee at-least-once delivery (retries until acknowledged) plus deduplication (repeats have no extra effect). Together that is effectively-once.
• Load: 5,000 TPS (transactions per second) of POST /payments at peak.
• Correctness bar: no duplicate charges under client retries, concurrent duplicates, server crashes at any point, or processor timeouts.

API contract

POST /payments
Idempotency-Key: 5b1e4f0a-6c2d-4b7e-9a51-0e3f7d8c2a19
{ "amount": 4999, "currency": "usd", "customer": "cus_42", "payment_method": "pm_9" }

Amounts are integers in minor units (4999 = 49.99 USD) so the fingerprint is stable and nothing rounds.

Data model

CREATE TABLE idempotency_keys (
  merchant_id     bigint       NOT NULL,
  idem_key        varchar(255) NOT NULL,
  request_hash    bytea        NOT NULL,   -- SHA-256 of the canonicalised body (JSON put into one fixed key order and formatting first, so the same logical request always hashes the same way)
  state           text         NOT NULL,   -- in_progress | completed
  recovery_point  text         NOT NULL,   -- started | psp_called | finished
  locked_until    timestamptz,             -- lease on an in_progress key: how long the current claim is valid before someone else may take over
  payment_id      bigint,
  response_code   smallint,
  response_body   jsonb,
  created_at      timestamptz  NOT NULL DEFAULT now(),
  PRIMARY KEY (merchant_id, idem_key)
);
CREATE INDEX ON idempotency_keys (created_at);   -- drives expiry

CREATE TABLE payments (
  id            bigint PRIMARY KEY,
  merchant_id   bigint NOT NULL,
  amount_minor  bigint NOT NULL,
  currency      char(3) NOT NULL,
  status        text NOT NULL,             -- created | pending_psp | succeeded | failed | unknown
  psp_idem_key  text NOT NULL UNIQUE,      -- what we send to the processor
  psp_charge_id text
);

Two design choices in that schema matter, plus two columns worth naming plainly:

• Key scope is per merchant. Two merchants who both happen to send order-1001 must not collide.
• The primary key is exactly (merchant_id, idem_key), with no timestamp in it. It is tempting to range-partition the table by day (splitting one logical table into several physical segments, one per day, so dropping an old day is a single fast DDL statement instead of a slow row-by-row delete) so expiry becomes a cheap DROP of an old partition. But Postgres only enforces a PRIMARY KEY or UNIQUE constraint on a partitioned table if the partition column is itself part of that key, so the key would have to become (merchant_id, idem_key, day). A retry that lands just after midnight then gets checked against the new day's partition, where its own (merchant_id, idem_key) looks unused, and the duplicate check silently passes. I keep one unpartitioned table and expire rows with a background job that deletes rows older than the TTL in small batches (if 5,000 TPS were sustained, that is 5,000 deletes per second across the fleet, 625 per second per shard with 8 shards, a shard being one of several independent database instances that between them hold a slice of the rows, split here by merchant_id).
• locked_until is a lease on the key while it is in_progress. A lease is a time-boxed claim: whoever holds it acts as the owner, but only until the clock in locked_until passes, so a crashed instance can never block a key forever; once the lease expires, a retry or a sweeper may take over.
• recovery_point narrows down where a stuck key actually died, moving through started (key and payment rows written, the processor was never called: safe to just call it now), psp_called (the request went out and the outcome is unknown: only ever retry with the same psp_idem_key, never a fresh one), and finished (transaction 2 committed). The recovery step below reads this column instead of guessing.

Where the keys live, and why

I keep keys in the same relational database, same transaction as the payment row. The alternative, Redis (an in-memory key-value store) in front, is faster, but a key store that is not atomic with the payment state has a gap: write the key, crash before the payment row, and the two disagree. Redis can also fail over (its primary node dies and a replica that was copying its data is promoted to take over), and that can lose recent writes: replication to the replica is asynchronous, so a write the primary already acknowledged a moment before it died may never have reached the replica, and is simply gone once the replica takes charge. Here, that means losing dedup records and charging twice. Redis is acceptable only as a read-through cache (on a lookup miss it fetches from the database and stores the result, so the next identical lookup is served from memory) of completed responses in front of the database, never as the source of truth.

The request flow (atomic phases)

sequenceDiagram
    participant Cl as Client
    participant API as Payments API
    participant DB as Payments DB
    participant P as Processor
    Cl->>API: POST /payments + key
    API->>DB: txn 1: claim key, insert payment (pending_psp)
    DB-->>API: claimed
    API->>P: charge with psp_idem_key
    P-->>API: result or timeout
    API->>DB: txn 2: update payment, store response, key completed
    API-->>Cl: 201 + payment
    Cl->>API: retry with same key
    API->>DB: key completed?
    DB-->>API: stored response
    API-->>Cl: replayed 201

1. Transaction 1: insert the key (in_progress, lease locked_until = now() + 30s) and the payment row with a freshly generated psp_idem_key. Commit. If the insert conflicts, read the existing row: hash differs → 422; completed → replay; in progress with a live lease → 409.
2. External call to the processor with psp_idem_key, outside any database transaction (never hold a row lock across a network call).
3. Transaction 2: record the outcome on the payment, store the response, mark the key completed.

Crash between steps 2 and 3 is the hard case. The key is stuck in_progress with an expired lease. A retry (or a background sweeper) takes over the lease and reads recovery_point: at started, the processor was never actually called, so it just calls it now; at psp_called, the outcome is unknown, so it re-drives step 2 with the same psp_idem_key rather than a fresh one, and the processor recognises it and returns the original charge instead of creating a new one. If the processor timed out, the payment goes to unknown, and the sweeper queries the processor's status rather than guessing.

Runnable demonstration

This uses SQLite as the shared store and a fake processor that honours idempotency keys, then fires 20 concurrent requests with one key, a late retry, a reused key with a different amount, and a crash between the processor call and the write-back. It also stores the winning request's real status code, so a replay genuinely returns what the table above promises (the original status, not a different one invented for the replay case).

Three SQLite specifics carry the whole concurrency mechanism, so here is what each means before the code: BEGIN IMMEDIATE takes this connection's write lock right away, before any other statement runs, so two threads can never both believe they are about to insert the same row. INSERT OR IGNORE tries to insert a new row and, if a row with that primary key already exists, does nothing instead of raising an error. cur.rowcount == 0 is how the code tells which of those happened: zero rows changed means the insert was ignored, i.e. someone else already claimed this key first, so this request lost the race and must read what the winner recorded instead of proceeding as if it won.

import sqlite3, threading, hashlib, json, os, tempfile

DB = os.path.join(tempfile.mkdtemp(), "payments.db")

def setup():
    c = sqlite3.connect(DB)
    c.execute("""CREATE TABLE idempotency_keys(
        merchant_id TEXT, idem_key TEXT, fingerprint TEXT,
        state TEXT, payment_id TEXT, response_code INTEGER, response TEXT,
        PRIMARY KEY (merchant_id, idem_key))""")
    c.commit(); c.close()

class FakePSP:
    """Stands in for the processor. It also honours an idempotency key, as real PSPs do."""
    def __init__(self):
        self.lock = threading.Lock(); self.charges = {}
    def charge(self, psp_key, amount):
        with self.lock:
            if psp_key not in self.charges:
                self.charges[psp_key] = f"ch_{len(self.charges)+1}"
            return self.charges[psp_key]

def fingerprint(body):
    return hashlib.sha256(json.dumps(body, sort_keys=True).encode()).hexdigest()

stats_lock = threading.Lock()
claimed_by = []   # demo-only instrumentation: who actually won the INSERT race

def post_payment(psp, merchant_id, key, body, crash_after_psp=False):
    c = sqlite3.connect(DB, timeout=30, isolation_level=None)
    fp = fingerprint(body)
    c.execute("BEGIN IMMEDIATE")
    cur = c.execute("INSERT OR IGNORE INTO idempotency_keys VALUES (?,?,?,?,?,?,?)",
                    (merchant_id, key, fp, "in_progress", None, None, None))
    if cur.rowcount == 0:                      # someone already owns this key
        row = c.execute("SELECT fingerprint, state, response_code, response FROM idempotency_keys "
                        "WHERE merchant_id=? AND idem_key=?", (merchant_id, key)).fetchone()
        c.execute("COMMIT"); c.close()
        if row[0] != fp:
            return (422, "same key, different request body")
        if row[1] == "completed":
            return (row[2], json.loads(row[3]))   # replay the stored status code and body, exactly
        return (409, "original request still in progress, retry later")
    c.execute("COMMIT")                        # we own the key
    with stats_lock:
        claimed_by.append(key)
    charge_id = psp.charge(f"{merchant_id}:{key}", body["amount"])
    if crash_after_psp:
        c.close(); return (500, "process died before recording the result")
    resp = {"payment_id": charge_id, "status": "succeeded", "amount": body["amount"]}
    c.execute("UPDATE idempotency_keys SET state='completed', payment_id=?, response_code=?, response=? "
              "WHERE merchant_id=? AND idem_key=?", (charge_id, 201, json.dumps(resp), merchant_id, key))
    c.close()
    return (201, resp)

def recover_stuck(psp, merchant_id, key, body):
    """Sweeper for keys left in_progress: re-drive the PSP call with the SAME PSP key."""
    c = sqlite3.connect(DB, isolation_level=None)
    charge_id = psp.charge(f"{merchant_id}:{key}", body["amount"])
    resp = {"payment_id": charge_id, "status": "succeeded", "amount": body["amount"]}
    c.execute("UPDATE idempotency_keys SET state='completed', payment_id=?, response_code=?, response=? "
              "WHERE merchant_id=? AND idem_key=? AND state='in_progress'",
              (charge_id, 201, json.dumps(resp), merchant_id, key))
    c.close()

setup()
psp = FakePSP()
body = {"amount": 4999, "currency": "usd", "customer": "cus_42"}

# 1) 20 concurrent requests carrying the same key
results = []
def worker(): results.append(post_payment(psp, "m_1", "k-abc", body))
ts = [threading.Thread(target=worker) for _ in range(20)]
[t.start() for t in ts]; [t.join() for t in ts]
winner_payment_id = next(r[1]["payment_id"] for r in results if r[0] == 201)
consistent = all(r[0] == 409 or (r[0] == 201 and r[1]["payment_id"] == winner_payment_id) for r in results)
print("concurrent requests:", len(results))
print("requests that actually won the INSERT and called the processor:", len(claimed_by))
print("every other response was a 409 (still in progress) or a 201 replay of the same payment:", consistent)
print("charges at the PSP after the race:", len(psp.charges))

# 2) a late retry replays the stored response, with the same status code as the original
print("late retry:", post_payment(psp, "m_1", "k-abc", body)[0])

# 3) same key, different amount
print("reused key, new amount:", post_payment(psp, "m_1", "k-abc", {**body, "amount": 5999}))

# 4) crash between the PSP call and recording the result
print("crash case:", post_payment(psp, "m_1", "k-crash", body, crash_after_psp=True))
print("retry while stuck:", post_payment(psp, "m_1", "k-crash", body)[0])
recover_stuck(psp, "m_1", "k-crash", body)
print("retry after sweeper:", post_payment(psp, "m_1", "k-crash", body)[0])
print("charges at the PSP (one per distinct key, 2 keys used):", len(psp.charges))

Output (identical on repeated runs, because it prints only the invariants, not which thread won):

concurrent requests: 20
requests that actually won the INSERT and called the processor: 1
every other response was a 409 (still in progress) or a 201 replay of the same payment: True
charges at the PSP after the race: 1
late retry: 201
reused key, new amount: (422, 'same key, different request body')
crash case: (500, 'process died before recording the result')
retry while stuck: 409
retry after sweeper: 201
charges at the PSP (one per distinct key, 2 keys used): 2

Every response that does come back as a replay carries 201 with the same payment id as the winner: that is the point of "replayed exactly", a caller cannot tell from a replayed response whether it won the race or arrived after. A response can still legitimately be 409 if it was read while the winner's second transaction had not yet committed, which is safe (the client just retries). Only the claimed_by count, demo-only instrumentation that a real client never sees, proves that exactly one of the 20 actually reached the processor.

The final count is 2 because two distinct keys were used (k-abc and k-crash); each produced exactly one charge despite 20 racing requests, a replay, a conflicting reuse and a crash.

Concurrency strategy: 409 versus wait

When a second request arrives while the first is in flight, the server can block until the first finishes or return 409 immediately. I return 409 (Stripe does the same: concurrent conflicting requests are not saved and can be retried). Blocking ties up a server thread per duplicate for the length of a processor call, and at 5,000 TPS a retry storm during a processor slowdown would exhaust the pool exactly when things are already bad.

TTL (time-to-live)

Keys must outlive any realistic client retry horizon: mobile apps retrying after a restart, queued jobs retrying with backoff. Stripe documents pruning keys once they are at least 24 hours old; I use 24 hours as the minimum and keep the payment rows (with psp_idem_key) forever. A key reused after expiry creates a new request, which the API documentation must say plainly. For extra protection against client bugs that regenerate keys, I also enforce uniqueness on the merchant's own order_id where one is supplied.

Capacity

• Rows in the window: 5,000 × 3,600 × 24 = 432,000,000 keys per 24 hours.
• At about 600 bytes per row including a stored response body: 432,000,000 × 600 = 259.2 × 10⁹ bytes ≈ 241 GiB, before indexes. A 48-hour window doubles that to about 483 GiB.
• Writes: an insert and an update per request, so 10,000 key writes per second at peak, plus payment writes. Sharding by merchant_id across 8 database shards gives 1,250 key writes per second per shard, comfortable for a well-provisioned Postgres primary. Very large merchants are the hot-shard risk: because the split is by merchant_id, one merchant's traffic always lands on the same shard rather than spreading out, so a single very large merchant can push one shard well past its 1,250 average and may need a shard of its own.

When the dedup store is unavailable

Fail closed: return 503, do not process. Processing without dedup exchanges a brief outage for duplicate charges that take weeks of refunds and disputes to clean up. Because keys and payments share a database, the question mostly answers itself: if the database is down you cannot record a payment either. If a Redis cache sits in front, a cache outage falls through to the database unique constraint and costs latency, not correctness.

Testing partial failures, and reconciliation as the backstop

• Fault injection at every boundary: crash after transaction 1, after the processor call, during transaction 2; processor timeout; processor returns 500 then succeeds on retry. Each test asserts one invariant: charges at the processor equal distinct keys.
• Concurrency tests: many identical requests in parallel, as in the demo, against the real database engine and isolation level used in production.
• Mixed-version tests: old and new service versions must compute identical fingerprints and processor keys, so a rolling deploy cannot split one intent into two.
• Daily reconciliation (comparing two independent records of the same money, here our own payments table and the processor's own report of what it actually charged, and investigating every difference instead of assuming both are right): match the processor's charge list against payments by psp_idem_key. Any processor charge with no payment, or two charges for one order, is a break investigated the same day. Idempotency prevents duplicates; reconciliation proves none got through.

Trade-offs and pitfalls

• Holding a DB transaction open across the processor call turns processor latency into database lock contention.
• Storing the key only after success gives no protection during the in-flight window, which is when retries actually happen.
• Replaying errors blindly: decide which outcomes are final (a card decline is) and which are not (a 503 from our own dependency should not be stored).
• Fingerprinting the raw JSON string breaks when a client reorders fields; canonicalise first.