PostgreSQL Audit Logging with Triggers in 2026: Change Data Capture and Temporal Tables

Every SOC 2, HIPAA, and GDPR audit asks the same question: "Who changed what, and when?" Application-level logging misses changes made directly in the database, during batch jobs, or via admin scripts. PostgreSQL triggers solve this at the database level—every row change captured automatically, regardless of which application path caused it.

This post covers two complementary approaches: a generic audit trigger that captures before/after row images for any table, and PostgreSQL temporal tables (via system-time versioning) that keep full row history queryable as time-travel queries.

---

Approach 1: Generic Audit Log Table

A single audit_log table captures changes across all tracked tables:

-- migrations/20260101_audit_log.sql

CREATE TABLE audit_log (
  id              BIGSERIAL PRIMARY KEY,
  
  -- What changed
  table_name      TEXT NOT NULL,
  row_id          TEXT NOT NULL,        -- PK of the changed row (cast to text)
  operation       TEXT NOT NULL,        -- INSERT | UPDATE | DELETE
  
  -- Who changed it
  app_user_id     UUID,                 -- From session variable (set by app)
  app_user_email  TEXT,
  db_user         TEXT NOT NULL DEFAULT current_user,
  app_name        TEXT,                 -- application_name from pg_stat_activity
  client_ip       INET,
  
  -- What it looked like
  old_data        JSONB,                -- NULL for INSERTs
  new_data        JSONB,                -- NULL for DELETEs
  changed_fields  TEXT[],              -- Column names that changed (UPDATE only)
  
  -- When
  changed_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  transaction_id  BIGINT NOT NULL DEFAULT txid_current()
);

-- Partition by month for performance (audit tables grow fast)
-- In practice, use pg_partman or manual monthly partitions:
-- CREATE TABLE audit_log_2027_01 PARTITION OF audit_log
--   FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');

CREATE INDEX idx_audit_table_row ON audit_log(table_name, row_id, changed_at DESC);
CREATE INDEX idx_audit_user ON audit_log(app_user_id, changed_at DESC)
  WHERE app_user_id IS NOT NULL;
CREATE INDEX idx_audit_time ON audit_log(changed_at DESC);
CREATE INDEX idx_audit_transaction ON audit_log(transaction_id);

---

The Generic Audit Trigger Function

-- One trigger function handles all tables
CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER  -- Run as owner, not the calling user
AS $$
DECLARE
  v_old_data    JSONB;
  v_new_data    JSONB;
  v_changed     TEXT[];
  v_row_id      TEXT;
  v_user_id     UUID;
  v_user_email  TEXT;
BEGIN
  -- Read app-set session variables (your app sets these on each DB connection)
  BEGIN
    v_user_id    := current_setting('app.current_user_id', TRUE)::UUID;
    v_user_email := current_setting('app.current_user_email', TRUE);
  EXCEPTION WHEN OTHERS THEN
    v_user_id    := NULL;
    v_user_email := NULL;
  END;

  IF TG_OP = 'INSERT' THEN
    v_new_data := to_jsonb(NEW);
    v_old_data := NULL;
    v_row_id   := NEW.id::TEXT;  -- Assumes tables have an `id` column
    v_changed  := NULL;

  ELSIF TG_OP = 'UPDATE' THEN
    v_old_data := to_jsonb(OLD);
    v_new_data := to_jsonb(NEW);
    v_row_id   := NEW.id::TEXT;
    
    -- Compute which fields changed
    SELECT array_agg(key ORDER BY key) INTO v_changed
    FROM (
      SELECT key
      FROM jsonb_each(v_new_data) n
      WHERE n.value IS DISTINCT FROM (v_old_data -> n.key)
    ) changed;

  ELSIF TG_OP = 'DELETE' THEN
    v_old_data := to_jsonb(OLD);
    v_new_data := NULL;
    v_row_id   := OLD.id::TEXT;
    v_changed  := NULL;
  END IF;

  -- Skip if nothing actually changed (UPDATE with identical values)
  IF TG_OP = 'UPDATE' AND (v_changed IS NULL OR array_length(v_changed, 1) = 0) THEN
    RETURN NULL;
  END IF;

  -- Redact sensitive columns before storing
  IF v_old_data IS NOT NULL THEN
    v_old_data := v_old_data
      - 'password_hash'
      - 'stripe_secret_key'
      - 'two_factor_secret';
  END IF;
  IF v_new_data IS NOT NULL THEN
    v_new_data := v_new_data
      - 'password_hash'
      - 'stripe_secret_key'
      - 'two_factor_secret';
  END IF;

  INSERT INTO audit_log (
    table_name, row_id, operation,
    app_user_id, app_user_email, db_user, app_name,
    old_data, new_data, changed_fields
  ) VALUES (
    TG_TABLE_NAME, v_row_id, TG_OP,
    v_user_id, v_user_email, session_user,
    current_setting('application_name', TRUE),
    v_old_data, v_new_data, v_changed
  );

  RETURN NULL; -- AFTER triggers ignore return value
