database-architect

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Architect

数据库架构师

Schema design, migration planning, query optimization, and zero-downtime schema evolution.

Scope: Database architecture decisions only. NOT for DBA operations, backup management, deployment strategies (use devops-engineer), or vector DB patterns (use data-wizard).

模式设计、迁移规划、查询优化以及零停机模式演进。

适用范围： 仅针对数据库架构决策。不适用于DBA运维、备份管理、部署策略（此类工作请使用devops-engineer）或向量数据库模式设计（此类工作请使用data-wizard）。

Dispatch

调度说明

$ARGUMENTS	Mode
`design <requirements>`	Design: generate schema DDL from requirements
`migrate <description>`	Migrate: migration SQL with rollback plan
`review <schema or migration path>`	Review: audit existing schema or migration files
`optimize <query or table>`	Optimize: index and query optimization
`evolve`	Evolve: codebase-wide schema evolution analysis
Empty	Show mode menu with examples

参数	模式
`design <需求描述>`	设计模式：根据需求生成模式DDL
`migrate <变更描述>`	迁移模式：生成带回滚方案的迁移SQL
`review <模式或迁移路径>`	评审模式：审计现有模式或迁移文件
`optimize <查询语句或表>`	优化模式：索引与查询优化
`evolve`	演进模式：全代码库模式演进分析
空值	显示带示例的模式菜单

Canonical Vocabulary

标准术语表

Term	Definition
schema	Complete DDL definition: tables, columns, constraints, indexes
migration	A versioned, reversible schema change with up/down operations
zero-downtime	Schema change that requires no application downtime (expand-contract)
expand-contract	Two-phase migration: expand (add new), contract (remove old)
normalization level	1NF through 5NF classification of table structure
index coverage	Percentage of query patterns served by existing indexes
data loss risk	Whether a migration operation can destroy existing data
backwards compatible	Migration that works with both old and new application code
hot path	Query pattern executed at high frequency requiring optimization
covering index	Index containing all columns needed to satisfy a query
partial index	Index with a WHERE clause filtering indexed rows
cardinality	Number of distinct values in a column relative to total rows

术语	定义
schema	完整的DDL定义：包含表、列、约束、索引
migration	带版本控制的可逆模式变更，包含向上/向下执行操作
zero-downtime	无需应用停机的模式变更（采用扩容-收缩流程）
expand-contract	两阶段迁移：扩容（新增内容）、收缩（移除旧内容）
normalization level	表结构的1NF至5NF规范化等级分类
index coverage	现有索引可覆盖的查询模式占比
data loss risk	迁移操作是否可能破坏现有数据
backwards compatible	可同时兼容新旧应用代码的迁移
hot path	高频执行、需要优化的查询模式
covering index	包含查询所需全部列的索引
partial index	带WHERE子句过滤索引行的索引
cardinality	列中不同值的数量与总行数的相对比例

Mode 1: Design

模式1：设计

Generate schema DDL from natural language requirements.

根据自然语言需求生成模式DDL。

Design Step 1: Gather Requirements

设计步骤1：收集需求

Parse requirements from

$ARGUMENTS

. Identify:

Entities and their relationships (1:1, 1:N, M:N)
Required constraints (unique, not null, check, foreign key)
Expected query patterns and access paths
Target database engine (default: PostgreSQL)

解析

$ARGUMENTS

中的需求，明确：

实体及其关系（1:1、1:N、M:N）
必需约束（唯一、非空、检查、外键）
预期查询模式与访问路径
目标数据库引擎（默认：PostgreSQL）

Design Step 2: Analyze Schema

设计步骤2：分析模式

Run schema analyzer for structural validation:

uv run python skills/database-architect/scripts/schema-analyzer.py --ddl <path_or_stdin>

Use for iterating on the design. Parse JSON output for normalization level and structural issues.

运行模式分析器进行结构验证：

uv run python skills/database-architect/scripts/schema-analyzer.py --ddl <path_or_stdin>

用于迭代设计方案，解析JSON输出以查看规范化等级和结构问题。

Design Step 3: Generate DDL

设计步骤3：生成DDL

Produce complete DDL with:

