Engineering guide
Payment reconciliation system design. From raw files to proven payments.
How to design a payment reconciliation system: the data model, evidence ingestion, deterministic matching, idempotency, the ledger, exceptions and scale.
By Sanjay Singh, Founder · Last updated:
Talk to our engineersOn this page 13 sections
The short answer
A payment reconciliation system keeps every piece of evidence exactly as it arrived, turns it into typed records, matches those records to what you expected with versioned, deterministic rules, and raises everything that does not match as an exception with an owner. A payment is finished only when the evidence agrees, and late evidence can reopen it.
Most reconciliation systems that go wrong were designed as a comparison job: two tables in, a list of differences out. That works for a demo. It fails when files arrive late, providers correct them, webhooks are duplicated, a batch settles as one line, or someone asks six months later why a payment was marked matched. This guide sets out a design that survives those cases.
- IngestStore every file, callback and response as it arrived, with a hash and its source.
- NormaliseParse it into typed evidence records, one per payment line.
- MatchApply versioned rules to evidence and expectations.
- RaiseEvery break becomes a typed exception with an owner.
- CloseClose the payment when the evidence agrees. Late evidence reopens it.
What the system must guarantee
Write these down before choosing a database or a framework. Every design decision later is a trade-off against one of them.
- Deterministic: the same evidence under the same rule version always gives the same result. Re-running a day must not change its answers unless the evidence or the rules changed.
- Complete: every payment you instructed and every line you received ends in a known state. Nothing is dropped because it failed to parse or had no match.
- Explainable: every match shows the evidence and the rule that produced it; every exception shows why it was raised.
- Idempotent: ingesting the same file twice, or receiving the same webhook twice, changes nothing the second time.
- Append-only: corrections are new records, never edits. You can reconstruct what the system believed on any date.
- Reopenable: a return, reversal or corrected file that arrives after closure reopens the payment with its history intact.
The data model
Reconciliation is mostly a data-modelling problem. Separate what was instructed, what each party claims, what you expected to receive, and what you decided.
| Entity | What it holds | Why it is separate |
|---|---|---|
| Payment intent | What you asked for: amount, currency, beneficiary, purpose, your reference. | The instruction never changes because a provider said something different. |
| Attempt | One instruction to one provider, with its idempotency key and state. | A payment can have several attempts across providers; each needs its own evidence. |
| Evidence item | One record from one source: a webhook, an API response, a settlement line, a bank statement line. Raw payload hash, source, grade and received time. | Different sources disagree. You need all of them, not the last one. |
| Expectation | What evidence a payment should receive, from which source, by when. | Without expectations, a missing file looks the same as no problem. |
| Match | Which evidence was linked to which payment, by which rule version and tolerance. | Matches must be explainable and reversible. |
| Exception | A typed break with an owner, a due time and a resolution. | Breaks are work items, not rows in a report. |
| Ledger entry | The double-entry financial effect of each event. | The ledger tells you what you owe and hold; reconciliation tells you whether that is true. |
| Closure record | The evidence, rule versions and balances that closed a payment or a day. | So closure can be audited and reopened. |
Grade evidence by who produced it. A bank statement is stronger proof that cash moved than a provider’s settlement file, which is stronger than a webhook, which is stronger than your own records. When sources disagree, the disagreement itself is recorded; it is never resolved by quietly keeping the last value.
Ingestion: keep the original, then parse
- Store raw first. Save the file, callback body or response exactly as received, with a content hash, the source, the channel it came through and the time. Parse from the stored copy, never from memory.
- Make ingestion idempotent. A file with a hash you already hold is a duplicate delivery, not new evidence. For line-level idempotency, key each line on the source’s own identifier where it has one.
- Treat corrections as versions. A provider that re-sends a corrected settlement file has issued new evidence. Keep both versions and record which one superseded which.
- Verify callbacks before parsing. Check signature, source and freshness first. An unverified callback is logged, not interpreted.
- Normalise time. Store timestamps in UTC with the source’s own time zone and business date alongside, because cut-offs are defined in local time.
- Fail loudly. A line that cannot be parsed becomes an exception, not a skipped row.
Bank statements increasingly arrive as ISO 20022 camt.053 (end of day) and camt.052 or camt.054 (intraday and notifications), alongside MT940 and CSV. Each format needs its own parser, but all of them should produce the same evidence record shape.
Matching: deterministic rules, not guesses
Match in passes, from the strongest key to the weakest, and stop at the first pass that produces a unique answer.
- Exact keys: your idempotency key or client reference echoed by the provider.
- Provider reference: the provider’s payout id, stored on the attempt when it was accepted.
- Rail reference: an end-to-end or rail-level reference that appears on the bank side.
- Composite keys: amount, currency, beneficiary identifier and a time window, only when the stronger keys are absent, and only when the result is unique.
Support the shapes real settlement takes: one-to-one, one settlement line for many payouts (a batch or net settlement), and many lines for one payout (a payment and its fee). Apply tolerances for fee and FX rounding explicitly, and log every use of a tolerance on the match record.
Rules, tolerances and expectations are configuration with an owner and a version. When a rule changes, re-running the affected days shows exactly which results changed and why. A model can suggest a likely match for a person to review; it should never create one on its own, because a suggestion is not evidence.
Expectations: detecting what did not arrive
The hardest breaks are absences. A provider forgets a line; a file does not arrive; a callback is lost. A comparison job never sees these, because there is nothing to compare.
Declare, per source, what should arrive and by when: this callback within this window, this file by this cut-off, this settlement line within this many business days. A scheduler checks expectations as their deadlines pass and raises an exception for each one that is unmet, such as a missing file, a missing callback or a status that has not moved.
The ledger and positions
Keep a double-entry ledger separate from reconciliation. The ledger records the financial effect of every event: money owed to a provider, money held in a prefunded balance, fees, FX gains and losses, amounts in transit. Reconciliation then checks the ledger against the outside world.
- Each prefunded provider balance, settlement account and bank account is a position in the ledger.
- At each cut-off, the ledger position is compared with the counterparty’s statement for the same account and time.
- A difference that no individual payment explains is an exception on the position itself, so an unexplained gap cannot hide inside a total.
Exceptions as first-class work
An exception needs a type, an owner, a due time, the evidence that raised it and a resolution that is itself recorded. Typed exceptions make breaks countable and routable: a missing file goes to operations, an FX difference to treasury, an unknown outcome to whoever owns that provider.
| Type | Raised when |
|---|---|
| Missing callback | A provider accepted the payout, but no callback or status change arrived in its window. |
| API and file disagree | The API says paid; the settlement file leaves the payment out or shows another amount. |
| Settlement mismatch | A payment is claimed paid with no settlement evidence, or totals do not agree. |
| Reversal after success | A payment reported as paid is later returned or reversed. |
| Fee or FX mismatch | The applied fee or rate differs from the booked quote beyond tolerance. |
| Orphan | Evidence with no matching attempt, or an attempt with no evidence. |
| Duplicate | Two provider references or two settlement lines for one attempt. |
| File missing | A declared file did not arrive by its cut-off. |
| Position unexplained | A ledger position has no corroborating evidence. |
Measure exceptions by count, value and age. A high automatic match rate says little if the remaining breaks are large or old.
Idempotency, concurrency and scale
- Partition by account and business date. Most matching is local to one provider account and one day, which makes it easy to run in parallel and re-run in isolation.
- Match incrementally. New evidence triggers matching for the payments it could affect, instead of re-running whole days on every file.
- Index the keys you match on. Client reference, provider reference, rail reference, and amount with currency and date.
- Guard state changes. Use optimistic concurrency on payments and exceptions, so two workers that process a webhook and a file at the same moment cannot both close a payment.
- Make replays safe. Because ingestion and matching are idempotent, you can replay a day of evidence after a bug fix and compare the results.
- Archive raw evidence, never delete it early. Retention follows your regulatory and contractual obligations.
The API side matters too. POST is not idempotent by definition, so a payment API needs an idempotency key per logical payment, reused on every retry with an identical body.
How Flominzo Recon applies this design
Flominzo Recon is built on this model. Raw files, callbacks and responses are stored with a hash and their provenance before they are interpreted. Evidence is graded INTERNAL, CLAIM, SETTLEMENT or CASH, and a disagreement between grades becomes an exception rather than being discarded. Matching rules, tolerances and expectations are versioned, so every result can be explained by the rule that produced it, and AI agents investigate and explain exceptions without ever deciding a match. Recon can start read-only alongside your existing systems.
That is what Flominzo means by 100% reconciliation: every payment is matched or explained, with evidence: it ends either matched against independent evidence - the provider’s records, the settlement file and the bank statement - or as an open exception with an owner and a reason. None is silently assumed paid.
Questions
Should reconciliation run in batch or in real time?
Both. Matching should react to each new piece of evidence as it arrives, while expectations and daily closure run on the schedule of the files and cut-offs they depend on. Real-time matching without scheduled expectation checks misses absences.
Do I need a ledger to reconcile payments?
You can match payments without one, but you cannot reconcile balances. Prefunded provider balances, settlement accounts and amounts in transit need a ledger position to compare with each counterparty statement.
Can AI do the matching?
It should not decide matches. Deterministic rules give the same answer every time and can be audited. AI is useful for investigating exceptions, proposing a likely cause and drafting an explanation for a person to review.
How do I handle a corrected settlement file?
Store it as a new version that supersedes the old one, re-run matching for the payments it covers, and record which results changed. Closed payments that the correction affects are reopened, not silently edited.
Sources
Let’s make it specific to you.
Bring your systems, payment flows, and questions. We’ll help define the next step.
Talk to the team