Reconciliation Engineering

Prerequisite: The Ledger

Your ledger says you sent $42,000 to a customer. Stripe says the payout was $41,850. Your bank statement shows a debit of $41,850.15. Three systems, three numbers, zero agreement. The $150 gap is a processing fee Stripe deducted before settlement. The 15 cents is a wire fee your bank applied. Both are correct — but until your reconciliation system can explain the gaps, you have a break, and a break is an open question about where money went.

Reconciliation is not “do the books balance?” It’s “can we prove, for every entry in every system, that every other system agrees — and when they don’t, can we explain why within a bounded time?”

Three-Way Matching

A single ledger in isolation is trivially consistent — it agrees with itself. The moment money crosses a system boundary, you need proof that both sides recorded the same thing. In production, you have at least three systems that must agree.

System	What it records	What it knows
Your ledger	Intent and execution — every entry you posted	Everything, by construction
Payment processor (Stripe, Adyen, etc.)	Transaction events — charges, refunds, payouts, fees	Its view of the transaction lifecycle
Bank statement (via BAI2, MT940, ISO camt.053)	Settlement — what actually moved in/out of your account	Cash position only, no transaction context

Three-way reconciliation matches each internal ledger entry to a processor event and a bank statement line. A clean match across all three is a confirmed transaction. A match across two but not the third is a timing difference or a data gap. A match against none is a potential problem.

Ledger Entry ←→ Processor Event ←→ Bank Statement Line
    ↕                  ↕                     ↕
 reference_id    transaction_id         description + amount

The join keys between these systems are never perfectly aligned. Your ledger has an internal entry_id and a processor reference_id. The processor has a transaction_id and a payout_id. The bank statement has a free-text description and an amount. Your reconciliation engine must bridge these identifiers across all three systems.

Bank statements often arrive as flat files (BAI2 in the US, MT940 internationally) with limited structure. A single bank statement line may aggregate multiple transactions into one lump settlement. Your matching engine must handle one-to-many and many-to-one relationships, not just one-to-one.

Deterministic Matching

Start with exact matches — they’re fast, unambiguous, and should cover 85–95% of your transaction volume.

A deterministic match requires agreement on a set of fields with zero tolerance. The strongest match key is a shared reference ID: your system and the counterparty both recorded the same identifier at transaction time.

SELECT l.entry_id, p.transaction_id
FROM ledger_entries l
JOIN processor_events p
  ON l.processor_reference = p.transaction_id
  AND l.amount = p.amount
  AND l.currency = p.currency
WHERE l.reconciliation_status = 'unmatched'
  AND p.reconciliation_status = 'unmatched';

Deterministic matching runs in three passes, each progressively relaxing the match criteria:

Pass	Match keys	Typical hit rate
1: Full match	Reference ID + amount + currency + date	80–90%
2: Key + amount	Reference ID + amount + currency (date tolerance ±1 day)	5–10%
3: Amount + date	Amount + currency + date (no reference ID)	2–5%

Pass 3 is dangerous — matching on amount and date alone produces false positives when multiple transactions share the same amount on the same day. Restrict Pass 3 to amounts above a uniqueness threshold for the given date, or flag matches for manual review.

After deterministic matching completes, the remaining unmatched entries — typically 2–8% of volume — fall to fuzzy matching or the exception queue.

Fuzzy Matching

Bank statements don’t speak your language. A payout you recorded as ref:PAY-2024-08-15-001 for $12,450.00 might appear on the bank statement as STRIPE PAYOUT 081524 12450.00 or ACH CREDIT STRIPE TECHNO 12,450. Fuzzy matching bridges this gap.

Three dimensions of fuzziness:

Amount tolerance. Processor fees, FX conversions, and bank charges create small discrepancies between what you sent and what arrived. A tolerance window absorbs these. ±$0.50 for domestic transactions, ±1% for cross-border (covering FX spread). The tolerance must be configurable per corridor and payment method.

Date windowing. ACH settlement takes 1–2 business days. Wire transfers settle same-day but post to statements the next business day. Cross-border SWIFT payments take 2–5 business days. Your date window must account for the settlement cycle of the payment rail.

Payment rail	Settlement window	Date tolerance
ACH	T+1 to T+2	±2 business days
Same-day ACH	T+0 to T+1	±1 business day
Wire (Fedwire)	T+0	±1 business day
SWIFT	T+2 to T+5	±5 business days
Card settlement	T+1 to T+3	±3 business days

Description parsing. Bank statement descriptions follow no standard. Extract candidate identifiers using regex patterns tuned to each bank’s format. Stripe payouts typically include STRIPE and a date fragment. ACH credits include the originator name. Parse what you can, score the match confidence, and set a threshold below which matches route to manual review.