END;
$$;

-- Attach to tables you want to audit
CREATE TRIGGER audit_projects
  AFTER INSERT OR UPDATE OR DELETE ON projects
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

CREATE TRIGGER audit_tasks
  AFTER INSERT OR UPDATE OR DELETE ON tasks
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

CREATE TRIGGER audit_team_members
  AFTER INSERT OR UPDATE OR DELETE ON team_members
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

CREATE TRIGGER audit_subscriptions
  AFTER INSERT OR UPDATE OR DELETE ON subscriptions
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

---

Setting User Context from Application

// lib/db/audit-context.ts
import { db } from "@/lib/db";

/**
 * Set per-connection session variables so triggers know who made the change.
 * Call this in middleware or at the start of each authenticated request.
 */
export async function withAuditContext<T>(
  userId: string,
  userEmail: string,
  fn: () => Promise<T>
): Promise<T> {
  return db.$transaction(async (tx) => {
    // Set session variables — visible to trigger functions
    await tx.$executeRaw`
      SELECT
        set_config('app.current_user_id', ${userId}, TRUE),
        set_config('app.current_user_email', ${userEmail}, TRUE)
    `;
    // TRUE = session-local (scoped to this transaction)

    return fn();
  });
}

// Usage in API route:
export async function updateProject(userId: string, userEmail: string, projectId: string, updates: any) {
  return withAuditContext(userId, userEmail, async () => {
    return db.project.update({
      where: { id: projectId },
      data: updates,
    });
    // Trigger fires automatically — audit_log gets the change with user context
  });
}

---

Querying the Audit Log

// lib/audit/queries.ts
import { db } from "@/lib/db";

export async function getRowHistory(tableName: string, rowId: string) {
  return db.$queryRaw<Array<{
    id: bigint;
    operation: string;
    app_user_email: string | null;
    old_data: Record<string, unknown> | null;
    new_data: Record<string, unknown> | null;
    changed_fields: string[] | null;
    changed_at: Date;
  }>>`
    SELECT
      id,
      operation,
      app_user_email,
      old_data,
      new_data,
      changed_fields,
      changed_at
    FROM audit_log
    WHERE table_name = ${tableName}
      AND row_id = ${rowId}
    ORDER BY changed_at DESC
    LIMIT 100
  `;
}

export async function getUserActivity(userId: string, since?: Date) {
  return db.$queryRaw<Array<{
    table_name: string;
    row_id: string;
    operation: string;
    changed_fields: string[] | null;
    changed_at: Date;
  }>>`
    SELECT table_name, row_id, operation, changed_fields, changed_at
    FROM audit_log
    WHERE app_user_id = ${userId}::uuid
      ${since ? db.$raw`AND changed_at > ${since}` : db.$raw``}
    ORDER BY changed_at DESC
    LIMIT 200
  `;
}

// What changed between two timestamps (compliance query)
export async function getChangesInWindow(start: Date, end: Date, tableName?: string) {
  return db.$queryRaw`
    SELECT
      table_name,
      row_id,
      operation,
      app_user_email,
      changed_fields,
      changed_at
    FROM audit_log
    WHERE changed_at BETWEEN ${start} AND ${end}
      ${tableName ? db.$raw`AND table_name = ${tableName}` : db.$raw``}
    ORDER BY changed_at
  `;
}

---

Approach 2: Temporal Tables (Full Row History)

For tables where you want true time-travel queries ("show me what this row looked like at 3pm yesterday"):

