Hermes CI Fix
9d9a79545a
3 new tables + idempotent DDL migration (prod guard: refuses DB name cma without approval): tax_filing_log (P_TAX_TIMELY), hr_monthly_ledger (L_EMPLOYEE_TURNOVER), licence_ledger (P_LICENCE_OK). Entry API /api/cma/ext-ledger: create / CSV batch import / query by entity+period / derive trigger. on_time is server-derived only; HR rows rejected when end != begin + hires - separations. Deriver writes kpi_values only (source_type=derived, idempotent upsert); denominator 0 returns None and writes nothing; never creates or changes kpi_definitions. Tests: 22 new pytest cases (22 passed); scratch-DB e2e battery 28 assertions PASS with zero production write.
78 lines
5.0 KiB
SQL
78 lines
5.0 KiB
SQL
-- ============================================================================
|
||
-- 外部输入台账 DDL（能力评价 M2 准入3 派生）
|
||
-- 任务: 0-P0-project-20260915-cma-eval-m2-ext-input-tables
|
||
-- 日期: 2026-09-15
|
||
--
|
||
-- 三张**外部输入**台账 = 能力评价 3 项缺口值的唯一合法取证来源（PRD v1.3 §4.0）：
|
||
-- tax_filing_log 申报行为台账 → P_TAX_TIMELY (A4 税务申报及时率, 月频)
|
||
-- hr_monthly_ledger 人事月度台账 → L_EMPLOYEE_TURNOVER (B5 员工流失率, 月频)
|
||
-- licence_ledger 证照台账 → P_LICENCE_OK (B2 经营许可合规率, 季频)
|
||
--
|
||
-- 执行方式（**勿直接 mysql < 本文件**，列级/表级幂等由脚本保证）：
|
||
-- backend/scripts/migrate_ext_input_ledgers_20260915.py --check # 只读预检（默认）
|
||
-- backend/scripts/migrate_ext_input_ledgers_20260915.py --apply # 建表（幂等）
|
||
-- backend/scripts/migrate_ext_input_ledgers_20260915.py --show-create # 验收证据
|
||
-- 生产库（cma）执行须审批后另派：脚本对 DB_NAME=='cma' 默认拒绝 --apply/--down
|
||
--
|
||
-- 设计纪律：
|
||
-- * 只新增表，不改既有表；不改任何 KPI 定义（kpi_definitions 属准入2/4 同表串行）
|
||
-- * 每表 entity_id NOT NULL，唯一键一律以 entity_id 打头（多租户隔离 + 索引可用性）
|
||
-- * on_time 为服务端派生字段（app/services/ext_input_derive.derive_on_time），录入方不得手填
|
||
-- * 不建物理外键（业务关系列走软链名），保持 ORM 声明 == DDL 实况
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `tax_filing_log` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`entity_id` INT NOT NULL COMMENT '企业ID（账套）',
|
||
`period` VARCHAR(20) NOT NULL COMMENT '属期 YYYY-MM（申报所属月份）',
|
||
`tax_type` VARCHAR(50) NOT NULL COMMENT '税种: 增值税/附加税/企业所得税/印花税…',
|
||
`legal_deadline` DATE NULL COMMENT '法定申报期限',
|
||
`filed_date` DATE NULL COMMENT '实际申报日（未申报为 NULL）',
|
||
`on_time` INT NULL COMMENT '是否按时申报 1/0（服务端派生，录入方不得手填）',
|
||
`evidence_no` VARCHAR(100) NULL COMMENT '申报回执/截图编号',
|
||
`registered_by` VARCHAR(100) NULL COMMENT '登记人（职能岗位）',
|
||
`remark` VARCHAR(500) NULL COMMENT '备注',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_tax_filing_log_entity_period_type` (`entity_id`,`period`,`tax_type`),
|
||
KEY `ix_tax_filing_log_entity_id` (`entity_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='申报行为台账（P_TAX_TIMELY 取证来源）';
|
||
|
||
CREATE TABLE IF NOT EXISTS `hr_monthly_ledger` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`entity_id` INT NOT NULL COMMENT '企业ID（账套）',
|
||
`period` VARCHAR(20) NOT NULL COMMENT '期间 YYYY-MM',
|
||
`headcount_begin` INT NOT NULL COMMENT '期初在职人数',
|
||
`hires` INT NOT NULL DEFAULT 0 COMMENT '入职人数',
|
||
`separations` INT NOT NULL DEFAULT 0 COMMENT '离职人数',
|
||
`headcount_end` INT NOT NULL COMMENT '期末在职人数（须= 期初+入职-离职）',
|
||
`registered_by` VARCHAR(100) NULL COMMENT '登记人（职能岗位）',
|
||
`remark` VARCHAR(500) NULL COMMENT '备注',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_hr_monthly_ledger_entity_period` (`entity_id`,`period`),
|
||
KEY `ix_hr_monthly_ledger_entity_id` (`entity_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='人事月度台账（L_EMPLOYEE_TURNOVER 取证来源）';
|
||
|
||
CREATE TABLE IF NOT EXISTS `licence_ledger` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`entity_id` INT NOT NULL COMMENT '企业ID（账套）',
|
||
`licence_name` VARCHAR(200) NOT NULL COMMENT '证照名称',
|
||
`licence_no` VARCHAR(100) NOT NULL COMMENT '证照编号',
|
||
`valid_from` DATE NULL COMMENT '有效期起',
|
||
`valid_to` DATE NULL COMMENT '有效期止（空=长期有效）',
|
||
`annual_check_status` VARCHAR(20) NOT NULL DEFAULT '未到期' COMMENT '年检状态: 未到期/已年检/待年检/过期',
|
||
`issuer` VARCHAR(200) NULL COMMENT '发证/主管机关',
|
||
`owner_role` VARCHAR(100) NULL COMMENT '责任岗位（职能，不指定自然人）',
|
||
`attachment_no` VARCHAR(100) NULL COMMENT '附件/扫描件编号',
|
||
`registered_by` VARCHAR(100) NULL COMMENT '登记人（职能岗位）',
|
||
`remark` VARCHAR(500) NULL COMMENT '备注',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_licence_ledger_entity_name_no` (`entity_id`,`licence_name`,`licence_no`),
|
||
KEY `ix_licence_ledger_entity_id` (`entity_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='证照台账（P_LICENCE_OK 取证来源）';
|