0029: App data in SQLite

Status: accepted. Its row for requests and dumps is replaced by 0052: the Monitor keeps them on disk, masked.

Context

Everything high-volume lived in leashd's memory and was lost on every restart, including every update. For most of it that's fine, but a webhook delivery you held to release later was silently dropped. Task lists and terminal details were whole JSON files rewritten on every change, under one process-wide lock, with no cap. The secrets files were written in place, so a crash part way through a save could quietly reset the Cloudflare tokens and the share password.

Decision

• Place each kind of data by what it needs.

  Data	Where	Kept
  Webhook deliveries, held ones included	Database; bodies over 64 KiB as files in db/blobs/, named by their SHA-256	Last 500 per webhook, for the days chosen in Settings › Webhooks (14 by default, 1 to 365); held ones until released
  Task lists	Database	Up to 1,000 tasks per site
  Terminal details	Database; their output stays in terminals/<id>.out	The newest 200 terminals
  Scratchpads and their last runs' code (added by 0030); what a run produced stays in memory	Database	100 per site, the last 50 runs of each
  Review comments on a site's changes (change_comments, added by 0080)	Database	Open ones until resolved or the site is removed (90 days after the newest one when the site is gone but wasn't removed); resolved ones for the days chosen in Settings › Models › More (30 by default, 1 to 3650)
  Notes with their tags, links and full-text index (added by 0087); pictures pasted into them as files in db/note-images/, named by their SHA-256	Database and files	Notes until deleted; a picture no note shows, 7 days after the last note stopped showing it
  Traffic per site per minute	Database: counts, 5xx, p95, median, slow queries. Never a header, path or body	24 hours
  Requests, dumps, process logs, notifications	Memory, as before	Until Short Leash restarts
  config.toml, secrets/*.json, the CA and certificates	Files, as before

  Requests and dumps hold cookies, tokens and bodies, and the docs promise that requests are never written to disk. Notifications stay in memory as 0027 decided; the one that matters after a restart, a held delivery, is raised again from the database. Settings stay a file you can edit, and secrets stay out of the database (0012).

• rusqlite with SQLite bundled (MIT, public domain), and rusqlite_migration (Apache-2.0) for migrations. The MSRV goes to 1.95 for rusqlite_migration. sqlx and SeaORM bring an async framework and a query macro for five tables; redb and fjall have no SQL and no inspection tool like sqlite3; turso isn't 1.0 yet and uses the same file format, so a later switch stays open.

• One file, one writer. <home>/db/leash.db, in a folder only you can open (0700), with the database, its write-ahead log and its copies readable only by you (0600). Only leashd opens it. The app, the CLI and AI tools go through leashd's API. One connection lives on its own thread: every call is a job run in order, writes use BEGIN IMMEDIATE, and no read is held across an .await. WAL with synchronous=NORMAL loses at most the last commits to a power cut and nothing to a crash.

• Retention on every table, applied when rows are written and by upkeep every ten minutes, which also runs incremental_vacuum and wal_checkpoint(TRUNCATE). optimize runs hourly. A full VACUUM never runs.

• Recovery. At open, quick_check runs. A damaged file is moved to leash.db.corrupt-<time> with its sidecar files (and its webhook bodies linked into leash.db.corrupt-<time>-blobs/), and the daily copy (leash.db.bak-daily, made with VACUUM INTO) is restored, or a new database started. A notification names the moved file. Migrations take a leash.db.bak-v<N> copy first. A database written by a newer Short Leash is left alone.

• Short Leash never fails to start because of the database. When it can't be opened, leashd runs as before it had one: deliveries in memory, tasks and terminals in their files. A notification says what failed and what to do.

• Updating from a version without a database imports tasks/*.json and terminals/terminals.json inside the transaction of the migration that creates their tables, and moves the files aside (tasks.imported/, terminals.json.imported) only after it commits. A file that can't be read is left in place and named. Rows go in with OR IGNORE, so files left behind are imported at the next start.

• Secrets are written atomically. fsx::write_private_atomic writes a 0600 temporary file, flushes it, renames it over the old one and flushes the folder. A secrets file that can't be parsed is moved to *.unreadable-<time> and named in a notification, never quietly treated as empty.

Consequences

• Held deliveries, tasks, terminal details and the Overview's last hour survive restarts and updates.
• The app adds about 1.7 MB and a C compile of SQLite. The Mac-hosted Windows clippy check sends libsqlite3-sys down its linked path so it compiles no C; real Windows CI builds the bundled SQLite.
• Webhook bodies and terminal details are now on disk. They're readable only by you, like terminals' saved output. Request content still never is.
• Reinstalling an older version shows no tasks, because their files were moved; renaming tasks.imported/ back to tasks/ brings them back. Deliveries held in an older daemon's memory can't be saved when it updates.
• Every new table needs a retention rule in its first migration, and every change to a table is a new migration.