Payment Reconciliation System Development Guide: Architecture, Data Model, and Exception Handling

At low transaction volumes, payment reconciliation stays manageable, and using spreadsheets and manual review is a viable option. A single PSP, a predictable settlement cycle, and a small operations team are more than enough to keep the books balanced.

But, as scale and complexity grow simultaneously, issues get introduced.

A second PSP with a different settlement cycle and identifier schema, a third with weekly batch settlements instead of daily files, refunds and chargebacks arriving weeks after the originating transaction, and FX transactions where the settled amount differs from the original due to currency conversion timing are some of the most common issues we see in production.

At $10 million in monthly payment volume, even a 1% mismatch rate produces $100,000 in unexplained gaps per settlement period, all of which require manual investigation. At scale, this isn’t possible.

The system that prevents these gaps is a reconciliation architecture that is built to automate the matching of internal ledgers, payment gateways, and bank statements.

If that system is built correctly, you ensure that every authorised transaction matches its settled amount and accounts for deductions like processing fees along the way.

Let’s look at all of the different components you need to keep in mind during payment reconciliation system development, so your system stays maintainable as PSP count grows, and compliant with the audit trail requirements that regulated fintechs face.

The best way to ensure a successful development project is to ensure you have the right people on your team. At Trio, we pre-vet our developers for fintech expertise, so you can match with the right talent in as little as 3-5 days, including senior payments and ledger engineers.

View capabilities.

Why Naive Reconciliation Systems Break at Scale

Most fintech teams that we partner with build their first reconciliation system reactively.

In these rudimentary systems, a PSP may start sending daily settlement CSV files, someone writes a script that reads the file and compares each row against the internal transaction record, and exceptions get flagged for manual review.

This works until one of three things happens:

1.  PSP schema drift: This is where PSPs update their settlement file formats regularly. Column names change, new transaction types appear, and date format conventions shift between regions. Without a dedicated normalization layer, every schema change breaks the matching logic and produces a flood of false exceptions.
2. Multi-leg transaction blindness: Batch settlements, partial captures, split refunds, and chargeback reversals don't follow a one-to-one pattern. A matching engine that only handles one-to-one transaction pairs may flag entire batches as exceptions.
3. Two-way reconciliation incompleteness: Matching internal records against PSP settlement files confirms that the PSP acknowledges the transactions, but does not confirm that they arrived in the bank account. Failure can result due to banking infrastructure issues, beneficiary account problems, or fraud holds.

The Canonical Data Model

A reconciliation system's canonical data model needs to represent the complete lifecycle of a payment transaction, from initiation through authorisation, capture, settlement, and payout.

Every PSP expresses this lifecycle differently, creating complications.

The canonical data model abstracts over those differences, so the matching engine reasons over a single, consistent structure. This happens regardless of which PSP was used to process the transaction.

Monetary precision becomes incredibly important here.

amount_minor_units stores amounts as integers in minor units (pence, cents, fils). Never FLOAT or DECIMAL. All arithmetic on financial amounts needs to use integer minor unit arithmetic

If your engineers don’t have any fintech exposure, this is an incredibly common data corruption failure that you need to watch for in any payment systems they build.

Core entities:

PaymentTransaction {

  internal_transaction_id     -- your system's identifier (UUID)

  psp_id                      -- which PSP processed this

  psp_transaction_id          -- PSP's identifier (normalised from PSP-specific field)

  psp_payment_intent_id       -- for PSPs that expose intent-level identifiers (Stripe)

  amount_minor_units          -- integer in minor units (cents), NEVER FLOAT

  currency                    -- ISO 4217 currency code

  status                      -- ENUM: AUTHORISED / CAPTURED / SETTLED /

                              --       REFUNDED / DISPUTED / REVERSED

  authorised_at               -- timestamp

  captured_at                 -- timestamp (nullable)

  settled_at                  -- timestamp (nullable, set when settlement confirmed)

  psp_settled_amount          -- amount PSP reports as settled (may differ from

                              -- captured due to FX)

  psp_settled_currency        -- settlement currency (may differ from transaction currency)

  psp_fee_amount              -- fees deducted before settlement

  bank_reference              -- reference from bank statement (nullable, set when matched)

  reconciliation_status       -- ENUM: UNMATCHED / PSP_MATCHED / BANK_MATCHED /

                              --       FULLY_RECONCILED / EXCEPTION

}

