Payment Reconciliation: Why Your Numbers Never Quite Match

A practical guide to payment reconciliation for small teams: why your ledger and your gateway disagree, and how to close the gap without losing your mind.

Every business that takes online payments eventually hits the same uncomfortable moment: the number in your database does not match the number from your payment provider, and neither matches what actually landed in your bank account. Three sources of truth, three different totals. Welcome to reconciliation — the unglamorous discipline that keeps the money side of your product honest.

It is tempting to treat this as an accounting problem to be solved later. It is not. Reconciliation is an engineering problem, and the teams that ignore it tend to discover missing revenue, duplicate refunds, and angry customers at the worst possible time.

Why the Numbers Drift Apart

The gap is rarely fraud. It is almost always timing and state. A payment moves through several stages — authorized, captured, settled, refunded, disputed — and each system observes those transitions at a slightly different moment.

Common culprits include:

• Pending settlements. Money confirmed by the gateway today may not clear your bank for two or three business days.
• Fees taken at the edge. Providers often deduct their cut before payout, so a $100 sale arrives as $97.10. If you record the gross and ignore the fee, you will never balance.
• Webhooks that arrive late, twice, or never. If your ledger depends only on webhooks, a single dropped event leaves a transaction stranded.
• Partial refunds and chargebacks that update a charge days after it was created.

The goal of reconciliation is not to prevent drift. Drift is normal. The goal is to detect it quickly and explain every cent of it.

Build a Ledger You Control

The most important decision is to keep your own immutable record of every money movement, rather than trusting the gateway's dashboard as your source of truth. Append-only is the rule: you never edit a row, you only add a new entry that supersedes it.

For each transaction, store the provider's reference ID, the amount in minor units (cents, not dollars — floating point and money do not mix), the currency, the status, and a timestamp for every state change. That reference ID is what lets you line your records up against the provider's later.

Reconcile on a Schedule, Not on Hope

Webhooks are a notification mechanism, not a system of record. Treat them as a hint that something changed, then verify against the provider's settlement report — the authoritative file most gateways publish daily.

A simple nightly job goes a long way:

1. Pull yesterday's settlement report from the provider.
2. Match each line to a row in your ledger by reference ID.
3. Flag anything that exists on one side but not the other.
4. Flag anything where the amounts disagree after fees.

Most days that job finds nothing, and that silence is exactly the point. When it does flag something, you have a small, dated list to investigate instead of a quarter-end mystery.

Make Mismatches Loud

A reconciliation system that quietly logs discrepancies is barely better than none. Route unmatched transactions to a place a human will actually see — a dashboard, an alert, a daily summary message. The dollar amount of an unresolved mismatch is a direct measure of risk sitting on your books.

The takeaway is simple: money deserves the same rigor you give your code. Keep your own immutable ledger, store amounts in minor units, verify against settlement reports instead of trusting notifications, and surface every mismatch loudly. Do that, and the question stops being "are we balanced?" and becomes "here is exactly why we're off by $3.40, and here's the fix."