Litestream replication and proven restore #73

2026-10-05T19:18:06-04:00

dev_agent commented

2026-10-05 19:18:06 -04:00

Maestro task: d2f2e87e-5114-4ef1-93ab-360ffb5c009e (Feature 07 - Deployment, Identity and Backups).

Done-condition

deploy/litestream.yml replicates /data/mealquest.db over SFTP to 10.60.1.50:/homes/Joshua/mealquest-backups (key in deploy/secrets, never committed); a litestream service in deploy/compose.yaml runs it alongside mealquest; a nightly job rsyncs /data/media to the same target. A restore drill on 10.60.1.14: stop the stack, move the DB aside, litestream restore from the replica, start, verify row counts of dinners and week_plans match. Evidence: replica listing, the drill log with before/after counts, restore procedure in deploy/RUNBOOK.md.

What this adds

deploy/litestream.yml (snapshot 24h/168h; sftp replica; pinned NAS ECDSA host key).
deploy/compose.yaml: a litestream service (litestream/litestream:0.5.17) that waits for mealquest to be healthy; runs as 10001, read-only fs, no-new-privileges, cap_drop ALL; mounts the shared mealquest-data volume, the config and the key read-only.
deploy/systemd/mealquest-media-backup.{sh,service,timer}: nightly 03:30 rsync of /data/media to the NAS; refuses to run if media is a symlink or absent, so --delete only acts inside mealquest-backups/media.

Host/NAS setup on 10.60.1.14

Dedicated ed25519 key at deploy/secrets/litestream_key (0600, 10001:10001), never committed.
Its public half appended to joshua@10.60.1.50:~/.ssh/authorized_keys (existing four keys retained; backup kept) and the line constrained with restrict to mirror the NestQuest key.
Remote folder /volume1/homes/Joshua/mealquest-backups/media created.

Evidence

Containers: mealquest (healthy) + litestream (running). First sync logged snapshot complete; replica listing via litestream ltx (0.5.17 has no snapshots subcommand) shows L0 generations. After the key restriction and a litestream restart, SFTP reconnected and replication continued.
Restore drill: full-database checksum before (after down) and after restore were identical (836e230e16c58dc7, 26 tables, integrity_check ok); dinners and week_plans were 0=0 (empty DB), so the whole-DB checksum is the operative evidence. Restore ran with run --rm --no-deps so compose did not start the app and create a fresh empty DB first.
Media backup script tested against a scratch target (copy/delete/no-op) and the symlink refusal path; empty source exits without running rsync.

Known limitations / security notes

The nightly media rsync runs as root over a container-writable folder; a symlink swapped in between the check and the rsync is a residual TOCTOU risk. Running as 10001 would close it but needs a NAS host-key file readable by that user.
The snapshots command does not exist in litestream 0.5.17; ltx is used instead (no code change needed).
deploy/RUNBOOK.md is task 876134bc.

