starRiverProperty/docs/superpowers/plans/2026-05-01-database-schema-reference.md

# 数据库表结构参考手册 — 实施计划

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** 走查全部 5 个数据库（2 库直连 + 3 库代码推导），输出带 Mermaid ER 图 + 脱敏样本数据的 Markdown 参考手册。

**Architecture:** 分三层执行 — 数据库查询层（直连 INFORMATION_SCHEMA + SELECT 样本）、代码扫描层（并行读取全部 MyBatis Mapper XML 提取表/列/JOIN）、文档生成层（交叉验证 + 组装 Markdown）。直连库与代码扫描完全并行。

**Tech Stack:** MySQL CLI (`mysql`), Bash, Python 3（JSON 缓存）, Mermaid erDiagram

**Spec:** `docs/superpowers/specs/2026-05-01-database-schema-reference-design.md`

---

## 前置检查

- [ ] **Step 0: 确认数据库可达**

```bash
mysql -h 192.168.3.12 -P 3307 -u root -p123456 -e "SELECT VERSION(); SHOW DATABASES LIKE '%component%'; SHOW DATABASES LIKE '%elevator%';"
```

期望输出: MySQL 版本号 + `component-organization` 和 `cw-elevator-application` 两个库存在。

> 若失败：全量降级为代码推导模式（见 Task 4 备选路径）。

---

### Task 1: 采集组件组织库 schema + 样本

**Files:**
- Create: `docs/superpowers/data/component-organization/schema_raw.json`
- Create: `docs/superpowers/data/component-organization/tables.json`
- Create: `docs/superpowers/data/component-organization/samples/`

- [ ] **Step 1: 导出表清单**

```bash
mkdir -p docs/superpowers/data/component-organization/samples
mysql -h 192.168.3.12 -P 3307 -u root -p123456 component-organization -N -e \
"SELECT TABLE_NAME, IFNULL(TABLE_ROWS,0), ENGINE, IFNULL(TABLE_COMMENT,'') \
FROM INFORMATION_SCHEMA.TABLES \
WHERE TABLE_SCHEMA='component-organization' AND TABLE_TYPE='BASE TABLE' \
ORDER BY TABLE_NAME;" > docs/superpowers/data/component-organization/tables.tsv
```

- [ ] **Step 2: 导出全部列定义**

```bash
mysql -h 192.168.3.12 -P 3307 -u root -p123456 component-organization -N -e \
"SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, IFNULL(COLUMN_DEFAULT,'NULL'), COLUMN_KEY, EXTRA, IFNULL(COLUMN_COMMENT,'') \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_SCHEMA='component-organization' AND TABLE_NAME NOT LIKE 'QRTZ_%' AND TABLE_NAME NOT LIKE 'quartz_%' \
ORDER BY TABLE_NAME, ORDINAL_POSITION;" > docs/superpowers/data/component-organization/columns.tsv
```

- [ ] **Step 3: 导出索引**

```bash
mysql -h 192.168.3.12 -P 3307 -u root -p123456 component-organization -N -e \
"SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX \
FROM INFORMATION_SCHEMA.STATISTICS \
WHERE TABLE_SCHEMA='component-organization' \
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;" > docs/superpowers/data/component-organization/indexes.tsv
```

- [ ] **Step 4: 逐表采集样本（每表 3 行）**

```bash
# 读取表清单，排除系统表，对每张表执行 SELECT * LIMIT 3
while IFS=$'\t' read -r table_name rest; do
  if [[ "$table_name" != QRTZ_* ]] && [[ "$table_name" != quartz_* ]]; then
    echo "--- Extracting: $table_name ---"
    mysql -h 192.168.3.12 -P 3307 -u root -p123456 component-organization -t -e \
    "SELECT * FROM \`$table_name\` ORDER BY 1 DESC LIMIT 3;" \
    > "docs/superpowers/data/component-organization/samples/${table_name}.txt" 2>&1
  fi
done < <(cut -f1 docs/superpowers/data/component-organization/tables.tsv)
```

