DB backup script invoked before every schema or destructive DB operation #5

New issue

Closed

opened 2026-04-17 11:48:47 -06:00 by claude-code · 0 comments

claude-code commented

2026-04-17 11:48:47 -06:00

Collaborator

Goal

Guardrail against losing the SQLite data file during development, tooling work, or manual recovery. A backup script must run before any operation that interacts with the database at the schema or destructive-data level.

Behavior

scripts/backup-db.sh [reason] copies quartermaster.db to backups/quartermaster-YYYYMMDD-HHMMSS-{slug}.db using sqlite3 .backup (WAL-safe).
If QUARTERMASTER_DB_URL is set and starts with sqlite:///, that path is backed up instead.
If the DB file does not exist, the script exits 0 with a "nothing to back up" message.
backups/ is gitignored and kept forever (small dataset).
Alembic's env.py invokes the script before every offline or online migration run, tagged with reason alembic.
A project-level CLAUDE.md documents the rule so future sessions see it on load.

Scope

Covers:

Schema changes via Alembic (automatic).
Any manual destructive command (rm, truncate, DROP, etc) I or the user runs: the rule is "run the backup script first".

Does not cover:

Ordinary application writes through the running server. Those are not considered "interactions" for this guardrail; the app's own writes are routine and do not warrant a backup per transaction.

Acceptance criteria

./scripts/backup-db.sh exists, is executable, and produces a timestamped file under backups/ using sqlite3 .backup
Script accepts an optional reason argument that becomes part of the filename slug
Script honours QUARTERMASTER_DB_URL when set
Alembic env.py invokes the script before migrations and does not fail the migration if the backup script itself errors out (the backup is defense in depth, not a hard prerequisite)
backups/ is in .gitignore
Project CLAUDE.md states the rule clearly
README documents where backups land and how to restore

Out of scope

Periodic / cron-driven backups
Off-machine backup replication
Backup pruning (retention is forever for now)

## Goal Guardrail against losing the SQLite data file during development, tooling work, or manual recovery. A backup script must run before any operation that interacts with the database at the schema or destructive-data level. ## Behavior * `scripts/backup-db.sh [reason]` copies `quartermaster.db` to `backups/quartermaster-YYYYMMDD-HHMMSS-{slug}.db` using `sqlite3 .backup` (WAL-safe). * If `QUARTERMASTER_DB_URL` is set and starts with `sqlite:///`, that path is backed up instead. * If the DB file does not exist, the script exits 0 with a "nothing to back up" message. * `backups/` is gitignored and kept forever (small dataset). * Alembic's `env.py` invokes the script before every offline or online migration run, tagged with reason `alembic`. * A project-level `CLAUDE.md` documents the rule so future sessions see it on load. ## Scope Covers: * Schema changes via Alembic (automatic). * Any manual destructive command (`rm`, `truncate`, `DROP`, etc) I or the user runs: the rule is "run the backup script first". Does not cover: * Ordinary application writes through the running server. Those are not considered "interactions" for this guardrail; the app's own writes are routine and do not warrant a backup per transaction. ## Acceptance criteria * [ ] `./scripts/backup-db.sh` exists, is executable, and produces a timestamped file under `backups/` using `sqlite3 .backup` * [ ] Script accepts an optional reason argument that becomes part of the filename slug * [ ] Script honours `QUARTERMASTER_DB_URL` when set * [ ] Alembic `env.py` invokes the script before migrations and does not fail the migration if the backup script itself errors out (the backup is defense in depth, not a hard prerequisite) * [ ] `backups/` is in `.gitignore` * [ ] Project `CLAUDE.md` states the rule clearly * [ ] README documents where backups land and how to restore ## Out of scope * Periodic / cron-driven backups * Off-machine backup replication * Backup pruning (retention is forever for now)