SettlementFile {

  file_id

  psp_id

  settlement_period_start

  settlement_period_end

  received_at

  file_format                 -- CSV / XML / ISO20022 / API

  total_transaction_count

  total_settled_amount

  total_fees

  processing_status           -- PENDING / NORMALISED / MATCHED / CLOSED

}

BankStatement {

  statement_id

  bank_account_id

  statement_date

  opening_balance

  closing_balance

  line_items[]                -- each bank line: amount, reference, posting_date

}

ReconciliationException {

  exception_id

  transaction_id              -- nullable (some exceptions have no matched transaction)

  exception_type              -- ENUM: AMOUNT_MISMATCH / TIMING_GAP /

                              --       MISSING_PSP_RECORD / MISSING_BANK_CREDIT /

                              --       FX_VARIANCE / DUPLICATE / UNKNOWN_TRANSACTION

  exception_severity          -- ENUM: AUTO_RESOLVE / REVIEW_REQUIRED / ESCALATE

  detected_at

  resolved_at

  resolution_action           -- what was done to close it

  resolver_id                 -- who resolved it (for audit trail)

}

The reconciliation_status field on PaymentTransaction is what drives the matching engine's state machine.

For example, a transaction moves from UNMATCHED to PSP_MATCHED when the PSP settlement confirms it, then to BANK_MATCHED when the bank statement confirms the payout, then to FULLY_RECONCILED when all three layers agree.

In the event that any record is stuck at UNMATCHED or PSP_MATCHED beyond the expected settlement window, it generates an exception that requires manual review.

The Normalisation Layer: PSP-Specific Adapters

The normalisation layer, as briefly mentioned, takes any PSP's settlement file or API response and produces a row in the canonical data model.

In doing so, it allows every PSP difference, including identifier naming, date formats, fee calculation methodology, file format, and currency representation, to be absorbed before proceeding.

Why does this need to be a dedicated layer?

If normalisation logic lives inside the matching engine, every PSP schema change requires matching engine changes.

That's a change to core financial logic in order to handle a CSV column rename.

A dedicated normalisation layer means schema updates get localised. All you need to do is update the adapter for the affected PSP, run the normalisation tests, and deploy. The matching engine doesn't change.

This architectural separation also means adding a new PSP requires only a new adapter, with no changes to the matching logic that other PSPs depend on.

At Trio, we place fintech engineers with production multi-PSP reconciliation experience in 3-5, at $40-$80/hr.

Per-PSP identifier resolution.

The hardest normalisation problem is identifier resolution. Each PSP surfaces the same underlying payment event under different identifiers, and the relationships between those identifiers are non-obvious.

• Stripe: A charge produces a charge_id. That charge belongs to a payment_intent_id. The settlement appears in a balance_transaction_id. The payout that includes this balance transaction carries a payout_id. Reconciling a Stripe payout line item back to its originating charge requires traversing all four identifiers, and this hierarchy isn't documented in one place.
• Checkout.com: A payment has a payment_id. Each action (capture, refund, chargeback) carries its own action_id. Settlement reports group by payment_id, listing each action underneath.
• Adyen: Uses pspReference for the original authorisation and originalReference on modifications (captures, refunds, chargebacks) to link back to the originating transaction.
• Braintree: Uses transaction_id on each transaction and settlement_batch_id to group settlements.

With a normalisation adapter, all these become psp_transaction_id in the canonical model, with the PSP-specific raw identifier stored in a raw_identifiers JSONB column for audit traceability.

This makes the matching engine PSP-agnostic. In other words, it reasons over canonical identifiers and doesn't need to know how Stripe's identifier hierarchy differs from Adyen's.

FX normalisation

PSPs apply FX at different points in the transaction lifecycle.

Stripe, for example, does it at capture, while Adyen has elected to apply it at settlement.

For transactions in a currency that differs from the settlement currency, the normalisation layer records both amount_minor_units (original transaction currency) and psp_settled_amount plus psp_settled_currency (post-FX settlement values).

Reconciling against the bank statement requires matching on settled currency amounts rather than transaction currency amounts. This is another common misstep that is made by engineers without production fintech experience.

Timing normalisation.

Some PSPs report in UTC; others use their local timezone. Some report settlement timestamps; others report posting timestamps.

The normalisation layer converts everything to UTC. It then records both the raw timestamp and the normalised UTC value.

Cut-off time mismatches can generate temporary breaks. But, instead of flagging these instances as hard exceptions,  the matching engine needs to carry forward to the next settlement period.

