How to Safely Automate Database Migrations with Antigravity Agents

Defining Migration Policies in AGENTS.md

Start by creating an AGENTS.md file at the root of your project with database-specific rules:

# Database Migration Policy
 
## Rules
- Always create reversible migrations (include down migration)
- Never drop columns directly — rename to _deprecated first
- Add NOT NULL columns with DEFAULT value to avoid breaking existing rows
- Create indexes concurrently for tables with > 10,000 rows
- Migration file names must follow: YYYYMMDD_HHMMSS_description.sql
 
## Naming Conventions
- Tables: snake_case, plural (e.g., user_profiles)
- Columns: snake_case (e.g., created_at)
- Indexes: idx_{table}_{columns} (e.g., idx_users_email)
- Foreign keys: fk_{table}_{ref_table} (e.g., fk_orders_users)
 
## Safety Checks
- Verify no data loss before applying
- Check for long-running locks on production tables
- Estimate migration duration for large tables

With this file in place, Antigravity Agent automatically references these policies when generating migration code. The NOT NULL incident I opened with would have been prevented by that single Add NOT NULL columns with DEFAULT value line. I think of the policy as a letter I write to my future agent, forwarding the mistakes of my past self.

A Risk Map by Change Type

To decide what you can safely delegate, first separate changes by how dangerous they are. I've updated this table after every failure. The rule: any change rated "High" can be generated by the agent, but a human always signs off.

Paste this table next to your AGENTS.md and the agent starts selecting safe procedures whenever it generates a "High" change. The important thing is to not let it judge risk by the surface of the request. Ask it to "drop a column" and its natural instinct is to write a plain DROP COLUMN. That's exactly why the knowledge that drops are the highest risk has to live in writing.

Generating Migrations with Prisma

In Prisma-based projects, you can instruct the agent using natural language to make schema changes.

Example Agent Instruction

In Antigravity's Agent mode, provide a request like this:

Add a profile image URL column to the users table.
Set the default to null for existing users, and plan
a two-phase migration to add a NOT NULL constraint later.

What the Agent Generates

The agent updates both the Prisma schema and creates the migration file:

// prisma/schema.prisma — auto-updated by agent
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  avatarUrl String?  // Phase 1: added as nullable
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

-- prisma/migrations/20260328_153000_add_avatar_url/migration.sql
-- Phase 1: Add nullable column (zero downtime)
ALTER TABLE "User" ADD COLUMN "avatar_url" TEXT;
 
-- Expected result: avatar_url column added to User table
-- Existing rows will have avatar_url = NULL

Following the AGENTS.md policy, the agent chooses the safe approach of adding a nullable column first, rather than directly adding a NOT NULL constraint.

Automating Migrations with Drizzle ORM

The same workflow applies seamlessly to Drizzle ORM projects:

// src/db/schema.ts — auto-updated by agent
import { pgTable, serial, text, timestamp, index } from "drizzle-orm/pg-core";
 
export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    email: text("email").notNull().unique(),
    name: text("name").notNull(),
    avatarUrl: text("avatar_url"), // Added by agent
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => ({
    // Agent also suggests appropriate indexes
    emailIdx: index("idx_users_email").on(table.email),
  })
);

# Commands auto-executed by the agent
npx drizzle-kit generate  # Generate migration file
npx drizzle-kit push      # Apply to dev DB
 
# Expected output:
# Generated migration: 0001_add_avatar_url.sql
# Applied 1 migration(s)

With Drizzle, the agent handles everything from schema definition changes to SQL generation and application in a single, seamless flow.

Splitting Into Expand and Contract — Zero-Downtime Phasing

The biggest lesson from my incident was to never finish a dangerous change in one shot. To alter a schema without stopping a live app, split the change into two phases: Expand and Contract. First add in a backward-compatible way (Expand); only after the code has fully moved to the new shape do you remove the old shape (Contract). A deploy sits between them.

Even a change as small as renaming avatar to avatar_url will throw exceptions the moment you rename in one step, because running code still references avatar. Split in two, it looks like this:

When delegating this phasing to the agent, write Renames and NOT NULL must be split into Expand/Contract phases across separate deploys into AGENTS.md. That one line keeps the agent from giving in to the temptation to cram everything into a single PR. Since adopting it, I haven't caused a single rename-related incident.

Automated Migration Review with AI

You can also set up the agent to review its own generated migrations. Add a review checklist to your AGENTS.md:

## Migration Review Checklist
- [ ] Reversible: DOWN migration exists and is tested
- [ ] No data loss: SELECT COUNT before/after matches
- [ ] Performance: No full table locks on tables > 100K rows
- [ ] Indexes: New columns used in WHERE/JOIN have indexes
- [ ] Defaults: NOT NULL columns have sensible defaults
- [ ] Naming: Follows project naming conventions

