Skip to main content

ADR 006: Audit log retention and archiving

Status: Accepted

Context​

The security audit log (audit_logs) records sign-ins, user and role changes, settings, tenant changes, access denials and exports for every org. HIPAA expects this kind of record to be kept for six years, but nobody reads the older years day to day. Keeping six years in Postgres would make the table the largest in the database, slow the filtered reads behind the audit page, and raise storage and backup costs for data that is almost never queried.

Audit rows must also outlive the things they describe. A removed user, a deleted vendor or a deleted org still has to show up in its history.

Decision​

  1. Postgres holds the last 12 months. That covers the audit page, exports, and new-device detection, which compares a sign-in with the user's earlier sign-ins.
  2. Older rows move to a separate, private R2 bucket (R2_AUDIT_ARCHIVE_BUCKET) as gzipped JSON Lines. That bucket holds nothing else.
    • Each line is a full audit_logs row.
    • Object keys are audit/yyyy/mm/batch-<firstId>-<lastId>-<sha12>.jsonl.gz, and every batch stays within a single calendar month.
    • Each object carries sha256, rows, first-id and last-id metadata.
  3. A monthly Vercel Cron does the move. The cron calls GET /api/v1/internal/audit-archive, which is guarded by CRON_SECRET (any other request gets a 404).
    • It runs at 03:00 UTC on days 1–3 of each month.
    • The cutoff is the start of the UTC month 12 months back, so Postgres holds 12–13 months.
    • A run stops starting new batches after about 100 seconds, because the function limit is 120 seconds. The day-2 and day-3 runs finish any backlog and do nothing otherwise.
  4. A batch is deleted from Postgres only after it is verified in R2. A HEAD request must match the content hash before the rows are deleted.
    • The key includes the content hash, so a re-run after a crash finds the object and skips the upload rather than overwriting it.
    • Without the bucket configured, the job fails with CONFIG_MISSING. It never deletes rows without real storage.
  5. R2 enforces the six years, not application code. A bucket lock rule (prefix audit/, 2192 days) makes objects impossible to delete or overwrite. A lifecycle rule removes them after the same period.
  6. Rows are snapshots. Actor email, name and role, team name and target label are copied at write time. teamId has no foreign key, so deleting an org neither fails because of its audit rows nor deletes them.

Consequences​

  • The audit page and /audit-logs/me show 12 months at most. Older history needs a manual restore: download the month's objects, gunzip, and load or grep the JSONL.
  • New-device and new-country alerts only compare against the last 12 months. A device last seen 13 months ago counts as new, which is acceptable.
  • Monthly, rather than yearly, keeps each run small and the table close to 12 months. A yearly run would have to move a full year in one window and would let the table grow to 24 months.
  • Adding a column to audit_logs needs no archive migration: older objects simply lack the field. Restore tooling should read JSONL loosely.
  • Production setup (the bucket, its lock and lifecycle rules, and the two environment variables on riskflow-api) is tracked in GitHub issue #7.

References​

  • apps/backend/src/modules/audit-log/audit-archive.service.ts
  • apps/backend/vercel.json (crons)
  • ADR 004 — 404 over 403: cross-tenant audit reads also return 404.