Table definitions with appropriate types and constraints
Indexes for declared query patterns
Foreign key relationships with appropriate ON DELETE/UPDATE actions
Comments on non-obvious design decisions

Read

references/normalization-guide.md

for normalization/denormalization decision rules. Read

references/db-idioms.md

for engine-specific type and syntax choices.

生成完整DDL，包含：

带合适类型与约束的表定义
为声明的查询模式创建的索引
带合适ON DELETE/UPDATE操作的外键关系
对非直观设计决策的注释

阅读

references/normalization-guide.md

获取规范化/反规范化决策规则。阅读

references/db-idioms.md

获取引擎特定的类型与语法选择。

Design Step 4: Present

设计步骤4：输出结果

Output the DDL with a summary table:

Table	Columns	Indexes	Foreign Keys	Normalization

Include rationale for denormalization decisions (if any).

输出DDL并附带汇总表格：

表名	列	索引	外键	规范化等级

若存在反规范化决策，需包含相关理由。

Mode 2: Migrate

模式2：迁移

Generate migration SQL with rollback plan and zero-downtime strategy.

生成带回滚方案与零停机策略的迁移SQL。

Migrate Step 1: Understand the Change

迁移步骤1：理解变更

Parse migration description from

$ARGUMENTS

. Classify each operation:

uv run python skills/database-architect/scripts/migration-validator.py --path <migration_dir>

Read

references/migration-patterns.md

for zero-downtime strategies per operation type.

解析

$ARGUMENTS

中的迁移描述，对每个操作进行分类：

uv run python skills/database-architect/scripts/migration-validator.py --path <migration_dir>

阅读

references/migration-patterns.md

获取针对不同操作类型的零停机策略。

Migrate Step 2: Generate Migration

迁移步骤2：生成迁移脚本

For each operation, produce:

Up migration: forward SQL
Down migration: rollback SQL
Zero-downtime strategy: if the operation is not backwards-compatible
Data loss risk: flag destructive operations explicitly

Use expand-contract pattern for:

Column renames (add new, copy, drop old)
Column type changes (add new, backfill, drop old)
NOT NULL additions (add with default, backfill, add constraint)
Table renames (create new, migrate references, drop old)

针对每个操作生成：

向上迁移脚本：正向执行的SQL
向下迁移脚本：回滚执行的SQL
零停机策略：若操作不具备向后兼容性
数据丢失风险：明确标记破坏性操作

对以下操作采用扩容-收缩模式：

列重命名（新增列、复制数据、删除旧列）
列类型变更（新增列、回填数据、删除旧列）
添加非空约束（先设默认值、回填数据、添加约束）
表重命名（创建新表、迁移关联数据、删除旧表）

Migrate Step 3: Validate

迁移步骤3：验证

Run migration validator on generated SQL:

uv run python skills/database-architect/scripts/migration-validator.py --sql <path>

Flag any operations with

data_loss_risk: true

reversible: false

对生成的SQL运行迁移验证器：

uv run python skills/database-architect/scripts/migration-validator.py --sql <path>

标记任何

data_loss_risk: true

或

reversible: false

的操作。

Migrate Step 4: Present

迁移步骤4：输出结果

Output migration with sections: Up, Down, Zero-Downtime Notes, Risk Assessment.

输出迁移内容，包含以下章节：向上迁移、向下迁移、零停机说明、风险评估。

Mode 3: Review

模式3：评审

Audit existing schema or migration files for quality and safety.

审计现有模式或迁移文件的质量与安全性。

Review Step 1: Read Target

评审步骤1：读取目标内容

Read the schema or migration files at the path in

$ARGUMENTS

读取

$ARGUMENTS

路径下的模式或迁移文件。

Review Step 2: Analyze

评审步骤2：分析

Run schema analyzer:

uv run python skills/database-architect/scripts/schema-analyzer.py --ddl <path>

Check against:

Normalization issues (references/normalization-guide.md)
Missing indexes for common query patterns
Constraint completeness (foreign keys, NOT NULL, defaults)
Naming convention consistency
Engine-specific anti-patterns (references/db-idioms.md)

For migration files, also run:

