Migration Workflow

AutoDB takes a safety-first approach to database migrations. Every migration goes through a structured lifecycle: analyze, review, approve, and execute. The system never applies changes to your production database without explicit approval via a cryptographically signed token.

Before execution, AutoDB parses your SQL into individual statements, scores each for risk, maps the blast radius across foreign key relationships, and runs the full migration in an ephemeral sandbox. If the sandbox catches a syntax error or constraint violation, you find out before production is touched.

This guide walks through the complete workflow from submitting SQL to polling for execution results.

The Workflow

1. Analyze your migration

   Submit your SQL to the analyze endpoint. AutoDB parses each statement, scores risk, detects affected tables (including cascades through foreign keys), runs sandbox validation, generates a rollback plan, and returns a signed approval token.

   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'\'';"}'

   The response includes a full risk report:

   {
     "risk_score": 35,
     "risk_category": "medium",
     "affected_tables": [
       { "table": "orders", "impact": "direct", "cascade_path": null },
       { "table": "order_items", "impact": "cascade", "cascade_path": ["orders", "order_items"] }
       // ...
     ],
     "sandbox_result": {
       "passed": true,
       "migration_applied": true
       // ...
     },
     "rollback_plan": {
       "has_irreversible": false,
       "combined_script": "BEGIN;\nALTER TABLE orders DROP COLUMN priority;\nCOMMIT;"
       // ...
     },
     "approval_token": "eyJzcWwiOi4uLn0.a1b2c3d4"
     // ...
   }

2. Review the risk report

   Every migration receives a risk score from 0 to 100. The score maps to one of four categories:

   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

   Check the blast radius in affected_tables. Each entry is marked as "direct" (the statement explicitly modifies it) or "cascade" (impacted through foreign key relationships). The cascade_path traces the FK chain.

   Check sandbox validation in sandbox_result. If passed is true, the migration applied successfully in an ephemeral PostgreSQL clone. If false, fix the SQL before proceeding.

   Caution

   When has_irreversible is true in the rollback plan, at least one statement destroys data that cannot be automatically recovered (e.g., DROP TABLE, TRUNCATE). Ensure you have backups before proceeding.

3. Execute the migration

   Submit the approval token along with the original SQL and connection ID:

   curl -X POST https://api.autodb.app/api/v1/migrations/execute \
     -H "Content-Type: application/json" \
     -H "X-API-Key: YOUR_API_KEY" \
     -d '{
       "approval_token": "eyJzcWwiOi4uLn0.a1b2c3d4",
       "sql": "ALTER TABLE orders ADD COLUMN priority VARCHAR(20) DEFAULT '\''normal'\'';"  ,
       "connection_id": "550e8400-e29b-41d4-a716-446655440000"
     }'

   The sql and connection_id must match the original analyze request exactly — byte-for-byte. The token is HMAC-signed against these values. It expires after 1 hour and is single-use.

4. Poll for status

   The execute endpoint returns a request_id immediately. Use it to poll for progress:

   curl https://api.autodb.app/api/v1/migrations/requests/{request_id} \
     -H "X-API-Key: YOUR_API_KEY"

   Migrations progress through: pending -> executing -> completed (or failed). A completed response looks like:

   {
     "request_id": "880e8400-e29b-41d4-a716-446655440003",
     "status": "completed",
     "result": {
       "columns_added": [
         { "table": "orders", "column": "priority", "type": "varchar(20)" }
       ],
       "execution_time_ms": 85
     }
     // ...
   }

Tips

Tip

If you get a 400 error on execute, the most common cause is whitespace differences between the analyzed SQL and the submitted SQL. Copy the exact sql value from your analyze request.

Tip

Use the rollback_plan.combined_script from the analysis response to quickly reverse a migration if needed. It wraps all rollback steps in a single transaction.

Next Steps

Migration Analysis Reference Risk levels, blast radius, and sandbox validation details

Migration Execution Reference Approval token flow, error handling, and status polling