PostgreSQL Audit Trail

A lightweight, trigger-based audit trail system for PostgreSQL. Automatically tracks all data changes (INSERT, UPDATE, DELETE) across your tables with full context — who changed what, when, and from where.

Features

• Automatic change logging — captures every INSERT, UPDATE, and DELETE
• Data recovery — restore deleted rows or revert updates with audit.restore()
• Selective column exclusion — skip sensitive fields like password_hash
• Configurable events — choose which operations to track per table
• Custom primary keys — works with any primary key column name
• Error-safe — trigger errors are logged as warnings, never breaking your transactions
• Zero dependencies — pure PL/pgSQL, no extensions required

Requirements

• PostgreSQL 13+

Quick Start

1. Deploy the audit infrastructure

\i sql/deploy.sql

This creates the audit schema with the log table, indexes, functions, and views.

2. Configure permissions and attach tables

Edit sql/setup.sql to set your database username and choose which tables to audit:

SET LOCAL audit.db_username = 'your_db_user';

SELECT audit.attach('public.orders',  _events := '{INSERT,UPDATE,DELETE}');
SELECT audit.attach('public.users',   _events := '{INSERT,UPDATE,DELETE}', _excluded := '{password_hash}');
SELECT audit.attach('public.products', _events := '{UPDATE,DELETE}');

Then run it:

\i sql/setup.sql

Usage

Attach a table

-- Track all operations
SELECT audit.attach('public.orders');

-- Track only UPDATE and DELETE, exclude sensitive columns
SELECT audit.attach('public.users',
    _events   := '{UPDATE,DELETE}',
    _excluded := '{password_hash,api_token}'
);

-- Custom primary key column
SELECT audit.attach('public.legacy_table',
    _primary_key := 'order_id'
);

Detach a table

SELECT audit.detach('public.orders');

View audit history

-- Human-readable change log
SELECT * FROM audit.changes ORDER BY logged_at DESC LIMIT 20;

-- Filter by table
SELECT * FROM audit.changes
WHERE full_table_name = 'public.orders'
ORDER BY logged_at DESC;

-- Filter by record
SELECT * FROM audit.changes
WHERE full_table_name = 'public.users' AND pk_value = '42';

Restore a change

-- Restore a deleted row or revert an update
SELECT * FROM audit.restore(7842);

restored_table	restored_action	restored_pk_value	status
public.orders	RE-INSERT	123	OK

List audited tables

SELECT * FROM audit.info();

Schema

The audit.log table stores all tracked changes:

Column	Type	Description
id	BIGSERIAL	Primary key
transaction_id	BIGINT	PostgreSQL transaction ID
schema_name	TEXT	Source schema
table_name	TEXT	Source table
pk_column	TEXT	Primary key column name (default: id)
pk_value	TEXT	Primary key value of affected row
operation	TEXT	I = INSERT, U = UPDATE, D = DELETE
logged_at	TIMESTAMPTZ	Timestamp of the change
username	TEXT	Database session user
client_address	INET	Client IP address
snapshot	JSONB	Full row snapshot (before change for U/D)
changed	JSONB	Changed columns only (UPDATE)
restored	BOOLEAN	TRUE if reverted via audit.restore()

Project Structure

.
├── sql/
│   ├── deploy.sql    # Core audit infrastructure (schema, table, functions)
│   └── setup.sql     # Permission grants and table attachment config
├── CHANGELOG.md
├── CONTRIBUTING.md
├── LICENSE
└── README.md

License

MIT