AI Agents for Database Migration Tickets: Safe Schema Changes With Review Gates

Last month I watched a Developer agent push a migration that dropped a column — in staging. No warning. No confirmation. Just gone.

The column was supposed to be renamed, not dropped. The agent read the ticket, interpreted "remove old_user_id and use user_id instead" as a deletion, and executed. Staging. Not production. We got lucky.

I'll be honest: I was the one who wrote that ticket poorly. "Remove old_user_id" is ambiguous. The agent did exactly what I asked — I just asked wrong.

That incident changed how I think about agents and database work. Schema migrations aren't feature code. They're irreversible in ways that a bad API endpoint isn't. You can roll back a buggy function. You can't un-drop a column with 2 million rows of data. (Ask me how I know. Actually, don't.)

Here's the thing though: agents are actually good at migration work. SQL generation is usually solid. Migration file structure follows patterns well. The problem isn't capability — it's guardrails. You need a workflow where the agent does the work and a human confirms the blast radius before anything touches a real database.

This tutorial walks through that workflow. By the end, you'll have a system where agents generate migrations, dry-runs validate them, and humans approve before execution. (If you're new to assigning tickets to agents, start with how to write tickets AI agents can complete first. For context on why multi-agent systems outperform single-agent tools, see our breakdown of why single-agent coding tools plateau.)

What We're Building

A migration workflow where:

1. Agent picks up a schema change ticket
2. Agent generates the migration files (up and down)
3. Dry-run executes against a shadow database
4. Static analysis flags destructive operations
5. PR opens with migration diff and dry-run output
6. Human reviews and approves
7. Migration runs in target environment

No agent-initiated production migrations without approval. Ever.

Prerequisites

