SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsschema-iteration

schema-iteration

Data & analytics

View repository

Updated 10-15 22:19

About this skill

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