Tamper-evident audit log

The audit_logs table is append-only and hash-chained. Every insert computes:

entry_hash = SHA256(
  prev_hash || id || tenant_id || actor_user_id ||
  action || resource || metadata || occurred_at
)

Where prev_hash is the entry_hash of the previous row in the same tenant (or 64 zeroes for the first entry).

Guarantees

• Append-only: UPDATE and DELETE are blocked by trigger (audit_logs_block_mutation). Even with the service role key, you cannot edit a past entry without raising an exception.
• Hash chain: any back-dated insert or in-place edit breaks the chain.
• Tenant isolation: chain is per-tenant. RLS limits SELECT to the tenant.

Verifying integrity

Workspace owners can call:

SELECT * FROM verify_audit_chain('<tenant-id>');
-- ok | broken_at_id | total_entries
-- t  | NULL         | 1342

If ok = false, broken_at_id is the first row whose hash does not match the recomputed value. The function refuses to run for non-owners.

What gets logged

• api_key.created, api_key.rotated, api_key.revoked
• webhook.created, webhook.test_sent, webhook.delivered
• policy.threshold_changed (also captured in policy_changes)
• auth.signin, auth.signout, auth.role_granted

Every entry stores actor_user_id, action, resource (e.g. api_key:<uuid>), and a JSON metadata blob. Avoid putting PII in metadata; reference IDs instead.

Retention

7 years from occurred_at. This matches the FCA's SYSC 9 record-keeping rule for regulated firms; banks will ask.