
Payment Reconciliation System Design: Data Model to Matching Engine
Most teams start reconciliation with a spreadsheet and a script, and both break the first month volume doubles. This guide covers payment reconciliation system design from an engineer's point of view: the data model, how to ingest from processors and banks without duplicates, how to build a matching engine you can trust, and how to handle the failures that make reconciliation hard. If you need the operational process first (what to match, in what order, and the finance checklist), read our 7-step guide to automated payment reconciliation. This article is about the software underneath it.
What the system actually has to prove
A reconciliation system answers one question with evidence: for every unit of money that should have moved, did it move, in the right amount, to the right place, and can we show why we believe that?
In practice that means linking three independent sources of truth:
- Your ledger: orders, invoices, refunds and credits your application created.
- The processor: charges, fees, refunds, disputes and the payouts that bundle them.
- The bank: deposits and withdrawals as they actually landed on the statement.
None of these sources share a primary key end to end. The order ID lives in your database and maybe in processor metadata; the payout ID lives at the processor and maybe in the bank deposit description; the bank line has an amount, a date and a free-text reference. The system's job is to build and keep the links between them, and to make every link explainable.
Payment reconciliation system design: the data model
The single most important decision is to separate what a source said from what you concluded about it. Raw records are immutable facts; matches are conclusions that can be revised.
-- 1. Raw, immutable records exactly as received
CREATE TABLE source_record (
id BIGSERIAL PRIMARY KEY,
source TEXT NOT NULL, -- 'ledger' | 'stripe' | 'bank_x'
external_id TEXT NOT NULL, -- provider's own ID or a stable hash
payload JSONB NOT NULL, -- original data, never edited
ingested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (source, external_id)
);
-- 2. Normalized view used by the matcher
CREATE TABLE txn (
id BIGSERIAL PRIMARY KEY,
source_record_id BIGINT NOT NULL REFERENCES source_record(id),
kind TEXT NOT NULL, -- charge | fee | refund | dispute | payout | deposit
amount_minor BIGINT NOT NULL, -- signed, in minor units (cents)
currency CHAR(3) NOT NULL,
occurred_on DATE NOT NULL,
reference TEXT, -- order id, payout id, bank reference
group_ref TEXT -- e.g. payout id that bundles this line
);
-- 3. Conclusions: a match group links N txns that must net to zero
CREATE TABLE match_group (
id BIGSERIAL PRIMARY KEY,
rule TEXT NOT NULL, -- which rule or person created it
status TEXT NOT NULL, -- active | reversed
created_by TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE match_line (
match_group_id BIGINT REFERENCES match_group(id),
txn_id BIGINT REFERENCES txn(id),
PRIMARY KEY (match_group_id, txn_id)
);
A few choices here are deliberate:
- Integer minor units plus a currency code. Floating point will eventually produce a one-cent difference that nobody can explain. Store amounts as signed integers and carry the currency on every row. Be aware that not every currency has two decimal places, so keep the exponent in a currency table rather than hard-coding a divide by 100.
- Signed amounts. Money in is positive, money out is negative, from the point of view of one account. A match group is valid when its lines sum to zero (or to an explicitly recorded, approved difference). That single invariant replaces a lot of special-case code.
- Many-to-many matches. One bank deposit usually corresponds to one payout, which corresponds to hundreds of charges minus fees and refunds. One order can also be paid in two installments. A
match_groupwith N lines handles all of these shapes; amatched_to_idcolumn on the transaction table handles none of them well. - Reversible conclusions. Unmatching never deletes rows. You mark the group
reversedand create a new one. Auditors and your future self will ask why something was matched on a given date.
Ingestion: webhooks for speed, pulls for truth
Webhooks are great for near-real-time status, but they are a notification channel, not a complete record. Events can arrive late, twice or out of order, and you will have outages on your side. A robust design uses both:
- Webhooks update operational state (order paid, refund issued) and enqueue a fetch.
- Scheduled pulls of the processor's settlement data are the reconciliation source of truth. On Stripe, for example, settlement data is exposed through the Balance Transactions API, and the docs show how to retrieve balance transactions and filter them by type.
- Bank statements arrive as files or through a bank API, on the bank's schedule, not yours.
Every ingestion path writes through the same idempotent upsert keyed on (source, external_id):
INSERT INTO source_record (source, external_id, payload)
VALUES ($1, $2, $3)
ON CONFLICT (source, external_id) DO NOTHING
RETURNING id;
Bank lines are the hard case because many banks do not give you a stable line ID. Derive one from fields that do not change between exports (account, booking date, amount, reference, and a per-day sequence number), and keep the original file so you can re-derive it if the bank changes its format.
The same discipline applies to calls you make outward, like issuing refunds. Stripe's idempotency documentation explains that it saves the status code and body of the first request for a given key and that keys can be removed once they are at least 24 hours old. That makes the key a retry guard for a single request, not a business-level duplicate check, so your own database still needs a unique constraint on "refund for order X, attempt Y".
The matching engine
Run rules from most certain to least certain, and stop considering a transaction as soon as it is matched. A typical tier structure:
- Exact reference match. Your order ID stored in processor metadata equals the ledger order ID, same amount and currency. Fully automatic.
- Settlement roll-up. All processor lines with the same
group_ref(the payout) sum to the payout amount, and the payout amount equals a bank deposit within a date window. Fully automatic when the sums are exact. - Candidate match. Same amount, same currency, date within N days, reference similarity above a threshold. Proposed, not committed, unless the rule has a proven track record.
- Exception. Anything left goes to a queue for a human.
def reconcile_payout(payout, bank_lines, processor_lines):
lines = [l for l in processor_lines if l.group_ref == payout.external_id]
if sum(l.amount_minor for l in lines) != payout.amount_minor:
return exception("payout_lines_do_not_sum", payout, lines)
candidates = [
b for b in bank_lines
if b.currency == payout.currency
and b.amount_minor == payout.amount_minor
and abs((b.occurred_on - payout.arrival_date).days) <= DATE_WINDOW
and not b.is_matched
]
if len(candidates) == 1:
return commit_group(rule="payout_rollup", txns=[payout, *lines, candidates[0]])
if len(candidates) > 1:
return exception("ambiguous_bank_line", payout, candidates)
return pending(payout) # bank line may not have arrived yet
Notice three outcomes besides success: a real exception, an ambiguity, and "not yet". Treating "not yet" as an error floods the queue every morning; treating it as fine forever hides missing money. Give pending items an age and escalate them after a threshold you agree with finance.
Deterministic rules vs. scoring
A scoring model (weighted similarity on amount, date and reference) catches more matches, but it is harder to explain and to audit. Deterministic rules are boring and explainable. A practical compromise: deterministic rules auto-commit, scoring only proposes, and a person accepts or rejects. Log every acceptance; after enough consistent decisions you have evidence to promote a pattern into a deterministic rule.
Architecture: batch, event-driven or hybrid
- Nightly batch. Simple, easy to rerun, and matches how banks deliver statements. The downside is that problems surface a day late.
- Event-driven. Each new record triggers matching for its candidates. Faster feedback, but you must handle concurrency: two workers can try to claim the same bank line. Use row locks (
SELECT ... FOR UPDATE SKIP LOCKED) or a unique constraint so a transaction can belong to only one active group. - Hybrid (usually the right answer). Event-driven for tier 1 matches that only need ledger and processor data, plus a scheduled run when bank data lands, plus a full rerun command for any date range.
Whatever you choose, make the matcher a pure function of the stored data: same inputs, same outputs. That is what lets you replay a month after fixing a rule bug and diff the results before committing them.
Failure handling: the cases that cause most of the work
- Late and reordered data. A refund can arrive after the payout that included it. Never assume the payout is final until its lines sum correctly.
- Partial refunds and disputes. Model them as separate negative transactions linked to the original charge, not as edits to the charge.
- Currency conversion. If the processor settles in a different currency than the charge, store both amounts and the rate that the processor reported. Do not recompute it with your own rate.
- Bank format changes. Version your parsers and keep raw files. When a parser changes, reprocess from raw and compare.
- Manual fixes. A person matching two items is a match group with
created_byset to that person and a required note. Consider requiring a second approver for groups that carry a non-zero difference.
Performance
Matching is mostly a search problem. Avoid comparing every unmatched transaction with every other one:
- Block candidates by currency, amount and a date window before applying any fuzzy logic. An index on
(currency, amount_minor, occurred_on)filtered to unmatched rows does most of the work. - Partition by date once tables grow, since nearly every query is bounded by a date range.
- Batch writes inside a transaction per match group, so a crash never leaves half a group committed.
Security and audit
- Use the narrowest API credentials each processor allows, read-only wherever reconciliation does not need to write.
- Never store card numbers; processor IDs are enough to reconcile.
- Encrypt raw bank files at rest and restrict who can download them.
- Keep an append-only audit log of rule changes, manual matches and reversals.
Common mistakes
- Using floats or decimals with inconsistent scale for money.
- Storing one
matched_tocolumn, which cannot represent payouts or installments. - Treating webhooks as a complete record of what happened.
- Deleting or editing raw records to "fix" a mismatch.
- Auto-committing fuzzy matches with no human review or log.
- Having no "pending" state, so normal settlement delays look like errors.
Implementation checklist
- Raw records stored immutably with a unique
(source, external_id). - Amounts as signed integers in minor units, with currency on every row.
- Many-to-many match groups that must net to zero.
- Webhooks for freshness, scheduled pulls for truth.
- Rules ordered from exact to fuzzy; fuzzy only proposes.
- Pending, ambiguous and exception states with aging.
- Replayable matcher and a rerun command for any date range.
- Audit log for every manual action and rule change.
Building this well takes careful engineering, and the payoff is a finance team that trusts the numbers. If you would rather have a team that has done it before, see how we approach custom software development for payment-heavy operations. Direct-selling companies have one more leg to reconcile, commission payouts to distributors, which is something our direct-selling platform with built-in payments and commissions is designed around.
Frequently asked questions
Should I build reconciliation or buy a tool?
Buy if your sources are standard and a tool supports your processors and banks out of the box. Build, or extend, when you have unusual flows such as marketplace splits, installments or commission payouts, or when reconciliation must feed your own product data.
Can I reconcile in real time?
Partially. Ledger-to-processor matches can be near real time through webhooks plus confirmation pulls. Bank-side matching runs at the pace your bank delivers statement data.
How do I handle a one-cent difference?
Do not hide it in a rounding rule. Record it as an explicit difference line inside the match group, with a reason and an approver, so the group still nets to zero and the difference is reportable.
Comments
No comments yet. Be the first to share what you think.


