In an app where data is edited from several devices, sooner or later you'll need to answer "who changed this, when, and from where?". An audit_log table answers that. But there's a harder question behind it: how do you know the audit log itself hasn't been edited?
This post walks through building a tamper-evident audit log in PostgreSQL, layer by layer, each one closing the gap left by the previous. The examples come from Financial Plan , my budgeting app (its sync is covered in local-first PWA sync ), where every record a device pushes is logged with its before and after versions, the device, the time and the outcome.
Layer 1: append only
The first step is to have the database itself refuse any update or delete on the log table:
CREATE FUNCTION audit_log_append_only () RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'audit_log is append-only' ; END $$ ;
CREATE TRIGGER audit_log_no_change BEFORE UPDATE OR DELETE ON audit_log FOR EACH ROW EXECUTE FUNCTION audit_log_append_only (); CREATE…