Most products describe their history table as an audit trail. Almost none of them can say what stops a future migration, a support script or a compromised process from editing it. Without that answer, the table is a changelog — useful, but not evidence.
Immutability is a privilege problem
The usual attempt is a trigger that raises an exception on update or delete. It is better than nothing, and it is not enough: whoever can create the trigger can drop it, and the audit of the audit trail becomes a question about migration review rather than about the database.
The stronger version is to simply not grant the capability. The role the service connects as has insert and select on the event log, and nothing else. There is no update grant to revoke and no delete grant to bypass, so a bug, a badly scoped script or a stolen connection string cannot rewrite history — they can only append to it, which is visible.
revoke all on event_log from app_writer;
grant insert, select on event_log to app_writer;
-- The identity sequence too, or inserts fail in a way
-- that looks like a bug in the application.
grant usage on sequence event_log_id_seq to app_writer;The log is written in the same transaction
The second half of the guarantee is atomicity. If the event is written by a background job, a queue consumer or an after-the-fact hook, then any failure between the change and the log produces a change with no history — and you will not find out until an auditor does.
So the handler that updates a row and the insert into the event log share one transaction. Either both land or neither does. This is unglamorous and it costs a little write throughput, and it is the entire reason the history can be trusted.
What an event actually records
- Who: the acting user and the organisation, never a shared service identity.
- What: the table, the row, and the before and after values of what changed.
- When: the transaction timestamp, not the time the log line was formatted.
- Why, where there is one: the reason text a user typed, or the import batch that caused it.
The before-and-after pair matters more than it looks. A log that records an asset was updated tells an inspector nothing. A log that records the calibration interval changed from six months to twelve, by this person, on this date, is the answer to the question they are actually asking.
Corrections, not edits
People do enter wrong data, and an append-only log has to answer that without an escape hatch. The answer is that a correction is another event. The original stays, the correction references it, and the current value is the latest one — so the register shows the truth while the history shows that it was fixed, by whom, and when.
If a mistake can disappear from the history, then every record in the history is a claim rather than a fact.
This is the part users push back on before they have been through an inspection, and the part they specifically ask for afterwards.