Append-Only Audit Log in PostgreSQL
Who changed what and when, using triggers, monthly partitions and hash-chained rows.
- Runs out of the box: yes
- Tests: yes
- CI: yes
- Docs: yes
- Used in production: yes
Purpose#
A reusable set of SQL migrations that adds an append-only audit table to any PostgreSQL application. Row-level triggers record the old and new values of audited tables, the table is partitioned by month, and each row stores a hash of the previous row so tampering with history is detectable.
The repository includes a verification function that walks the chain and reports the first broken link, plus a retention script that detaches and archives partitions older than the configured window.
What you will learn#
- Attach audit triggers to existing tables through a repeatable migration
- Partition an audit table by month and detach old partitions safely
- Verify a hash chain and interpret a reported break
Technologies & features#
- Technologies
- SQLPostgreSQL
- Features
- Audit LoggingSchema Migration
- Topics
- Authorization & SecurityprimaryData & Persistence
Lessons learned#
- Worked
Writing audit rows from triggers instead of application code meant that bulk fixes run from a SQL console were captured too.
- Didn’t work
Hashing the whole row as JSON was slow on wide tables. Hashing a fixed list of columns per table cut insert overhead noticeably.
- Limitation
Superusers can still disable triggers. The chain proves tampering after the fact; it does not prevent it.
Try it#
git clone https://git.example.internal/business-systems/pg-audit-log.git
cd pg-audit-log
docker compose up -d db
./scripts/apply-migrations.sh
psql -h localhost -U demo -c "select * from audit.verify_chain();"Commands come from the owner’s README. Credentials are never stored here.
