[Senate] Hash-chain the audit log so tampering is detectable done

Goal

SciDEX writes audit-relevant rows into multiple tables — task_events, artifact_provenance, senate_alerts, comment_classifier_runs, sandbox_audit_event, the new actor_write_trip_event — but each row stands alone. An operator with
DB write access (or a compromised migration script) can DELETE FROM
artifact_provenance WHERE id = X and leave no trace. This task makes
the audit log append-only-by-construction: each new row is
hash-chained to its predecessor, the chain head is published to a
durable external sink, and a verifier detects any retroactive edit or
deletion. The threat model is "one of our own DBAs / agents /
migrations is malicious or compromised", and the integrity guarantee
is "tampering is detectable within one verification cycle even if the
attacker has full DB write authority".

Effort: thorough

Acceptance Criteria

CREATE TABLE audit_chain (
        seq          BIGSERIAL PRIMARY KEY,
        event_kind   TEXT NOT NULL,    -- mirrors source table name
        event_pk     TEXT NOT NULL,    -- foreign-row PK as text
        event_hash   BYTEA NOT NULL,   -- SHA-256 of canonical event payload
        prev_hash    BYTEA NOT NULL,   -- previous row's chain_hash, all zeros for genesis
        chain_hash   BYTEA NOT NULL,   -- SHA-256(prev_hash || event_hash || seq::bytea)
        appended_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        UNIQUE (event_kind, event_pk)
      );
      CREATE INDEX idx_ac_kind_pk ON audit_chain (event_kind, event_pk);

- append(conn, event_kind: str, event_pk: str, payload: dict)
— canonicalises payload (json.dumps(payload, sort_keys=True,
separators=(',',':'), default=str)), hashes, takes a row
lock on the latest row of audit_chain, computes
chain_hash, and inserts. Uses
SELECT ... FROM audit_chain ORDER BY seq DESC LIMIT 1 FOR
UPDATE to serialise.
- verify(start_seq: int = 1, end_seq: int | None = None)
-> VerifyReport walks the chain and re-derives each
chain_hash, returning the first divergence (or None). trigger appends to audit_chain automatically:

CREATE TRIGGER trg_artifact_prov_chain
        AFTER INSERT ON artifact_provenance
        FOR EACH ROW EXECUTE FUNCTION audit_chain_append('artifact_provenance');

The PL/pgSQL function audit_chain_append mirrors
Python's append logic. Apply triggers to:
artifact_provenance, task_events, senate_alerts,
actor_write_trip_event, sandbox_audit_event,
senate_pause, audit_log (if exists; create if not).

latest (seq, chain_hash, appended_at) to
/data/audit/chain_head.log (a separate filesystem path, also
copied to data/scidex-papers/audit_chain_head.log so it
lands in the papers submodule's git history — a
tamper-evident external sink). This is the operator-side
attestation that yesterday's chain head was X. verify() against the previous-day window. Any divergence
emits a senate_alerts row of kind='audit_chain_break'
with severity='critical' and the offending seq/event_pk. historical rows of the chained tables (oldest first) and
seeds the chain. Records the seed-cutoff timestamp; rows
older than the cutoff are not chained (we cannot retroactively
attest to history we did not chain at write time, and saying
otherwise would be the kind of false confidence this task
exists to prevent). created with SELECT only on audit_chain; the verifier
runs as that role so a compromised app DB user cannot tamper
with the chain even if they connect via the API path. prefix, last verify result, last divergence (if any). genesis row; chain extension; verify happy path; verify with
simulated tampering (manually overwrite one row's
event_hash) → expects divergence at exactly that seq;
concurrent appends serialise correctly under
pgbench-style load (use psycopg2 ThreadedConnectionPool).

Approach

Implement Python append + verify first against a fixture DB; pin

canonicalisation rules in unit tests.

Migration with the table + the PL/pgSQL trigger function. Apply

triggers one table at a time, each in its own migration step.

Backfill script; run dry; run live; record cutoff in Work Log.

External-head publisher + verifier cron.

RO role + smoke verifier-from-RO-role.

Tampering simulation test; tile.

Dependencies

• q-safety-emergency-pause — supplies senate_alerts table.

Dependents

• q-trust-governance-transparency-log — uses the chain to attest

governance decisions.

• q-trust-provenance-integrity-scanner — uses the chain as ground

truth when reconciling artifact lineage.

Work Log

2026-04-27 07:14 PT — Slot 0 (minimax:73)

• Started: claimed task, read AGENTS.md, confirmed worktree is current with origin/main
• Verification: confirmed audit_chain table does NOT exist on main; task is not stale
• Staleness check: task created 2026-04-27, still fresh; checked main HEAD matches
• Created migration: migrations/20260428_audit_chain.sql — CREATE TABLE audit_chain, CREATE INDEX, audit_log table, audit_chain_append() trigger function using digest() (pgcrypto), triggers on 5 tables
• Created module: scidex/senate/audit_chain.py — append(), verify(), get_head(), publish_head(), verify_and_alert() with VerifyReport dataclass
• DB triggers: Created audit_chain_append() PostgreSQL function using digest() from pgcrypto (sha256() only accepts bytea). Verified trigger fires correctly on INSERT; audit_chain rows created for senate_alerts inserts.
• Created backfill script: scripts/backfill_audit_chain.py — handles tables with non-standard PKs (artifact_provenance uses action_id), dedup within run
• Updated spec acceptance criteria with checkmarks for completed items
• Note: task_events, actor_write_trip_event, senate_pause tables do not exist on current DB; triggers for existing tables only. The Python module provides append() for programmatic use.
• Verification: verify() returns VerifyReport(valid=True) for existing chain; get_head() returns (seq, chain_hash, appended_at)