Reconciliation & Double-Entry Ledger

A billing system that trusts a single mutable balance column eventually lies to you: a webhook lands twice, a refund races a chargeback, a fee gets netted out of a payout you never recorded, and now your reported revenue disagrees with the money that actually hit your bank account. The fix is an immutable double-entry ledger that serves as the authoritative record of every value movement, plus a reconciliation process that continuously proves that ledger against what your payment gateway settled. This guide sits under Tax, Compliance & Revenue Recognition and covers the ledger model, the matching engine, discrepancy detection, and period close.

Double-entry is not bookkeeping nostalgia. It is a structural invariant — every transaction touches at least two accounts and the signed amounts sum to zero — that makes whole classes of bugs impossible to commit and trivial to detect. Reconciliation then layers an external proof on top: the gateway is the ground truth for cash, the ledger is the ground truth for what you think happened, and the job is to make them agree to the cent. When they do not, you want to know within a close cycle, not at audit time. Reliable posting depends on events arriving exactly once, so this work builds directly on the outbox pattern for reliable billing events and on idempotent webhook handlers in Node.js.

Prerequisites

Reconciliation proves your ledger against external cash, so it depends on two things being solid first: events posted exactly once, and money stored as integer minor units. The stack below lists the foundations the matcher assumes.

• A payment gateway that exposes settlement/balance-transaction and payout reports (Stripe Balance Transactions, Adyen settlement details, etc.)
• An outbox or equivalent guarantee that each billing event is posted to the ledger exactly once
• An idempotency-key store so replayed webhooks do not double-post entries
• A PostgreSQL database where money is stored as BIGINT minor units, never floats
• A scheduled job runner (cron, Temporal, or a queue worker) for nightly reconciliation
• Agreement on a single ledger currency per legal entity, or per-currency sub-ledgers

The word “solid” in that first line is load-bearing. If your posting path is even occasionally non-idempotent, reconciliation stops being a proof and becomes a source of false alarms: the matcher flags a duplicate ledger_entry against a single settlement_line, an operator investigates, finds nothing wrong at the gateway, and learns to distrust the tool. Within three or four false positives per close, people start clicking “acknowledge” without looking, and the one real discrepancy that matters slips through with the noise. If you cannot yet promise exactly-once, build the idempotency store first and come back; a reconciliation layer on top of a leaky ledger reports on its own bugs rather than on the gateway.

One prerequisite deserves an explicit decision before you write any schema: what a single external_ref means at your gateway. Stripe, for example, issues a charge id, a balance_transaction id, and a payout id for what a naive reading treats as “one payment,” and they carry different amounts because the balance transaction already nets the processing fee. Pick the identifier that appears on both sides — usually the balance-transaction or settlement-detail id — and store the others as secondary columns for human investigation, not as the join key.

Architecture & Data Flow

The ledger is fed by domain events (invoice finalized, payment succeeded, refund issued, payout paid). Each event is translated into one balanced transaction — a set of ledger_entries that share a transaction_id and whose amount values sum to exactly zero across debits and credits. Separately, a settlement ingester pulls the gateway’s own record of money movements into a staging table. The matcher joins the two by an external reference (the gateway charge or payout id) and asserts amount equality. Whatever does not match becomes a discrepancy.

The flow is inputs (domain events + gateway reports), processing (balanced posting + matching), outputs (a closed period or a queue of discrepancies). Crucially, posting and matching are decoupled: the ledger is written synchronously with business logic, while reconciliation runs asynchronously against settlement data that often lags by hours or days.

Why the two writers must not share a transaction

A tempting shortcut is to post the ledger entry and mark the settlement line matched inside the same database transaction, from the same webhook handler. Resist it. The settlement report for a payment_succeeded event frequently does not exist yet at the moment the payment succeeds — Stripe finalizes the balance transaction and assigns it to a payout on its own schedule, often the next business day. If posting waited for settlement data, you could not record accounts receivable until the cash cleared, which defeats the purpose of a ledger that tracks obligations as they arise. So the ledger write happens immediately, keyed by invoice_id and the charge’s external_ref, and the settlement line arrives hours later to be matched against an entry that is already sitting in the table. The two writers touch the same rows but never at the same time, and the status column on settlement_lines is the only mutable field in the whole design.

