Schema Iteration

Analyzes a database schema for structural problems, produces a refactored version, and generates a safe migration that preserves all existing data.

---

Workflow

1. Ingest the Schema

Accept input in any form:

• Prisma schema (.prisma)
• Raw SQL (CREATE TABLE statements)
• ORM model definitions (SQLAlchemy, Django, ActiveRecord, TypeORM)
• A prose description of the current tables and fields

If the schema is missing, ask for it. If only a prose description is given, reconstruct a working schema from it before analyzing.

Also clarify (if not obvious):

• Database engine (PostgreSQL, MySQL, SQLite, etc.)
• ORM in use (Prisma, Drizzle, SQLAlchemy, etc.) — determines migration output format
• Approximate data volume — affects index strategy and migration approach
• Any known pain points the user has already noticed

---

2. Audit the Schema

Work through each category. Flag every issue found with a short label so the user can follow along.

Normalization

• Repeated columns that belong in a separate table (1NF/2NF/3NF violations)
• Many-to-many relations stored as comma-separated strings or JSON arrays instead of a join table
• Denormalized fields that create update anomalies (e.g. storing user_email on every Order row)

Relation integrity

• Missing foreign key constraints
• Nullable foreign keys that should be non-nullable (or vice versa)
• Cascade behavior not set where it should be (ON DELETE CASCADE vs SET NULL vs RESTRICT)
• Circular or ambiguous relations

Indexes

• Missing index on every foreign key column (the most common oversight)
• Missing index on columns used in frequent WHERE, ORDER BY, or JOIN clauses
• Redundant indexes (e.g. an index on (a, b) makes a standalone index on (a) redundant)
• Unique constraints missing on columns that must be unique

Naming and conventions

• Inconsistent casing (userId vs user_id in the same schema)
• Ambiguous column names (data, info, value, flag)
• Table names that mix singular/plural

Types

• Using TEXT where a constrained VARCHAR or ENUM would enforce correctness
• Using INT for IDs on a table that will grow large (consider BIGINT)
• Storing money as FLOAT instead of DECIMAL/NUMERIC
• Storing datetimes as strings instead of proper timestamp types

---

3. Produce the Refactored Schema

Output the full cleaned schema — not just the diffs. Include:

• All original tables (with fixes applied)
• Any new tables introduced (e.g. join tables, lookup tables)
• All foreign keys with explicit cascade behavior
• All indexes (both those that existed and new ones added)
• Inline comments explaining non-obvious decisions

For Prisma: output a valid .prisma schema block. For SQL: output complete CREATE TABLE statements. For ORM models: match the user's existing ORM style.

---

4. Generate the Migration

This is the most safety-critical part. Every step must be reversible or at least non-destructive.

Golden rules:

• Never DROP COLUMN or DROP TABLE in the same transaction as data is being moved — always move first, verify, then drop in a separate step (or leave the drop for the user to run after they confirm)
• Never rename a column directly on a busy production table — add the new column, backfill, then drop the old one
• Add indexes CONCURRENTLY (PostgreSQL) or with ALGORITHM=INPLACE (MySQL) when possible to avoid table locks
• Wrap DDL in a transaction where the engine supports transactional DDL (PostgreSQL yes, MySQL no — call this out)

Migration output structure:

-- ============================================================
-- Migration: <short description>
-- Generated by: schema-iteration skill
-- Safe to run on: <engine>
-- Transactional DDL: yes/no
-- ============================================================

-- STEP 1: Add new columns / tables (non-destructive)
...

-- STEP 2: Backfill data
...

-- STEP 3: Add constraints and indexes
...

-- STEP 4 (run after verifying data): Drop deprecated columns/tables
-- NOTE: Review before running. These statements are intentionally
--       separated so you can verify data integrity first.
...

For ORM migrations (Prisma, Alembic, etc.), output the migration file in the correct format for that tool. See references/orm-migrations.md for tool-specific patterns.

---

5. Summarize Changes

After the schema and migration, produce a concise change log:

CHANGES SUMMARY
───────────────
Normalized:   [list tables/columns affected]
New tables:   [list any added]
FKs added:    [count and list]
Indexes added: [count and list]
Types fixed:  [list]
Naming fixed: [list]
⚠ Breaking:   [any changes that require app-layer updates]

Call out anything that requires a corresponding change in application code (e.g. a renamed column, a removed denormalized field now fetched via join).

---

6. Optional Follow-ups (offer after main output)

• Query audit: given sample queries from the user, verify they'll benefit from the new indexes
• Seed data update: update fixture/seed files to match the new schema
• ORM model update: update TypeScript types, Pydantic models, etc. to match
• Rollback migration: generate the inverse migration

---

Reference Files

• references/orm-migrations.md — Prisma, Alembic, Drizzle, and ActiveRecord migration patterns
• references/index-strategy.md — When to add composite indexes, partial indexes, and covering indexes