Payment and Transaction Processing Systems Questions

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

Instrument the outcomes customers and finance experience, not just server health: authorization success split by processor (the company that actually routes the authorization call to the card networks and returns approved, declined or timed out, for example Stripe or Adyen), card brand (the network the card runs on, such as Visa or Mastercard) and issuer country (the country of the issuer, the shopper's own bank and the party that actually decides approve or decline); how fraud scores and decisions are distributed; how long money sits in each state between capture (telling the processor to actually collect funds that were only held at authorization) and settlement (the multi-day process by which that captured money actually lands in the merchant's bank account); and how close each dependency is to its limits. Page on-call only when a signal is customer-impacting now, urgent, and actionable by a human, measured against an SLO (service-level objective, a target such as "99.9% of authorization requests succeed technically over 30 days") and using burn-rate alerts; everything that is slow-moving, needs business judgment, or can wait until morning goes to a ticket or a dashboard.

What to instrument, by failure family

1. Degraded authorization health

• Technical success rate (the call got an answer from the processor) versus approval rate (the issuer said yes). They fail differently: a processor outage kills the first; a misconfigured merchant category code (the four-digit code that tells the issuer what kind of business this is, which some fraud rules key on) or an issuer's new fraud rule drops the second while every call "succeeds".
• Approval rate segmented by processor, card brand, issuer country, payment method and merchant. A 30-point drop for one issuer is invisible in the global average.
• Decline-code mix: a jump in "do not honor" (the issuer's generic refusal, usually a fraud or risk rule) versus "insufficient funds" (the shopper's own account, not fraud) tells you whether it is fraud rules or customer behaviour.
• Latency percentiles (p50, p95, p99: the latency that half, 95% and 99% of calls beat) per processor, and timeouts, especially timeouts with unknown outcome, which risk double charges: a naive retry cannot tell whether the first attempt actually succeeded, so if it did, retrying charges the card a second time.
• Business canary: payments completed per minute compared with the same minute last week. It catches problems no technical metric sees (a broken checkout button).

2. Fraud-signal drift

• Distribution of fraud scores over time, compared to a baseline with a drift statistic such as PSI (population stability index, which compares the share of traffic in each score bucket now versus a reference period; values above about 0.25 are conventionally treated as a large shift).
• Decision rates: approve / review / decline / step-up (sending the shopper through 3-D Secure authentication, an extra verification step such as a one-time code, that shifts liability for fraud onto the issuer) per hour.
• Fallback rate: share of decisions made without the primary fraud model or vendor.
• Lagging truth: chargeback (the issuer forcibly reversing a captured payment after the cardholder disputes it) and confirmed-fraud rates by cohort week (they arrive weeks later, so they are for trend, not paging).

3. Stuck or mismatched settlements

• Age of money in each state: oldest captured-but-not-settled payment, oldest unknown outcome, oldest payout in_transit. An "age of oldest item" metric catches a stuck pipeline that a throughput metric misses.
• Settlement file arrival: did today's file from each processor arrive, and does its record count look normal?
• Reconciliation mismatch (comparing two independent records of the same money and flagging where they disagree): count and total amount of payments present in the ledger (our own append-only record of every money movement) but not in the processor's settlement file, and vice versa, plus the fee variance against the contract.
• Ledger integrity: debits equal credits per shard; a non-zero sum is always a severe bug.

4. Capacity problems

• Saturation: database connection pool use, queue consumer lag (how far behind a consumer is from the newest message on the queue), partition skew (one partition of a queue or database getting far more traffic than the others, so adding more consumers does not help), thread pools per processor adapter.
• Headroom against external limits: requests per second versus each processor's contracted rate limit, and 429 (rate-limited) responses.
• Error budget: remaining budget for the month per SLO.

Paging versus dashboard: the decision rule

A signal pages only if all four are true:

1. Customers or money are affected now (not "might be next week").
2. It is urgent: waiting until business hours makes the damage materially worse.
3. A human can act: fail over a processor, roll back, pause a merchant.
4. It is reliable: it would not have fired falsely more than rarely in the last quarter.

Burn-rate alerting (worked example)