It helps to be precise about which system is the source of truth for what. The ledger is authoritative for intent and classification: it knows that a 2900-cent movement is revenue for subscription_id sub_8fic versus a refund against a disputed charge, because it was written by code that understood the business event. The gateway is authoritative for cash: it knows, to the cent and to the second, what actually left or entered your bank. When you frame it this way, a discrepancy is never “the ledger is wrong” or “the gateway is wrong” in the abstract; it is a specific claim that two systems disagree about one number attached to one external_ref, which is a far more tractable thing to investigate.

Implementation Walkthrough

The five steps build the ledger, post balanced transactions, ingest settlements, match, then close. The invariant that makes it all work is the zero-sum: every transaction’s legs sum to zero, asserted before commit. The diagram shows a single charge posting as balanced legs and reconciling against the gateway.

1. Model accounts and append-only entries

Define a small chart of accounts and an entry table where money is a signed BIGINT in minor units. Debits are positive, credits negative, by convention; the only hard rule is that entries sharing a transaction_id sum to zero.

CREATE TABLE ledger_accounts (
  ledger_account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code        TEXT NOT NULL UNIQUE,         -- 'cash_in_transit', 'accounts_receivable'
  kind        TEXT NOT NULL CHECK (kind IN ('asset','liability','revenue','expense','equity')),
  currency    CHAR(3) NOT NULL
);

