PostgreSQL Audit Trails with Triggers

In enterprise software engineering, tracking historical changes to sensitive business data is a strict regulatory requirement (SOC 2, ISO 27001, GDPR). Relying on application code to record audit entries before or after executing database updates is notoriously fragile—a bug or direct database admin intervention bypasses application logging entirely.

PostgreSQL provides a robust native solution using PL/pgSQL Event Triggers and JSONB Data Types. By capturing every INSERT, UPDATE, and DELETE directly within the database engine, PostgreSQL generates immutable, tamper-proof audit trails. In this article, we build an automated PL/pgSQL auditing system that computes JSONB column deltas and captures session metadata.

1. Designing the Immutable Audit Log Schema

The audit log table must record the affected schema name, target table, operation type (INSERT, UPDATE, DELETE), previous data state (OLD), new data state (NEW), changed field deltas, execution timestamp, and initiating user identity:

-- Immutable Central Audit Table
CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    schema_name TEXT NOT NULL,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
    record_id TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_fields JSONB,
    performed_by TEXT DEFAULT current_setting('app.current_user', true),
    client_ip INET DEFAULT inet_client_addr(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- Index audit log table for fast temporal and table lookups
CREATE INDEX idx_audit_logs_table_record 
ON audit_logs (table_name, record_id);

CREATE INDEX idx_audit_logs_created_at 
ON audit_logs (created_at DESC);

2. Calculating JSONB Field Deltas

Storing full snapshots of OLD and NEW rows for every minor update leads to rapid database growth. To optimize storage, we write a helper function that compares two JSONB objects and extracts only modified attributes:

-- Function to calculate JSONB deltas (differences between OLD and NEW)
CREATE OR REPLACE FUNCTION jsonb_diff(val_old JSONB, val_new JSONB)
RETURNS JSONB 
LANGUAGE sql 
IMMUTABLE 
AS $$
SELECT jsonb_object_agg(new_keys.key, new_keys.value)
FROM jsonb_each(val_new) AS new_keys
WHERE val_old -> new_keys.key IS DISTINCT FROM new_keys.value;
$$;

3. Building the Generic PL/pgSQL Audit Trigger

Next, we write a single, reusable trigger function that automatically attaches to any database table without hardcoding column names:

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER 
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    old_json JSONB := NULL;
    new_json JSONB := NULL;
    delta_json JSONB := NULL;
    rec_id TEXT := NULL;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        old_json := to_jsonb(OLD);
        new_json := to_jsonb(NEW);
        delta_json := jsonb_diff(old_json, new_json);
        
        -- Retrieve primary key dynamically (assumes column named 'id')
        rec_id := COALESCE(new_json ->> 'id', old_json ->> 'id');
        
    ELSIF (TG_OP = 'DELETE') THEN
        old_json := to_jsonb(OLD);
        rec_id := old_json ->> 'id';
        
    ELSIF (TG_OP = 'INSERT') THEN
        new_json := to_jsonb(NEW);
        rec_id := new_json ->> 'id';
    END IF;

    -- Record immutable audit log entry
    INSERT INTO audit_logs (
        schema_name,
        table_name,
        operation,
        record_id,
        old_data,
        new_data,
        changed_fields
    ) VALUES (
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME,
        TG_OP,
        rec_id,
        old_json,
        new_json,
        delta_json
    );

    RETURN NULL; -- AFTER trigger returns NULL
END;
$$;

4. Attaching the Audit Trigger to Target Tables

To enable auditing on any production table (e.g. user_accounts), attach an AFTER INSERT OR UPDATE OR DELETE trigger:

-- Attach audit trigger to user_accounts table
CREATE TRIGGER audit_user_accounts_trigger
AFTER INSERT OR UPDATE OR DELETE ON user_accounts
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

5. Archiving Audit Logs with Declarative Partitioning

Because high-throughput tables generate millions of audit records monthly, partition the audit_logs table by range using created_at (e.g. monthly partitions). Legacy partitions can be detached and archived to cheap cloud storage (Amazon S3 / DigitalOcean Spaces) to preserve query speed on active tables.

Conclusion

Implementing native PostgreSQL audit trails using PL/pgSQL triggers and JSONB deltas ensures comprehensive compliance auditing. By capturing state mutations inside the database engine, software systems gain immutable data history that cannot be bypassed.