Payment and Transaction Processing Systems Questions

Direct answer

Build it around an immutable allocation and a double-entry ledger (every money movement recorded as debit and credit lines that sum to zero, so the books always balance). When a ride is priced, a versioned policy splits the fare, in integer minor units (cents rather than decimal euros), into allocation lines, one per payer (employee, each department, the corporate subsidy, the partner), with tax computed per line; the split is stored with the exact policy version and exchange rate that produced it. Every later change (a refund, a re-priced partial ride, a currency correction) is a new allocation event that reverses and re-posts lines, never an edit. Each financial command carries an idempotency key (an identifier that makes a repeated request return the first result instead of acting again) derived from the business event, each ledger batch must sum to zero, and ERP exports are keyed batches that are matched back line by line.

Requirements (clarified)

• Split each ride's cost among up to four kinds of payer: the employee (charged to their card or deducted from pay), the corporate subsidy (the employer's central budget), departmental chargebacks (internal re-billing of cost to cost centres such as sales or engineering), and partner contributions (a third party, for example a venue or a ride provider promotion, paying a fixed or percentage amount).
• Refunds (full or partial), partial rides (cancelled mid-trip, re-priced), multi-currency (ride in one currency, employer books in another), jurisdiction tax rules, and export to external ERP systems (enterprise resource planning: the finance system of record, such as SAP or Oracle, where invoices and general-ledger journals live).
• Money must never be created or lost by rounding, and every number must be explainable months later.

Data model

flowchart TB
    RIDE[ride] --> PRICE[ride_pricing: fare, tax basis, currency]
    PRICE --> ALLOC[allocation: version, policy_version, fx_rate_id]
    ALLOC --> LINE[allocation_line: payer, amount_minor, tax_minor]
    LINE --> LEDGER[ledger_entry: account, debit or credit]
    LEDGER --> BATCH[erp_export_batch]
    BATCH --> ACK[erp_ack: matched, rejected]
    POLICY[allocation_policy versions] --> ALLOC

Transaction lifecycle

1. Priced: the ride completes; the pricing service emits RideCompleted(ride_id, fare, currency, jurisdiction).
2. Allocated: the engine loads the policy version in force at the ride time, computes lines (the algorithm below), computes tax per line, and writes allocation v1, its lines and the balanced ledger batch in one database transaction. Idempotency key: ride_id:completed.
3. Collected: per payer: the employee share is charged to their card (with its own processor idempotency key), department and subsidy shares accumulate as receivables (amounts owed to us that we expect to collect later) on the monthly corporate invoice, the partner share accrues (builds up over time as an amount owed) to a partner receivable.
4. Adjusted: a refund or re-pricing creates allocation v2 (ride_id:refund:<refund_id>) with negative or corrected lines; the original is never touched.
5. Exported: at period close, ledger entries roll up into ERP journal lines (grouped debit and credit entries, formatted the way the finance system expects to post them) per cost centre and GL (general ledger) account.
6. Reconciled: ERP acknowledgements and payment receipts are matched back; breaks go to a finance queue.

The split, in minor units, with no lost cents

Percentages rarely divide an amount exactly. The largest-remainder method floors each share, then hands leftover cents to the shares with the largest fractional parts (ties broken by a fixed ordering so the result is deterministic). The program below splits a 47.35 EUR tax-inclusive ride (the 47.35 already has VAT baked in, rather than VAT being added on top at checkout), applies a 12.00 partial refund pro rata to what each party paid, and derives the VAT (value-added tax) inside the employee's net share at 20%.

from fractions import Fraction

def split(total, weights):
    """Split an integer amount (minor units) by weights; largest remainder, sum is exact."""
    wsum = sum(weights.values())
    exact = {k: Fraction(total) * w / wsum for k, w in weights.items()}
    floors = {k: int(v) for k, v in exact.items()}           # amounts are non-negative
    leftover = total - sum(floors.values())
    # hand leftover cents to the largest fractional parts; ties broken by name for determinism
    order = sorted(exact, key=lambda k: (-(exact[k] - floors[k]), k))
    for k in order[:leftover]:
        floors[k] += 1
    return floors

