An invoice is a snapshot, not a calculation

An issued invoice is a record of what you told a customer, not a function of today's data. If you store inputs and recompute totals on read, a new tax rate, a rounding fix or a changed discount policy rewrites documents that already left your system. Freeze every computed amount as integer minor units, record the rule version and exact inputs, make issued rows insert-only, and correct with new linked documents. I learned this on cloud pre-accounting and e-invoice systems; it applies to any system that issues financial documents.

#Why does recomputing totals break?

A draft total is a calculation. An issued total is a fact: it sits in a PDF, an e-invoice file and the counterparty's ledger, and tax records are usually kept for years, depending on the jurisdiction. Recompute-on-read changes it behind all three, using rates, rules and code that have since moved on.

• Rates. If a rate was 8% at issue and is 10% today, Example A below goes from 0.44 tax to 0.55 per document.
• Rounding. A rounding fix, or tax moved from per line to per document, shifts old documents by a cent.
• Discounts. If a tier that gave 10% off at five units now starts at ten, old invoices lose a discount the customer got.

A stored integer needs none of that reconstructed.

#What do I freeze?

The results are the document; inputs and rules are evidence of how they were derived.

Amounts are bigint minor units with a currency code. Floats fail quietly: in JavaScript, Math.round(1.005 * 100) returns 100, because the product is 100.49999999999999. The decimal answer is a tie, and half-up gives 101.

#Why do per-line and per-total rounding disagree?

Example A: five lines at 1.10, tax 8%. Per line, 1.10 times 0.08 is 0.088, which rounds to 0.09; five lines give 0.45. Per document, net 5.50 times 0.08 is 0.44. Neither is wrong. They are different rules, and your market or your customer's system expects one.

Example B: four lines at 1.05, 1.15, 1.25 and 1.35, tax 10%. Per-line tax in cents is 10.5, 11.5, 12.5 and 13.5, all exact ties. Half-up gives 11, 12, 13, 14: 0.50. Half-even sends each tie to the even neighbor: 10, 12, 12, 14: 0.48, the exact tax on the 4.80 total. Half-up pushes every tie the same way, so error accumulates; half-even cancels on average.

Which row is correct depends on the rule set: scope and mode are columns there, and the document points at it.

The currency exponent matters too: ISO 4217 gives USD 2 decimals, JPY 0 and KWD 3. The integer math is identical; the outcome is not: five lines of 1.100 KWD at 8% are 0.088 each, exact, so both scopes give 0.440. Reject input with extra decimals instead of rounding it. Keep the exponent on the row; ISO has changed one (CLF went from 0 to 4 decimals in 2014).

#What does the freezing code look like?

export type Mode = "half_even" | "half_up";
export interface Rules {
  version: string; // rule_sets id
  scope: "line" | "document";
  mode: Mode;
}
export interface LineInput {
  qtyMilli: bigint; // 1.5 units = 1500n
  unitMinor: bigint;
  discountBp: number; // 1000 = 10.00%
  taxRateBp: number; // 825 = 8.25%
}

function divRound(n: bigint, d: bigint, mode: Mode): bigint {
  if (n < 0n || d <= 0n) throw new RangeError("divRound domain");
  const q = n / d;
  const twice = (n % d) * 2n;
  if (twice < d) return q;
  if (twice > d) return q + 1n;
  return mode === "half_up" || q % 2n === 1n ? q + 1n : q;
}

export function freeze(currency: string, rules: Rules, input: LineInput[]) {
  const { mode, scope } = rules;
  const lines = input.map((l) => {
    const grossMinor = divRound(l.qtyMilli * l.unitMinor, 1000n, mode);
    const discountMinor = divRound(grossMinor * BigInt(l.discountBp), 10000n, mode);
    const netMinor = grossMinor - discountMinor;
    const taxMinor =
      scope === "line" ? divRound(netMinor * BigInt(l.taxRateBp), 10000n, mode) : null;
    return { ...l, grossMinor, discountMinor, netMinor, taxMinor };
  });

  const byRate = new Map<number, { net: bigint; lineTax: bigint }>();
  for (const l of lines) {
    const e = byRate.get(l.taxRateBp) ?? { net: 0n, lineTax: 0n };
    byRate.set(l.taxRateBp, { net: e.net + l.netMinor, lineTax: e.lineTax + (l.taxMinor ?? 0n) });
  }
  const taxByRate = [...byRate]
    .sort(([a], [b]) => a - b) // fixed order keeps the stored JSON stable
    .map(([rateBp, e]) => ({
      rateBp,
      taxableMinor: e.net,
      taxMinor: scope === "line" ? e.lineTax : divRound(e.net * BigInt(rateBp), 10000n, mode),
    }));

  const netMinor = lines.reduce((s, l) => s + l.netMinor, 0n);
  const taxMinor = taxByRate.reduce((s, t) => s + t.taxMinor, 0n);
  return { currency, rules, lines, taxByRate, netMinor, taxMinor, totalMinor: netMinor + taxMinor };
}

The code uses bigint throughout: no clock, locale or float, and a sorted per-rate breakdown, so equal inputs give equal results. With several rates, "per document" means per rate: sum net by rate, round once. JSON.stringify throws on bigint, so store amounts as strings; node-postgres returns bigint columns as strings too.

A CI job re-freezes old documents' stored inputs and compares them with the stored results. A mismatch means a code change would have rewritten history; the stored value wins.

#How does PostgreSQL enforce it?

