database-architect
Design schemas, plan migrations, and optimize queries. Six modes from modeling to evolution. Use for database architecture. NOT for DBA ops, backups, or deployment (devops-engineer).
database-architect1303 wordsMITRepo-owned
Design schemas, plan migrations, and optimize queries. Six modes from modeling to evolution. Use for database architecture. NOT for DBA ops, backups, or deployment (devops-engineer).
Quick Start
Install:
npx skills add github:wyattowalsh/agents --skill database-architect -y -g --agent antigravity --agent claude-code --agent codex --agent crush --agent cursor --agent gemini-cli --agent github-copilot --agent grok --agent opencodeUse: /database-architect <mode> [target]
Works with Claude Code, Gemini CLI, OpenCode, and other agentskills.io-compatible agents.
What It Does
Section titled “What It Does”Schema design, migration planning, query optimization, and zero-downtime schema evolution.
| $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 |
Critical Rules
Section titled “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 honest-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_descriptionconvention (sequential, descriptive)
Canonical Vocabulary
Section titled “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 |
Mode 1: Design
Section titled “Mode 1: Design”Generate schema DDL from natural language requirements.
Design Step 1: Gather Requirements
Section titled “Design Step 1: Gather Requirements”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)
Design Step 2: Analyze Schema
Section titled “Design Step 2: Analyze Schema”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.
Design Step 3: Generate DDL
Section titled “Design Step 3: Generate 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.
Design Step 4: Present
Section titled “Design Step 4: Present”Output the DDL with a summary table:
| Table | Columns | Indexes | Foreign Keys | Normalization |
|---|
Include rationale for denormalization decisions (if any).
Mode 2: Migrate
Section titled “Mode 2: Migrate”Generate migration SQL with rollback plan and zero-downtime strategy.
Migrate Step 1: Understand the Change
Section titled “Migrate Step 1: Understand the Change”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.
Migrate Step 2: Generate Migration
Section titled “Migrate Step 2: Generate Migration”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)
Migrate Step 3: Validate
Section titled “Migrate Step 3: Validate”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 or reversible: false.
Migrate Step 4: Present
Section titled “Migrate Step 4: Present”Output migration with sections: Up, Down, Zero-Downtime Notes, Risk Assessment.
Mode 3: Review
Section titled “Mode 3: Review”Audit existing schema or migration files for quality and safety.
Review Step 1: Read Target
Section titled “Review Step 1: Read Target”Read the schema or migration files at the path in $ARGUMENTS.
Review Step 2: Analyze
Section titled “Review Step 2: Analyze”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
Review Step 3: Present Findings
Section titled “Review Step 3: Present Findings”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
Section titled “Mode 4: Optimize”Index and query optimization recommendations.
Optimize Step 1: Gather Context
Section titled “Optimize Step 1: Gather Context”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
Optimize Step 2: Analyze
Section titled “Optimize Step 2: Analyze”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.
Optimize Step 3: Present Recommendations
Section titled “Optimize Step 3: Present Recommendations”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
Section titled “Mode 5: Evolve”Codebase-wide schema evolution analysis.
Evolve Step 1: Discover
Section titled “Evolve Step 1: Discover”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.
Evolve Step 2: Analyze Evolution
Section titled “Evolve Step 2: Analyze Evolution”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
Evolve Step 3: Present Report
Section titled “Evolve Step 3: Present Report”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.htmlto a temporary file, inject analysis JSON into the data script tag, open in browser.
Reference Files
Section titled “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 |
| Field | Value |
|---|---|
| Source Type | repo-owned |
| Display Source | github:wyattowalsh/agents |
| Source Kind | repo |
| Installability | portable command |
| Review State | reviewed |
| Target Agents | antigravity, claude-code, codex, crush, cursor, gemini-cli, github-copilot, grok, opencode |
| Field | Value |
|---|---|
| Name | database-architect |
| License | MIT |
| Version | 1.0.0 |
| Author | wyattowalsh |
| Field | Value |
|---|---|
| Model | opus |
| Argument Hint | [mode] [target] |
View Full SKILL.md
---name: database-architectdescription: >- Design schemas, plan migrations, and optimize queries. Six modes from modeling to evolution. Use for database architecture. NOT for DBA ops, backups, or deployment (devops-engineer).argument-hint: "<mode> [target]"model: opuslicense: MITmetadata: author: wyattowalsh version: "1.0.0"---
# 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).
## 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 |
## 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 |
## Mode 1: Design
Generate schema DDL from natural language requirements.
### Design Step 1: Gather Requirements
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)
### Design Step 2: Analyze Schema
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.
### Design Step 3: Generate 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.
### Design Step 4: Present
Output the DDL with a summary table:
| Table | Columns | Indexes | Foreign Keys | Normalization ||-------|---------|---------|--------------|---------------|
Include rationale for denormalization decisions (if any).
## Mode 2: Migrate
Generate migration SQL with rollback plan and zero-downtime strategy.
### Migrate Step 1: Understand the Change
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.
### Migrate Step 2: Generate Migration
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)
### Migrate Step 3: Validate
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` or `reversible: false`.
### Migrate Step 4: Present
Output migration with sections: Up, Down, Zero-Downtime Notes, Risk Assessment.
## Mode 3: Review
Audit existing schema or migration files for quality and safety.
### Review Step 1: Read Target
Read the schema or migration files at the path in `$ARGUMENTS`.
### Review Step 2: Analyze
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
### Review Step 3: Present Findings
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
Index and query optimization recommendations.
### Optimize Step 1: Gather Context
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
### Optimize Step 2: Analyze
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.
### Optimize Step 3: Present Recommendations
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
Codebase-wide schema evolution analysis.
### Evolve Step 1: Discover
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.
### Evolve Step 2: Analyze Evolution
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
### Evolve Step 3: Present Report
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.
## 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 |
## Critical Rules
1. Every migration must have a rollback plan — no irreversible changes without explicit user acknowledgment2. Never recommend dropping columns/tables without confirming data preservation strategy3. Always flag data loss risk explicitly — silent destructive operations are unacceptable4. Zero-downtime means schema-level compatibility, NOT deployment coordination (that is devops-engineer)5. Default to PostgreSQL when no engine is specified — state the assumption6. Every index recommendation must include write overhead trade-off7. Do not generate ORM code — output raw DDL/SQL only8. Normalization decisions must cite the specific normal form and violation9. Run schema-analyzer.py or migration-validator.py before presenting results — do not rely on LLM analysis alone10. Never copy honest-review's wave pipeline, confidence scoring, or team structure — this is a generator skill11. Always present before executing — approval gate before any schema modification12. Migration naming must follow `NNNN_description` convention (sequential, descriptive)Resources
Section titled “Resources” Skill Catalog Browse custom and external skills.
CLI Reference Install and manage skills.
agentskills.io The open ecosystem for cross-agent skills.