Database Designer

The agent analyzes SQL schemas for normalization compliance, recommends optimal indexes based on query patterns, and generates safe migration scripts with rollback procedures. It produces Mermaid ERDs, detects redundant indexes, and implements zero-downtime expand-contract migration patterns for PostgreSQL and MySQL.

Quick Start

# Analyze a schema for normalization issues and generate ERD
python schema_analyzer.py --input schema.sql --generate-erd --output-format json

# Recommend indexes based on query patterns
python index_optimizer.py --schema schema.json --queries queries.json --analyze-existing

# Generate migration scripts between schema versions
python migration_generator.py --current current.json --target target.json --zero-downtime

Core Workflows

Workflow 1: Analyze and Optimize a Schema

1. Provide DDL (SQL) or JSON schema definition
2. Run

   schema_analyzer.py

   to detect normalization violations (1NF-BCNF), missing constraints, and naming issues
3. Review generated Mermaid ERD for relationship visualization
4. Run

   index_optimizer.py

   with query patterns to get index recommendations
5. Validation checkpoint: All 1NF-3NF violations addressed; foreign keys declared; no redundant indexes

python schema_analyzer.py -i schema.sql -f json -e -o report.json
python index_optimizer.py -s schema.json -q queries.json -e -p 2 -o index_report.json

Workflow 2: Generate a Safe Migration

1. Export current and target schemas as JSON
2. Run

   migration_generator.py

   to produce forward and rollback SQL
3. For large tables (10M+ rows), add

   --zero-downtime

   for expand-contract pattern
4. Review validation queries that confirm migration success
5. Validation checkpoint: Every forward step has a rollback counterpart; validation queries pass on test data

python migration_generator.py -c current.json -t target.json -z --include-validations -f json -o plan.json

Workflow 3: Index Optimization for Query Patterns

1. Document top 10 query patterns as JSON (WHERE clauses, JOINs, ORDER BY)
2. Run

   index_optimizer.py

   with

   --analyze-existing

   to find redundancies
3. Review composite index column ordering (most selective first)
4. Check for covering index opportunities
5. Validation checkpoint: Query patterns covered; no overlapping indexes; estimated 40%+ query time reduction

Index Type Selection

CREATE INDEX idx ON tasks (status, created_date)

CREATE INDEX idx ON users (email) WHERE status = 'active'

CREATE INDEX idx ON users (email) INCLUDE (name, status)

CREATE INDEX idx ON docs USING GIN (data)

Anti-Patterns

• Over-indexing -- every column indexed wastes write performance and storage; index only columns appearing in WHERE, JOIN, and ORDER BY
• Missing foreign keys -- relying on application-layer referential integrity leads to orphaned records; always declare FK constraints
• VARCHAR(255) everywhere -- oversized columns waste memory in indexes; right-size columns based on actual data
• Premature denormalization -- denormalize only when EXPLAIN ANALYZE shows join-related bottlenecks, not preemptively
• Direct ALTER on large tables --

  ALTER TABLE ... SET NOT NULL

  on a 100M-row table locks the table; use expand-contract pattern
• No validation queries in migrations -- migrations without post-step validation risk silent data corruption

Troubleshooting

WHERE status = 'active'

ALTER COLUMN ... TYPE

--zero-downtime

cardinality_estimate

--validate-only

Success Criteria

• Schema analysis detects 90%+ of normalization violations (1NF through BCNF) when provided complete DDL input
• Index recommendations reduce query execution time by 40%+ for analyzed query patterns (measured via EXPLAIN ANALYZE before/after)
• Migration scripts execute with zero data loss and include verified rollback for every forward step
• ERD generation produces valid Mermaid diagrams that render correctly for schemas with up to 50 tables
• Redundant index detection identifies 95%+ of duplicate and overlapping indexes with less than 5% false positive rate
• Zero-downtime migrations maintain full application availability during schema changes on tables with 10M+ rows
• Generated SQL statements are syntactically valid and compatible with PostgreSQL 14+ and MySQL 8.0+

Scope & Limitations

Covers:

• Schema design analysis for SQL databases (PostgreSQL, MySQL) including normalization, constraints, naming, and data types
• Index optimization with selectivity estimation, composite index ordering, covering indexes, and redundancy detection
• Migration generation with forward/rollback scripts, zero-downtime patterns, and validation queries
• ERD generation in Mermaid format from DDL or JSON schema definitions