def fuzzy_score(ledger_entry, bank_line):
    score = 0.0
    amount_diff = abs(ledger_entry.amount - bank_line.amount)
    if amount_diff == 0:
        score += 0.5
    elif amount_diff <= tolerance_for(ledger_entry.rail):
        score += 0.3
    else:
        return 0.0  # Amount too far off — not a match

    date_diff = business_days_between(ledger_entry.expected_settle, bank_line.date)
    if date_diff <= window_for(ledger_entry.rail):
        score += 0.3

    if extract_reference(bank_line.description) in ledger_entry.references:
        score += 0.2

    return score

A score above 0.8 is an auto-match. Between 0.5 and 0.8, flag for review. Below 0.5, no match — the entry stays in the exception queue.

Break Detection and Classification

A break is any entry that cannot be matched to its counterpart after all matching passes complete. Breaks are not errors — they are questions that need answers. The classification determines the urgency and the resolution path.

Break type	Definition	Typical cause	SLA
Timing break	Entry exists in one system, expected in another within the settlement window	Normal settlement delay	Auto-resolves within window
Amount break	Matched by reference but amounts differ	Fees, FX, partial settlement	24 hours
Missing entry	Entry in external system with no internal counterpart	Failed webhook, dropped event, manual bank transaction	4 hours
Orphan entry	Entry in internal ledger with no external counterpart	Processor rejection not recorded, void not propagated	4 hours
Duplicate	Two entries match the same counterpart	Retry without idempotency, double-posting	1 hour

Timing breaks are the most common and the least urgent. An ACH payment initiated today won’t appear on the bank statement until tomorrow. Your system should track expected settlement dates per payment rail and suppress timing break alerts until the settlement window expires.

Missing entries and orphans are the dangerous ones. A missing entry means money moved externally but your ledger doesn’t know about it. An orphan means your ledger thinks money moved but the external system disagrees. Both require immediate investigation.

A duplicate break is a potential double-spend or double-credit. If two ledger entries both claim the same bank statement line, one of them is wrong. Duplicate detection must run before amount matching — catching duplicates after they’ve been auto-matched means unwinding a completed reconciliation.

Exception Queues

Automated matching handles 92–98% of volume. The remaining 2–8% enters the exception queue — a prioritized work queue where operations analysts investigate and resolve breaks.

The exception queue is not a list. It’s a workflow engine with assignment, escalation, and audit trail.

Break detected → Classify → Route by type and amount
    ↓
Assign to analyst (round-robin within tier)
    ↓
Analyst investigates → one of:
    ├── Match manually (with justification)
    ├── Create adjusting entry (approved by supervisor if > $1,000)
    ├── Flag as expected (fee, FX, timing) with evidence
    └── Escalate (fraud, system error, unknown)
    ↓
Resolution recorded → Break closed → Audit trail preserved

Routing rules determine urgency:

Amount threshold	Assignment	Escalation deadline
< $100	Junior analyst	48 hours
$100 – $10,000	Senior analyst	24 hours
$10,000 – $100,000	Team lead	8 hours
> $100,000	Head of operations + finance	2 hours

Every resolution must include a reason_code (a controlled vocabulary — fee_deduction, fx_difference, timing, system_error, manual_adjustment, fraud_suspected) and a free-text justification. The reason code drives reporting. The justification satisfies auditors.

Breaks that age past their SLA generate alerts. Breaks that age past 2x their SLA escalate automatically. A break older than 30 days without resolution is a regulatory finding waiting to happen — most state money transmitter licenses require reconciliation within specific timeframes, typically 1–3 business days for customer funds.

Nostro/Vostro Reconciliation

Correspondent banking adds a layer. Your nostro account — the account you hold at another bank — has a balance you expect and a balance the correspondent reports. These must agree.

The expected balance is derived from your internal ledger: the sum of all entries booked to the nostro account. The actual balance comes from the correspondent’s statement (typically MT950 for intraday, MT940 for end-of-day, or camt.053 for ISO 20022 reporters).

Expected position = Opening balance (agreed)
                  + Debits you initiated
                  - Credits you initiated
                  ± Items in transit

Actual position   = Correspondent's reported balance

Break = Expected - Actual

Items in transit are the primary source of nostro breaks. You sent a SWIFT payment at 4:30 PM; the correspondent processes it at 9:00 AM the next day in their timezone. For that overnight window, your expected position includes the debit but the correspondent’s balance does not. This is a legitimate timing break — but you must track it explicitly to distinguish “payment in transit” from “payment lost.”

Nostro reconciliation operates on positions, not individual transactions. The goal is not to match every entry one-to-one (though large-value items should be matched individually). The goal is to explain the position difference — the gap between what you expect the balance to be and what the correspondent says it is. Every component of that gap must have a name: fees, FX revaluation, items in transit, rejected items, or a genuine discrepancy.

