compare_schemas

Compare database schemas between environments to detect drift and plan migrations.

The compare_schemas tool provides intelligent schema comparison using ICE (Insight-Context-Execution) scoring to prioritize differences and generate actionable migration plans.

Overview

Schema drift is a common challenge in multi-environment deployments. Development schemas evolve rapidly, but keeping staging and production synchronized requires careful planning. The compare_schemas tool automates drift detection and provides ICE-scored recommendations for safe migration.

Key Features

• 🔍 Comprehensive Drift Detection: Missing tables, columns, indexes, foreign keys, and type mismatches
• 🎯 ICE Scoring: Every difference scored on Insight, Context, and Execution dimensions
• 📊 Priority Derivation: Automatic high/medium/low prioritization based on combined ICE scores
• 🔄 Migration Planning: Generated SQL statements ordered by dependencies
• 📈 Environment Awareness: Context scores reflect production criticality (10) vs development (4)

Parameters

{
  sourceDatabaseId: string,      // Database ID to compare FROM
  sourceEnvironment: Environment, // "development" | "staging" | "production"
  targetDatabaseId: string,      // Database ID to compare TO
  targetEnvironment: Environment  // "development" | "staging" | "production"
}

Parameter Details

Parameter	Type	Required	Description
sourceDatabaseId	string	✅	Source database identifier (e.g., "dev-db-abc123")
sourceEnvironment	Environment	✅	Source environment context for ICE scoring
targetDatabaseId	string	✅	Target database identifier (must differ from source)
targetEnvironment	Environment	✅	Target environment context for ICE scoring

Environment Types

• development: Low criticality (Context score = 4), experimental changes
• staging: Medium criticality (Context score = 7), pre-production validation
• production: High criticality (Context score = 10), business-critical operations

Returns

Response Structure

{
  result: SchemaComparisonResult,
  sourceTableCount: number,
  targetTableCount: number,
  executionTimeMs: number
}

SchemaComparisonResult

{
  summary: {
    totalDifferences: number,
    missingTables: number,
    missingColumns: number,
    typeMismatches: number,
    missingIndexes: number,
    missingForeignKeys: number,
    isIdentical: boolean
  },
  differences: SchemaDifference[],
  sourceEnvironment: Environment,
  targetEnvironment: Environment,
  comparedAt: Date
}

SchemaDifference

Each difference includes:

{
  type: DifferenceType,           // "missing_table" | "missing_column" | etc.
  severity: DifferenceSeverity,   // "critical" | "warning" | "info"
  table: string,
  column?: string,
  description: string,
  sourceValue?: any,
  targetValue?: any,
  iceScore: ICEScore,
  insightAnalysis: InsightAnalysis,
  contextAnalysis: ContextAnalysis,
  executionPlan: ExecutionPlan,
  migrationSql: string,
  sourceEnvironment: Environment,
  targetEnvironment: Environment
}

ICE Scoring Methodology

Each schema difference is evaluated across three dimensions:

Insight (I): 0-10

Semantic depth and business impact

• Table-level changes (8-10): New/missing tables affect entire features
• Primary key issues (9): Identity and uniqueness guarantees
• Foreign key constraints (7-8): Referential integrity and relationships
• Column changes (5-7): Data structure modifications
• Type mismatches (6-8): Data integrity and compatibility risks
• Index changes (4-6): Performance implications

Context (C): 0-10

Environmental criticality and risk

• Production target (10): Business-critical, zero tolerance for errors
• Staging target (7): Pre-production validation, high caution
• Development target (4): Experimental, low risk
• Migration complexity adjustments based on dependencies

Execution (E): 0-10

Action clarity and implementation ease

• SQL precision (8-10): Can generate exact CREATE/ALTER statements
• Rollback safety (0-10): Whether change is easily reversible
• Implementation complexity (inverse): Simple changes score higher
• Testing requirements: More complex changes require more testing

Combined Score

