Database Architect

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 | Design: generate schema DDL from requirements | | migrate | Migrate: migration SQL with rollback plan | | review | Review: audit existing schema or migration files | | optimize | 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 

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 

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 

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 

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 

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  --queries 

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

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: wyattowalsh
• Source: wyattowalsh/agents
• License: MIT
• Homepage: https://agents.w4w.dev

Install and usage instructions live in the source repository linked above.