You (Commander)
 |
 |-- Phase 1: Source Schema Discovery
 |    |-- Connect to source (connection string or dump file)
 |    |-- Extract full schema: tables, columns, types, defaults, constraints
 |    |-- Extract indexes, foreign keys, unique constraints, check constraints
 |    |-- Extract triggers, stored procedures, functions, views
 |    |-- Extract sequences, enums, custom types
 |    |-- Extract row counts per table
 |    |-- Extract sample data for type inference (MongoDB)
 |
 |-- Phase 2: Schema Analysis and Mapping
 |    |-- Map source data types to target data types
 |    |-- Identify incompatible features (provider-specific)
 |    |-- Resolve foreign key creation order (topological sort)
 |    |-- Plan trigger and stored procedure conversion
 |    |-- Identify data that needs transformation
 |    |-- Flag potential data loss or precision changes
 |
 |-- Phase 3: Migration Script Generation
 |    |-- Generate DDL scripts (CREATE TABLE, INDEX, etc.)
 |    |-- Generate DML scripts (INSERT, data transformation)
 |    |-- Generate trigger and stored procedure translations
 |    |-- Generate validation queries (row counts, checksums)
 |    |-- Generate rollback scripts (DROP, reverse migration)
 |
 |-- Phase 4: Validation Plan
 |    |-- Row count comparison queries
 |    |-- Data checksum queries (MD5/SHA256 of key columns)
 |    |-- Foreign key integrity checks
 |    |-- Index existence verification
 |    |-- Trigger and procedure existence verification
 |    |-- Sample data spot-checks
 |
 |-- Phase 5: migration-plan.md Generation
 |    |-- Executive summary
 |    |-- Step-by-step execution guide
 |    |-- Rollback procedures
 |    |-- Estimated downtime
 |    |-- Risk assessment
 |    |-- Pre-migration checklist
 |    |-- Post-migration verification

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;

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;

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;

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;

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;

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;

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

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;

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;

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;

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;

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;

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;

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;

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;

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

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;

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;

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

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;

// 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());
});

-- 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;

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.

-- =============================================================================
-- 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]';

// 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;
}

# 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

# 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]

# 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/

-- 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;

-- 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];

-- 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];

# 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

-- 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;

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];

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];

// 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"
        }}
    }}
]);

-- 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;

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

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;

-- 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();

-- 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;

-- =============================================================================
-- 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];

# 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]/

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

# 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 |