Payment Reconciliation System Design: Build It Right

Every payment platform eventually ships a reconciliation system. Most of them lie — not through malice, but through omission. A record shows "settled" because the ledger entry exists, not because the bank confirmed the funds moved, the counterparty acknowledged receipt, and the amount matches to the cent. That gap between apparent reconciliation and actual reconciliation is where eight-figure write-offs are born.

This post is a technical and operational blueprint for building a payment reconciliation system that enforces truth at every layer: the three-way match, the exception lifecycle, and the SLA commitments that determine whether your finance team trusts the dashboard or ignores it. We'll use exact schema patterns, real failure modes, and the numbers that matter in production at scale.

Why "Reconciled" Is the Most Abused State in Fintech

The word "reconciled" appears in ledger systems, bank portals, and Slack alerts — and it means something different in each place. A payment processor might mark a transaction reconciled the moment a settlement file arrives. Your internal ledger might reconcile when a journal entry posts. Your bank might consider funds settled two business days after the ACH origination date.

The Federal Reserve's Fedwire Funds Service settles in real time and is irrevocable. ACH, by contrast, operates on a T+1 or T+2 cycle with a return window that extends to R01 returns up to two banking days post-settlement. SWIFT gpi messages carry a tracker UETR, but confirmation of credit to the beneficiary account is a distinct event from debit of the sending bank. If your reconciliation system treats all three of these as equivalent "settled" states, you are building on sand.

The fix is definitional precision before you write a single line of code. Define reconciled state contractually — internally — as: the condition in which all three legs of a money movement are confirmed, amounts agree within tolerance, and no open exception exists.

The Three-Way Match: The Only Model Worth Building

Three-way match in payment reconciliation borrows from procurement accounting. In procurement, you match the purchase order, the goods receipt, and the supplier invoice. In money movement, you match:

1. The instruction record — what your system intended to send (amount, currency, beneficiary, payment rail, timestamp)
2. The processor or network confirmation — what the rail actually processed (settlement file, SWIFT MT910, ACH return code, card network clearing record)
3. The bank statement or cash position — what your bank of record actually shows as a debit or credit

Only when all three legs are present and agree — to the cent, and within a defined timestamp window — does a transaction earn reconciled status. Any other state is an exception.

This sounds obvious. It is rarely implemented correctly because teams build the match logic incrementally: first leg one and two, then "we'll add the bank statement feed later." Later never arrives before the first audit.

Schema Design for Three-Way Match

A minimal but production-grade reconciliation record needs at minimum these fields:

• payment_id — your internal UUID, immutable
• instruction_amount_minor — amount in minor currency units (cents, pence), from the originating instruction
• instruction_currency — ISO 4217
• instruction_ts — RFC 3339 timestamp of instruction creation
• processor_confirmation_id — external reference from rail or processor
• processor_settled_amount_minor — what the processor confirmed as settled
• processor_settled_ts — timestamp from processor confirmation
• bank_statement_amount_minor — amount per bank statement line
• bank_statement_date — value date per bank record
• bank_statement_reference — end-to-end or transaction reference
• reconciliation_status — enum: PENDING, MATCHED, EXCEPTION, MANUALLY_RESOLVED
• match_delta_minor — signed integer: processor confirmed minus instruction; zero on a clean match
• exception_bucket — nullable enum, populated when status is EXCEPTION
• resolved_by — nullable, operator ID or automated rule ID
• resolved_ts — nullable RFC 3339

The match_delta_minor field is non-negotiable. Without it, a $10,000.01 settlement against a $10,000.00 instruction silently matches in systems that cast to float. Minor units with integer arithmetic eliminate this class of bug entirely.

Exception Buckets: Naming the Ways Things Break

An exception is not an error. It is an open question about money. Every exception needs a bucket — a typed category that determines the resolution workflow, the escalation SLA, and the financial treatment. Generic "reconciliation exceptions" that land in a single queue are unworkable at volume.

Here are the six exception buckets that cover the vast majority of real-world settlement reconciliation failures:

