The App Balanced the Ledger. The Database Refused the Write.

I added a check constraint to a ledger table after a code review passed an unbalanced write. The TypeScript types were correct, the unit tests were green, and the postings still summed to nonzero. The database refused the commit, which was the first time in that project the rule held without anyone remembering to enforce it.

The ledger was a small double entry core in Node and TypeScript in strict mode. Each business event wrote two or more postings inside one Postgres transaction, debits positive, credits negative, and the transaction had to sum to zero. The audit table was append only. That was the whole design, and the bug was that every part of it lived in application code.

Types stop at the process boundary

Strict TypeScript helps inside the process. It does not help once the rows leave it. A migration, a backfill script, a manual fix in a console, a second writer added six months later, each of them can insert postings without ever importing the type that was supposed to protect the invariant.

I kept the types for readability and shape errors. I stopped treating them as enforcement. Enforcement sits one layer down, where every writer passes through it.

If the invariant must hold when the app has a bug and when someone runs SQL by hand, it belongs in Postgres. Balance belongs in Postgres.

The balance check lives in one transaction

The schema has a ledger entry header and a postings table. Amounts are integer minor units, so cents or their equivalent, stored as bigint, never float. Each posting references its entry, an account, and a signed amount.

create table ledger_entries (
  id uuid primary key default gen_random_uuid(),
  business_key text not null unique,
  created_at timestamptz not null default now()
);

create table ledger_postings (
  id uuid primary key default gen_random_uuid(),
  entry_id uuid not null references ledger_entries(id),
  account text not null,
  amount_minor bigint not null check (amount_minor <> 0)
);

create index on ledger_postings (entry_id);

The balance rule cannot be a row check because no single row is balanced on its own. It is a property of the set. Two shapes work. One is a deferred constraint trigger that runs at commit time and asserts the sum per entry is zero. Deferred matters because within the transaction the rows arrive one insert at a time, and each intermediate state looks unbalanced until the last posting lands.

create or replace function assert_entry_balanced()
returns trigger language plpgsql as $$
begin
  if exists (
    select 1 from ledger_postings
    where entry_id = new.entry_id
    group by entry_id
    having sum(amount_minor) <> 0
  ) then
    raise exception 'unbalanced ledger entry %', new.entry_id;
  end if;
  return new;
end $$;

drop trigger if exists trg_balanced on ledger_postings;
create constraint trigger trg_balanced
after insert or update on ledger_postings
deferrable initially deferred
for each row execute function assert_entry_balanced();

The second shape is an insert wrapped in a transaction that writes the full set and verifies the sum before commit. I run both. The trigger is the backstop. The explicit check is the readable error.

What this removes is a quiet failure. A developer adds a fee posting and forgets the offsetting leg. Review misses it. The happy path test passes. The commit fails in integration with the entry id attached, before merge rather than at month end.

Money stays in integer minor units

If a float touches a monetary amount, rounding stops being deterministic. A percentage fee computed in float and rounded at the wrong step leaves one minor unit unaccounted for, and the balance trigger fails on entries that look correct in the logs.

Amounts cross boundaries as integers with explicit currency. Display formatting happens once at the edge from the integer. Parsing happens once at the edge into the integer. Everything between is integer arithmetic.

import { z } from "zod";

const MoneyMinor = z.object({
  currency: z.string().length(3),
  amountMinor: z.number().int().safe(),
});

type MoneyMinor = z.infer<typeof MoneyMinor>;

function addFee(total: MoneyMinor, basisPoints: number): MoneyMinor {
  const fee = Math.round((total.amountMinor * basisPoints) / 10_000);
  return { currency: total.currency, amountMinor: total.amountMinor + fee };
}

There is no float in that path. The rounding point is explicit and it is the only one. When the trigger complains, look first for new arithmetic that added a second rounding step.

Audit rows are append only by trigger, not by agreement

An audit log the application promises not to update is a convention. It holds until the first incident where an update would be convenient. Postgres can make it a property.

create table audit_log (
  id bigserial primary key,
  actor text not null,
  action text not null,
  payload jsonb not null,
  created_at timestamptz not null default now()
);

create or replace function refuse_audit_mutation()
returns trigger language plpgsql as $$
begin
  raise exception 'audit_log is append only';
  return null;
end $$;

drop trigger if exists trg_audit_no_update on audit_log;
create trigger trg_audit_no_update
before update or delete on audit_log
for each row execute function refuse_audit_mutation();

Retention is the same idea in reverse. A scheduled delete that removes too much is a normal bug with abnormal consequences. A guard that refuses deletes younger than the retention window turns that bug into a failed job with a clear message.

Every control needs a test that tries the forbidden thing

Happy path tests prove the feature works when used correctly. They do not prove the control exists. Each rule above ships with a test that attempts the forbidden write and asserts Postgres refuses it.

test("unbalanced postings are refused", async () => {
  const entry = await createEntry(db, { businessKey: "pay_test_001" });
  await expect(
    db.insertPostings(entry.id, [
      { account: "cash", amountMinor: 1000 },
      { account: "revenue", amountMinor: -999 },
    ])
  ).rejects.toThrow(/unbalanced/i);
});

test("audit rows cannot be updated", async () => {
  const row = await db.insertAudit({ actor: "system", action: "hold", payload: {} });
  await expect(db.updateAudit(row.id, { action: "release" })).rejects.toThrow(/append only/i);
});

test("idempotency key replays safely", async () => {
  const key = "quote_test_001";
  const first = await service.quote(key, input);
  const second = await service.quote(key, input);
  expect(second.id).toBe(first.id);
});

That last test bridges to the integration layer. Mutating endpoints take idempotency keys, and a retry with the same key returns the original record. The database side is a unique key on the idempotency column, which turns a double submit into a conflict the handler maps back to the first result.

Orchestration is quote, validate, commit, submit, settle, reverse, with postings at each transition. Reconciliation holds through hold, release, and reversal because it sums postings rather than trusting a status column. A status can lie after a partial failure. A balanced set of postings cannot, because any lie would have to balance.

The timeout case is where this pays for itself. A screening call times out between quote and commit, so the system does not know whether the vendor acted. The safe move is to hold the payment in pending under the idempotency key, log the attempt, retry with the same key on a schedule, and reconcile against the vendor on read. Never mark unknown as failed and never replay without the key.

What I would tell someone building this

Put the balance rule in Postgres, not in review. Review catches the bug you can see. A deferred trigger catches the branch you did not know existed.

Store money as integers and round once. The second rounding step is where the missing minor unit comes from, and the trigger will find it before your tests do.

Make the audit log physically append only. A trigger that refuses updates is shorter than the policy document and it works on weekends.

Test the refusal, not just the path. Each constraint ships with a test that tries to break it. If that test does not exist, the constraint is a rumor.

Reconcile from postings, not from status. Statuses describe what the code hoped. Postings describe what the money did.