Database Migrator

Database Migrator

Move schemas, data, and logic between database providers. This is a cross-provider migration engine, not a schema designer: it handles data type incompatibilities, provider-specific SQL dialects, foreign key ordering, sequence and auto-increment translation, trigger rewrites, and stored procedure conversion. It validates with row counts and checksums and produces an auditable migration-plan.md.

Contents

• references/schema-discovery.md -- Extraction queries for relational, MongoDB, Supabase, and PlanetScale sources.
• references/type-mapping.md -- Data type maps (PG/MySQL/Mongo) and SQL function translation.
• references/script-generation.md -- DDL, sequence, trigger, procedure, and view translation.
• references/data-migration.md -- Export, transform, and import commands per provider.
• references/validation.md -- Row count, checksum, FK integrity, index, and spot-check queries.
• references/rollback-and-downtime.md -- Rollback scripts, backup/restore, downtime estimation.
• references/migration-plan-template.md -- Full migration-plan.md output template.
• references/edge-cases.md -- Large tables, lossy mappings, document flattening, multi-schema, quality checklist.

Supported Paths and Complexity

Supported source/target pairs: PostgreSQL, MySQL, Supabase (Postgres), PlanetScale (MySQL), and MongoDB, in any direction.

• Low: Same underlying engine (e.g., Postgres to Supabase). Mostly connection and permission changes.
• Medium: Same paradigm, different dialect (e.g., Postgres to MySQL). Requires type mapping and dialect translation.
• Medium-High: Different dialect plus provider constraints (e.g., PlanetScale has no database-level foreign keys).
• High: Paradigm shift (e.g., relational to document). Requires schema redesign, not just translation.

When to Use

• Moving a production database from one provider to another.
• Migrating between self-hosted and managed services.
• Replicating a schema across providers for multi-cloud or disaster recovery.
• Consolidating multiple databases into a single provider.
• Translating a complete data model between relational and MongoDB.
• Producing a validated, auditable migration plan before executing in production.

When NOT to Use

• Designing a new schema from scratch (use database-schema-designer).
• Migrating application code between frameworks (use full-codebase-migrator).
• Changing a few columns or adding one table (write the ALTER statements directly).
• Real-time replication or CDC -- this skill generates point-in-time scripts, not streaming pipelines.

Workflow

Follow these steps precisely when this skill is invoked.

1. Gather migration parameters. Establish: source provider and version; target provider and version; connection method (live or dump file); schema scope (which schemas/tables); data migration (schema only or schema + data, full or partial); downtime tolerance; data volume; application dependencies; and output location (default to current directory). If the user already supplied these, skip the questions and proceed.

2. Discover the source schema. Extract tables, columns, types, defaults, constraints, indexes, foreign keys, triggers, procedures, functions, views, sequences, enums, and row counts. For MongoDB, scan collections to infer schema. For Supabase, also extract RLS policies, extensions, and publications. See references/schema-discovery.md.

3. Map data types. Translate every source type to the best target type and flag lossy or precision-changing conversions. Translate provider-specific SQL functions. See references/type-mapping.md.

4. Generate schema scripts. Resolve table creation order by topological sort (defer cyclic foreign keys). Generate DDL, translate sequences/auto-increment, rewrite triggers and stored procedures, and translate views. See references/script-generation.md.

5. Generate data migration scripts. Produce export, transform, and import commands for the source and target providers. See references/data-migration.md.

6. Generate the validation plan. Produce row count, checksum, foreign key integrity, index, trigger/procedure, and sample data spot-check queries for source and target. See references/validation.md.

7. Generate the rollback plan and downtime estimate. Produce reverse-order DROP scripts, backup/restore commands, application rollback steps, and a phased downtime estimate with reduction strategies. See references/rollback-and-downtime.md.

8. Generate migration-plan.md. Assemble the executive summary, scope, schema inventory, type mapping, incompatibilities, scripts, validation, rollback, downtime, risk assessment, checklists, step-by-step execution guide, and required application changes. Use the template in references/migration-plan-template.md.

9. Handle edge cases. Account for extremely large tables, lossy mappings, MongoDB document flattening, PlanetScale foreign key workarounds, Supabase specifics, and multi-schema migrations. Verify the plan against the quality checklist. See references/edge-cases.md.

Data Migration Scripts

Data Export from Source

PostgreSQL / Supabase:

# Full table export to CSV
pg_dump --data-only --format=plain --table=[schema].[table] --file=[table].sql [dbname]

# Or CSV format for cross-platform compatibility
psql -c "COPY [schema].[table] TO STDOUT WITH CSV HEADER" [dbname] > [table].csv

MySQL / PlanetScale:

# Full table export
mysqldump --no-create-info --tab=/tmp/export --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' [dbname] [table]

# Or SELECT INTO OUTFILE
mysql -e "SELECT * INTO OUTFILE '/tmp/[table].csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM [table]" [dbname]

MongoDB:

# Export collection to JSON
mongoexport --db=[dbname] --collection=[collection] --out=[collection].json --jsonArray

# Or BSON for binary data preservation
mongodump --db=[dbname] --collection=[collection] --out=./dump/

Data Transformation

Generate transformation scripts for data that needs conversion:

-- Example: PostgreSQL boolean to MySQL tinyint
-- In the INSERT or LOAD DATA statement:
-- Replace TRUE with 1, FALSE with 0

-- Example: PostgreSQL array to MySQL JSON
-- Transform: '{1,2,3}' becomes '[1,2,3]'

-- Example: PostgreSQL UUID to MySQL CHAR(36)
-- No transformation needed if stored as text

