Advanced Features Practice: Audit System

What You'll Build

You will build a complete, application-agnostic audit system using the module's features together: a generic trigger function that captures every INSERT, UPDATE, and DELETE as a JSONB before/after snapshot into a central audit table, attached to whichever tables you want tracked, with a scheduled pg_cron job that enforces a retention policy on the audit history. It is a realistic, self-contained system that no application can bypass.

The design uses one reusable PL/pgSQL trigger function (driven by TG_OP and TG_TABLE_NAME), JSONB to store arbitrary row shapes, triggers to make capture automatic, and pg_cron to purge old audit rows. By the end you will have a tamper-resistant change history that records who changed what, when, and how — the kind of audit trail compliance and debugging both demand.

Prerequisites

• Completion of lessons 21–24, or equivalent familiarity with JSONB, PL/pgSQL, triggers, and pg_cron.
• PostgreSQL 15+ with superuser access to create the pg_cron extension (or an external scheduler as a fallback).
• Permission to create tables, functions, and triggers.
• One or more business tables to audit (the example uses accounts and players).
• Comfort reading JSONB and writing a basic PL/pgSQL function.

Setup & Project Structure

Create a single central audit_log table that can hold changes from any table: columns for the source table name, the operation, the timestamp, the user, and two JSONB columns for the old and new row states. Using JSONB means one audit table serves every audited table regardless of their differing columns.

Index the audit table for the queries you will run against it — by table and time, and optionally a GIN index on the JSONB if you will search within changes. Keep it in its own schema if you want to lock down access separately from the business tables.

CREATE TABLE audit_log (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name  text        NOT NULL,
    operation   text        NOT NULL,           -- INSERT / UPDATE / DELETE
    changed_at  timestamptz NOT NULL DEFAULT now(),
    changed_by  text        NOT NULL DEFAULT current_user,
    old_row     jsonb,
    new_row     jsonb
);

-- Query patterns: by table over time, and optional containment search
CREATE INDEX idx_audit_table_time ON audit_log (table_name, changed_at DESC);
CREATE INDEX idx_audit_new_gin   ON audit_log USING gin (new_row jsonb_path_ops);

Step 1 — Write the Generic Audit Trigger Function

Write one PL/pgSQL function, RETURNS trigger, that works for any table. Use TG_OP to know the operation and TG_TABLE_NAME for the source, and serialise OLD and NEW with to_jsonb, recording old_row for UPDATE/DELETE and new_row for INSERT/UPDATE. Because it is an AFTER trigger, its return value is ignored, so RETURN NULL.

The genius of to_jsonb(OLD) and to_jsonb(NEW) is that the function needs no knowledge of any table's columns — it captures whatever shape the row has. This single function will audit every table you attach it to, which is exactly what makes the system reusable.

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_log(table_name, operation, old_row, new_row)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
    );
    RETURN NULL;                       -- AFTER trigger: return value ignored
END;
$$ LANGUAGE plpgsql;

Step 2 — Attach Triggers to the Audited Tables

Attach the function to each table you want audited with an AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW trigger. The same function serves them all; you simply create one trigger per table. Choose AFTER so you record durable changes, and FOR EACH ROW so each changed row is captured individually with its values.

If you audit tables that receive large bulk updates, consider a statement-level variant with transition tables for efficiency, but per-row is the simplest correct default for typical write volumes. Verify that inserting, updating, and deleting a row each produces the expected audit_log entry.

CREATE TRIGGER trg_audit_accounts
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit_changes();

CREATE TRIGGER trg_audit_players
AFTER INSERT OR UPDATE OR DELETE ON players
FOR EACH ROW EXECUTE FUNCTION audit_changes();

-- Smoke test: each of these should create one audit_log row
INSERT INTO accounts(id, balance) VALUES (1, 100);
UPDATE accounts SET balance = 150 WHERE id = 1;
DELETE FROM accounts WHERE id = 1;

Step 3 — Schedule Retention with pg_cron

An audit log grows forever, so add a retention policy. Enable pg_cron and schedule a daily job that deletes audit rows older than your retention window — say one year. The DELETE is idempotent and bounded by the date predicate, making it safe to run repeatedly on the schedule.

For very large audit tables, consider partitioning audit_log by month (from Lesson 14) so retention becomes an instant DROP of an old partition instead of a large DELETE; the pg_cron job would then drop expired partitions and create upcoming ones. Either way, the schedule keeps the audit history bounded without manual intervention.

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Daily at 02:30, purge audit rows older than one year (idempotent, bounded)
SELECT cron.schedule('audit-retention', '30 2 * * *',
    $$DELETE FROM audit_log WHERE changed_at < now() - interval '1 year'$$);

-- Confirm the job is scheduled and watch its outcomes
SELECT jobname, schedule, command FROM cron.job WHERE jobname = 'audit-retention';
SELECT jobname, status, start_time FROM cron.job_run_details
WHERE jobname = 'audit-retention' ORDER BY start_time DESC LIMIT 5;

Step 4 — Testing & Verification

Exercise the whole system: perform inserts, updates, and deletes on the audited tables and confirm each produces a correctly typed audit_log row with the right old_row/new_row JSONB. Then query the audit trail to reconstruct a row's history, and confirm the retention job is scheduled and (by lowering the interval briefly) actually removes old rows.

-- Reconstruct the full change history of one account from the audit log
SELECT operation, changed_at, changed_by,
       old_row ->> 'balance' AS old_balance,
       new_row ->> 'balance' AS new_balance
FROM audit_log
WHERE table_name = 'accounts' AND (old_row ->> 'id' = '1' OR new_row ->> 'id' = '1')
ORDER BY changed_at;

-- Verify a containment search works via the GIN index
SELECT count(*) FROM audit_log WHERE new_row @> '{"balance": 150}';

-- Confirm retention removes old rows (test with a short window first)
DELETE FROM audit_log WHERE changed_at < now() - interval '1 year';

• One generic PL/pgSQL trigger function (using TG_OP, TG_TABLE_NAME, to_jsonb) can audit any table.
• to_jsonb(OLD)/to_jsonb(NEW) capture arbitrary row shapes, so a single JSONB audit table serves every table.
• Use AFTER … FOR EACH ROW triggers for durable per-row capture; the function returns NULL.
• Attach the same function to each audited table with one trigger per table.
• Schedule retention with a pg_cron DELETE (or partition + DROP) to keep the audit log bounded.
• Audit triggers add write overhead — use statement-level/transition tables for bulk tables and audit selectively.