ADR-0002: PostgreSQL AFTER triggers for audit trails

Status

Accepted

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

Approach	Atomic?	Queryable?	App changes?	Choice
AFTER triggers	Yes	Yes (table)	None	Chosen
pgaudit	No (async logs)	Files only	Requires log shipper	Rejected
Application code	Yes	Yes (table)	Every use case	Rejected

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.

Architecture Decision Records

Digital Worker

Fieldforce Mobile (Flutter)

0002 postgresql after triggers for audit trails

ADR-0002: PostgreSQL AFTER triggers for audit trails

Status

Tags

Decision

Why

Enabled approach

Schema

Rules for agents

Bad pattern (do not generate)

Good pattern

Phase scope

Rollback

​ADR-0002: PostgreSQL AFTER triggers for audit trails

​Status

​Tags

​Decision

​Why

​Enabled approach

​Schema

​Rules for agents

​Bad pattern (do not generate)

​Good pattern

​Phase scope

​Rollback

ADR-0002: PostgreSQL AFTER triggers for audit trails

Status

Tags

Decision

Why

Enabled approach

Schema

Rules for agents

Bad pattern (do not generate)

Good pattern

Phase scope

Rollback