Migration Analysis

Overview

AutoDB analyzes SQL migrations before they touch production. Every migration is parsed into individual statements, scored for risk, checked for blast radius across foreign key relationships, and tested in an ephemeral sandbox. The result is a structured report that tells you exactly what will happen before you approve execution.

Risk Levels

Each statement in a migration receives a risk score from 0 to 100. The aggregate migration score uses the maximum individual statement score for conservative reporting.

Category	Score Range	Description
low	0—24	Short locks, no data loss risk, limited blast radius
medium	25—49	Moderate lock impact or FK cascade depth
high	50—74	Destructive operations or wide FK blast radius
critical	75—100	Destructive changes with extensive cascade impact

Risk factors include lock severity, foreign key cascades, destructive potential, and index complexity.

Blast Radius

The affected_tables field maps every table impacted by the migration, including tables affected indirectly through foreign key relationships.

Each entry includes:

• table — the table name
• impact — either "direct" (the statement explicitly modifies it) or "cascade" (impacted through foreign keys)
• cascade_path — for cascade impacts, the ordered chain of tables from the directly affected table to this one

For example, altering the orders table cascades to order_items via a foreign key relationship. The analysis would show:

[
  { "table": "orders", "impact": "direct", "cascade_path": null },
  { "table": "order_items", "impact": "cascade", "cascade_path": ["orders", "order_items"] }
]

Sandbox Validation

AutoDB clones your database schema into an ephemeral PostgreSQL instance, applies the migration, and reports whether it succeeded. This catches syntax errors, constraint violations, and type mismatches before anything runs on production.

Key fields in sandbox_result:

• passed — whether the migration applied successfully in the sandbox
• migration_applied — whether the migration SQL was executed (false if setup failed)
• tables_created — number of tables created during schema cloning
• duration_ms — total sandbox execution time in milliseconds

Rollback Plan

AutoDB generates a reverse migration for every statement it can automatically undo. The rollback_plan includes:

• steps — one rollback step per original statement, each with the reverse SQL
• combined_script — a transaction-wrapped SQL script combining all rollback steps, ready to execute
• has_irreversible — true if any statement cannot be automatically reversed

Caution

Some operations are irreversible. DROP TABLE and TRUNCATE destroy data that cannot be recovered through a generated rollback script. When has_irreversible is true, review the rollback plan carefully and ensure you have backups before proceeding.

Example: Analyzing a Migration

curl -X POST https://api.autodb.app/api/v1/connections/550e8400-e29b-41d4-a716-446655440000/migrations/analyze \
  -H "Content-Type: application/json" \
  -H "X-API-Key: YOUR_API_KEY" \
  -d '{"sql": "ALTER TABLE orders ADD COLUMN priority VARCHAR(20) DEFAULT '\''normal'\'';\nCREATE INDEX CONCURRENTLY idx_orders_priority ON orders(priority);"}'

Response:

{
  "connection_id": "550e8400-e29b-41d4-a716-446655440000",
  "total_statements": 1,
  "risk_score": 35,
  "risk_category": "medium",
  "statements": [
    {
      "index": 0,
      "sql": "ALTER TABLE orders ADD COLUMN priority VARCHAR(20) DEFAULT 'normal'",
      "statement_type": "ALTER TABLE",
      "table": "orders",
      "lock_type": "ACCESS EXCLUSIVE",
      "lock_duration_ms": 150,
      "risk_score": 35,
      "risk_category": "medium",
      "affected_tables": [
        { "table": "orders", "impact": "direct", "cascade_path": null }
      ],
      "rollback": {
        "statement_index": 0,
        "sql": "ALTER TABLE orders DROP COLUMN priority",
        "reversible": true,
        "warning": null
      },
      "warnings": [
        "ACCESS EXCLUSIVE lock will block all concurrent reads and writes"
      ]
    }
  ],
  "affected_tables": [
    { "table": "orders", "impact": "direct", "cascade_path": null },
    { "table": "order_items", "impact": "cascade", "cascade_path": ["orders", "order_items"] }
  ],
  "rollback_plan": {
    "steps": [
      {
        "statement_index": 0,
        "sql": "ALTER TABLE orders DROP COLUMN priority",
        "reversible": true,
        "warning": null
      }
    ],
    "combined_script": "BEGIN;\nALTER TABLE orders DROP COLUMN priority;\nCOMMIT;",
    "has_irreversible": false
  },
  "warnings": [],
  "business_narrative": {
    "summary": "Adding a priority column to the orders table enables order prioritization workflows. Low risk as it adds a nullable column with a default value.",
    "affected_processes": ["Order fulfillment", "Shipping prioritization"],
    "citations": [
      {
        "document_filename": "data-dictionary.pdf",
        "excerpt": "Orders table tracks customer purchase records",
        "relevance": "Confirms orders table is central to fulfillment"
      }
    ],
    "schema_only": false
  },
  "impact_chains": [],
  "sandbox_result": {
    "passed": true,
    "migration_applied": true,
    "migration_error": null,
    "tables_created": 8,
    "rows_inserted": 120,
    "validation_results": [],
    "duration_ms": 4500
  },
  "approval_token": "eyJzcWwiOiAiQUxURVIgVEFCTEUgb3JkZXJzIC4uLiIsICJjb25uX2lkIjogIjU1MGU4NDAwLi4uIiwgIm5vbmNlIjogImFiYzEyMyIsICJleHAiOiAxNzA1MzEyMDAwfQ.a1b2c3d4e5f6",
  "non_transactional_statements": []
}

The approval_token in the response is what you submit to the execution endpoint to proceed with applying the migration.

Full API Schema See complete request and response fields