yumoqing
f209b2a645
- cockpit_chat: SECURITY_RULES入口审查拒绝危险请求；new_task改pipeline_role_submit写任务表带role；answer_question意图+question_answer回填路由；_route_pending_questions每轮路由pending问题(主agent答/转客户) - cockpit_agent: 默认role改develop对齐归一化 - mysql.ddl.sql: 同步pipeline_agent_questions建表
290 lines
11 KiB
SQL
290 lines
11 KiB
SQL
|
||
-- models/sd_deploy_envs.json
|
||
|
||
|
||
|
||
|
||
|
||
-- 建库时请用以下语句，支持emoji字符
|
||
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
drop table if exists sd_deploy_envs;
|
||
CREATE TABLE sd_deploy_envs
|
||
(
|
||
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
|
||
`env_type` VARCHAR(20) NOT NULL comment '环境类型',
|
||
`host` VARCHAR(200) NOT NULL comment 'SSH主机地址',
|
||
`port` int NOT NULL DEFAULT '22' comment 'SSH端口',
|
||
`user` VARCHAR(100) NOT NULL comment 'SSH用户',
|
||
`ssh_key_path` VARCHAR(500) comment 'SSH密钥路径',
|
||
`sudo_enabled` VARCHAR(1) NOT NULL DEFAULT ''N'' comment '免密Sudo',
|
||
`deploy_path` VARCHAR(500) NOT NULL comment '部署目录',
|
||
`python_path` VARCHAR(500) comment 'Python路径',
|
||
`db_host` VARCHAR(200) comment '数据库地址',
|
||
`db_port` int DEFAULT '3306' comment '数据库端口',
|
||
`db_name` VARCHAR(100) comment '数据库名',
|
||
`db_user` VARCHAR(100) comment '数据库用户',
|
||
`db_password` VARCHAR(500) comment '数据库密码(加密)',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT ''configured'' comment '环境状态',
|
||
`verified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '最近验证时间',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '更新时间'
|
||
|
||
|
||
,primary key(id)
|
||
|
||
|
||
)
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci
|
||
engine=innodb
|
||
comment '部署环境表'
|
||
;
|
||
|
||
CREATE INDEX sd_deploy_envs_idx_sd_deploy_envs_project ON sd_deploy_envs(project_id);
|
||
CREATE INDEX sd_deploy_envs_idx_sd_deploy_envs_type ON sd_deploy_envs(env_type);
|
||
CREATE UNIQUE INDEX sd_deploy_envs_idx_sd_deploy_envs_unique ON sd_deploy_envs(project_id,env_type);
|
||
|
||
-- models/sd_test_plans.json
|
||
|
||
|
||
|
||
|
||
|
||
-- 建库时请用以下语句，支持emoji字符
|
||
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
drop table if exists sd_test_plans;
|
||
CREATE TABLE sd_test_plans
|
||
(
|
||
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`iteration_id` VARCHAR(32) NOT NULL comment '迭代ID',
|
||
`plan_name` VARCHAR(200) NOT NULL comment '方案名称',
|
||
`plan_type` VARCHAR(20) NOT NULL comment '方案类型',
|
||
`scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '测试范围',
|
||
`environment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '测试环境要求(JSON)',
|
||
`entry_criteria` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '准入条件',
|
||
`exit_criteria` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '准出条件',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT ''draft'' comment '方案状态',
|
||
`created_by` VARCHAR(32) comment '创建人',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '更新时间'
|
||
|
||
|
||
,primary key(id)
|
||
|
||
|
||
)
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci
|
||
engine=innodb
|
||
comment '测试方案表'
|
||
;
|
||
|
||
CREATE INDEX sd_test_plans_idx_sd_test_plans_iteration ON sd_test_plans(iteration_id);
|
||
CREATE INDEX sd_test_plans_idx_sd_test_plans_status ON sd_test_plans(status);
|
||
|
||
-- models/sd_test_cases.json
|
||
|
||
|
||
|
||
|
||
|
||
-- 建库时请用以下语句，支持emoji字符
|
||
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
drop table if exists sd_test_cases;
|
||
CREATE TABLE sd_test_cases
|
||
(
|
||
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`plan_id` VARCHAR(32) NOT NULL comment '方案ID',
|
||
`case_name` VARCHAR(200) NOT NULL comment '用例名称',
|
||
`case_type` VARCHAR(20) NOT NULL comment '用例类型',
|
||
`priority` VARCHAR(10) NOT NULL DEFAULT ''P2'' comment '优先级',
|
||
`precondition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '前置条件',
|
||
`steps` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '测试步骤(JSON数组)',
|
||
`expected_result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '预期结果',
|
||
`actual_result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '实际结果',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT ''pending'' comment '用例状态',
|
||
`executed_by` VARCHAR(32) comment '执行人',
|
||
`executed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '执行时间',
|
||
`duration_ms` int comment '执行耗时(毫秒)',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间'
|
||
|
||
|
||
,primary key(id)
|
||
|
||
|
||
)
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci
|
||
engine=innodb
|
||
comment '测试用例表'
|
||
;
|
||
|
||
CREATE INDEX sd_test_cases_idx_sd_test_cases_plan ON sd_test_cases(plan_id);
|
||
CREATE INDEX sd_test_cases_idx_sd_test_cases_status ON sd_test_cases(status);
|
||
CREATE INDEX sd_test_cases_idx_sd_test_cases_type ON sd_test_cases(case_type);
|
||
|
||
-- models/sd_iterations.json
|
||
|
||
|
||
|
||
|
||
|
||
-- 建库时请用以下语句，支持emoji字符
|
||
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
drop table if exists sd_iterations;
|
||
CREATE TABLE sd_iterations
|
||
(
|
||
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
|
||
`iteration_name` VARCHAR(200) NOT NULL comment '迭代名称',
|
||
`iteration_type` VARCHAR(20) NOT NULL DEFAULT ''new_feature'' comment '迭代类型',
|
||
`scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '迭代范围(JSON)',
|
||
`task_id` VARCHAR(32) comment '关联Pipeline任务ID',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT ''planning'' comment '迭代状态',
|
||
`priority` int NOT NULL DEFAULT '5' comment '优先级',
|
||
`started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '开始时间',
|
||
`completed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '完成时间',
|
||
`created_by` VARCHAR(32) comment '创建人',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '更新时间'
|
||
|
||
|
||
,primary key(id)
|
||
|
||
|
||
)
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci
|
||
engine=innodb
|
||
comment 'SDLC迭代表'
|
||
;
|
||
|
||
CREATE INDEX sd_iterations_idx_sd_iterations_project ON sd_iterations(project_id);
|
||
CREATE INDEX sd_iterations_idx_sd_iterations_status ON sd_iterations(status);
|
||
|
||
-- models/sd_bugs.json
|
||
|
||
|
||
|
||
|
||
|
||
-- 建库时请用以下语句，支持emoji字符
|
||
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
drop table if exists sd_bugs;
|
||
CREATE TABLE sd_bugs
|
||
(
|
||
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`iteration_id` VARCHAR(32) NOT NULL comment '迭代ID',
|
||
`case_id` VARCHAR(32) comment '关联用例ID',
|
||
`step_name` VARCHAR(100) comment '发现步骤名',
|
||
`title` VARCHAR(300) NOT NULL comment 'Bug标题',
|
||
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '详细描述',
|
||
`severity` VARCHAR(10) NOT NULL DEFAULT ''major'' comment '严重程度',
|
||
`priority` VARCHAR(10) NOT NULL DEFAULT ''P1'' comment '优先级',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT ''open'' comment 'Bug状态',
|
||
`reporter_type` VARCHAR(10) NOT NULL comment '提交人类型',
|
||
`reporter_id` VARCHAR(32) NOT NULL comment '提交人ID',
|
||
`assignee_id` VARCHAR(32) comment '处理人ID',
|
||
`fix_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '修复说明',
|
||
`fix_commit` VARCHAR(100) comment '修复Commit',
|
||
`verified_by` VARCHAR(32) comment '验证人',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '更新时间',
|
||
`closed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '关闭时间'
|
||
|
||
|
||
,primary key(id)
|
||
|
||
|
||
)
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci
|
||
engine=innodb
|
||
comment 'Bug管理表'
|
||
;
|
||
|
||
CREATE INDEX sd_bugs_idx_sd_bugs_iteration ON sd_bugs(iteration_id);
|
||
CREATE INDEX sd_bugs_idx_sd_bugs_status ON sd_bugs(status);
|
||
CREATE INDEX sd_bugs_idx_sd_bugs_severity ON sd_bugs(severity);
|
||
CREATE INDEX sd_bugs_idx_sd_bugs_assignee ON sd_bugs(assignee_id);
|
||
|
||
-- models/sd_projects.json
|
||
|
||
|
||
|
||
|
||
|
||
-- 建库时请用以下语句，支持emoji字符
|
||
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
drop table if exists sd_projects;
|
||
CREATE TABLE sd_projects
|
||
(
|
||
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`name` VARCHAR(200) NOT NULL comment '项目名称',
|
||
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '项目描述',
|
||
`project_type` VARCHAR(50) NOT NULL DEFAULT ''web_app'' comment '项目类型',
|
||
`tech_stack` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '技术栈配置(JSON)',
|
||
`repo_url` VARCHAR(500) comment '代码仓库地址',
|
||
`pipeline_id` VARCHAR(32) comment '关联Pipeline定义ID',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT ''draft'' comment '项目状态',
|
||
`org_id` VARCHAR(32) NOT NULL DEFAULT ''0'' comment '组织ID',
|
||
`created_by` VARCHAR(32) comment '创建人',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '更新时间'
|
||
|
||
|
||
,primary key(id)
|
||
|
||
|
||
)
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci
|
||
engine=innodb
|
||
comment 'SDLC项目表'
|
||
;
|
||
|
||
CREATE INDEX sd_projects_idx_sd_projects_status ON sd_projects(status);
|
||
CREATE INDEX sd_projects_idx_sd_projects_org ON sd_projects(org_id);
|
||
|
||
|
||
-- models/pipeline_agent_questions.json（与 pipeline-service 共用 pipeline 库）
|
||
drop table if exists pipeline_agent_questions;
|
||
CREATE TABLE pipeline_agent_questions
|
||
(
|
||
|
||
`id` VARCHAR(32) NOT NULL comment '主键ID',
|
||
`tenant_id` VARCHAR(32) NOT NULL comment '租户ID',
|
||
`task_id` VARCHAR(32) NOT NULL comment '任务ID',
|
||
`from_role` VARCHAR(32) NOT NULL comment '提问角色',
|
||
`question` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '问题内容',
|
||
`context` longtext comment '问题上下文(JSON)',
|
||
`answer` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '回答内容',
|
||
`answered_by` VARCHAR(64) comment '回答人',
|
||
`answer_source` VARCHAR(16) comment '回答来源(main_agent/customer)',
|
||
`status` VARCHAR(32) NOT NULL DEFAULT 'pending' comment '问题状态(pending/forwarded/answered)',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '创建时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL comment '更新时间'
|
||
|
||
|
||
,primary key(id)
|
||
|
||
|
||
)
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci
|
||
engine=innodb
|
||
comment '角色agent问题回路表'
|
||
;
|
||
|
||
CREATE INDEX pipeline_agent_questions_idx_paq_tenant ON pipeline_agent_questions(tenant_id);
|
||
CREATE INDEX pipeline_agent_questions_idx_paq_task ON pipeline_agent_questions(task_id);
|
||
CREATE INDEX pipeline_agent_questions_idx_paq_status ON pipeline_agent_questions(status);
|
||
|