When asking the agent to review, provide a clear instruction:

Review the generated migration files against the
Migration Review Checklist in AGENTS.md.
If there are issues, provide specific fixes.

The agent will systematically verify each item and report problems with concrete fix suggestions. For example, if it detects a missing index:

-- Agent improvement suggestion
-- WARNING: If avatar_url will be used in WHERE clauses,
-- adding an index is recommended
 
CREATE INDEX CONCURRENTLY idx_users_avatar_url
  ON "users" ("avatar_url")
  WHERE "avatar_url" IS NOT NULL;
 
-- Using CONCURRENTLY avoids locking the table

One caveat here. Agent self-review is great for doubling up on oversights, but because the author is grading its own work, it tends to miss fundamental design mistakes. So I assign the reviewer role to a separate session — or a different model — from the one that wrote the migration. Simply separating author from grader visibly raised the quality of the findings.

Verifying the Diff Before Applying, With a Shadow DB

No matter how careful the review, some things only surface once you actually apply. So I first apply against a shadow DB that mirrors the production schema, and check the schema diff mechanically before touching production. You can delegate this verification to the Antigravity agent as a single task.

#!/usr/bin/env bash
# scripts/verify-migration.sh — a verification task to hand the agent
set -euo pipefail
 
SHADOW_URL="postgresql://postgres:test@localhost:5432/shadow_db"
: "${PROD_URL:?set PROD_URL before running}"
 
# 1. Wipe the shadow DB first, then clone the production schema (structure only).
#    Leftovers from a previous run make a "no drift" result meaningless.
psql "$SHADOW_URL" -v ON_ERROR_STOP=1 \
  -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public;'
pg_dump --schema-only "$PROD_URL" | psql "$SHADOW_URL" -v ON_ERROR_STOP=1
 
# 2. Apply the new migration to the shadow DB
DATABASE_URL="$SHADOW_URL" npx prisma migrate deploy
 
# 3. Read drift from the EXIT CODE, not from stdout.
#    --exit-code … no diff = 0 / error = 1 / diff found = 2
rc=0
DATABASE_URL="$SHADOW_URL" npx prisma migrate diff \
  --exit-code \
  --from-schema prisma/schema.prisma \
  --to-config-datasource || rc=$?
 
case "$rc" in
  0)
    echo "✅ Shadow DB verification passed"
    ;;
  2)
    echo "❌ Schema drift detected. Aborting apply."
    DATABASE_URL="$SHADOW_URL" npx prisma migrate diff \
      --from-schema prisma/schema.prisma \
      --to-config-datasource --script
    exit 1
    ;;
  *)
    echo "❌ The diff command itself failed (exit=$rc)"
    exit 1
    ;;
esac

This script surfaces any mismatch (drift) between the migration files and the Prisma schema before you touch production. Reading the verdict from the exit code rather than from stdout is the part that pays off later, as the next section explains. What helped me most was catching the quiet failure where the agent hand-writes some SQL but forgets to update the schema definition. Discovering that after applying versus getting stopped at the shadow stage are two very different heart rates.

For stricter checks, seed the shadow DB with a fixed number of dummy production-like rows and confirm that SELECT COUNT(*) and checksums on key columns match before and after the migration. Catch data loss before it happens rather than after. That one step is the foundation for running automation with peace of mind.

Read Drift From the Exit Code, Not the Output

The first version of this script never once went green. Even with nothing to diff, every run stopped at "schema drift detected."

The problem was --script. When there is no difference at all, prisma migrate diff --script does not return an empty string — it returns a single line, -- This is an empty migration. So if [ -n "$DRIFT" ] was always true, and what I had built was a gate that fails no matter what.

The awkward part is how safe that failure looks. It's stopping, so surely it's protecting you. In practice, a CI job that goes red every single time gets a continue-on-error: true bolted on before long, or someone quietly comments the whole step out.

A gate that always fails is a gate that eventually gets removed. When I add a check now, I spend as much effort confirming it passes when it should as I do confirming it fails when it should.

The supported way to read the result is the --exit-code flag. By default the command is binary — success 0, error 1 — but with that flag it becomes three-valued.

Separating 1 from 2 is the whole point. Under the old "non-empty output means failure" style, a run that merely failed to connect and a run where the schema had genuinely drifted arrive as the same red. Give two different causes the same color and people stop reading the color.

There's a second trap: the flag names themselves changed between generations. Prisma v7 cleaned up the direct-URL options and moved datasource configuration into prisma.config.ts. Hand an agent an older article or an older prompt and it will confidently return a script that cannot run.

