Revenue Reconciliation & Billing Audits

Reconciliation is the control that proves what you metered, what you invoiced, and what you booked are the same number, and it closes the loop on the Tenant Billing & Usage Metering framework that produces all three. Without it, a metering pipeline is a plausible story about revenue rather than a measurement of it.

Multi-tenancy makes the problem structural rather than incidental. Usage is counted per tenant in one system, aggregated into subscription items in another, invoiced by a payment processor that is authoritative for money, and summarised into a ledger that finance and auditors read. Each hop is a place where a tenant's number can change: a dropped event, a late arrival counted in the wrong period, a proration that the local mirror never saw, a currency rounding rule applied twice. Individually each is small; in aggregate they are the difference between "we bill accurately" and "we think we bill accurately".

This page builds the three-way reconciliation, the drift taxonomy that turns a mismatch into an action, the evidence store that satisfies an auditor, and the operating thresholds that decide when a discrepancy is noise and when it is an incident.

Prerequisites

• [ ] A metering store with immutable, per-tenant event records and defined aggregation windows, per Usage Metering Event Pipelines.
• [ ] A local mirror of processor state — subscription status, invoice totals, and line items — kept current by webhooks.
• [ ] Deterministic period boundaries: an explicit billing period with a timezone, not "the last 30 days".
• [ ] Immutable ledger entries with a stable posting reference per invoice and per tenant.
• [ ] A processor account with read access to invoices and their line items for the reconciliation job.
• [ ] Decimal arithmetic everywhere — integer minor units for money, never floating point.
• [ ] An append-only evidence store where each reconciliation run's inputs, outputs, and hashes are retained.

The Three-Way Reconciliation

Two-way reconciliation is the trap. Comparing metering against invoices tells you the pipeline is self-consistent but says nothing about whether the money was booked; comparing invoices against the ledger tells you accounting matches billing but not whether billing matched reality. Only the triangle closes.

The third leg is the one teams skip and the one that finds the expensive bugs. Legs one and two can both pass while a price tier is applied incorrectly for every tenant on a plan, because the wrong number flows consistently through all three systems. Recomputing expected revenue from raw usage and the price book, independently of the billing code path, is what catches it.

Step-by-Step Implementation

Step 1 — Freeze the period before you reconcile it

A period that is still accepting late events cannot be reconciled, because every run produces a different answer. Define an explicit close: after the grace window, the period is sealed and any later event is booked to the next period with a reference back.

CREATE TABLE billing_period (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL,
  starts_at    timestamptz NOT NULL,
  ends_at      timestamptz NOT NULL,
  grace_until  timestamptz NOT NULL,      -- late events accepted until this instant
  sealed_at    timestamptz,
  UNIQUE (tenant_id, starts_at)
);

-- Only sealed periods are reconcilable; everything else is still moving.
CREATE VIEW reconcilable_period AS
  SELECT * FROM billing_period WHERE sealed_at IS NOT NULL;

The grace window is a business decision, not a technical one — typically 24 to 72 hours, long enough to absorb ordinary ingestion delay and short enough that finance can close the month. The handling of events that arrive after the seal is covered in handling late-arriving usage events with watermarks.

Step 2 — Compute the metering side deterministically

The aggregate must be reproducible: same inputs, same output, forever. That means aggregating from immutable event rows with an explicit period predicate, never from a mutable running counter.

-- Metered quantity per tenant, per meter, for one sealed period.
SELECT  e.tenant_id,
        e.meter_key,
        sum(e.quantity)          AS metered_quantity,
        count(*)                 AS event_count,
        md5(string_agg(e.event_id::text, ',' ORDER BY e.event_id)) AS input_digest
FROM    usage_event e
JOIN    reconcilable_period p
  ON    p.tenant_id = e.tenant_id
 AND    e.occurred_at >= p.starts_at
 AND    e.occurred_at <  p.ends_at
WHERE   p.id = $1
GROUP BY e.tenant_id, e.meter_key;

The input_digest is what makes the run auditable. Re-running reconciliation months later must produce the same digest; if it does not, someone mutated history, and that is a finding in its own right.

Step 3 — Pull the invoice side from the processor, not from your mirror

Your local mirror is a cache and can be stale in exactly the ways reconciliation exists to detect. Read invoices from the processor for the reconciliation leg, and treat any disagreement with the mirror as its own drift class.

// reconcile-invoice-leg.ts
import Stripe from 'stripe';

