A reconciliation data model is only as useful as the degree to which it enforces structural discipline on heterogeneous input data. In cross-border payment operations, heterogeneous is an understatement — a single merchant may ingest settlement data from six PSPs, each using different field names for the same concept, different timestamp formats, different conventions for representing refunds and chargebacks, and different granularity (transaction-level vs. batch-level). The data model's job is to absorb that heterogeneity at the ingestion boundary and present a uniform surface for matching logic, exception management, and reporting.
What follows is a walkthrough of the canonical model we use to normalize PSP settlement feeds. This isn't a theoretical schema — it reflects the specific design decisions forced on us by real PSP edge cases across Brazil, Nigeria, Philippines, and Indonesia corridors.
Core Entities: Three Tables That Matter Most
payment_event
Every action in the payment lifecycle — authorization, capture, settlement, refund, chargeback debit, chargeback reversal, fee assessment — is modeled as a distinct payment_event record rather than a status update to a single payment record. This is the most important structural decision in the model. Using status-based modeling (a single payment row with a status column updated through its lifecycle) seems simpler but breaks when you need to answer questions like: "What was the settlement amount on this transaction, and what was the FX rate that was applied?"
payment_event (
event_id UUID PRIMARY KEY,
canonical_txn_id UUID NOT NULL, -- stable internal ID
psp_event_id TEXT, -- PSP's own event reference
event_type TEXT NOT NULL, -- auth|capture|settlement|refund|chargeback_debit|...
event_ts_utc TIMESTAMPTZ NOT NULL, -- when event occurred, normalized to UTC
report_ts_utc TIMESTAMPTZ, -- when event appeared in a PSP report
txn_currency CHAR(3), -- original transaction currency (e.g. BRL)
billing_currency CHAR(3), -- cardholder billing currency (e.g. USD)
settle_currency CHAR(3), -- currency in which merchant received funds
txn_amount NUMERIC(18,6), -- amount in txn_currency
billing_amount NUMERIC(18,6), -- amount in billing_currency
settle_amount NUMERIC(18,6), -- amount in settle_currency
fx_rate_applied NUMERIC(18,8), -- rate used for conversion (if known)
psp_id UUID NOT NULL, -- FK to psp_config
corridor_id UUID, -- FK to corridor_config
raw_source_ref TEXT -- pointer to raw ingested file/row for audit
)The three-currency structure (txn, billing, settle) is essential for cross-border operations. A Nigerian consumer pays in NGN (txn_currency). Their Visa card bills them in NGN but the merchant's billing arrangement is in USD (billing_currency). The merchant receives a settlement in USD via SWIFT (settle_currency). These three amounts — and the conversion rates between them — are all potentially different, and all potentially useful for different downstream purposes. Collapsing them into a single "amount" field produces a model that works until a corridor doesn't fit the assumption, then fails silently.
psp_config
PSP configuration is a first-class entity, not a reference table. It stores the metadata needed to correctly ingest and interpret that PSP's settlement files: file format and delivery mechanism, field-name mappings to the canonical schema, known quirks (e.g., "this PSP reports refunds as negative settlement amounts rather than separate refund events"), and contractual terms relevant to reconciliation (settlement frequency, FX markup rate if known, dispute window SLAs).
Having this as a database-backed config — rather than hardcoded in ingestion scripts — means adding a new PSP corridor is a configuration operation, not a code deployment. It also means that when a PSP changes its file format (which happens more often than PSPs communicate in advance), the fix is a config update to the field mappings rather than a code change that requires review and deployment.
corridor_config
Corridor configuration stores the expected settlement behavior for each payment corridor: the PSP, the market, the payment method type, the expected settlement window (min and max business days), the FX tolerance band for matching (in basis points), and the holiday calendar ID. This is what the matching engine queries to determine whether a given item is expected-pending (within the normal window) or genuinely overdue.
corridor_config (
corridor_id UUID PRIMARY KEY,
psp_id UUID NOT NULL,
market_iso2 CHAR(2) NOT NULL, -- e.g. BR, NG, PH, ID
payment_method TEXT NOT NULL, -- card|pix|m-pesa|qris|upi|nip|...
settle_window_min_days INT NOT NULL,
settle_window_max_days INT NOT NULL,
fx_tolerance_bps INT NOT NULL,
holiday_calendar_id UUID,
notes TEXT
)Holiday calendar IDs reference a separate table of market-specific public holidays, maintained per corridor-year. This is non-trivial operational overhead, but the alternative — having settlement items flagged as overdue every time a Indonesian bank holiday causes a 1-day delay — creates more overhead in false-positive exception investigation.
The Ingestion Boundary: Raw vs. Canonical
The model maintains a strict separation between raw ingested data and canonical event records. Raw files — PSP settlement CSVs, bank MT940 statements, webhook payloads — are stored immutably in an ingestion archive. Canonical event records are produced by the normalization layer that reads raw files and writes to payment_event.
This separation has two operational benefits. First, when a PSP sends a corrected settlement file (which happens — PSPs issue amended reports), the normalization layer can re-process the raw file without ambiguity about what changed, because the original raw file is preserved. Second, when a reconciliation discrepancy is traced back to a data issue, the audit trail from payment_event.raw_source_ref leads directly to the specific row in the specific raw file, without requiring anyone to remember which file was processed when.
Exception Records and the Open Item Ledger
Reconciliation matching produces two categories of output: matched pairs (a payment_event of type authorization linked to a corresponding payment_event of type settlement, within tolerance) and exception items (events that don't produce a clean match).
Exception items are written to a separate recon_exception table rather than flagged inline on the payment_event record. This is a deliberate design choice: exception records need additional lifecycle fields (owner, status, priority score, response deadline, resolution reason) that don't belong on the core event record. Mixing lifecycle management state into the core event entity creates confusion about what the event record represents.
The open item ledger — the set of all unresolved exceptions — is a query over recon_exception filtered on resolution_status IS NULL OR resolution_status = 'pending'. Aging queries against this table, segmented by corridor and exception type, give the ops team the data to prioritize exception work by value and urgency. Exception items with a dispute window deadline within 7 days and value above a configured threshold should surface in a high-priority queue automatically — this is a query, not a custom notification system.
What the Model Deliberately Excludes
This model does not attempt to be an accounting general ledger. It does not model double-entry bookkeeping, accruals, or period-close adjustments. Those belong in the finance system of record. The reconciliation model's output — matched transactions, exception resolutions, FX deltas — feeds into the accounting system as journal entry inputs, but the two systems are distinct and should remain distinct.
Attempting to make a reconciliation data model serve as both an operational matching engine and an accounting ledger produces a system that does both tasks inadequately. The operational requirements (low-latency ingestion, real-time exception queuing, flexible matching configuration) conflict with the accounting requirements (auditability, period locking, double-entry integrity). Keeping them separate, with a clean data handoff interface between them, produces better outcomes than attempting a unified system.
The handoff interface is simple: a recon_journal_export view that produces the data an accounting team needs to post reconciliation-period journal entries. The format of that export can be tailored to whatever accounting system is in use without touching the underlying event model.