Payment Reconciliation Systems Explained
A customer pays 100.00. Your database records a 100.00 sale, marks the order paid, ships the goods. Two days later the money lands in your bank account and it is 97.10. Nobody stole anything. Stripe took a fee, the payout batched three days of charges together, and one refund from last week netted out inside the same transfer. Your ledger says one number, the settlement report says another, and finance wants to know which one is real.
Here is the uncomfortable truth: both are real, they are just measured at different points in time with different adjustments applied. Reconciliation is the discipline of matching your internal ledger against the payment processor’s settlement and the bank’s deposit, finding every difference, and either explaining it or flagging it as an exception. Get this wrong and you either lose money quietly or fail an audit loudly. (The fee amounts in this post are illustrative, not any processor’s actual pricing.) Here is how to build it correctly.
What reconciliation actually is
Reconciliation is a matching problem across three independent records of the same money:
- Your internal ledger (what your system believes happened).
- The PSP settlement report (what Stripe, Adyen, or your acquirer says happened, net of fees).
- The bank statement (what actually hit your account).
When all three agree on an amount, you have a match. When they disagree, you have a difference that must be classified and resolved. That is the whole game. The hard part is that the three records use different identifiers, different timing, and different amounts (because of fees), so naive amount == amount matching falls apart on day one.
If you have not modeled your money movement as first-class events yet, start with the core pillars of fintech and the patterns for building scalable and resilient fintech systems. Reconciliation sits on top of a clean ledger; it cannot fix a broken one.
The double-entry ledger underneath
Every reliable reconciliation system rests on a double-entry ledger. Two rules define it:
- Every transaction has at least one debit and one credit, and the sum of debits equals the sum of credits, per currency. You never let a USD debit balance an EUR credit.
- Records are append-only and immutable. You never
UPDATEa money row. To correct a mistake you write a new, reversing entry.
That second rule is the one engineers fight and then regret ignoring. An immutable, append-only log means any past state can be reconstructed and the audit trail can never be quietly rewritten. Modern Treasury builds its entire ledger product on exactly this principle.
-- Accounts (assets increase on debit; liabilities/revenue increase on credit)
CREATE TABLE ledger_accounts (
id bigint PRIMARY KEY,
name text NOT NULL,
normality text NOT NULL CHECK (normality IN ('debit', 'credit'))
);
-- A transaction groups entries that must net to zero per currency
CREATE TABLE ledger_transactions (
id uuid PRIMARY KEY,
idempotency_key text UNIQUE NOT NULL, -- one key, one transaction, ever
external_ref text, -- PSP charge id, e.g. ch_3Xyz...
created_at timestamptz NOT NULL DEFAULT now()
);
-- Immutable entries. No UPDATE, no DELETE. Corrections are new rows.
CREATE TABLE ledger_entries (
id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
transaction_id uuid NOT NULL REFERENCES ledger_transactions(id),
account_id bigint NOT NULL REFERENCES ledger_accounts(id),
direction text NOT NULL CHECK (direction IN ('debit', 'credit')),
amount_minor bigint NOT NULL CHECK (amount_minor > 0), -- cents, never floats
currency char(3) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);Store money in minor units (integer cents), never floating point. 0.1 + 0.2 != 0.3 in IEEE 754, and that rounding error is precisely the kind of penny drift reconciliation exists to catch, so do not manufacture it yourself.
Idempotency is the foundation
The idempotency_key UNIQUE constraint is not decoration. A webhook from your PSP can fire twice, a retry can replay a request, and a network timeout can leave you unsure whether the first attempt landed. Without idempotency you post the same charge twice and your ledger inflates. This is the same principle that stops double charges at the payment edge, covered in idempotency keys prevent double charges, and it is the reason retries are not a fix on their own. Idempotency plus an append-only ledger is what makes retries safe.
The three-way match
Here is the flow when money moves and later settles.
sequenceDiagram
participant App as Your App
participant Ledger as Internal Ledger
participant PSP as PSP (Stripe)
participant Bank as Bank Account
App->>PSP: Charge 100.00 (idempotency-key)
PSP-->>App: charge succeeded (ch_123)
App->>Ledger: post +100.00 (gross), external_ref=ch_123
Note over PSP: T+2 payout batches charges,<br/>deducts fees, nets refunds
PSP->>Bank: payout 97.10 (po_456)
PSP-->>App: balance_transaction report (net 97.10, fee 2.90)
App->>Ledger: match ch_123 -> po_456, post fee 2.90
Bank-->>App: statement line +97.10
Note over Ledger,Bank: three-way match:<br/>ledger + PSP + bank agree
Stripe’s balance transaction object is built for exactly this. Each object carries amount (gross), fee, and net, where net = amount - fee. It also has status (pending or available), and available_on, the timestamp when the net funds become available in your Stripe balance. That available_on gap is your T+N settlement delay made explicit. You reconcile the internal charge against the balance transaction, then reconcile the batched payout against the bank deposit.
type BalanceTxn = {
id: string; // txn_...
source: string; // ch_123 -> links to your ledger's external_ref
amount: number; // gross, minor units
fee: number; // positive when assessed
net: number; // amount - fee
currency: string;
status: "pending" | "available";
available_on: number; // unix seconds; the T+N moment
};
function classify(ledgerAmount: number, txn: BalanceTxn): "matched" | "exception" {
// Match on gross, then account for the fee separately. Never expect
// the bank deposit to equal your gross sale.
if (ledgerAmount === txn.amount) return "matched";
return "exception";
}Matched, unmatched, and exceptions
Every record lands in one of three states.
- Matched: found on all three sides, amounts reconcile after fees. Nothing to do.
- Unmatched: present on one side, not yet on another. Usually a timing gap. A charge posted today whose payout arrives at T+2 is unmatched, not wrong. You leave it open and re-run tomorrow.
- Exception: a real discrepancy. The bank shows a deposit with no matching charge, an amount that does not reconcile even after fees, a duplicate, or a chargeback you did not book.
Resolving exceptions is where reconciliation earns its keep. The three biggest sources of legitimate differences:
- Fees. The bank deposit is always net of PSP fees. Book the fee as its own ledger entry against a fees expense account so gross and net both stay explicit.
- Currency. Cross-currency settlements introduce FX conversion and rounding. Match per currency and record the FX rate used, or you will chase phantom pennies forever.
- Timing (T+N). Charges settle on
available_on, not on capture. A payout batches many charges across days and nets refunds inside it. Match against the batch, not against wall-clock date.
The correct fix for an exception is never UPDATE ledger_entries SET amount = .... It is a new reversing entry plus a new correct entry, so the history shows exactly what happened and when. Immutability is what lets an auditor trust the number.
The takeaway
Reconciliation is not a nightly cron that prints “all good.” It is a three-way match between an immutable double-entry ledger, the PSP settlement, and the bank, designed from the start to expect that the three numbers differ by fees, currency, and time. Store money in integer minor units, make every posting idempotent, correct with reversing entries instead of updates, and classify everything as matched, unmatched, or exception. Do that and the day finance asks “why is the bank 2.90 short,” you point at a fee entry and move on, instead of opening a spreadsheet and praying.