agent's schema migration dropped a column with data URIs dry-run diff first

TL;DR

Never let an agent apply DDL directly. Require a dry-run diff first, flag any DROP COLUMN against actual row data, back up the table, and only then apply.

Symptom

After an agent-run migration, a column is gone along with its data. Downstream queries break on the missing column, and the data is recoverable only from backups, if they exist.

Steps

1. Require the agent to emit the migration as a diff first (dry-run or plan mode), never apply directly. Most migration frameworks support this:

# example: print the SQL without running it
migrate plan --dry-run

Expected output: the full DDL the agent intends to run, reviewable before anything changes.

2. Review the diff for DROP COLUMN and other destructive statements. For each dropped column, check whether it holds data URIs

SELECT COUNT(*) FROM [table] WHERE [column] IS NOT NULL;

Expected output: a non-zero count means the drop destroys real data; that migration does not ship without explicit approval.

3. Back up the table (snapshot or CREATE TABLE backup AS SELECT) before applying anything destructive.

Expected output: a restore path exists even if the review missed something.

4. Apply, then verify column presence and row counts; keep the backup until the next successful deploy.

Expected output: the schema change lands with proof nothing unexpected was lost.

When to use

• An agent generates and applies schema migrations
• Migrations auto-apply on deploy or on agent run
• You need a reviewable diff of exactly what will change

When not to use

• Application-code deploys with no DDL (nothing destructive to review)
• Intentional destructive migrations you already approved (the diff still helps, but the gate is already passed)
• Read-only schema inspections (no apply step, no risk)

Variant phrasings

agent renamed a column and the diff shows drop plus add

Renames look like drop+add in most diff tools. Confirm intent explicitly; a true rename needs data copied, not dropped.

agent's migration dropped an index, not a column

Same gate applies: indexes are cheap to rebuild but expensive to live without. Flag drops, confirm intent.

Why it happens

Agents optimize for the requested schema shape and treat unmentioned columns as disposable. Only a diff makes the destruction visible before it happens; without it, the first sign is broken queries.

Edge cases

• Some frameworks auto-apply migrations on boot; disable that wherever an agent can trigger a boot.
• Large tables need snapshot windows; a backup that takes hours needs scheduling, not ad-hoc runs.
• Column drops inside transactions still lock the table; plan for the lock on hot tables.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_tQoNB5NxPtiOhyGBbVrRBA