CREATE TABLE rule_sets (
  id             text PRIMARY KEY,
  rounding_scope text NOT NULL CHECK (rounding_scope IN ('line', 'document')),
  rounding_mode  text NOT NULL CHECK (rounding_mode IN ('half_even', 'half_up')),
  rules          jsonb NOT NULL
);

CREATE TABLE number_counters (
  tenant_id uuid NOT NULL,
  series    text NOT NULL,
  last_seq  bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (tenant_id, series)
);

CREATE TABLE documents (
  id              uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  kind            text NOT NULL CHECK (kind IN ('invoice', 'credit_note')),
  series          text NOT NULL,
  seq_no          bigint NOT NULL CHECK (seq_no > 0),
  corrects_id     uuid REFERENCES documents (id),
  currency        text NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
  minor_exponent  smallint NOT NULL CHECK (minor_exponent BETWEEN 0 AND 4),
  rule_set_id     text NOT NULL REFERENCES rule_sets (id),
  engine_version  text NOT NULL,        -- git sha
  inputs          jsonb NOT NULL,
  inputs_sha256   bytea NOT NULL,
  idempotency_key text NOT NULL,
  net_minor       bigint NOT NULL CHECK (net_minor >= 0),
  tax_minor       bigint NOT NULL CHECK (tax_minor >= 0),
  total_minor     bigint NOT NULL,
  tax_by_rate     jsonb NOT NULL,       -- per rate; amounts as strings
  issued_at       timestamptz NOT NULL DEFAULT clock_timestamp(),
  CHECK (total_minor = net_minor + tax_minor),
  CHECK ((kind = 'invoice') = (corrects_id IS NULL)),
  UNIQUE (tenant_id, series, seq_no),
  UNIQUE (tenant_id, idempotency_key)
);

CREATE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE EXCEPTION '% on % is not allowed', TG_OP, TG_TABLE_NAME; END $$;

CREATE TRIGGER no_change BEFORE UPDATE OR DELETE ON documents
  FOR EACH ROW EXECUTE FUNCTION forbid_change();
-- same trigger on document_lines, rule_sets and document_events

A document_lines table (not shown) holds each line's frozen gross, discount, net, tax rate and tax, with tax_minor NULL when tax is per document. CHECK constraints reject an inconsistent row, such as net = gross - discount. They cannot span rows, so "lines sum to the document net" needs a deferred constraint trigger or a read-back in the issuing transaction. jsonb drops key order, whitespace and duplicate keys, so hash a canonical form (sorted keys, no whitespace) that you can rebuild from the stored value.

The trigger is a guard rail; privileges are the boundary. The application role does not own the tables: it gets INSERT and SELECT on all of them except number_counters, where it gets SELECT and UPDATE. An owner can disable or drop triggers, and row triggers do not fire on TRUNCATE; privileges cover both. Anything that changes after issue (sent, paid, submitted to a gateway) goes into an append-only document_events table.

#How do I issue once, with numbers that do not skip?

Idempotency. The client sends a key, and UNIQUE (tenant_id, idempotency_key) stops a retry issuing twice. The same key with the same input hash returns the stored document; a different hash is an error. For concurrent duplicates the second waits, then fails with 23505; roll back and re-read the winner.

Gapless numbers. Sequences are not gapless: per the PostgreSQL documentation, aborted transactions and crashes leave gaps, and INSERT ... ON CONFLICT calls nextval before it detects a conflict. For a gapless series, use a counter row:

UPDATE number_counters SET last_seq = last_seq + 1
WHERE tenant_id = $1 AND series = $2 RETURNING last_seq;

Create the row when you create the series; zero rows returned means no such series. Run the update first in the issuing transaction, then insert. The row lock lasts until commit, so issuers queue, numbers follow commit order, and a rollback gives the number back. Under READ COMMITTED, the default, a waiter re-reads the committed row; REPEATABLE READ would fail it with a serialization error. Stamp issued_at with clock_timestamp(), as the table does: now() is the transaction start, so a waiter could carry an earlier date than the number before it.

The cost is one issuer at a time per series, so keep the transaction short: no PDF rendering or gateway calls before commit. Whether a series must be gapless is a jurisdiction question: ask the accountant, and make it a per-series setting.

#How do corrections work?

An issued document is never edited or deleted. A full reversal is a numbered credit_note whose corrects_id points at the original, with the original's stored amounts copied, not recomputed. A partial credit defaults to the original's rule set, so tax reverses at the rate charged; check your market's rules. A correction that raises the amount is a new invoice or a debit note, depending on the market. The adjusted total is derived (original total minus linked credit notes), never an updated column. The open balance also subtracts payments, which live outside the document.

#When is recalculation fine, and what does this cost?

• Drafts, quotes, carts. Recompute freely. Freeze at issue, or at acceptance if a quote must hold its price.
• Migrations. You cannot backfill history without disabling the guard, so new columns are nullable with a documented meaning for legacy rows. A frozen bug is fixed forward with corrections.
• Price scale. Unit prices are whole minor units here; 1.459 per liter needs a scale column.
• The alternative. Bitemporal rules plus a pinned engine can reproduce old documents, but every old engine must stay runnable.

#Checklist

• Amounts are bigint minor units, with currency and exponent on the row.
• Store the rule set id, engine version and exact inputs.
• Issued rows are insert-only: a trigger for errors, privileges for enforcement.
• One idempotency key per issue; a counter row if numbers must be gapless.
• Corrections are linked credit notes; status changes are events.
• A replay test compares stored results with a fresh freeze.