31 lines
2.0 KiB
SQL
31 lines
2.0 KiB
SQL
-- Admin audit log: append-only record of admin-side actions (who/what/when/
|
|
-- where/outcome). Envelope + key-data enrichment (resource_name, details); no
|
|
-- request bodies or field diffs, so there is no secret-bearing data here.
|
|
-- Retention is driven from the config file (audit.retention-days), not
|
|
-- app_settings.
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
created_at INTEGER NOT NULL,
|
|
actor_type TEXT NOT NULL DEFAULT '', -- 'user' | 'principal' | ''
|
|
actor_id TEXT NOT NULL DEFAULT '', -- public id snapshot
|
|
actor_name TEXT NOT NULL DEFAULT '', -- username / principal name snapshot
|
|
actor_is_admin INTEGER NOT NULL DEFAULT 0,
|
|
auth_method TEXT NOT NULL DEFAULT '', -- 'session' | 'principal'
|
|
category TEXT NOT NULL DEFAULT 'admin', -- 'admin' | 'self_service' (v2)
|
|
method TEXT NOT NULL DEFAULT '',
|
|
path TEXT NOT NULL DEFAULT '', -- concrete request path
|
|
route_pattern TEXT NOT NULL DEFAULT '', -- matched template, e.g. /api/admin/pki/certs/:id/revoke
|
|
resource_type TEXT NOT NULL DEFAULT '', -- derived from the pattern, e.g. pki/certs
|
|
resource_id TEXT NOT NULL DEFAULT '', -- :id path param, if any
|
|
resource_name TEXT NOT NULL DEFAULT '', -- human name of the target (handler-annotated)
|
|
details TEXT NOT NULL DEFAULT '', -- small REDACTED JSON object; never secret values
|
|
status INTEGER NOT NULL DEFAULT 0, -- HTTP status
|
|
outcome TEXT NOT NULL DEFAULT '', -- 'success' | 'denied' | 'error'
|
|
remote_ip TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
session_id TEXT NOT NULL DEFAULT ''
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_created ON audit_log(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_actor ON audit_log(actor_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_resource ON audit_log(resource_type, resource_id);
|