• AMOUNT_MISMATCH — legs exist but amounts differ beyond tolerance. Common cause: FX conversion at a rate that differs from your rate lock, or processor fee netting that wasn't anticipated.
• MISSING_PROCESSOR_LEG — instruction was sent, no confirmation received within the expected settlement window. May indicate a reject, a stuck transaction, or a feed outage.
• MISSING_BANK_LEG — processor confirmed settlement, but the bank statement shows no corresponding entry. Rarer with Fedwire; more common with cross-border wires where correspondent banks hold funds.
• DUPLICATE — two or more records match to the same processor confirmation ID or bank reference. Classic symptom of a retry storm or an idempotency failure upstream.
• TIMING_BREACH — all amounts match, but the value date or settlement timestamp falls outside the contractual window. Often a compliance or SLA issue rather than a cash issue.
• UNMATCHED_CREDIT — a credit appears on the bank statement with no corresponding instruction in your system. Could be a customer refund hitting the wrong account, an inbound wire you weren't expecting, or fraud.

Engineering note: Treat UNMATCHED_CREDIT as a security event first and a reconciliation event second. An unexpected credit hitting your operating account has a non-trivial probability of being a misdirected wire, an ACH reversal, or — in rare cases — a fraudulent deposit designed to be "returned" via a different channel. Alert your fraud team in parallel with your finance team.

Each bucket maps to a different resolution playbook and a different SLA clock. An AMOUNT_MISMATCH on a domestic ACH for $12.47 has a different urgency profile than an AMOUNT_MISMATCH on a $2.3M cross-border wire.

SLA Architecture: What Your CFO Actually Tracks

Reconciliation SLAs are often designed by engineers and ignored by finance. The reason: engineers think in terms of job completion time ("the reconciliation job runs at 06:00 UTC and finishes in 14 minutes"). Finance thinks in terms of when can I trust the number.

The SLA your CFO cares about is time-to-reconciled-state (TTRS): the elapsed time from a payment instruction being created to that payment appearing in MATCHED status with zero open exceptions. Secondary metrics:

1. Exception rate — exceptions as a percentage of total payment volume. A healthy system operating domestic ACH should target below 0.3%. Cross-border payments will run higher; 1.5–2.5% is typical before exception automation kicks in (per operational benchmarks published in the SWIFT gpi tracker documentation and corroborated by payments operations teams at Tier 1 banks).
2. Auto-resolution rate — the percentage of exceptions your system closes without human intervention. Target 80%+ for TIMING_BREACH and DUPLICATE buckets; AMOUNT_MISMATCH above $10,000 should almost always require human sign-off.
3. Exception aging — the distribution of open exception age. Any exception older than two business days should generate an escalation. Any exception older than five business days is a potential regulatory disclosure depending on jurisdiction.
4. False positive rate — exceptions your system raises that resolve to MATCHED upon investigation. High false positive rates destroy trust faster than genuine exceptions, because operators start ignoring the queue.

Publish these four metrics on a live dashboard visible to engineering, finance, and operations simultaneously. The act of shared visibility alone reduces TTRS by forcing cross-functional triage rather than queue handoffs.

Feed Architecture and the Failure Modes You Will Hit

A reconciliation system is only as good as its data feeds. The three legs of the match each arrive via a different integration point, on a different schedule, with different reliability characteristics.

Bank statement feeds via SFTP (the MT940/camt.053 file formats) are delivered once or twice daily by most commercial banks. Real-time account reporting via SWIFT MX (ISO 20022 camt.052 intraday statements) is available from banks that support it — as of Q1 2027, roughly 60% of G10 currency correspondent banks support intraday camt.052 per ECB TARGET2 migration statistics. If your bank offers it, use it. The difference between daily and intraday reconciliation is the difference between knowing you have a cash discrepancy at 18:00 and knowing at 09:45.

Processor settlement files arrive on their own schedule and format. Stripe delivers per-payout CSV files accessible via API. Adyen delivers settlement detail reports in their reporting API. Card networks (Visa, Mastercard) deliver interchange and settlement files in proprietary formats that require parsers per BIN range and region. Budget for parser maintenance — format changes ship with little warning.

The failure modes that will actually affect you in production:

