DEV Community

Payneteasy
Payneteasy

Posted on

Your daily reconciliation breaks at the cutoff, not at midnight

We had a reconciliation job that matched our orders to the acquirer's settlement report by calendar day. It worked for weeks. Then finance started seeing a handful of unmatched transactions every morning, always clustered in the same two-hour window in the evening.

Nothing was missing. The transactions showed up the next day, in the next report.

The cause: we bucketed by our own created_at in UTC midnight to midnight. The acquirer's settlement batch closes at a fixed cutoff that is not midnight UTC. In our case it was a few hours earlier. A payment captured at 21:40 UTC belonged to tomorrow's batch for the acquirer and to today's for us. So every day, a few transactions were "missing" from one side and "extra" on the other, and then self-healed 24 hours later. That is why nobody could reproduce it on demand.

The fix was boring. We stopped deriving the batch window ourselves. We key reconciliation on the settlement batch ID or date the acquirer reports for each transaction, and treat our own timestamp only as a fallback. We also store the cutoff as config per acquirer, because two acquirers on the same gateway had different ones.

Two things I'd check in any recon design: which timestamp defines "the day", and whose clock owns it. If the answer is "ours", you will get a small, recurring, self-healing mismatch.

How do you handle per-acquirer cutoffs: config table, or pulled from the settlement files?

Payneteasy handles this kind of payment flow for merchants and PSPs — see how it works.

Top comments (2)

Collapse
 
fintechrailsdigest profile image
Fintech Rails Digest •

the two-hour evening cluster is the tell that separates this from a data-quality bug: a genuine mismatch scatters across the day, a window mismatch repeats at the same wall-clock time until someone notices. bucketing the acquirer side by its own cutoff clock and reconciling on overlapping business dates, rather than forcing both sides onto UTC midnight, is usually the cheaper fix than backdating transactions to fit the batch. keeping the raw created_at untouched on your side matters too, since you will need it again when finance asks why a 23:40 payment settled with yesterday's batch.

Collapse
 
payneteasy profile image
Payneteasy •

Exactly, the same wall-clock time every day is what gives it away. And agreed on keeping created_at untouched: it’s the evidence you’ll need when finance asks. One more trap from the same family: if the acquirer’s cutoff is set in local time, it moves in UTC twice a year with daylight saving. So the cutoff should be stored with its time zone, not as a fixed UTC hour, or the cluster quietly shifts by an hour every spring and autumn.