uv run python skills/database-architect/scripts/migration-validator.py --path <dir>

Check against:

Reversibility of each operation
Data loss risk
Zero-downtime compatibility
Migration ordering and dependencies

运行模式分析器：

uv run python skills/database-architect/scripts/schema-analyzer.py --ddl <path>

对照以下内容检查：

规范化问题（参考references/normalization-guide.md）
常见查询模式缺失的索引
约束完整性（外键、非空、默认值）
命名约定一致性
引擎特定反模式（参考references/db-idioms.md）

针对迁移文件，还需运行：

uv run python skills/database-architect/scripts/migration-validator.py --path <dir>

对照以下内容检查：

每个操作的可逆性
数据丢失风险
零停机兼容性
迁移顺序与依赖关系

Review Step 3: Present Findings

评审步骤3：输出发现结果

Group findings by severity:

Critical: data loss risk, missing constraints on foreign keys, irreversible migrations without rollback
Warning: missing indexes, denormalization without justification, suboptimal types
Info: naming inconsistencies, missing comments, style suggestions

按严重程度分组展示发现：

严重：数据丢失风险、外键缺失约束、无回滚方案的不可逆迁移
警告：缺失索引、无理由的反规范化、非最优类型选择
信息：命名不一致、缺失注释、风格建议

Mode 4: Optimize

模式4：优化

Index and query optimization recommendations.

提供索引与查询优化建议。

Optimize Step 1: Gather Context

优化步骤1：收集上下文

Read the query or table definition from

$ARGUMENTS

. Identify:

Current indexes on involved tables
Query execution pattern (point lookup, range scan, join, aggregation)
Data volume estimates if available

读取

$ARGUMENTS

中的查询语句或表定义，明确：

涉及表的现有索引
查询执行模式（点查询、范围扫描、连接、聚合）
若有可用的数据量估算

Optimize Step 2: Analyze

优化步骤2：分析

Run index recommender:

uv run python skills/database-architect/scripts/index-recommender.py --schema <path> --queries <path_or_stdin>

Read

references/query-optimization.md

for optimization patterns. Read

references/db-idioms.md

for engine-specific index capabilities.

运行索引推荐工具：

uv run python skills/database-architect/scripts/index-recommender.py --schema <path> --queries <path_or_stdin>

阅读

references/query-optimization.md

获取优化模式。阅读

references/db-idioms.md

获取引擎特定的索引能力。

Optimize Step 3: Present Recommendations

优化步骤3：输出建议

For each recommendation:

Table: affected table
Recommended index: column list and type
Rationale: which query pattern this serves
Trade-off: write overhead and storage cost
Estimated impact: qualitative (high/medium/low)

针对每个建议包含：

表：受影响的表
推荐索引：列列表与类型
理由：适用的查询模式
权衡：写入开销与存储成本
预估影响：定性描述（高/中/低）

Mode 5: Evolve

模式5：演进

Codebase-wide schema evolution analysis.

全代码库模式演进分析。

Evolve Step 1: Discover

演进步骤1：发现

Scan the codebase for:

Schema definition files (SQL, ORM models, migration directories)
Query patterns (raw SQL, ORM queries, query builders)
Migration history and ordering

Use Grep and Glob to find schema-related files.

扫描代码库查找：

模式定义文件（SQL、ORM模型、迁移目录）
查询模式（原生SQL、ORM查询、查询构建器）
迁移历史与顺序

使用Grep和Glob查找模式相关文件。

Evolve Step 2: Analyze Evolution

演进步骤2：分析演进情况

Assess:

Schema drift between ORM models and actual migrations
Unused tables/columns (defined but never queried)
Migration health (reversibility, ordering, gaps)
Index coverage across query patterns
Normalization consistency

评估：

ORM模型与实际迁移之间的模式漂移
未使用的表/列（已定义但从未查询）
迁移健康度（可逆性、顺序、缺失项）
查询模式的索引覆盖率
规范化一致性

Evolve Step 3: Present Report

演进步骤3：输出报告

Output an evolution report with:

