Hardening the audit trail

The effaced_audit_events table is append-only by construction in the library: no code path updates or deletes rows, and the AuditSink protocol exposes no surface to do so. That covers everything effaced itself can do.

It does not cover everything your database can do. The table’s schema alone cannot stop raw SQL in a psql session, a migration gone wrong, or another application sharing the database from rewriting history. If your threat model includes writers outside effaced, enforce append-only at the database itself.

The hash chain detects rewrites

DatabaseAuditSink chains every appended row to the one before it: each row stores the SHA-256 hash of its own content together with the prior row’s hash (prior_hash and event_hash columns). Editing, deleting, or reordering any recorded row changes its recomputed hash and breaks every later link, so recomputing the chain reveals exactly where history was modified out of band.

AuditChainVerifier does the recomputation — it reads the trail, rebuilds the chain, and returns a verdict:

from effaced import AuditChainVerifier, bind_tables

tables = bind_tables(metadata)
verifier = AuditChainVerifier(session_factory, tables.audit_events)

verdict = verifier.verify()
if not verdict.verified:
    # verdict.first_broken_event_id names the earliest modified row
    alert(f"audit chain broken at {verdict.first_broken_event_id}")

Verification reads only — it writes nothing and appends no event.

Rows written before this feature shipped — or by a custom sink that does not compute the chain — carry NULL hashes. The verifier treats them as an unchained prefix: it skips them and verifies from the first row that has a hash. A missing hash is absence of evidence, never evidence of tampering, so an un-upgraded trail never reads as broken.

The two nullable columns are an additive owned-table change: after upgrading, re-run alembic revision --autogenerate (see the Alembic migrations guide) and apply the generated ADD COLUMNs like any other revision.

A trigger that rejects rewrites

On Postgres, a row-level trigger blocks UPDATE and DELETE for every role, including the application’s own:

CREATE FUNCTION effaced_audit_events_no_rewrite() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION 'effaced_audit_events is append-only (% blocked)', TG_OP;
END;
$$;

CREATE TRIGGER effaced_audit_events_append_only
    BEFORE UPDATE OR DELETE ON effaced_audit_events
    FOR EACH ROW EXECUTE FUNCTION effaced_audit_events_no_rewrite();

effaced never updates or deletes audit rows, so the trigger changes nothing about normal operation — it only turns an out-of-band rewrite into a loud error.

Shipping it with Alembic

The trigger is plain DDL, so it rides your migrations like any other schema change:

def upgrade() -> None:
    op.execute("""
        CREATE FUNCTION effaced_audit_events_no_rewrite() ...
    """)  # full SQL from above


def downgrade() -> None:
    op.execute("DROP TRIGGER effaced_audit_events_append_only ON effaced_audit_events")
    op.execute("DROP FUNCTION effaced_audit_events_no_rewrite()")

Gaps the trigger does not close

Table owners and superusers can drop the trigger. Combine it with restricted grants if that matters to you: run the application as a role that does not own the table.
TRUNCATE is not covered by row-level triggers. Revoke it explicitly:
```
REVOKE TRUNCATE ON effaced_audit_events FROM your_app_role;
```

Retention of the trail itself

With the trigger in place, pruning very old audit events requires deliberately dropping the trigger in a migration — an explicit, reviewable step rather than a quiet DELETE. That friction is the point: the record of what your erasure machinery did should not be erasable in passing.