CREATE TABLE ledger_entries (
  ledger_entry_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_id    UUID NOT NULL,                       -- groups the balanced legs
  ledger_account_id UUID NOT NULL REFERENCES ledger_accounts,
  amount            BIGINT NOT NULL,                     -- minor units, signed
  currency          CHAR(3) NOT NULL,
  external_ref      TEXT,                                -- gateway charge/refund/payout id
  occurred_at       TIMESTAMPTZ NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_entries_txn ON ledger_entries (transaction_id);
CREATE INDEX idx_entries_ext ON ledger_entries (external_ref);

2. Post events as balanced transactions

Translate one domain event into one transaction. The posting function asserts the zero-sum invariant before committing so an unbalanced bug can never reach the table.

from dataclasses import dataclass

@dataclass
class Leg:
    account_code: str
    amount: int          # minor units, signed
    external_ref: str | None = None

def post_transaction(conn, transaction_id: str, currency: str, legs: list[Leg], occurred_at):
    if sum(leg.amount for leg in legs) != 0:
        raise ValueError("unbalanced transaction")          # ✗ never commit
    with conn.transaction():
        for leg in legs:
            conn.execute(
                """INSERT INTO ledger_entries
                   (transaction_id, ledger_account_id, amount, currency, external_ref, occurred_at)
                   VALUES (%s, (SELECT ledger_account_id FROM ledger_accounts WHERE code=%s),
                           %s, %s, %s, %s)""",
                (transaction_id, leg.account_code, leg.amount, currency,
                 leg.external_ref, occurred_at),
            )                                                # ✅ all legs or none

For a successful $29.00 charge you post cash_in_transit +2900 and accounts_receivable -2900. When the gateway later pays out and deducts an $0.87 fee, that is a separate transaction: bank +2813, processor_fees +87, cash_in_transit -2900. Modeling fees as their own legs is what makes fee netting reconcilable later.

Notice that cash_in_transit is the account that ties the two transactions together: the first debits it when the charge succeeds, the second credits it when the payout clears. If the ledger is internally consistent, the running balance of cash_in_transit at any instant equals the money the gateway is holding on your behalf but has not yet deposited — and that balance is independently checkable against the gateway’s own reported “in transit” or “pending” figure. Building the account structure so that a single balance query answers a real operational question (“how much of my money is stuck at the processor right now?”) is a good test of whether your chart of accounts models the actual flow of cash rather than an accountant’s abstraction of it.

The occurred_at argument matters more than it looks. Use the timestamp of the business event — when the payment succeeded — not now() at posting time. If a webhook is delayed by a retry storm and you post an entry at 00:07 for a payment that succeeded at 23:58 the previous day, dating the entry with now() silently moves revenue across a period boundary. Passing the gateway’s event timestamp through as occurred_at keeps the ledger’s period assignment stable regardless of how late your handler runs, which is the difference between a clean month-end and a reopened close.

Because ledger_entries is append-only, the posting function is also the correction function. When a support agent voids an invoice that was already posted, you do not delete the original legs; you call post_transaction again with the same external_ref, the amounts negated, and a fresh transaction_id. Both the mistake and its reversal survive in the record with their own occurred_at timestamps, which is precisely the trail a mutable UPDATE destroys. The only discipline required is that a reversal must reference the original so the pair can be found.

3. Ingest settlement and payout reports

Pull the gateway’s balance transactions into a staging table verbatim. Do not transform amounts; store them exactly as reported so the matcher compares apples to apples.

CREATE TABLE settlement_lines (
  settlement_line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  external_ref   TEXT NOT NULL,           -- gateway txn id
  kind           TEXT NOT NULL,           -- 'charge','refund','fee','payout','adjustment'
  amount         BIGINT NOT NULL,         -- minor units, signed (gateway convention)
  currency       CHAR(3) NOT NULL,
  payout_id      TEXT,
  reported_at    TIMESTAMPTZ NOT NULL,
  matched_entry  UUID REFERENCES ledger_entries,
  status         TEXT NOT NULL DEFAULT 'unmatched'
                 CHECK (status IN ('unmatched','matched','discrepant'))
);

4. Run the matcher

Match by external_ref first, then assert amount equality. Anything that fails either step is a discrepancy, not silently dropped.

-- ✅ exact matches: same ref, same signed amount
UPDATE settlement_lines s
SET matched_entry = e.ledger_entry_id, status = 'matched'
FROM ledger_entries e
WHERE s.status = 'unmatched'
  AND e.external_ref = s.external_ref
  AND e.amount = s.amount
  AND e.currency = s.currency;

-- ⚠️ ref matches but amount differs → discrepancy
UPDATE settlement_lines s
SET status = 'discrepant'
WHERE s.status = 'unmatched'
  AND EXISTS (SELECT 1 FROM ledger_entries e WHERE e.external_ref = s.external_ref);

5. Surface discrepancies and close the period

A period is closeable when every settlement line in its window is matched and no ledger entry in the window lacks a settlement counterpart. Record the close as an immutable marker so re-runs are idempotent.

CREATE TABLE period_closes (
  period_close_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  period_start    DATE NOT NULL,
  period_end      DATE NOT NULL,
  closed_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  entry_count     BIGINT NOT NULL,
  net_minor_units BIGINT NOT NULL,
  checksum        TEXT NOT NULL,             -- hash over ordered entry ids + amounts
  UNIQUE (period_start, period_end)
);

The checksum lets a later audit prove the period was not altered after close. For the concrete schema with append-only enforcement see building an immutable double-entry billing ledger in PostgreSQL.

Compute the checksum over a deterministic ordering — sort the in-window entries by (occurred_at, ledger_entry_id) and hash the concatenation of ledger_entry_id, amount, and ledger_account_id for each. The ordering has to be stable across re-runs or the hash is meaningless: two closes over the same data must produce the same digest. This is why ledger_entry_id rather than insertion order is part of the sort key; UUIDs are stable where a sequence-assigned row number is not if a partition is ever rebuilt. Store entry_count and net_minor_units alongside the checksum as cheap human-readable sanity checks: an operator glancing at a close row can see “4,812 entries, net +1,204,338 minor units” and immediately notice if next month reports 40 entries. Make the close idempotent by keying it on the UNIQUE (period_start, period_end) constraint with ON CONFLICT DO NOTHING, so a job that crashes after writing the marker is safe to re-run; if a re-run’s recomputed checksum differs from the stored one, that is itself an alarm — entries were posted into a window after it was declared closed.

Edge Cases & Failure Modes

The reconciliation edge cases cluster around three sources: partial writes, timing/boundary skew, and money arithmetic (fees, FX, refunds). The map sorts them so the defense — a transaction, settlement-period assignment, or explicit fee legs — is obvious.

The many-to-one settlement problem

The matching SQL above assumes one ledger entry maps to one settlement line, but real gateways aggregate: a single payout line may correspond to hundreds of charges. When the cardinality is many-to-one, an exact-amount join on a single external_ref cannot succeed, and a naive matcher marks the whole payout discrepant. The fix is a two-tier match: first reconcile individual charge and refund lines against their entries by external_ref, then reconcile the payout as an aggregate by asserting that payout.amount == sum(matched charge legs) - sum(matched fee legs) - sum(matched refund legs) for exactly the set of lines the gateway grouped into that payout_id. If the aggregate equation holds, the payout is proven transitively; if it fails, the difference is almost always a fee or adjustment line the gateway included in the payout but did not export as its own row — a report-completeness bug worth escalating to the gateway rather than a ledger error.

Not every mismatch is a defect. A charge that succeeds at 23:59 on the last day of the month has a ledger entry dated that day but a settlement line dated the next, because the gateway batched it into the following day’s activity. Rather than “fixing” it, the matcher must be timing-aware: match on external_ref across a rolling window (typically two to five settlement days) and only escalate to discrepant when a line stays unmatched beyond the gateway’s documented settlement latency. Encoding the gateway’s SLA as a concrete number of days turns “everything that does not match today” into “everything that should have settled by now and has not” — a much smaller and more actionable set, and getting this window too tight is the single most common cause of dashboards that cry wolf.

Performance & Scale

Posting and matching have opposite performance shapes: posting is a cheap synchronous handful of inserts per event, while matching is a batch set-based job run against settlement data that lags. Keeping them decoupled means neither blocks the other. The diagram contrasts the two paths.

The hot path is posting, and it is cheap: a handful of inserts per event. Index transaction_id and external_ref; the latter is what the matcher joins on. Balance queries should never scan the whole table — maintain a per-account running balance via a materialized view refreshed after each close, or a summary table updated in the same transaction as posting. At 100k subscriptions cycling monthly you generate low-millions of entries per month, which a single partitioned table (range-partitioned by occurred_at month) handles comfortably; old partitions are read-only by definition since entries are append-only.

The matcher is a batch job, not a per-request operation. Ingest settlements in bulk, run the two matching UPDATEs as set-based SQL (not row-by-row), and let unmatched volume be your alert signal. A healthy reconciliation matches well over 99% of lines automatically; the remainder is fees, FX rounding, and timing, which you handle with the buckets above.

The set-based UPDATEs are correct but they will get slow if settlement_lines accumulates every historical row and the matcher rescans all of them each night. The WHERE s.status = 'unmatched' predicate is what saves you: a partial index on settlement_lines (external_ref) WHERE status = 'unmatched' keeps the working set proportional to the number of open lines, not the lifetime total. Once a line reaches matched it drops out of that index and the matcher never touches it again — the unmatched set is naturally self-limiting where the matched set grows without bound, so index the thing that stays small. On the ledger side the join reads ledger_entries by external_ref, so keep that index but resist speculative indexes on amount or occurred_at the matcher never uses, since every extra index taxes the append-only insert path that must stay fast.

There is a subtler scaling concern in the aggregate payout match. Summing charge legs for a payout of 5,000 charges is a GROUP BY payout_id over thousands of rows, and if you run it per payout in a loop you have quietly built an N+1 query. Instead, compute all payout aggregates in a single pass and compare each to its payout line’s amount in the same result set. One query proves every payout in the batch, where the looped form would hold a transaction open long enough to interfere with the nightly posting backlog.

Testing Strategy

The tests center on one invariant and three scenarios: every transaction sums to zero, the matcher classifies a fixture of settlement shapes correctly, replays don’t duplicate, and a one-unit mutation surfaces as discrepant rather than silently matching. The panel lists them.

Reconciliation tests must be deterministic. Inject a mock clock so occurred_at and reported_at are fixed, then assert the period assignment. Build a fixture of synthetic settlement reports — a clean charge, a charge with a fee, a refund, a partial payout — and assert the matcher’s final status distribution exactly. Add a property test that asserts every committed transaction_id sums to zero across its legs; this single invariant catches most posting bugs. Replay the same webhook twice and assert no duplicate entries appear (the idempotency guard). Finally, simulate a discrepancy (mutate one staged amount by one minor unit) and assert it surfaces as discrepant rather than silently matching.

The property test is worth generating rather than hand-writing. Have the generator emit random balanced transactions — pick a random set of accounts, assign random signed amounts, and force the last leg to be the negation of the sum of the others so the transaction is balanced by construction. Post a few thousand, then assert that a GROUP BY transaction_id HAVING sum(amount) <> 0 query returns zero rows. Because the generator can also emit deliberately unbalanced transactions, the same test proves the negative case: the posting function must reject every unbalanced input and leave the table untouched. A generator that occasionally produces amounts near BIGINT limits also flushes out overflow bugs that a fixture of tidy 2900-cent charges never would.

A second class of test that teams skip and later regret targets the period boundary directly. Construct a charge whose occurred_at is 23:59:59 on the last day of a period and whose settlement reported_at is 00:00:01 the next day, run the first period’s close, and assert the entry is included by occurred_at while the still-unmatched settlement line does not block the close; then close the second period and assert the line matches there. This one scenario encodes the entire timing philosophy of the design and fails loudly the day someone “simplifies” the matcher to join on timestamp.

Add one more test that closes a period, records the checksum, then attempts a forbidden mutation — inserting a backdated entry into the closed window — and asserts that a re-close either refuses or produces a different checksum. This is the test that proves your immutability guarantee is real rather than aspirational, and a green result is what lets you tell an auditor that a closed period cannot be silently rewritten, backed by a test id rather than a promise.

Frequently Asked Questions

Why not just store a running balance and trust the gateway? Because a single mutable balance has no audit trail and no way to detect when it drifts. A double-entry ledger records why the balance is what it is, and reconciliation proves it against external cash. When they disagree you get an alert; with a bare balance you get a surprise at audit.

Should debits be positive or negative? Pick one convention and enforce it everywhere. This guide uses debits positive, credits negative, so the only invariant is that legs of a transaction sum to zero. The sign convention matters less than applying it consistently and asserting the zero-sum in code.

How do I reconcile when payouts net out fees and refunds? Post every component as its own ledger leg — charge, fee, refund — rather than recording only the net payout. Then the payout reconciles arithmetically: payout amount should equal the sum of charge legs minus fee legs minus refund legs in that batch. Netting is only a problem when you fail to record the components.

Can I edit a wrong ledger entry? No. Entries are append-only. To correct an error, post a reversing transaction that cancels the bad one and then post the correct transaction. The mistake and its correction both remain visible, which is exactly what an auditor wants.

How often should reconciliation run? At least daily, aligned to the gateway’s settlement cadence. Run it as a scheduled job that ingests the latest settlement report, matches, and reports the unmatched count. Period close (monthly or per your accounting calendar) is a stricter gate that requires zero open discrepancies.

What amount tolerance should the matcher allow? For same-currency lines, zero — an exact signed-integer equality on minor units. Any tolerance on domestic amounts hides real bugs, because there is no legitimate way for a same-currency charge to reconcile off by a cent. The one documented exception is cross-currency settlement, where the gateway’s FX conversion and yours can differ by a single minor unit of rounding. There, allow a one-unit bucket, book the residue explicitly to an fx_rounding account so it stays visible in the ledger, and alert if the accumulated residue in that account grows faster than transaction volume would explain — a growing rounding balance means a systematic conversion error, not innocent rounding.

How do I reconcile a refund that exceeds the original charge’s remaining balance? You should not be able to, and the ledger is where you enforce it. Before posting a refund transaction for invoice_id inv_44921, sum the existing charge and refund legs tied to that invoice’s external_ref; if the refund would drive the net below zero, reject it as an over-refund rather than posting a leg that makes the customer’s receivable negative. Gateways do enforce this too, but relying solely on the gateway means a race between two concurrent refund requests can slip a double refund through. Checking the ledger balance inside the same transaction that posts the refund closes that race with the database’s own isolation guarantees.

Where should processing fees show up in revenue? Nowhere near it. A fee is an expense, posted to processor_fees, and revenue is recognized gross of fees. If you net fees out of revenue at posting time, your recognized revenue silently tracks your processor’s pricing, and a fee-schedule change looks like a revenue change in every downstream report. Keeping the fee on its own leg means gross revenue, fee expense, and net cash are three separately queryable numbers, which is exactly what both your finance team and the reconciliation matcher need.

Related

• Building an Immutable Double-Entry Billing Ledger in PostgreSQL
• PostgreSQL vs Event-Sourced Ledger for Billing
• Reconciling Stripe Payouts Against Your Ledger
• Handling Multi-Currency FX Gain/Loss in the Ledger
• Using the Outbox Pattern for Reliable Billing Events
• Building Idempotent Webhook Handlers in Node.js
• Tax, Compliance & Revenue Recognition