| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Generic audit log for security-relevant events. Schema is intentionally |
| 4 | -- broad so future sprints can reuse it (S15 permissions, S30 org changes, |
| 5 | -- S34 admin actions, S07 SSH key changes). |
| 6 | -- |
| 7 | -- - actor_id: the user performing the action (NULL for unauthenticated / |
| 8 | -- admin-CLI actions; admin actions populate meta->>'admin' instead). |
| 9 | -- - action: short snake_case verb (e.g. '2fa_enabled', 'recovery_regenerated', |
| 10 | -- 'admin_cleared_2fa'). |
| 11 | -- - target_type, target_id: the entity affected (e.g. 'user', user.id). |
| 12 | -- - meta: free-form JSON for action-specific detail (no secrets here). |
| 13 | |
| 14 | -- +goose Up |
| 15 | CREATE TABLE auth_audit_log ( |
| 16 | id bigserial PRIMARY KEY, |
| 17 | actor_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 18 | action text NOT NULL, |
| 19 | target_type text NOT NULL, |
| 20 | target_id bigint, |
| 21 | meta jsonb NOT NULL DEFAULT '{}'::jsonb, |
| 22 | created_at timestamptz NOT NULL DEFAULT now() |
| 23 | ); |
| 24 | |
| 25 | CREATE INDEX auth_audit_log_actor_id_idx ON auth_audit_log (actor_id); |
| 26 | CREATE INDEX auth_audit_log_target_idx ON auth_audit_log (target_type, target_id); |
| 27 | CREATE INDEX auth_audit_log_action_idx ON auth_audit_log (action); |
| 28 | CREATE INDEX auth_audit_log_created_at_idx ON auth_audit_log (created_at DESC); |
| 29 | |
| 30 | -- +goose Down |
| 31 | DROP TABLE IF EXISTS auth_audit_log; |
| 32 |