Context
The daemon is the sole owner of the SQLite database at ~/.local/share/finch/finch.db and runs as a long-lived user service. External tooling that needs a copy of the data — a backup, a migration, an ad-hoc export — has no path to obtain one except copying the live database file out from under the running daemon.
This surfaced while wiring an off-machine backup of finch.db: the backup does a raw file copy of the live database.
Problem
finch exposes no mechanism to produce a consistent point-in-time snapshot of its database while the daemon is running. Because the daemon holds the database open and may write at any time, any external copy races the writer:
- A copy that captures the main database file mid-transaction (or without the accompanying WAL frames) can be torn — internally inconsistent — or stale, missing writes that hadn't checkpointed yet.
- The failure is silent: the copy looks like a valid
.db file, and the inconsistency only surfaces when something later reads it.
This is not specific to one backup tool. Any consumer that wants the data — replication, a future restore drill, a "hand me my data" export, a schema migration that snapshots first — hits the same wall, because the only available primitive is "copy the live files."
Evidence
- A b2-sync target now copies
~/.local/share/finch/ to off-machine storage on a timer via a raw rclone copy. It is safe today only because the daemon is write-rare and the current data is fictional during early development — neither of which is a property to rely on once the database holds real financial records.
- SQLite explicitly documents that copying a live database file with normal filesystem tools is unsafe while a connection is open, and provides the online Backup API (and
VACUUM INTO) precisely to produce a settled copy under concurrent access.
Suggested approaches
Options, not a prescription:
- An export/backup capability on the daemon — a
finch backup/export subcommand or a gRPC method that uses SQLite's online Backup API or VACUUM INTO to write a settled snapshot to a caller-specified path. Backups then copy that settled output instead of the live database.
- Orchestration is a downstream detail once the capability exists — e.g., a backup step invokes the export immediately before copying, or a systemd unit runs it ahead of the off-machine sync. Worth noting so the export is designed to be callable non-interactively, but not part of the core gap.
Context
The daemon is the sole owner of the SQLite database at
~/.local/share/finch/finch.dband runs as a long-lived user service. External tooling that needs a copy of the data — a backup, a migration, an ad-hoc export — has no path to obtain one except copying the live database file out from under the running daemon.This surfaced while wiring an off-machine backup of
finch.db: the backup does a raw file copy of the live database.Problem
finch exposes no mechanism to produce a consistent point-in-time snapshot of its database while the daemon is running. Because the daemon holds the database open and may write at any time, any external copy races the writer:
.dbfile, and the inconsistency only surfaces when something later reads it.This is not specific to one backup tool. Any consumer that wants the data — replication, a future restore drill, a "hand me my data" export, a schema migration that snapshots first — hits the same wall, because the only available primitive is "copy the live files."
Evidence
~/.local/share/finch/to off-machine storage on a timer via a rawrclonecopy. It is safe today only because the daemon is write-rare and the current data is fictional during early development — neither of which is a property to rely on once the database holds real financial records.VACUUM INTO) precisely to produce a settled copy under concurrent access.Suggested approaches
Options, not a prescription:
finch backup/exportsubcommand or a gRPC method that uses SQLite's online Backup API orVACUUM INTOto write a settled snapshot to a caller-specified path. Backups then copy that settled output instead of the live database.