The Matching Engine: Handling Multi-Leg Transactions

The matching engine handles the actual comparison logic across the canonical data model. Most naive reconciliation scripts handle only one pattern, as we have already mentioned.

However, a production engine needs to be able to handle four:

Pattern 1: One-to-one

This is the simple case, and the only one most naive systems handle quite well.

In this pattern, a single captured transaction in the internal ledger matches a single line in the PSP settlement file, which matches a single credit in the bank statement.

Pattern 2: One-to-many (batch settlement)

 A single bank credit represents a payout from a PSP aggregating hundreds of individual transactions.

Here, the matching engine needs to resolve the one-to-many relationship, or one bank statement line matched to many PSP settlement rows, each matched to their corresponding internal transaction records.

The payout only reaches FULLY_RECONCILED when all three layers agree.

The algorithm for the one-to-many pattern, like this, is to sum all PSP settlement rows for the payout period, verify that the sum matches the bank credit within fee tolerance, then individually match each PSP row to its internal transaction record.

Pattern 3:  Many-to-one (partial settlement)

A single internal transaction may appear across multiple PSP settlement batches when it was partially captured.

We commonly see this in B2B contexts where goods ship in multiple fulfilments.

The matching engine accumulates PSP rows over multiple settlement periods until the total captured amount equals the original authorisation amount.

Pattern 4: Many-to-many (refunds and chargebacks crossing settlement periods)

A refund processed in September might relate to a capture from August.

The matching engine needs to be able to maintain linkage across settlement periods, connecting downstream events back to their originating transactions.

From what we have seen, this is where most reconciliation systems that are otherwise solid accumulate unresolvable exceptions.

The matching algorithm structure for each settlement period is:

  1. Ingest and normalise all PSP settlement files

  2. Run Phase 1 matching: exact one-to-one matches by PSP transaction ID

  3. Run Phase 2 matching: batch aggregation matching (one-to-many by payout_id)

  4. Run Phase 3 matching: cross-period linkage for refunds and chargebacks

  5. Classify unmatched records as exceptions with specific exception_type

  6. Trigger three-way reconciliation for all PSP_MATCHED records

Matching tolerance for FX and fees.

Amount matching needs to accommodate legitimate variance that results from rounding in FX conversion, PSP fee calculation rounding, and timing differences in FX rate application.

A configurable tolerance, for example, ±0.01% or ±$0.05, whichever is smaller, prevents legitimate transactions from generating false exceptions while still letting you flag genuine discrepancies.

You don’t want this hardcoded in your matching logic, because you will want to adjust it for each PSP as fee structures change.

Exception Classification and Routing

An exception is any transaction that fails to complete the full matching cycle within the expected settlement window.

Since there can be such a big variance in their cases, not all exceptions warrant the same response.

Here is a summary of how each case should be handled:

Exception Type	Cause	Typical Resolution
TIMING_GAP	Authorised but not yet settled (T+1 or T+2 settlement lag)	Auto-resolve after the settlement window passes
AMOUNT_MISMATCH	PSP settled amount differs from the captured amount	Manual review: may indicate fee miscalculation or PSP error
FX_VARIANCE	FX rate difference between the internal record and the PSP settlement	Auto-resolve if within tolerance; escalate if above
MISSING_PSP_RECORD	Internal transaction not in PSP settlement file	Investigate: transaction may be pending, duplicated, or failed
MISSING_BANK_CREDIT	PSP settlement file shows payout; bank has no corresponding credit	Escalate: funds may be held, account details may be incorrect, or fraud
DUPLICATE	The same transaction ID appears in multiple settlement files	Auto-flag for PSP dispute; potential double-billing
UNKNOWN_TRANSACTION	The PSP settlement file contains a transaction with no internal record	Escalate immediately: may indicate fraudulent use of credentials

Auto-resolution logic

TIMING_GAP exceptions below the PSP's stated settlement window (typically 24-72 hours) should resolve automatically, as they're expected and, most of the time, completely benign. 

FX_VARIANCE exceptions below the configured tolerance should also auto-resolve, with the variance recorded for audit

 Everything else requires human review, with MISSING_BANK_CREDIT and UNKNOWN_TRANSACTION triggering immediate escalation alerts.

Severity-based SLA routing

The best way to route exceptions is to utilize resolution queues with explicit timers:

