Building a payment reconciliation engine that catches every penny

The first time our daily reconciliation report showed a discrepancy, it was seven cents. Seven cents across 340 transactions. My co-founder asked if we could just ignore it. I said no. In fintech, seven cents today is seven thousand dollars tomorrow. If your reconciliation engine cannot explain every penny, you do not have a reconciliation engine. You have a hope-based accounting system.

I spent three weeks building a three-way reconciliation system that matches bank statements, payment processor webhooks, and our internal ledger to the cent. Six months in, it has caught discrepancies totaling over $14,000 that would have gone unnoticed without automated matching.

In that first stretch at FinanceOps, I was still learning how to wear the Head of Engineering title without hiding behind it. It also builds on what I learned earlier in “TypeScript 5.5 inferred type predicates changed how I write validation code.” The only credibility that mattered was whether the decision survived contact with real money, ugly edge cases, and the next person I would eventually hire. That same bias toward strict boundaries later shaped how I approached ftryos and pipeline-sdk: make correctness boring before you make the API clever.

The Three-Way Match

Every financial transaction in our system has three independent records that must agree. The bank statement shows the actual money movement. The payment processor webhook confirms the transaction status and fees. Our internal ledger records the business-level transaction with customer context. Reconciliation means proving that all three records describe the same event with the same amounts.

• Source 1 - Bank statement: Downloaded nightly via bank API. Contains settlement amount, transaction date, and a reference ID that may or may not match our internal IDs.
• Source 2 - Payment processor: Webhooks arrive in real time with transaction ID, gross amount, fees, and net amount. These are our primary source of truth for what the processor charged.
• Source 3 - Internal ledger: Our PostgreSQL records with the business context, customer ID, invoice reference, and expected amounts.

A transaction is “reconciled” when all three sources agree on the amount to the cent. A transaction is “flagged” when any source disagrees. A transaction is “missing” when it appears in one source but not the others.

The Matching Algorithm

Matching is harder than it sounds because the three sources use different identifiers, different timestamps, and sometimes different amounts due to fees and currency conversion. The matching runs in three passes.

1
// Pass 1: Exact match on processor transaction ID
2
// Matches ~92% of transactions immediately
3
const exactMatches = await db.execute(sql`
4
  SELECT l.id, p.id as processor_id, b.id as bank_id
5
  FROM ledger l
6
  JOIN processor_events p ON l.processor_tx_id = p.tx_id
7
  JOIN bank_statements b ON p.settlement_ref = b.reference
8
  WHERE l.reconciled_at IS NULL
9
    AND l.amount_cents = p.net_amount_cents
10
    AND p.net_amount_cents = b.amount_cents
11
`);
12

13
// Pass 2: Fuzzy match on amount + date range
14
// Catches transactions with mismatched reference IDs
15
// Matches ~6% of remaining transactions
16

17
// Pass 3: Manual review queue
18
// The remaining ~2% need human investigation

The first pass catches 92% of transactions through direct ID matching. These are the easy ones where all three sources have consistent reference IDs. The second pass uses amount matching within a date window to catch transactions where reference IDs were reformatted or truncated. The remaining 2% go into a manual review queue with all available context.

Handling Timing Differences

The most common source of false mismatches is timing. A payment processor confirms a transaction on Tuesday but the bank settlement does not appear until Thursday. During that gap, the transaction looks like it is missing from the bank statement. Naive reconciliation would flag this as an error.

We handle this with a settlement window. Each payment processor has a configured expected settlement delay. Stripe settles in 2 business days. Our bank wire processor settles same-day. The reconciliation engine does not flag a missing bank entry until the settlement window has passed. Once the window expires, a missing bank entry becomes a real alert that triggers investigation.

• Stripe: 2 business day settlement window. Transactions are expected to appear in bank statements within 3 calendar days.
• Bank transfers: Same-day settlement. Missing bank entries are flagged within 24 hours.
• International transfers: 5 business day window. Cross-border settlements are unpredictable and the window accounts for weekends and holidays in both countries.

The Dashboard

The reconciliation dashboard shows three numbers at the top: total reconciled, total pending within settlement window, and total flagged. Flagged transactions have a detail view that shows all three sources side by side with the specific field that disagrees highlighted.

The dashboard also tracks a metric I call the reconciliation rate: the percentage of transactions that match on the first pass without any fuzzy matching or manual intervention. Our target is 95%. We currently sit at 97.3%. Any week where the rate drops below 95% triggers a root cause investigation because it usually means a payment processor changed their webhook format or a bank API started returning data differently.

Every unreconciled penny has a status, an age, and an assigned owner. Nothing falls through the cracks because the system will not let it. Every morning at 8am, an automated report lists every transaction that has been unreconciled for more than its settlement window. That report goes to me and our finance advisor. It has never been empty for more than three consecutive days.

That was the pattern of my first months at FinanceOps: I did not have management scar tissue yet, so I earned trust by making technical decisions that stayed boring under pressure. The same bias toward strict defaults still shows up in ftryos and pipeline-sdk today.

If you are building fintech and your reconciliation process is a person opening spreadsheets, you are one vacation day away from losing money you cannot explain. Automate it. Match to the penny. Alert on every discrepancy. The seven cents you ignore today will be seven thousand dollars next quarter.