- [ ] **Step 5: 提交中间产物**

```bash
git add docs/superpowers/data/component-organization/
git commit -m "data: add component-organization schema raw dump and samples"
```

---

### Task 2: 采集电梯应用库 schema + 样本

**Files:**
- Create: `docs/superpowers/data/cw-elevator-application/schema_raw.json`
- Create: `docs/superpowers/data/cw-elevator-application/tables.json`
- Create: `docs/superpowers/data/cw-elevator-application/samples/`

- [ ] **Step 1: 导出表清单**

```bash
mkdir -p docs/superpowers/data/cw-elevator-application/samples
mysql -h 192.168.3.12 -P 3307 -u root -p123456 cw-elevator-application -N -e \
"SELECT TABLE_NAME, IFNULL(TABLE_ROWS,0), ENGINE, IFNULL(TABLE_COMMENT,'') \
FROM INFORMATION_SCHEMA.TABLES \
WHERE TABLE_SCHEMA='cw-elevator-application' AND TABLE_TYPE='BASE TABLE' \
ORDER BY TABLE_NAME;" > docs/superpowers/data/cw-elevator-application/tables.tsv
```

- [ ] **Step 2: 导出全部列定义**

```bash
mysql -h 192.168.3.12 -P 3307 -u root -p123456 cw-elevator-application -N -e \
"SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, IFNULL(COLUMN_DEFAULT,'NULL'), COLUMN_KEY, EXTRA, IFNULL(COLUMN_COMMENT,'') \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_SCHEMA='cw-elevator-application' AND TABLE_NAME NOT LIKE 'QRTZ_%' AND TABLE_NAME NOT LIKE 'quartz_%' \
ORDER BY TABLE_NAME, ORDINAL_POSITION;" > docs/superpowers/data/cw-elevator-application/columns.tsv
```

- [ ] **Step 3: 导出索引**

```bash
mysql -h 192.168.3.12 -P 3307 -u root -p123456 cw-elevator-application -N -e \
"SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX \
FROM INFORMATION_SCHEMA.STATISTICS \
WHERE TABLE_SCHEMA='cw-elevator-application' \
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;" > docs/superpowers/data/cw-elevator-application/indexes.tsv
```

- [ ] **Step 4: 逐表采集样本（每表 3 行）**

```bash
while IFS=$'\t' read -r table_name rest; do
  if [[ "$table_name" != QRTZ_* ]] && [[ "$table_name" != quartz_* ]]; then
    echo "--- Extracting: $table_name ---"
    mysql -h 192.168.3.12 -P 3307 -u root -p123456 cw-elevator-application -t -e \
    "SELECT * FROM \`$table_name\` ORDER BY 1 DESC LIMIT 3;" \
    > "docs/superpowers/data/cw-elevator-application/samples/${table_name}.txt" 2>&1
  fi
done < <(cut -f1 docs/superpowers/data/cw-elevator-application/tables.tsv)
```

- [ ] **Step 5: 提交中间产物**

```bash
git add docs/superpowers/data/cw-elevator-application/
git commit -m "data: add cw-elevator-application schema raw dump and samples"
```

---

### Task 3: 扫描所有 MyBatis Mapper XML 提取表/列/JOIN

**Files:**
- Create: `docs/superpowers/data/mapper_tables.tsv`

- [ ] **Step 1: 枚举所有 Mapper XML 文件**

```bash
find ./maven-cw-elevator-application ./maven-cwos-resource ./maven-ninca-crk ./maven-ninca-qk-alarm \
  -name "*Mapper.xml" -path "*/src/main/*" 2>/dev/null | sort \
  > docs/superpowers/data/mapper_files.txt
wc -l docs/superpowers/data/mapper_files.txt
```

期望输出: ~80+ 个 Mapper XML 文件。

- [ ] **Step 2: 从 Mapper XML 提取 INSERT/UPDATE/FROM 表名**

