An append-only, hash-chained audit log in the database

Decision

Security-relevant events go to an audit_log table that database triggers make append-only (every UPDATE and DELETE aborts). Each entry stores the SHA-256 of the previous entry's hash plus its own fields, starting from a fixed genesis value, and prev_hash is unique so concurrent writers can't fork the chain. Admins see the log in /admin/records with a chain check run on every view, and can export it as CSV. Client IPs are stored only as an HMAC with the signing key.

Events recorded: sign-in (success, failure, throttled), demo sign-in, sign-out, sign-up (with the 2022 status code on failure), password change and rehash, session rotation, sign-out everywhere, CSRF refusals, rate-limit refusals, codes minted and borrowed, invites mailed, profile updates, email confirmations, record exports, sandbox SQL runs and AI decisions.

Context

The first revival had no audit trail: Vercel's function logs are short-lived and not something an admin of the app can browse. The upgrade adds features (AI, SQL) where "who did what, when" matters, and the governance pillar of this project calls for a record that can't be quietly rewritten.

Options considered

1. Console logs only. Free, but short retention and no way to show them in the app.
2. An external log service. Better retention and search, but another account and another place for data to live.
3. A plain database table. Easy, but any bug or admin script could edit history.
4. A table with append-only triggers and a hash chain (chosen).
5. The same, with each entry signed (HMAC) instead of plainly hashed. Stronger against someone who can write to the database, but the key lives in the same environment.

Why

Triggers stop the application (or a careless script) from changing history. The hash chain catches what triggers can't: an edit made around them, straight on the database file, shows up at the exact entry that was changed. A unique prev_hash makes a fork impossible, so writers don't need an interactive transaction (expensive on Turso): if two append at once, one gets a unique-constraint error and retries on the new head.

What happened

• Tests show 25 concurrent writers produce one unbroken chain, that UPDATE and DELETE fail at the database, and that an edit made after dropping the trigger is reported at the edited entry. A property test checks 300 random chains with one tampered field each.
• Writes are best-effort: if the log write fails, the action still completes and the failure goes to the server console. Refusing a sign-in over a logging error seemed worse for members, but it means the log can have gaps.
• Honest limit: anyone who can write to the database can delete the triggers, change an entry and recompute every hash after it. A hash chain without an anchor outside the database proves the log is internally consistent, not that it is authentic.
• Reseeding the demo keeps the audit rows, because the triggers forbid deleting them. History of the demo outlives its data, by design.

What I'd change

• Anchor the chain: publish the head hash daily somewhere the database can't rewrite (a git commit, or object storage with retention locks).
• Add a retention policy and a documented way to archive old entries without breaking verification.
• Record the request id and route for each event to make cross-referencing easier.