fare = 4735                      # 47.35 EUR ride, tax-inclusive, in cents
partner = 500                    # partner contributes a fixed 5.00
rest = fare - partner            # 4235 left for employer and employee
corp_share = split(rest, {"corporate": 70, "employee": 30})
depts = split(corp_share["corporate"], {"dept:sales": 1, "dept:eng": 1, "dept:ops": 1})
alloc = {"partner": partner, "employee": corp_share["employee"], **depts}
print("allocation:", alloc, "sum =", sum(alloc.values()))

refund = 1200                    # 12.00 partial refund, pro rata to what each party paid
refund_alloc = split(refund, alloc)
print("refund:    ", refund_alloc, "sum =", sum(refund_alloc.values()))
net = {k: alloc[k] - refund_alloc[k] for k in alloc}
print("net:       ", net, "sum =", sum(net.values()))

# VAT inside the tax-inclusive employee portion at 20%: net = gross * 100/120
gross = net["employee"]
vat = gross - round(Fraction(gross * 100, 120))
print("employee gross", gross, "-> VAT", vat, "net of VAT", gross - vat)

Output:

allocation: {'partner': 500, 'employee': 1270, 'dept:sales': 988, 'dept:eng': 989, 'dept:ops': 988} sum = 4735
refund:     {'partner': 127, 'employee': 322, 'dept:sales': 250, 'dept:eng': 251, 'dept:ops': 250} sum = 1200
net:        {'partner': 373, 'employee': 948, 'dept:sales': 738, 'dept:eng': 738, 'dept:ops': 738} sum = 3535
employee gross 948 -> VAT 158 net of VAT 790

Every split sums exactly to its whole (4,735, 1,200 and 3,535 cents). The corporate portion (2,965 cents, 70% of 4,235 after the tie on half a cent went to "corporate") was then split across three departments, and the one leftover cent went deterministically to dept:eng. The VAT step above extracts tax only from the employee's line to keep the code short; in the real engine the same extraction runs once per payer line, each using that line's own tax_code and rate from the tax service, because a department (a business payer, potentially eligible to reclaim VAT) or the partner (who may sit in a different jurisdiction) can carry a different rate or exemption from the employee's consumer-facing rate.

Partial rides and refunds

• Partial ride (trip cut short, fare re-priced from 47.35 to 30.00): write allocation v2 that reverses v1 entirely and re-allocates 30.00 under the same policy version as the original ride, so a policy change in between does not re-price history. Concretely: v2 posts the negative of every v1 line (partner −5.00, employee −12.70, dept:sales −9.88, dept:eng −9.89, dept:ops −9.88, summing to −47.35) alongside a fresh split of the new 30.00 fare through the same policy: partner is still 5.00 (a fixed amount, unaffected by the fare), the remaining 25.00 splits 70/30 into corporate 17.50 and employee 7.50, and the 17.50 corporate share splits three ways to dept:eng 5.84, dept:ops 5.83, dept:sales 5.83 (the extra cent lands on dept:eng by the same tie-break rule as the original split). Net of the two versions, the payers move from employee 12.70 to 7.50, dept:eng 9.89 to 5.84, dept:ops 9.88 to 5.83 and dept:sales 9.88 to 5.83, partner unchanged at 5.00, a total swing of 17.35, exactly the 47.35 minus 30.00 fare reduction.
• Partial refund (12.00 goodwill credit, as above): allocate the refund pro rata to what each payer actually paid, unless the policy says otherwise (for example, "goodwill refunds go only to the employee"). That rule lives in the policy, not in code.
• Caps interacting with refunds: for example, a 200.00 ride with no partner contribution would ask the company for its uncapped 70% share, 140.00, but the 50.00 per-ride cap holds the company to 50.00 and the employee's line absorbs the 90.00 shortfall (60.00 uncapped plus the 90.00 absorbed = 150.00, so the ride still sums to 200.00); the company's capped 50.00 is then still split across the rider's departments the same way as any other corporate share. If the company cap was hit on v1, the refund recomputation must use the same cap logic, which is why the engine always recomputes from the policy rather than scaling old lines.

