WORM enforcement: write-once-read-many for the journal
Auditors trust your ledger to the degree they trust nobody can edit history.
By Solomon Ajayi · Free to read, no signup
Your journal entries claim to be IMMUTABLE, once posted, never changed. But unless your database actively ENFORCES that, a rogue admin (or a panicked engineer) can UPDATE journal_line and silently rewrite history. Regulators won't trust ledger reports based on a 'we promise we don't update' policy. The real defense: WORM (Write-Once-Read-Many) enforcement at the database level. Tactics: revoke UPDATE/DELETE grants on the journal_line table from all application roles; add a Postgres trigger that RAISES on any UPDATE; periodically hash each row and write the hash to an append-only log; archive period-close snapshots to S3 with object lock. This lesson posts an entry and discusses the WORM checks running around it.
Every lesson in this course has insisted the journal is immutable, but immutability you only promise is not immutability. If the application role still holds UPDATE and DELETE grants on journal_line, then a rogue admin or a panicked engineer with database access can rewrite history, and your immutability is a convention enforced by good manners. An auditor cannot trust reports built on good manners, because the whole value of a ledger is that the past cannot be edited.
WORM, write-once-read-many, turns the promise into a property the system enforces. The first layer is permissions: revoke UPDATE and DELETE from the app role so the privilege to mutate simply does not exist. The second is a BEFORE-UPDATE trigger that raises on any attempt, so even a privileged session hits a wall reading WORM violation. The deposit in this lesson posts completely normally; the controls live around it, invisible until someone tries to change what was written.
The outermost layer assumes the inner ones might one day be bypassed. At period close you hash the journal (a row hash log, or a Merkle root over the period) and write it to object-locked storage in compliance mode, where even root credentials cannot delete it before the retention window expires. If anyone ever does alter a historical row, the next snapshot hash will not match the stored one, so tampering stops being silent and becomes detectable after the fact.
Worked example, step by step
Standard write: ₦10,000 deposit (subject to WORM controls)
The journal entry posts normally. INVISIBLE to the application: a Postgres trigger fires on the INSERT to compute a row hash and write it to a separate hash log. Any future UPDATE to this row would be rejected by another trigger.
| Account | Debit | Credit |
|---|---|---|
| Bank Account (1200) | ₦10,000.00 | |
| User Wallet (2000) | ₦10,000.00 |
Standard 2-line entry. The WORM controls are around it, not in the journal entry itself. If a DBA were to log into the database and run `UPDATE journal_line SET debit = 0 WHERE id = X;`, the BEFORE-UPDATE trigger fires and raises 'WORM violation: journal_line is append-only.'
Period close: snapshot hash to S3 with object-lock
At quarter-end, your close job computes the SHA-256 hash of the full journal_line table (or a Merkle root over the period) and writes it to an S3 object with COMPLIANCE-MODE object lock for 7 years. Even AWS root credentials can't delete the object before the lock expires.
| Account | Debit | Credit |
|---|---|---|
| Bank Account (1200) | ₦0.01 | |
| User Wallet (2000) | ₦0.01 |
No new journal entry. The snapshot hash is METADATA about the state of the journal at period close. If anyone manages to bypass the database WORM trigger and alters historical journal_line rows, the next snapshot hash will mismatch the previously-stored hash, auditors will catch it.
Takeaway
WORM enforcement turns 'we promise we don't update the journal' into 'we technically can't.' Multiple layers: (1) database-level GRANT removes UPDATE/DELETE from the journal_line table for the app role, (2) BEFORE-UPDATE trigger raises on any attempt, (3) period-close hash committed to object-locked storage so historical tampering is detectable. The regulator and the auditor will both ask about this; have an architecture diagram ready. The cost is low (one-time setup), the trust gain is enormous, and it's the single biggest control between you and the next 'ledger was rewritten' headline.
The code behind it
Two layers of WORM: revoke the grants, then trap any mutation at a trigger.
-- Layer 1: permissions. The app role can INSERT and SELECT, never mutate.
-- Even if the trigger below were dropped, the privilege simply does not exist.
GRANT SELECT, INSERT ON journal_line TO ledger_app;
REVOKE UPDATE, DELETE ON journal_line FROM ledger_app;
-- Layer 2: a trigger that traps mutations from ANY session, including a
-- privileged DBA who still holds UPDATE/DELETE. journal_line is append-only.
CREATE OR REPLACE FUNCTION reject_journal_mutation()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'WORM violation: journal_line is append-only'
USING ERRCODE = 'integrity_constraint_violation';
END;
$$;
CREATE TRIGGER journal_line_worm
BEFORE UPDATE OR DELETE ON journal_line
FOR EACH ROW
EXECUTE FUNCTION reject_journal_mutation();
-- Proof: the deposit posts fine; rewriting history is rejected at the wall.
INSERT INTO journal_line (account_code, debit, credit) -- 1200 Bank Account
VALUES ('1200', 1000000, 0), ('2000', 0, 1000000); -- 2000 User Wallet
-- UPDATE journal_line SET debit = 0 WHERE account_code = '1200';
-- ERROR: WORM violation: journal_line is append-onlyPractice this on a real ledger
Reading is half of it. Open this lesson in the lab to post the entries yourself against a real Postgres-backed double-entry ledger, with the validation on. Free, your sandbox is yours.