Does NOT cover:

• Runtime query performance monitoring or live database profiling (see

  performance-profiler

  skill)
• NoSQL-specific schema design for MongoDB, DynamoDB, or Cassandra (conceptual guidance only in the reference sections)
• Database administration tasks such as backup/restore, replication setup, or user/role management
• Application-level ORM configuration, connection pool tuning, or driver-specific optimizations (see

  database-schema-designer

  for ORM-adjacent patterns)

Integration Points

migration-architect

database-schema-designer

performance-profiler

api-design-reviewer

ci-cd-pipeline-builder

observability-designer

Tool Reference

schema_analyzer.py

Purpose: Analyzes SQL DDL statements and JSON schema definitions for normalization compliance, missing constraints, data type issues, naming convention violations, and relationship mapping. Generates Mermaid ERD diagrams.

Usage:

python schema_analyzer.py --input schema.sql --output-format json
python schema_analyzer.py --input schema.json --output-format text
python schema_analyzer.py --input schema.sql --generate-erd --output analysis.json
python schema_analyzer.py --input schema.sql --erd-only

Flags/Parameters:

--input

-i

--output

-o

--output-format

-f

json

text

text

--generate-erd

-e

--erd-only

Example:

python schema_analyzer.py -i my_schema.sql -f json -e -o report.json

Output Formats:

• text

  -- Human-readable report with normalization findings, constraint issues, data type recommendations, and naming violations

• json

  -- Structured JSON with

  normalization_issues

  ,

  constraint_issues

  ,

  data_type_issues

  ,

  naming_issues

  ,

  relationships

  , and optional

  erd_diagram

  fields

index_optimizer.py

Purpose: Analyzes schema definitions and query patterns to recommend optimal indexes. Identifies missing indexes, detects redundant and overlapping indexes, suggests composite index column ordering, estimates selectivity, and generates CREATE INDEX statements.

Usage:

python index_optimizer.py --schema schema.json --queries queries.json --format text
python index_optimizer.py --schema schema.json --queries queries.json --output recommendations.json --format json
python index_optimizer.py --schema schema.json --queries queries.json --analyze-existing
python index_optimizer.py --schema schema.json --queries queries.json --min-priority 2

Flags/Parameters:

--schema

-s

--queries

-q

--output

-o

--format

-f

json

text

text

--analyze-existing

-e

--min-priority

-p

4

Example:

python index_optimizer.py -s schema.json -q queries.json -f json -e -p 2 -o index_report.json

Output Formats:

• text

  -- Human-readable report with analysis summary, high-priority recommendations, redundancy issues, performance impact analysis, and CREATE INDEX statements

• json

  -- Structured JSON with

  analysis_summary

  ,

  index_recommendations

  (by priority),

  redundancy_analysis

  ,

  size_estimates

  ,

  sql_statements

  , and

  performance_impact

  fields

migration_generator.py

Purpose: Generates safe migration scripts between schema versions. Compares current and target schemas, produces ALTER TABLE statements, implements zero-downtime expand-contract patterns, creates rollback scripts, and generates validation queries.

Usage:

python migration_generator.py --current current.json --target target.json --format text
python migration_generator.py --current current.json --target target.json --output migration.sql --format sql
python migration_generator.py --current current.json --target target.json --zero-downtime --format json
python migration_generator.py --current current.json --target target.json --validate-only

Flags/Parameters:

--current

-c

--target

-t

--output

-o

--format

-f

json

text

sql

text

--zero-downtime

-z

--validate-only

-v

--include-validations

Example:

python migration_generator.py -c current.json -t target.json -z --include-validations -f json -o migration_plan.json

Output Formats:

• text

  -- Human-readable migration plan with ordered steps, forward SQL, rollback SQL, risk levels, and execution timeline

• json

  -- Structured JSON with

  migration_id

  ,

  steps

  (each with

  sql_forward

  ,

  sql_rollback

  ,

  validation_sql

  ,

  risk_level

  ,

  zero_downtime_phase

  ),

  summary

  ,

  execution_order

  , and

  rollback_order

• sql

  -- Raw SQL output with forward migration statements, suitable for direct execution or piping into a database client