archeious/quartermaster
1
0
Fork 0
Code Issues 4 Pull requests 1 Projects Releases Packages Wiki Activity Actions

DB backup script and alembic auto-backup hook #6

Merged
claude-code merged 4 commits from feat/5-db-backups into main 2026-04-17 11:57:19 -06:00
Conversation 0 Commits 4 Files changed 6 +260
claude-code commented 2026-04-17 11:52:14 -06:00
Collaborator
Copy link

Closes #5 (will be manually closed on merge).

Summary

Guardrail against data loss during schema work or destructive operations.

  • scripts/backup-db.sh [reason] writes a timestamped copy of quartermaster.db to backups/quartermaster-YYYYMMDD-HHMMSS-{slug}.db
  • Uses SQLite's online backup API (sqlite3.Connection.backup), not a plain file copy, so it is safe while the app is writing
  • Resolves the DB path from QUARTERMASTER_DB_URL (sqlite:/// only) or ./quartermaster.db; override the destination directory with QUARTERMASTER_BACKUP_DIR
  • Missing DB file exits 0 with a "nothing to back up" message so the script is safe to call from hooks
  • alembic/env.py runs the script automatically with reason alembic before every migration / current / revision command; a failing backup does not stop the migration
  • CLAUDE.md codifies the durable rule: run the script before any schema change or destructive DB operation
  • README documents location, override env, and restore steps

Why

Earlier today I wiped the local quartermaster.db multiple times during development (rm before alembic autogen, rm before and after live smoke tests). The file is gitignored, so there was no recovery path. This PR makes losing the data require deliberate action in the presence of a working hook.

Test plan

  • uv run pytest passes (18/18, +5 new)
  • ./scripts/backup-db.sh smoke-test creates backups/quartermaster-*-smoke-test.db containing the same rows as the source
  • ./scripts/backup-db.sh on a missing DB exits 0 with the expected message
  • Non-sqlite QUARTERMASTER_DB_URL exits 1 with a clear error
  • uv run alembic current fires the hook: backup-db.sh: /tmp/qm-dev.db -> /tmp/qm-dev-backups/quartermaster-...-alembic.db
  • Alembic hook runs on a missing DB without failing the migration

Out of scope

  • Periodic / cron-driven backups
  • Off-machine replication
  • Backup pruning (retention is forever)
Closes #5 (will be manually closed on merge). ## Summary Guardrail against data loss during schema work or destructive operations. * `scripts/backup-db.sh [reason]` writes a timestamped copy of `quartermaster.db` to `backups/quartermaster-YYYYMMDD-HHMMSS-{slug}.db` * Uses SQLite's online backup API (`sqlite3.Connection.backup`), not a plain file copy, so it is safe while the app is writing * Resolves the DB path from `QUARTERMASTER_DB_URL` (sqlite:/// only) or `./quartermaster.db`; override the destination directory with `QUARTERMASTER_BACKUP_DIR` * Missing DB file exits 0 with a "nothing to back up" message so the script is safe to call from hooks * `alembic/env.py` runs the script automatically with reason `alembic` before every migration / current / revision command; a failing backup does not stop the migration * `CLAUDE.md` codifies the durable rule: run the script before any schema change or destructive DB operation * README documents location, override env, and restore steps ## Why Earlier today I wiped the local `quartermaster.db` multiple times during development (rm before alembic autogen, rm before and after live smoke tests). The file is gitignored, so there was no recovery path. This PR makes losing the data require deliberate action in the presence of a working hook. ## Test plan * [x] `uv run pytest` passes (18/18, +5 new) * [x] `./scripts/backup-db.sh smoke-test` creates `backups/quartermaster-*-smoke-test.db` containing the same rows as the source * [x] `./scripts/backup-db.sh` on a missing DB exits 0 with the expected message * [x] Non-sqlite `QUARTERMASTER_DB_URL` exits 1 with a clear error * [x] `uv run alembic current` fires the hook: `backup-db.sh: /tmp/qm-dev.db -> /tmp/qm-dev-backups/quartermaster-...-alembic.db` * [x] Alembic hook runs on a missing DB without failing the migration ## Out of scope * Periodic / cron-driven backups * Off-machine replication * Backup pruning (retention is forever)
claude-code added 4 commits 2026-04-17 11:52:15 -06:00 
Uses sqlite3.Connection.backup for an online, WAL-safe copy. Resolves
the DB path from QUARTERMASTER_DB_URL or ./quartermaster.db, places
the snapshot in <db-dir>/backups (override with QUARTERMASTER_BACKUP_DIR),
timestamps the filename, and tags it with an optional reason slug.
Absent DB file is a soft exit so the script is safe to call from hooks.

Refs #5

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Runs at env module load so the backup fires ahead of offline and
online migration paths, as well as alembic current / revision. A
failing backup does not stop the migration: this is defense in depth,
not a hard prerequisite, and the common failure case is "DB does not
exist yet" on a fresh checkout.

Refs #5

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
CLAUDE.md states the durable rule: run scripts/backup-db.sh before
any schema change, data migration, or destructive DB operation. The
rule deliberately excludes routine app writes. README summarises
backup location, override env var, and restore procedure.

Refs #5

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Asserts soft exit when the source DB is missing, a successful backup
round-trips sqlite rows, a non-file sqlite URL is rejected, the reason
slug defaults to "manual", and a messy reason is sanitised to a safe
filename fragment.

Refs #5

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
claude-code merged commit 6986081ee4 into main 2026-04-17 11:57:19 -06:00
Sign in to join this conversation.
Reviewers
No reviewers
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: archeious/quartermaster#6
No description provided.
Delete branch "feat/5-db-backups"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?