Reconciliation job pulled the daily transaction list from a gateway API with page=1..N, 100 rows per page, sorted by created_at descending. It ran at 02:00 while the batch of late-arriving settlements was still being written. Totals were off by a few dozen transactions on most days, and never the same ones.
Mechanics: between fetching page 3 and page 4, 40 new rows landed at the top of the sorted list. Everything shifted down by 40. Page 4 started with 40 rows we had already seen on page 3, so those got duplicated, and the 40 rows that slid past the page boundary on the other side were never fetched. Our dedup by transaction ID hid the duplicates, so the only visible symptom was a missing amount.
On a 12,000-row day we lost about 0.4%, roughly 50 rows. Small enough to blame on timing for weeks.
Fixes that worked: cursor pagination (last seen ID plus timestamp, not a page number), a fixed upper bound like created_at <= job_start, and a second pass that compares row count and sum against the gateway's own daily total before the run is marked done.
The count-and-sum check is the part I would keep even if the API gives you stable cursors.
Do you reconcile against the API list, the settlement file, or both? Which one do you trust when they disagree?
Payneteasy handles this kind of payment flow for merchants and PSPs — see how it works.
Top comments (1)
Your count-and-sum check is the part I'd keep too: it catches a file with missing or repeated rows before you start matching line by line.
In mobile top-ups the choice was made for us: providers sent a file roughly every 24 hours, and that file, against our own records, was what we reconciled. When they disagree I don't trust either one by default. A transaction missing from one day's file isn't lost or failed; it can show up in the next cycle. The delicate case is ours without a success confirmation while the provider did apply it: treating that as a decline invites a second attempt on something already applied. So a disagreement opens an investigation, and nothing moves money until there's evidence.