-- Audit log. Append-only. Deliberately no foreign key to tenants or users:
-- entries must survive the resources they describe (a Tenants_Delete entry
-- outlives its tenant).
--
-- Entries are scoped by tenant (administrative actions), by subject
-- (identity/account events concerning a user), or both.
CREATE TABLE audit_log (
    id       TEXT PRIMARY KEY,
    tenant   TEXT NOT NULL,
    subject  TEXT NOT NULL,
    time     TIMESTAMP NOT NULL,
    actor    TEXT NOT NULL,
    action   TEXT NOT NULL,
    resource TEXT NOT NULL,
    detail   TEXT NOT NULL,
    request  TEXT NOT NULL
);

CREATE INDEX idx_audit_log_tenant ON audit_log(tenant, id) WHERE tenant != '';
CREATE INDEX idx_audit_log_subject ON audit_log(subject, id) WHERE subject != '';