Recipe
Audit log schema + retention policy
Immutable event ledger for every license activation, deactivation, and admin action. Built on a single append-only table with time-based partitioning and automated cold storage.
Schema
| Column | Type | Purpose |
|---|---|---|
| id | uuid v7 | Time-sortable primary key |
| event_at | timestamptz | Wall-clock occurrence time |
| actor_id | uuid nullable | Admin or system principal |
| event_type | text | activation, deactivation, ban, unban, key_rotate |
| payload | jsonb | License key hash, HWID, IP, reason |
Retention policy
- Hot tier (30 days): Primary DB, full query access for dashboard and support.
- Warm tier (365 days): Partitioned by month, compressed, slower queries.
- Cold tier (7 years): Object storage as gzipped JSON lines. Restore on demand.
Indexes
Composite index on (event_type, event_at DESC) for filtered time-range scans. Partial index on event_type = 'activation' for the license activation heatmap. No index on payload — queries use application-side filtering after narrow time-range fetch.