Changing data safely

Most database accidents are not exotic. They are a statement that was broader than intended, or a migration replayed against a database that already had the objects. Both are cheap to prevent and expensive to undo, so this page collects the habits that make them survivable.

The one rule worth internalising

Overwritten values are gone. A database stores the current state, not the history of how it got there. If you overwrite a column across a thousand rows, the previous values are not sitting somewhere waiting to be read back — the only way to recover them is from something recorded before the change: a restore point, a backup, or your own audit table.

Recovering from a related table afterwards is not the same thing. It gets you the rows back, but usually at a coarser grain than the original — per-record rather than per-field — because the related table only knows what it knows. That is a real loss of fidelity, and it is avoidable.

Before a risky change: take a restore point

A restore point is a named marker you can recover to. It costs nothing to take and turns "we overwrote production" into a recoverable event.

capydb restore-points create --label "before provenance backfill"

The loop is: create a restore point → apply the change → verify → on success delete the restore point, on failure restore into a preview and repair from there. Agent safety loop covers this in depth, including how to drive it from an agent.

Scope your writes

An UPDATE or DELETE without a WHERE clause changes every row in the table. That is occasionally what you want and usually not.

-- rewrites every row's source attribution
UPDATE contacts SET source = 'import';

-- rewrites only what you meant
UPDATE contacts SET source = 'import' WHERE source IS NULL;

Two habits that catch this before it lands:

1. Run it as a SELECT first. Swap UPDATE ... SET for SELECT * with the same WHERE and look at the row count. If it says 1,240 and you expected 12, you just saved yourself an afternoon.

2. Wrap it in a transaction and check the count before committing:

   BEGIN;
   UPDATE contacts SET source = 'import' WHERE source IS NULL;
   -- inspect the reported row count; ROLLBACK if it is not what you expected
   COMMIT;

The dashboard SQL runner flags an unqualified UPDATE/DELETE distinctly from an ordinary write, because the two carry very different risk.

Rehearse on a preview

A preview database is a real copy of your data with its own connection string and a TTL. It is the right place to rehearse anything you have not run before — a backfill, a destructive migration, a bulk rewrite — because getting it wrong there costs nothing.

capydb preview create --name backfill-rehearsal --mode clone
# run the change against the preview's connection string, check the result
capydb preview delete <id>

Keep migration history honest

push and migrate are different workflows and mixing them is the most common way to end up stuck.

• push diffs your schema against the database and applies the difference. It records nothing in the migrations table.
• migrate replays migration files in order and records each one.

So a database built with push has the tables but an empty history. The first migrate against it starts from migration #1, tries to create objects that already exist, and fails with relation "..." already exists. Nothing is broken — the history just never knew about the schema — and the fix is to baseline, not to drop anything. The Drizzle guide walks through it.

Pick one workflow per environment: push for local and previews where speed matters and the data is disposable, migrate for anything whose history you need to reproduce.

capydb doctor checks both halves of this for you: it warns when a project defines scripts for both tools, and — for a linked project — compares the migrations in your repo against what the live database has actually recorded, which is the only way to see the divergence before it fails.

Bulk writes

Large backfills hit limits that ordinary queries never touch: the plan's statement_timeout cancels a single enormous statement, and idle_in_transaction_session_timeout cuts a session that holds one transaction open while the client works. Use the direct URL and commit in batches — the recipe is in Connection pooling.

If something has already gone wrong

1. Stop writing to the affected tables. Every subsequent write makes reconstruction harder.
2. Check what you have to recover from: capydb restore-points list, capydb backups list, and the PITR window on your plan.
3. Restore into a preview, not over production. Recover the correct values there, confirm them, then apply the repair to production as a scoped write.

Restoring into a preview is deliberately the default: it lets you compare old and new side by side before changing anything, instead of trading one irreversible action for another.