An audit trail the application cannot rewrite

8 August 2026Majed Alandajani

An audit trail exists to answer one question: who did what, and when? The whole value sits in that answer staying put.

Most audit logs are protected by convention. Somewhere in the codebase lives an unwritten rule, "we do not write a delete against this table," and everyone hopes it holds. That is not protection, it is an agreement, and the first bug or the first new developer breaks it.

The alternative I rejected

My first idea was to forbid deletion inside the data access layer: one function writes to the log, no function deletes. That works right up until you remember it only covers paths that go through the layer. A direct statement in a migration or a maintenance script walks straight past it.

I did not want a rule that depends on everyone remembering it.

What I did instead

I moved the constraint into the engine:


CREATE TRIGGER trg_audit_no_update BEFORE UPDATE ON admin_audit_log
BEGIN SELECT RAISE(ABORT, 'audit log is append-only'); END;

CREATE TRIGGER trg_audit_no_delete BEFORE DELETE ON admin_audit_log
BEGIN SELECT RAISE(ABORT, 'audit log is append-only'); END;

Any attempt to update or delete now fails. The application, a migration, a script, an administrative console: the trigger treats them all the same.

What this actually buys

Suppose someone compromises the application and gains write access. The trail they leave stays written, because the database refuses to erase it. The same holds for my own mistakes: if I ship an accidental delete, it aborts at the engine.

The trap I fell into

I applied the same pattern to the proposal approvals table and my tests broke. Test data was being cleaned up by deleting rows, and deletion was now forbidden.

Disabling the trigger for tests would have defeated the whole exercise. I switched to creating a fresh database per test, which is better practice anyway, and the cleanup deletes disappeared with it.

How to be sure it works

Writing the trigger is half the job. Prove it fires:


assert.throws(() => db.exec("DELETE FROM admin_audit_log"), /append-only/);

Mine runs against the real migration files, not a simplified schema, so whatever passes the test is exactly what production enforces.