Combined = (Insight × Context × Execution) / 100

Priority Derivation

Combined Score	Priority	Typical Actions
≥ 6.0	High	Immediate attention, critical for deployment
3.0 - 5.9	Medium	Important, plan for next release
< 3.0	Low	Nice to have, technical debt

Example Scenarios

Scenario 1: Development → Production Sync

Goal: Promote tested schema changes from dev to prod

{
  "name": "compare_schemas",
  "arguments": {
    "sourceDatabaseId": "dev-database-xyz",
    "sourceEnvironment": "development",
    "targetDatabaseId": "prod-database-xyz",
    "targetEnvironment": "production"
  }
}

Sample Response:

{
  "result": {
    "summary": {
      "totalDifferences": 3,
      "missingTables": 1,
      "missingColumns": 2,
      "typeMismatches": 0,
      "missingIndexes": 0,
      "missingForeignKeys": 0,
      "isIdentical": false
    },
    "differences": [
      {
        "type": "missing_table",
        "severity": "critical",
        "table": "user_sessions",
        "description": "Table 'user_sessions' exists in development but not in production",
        "iceScore": {
          "insight": 9,
          "context": 10,
          "execution": 8,
          "combined": 7.2,
          "priority": "high"
        },
        "insightAnalysis": {
          "score": 9,
          "reasoning": "New table indicates significant feature addition affecting user authentication flow",
          "impactAreas": ["authentication", "session_management", "user_experience"]
        },
        "contextAnalysis": {
          "score": 10,
          "reasoning": "Production deployment requires zero downtime and careful validation",
          "environmentCriticality": "production",
          "migrationRisk": "medium"
        },
        "executionPlan": {
          "score": 8,
          "reasoning": "Clear CREATE TABLE statement, straightforward rollback with DROP",
          "sqlPrecision": "exact",
          "rollbackSafety": "safe",
          "estimatedComplexity": "low"
        },
        "migrationSql": "CREATE TABLE user_sessions (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, token TEXT NOT NULL, expires_at INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id));",
        "sourceEnvironment": "development",
        "targetEnvironment": "production"
      },
      {
        "type": "missing_column",
        "severity": "warning",
        "table": "users",
        "column": "last_login_at",
        "description": "Column 'last_login_at' exists in development but not in production",
        "iceScore": {
          "insight": 5,
          "context": 10,
          "execution": 9,
          "combined": 4.5,
          "priority": "medium"
        },
        "migrationSql": "ALTER TABLE users ADD COLUMN last_login_at INTEGER;",
        "sourceEnvironment": "development",
        "targetEnvironment": "production"
      }
    ]
  },
  "sourceTableCount": 12,
  "targetTableCount": 11,
  "executionTimeMs": 285
}

Scenario 2: Staging Validation

Goal: Ensure staging matches production before deployment

{
  "name": "compare_schemas",
  "arguments": {
    "sourceDatabaseId": "staging-db-abc",
    "sourceEnvironment": "staging",
    "targetDatabaseId": "prod-db-abc",
    "targetEnvironment": "production"
  }
}

Use Case: Pre-deployment checklist verification

• Staging should be identical to production + planned changes
• Any unexpected differences indicate drift or incomplete migrations
• High-priority differences block deployment

Scenario 3: Type Mismatch Detection

Goal: Find incompatible schema changes

{
  "result": {
    "differences": [
      {
        "type": "type_mismatch",
        "severity": "critical",
        "table": "orders",
        "column": "total",
        "sourceValue": "REAL",
        "targetValue": "INTEGER",
        "description": "Column 'total' has type 'REAL' in development but 'INTEGER' in production",
        "iceScore": {
          "insight": 8,
          "context": 10,
          "execution": 6,
          "combined": 4.8,
          "priority": "medium"
        },
        "migrationSql": "-- WARNING: Type change requires data migration\n-- Step 1: Add new column\nALTER TABLE orders ADD COLUMN total_new REAL;\n-- Step 2: Migrate data\nUPDATE orders SET total_new = CAST(total AS REAL);\n-- Step 3: Drop old column (requires table recreation in SQLite)\n-- Step 4: Rename new column",
        "contextAnalysis": {
          "reasoning": "Type change affects monetary calculations - high risk of data loss or precision issues"
        }
      }
    ]
  }
}

