Why our audit logs are append-only at the database level

HB 1605 expects districts to keep a tamper-proof audit trail of every parent-facing preview, every compliance check, every decision that touches a teacher's portfolio. Application-level discipline is not enough for that bar. Any developer with commit access could write a one-line patch that quietly mutates a log row, and the next code review might not catch it. We wanted a guarantee that an audit log row, once written, would survive a sleepy developer accident — and any sufficiently motivated insider too.

The answer turned out to be a few lines of Postgres. Our compliance tables — primarily planning_shield_logs, tia_audit_logs, and credit_ledger — are append-only at the database level via BEFORE UPDATE and BEFORE DELETE triggers that raise an exception unconditionally. The application has insert privileges. It does not have update or delete privileges, full stop. Even a service-role bypass cannot get past the trigger because the trigger fires before the row is touched.

Why a trigger and not RLS

Row-level security can deny an update at the policy layer, but RLS is opt-in and bypassable by the service role we use for backend writes. The whole point of the audit log is to be writable by the backend but not mutable afterward. A trigger fires regardless of role, regardless of session settings, regardless of the JWT in flight. It is the only construct in Postgres that runs on every row mutation with no escape hatch short of dropping the trigger itself — which is a privileged DDL operation that shows up in the database audit trail.

The shape of the trigger

The trigger function is intentionally boring. It does not check the user, does not check the row, does not allow exceptions. It raises a generic cannot modify append-only table error and that is the entire body. We attach two triggers per table — one for BEFORE UPDATE, one for BEFORE DELETE. Inserts are unaffected. This means migrations that try to backfill data in place will fail, which is the correct behavior: backfills should add new rows that supersede old ones, not silently rewrite history.

Verifying the guarantee

There is a subtle gotcha that bit us during the first round of tests: a BEFORE UPDATE or BEFORE DELETE trigger only fires when at least one row matches the WHERE clause. The naive test of running UPDATE planning_shield_logs SET x = 1 WHERE id = -1 against an empty table proves nothing — zero rows match, the trigger never runs, the query succeeds with zero rows affected. We now verify the guarantee with a different shape: insert a real row inside a transaction, attempt the mutation, expect the exception, raise our own rollback marker to discard the test row. That confirms the trigger fires in the happy path, not just the empty-table path.

What this buys us

For HB 1605, it means the parent-preview log cannot be back-dated. For TIA, it means the audit trail on every approval, edit, or import is permanent. For credits, it means a debit cannot be quietly reversed without leaving a counter-credit row that shows in every report. None of this is a substitute for application-level logging, but it is the floor that the application logs sit on top of. The discipline lives in the database, not in the next pull request.