• A project using a migration tool (Prisma, Drizzle, Flyway, Liquibase — any works)
• CI pipeline with database access (we'll use GitHub Actions, but adapt to yours)
• A shadow database for dry-run testing (clone of your prod schema)
• Basic familiarity with your migration tool's CLI

Step 1: Write the Migration Ticket

Agent tickets for migrations need more structure than normal feature tickets. The blast radius demands it.

Here's the template that works:

## Summary
Add `subscription_tier` column to users table for pricing feature

## Schema Change
- Table: `users`
- Operation: ADD COLUMN
- Column: `subscription_tier` (varchar(50), nullable, default 'free')
- Index: Yes, for filtering queries

## Acceptance Criteria
- [ ] Migration file generated with timestamp prefix
- [ ] Up migration adds column with correct type and default
- [ ] Down migration removes column cleanly
- [ ] Dry-run passes against shadow database
- [ ] No destructive operations flagged

## Out of Scope
- Backfilling existing users (separate ticket)
- Application code changes (separate ticket)
- Index on other columns

## Relevant Files
- `prisma/migrations/` — migration output directory
- `prisma/schema.prisma` — source of truth for schema

Notice what's different from a normal ticket: the Schema Change section. You're telling the agent exactly what operation to perform, what table, what column, what type. No ambiguity.

An agent reading "add a column for subscription data" might add five columns. An agent reading "ADD COLUMN subscription_tier varchar(50) nullable default 'free'" does exactly that. I learned this the hard way with the old_user_id incident. Vague tickets produce creative interpretations.

The Acceptance Criteria section includes "No destructive operations flagged" — that's your static analysis gate. More on that in Step 4.

Step 2: Configure the Agent's Migration Permissions

Before an agent generates migrations, you need guardrails on what it can do. This isn't about capability — agents are capable of running DROP DATABASE if you let them. It's about policy.

In your agent configuration (DevOS uses a .devos/agents.yaml, your platform will vary). If you're building a CI/CD pipeline with AI agents, this configuration integrates directly:

developer_agent:
  tools:
    database:
      allowed:
        - schema:read
        - migration:generate
        - migration:dry-run
      blocked:
        - migration:execute
        - schema:destructive
  review_gates:
    - trigger: migration:generate
      requires: human_approval
      environments: [staging, production]

The key line: migration:execute is blocked. The agent can generate migrations all day long. It cannot run them. That's what the CI pipeline does — after human approval.

Some teams add schema:destructive to flag any operation that drops, truncates, or deletes. The agent can still generate those migrations, but they're automatically flagged for review.

Step 3: Generate the Migration

Once the ticket is assigned, the agent reads the schema definition, generates the migration, and opens a PR. For Prisma, that looks like:

npx prisma migrate dev --name add_subscription_tier --create-only

The --create-only flag is critical. It generates the migration file without executing it. The agent pushes the generated file to a branch, runs the dry-run, and opens the PR.

A good migration PR from an agent includes:

1. The migration SQL (or TypeScript if you're using Drizzle/Kysely)
2. The rollback migration
3. Dry-run output showing what would happen
4. Static analysis results

That third item — dry-run output — is what makes this workflow safe. You see exactly what SQL will run before anything executes.

Honestly, most agents nail the migration generation. It's the "should I actually run this" judgment that they lack. So we don't give them the option. (This is related to why AI coding agents hallucinate infrastructure — they're good at generating, bad at judging.)

Step 4: Dry-Run Against Shadow Database

A dry-run does two things: validates the SQL syntax and catches schema conflicts your linter missed.

Here's a GitHub Actions workflow that runs after the agent opens a PR:

name: Migration Dry-Run

on:
  pull_request:
    paths:
      - 'prisma/migrations/**'

jobs:
  dry-run:
    runs-on: ubuntu-latest
    services:
      shadow-db:
        image: postgres:15
        env:
          POSTGRES_DB: shadow
          POSTGRES_USER: shadow
          POSTGRES_PASSWORD: shadow
        ports:
          - 5432:5432
    steps:
      - uses: actions/checkout@v4

      - name: Restore shadow schema
        run: |
          psql $SHADOW_URL -f ./scripts/shadow-schema.sql
        env:
          SHADOW_URL: postgres://shadow:shadow@localhost:5432/shadow

      - name: Run migration dry-run
        run: |
          npx prisma migrate deploy --preview-feature
        env:
          DATABASE_URL: postgres://shadow:shadow@localhost:5432/shadow

      - name: Static analysis
        run: |
          ./scripts/analyze-migration.sh prisma/migrations/**/migration.sql

The shadow database starts fresh each run, gets your current schema loaded, then the migration applies. If it fails, the PR fails. If destructive operations are detected, the PR gets flagged.

The analyze-migration.sh script scans for dangerous patterns:

#!/bin/bash
MIGRATION_FILE=$1

# Check for destructive operations
if grep -qiE "(DROP TABLE|DROP COLUMN|TRUNCATE|DELETE FROM)" "$MIGRATION_FILE"; then
  echo "::warning::Destructive operation detected — requires manual review"
  echo "DESTRUCTIVE=true" >> $GITHUB_ENV
fi

# Check for missing transactions
if ! grep -qi "BEGIN" "$MIGRATION_FILE"; then
  echo "::warning::Migration not wrapped in transaction"
fi

This is basic static analysis. Production systems might use fancier tools — there's a whole ecosystem of SQL linters (sqlfluff, squawk, pgFormatter). The point is: the agent's work gets validated before anyone reviews it.

Is this overkill? Maybe. But "overkill" starts looking reasonable after you've explained a dropped table to your CTO.

Step 5: The Review Gate

With dry-run passing and static analysis clean, the PR is ready for human review. This is where you actually look at what the agent generated.

What to check:

The migration matches the ticket. Did the agent add the column described? Correct type? Correct default? It sounds obvious but agents occasionally misread tickets — especially when multiple operations are described. (Our guide on scoping work for agents covers this.)

The rollback is clean. If the up migration adds a column, the down migration drops it. If the up migration creates an index, the down migration removes it. No orphaned changes.

The dry-run output makes sense. Sometimes the SQL is valid but the execution plan is insane — like a full table scan on a 10M row table because of a missing index. The dry-run won't catch this. You will.

The blast radius is acceptable. Adding a nullable column? Low risk. Changing a column type with implicit cast? Higher risk. Dropping a column that the application still references? Block the PR until that's resolved.

My hot take: the human review step should take longer than the agent's work. If you're rubber-stamping agent PRs in 30 seconds, you're not reviewing — you're hoping.

Once you approve, the migration moves to the execution phase.

Step 6: Execute With Approval

After human approval, the migration runs. In most setups, this means merging the PR triggers a deployment that includes migration execution.

Here's the deployment workflow:

name: Deploy with Migrations

on:
  push:
    branches: [main]
    paths:
      - 'prisma/migrations/**'

jobs:
  deploy:
    runs-on: ubuntu-latest
    environment: production  # Requires manual approval
    steps:
      - uses: actions/checkout@v4

      - name: Run migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.PRODUCTION_DATABASE_URL }}

      - name: Deploy application
        run: ./scripts/deploy.sh

The environment: production line is the key. GitHub requires manual approval before jobs in protected environments run. Even after merging, someone has to click "Approve" before the migration touches production.

This is the final gate. The agent generated it. The dry-run validated it. A human reviewed it. Another human (or the same one) approves execution. Four checkpoints before any production data is modified.

Paranoid? Absolutely. But database migrations are one of the few places where paranoia is a feature, not a bug.

Common Errors and Fixes

Error: Migration fails with "column already exists"

The agent generated a migration for a column that was already added in a previous migration. This happens when the agent's schema cache is stale.

Fix: Make sure the agent reads the current schema before generating. In Prisma, that means running prisma db pull or ensuring the schema.prisma is synced with the database.

Error: Dry-run passes but production fails with constraint violation

The shadow database doesn't have the same data as production. A constraint that passes on empty tables fails when there's actual data.

Fix: Seed your shadow database with representative data, or use a production clone (sanitized) for dry-runs. The second option catches more bugs but is slower and raises privacy concerns.

Error: Static analysis flags a false positive

The script detected "DROP" in a comment or string literal, not an actual DROP statement.

Fix: Improve the static analysis regex to ignore comments and string literals. Or switch to an AST-based SQL analyzer that understands syntax.

Error: Rollback migration causes data loss

The up migration adds a column and backfills data. The down migration drops the column — and the data. If you roll back and forward again, the backfill runs twice or the data is gone.

Fix: Separate data migrations from schema migrations. The schema change (add column) has a clean rollback (drop column). The data backfill is a separate, non-reversible operation with its own rollback strategy.

This one bit us hard. We had an agent generate a migration that added a column AND backfilled it from another table — all in one file. Rolling back meant losing the backfilled data. Lesson learned: one operation per migration.

Next Steps

Once you've got the basic workflow running, there's more to build:

Shadow database automation. Instead of manually maintaining a shadow schema file, clone production nightly. Tools like Railway make database cloning straightforward — DevOS uses Railway as its deployment target, so the integration is already there.

Canary migrations. Run the migration on a small percentage of sharded databases before full rollout. If it fails on 1% of shards, you've caught the issue before it hits 100%.

Automatic rollback triggers. If error rates spike within 5 minutes of a migration, automatically roll back. This requires monitoring integration — JustAnalytics can feed alerts into your rollback automation. (See how to correlate errors with analytics funnel drop-offs for the setup.)

Multi-environment promotion. Migrations run in dev, then staging, then production — with approval gates at each step. The agent generates once; humans approve at each environment boundary.

For teams tracking paid acquisition alongside product development, ClickzProtect catches fraudulent clicks that waste budget while your team focuses on shipping.

FAQ

Should AI agents ever run database migrations in production without approval?

No. Production migrations should always require human approval, regardless of how good your agent is. The blast radius is too high. Configure your pipeline so production migrations trigger a review request and block until a human approves. Staging and dev environments can be more automated, but production is sacred.

How do I prevent an agent from running destructive migrations like DROP TABLE?

Two layers: static analysis that scans the generated SQL for destructive keywords (DROP, TRUNCATE, DELETE without WHERE), and a policy that flags those migrations for mandatory human review. The agent can still generate the migration — but the pipeline blocks it from executing until someone signs off.

What's the difference between a dry-run and a shadow database migration?

A dry-run shows what SQL would execute without touching the database. A shadow database actually runs the migration against a clone of your production schema, catching issues that static analysis misses — like data type incompatibilities or constraint violations. Dry-runs are fast and cheap. Shadow databases are slower but catch more bugs.

Can an AI agent write rollback migrations automatically?

Yes, and you should require it. When the agent generates an up migration, it should also generate the corresponding down migration in the same PR. Your review gate checks that both exist and that the down migration actually reverses the up. If the agent can't generate a clean rollback, that's a signal the migration needs human review.

---

Join the DevOS Waitlist

AI agents that work as employees inside your sprints — not single-task copilots. Four built-in agents (Planner, Developer, QA, DevOps) pick up tickets, open PRs, and hand off to each other. Multi-model routing across Anthropic, Google, DeepSeek, and OpenAI picks the cheapest capable model per task. Three-tier memory keeps context across weeks. Pre-launch.

Join the waitlist → · How agents-as-employees works