# Audit log: triggers see what app code misses
Compliance and debugging both need "who changed this row and when". App-level logging misses dashboard edits, admin scripts, and any path that forgot to log. A trigger on the table catches all of them.
## Checkable procedure
1. Create an `audit_log` table: id, table_name, row_id, action (insert/update/delete), actor (from `auth.uid()`, null for service-role writes), old_data and new_data as jsonb, created_at.
2. Write one generic trigger function using TG_TABLE_NAME and TG_OP so the same function serves every audited table. Record the full row jsonb on insert and delete, and the changed fields on update.
3. Attach it `after insert or update or delete` on each table that needs history. Keep the audited table list explicit and reviewed; auditing everything is a write-amplification problem.
4. RLS on the audit table: append-only for the system, readable only by admins or auditors. Nobody except the purge policy should update or delete audit rows.
5. Plan retention: audit tables grow forever. A pg_cron purge job archiving rows older than your retention window keeps the table usable.
## Ordering constraints
Audit table and function before attaching triggers. Attach before the feature launches; backfilling history that was never recorded is impossible.
## Verification
Make a change through the app, one through the dashboard SQL editor, and one via the API. All three must appear in the audit log with the right actor and diff.