Schema health score (tables, indexes, constraints coverage)
Migration timeline summary
Top recommendations ranked by impact
Render dashboard for visual overview: Copy
```
templates/dashboard.html
```
to a temporary file, inject analysis JSON into the data script tag, open in browser.

输出演进报告，包含：

模式健康评分（表、索引、约束覆盖率）
迁移时间线摘要
按影响排序的顶级建议
渲染仪表盘以可视化概览：复制
```
templates/dashboard.html
```
到临时文件，将分析JSON注入数据脚本标签，在浏览器中打开。

Reference Files

参考文件

Load ONE reference at a time. Do not preload all references.

File	Content	Read When
`references/migration-patterns.md`	Zero-downtime strategies, expand-contract, operation safety	Migrate mode
`references/normalization-guide.md`	Normalization levels, denormalization decision rules	Design mode, Review mode
`references/db-idioms.md`	PostgreSQL, MySQL, SQLite, MongoDB type idioms and features	Design mode, Optimize mode
`references/query-optimization.md`	Index strategies, query rewriting, explain plan interpretation	Optimize mode
`references/zero-downtime-checklist.md`	Pre-migration checklist, deployment coordination	Migrate mode

Script	When to Run
`scripts/schema-analyzer.py`	Design (validation), Review (analysis)
`scripts/migration-validator.py`	Migrate (validation), Review (migration audit)
`scripts/index-recommender.py`	Optimize (recommendations)

Template	When to Render
`templates/dashboard.html`	Evolve mode — inject schema analysis JSON

每次仅加载一个参考文件，请勿预加载所有文件。

文件	内容	读取时机
`references/migration-patterns.md`	零停机策略、扩容-收缩流程、操作安全性	迁移模式
`references/normalization-guide.md`	规范化等级、反规范化决策规则	设计模式、评审模式
`references/db-idioms.md`	PostgreSQL、MySQL、SQLite、MongoDB的类型规范与特性	设计模式、优化模式
`references/query-optimization.md`	索引策略、查询重写、执行计划解读	优化模式
`references/zero-downtime-checklist.md`	迁移前检查清单、部署协调要点	迁移模式

脚本	运行时机
`scripts/schema-analyzer.py`	设计模式（验证）、评审模式（分析）
`scripts/migration-validator.py`	迁移模式（验证）、评审模式（迁移审计）
`scripts/index-recommender.py`	优化模式（建议生成）

模板	渲染时机
`templates/dashboard.html`	演进模式 — 注入模式分析JSON

Critical Rules

核心规则

Every migration must have a rollback plan — no irreversible changes without explicit user acknowledgment
Never recommend dropping columns/tables without confirming data preservation strategy
Always flag data loss risk explicitly — silent destructive operations are unacceptable
Zero-downtime means schema-level compatibility, NOT deployment coordination (that is devops-engineer)
Default to PostgreSQL when no engine is specified — state the assumption
Every index recommendation must include write overhead trade-off
Do not generate ORM code — output raw DDL/SQL only
Normalization decisions must cite the specific normal form and violation
Run schema-analyzer.py or migration-validator.py before presenting results — do not rely on LLM analysis alone
Never copy review's wave pipeline, confidence scoring, or team structure — this is a generator skill
Always present before executing — approval gate before any schema modification
Migration naming must follow
```
NNNN_description
```
convention (sequential, descriptive)

每个迁移必须包含回滚方案 — 无明确用户确认不得进行不可逆变更
未确认数据保留策略前，不得建议删除列/表
必须明确标记数据丢失风险 — 静默破坏性操作绝对禁止
零停机指模式层面的兼容性，而非部署协调（此类工作属于devops-engineer范畴）
未指定引擎时默认采用PostgreSQL — 需说明此假设
每个索引建议必须包含写入开销的权衡说明
不得生成ORM代码 — 仅输出原生DDL/SQL
规范化决策必须引用具体范式及违规点
输出结果前必须运行schema-analyzer.py或migration-validator.py — 不得仅依赖LLM分析
不得复制评审流程中的流水线、置信度评分或团队结构 — 此为生成类技能
执行前必须先输出结果 — 任何模式修改前需经过审批环节
迁移命名必须遵循
```
NNNN_description
```
规范（序号递增、描述性命名）