Audit trail

An audit log answers who did what, to what, when, from where, and what changed, and it cannot be quietly edited afterwards.

Schema (relational example)

create table audit_events (
  id            uuid primary key default gen_random_uuid(),
  occurred_at   timestamptz not null default now(),
  tenant_id     uuid not null,            -- org / workspace scope
  actor_type    text not null,            -- user | api_key | system | support
  actor_id      text,                     -- null for system
  actor_label   text,                     -- display name at the time
  action        text not null,            -- e.g. 'member.role.changed'
  target_type   text not null,            -- e.g. 'member'
  target_id     text,
  ip            inet,
  user_agent    text,
  request_id    text,                     -- correlate with app logs
  before        jsonb,                    -- changed fields only, redacted
  after         jsonb,
  metadata      jsonb not null default '{}'
);
create index on audit_events (tenant_id, occurred_at desc);
create index on audit_events (tenant_id, target_type, target_id, occurred_at desc);

What to audit

• Auth: sign-in success/failure, sign-out, MFA changes, password reset, session revoke.
• Access: invite, join, remove, role change, API key create/rotate/revoke.
• Money and data: price/total edits, refunds, exports, bulk deletes, imports.
• Settings: anything in an admin console.
• Support/impersonation: always, with the real operator as actor.

Name actions resource.verb or resource.field.verb in past tense (invoice.voided, member.role.changed). Keep the list in code as an enum.

Write rules

1. Append-only. No updates or deletes from the app. Enforce in the database: revoke update/delete, or a trigger that raises.
2. Write in the same transaction as the change when the change must not happen unaudited; for best-effort events (reads, sign-in failures), write outside it so a rollback doesn't erase the attempt.
3. Server decides actor, tenant, IP and time. Never trust the client for them.
4. Diff, not dump: store only changed fields in before/after.
5. Redact: never store passwords, tokens, secrets, full card numbers or unnecessary personal data. Hash or mask where an identifier is needed.
6. Optional tamper evidence: a hash chain (hash = sha256(prev_hash || row)) or periodic export to write-once storage.

Read rules

• Tenant-scoped; only admins (and the actor, for their own events) can read.
• Reading exports of the audit log is itself audited.
• UI: a filterable timeline per tenant and per object ("History" tab).

Retention and export

• Retention by policy ({{RETENTION}}, e.g. 1 year hot, 7 years archived).
• Purge by partition/date, by a privileged job, which logs its own run.
• Export as CSV/JSON with the same filters as the UI.
• Personal-data erasure: pseudonymize actor_label/IP rather than deleting events.

Checklist

• Every action in the list writes an event (test per action)
• App role cannot update/delete audit rows (test it)
• No secrets in before/after (test with a password change)
• Tenant isolation on reads (test cross-tenant)
• Retention job documented

Customize for your project

Database ({{DATABASE}}), tenant model, {{RETENTION}}, compliance regime (SOC 2, HIPAA, GDPR), and where the History UI lives.