pipeline-opportunity/mysql.ddl.sql

-- /home/ymq/work/repos/pipeline-opportunity/models/opp_reports.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_reports;
CREATE TABLE opp_reports
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `project_id` VARCHAR(32)   comment '项目ID',
  `software` VARCHAR(128) NOT NULL  comment '软件/主题',
  `title` VARCHAR(255) NOT NULL  comment '报告标题',
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   comment '报告正文',
  `status` VARCHAR(24) NOT NULL  DEFAULT 'draft' comment '报告状态',
  `report_type` VARCHAR(16)   DEFAULT 'research' comment '报告类型(research调研/feasibility可行性研究)',
  `confirm_task_id` VARCHAR(32)   comment '确认任务ID',
  `ppt_path` VARCHAR(500)   comment 'PPT文件路径',
  `confirmed_by` VARCHAR(64)   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 '研发报告'
;


-- /home/ymq/work/repos/pipeline-opportunity/models/opp_demand_atoms.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_demand_atoms;
CREATE TABLE opp_demand_atoms
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `cluster_id` VARCHAR(32) NOT NULL  comment '所属类别ID',
  `org_id` VARCHAR(32) NOT NULL  comment '机构ID',
  `snap_id` VARCHAR(32) NOT NULL  comment '来源需求快照ID',
  `atom_text` VARCHAR(255) NOT NULL  comment '原子需求文本(LLM提取)',
  `std_demand_id` VARCHAR(32)   comment '归一后标准需求ID',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL  comment '创建时间'


,primary key(id)


)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
engine=innodb 
comment '需求原子(类内LLM原子化结果)'
;


-- /home/ymq/work/repos/pipeline-opportunity/models/opp_approvals.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_approvals;
CREATE TABLE opp_approvals
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `report_id` VARCHAR(32) NOT NULL  comment '报告ID',
  `project_id` VARCHAR(32)   comment '项目ID',
  `status` VARCHAR(16) NOT NULL  DEFAULT 'initiated' comment '审批状态',
  `note` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   comment '审批说明',
  `created_by` VARCHAR(64)   comment '发起人',
  `resolved_by` VARCHAR(64)   comment '审批人',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL  comment '创建时间',
  `resolved_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP   comment '结论时间'


,primary key(id)


)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
engine=innodb 
comment '研发审批'
;


-- /home/ymq/work/repos/pipeline-opportunity/models/opp_clusters.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_clusters;
CREATE TABLE opp_clusters
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `batch_id` VARCHAR(32) NOT NULL  comment '批次ID',
  `org_id` VARCHAR(32) NOT NULL  comment '机构ID',
  `project_id` VARCHAR(32)   comment '项目ID(空=平台级挖掘)',
  `name` VARCHAR(128)   comment '类别名称(LLM命名)',
  `doc_count` int NOT NULL  DEFAULT '0' comment '类内需求数',
  `share` double(6,4)   DEFAULT '0' comment '占比',
  `heat_rank` int   DEFAULT '0' comment '热度排名',
  `naming_evidence` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   comment '命名证据(样例标题JSON)',
  `centroid_snap_id` VARCHAR(32)   comment '质心需求快照ID(代表样本)',
  `normalize_status` VARCHAR(16)   DEFAULT 'init' comment '归一化状态(init/running/done/failed)',
  `normalize_error` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   comment '归一化失败原因',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL  comment '创建时间'


,primary key(id)


)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
engine=innodb 
comment '需求类别(聚类结果)'
;


-- /home/ymq/work/repos/pipeline-opportunity/models/opp_demand_domains.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_demand_domains;
CREATE TABLE opp_demand_domains
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `cluster_id` VARCHAR(32) NOT NULL  comment '所属类别ID',
  `org_id` VARCHAR(32) NOT NULL  comment '机构ID',
  `parent_id` VARCHAR(32)   comment '父能力域ID(空=顶层)',
  `name` VARCHAR(128) NOT NULL  comment '能力域名(如:人员管理/工时与考勤)',
  `std_count` int NOT NULL  DEFAULT '0' comment '下辖标准需求数',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL  comment '创建时间'


,primary key(id)


)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
engine=innodb 
comment '需求能力域(两层需求模型上层)'
;


-- /home/ymq/work/repos/pipeline-opportunity/models/opp_std_demands.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_std_demands;
CREATE TABLE opp_std_demands
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `cluster_id` VARCHAR(32) NOT NULL  comment '所属类别ID',
  `org_id` VARCHAR(32) NOT NULL  comment '机构ID',
  `name` VARCHAR(128) NOT NULL  comment '标准需求名(LLM裁决命名)',
  `domain_id` VARCHAR(32)   comment '能力域ID(两层模型上层)',
  `doc_count` int NOT NULL  DEFAULT '0' comment '覆盖文档数',
  `coverage` double(6,4)   DEFAULT '0' comment '覆盖率(覆盖文档数/类内文档数)',
  `tier` VARCHAR(16)   DEFAULT 'personal' comment '层级(core核心/ext扩展/personal个性)',
  `evidence_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   comment '证据(原子样例JSON)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL  comment '创建时间'


,primary key(id)


)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
engine=innodb 
comment '标准需求(归一化结果)'
;


-- /home/ymq/work/repos/pipeline-opportunity/models/opp_mining_batches.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_mining_batches;
CREATE TABLE opp_mining_batches
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `org_id` VARCHAR(32) NOT NULL  comment '机构ID',
  `project_id` VARCHAR(32)   comment '项目ID(空=平台级挖掘)',
  `scope` VARCHAR(16) NOT NULL  DEFAULT 'top' comment '挖掘范围(top=TopX全量/targeted=指定类型)',
  `params_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   comment '批次参数(days/sources/record_type/keyword/category)',
  `status` VARCHAR(24) NOT NULL  DEFAULT 'pulling' comment '批次状态(pulling/embedding/clustering/naming/done/failed)',
  `vdb_col` VARCHAR(64)   comment '批次向量工作集collection名',
  `stats_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   comment '批次统计(需求数/嵌入数/簇数/失败数)',
  `error_msg` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   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 '需求挖掘批次'
;


-- /home/ymq/work/repos/pipeline-opportunity/models/opp_demand_snap.json





-- 建库时请用以下语句，支持emoji字符
-- CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
drop table if exists opp_demand_snap;
CREATE TABLE opp_demand_snap
(

  `id` VARCHAR(32) NOT NULL  comment '主键ID',
  `batch_id` VARCHAR(32) NOT NULL  comment '批次ID',
  `org_id` VARCHAR(32) NOT NULL  comment '机构ID',
  `src_id` VARCHAR(64) NOT NULL  comment '爬虫平台需求ID(去重键)',
  `source` VARCHAR(32)   comment '数据来源(zbj/epwk/freelancer/pph)',
  `title` VARCHAR(500) NOT NULL  comment '需求标题',
  `item_category` VARCHAR(128)   comment '站内类目',
  `budget_wan` double(15,2)   comment '预算(万元)',
  `url` VARCHAR(500)   comment '需求原文链接',
  `publish_time` VARCHAR(32)   comment '发布时间',
  `cluster_id` VARCHAR(32)   comment '所属类别ID(聚类后回填)',
  `embed_status` VARCHAR(16)   DEFAULT 'new' comment '向量化状态(cached/new/failed)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL  comment '创建时间'


,primary key(id)


)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
engine=innodb 
comment '需求快照'
;