Database migrations and destructive SQL

Migrations, DROP, TRUNCATE and a DELETE without a WHERE wait for a person who can read the statement, from CI, a script or a coding agent.

Updated

Agents touch databases in three ways: a pipeline applies migrations, a coding agent runs psql on a laptop, and a data agent writes SQL to answer a request. Each can drop a column that a running service still reads, or delete every row a WHERE clause was meant to narrow.

The risk

  • A DELETE without its WHERE. Or with a WHERE that matches far more than intended. The statement runs exactly as written.
  • Schema changes out of order. Dropping a column before the code that reads it is gone takes the service down.
  • No way back. A dropped table returns only from a backup, with every write since lost.

Measure before you ask

A person cannot judge DELETE FROM sessions WHERE … without knowing how many rows it hits. So the tool runs the statement once in a transaction it rolls back, counts the rows, and names the action by what it found:

  • db.write.small: at most 100 rows in the dry run.
  • db.write.large: more than that.
  • db.schema_change: anything that is not one plain UPDATE or DELETE, such as CREATE, ALTER, DROP, TRUNCATE or several statements at once. The tool sorts by allowing what it recognises, never by spotting what it fears, and it never runs these itself.
  • db.migrate.production: migrations applied by the pipeline.

The count comes from your code and the database, not from the model, so a rule can trust it. Reads do not ask: they go through a separate, read-only database role, so a mistake in sorting statements cannot turn into a write.

The rules

  1. Always ask about db.migrate.production, two peopleA migration in CI waits for two different people. Most migrations cannot be rolled back cleanly.
  2. Never allow db.schema_change, for data-agentThe data agent may change rows, never tables. Schema changes go through reviewed migrations.
  3. Always ask about db.write.large, for AnaA write that touched more than 100 rows in its dry run goes to the person who owns the data.
  4. Always allow db.write.small, for data-agent (added later)Small corrections, at most 100 rows in the dry run, once you trust the agent with them.
The same rules as API bodies
# POST /v1/workspaces/{workspace_id}/rules, one call per rule
{
  "action": "db.migrate.production",
  "approvals": 2,
  "effect": "ask"
}

{
  "action": "db.schema_change",
  "agent_key_id": "DATA_AGENT_KEY_ID",
  "effect": "deny"
}

{
  "action": "db.write.large",
  "deciders": [
    "USER_ID_OF_ANA"
  ],
  "effect": "ask"
}

{
  "action": "db.write.small",
  "agent_key_id": "DATA_AGENT_KEY_ID",
  "effect": "allow"
}

Owners write rules under Rules in the app, or post these bodies while signed in. Replace the ids in capitals with your own.

The agent’s side

# Apply new migrations to production after two people approve.
NEW=$(git diff --name-only --diff-filter=A "$DEPLOYED_SHA" HEAD -- prisma/migrations \
  | xargs -r -n1 dirname | sort -u | xargs -r -n1 basename | paste -sd, -)
if [ -z "$NEW" ]; then
  echo "No new migrations: nothing to approve."
else
  variantgate run --agent ci --action db.migrate.production \
    --summary "Migrate production: $NEW" \
    --details "$(jq -n --arg new "$NEW" --arg commit "$GITHUB_SHA" '{migrations: $new, commit: $commit}')" \
    -- npx prisma migrate deploy
fi
  • On approval the statement runs again in a transaction that commits only if it changes the same rows, ending up exactly as in the dry run: the tool compares a count and a hash of the changed rows, not just the count. If anything changed in between, it rolls back and the outcome is recorded as failed.
  • variantgate run in CI applies the migrations only after the yes and passes on the migrate command’s exit code. If nobody answers within 15 minutes, the step fails and can be re-run. A deploy with no new migrations asks nobody.
  • For a coding agent, the Claude Code or Codex hook asks before every shell command, psql included, and shows the full command.

What the person approving sees

data-agent

db.write.large · asked just now

14 min left

Delete sessions not seen for 90 days (1,204 rows in the dry run)

For ana@example.com

database

production

rows in dry run

1204

sql

DELETE FROM sessions WHERE last_seen < now() - interval '90 days'

How this request looks in the inbox, with example values.
  • The statement itself and the dry-run count. 1,204 rows for a cleanup of old sessions is plausible; 1,204,000 is the reason to deny.
  • Deny with a note such as “Keep 180 days, not 90”. The agent rewrites the statement, measures again and asks again.
  • A migration shows the new migration names and the commit, and needs a second person before it runs.

Do not use “Edit and approve” to change SQL. The agent acts on the statement it measured; the code above does nothing on a changed approval. Deny with a note instead.

Rolling it out

  1. Give the agent a read-only role first. Everything else goes through the write tool, and every write asks.
  2. Write the cautious rules on day one: two people for migrations, never for schema changes from the data agent, and large writes to the data owner.
  3. After a few weeks, allow small writes for the data agent’s key, if the record shows you approved them unchanged.
  4. Keep backups you have restored once. An approval makes a mistake less likely; a tested restore makes it survivable.

The pipeline side is the same as for deploys; see production deploys and GitHub Actions.