---
name: "ddl-layered-structure"
version: "1.0.0"
origin: "captured"
generation: 0
parent_skill_ids: []
status: "stable"
description: "IMEX EOMS 数据库 DDL 分层结构规范。定义早期开发阶段（drop-and-create 模式）和上线后阶段（incremental 模式）下的目录结构、命名规则、完整性约束和合并视图生成规则。确保模块级全量 schema 与切片级增量 DDL 各司其职，避免索引/约束/注释在 drop-recreate 循环中丢失。"
trigger_phases: ["architecture", "implementation", "code-review", "schema-guardian"]
applicable_agents: ["Copilot Architect", "Copilot Code Gen Specialist", "Copilot Implementation", "Copilot Code Review", "Copilot Schema Guardian"]
priority: 10
---

# DDL 分层结构规范 (ddl-layered-structure)

> **适用范围**: IMEX EOMS 所有新建/修改数据库表的切片。
> **核心原则**: 每张表的 DDL 自包含（表定义 + 索引 + 注释 + 约束），不允许将索引/FK 散落到"补丁文件"；模块级合并视图只从切片级 DDL 合并产生，不手写。
> **关联**: `dict-biz-integration/SKILL.md`（字典种子）/ `physical-data-model-review/SKILL.md`（物理模型评审）。

---

## 1. 两个阶段、两种模式

| 阶段 | 模式 | 触发条件 | DDL 模板 |
|---|---|---|---|
| **早期开发** | `drop-and-create` | 模块未在生产环境上线，租户数据为空或可清空 | `DROP TABLE IF EXISTS` + `CREATE TABLE` |
| **上线后演进** | `incremental` | 模块已有真实租户数据 / 已有至少一次生产发布 | `ALTER TABLE` + Flyway/Liquibase 版本化迁移 |

**现状（2026-04）**: IMEX 整体处于早期开发阶段，采用 `drop-and-create` 模式。本 Skill 主要规范该阶段。`incremental` 模式的迁移路径在 §8 定义。

---

## 2. 目标目录结构（早期开发阶段）

```
mes-enreach-mom-base-server/doc/sql/
  bladex/                                        # 平台底座 DDL（BladeX 原生）
    bladex.mysql.all.create.sql                  # 平台全量（drop+create）
    bladex.postgresql.all.create.sql             # 多方言（如需）
  <module>/                                      # 业务模块目录
    schema/                                      # ① 表结构层
      <module>.mysql.all.create.sql              # 模块全量合并视图（drop+create）
      <module>-<entity>-ddl.sql                  # 单表 DDL（drop+create，自包含表+索引+FK+注释）
    dict-biz/                                    # ② 字典种子层（见 dict-biz-integration SKILL §11）
      V001__init-<dict-code>.sql
      V002__add-<dict-code>-<key>.sql
      references.md                              # 字典引用矩阵
      audit/                                     # 孤儿数据审计 SQL
        <dict-code>-orphan-check.sql
    test-seed/                                   # ③ 测试演示数据层
      <entity>-test-seed.sql                     # 测试/演示数据（可选，按切片生成）
    menu-seed.sql                                # ④ 菜单权限种子（按模块一份）
    role-grant.sql                               # ⑤ 角色授权（按模块一份，可选）
```

### 2.1 模块示例（以 CRM 为例）

```
doc/sql/crm/
  schema/
    crm.mysql.all.create.sql                     # CRM 模块所有表的合并视图
    crm-lead-ddl.sql                             # 线索域（crm_lead / crm_lead_pool_log / crm_lead_source / crm_lead_capture_task）
    crm-opportunity-ddl.sql                      # 商机域
    crm-contract-ddl.sql                         # 合同域
    crm-copper-price-ddl.sql                     # 铜价基准域
    crm-price-list-ddl.sql                       # 价目表域
    crm-m3-ddl.sql                               # 多态关联域
  dict-biz/
    V001__init-crm-lead-dicts.sql
    V002__init-crm-opportunity-dicts.sql
    V003__init-crm-contract-dicts.sql
    references.md
    audit/
      crm_lead_status-orphan-check.sql
  test-seed/
    crm-lead-test-seed.sql
    crm-price-list-test-seed.sql
    crm-opportunity-seed.sql
  menu-seed.sql
  role-grant.sql
```

