894 lines
55 KiB
MySQL
894 lines
55 KiB
MySQL
-- =====================================================================
|
||
-- PBL Agent OS 验证原型（Phase 0/1）—— 36 表可执行 DDL
|
||
-- 方言：mariadb（InnoDB + utf8mb4/utf8mb4_general_ci）
|
||
-- 规则：id BIGINT NOT NULL AUTO_INCREMENT 物理主键 + xxx_code VARCHAR(32) 业务主键（唯一索引）
|
||
-- tenant_id VARCHAR(32) NOT NULL 为第一业务列，所有索引以 tenant_id 打头
|
||
-- 禁止 FOREIGN KEY / 禁止 ENUM / 禁止原生 TIMESTAMP（用 DATETIME）
|
||
-- append-only 表无 update_time、无物理删除（11 张，见文末清单）
|
||
-- 权威来源：projects/pbls/docs/01-design/data-model.md
|
||
-- 执行：apps/pbls/build.sh -> mysql --protocol=TCP -h127.0.0.1 -P3306 pbls < pbls_tables.sql
|
||
-- =====================================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_governance（3 表）
|
||
-- =====================================================================
|
||
|
||
-- 1. pbl_tenant 租户主表
|
||
CREATE TABLE IF NOT EXISTS pbl_tenant (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
tenant_code VARCHAR(32) NOT NULL COMMENT '租户业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID（第一业务列）',
|
||
tenant_name VARCHAR(128) NOT NULL COMMENT '租户名称',
|
||
org_code VARCHAR(32) DEFAULT NULL COMMENT '所属机构编码（rbac 侧只读引用）',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/suspended/closed（appcodes_kv: pbl_tenant_status）',
|
||
quota_bp INT NOT NULL DEFAULT 100 COMMENT '蓝图配额',
|
||
quota_stu INT NOT NULL DEFAULT 500 COMMENT '学生席位配额',
|
||
remark VARCHAR(512) DEFAULT NULL COMMENT '备注',
|
||
create_by VARCHAR(32) DEFAULT NULL COMMENT '创建人',
|
||
create_time DATETIME DEFAULT NULL COMMENT '创建时间',
|
||
update_by VARCHAR(32) DEFAULT NULL COMMENT '更新人',
|
||
update_time DATETIME DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_tenant_code (tenant_code),
|
||
KEY idx_pbl_tenant_tid_status (tenant_id, status),
|
||
KEY idx_pbl_tenant_tid_org (tenant_id, org_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 租户主表';
|
||
|
||
-- 2. pbl_role_binding 租户内角色绑定（复用 rbac，不改 rbac 基表）
|
||
CREATE TABLE IF NOT EXISTS pbl_role_binding (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
binding_code VARCHAR(32) NOT NULL COMMENT '绑定业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
user_code VARCHAR(32) NOT NULL COMMENT '用户编码（rbac 只读引用）',
|
||
role_code VARCHAR(32) NOT NULL COMMENT '角色编码：teacher/assistant/student/observer/admin（appcodes_kv: pbl_role）',
|
||
scope_type VARCHAR(16) NOT NULL DEFAULT 'tenant' COMMENT '作用域类型：tenant/blueprint/class/team',
|
||
scope_code VARCHAR(32) DEFAULT NULL COMMENT '作用域对象编码',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/revoked',
|
||
effective_from DATETIME DEFAULT NULL COMMENT '生效起',
|
||
effective_to DATETIME DEFAULT NULL COMMENT '生效止',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_role_binding_code (binding_code),
|
||
KEY idx_pbl_role_binding_tid_user (tenant_id, user_code, status),
|
||
KEY idx_pbl_role_binding_tid_scope (tenant_id, scope_type, scope_code),
|
||
KEY idx_pbl_role_binding_tid_role (tenant_id, role_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 租户角色绑定';
|
||
|
||
-- 3. pbl_audit_log 审计日志（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_audit_log (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
audit_code VARCHAR(32) NOT NULL COMMENT '审计业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
actor_type VARCHAR(16) NOT NULL DEFAULT 'user' COMMENT '主体类型：user/agent/system',
|
||
actor_code VARCHAR(32) DEFAULT NULL COMMENT '主体编码',
|
||
action_code VARCHAR(64) NOT NULL COMMENT '动作编码（appcodes_kv: pbl_audit_action）',
|
||
object_type VARCHAR(32) DEFAULT NULL COMMENT '对象类型',
|
||
object_code VARCHAR(32) DEFAULT NULL COMMENT '对象编码',
|
||
result VARCHAR(16) NOT NULL DEFAULT 'success' COMMENT '结果：success/denied/error',
|
||
detail TEXT DEFAULT NULL COMMENT '详情 JSON',
|
||
trace_id VARCHAR(64) DEFAULT NULL COMMENT '链路追踪ID',
|
||
client_ip VARCHAR(64) DEFAULT NULL COMMENT '来源IP',
|
||
create_time DATETIME DEFAULT NULL COMMENT '发生时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_audit_code (audit_code),
|
||
KEY idx_pbl_audit_tid_time (tenant_id, create_time),
|
||
KEY idx_pbl_audit_tid_obj (tenant_id, object_type, object_code),
|
||
KEY idx_pbl_audit_tid_actor (tenant_id, actor_type, actor_code),
|
||
KEY idx_pbl_audit_tid_trace (tenant_id, trace_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 审计日志（append-only）';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_blueprint（11 表，M1a）
|
||
-- =====================================================================
|
||
|
||
-- 4. pbl_blueprint 蓝图聚合根
|
||
CREATE TABLE IF NOT EXISTS pbl_blueprint (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_name VARCHAR(128) NOT NULL COMMENT '蓝图名称',
|
||
subject_code VARCHAR(32) DEFAULT NULL COMMENT '学科编码（appcodes_kv: pbl_subject）',
|
||
grade_code VARCHAR(32) DEFAULT NULL COMMENT '学段编码（appcodes_kv: pbl_grade）',
|
||
driving_q TEXT DEFAULT NULL COMMENT '驱动性问题',
|
||
summary VARCHAR(1024) DEFAULT NULL COMMENT '蓝图摘要',
|
||
duration_hours INT NOT NULL DEFAULT 0 COMMENT '总课时（小时）',
|
||
team_size_min INT NOT NULL DEFAULT 1 COMMENT '最小团队人数',
|
||
team_size_max INT NOT NULL DEFAULT 6 COMMENT '最大团队人数',
|
||
cur_version INT NOT NULL DEFAULT 1 COMMENT '当前版本号（乐观锁）',
|
||
quality_level VARCHAR(16) NOT NULL DEFAULT 'L0_draft' COMMENT '质量等级 L0~L4（appcodes_kv: pbl_quality_level）',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'draft' COMMENT '状态：draft/validating/validated/compiling/published/archived（appcodes_kv: pbl_bp_status）',
|
||
source_type VARCHAR(16) NOT NULL DEFAULT 'manual' COMMENT '来源：manual/template/fork/agent',
|
||
source_code VARCHAR(32) DEFAULT NULL COMMENT '来源对象编码（模板/父蓝图）',
|
||
owner_code VARCHAR(32) DEFAULT NULL COMMENT '归属教师',
|
||
lock_holder VARCHAR(32) DEFAULT NULL COMMENT '当前编辑锁持有者',
|
||
lock_expire DATETIME DEFAULT NULL COMMENT '编辑锁过期时间',
|
||
extra TEXT DEFAULT NULL COMMENT '扩展 JSON',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_blueprint_code (bp_code),
|
||
KEY idx_pbl_bp_tid_status (tenant_id, status),
|
||
KEY idx_pbl_bp_tid_owner (tenant_id, owner_code),
|
||
KEY idx_pbl_bp_tid_subject (tenant_id, subject_code, grade_code),
|
||
KEY idx_pbl_bp_tid_quality (tenant_id, quality_level),
|
||
KEY idx_pbl_bp_tid_source (tenant_id, source_type, source_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 蓝图聚合根';
|
||
|
||
-- 5. pbl_blueprint_version 版本快照 + change_delta（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_blueprint_version (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
version_code VARCHAR(32) NOT NULL COMMENT '版本业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
version_no INT NOT NULL COMMENT '版本号（单调递增）',
|
||
snapshot LONGTEXT DEFAULT NULL COMMENT '蓝图全量快照 JSON',
|
||
change_delta LONGTEXT DEFAULT NULL COMMENT '相对上一版本的差异 JSON（可回放）',
|
||
checksum VARCHAR(64) DEFAULT NULL COMMENT '快照 sha256',
|
||
reason VARCHAR(512) DEFAULT NULL COMMENT '变更原因',
|
||
created_by VARCHAR(32) DEFAULT NULL COMMENT '提交人',
|
||
create_time DATETIME DEFAULT NULL COMMENT '提交时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_bp_version_code (version_code),
|
||
UNIQUE KEY uk_pbl_bp_version_no (tenant_id, bp_code, version_no),
|
||
KEY idx_pbl_bp_version_tid_bp (tenant_id, bp_code, create_time),
|
||
KEY idx_pbl_bp_version_tid_ck (tenant_id, checksum)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 蓝图版本快照（append-only）';
|
||
|
||
-- 6. pbl_blueprint_lock 编辑锁
|
||
CREATE TABLE IF NOT EXISTS pbl_blueprint_lock (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
lock_code VARCHAR(32) NOT NULL COMMENT '锁业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
holder_code VARCHAR(32) NOT NULL COMMENT '持有者',
|
||
session_id VARCHAR(64) DEFAULT NULL COMMENT '会话ID',
|
||
lock_state VARCHAR(16) NOT NULL DEFAULT 'held' COMMENT '锁状态：held/released/expired/forced',
|
||
acquired_at DATETIME DEFAULT NULL COMMENT '获取时间',
|
||
expire_at DATETIME DEFAULT NULL COMMENT '过期时间',
|
||
released_at DATETIME DEFAULT NULL COMMENT '释放时间',
|
||
version_no INT NOT NULL DEFAULT 0 COMMENT '加锁时版本号',
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_bp_lock_code (lock_code),
|
||
UNIQUE KEY uk_pbl_bp_lock_active (tenant_id, bp_code, lock_state),
|
||
KEY idx_pbl_bp_lock_tid_holder (tenant_id, holder_code),
|
||
KEY idx_pbl_bp_lock_tid_expire (tenant_id, expire_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 蓝图编辑锁';
|
||
|
||
-- 7. pbl_subobject 7 类子对象泛化主表
|
||
CREATE TABLE IF NOT EXISTS pbl_subobject (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
obj_code VARCHAR(32) NOT NULL COMMENT '子对象业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '所属蓝图',
|
||
obj_type VARCHAR(32) NOT NULL COMMENT '子对象类型：objective/stage/task/artifact_spec/rubric_ref/resource_ref/assessment_plan（appcodes_kv: pbl_subobj_type）',
|
||
parent_code VARCHAR(32) DEFAULT NULL COMMENT '父子对象编码（树形）',
|
||
obj_name VARCHAR(128) NOT NULL COMMENT '名称',
|
||
obj_summary VARCHAR(1024) DEFAULT NULL COMMENT '摘要',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '同级排序',
|
||
payload LONGTEXT DEFAULT NULL COMMENT '类型化载荷 JSON（泛化契约）',
|
||
schema_ver VARCHAR(16) NOT NULL DEFAULT 'v1' COMMENT '载荷 schema 版本',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/disabled/deleted',
|
||
version_no INT NOT NULL DEFAULT 1 COMMENT '乐观锁版本号',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_subobject_code (obj_code),
|
||
KEY idx_pbl_subobj_tid_bp_type (tenant_id, bp_code, obj_type, sort_no),
|
||
KEY idx_pbl_subobj_tid_parent (tenant_id, parent_code),
|
||
KEY idx_pbl_subobj_tid_status (tenant_id, status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 蓝图 7 类子对象泛化主表';
|
||
|
||
-- 8. pbl_subobject_attr 子对象泛化属性（KV）
|
||
CREATE TABLE IF NOT EXISTS pbl_subobject_attr (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
attr_code VARCHAR(32) NOT NULL COMMENT '属性业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
obj_code VARCHAR(32) NOT NULL COMMENT '子对象编码',
|
||
attr_key VARCHAR(64) NOT NULL COMMENT '属性键',
|
||
attr_value TEXT DEFAULT NULL COMMENT '属性值',
|
||
value_type VARCHAR(16) NOT NULL DEFAULT 'string' COMMENT '值类型：string/int/float/bool/json/date',
|
||
required_flag TINYINT NOT NULL DEFAULT 0 COMMENT '是否必填 0否1是',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_subobj_attr_code (attr_code),
|
||
UNIQUE KEY uk_pbl_subobj_attr_key (tenant_id, obj_code, attr_key),
|
||
KEY idx_pbl_subobj_attr_tid_obj (tenant_id, obj_code, sort_no)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 子对象泛化属性 KV';
|
||
|
||
-- 9. pbl_subobject_rel 子对象间关系
|
||
CREATE TABLE IF NOT EXISTS pbl_subobject_rel (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
rel_code VARCHAR(32) NOT NULL COMMENT '关系业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '所属蓝图',
|
||
src_obj_code VARCHAR(32) NOT NULL COMMENT '源子对象',
|
||
dst_obj_code VARCHAR(32) NOT NULL COMMENT '目标子对象',
|
||
rel_type VARCHAR(32) NOT NULL COMMENT '关系类型：depends_on/produces/assesses/references/contains（appcodes_kv: pbl_rel_type）',
|
||
weight DECIMAL(6,3) NOT NULL DEFAULT 1.000 COMMENT '关系权重',
|
||
remark VARCHAR(512) DEFAULT NULL COMMENT '备注',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_subobj_rel_code (rel_code),
|
||
UNIQUE KEY uk_pbl_subobj_rel_triple (tenant_id, src_obj_code, dst_obj_code, rel_type),
|
||
KEY idx_pbl_subobj_rel_tid_bp (tenant_id, bp_code, rel_type),
|
||
KEY idx_pbl_subobj_rel_tid_dst (tenant_id, dst_obj_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 子对象关系';
|
||
|
||
-- 10. pbl_subobject_asset 子对象资产引用（只读引用平台资产，不落资产本体）
|
||
CREATE TABLE IF NOT EXISTS pbl_subobject_asset (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
asset_code VARCHAR(32) NOT NULL COMMENT '资产引用业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
obj_code VARCHAR(32) NOT NULL COMMENT '子对象编码',
|
||
asset_kind VARCHAR(32) NOT NULL COMMENT '资产种类：image/model/audio/video/doc/url（appcodes_kv: pbl_asset_kind）',
|
||
asset_uri VARCHAR(512) DEFAULT NULL COMMENT '资产 URI',
|
||
asset_name VARCHAR(128) DEFAULT NULL COMMENT '资产名称',
|
||
mime_type VARCHAR(64) DEFAULT NULL COMMENT 'MIME',
|
||
size_bytes BIGINT NOT NULL DEFAULT 0 COMMENT '字节数',
|
||
checksum VARCHAR(64) DEFAULT NULL COMMENT 'sha256',
|
||
license_code VARCHAR(32) DEFAULT NULL COMMENT '许可编码',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_subobj_asset_code (asset_code),
|
||
KEY idx_pbl_subobj_asset_tid_obj (tenant_id, obj_code, sort_no),
|
||
KEY idx_pbl_subobj_asset_tid_kind (tenant_id, asset_kind)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 子对象资产引用';
|
||
|
||
-- 11. pbl_subobject_ref 子对象对复用域对象的只读引用（world/scene/entity/script）
|
||
CREATE TABLE IF NOT EXISTS pbl_subobject_ref (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
ref_code VARCHAR(32) NOT NULL COMMENT '引用业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
obj_code VARCHAR(32) NOT NULL COMMENT '子对象编码',
|
||
ref_module VARCHAR(32) NOT NULL COMMENT '被引用模块：world/scene/entity/script_engine/scense/scense_game',
|
||
ref_table VARCHAR(64) NOT NULL COMMENT '被引用表名',
|
||
ref_id BIGINT DEFAULT NULL COMMENT '被引用行 id',
|
||
ref_biz_code VARCHAR(64) DEFAULT NULL COMMENT '被引用业务编码',
|
||
ref_mode VARCHAR(16) NOT NULL DEFAULT 'readonly' COMMENT '引用模式：readonly（Phase 0/1 唯一合法值）',
|
||
snapshot_json TEXT DEFAULT NULL COMMENT '引用时只读快照（不改基表）',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_subobj_ref_code (ref_code),
|
||
KEY idx_pbl_subobj_ref_tid_obj (tenant_id, obj_code),
|
||
KEY idx_pbl_subobj_ref_tid_mod (tenant_id, ref_module, ref_table, ref_biz_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 子对象对复用域只读引用';
|
||
|
||
-- 12. pbl_subobject_review 子对象评审意见（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_subobject_review (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
review_code VARCHAR(32) NOT NULL COMMENT '评审业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
obj_code VARCHAR(32) DEFAULT NULL COMMENT '子对象编码（空=蓝图级）',
|
||
reviewer_type VARCHAR(16) NOT NULL DEFAULT 'user' COMMENT '评审主体：user/agent_critic/agent_designer',
|
||
reviewer_code VARCHAR(32) DEFAULT NULL COMMENT '评审主体编码',
|
||
verdict VARCHAR(16) NOT NULL DEFAULT 'comment' COMMENT '结论：pass/comment/revise/reject',
|
||
score DECIMAL(6,2) DEFAULT NULL COMMENT '评分',
|
||
comment TEXT DEFAULT NULL COMMENT '意见正文',
|
||
create_time DATETIME DEFAULT NULL COMMENT '评审时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_subobj_review_code (review_code),
|
||
KEY idx_pbl_subobj_review_tid_bp (tenant_id, bp_code, create_time),
|
||
KEY idx_pbl_subobj_review_tid_obj (tenant_id, obj_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 子对象评审意见（append-only）';
|
||
|
||
-- 13. pbl_blueprint_tag 蓝图标签
|
||
CREATE TABLE IF NOT EXISTS pbl_blueprint_tag (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
tag_code VARCHAR(32) NOT NULL COMMENT '标签业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
tag_key VARCHAR(64) NOT NULL COMMENT '标签键',
|
||
tag_value VARCHAR(128) DEFAULT NULL COMMENT '标签值',
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_bp_tag_code (tag_code),
|
||
UNIQUE KEY uk_pbl_bp_tag_kv (tenant_id, bp_code, tag_key),
|
||
KEY idx_pbl_bp_tag_tid_key (tenant_id, tag_key, tag_value)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 蓝图标签';
|
||
|
||
-- 14. pbl_blueprint_publish 发布记录（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_blueprint_publish (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
publish_code VARCHAR(32) NOT NULL COMMENT '发布业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
version_no INT NOT NULL COMMENT '发布版本号',
|
||
gd_code VARCHAR(32) DEFAULT NULL COMMENT '关联 Game Definition 编码',
|
||
channel VARCHAR(32) NOT NULL DEFAULT 'class' COMMENT '发布渠道：class/school/public',
|
||
target_code VARCHAR(32) DEFAULT NULL COMMENT '目标对象（班级编码）',
|
||
publish_state VARCHAR(16) NOT NULL DEFAULT 'published' COMMENT '状态：published/revoked',
|
||
published_by VARCHAR(32) DEFAULT NULL COMMENT '发布人',
|
||
create_time DATETIME DEFAULT NULL COMMENT '发布时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_bp_publish_code (publish_code),
|
||
KEY idx_pbl_bp_publish_tid_bp (tenant_id, bp_code, create_time),
|
||
KEY idx_pbl_bp_publish_tid_target (tenant_id, channel, target_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 蓝图发布记录（append-only）';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_template（2 表，M1b）
|
||
-- =====================================================================
|
||
|
||
-- 15. pbl_template 模板主表（含离线兜底模板）
|
||
CREATE TABLE IF NOT EXISTS pbl_template (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
tpl_code VARCHAR(32) NOT NULL COMMENT '模板业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID（__system__ 表示平台内置）',
|
||
tpl_name VARCHAR(128) NOT NULL COMMENT '模板名称',
|
||
tpl_kind VARCHAR(32) NOT NULL DEFAULT 'blueprint' COMMENT '模板种类：blueprint/subobject/rubric',
|
||
subject_code VARCHAR(32) DEFAULT NULL COMMENT '适用学科',
|
||
grade_code VARCHAR(32) DEFAULT NULL COMMENT '适用学段',
|
||
offline_flag TINYINT NOT NULL DEFAULT 0 COMMENT '是否离线兜底模板 0否1是',
|
||
body LONGTEXT DEFAULT NULL COMMENT '模板正文 JSON（含子对象树）',
|
||
id_prefix_rule VARCHAR(64) DEFAULT NULL COMMENT '实例化 ID 重映射前缀规则',
|
||
version_no INT NOT NULL DEFAULT 1 COMMENT '模板版本',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/disabled',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_template_code (tpl_code),
|
||
KEY idx_pbl_template_tid_kind (tenant_id, tpl_kind, status),
|
||
KEY idx_pbl_template_tid_subj (tenant_id, subject_code, grade_code),
|
||
KEY idx_pbl_template_tid_offline (tenant_id, offline_flag)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 模板主表';
|
||
|
||
-- 16. pbl_template_item 模板条目（实例化 ID 重映射映射表）
|
||
CREATE TABLE IF NOT EXISTS pbl_template_item (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
item_code VARCHAR(32) NOT NULL COMMENT '条目业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
tpl_code VARCHAR(32) NOT NULL COMMENT '模板编码',
|
||
tpl_obj_key VARCHAR(64) NOT NULL COMMENT '模板内占位键（如 __OBJ_1__）',
|
||
obj_type VARCHAR(32) NOT NULL COMMENT '子对象类型',
|
||
parent_key VARCHAR(64) DEFAULT NULL COMMENT '父占位键',
|
||
payload LONGTEXT DEFAULT NULL COMMENT '条目载荷 JSON',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_template_item_code (item_code),
|
||
UNIQUE KEY uk_pbl_template_item_key (tenant_id, tpl_code, tpl_obj_key),
|
||
KEY idx_pbl_template_item_tid_tpl (tenant_id, tpl_code, sort_no)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 模板条目';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_validation（3 表，M2：14 维 + 5 级质量状态）
|
||
-- =====================================================================
|
||
|
||
-- 17. pbl_validation_ruleset 校验规则集
|
||
CREATE TABLE IF NOT EXISTS pbl_validation_ruleset (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
ruleset_code VARCHAR(32) NOT NULL COMMENT '规则集业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
ruleset_name VARCHAR(128) NOT NULL COMMENT '规则集名称',
|
||
dim_code VARCHAR(32) NOT NULL COMMENT '维度编码 D01~D14（appcodes_kv: pbl_valid_dim）',
|
||
dim_name VARCHAR(64) NOT NULL COMMENT '维度名称',
|
||
rule_expr TEXT DEFAULT NULL COMMENT '规则表达式 JSON（确定性可执行）',
|
||
severity VARCHAR(16) NOT NULL DEFAULT 'error' COMMENT '严重度：blocker/error/warning/info',
|
||
weight DECIMAL(6,3) NOT NULL DEFAULT 1.000 COMMENT '维度权重',
|
||
level_effect VARCHAR(16) DEFAULT NULL COMMENT '对质量等级的影响：downgrade/gate/none',
|
||
enabled TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否1是',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '维度排序',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_valid_ruleset_code (ruleset_code),
|
||
KEY idx_pbl_valid_ruleset_tid_dim (tenant_id, dim_code, enabled),
|
||
KEY idx_pbl_valid_ruleset_tid_sort (tenant_id, sort_no)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 14 维校验规则集';
|
||
|
||
-- 18. pbl_validation_run 校验执行批次
|
||
CREATE TABLE IF NOT EXISTS pbl_validation_run (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
run_code VARCHAR(32) NOT NULL COMMENT '执行批次业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
version_no INT NOT NULL DEFAULT 1 COMMENT '被校验版本号',
|
||
ruleset_ver VARCHAR(32) DEFAULT NULL COMMENT '规则集版本',
|
||
trigger_type VARCHAR(16) NOT NULL DEFAULT 'manual' COMMENT '触发：manual/auto/agent/pre_compile',
|
||
run_state VARCHAR(16) NOT NULL DEFAULT 'running' COMMENT '状态：running/passed/failed/error',
|
||
dims_total INT NOT NULL DEFAULT 14 COMMENT '维度总数',
|
||
dims_passed INT NOT NULL DEFAULT 0 COMMENT '通过维度数',
|
||
blocker_count INT NOT NULL DEFAULT 0 COMMENT 'blocker 数',
|
||
error_count INT NOT NULL DEFAULT 0 COMMENT 'error 数',
|
||
warning_count INT NOT NULL DEFAULT 0 COMMENT 'warning 数',
|
||
score DECIMAL(6,2) DEFAULT NULL COMMENT '加权得分',
|
||
quality_level VARCHAR(16) DEFAULT NULL COMMENT '产出质量等级 L0~L4',
|
||
started_at DATETIME DEFAULT NULL COMMENT '开始时间',
|
||
finished_at DATETIME DEFAULT NULL COMMENT '结束时间',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_valid_run_code (run_code),
|
||
KEY idx_pbl_valid_run_tid_bp (tenant_id, bp_code, create_time),
|
||
KEY idx_pbl_valid_run_tid_state (tenant_id, run_state),
|
||
KEY idx_pbl_valid_run_tid_level (tenant_id, quality_level)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 校验执行批次';
|
||
|
||
-- 19. pbl_validation_finding 校验发现明细（append-only 留痕）
|
||
CREATE TABLE IF NOT EXISTS pbl_validation_finding (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
finding_code VARCHAR(32) NOT NULL COMMENT '发现业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
run_code VARCHAR(32) NOT NULL COMMENT '执行批次编码',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
dim_code VARCHAR(32) NOT NULL COMMENT '维度编码',
|
||
obj_code VARCHAR(32) DEFAULT NULL COMMENT '命中子对象',
|
||
severity VARCHAR(16) NOT NULL DEFAULT 'error' COMMENT '严重度',
|
||
finding_msg VARCHAR(1024) DEFAULT NULL COMMENT '问题描述',
|
||
suggestion VARCHAR(1024) DEFAULT NULL COMMENT '修复建议',
|
||
evidence_json TEXT DEFAULT NULL COMMENT '证据 JSON',
|
||
create_time DATETIME DEFAULT NULL COMMENT '产生时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_valid_finding_code (finding_code),
|
||
KEY idx_pbl_valid_finding_tid_run (tenant_id, run_code, dim_code),
|
||
KEY idx_pbl_valid_finding_tid_bp (tenant_id, bp_code, create_time),
|
||
KEY idx_pbl_valid_finding_tid_obj (tenant_id, obj_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 校验发现明细（append-only）';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_compiler（4 表，M3a/M3b：确定性编译 + Game Definition）
|
||
-- =====================================================================
|
||
|
||
-- 20. pbl_compile_job 编译作业
|
||
CREATE TABLE IF NOT EXISTS pbl_compile_job (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
job_code VARCHAR(32) NOT NULL COMMENT '作业业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
version_no INT NOT NULL COMMENT '被编译版本号',
|
||
run_code VARCHAR(32) DEFAULT NULL COMMENT '前置校验批次（必须 passed）',
|
||
compiler_ver VARCHAR(32) NOT NULL DEFAULT 'v1' COMMENT '编译器版本',
|
||
job_state VARCHAR(16) NOT NULL DEFAULT 'queued' COMMENT '状态：queued/running/success/failed/cancelled',
|
||
input_checksum VARCHAR(64) DEFAULT NULL COMMENT '输入快照 sha256（确定性依据）',
|
||
output_checksum VARCHAR(64) DEFAULT NULL COMMENT '输出 Game Definition sha256',
|
||
gd_code VARCHAR(32) DEFAULT NULL COMMENT '产出 Game Definition 编码',
|
||
error_msg VARCHAR(1024) DEFAULT NULL COMMENT '错误信息',
|
||
started_at DATETIME DEFAULT NULL,
|
||
finished_at DATETIME DEFAULT NULL,
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_compile_job_code (job_code),
|
||
KEY idx_pbl_compile_job_tid_bp (tenant_id, bp_code, create_time),
|
||
KEY idx_pbl_compile_job_tid_state (tenant_id, job_state),
|
||
KEY idx_pbl_compile_job_tid_ick (tenant_id, input_checksum)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 编译作业';
|
||
|
||
-- 21. pbl_compile_artifact 编译中间产物
|
||
CREATE TABLE IF NOT EXISTS pbl_compile_artifact (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
artifact_code VARCHAR(32) NOT NULL COMMENT '产物业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
job_code VARCHAR(32) NOT NULL COMMENT '编译作业编码',
|
||
stage_code VARCHAR(32) NOT NULL COMMENT '编译阶段：normalize/resolve/link/optimize/emit',
|
||
artifact_kind VARCHAR(32) NOT NULL COMMENT '产物种类：node_set/edge_set/rule_set/asset_map/gd_draft',
|
||
body LONGTEXT DEFAULT NULL COMMENT '产物正文 JSON',
|
||
checksum VARCHAR(64) DEFAULT NULL COMMENT 'sha256',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '阶段排序',
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_compile_artifact_code (artifact_code),
|
||
KEY idx_pbl_compile_artifact_tid_job (tenant_id, job_code, sort_no),
|
||
KEY idx_pbl_compile_artifact_tid_kind (tenant_id, artifact_kind)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 编译中间产物';
|
||
|
||
-- 22. pbl_game_definition Game Definition（编译最终产出）
|
||
CREATE TABLE IF NOT EXISTS pbl_game_definition (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gd_code VARCHAR(32) NOT NULL COMMENT 'Game Definition 业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '来源蓝图',
|
||
version_no INT NOT NULL COMMENT '来源蓝图版本',
|
||
gd_version INT NOT NULL DEFAULT 1 COMMENT 'GD 自身版本',
|
||
schema_ver VARCHAR(16) NOT NULL DEFAULT 'gd-v1' COMMENT 'GD schema 版本',
|
||
body LONGTEXT DEFAULT NULL COMMENT 'Game Definition 全文 JSON（schema 校验通过）',
|
||
checksum VARCHAR(64) DEFAULT NULL COMMENT 'sha256（同输入同输出）',
|
||
world_ref VARCHAR(64) DEFAULT NULL COMMENT '引用世界编码（只读）',
|
||
scene_ref VARCHAR(64) DEFAULT NULL COMMENT '引用场景编码（只读）',
|
||
script_ref VARCHAR(64) DEFAULT NULL COMMENT '引用脚本编码（只读）',
|
||
gd_state VARCHAR(16) NOT NULL DEFAULT 'draft' COMMENT '状态：draft/validated/published/archived',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_gd_code (gd_code),
|
||
UNIQUE KEY uk_pbl_gd_bp_ver (tenant_id, bp_code, version_no, gd_version),
|
||
KEY idx_pbl_gd_tid_state (tenant_id, gd_state),
|
||
KEY idx_pbl_gd_tid_ck (tenant_id, checksum),
|
||
KEY idx_pbl_gd_tid_world (tenant_id, world_ref, scene_ref)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL Game Definition';
|
||
|
||
-- 23. pbl_compile_log 编译日志（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_compile_log (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
log_code VARCHAR(32) NOT NULL COMMENT '日志业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
job_code VARCHAR(32) NOT NULL COMMENT '编译作业编码',
|
||
stage_code VARCHAR(32) DEFAULT NULL COMMENT '阶段',
|
||
log_level VARCHAR(16) NOT NULL DEFAULT 'info' COMMENT '级别：debug/info/warn/error',
|
||
log_msg VARCHAR(2048) DEFAULT NULL COMMENT '日志正文',
|
||
context_json TEXT DEFAULT NULL COMMENT '上下文 JSON',
|
||
seq_no BIGINT NOT NULL DEFAULT 0 COMMENT '作业内单调序号',
|
||
create_time DATETIME DEFAULT NULL COMMENT '记录时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_compile_log_code (log_code),
|
||
KEY idx_pbl_compile_log_tid_job (tenant_id, job_code, seq_no),
|
||
KEY idx_pbl_compile_log_tid_level (tenant_id, log_level, create_time)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 编译日志（append-only）';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_agent_runtime（4 表，M4a/M4b：Designer/Critic + fail-closed 裁决）
|
||
-- =====================================================================
|
||
|
||
-- 24. pbl_agent_session Agent 会话
|
||
CREATE TABLE IF NOT EXISTS pbl_agent_session (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
session_code VARCHAR(32) NOT NULL COMMENT '会话业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) DEFAULT NULL COMMENT '关联蓝图',
|
||
agent_role VARCHAR(32) NOT NULL COMMENT 'Agent 角色：designer/critic（appcodes_kv: pbl_agent_role）',
|
||
model_name VARCHAR(64) DEFAULT NULL COMMENT '使用的平台模型注册名',
|
||
session_state VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/paused/closed/error',
|
||
owner_code VARCHAR(32) DEFAULT NULL COMMENT '发起用户',
|
||
turn_count INT NOT NULL DEFAULT 0 COMMENT '轮次数',
|
||
tool_call_count INT NOT NULL DEFAULT 0 COMMENT '工具调用总数',
|
||
tool_deny_count INT NOT NULL DEFAULT 0 COMMENT '被拒绝调用数',
|
||
context_json LONGTEXT DEFAULT NULL COMMENT '会话上下文 JSON',
|
||
started_at DATETIME DEFAULT NULL,
|
||
closed_at DATETIME DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_agent_session_code (session_code),
|
||
KEY idx_pbl_agent_session_tid_bp (tenant_id, bp_code, session_state),
|
||
KEY idx_pbl_agent_session_tid_role (tenant_id, agent_role),
|
||
KEY idx_pbl_agent_session_tid_owner (tenant_id, owner_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL Agent 会话';
|
||
|
||
-- 25. pbl_agent_message Agent 消息（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_agent_message (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
msg_code VARCHAR(32) NOT NULL COMMENT '消息业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
session_code VARCHAR(32) NOT NULL COMMENT '会话编码',
|
||
turn_no INT NOT NULL DEFAULT 0 COMMENT '轮次号',
|
||
role VARCHAR(16) NOT NULL COMMENT '角色：system/user/assistant/tool',
|
||
content LONGTEXT DEFAULT NULL COMMENT '消息正文',
|
||
token_in INT NOT NULL DEFAULT 0 COMMENT '输入 token',
|
||
token_out INT NOT NULL DEFAULT 0 COMMENT '输出 token',
|
||
latency_ms INT NOT NULL DEFAULT 0 COMMENT '耗时毫秒',
|
||
create_time DATETIME DEFAULT NULL COMMENT '产生时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_agent_msg_code (msg_code),
|
||
KEY idx_pbl_agent_msg_tid_sess (tenant_id, session_code, turn_no),
|
||
KEY idx_pbl_agent_msg_tid_time (tenant_id, create_time)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL Agent 消息（append-only）';
|
||
|
||
-- 26. pbl_agent_tool_call 工具调用记录（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_agent_tool_call (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
call_code VARCHAR(32) NOT NULL COMMENT '调用业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
session_code VARCHAR(32) NOT NULL COMMENT '会话编码',
|
||
turn_no INT NOT NULL DEFAULT 0 COMMENT '轮次号',
|
||
tool_name VARCHAR(64) NOT NULL COMMENT '工具名（13 启用 / 9 禁用白名单外）',
|
||
tool_state VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT '状态：pending/allowed/executed/denied/error',
|
||
params_json LONGTEXT DEFAULT NULL COMMENT '入参 JSON',
|
||
result_json LONGTEXT DEFAULT NULL COMMENT '返回 JSON',
|
||
latency_ms INT NOT NULL DEFAULT 0 COMMENT '耗时毫秒',
|
||
create_time DATETIME DEFAULT NULL COMMENT '调用时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_agent_call_code (call_code),
|
||
KEY idx_pbl_agent_call_tid_sess (tenant_id, session_code, turn_no),
|
||
KEY idx_pbl_agent_call_tid_tool (tenant_id, tool_name, tool_state),
|
||
KEY idx_pbl_agent_call_tid_time (tenant_id, create_time)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL Agent 工具调用（append-only）';
|
||
|
||
-- 27. pbl_agent_arbitration fail-closed 裁决记录（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_agent_arbitration (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
arb_code VARCHAR(32) NOT NULL COMMENT '裁决业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
session_code VARCHAR(32) DEFAULT NULL COMMENT '会话编码',
|
||
call_code VARCHAR(32) DEFAULT NULL COMMENT '关联调用编码',
|
||
tool_name VARCHAR(64) NOT NULL COMMENT '被裁决工具名',
|
||
decision VARCHAR(16) NOT NULL DEFAULT 'deny' COMMENT '裁决：allow/deny（默认 deny，fail-closed）',
|
||
deny_reason VARCHAR(32) NOT NULL DEFAULT 'not_in_whitelist' COMMENT '拒绝原因：not_in_whitelist/invalid_params/missing_tenant_context/write_protected/quota_exceeded',
|
||
rule_ref VARCHAR(64) DEFAULT NULL COMMENT '命中规则引用',
|
||
detail_json TEXT DEFAULT NULL COMMENT '裁决详情 JSON',
|
||
arbiter VARCHAR(32) NOT NULL DEFAULT 'system' COMMENT '裁决者',
|
||
create_time DATETIME DEFAULT NULL COMMENT '裁决时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_agent_arb_code (arb_code),
|
||
KEY idx_pbl_agent_arb_tid_tool (tenant_id, tool_name, decision),
|
||
KEY idx_pbl_agent_arb_tid_sess (tenant_id, session_code, create_time),
|
||
KEY idx_pbl_agent_arb_tid_reason (tenant_id, deny_reason)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL Agent fail-closed 裁决记录（append-only）';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_evidence（4 表，M5a/M5b：产出物采集 + 证据幂等）
|
||
-- =====================================================================
|
||
|
||
-- 28. pbl_artifact 学生产出物
|
||
CREATE TABLE IF NOT EXISTS pbl_artifact (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
artifact_code VARCHAR(32) NOT NULL COMMENT '产出物业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
session_code VARCHAR(32) DEFAULT NULL COMMENT '运行会话编码',
|
||
team_code VARCHAR(32) DEFAULT NULL COMMENT '团队编码',
|
||
obj_code VARCHAR(32) DEFAULT NULL COMMENT '对应产出物规格子对象',
|
||
artifact_name VARCHAR(128) NOT NULL COMMENT '产出物名称',
|
||
artifact_kind VARCHAR(32) NOT NULL COMMENT '种类：doc/image/model/audio/video/code/log/chat（appcodes_kv: pbl_artifact_kind）',
|
||
storage_uri VARCHAR(512) DEFAULT NULL COMMENT '存储 URI',
|
||
mime_type VARCHAR(64) DEFAULT NULL COMMENT 'MIME',
|
||
size_bytes BIGINT NOT NULL DEFAULT 0 COMMENT '字节数',
|
||
checksum VARCHAR(64) DEFAULT NULL COMMENT 'sha256',
|
||
content_text LONGTEXT DEFAULT NULL COMMENT '文本类正文（可索引）',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/archived/deleted',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_artifact_code (artifact_code),
|
||
KEY idx_pbl_artifact_tid_bp (tenant_id, bp_code, artifact_kind),
|
||
KEY idx_pbl_artifact_tid_team (tenant_id, team_code),
|
||
KEY idx_pbl_artifact_tid_sess (tenant_id, session_code),
|
||
KEY idx_pbl_artifact_tid_ck (tenant_id, checksum)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 学生产出物';
|
||
|
||
-- 29. pbl_evidence 证据主表
|
||
CREATE TABLE IF NOT EXISTS pbl_evidence (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
evidence_code VARCHAR(32) NOT NULL COMMENT '证据业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
artifact_code VARCHAR(32) DEFAULT NULL COMMENT '关联产出物',
|
||
obj_code VARCHAR(32) DEFAULT NULL COMMENT '关联子对象（学习目标/任务）',
|
||
team_code VARCHAR(32) DEFAULT NULL COMMENT '团队编码',
|
||
student_code VARCHAR(32) DEFAULT NULL COMMENT '学生编码',
|
||
evidence_type VARCHAR(32) NOT NULL COMMENT '证据类型：artifact/event/dialogue/state_snapshot/metric（appcodes_kv: pbl_evidence_type）',
|
||
idem_key VARCHAR(128) NOT NULL COMMENT '幂等键（tenant+bp+type+source+hash）',
|
||
payload LONGTEXT DEFAULT NULL COMMENT '证据载荷 JSON',
|
||
occurred_at DATETIME DEFAULT NULL COMMENT '证据发生时间',
|
||
trust_level VARCHAR(16) NOT NULL DEFAULT 'normal' COMMENT '可信度：high/normal/low',
|
||
anonymized TINYINT NOT NULL DEFAULT 0 COMMENT '是否已匿名化 0否1是',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/invalidated',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_evidence_code (evidence_code),
|
||
UNIQUE KEY uk_pbl_evidence_idem (tenant_id, idem_key),
|
||
KEY idx_pbl_evidence_tid_bp (tenant_id, bp_code, evidence_type),
|
||
KEY idx_pbl_evidence_tid_team (tenant_id, team_code, student_code),
|
||
KEY idx_pbl_evidence_tid_obj (tenant_id, obj_code),
|
||
KEY idx_pbl_evidence_tid_time (tenant_id, occurred_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 证据主表（幂等键唯一）';
|
||
|
||
-- 30. pbl_evidence_idem 证据幂等提交台账（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_evidence_idem (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
idem_code VARCHAR(32) NOT NULL COMMENT '幂等台账业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
idem_key VARCHAR(128) NOT NULL COMMENT '幂等键',
|
||
evidence_code VARCHAR(32) DEFAULT NULL COMMENT '首次落库证据编码',
|
||
submit_count INT NOT NULL DEFAULT 1 COMMENT '累计提交次数（重复提交只增此计数，不新增证据行）',
|
||
last_payload_hash VARCHAR(64) DEFAULT NULL COMMENT '最近一次载荷 hash',
|
||
conflict_flag TINYINT NOT NULL DEFAULT 0 COMMENT '是否载荷冲突 0否1是',
|
||
source_channel VARCHAR(32) DEFAULT NULL COMMENT '来源渠道：runtime/manual/agent/import',
|
||
create_time DATETIME DEFAULT NULL COMMENT '首次提交时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_evidence_idem_code (idem_code),
|
||
UNIQUE KEY uk_pbl_evidence_idem_key (tenant_id, idem_key),
|
||
KEY idx_pbl_evidence_idem_tid_time (tenant_id, create_time),
|
||
KEY idx_pbl_evidence_idem_tid_conf (tenant_id, conflict_flag)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 证据幂等台账（append-only）';
|
||
|
||
-- 31. pbl_evidence_binding 证据与复用域对象绑定（零改表关联）
|
||
CREATE TABLE IF NOT EXISTS pbl_evidence_binding (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
bind_code VARCHAR(32) NOT NULL COMMENT '绑定业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
evidence_code VARCHAR(32) NOT NULL COMMENT '证据编码',
|
||
ref_module VARCHAR(32) NOT NULL COMMENT '复用域模块：world/scene/entity/script_engine/scense_runtime/scense/scense_game',
|
||
ref_table VARCHAR(64) NOT NULL COMMENT '复用域表名',
|
||
ref_id BIGINT DEFAULT NULL COMMENT '复用域行 id',
|
||
ref_biz_code VARCHAR(64) DEFAULT NULL COMMENT '复用域业务编码',
|
||
bind_role VARCHAR(32) NOT NULL DEFAULT 'source' COMMENT '绑定角色：source/target/context',
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_evidence_binding_code (bind_code),
|
||
KEY idx_pbl_evidence_binding_tid_ev (tenant_id, evidence_code),
|
||
KEY idx_pbl_evidence_binding_tid_ref (tenant_id, ref_module, ref_table, ref_biz_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 证据与复用域绑定（零改表）';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_assessment（3 表，M6：Rubric 加权评估与报告）
|
||
-- =====================================================================
|
||
|
||
-- 32. pbl_rubric 量规主表
|
||
CREATE TABLE IF NOT EXISTS pbl_rubric (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
rubric_code VARCHAR(32) NOT NULL COMMENT '量规业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) DEFAULT NULL COMMENT '关联蓝图（空=通用量规）',
|
||
rubric_name VARCHAR(128) NOT NULL COMMENT '量规名称',
|
||
level_count INT NOT NULL DEFAULT 5 COMMENT '等级数（默认 5 级）',
|
||
total_weight DECIMAL(8,3) NOT NULL DEFAULT 100.000 COMMENT '总权重',
|
||
pass_score DECIMAL(6,2) NOT NULL DEFAULT 60.00 COMMENT '及格分',
|
||
grade_rule TEXT DEFAULT NULL COMMENT '等级映射规则 JSON',
|
||
is_default TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认量规 0否1是',
|
||
version_no INT NOT NULL DEFAULT 1 COMMENT '版本',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/disabled',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_rubric_code (rubric_code),
|
||
KEY idx_pbl_rubric_tid_bp (tenant_id, bp_code, status),
|
||
KEY idx_pbl_rubric_tid_default (tenant_id, is_default)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 量规主表';
|
||
|
||
-- 33. pbl_rubric_criterion 量规评价维度条目
|
||
CREATE TABLE IF NOT EXISTS pbl_rubric_criterion (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
crit_code VARCHAR(32) NOT NULL COMMENT '条目业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
rubric_code VARCHAR(32) NOT NULL COMMENT '量规编码',
|
||
obj_code VARCHAR(32) DEFAULT NULL COMMENT '关联学习目标子对象',
|
||
crit_name VARCHAR(128) NOT NULL COMMENT '维度名称',
|
||
crit_desc VARCHAR(1024) DEFAULT NULL COMMENT '维度说明',
|
||
weight DECIMAL(6,3) NOT NULL DEFAULT 1.000 COMMENT '权重',
|
||
level_desc TEXT DEFAULT NULL COMMENT '各等级描述 JSON（level -> desc）',
|
||
max_score DECIMAL(6,2) NOT NULL DEFAULT 5.00 COMMENT '最高分',
|
||
evidence_required TINYINT NOT NULL DEFAULT 1 COMMENT '是否必须有证据支撑 0否1是',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_rubric_crit_code (crit_code),
|
||
KEY idx_pbl_rubric_crit_tid_rub (tenant_id, rubric_code, sort_no),
|
||
KEY idx_pbl_rubric_crit_tid_obj (tenant_id, obj_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 量规评价维度';
|
||
|
||
-- 34. pbl_assessment_report 评估报告
|
||
CREATE TABLE IF NOT EXISTS pbl_assessment_report (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
report_code VARCHAR(32) NOT NULL COMMENT '报告业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bp_code VARCHAR(32) NOT NULL COMMENT '蓝图编码',
|
||
rubric_code VARCHAR(32) NOT NULL COMMENT '量规编码',
|
||
team_code VARCHAR(32) DEFAULT NULL COMMENT '团队编码',
|
||
student_code VARCHAR(32) DEFAULT NULL COMMENT '学生编码（团队报告时为空）',
|
||
scope_type VARCHAR(16) NOT NULL DEFAULT 'team' COMMENT '范围：team/student/blueprint',
|
||
score_total DECIMAL(6,2) NOT NULL DEFAULT 0.00 COMMENT '加权总分（可复算）',
|
||
grade_code VARCHAR(16) DEFAULT NULL COMMENT '等级 A/B/C/D/E',
|
||
detail_json LONGTEXT DEFAULT NULL COMMENT '逐维度得分明细 JSON（含证据引用）',
|
||
evidence_count INT NOT NULL DEFAULT 0 COMMENT '引用证据数',
|
||
missing_evidence_count INT NOT NULL DEFAULT 0 COMMENT '缺证据维度数',
|
||
assessor_type VARCHAR(16) NOT NULL DEFAULT 'agent' COMMENT '评估者：agent/user/mixed',
|
||
assessor_code VARCHAR(32) DEFAULT NULL COMMENT '评估者编码',
|
||
report_state VARCHAR(16) NOT NULL DEFAULT 'draft' COMMENT '状态：draft/published/archived',
|
||
calc_checksum VARCHAR(64) DEFAULT NULL COMMENT '复算校验 sha256',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_assess_report_code (report_code),
|
||
KEY idx_pbl_assess_report_tid_bp (tenant_id, bp_code, scope_type),
|
||
KEY idx_pbl_assess_report_tid_team (tenant_id, team_code, student_code),
|
||
KEY idx_pbl_assess_report_tid_state (tenant_id, report_state),
|
||
KEY idx_pbl_assess_report_tid_grade (tenant_id, grade_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 评估报告';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_domain_ext（1 表，M8：world/scene/entity 租户·班级·团队关联，不改基表）
|
||
-- =====================================================================
|
||
|
||
-- 35. pbl_domain_binding 复用域对象绑定（零改表唯一关联落点）
|
||
CREATE TABLE IF NOT EXISTS pbl_domain_binding (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
bind_code VARCHAR(32) NOT NULL COMMENT '绑定业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
bind_kind VARCHAR(32) NOT NULL COMMENT '绑定种类：tenant_world/class_scene/team_entity/bp_script/session_world（appcodes_kv: pbl_bind_kind）',
|
||
class_code VARCHAR(32) DEFAULT NULL COMMENT '班级编码',
|
||
team_code VARCHAR(32) DEFAULT NULL COMMENT '团队编码',
|
||
bp_code VARCHAR(32) DEFAULT NULL COMMENT '蓝图编码',
|
||
session_code VARCHAR(32) DEFAULT NULL COMMENT '运行会话编码',
|
||
ref_module VARCHAR(32) NOT NULL COMMENT '复用域模块：world/scene/entity/script_engine/scense_runtime/scense/scense_game',
|
||
ref_table VARCHAR(64) NOT NULL COMMENT '复用域表名',
|
||
ref_id BIGINT DEFAULT NULL COMMENT '复用域行 id',
|
||
ref_biz_code VARCHAR(64) DEFAULT NULL COMMENT '复用域业务编码',
|
||
access_mode VARCHAR(16) NOT NULL DEFAULT 'readonly' COMMENT '访问模式：readonly（Phase 0/1 唯一合法值，写保护）',
|
||
bind_state VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态：active/released',
|
||
extra_json TEXT DEFAULT NULL COMMENT '扩展 JSON',
|
||
create_by VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME DEFAULT NULL,
|
||
update_by VARCHAR(32) DEFAULT NULL,
|
||
update_time DATETIME DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_domain_binding_code (bind_code),
|
||
UNIQUE KEY uk_pbl_domain_binding_ref (tenant_id, bind_kind, ref_module, ref_table, ref_biz_code),
|
||
KEY idx_pbl_domain_binding_tid_class (tenant_id, class_code, team_code),
|
||
KEY idx_pbl_domain_binding_tid_bp (tenant_id, bp_code),
|
||
KEY idx_pbl_domain_binding_tid_sess (tenant_id, session_code),
|
||
KEY idx_pbl_domain_binding_tid_ref (tenant_id, ref_module, ref_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 复用域绑定（零改表）';
|
||
|
||
-- =====================================================================
|
||
-- 模块 pbl_analytics（1 表，M10：分析看板快照，append-only）
|
||
-- =====================================================================
|
||
|
||
-- 36. pbl_analytics_snapshot 分析快照（append-only）
|
||
CREATE TABLE IF NOT EXISTS pbl_analytics_snapshot (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
snap_code VARCHAR(32) NOT NULL COMMENT '快照业务主键',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
metric_group VARCHAR(32) NOT NULL COMMENT '指标组：blueprint/validation/compile/runtime/evidence/assessment（appcodes_kv: pbl_metric_group）',
|
||
scope_type VARCHAR(16) NOT NULL DEFAULT 'tenant' COMMENT '范围：tenant/class/team/blueprint',
|
||
scope_code VARCHAR(32) DEFAULT NULL COMMENT '范围对象编码',
|
||
period_key VARCHAR(16) NOT NULL COMMENT '周期键：2026-09 / 2026-W38 / 2026-09-15',
|
||
metrics_json LONGTEXT DEFAULT NULL COMMENT '指标 JSON（计数/均值/分布）',
|
||
anonymized TINYINT NOT NULL DEFAULT 1 COMMENT '是否匿名聚合 0否1是（KDB 侧强制 1）',
|
||
sample_size INT NOT NULL DEFAULT 0 COMMENT '样本量',
|
||
computed_at DATETIME DEFAULT NULL COMMENT '计算时间',
|
||
create_time DATETIME DEFAULT NULL COMMENT '入库时间（append-only，无 update_time）',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_pbl_analytics_snap_code (snap_code),
|
||
UNIQUE KEY uk_pbl_analytics_snap_dim (tenant_id, metric_group, scope_type, scope_code, period_key),
|
||
KEY idx_pbl_analytics_snap_tid_group (tenant_id, metric_group, period_key),
|
||
KEY idx_pbl_analytics_snap_tid_scope (tenant_id, scope_type, scope_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='PBL 分析看板快照（append-only）';
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- =====================================================================
|
||
-- 契约自检（供 build.sh / QC grep 核验）
|
||
-- 表总数：36（pbl_governance 3 + pbl_blueprint 11 + pbl_template 2 + pbl_validation 3
|
||
-- + pbl_compiler 4 + pbl_agent_runtime 4 + pbl_evidence 4 + pbl_assessment 3
|
||
-- + pbl_domain_ext 1 + pbl_analytics 1 = 36）
|
||
-- append-only 表（11，无 update_time）：
|
||
-- pbl_audit_log / pbl_blueprint_version / pbl_subobject_review / pbl_blueprint_publish
|
||
-- pbl_validation_finding / pbl_compile_log / pbl_agent_message / pbl_agent_tool_call
|
||
-- pbl_agent_arbitration / pbl_evidence_idem / pbl_analytics_snapshot
|
||
-- 零命中项（mariadb 方言硬约束）：
|
||
-- FOREIGN KEY / REFERENCES / ENUM( / SERIAL / BIGSERIAL / nextval / TIMESTAMP
|
||
-- 全部索引以 tenant_id 打头；每表 id BIGINT NOT NULL AUTO_INCREMENT + xxx_code 唯一业务主键。
|
||
-- =====================================================================
|