ADR-0002: PostgreSQL AFTER triggers for audit trails
Status
AcceptedTags
database, postgresql, audit, compliance, triggers, leaveDecision
Use PostgreSQL AFTER triggers to capture row-level change history for compliance-sensitive tables. Phase 1 scope:leave_requests, leave_balances, leave_types. Phase 2 added customers, orders, order_items; the rental module registers its own tables in migrator_rental.go.
Why
After migrating from MongoDB to PostgreSQL, the leave module had business-level audit logging (“who did what?”) but no database-level change tracking (“what changed in this row?”). Compliance teams need queryable before/after snapshots without parsing logs or rebuilding history from application events. AFTER triggers fire in the same transaction — if the write rolls back, the audit entry rolls back too. No application code changes required; the trigger fires transparently on every INSERT/UPDATE/DELETE.Enabled approach
pgaudit rejected because it requires shared_preload_libraries restart and ships logs to files.
Application code rejected because it requires instrumentation at every call site — error-prone.
Schema
fn_audit_trail() using TG_TABLE_NAME and to_jsonb(OLD/NEW) — one function covers all tables, no per-table duplication. fn_audit_trail_omit() is the same function with the trigger arguments (TG_ARGV) removed from the snapshots.
Rules for agents
- Never instrument application code to write audit trail entries — the trigger handles it automatically
- New tables requiring audit coverage: add a
DROP TRIGGER IF EXISTS+CREATE TRIGGERpair increateAuditTriggers()inmigrator.go - Use
CREATE OR REPLACE FUNCTIONfor the trigger function (idempotent) - Use
DROP TRIGGER IF EXISTS+CREATE TRIGGERfor per-table triggers (idempotent;CREATE TRIGGER IF NOT EXISTSrequires PG 17+) - Snapshots are full rows. The one exception is a column that is large and carries no history value: register the trigger with
fn_audit_trail_omit('<column>', ...)instead offn_audit_trail(), which drops the named keys fromold_data/new_data. Today onlycustomers.embedding(a re-generated JSON vector) is omitted. - Audit trails are append-only — never UPDATE or DELETE rows from
audit_trails
Bad pattern (do not generate)
Good pattern
Phase scope
Phase 1 (done):leave_requests, leave_balances, leave_types — sync AFTER triggers.
Phase 2 (done, issue #195): customers, orders, order_items — sync AFTER triggers, the same as leave and rental. The earlier plan to audit these through the outbox (audit.AuditHelper → NATS → audit.AsyncConsumer) was built, never wrote a row (the consumer listened on a different subject than the relay published), and was removed. One mechanism, in the same transaction, with no call site to forget.
Phase 3 (future): REST query endpoint GET /api/audit-trails?table=...&row_id=...&from=...&to=...
Phase 4 (future): Retention policy — DELETE FROM audit_trails WHERE changed_at < NOW() - INTERVAL '5 years'