---

## 3. 单表 DDL 的完整性要求（`<module>-<entity>-ddl.sql`）

> **核心约束**: 每个表 DDL 必须自包含。不允许将索引、FK、注释散落到独立补丁文件，因为补丁会在下次 drop-recreate 循环中消失。

### 3.1 必须包含

| 项 | 要求 | 示例 |
|---|---|---|
| `DROP TABLE IF EXISTS` | 每表前一行，drop-and-create 模式必需 | `DROP TABLE IF EXISTS \`crm_lead\`;` |
| `CREATE TABLE` | 完整列定义 | 略 |
| 列 `COMMENT` | 每个业务列必须有中文注释 | `\`lead_status\` VARCHAR(32) DEFAULT 'NEW' COMMENT '线索状态(dict:crm_lead_status)'` |
| 字典引用标记 | 字典字段注释包含 `(dict:<code>)` | 同上 |
| 主键 | `PRIMARY KEY (\`id\`)` | 略 |
| 唯一索引 | 业务编号字段必须 `UNIQUE KEY`（含 `tenant_id` 前缀） | `UNIQUE KEY \`uk_lead_code\` (\`lead_code\`, \`tenant_id\`)` |
| 租户索引 | `TenantEntity` 表必须有 `idx_tenant_id` | `KEY \`idx_tenant_id\` (\`tenant_id\`)` |
| 查询索引 | 列表筛选/排序字段必须有索引 | `KEY \`idx_lead_status\` (\`lead_status\`)` |
| FK 索引 | 所有 `*_id` 字段必须有 `KEY idx_<field>` | `KEY \`idx_owner_id\` (\`owner_id\`)` |
| 引擎 / 字符集 | `ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci` | 略 |
| 表 `COMMENT` | 表注释，中文业务名 | `COMMENT='线索主表'` |
| 审计字段 | `TenantEntity` 的全部 9 个字段（`tenant_id` `create_user` `create_dept` `create_time` `update_user` `update_time` `status` `is_deleted`）| 略 |

### 3.2 DDL 文件头部约定

```sql
-- ============================================================
-- Module : crm
-- Slice  : lead-lifecycle
-- Mode   : drop-and-create (early development)
-- Scope  : crm_lead, crm_lead_pool_log, crm_lead_source, crm_lead_capture_task
-- Depends on:
--   - bladex/bladex.mysql.all.create.sql (blade_user, blade_dept)
-- Dict-biz references:
--   - crm_lead_status, crm_lead_grade_level, crm_lead_industry_tag
--   - crm_lead_company_size, crm_lead_enterprise_nature, crm_lead_source_mode
--   - crm_lead_ai_churn_risk, crm_lead_pool_action_type, crm_lead_capture_task_type
-- Generated: 2026-04-24
-- ============================================================

-- Drop in reverse dependency order
DROP TABLE IF EXISTS `crm_lead_capture_task`;
DROP TABLE IF EXISTS `crm_lead_source`;
DROP TABLE IF EXISTS `crm_lead_pool_log`;
DROP TABLE IF EXISTS `crm_lead`;

-- Create in forward dependency order
CREATE TABLE `crm_lead` ( ... );
CREATE TABLE `crm_lead_pool_log` ( ... );
CREATE TABLE `crm_lead_source` ( ... );
CREATE TABLE `crm_lead_capture_task` ( ... );
```

### 3.3 禁止模式（反模式）