```bash
# 提取 INSERT INTO / UPDATE / FROM / JOIN 后的表名
> docs/superpowers/data/mapper_tables.tsv
while IFS= read -r xmlfile; do
  module=$(echo "$xmlfile" | cut -d'/' -f2)
  tablenames=$(grep -oP '(INSERT\s+INTO\s+|UPDATE\s+|FROM\s+|JOIN\s+)\s*\`?\K[a-z_][a-z0-9_]*' "$xmlfile" 2>/dev/null | sort -u | tr '\n' ',')
  echo -e "$module\t$xmlfile\t$tablenames"
done < docs/superpowers/data/mapper_files.txt > docs/superpowers/data/mapper_tables.tsv
```

- [ ] **Step 3: 提取 resultMap 列映射**

对每个 Mapper XML，读取 `<resultMap>` 中的 `<result column="..." property="...">` 提取列名。用下面脚本：

```bash
> docs/superpowers/data/mapper_columns.tsv
while IFS= read -r xmlfile; do
  cols=$(grep -oP '<result\s+column="\K[^"]+' "$xmlfile" 2>/dev/null | sort -u | tr '\n' ',')
  ids=$(grep -oP '<id\s+column="\K[^"]+' "$xmlfile" 2>/dev/null | sort -u | tr '\n' ',')
  echo -e "${xmlfile}\t${ids}|${cols}"
done < docs/superpowers/data/mapper_files.txt > docs/superpowers/data/mapper_columns.tsv
```

- [ ] **Step 4: 提取 JOIN 关系**

```bash
> docs/superpowers/data/mapper_joins.tsv
while IFS= read -r xmlfile; do
  joins=$(grep -oP '(LEFT\s+|RIGHT\s+|INNER\s+)?JOIN\s+\`?\K[a-z_][a-z0-9_]*\s+ON\s+\K[^;]+' "$xmlfile" 2>/dev/null | tr '\n' '|')
  if [ -n "$joins" ]; then
    echo -e "${xmlfile}\t${joins}"
  fi
done < docs/superpowers/data/mapper_files.txt > docs/superpowers/data/mapper_joins.tsv
```

- [ ] **Step 5: 扫描 ShardingSphere 分表配置**

```bash
grep -r "actual-data-nodes\|sharding-column\|table-strategy" \
  --include="*.properties" --include="*.yml" --include="*.yaml" \
  ./maven-cw-elevator-application/ ./maven-ninca-crk/ 2>/dev/null \
  > docs/superpowers/data/sharding_config.txt
```

- [ ] **Step 6: 提交**

```bash
git add docs/superpowers/data/mapper_*.tsv docs/superpowers/data/mapper_files.txt docs/superpowers/data/sharding_config.txt
git commit -m "data: add MyBatis mapper table/column/join extraction"
```

---

### Task 4: 代码推导不可达库的表结构

**Files:**
- Create: `docs/superpowers/data/ninca-crk-std/mapper_tables.tsv`
- Create: `docs/superpowers/data/alarm-deploy/mapper_tables.tsv`
- Create: `docs/superpowers/data/cwos-resource/mapper_tables.tsv`

**ninca_crk_std** 和 **alarm_deploy** 的 MyBatis mapper 路径在 `application.properties` 中声明：
- ninca-crk: `classpath*:cn/cloudwalk/data/**/mysql/*.xml,classpath*:cn/cloudwalk/task/data/**/mysql/*.xml`
- alarm: `classpath:cn/cloudwalk/data/**/mysql/*.xml`
- cwos-resource: Mapper XML 已在 Task 3 中扫描

- [ ] **Step 1: 从 ninca-crk 的 Mapper XML 提取表信息**

```bash
# ninca-crk 的 Mapper XML 位于 src/main/java 下（非 resources/mapper）
find ./maven-ninca-crk -name "*.xml" -path "*/mysql/*" 2>/dev/null | sort > docs/superpowers/data/ninca-crk-std/mapper_files.txt

while IFS= read -r xmlfile; do
  tablenames=$(grep -oP '(INSERT\s+INTO\s+|UPDATE\s+|FROM\s+|JOIN\s+)\s*\`?\K[a-z_][a-z0-9_]*' "$xmlfile" 2>/dev/null | sort -u | tr '\n' ',')
  echo -e "ninca-crk-std\t$xmlfile\t$tablenames"
done < docs/superpowers/data/ninca-crk-std/mapper_files.txt > docs/superpowers/data/ninca-crk-std/mapper_tables.tsv
```

