Every payments company eventually runs into the same uncomfortable truth: the money you think you have and the money you actually have are two different numbers, and the gap between them is where your business lives or dies. Reconciliation is the discipline of closing that gap. It is unglamorous, it rarely shows up on a product roadmap, and it is the single most important piece of infrastructure I have built in my career.
I want to walk through how I think about designing a reconciliation engine from the ground up. Not the marketing version, but the real one: the data model, the matching logic, the failure modes, and the operational discipline that keeps it trustworthy. If you operate in a regulated environment, the engine is not a convenience. It is the thing your auditors, your banking partners, and your finance team will judge you by.
What Reconciliation Actually Means
Reconciliation, at its core, is the act of proving that two independent records of the same economic event agree. You have an internal ledger that says a customer was charged forty-two pounds. Your acquirer sends a settlement file that says forty-two pounds was captured and, after fees, thirty-nine pounds and change was deposited into your account. Reconciliation is the process that ties those two facts together, accounts for the fee, and flags anything that does not line up.
The naive version of this is a spreadsheet that finance runs at month end. That works until you process more than a few hundred transactions a day, at which point the manual approach quietly starts hiding problems. Duplicate captures, missing payouts, fee miscalculations, and timing differences accumulate in the gaps that no human has time to inspect. The discipline degrades into a ritual that produces a number rather than a guarantee.
An engine replaces the ritual with a system. It ingests records from every source, normalises them into a common shape, matches them against each other under explicit rules, and produces a continuously updated picture of what is reconciled, what is not, and why. The goal is not to eliminate exceptions. Exceptions are inevitable. The goal is to make every exception visible, categorised, and assigned to someone who can resolve it.
The Sources of Truth Problem
The first design decision is the hardest one to get right, and it is conceptual rather than technical. You must decide which records are authoritative for which facts, because no single source knows everything. Your application knows the customer's intent. Your processor knows what was actually authorised and captured at the network. Your bank knows what money physically moved and when. These three views overlap, but they disagree constantly, and the disagreements are meaningful.
I treat the bank statement as authoritative for cash movement, the processor settlement file as authoritative for transaction-level fees and capture status, and our own ledger as authoritative for customer-facing intent and obligations. When they conflict, the rule for whose version wins is encoded explicitly, never left to whoever happens to be looking at the screen that day. Ambiguity here is how teams end up arguing about a discrepancy for a week instead of resolving it in an hour.
A reconciliation engine is only as trustworthy as its weakest data source. If you cannot trace a single penny back to a specific row in a specific file received at a specific time, you do not have reconciliation. You have a guess with good production values.
Designing the Data Model
The data model is where most reconciliation projects succeed or fail, and the temptation is always to start matching before the model is sound. Resist that. I model every inbound record as an immutable event that lands in a staging table exactly as it arrived, with the raw payload preserved alongside the parsed fields. Immutability matters because reconciliation is fundamentally a historical claim, and you cannot make historical claims against data you have edited in place.
On top of the raw events sits a normalised transaction record with a canonical set of fields: amount in minor units, currency, value date, external reference, source system, and a status. Everything from every source maps into this shape. The matching layer then operates on normalised records and produces match records that link two or more normalised rows together with a match type and a confidence indicator.
CREATE TABLE recon_normalised (
id BIGINT IDENTITY PRIMARY KEY,
source_system VARCHAR(32) NOT NULL,
external_ref VARCHAR(128) NOT NULL,
amount_minor BIGINT NOT NULL,
currency CHAR(3) NOT NULL,
value_date DATE NOT NULL,
raw_event_id BIGINT NOT NULL,
match_status VARCHAR(16) NOT NULL DEFAULT 'UNMATCHED',
CONSTRAINT uq_source_ref UNIQUE (source_system, external_ref)
);
CREATE TABLE recon_match (
id BIGINT IDENTITY PRIMARY KEY,
left_id BIGINT NOT NULL REFERENCES recon_normalised(id),
right_id BIGINT NOT NULL REFERENCES recon_normalised(id),
match_type VARCHAR(24) NOT NULL, -- EXACT, FUZZY, MANUAL
matched_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
matched_by VARCHAR(64) NULL -- operator id when MANUAL
);The Matching Engine Itself
Matching is best understood as a series of progressively looser passes, each one operating only on the records that earlier passes could not resolve. The first pass is exact: same external reference, same amount, same currency, value dates within an agreed tolerance. In a healthy system this pass clears the overwhelming majority of volume, and that is exactly what you want, because exact matches require no human judgement and carry no risk of incorrect pairing.
Subsequent passes relax constraints deliberately and one at a time. A second pass might match on amount and date when references differ, which happens when a processor truncates or reformats your identifier. A third pass might handle aggregated payouts, where the bank deposits one sum representing a batch of individual transactions. Each looser pass carries more risk, so each one records why it matched and, critically, never overwrites a result from a stricter pass.
I keep the matching strategies as discrete, individually testable units rather than one sprawling procedure. In C# that tends to look like a small interface implemented once per strategy, with the engine running them in order:
public interface IMatchStrategy
{
string Name { get; }
IReadOnlyList<MatchResult> Match(
IReadOnlyList<NormalisedRecord> left,
IReadOnlyList<NormalisedRecord> right);
}
public sealed class ExactReferenceStrategy : IMatchStrategy
{
public string Name => "EXACT";
public IReadOnlyList<MatchResult> Match(
IReadOnlyList<NormalisedRecord> left,
IReadOnlyList<NormalisedRecord> right)
{
var index = right.ToLookup(r => (r.ExternalRef, r.AmountMinor, r.Currency));
var results = new List<MatchResult>();
foreach (var l in left)
{
var candidate = index[(l.ExternalRef, l.AmountMinor, l.Currency)]
.FirstOrDefault(r => Math.Abs((r.ValueDate - l.ValueDate).Days) <= 1);
if (candidate is not null)
results.Add(new MatchResult(l.Id, candidate.Id, Name));
}
return results;
}
}Handling Timing and Fees
Two real-world frictions break naive matching more than anything else: timing and fees. Timing differences arise because the events you reconcile do not happen at the same moment. A card is captured on Monday, the processor batches it overnight, and the bank settles it on Wednesday, net of a weekend. If your engine demands same-day matching, it will flag thousands of perfectly healthy transactions as exceptions, and your team will learn to ignore the exception queue entirely. That is the worst outcome, because a queue everyone ignores is worse than no queue at all.
The answer is to model expected timing windows per payment method and treat anything inside the window as reconciled-pending rather than unmatched. Only when a transaction ages past its window without a counterpart does it escalate to a genuine exception. Fees require a parallel treatment: you cannot expect the gross amount you captured to equal the net amount the bank deposits, so the engine must compute the expected fee from the processor's published schedule and reconcile against the net.
- Capture date and settlement date will differ; reconcile on value date with a method-specific tolerance window.
- Gross capture and net deposit will differ by fees; reconcile net amounts, not gross.
- Refunds and chargebacks flow backwards and must be matched to their original transaction, not treated as new payments.
- Currency conversion introduces rounding that accumulates; reconcile in minor units and account for the rounding line explicitly.
- Batch payouts collapse many transactions into one bank line; the engine must support many-to-one matching.
The Exception Workflow
Whatever the engine cannot match automatically becomes an exception, and the quality of your exception workflow determines whether the engine is an asset or a liability. An exception is not just an error log entry. It is a unit of work that needs an owner, a category, an age, and a resolution. I treat the exception queue as a first-class product surface, with the same care I would give a customer-facing feature, because the finance team lives in it every day.
Categorisation is the lever that makes the queue manageable. A missing payout is a different problem from a duplicate capture, which is different again from a fee mismatch. By classifying exceptions at the moment they are created, you let the team triage by type and spot systemic issues quickly. When forty fee-mismatch exceptions appear on the same day, that is not forty problems. That is one problem, probably a processor fee schedule change you were not told about, and the categorisation surfaces it instantly.
Every manual resolution must be recorded with who did it, when, and why, and that record must be immutable. This is partly for audit and partly because manual resolutions are training data. If operators repeatedly resolve a particular pattern the same way, that pattern is a candidate for a new automated matching rule, and you should be feeding those observations back into the engine deliberately rather than letting institutional knowledge live only in people's heads.
Building for Auditability
In a regulated environment, being correct is not enough. You must be able to demonstrate that you were correct, after the fact, to someone who does not trust you by default. That demand shapes the engine more than any performance consideration. Every record carries its provenance, every match carries its reasoning, and every state change is appended rather than overwritten. When an auditor asks how a figure on a regulatory return was derived, the honest answer should be a query, not a meeting.
I learned to design the engine so that any reconciled balance at any point in history can be reconstructed from the raw events alone. If a number cannot be rebuilt from first principles, it cannot be defended, and a number you cannot defend is a liability waiting for an inconvenient moment. This is also why I avoid clever in-place updates that lose information. Storage is cheap; a forensic gap during an examination is not.
The question an auditor really asks is not "is this number right" but "how would you know if it were wrong". A reconciliation engine that cannot answer the second question has not earned the right to assert the first.
Operational Discipline
The engine is software, but reconciliation is an operational discipline, and the software only works inside a healthy operating rhythm. I run reconciliation continuously rather than at month end, because daily reconciliation catches a problem when it is one day old and one transaction wide, while monthly reconciliation catches the same problem when it is thirty days old and tangled with three weeks of unrelated activity. The cost of investigation rises with age, and continuous running keeps that cost low.
Just as important is monitoring the engine's own health. I track the match rate per source, the age distribution of open exceptions, and the total value sitting in the unmatched state. A sudden drop in match rate usually means an upstream file format changed, and I want to know that within minutes, not at month end. These operational signals are as important as the reconciliation output itself, because an engine that silently stops working is more dangerous than one that loudly fails.
Conclusion
A reconciliation engine is not a feature you ship once and forget. It is a living system that grows alongside your payment flows, your partners, and your regulatory obligations. The work is rarely visible from the outside, but it is the foundation on which every other claim about your business rests. When you tell a customer their money is safe, when you tell a regulator your books are accurate, when you tell your board what your revenue actually is, you are leaning on this engine whether you acknowledge it or not. Build it with the seriousness that deserves, invest in the data model and the exception workflow before the clever matching, and treat auditability as a first-class requirement rather than an afterthought. Do that, and the gap between the money you think you have and the money you actually have stays small, visible, and explainable. That is the whole job.