$
copilot --idea "Database Time Machine"
→
Schema design, migration generation, cross-database conversion, and realistic seed data
The Problem
Database schemas evolve organically. What started as a clean 5-table design is now 47 tables with circular foreign keys, columns named `data2`, and a `misc` JSON column that holds half the application state. Migrations are written by hand and nobody tests the rollback.
What You'll Build
A database management workflow that covers:
- Schema analysis and normalisation recommendations
- Migration generation with rollback scripts
- Cross-database conversion (SQL Server ? PostgreSQL, etc.)
- Realistic seed data generation
- Query optimisation and index recommendations
Step-by-Step Walkthrough
Task 1: Schema Health Check
$
"Analyse this database schema (schema.sql) and report:
- Tables without primary keys
- Missing foreign key constraints (inferred from column names)
- Columns that should be indexed based on likely query patterns
- Denormalisation issues (repeated data across tables)
- Columns with poor naming (id1, data2, temp_field)
- Tables with too many columns (should be split)
Rate the overall schema health 1-10."
Task 2: Generate Migrations
$
"I need to add soft deletes to every table in the schema.
Generate:
1. An UP migration that adds deleted_at DATETIME NULL to each table
2. An index on deleted_at for each table
3. A DOWN migration that reverses everything
4. An update to the application's query layer to filter soft-deleted rows
Use the project's migration framework (Knex/Sequelize/Prisma/EF)."
Task 3: Cross-Database Conversion
$
"Convert this SQL Server schema to PostgreSQL.
Handle:
- IDENTITY ? SERIAL/BIGSERIAL
- NVARCHAR ? VARCHAR
- DATETIME2 ? TIMESTAMPTZ
- BIT ? BOOLEAN
- UNIQUEIDENTIFIER ? UUID
- Computed columns ? generated columns
- SQL Server-specific functions ? PostgreSQL equivalents
Generate both the schema DDL and a data migration script."
Task 4: Realistic Seed Data
$
"Generate 500 rows of realistic seed data for the users table.
Requirements:
- Real-sounding names (diverse, international)
- Valid email addresses matching the name
- Phone numbers in correct format for the country
- Addresses that look real (not '123 Test Street')
- Created dates distributed over the last 2 years
- Some users inactive, some admins, mostly regular
- Referral chains (user A referred user B who referred user C)
Output as INSERT statements and as a CSV."
Task 5: Query Optimisation
$
"Here's a slow query that takes 12 seconds on 1M rows:
[paste query]
1. Explain why it's slow (execution plan analysis)
2. Suggest indexes that would help
3. Rewrite the query for better performance
4. Estimate the performance improvement
5. Check if the ORM is generating an N+1 query problem"
Task 6: Orphan Detection
$
"Write queries to find data integrity issues:
- Orphaned records (FK references that point to deleted rows)
- Duplicate entries that should be unique
- NULL values in columns that should always have data
- Inconsistent status values ('active' vs 'Active' vs 'ACTIVE')
- Date anomalies (created_at > updated_at, future dates)
Generate cleanup scripts with transaction safety."
Pro Tips
• Always test migrations on a copy of production data, not just empty schemas
• Ask for rollback scripts for every migration — you WILL need them
• Realistic seed data makes development and demos 10x better
• For large schema conversions, go table by table and verify each one
What You'll Learn
• Database design principles and normalisation
• Migration strategies and version control for schemas
• Cross-database SQL syntax differences
• Query optimisation and indexing strategies