Schedule the lead-database backup and get a copy off the operator's machine #211

New Issue

Open

opened 2026-08-18 01:26:21 -05:00 by null · 1 comment

null commented

2026-08-18 01:26:21 -05:00

Owner

/app/db/queuenorth.db is the only live copy of every lead and support request the site has ever taken. It lives in the Docker named volume qn-website-dev_queuenorth-db on nebula, and it took writes on 2026-08-18.

What is true now. As of 2026-08-18 this is no longer "no backups at all":

scripts/backup.sh works. Its ENGINE block was rewritten for SQLite — better-sqlite3's online .backup() inside the running container, then PRAGMA integrity_check and a table count before anything is renamed into place.
scripts/restore-check.sh works, and has been run against production: the dump was replayed from SQL into a scratch database — 2 tables, 3 rows, under one second. docs/OPERATIONS.md carries the date.

What is still missing, and this is the whole issue:

Nothing runs on a schedule. One backup taken by hand is not a backup regime. The newest dump is as old as the last time somebody remembered.
The dump is on one machine, beside nothing. It is in $HOME/backups/queue-north-website on the operator's workstation. backup.sh says this itself on every run: a backup that only exists beside the database it came from does not survive the disk, the host or the account.

What to do, in order. The order matters — a schedule writing to a destination that does not survive is the more comfortable half of the job and the less useful one.

Pick a destination that is not the operator's workstation and not nebula.
Extend backup.sh's tail, or wrap it, to copy there. Keep the verify-before-rename contract intact.
Schedule it — cron, a systemd timer, or an OpenClaw cron job, which can also deliver the failure.
Set RESTORE_MIN_ROWS above 0 once the lead count is meaningful. It is 0 today, which means the check cannot yet catch a snapshot of an emptied volume — the one failure no structural check can see.
Run restore-check.sh on a schedule too, and update Last verified restore in docs/OPERATIONS.md when you do.

The trap. A backup job that runs nightly for a year and writes a file every night for a year, where the file is a snapshot of the wrong container or an empty volume, is indistinguishable from a working one by looking at the directory. The size column is plausible and the timestamps march forward. That is why step 4 is on this list and not optional.

Why P2 and not higher. The database holds three leads. The exposure is real and the loss today would be small; it grows every week this stays open.

Verify: a scheduled job produces a dated dump in a location that is neither nebula nor the operator's workstation, bash scripts/restore-check.sh passes against the newest one, and docs/OPERATIONS.md's Last verified restore row names a date from that schedule rather than a manual run.

`/app/db/queuenorth.db` is the only live copy of every lead and support request the site has ever taken. It lives in the Docker named volume `qn-website-dev_queuenorth-db` on nebula, and it took writes on 2026-08-18. **What is true now.** As of 2026-08-18 this is no longer "no backups at all": - `scripts/backup.sh` works. Its ENGINE block was rewritten for SQLite — better-sqlite3's online `.backup()` inside the running container, then `PRAGMA integrity_check` and a table count before anything is renamed into place. - `scripts/restore-check.sh` works, and **has been run against production**: the dump was replayed from SQL into a scratch database — 2 tables, 3 rows, under one second. `docs/OPERATIONS.md` carries the date. **What is still missing, and this is the whole issue:** 1. **Nothing runs on a schedule.** One backup taken by hand is not a backup regime. The newest dump is as old as the last time somebody remembered. 2. **The dump is on one machine, beside nothing.** It is in `$HOME/backups/queue-north-website` on the operator's workstation. `backup.sh` says this itself on every run: *a backup that only exists beside the database it came from does not survive the disk, the host or the account.* **What to do, in order.** The order matters — a schedule writing to a destination that does not survive is the more comfortable half of the job and the less useful one. 1. Pick a destination that is not the operator's workstation and not nebula. 2. Extend `backup.sh`'s tail, or wrap it, to copy there. Keep the verify-before-rename contract intact. 3. Schedule it — cron, a systemd timer, or an OpenClaw cron job, which can also deliver the failure. 4. Set `RESTORE_MIN_ROWS` above 0 once the lead count is meaningful. It is 0 today, which means the check cannot yet catch a snapshot of an emptied volume — the one failure no structural check can see. 5. Run `restore-check.sh` on a schedule too, and update *Last verified restore* in `docs/OPERATIONS.md` when you do. **The trap.** A backup job that runs nightly for a year and writes a file every night for a year, where the file is a snapshot of the wrong container or an empty volume, is indistinguishable from a working one by looking at the directory. The size column is plausible and the timestamps march forward. That is why step 4 is on this list and not optional. **Why P2 and not higher.** The database holds three leads. The exposure is real and the loss today would be small; it grows every week this stays open. Verify: a scheduled job produces a dated dump in a location that is neither nebula nor the operator's workstation, `bash scripts/restore-check.sh` passes against the newest one, and `docs/OPERATIONS.md`'s *Last verified restore* row names a date from that schedule rather than a manual run.

null added this to the Batch 15 — Adoption follow-ups milestone 2026-08-18 01:26:21 -05:00

null added the

label 2026-08-18 01:26:21 -05:00

null commented

2026-08-18 01:28:22 -05:00

Author

Owner

Evidence that the first half of this is already done, recorded here so the remaining scope is unambiguous.

scripts/backup.sh and scripts/restore-check.sh were both run against production on 2026-08-18:

backup:  wrote queuenorth-leads-20260818T061237Z.sqlite
         28.0 KiB, 2 tables, verified readable.

restore-check:  leads              3 row(s)
                support_requests   0 row(s)
                restored in 0s — 2 table(s), 3 row(s) total.

So the scripts work and the dump is restorable. docs/OPERATIONS.md carries Last verified restore: 2026-08-18.

What is left is the part in the title: a schedule, and a copy off the operator's machine. Neither exists.

Evidence that the first half of this is already done, recorded here so the remaining scope is unambiguous. `scripts/backup.sh` and `scripts/restore-check.sh` were both run against production on 2026-08-18: ``` backup: wrote queuenorth-leads-20260818T061237Z.sqlite 28.0 KiB, 2 tables, verified readable. restore-check: leads 3 row(s) support_requests 0 row(s) restored in 0s — 2 table(s), 3 row(s) total. ``` So the scripts work and the dump is restorable. `docs/OPERATIONS.md` carries **Last verified restore: 2026-08-18**. What is left is the part in the title: a **schedule**, and a copy **off the operator's machine**. Neither exists.