- [ ] **Step 2: 从 alarm 的 Mapper XML 提取表信息**

```bash
find ./maven-ninca-qk-alarm -name "*.xml" -path "*/mysql/*" 2>/dev/null | sort > docs/superpowers/data/alarm-deploy/mapper_files.txt

while IFS= read -r xmlfile; do
  tablenames=$(grep -oP '(INSERT\s+INTO\s+|UPDATE\s+|FROM\s+|JOIN\s+)\s*\`?\K[a-z_][a-z0-9_]*' "$xmlfile" 2>/dev/null | sort -u | tr '\n' ',')
  echo -e "alarm-deploy\t$xmlfile\t$tablenames"
done < docs/superpowers/data/alarm-deploy/mapper_files.txt > docs/superpowers/data/alarm-deploy/mapper_tables.tsv
```

- [ ] **Step 3: 从 cwos-resource 的 Mapper XML 提取表信息**

```bash
# cwos-resource 有 db2/mysql/oracle 三个方言目录，以 mysql/ 为准
find ./maven-cwos-resource -name "*.xml" -path "*/mysql/*" 2>/dev/null | sort > docs/superpowers/data/cwos-resource/mapper_files.txt

while IFS= read -r xmlfile; do
  tablenames=$(grep -oP '(INSERT\s+INTO\s+|UPDATE\s+|FROM\s+|JOIN\s+)\s*\`?\K[a-z_][a-z0-9_]*' "$xmlfile" 2>/dev/null | sort -u | tr '\n' ',')
  echo -e "cwos-resource\t$xmlfile\t$tablenames"
done < docs/superpowers/data/cwos-resource/mapper_files.txt > docs/superpowers/data/cwos-resource/mapper_tables.tsv
```

- [ ] **Step 4: 提交**

```bash
git add docs/superpowers/data/ninca-crk-std/ docs/superpowers/data/alarm-deploy/ docs/superpowers/data/cwos-resource/
git commit -m "data: add code-derived table info for unreachable databases"
```

---

### Task 5: 交叉验证与生成最终 Markdown 文档

**Files:**
- Create: `docs/superpowers/specs/2026-05-01-database-schema-reference.md`（最终产物）

此任务通过 subagent 执行——将 Task 1-4 采集的原始数据 + 现有架构文档 + Mapper 扫描结果汇总为最终手册。

- [ ] **Step 1: 交付 subagent 生成文档**

委托 `deep` agent，传入以下 prompt：

