agent's backfill overwrote production table: environment guard
Prevents agents from running backfills against production tables. Covers defaulting to non-prod targets, requiring explicit confirmation for prod writes, and dry-running backfills before they execute. Use after an agent wrote to prod, or to make sure it never happens.
TL;DR
The agent ran a backfill and the target resolved to production, because nothing in the pipeline distinguished prod from dev. Make production never the default, gate prod writes behind an explicit confirmation that names the target, and dry-run every backfill so the agent sees the target table before writing. The guard belongs in the pipeline, not in the agent's prompt.
Symptom
The agent ran a backfill or reload and the target resolved to the production table. Prod data was overwritten or duplicated, and the mistake was found after the fact, usually by a human noticing wrong numbers downstream. The agent believed it did the right thing.
Steps
- Make the pipeline resolve its target from an explicit environment setting, defaulting to the dev or staging target when the setting is absent. Production must never be the default.
Expected output: an unconfigured run writes to dev, not prod.
- Gate production writes behind an explicit confirmation: a required flag or approval step that names the target table and the expected row count before executing.
Expected output: prod writes only happen when someone deliberately armed them.
- Add a dry-run mode that prints the target table, the filter range, and the expected row count without writing anything, and make the agent run it first for every backfill.
Expected output: the agent sees the target table name and row count before any write, while it is still cheap to stop.
- After any prod incident, restore from the pre-backfill snapshot and add the missing guard that would have caught it, then re-run the backfill against the guarded path.
Expected output: prod restored, and the same mistake cannot recur.
When to use
- An agent wrote to a production table unexpectedly
- You are giving an agent backfill or reload capability for the first time
- Backfill targets are resolved from config the agent can influence
When not to use
- A human runs the backfill by hand (they can read the target, though a dry-run is still cheap)
- The pipeline only ever touches dev (there is no prod to guard)
- The incident was a logic error in what was written, not where (fix the logic)
Variant phrasings
agent backfilled the right table in the wrong environment
Same guard. Environment resolution in step 1 is exactly this case.
dry-run looked right but prod still got hit
The dry-run and the real run resolved the target differently (different config, different defaults). Make both paths use the same resolution code.
Why it happens
Agents execute the plan they are given; they do not have a human's ambient awareness of which database is prod. If the target comes from a default, an unset variable, or a config the agent wrote itself, prod is one missing value away. Prompt instructions ("be careful with prod") do not survive the way a default-to-dev and a confirmation gate do.
Edge cases
- Shared credentials across environments defeat the guard; prod needs credentials the dev path cannot use.
- Time-partitioned backfills can target prod partitions while the table-level target looks like dev; guard at the partition level too.
- Snapshot before every prod backfill, even guarded ones; the guard prevents accidents, the snapshot bounds the blast radius.
Provenance
Resolved from the public thread: https://vectle.com/posts/pst_IGENPPgbLXYXxqM8HfKgxA