Difference Types

Type	Severity	Description
missing_table	Critical	Table in source not found in target
extra_table	Warning	Table in target not in source (potential deletion)
missing_column	Warning	Column in source table not in target
extra_column	Info	Column in target not in source
type_mismatch	Critical	Column type differs between schemas
missing_index	Warning	Index in source not in target
missing_foreign_key	Warning	Foreign key constraint missing in target
constraint_mismatch	Warning	Different constraints (nullable, default, etc.)

Migration Planning

The tool provides ordered migration SQL based on dependency analysis:

Dependency Resolution

1. Tables before columns: Create missing tables first
2. Columns before indexes: Add columns before indexing them
3. Foreign keys last: Ensure referenced tables/columns exist
4. Type changes careful: Often require data migration

Generated Migration Plans

result.getMigrationPlan(): string[]

Returns array of SQL statements in dependency order:

-- Step 1: Create missing tables
CREATE TABLE user_sessions (...);

-- Step 2: Add missing columns
ALTER TABLE users ADD COLUMN last_login_at INTEGER;

-- Step 3: Add missing indexes
CREATE INDEX idx_users_email ON users(email);

-- Step 4: Add foreign keys (if supported by SQLite version)
-- Note: SQLite requires table recreation for FK changes

Use Cases

1. Continuous Deployment Pipeline

// Pre-deployment check
const comparison = await compare_schemas({
  sourceDatabaseId: process.env.STAGING_DB_ID,
  sourceEnvironment: "staging",
  targetDatabaseId: process.env.PROD_DB_ID,
  targetEnvironment: "production"
});

if (!comparison.result.isIdentical()) {
  const criticalDiffs = comparison.result.getCriticalDifferences();
  if (criticalDiffs.length > 0) {
    throw new Error("Critical schema differences detected - blocking deployment");
  }
}

2. Schema Evolution Tracking

Track how schemas diverge over time:

// Weekly drift report
const drift = await compare_schemas({
  sourceDatabaseId: "dev-db",
  sourceEnvironment: "development",
  targetDatabaseId: "prod-db",
  targetEnvironment: "production"
});

// Generate report
console.log(`Schema Drift Report:
  Total Differences: ${drift.result.summary.totalDifferences}
  High Priority: ${drift.result.differences.filter(d => d.iceScore.priority === 'high').length}
  Tables Ahead: ${drift.result.summary.missingTables}
  Columns Ahead: ${drift.result.summary.missingColumns}
`);

3. Migration Script Generation

const comparison = await compare_schemas({
  sourceDatabaseId: "dev-db",
  sourceEnvironment: "development",
  targetDatabaseId: "prod-db",
  targetEnvironment: "production"
});

// Generate migration file
const migrationPlan = comparison.result.getMigrationPlan();
fs.writeFileSync('migration-001.sql', migrationPlan.join('\n\n'));

4. Environment Parity Validation

// Ensure staging matches production (parity check)
const comparison = await compare_schemas({
  sourceDatabaseId: "staging-db",
  sourceEnvironment: "staging",
  targetDatabaseId: "prod-db",
  targetEnvironment: "production"
});

if (comparison.result.isIdentical()) {
  console.log("✅ Staging and production schemas are in sync");
} else {
  console.warn(`⚠️  Found ${comparison.result.summary.totalDifferences} differences`);
}

Best Practices

1. Always Compare Source → Target

Convention: Compare "newer" schema against "older" schema

• Development → Production: Promote changes
• Staging → Production: Pre-deployment validation
• Production → Development: Reverse sync (rare)

2. Review ICE Scores Before Migration

