ADR-0002: PostgreSQL AFTER triggers for audit trails

Status

Accepted

Tags

database, postgresql, audit, compliance, triggers, leave

Decision

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

Single shared trigger function 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 TRIGGER pair in createAuditTriggers() in migrator.go
  • Use CREATE OR REPLACE FUNCTION for the trigger function (idempotent)
  • Use DROP TRIGGER IF EXISTS + CREATE TRIGGER for per-table triggers (idempotent; CREATE TRIGGER IF NOT EXISTS requires 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 of fn_audit_trail(), which drops the named keys from old_data/new_data. Today only customers.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'

Rollback

No application code changes required — zero-downtime rollback.