Key Takeaways
- Naive reconciliation systems break when PSP schema drift floods the matching engine with false exceptions, multi-leg transaction blindness flags entire batch settlements as errors, and two-way reconciliation gives a false sense of completeness by never confirming funds actually landed in the bank.
- The canonical data model, specifically storing amounts as integer minor units and never as FLOAT, represents the most consequential single architectural decision.
- Multi-PSP reconciliation requires a dedicated normalisation layer per PSP. Stripe alone requires traversing four linked identifiers (charge_id, payment_intent_id, balance_transaction_id, payout_id) to reconcile a single payout line.
- Three-way reconciliation, matching the internal ledger against the PSP settlement file against the bank statement, distinguishes settlement certainty from settlement assumption.
- Exception management ranks among the three most time-consuming reconciliation steps for 32% of UK payments professionals, tied with data matching and data collection. A severity-based taxonomy with explicit SLA timers turns that burden into an auditable workflow.
- Settled and final are different states. Card payments stay disputable for 120 calendar days, and up to 540 where delivery is delayed, so coverage reported on day one will not match the same cohort two months later.
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.
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:
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.
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.
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.
Most teams reach for automating the matching step first, which helps, though it tends to expose how little the underlying data model was designed for the job.
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.
In this example, amount_minor_units stores amounts as integer minor units, such as cents. This is the example’s design choice, rather than a universal requirement. PostgreSQL’s DECIMAL type provides exact decimal storage, while FLOAT uses an approximate floating-point representation.
For decimal storage, define precision (the total number of significant digits), scale (the number of fractional digits), and rounding rules.
PostgreSQL rounds inputs that exceed a column’s declared scale, so exact decimal storage still requires explicit rounding decisions. See PostgreSQL’s numeric-type documentation: https://www.postgresql.org/docs/current/datatype-numeric.html
The concern here is how amounts are represented and rounded. Approximate floating-point values can introduce small discrepancies, but choosing exact decimal storage does not itself imply data corruption. Document the representation and rounding rules your reconciliation comparisons will use.
Core entities in the reconciliation schema
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 (ENUM): 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 days, at $40-80/hr.
How do you resolve identifiers across different PSPs?
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
What matching tolerance should you allow 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.
The operational weight behind this is real. In Kani Payments' 2025 survey of 250 UK payments professionals, 32% put exception management among the three most time-consuming steps in reconciliation, level with data matching and data collection.
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 carry richer remittance information in structured fields than the MT formats they replaced. Fedwire moved across in July 2025, and SWIFT's cross-border coexistence period closed in November 2025, so these structured fields are now the norm rather than something to plan for.
Legacy MT formats still show up in archives and in a few domestic corridors, so a normalisation layer that reads both costs little to keep. The structured remittance fields matter more day to day, because they carry the end-to-end reference that makes a bank credit matchable without guesswork.
When is a payment actually final?
Settlement and finality often get treated as one event. Settlement means the money moved. Finality means it stops being reversible, and on most rails that happens later, sometimes much later.
A reconciliation run that closes on settlement produces a clean report that can still unwind weeks afterwards. Reversal windows differ enough between rails that they belong in the data model rather than in someone's head.
Fedwire, RTP, and FedNow settle with finality. Once the receiving bank accepts, there is no return path, which is part of why these rails carry the tightest sanctions screening.
Card payments stay disputable for 120 calendar days from the transaction date in the general case. Visa and Mastercard allow up to 540 calendar days where goods or services were not delivered immediately, which covers pre-orders and most travel bookings.
ACH splits by return reason. Ordinary returns such as insufficient funds or a closed account land by the opening of business on the second banking day after settlement, while the unauthorised-debit codes (R05, R07, R10 and R11) run to the 60th calendar day.
On-chain settlement reaches practical finality in minutes, but how many confirmations count as final tends to be a policy your team sets rather than one the rail hands you, so it needs writing down somewhere the matching engine can read.
A transaction marked FULLY_RECONCILED on the day its bank credit matches is reconciled, not final. Carrying a separate finality_at timestamp, derived from the rail rather than hard-coded, keeps that distinction visible to whoever reads the record months later.
This shows up in the coverage report more than anywhere else. Coverage measured on day one and the same cohort measured after the card dispute window closes rarely agree, and the gap is worth tracking as its own number rather than writing off as an accounting artefact.
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 developers are pre-vetted by engineers who have shipped payment systems themselves and know what to look for.
These developers can be placed in as little as 3-5 days. If you decide that you made a mistake, replacements happen quickly, without you needing to start the hiring process from scratch.

Alex Kugell




