Imported from miniidealab/openlogos (skills/db-designer/SKILL.md). Install upstream with npx skills add miniidealab/openlogos --skill db-designer. Copyright stays with the author.

Skill: DB Designer

从 API 规格推导数据库表结构，生成对应方言的 SQL DDL。数据库类型由 Phase 3 Step 0 技术选型确定，确保字段类型、约束、索引和安全策略与 API 端点完全对齐。

触发条件

• 用户要求设计数据库或编写 SQL
• 用户提到 "Phase 3 Step 2"、"DB 设计"、"表结构"
• 已有 API YAML 规格，需要推导数据库设计
• 用户提供了数据模型需要转化为 DDL

核心能力

1. 从 API 请求/响应结构推导表结构
2. 读取 logos-project.yaml 的 tech_stack.database 确定数据库类型
3. 生成对应数据库方言的 SQL DDL
4. 设计索引并说明设计理由
5. 设计安全策略（RLS / 应用层权限）
6. 为每张表、每个字段添加注释

前置依赖

• logos/resources/api/ 中包含 API YAML 规格（api-designer 产出）
• logos-project.yaml 的 tech_stack.database 已填写

如果 API 目录为空，提示用户先完成 Phase 3 Step 2 的 API 设计（api-designer）。如果 tech_stack.database 未填写，提示用户先完成 Phase 3 Step 0（architecture-designer）。

执行步骤

Step 1: 确认数据库类型

读取 logos/logos-project.yaml 的 tech_stack 字段，确定数据库类型和方言：

• PostgreSQL → 使用 UUID、TIMESTAMPTZ、RLS、JSONB 等特性
• MySQL → 使用 InnoDB、utf8mb4、TIMESTAMP 等特性
• SQLite → 使用 INTEGER PRIMARY KEY、TEXT 等简化类型
• 其他 → 与用户确认后选择最接近的方言

Step 2: 提取数据实体

从 API YAML 中提取所有需要持久化的数据实体：

1. 扫描所有端点的 requestBody 和 responses，识别核心数据对象
2. 区分"需要持久化"与"仅传输用"的数据：
   • 有 CRUD 操作的对象 → 需要建表（如 users、projects）
   • 只出现在请求/响应中但不直接存储的 → 不建表（如 loginRequest）
3. 为每个对象标注来源 API 端点

输出实体清单供用户确认：

从 API 规格中识别到 N 个需要持久化的数据实体：

| # | 实体 | 来源端点 | 核心字段 |
|---|------|---------|---------|
| 1 | users | auth.yaml → register, login | email, password, status |
| 2 | projects | projects.yaml → create, list, get | name, description, owner_id |
| 3 | subscriptions | billing.yaml → subscribe | plan, status, expires_at |

Step 3: 设计表结构

为每个实体设计完整的表结构，遵循当前数据库方言：

每张表必须包含：

• 主键（UUID 或自增 ID，视方言决定）
• 业务字段（从 API schema 映射，类型转换为数据库类型）
• 审计字段：created_at、updated_at
• 软删除字段：deleted_at（按需）
• 字段约束：NOT NULL、UNIQUE、CHECK、DEFAULT

类型映射原则：

• API string + format: email → TEXT NOT NULL（配合 CHECK 约束或应用层校验）
• API string + format: uuid → UUID（PostgreSQL）/ CHAR(36)（MySQL）
• API integer → INTEGER / BIGINT
• API boolean → BOOLEAN（PostgreSQL）/ TINYINT(1)（MySQL）
• API string + enum → TEXT + CHECK 约束（列出枚举值）
• 金额字段 → INTEGER（存分值），禁止 DECIMAL/FLOAT

示例（PostgreSQL）：

-- 用户表（来源：auth.yaml → register, login）
CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  password    TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'active', 'disabled')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

示例（SQLite — 使用 @comment 结构化注释）：