• MISSING_BANK_CREDIT and UNKNOWN_TRANSACTION: 4-hour response SLA
• AMOUNT_MISMATCH: 24-hour response SLA
• TIMING_GAP outside window: 48-hour response SLA

SLA breaches trigger escalation notifications.

Every exception resolution action needs to record the resolver's identity and the action taken, forming your audit trail.

Three-Way Reconciliation: Internal Ledger vs. PSP vs. Bank

Two-way reconciliation, matching the internal ledger against PSP settlement files, confirms that the PSP acknowledges the transactions, but does not confirm that the funds arrived in the bank account.

We have already mentioned that the solution to this is three-way reconciliation, but let’s look at this process in more detail:

• Stage 1: Internal to PSP matching. The two-way matching is described in the engine section above. Confirmed records move to reconciliation_status = PSP_MATCHED.
• Stage 2: PSP to bank matching. For each PSP_MATCHED payout, the system locates the corresponding credit in the bank statement. The matching key is the payout reference. When found and amounts match within tolerance, the record moves to BANK_MATCHED.
• Stage 3: Fully reconciled confirmation. A transaction reaches FULLY_RECONCILED only when it holds a matching record in all three systems. Transactions that are PSP_MATCHED but not BANK_MATCHED after the expected settlement window generate a MISSING_BANK_CREDIT exception.

The ISO 20022 advantage

ISO 20022 payment messages, relevant for Fedwire after July 2025 and SWIFT MX after November 2025, carry richer remittance information in structured fields than their MT-format predecessors.

For cross-border reconciliation specifically, teams should architect the normalisation layer to handle both legacy MT and ISO 20022 message formats from the outset, to help you avoid costly rework later.

Audit Trail and Regulatory Requirements

Financial applications are incredibly regulated, and a reconciliation system is subject to the same audit trail requirements as the payment ledger it validates.

Three specific requirements shape the data architecture:

Immutability of reconciliation records.

No reconciliation decision should overwrite a prior state. Every status transition on PaymentTransaction.reconciliation_status and every ReconciliationException record needs to be append-only.

In other words, the audit trail needs to record the entire sequence of state transitions.

Resolver identity on exception resolution.

Every exception resolution action needs to be recorded, along with information like who resolved it, when, and what action was taken, to satisfy the governance documentation requirements under SR 11-7 and equivalent frameworks.

Reconciliation coverage reporting.

The percentage of transactions achieving FULLY_RECONCILED status within the expected window is an incredibly valuable metric to review.

The system should produce a daily reconciliation summary report that includes total transactions processed, total FULLY_RECONCILED, total open exceptions by type, and exception resolution rate.

The Engineering Team for a Reconciliation System

A production-grade payment reconciliation system requires three engineering disciplines that are frequently underrepresented at growth-stage fintechs because of how difficult these people are to find, and the rates that they charge.

• Backend engineers with fintech ledger experience (2-3): They design and implement the canonical data model, normalisation adapters for each PSP, and the matching engine. They need to understand financial data conventions like integer minor unit arithmetic, double-entry ledger principles, idempotent processing, and the transaction lifecycle from authorisation to settlement.
• Data engineers (1-2): They build and maintain the settlement file ingestion pipeline, handle format changes from PSPs as they occur, implement the PSP-to-bank statement linking pipeline, and produce reconciliation coverage reports.
• Integration and operations engineers (1): They build and maintain the exception routing workflows, escalation alerting, and the operations dashboard that finance teams use for manual exception resolution.

Build vs. buy

Before you decide to hire a full internal team to build your payment reconciliation system from scratch, you should consider whether existing off-the-shelf reconciliation infrastructure covers your needs.

Platforms like Ledge and Stripe's reconciliation tooling can reduce custom engineering time substantially, getting your product on the market faster without sacrificing compliance.

This is the path we usually recommend for teams with standard PSP configurations and settlement patterns.

However, a custom build tends to win if you need highly custom legacy core banking integrations, multi-currency FX complexity, or compliance requirements that demand a specific audit trail structure that generic platforms don't support.

If this is the pathway you decide to go, finding the right developers quickly can make all the difference in your time-to-market. Hiring through traditional models can take as long as six months for the niche roles required.

And, if you hire the wrong person, you need to start the hiring process from scratch.

At Trio, our experts are pre-vetted by developers who have real production experience and know what to look for.

These developers can be placed in as little as 3-5 days. And, if you decide that you made a mistake, replacements happen quickly, without you needing to start the hiring process from scratch.

Request a consult.