Multi-currency

• Allocation happens in the ride currency (what the fare was). Each ledger entry also stores the amount in the payer's functional currency (the employer's book currency) with the rate ID used, fixed at allocation time.
• Refunds are allocated in ride currency too; the functional-currency difference between the ride-day and refund-day rate is posted as FX (foreign exchange) gain or loss, not buried in the refund.
• The monthly corporate invoice is issued in the contract currency; any conversion is shown on the invoice with its rate.

Tax rules

• A tax service returns rate and treatment for (jurisdiction, service type, payer type, date). Rides are often taxed at the pickup location; business payers may be able to reclaim VAT (get the value-added tax they paid back from their own tax authority, which only works with a proper tax invoice) and need a tax invoice with their tax registration number, so the tax line is attached to each payer's allocation line, not only to the ride.
• Tax is computed on each payer's share and rounded per line; the sum of line taxes is what is invoiced and exported, so the ERP totals match the invoices exactly.

Idempotency

Auditability

• Append-only: allocations, lines and ledger entries are never updated or deleted; corrections are reversals plus re-posts.
• Reproducibility: each allocation stores policy_version, fx_rate_id, the tax service's response ID and the engine version, so an auditor can re-run it and get identical lines.
• Invariants checked continuously: each ledger batch sums to zero; for every ride, the sum over allocation versions of all lines equals the fare net of refunds; receivables per payer equal the sum of their open lines.
• Access: manual adjustments require a reason code and a second approver, and are themselves allocation versions.

ERP reconciliation

