Payment reconciliation: build an engine that survives

Most payments stacks ship without a reconciliation engine. The team integrates an acquirer, money starts moving, and reconciliation becomes a spreadsheet someone updates on Mondays. That works until you add a second processor, a second currency, and a finance lead who asks why the settled amount is 0.4% short of what the dashboard says. Then reconciliation stops being a chore and becomes an architecture problem.

This is a practical guide to building a payment reconciliation engine that holds up at scale: the data model that makes matching possible, the matching ladder that handles missing IDs, and the failure modes that bite once you cross processors, currencies, and time zones.

What reconciliation actually has to prove

Reconciliation answers one question for every transaction: did the money you expected actually arrive, in the amount you expected, net of the fees you agreed to? You have two sides. On one side, your own ledger of charges (what you told the customer they paid). On the other, the settlement files your acquirers send (what the money movement actually was, after fees and FX). The engine's job is to match those two sides and explain every row that does not line up.

The reason this is hard is not the matching. It is that the two sides speak different dialects. Your ledger thinks in your transaction IDs and your presentment currency. The settlement file thinks in the acquirer's IDs, its own fee structure, its settlement currency, and a value date that can lag the transaction by days. Reconciliation is a translation problem before it is a matching problem.

Step 1: normalize every file into one event table

Do not try to reconcile files directly against your ledger. Settlement files differ by acquirer, by region, and by year (acquirers change their layouts without warning). Instead, parse every file into a single normalized table, often called settlement_events, and reconcile against that.

Each row should carry:

• Source identity: acquirer, file name, external transaction ID, original row stored as JSON so you can re-parse later.
• Money in minor units: gross, fees, and net as integers (cents, not floats). Floating point on money is a bug waiting for a rounding audit.
• Currency context: transaction currency, settlement currency, and the FX rate, stored as three separate fields.
• Time: the event timestamp and the value date (when funds actually settle).

Keeping the raw row as JSON matters more than it looks. When an acquirer quietly adds a column or shifts a fee into a new field, you re-parse history instead of begging them to resend six months of files. Version your parsers and keep the old ones, so a file from March still parses in September after the layout changed.

From this one table you project two read models: your internal charges and the settled transactions. Now reconciliation is a SQL join between two clean shapes, not a fight with raw files.

Step 2: match on IDs first, then climb a ladder

The happy path is an exact match on the acquirer's external transaction ID. In a healthy integration that covers the large majority of rows. The interesting work is what you do for the rest, because external IDs go missing constantly: truncated fields, fees delivered in a separate file with no ID, refunds that reference the original transaction under a different column name.

Do not jump straight to fuzzy matching. Build a deterministic ladder and only descend when the rung above fails.

A fallback that combines amount, currency, a value-date window, acquirer, and card last-4 will recover the overwhelming majority of rows that lack a usable external ID. The point is that each rung is still deterministic and explainable. You are not guessing, you are widening the key in a controlled way and recording which rung made the match, so a human can audit any decision later.

Machine learning has a place here, but later. Rules break when patterns change and need manual upkeep; ML learns from historical matches and copes with messier data. But a learned matcher you cannot explain is a liability in a financial control, and most teams hit an accuracy ceiling around 85 to 92% not because the algorithm is weak but because the system lacks context (the parent transaction, the fee schedule, the prior match history). Add the context layer before you reach for the model.

Step 3: model fees, FX, and refunds as first-class events

Three things turn a clean matcher into a wrong one if you model them lazily.

Fees. They arrive inline on the transaction row or in a separate fee file, depending on the acquirer. Normalize both as distinct event types and aggregate them per transaction in the read model. Do not maintain two parallel code paths, one for inline fees and one for separate ones. One event shape, aggregated.

FX. This is where money silently disappears. Store the transaction currency, the settlement currency, and the conversion rate as three fields, and never store only the converted amount. The moment you collapse FX into a single settled number, you lose the ability to prove where a 0.3% gap came from, and you will be asked.

Refunds and chargebacks. Treat them as new events with their own external IDs, not as updates to the original row. The original charge stays immutable; the refund references it. Acquirers name that parent reference inconsistently (original_transaction_id, orig_txn, and worse), so normalize the field on ingest. An append-only event log here is not academic purity, it is what lets you reconstruct the full history of a disputed transaction months later.

If you are reconciling on-chain settlement too, the same append-only discipline carries over. We wrote about keeping fiat and on-chain ledgers honest in shipping stablecoin rails without the pain, and the integrity rules are the same on either rail.

Step 4: bucket the exceptions, do not hide them

A reconciliation run should never produce a single pass or fail. It should produce a diff that sorts every unmatched row into a typed bucket, each with its own owner and runbook:

• Missing in settlement: in your ledger, not in the file (money you expected that has not arrived).
• Unknown in settlement: in the file, not in your ledger (money that arrived you cannot explain).
• Currency mismatch: matched transaction, wrong settlement currency.
• Gross mismatch: amounts disagree beyond tolerance.
• Fee mismatch: the fee differs from the agreed schedule.
• Matched: the boring, healthy majority.

Set an amount tolerance so legitimate rounding from FX and fee math does not flood the queue. A band like plus or minus 0.01% or 0.05 in minor units, whichever is smaller, absorbs noise without hiding real gaps. Anything outside the band is a real exception, not rounding.

Step 5: verify with three metrics, every day

You cannot eyeball reconciliation health at scale. Put three numbers on a dashboard and alert on them:

1. Match rate by the T+1 close. A healthy integration sits above 99%. A drop is the first sign an acquirer changed a file layout.
2. Age of the oldest unmatched item per bucket. Reconciliation debt is like any debt: the old items are the dangerous ones.
3. Net currency delta per acquirer. It should stay inside your rounding tolerance. A drift means an FX or fee assumption is wrong somewhere.

When one of these moves, it is usually not a data glitch. It is a dependency you did not model. The most useful framing we have seen is that persistent reconciliation exceptions are a diagnostic: they signal hidden coupling in distributed systems, where "completed" means one thing to your ledger and another to the acquirer, or where a settlement window shifted under load and broke an ordering assumption nobody wrote down.

Why this matters more every quarter

The reconciliation surface is growing, not shrinking. Stripe's Sessions 2026 push into multicurrency treasury, instant settlement, and adaptive local pricing makes multi-currency, multi-rail money movement the default rather than the exception. Every new currency and every new rail multiplies the edge cases your engine has to absorb. The same pressure shows up in agentic commerce, where machine-driven micropayments add volume and new settlement paths; we covered those rails in what x402, AP2 and MPP mean for builders, and the tokenized-asset world raises the same integrity bar we discussed in real-world asset tokenization architecture.

The teams that treat reconciliation as a core service from day one (a normalized event table, a deterministic matching ladder, typed exceptions, and three honest metrics) spend their time resolving real discrepancies. The teams that bolt it on later spend their time arguing about whose number is right. If you are designing the part of your payments stack nobody architects for, we would be glad to talk through your reconciliation architecture before the second processor and the second currency force the question.