Urgent.News

What's breaking now, across hundreds of outlets.

Tech

Payment Reconciliation System Design: Data Model to Matching Engine

Most teams start reconciliation with a spreadsheet and a script, and both break the first month volume doubles. This guide covers payment reconciliation system design from an engineer's point of view: the data model, how to ingest from processors and banks without duplicates, how to build a matching engine you can trust, and how to handle the failures that make reconciliation hard. If you need…

Reconciliation systems aim to verify that every unit of money accounted for moved as expected, in the correct amount to the right destination, and can provide evidence to support the conclusion. This involves linking three distinct data sources: your internal ledger, the payment processor, and the bank. These disparate sources lack a common, shared primary key across their data.

The reconciliation system's primary task is to establish and maintain connections between these disparate data sources, and to ensure that every link can be explained and validated. A key aspect of design is to differentiate between raw, immutable records received from sources and the conclusions drawn from those records. The raw records are considered factual and should never be edited or altered.

In contrast, matches made during reconciliation are conclusions that may be revised based on new information. The raw data is stored in a table called source_record, which captures the raw information as received from each source. This table includes fields for the source type, external identifier, the original payload, and the timestamp of ingestion.

A normalized view of the data, called txn, is used by the matching engine. This table contains information about each transaction, including its kind (charge, fee, refund, etc.), amount in minor units (cents), currency, date of occurrence, and any relevant references. The match_group table stores conclusions about which transactions should be matched together.

These groups are created based on rules defined by the system or by human operators. Each match group is assigned a status indicating whether it is active or has been reversed. A match_line table links transactions within a match group, providing a many-to-many relationship that can accommodate complex payment scenarios involving multiple transactions.

The design of the system incorporates several deliberate choices to ensure robustness and accuracy. One critical decision is to treat source records as immutable facts, while matches are conclusions that can be revised. This separation allows for the creation of new match groups when discrepancies are discovered, rather than permanently altering existing data.

Amounts are stored as signed integers representing minor units (e.g., cents), enabling precise calculations without the risk of floating-point errors. This approach ensures that all monetary values are consistently represented and compared. The system employs a many-to-many matching approach, recognizing that a single bank deposit may correspond to multiple payouts, which in turn may have hundreds of charges, fees, and refunds associated with them.

This flexibility in matching shapes accommodates various payment patterns, such as orders paid in installments. Reversing a conclusion in the system does not involve deleting rows but rather marking the match group as reversed and creating a new one. This approach maintains an audit trail and allows for transparency in the reconciliation process.

Data ingestion is accomplished through two primary channels: webhooks for near-real-time updates and scheduled pulls of settlement data from processors for the ultimate source of truth. Webhooks provide quick notification of events but can be unreliable due to potential delays, duplicates, or out-of-order delivery. Scheduled pulls ensure the accuracy and completeness of the data used for reconciliation.

Bank statements are typically received in various formats, and deriving stable line identifiers from non-changing fields (such as account number, booking date, amount, reference, and a sequence number) is crucial for accurate matching. Similarly, outbound requests like issuing refunds must be handled with idempotency in mind to prevent duplicate processing.

By implementing these design principles, the reconciliation system can effectively link disparate data sources, maintain a robust matching engine, and provide transparent, auditable evidence of financial transactions.

Rewritten by urgent.news; may contain errors — check the original.

Read the original at dev.to →

Report a problem with this story

More in Tech

CVE-2025-42884 remediation without overstating the evidence

CVE-2025-42884 remediation without overstating the evidence Known exploitation is enough to raise priority, but it is not a substitute for evidence about this vulnerability.

  • CVE-2025-42884 vulnerability affects SAP NetWeaver EP-BASIS and EP-RUNTIME versions 7.50.
  • Unauthenticated attacker can inject JNDI properties/URLs, but exploit chain not confirmed.
  • Remediation involves SAP Security Note 3660969, configuration validation, and system restart.

More from Saturday 21 November →