Cross-border payment reconciliation: matching multi-currency, multi-acquirer settlement files

TL;DR Reconciliation is the part of a payments stack nobody architects for on day one and everyone pays for on day 200. The job: prove that every internal transaction matches the acquirer's settlement file, in the right currency, with the right fees, on the right value date — or surface the diff fast. The mechanics: normalize files → land into an events table → project to a read model → diff against the internal read model → buckets for ops to resolve. The boring details (file formats, fee parsing, FX rounding, value dates) are where 90% of the work lives. If you've ever opened a CSV from an acquirer at the end of the month, sorted by amount, and tried to "just match it in Excel" — yes, this post is for you. What "reconciled" actually means A transaction is reconciled when, for the same logical payment, three views agree: What you sent — your internal record of the charge/payout (your read model). What the acquirer says happened — their settlement file or API report. What the bank actually credited / debited — the bank statement. Disagreements are normal. Persistent disagreements are how you lose money slowly and never know. The shape of a settlement file Across the major acquirers, settlement files look broadly similar — and broadly different in the places that matter: Field Variants you'll see Transaction reference acquirer's transaction_id , sometimes plus a merchant_reference round-tripped from you Gross amount minor units / decimal; transaction currency vs settlement currency Fees inline per-row, or aggregated at the file footer, or in a separate fees file FX inline rate vs separate FX file; sometimes only the converted amount Value date when the bank actually moves money — often T+1/T+2 from event date Adjustments refunds, chargebacks, fee corrections, reserves — usually mixed in Encoding UTF-8 if you're lucky; CP1252 / fixed-width / SWIFT MT940 if you're not Granularity one row per transaction or daily aggregates per merchant or both There's no industry-clean