| 反模式 | 后果 | 正确做法 |
|---|---|---|
| `crm-lead-indexes.sql` 独立文件放索引 | 下次 drop 表后索引消失 | 索引内联到表 DDL 的 `CREATE TABLE` 里 |
| `crm-lead-fk-add.sql` 独立文件追加 FK | 同上 | FK（若使用）内联到表 DDL |
| `crm-lead-comments.sql` 独立文件补注释 | 同上 | 注释内联到 `CREATE TABLE` 列定义 |
| 只 `CREATE TABLE` 不 `DROP` | 重复执行报错 | drop-and-create 模式必须含 DROP |
| `CREATE TABLE IF NOT EXISTS` 替代 DROP | 修改列后不会生效 | 明确用 `DROP TABLE IF EXISTS` + `CREATE TABLE` |
| 审计字段缺失（如没有 `tenant_id`） | Entity 继承 TenantEntity 但表没列 → 运行时报错 | 按 §3.1 补齐 9 字段 |
| 缺 `COLLATE` | 租户多语言查询排序结果不稳定 | 明确 `COLLATE=utf8mb4_general_ci` |

---

## 4. 模块合并视图 `<module>.mysql.all.create.sql`

### 4.1 生成原则

**严禁手写**。从切片级 DDL 合并生成：

1. 扫描 `doc/sql/<module>/schema/*-ddl.sql`（排除合并视图本身）
2. 按以下顺序规则排序：
   - 若 `<module>/schema/.merge-order` 存在，按其声明顺序优先
   - 剩余文件按文件名字母序追加
3. 每个切片 DDL 已自包含"DROP + CREATE + 索引 + 注释"（§3），合并器不拆解 DROP/CREATE 段，按文件整体拼接
4. 输出到 `<module>.<dialect>.all.create.sql`，外层包裹 `SET FOREIGN_KEY_CHECKS=0/1` 保护壳

> 注：初版未引入基于 Depends on 注释的拓扑排序解析（易碎且对 6–10 文件无显著收益）。跨文件依赖通过 `.merge-order` 显式声明。若未来切片数 > 15 或发现字母序不足以表达依赖，再引入 DAG 解析。

### 4.2 合并脚本（已落地）

**工具路径**: `tools/blade/merge-ddl.sh` / `tools/blade/cli.sh merge-ddl <module>`

```bash
# 预览（不写文件）
tools/blade/cli.sh merge-ddl crm --dry-run

# 正式生成（默认读 doc/sql/<module>/schema/）
tools/blade/cli.sh merge-ddl crm

# 过渡期：模块尚未迁移到 schema/，指向扁平目录
tools/blade/cli.sh merge-ddl crm \
  --input-dir mes-enreach-mom-base-server/doc/sql/crm \
  --output    mes-enreach-mom-base-server/doc/sql/crm/crm.mysql.all.create.sql

# 其他方言
tools/blade/cli.sh merge-ddl crm --dialect postgresql
```

**.merge-order 示例**（放在 `schema/` 目录下）：

```text
# CRM schema merge order — dependency-first
# Parent-type tables must come before children
crm-lead-ddl.sql
crm-opportunity-ddl.sql
crm-contract-ddl.sql
crm-copper-price-ddl.sql
crm-price-list-ddl.sql
# m3 polymorphic interaction depends on lead/opportunity
crm-m3-ddl.sql
```

```bash
# （参考：原始接口说明）
# tools/blade/merge-ddl.sh <module>
# 输入: doc/sql/<module>/schema/*-ddl.sql
# 输出: doc/sql/<module>/schema/<module>.mysql.all.create.sql
```

### 4.3 合并视图的用途

- 开发环境首次建库：执行 `bladex.all.create.sql` + `<module>.mysql.all.create.sql`
- CI 验证：空库上执行合并视图应无错 → 证明 DDL 集自洽
- 新开发者本地环境初始化

### 4.4 合并视图不允许的内容

- 业务种子数据（放 `dict-biz/`、`test-seed/`、`menu-seed.sql`）
- 视图 / 存储过程（如需，独立 `<module>-views.sql`）
- 触发器（如需，独立 `<module>-triggers.sql`）

---

## 5. 切片 DDL 依赖声明

每个 `<module>-<entity>-ddl.sql` 头部必须声明依赖，让合并工具能构建 DAG：

```sql
-- Depends on:
--   - bladex/bladex.mysql.all.create.sql       # 依赖平台底座表
--   - crm/schema/crm-product-ddl.sql           # 依赖同模块其他切片表
--   - mdm/schema/mdm-product-ddl.sql           # 依赖跨模块表
```