With an SLO of 99.9% technical success over 30 days, the error budget is 0.1% of requests. Burn rate is how many times faster than "exactly on budget" you are failing. A 30-day window is 720 hours. Both thresholds start from the same paging rule, chosen backwards: page if a sustained burn would consume 2% of the month's budget within 1 hour, or 5% within 6 hours, because burning that fast for that long breaks the SLO well before the month ends. Solving each for the burn rate gives the numbers below:

• Burn rate 14.4 sustained for 1 hour consumes 14.4 × 1 ÷ 720 = 2% of the month's budget: page.
• Burn rate 6 sustained for 6 hours consumes 6 × 6 ÷ 720 = 5%: page.
• Slower burns become tickets.

Pair each long window with a short one (for example 5 minutes) that must also be firing, so the alert stops soon after recovery.

Minimum volume guard: a merchant with 20 payments an hour will show a "40% drop" on two declines. Segment alerts require a minimum count (say 200 attempts in the window) before they can fire.

Worked example: an approval-rate drop

Normal approval rate for UK debit via processor A is 94% on about 3,000 attempts per 10 minutes. It falls to 78%. The alert fires because the segment has volume and the drop exceeds the threshold, while the global approval rate only falls from 91% to 88% (UK debit via A is one segment among many). That is consistent with a total volume of about 16,000 attempts per 10 minutes across all processors and segments: this segment's approvals drop by 3,000 × (94% − 78%) = 480, and 480 as a share of 16,000 attempts is 3 points, matching the global 91% → 88% move. The decline-code panel shows "do not honor" spiking; latency is normal, so the processor is up but issuers are declining. On-call shifts that segment to processor B via the router, sees B's approval at 93%, and files a ticket with A. Without segmentation this would have looked like noise.

Trade-offs and pitfalls

• Alerting on causes (CPU at 80%) instead of symptoms pages people for things customers never notice.
• Dashboards without owners rot. Each ticket-level signal needs a named team and a review cadence.
• Per-merchant alerts at scale create thousands of noisy alerts; alert per segment and let merchant-level anomalies feed a daily report.
• Instrument the unknown outcomes: the most damaging payment incidents (double charges) show up there first.

Direct answer

