break
b3499adfca
Constraint: 切换 main 前必须保存当前七月分支全部项目进展，套餐生效提案仅属于 Iteration/7-11。 Rejected: 将七月套餐修复直接移植到 main | 两个分支的可靠投递架构不同。 Confidence: medium Scope-risk: broad Directive: 不得将本提交整体 cherry-pick 到 main；main 套餐热修必须基于其纯 Asynq 代码独立实施。 Tested: git diff --check；openspec validate fix-package-activation-starvation --strict。 Not-tested: 按用户要求未运行自动化测试；go build ./... 因当前审计改造中的 Enterprise 模型字面量和 role.recordFailure 参数类型错误未通过。
155 lines
9.7 KiB
PL/PgSQL
155 lines
9.7 KiB
PL/PgSQL
CREATE TABLE tb_audit_event (
|
||
id bigserial PRIMARY KEY,
|
||
event_id varchar(64) NOT NULL UNIQUE,
|
||
occurred_at timestamptz NOT NULL,
|
||
category varchar(64) NOT NULL,
|
||
action_code varchar(100) NOT NULL,
|
||
action_name varchar(200) NOT NULL,
|
||
summary varchar(500) NOT NULL,
|
||
actor_kind varchar(32) NOT NULL,
|
||
actor_id varchar(128) NOT NULL,
|
||
actor_name varchar(200) NOT NULL,
|
||
actor_shop_id bigint,
|
||
actor_shop_name varchar(200) NOT NULL DEFAULT '',
|
||
actor_enterprise_id bigint,
|
||
actor_enterprise_name varchar(200) NOT NULL DEFAULT '',
|
||
source varchar(32) NOT NULL,
|
||
request_path varchar(300) NOT NULL DEFAULT '',
|
||
request_method varchar(16) NOT NULL DEFAULT '',
|
||
ip_address varchar(64) NOT NULL DEFAULT '',
|
||
user_agent varchar(500) NOT NULL DEFAULT '',
|
||
scope_type varchar(32) NOT NULL,
|
||
scope_id varchar(128) NOT NULL DEFAULT '',
|
||
scope_name varchar(200) NOT NULL DEFAULT '',
|
||
result varchar(16) NOT NULL,
|
||
risk_level varchar(16) NOT NULL,
|
||
error_code varchar(100) NOT NULL DEFAULT '',
|
||
error_summary varchar(500) NOT NULL DEFAULT '',
|
||
request_id varchar(100) NOT NULL DEFAULT '',
|
||
correlation_id varchar(100) NOT NULL DEFAULT '',
|
||
parent_event_id varchar(64) NOT NULL DEFAULT '',
|
||
batch_total integer NOT NULL DEFAULT 0,
|
||
success_count integer NOT NULL DEFAULT 0,
|
||
fail_count integer NOT NULL DEFAULT 0,
|
||
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
content_hash varchar(64) NOT NULL,
|
||
created_at timestamptz NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT ck_audit_event_identity CHECK (event_id <> '' AND action_code <> '' AND action_name <> ''),
|
||
CONSTRAINT ck_audit_event_result CHECK (result IN ('success', 'failed', 'denied', 'partial', 'unknown')),
|
||
CONSTRAINT ck_audit_event_risk CHECK (risk_level IN ('low', 'normal', 'high', 'critical')),
|
||
CONSTRAINT ck_audit_event_batch CHECK (
|
||
batch_total >= 0 AND success_count >= 0 AND fail_count >= 0
|
||
AND success_count + fail_count <= batch_total
|
||
),
|
||
CONSTRAINT ck_audit_event_metadata CHECK (jsonb_typeof(metadata) = 'object')
|
||
);
|
||
|
||
CREATE TABLE tb_audit_event_resource (
|
||
id bigserial PRIMARY KEY,
|
||
audit_event_id bigint NOT NULL,
|
||
resource_type varchar(64) NOT NULL,
|
||
resource_id varchar(128),
|
||
resource_key varchar(200) NOT NULL,
|
||
display_name varchar(255) NOT NULL DEFAULT '',
|
||
relation varchar(16) NOT NULL,
|
||
role varchar(64) NOT NULL,
|
||
identity_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
before_data jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
after_data jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
subject_visibility varchar(24) NOT NULL,
|
||
subject_summary varchar(500) NOT NULL DEFAULT '',
|
||
subject_data jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
sort_order integer NOT NULL DEFAULT 0,
|
||
created_at timestamptz NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT ck_audit_resource_identity CHECK (resource_type <> '' AND resource_key <> ''),
|
||
CONSTRAINT ck_audit_resource_relation CHECK (relation IN ('primary', 'affected', 'reference')),
|
||
CONSTRAINT ck_audit_resource_visibility CHECK (subject_visibility IN ('internal_only', 'subject_result', 'subject_detail')),
|
||
CONSTRAINT ck_audit_resource_identity_snapshot CHECK (jsonb_typeof(identity_snapshot) = 'object'),
|
||
CONSTRAINT ck_audit_resource_before_data CHECK (jsonb_typeof(before_data) = 'object'),
|
||
CONSTRAINT ck_audit_resource_after_data CHECK (jsonb_typeof(after_data) = 'object'),
|
||
CONSTRAINT ck_audit_resource_subject_data CHECK (jsonb_typeof(subject_data) = 'object')
|
||
);
|
||
|
||
CREATE UNIQUE INDEX uq_audit_event_resource_role
|
||
ON tb_audit_event_resource (audit_event_id, resource_type, resource_key, relation, role);
|
||
CREATE INDEX idx_audit_event_occurred ON tb_audit_event (occurred_at DESC, id DESC);
|
||
CREATE INDEX idx_audit_event_actor ON tb_audit_event (actor_kind, actor_id, occurred_at DESC, id DESC);
|
||
CREATE INDEX idx_audit_event_action_result_risk ON tb_audit_event (action_code, result, risk_level, occurred_at DESC, id DESC);
|
||
CREATE INDEX idx_audit_event_scope ON tb_audit_event (scope_type, scope_id, occurred_at DESC, id DESC);
|
||
CREATE INDEX idx_audit_event_request ON tb_audit_event (request_id) WHERE request_id <> '';
|
||
CREATE INDEX idx_audit_event_correlation ON tb_audit_event (correlation_id, occurred_at DESC, id DESC) WHERE correlation_id <> '';
|
||
CREATE INDEX idx_audit_event_parent ON tb_audit_event (parent_event_id) WHERE parent_event_id <> '';
|
||
CREATE INDEX idx_audit_resource_event ON tb_audit_event_resource (audit_event_id, sort_order, id);
|
||
CREATE INDEX idx_audit_resource_id_timeline
|
||
ON tb_audit_event_resource (resource_type, resource_id, created_at DESC, id DESC)
|
||
WHERE resource_id IS NOT NULL;
|
||
CREATE INDEX idx_audit_resource_key_timeline
|
||
ON tb_audit_event_resource (resource_type, resource_key, created_at DESC, id DESC);
|
||
|
||
CREATE FUNCTION reject_audit_fact_update() RETURNS trigger AS $$
|
||
BEGIN
|
||
RAISE EXCEPTION '审计事实不可修改；业务修正必须追加新事件';
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER trg_audit_event_immutable
|
||
BEFORE UPDATE ON tb_audit_event
|
||
FOR EACH ROW EXECUTE FUNCTION reject_audit_fact_update();
|
||
CREATE TRIGGER trg_audit_event_resource_immutable
|
||
BEFORE UPDATE ON tb_audit_event_resource
|
||
FOR EACH ROW EXECUTE FUNCTION reject_audit_fact_update();
|
||
|
||
COMMENT ON TABLE tb_audit_event IS '统一不可变业务审计事件';
|
||
COMMENT ON TABLE tb_audit_event_resource IS '审计事件发生时的独立资源快照';
|
||
COMMENT ON COLUMN tb_audit_event.id IS '审计事件数据库主键';
|
||
COMMENT ON COLUMN tb_audit_event.event_id IS '对外稳定审计事件ID';
|
||
COMMENT ON COLUMN tb_audit_event.occurred_at IS '业务事实实际发生时间';
|
||
COMMENT ON COLUMN tb_audit_event.category IS '动作所属稳定业务类别';
|
||
COMMENT ON COLUMN tb_audit_event.action_code IS 'Action Registry 注册的稳定动作编码';
|
||
COMMENT ON COLUMN tb_audit_event.action_name IS '事件发生时的中文动作名称快照';
|
||
COMMENT ON COLUMN tb_audit_event.summary IS '平台内部可读业务摘要';
|
||
COMMENT ON COLUMN tb_audit_event.actor_kind IS '真实操作者类型';
|
||
COMMENT ON COLUMN tb_audit_event.actor_id IS '真实操作者稳定ID';
|
||
COMMENT ON COLUMN tb_audit_event.actor_name IS '事件发生时的操作者名称快照';
|
||
COMMENT ON COLUMN tb_audit_event.actor_shop_id IS '操作者所属店铺ID快照';
|
||
COMMENT ON COLUMN tb_audit_event.actor_shop_name IS '操作者所属店铺名称快照';
|
||
COMMENT ON COLUMN tb_audit_event.actor_enterprise_id IS '操作者所属企业ID快照';
|
||
COMMENT ON COLUMN tb_audit_event.actor_enterprise_name IS '操作者所属企业名称快照';
|
||
COMMENT ON COLUMN tb_audit_event.source IS '操作入口来源';
|
||
COMMENT ON COLUMN tb_audit_event.request_path IS 'HTTP请求路径摘要';
|
||
COMMENT ON COLUMN tb_audit_event.request_method IS 'HTTP请求方法';
|
||
COMMENT ON COLUMN tb_audit_event.ip_address IS '操作者请求IP';
|
||
COMMENT ON COLUMN tb_audit_event.user_agent IS '操作者User-Agent摘要';
|
||
COMMENT ON COLUMN tb_audit_event.scope_type IS '事件主要业务范围类型';
|
||
COMMENT ON COLUMN tb_audit_event.scope_id IS '事件主要业务范围ID';
|
||
COMMENT ON COLUMN tb_audit_event.scope_name IS '事件主要业务范围名称快照';
|
||
COMMENT ON COLUMN tb_audit_event.result IS '事件结果：success、failed、denied、partial或unknown';
|
||
COMMENT ON COLUMN tb_audit_event.risk_level IS 'Action Registry 注册的风险等级';
|
||
COMMENT ON COLUMN tb_audit_event.error_code IS '稳定业务错误码';
|
||
COMMENT ON COLUMN tb_audit_event.error_summary IS '不含底层敏感信息的错误摘要';
|
||
COMMENT ON COLUMN tb_audit_event.request_id IS '同一HTTP请求关联ID';
|
||
COMMENT ON COLUMN tb_audit_event.correlation_id IS '跨请求与异步业务链路关联ID';
|
||
COMMENT ON COLUMN tb_audit_event.parent_event_id IS '直接父审计事件稳定ID';
|
||
COMMENT ON COLUMN tb_audit_event.batch_total IS '批量根事件输入总数';
|
||
COMMENT ON COLUMN tb_audit_event.success_count IS '批量根事件成功数';
|
||
COMMENT ON COLUMN tb_audit_event.fail_count IS '批量根事件失败数';
|
||
COMMENT ON COLUMN tb_audit_event.metadata IS '清理且有界的业务补充参数';
|
||
COMMENT ON COLUMN tb_audit_event.content_hash IS '清理并标准化后的事件与资源内容 SHA-256';
|
||
COMMENT ON COLUMN tb_audit_event.created_at IS '审计事件数据库写入时间';
|
||
COMMENT ON COLUMN tb_audit_event_resource.id IS '审计事件资源数据库主键';
|
||
COMMENT ON COLUMN tb_audit_event_resource.audit_event_id IS '审计事件内部 ID，仅普通索引，不建立外键';
|
||
COMMENT ON COLUMN tb_audit_event_resource.resource_type IS 'Resource Registry 注册的资源类型';
|
||
COMMENT ON COLUMN tb_audit_event_resource.resource_id IS '可空的资源内部稳定ID';
|
||
COMMENT ON COLUMN tb_audit_event_resource.resource_key IS '事件发生时的稳定业务Key';
|
||
COMMENT ON COLUMN tb_audit_event_resource.display_name IS '事件发生时的资源显示名称';
|
||
COMMENT ON COLUMN tb_audit_event_resource.relation IS '资源关系：primary、affected或reference';
|
||
COMMENT ON COLUMN tb_audit_event_resource.role IS '资源在本次业务动作中的稳定角色';
|
||
COMMENT ON COLUMN tb_audit_event_resource.identity_snapshot IS '事件发生时的资源身份快照';
|
||
COMMENT ON COLUMN tb_audit_event_resource.before_data IS '该资源本次操作前的直接业务字段';
|
||
COMMENT ON COLUMN tb_audit_event_resource.after_data IS '该资源本次操作后的直接业务字段';
|
||
COMMENT ON COLUMN tb_audit_event_resource.subject_visibility IS '主体可见级别';
|
||
COMMENT ON COLUMN tb_audit_event_resource.subject_summary IS '代理或企业可见的安全业务结论';
|
||
COMMENT ON COLUMN tb_audit_event_resource.subject_data IS '按Action Registry白名单生成的主体业务字段';
|
||
COMMENT ON COLUMN tb_audit_event_resource.sort_order IS '同一事件内资源稳定展示顺序';
|
||
COMMENT ON COLUMN tb_audit_event_resource.created_at IS '资源快照数据库写入时间';
|