ops: document a consistent backup procedure; cp of a live WAL database is unsafe #153

New issue

Closed

opened 2026-08-09 01:05:28 +00:00 by rosa · 1 comment

rosa commented

2026-08-09 01:05:28 +00:00

Owner

Found during a pre-production review.

Location: ADR-0016 (docs/adr/0016-vernier-stores-its-data-in-sqlite.md), ADR-0017 (docs/adr/0017-media-is-stored-as-optimized-blobs-in-sqlite.md); pool config at crates/infra/src/repositories/sqlite.rs:44
Severity: Data-loss risk — production operability

Problem

Both ADRs document the backup procedure as cp vernier.db somewhere. The pool opens the database in WAL mode (crates/infra/src/repositories/sqlite.rs:54), so a plain cp of the main DB file while the server is running is not a consistent snapshot: it can capture a torn file (a checkpoint mid-flight), and it omits the -wal and -shm sidecars that hold not-yet-checkpointed committed transactions. A restore from such a copy can be stale or corrupt.

This is the highest-stakes gap for going to production: media are stored as blobs in the same database (ADR-0017), so this one file is the entire instance. A bad backup loses posts, accounts, and all media at once.

Suggested fix

Give operators a consistent-snapshot path and document it in place of cp. Options, roughly in order of upkeep:

