Designing Robust Financial Reconciliation: Timestamp and Cutoff Challenges
This article highlights a common pitfall in financial reconciliation systems: mismatches due to differing definitions of 'day' or batch cutoffs between internal systems and external financial partners. It emphasizes the importance of aligning with the authoritative source's timestamp and batching logic to prevent recurring reconciliation discrepancies.
Read original on Dev.to #architectureThe Challenge of Distributed Reconciliation
Reconciliation, especially in financial systems, involves matching transactions across multiple disparate systems (internal ledgers, payment gateways, acquirers, etc.). A frequent source of errors arises from differing interpretations of time and batching. This article provides a concrete example of how naive timestamp reliance can lead to subtle, self-healing, but persistent discrepancies.
Key Reconciliation Pitfall
Relying solely on an internal system's `created_at` timestamp and a universal 'midnight to midnight' definition for daily reconciliation can cause mismatches if external partners use different cutoffs or time definitions. These discrepancies are often self-healing over 24 hours, making them hard to diagnose.
Root Cause: Misaligned Batching Logic
The core issue described is a divergence in how 'a day's transactions' are defined. While the internal system used UTC midnight as its cutoff, the acquirer closed its settlement batch several hours earlier. This created a 'window of ambiguity' where transactions occurring after the acquirer's cutoff but before midnight UTC would be processed by the internal system on 'today's' batch but by the acquirer on 'tomorrow's' batch.
Architectural Solution: External Source as Authority
The fundamental fix involves treating the external financial partner's batch identifier or date as the authoritative source for reconciliation. Instead of deriving batch windows internally, the system should rely on the `settlement batch ID` or `settlement date` reported by the acquirer for each transaction. This shifts the responsibility for defining the batch boundary to the system that actually controls it, eliminating the temporal mismatch.
- Key reconciliation on the acquirer's reported `settlement batch ID` or `settlement date`.
- Use internal timestamps only as a fallback or for auditing, not for primary batching logic.
- Store per-acquirer specific cutoffs as configuration, as these can vary even within the same gateway.
This case highlights a critical design principle in distributed systems, especially in financial domains: when integrating with external systems, always align with their authoritative data sources and business logic, particularly concerning time and batch boundaries, to ensure data consistency and accuracy.