Files
QuoteForge/bible-local/06-backup.md

6.3 KiB

06 - Backup

Scope

QuoteForge creates rotating local ZIP backups of:

  • a consistent SQLite snapshot saved as qfs.db;
  • config.yaml when present.

The backup intentionally does not include local_encryption.key.

Location and naming

Default root:

  • <db dir>/backups

Subdirectories:

  • daily/
  • weekly/
  • monthly/
  • yearly/

Archive name:

  • qfs-backp-YYYY-MM-DD.zip

Retention

Period Keep
Daily 7
Weekly 4
Monthly 12
Yearly 10

Behavior

  • on startup, QuoteForge creates a backup if the current period has none yet;
  • a daily scheduler creates the next backup at backup.time;
  • duplicate snapshots inside the same period are prevented by a period marker file;
  • old archives are pruned automatically.

Safety rules

  • backup root must be outside the git worktree;
  • backup creation is blocked if the resolved backup root sits inside the repository;
  • SQLite snapshot must be created from a consistent database copy, not by copying live WAL files directly;
  • restore to another machine requires re-entering DB credentials unless the encryption key is migrated separately.

Restore

  1. stop QuoteForge;
  2. unpack the chosen archive outside the repository;
  3. replace qfs.db;
  4. replace config.yaml if needed;
  5. restart the app;
  6. re-enter MariaDB credentials if the original encryption key is unavailable.

Pre-reset safety backup

Before clearing any table, -reset-localdb creates a fresh ZIP under <backup root>/pre-reset/qfs-pre-reset-v1-<UTC timestamp>-<unique>.zip, outside normal retention and regardless of QFS_BACKUP_DISABLE. The archive contains a consistent SQLite snapshot and the selected config when present. Every ZIP entry is read back for CRC validation; the extracted database must pass SQLite integrity_check. A backup or verification failure prevents the reset. The backup directory must also resolve outside git after following symlinks.

Reset refuses queued changes and any project/configuration not marked synced, including NULL/legacy states. A missing sync-status column also blocks reset. Only -reset-localdb -force-reset-localdb bypasses this data check. Deletions run in one transaction and preserve connection settings. Missing legacy tables are skipped. Use the restore procedure above for the pre-reset archive; retain it until the reset has been verified. The environment reset switch was removed to prevent repeated destructive startup.

Server migration snapshots

qfs -migrate writes mandatory archives under <backup root>/server-migrations/qfs-mariadb-v1-<label>-<UTC timestamp>-<unique>.zip. QFS_BACKUP_DIR overrides the root; QFS_BACKUP_DISABLE never disables safety snapshots. Paths are checked outside git, including symlink targets, before creation and restore. Archives have private filesystem permissions and contain server table schemas and data, which may be sensitive; never put them in git. Connection passwords and the local encryption key are not copied into metadata.

One full snapshot precedes the session. Before each file, a separate snapshot contains a schema definition and row stream for every table: this includes all affected tables even when a legacy Tables-affected header is incomplete. This intentionally favors recovery over storage/time efficiency for the manual DBA tool. Retain safety snapshots outside normal rotation until the migration has been verified; remove verified-session artifacts as operational cleanup.

The versioned ZIP manifest records table schemas, writable column order, row counts and order-independent row digests. Row streams preserve NULL, empty strings, binary values and fractional timestamps. Restore temporarily uses NO_AUTO_VALUE_ON_ZERO to preserve explicit zero IDs and legacy zero dates; row digests detect coercion and the original session mode is restored. Generated columns are rebuilt from schema, not inserted. Trigger bodies, definers, firing order and creation SQL/charset context are preserved. Triggers are recreated after row loading so restore does not duplicate audit data or alter restored values. CRC/format/digests are checked, then each archive is restored into a unique temporary schema on the same server and compared for schema and data equality. The temporary schema is dropped afterward. A failed or interrupted check must be investigated before retry; abandoned qfs_verify_ schemas are DBA cleanup artifacts, never application data.

Stop every other writer for the entire migration/recovery window, including clients that could reconnect. The runner detects other current schema connections and uses an advisory lock to exclude a second runner; the lock does not stop unrelated SQL clients. An active client appearing during failure recovery causes recovery refusal rather than overwriting its work. InnoDB base tables and triggers are supported; views, routines, events and cross-schema FKs require explicit recovery support before migration. Triggers whose recorded database collation differs from the current database collation also cause refusal until that legacy state has supported recovery. Trigger routine calls and qualified references to other schemas are refused; source SQL must have no effects outside the captured schema. Unsupported schemas stop before changes. Runtime database-user grants stay as listed in 03-database.md; the tool requires an existing DBA account.

Failure restores step snapshots in reverse order; the full-session archive is used if step recovery is insufficient. Schema, writable data and migration history must match the session start before recovery is reported successful. The persistent .pending marker is keyed by configured endpoint and schema, so server restarts do not hide an interrupted session. Backups survive connection loss; a failed recovery retains its marker and reports the full snapshot path.

After an interruption, keep clients stopped and use the snapshot path reported by the runner:

go run ./cmd/qfs -migrate-restore /outside/repository/server-migrations/qfs-mariadb-v1-session-<timestamp>-<unique>.zip
make migrate-status
make migrate

Restore uses the currently saved DBA connection, requires the same database name, checks the archive by restoring it into a temporary schema first, restores the original schema, verifies equality and clears the matching pending marker. It does not require mysql/mysqldump or any operator-installed database utility.