export async function invoiceSide(stripe: Stripe, customerId: string, period: { start: number; end: number }) {
  const totals = new Map<string, { quantity: number; amountMinor: number }>();
  for await (const inv of stripe.invoices.list({
    customer: customerId, created: { gte: period.start, lt: period.end }, expand: ['data.lines'],
  })) {
    if (inv.status === 'void' || inv.status === 'draft') continue;
    for (const line of inv.lines.data) {
      const key = line.price?.lookup_key ?? line.price?.id ?? 'unknown';
      const prev = totals.get(key) ?? { quantity: 0, amountMinor: 0 };
      totals.set(key, {
        quantity: prev.quantity + (line.quantity ?? 0),
        amountMinor: prev.amountMinor + line.amount,
      });
    }
  }
  return totals;
}

Skipping draft and void invoices is deliberate: a draft is not a commitment and a void is a reversal, and counting either produces phantom drift every month.

Step 4 — Recompute expected revenue independently for the third leg

Write the rating calculation a second time, from the price book and the metered quantities, in code that does not import the billing module. Independent reimplementation is the point; a shared helper would reproduce the bug on both sides.

# expected_revenue.py — deliberately does NOT import the billing service
from decimal import Decimal

def rate(metered: dict[str, int], price_book: dict[str, list[tuple[int, int]]]) -> int:
    """Return expected revenue in minor units. price_book maps meter -> [(upto, unit_minor)]."""
    total = Decimal(0)
    for meter, qty in metered.items():
        remaining, tiers = qty, price_book[meter]
        previous_ceiling = 0
        for upto, unit_minor in tiers:
            band = min(remaining, upto - previous_ceiling) if upto else remaining
            if band <= 0:
                break
            total += Decimal(band) * Decimal(unit_minor)
            remaining -= band
            previous_ceiling = upto
    return int(total)

Step 5 — Classify every difference, then act on the class

An unclassified mismatch is a number nobody can act on. Map each difference to a named drift class with an owner and a remedy, and store the classification with the run.

// classify.ts
type Drift =
  | 'quantity_short'      // metering > invoiced: usage was not reported
  | 'quantity_over'       // invoiced > metering: double-reported usage
  | 'unposted_invoice'    // invoice exists, no ledger entry
  | 'orphan_posting'      // ledger entry with no invoice
  | 'rating_mismatch'     // totals agree with each other but not with the price book
  | 'rounding'            // within tolerance, informational only
  | 'fx_variance';        // currency conversion difference

export function classify(m: Leg, i: Leg, l: Leg, tolMinor = 2): Drift | null {
  if (Math.abs(i.amountMinor - l.amountMinor) > tolMinor)
    return l.amountMinor === 0 ? 'unposted_invoice' : 'orphan_posting';
  if (m.quantity > i.quantity) return 'quantity_short';
  if (i.quantity > m.quantity) return 'quantity_over';
  if (Math.abs(i.amountMinor - m.expectedMinor) > tolMinor) return 'rating_mismatch';
  if (i.amountMinor !== m.expectedMinor) return 'rounding';
  return null;
}

Drift Classification Reference

Charted as a waterfall from metered revenue down to booked revenue, a typical month makes the relative size of each class obvious — and shows why the small classes are the ones worth automating away.

rating_mismatch is the only class that should stop a billing run. It is rarely one tenant: if the price book and the billing code disagree, they disagree for every tenant on that plan, and issuing the invoices before fixing it multiplies the number of credit notes you will write.

Dynamic Query Scoping & Reconciliation Runs

Reconciliation reads across tenants by design, which makes it one of the few legitimate cross-tenant workloads on the platform — and therefore one that needs deliberate constraints. Run it under a dedicated role with read-only access to the tables it needs, log every run with the operator or schedule that triggered it, and never let it write to operational tables. Corrections flow back as new events or new ledger entries, never as edits.

Scale the run by tenant batch rather than by one enormous query. A platform with tens of thousands of tenants reconciling a month of usage will otherwise build a join that spills to disk and holds a snapshot open for hours, which blocks vacuum and inflates bloat on the very tables billing depends on. Batching by a few hundred tenants keeps each transaction short and makes partial failure resumable — important, because the run must be idempotent: reconciling the same sealed period twice must produce identical results and no side effects.

