ERP Audit Log in PostgreSQL: Schema, Trigger & UI

A single, month-partitioned audit table for every audited entity.

CREATE TABLE audit_log (
  id            bigint GENERATED ALWAYS AS IDENTITY,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  table_name    text        NOT NULL,
  record_id     text        NOT NULL,
  action        text        NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  actor_id      text,                 -- app user, NULL for system jobs
  reason        text,                 -- optional, e.g. 'credit note #CN-104'
  old_data      jsonb,
  new_data      jsonb,
  changed_cols  text[],               -- UPDATE only: which columns changed
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE audit_log_2026_10 PARTITION OF audit_log
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- The query you'll run most: history of one record, newest first
CREATE INDEX ON audit_log (table_name, record_id, occurred_at DESC);

Generic audit trigger: attach it to any table with a primary key column named id.

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_old jsonb := CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END;
  v_new jsonb := CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END;
  v_changed text[];
BEGIN
  IF TG_OP = 'UPDATE' THEN
    SELECT array_agg(n.key) INTO v_changed
    FROM jsonb_each(v_new) n
    WHERE n.value IS DISTINCT FROM v_old -> n.key;

    IF v_changed IS NULL THEN
      RETURN NEW; -- nothing changed, nothing to log
    END IF;
  END IF;

  INSERT INTO audit_log (table_name, record_id, action, actor_id, reason,
                         old_data, new_data, changed_cols)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(v_new ->> 'id', v_old ->> 'id'),
    TG_OP,
    NULLIF(current_setting('app.user_id', true), ''),
    NULLIF(current_setting('app.reason', true), ''),
    v_old, v_new, v_changed
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER invoices_audit
AFTER INSERT OR UPDATE OR DELETE ON invoices
FOR EACH ROW EXECUTE FUNCTION audit_row_change();

Run every write inside a transaction that tells PostgreSQL who is acting (node-postgres).

import { Pool, type PoolClient } from "pg";

const pool = new Pool();

export async function withActor<T>(
  actor: { userId: string; reason?: string },
  work: (db: PoolClient) => Promise<T>,
): Promise<T> {
  const db = await pool.connect();
  try {
    await db.query("BEGIN");
    // third argument true = transaction-local, safe with connection pooling
    await db.query("SELECT set_config('app.user_id', $1, true)", [actor.userId]);
    await db.query("SELECT set_config('app.reason', $1, true)", [actor.reason ?? ""]);
    const result = await work(db);
    await db.query("COMMIT");
    return result;
  } catch (err) {
    await db.query("ROLLBACK");
    throw err;
  } finally {
    db.release();
  }
}

// Usage in a route handler
await withActor({ userId: req.user.id, reason: "Discount approved by manager" }, (db) =>
  db.query("UPDATE invoices SET discount = $1 WHERE id = $2", [discount, invoiceId]),
);

A minimal record-history timeline driven by changed_cols.

type AuditEntry = {
  id: string;
  occurred_at: string;
  action: "INSERT" | "UPDATE" | "DELETE";
  actor_name: string | null;
  reason: string | null;
  changed_cols: string[] | null;
  old_data: Record<string, unknown> | null;
  new_data: Record<string, unknown> | null;
};

const VERB = { INSERT: "created", UPDATE: "updated", DELETE: "deleted" } as const;

export function RecordHistory({ entries }: { entries: AuditEntry[] }) {
  return (
    <ol className="space-y-4">
      {entries.map((e) => (
        <li key={e.id}>
          <p>
            <strong>{e.actor_name ?? "System"}</strong>{" "}
            {VERB[e.action]} this record on{" "}
            <time dateTime={e.occurred_at}>{new Date(e.occurred_at).toLocaleString()}</time>
            {e.reason && <> · {e.reason}</>}
          </p>
          {e.action === "UPDATE" && (
            <ul>
              {e.changed_cols?.map((col) => (
                <li key={col}>
                  {col}: {String(e.old_data?.[col] ?? "—")} → {String(e.new_data?.[col] ?? "—")}
                </li>
              ))}
            </ul>
          )}
        </li>
      ))}
    </ol>
  );
}