For most merchants, and specifically for a medium-sized SaaS company that wants customers to adopt its product quickly, recommend embedded hosted fields (the payment service provider's, or PSP's, card inputs rendered in iframes inside the merchant's own checkout page): they keep the merchant in the smallest PCI self-assessment (SAQ A) like a redirect does, while keeping the checkout on-brand and in-page. Use a full redirect when speed of integration matters above everything else, and direct API tokenization in the merchant's own page only when there is a feature need that hosted fields cannot meet, because it moves the merchant into a much larger compliance scope.

The terms

• PCI DSS (Payment Card Industry Data Security Standard): the card networks' security rules for anyone who stores, processes or transmits card data. Smaller merchants prove compliance with a SAQ (self-assessment questionnaire); which SAQ applies depends on how card data touches their systems. The tiers differ hugely in size: SAQ A is a short questionnaire of a few dozen questions, SAQ A-EP runs to roughly 150 to 190 questions covering nearly the full standard, and SAQ D covers all 12 PCI DSS requirements (commonly cited as 300-plus questions across 90-plus pages) plus ongoing obligations SAQ A never asks for, such as quarterly external vulnerability scans.
• PSP (payment service provider): the company that processes cards for the merchant, for example Stripe, Adyen or Braintree.
• Tokenization: exchanging the card number for a token (a random reference) that the merchant can store and charge without ever holding the card number.
• Separate origin: the browser's same-origin security policy, which stops the parent page's JavaScript from reading anything inside an iframe served from a different domain (here, the PSP's). This is why a hosted-fields iframe keeps card data out of the merchant's own code even if that code is compromised.

The three options

About SAQ A: since 31 March 2025 the SAQ A version in force no longer lists the payment-page script requirements 6.4.3 (keeping and justifying an inventory of every script that runs on the payment page) and 11.6.1 (a mechanism that detects and alerts on unauthorized changes to that page); instead the merchant must confirm, as an eligibility condition, that its site is not susceptible to attacks from scripts that could affect its e-commerce systems. That means even an iframe merchant still has to keep its own page's scripts under control; it is just assessed through eligibility rather than those two controls. SAQ A-EP merchants must meet those script requirements in full.

Typical failure modes

Redirect

• Return-URL failures: shopper closes the tab after paying, so the merchant never sees the redirect back. Fix: rely on the server-side webhook to confirm the order, and treat the return as a hint.
• Trust drop at the hand-off: shoppers abandon when they land on an unfamiliar domain.
• Session loss across the round trip (cart emptied, logged out) if checkout state lives only in the browser.

Hosted fields

• The iframe script fails to load (ad blockers, strict Content Security Policy headers (a browser header that restricts which domains a page is allowed to load scripts and iframes from) that do not allow the PSP's domain, PSP incident): show a clear error and a redirect fallback.
• Styling and accessibility limits: the iframe controls focus and screen-reader labels, so test with keyboard and screen readers.
• Page-level script compromise: an attacker who injects script into the parent page can overlay a fake form on top of the iframe. This is why the SAQ A eligibility condition about scripts matters.

Direct API tokenization

• Skimming attacks (malicious JavaScript injected into the page reads the card fields directly); the merchant is responsible for detecting script changes.
• Scope creep: a developer adds logging on the checkout page or posts through the backend "for convenience" and the merchant is suddenly in SAQ D scope.
• Higher engineering and audit cost for every change to the checkout.

Worked example: the SaaS recommendation

A SaaS company with about 200 employees sells monthly plans at 49 to 499 USD and wants self-serve signup to convert quickly. Its constraints: a branded, in-app upgrade flow (sending users to another domain during signup hurts trust in a product they are just evaluating), a small security team, and no appetite for a large PCI programme.

• Redirect: fastest to ship (days), SAQ A, but the off-brand hop sits in the middle of the signup funnel.
• Direct API tokenization: full design control, but SAQ A-EP means payment-page script inventory, integrity checks and change detection, more audit questions, and ongoing engineering. Nothing the SaaS needs requires it.
• Hosted fields: recommended. SAQ A, the upgrade form looks native to the product, and the integration is a JavaScript SDK plus the same webhook handling a redirect needs. It fits the "fast adoption" goal because it removes the domain hop from signup without adding compliance work. Plan a redirect fallback for when the SDK fails to load.

What would change the recommendation: if the company needs a fully custom card form experience that the PSP's fields cannot style, or wants to route across several PSPs (in which case a vault or orchestration provider (a PSP-agnostic layer that tokenizes the card once and can route the resulting token to more than one downstream PSP), whose own hosted fields preserve SAQ A); if it sells mostly via invoices, the PSP's hosted invoice page (a redirect) is simpler still.

Trade-offs and pitfalls

• "We use tokens, so we are out of scope" is wrong if the card number passed through your page code or servers before becoming a token.
• Branding differences between hosted fields and native fields are smaller than teams expect; the conversion loss usually comes from the redirect's domain change, not from input styling.
• Whatever the option, confirm orders from server-side webhooks (HTTP callbacks the PSP sends directly to your server when a payment's status changes, bypassing the browser), never from what the browser reports back.

Direct answer

Keep the operational tables the question sketches (orders, payouts, adjustments) but make an append-only double-entry ledger the source of truth for money: every order, refund, dispute and correction becomes a balanced set of ledger entries (credits and debits that sum to zero), and a payout is simply "pay out the positive balance of this party's payable account as of the period end". The ETL (extract, transform, load) pipeline is incremental and idempotent: it reads order and adjustment changes, applies date-effective commission and tax rules, and posts entries with deterministic IDs so a rerun cannot double-count. Refunds, disputes and retroactive corrections never edit a past row or a paid payout; they post new entries in the current period, which is what makes the whole thing auditable.

Data model

Extending the sample schemas (amounts in integer minor units, the smallest whole subdivision of a currency, such as cents for USD, so 6000 means 60.00 dollars, suffixed _cents in the column names below, to avoid floating-point rounding):

orders(order_id, merchant_id, driver_id, jurisdiction, amount_cents,
       delivery_fee_cents, tax_cents, currency, status, created_at)
commission_rules(jurisdiction, rate_bps, effective_from, effective_to, rule_version)
tax_rules(jurisdiction, tax_type, rate_bps, collector{platform|merchant},
          effective_from, effective_to, rule_version)
adjustments(adjustment_id, order_id, party, amount_cents, reason,
            effective_date, created_at, created_by, approved_by)
disputes(dispute_id, order_id, amount_cents, status, opened_at, resolved_at)
ledger(entry_id, txn_id, account, amount_cents, source, posted_at,
       UNIQUE(txn_id, account))
payouts(payout_id, party, amount_cents, period_end, payout_date, status)
payout_lines(payout_id, txn_id)                 -- which entries a payout covered

• bps is basis points, hundredths of a percent: 1500 bps = 15%.
• Accounts are named per party: payable:m1 (owed to merchant m1), payable:d1 (owed to driver d1), revenue:commission, tax_payable:CA-ON (sales tax owed to the Ontario authority), customer_cash (a balancing account recording where the money for a transaction came from, not a real bank account) and bank_clearing (a balancing account recording that money has been handed off to the outside bank for an actual transfer, before that transfer settles).
• Sign convention: every ledger entry is a credit (posted positive) or a debit (posted negative), and one balanced transaction's entries always sum to zero. When an order is recognised, customer_cash is debited (negative) for the full amount the customer paid, because that same value is simultaneously credited (positive) into payable:*, revenue:commission and tax_payable:*; customer_cash is the balancing entry that makes one transaction close to zero, not a place where real money sits. A refund does the reverse: it credits customer_cash back up (positive) by the refunded amount, undoing part of the original debit, while debiting the responsible party's payable by the same amount to reduce what they are still owed.
• Date-effective rules with versions let you answer "which rate was used for this order, and which rate should have been".

Collector: platform-collected vs merchant-collected tax

The tax_rules.collector field (platform or merchant) decides who is legally responsible for remitting that jurisdiction's tax to the tax authority, and it changes what the ledger and the reports do:

• collector: platform (common where marketplace-facilitator law makes the platform responsible for tax on sales it facilitates): tax is posted to a platform-owned tax_payable:<jurisdiction> account, exactly as the CA-ON example above does, and the platform's own remittance report and filing cover it. The merchant never carries this tax as their liability.
• collector: merchant (the merchant remains the tax authority's registrant): the platform still collects the tax from the customer at checkout for cash-flow reasons, but it must pass that tax through to the merchant rather than keep it as a platform liability, because the merchant is the one who owes the tax authority and will file the return. The posting differs by one account: instead of crediting tax_payable:<jurisdiction>, credit payable:<merchant> for the tax amount too, so it flows into the merchant's own payout, and the report generator marks that line "collected on the merchant's behalf, remit yourself" rather than folding it into the platform's own remittance total.

Hand-computed illustration (not run; the CA-ON demo above stays single-jurisdiction, this shows the difference the collector field makes): a 50.00 order in a collector: merchant jurisdiction with a 10% commission and 8.00 tax, no driver fee for simplicity. Platform-collected posts customer_cash = -(50+8) = -58, payable:m = 50-5 = 45, revenue:commission = 5, tax_payable:jur = 8; sum = -58+45+5+8 = 0. Merchant-collected instead posts customer_cash = -58 (the customer still pays 58 at checkout either way), payable:m = 45+8 = 53 (the tax passes through into the merchant's own payout), revenue:commission = 5, and nothing at all to tax_payable:jur, because the platform never owes that 8.00 to the tax authority, the merchant does, out of the 53.00 they now hold; sum = -58+53+5 = 0. Both variants balance; only where the tax liability sits differs.

ETL pipeline

flowchart LR
  OLTP[(Order DB)] -->|CDC| RAW[(Raw change log)]
  RAW --> N[Normalize and validate]
  RULES[(Commission and tax rules)] --> C[Compute and post entries]
  N --> C
  ADJ[(Adjustments and disputes)] --> C
  C --> L[(Ledger)]
  L --> P[Payout run per period]
  L --> T[Tax remittance reports]
  L --> R[Reconciliation vs bank and payment provider]

The diagram's OLTP node (online transaction processing: the live application database that serves the app's everyday reads and writes, as opposed to an analytics warehouse) is the order database itself.

1. Extract: CDC (change-data-capture, streaming row changes from the order database's log) into a raw, immutable change log partitioned by date.
2. Normalize: validate currency, jurisdiction, required fields; quarantine bad rows rather than dropping them.
3. Compute and post: for each delivered order, look up the rule version effective on the order date and post one balanced transaction. The transaction ID is deterministic (order_id:v<rule_version>), and the ledger's unique key on (txn_id, account) makes posting idempotent.
4. Payout run: for each party with a positive payable balance as of the period end, create a payout and post payable → bank_clearing. Negative balances carry forward.
5. Reports: tax remittance (paying over the tax that was collected to the jurisdiction's tax authority, and filing the return that documents it) payable per jurisdiction per period, commission revenue, payout files for the bank.

Refunds, disputes and retroactive corrections

• Refund: an adjustments row plus ledger entries that debit the responsible party's payable. If the merchant was already paid for the order, their next payout is smaller, or their balance goes negative. Whether the platform returns its commission on a refund is a policy, expressed as an extra pair of entries.
• Dispute: on open, move the disputed amount to a held account; on resolution, post the final entries (release or loss). The dispute ID is the idempotency key. For example, suppose order o3 (merchant m2, 60.00 + 8.00 driver fee + 7.80 tax, recognised and corrected exactly as the demo below does it) is disputed three weeks after delivery, once m2 has already been paid the full 51.00 (6000 - 900 at the corrected 15% rate, in cents: payable:m2 is already 0 by then, per the demo's own printed balances). Opening the dispute posts post("dispute:o3-open", [("payable:m2", -5100), ("held:m2", 5100)]): payable:m2 goes to -5100 (m2 now owes the platform, to be recovered from future sales, exactly like the partial-refund case above), and a new held:m2 account holds the disputed funds separately so they cannot be paid out again mid-dispute. If the platform loses two weeks later: post("dispute:o3-lost", [("held:m2", -5100), ("customer_cash", 5100)]), releasing the hold back to the customer side of the ledger. If it had won instead, the reversing pair would be [("held:m2", -5100), ("payable:m2", 5100)], restoring m2's payable to exactly where it stood before the dispute opened.
• Retroactive correction: a rule was wrong (for example the Ontario commission was loaded as 20% when the contract says 15%). Add the corrected rule as a new version, compute the difference per affected order, and post correction entries dated today, referencing the original transaction. The paid payouts stay untouched; the difference flows into the next payout.

Worked example (runnable)

Three orders in Ontario, commission rule loaded wrongly at 20% then corrected to 15%, and a 10.00 partial refund charged to merchant m1 after week one was already paid:

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders(order_id TEXT PRIMARY KEY, merchant_id TEXT, driver_id TEXT,
  jurisdiction TEXT, amount_cents INT, delivery_fee_cents INT, tax_cents INT,
  status TEXT, created_at TEXT);
CREATE TABLE commission_rules(jurisdiction TEXT, rate_bps INT,
  effective_from TEXT, effective_to TEXT, rule_version INT);
CREATE TABLE adjustments(adjustment_id TEXT PRIMARY KEY, order_id TEXT, party TEXT,
  amount_cents INT, reason TEXT, effective_date TEXT, created_at TEXT);
-- append-only double-entry ledger: credits positive, debits negative; every txn sums to zero
CREATE TABLE ledger(entry_id INTEGER PRIMARY KEY, txn_id TEXT, account TEXT,
  amount_cents INT, source TEXT, posted_at TEXT, UNIQUE(txn_id, account));
CREATE TABLE payouts(payout_id TEXT PRIMARY KEY, party TEXT, amount_cents INT,
  period_end TEXT, payout_date TEXT);
""")
db.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?)", [
 ("o1","m1","d1","CA-ON",4000,600,520,"delivered","2026-09-01"),
 ("o2","m1","d1","CA-ON",2500,600,325,"delivered","2026-09-02"),
 ("o3","m2","d1","CA-ON",6000,800,780,"delivered","2026-09-03"),
])
# version 1 was loaded with the wrong rate (20%); version 2 corrects it to 15% retroactively
db.execute("INSERT INTO commission_rules VALUES ('CA-ON',2000,'2026-01-01','9999-12-31',1)")

def post(txn, legs, source, when):
    assert sum(a for _, a in legs) == 0, "unbalanced"
    for acct, amt in legs:  # INSERT OR IGNORE + UNIQUE key = idempotent rerun
        db.execute("INSERT OR IGNORE INTO ledger(txn_id,account,amount_cents,source,posted_at)"
                   " VALUES (?,?,?,?,?)", (txn, acct, amt, source, when))

def rate(jur, day, version):
    return db.execute("SELECT rate_bps FROM commission_rules WHERE jurisdiction=? AND ?"
      " BETWEEN effective_from AND effective_to AND rule_version=?", (jur, day, version)).fetchone()[0]

def recognise_orders(version, as_of):
    for oid, m, d, jur, amt, fee, tax, _, day in db.execute("SELECT * FROM orders").fetchall():
        comm = amt * rate(jur, day, version) // 10000
        post(f"{oid}:v{version}", [("customer_cash", -(amt+fee+tax)),
             (f"payable:{m}", amt-comm), ("revenue:commission", comm),
             (f"payable:{d}", fee), (f"tax_payable:{jur}", tax)], "orders", as_of)

def run_payouts(period_end, pay_date):
    rows = db.execute("SELECT account, SUM(amount_cents) FROM ledger WHERE account LIKE 'payable:%'"
                      " AND posted_at <= ? GROUP BY account", (period_end,)).fetchall()
    for acct, bal in rows:
        party = acct.split(":")[1]
        if bal > 0:
            pid = f"{party}:{period_end}"
            db.execute("INSERT OR IGNORE INTO payouts VALUES (?,?,?,?,?)", (pid, party, bal, period_end, pay_date))
            post(f"payout:{pid}", [(acct, -bal), ("bank_clearing", bal)], "payouts", pay_date)

recognise_orders(1, "2026-09-06")
recognise_orders(1, "2026-09-06")          # rerun of the same batch: no double count
run_payouts("2026-09-06", "2026-09-07")

# week 2: partial refund on o1 (merchant-fault, 1000c) and a retroactive rate correction
db.execute("INSERT INTO adjustments VALUES ('a1','o1','m1',-1000,'partial_refund','2026-09-09','2026-09-09')")
post("a1", [("payable:m1", -1000), ("customer_cash", 1000)], "adjustments", "2026-09-09")
db.execute("INSERT INTO commission_rules VALUES ('CA-ON',1500,'2026-01-01','9999-12-31',2)")
for oid, jur, amt, day, m in db.execute("SELECT order_id,jurisdiction,amount_cents,created_at,merchant_id FROM orders").fetchall():
    delta = amt * (rate(jur, day, 1) - rate(jur, day, 2)) // 10000   # commission over-charged
    post(f"{oid}:rate-v1-to-v2", [("revenue:commission", -delta), (f"payable:{m}", delta)], "correction", "2026-09-10")
run_payouts("2026-09-13", "2026-09-14")

for r in db.execute("SELECT payout_id, amount_cents FROM payouts ORDER BY payout_date, party"):
    print("payout", r[0], r[1])
for acct, bal in db.execute("SELECT account, SUM(amount_cents) FROM ledger WHERE account LIKE 'payable:%' GROUP BY account ORDER BY account"):
    print("balance", acct, bal)
print("ledger sums to", db.execute("SELECT SUM(amount_cents) FROM ledger").fetchone()[0])
print("commission revenue", db.execute("SELECT SUM(amount_cents) FROM ledger WHERE account='revenue:commission'").fetchone()[0])
print("tax owed CA-ON", db.execute("SELECT SUM(amount_cents) FROM ledger WHERE account='tax_payable:CA-ON'").fetchone()[0])

Output:

payout d1:2026-09-06 2000
payout m1:2026-09-06 5200
payout m2:2026-09-06 4800
payout m2:2026-09-13 300
balance payable:d1 0
balance payable:m1 -675
balance payable:m2 0
ledger sums to 0
commission revenue 1875
tax owed CA-ON 1625

Checking the numbers by hand:

• Week 1, at the wrong 20%: m1 gets (4000 − 800) + (2500 − 500) = 5200; m2 gets 6000 − 1200 = 4800; driver d1 gets the delivery fees 600 + 600 + 800 = 2000.
• Correction: the over-charge is 5% of each order, 200 + 125 = 325 back to m1 and 300 back to m2. Commission revenue ends at 2500 − 625 = 1875, which is exactly 15% of 12,500.
• m1 also has the 1000 refund, so m1's balance is 325 − 1000 = −675: nothing is paid and the debt carries forward to the next period. m2 is paid 300.
• Tax payable is 520 + 325 + 780 = 1625, unaffected by commission changes.
• The ledger sums to zero, and running the week-1 batch twice changed nothing.

Auditability

• Nothing is updated or deleted in the ledger; every entry carries source, the rule version, and (for adjustments) who created and who approved it.
• payout_lines ties each payout to the entries it paid, so any payout can be re-derived.
• Balances "as of" any date are a query (posted_at <= date), so you can reproduce a statement exactly as it was issued.
• Daily checks: ledger sum is zero; payouts sent match bank confirmations; tax payable per jurisdiction matches filed returns.

Trade-offs and pitfalls

• Recomputing history in place (rerunning last month's ETL with the new rate and overwriting payouts) is the common wrong turn: statements already sent no longer match the database and the audit trail is gone.
• Floats for money produce cent-level drift; use integer minor units and a stated rounding rule per jurisdiction.
• Multi-currency: post each entry in its own currency and record FX (foreign exchange) conversions as explicit transactions, never as a silently converted amount.
• Warehouse versus ledger: the analytics warehouse can be rebuilt from the ledger; the reverse is not true. Payouts must be driven from the ledger, not from a warehouse table.

Direct answer

Treat every bank message as an untrusted, possibly repeated report about money that your ledger (the internal, append-only record of money movements) already expects. Land each raw message unchanged in an append-only store, de-duplicate on the bank's own reference, match it to ledger entries by your payment reference and amount, and record the outcome as a new reconciliation record (proving your ledger and the bank's report agree, or documenting exactly how they do not) rather than changing the ledger. Anything that does not match becomes a tracked break that finance can see and resolve on a dashboard. Double-processing is prevented because the only thing a confirmation can do is move a ledger item from "expected" to "confirmed" once, guarded by a unique constraint.

Key terms

• Ledger: the internal, append-only record of money movements, using double-entry bookkeeping (each movement's debits and credits sum to zero).
• Reconciliation: proving two independent records agree, here your ledger and the bank.
• Confirmation: a message from the bank saying a transfer happened: a real-time webhook or API notification, or a line in an end-of-day statement file (formats such as ISO 20022 camt.053 or older MT940 files).
• Break: an item that does not reconcile (missing, unexpected, or a different amount).
• Eventual consistency: records may disagree for a while (the bank confirms tomorrow) but converge; the system must know which disagreements are merely early and which are real.

Architecture

1. Ingestion. Webhook receiver, API poller and statement-file fetcher each write the raw message, byte for byte, to an append-only raw store with arrival time and source. Nothing is parsed before it is safely stored, so any later bug can be fixed and replayed.
2. Normalisation. A parser turns each raw message into a standard record: bank reference, our reference (if the bank echoes it), amount in minor units, currency, value date (the date the bank treats the funds as having moved, which can differ from the date the confirmation message arrived), direction, source.
3. De-duplication. The dedup key is the bank's own transaction reference, with a unique constraint. The same transfer arrives as a webhook, a redelivered webhook and a statement line; the first creates the record, later ones are logged as corroborating sources on it, never as new money.
4. Matching engine. Matches by our reference and amount first, then falls back to fuzzy rules for references the bank mangled (same amount, same counterparty (the other party to the transfer, the sender or receiver on the bank's side), value date within one business day), which only ever propose a match for a human to confirm.
5. Reconciliation records. A separate table recon(ledger_item_id UNIQUE, bank_ref UNIQUE, status, matched_at, rule) records the pairing. The ledger itself is not updated by matching.
6. Break management and dashboards. Unmatched or mismatched items become tracked break records with an owner and an age; finance works them from the dashboards described later in this answer, under Dashboards for finance.

Eventual consistency and timing

Each expected ledger item has a due window: an instant transfer should confirm within minutes, a standard transfer by the next business day. Before the window closes it is pending, not a break. After it closes it becomes missing_from_bank. A confirmation that arrives after an item was flagged simply resolves the break. This avoids flooding finance with items that are merely early.

Worked example (runnable)

from collections import defaultdict

# Internal ledger: payouts we expect the bank to confirm (amounts in cents).
ledger = {
    "PO-1001": {"amount": 250000, "currency": "USD"},
    "PO-1002": {"amount": 99900,  "currency": "USD"},
    "PO-1003": {"amount": 1500,   "currency": "USD"},
    "PO-1004": {"amount": 730000, "currency": "USD"},
}

# Bank confirmations as they arrived: a webhook, a redelivered webhook, and the
# end-of-day statement line for the same transfer all carry the same bank_ref.
confirmations = [
    {"bank_ref": "B-77", "our_ref": "PO-1001", "amount": 250000, "source": "webhook"},
    {"bank_ref": "B-77", "our_ref": "PO-1001", "amount": 250000, "source": "webhook"},
    {"bank_ref": "B-77", "our_ref": "PO-1001", "amount": 250000, "source": "statement"},
    {"bank_ref": "B-78", "our_ref": "PO-1002", "amount": 99000,  "source": "statement"},
    {"bank_ref": "B-79", "our_ref": "PO-1004", "amount": 730000, "source": "webhook"},
    {"bank_ref": "B-80", "our_ref": None,      "amount": 4200,   "source": "statement"},
]

seen, unique, dupes = set(), [], 0
for c in confirmations:                 # dedup key: the bank's own reference
    if c["bank_ref"] in seen:
        dupes += 1
        continue
    seen.add(c["bank_ref"])
    unique.append(c)

result = defaultdict(list)
matched_refs = set()
for c in unique:
    exp = ledger.get(c["our_ref"])
    if exp is None:
        result["unexpected_in_bank"].append(c["bank_ref"])
    elif exp["amount"] != c["amount"]:
        result["amount_mismatch"].append((c["our_ref"], exp["amount"] - c["amount"]))
        matched_refs.add(c["our_ref"])
    else:
        result["matched"].append(c["our_ref"])
        matched_refs.add(c["our_ref"])
result["missing_from_bank"] = sorted(set(ledger) - matched_refs)

print("duplicates dropped:", dupes)
for k in ["matched", "amount_mismatch", "missing_from_bank", "unexpected_in_bank"]:
    print(f"{k}: {result[k]}")

Output:

duplicates dropped: 2
matched: ['PO-1001', 'PO-1004']
amount_mismatch: [('PO-1002', 900)]
missing_from_bank: ['PO-1003']
unexpected_in_bank: ['B-80']

Reading the result: B-77 arrived three times (two webhooks and a statement line) and counted once. PO-1002 was confirmed 900 cents short, typically a bank fee deducted in transit, which becomes a break assigned to finance. PO-1003 has no confirmation yet; whether that is a break depends on its due window. B-80 is money the bank says arrived that the ledger never expected, such as a customer paying by direct transfer, which must be investigated and never automatically booked.

Protecting against double-processing

• Unique constraints, not application checks. bank_ref is unique in the confirmations table and both ledger_item_id and bank_ref are unique in the reconciliation table, so no race between two workers can match one item twice or one confirmation twice.
• Idempotent consumers. (a consumer is idempotent when processing the same input twice has the same effect as processing it once.) Every worker's write is keyed so re-running a batch (after a crash, or a deliberate replay) produces the same result.
• Replays are safe by design. Because raw messages are stored and processing is idempotent, you can reprocess a day's files after fixing a parser bug without double counting.
• Downstream actions happen once. If a confirmation triggers something (release funds to a seller, mark an invoice paid), publish that event via an outbox (a table written in the very same local transaction as the reconciliation record, from which a separate process reliably publishes the event afterwards, so the write and the notification can never disagree) in the same transaction as the reconciliation record, and have the consumer de-duplicate by event ID.

Audit trail

• Raw messages retained unchanged for the regulatory retention period.
• Every reconciliation record stores which rule matched it (exact or fuzzy) and, for manual matches and break resolutions, who approved it and why.
• Corrections are new records that reference the one they supersede; nothing is overwritten.

Dashboards for finance

Trade-offs and pitfalls

• Fuzzy matching saves manual work but can pair the wrong items when many payments share an amount; keep it as a suggestion that needs approval above a small value.
• Using the webhook alone is fast but banks sometimes send webhooks for transfers that are later returned (the bank reverses a transfer it had already reported as sent, for example because the receiving account was closed); treat the statement as the authoritative confirmation and the webhook as early notice.
• Editing the ledger to make it agree with the bank destroys the evidence of what went wrong; post an adjustment transaction with a reason instead.
• Currency and fee handling: banks may convert currency or deduct fees; store gross, fee and net separately, or every foreign transfer looks like a mismatch.