-- 用户表（来源：auth.yaml → register, login）
CREATE TABLE users (
  -- @comment 用户唯一标识，UUID v4 字符串
  id TEXT PRIMARY KEY NOT NULL,
  -- @comment 用户邮箱，已归一化为小写
  email TEXT NOT NULL UNIQUE,
  -- @comment Argon2id 密码哈希，仅存哈希
  password_hash TEXT NOT NULL,
  -- @comment 创建时间，ISO 8601 格式
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  -- @comment 最后更新时间
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
-- @table-comment users 用户表，存储注册用户的核心信息

SQLite 注释约定：SQLite 不支持 COMMENT ON 语法，必须使用 -- @comment 前置注释（字段）和 -- @table-comment <表名> <描述> 后置注释（表）。规则详见 logos/spec/sql-comment-convention.md。

Step 4: 设计表间关联

根据 API 中的实体关系设计外键：

1. 从 API 端点的嵌套路径和引用字段推导关联（如 /api/projects/:projectId/members → project_members 表关联 projects 和 users）
2. 确定关联类型（一对多、多对多）
3. 设计外键约束和级联策略：
   • ON DELETE CASCADE：父记录删除时子记录跟随删除（如用户删除 → 项目删除）
   • ON DELETE SET NULL：父记录删除时子记录保留但外键置空
   • ON DELETE RESTRICT：父记录有子记录时禁止删除

Step 5: 设计安全策略

根据数据库类型设计对应的安全机制：

PostgreSQL — 行级安全（RLS）：

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY projects_owner_policy ON projects
  USING (owner_id = auth.uid());

• 所有包含用户数据的表启用 RLS
• 为每张表设计至少一条 Policy（owner / admin / public）
• 注明 RLS 策略与 API 认证方案的对应关系

MySQL — 应用层权限：

• 在表注释中标注数据访问权限（owner-only / admin / public）
• 不在 DDL 中实现权限控制，交由应用层处理

Step 6: 设计索引

为常见查询模式设计索引，每个索引附设计理由：

-- 用户按邮箱查找（登录场景，来源 S02）
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 项目按 owner 查找（项目列表，来源 S04 Step 1）
CREATE INDEX idx_projects_owner ON projects(owner_id);

索引设计原则：

• 外键列：必须建索引（避免 JOIN 全表扫描）
• 唯一约束列：自动创建唯一索引
• 高频查询列：根据 API 的查询参数判断
• 复合索引：多条件查询时考虑（最左前缀原则）
• 不过度索引：写多读少的表控制索引数量

Step 7: 输出完整 DDL

按以下顺序组织 DDL 文件：

1. 文件头注释（来源、数据库类型、生成时间）
2. 基础表（无外键依赖的表先建）
3. 关联表（有外键依赖的表后建）
4. 索引
5. 安全策略（RLS / Policy）
6. 表和字段注释：
   • PostgreSQL：使用 COMMENT ON TABLE / COMMENT ON COLUMN
   • MySQL：使用内联 COMMENT
   • SQLite：使用 -- @comment（字段）+ -- @table-comment（表），详见下方 SQLite 注释规则

每段 DDL 上方用注释标注来源 API 端点。

SQLite 注释规则（MUST Follow）：

当 tech_stack.database 为 SQLite 时，必须使用以下结构化注释格式：

1. 字段注释：在字段定义行的紧邻上方写 -- @comment <描述>
   • 与字段之间不允许空行（空行会断开关联）
   • 多行注释：连续多个 -- @comment 行自动拼接
2. 表注释：在 CREATE TABLE ... (); 语句紧邻下方写 -- @table-comment <表名> <描述>
3. 约束行（FOREIGN KEY、独立 CHECK、UNIQUE）不需要 -- @comment

输出规范

• 文件格式：SQL（方言由 tech_stack.database 决定）
• 存放位置：logos/resources/database/
• 单文件输出：schema.sql（简单项目）；或按领域分文件：auth.sql、billing.sql（复杂项目）
• 每张表必须有注释（PostgreSQL: COMMENT ON TABLE；MySQL: COMMENT = '...'；SQLite: -- @table-comment）
• 每个字段必须有注释（PostgreSQL: COMMENT ON COLUMN；MySQL: 字段定义后 COMMENT '...'；SQLite: -- @comment）
• 每段 DDL 上方用 SQL 注释标注来源 API 端点

数据库方言差异速查

实践经验

通用（所有数据库）

• 金额一律 INTEGER 存分值：禁止 DECIMAL/FLOAT，避免浮点精度问题
• 软删除：优先使用 deleted_at 时间字段而非物理删除
• 审计字段：每张表包含 created_at 和 updated_at
• 时间字段带时区：避免时区陷阱
• 字段名与 API 一致：DB 列名尽量与 API YAML 中的字段名对齐（如 API 用 userId → DB 用 user_id，映射规则明确即可），减少代码层的无谓转换
• 先出核心表再补辅助表：不要试图一次设计所有表——先输出核心业务表让用户 review，再补充辅助表

PostgreSQL 特有

• 主键：id UUID DEFAULT gen_random_uuid() PRIMARY KEY
• 时间类型：使用 TIMESTAMPTZ
• RLS：所有表启用 ALTER TABLE ... ENABLE ROW LEVEL SECURITY;
• JSONB：需要非结构化存储时优先使用 JSONB 并建 GIN 索引

MySQL 特有

• 主键：id CHAR(36) DEFAULT (UUID()) PRIMARY KEY 或自增 BIGINT
• 时间类型：使用 TIMESTAMP（自动时区转换）或 DATETIME（原样存储）
• 字符集：建表时指定 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
• 引擎：一律使用 ENGINE=InnoDB

SQLite 特有

• 主键：TEXT PRIMARY KEY NOT NULL（应用层生成 UUID v4）或 INTEGER PRIMARY KEY AUTOINCREMENT
• 时间类型：使用 TEXT 存储 ISO 8601 字符串，默认值 DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
• 外键：须在连接时执行 PRAGMA foreign_keys = ON;
• 注释：必须使用 -- @comment / -- @table-comment 结构化注释（见 logos/spec/sql-comment-convention.md）
• 无触发器：updated_at 须在应用层刷新，不依赖 ON UPDATE 触发器

推荐提示词

以下提示词可以直接复制给 AI 使用：

• 帮我设计数据库
• 基于 API 规格帮我推导数据库 DDL
• 帮我设计 S01 涉及的数据库表
• 帮我给现有表结构补充索引和 RLS 策略

⚠️ 收尾步骤（强制）：更新 resource_index

完成本 Skill 的所有数据库 DDL 产出后，必须将新生成的文件追加写入 logos/logos-project.yaml 的 resource_index 字段：

resource_index:
  # ...已有条目...
  - path: logos/resources/database/<文件名>.sql
    desc: 数据库完整 Schema（DDL）。涉及表结构、字段定义、索引策略、外键约束时必读。

不执行此步骤将导致后续 code-implementor 无法感知数据库 Schema，AI 将无法生成与真实表结构一致的 ORM 代码和查询逻辑。

S39 delta 模式：按持久化证据生成唯一 DB delta

激活与适用性

on-touch-v1 场景出现持久化实体、关系、查询、索引、事务、迁移或保留策略时启用。只使用内存或无结构临时数据时可 SKIP，并把时序/代码证据交回闭包矩阵。

结构化文件若是稳定持久化契约，应至少进入架构/数据模型说明，不能因为“不是 SQL”而自动认为没有数据设计。

输入

• effective requirement/scenario/API；
• 现有 DB schema/DDL/migration；
• 可重算 ORM model、SQL、fixture 与查询代码；
• 本次 proposal 的数据保留/兼容意图。

代码只证明现状表/字段，不提供历史业务 Why。无法决定唯一性、级联、保留期等产品选择时返回 AMBIGUOUS。

MODIFY

• 读取主 schema + 当前同目标 delta 的 effective view；
• 在一个 delta 中聚合所有受影响表/字段/索引/迁移；
• 保留未变更结构，显式描述 rename/drop/backfill；
• 给出向前迁移、向后兼容与回滚策略；禁止另建“DB 基线 delta”。
• 对 .sql 目标，输出必须是整文件最终态，首行为 ## MODIFIED — <canonical target>（整文件替换）；其后 payload 是完整、对应方言可解析的 DDL，不得输出差异片段或 Markdown 章节 marker。

CREATE

目标缺失时返回完整可执行/可审查的数据规格，至少含：

• 实体/表/集合及字段类型、nullable/default；
• 主键、外键、唯一/检查约束；
• 索引及其查询依据；
• 关系、级联和事务边界；
• 初始化/迁移顺序；
• 旧数据 backfill 与兼容窗口（适用时）；
• 回滚/失败恢复；
• scenario step/API schema 到数据结构的追溯。

禁止只有新增字段片段、没有建表/约束上下文的“全量”文档；禁止 TODO/占位。

non-Markdown SQL CREATE 的输出格式固定：

## ADDED — logos/resources/database/<file>.sql（新文件，整文件）
-- 后续为完整 SQL payload
CREATE TABLE ...;

• 首行声明 target 必须与 delta 路径映射结果一致；CREATE 只能用 ADDED 且目标缺失。marker 在 SQL 校验/写入前由 merge/lint 剥离，最终 DDL 不得含 marker。
• SQL 方言必须来自已合并架构或 logos-project.yaml tech_stack.database；若方言缺失/冲突或 validator 不可用，返回 AMBIGUOUS/non_markdown_delta_invalid，不得用通用分号检查猜测通过。
• 剥离后的 payload 必须被方言 parser 完整消费；有适配器时在临时空库/事务执行并回滚，SQLite 夹具强制真实执行。CREATE 还需通过表/键/约束/索引/迁移/回滚完整度。
• marker、声明 target、mode/存在性、语法或执行任一失败时整批零落盘；协议权威定义见 merge-executor 的 non-Markdown 整文件章节。

输出与所有权

本 Skill 把内容、受影响实体清单、migration/smoke 风险交回 change-writer。最终 canonical target 只由 change-writer 写一份 delta。数据迁移会同时触发 deployment/smoke 影响复核。

完成检查

• target mode 与磁盘存在性一致；
• schema/DDL 语法与引用关系可验证；
• 数据约束与 scenario 异常路径一致；
• test-writer 获得需要覆盖的事务/约束/迁移断言；
• 不采信 partial seed，不写确认/verified 状态。