### 5.1 跨模块依赖约束

- 允许下游模块依赖上游模块的表（如 CRM 依赖 MDM 的 `mdm_product`）
- **禁止**循环依赖（如 CRM ↔ SCM 互相 FK）
- **禁止**对平台 BladeX 表写 FK 约束（`blade_user` / `blade_dept` / `blade_role` 等），因为 BladeX 表由平台独立演进，业务模块不应绑定 FK

---

## 6. 与 BladeX 代码生成器的协作

`blade-develop` 的 `IGenerateService` 生成的 DDL 默认使用 `CREATE TABLE IF NOT EXISTS`。生成后需要：

1. 将 `CREATE TABLE IF NOT EXISTS` 替换为 `CREATE TABLE`
2. 在每个表前加 `DROP TABLE IF EXISTS`
3. 补齐 §3.2 的文件头部元数据
4. 检查索引是否覆盖 §3.1 的必须项
5. 按字典引用约定补注释 `(dict:<code>)`
6. 由 Schema Guardian 跑 `ddl-recreatability` 模式校验

`Copilot Code Gen Specialist` 在生成后必须执行以上 6 步再归档。

---

## 7. 测试数据 SQL（`test-seed/`）

### 7.1 命名与范围

```
doc/sql/<module>/test-seed/<entity>-test-seed.sql
```

每个文件只包含一个领域聚合的数据（如 `crm-lead-test-seed.sql` 包含 `crm_lead` 主表 + `crm_lead_pool_log` 子表 + `crm_lead_source` 配置）。

### 7.2 测试数据的幂等性

```sql
-- Step 1: 按 tenant_id + 业务编号前缀清理旧数据
DELETE FROM `crm_lead_pool_log` WHERE `lead_id` IN (
  SELECT id FROM `crm_lead` WHERE `lead_code` LIKE 'LEAD-DEMO-%' AND `tenant_id` = '000000'
);
DELETE FROM `crm_lead` WHERE `lead_code` LIKE 'LEAD-DEMO-%' AND `tenant_id` = '000000';

-- Step 2: 插入演示数据（ID 按模块规划段位）
INSERT INTO `crm_lead` (...) VALUES (...);
```

### 7.3 与字典 seed 的一致性

测试 SQL 中的枚举字段值必须与 `dict-biz/` 下 seed 中的 `dict_key` 一致。`Schema Guardian` 的 `dict-evolution` 模式会校验。

---

## 8. 上线后迁移到 incremental 模式

### 8.1 切换门槛

满足以下任一条件时，该模块从 `drop-and-create` 切到 `incremental`：

- 模块已产生真实租户业务数据（租户数 ≥ 1 且非测试租户）
- 模块已有至少一次生产发布版本标签
- 模块计划接入 Flyway/Liquibase 版本化迁移工具

### 8.2 切换步骤

1. 将现有 `<module>.mysql.all.create.sql` 快照为 `<module>/migrations/V001__baseline.sql`
2. 后续任何 schema 变更以 Flyway 风格增量文件维护：
   ```
   <module>/migrations/
     V001__baseline.sql
     V002__add-column-<table>-<column>.sql
     V003__add-index-<table>-<index>.sql
     V004__rename-column-<table>-<old>-to-<new>.sql
   ```
3. 原 `schema/*-ddl.sql` 可保留作为开发环境快速建库的捷径，但不再是"权威来源"；`migrations/` 成为权威
4. 合并视图 `<module>.mysql.all.create.sql` 改为从 `migrations/` 叠加生成

### 8.3 过渡期（部分模块已上线、部分仍在开发）

允许 `schema/` 与 `migrations/` 并存，由模块根目录的 `README.md` 声明当前模式：

```
doc/sql/crm/README.md
```

```markdown
# CRM 模块 DDL 状态
- Mode: drop-and-create (as of 2026-04)
- Authority source: schema/crm.mysql.all.create.sql
- Migration scheduled: 2026-Q3 after Wave-3 go-live
```