```
TASK: 根据以下原始数据生成数据库表结构参考手册 Markdown 文件。

EXPECTED OUTCOME: 
- 文件写入 docs/superpowers/specs/2026-05-01-database-schema-reference.md
- 包含 9 个章节（见设计说明 §2）
- 每库有 Mermaid erDiagram ER 图
- 每表有列清单（列名、类型、可空、键、注释）
- 可连库的表有脱敏样本数据（Markdown 表格，≤3 行）
- 跨库关系总图
- 代码-表映射索引

REQUIRED TOOLS: Read, Write, Bash

MUST DO:
1. 读取设计说明: docs/superpowers/specs/2026-05-01-database-schema-reference-design.md
2. 读取现有架构文档: docs/architecture/租户组织人员访客-数据模型与用例.md
3. 读取 Task 1-2 的 schema 原始数据: docs/superpowers/data/component-organization/ 和 cw-elevator-application/ 下的 tables.tsv, columns.tsv, indexes.tsv
4. 读取 Task 1-2 的样本数据: samples/ 目录下各 .txt 文件
5. 读取 Task 3-4 的 Mapper 扫描结果: docs/superpowers/data/mapper_tables.tsv, mapper_columns.tsv, mapper_joins.tsv
6. 对每张表应用脱敏规则（设计说明 §5.3）：姓名截断、手机号截断、IP替换、密码列跳过、时间戳转换
7. 按设计说明 §4 规范绘制 Mermaid ER 图：业务关键列、三种关系线（约束/JOIN/跨库）、subgraph 分包
8. 交叉验证：列出「库中有但代码无 Mapper」的表（标注为运维/外部表），对比 DDL 与实际列
9. 对不可达库（ninca-crk-std, alarm-deploy, cwos-resource）显式标注「⚠️ 未连接生产库，从代码推导」
10. Mermaid 语法必须可渲染（erDiagram 关键字、正确的 {} 语法、||--o{ 关系线）
11. 文档中不出现 "TBD"、"TODO"、"待补充"

MUST NOT DO:
- 不要编造未在原始数据中出现的表名或列名
- 不要对不可达库声称有样本数据
- 不要包含系统表（QRTZ_*, quartz_*）
- 不要包含未脱敏的姓名/手机号/IP

CONTEXT:
- 仓库路径: /media/zebra/9e8fa357-7db6-4d70-88ed-d5de5a059a663/星河湾星中星/源码
- 数据库环境: 192.168.3.12:3307 (MySQL), user=root, password=123456
- 数据库: component-organization, cw-elevator-application (直连); ninca_crk_std, alarm_deploy, cwos_resource (代码推导)
- 现有架构文档已有 organization 库 5 表 + elevator 库 2 表的 ER 模型，以此为起点扩展
- MyBatis Mapper XML 路径已在 mapper_files.txt 中列出
```

- [ ] **Step 2: 验证文档完整性**

```bash
# 检查文档存在且非空
wc -l docs/superpowers/specs/2026-05-01-database-schema-reference.md

# 检查 Mermaid 块数量（应有 ≥5 个 erDiagram 块）
grep -c 'erDiagram' docs/superpowers/specs/2026-05-01-database-schema-reference.md

# 检查无占位符
! grep -n 'TBD\|TODO\|待补充' docs/superpowers/specs/2026-05-01-database-schema-reference.md
echo "Exit: $?"
```

期望: 文件 ≥ 500 行，≥ 5 个 erDiagram 块，无占位符匹配（exit 0）。

- [ ] **Step 3: 提交**

```bash
git add docs/superpowers/specs/2026-05-01-database-schema-reference.md
git commit -m "docs: add full database schema reference manual with ER diagrams and sample data"
```

---

### Task 6: 收尾清理

- [ ] **Step 1: 取消中间数据跟踪（可选）**

若中间数据不应入库，更新 `.gitignore`：

```bash
# 如需排除原始数据
echo "docs/superpowers/data/" >> .gitignore
```

- [ ] **Step 2: 最终提交**

```bash
git add -A
git status
git commit -m "chore: finalize database schema reference delivery"
```

---

## 备选路径

### 若 192.168.3.12 不可达

跳过 Task 1 和 Task 2。所有表结构从代码 Mapper XML 推导，标注「⚠️ 数据库不可达，全部信息从代码推导」。在 Task 5 的 subagent prompt 中移除「读取样本数据」步骤。

### 若某个库的 Mapper XML 为空

对应章节仅输出「该模块未发现 MyBatis Mapper XML，无法从代码推导表结构」，不生成 ER 图。

---

## 完成检查清单

- [ ] 产物文件 `docs/superpowers/specs/2026-05-01-database-schema-reference.md` 存在且 ≥ 500 行
- [ ] 5 个数据库各有独立章节 + ER 图
- [ ] 跨库关系总图存在
- [ ] 代码-表映射索引覆盖全部 Mapper XML
- [ ] 可连库的每张表有 1-3 行脱敏样本
- [ ] 无 `TBD` / `TODO` / `待补充`
- [ ] 所有 Mermaid 块语法正确可渲染
- [ ] 脱敏规则已应用（姓名截断、手机号截断等）