• Feed delay — the bank file arrives four hours late. Your reconciliation job completes with 40% of transactions in PENDING state. Decision: do you publish a partial reconciliation run or hold until the feed arrives? Answer: publish partial with a feed-status indicator, never suppress the run.
• Duplicate feed delivery — SFTP drops the same file twice. Without idempotent ingestion keyed on file hash and statement date, you double-count every transaction. Implement content-addressed deduplication at the ingestion layer, not the match layer.
• Amount encoding differences — one feed delivers amounts as 10000 (cents), another as 100.00 (dollars), another as 1,00000 (European decimal format). Normalize to minor units at ingestion, never at match time.
• Timezone mismatches — your instruction timestamp is UTC, the processor confirmation is US Eastern, the bank value date is local business day in Singapore. A transaction instructed at 23:45 UTC on March 31 settles on April 1 in every timezone. Your TIMING_BREACH detector needs timezone-aware business day logic, not naive date subtraction.

Operationalizing the Exception Queue

An unworked exception queue is a liability. The operational design of the queue matters as much as the technical design of the match logic.

Structure the exception queue as a prioritized worklist, not a FIFO list. Priority should be a function of: exception bucket severity weight × absolute amount × age in hours. A $500,000 MISSING_BANK_LEG that is six hours old should appear above a $34.00 TIMING_BREACH that is two days old. This sounds obvious — most queue implementations don't do it.

Every exception record should carry a suggested resolution action generated by rule at creation time. For a DUPLICATE with a matching payment_id and a processor confirmation ID that appears on a prior MATCHED record: the suggested action is "void duplicate, no cash impact." For an AMOUNT_MISMATCH where the delta equals the processor's published cross-border fee: the suggested action is "approve fee delta, update cost center allocation." Operators who see a pre-computed suggestion resolve exceptions 3–4× faster than operators staring at raw data, per internal benchmarks across payments operations teams.

Require a four-eyes approval for any manual resolution above a configurable threshold — $10,000 is a common floor. Log every resolution action to an immutable audit trail with operator ID, timestamp, and free-text justification. The CFPB's examination procedures for payment processors and the SEC's books-and-records requirements under Rule 17a-3 both require that financial firms maintain auditable records of adjustments to their books. Your exception resolution log is a books-and-records artifact.

Building Toward Continuous Reconciliation

Batch reconciliation — the nightly job that processes yesterday's transactions — was the correct architecture when data arrived in batch files. In 2027, with real-time payment rails (RTP via The Clearing House, FedNow, and their equivalents in the EU under SEPA Instant) settling in under ten seconds, batch reconciliation creates a structural lag that is operationally and sometimes legally untenable.

Continuous reconciliation — triggered on every inbound feed event rather than on a schedule — is achievable with an event-driven architecture. The pattern: every feed ingestion publishes a normalized event to an internal message bus. A reconciliation worker subscribes to these events and attempts a match against existing records in real time. Unmatched events age into PENDING state and are swept by a periodic job that escalates them to EXCEPTION once their window expires.

The sweep interval for PENDING-to-EXCEPTION promotion should map to the settlement window of the rail: 10 seconds for FedNow, 2 business days for ACH, next business day for same-day ACH. Hardcoding these as constants in a configuration file — not in application code — makes them auditable and adjustable without a deployment.

At AtlasForge Financial, the AtlasForge Financial API exposes reconciliation event webhooks and a settlement state endpoint designed for exactly this architecture — so your internal reconciliation worker can subscribe to confirmed money movement events rather than polling for settlement files. If you're building or refactoring a reconciliation system and want to see how the event schema maps to the three-way match model described here, the developer documentation walks through the payload structure end to end. For teams evaluating the full platform, the AtlasForge Financial platform overview covers how settlement reconciliation integrates with cash position reporting and ledger posting in a single API surface — which eliminates the feed-normalization layer entirely for transactions processed through us.

Building a reconciliation system that doesn't lie takes longer than building one that merely appears to work. The difference shows up the first time your CFO asks why the dashboard says you have $4.2M in settled funds and the bank says $3.9M. At that point, the architecture is already written — the only question is whether it was written with enough precision to answer the question in minutes rather than days.

Further reading

• Webhook Scaling for Fintech: Reliable Delivery at 10M/day
• Background Jobs in Fintech: Sidekiq vs Temporal 2027