Audit log
An append-only record of who changed what, when, and to what value, so 'who did this?' has an answer months later.
See it
What it is
An audit log is an append-only table where every meaningful change gets its own immutable row: actor, action, target record, timestamp, and usually the before and after values. Rows are immutable for as long as they are retained: nothing updates them, and the only deletion allowed is a documented retention policy running on a schedule. That is the whole point, and it is also why 'our code only ever inserts' is not enough. Take UPDATE and DELETE privileges away from the application role at the database level, and when a regulator or a lawyer is the eventual reader, mirror the rows into append-only or tamper-evident storage (WORM object storage, hash-chained records) as well.
Three common ways to write one. Application level, where your service records the change alongside the write (easiest to enrich with request context like IP and user agent, easiest to forget). Database triggers, which catch every write including manual psql edits but know nothing about who the human was. Change data capture, which streams the write-ahead log out to a separate store. Pick app level for product features like 'activity feed', triggers for compliance coverage.
Gotchas: audit tables outgrow the tables they watch, fast, so partition by month and set a retention policy before you need one. Do not dump raw row diffs blindly, or you will happily archive password hashes and card details forever. And an audit log is not undo: it records what happened, it does not restore state (that is soft delete or point-in-time recovery).
Ask AI for it
Add an append-only audit_logs table with columns: id, actor_id, actor_type, action (created/updated/deleted), entity_type, entity_id, changed_fields as jsonb holding before/after values, ip_address, request_id, and created_at. Grant the application role INSERT and SELECT only, with no UPDATE or DELETE, so rows stay immutable while retained, and do the only deletion through a scheduled retention job running as a separate privileged role against a stated retention window. Write to it inside the same database transaction as the change so the log cannot drift from reality, redact any field named password, token, or secret, and index (entity_type, entity_id, created_at DESC) plus (actor_id, created_at DESC) for the per-record and per-user history views.