-- 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);