-- Example: PostgreSQL TIMESTAMP WITH TIME ZONE to MySQL DATETIME
-- Convert to UTC before export:
SET timezone = 'UTC';
COPY (SELECT id, created_at AT TIME ZONE 'UTC' AS created_at FROM table) TO STDOUT WITH CSV HEADER;

Data Import to Target

PostgreSQL / Supabase (target):

-- Disable triggers during load
ALTER TABLE [table] DISABLE TRIGGER ALL;

-- Disable foreign key checks during load
SET session_replication_role = replica;

-- Load data
COPY [schema].[table] FROM '/path/to/[table].csv' WITH CSV HEADER;

-- Reset sequences after load
SELECT setval(pg_get_serial_sequence('[schema].[table]', '[id_column]'),
    (SELECT COALESCE(MAX([id_column]), 0) FROM [schema].[table]));

-- Re-enable triggers
ALTER TABLE [table] ENABLE TRIGGER ALL;

-- Re-enable foreign key checks
SET session_replication_role = DEFAULT;

-- Analyze tables for query planner
ANALYZE [schema].[table];

MySQL / PlanetScale (target):

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

-- Load data
LOAD DATA INFILE '/path/to/[table].csv'
INTO TABLE [table]
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Reset auto_increment
ALTER TABLE [table] AUTO_INCREMENT = (SELECT MAX(id) + 1 FROM [table]);

-- Re-enable checks
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
COMMIT;

-- Analyze tables
ANALYZE TABLE [table];

MongoDB (target):

# Import from JSON
mongoimport --db=[dbname] --collection=[collection] --file=[collection].json --jsonArray

# Or from BSON dump
mongorestore --db=[dbname] --collection=[collection] ./dump/[dbname]/[collection].bson

Edge Cases and Quality Checklist

Extremely Large Tables (100M+ rows)

For tables exceeding 100 million rows:

1. Chunked export: Export in batches of 1-10 million rows using LIMIT/OFFSET or range-based WHERE id BETWEEN x AND y. 2. Parallel import: Split data files and import in parallel using multiple connections. 3. Deferred index creation: Create the table without indexes, load data, then create indexes. This is significantly faster than loading into an indexed table. 4. Partitioned loading: If the target supports partitioning, create partitions first and load into each partition in parallel. 5. Progress tracking: Generate a progress script that reports rows loaded vs total rows.

Schema Differences That Require Data Transformation

When a type mapping is not lossless:

1. Precision loss: Flag any column where the target type has less precision. Example: PostgreSQL NUMERIC(38,18) to MySQL DECIMAL(38,18) is lossless, but NUMERIC(100,50) exceeds MySQL's limit of DECIMAL(65,30). 2. Encoding issues: UTF-8 4-byte characters require utf8mb4 in MySQL, not utf8. 3. Timezone handling: Document whether timestamps are stored as UTC or local time, and how the target handles timezone conversion. 4. NULL vs empty string: PostgreSQL distinguishes NULL from empty string; some applications may not.

MongoDB Document Flattening (MongoDB to Relational)

When migrating from MongoDB to a relational database:

1. Top-level fields: Map directly to columns in a primary table. 2. Embedded objects: Two strategies:

• Flatten: Prefix column names with the object path (e.g., address.street becomes address_street). Use when the object is always present and has a fixed schema.
• Separate table: Create a related table with a foreign key. Use when the object is optional or has variable schema.

3. Embedded arrays: Always create a junction or child table. Each array element becomes a row. 4. Polymorphic documents: Documents in the same collection with different shapes. Options:

• Single Table Inheritance: One wide table with nullable columns for each document shape.
• Class Table Inheritance: Separate tables per document shape with a shared base table.
• Discriminator column: Single table with a type column to distinguish shapes.

5. Nested arrays of objects: Requires recursive flattening into multiple tables with foreign keys at each level.

PlanetScale Foreign Key Workarounds

Since PlanetScale does not support database-level foreign keys:

1. Document all relationships in the migration plan as application-level constraints. 2. Generate application-level validation code (e.g., Prisma schema with @relation, or custom middleware). 3. Generate orphan detection queries to run periodically:

   -- Check for orphaned child rows (run periodically)
   SELECT c.id, c.[fk_column]
   FROM [child_table] c
   LEFT JOIN [parent_table] p ON c.[fk_column] = p.id
   WHERE c.[fk_column] IS NOT NULL AND p.id IS NULL;

4. Document cascade delete logic that must be implemented in the application.

Supabase-Specific Migration Considerations

When migrating to Supabase:

1. Row Level Security (RLS): Generate RLS policies based on the application's authorization model. Document that RLS must be enabled on all tables exposed via the Supabase API. 2. Realtime subscriptions: Identify tables that need realtime and add them to the Supabase publication. 3. Storage buckets: If the source database stores file references, map them to Supabase Storage. 4. Edge Functions: If stored procedures contain API-callable logic, recommend migrating to Supabase Edge Functions. 5. Auth integration: If the source has a users table, document how to integrate with Supabase Auth.

Multi-Schema or Multi-Database Migration

When the source has multiple schemas or databases:

1. Map source schemas to target schemas (if the target supports multiple schemas). 2. Merge schemas: If the target is single-schema (e.g., PlanetScale), prefix table names with the schema name. 3. Cross-schema references: Identify and document all cross-schema foreign keys. These may need special handling. 4. Schema-level permissions: Document permission differences between source and target.

Quality Checklist for the Migration Plan

Before delivering the plan, verify:

