CREATE TABLE audit_events (

id UUID PRIMARY KEY,

kb_id UUID,

actor_id UUID,

action TEXT NOT NULL,

target_kind TEXT NOT NULL,

target_id UUID,

-- 变更要点，按 action 语义自定

detail JSONB NOT NULL DEFAULT '{}',

created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

-- 从哪里做的。ISO 27001 A.8.15 要求日志覆盖 Where / How，即请求的来源；

-- 没有它，一个账号被盗用后的所有活动看起来与本人操作毫无差别。

-- 两列都可空：后台任务（攒批裁决、定时同步）没有客户端，本就该是空

client_ip TEXT,

user_agent TEXT,

-- 操作者的身份快照。actor_id 没有外键，用户停用时行会留下，但 LEFT JOIN

-- users 取不到名字，界面只剩一串 UUID。把当时的邮箱与显示名一并写进记录，

-- 台账才真正自包含

actor_label TEXT

);

CREATE INDEX audit_events_kb_time_idx ON audit_events (kb_id, created_at DESC);

CREATE INDEX audit_events_client_ip_idx ON audit_events (client_ip, created_at DESC)

WHERE client_ip IS NOT NULL;

CREATE FUNCTION audit_events_immutable() RETURNS trigger AS $$

RAISE EXCEPTION 'audit_events is append-only (attempted %)', TG_OP

USING HINT = 'Audit records cannot be modified or deleted.';

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_events_no_update_delete

BEFORE UPDATE OR DELETE ON audit_events

FOR EACH ROW EXECUTE FUNCTION audit_events_immutable();

CREATE TRIGGER audit_events_no_truncate

BEFORE TRUNCATE ON audit_events

FOR EACH STATEMENT EXECUTE FUNCTION audit_events_immutable();