safety: automatic backup of ~/.vireo/vireo.db before schema migrations

[jss367]

Context

PR #636 performed an irreversible forward-only migration on the user's primary database: dropped detections.workspace_id, deduped predictions, table-rewrote both, backfilled prediction_review. The CHANGELOG describes what changed but does not tell users to back up before upgrading. There is no automatic backup. If anything goes wrong on a particular user's DB (corruption, edge-case data, partial run on power loss), there is no rollback.

The storage-philosophy roadmap (#645) plans seven more migrations of similar shape. Each one is a fresh chance for an unrecoverable failure on a real user's DB.

What to build

On first boot of a new schema version (after the migration runner from #652 lands), before any DDL runs:

1. Detect that schema_version will be advanced this boot.
2. Snapshot the current DB to ~/.vireo/backups/vireo.db.pre-v<old>-to-v<new>-<YYYYMMDD-HHMMSS>.bak (or similar). Use SQLite's online backup API (sqlite3.connect(...).backup(...)) so it works on a live WAL-mode DB without races.
3. Log the backup path at INFO so users can find it in ~/.vireo/vireo.log.
4. Surface it in the UI on first launch ("Database upgraded. Pre-upgrade backup saved at ").
5. Cap retention: keep the most recent N (e.g. 3) pre-migration backups and prune older ones, or cap by total size, to avoid silently filling the user's disk.

Behavior on backup failure

If the backup itself fails (disk full, permissions), abort the migration and surface a clear error rather than proceeding without a snapshot. Better to refuse to upgrade than to upgrade unsafely.

Out of scope

• Continuous / scheduled backups (this is not the right place for that).
• Backups before non-schema operations (scan, classify, etc.).
• Cross-machine sync of backups.

Why now

The same reasoning as #652: do this once, before #646 (Phase 1) and the six phases that follow each get their own migration. Cheap insurance, and the moment a Phase 6 cleanup goes wrong on a 100k-photo DB it's the difference between "ugh, restore" and "lost everything."

References

• ~/.vireo/vireo.db is the path used by the running app (set in vireo/app.py)
• vireo/db.py __init__ — where the new logic would slot in (after connect, before any DDL)
• infra: numbered migrations + schema_version table #652 — migration runner; pairs naturally
• Python's sqlite3.Connection.backup() — the right primitive

[jss367]

Rescoping for solo-user reality

The runtime backup hook described above is more infrastructure than this app needs. Vireo has one user and one DB; the actual ask is: "don't fat-finger irreversible edits."

Simpler form:

• Convention: any one-shot script that modifies ~/.vireo/vireo.db schema first runs cp ~/.vireo/vireo.db ~/.vireo/vireo.db.$(date +%Y%m%d-%H%M%S).bak.
• Document this in CLAUDE.md as the standard recipe for schema-touching scripts.
• Keep the most recent N backups by hand; no automatic retention logic needed.

No app-level hook, no DDL detection, no first-launch surfacing. Just a documented bash one-liner at the top of any migration script.

Original body kept below for reference; treat it as the maximalist version that we're not building.