See The Ledger. A nostro account is just another account in your ledger. The reconciliation question is the same — does the derived balance match reality? The difference is that “reality” comes from an external correspondent, not from your own database.

Continuous vs Batch Reconciliation

Nightly batch reconciliation is a delayed alarm. If a payment fails at 9:00 AM and reconciliation runs at midnight, you have a 15-hour window where your ledger is wrong and nobody knows. For a customer-facing fintech processing real-time payments, that window is unacceptable.

Continuous reconciliation processes matches as events arrive, not at the end of the day.

Approach	Latency to detect break	Throughput	Operational cost
Nightly batch	Up to 24 hours	High (bulk SQL)	Low — runs once
Hourly micro-batch	Up to 1 hour	High	Low-moderate
Streaming (event-driven)	Seconds to minutes	Moderate per-event	Higher — always running

The streaming approach uses change data capture (CDC) or webhook events as triggers. When a processor event arrives, the reconciliation engine immediately attempts to match it against unmatched ledger entries. When a bank statement file lands, it’s parsed and matched within minutes.

Processor webhook → Parse event → Attempt match against ledger
    ├── Match found → Mark both sides reconciled
    └── No match → Start settlement timer
                    └── Timer expires → Break created → Exception queue

The settlement timer is critical. A processor event with no ledger match is not immediately a break — the ledger entry might post seconds later due to processing order. The timer waits for the settlement window (configurable per rail and event type) before declaring a break. Without the timer, you generate thousands of false-positive breaks from normal processing latency.

In practice, most teams land on hourly micro-batch as the right tradeoff. It detects breaks within an hour — fast enough for regulatory purposes — while maintaining the throughput advantages of bulk matching. True streaming is reserved for high-value payment corridors where a 15-minute detection window matters.

Scale: Matching Millions of Entries

At 100,000 transactions per day, deterministic matching completes in seconds. At 10 million per day, naive cross-joins between unmatched ledger entries and bank statement lines become quadratic and blow past any reasonable SLA.

Three techniques keep matching linear:

Partition by date. Entries settle within a bounded window. An ACH entry from Monday will not match a bank statement line from three weeks ago. Partition unmatched entries into daily buckets and only match within the relevant window. A 5-day window means each bank statement line compares against 5 days of ledger entries, not the entire unmatched set.

Index on match keys. Build composite indexes on (currency, amount, date) and (processor_reference). The deterministic matching passes become index lookups instead of table scans.

CREATE INDEX idx_recon_match
  ON ledger_entries (currency, amount, booking_date)
  WHERE reconciliation_status = 'unmatched';

CREATE INDEX idx_recon_ref
  ON ledger_entries (processor_reference)
  WHERE reconciliation_status = 'unmatched';

Partial indexes on reconciliation_status = 'unmatched' keep the index small. As entries are reconciled, they leave the index automatically. A system processing 10 million entries per day with a 98% auto-match rate maintains a working set of roughly 200,000 unmatched entries — well within single-node Postgres capability.

Batch by counterparty. Don’t match all unmatched entries against all bank lines. Partition by bank account and processor. Stripe entries match against Stripe payout lines. Wire entries match against wire settlement lines. Each partition runs independently and can parallelize across workers.

Daily volume	Matching strategy	Expected matching time
< 100K	Single-pass, no partitioning	< 10 seconds
100K – 1M	Date-partitioned, indexed	< 2 minutes
1M – 10M	Date + counterparty partitioned, parallel workers	< 15 minutes
> 10M	Streaming with micro-batch fallback	Continuous

The Reconciliation Report

Auditors don’t ask “is everything reconciled?” They ask “show me everything that isn’t, how long it’s been open, and what you’re doing about it.”

The reconciliation report is a daily artifact — generated automatically, reviewed by operations, archived for audit. It contains:

Summary metrics:

Metric	What it measures	Red threshold
Match rate	% of entries auto-matched	< 92%
Open break count	Unresolved breaks	> 50 over 3 days old
Open break value	Total $ of unresolved breaks	> $10,000
Aged breaks	Breaks older than SLA	Any
Mean time to resolution	Average break resolution time	> 24 hours

Break aging schedule: Every open break listed with its age, type, amount, assignment, and current status. Auditors focus on the tail — the breaks that have been open the longest. A single break open for 60 days will draw more scrutiny than 500 breaks resolved in 4 hours.

Resolution audit trail: Every closed break with its resolution reason code, the analyst who resolved it, the supervisor who approved adjustments, and the timestamp chain from detection to closure. The trail must be immutable — if an analyst changes the resolution reason, both the old and new reasons are recorded.

The report is not a dashboard. Dashboards show current state. The report is a historical record that proves your reconciliation process is functioning. When an examiner asks “how do you know your books are accurate?” the answer is the unbroken chain of daily reconciliation reports showing breaks detected, investigated, and resolved within SLA.