43 lines
1.6 KiB
SQL
43 lines
1.6 KiB
SQL
-- Muse P1 合同操作审计与幂等 Schema（PostgreSQL）
|
||
-- 目的：为 Meta/Knowledge/Market/AI/Account 的全量合同入口提供统一幂等、审计和兜底查询状态。
|
||
|
||
CREATE TABLE muse_domain_operation_record (
|
||
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
domain VARCHAR(50) NOT NULL,
|
||
side VARCHAR(20) NOT NULL,
|
||
operation_id VARCHAR(120) NOT NULL,
|
||
command_id VARCHAR(120),
|
||
actor_user_id BIGINT,
|
||
resource_type VARCHAR(80) NOT NULL,
|
||
resource_id BIGINT,
|
||
resource_key VARCHAR(200),
|
||
parent_type VARCHAR(80),
|
||
parent_id BIGINT,
|
||
status VARCHAR(30) NOT NULL DEFAULT 'accepted',
|
||
revision INT NOT NULL DEFAULT 1,
|
||
path_variables JSONB,
|
||
query_params JSONB,
|
||
request_payload JSONB,
|
||
response_payload JSONB,
|
||
creator VARCHAR(64) NOT NULL DEFAULT '',
|
||
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updater VARCHAR(64) NOT NULL DEFAULT '',
|
||
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0
|
||
);
|
||
|
||
CREATE UNIQUE INDEX uk_muse_domain_op_command
|
||
ON muse_domain_operation_record(tenant_id, domain, command_id)
|
||
WHERE command_id IS NOT NULL;
|
||
|
||
CREATE INDEX idx_muse_domain_op_resource
|
||
ON muse_domain_operation_record(tenant_id, domain, resource_type, resource_id, create_time);
|
||
|
||
CREATE INDEX idx_muse_domain_op_actor
|
||
ON muse_domain_operation_record(tenant_id, actor_user_id, create_time);
|
||
|
||
CREATE TRIGGER trg_muse_domain_operation_record_updated_at
|
||
BEFORE UPDATE ON muse_domain_operation_record
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|