-- Resumable batch: reconcile the next N unreconciled sealed periods.
WITH batch AS (
  SELECT id FROM billing_period
  WHERE  sealed_at IS NOT NULL
    AND  id NOT IN (SELECT period_id FROM reconciliation_run WHERE status = 'complete')
  ORDER  BY sealed_at
  LIMIT  200
  FOR UPDATE SKIP LOCKED
)
SELECT id FROM batch;

FOR UPDATE SKIP LOCKED lets several workers share the queue without coordinating, and SKIP LOCKED means a stuck worker delays one batch rather than the whole close. The same tenant-scoping discipline used everywhere else still applies inside each batch: the aggregate query carries an explicit tenant predicate so a bug cannot mix two tenants' usage into one invoice comparison.

Security Enforcement & Access Control

Billing data is the most attractive target on the platform after credentials, and reconciliation touches all of it at once.

Separation of duties is the control auditors probe hardest, and the one most often absent in engineering-led billing systems. If the same person can change the price book, run the correction, and approve the credit note, the reconciliation produces evidence about the system but none about the process. Put approval of any correction above a threshold with finance, and record the approval alongside the run.

Operational Overhead & Scaling Metrics

Drift measured as a share of revenue is the number to put in front of leadership, but the count of affected tenants is the number that predicts support load. A single enterprise tenant with a 4% discrepancy is one difficult conversation; four hundred tenants each 0.1% wrong is a public incident, even though the revenue impact is smaller. Alert on both.

Pitfalls & Anti-Patterns

• Reconciling an open period: Every run returns a different answer while late events keep arriving, so nobody can tell drift from ingestion lag. Seal the period after an explicit grace window, and reconcile only sealed periods.

• Comparing against your local mirror instead of the processor: The mirror is populated by the same webhook path that reconciliation exists to verify, so agreement proves nothing. Read invoices from the processor for the invoice leg.

• Sharing the rating code between billing and the audit: A shared helper reproduces its own bug on both sides of the comparison and reports zero drift. Reimplement the expected-revenue calculation independently from the price book.

• Floating-point money: Accumulating a few hundred thousand line items in floats produces cent-level drift that looks exactly like a real defect and wastes days. Use integer minor units, or a decimal type, end to end.

• Netting positive and negative drift: Two tenants wrong by equal and opposite amounts sum to zero and disappear from the report while both customers are billed incorrectly. Always report absolute drift and affected-tenant counts.

• Correcting by editing history: Updating a metering row or an invoice mirror to make the numbers agree destroys the audit trail and guarantees the next reconciliation disagrees with the ledger. Corrections are new records with a reference to what they correct.

Frequently Asked Questions

How often should reconciliation run? Daily for the current open period as an early-warning signal, and definitively after each period is sealed. The daily run is not authoritative — the period is still moving — but it catches a broken usage-reporting job within a day instead of at month end, when the only remedy left is a credit note. The post-seal run is the one whose output becomes evidence.

What level of drift is acceptable? Rounding-scale drift of a few minor units per tenant is inherent to tier boundaries and currency conversion, and should be classified and ignored. Anything above roughly 0.1% of period revenue, or affecting more than a couple of percent of tenants, is a defect rather than noise. The important discipline is that the threshold is written down in advance: a tolerance chosen after seeing the number is not a control.

Who is authoritative when metering and the processor disagree? It depends on the leg. For quantity, the metering store is authoritative because it holds the immutable record of what actually happened. For money, the processor is authoritative because it is the system that charged the customer and holds the settlement record. When those two conflict — you metered more than you invoiced — the resolution is a corrective invoice or credit, not an edit to either system.

How long should reconciliation evidence be kept? As long as the underlying financial records, which in most jurisdictions means seven to ten years. Retain the run's inputs digest, outputs, classification, and approvals rather than the raw event stream, so the volume stays manageable. This is also the reason a tenant's erasure request cannot delete billing records outright — the treatment of that conflict is set out in Tenant Offboarding & Data Retention Workflows.

Can reconciliation be fully automated? The detection and classification can and should be. The correction cannot, above a threshold, because auto-correcting revenue is exactly the capability an attacker or a buggy deploy would most like to have. Automate the safe classes — replaying a failed usage report before the seal, re-posting a missed invoice — and route anything that issues a credit, changes a price, or moves money to a human with an approval record.

Related

• Tenant Billing & Usage Metering
• Billing Sync with Stripe
• Usage Metering Event Pipelines
• Reconciling Stripe Webhooks per Tenant