This chapter guards one thing: that a data operation never surprises you with its result. Hence two iron rules.

Dry-run — A “dry” run — the script shows what it WOULD do but changes nothing. You see the effects before executing; a data change happens only after deliberate confirmation. by default. Every script that changes data first only shows the plan — “I’ll change 4 rows, skip 0” — and actually writes only after your deliberate --execute. It’s a safety catch: you see the effect before it happens.

A copy before every change. Before you touch the database layout or live data, you take a snapshot of it. Going back = restoring that copy (database changes go “forward only”, so the copy is the only way back).

The rest are practical traps. The trickiest: the same /tmp path means different things in different tools — Git Bash, Python — A popular, readable programming language — The Craft’s default for scripts, data and the back end. A proven default: lots of ready tools and libraries, easy to drive with AI. and PowerShell expand it to three different folders. A script can “do nothing” because it reads an empty file in one place while your database sits in another. The cure: give full, unambiguous paths (Z:/data/app.db, not /tmp/app.db).

Example of Idempotency — A property of an operation you can run many times with the same result — no duplication. Key for scripts and events: a re-run doesn’t break data. Like an “ON” switch.: a good data loader uses INSERT OR IGNORE, so run twice it doesn’t duplicate records — just as safe the first time and the fifth.

04 — Scripts and Databases

Commandments IV and V: Dry-run — A “dry” run — the script shows what it WOULD do but changes nothing. You see the effects before executing; a data change happens only after deliberate confirmation. is the default; the backup is your Rollback — Reverting a change to the previous, working state — “Ctrl+Z” for a deployment. When a new version breaks production, a rollback restores the previous one in seconds instead of fixing in a panic. mechanism.

Scripts — the rules

1. Dry-run is the default

Every script that changes data runs as --dry-run by default and prints the plan: what, how many rows, where. A deliberate --execute turns on the mutation. This has saved the reference project more than once — you see “4 OK, 0 skipped” before anything is written.

python -m scripts.x.do_thing            # dry-run: shows the plan
python -m scripts.x.do_thing --execute  # writes

2. Idempotency

A script run twice should yield the same state, not a doubled one. Write Migration (database) — A controlled change to the database layout — adding a column, a table or moving data — step by step. Like a renovation to plan: rebuilding data in a set order so nothing “collapses”. and content loaders so that INSERT OR IGNORE / UPSERT make a re-run safe (in the reference project the content loaders from an external database are explicitly Idempotency — A property of an operation you can run many times with the same result — no duplication. Key for scripts and events: a re-run doesn’t break data. Like an “ON” switch., and that’s a deploy requirement).

3. Organize by pipeline stage

Group scripts by phase (setup/, normalize/, enrich/, validate/, images/, dedup/, orchestration/). Leave compatibility shims when you move files. Compose them into composable pipelines (post-scrape, full) — but keep catalog-mutating operations OUT of the automatic pipeline (run dedup/merge by hand: dry-run → review → --execute).

4. Log what you did NOT do

If a script truncates scope (top-N, sampling, skipped rows) — print it. A silent truncation reads as “I covered everything” when you didn’t.

5. Record the environment in the docs

The interpreter, the variables (PYTHONIOENCODING=utf-8 on Windows), where to get the venv. The agent doesn’t guess the path to Python — A popular, readable programming language — The Craft’s default for scripts, data and the back end. A proven default: lots of ready tools and libraries, easy to drive with AI. — it reads it from CLAUDE.md.

6. Absolute paths when a value crosses a tool boundary

A file path is not portable between tools. Git Bash resolves /tmp to the MSYS temp dir; Python on Windows resolves the same /tmp to <cwd-drive>:\tmp; PowerShell to yet another place. Hand a relative — or rooted-but-driveless — path from one tool to another and each reads/writes a different file: a migration script silently churning an empty DB while your 200 MB copy sits untouched in the shell’s temp dir. Whenever a path (or any value) passes shell → interpreter → app, make it an absolute, fully-qualified path (Z:/work/x.db, not /tmp/x.db), or resolve it once and pass the resolved form. When a “did nothing” result surprises you, print os.path.abspath(p) / pwd -W and compare — it’s usually two tools disagreeing about where a relative path points.

Databases and migrations

Migrations are forward-only

No down-migrations. One numbered file = one step forward. Rolling back a schema = restoring the backup, not a reverse migration. Therefore:

Back up BEFORE every schema/data change on prod

sqlite3 data/app.db ".backup backups/pre-deploy-$(date +%F_%H%M%S).db"

This isn’t caution — it’s the undo mechanism. Keep a retention window (e.g. 14 days) and name the snapshots legibly (pre-deploy-*, pre-swap-*, replaced-prod-*).

Additive > destructive

ADD COLUMN is backward-compatible (old code still works). Never DROP/RENAME a column in the same migration where running old code still uses it — split the destructive change into a later deploy, once nothing reads the column anymore.

Integrity after the fact

After every data operation: PRAGMA integrity_check + PRAGMA foreign_key_check + count the rows. Distinguish pre-existing noise (orphaned staging rows present both before and after) from a regression (new violations that weren’t there before). In the reference project the merged DB had fewer violations than the local one — a signal that the migration cleaned up rather than broke things.

”Active row” model instead of overwriting

A pattern from prices: instead of an in-place UPDATE, expire the old row (expired_at) and insert a new one; a partial unique Index (database) — A lookup in the database that makes searching instant instead of scanning everything in order. The first move for slow queries — like an index at the back of a book instead of reading 400 pages. enforces “one active per key”. History is preserved and the audit trail comes for free. Consider this model anywhere history has value.

Make a transfer snapshot with `.backup` / `VACUUM INTO`, not `cp` on a live file

A live SQLite — The simplest database — the whole thing lives in one file, with no separate server. A great default to start: zero configuration, easy backup (you copy the file). As you grow, you migrate up. with WAL — Write-Ahead Log: a database mode where changes go to a journal first, then to the data. Gives safety (recovery after a crash) and better read/write concurrency. copied via cp can be inconsistent. .backup (online-safe) or VACUUM INTO give a consistent, defragmented copy. Never SCP a live .db.

Rehearse a bulk/irreversible data mutation on a fresh prod COPY — not just a dry-run

A dry-run prints the plan; it can’t prove the outcome or surface conflicts that live only in real data — e.g. a merge tool that mis-reads a stale expired row as a conflict and deletes the wrong active one, or a snapshot whose IDs drifted since the plan was written. Before a bulk merge/dedup/mass-update on prod: .backup prod → run the operation on the copy → check the result (row counts, integrity_check, and spot-check the tricky cases). A same-ID prod snapshot lets you rehearse the exact run before touching the live data — much cheaper than a rollback. This is the data sibling of “rehearse the deploy on preprod” (→ 05).

Anti-patterns

🚫 A mutating script without --dry-run.
🚫 A deploy with a migration but no backup “because it’s additive”.
🚫 DROPping a column used by running code.
🚫 cp app.db with the server running → an inconsistent snapshot.
🚫 Wiring a catalog-mutating operation into the automatic pipeline.

In practice

Relational database → forward-only migrations + a backup before each one.
A pipeline processing sensitive data (e.g. anonymization / distilling submissions) = a separate, idempotent script with dry-run; the contract: raw data in, PII-free data out, with a test that re-identification is impossible. Keep it OUT of the user-facing path. → 09, 11