Audit Logging — Data Model

One table plus two enums. Defined in libs/db/src/lib/schema/audit-events.ts; enums in libs/db/src/lib/schema/enums.ts. Migration: libs/db/drizzle/0102_glossy_domino.sql (table + both enums).

audit_events

No updated_at. No deleted_at. Deliberate: there is no update or soft-delete path anywhere in the codebase. The only writer is AuditService.record() and the only deleter is the retention cron.

Indexes

Every index leads with organization_id, which is both the isolation key and the highest-selectivity column in a multi-tenant table.

Enums

audit_category

'auth' | 'membership' | 'billing' | 'payment' | 'settings' | 'operations'

Coarse on purpose. The fine grain lives in action, so adding an event never needs a migration; only a genuinely new domain would. operations was the one such addition after the initial build — destructive staff actions on the gym’s operating data had no home in the original five, and filing them under settings would have made that filter meaningless.

Mapping rule:

• membership.* → membership
• subscription lifecycle → billing
• plan catalog, charges, refunds, payment methods, provider config → payment
• org settings → settings
• Clerk session/account events → auth
• staff destroying operating data (sessions, bookings, workouts, programs, templates, class types, forms, leads, announcements) → operations

Adding a value to this enum is an ALTER TYPE … ADD VALUE, which Postgres will not run inside a transaction that then uses the value. Ten earlier migrations in this repo do exactly this, so the pattern is proven — but a category addition and a backfill that writes rows with it must never be the same migration.

audit_actor_type

'user' | 'system'

system covers crons, provider webhooks and any path with no authenticated request context. Those rows carry actor_user_id = NULL; the FK and the enum are kept consistent by AuditService (actorType: actorUserId ? 'user' : 'system').

metadata convention

{
  "before": { "role": "member" },      // only the fields that changed
  "after":  { "role": "coach" },
  "userId": "…",                        // free-form extras alongside
  "membershipId": "…"
}

Rules the instrumentation follows:

• Only what moved. Both plans.update and organizations.update diff the prior row against the new one and record just the differing keys.
• Never a secret. Provider credentials, their ciphertext, card tokens and join tokens are excluded by construction. Credential rotation records credentialsRotated: true, never the value.
• Redact what the schema doesn’t constrain. ConfigureProviderDto.config is a bare @IsObject() with no key schema, so redactValues() keeps the keys and masks every value — { apiPassword: "[redacted]" }. It masks non-secret-looking keys too: a deny-list of *token* / *secret* names fails open the day a provider ships merchantCode2.
• Never free-text PII the staffer typed. A manual charge’s description is excluded for the same reason it’s kept out of PostHog. Cancellation reason and resolverNote are recorded — they are the substance of the decision being audited — which is flagged for counsel in the README’s compliance section.
• Null over empty. buildMetadata returns null rather than {} so an empty object never masquerades as a recorded diff.

Self-audit rows

audit.viewed, audit.exported and audit.access_denied carry no before/after — nothing changed. Their metadata records the scope of the access instead, which is the reg. 10 field they satisfy:

{
  "endpoint": "export",                  // 'list' | 'export'
  "filters": { "category": "payment" },  // the filters in force; pagination dropped
  "rowCount": 842                        // returnedCount/matchedCount on a view;
                                         // reason/role/outcome on a denial
}

target_type is audit_log and target_id is the org id, so the per-entity drill-down index finds them.

Retention

Rows older than 24 months are archived and deleted monthly (0 3 1 * *).

• Archive key: audit-archive/<orgId>/<YYYY-MM>.ndjson
• Bucket: R2_COMPLIANCE_BUCKET_NAME (falls back to the default bucket in dev), per ADR-0007
• Order: upload, then delete that bucket’s exact row ids. A failed upload aborts only that bucket’s delete; the rows survive to next month’s run.
• Boundary: strict lt(created_at, cutoff) via the exported computeRetentionCutoff(now), so a row exactly 24 months old survives. 24 months is the regulatory floor, not a target.
• The NDJSON carries the full row — every column, created_at serialized as ISO 8601 — so an archived object is a complete substitute for the deleted rows.
• No expiry on the archive: objects persist indefinitely once written. Flagged in the README’s compliance gaps.

Relationship to audit_logs

audit_logs (libs/db/src/lib/schema/admin.ts) is the platform-admin table: Taikan staff acting across orgs, keyed on actor_clerk_id, with no organization_id. It predates this feature and is untouched. See the README’s “Two audit tables” section.