VACUUM INTO 'backup.db' or sqlite3 vernier.db ".backup 'backup.db'" — online, consistent single-file snapshot; easy to cron. Lowest-friction fit for the "small box" goal.
Litestream — continuous streaming replication to object storage; point-in-time recovery, but adds a sidecar process (weigh against Goal #1, "runs on a small box... little upkeep").

At minimum, update ADR-0016/0017 and the README so no one relies on cp of a live WAL database. Consider whether a built-in snapshot command (or a documented one-liner) is warranted so backup doesn't require the operator to know SQLite internals.

Found during a pre-production review. **Location:** ADR-0016 (`docs/adr/0016-vernier-stores-its-data-in-sqlite.md`), ADR-0017 (`docs/adr/0017-media-is-stored-as-optimized-blobs-in-sqlite.md`); pool config at `crates/infra/src/repositories/sqlite.rs:44` **Severity:** Data-loss risk — production operability ## Problem Both ADRs document the backup procedure as `cp vernier.db somewhere`. The pool opens the database in WAL mode (`crates/infra/src/repositories/sqlite.rs:54`), so a plain `cp` of the main DB file while the server is running is **not** a consistent snapshot: it can capture a torn file (a checkpoint mid-flight), and it omits the `-wal` and `-shm` sidecars that hold not-yet-checkpointed committed transactions. A restore from such a copy can be stale or corrupt. This is the highest-stakes gap for going to production: media are stored as blobs in the same database (ADR-0017), so this one file *is* the entire instance. A bad backup loses posts, accounts, and all media at once. ## Suggested fix Give operators a consistent-snapshot path and document it in place of `cp`. Options, roughly in order of upkeep: - **`VACUUM INTO 'backup.db'`** or `sqlite3 vernier.db ".backup 'backup.db'"` — online, consistent single-file snapshot; easy to cron. Lowest-friction fit for the "small box" goal. - **Litestream** — continuous streaming replication to object storage; point-in-time recovery, but adds a sidecar process (weigh against Goal #1, "runs on a small box... little upkeep"). At minimum, update ADR-0016/0017 and the README so no one relies on `cp` of a live WAL database. Consider whether a built-in snapshot command (or a documented one-liner) is warranted so backup doesn't require the operator to know SQLite internals.

rosa added the

labels

2026-08-09 01:05:37 +00:00

rosa commented

2026-08-09 01:09:33 +00:00

Author

Owner

This was generated by AI during triage.

Agent Brief

Category: bug
Summary: Replace the unsafe cp vernier.db backup guidance with a consistent-snapshot procedure in the ADRs and README.

Current behavior:
The project's operator documentation prescribes backup as copying the SQLite database file (cp vernier.db somewhere). This appears in the SQLite storage ADR, the media-blob storage ADR, and the README. The running server opens the database in WAL journal mode, so a file copy taken while the server is live is not a consistent snapshot: committed transactions that still live in the -wal sidecar are omitted, and a copy taken mid-checkpoint can be torn. Because media are stored as blobs in this same database, the file is the entire instance — a bad restore loses posts, accounts, and media together.

Desired behavior:
The documentation describes a backup procedure that produces a consistent single-file snapshot of a live WAL-mode database, and no longer instructs operators to cp the database file. Use SQLite's online backup — VACUUM INTO 'backup.db' or sqlite3 <db> ".backup 'backup.db'" — and explain briefly why a raw cp is unsafe under WAL so the reasoning is durable. Restore is copying the snapshot back into place while the server is stopped. Keep it to a documentation change: a one-liner an operator can cron.

Key interfaces:

The two storage ADRs (SQLite datastore; media-as-blobs) — each currently states the backup method; both must be corrected consistently.
The README's backup/operations guidance — same correction, aimed at operators rather than as a design record.
No code change: this issue does not add an in-app command (tracked separately).

Acceptance criteria:

No doc instructs backing up by cp/copying the live database file.
Each place that mentioned the old method now gives a consistent online-snapshot command (VACUUM INTO or .backup) and a matching restore note.
The docs briefly state why copying a live WAL database is unsafe.
The three surfaces (both ADRs + README) agree with each other.

Out of scope:

Any in-application backup/snapshot command or subcommand.
Continuous replication (Litestream) or off-box/object-storage shipping.
Scheduling/cron wiring, retention, or encryption of backups.
Changing the storage decision itself (SQLite, single file, media-as-blobs).

> *This was generated by AI during triage.* ## Agent Brief **Category:** bug **Summary:** Replace the unsafe `cp vernier.db` backup guidance with a consistent-snapshot procedure in the ADRs and README. **Current behavior:** The project's operator documentation prescribes backup as copying the SQLite database file (`cp vernier.db somewhere`). This appears in the SQLite storage ADR, the media-blob storage ADR, and the README. The running server opens the database in WAL journal mode, so a file copy taken while the server is live is not a consistent snapshot: committed transactions that still live in the `-wal` sidecar are omitted, and a copy taken mid-checkpoint can be torn. Because media are stored as blobs in this same database, the file is the entire instance — a bad restore loses posts, accounts, and media together. **Desired behavior:** The documentation describes a backup procedure that produces a consistent single-file snapshot of a *live* WAL-mode database, and no longer instructs operators to `cp` the database file. Use SQLite's online backup — `VACUUM INTO 'backup.db'` or `sqlite3 <db> ".backup 'backup.db'"` — and explain briefly why a raw `cp` is unsafe under WAL so the reasoning is durable. Restore is copying the snapshot back into place while the server is stopped. Keep it to a documentation change: a one-liner an operator can cron. **Key interfaces:** - The two storage ADRs (SQLite datastore; media-as-blobs) — each currently states the backup method; both must be corrected consistently. - The README's backup/operations guidance — same correction, aimed at operators rather than as a design record. - No code change: this issue does not add an in-app command (tracked separately). **Acceptance criteria:** - [ ] No doc instructs backing up by `cp`/copying the live database file. - [ ] Each place that mentioned the old method now gives a consistent online-snapshot command (`VACUUM INTO` or `.backup`) and a matching restore note. - [ ] The docs briefly state why copying a live WAL database is unsafe. - [ ] The three surfaces (both ADRs + README) agree with each other. **Out of scope:** - Any in-application backup/snapshot command or subcommand. - Continuous replication (Litestream) or off-box/object-storage shipping. - Scheduling/cron wiring, retention, or encryption of backups. - Changing the storage decision itself (SQLite, single file, media-as-blobs).

rosa referenced this issue

2026-08-09 01:09:33 +00:00

ops: ship a built-in backup mechanism (split from #153) #157

rosa referenced this issue from a commit

2026-08-10 02:50:35 +00:00

docs: replace unsafe cp backup guidance with an online snapshot

rosa closed this issue

2026-08-10 02:50:35 +00:00