​
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.

​
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

ApproachAtomic?Queryable?App changes?Choice
AFTER triggersYesYes (table)NoneChosen
pgauditNo (async logs)Files onlyRequires log shipperRejected
Application codeYesYes (table)Every use caseRejected
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

CREATE TABLE audit_trails (
  id          UUID PRIMARY KEY,
  table_name  VARCHAR(100)  NOT NULL,
  operation   VARCHAR(10)   NOT NULL,  -- INSERT, UPDATE, DELETE
  row_id      TEXT          NOT NULL,
  old_data    JSONB,                   -- NULL for INSERT
  new_data    JSONB,                   -- NULL for DELETE
  changed_at  TIMESTAMPTZ   NOT NULL
);

CREATE INDEX idx_audit_trails_table_changed ON audit_trails(table_name, changed_at DESC);
CREATE INDEX idx_audit_trails_table_row     ON audit_trails(table_name, row_id);
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.

​
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+)
  • Do not add old_data/new_data filtering in Phase 1 — full JSONB snapshots are correct
  • Audit trails are append-only — never UPDATE or DELETE rows from audit_trails

​
Bad pattern (do not generate)

// Don't write audit entries in application code
func (s *LeaveService) Approve(ctx context.Context, id string) error {
    if err := s.repo.Update(ctx, request); err != nil { return err }
    s.auditRepo.Insert(ctx, AuditTrail{...}) // wrong — trigger handles this
    return nil
}

// Don't use CREATE TRIGGER IF NOT EXISTS — requires PG 17
CREATE TRIGGER trg_audit_foo IF NOT EXISTS ...

​
Good pattern

// Application code is unchanged — trigger fires automatically
func (s *LeaveService) Approve(ctx context.Context, id string) error {
    return s.repo.Update(ctx, request) // trigger captures old/new data
}

// Idempotent trigger registration in migrator.go
DROP TRIGGER IF EXISTS trg_audit_leave_requests ON leave_requests;
CREATE TRIGGER trg_audit_leave_requests
  AFTER INSERT OR UPDATE OR DELETE ON leave_requests
  FOR EACH ROW EXECUTE FUNCTION fn_audit_trail();

​
Phase scope

Phase 1 (done): leave_requests, leave_balances, leave_types — sync AFTER triggers. Phase 2 (future): High-volume tables (orders, customers, products) — async via outbox pattern to avoid write amplification at scale. 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

DROP TRIGGER IF EXISTS trg_audit_leave_requests ON leave_requests;
DROP TRIGGER IF EXISTS trg_audit_leave_balances ON leave_balances;
DROP TRIGGER IF EXISTS trg_audit_leave_types    ON leave_types;
DROP FUNCTION IF EXISTS fn_audit_trail();
-- audit_trails table is left in place (harmless)
No application code changes required — zero-downtime rollback.