yumoqing/pipeline-bidding
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
pipeline-bidding/mysql.ddl.sql
yumoqing 44f8c18ac3 feat(bidding): 产线起点改为上传招标文件+解析产出QC契合度审核门禁 
清理（前三步归商机产线）:
- 删邮箱采集: bid_mail.py/bid_mail_accounts表与页面/首页按钮
- 删招标审批状态机: TD_*/HT_TENDER_APPROVAL/摘要审批工具族
- bid_tenders瘦身为解析抽取的商务要素(去邮件字段)

QC契合度审核:
- 新表bid_qc_reviews(qc_type/round/fit_score/pass_score/passed/improvement)
- bid_qc_capability: start_qc/finish_qc/list_qc/qc_report, 四类产出逐类审核
- 判定: 10分制打分, >9.5放行(参数bid_qc_pass_score); 不通过给改进意见
  +清空产出+对账器重派解析任务带意见重做; 超轮次抛qc_escalation人工
- bid_flow对账器A2门禁阶段: 幂等/在办去重/轮次守卫/重做不计首跑上限
- 驾驶舱: qc_report工具+解析QC审核菜单; 参数读appbase表+默认兜底
2026-08-31 13:37:49 +08:00

509 lines
18 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_scoring_items.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_scoring_items;
CREATE TABLE bid_scoring_items
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`section` VARCHAR(32) NOT NULL DEFAULT 'technical' comment '评分部分',
`item_no` VARCHAR(32) comment '评分项编号',
`item_name` VARCHAR(255) NOT NULL comment '评分项名称',
`max_score` double(8,2) NOT NULL DEFAULT '0' comment '满分',
`scoring_rule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '得分规则',
`is_veto` VARCHAR(1) NOT NULL DEFAULT '0' comment '否决项',
`weight` double(8,4) comment '权重',
`source_ref` VARCHAR(500) comment '招标文件出处',
`target_chapter_no` VARCHAR(32) comment '对应章节号',
`order_no` int DEFAULT '0' 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 bid_scoring_items_idx_bsi_project ON bid_scoring_items(project_id);
CREATE INDEX bid_scoring_items_idx_bsi_section ON bid_scoring_items(section);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_doc_requirements.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_doc_requirements;
CREATE TABLE bid_doc_requirements
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`req_type` VARCHAR(32) NOT NULL DEFAULT 'structure' comment '要求类型',
`chapter_no` VARCHAR(32) comment '章节号',
`chapter_title` VARCHAR(255) comment '章节标题',
`requirement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '要求内容',
`page_limit` int comment '页数限制',
`source_ref` VARCHAR(500) comment '招标文件出处',
`order_no` int DEFAULT '0' 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 bid_doc_requirements_idx_bdr_project ON bid_doc_requirements(project_id);
CREATE INDEX bid_doc_requirements_idx_bdr_type ON bid_doc_requirements(req_type);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_members.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_members;
CREATE TABLE bid_members
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`user_id` VARCHAR(32) comment '用户ID',
`user_name` VARCHAR(128) comment '姓名',
`member_role` VARCHAR(64) NOT NULL DEFAULT 'writer' comment '承担角色',
`duty` VARCHAR(500) comment '职责说明',
`status` VARCHAR(16) DEFAULT 'active' 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 bid_members_idx_bm_project ON bid_members(project_id);
CREATE INDEX bid_members_idx_bm_user ON bid_members(user_id);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_tender_files.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_tender_files;
CREATE TABLE bid_tender_files
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`tender_id` VARCHAR(32) comment '招标信息ID',
`file_name` VARCHAR(255) NOT NULL comment '文件名',
`file_path` VARCHAR(500) comment '文件路径',
`file_type` VARCHAR(32) NOT NULL DEFAULT 'tender_doc' comment '文件类型',
`file_size` int DEFAULT '0' comment '文件大小',
`content_text` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '正文提取',
`extract_status` VARCHAR(16) NOT NULL DEFAULT 'pending' comment '抽取状态',
`extract_note` VARCHAR(500) comment '抽取说明',
`uploaded_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 bid_tender_files_idx_btf_project ON bid_tender_files(project_id);
CREATE INDEX bid_tender_files_idx_btf_tender ON bid_tender_files(tender_id);
CREATE INDEX bid_tender_files_idx_btf_status ON bid_tender_files(extract_status);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_chapters.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_chapters;
CREATE TABLE bid_chapters
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`chapter_no` VARCHAR(32) NOT NULL comment '章节号',
`parent_no` VARCHAR(32) comment '父章节号',
`title` VARCHAR(255) NOT NULL comment '章节标题',
`section` VARCHAR(32) DEFAULT 'technical' comment '所属部分',
`scoring_item_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '对应评分项(JSON)',
`outline` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '章节要点',
`status` VARCHAR(16) NOT NULL DEFAULT 'pending' comment '章节状态',
`version` int NOT NULL DEFAULT '1' comment '版本号',
`content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '章节正文',
`file_path` VARCHAR(500) comment '章节文件路径',
`word_count` int DEFAULT '0' comment '字数',
`review_score` double(8,2) comment '评审得分',
`max_score` double(8,2) comment '对应满分',
`review_comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '评审意见/改进意见',
`revise_count` int NOT NULL DEFAULT '0' comment '重做次数',
`assignee_role` VARCHAR(64) DEFAULT 'agent.bid_writer' comment '承办角色',
`task_id` VARCHAR(32) comment '关联任务ID',
`order_no` int DEFAULT '0' 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 bid_chapters_idx_bc_project ON bid_chapters(project_id);
CREATE INDEX bid_chapters_idx_bc_status ON bid_chapters(status);
CREATE INDEX bid_chapters_idx_bc_no ON bid_chapters(project_id,chapter_no);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_reviews.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_reviews;
CREATE TABLE bid_reviews
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`scope` VARCHAR(16) NOT NULL DEFAULT 'chapter' comment '评审范围',
`chapter_id` VARCHAR(32) comment '章节ID',
`document_id` VARCHAR(32) comment '标书ID',
`round` int NOT NULL DEFAULT '1' comment '评审轮次',
`reviewer` VARCHAR(64) comment '评审方',
`review_type` VARCHAR(16) DEFAULT 'agent' comment '评审类型',
`score` double(8,2) comment '得分',
`max_score` double(8,2) comment '满分',
`pass_ratio` double(5,4) comment '通过门限',
`passed` VARCHAR(1) NOT NULL DEFAULT '0' comment '是否通过',
`comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '改进意见',
`detail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '评审明细(JSON)',
`task_id` VARCHAR(32) comment '关联任务ID',
`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 bid_reviews_idx_br_project ON bid_reviews(project_id);
CREATE INDEX bid_reviews_idx_br_scope ON bid_reviews(scope);
CREATE INDEX bid_reviews_idx_br_chapter ON bid_reviews(chapter_id);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_tenders.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_tenders;
CREATE TABLE bid_tenders
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`org_id` VARCHAR(32) NOT NULL DEFAULT '0' comment '机构ID',
`project_id` VARCHAR(32) comment '项目ID',
`source` VARCHAR(16) NOT NULL DEFAULT 'tender_doc' comment '来源',
`title` VARCHAR(255) comment '项目名称',
`purchaser` VARCHAR(255) comment '招标人/采购人',
`tender_no` VARCHAR(64) comment '招标编号',
`industry` VARCHAR(64) comment '行业',
`summary` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '项目概况摘要',
`bid_start_at` VARCHAR(16) comment '投标开始时间',
`bid_deadline` VARCHAR(16) comment '投标截止时间',
`project_period` VARCHAR(64) comment '项目周期',
`budget_amount` double(14,2) comment '招标金额',
`deposit_amount` double(14,2) comment '投标保证金',
`qualification_req` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '资质要求摘要',
`key_dates` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '关键节点(JSON)',
`risk_note` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '风险提示',
`status` VARCHAR(16) DEFAULT 'summarized' comment '状态',
`created_by` VARCHAR(64) 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 bid_tenders_idx_bt_org ON bid_tenders(org_id);
CREATE INDEX bid_tenders_idx_bt_project ON bid_tenders(project_id);
CREATE INDEX bid_tenders_idx_bt_deadline ON bid_tenders(bid_deadline);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_documents.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_documents;
CREATE TABLE bid_documents
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`version` int NOT NULL DEFAULT '1' comment '版本号',
`doc_name` VARCHAR(255) comment '标书名称',
`file_path` VARCHAR(500) comment '文件路径',
`page_count` int DEFAULT '0' comment '页数',
`chapter_count` int DEFAULT '0' comment '章节数',
`total_score` double(8,2) comment '总得分',
`max_score` double(8,2) comment '总满分',
`pass_ratio` double(5,4) comment '通过门限',
`status` VARCHAR(16) NOT NULL DEFAULT 'draft' comment '状态',
`review_id` VARCHAR(32) comment '评审记录ID',
`task_id` VARCHAR(32) 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 '合成标书'
;
CREATE INDEX bid_documents_idx_bdoc_project ON bid_documents(project_id);
CREATE INDEX bid_documents_idx_bdoc_status ON bid_documents(status);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_kb_docs.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_kb_docs;
CREATE TABLE bid_kb_docs
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`org_id` VARCHAR(32) NOT NULL DEFAULT '0' comment '机构ID',
`doc_type` VARCHAR(32) NOT NULL DEFAULT 'qualification' comment '文档类型',
`doc_name` VARCHAR(255) NOT NULL comment '文档名称',
`doc_no` VARCHAR(64) comment '证书/合同编号',
`tags` VARCHAR(500) comment '标签(逗号分隔)',
`file_path` VARCHAR(500) comment '文件路径',
`content_text` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '正文内容',
`issuer` VARCHAR(255) comment '颁发/签约方',
`customer` VARCHAR(255) comment '案例客户',
`contract_amount` double(18,2) comment '合同金额',
`valid_from` date comment '生效日期',
`valid_until` date comment '有效期至',
`status` VARCHAR(16) DEFAULT 'active' 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 bid_kb_docs_idx_bkd_org ON bid_kb_docs(org_id);
CREATE INDEX bid_kb_docs_idx_bkd_type ON bid_kb_docs(doc_type);
CREATE INDEX bid_kb_docs_idx_bkd_status ON bid_kb_docs(status);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_scores.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_scores;
CREATE TABLE bid_scores
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`review_id` VARCHAR(32) comment '评审记录ID',
`scoring_item_id` VARCHAR(32) comment '评分项ID',
`item_name` VARCHAR(255) comment '评分项名称',
`score` double(8,2) comment '得分',
`max_score` double(8,2) comment '满分',
`reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '评分理由',
`improvement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '改进意见',
`target_chapter_id` VARCHAR(32) comment '待改章节ID',
`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 bid_scores_idx_bs_project ON bid_scores(project_id);
CREATE INDEX bid_scores_idx_bs_review ON bid_scores(review_id);
CREATE INDEX bid_scores_idx_bs_chapter ON bid_scores(target_chapter_id);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_qualifications.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_qualifications;
CREATE TABLE bid_qualifications
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`qual_name` VARCHAR(255) NOT NULL comment '资质名称',
`requirement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '资质要求原文',
`is_mandatory` VARCHAR(1) NOT NULL DEFAULT '1' comment '是否必需',
`owner` VARCHAR(16) NOT NULL DEFAULT 'kb' comment '提供方',
`kb_doc_id` VARCHAR(32) comment '知识库文档ID',
`match_status` VARCHAR(16) NOT NULL DEFAULT 'pending' comment '匹配状态',
`file_path` VARCHAR(500) comment '文档路径',
`note` VARCHAR(500) comment '说明',
`human_task_id` VARCHAR(32) comment '人类任务ID',
`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 bid_qualifications_idx_bq_project ON bid_qualifications(project_id);
CREATE INDEX bid_qualifications_idx_bq_match ON bid_qualifications(match_status);
-- /home/ymq/work/pipeline/pipeline-bidding/models/bid_qc_reviews.json
-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists bid_qc_reviews;
CREATE TABLE bid_qc_reviews
(
`id` VARCHAR(32) NOT NULL comment '主键ID',
`project_id` VARCHAR(32) NOT NULL comment '项目ID',
`qc_type` VARCHAR(32) NOT NULL comment '审核对象',
`round` int NOT NULL DEFAULT '1' comment '审核轮次',
`reviewer` VARCHAR(64) comment '审核方',
`review_type` VARCHAR(16) DEFAULT 'agent' comment '审核类型',
`fit_score` double(4,2) comment '契合度得分(10分制)',
`pass_score` double(4,2) comment '通过分',
`passed` VARCHAR(1) NOT NULL DEFAULT '0' comment '是否通过',
`improvement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '改进意见',
`comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci comment '审核说明',
`task_id` VARCHAR(32) comment '关联任务ID',
`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 '解析产出QC审核记录'
;
CREATE INDEX bid_qc_reviews_idx_bqr_project ON bid_qc_reviews(project_id);
CREATE INDEX bid_qc_reviews_idx_bqr_type ON bid_qc_reviews(qc_type);
Reference in New Issue View Git Blame Copy Permalink