1. Export one journal per period per legal entity (a distinct registered company within the corporate group, each keeping its own books): lines aggregated by (GL account, cost centre, currency, tax code), each with a control total (the batch's total amount, included so the ERP can independently verify nothing was added, dropped or altered in transit) and the list of source entry IDs retained on our side.
2. The ERP returns an acknowledgement per batch (accepted, rejected with reason). Rejected lines, for example an unknown cost centre, are fixed via a mapping table and re-exported with a new sequence number; the ledger does not change.
3. Three-way match (comparing three independent records of the same money and requiring all three to agree) monthly: our ledger totals by cost centre vs the ERP's posted journals vs cash received from invoices and card settlements. Differences are typed breaks (missing in ERP, amount mismatch, unmapped cost centre, timing) with owners and ageing (how long each unresolved break has been open, since one sitting for weeks is a bigger problem than one from yesterday).

Trade-offs and pitfalls

• Storing only percentages and recomputing at report time drifts whenever policies or rates change; store the resulting amounts.
• Rounding each payer independently loses or invents cents; allocate with a remainder method so the parts always sum to the whole.
• Editing a posted allocation destroys the audit trail and the ERP no longer matches; always reverse and re-post.
• Synchronous ERP calls on the ride path couple ride completion to an external system's uptime; export in batches.
• What would change the design: if the only payers were the employee and a single employer with no tax reclaim, a simple two-line split with a monthly invoice would do, without a policy engine; if volume reached millions of rides per hour, partition the ledger by legal entity and period, keeping each batch-sums-to-zero check local to one partition.

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

Model money as a double-entry, append-only journal and keep a per-account balance row that is updated in the same database transaction as the journal entries, using a conditional update ("subtract only if the balance stays non-negative") so two concurrent debits can never both spend the same funds. Shard by merchant so each merchant's accounts live together and most transactions stay on one shard; split the platform's own hot accounts (shared balances, such as a fee-collection account, that nearly every transaction touches) per shard, and split very busy merchants into several sub-balances. Idempotency comes from a unique transaction ID stored in the same transaction, correctness is proved continuously by reconciliation, and reporting reads an eventually consistent copy that is never used to decide whether money can move.

Key terms

• Double-spend: the same funds being spent twice, for example two refunds that both see a balance of 1,000 and both succeed, taking the merchant to -200.
• Ledger / journal: the record of money movements. Double-entry means each movement is at least two lines (a debit on one account, an equal credit on another) summing to zero, so money is never created or destroyed by a bug without the books showing it.
• Idempotency: repeating the same request has the same effect as doing it once.
• Reconciliation: checking that internal records agree with each other and with processors (the external companies that route card authorizations to the networks and later move the settled money) and banks.
• Shard: one partition of the data held on its own database node.

Data model

• account(id, merchant_id, type, available, pending, version): one row per balance, for example merchant:42:available.
• txn(txn_id PRIMARY KEY, kind, created_at, ...): one row per business movement. The primary key is the idempotency guard.
• entry(txn_id, account_id, amount, ...): append-only; never updated or deleted. Corrections are new reversing transactions.
• Invariants: entries of every transaction sum to zero; each account's balance equals its opening balance plus the sum of its entries; available balance never goes below zero (or below an agreed negative limit).

Separate pending from available: a captured payment (one where the merchant has confirmed it wants the authorized funds, rather than a hold that might still expire unused) credits pending; it moves to available when settlement funds arrive (the days-later bank transfer that actually moves the money, distinct from the instant authorization hold). Refunds and payouts debit available only, which is what prevents refunding money the platform has not yet received.

Concurrency control

The balance check and the write must be one atomic step. Reading the balance, deciding in application code, then writing is the classic race: both requests read 1,000 and both write. Three correct options, differing in when they lock: pessimistic locking takes the lock before deciding, so every other writer waits; optimistic locking decides first, and only fails at write time if someone else got there first.

When a transaction touches several accounts, lock them in a fixed order (for example sorted by account ID) so two transactions cannot deadlock waiting on each other.

Runnable demonstration

This uses SQLite so it runs anywhere; the same statements work on PostgreSQL. One refund of 30 runs first, then 50 new refunds of 30 race each other alongside 10 client retries of the first refund.

import sqlite3, threading, os

DB = "/tmp/ledger-demo.db"
if os.path.exists(DB):
    os.remove(DB)

setup = sqlite3.connect(DB)
setup.executescript("""
CREATE TABLE account (id TEXT PRIMARY KEY, available INTEGER NOT NULL,
                      version INTEGER NOT NULL DEFAULT 0);
CREATE TABLE entry (id INTEGER PRIMARY KEY, txn_id TEXT NOT NULL,
                    account_id TEXT NOT NULL, amount INTEGER NOT NULL);
CREATE TABLE txn (txn_id TEXT PRIMARY KEY, kind TEXT NOT NULL);
INSERT INTO account VALUES ('merchant:42', 1000, 0), ('platform:refund_clearing', 0, 0);
""")
setup.commit()
setup.close()

def refund(txn_id, amount):
    """Debit the merchant, credit refund clearing, atomically and idempotently."""
    conn = sqlite3.connect(DB, timeout=30, isolation_level=None)
    try:
        # BEGIN IMMEDIATE grabs SQLite's write lock right away, instead of the default
        # behavior of only upgrading to a write lock at the first actual write; that
        # closes the gap where two transactions could both read the balance before
        # either one writes, which is exactly the race this function must prevent.
        conn.execute("BEGIN IMMEDIATE")
        try:
            conn.execute("INSERT INTO txn VALUES (?, 'refund')", (txn_id,))
        except sqlite3.IntegrityError:
            conn.execute("ROLLBACK")
            return "duplicate"
        cur = conn.execute(
            "UPDATE account SET available = available - ?, version = version + 1 "
            "WHERE id = 'merchant:42' AND available >= ?", (amount, amount))
        if cur.rowcount == 0:
            conn.execute("ROLLBACK")
            return "insufficient_funds"
        conn.execute("UPDATE account SET available = available + ? "
                     "WHERE id = 'platform:refund_clearing'", (amount,))
        conn.executemany("INSERT INTO entry (txn_id, account_id, amount) VALUES (?,?,?)",
                         [(txn_id, 'merchant:42', -amount),
                          (txn_id, 'platform:refund_clearing', amount)])
        conn.execute("COMMIT")
        return "ok"
    finally:
        conn.close()

print("first refund:", refund("refund-first", 30))   # balance 1000 -> 970

results = []
lock = threading.Lock()
def worker(txn_id):
    r = refund(txn_id, 30)
    with lock:
        results.append(r)

# 50 new refunds of 30 race each other and 10 client retries of the first refund.
threads = [threading.Thread(target=worker, args=(f"refund-{i}",)) for i in range(50)]
threads += [threading.Thread(target=worker, args=("refund-first",)) for _ in range(10)]
for t in threads: t.start()
for t in threads: t.join()

conn = sqlite3.connect(DB)
print("ok:", results.count("ok"),
      "insufficient_funds:", results.count("insufficient_funds"),
      "duplicate:", results.count("duplicate"))
print("merchant balance:", conn.execute(
    "SELECT available FROM account WHERE id='merchant:42'").fetchone()[0])
print("entries sum to zero:", conn.execute("SELECT SUM(amount) FROM entry").fetchone()[0] == 0)
print("balance equals 1000 + sum of merchant entries:", conn.execute(
    "SELECT 1000 + SUM(amount) FROM entry WHERE account_id='merchant:42'").fetchone()[0])

Output (identical on every run, because the outcome does not depend on thread scheduling):

first refund: ok
ok: 32 insufficient_funds: 18 duplicate: 10
merchant balance: 10
entries sum to zero: True
balance equals 1000 + sum of merchant entries: 10

After the first refund the balance is 970, which covers exactly 32 more refunds of 30 (970 / 30 = 32.3). All 10 retries are rejected by the txn primary key, so no refund applies twice, and no interleaving pushes the balance below zero.

Partitioning and sharding

• Shard key: merchant ID. All of a merchant's accounts sit on one shard, so the common transaction (debit merchant, credit something) commits on one node with a local transaction.
• Platform accounts are the trap. Every refund credits a platform "refund clearing" account; if that is one row, every shard's transactions contend on it and every transaction becomes cross-shard (touching accounts that live on two different shards, which cannot be updated together in one local, atomic transaction). Instead, give each shard its own platform sub-account (platform:refund_clearing:shard-17) and sum them for reporting.
• Hot merchants. A single row is updated one transaction at a time. If each update holds the row lock for about 2 ms (an assumption to measure), one row supports at most

A marketplace or large merchant above that gets its balance split into N sub-balances (say 16, for a ceiling near 8,000 updates/s under the same assumption). Credits go to a sub-balance chosen by hash; a debit takes from one sub-balance that has enough, and a background job rebalances. The cost: a large debit may need to gather from several sub-balances in one transaction.

Idempotency

• Every movement carries a txn_id derived from the business event (for example refund:re_881), not generated fresh on each attempt, so a retry produces the same ID.
• The txn insert, the balance updates and the entries commit together. A retry either finds the row (duplicate, return the original result) or finds nothing (the first attempt never committed, safe to apply).
• Events from upstream (payment captured, refund requested) are consumed at-least-once (a message may be delivered and processed more than once after a crash or retry, but is never silently dropped); the unique txn_id makes the consumer safe to re-run.

Eventual consistency for non-financial services

Journal entries are published through an outbox (an events table written in the same transaction as the entries) or through change-data-capture (reading the database's commit log) to a stream (a continuously flowing feed of events that other services subscribe to, instead of querying the database directly), which feeds the reporting warehouse, merchant dashboards and search. These can lag seconds or minutes. The rule: decisions that move money read the ledger primary; everything else may read the copy. A dashboard may show a balance a few seconds old; a payout job may not.

Reconciliation

• Internal, continuous: per transaction, entries sum to zero; per account, the stored balance equals the sum of its entries (the demo's last two lines are this check). Run it per shard in rolling windows and page on any difference.
• External, daily: processor settlement reports and bank statements are matched to ledger entries; unmatched items become tracked cases for finance.

Scaling to millions of merchants

Assume 2 million merchants and 50 million money movements per day, each written as 2 entries of about 200 bytes, with a peak 5 times the average:

If one shard comfortably sustains 2,000 entry writes per second (again, measure yours), peak needs 3 shards; choose 16 so each runs well below its limit and there is room for growth. Keep recent entries hot and move closed months to cheaper storage while keeping them queryable for audit.

Trade-offs and pitfalls

• Balance column vs computing from entries. Summing entries on every check is always correct but slows as history grows; the cached balance is fast but must be updated in the same transaction and verified by reconciliation.
• Cross-shard transfers (merchant to merchant on different shards) need a two-step flow through a clearing account with idempotent steps, not a distributed lock.
• Never update or delete entries. An "edit" destroys the audit trail; post a reversing transaction instead.
• Floating point for money causes rounding drift; use integer minor units (cents) plus a currency code.
• Reading balances from a replica to approve a refund reintroduces double-spend through replication lag.

Direct answer

Treat every multi-currency payment as three amounts that must never be confused: the presentment amount (what the shopper sees and is charged, in their currency), the settlement amount (what the payment provider actually pays you, in your payout currency) and the accounting amount (what your books record, in your functional currency: the single currency a legal entity keeps its accounts in, normally the currency of the country where it primarily operates). Lock an exchange rate at checkout with a short expiry, charge the shopper in the presentment currency so the authorization and the capture use the same number, record the quote that produced it, and book the difference between the quoted and the realized conversion as an explicit FX (foreign exchange) gain or loss line. Refunds and disputes are always paid back in the presentment currency, so the FX difference on them is a cost you must model, not a rounding error.

Requirements I am designing to

• A marketplace or merchant platform selling in one base (price-list) currency, USD in the example, to shoppers in 30+ currencies.
• Shopper-facing: see a final price in their currency before paying, and be charged exactly that.
• Finance-facing: every entry reproducible later (which rate, from where, at what time), month-end reports in one functional currency, and correct tax per locale.
• Money is stored as an integer count of minor units (cents, yen) plus an ISO 4217 currency code, never as a float. The minor-unit exponent differs by currency: JPY has 0 decimals, EUR 2, and a few (such as KWD, the Kuwaiti dinar) have 3.

1. Currency selection at checkout

• Default from the shopper's billing country or saved preference, with a picker to override. Geolocation (IP address) is only a hint; the card's issuing country and the billing address are better signals.
• Only offer currencies you can actually charge and settle. A currency your PSP (payment service provider, such as Stripe or Adyen) cannot process for that payment method must not appear in the picker.
• Two different mechanisms, do not mix them up:
  • Multi-currency pricing: you price and charge in the shopper's currency. The shopper's bank sees a local-currency charge and applies no conversion. This is the design below.
  • DCC (dynamic currency conversion): the shopper is shown a conversion at the moment of payment of a charge that would otherwise be in the merchant's currency, and chooses. It is regulated (the shopper must be shown the rate and the choice) and often a poor deal for the shopper, so treat it as a separate product decision.

2. Where rates come from

• A Rate Service pulls mid-market rates (the midpoint between the buy and sell price in the currency market, with no markup added) from one or two providers (a market-data vendor, a bank, or the PSP's own FX quotes) on a schedule, for example every 60 seconds for majors (heavily-traded currencies like EUR, GBP or JPY) and less often for thin currencies (low-volume currencies where quotes are sparser and move in bigger jumps).
• Every stored rate row is immutable: rate_id, base, quote, mid_rate, source, fetched_at. A new fetch is a new row; nothing is updated in place.
• Guards: reject a new rate that moves more than a threshold (say 3%) from the previous one without a second source agreeing; if every source is stale beyond a limit, stop offering that currency rather than charging on an old rate.
• The shopper rate is mid rate times (1 + spread). The spread is a product and pricing decision (it pays for FX risk and provider fees) and is stored with the quote so it is auditable.

3. When the conversion happens (pre-authorization vs settlement)

Authorization is the bank's real-time approval and hold; capture is you confirming the amount to collect; settlement is the provider paying you days later.

Rule: the rate must be fixed before authorization and the captured amount must not be re-converted. If the capture is delayed (ship later, booking confirmed later), the presentment amount stays the same and only the settlement conversion moves. That moving part is exactly what the FX variance line measures.

4. Storing display and accounting currency

CREATE TABLE fx_quote (
  fx_quote_id  uuid PRIMARY KEY,
  rate_id      uuid NOT NULL,               -- the mid rate used
  shopper_rate numeric(18,8) NOT NULL,      -- mid * (1 + spread)
  spread       numeric(6,4) NOT NULL,
  expires_at   timestamptz NOT NULL
);
CREATE TABLE payment (
  payment_id          uuid PRIMARY KEY,
  order_id            uuid NOT NULL,
  presentment_amount  bigint NOT NULL,      -- minor units, e.g. 9384
  presentment_ccy     char(3) NOT NULL,     -- 'EUR'
  base_amount         bigint NOT NULL,      -- price-list amount, e.g. 10000
  base_ccy            char(3) NOT NULL,     -- 'USD'
  fx_quote_id         uuid NOT NULL REFERENCES fx_quote,
  settlement_amount   bigint,               -- filled from the provider's settlement report
  settlement_ccy      char(3),
  settlement_rate     numeric(18,8),
  status              text NOT NULL
);

The ledger (an append-only record of money movements using double-entry bookkeeping, where every transaction has debits and credits that sum to zero) records each entry in its transaction currency and its functional currency (USD here), along with the rate used. Recognized revenue, FX spread income and realized FX gain/loss are separate accounts, so finance can see them apart.

5. Worked example (computed in Python with decimal, half-up rounding to cents)

• Price 100.00 USD. Mid rate at checkout: 1 USD = 0.9200 EUR. Spread 2%, so shopper rate = 0.9384 and the display price is 93.84 EUR. At the quote's own mid rate that EUR amount is worth 93.84 / 0.92 = 102.00 USD: 100.00 of revenue plus 2.00 of spread income.
• Converting the checkout mid rate to the same EUR-to-USD direction as the numbers below: 1 ÷ 0.9200 = 1.0870 USD per EUR. The provider settles two days later at 1 EUR = 1.0850 USD, below that 1.0870, meaning EUR weakened against USD between checkout and settlement: 93.84 x 1.0850 = 101.82 USD. Ledger lines: revenue 100.00, FX spread income 2.00, realized FX loss 0.18 (an actual cash-flow loss booked once the money changed hands, as opposed to an unrealized, mark-to-market change on a balance still held) (101.82 minus 102.00).
• Thirty days later the order is refunded in full. The shopper gets back exactly 93.84 EUR (never a re-converted amount). EUR has weakened to 1.0700 USD, so the refund costs 93.84 x 1.07 = 100.41 USD. Across sale and refund the platform keeps 101.82 minus 100.41 = 1.41 USD. This full refund reverses the whole sale, so none of the 2.00 spread income survives and none of the 100.00 revenue does either; what is left is purely the sum of currency movement across checkout, settlement and refund. It is tempting to compute that as spread income minus the settlement loss plus a "refund gain" measured against the checkout rate (2.00 − 0.18 + 1.59 = 3.41, where 1.59 is 102.00 minus 100.41), but that double-counts the checkout benchmark: the only correct calculation is actual cash received at settlement minus actual cash paid at refund, 101.82 − 100.41 = 1.41, because the spread and revenue no longer exist as separately kept amounts once the sale is fully reversed. If EUR had strengthened instead, the same refund would have cost more than was collected.

6. Reporting and disputes

• Reporting: roll up in functional currency using the rate stored on each entry (never today's rate applied to old rows). Show gross sales by presentment currency, spread income, realized FX gain/loss, and, at month-end, unrealized gain/loss (a paper gain or loss on a balance still held, not yet turned into cash, unlike the realized loss above which already was) on balances still held in foreign currency (revalued at the period-end rate).
• Chargebacks (the cardholder's bank reverses a charge on the cardholder's behalf): the disputed amount is in the presentment currency; the provider debits you in settlement currency at the rate on the day of the dispute, which is usually not the original rate. Store the dispute with its own rate and post the difference as FX gain/loss, so dispute losses and FX losses are not blended. When you respond to a dispute, the evidence shows the original presentment amount and the rate the shopper was shown and agreed to.
• Partial refunds are computed in presentment minor units from the original presentment amount, then converted at the refund-day rate for the books.

7. Tax and regulatory differences by locale

• Tax is calculated in the presentment currency on the presentment amount, because that is what appears on the shopper's receipt and what the tax authority expects. EU VAT (value-added tax) is usually shown tax-inclusive to consumers, US sales tax is added at checkout and varies by state and locality, Australia's and India's GST (goods and services tax) have their own registration thresholds.
• A tax engine (in-house rules or a tax provider) takes seller location, buyer location, product category and date, and returns rate and amount. Store the tax lines with the transaction and on the receipt.
• Some jurisdictions require invoices or tax reporting in the local currency even if you settle in USD; keep the presentment amounts, never only the converted ones.
• Currency controls: a few currencies cannot be settled offshore or have restrictions on refunds to other currencies. Encode a per-currency capability table (can present, can settle, refund rules) rather than scattering special cases.

8. Rolling out a new currency (phased)

1. Configuration: add the currency to the capability table (minor-unit exponent, rounding rule, supported payment methods, tax registration), disabled.
2. Offline tests: property tests (automated tests that generate many random inputs and check an invariant holds for all of them, rather than checking one fixed example) on money arithmetic (for any split, the parts sum exactly to the whole in minor units), a replay of last month's orders priced in the new currency, and end-to-end tests against the provider's test mode.
3. Shadow mode: compute and log the new-currency prices for real traffic without showing them; compare against expectations.
4. Canary: enable for a small share of shoppers in one country behind a feature flag (a runtime switch that turns code paths on for a chosen audience), watch authorization rate, refund and dispute rates, and daily reconciliation (matching your ledger against the provider's settlement report) for that currency.
5. Widen, keeping a kill switch that falls back to base-currency pricing if rates go stale or reconciliation breaks.

flowchart LR
    RS[Rate Service] --> Q[FX quote with TTL]
    Q --> CO[Checkout: presentment price]
    CO --> AUTH[Authorize and capture in presentment ccy]
    AUTH --> PSP[Provider settles in payout ccy]
    PSP --> REC[Reconciliation]
    CO --> L[Ledger: presentment + functional amounts]
    REC --> L
    L --> RPT[Reports: spread, realized and unrealized FX]

Trade-offs and pitfalls

• Floats and "convert, then convert back". 0.1 + 0.2 in floating point is not 0.3; and converting 93.84 EUR back to USD at a different rate never returns 100.00. Store the original amounts and derive, never round-trip.
• Rounding per line vs per total. Convert the order total once, then allocate to line items with a largest-remainder split (round every line down to the nearest cent, then hand the leftover pennies to the lines with the largest fractional remainder, one cent each, until the total matches exactly) so the lines still sum to the charged total.
• Rate at authorization but capture much later. Locking gives the shopper certainty and gives you risk. For long gaps (a booking captured weeks later) either widen the spread for that product or settle in the shopper's currency and hold a balance in it.
• Recomputing reports with today's rate. It silently rewrites history; always use the rate stored with the entry.
• What would change the design: if the business settles in many currencies (holds EUR balances and pays EUR costs), FX exposure is naturally hedged (gains on one side offset losses on the other, so net exposure to rate movement is small) and you can drop the settlement conversion for those currencies entirely; if volumes are small, using the provider's built-in conversion and simply recording its reported rates is a reasonable first version.

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.