Two commands tell you which generation you're on: npx prisma -v and npx prisma migrate diff --help. What worked for me was pasting both outputs into the session before asking an agent for any migration helper script. It closes off the older syntax sitting in its training data before the agent can reach for it.

One last step on the day you add the gate. Put a throwaway column into prisma/schema.prisma, confirm that migrate diff really does return 2, then take it back out. It takes about thirty seconds — and skipping those thirty seconds is how I ended up running a verification for weeks that had never detected anything at all.

Automatic Rollback Script Generation

For production safety, have the agent generate rollback scripts alongside every migration:

Generate a rollback script for this migration.
Include safety checks to verify no data loss will occur.

Here's an example of what the agent produces:

-- rollback/20260328_153000_remove_avatar_url.sql
 
-- Safety check: Verify if column contains data
DO $$
DECLARE
  row_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO row_count
  FROM "users"
  WHERE "avatar_url" IS NOT NULL;
 
  IF row_count > 0 THEN
    RAISE NOTICE '⚠ % rows have avatar_url data. Backing up...', row_count;
 
    -- Back up data to safety table
    CREATE TABLE IF NOT EXISTS "_backup_users_avatar_url" AS
    SELECT id, avatar_url FROM "users" WHERE "avatar_url" IS NOT NULL;
  END IF;
END $$;
 
-- Execute rollback
ALTER TABLE "users" DROP COLUMN IF EXISTS "avatar_url";
 
-- Expected result: avatar_url column removed
-- If data existed, it's preserved in _backup_users_avatar_url

The agent ensures data safety while automating the entire rollback preparation. But in my experience, a rollback is insurance, not a strategy. If you honor Expand/Contract, the situations that actually need a rollback almost never arrive. Keep the rollback scripts on hand, yet aim for a design where you never use them. That two-layer stance is what lets me sleep during a release week.

Measured Numbers on Large Tables — Locks and Duration

Why a safe procedure is safe becomes clear when you look at the numbers. On my M2 Mac (PostgreSQL 16, local), I measured the same change against users tables of different sizes. It's just one sample, but the trend is instructive.

Read it this way: CONCURRENTLY is slower than a normal index build, yet it never stops writes while it runs. Duration and lock time are two different things, and in production it's the latter that bites. Even 2.6 seconds of blocked writes on a million-row table can avalanche the requests piling up behind it. My opening incident was exactly this full-table ACCESS EXCLUSIVE lock. From PostgreSQL 11 on, NOT NULL with a DEFAULT avoids the full-row rewrite, so I always make the agent pick the newer approach. Take these numbers once in your own environment and you'll stop taking the agent's suggestions on faith.

CI/CD Pipeline Integration

To take migration automation to the next level, integrate it into your CI/CD pipeline. This ensures that every migration file generated by the agent is automatically validated before merging:

# .github/workflows/migration-check.yml
name: Migration Safety Check
 
on:
  pull_request:
    paths:
      - "prisma/migrations/**"
      - "drizzle/**"
      - "src/db/schema.*"
 
jobs:
  migration-check:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: test
          POSTGRES_DB: test_db
        ports:
          - 5432:5432
 
    steps:
      - uses: actions/checkout@v4
 
      - name: Apply migrations to test DB
        env:
          DATABASE_URL: postgresql://postgres:test@localhost:5432/test_db
        run: |
          npx prisma migrate deploy
          echo "✅ Migrations applied successfully"
 
      - name: Verify rollback scripts exist
        run: |
          # Check that each migration has a corresponding rollback
          for dir in prisma/migrations/*/; do
            migration_name=$(basename "$dir")
            if [ ! -f "rollback/${migration_name}_rollback.sql" ]; then
              echo "❌ Missing rollback for: $migration_name"
              exit 1
            fi
          done
          echo "✅ All rollback scripts present"

This CI configuration automatically validates test DB application and rollback script presence for every pull request that modifies migration files. Combined with agent-generated code, you get a double safety net. Add the shadow-DB verification script from the earlier section as one more step and you have a triple net that includes drift detection.

For more advanced CI/CD pipeline patterns, check out Antigravity × GitHub Actions Advanced CI/CD Pipeline, which covers matrix builds, security scanning, and automated releases.

What to Do Next

If you start with just one thing today, paste the "risk map by change type" table into your AGENTS.md. The agent's speed only becomes an asset once it's paired with a template for safety. In order: write the policy down first, make Expand/Contract your default practice next, then slot shadow-DB verification into CI. Get this far and even a late-night migration becomes something you can run calmly.

I only arrived at this shape after failures like the one I opened with. I'm still learning, but if it spares even one person the same rut, writing this down was worth it. Thank you for reading.

If you'd like to go deeper on security and validation around database operations, Antigravity × AI-Driven Security Audit Automation provides a comprehensive guide to vulnerability detection and automated remediation pipelines.