Maestro task: d2f2e87e-5114-4ef1-93ab-360ffb5c009e (Feature 07 - Deployment, Identity and Backups). ## Done-condition deploy/litestream.yml replicates /data/mealquest.db over SFTP to 10.60.1.50:/homes/Joshua/mealquest-backups (key in deploy/secrets, never committed); a litestream service in deploy/compose.yaml runs it alongside mealquest; a nightly job rsyncs /data/media to the same target. A restore drill on 10.60.1.14: stop the stack, move the DB aside, litestream restore from the replica, start, verify row counts of dinners and week_plans match. Evidence: replica listing, the drill log with before/after counts, restore procedure in deploy/RUNBOOK.md. ## What this adds - deploy/litestream.yml (snapshot 24h/168h; sftp replica; pinned NAS ECDSA host key). - deploy/compose.yaml: a litestream service (litestream/litestream:0.5.17) that waits for mealquest to be healthy; runs as 10001, read-only fs, no-new-privileges, cap_drop ALL; mounts the shared mealquest-data volume, the config and the key read-only. - deploy/systemd/mealquest-media-backup.{sh,service,timer}: nightly 03:30 rsync of /data/media to the NAS; refuses to run if media is a symlink or absent, so --delete only acts inside mealquest-backups/media. ## Host/NAS setup on 10.60.1.14 - Dedicated ed25519 key at deploy/secrets/litestream_key (0600, 10001:10001), never committed. - Its public half appended to joshua@10.60.1.50:~/.ssh/authorized_keys (existing four keys retained; backup kept) and the line constrained with `restrict` to mirror the NestQuest key. - Remote folder /volume1/homes/Joshua/mealquest-backups/media created. ## Evidence - Containers: mealquest (healthy) + litestream (running). First sync logged snapshot complete; replica listing via `litestream ltx` (0.5.17 has no `snapshots` subcommand) shows L0 generations. After the key restriction and a litestream restart, SFTP reconnected and replication continued. - Restore drill: full-database checksum before (after `down`) and after restore were identical (`836e230e16c58dc7`, 26 tables, integrity_check ok); dinners and week_plans were 0=0 (empty DB), so the whole-DB checksum is the operative evidence. Restore ran with `run --rm --no-deps` so compose did not start the app and create a fresh empty DB first. - Media backup script tested against a scratch target (copy/delete/no-op) and the symlink refusal path; empty source exits without running rsync. ## Known limitations / security notes - The nightly media rsync runs as root over a container-writable folder; a symlink swapped in between the check and the rsync is a residual TOCTOU risk. Running as 10001 would close it but needs a NAS host-key file readable by that user. - The `snapshots` command does not exist in litestream 0.5.17; `ltx` is used instead (no code change needed). - deploy/RUNBOOK.md is task 876134bc.

dev_agent added 1 commit 2026-10-05 19:18:07 -04:00

Litestream replication to the NAS and nightly media backup

CI / ci (pull_request) Successful in 1m38s

Details

CI / ci (push) Successful in 1m34s

Details

9ac3342664

Add a litestream service (0.5.17) to the compose stack that streams
/data/mealquest.db over SFTP to the Synology NAS with a dedicated key,
daily snapshots kept for a week. Add a systemd timer that rsyncs the
media directory to the NAS nightly at 03:30.

Task: d2f2e87e-5114-4ef1-93ab-360ffb5c009e

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

review_agent requested changes

2026-10-05 19:20:44 -04:00

Dismissed

review_agent left a comment

Independent Codex gpt-6-sol review: CHANGES_REQUIRED for this exact commit.

overseer added 1 commit 2026-10-05 19:30:30 -04:00

Run the media backup as uid 10001 and add the deploy runbook

CI / ci (push) Successful in 1m35s

Details

CI / ci (pull_request) Successful in 1m34s

Details

765740b921

Address review of 9ac3342:

- The media backup now runs as the host user mealquest (10001) with no
  capabilities. systemd bind-mounts the data volume read-only at
  /run/mealquest-data and passes the NAS key as a credential; the script
  changes into media, refuses it if the physical path differs (symlink)
  and rsyncs "./", so the path cannot be swapped under it. ssh pins the
  NAS host keys from /etc/mealquest/known_hosts (deploy/known_hosts).
- A missing media directory now fails the job. The deploy step creates it.
- deploy/RUNBOOK.md records the Traefik route (task 0c1dc172), backup
  setup, the restore procedure (stop, move db/-wal/-shm aside, restore,
  count check, start) and the 2026-10-05 drill with ltx -level all.

Task: d2f2e87e-5114-4ef1-93ab-360ffb5c009e

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

review_agent approved these changes

2026-10-05 19:36:54 -04:00

review_agent left a comment

Independent Codex gpt-6-sol review: APPROVED for this exact commit.

approver_agent merged commit ceb68cc26f into feature/ec45cc08-b16d-4b8f-b617-7e79308a4f49-deployment

2026-10-05 19:41:12 -04:00

dev_agent referenced this pull request

2026-10-05 20:30:09 -04:00

Feature 07: Deployment, identity and backups #75

dev_agent referenced this pull request

2026-10-05 21:01:51 -04:00

Promote dev to main: Feature 07 (deployment, identity and backups) #76