What stays immutable when an audit trail has to hold up years later

What stays immutable when an audit trail has to hold up years later

A school's accounts are audited eighteen months after the fact. The auditor wants to see every change to a student's fee waiver record: who entered it, who approved it, what the values were at each step, and when each change happened. The IT head pulls up the record. The current state looks correct. The history table shows three edits. But the auditor asks a question nobody can answer: was the approval timestamp captured before or after the fee challan was generated? The system stored a `last_modified_at` on the record and a separate `updated_at` on the history row. Neither is authoritative because both are set by the application server's clock, and the application server's clock was corrected by 47 seconds during a maintenance window in the intervening year.

This is the core problem with audit trails that have to hold up under inspection long after the people who built the system have left. It is not enough to log changes. You have to decide what is reconstructable and what is not, and make the irreconstructable parts immutable from the moment they are written.

What has to be immutable

The records that matter for compliance — fee transactions, grade changes, attendance corrections, admission decisions — need an append-only history. Not a soft-delete with a `deleted_at` column. An actual separate table where rows are never updated or deleted. Every state transition writes a new row. The row contains the full snapshot of the record at that point, not just the diff, because diffs require you to replay the full history to reconstruct a past state, and replay code is where bugs hide.

The timestamp on each history row has to come from the database, not the application. In PostgreSQL, `clock_timestamp()` at insert time. Not `now()`, which returns the transaction start time and can mask the actual order of events within a long-running transaction. Not the application server's time, which drifts and gets corrected.

The actor has to be captured as a foreign key to a users table, not a stored name or email. People's names change. Staff leave. If you store `entered_by_name = "Priya Sharma"` and Priya gets married and changes her name in the system, the audit trail now has two identities for the same person and no way to link them.

What you can skip

You do not need an immutable history for every table. The library catalog's `book_condition` field does not need an append-only log. The staff directory's `extension_number` does not need one. The test for whether something needs an immutable trail is: could someone challenge this record's validity months or years later, and would we need to prove the sequence of events? If the answer is no, a simple `updated_at` column is enough.

The hash chain

For records that face external inspection — fee records that the education department might audit, attendance records that feed into board exam eligibility — we add a hash chain. Each history row stores a SHA-256 hash of its own content concatenated with the previous row's hash. This makes retroactive modification detectable: if someone with database access edits a row in the middle of the chain, every subsequent hash breaks.

CREATE TABLE fee_waiver_history (
  id BIGSERIAL PRIMARY KEY,
  record_id BIGINT NOT NULL,
  snapshot JSONB NOT NULL,
  actor_id BIGINT NOT NULL REFERENCES users(id),
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  prev_hash BYTEA,
  row_hash BYTEA NOT NULL
);

The `row_hash` is computed in the application before insert: `sha256(snapshot || occurred_at || actor_id || prev_hash)`. We store `prev_hash` so verification is a single pass through the table ordered by `id`.

This is not a blockchain. There is no distributed consensus. It is a tamper-evidence mechanism that catches the most likely threat: someone with database credentials editing history directly because they have access and the application UI does not let them. A determined attacker with write access to both the table and the hash column can still forge a chain. For that threat model you need database-level controls, not application-level hashing.

What we do not do

We do not build audit trails that depend on the application being running. If verifying a record's history requires calling an API endpoint, the audit trail is useless when the application is down or has been replaced. The history table has to be queryable with a plain SQL client against a database backup. That is the format inspectors and internal auditors actually work with.

We also do not rely on ORM lifecycle hooks to write history rows. Hooks fire in application code, which means they can be bypassed by a bulk update, a migration script, or a direct database fix during an incident. History writes go in the same transaction as the record change, triggered by a database function or written explicitly in the data access layer. The goal is that there is no code path that modifies the record without also writing a history row in the same transaction.