High-priority differences require:

• Code review
• Testing in staging
• Rollback plan
• Monitoring strategy

3. Handle Type Mismatches Carefully

SQLite type changes often require:

1. Create new column with correct type
2. Migrate data with CAST operations
3. Drop old column (requires table recreation)
4. Rename new column

4. Test Migrations in Staging First

// Step 1: Compare dev → staging
compare_schemas({ sourceDatabaseId: "dev", targetDatabaseId: "staging", ... })

// Step 2: Apply migration to staging
// Step 3: Test thoroughly
// Step 4: Compare staging → prod
compare_schemas({ sourceDatabaseId: "staging", targetDatabaseId: "prod", ... })

// Step 5: Apply to production

5. Automate Drift Detection

Run comparisons on schedule:

• Daily: dev → staging
• Weekly: staging → prod (drift detection)
• Pre-deployment: automated gate

Validation Rules

Self-Comparison Prevention

// ❌ Error: Cannot compare database with itself
compare_schemas({
  sourceDatabaseId: "prod-db",
  sourceEnvironment: "production",
  targetDatabaseId: "prod-db",  // Same database!
  targetEnvironment: "staging"   // Different environment doesn't matter
})

Error: "Cannot compare a database with itself. Source and target must be different."

Database Access

Both databases must be:

• Accessible with configured Cloudflare credentials
• Part of the same Cloudflare account
• D1 databases (not KV, R2, etc.)

Performance

Execution Time

Typical comparison times:

Schema Size	Tables	Execution Time
Small	5-10	100-200ms
Medium	10-50	200-500ms
Large	50-100+	500ms-2s

Optimization Tips

1. Database proximity: Same region = faster queries
2. Schema complexity: More indexes/FKs = longer analysis
3. Network latency: Cloudflare API response times vary

Error Handling

Common errors and solutions:

Database Not Found

{
  "error": "Database 'xyz-123' not found",
  "suggestion": "Verify database ID in Cloudflare dashboard"
}

Permission Denied

{
  "error": "Permission denied for database access",
  "suggestion": "Check API token has D1 read permissions"
}

Network Timeout

{
  "error": "Request timeout after 30s",
  "suggestion": "Check Cloudflare API status, retry request"
}

Integration Examples

GitHub Actions Pre-Deployment Check

name: Schema Validation
on:
  pull_request:
    branches: [main]

jobs:
  validate-schema:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - name: Compare schemas
        run: |
          npx perchiqx compare-schemas \
            --source-db ${{ secrets.STAGING_DB_ID }} \
            --source-env staging \
            --target-db ${{ secrets.PROD_DB_ID }} \
            --target-env production
      - name: Check for critical differences
        run: |
          # Fail if high-priority differences found
          # Generate migration plan
          # Post comment to PR

Continuous Monitoring

import cron from 'node-cron';

// Check for drift every day at 9am
cron.schedule('0 9 * * *', async () => {
  const drift = await compare_schemas({
    sourceDatabaseId: process.env.DEV_DB,
    sourceEnvironment: "development",
    targetDatabaseId: process.env.PROD_DB,
    targetEnvironment: "production"
  });

  if (drift.result.summary.totalDifferences > 10) {
    await sendAlert({
      message: `Schema drift detected: ${drift.result.summary.totalDifferences} differences`,
      priority: 'medium'
    });
  }
});

Related Tools

• analyze_database_schema - Deep dive into single schema
• validate_database_schema - Check for anti-patterns
• suggest_database_optimizations - Performance recommendations
• ICE Methodology - Understanding the scoring system

Next Steps

1. Try it: Compare your dev and prod schemas
2. Review differences: Analyze ICE scores and priorities
3. Generate migrations: Use migration SQL as starting point
4. Automate: Integrate into CI/CD pipeline
5. Monitor drift: Set up scheduled comparisons

---

Need help? Check the ICE Methodology guide to understand how priorities are derived.