Ledger invariants the database enforces, not your prose

Real Postgres in the test

No Docker, no server, no fixture database — PGlite runs Postgres in-process. Every rejection on this page is Postgres rejecting it: real CHECK constraints, a real deferred constraint trigger, real REVOKE against a real role.

The check is right, and it is never asked

await expect(postThroughTheApp(db, UNBALANCED)).rejects.toThrow(OutOfBalance);

Nothing wrong with that code. It works, and every test of the posting path is green because of it.

Then the same rows arrive through the other door:

await postDirectly(db, UNBALANCED);

expect(await lineCount(db)).toBe(1);      // half an entry, posted
expect(await balanceOf(db, "bank")).toBe(40);

The ledger no longer balances and nothing raised its voice. The check did not fail — it was never asked.

That door is not an attacker. It is a migration, an admin console, a report job that needed to backfill a couple of rows, a second service pointed at the same database. A colleague, with credentials.

Move the rule to where every writer passes

await expect(postDirectly(db, UNBALANCED)).rejects.toThrow(/out of balance by 40.00/);
expect(await lineCount(db)).toBe(0);

Same rows, same connection, same absence of an application. Who called it stops being a question anybody has to get right.

The detail that decides whether the rule survives

An entry is written line by line, so it is out of balance between its first INSERT and its last. A check that ran per statement would make every correct posting impossible — and a rule that blocks correct work is dropped within the month.

CREATE CONSTRAINT TRIGGER entry_must_balance
  AFTER INSERT ON ledger_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_entry_balanced();

Deferred, it asks at COMMIT, when the transaction has said everything it has to say:

await postThroughTheApp(db, BALANCED);
expect(await lineCount(db)).toBe(2);

This is also why the balance rule cannot be a CHECK: a CHECK sees one row, and this invariant is about a set of them.

One invariant is not the invariant

await expect(postDirectly(db, [ …, debit: "-40.00", …, credit: "-40.00" ])).rejects
  .toThrow(/amounts_not_negative/);

That pair sums to zero. The balance rule is satisfied and the entry is still nonsense — a negative debit is a credit in a disguise, and every report that groups by side is wrong. So is a line that is a debit and a credit:

CONSTRAINT amounts_not_negative CHECK (debit >= 0 AND credit >= 0),
CONSTRAINT one_side_only        CHECK ((debit = 0) <> (credit = 0))

Three ways to be wrong, and you are protected only from the ones you wrote down.

Append-only is a privilege, not a method

await expect(db.exec("UPDATE ledger_entries SET debit = '400.00' …")).rejects.toThrow(/permission denied/);
await expect(db.exec("DELETE FROM ledger_entries …")).rejects.toThrow(/permission denied/);

GRANT SELECT, INSERT ON ledger_entries TO app;
REVOKE UPDATE, DELETE ON ledger_entries FROM app;

Not a repository that declines, not a convention: the role the application connects as has no such privilege, so neither does anything holding those credentials — including the console somebody opens at midnight.

Be honest about the limit: the table's owner and a superuser are not bound by this, and no REVOKE will bind them. What it buys is that violating the rule requires deliberately connecting as somebody else — an act with a name, that leaves a trace, that nobody performs by accident during a backfill.

The same table without the rules

await postDirectly(db, [{ … debit: "-40.00", credit: "-40.00" … }]);
await db.exec("UPDATE ledger_entries SET debit = '999.00' …");
await db.exec("DELETE FROM ledger_entries …");

expect(await lineCount(db)).toBe(0);

Out of balance, negative, on both sides at once, then edited, then gone. Six lines, no error — with the same application code sitting on top, all its checks intact, and none of them asked.

Where this comes from

niiko keeps a double-entry ledger where money is concerned, and its rule is that no invariant about money lives only in application code. sql/enforced.sql is the shape the real migration takes.

Related: append-only-that-blocked-itself — what REVOKE UPDATE, DELETE costs the day you have to correct a genuine mistake, and why it is still right.

License

Apache-2.0 — see LICENSE. This is a demonstration, not a package. Copy what you need.

---

Built by Vorluno — a software studio from Panamá.