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

• 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

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.

Implementation Walkthrough

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.

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.

Edge Cases & Failure Modes

Performance & Scale

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.

Testing Strategy

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.

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.

Related

• Building an Immutable Double-Entry Billing Ledger in PostgreSQL
• PostgreSQL vs Event-Sourced Ledger for Billing
• Using the Outbox Pattern for Reliable Billing Events
• Building Idempotent Webhook Handlers in Node.js
• Tax, Compliance & Revenue Recognition