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 plainUPDATEorDELETE, such asCREATE,ALTER,DROP,TRUNCATEor 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
- Always ask about db.migrate.production, two peopleA migration in CI waits for two different people. Most migrations cannot be rolled back cleanly.
- Never allow db.schema_change, for data-agentThe data agent may change rows, never tables. Schema changes go through reviewed migrations.
- 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.
- 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
fiimport pg from "pg";
import { ApprovalClient } from "@variantgate/agent";
const pool = new pg.Pool({ connectionString: process.env.WRITER_URL });
const approvals = new ApprovalClient({
agent: "data-agent",
apiUrl: process.env.VARIANTGATE_API_URL ?? "https://api.variantgate.com",
key: process.env.VARIANTGATE_KEY ?? "",
});
const COMMENTS = /--[^\n]*|\/\*[\s\S]*?\*\//g;
const ROW_WRITE = /^\s*(update|delete)\b/i;
/** One plain UPDATE or DELETE. Anything else, or anything unclear, is not. */
function isRowWrite(statement: string) {
return !statement.includes(";") && ROW_WRITE.test(statement.replace(COMMENTS, " "));
}
/**
* Runs `statement` in a transaction, and counts and hashes the rows it
* changes as they end up. Commits only when `keep` accepts the hash.
*/
async function changes(statement: string, keep: (hash: string) => boolean) {
const client = await pool.connect();
try {
await client.query("BEGIN");
const { rows } = await client.query(
`WITH changed AS (${statement}\nRETURNING *) SELECT count(*)::int AS count,
md5(coalesce(string_agg(changed::text, '|' ORDER BY changed::text), '')) AS hash
FROM changed`
);
const { count, hash } = rows[0] as { count: number; hash: string };
await client.query(keep(hash) ? "COMMIT" : "ROLLBACK");
return { count, hash };
} catch (error) {
await client.query("ROLLBACK");
throw error;
} finally {
client.release();
}
}
export async function runWrite(sql: string, reason: string) {
const statement = sql.trim().replace(/;+$/, "");
const dryRun = isRowWrite(statement) ? await changes(statement, () => false) : null;
const size = dryRun && dryRun.count <= 100 ? "small" : "large";
return approvals.guard(
{
// A rule denies db.schema_change, and this tool never runs it.
action: dryRun ? `db.write.${size}` : "db.schema_change",
summary: `${reason} (${dryRun?.count ?? "unknown"} rows in the dry run)`,
details: { sql: sql.slice(0, 1000), "rows in dry run": dryRun?.count ?? null, database: "production" },
},
async () => {
if (!dryRun) throw new Error("This tool runs one UPDATE or DELETE, nothing else.");
const done = await changes(statement, (hash) => hash === dryRun.hash);
if (done.hash !== dryRun.hash) throw new Error("The rows changed since the dry run; rolled back. Ask again.");
return `${done.count} rows`;
}
);
}import re
import psycopg
from variantgate import VariantGateError, require_approval
COMMENTS = re.compile(r"--[^\n]*|/\*.*?\*/", re.DOTALL)
ROW_WRITE = re.compile(r"^\s*(update|delete)\b", re.IGNORECASE)
# The rows a write changes, counted and hashed as they end up. A SET that
# uses now() or random() never hashes the same twice: give such values
# literally.
CHANGED = (
"WITH changed AS ({sql}\nRETURNING *) SELECT count(*), "
"md5(coalesce(string_agg(changed::text, '|' ORDER BY changed::text), '')) "
"FROM changed"
)
def is_row_write(statement: str) -> bool:
"""One plain UPDATE or DELETE. Anything else, or anything unclear, is not."""
return ";" not in statement and bool(ROW_WRITE.match(COMMENTS.sub(" ", statement)))
def run_write(sql: str, reason: str) -> str:
"""The tool for anything but SELECT. Reads use a read-only role elsewhere."""
statement = sql.strip().rstrip(";")
rows, seen = None, None
if is_row_write(statement):
with psycopg.connect(WRITER_DSN) as conn, conn.transaction(force_rollback=True):
rows, seen = conn.execute(CHANGED.format(sql=statement)).fetchone() # dry run
action = "db.write.small" if rows <= 100 else "db.write.large"
else:
action = "db.schema_change" # a rule denies it, and this tool never runs it
try:
with require_approval(
action,
f"{reason} ({rows if rows is not None else 'unknown'} rows in the dry run)",
agent="data-agent",
details={"sql": sql[:1000], "rows in dry run": rows, "database": "production"},
) as approval:
if seen is None:
raise RuntimeError("This tool runs one UPDATE or DELETE, nothing else.")
with psycopg.connect(WRITER_DSN) as conn, conn.transaction():
done, now = conn.execute(CHANGED.format(sql=statement)).fetchone()
if now != seen: # raising rolls the transaction back
raise RuntimeError("The rows changed since the dry run. Ask again.")
approval.note = f"{done} rows"
return f"Done, {done} rows."
except VariantGateError as refused:
return f"Not run. {refused} Do not run it another way."curl -X POST https://api.variantgate.com/v1/requests \
-H "authorization: Bearer $VARIANTGATE_KEY" \
-H 'content-type: application/json' \
-d '{
"action": "db.write.large",
"agent": "data-agent",
"details": {
"database": "production",
"rows in dry run": 1204,
"sql": "DELETE FROM sessions WHERE last_seen < now() - interval '\''90 days'\''"
},
"summary": "Delete sessions not seen for 90 days (1,204 rows in the dry run)"
}'{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "request_approval",
"arguments": {
"action": "db.write.large",
"agent": "data-agent",
"details": {
"database": "production",
"rows in dry run": 1204,
"sql": "DELETE FROM sessions WHERE last_seen < now() - interval '90 days'"
},
"summary": "Delete sessions not seen for 90 days (1,204 rows in the dry run)"
}
}
}- 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 runin 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,
psqlincluded, and shows the full command.
What the person approving sees
data-agent
db.write.large · asked just now
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'
- 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
- Give the agent a read-only role first. Everything else goes through the write tool, and every write asks.
- 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.
- After a few weeks, allow small writes for the data agent’s key, if the record shows you approved them unchanged.
- 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.