---

## 9. 交付检查清单

| # | 检查项 | 层级 | 验证方式 |
|---|---|---|---|
| DDL-01 | 每个 `<entity>-ddl.sql` 含 `DROP TABLE IF EXISTS` | DB | 文件头部 |
| DDL-02 | 每个表有完整列定义 + 索引 + 注释 + 字符集 | DB | 按 §3.1 核对 |
| DDL-03 | 业务编号字段有 `UNIQUE KEY` 且含 `tenant_id` | DB | `uk_*` 索引 |
| DDL-04 | `TenantEntity` 表含 9 个审计字段 | DB | 列清单核对 |
| DDL-05 | 每个 FK `*_id` 字段有 `idx_*` 索引 | DB | 索引核对 |
| DDL-06 | 字典字段注释含 `(dict:<code>)` 标记 | DB | 列注释扫描 |
| DDL-07 | 文件头部声明模块 / 切片 / 依赖 / 字典引用 | DB | §3.2 格式 |
| DDL-08 | 模块合并视图存在且仅从切片合并生成 | DB | 对比切片与合并视图的 CREATE 段 |
| DDL-09 | 合并视图在空库上可一次性跑通 | CI | 执行测试 |
| DDL-10 | 索引/FK/注释不出现在独立"补丁文件"中 | DB | 目录扫描 `*-index.sql` / `*-fk*.sql` |
| DDL-11 | 测试 SQL 的枚举值与字典 seed 一致 | DB | 对比 dict-biz seed |
| DDL-12 | 跨模块依赖无循环，FK 不指向 BladeX 平台表 | DB | 依赖 DAG 检查 |

---

## 10. 反模式（禁止）

| 反模式 | 正确做法 |
|---|---|
| 将索引单独放 `<module>-indexes.sql` | 索引内联到 `CREATE TABLE` |
| 补注释/补默认值放 `<module>-alters.sql` | 直接改原 DDL 重新 drop-recreate |
| 手写 `<module>.mysql.all.create.sql` | 从切片 DDL 按依赖合并生成 |
| 业务 FK 指向 `blade_user.id` | FK 仅用注释声明逻辑关系，不建物理 FK |
| `CREATE TABLE IF NOT EXISTS` 未配 `DROP` | drop-and-create 模式强制 DROP + CREATE |
| 测试数据混入 `<entity>-ddl.sql` | 测试数据必须放 `test-seed/` |
| 字典 seed 混入 `<module>.mysql.all.create.sql` | 字典 seed 必须放 `dict-biz/` |
| DDL 文件无头部元数据 | 强制 §3.2 头部声明 |

---

## 11. Agent 集成点

| Agent | 触发阶段 | 职责 |
|---|---|---|
| `Copilot Architect` | `architecture` | 设计阶段确认表归属模块、依赖模块，输出依赖 DAG |
| `Copilot Code Gen Specialist` | `implementation` | 生成后按 §6 六步调整 DDL |
| `Copilot Implementation` | `implementation` | 切片 DDL 落到正确目录层；调用合并脚本更新合并视图 |
| `Copilot Schema Guardian` | `post-implementation` / `pr-gate` / `ddl-recreatability` | 执行 §9 交付清单，特别是 DDL-08~DDL-12 |
| `Copilot Code Review` | `review` | 检查反模式（§10），阻止索引散落到补丁文件 |

---

## 12. 参考实现

- **平台底座**: `doc/sql/bladex/bladex.mysql.all.create.sql`
- **CRM 切片 DDL**: `doc/sql/crm/crm-lead-ddl.sql`（旧结构，待迁移到 `schema/`）
- **字典种子**: `doc/sql/crm/crm-dict-biz-seed.sql`（旧结构，待迁移到 `dict-biz/V*.sql`）
- **测试数据**: `doc/sql/crm/crm-price-list-test-seed.sql`（旧结构，待迁移到 `test-seed/`）
- **迁移试点**: 本 Skill 首个试点模块为 CRM Lead（见 `doc/sql/crm/schema/` / `dict-biz/` / `test-seed/` 目录）