• [ ] Every source table is accounted for in the schema inventory
• [ ] Every column has a type mapping documented
• [ ] Every foreign key has a creation order assigned
• [ ] Every index is included in the DDL scripts
• [ ] Every trigger is translated or documented as needing application-level migration
• [ ] Every stored procedure is translated or documented as needing application-level migration
• [ ] Every view is translated
• [ ] Data type incompatibilities are flagged with workarounds
• [ ] Row count validation queries are generated for every table
• [ ] Checksum validation queries are generated for critical tables
• [ ] Rollback scripts are complete and tested
• [ ] Downtime estimate accounts for all phases
• [ ] Pre-migration and post-migration checklists are included
• [ ] The step-by-step execution guide is actionable -- a DBA can follow it without additional context

migration-plan.md Template

Write the final deliverable using this structure.

# Database Migration Plan: [Source Provider] to [Target Provider]

**Generated**: [timestamp]
**Source**: [provider] [version] at [host/connection]
**Target**: [provider] [version] at [host/connection]
**Total tables**: [N]
**Total estimated rows**: [N]
**Total estimated data size**: [N GB]
**Estimated downtime**: [N hours N minutes]
**Migration strategy**: [Offline / Online with dual-write / Replication-based]

---

## Table of Contents

1. [Executive Summary](#executive-summary)
2. [Migration Scope](#migration-scope)
3. [Schema Inventory](#schema-inventory)
4. [Data Type Mapping](#data-type-mapping)
5. [Incompatibilities and Workarounds](#incompatibilities-and-workarounds)
6. [Migration Scripts](#migration-scripts)
7. [Data Migration Procedure](#data-migration-procedure)
8. [Trigger and Procedure Translation](#trigger-and-procedure-translation)
9. [Validation Plan](#validation-plan)
10. [Rollback Procedures](#rollback-procedures)
11. [Downtime Estimate](#downtime-estimate)
12. [Risk Assessment](#risk-assessment)
13. [Pre-Migration Checklist](#pre-migration-checklist)
14. [Step-by-Step Execution Guide](#step-by-step-execution-guide)
15. [Post-Migration Verification](#post-migration-verification)
16. [Application Changes Required](#application-changes-required)

## Executive Summary

[2-3 paragraphs: what is being migrated, why, key risks, data volume,
estimated downtime, recommended strategy, and critical incompatibilities
that require attention.]

## Migration Scope

- **Source**: [provider, version, host, schema/database name]
- **Target**: [provider, version, host, schema/database name]
- **Tables included**: [N] (list or "all")
- **Tables excluded**: [list, if any, with reasons]
- **Data scope**: [Full / Partial (e.g., last 90 days)]
- **Includes triggers**: [Yes/No -- count]
- **Includes stored procedures**: [Yes/No -- count]
- **Includes views**: [Yes/No -- count]

## Schema Inventory

### Table Summary
| # | Table Name | Columns | Rows (est.) | Size (est.) | Foreign Keys | Indexes | Triggers | Notes |
|---|-----------|---------|-------------|-------------|-------------|---------|----------|-------|
| 1 | users | 12 | 50,000 | 15 MB | 0 | 3 | 1 | -- |
| 2 | orders | 18 | 1,200,000 | 890 MB | 3 | 7 | 2 | Largest table |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |

### Enums and Custom Types
[List all enums, custom types, and their translation strategy]

### Sequences
[List all sequences and their translation strategy]

## Data Type Mapping

[Table showing every column's source type, target type, and any transformation needed]

| Table | Column | Source Type | Target Type | Transformation | Risk |
|-------|--------|-----------|-------------|----------------|------|
| users | id | UUID | CHAR(36) | None | Low |
| users | metadata | JSONB | JSON | Loses GIN index; add generated columns | Medium |
| orders | total | NUMERIC(12,4) | DECIMAL(12,4) | None | Low |
| ... | ... | ... | ... | ... | ... |

## Incompatibilities and Workarounds

[List every feature that does not translate directly, with the recommended workaround]

| Feature | Source Behavior | Target Limitation | Workaround |
|---------|---------------|-------------------|------------|
| JSONB GIN indexes | Native indexed JSON queries | JSON type without GIN | Add generated columns + B-tree indexes |
| Array columns | Native ARRAY type | No native arrays | Store as JSON array |
| ... | ... | ... | ... |

## Migration Scripts

[Include or reference the generated DDL scripts, organized by execution order]

### Table Creation Order
1. [table with no FK dependencies]
2. [table with no FK dependencies]
3. [table depending on #1]
4. ...

### DDL Scripts
[Full CREATE TABLE, INDEX, CONSTRAINT statements]

### Deferred Constraints
[ALTER TABLE statements for cyclic foreign keys, to run after all tables exist]

## Data Migration Procedure

[Step-by-step data export, transform, load instructions]

## Trigger and Procedure Translation

[For each trigger and procedure: original source code, translated target code, behavioral differences]

## Validation Plan

### Row Count Checks
[Queries to compare row counts between source and target]

### Data Checksum Checks
[Queries to compare checksums of critical tables]

### Referential Integrity Checks
[Queries to verify foreign key integrity in the target]

### Index Verification
[Queries to verify all indexes exist in the target]

### Sample Data Spot-Checks
[Specific rows to compare between source and target]

## Rollback Procedures

### Full Rollback
[Complete rollback script to reverse the migration]

### Partial Rollback (per-table)
[Rollback scripts for individual tables if a single table fails]

### Application Rollback
[Steps to revert application connection strings and code changes]

### Backup and Restore
[Backup commands to run before migration; restore commands for recovery]

## Downtime Estimate

| Phase | Estimated Duration | Can Run Before Maintenance Window |
|-------|-------------------|----------------------------------|
| Pre-create schema | [N min] | Yes |
| Export data from source | [N min] | Yes (if acceptable staleness) |
| Transfer data | [N min] | Depends on strategy |
| Import data to target | [N min] | No (requires write lock) |
| Create indexes | [N min] | After import |
| Run validation | [N min] | After import |
| Application switchover | [N min] | Final step |
| **Total maintenance window** | **[N hours N min]** | -- |
| **Total with buffer (20%)** | **[N hours N min]** | -- |

## Risk Assessment

| Risk | Likelihood | Impact | Mitigation |
|------|-----------|--------|------------|
| Data type precision loss | [L/M/H] | [L/M/H] | [Mitigation] |
| Trigger behavior difference | [L/M/H] | [L/M/H] | [Mitigation] |
| Application query incompatibility | [L/M/H] | [L/M/H] | [Mitigation] |
| Downtime exceeds estimate | [L/M/H] | [L/M/H] | [Mitigation] |
| Rollback needed after partial migration | [L/M/H] | [L/M/H] | [Mitigation] |
| Connection pooler incompatibility | [L/M/H] | [L/M/H] | [Mitigation] |

## Pre-Migration Checklist

- [ ] Source database credentials verified and tested
- [ ] Target database provisioned and accessible
- [ ] Target database version confirmed compatible
- [ ] Network connectivity between source and target verified
- [ ] Sufficient disk space on export machine (2x data size recommended)
- [ ] Sufficient disk space on target (3x data size for import + index building)
- [ ] Pre-migration backup of source database completed
- [ ] Pre-migration backup of target database completed (if not empty)
- [ ] All team members notified of maintenance window
- [ ] Application deployment pipeline ready for connection string update
- [ ] Monitoring and alerting configured for target database
- [ ] Rollback procedure reviewed and tested on staging
- [ ] Migration scripts tested on staging environment with production-like data
- [ ] Application tested against target database on staging
- [ ] DNS TTL lowered (if using DNS-based switchover)

## Step-by-Step Execution Guide

### Before Maintenance Window (can be done in advance)

1. [ ] Run pre-migration backup of source database
2. [ ] Execute schema DDL scripts on target (tables, indexes, types, enums)
3. [ ] Verify schema created correctly (run index and constraint verification queries)
4. [ ] Test application connectivity to target database (read-only)

### During Maintenance Window

5. [ ] Announce maintenance window start
6. [ ] Set application to maintenance mode / read-only mode
7. [ ] Verify no active writes to source database
8. [ ] Export data from source database
9. [ ] Transform data (if transformations needed)
10. [ ] Disable foreign key checks and triggers on target
11. [ ] Import data to target database
12. [ ] Re-enable foreign key checks and triggers on target
13. [ ] Reset sequences / auto-increment values
14. [ ] Run validation: row counts
15. [ ] Run validation: checksums on critical tables
16. [ ] Run validation: foreign key integrity
17. [ ] Run validation: sample data spot-checks
18. [ ] If validation passes: update application connection string to target
19. [ ] If validation fails: execute rollback procedure, restore source
20. [ ] Deploy application with new connection string
21. [ ] Verify application is functioning correctly
22. [ ] Monitor error rates and query performance for 30 minutes
23. [ ] Announce maintenance window end

### After Maintenance Window

24. [ ] Monitor target database performance for 24 hours
25. [ ] Compare query performance (slow query log) against baseline
26. [ ] Verify all scheduled jobs and background workers are functioning
27. [ ] Keep source database running (read-only) for 7 days as safety net
28. [ ] After 7 days with no issues: decommission source database
29. [ ] Update documentation with new connection details
30. [ ] Archive migration scripts and plan for audit trail

## Post-Migration Verification

- [ ] All tables present in target with correct schema
- [ ] Row counts match between source and target for all tables
- [ ] Checksums match for critical tables
- [ ] All indexes present and functional
- [ ] All foreign keys present and valid (or documented as application-level)
- [ ] All triggers present and functional (or documented as moved to application)
- [ ] All stored procedures present and functional (or documented as moved to application)
- [ ] All views present and returning correct results
- [ ] Application login and authentication working
- [ ] Application CRUD operations working
- [ ] Application search functionality working
- [ ] Background jobs executing successfully
- [ ] API response times within acceptable range
- [ ] No increase in error rates
- [ ] Monitoring dashboards updated to track target database

## Application Changes Required

[List all application-level changes needed to work with the new database]

| Change | File/Service | Description | Priority |
|--------|-------------|-------------|----------|
| Connection string | .env / config | Update DATABASE_URL to target | Critical |
| ORM dialect | db/config | Change dialect from X to Y | Critical |
| Query syntax | [list files] | Rewrite provider-specific queries | High |
| Trigger logic | [list files] | Move trigger logic to application middleware | High |
| Stored proc calls | [list files] | Replace CALL/SELECT with application functions | High |
| Type handling | [list files] | Update type mappings (e.g., boolean handling) | Medium |

Rollback Plan and Downtime Estimation

Rollback Plan

Every migration must have a tested rollback plan. Generate rollback scripts for each phase.

Schema Rollback

Generate DROP statements in reverse topological order:

-- =============================================================================
-- ROLLBACK SCRIPT: Drop all migrated objects
-- Execute in this exact order (reverse dependency order)
-- =============================================================================

-- Drop views first (they depend on tables)
DROP VIEW IF EXISTS [target_schema].[view_name] CASCADE;

-- Drop triggers
DROP TRIGGER IF EXISTS [trigger_name] ON [target_schema].[table_name];

-- Drop functions and procedures
DROP FUNCTION IF EXISTS [target_schema].[function_name]([arg_types]);
DROP PROCEDURE IF EXISTS [target_schema].[procedure_name]([arg_types]);

-- Drop tables in reverse topological order (children before parents)
DROP TABLE IF EXISTS [target_schema].[child_table] CASCADE;
DROP TABLE IF EXISTS [target_schema].[parent_table] CASCADE;

-- Drop sequences (PostgreSQL)
DROP SEQUENCE IF EXISTS [target_schema].[sequence_name];

-- Drop custom types (PostgreSQL)
DROP TYPE IF EXISTS [target_schema].[type_name];

-- Drop schema if it was created for the migration
DROP SCHEMA IF EXISTS [target_schema];

Data Rollback

If the migration replaces an existing database (not a fresh target):

1. Pre-migration backup: Generate a backup command to run BEFORE the migration starts. 2. Restore from backup: Document the restore procedure.

# Pre-migration backup (PostgreSQL)
pg_dump --format=custom --file=pre_migration_backup_$(date +%Y%m%d_%H%M%S).dump [dbname]

# Restore from backup (PostgreSQL)
pg_restore --clean --if-exists --dbname=[dbname] pre_migration_backup_[timestamp].dump

# Pre-migration backup (MySQL)
mysqldump --single-transaction --routines --triggers --events [dbname] > pre_migration_backup_$(date +%Y%m%d_%H%M%S).sql

# Restore from backup (MySQL)
mysql [dbname] < pre_migration_backup_[timestamp].sql

# Pre-migration backup (MongoDB)
mongodump --db=[dbname] --out=pre_migration_backup_$(date +%Y%m%d_%H%M%S)/

# Restore from backup (MongoDB)
mongorestore --db=[dbname] --drop pre_migration_backup_[timestamp]/[dbname]/

Application Rollback

Document application changes needed if the migration is rolled back:

• Connection string changes to revert
• ORM/query changes to revert
• Environment variable changes to revert
• DNS / connection pooler changes to revert

Downtime Estimation

Calculate estimated downtime based on data volume and migration method.

Downtime Factors

Estimation Formula

Estimated downtime =
    Schema DDL:           ~1 minute per 100 tables
  + Data export:          ~1 minute per GB (SSD) or ~3 minutes per GB (HDD)
  + Data transfer:        data_size_gb / (network_bandwidth_mbps / 8 / 1024)
  + Data import:          ~2 minutes per GB (without indexes) or ~5 minutes per GB (with indexes)
  + Index creation:       ~1 minute per index per million rows
  + Constraint validation: ~30 seconds per foreign key per million rows
  + Validation:           ~2 minutes per 10 tables
  + Application switch:   ~5 minutes (conservative)
  + Buffer (20%):         total * 0.2

Downtime Reduction Strategies

Document these options in the migration plan:

1. Pre-create schema: Create all tables, indexes, and constraints before the maintenance window. Only data load happens during downtime. 2. Create indexes after load: Load data without indexes, then create indexes. Faster overall but indexes are built from scratch. 3. Parallel table loads: Load independent tables simultaneously (tables with no FK dependencies between them). 4. Disable constraints during load: Turn off FK checks and unique checks during bulk load. Re-enable after. 5. Use native replication for zero-downtime: For same-engine migrations (e.g., Postgres to Supabase), use logical replication to sync in real-time, then cut over. 6. Dual-write period: Application writes to both old and new database during transition. Complex but eliminates downtime.

Source Schema Discovery

Extract everything from the source database. This is the foundation of the entire migration.

Relational Databases (Postgres, MySQL, Supabase, PlanetScale)

Extract the following using information_schema queries or provider-specific catalog queries.

Tables and Columns

PostgreSQL / Supabase:

SELECT
    t.table_schema,
    t.table_name,
    c.column_name,
    c.ordinal_position,
    c.data_type,
    c.udt_name,
    c.character_maximum_length,
    c.numeric_precision,
    c.numeric_scale,
    c.is_nullable,
    c.column_default,
    c.is_identity,
    c.identity_generation,
    pgd.description AS column_comment
FROM information_schema.tables t
JOIN information_schema.columns c
    ON t.table_schema = c.table_schema AND t.table_name = c.table_name
LEFT JOIN pg_catalog.pg_statio_all_tables psat
    ON psat.schemaname = t.table_schema AND psat.relname = t.table_name
LEFT JOIN pg_catalog.pg_description pgd
    ON pgd.objoid = psat.relid AND pgd.objsubid = c.ordinal_position
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
    AND t.table_type = 'BASE TABLE'
ORDER BY t.table_schema, t.table_name, c.ordinal_position;

MySQL / PlanetScale:

SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    c.COLUMN_NAME,
    c.ORDINAL_POSITION,
    c.DATA_TYPE,
    c.COLUMN_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH,
    c.NUMERIC_PRECISION,
    c.NUMERIC_SCALE,
    c.IS_NULLABLE,
    c.COLUMN_DEFAULT,
    c.EXTRA,
    c.COLUMN_COMMENT
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
    AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, c.ORDINAL_POSITION;

Primary Keys

PostgreSQL / Supabase:

SELECT
    tc.table_schema,
    tc.table_name,
    tc.constraint_name,
    kcu.column_name,
    kcu.ordinal_position
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
    AND tc.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY tc.table_schema, tc.table_name, kcu.ordinal_position;

MySQL / PlanetScale:

SELECT
    tc.TABLE_SCHEMA,
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    kcu.COLUMN_NAME,
    kcu.ORDINAL_POSITION
FROM information_schema.TABLE_CONSTRAINTS tc
JOIN information_schema.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND tc.TABLE_NAME = kcu.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND tc.TABLE_SCHEMA = DATABASE()
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, kcu.ORDINAL_POSITION;

Foreign Keys

PostgreSQL / Supabase:

SELECT
    tc.table_schema,
    tc.table_name,
    tc.constraint_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
JOIN information_schema.referential_constraints rc
    ON tc.constraint_name = rc.constraint_name AND tc.table_schema = rc.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY tc.table_schema, tc.table_name;

MySQL / PlanetScale:

SELECT
    tc.TABLE_SCHEMA,
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    kcu.COLUMN_NAME,
    kcu.REFERENCED_TABLE_SCHEMA,
    kcu.REFERENCED_TABLE_NAME,
    kcu.REFERENCED_COLUMN_NAME,
    rc.UPDATE_RULE,
    rc.DELETE_RULE
FROM information_schema.TABLE_CONSTRAINTS tc
JOIN information_schema.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND tc.TABLE_NAME = kcu.TABLE_NAME
JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
    ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND tc.TABLE_SCHEMA = DATABASE()
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME;

Indexes

PostgreSQL / Supabase:

SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename, indexname;

MySQL / PlanetScale:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    INDEX_TYPE,
    SUB_PART,
    EXPRESSION
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

Check Constraints

PostgreSQL / Supabase:

SELECT
    tc.table_schema,
    tc.table_name,
    tc.constraint_name,
    cc.check_clause
FROM information_schema.table_constraints tc
JOIN information_schema.check_constraints cc
    ON tc.constraint_name = cc.constraint_name AND tc.constraint_schema = cc.constraint_schema
WHERE tc.constraint_type = 'CHECK'
    AND tc.table_schema NOT IN ('pg_catalog', 'information_schema')
    AND cc.check_clause NOT LIKE '%IS NOT NULL%'
ORDER BY tc.table_schema, tc.table_name;

MySQL 8.0+:

SELECT
    tc.TABLE_SCHEMA,
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    cc.CHECK_CLAUSE
FROM information_schema.TABLE_CONSTRAINTS tc
JOIN information_schema.CHECK_CONSTRAINTS cc
    ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME AND tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
WHERE tc.CONSTRAINT_TYPE = 'CHECK'
    AND tc.TABLE_SCHEMA = DATABASE()
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME;

Triggers

PostgreSQL / Supabase:

SELECT
    trigger_schema,
    trigger_name,
    event_manipulation,
    event_object_schema,
    event_object_table,
    action_statement,
    action_timing,
    action_orientation
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY trigger_schema, event_object_table, trigger_name;

MySQL / PlanetScale:

SELECT
    TRIGGER_SCHEMA,
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_SCHEMA,
    EVENT_OBJECT_TABLE,
    ACTION_STATEMENT,
    ACTION_TIMING,
    ACTION_ORIENTATION
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY TRIGGER_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME;

Stored Procedures and Functions

PostgreSQL / Supabase:

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS return_type,
    CASE p.prokind
        WHEN 'f' THEN 'FUNCTION'
        WHEN 'p' THEN 'PROCEDURE'
        WHEN 'a' THEN 'AGGREGATE'
        WHEN 'w' THEN 'WINDOW'
    END AS kind,
    l.lanname AS language,
    pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;

MySQL / PlanetScale:

SELECT
    ROUTINE_SCHEMA,
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DATA_TYPE,
    ROUTINE_DEFINITION,
    EXTERNAL_LANGUAGE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME;

Views

PostgreSQL / Supabase:

SELECT
    table_schema,
    table_name AS view_name,
    view_definition
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

MySQL / PlanetScale:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME AS VIEW_NAME,
    VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Sequences (PostgreSQL / Supabase only)

SELECT
    schemaname,
    sequencename,
    data_type,
    start_value,
    min_value,
    max_value,
    increment_by,
    cycle,
    last_value
FROM pg_sequences
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, sequencename;

Enums (PostgreSQL / Supabase only)

SELECT
    n.nspname AS schema_name,
    t.typname AS enum_name,
    string_agg(e.enumlabel, ', ' ORDER BY e.enumsortorder) AS enum_values
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY n.nspname, t.typname
ORDER BY n.nspname, t.typname;

Row Counts

PostgreSQL / Supabase (fast estimate):

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS estimated_row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

MySQL / PlanetScale:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS AS estimated_row_count,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_ROWS DESC;

MongoDB

MongoDB has no enforced schema, so discovery requires collection scanning:

// List all collections
db.getCollectionNames().forEach(function(collName) {
    print("--- Collection: " + collName + " ---");

    // Row count
    print("Document count: " + db[collName].countDocuments({}));

    // Sample documents for schema inference
    var sample = db[collName].aggregate([{ $sample: { size: 100 } }]).toArray();

    // Infer schema from sample
    var schema = {};
    sample.forEach(function(doc) {
        function inferType(obj, prefix) {
            for (var key in obj) {
                var fullKey = prefix ? prefix + "." + key : key;
                var val = obj[key];
                var type = typeof val;
                if (val === null) type = "null";
                else if (Array.isArray(val)) type = "array";
                else if (val instanceof ObjectId) type = "ObjectId";
                else if (val instanceof Date) type = "Date";
                else if (val instanceof NumberDecimal) type = "Decimal128";
                else if (type === "object") {
                    inferType(val, fullKey);
                    type = "object";
                }
                if (!schema[fullKey]) schema[fullKey] = {};
                schema[fullKey][type] = (schema[fullKey][type] || 0) + 1;
            }
        }
        inferType(doc, "");
    });

    printjson(schema);

    // Indexes
    printjson(db[collName].getIndexes());
});

Also extract:

• Validators: db.getCollectionInfos() for JSON Schema validators
• Capped collections: Size and max document limits
• Sharding config: sh.status() if sharded
• Aggregation pipelines saved as views: db.system.views.find()

Supabase-Specific Extraction

When the source or target is Supabase, also extract:

-- Row Level Security policies
SELECT
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename, policyname;

-- RLS enabled tables
SELECT
    schemaname,
    tablename,
    rowsecurity
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

-- Extensions
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- Publication/subscription (for realtime)
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

PlanetScale-Specific Considerations

PlanetScale does not support:

• Foreign key constraints at the database level (enforced at application level)
• Stored procedures
• Triggers
• Events

When PlanetScale is the target, flag all of these for application-level handling. When PlanetScale is the source, note that foreign key relationships must be inferred from naming conventions and application code.

Schema Translation and Script Generation

With the full schema extracted and type mapping established, generate the migration scripts.

Table Creation Order

Foreign keys create dependencies between tables. Tables must be created in topological order (dependencies first):

1. Build a directed graph where an edge from table A to table B means A has a foreign key referencing B. 2. Perform a topological sort on this graph. 3. If cycles exist (mutual foreign keys), break the cycle by deferring one foreign key constraint to be added after all tables are created.

Table creation order algorithm:

1. Find all tables with zero foreign key dependencies -- these go first.
2. Remove those tables from the graph.
3. Repeat until all tables are placed.
4. If the graph is not empty after exhaustion, cycles exist.
   For each cycle: create all tables without the cyclic FK, then ALTER TABLE to add it.

DDL Script Generation

For each table, generate the target-dialect CREATE TABLE statement. The script must include:

1. Table definition with all columns, mapped types, defaults, and NOT NULL constraints 2. Primary key definition (inline or as constraint) 3. Unique constraints 4. Check constraints (translated to target dialect) 5. Foreign key constraints (respecting creation order above) 6. Indexes (translated to target syntax) 7. Comments on tables and columns (if supported by target)

Template for each table in the output:

-- =============================================================================
-- Table: [schema].[table_name]
-- Source: [source_provider] [schema].[table_name]
-- Rows (estimated): [N]
-- =============================================================================

CREATE TABLE [target_schema].[table_name] (
    [column definitions with mapped types]
);

-- Primary Key
ALTER TABLE [target_schema].[table_name]
    ADD CONSTRAINT pk_[table_name] PRIMARY KEY ([columns]);

-- Unique Constraints
ALTER TABLE [target_schema].[table_name]
    ADD CONSTRAINT uk_[table_name]_[columns] UNIQUE ([columns]);

-- Check Constraints
ALTER TABLE [target_schema].[table_name]
    ADD CONSTRAINT ck_[table_name]_[name] CHECK ([translated_expression]);

-- Foreign Keys (only if target supports them)
ALTER TABLE [target_schema].[table_name]
    ADD CONSTRAINT fk_[table_name]_[column]
    FOREIGN KEY ([column]) REFERENCES [target_schema].[referenced_table]([referenced_column])
    ON UPDATE [action] ON DELETE [action];

-- Indexes
CREATE INDEX idx_[table_name]_[columns] ON [target_schema].[table_name] ([columns]);
CREATE UNIQUE INDEX uidx_[table_name]_[columns] ON [target_schema].[table_name] ([columns]);

-- Comments
COMMENT ON TABLE [target_schema].[table_name] IS '[description]';
COMMENT ON COLUMN [target_schema].[table_name].[column] IS '[description]';

Sequence and Auto-Increment Translation

PostgreSQL to MySQL:

• Replace SERIAL / BIGSERIAL with AUTO_INCREMENT
• Replace GENERATED ALWAYS AS IDENTITY with AUTO_INCREMENT
• Remove all CREATE SEQUENCE statements
• Remove all DEFAULT nextval('sequence_name') and use AUTO_INCREMENT on the column
• After data load, set AUTO_INCREMENT value: ALTER TABLE t AUTO_INCREMENT = [max_id + 1];

MySQL to PostgreSQL:

• Replace AUTO_INCREMENT with GENERATED ALWAYS AS IDENTITY (preferred) or SERIAL
• After data load, reset sequence: SELECT setval('table_column_seq', (SELECT MAX(column) FROM table));

Relational to MongoDB:

• Remove auto-increment entirely; use ObjectId for _id unless the application requires numeric IDs
• If numeric IDs are required, document a counter collection pattern:

// Counter collection for auto-increment emulation
db.counters.insertOne({ _id: "table_name", seq: 0 });

// Get next ID
function getNextSequence(name) {
    var ret = db.counters.findOneAndUpdate(
        { _id: name },
        { $inc: { seq: 1 } },
        { returnDocument: "after" }
    );
    return ret.seq;
}

Trigger Translation

Triggers are the most provider-specific feature. Each translation requires careful rewriting.

PostgreSQL triggers to MySQL:

• PostgreSQL uses trigger functions (PL/pgSQL); MySQL uses inline trigger bodies
• Replace NEW.column / OLD.column syntax (same in both, but function wrapper differs)
• Replace RETURN NEW; / RETURN OLD; (not needed in MySQL)
• Replace TG_OP with separate triggers per operation
• Replace RAISE EXCEPTION with SIGNAL SQLSTATE

MySQL triggers to PostgreSQL:

• Wrap trigger body in a PL/pgSQL function
• Add RETURN NEW; or RETURN NULL; as appropriate
• Replace SIGNAL SQLSTATE with RAISE EXCEPTION

Relational triggers to MongoDB:

• Document that MongoDB does not have database-level triggers
• Recommend alternatives:
• MongoDB Change Streams (for event-driven processing)
• Application-level middleware (Mongoose pre/post hooks)
• Atlas Triggers (if using MongoDB Atlas)

PlanetScale target:

• PlanetScale does not support triggers
• Document all trigger logic that must move to the application layer
• Generate application-level middleware code or ORM hooks as replacements

Stored Procedure and Function Translation

PostgreSQL to MySQL:

• Replace CREATE OR REPLACE FUNCTION with CREATE PROCEDURE or CREATE FUNCTION
• Replace PL/pgSQL syntax with MySQL procedural SQL
• Replace RETURNS TABLE(...) with result set from SELECT
• Replace $$ delimiters with DELIMITER // ... // pattern
• Replace RAISE NOTICE with SELECT for debug output
• Replace RAISE EXCEPTION with SIGNAL SQLSTATE
• Replace PERFORM with DO or SELECT ... INTO @dummy
• Replace RETURNING clause (not available in MySQL; use LAST_INSERT_ID())

MySQL to PostgreSQL:

• Replace DELIMITER pattern with $$ delimiters
• Replace SIGNAL SQLSTATE with RAISE EXCEPTION
• Replace LAST_INSERT_ID() with RETURNING clause or currval()
• Replace GROUP_CONCAT with string_agg
• Replace IFNULL with COALESCE
• Replace IF() function with CASE WHEN

Relational to MongoDB:

• Stored procedures do not exist in MongoDB
• Translate to:
• Aggregation pipelines (for data processing logic)
• Application-level service functions
• MongoDB Atlas Functions (if using Atlas)

PlanetScale target:

• PlanetScale does not support stored procedures
• All procedural logic must move to the application layer

View Translation

Views are generally straightforward to translate but may contain provider-specific SQL:

1. Extract the view definition SQL. 2. Translate any provider-specific functions (see references/type-mapping.md function mapping). 3. Translate data types in CAST expressions. 4. Adjust JOIN syntax if needed. 5. For materialized views (PostgreSQL), note that MySQL does not support them natively -- recommend creating a table with a refresh procedure instead.

Data Type and Function Mapping

The core translation layer. Map every source type to the best target type, and translate provider-specific SQL functions.

PostgreSQL to MySQL Type Map

MySQL to PostgreSQL Type Map

Relational to MongoDB Type Map

MongoDB to Relational Type Map

Common SQL Function Mapping

Validation Plan

Every migration must be validated. Generate validation queries for both source and target.

Row Count Validation

Generate a query for each table that compares source and target row counts:

-- Source (run on source database)
SELECT '[table_name]' AS table_name, COUNT(*) AS row_count FROM [source_schema].[table_name]
UNION ALL
SELECT '[table_name_2]', COUNT(*) FROM [source_schema].[table_name_2]
-- ... repeat for all tables
ORDER BY table_name;

-- Target (run on target database)
SELECT '[table_name]' AS table_name, COUNT(*) AS row_count FROM [target_schema].[table_name]
UNION ALL
SELECT '[table_name_2]', COUNT(*) FROM [target_schema].[table_name_2]
-- ... repeat for all tables
ORDER BY table_name;

Expected result: every table has identical row counts.

Data Checksum Validation

Generate checksum queries for critical tables. Compare a hash of key columns between source and target.

PostgreSQL (source or target):

SELECT
    MD5(string_agg(
        COALESCE(id::text, 'NULL') || '|' ||
        COALESCE(name, 'NULL') || '|' ||
        COALESCE(email, 'NULL') || '|' ||
        COALESCE(created_at::text, 'NULL'),
        ',' ORDER BY id
    )) AS table_checksum
FROM [schema].[table];

MySQL (source or target):

SELECT
    MD5(GROUP_CONCAT(
        CONCAT_WS('|',
            COALESCE(id, 'NULL'),
            COALESCE(name, 'NULL'),
            COALESCE(email, 'NULL'),
            COALESCE(created_at, 'NULL')
        )
        ORDER BY id SEPARATOR ','
    )) AS table_checksum
FROM [table];

MongoDB (source or target):

// Hash all documents in a collection
var hash = db[collection].aggregate([
    { $sort: { _id: 1 } },
    { $group: {
        _id: null,
        docs: { $push: { $concat: [
            { $toString: "$_id" }, "|",
            { $ifNull: ["$name", "NULL"] }, "|",
            { $ifNull: ["$email", "NULL"] }
        ]}}
    }},
    { $project: {
        checksum: { $function: {
            body: function(arr) { return hex_md5(arr.join(",")); },
            args: ["$docs"],
            lang: "js"
        }}
    }}
]);

Expected result: checksums match between source and target for every validated table.

Foreign Key Integrity Validation

For every foreign key in the target, verify referential integrity:

-- Verify no orphaned foreign keys
SELECT COUNT(*) AS orphaned_rows
FROM [child_table] c
LEFT JOIN [parent_table] p ON c.[fk_column] = p.[pk_column]
WHERE c.[fk_column] IS NOT NULL AND p.[pk_column] IS NULL;

Expected result: zero orphaned rows for every foreign key relationship.

Index Verification

PostgreSQL / Supabase:

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = '[target_schema]'
ORDER BY tablename, indexname;

MySQL / PlanetScale:

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

Compare the count and names against the expected index list from the migration scripts.

Trigger and Procedure Verification

-- PostgreSQL: verify triggers
SELECT trigger_name, event_object_table, action_timing, event_manipulation
FROM information_schema.triggers
WHERE trigger_schema = '[target_schema]';

-- PostgreSQL: verify functions/procedures
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = '[target_schema]';

-- MySQL: verify triggers
SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

-- MySQL: verify procedures
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE();

Sample Data Spot-Check

For the 5 largest tables, generate queries that compare specific rows:

-- Pick 10 random IDs from source, then verify those exact rows exist in target with matching data
-- Source:
SELECT * FROM [table] WHERE id IN ([random_id_1], [random_id_2], ..., [random_id_10]) ORDER BY id;

-- Target:
SELECT * FROM [table] WHERE id IN ([random_id_1], [random_id_2], ..., [random_id_10]) ORDER BY id;