-- Temporal table pattern: main table + history table
-- (PostgreSQL doesn't have built-in temporal tables yet — simulate with triggers)

-- Main table
CREATE TABLE project_settings (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id  UUID NOT NULL REFERENCES projects(id),
  settings    JSONB NOT NULL DEFAULT '{}',
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by  UUID REFERENCES users(id),
  
  -- System-time columns
  valid_from  TIMESTAMPTZ NOT NULL DEFAULT now(),
  valid_to    TIMESTAMPTZ NOT NULL DEFAULT 'infinity'::timestamptz,
  
  -- Only one current row per project (valid_to = infinity)
  CONSTRAINT project_settings_current EXCLUDE USING gist (
    project_id WITH =,
    tstzrange(valid_from, valid_to) WITH &&
  )
);

-- History table — same structure, no constraints
CREATE TABLE project_settings_history (LIKE project_settings);
CREATE INDEX idx_psh_project_time ON project_settings_history(project_id, valid_from DESC);

-- Trigger: before update, copy current row to history
CREATE OR REPLACE FUNCTION versioned_update_fn()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Close the current version
  NEW.valid_from := now();
  NEW.valid_to   := 'infinity'::timestamptz;

  -- Archive the old version
  OLD.valid_to := now();
  INSERT INTO project_settings_history VALUES (OLD.*);

  RETURN NEW;
END;
$$;

CREATE TRIGGER versioned_update
  BEFORE UPDATE ON project_settings
  FOR EACH ROW EXECUTE FUNCTION versioned_update_fn();

-- Trigger: on delete, archive to history
CREATE OR REPLACE FUNCTION versioned_delete_fn()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  OLD.valid_to := now();
  INSERT INTO project_settings_history VALUES (OLD.*);
  RETURN OLD;
END;
$$;

CREATE TRIGGER versioned_delete
  BEFORE DELETE ON project_settings
  FOR EACH ROW EXECUTE FUNCTION versioned_delete_fn();

Time-travel queries:

-- What did project settings look like at 2027-01-15 14:30?
SELECT *
FROM project_settings
WHERE project_id = 'proj-123'
  AND valid_from <= '2027-01-15 14:30:00+00'
  AND valid_to > '2027-01-15 14:30:00+00'

UNION ALL

SELECT *
FROM project_settings_history
WHERE project_id = 'proj-123'
  AND valid_from <= '2027-01-15 14:30:00+00'
  AND valid_to > '2027-01-15 14:30:00+00';

-- Show all versions of this row
SELECT
  settings,
  updated_by,
  valid_from,
  CASE WHEN valid_to = 'infinity' THEN NULL ELSE valid_to END AS valid_to,
  CASE WHEN valid_to = 'infinity' THEN TRUE ELSE FALSE END AS is_current
FROM (
  SELECT * FROM project_settings WHERE project_id = 'proj-123'
  UNION ALL
  SELECT * FROM project_settings_history WHERE project_id = 'proj-123'
) versions
ORDER BY valid_from DESC;

---

Performance Considerations

-- Audit log grows fast — partition by month
ALTER TABLE audit_log RENAME TO audit_log_default;

CREATE TABLE audit_log (
  LIKE audit_log_default INCLUDING ALL
) PARTITION BY RANGE (changed_at);

CREATE TABLE audit_log_2027_01 PARTITION OF audit_log
  FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');

CREATE TABLE audit_log_2027_02 PARTITION OF audit_log
  FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');

-- Auto-create future partitions with pg_partman extension:
-- SELECT partman.create_parent('public.audit_log', 'changed_at', 'native', 'monthly');

-- For high-write tables: insert audit rows async via LISTEN/NOTIFY
-- (reduces write amplification on hot tables)

---

Cost and Timeline

---

See Also

• SaaS Audit Logging — Application-layer audit logs (complements DB triggers)
• PostgreSQL Row-Level Security — Row-level access control
• PostgreSQL Advisory Locks — Coordinating migrations on audited tables
• SaaS GDPR Data Export — Using audit logs for DSAR responses

---

Working With Viprasol

We implement audit logging and compliance infrastructure for SaaS products — from database-level triggers through application audit trails and GDPR-ready data export pipelines. Our team has built audit systems for products requiring SOC 2 and HIPAA compliance.

What we deliver:

• Generic audit trigger covering all tracked tables
• Session variable injection for user-attributed changes
• Temporal table implementation for time-travel queries
• Audit log partitioning for long-term retention
• Compliance reporting queries for SOC 2 and GDPR audits

Explore our web development services or contact us to add database-level audit logging to your product.