Skip to content

Storage

Formerly design.md §5 (store, retention, Postgres concurrency, SQLite → PostgreSQL) and §16’s backup section. The database is the product: channels, tags, proposals and the audit trail live there.

One store, two backends

One Store holds provisioning, scheduling, filler, users, jobs, proposals and settings. SQLite and PostgreSQL are both first-class (decision 0001); the store code path is shared through database/sql.

  • Selection: the DATABASE_URL scheme, sqlite:///data/loomarr.db or postgres://…. An unknown scheme fails at boot.
  • SQLite uses modernc.org/sqlite (pure Go, no cgo), opened with WAL, synchronous=NORMAL, a 15 s busy_timeout and _txlock=immediate, in a pool of four connections that River shares. Reads run beside the one writer SQLite allows; each write transaction takes the write lock when it begins, so a waiting writer queues on busy_timeout instead of failing mid-transaction. Until #2446 the pool had one connection, and every read queued behind every write. Open also runs PRAGMA optimize so the planner has statistics. The file uses auto_vacuum=INCREMENTAL. A database from before #2446 is converted by one VACUUM on its first open, and the nightly housekeeping pass ends with PRAGMA incremental_vacuum and PRAGMA optimize, so space freed by purges goes back to the disk instead of the file keeping its largest size. PostgreSQL uses pgx through its database/sql shim, in a pool of up to 20 connections per replica that stay open between bursts (idle ones close after 5 minutes, and every one is replaced after an hour). The invalidation listener holds one more, outside the pool.
  • Schema: goose migrations embedded in the binary, one directory per dialect so dialect DDL never leaks; they run at startup. SQL stays ANSI where it can (INSERT … ON CONFLICT … DO UPDATE works on both). Migrations are forward-only.
  • The shared SQLite/PostgreSQL conformance suite is the contract for both backends.
  • A column’s name says its unit and its id space. _at is unix seconds, _at_ms unix milliseconds and _at_ns unix nanoseconds, so a query never compares two of them by accident (#2319). An id names what it identifies: inventory_item_id is Loomarr’s inventory item, while item_id and library_id on the media-server tables are the media server’s (#2320).
  • A state column lists its states in a CHECK. titles.state, proposals.status and proposal_jobs.status refuse a value the code does not know, so a misspelt state fails at the write. A new state needs a migration that widens the CHECK (a table rebuild on SQLite).
  • A yes/no column is BOOLEAN on PostgreSQL (SQLite stores it as INTEGER 0/1), and SQL writes TRUE/FALSE, never 1/0, which both dialects read. A time column is BIGINT; a 32-bit INTEGER of unix seconds runs out in 2038.
  • Names for new tables. A table is a plural noun for what one row is (proposal_jobs, not jobs). A time names its event (arrived_at, not available_at). The next time work is due is due_at, and a claim’s lease ends at lease_expires_at. Older tables keep deadline, next_run and their variants, because renaming them all is churn for no reader.
  • An index is idx_<table>_<purpose> (idx_proposal_jobs_status_deadline), the same name on both dialects. An index whose leading columns a primary key or UNIQUE already covers is redundant: drop it. SQLite cannot rename an index, so a rename there is a drop and re-create.

Foreign keys

SQLite enforces foreign keys (foreign_keys(on) in every DSN), so both backends behave alike.

  • A child row gets a foreign key when its parent is hard-deleted and never rebuilt, and the key always says its ON DELETE: CASCADE when the child means nothing without the parent (a channel’s airings and favourites, a user’s sessions), SET NULL when the child outlives it (a crash report keeps its device and stack after the account goes).
  • No foreign key, on purpose:
    • clip_hash columns. clips is a cache of the drop folder that has been dropped and rebuilt (00044), so pipeline state, split proposals and feedback must outlive a rebuild. The filler sync’s prune removes them instead (00051).
    • A column whose “none” is '', such as proposal_jobs.channel_id, because '' cannot reference a row.
    • A polymorphic id, such as discovery_feedback.scope_id (a channel, or nothing for the household). Its owner deletes it in the same transaction, as DeleteChannel does.
    • proposals.job_id, because retention purges jobs and keeps the approved proposal as the audit trail.
  • Keys without ON DELETE restrict the delete. The older ones on filler evaluation, acquisition, quality snapshot and holiday tables do so deliberately: the parent may only go once nothing records against it.
  • Rebuilding a parent on SQLite rebuilds its children. ALTER TABLE … RENAME rewrites the children’s keys to the renamed table, and dropping it then cascades. Rename the children too, copy parent before child, and drop child before parent (00152, 00166).

Concurrency on PostgreSQL

SQLite means exactly one instance. PostgreSQL permits replicas, which changes what correctness needs:

  • Claims use SELECT … FOR UPDATE SKIP LOCKED (ClaimDueTitles and the other ClaimDue*), so two replicas never both fire a retry or give-up. On SQLite the same method is a plain query.
  • Every channel-row mutation advances a monotonic revision. Full saves are compare-and-swap on the revision they read. An operator’s PATCH carries the revision the editor showed and gets 409 when stale, because silently replaying a whole-lineup replacement could erase a title they never saw. Timestamps cannot do this job: they are second-resolution.
  • Changing and publishing the global playout backend holds one workflow lock (an in-process mutex on SQLite, a database advisory lock on a dedicated PostgreSQL connection). A waiting replica refreshes its settings snapshot only after taking the lock, so two controllers cannot overlap a publication.
  • Publication reads use the durable checkpoint row, not a process cache. Each reconcile attempt or routing request reads it once without taking the workflow lock; a missing or unreadable checkpoint fails closed.
  • Runtime settings converge across replicas within 30 seconds: each generation re-reads all settings as one snapshot on a timer. SQLite starts no reader.
  • Host filesystem layout is per replica. Changing filler.dir means presenting the same paths to every replica and restarting each one; a database lock cannot orchestrate mounts.

One replica is the supported configuration until a multi-replica test passes.

Decisions are atomic

Approving a Proposal is a compare-and-swap from submitted. The final audited Proposal, every newly tracked title and the intent’s building channel are written in one transaction, so a concurrent approve, deny or retry has exactly one winner. A failed approval changes nothing. A channel’s non-empty intent_ref is unique in the database, so concurrent materialization cannot create two channels for one intent; uniqueness races roll back and replan. A refine or re-curation job names its channel (proposal_jobs.channel_id) instead, and approval checks, in the same transaction, that the planned channel is the one the job belongs to, and a newer approved proposal of any job of that channel supersedes an older candidate (supersession is per channel). A channel carries its own approved intent (intent_json) and requester (requested_by), so retention never removes what a refine or re-curation needs. Codec derivation and the first Tunarr reconcile run after commit and are best-effort: the periodic channel sweep is the durable retry. A denial can never overwrite an approval.

Retention

The daily housekeeping job enforces retention:

DataRule
Sessionssliding TTL SESSION_TTL (30 days); expired rows purged
Activity feedpurged after activity.retention (30 days)
Notificationsterminal intents and their attempts purged after 30 days; queued and sending work never
Diagnosticspurged after diagnostics.retention (7 days), then trimmed oldest-first to diagnostics.max_storage_mb (512 MiB); an active process run is never removed, and each run has a fixed output cap that keeps its start and failure tail
Jobsdone and failed purged after JOBS_RETENTION (30 days); queued and running never, because age is not evidence that work finished; a done job whose proposal is still submitted is kept until the proposal is decided; no channel depends on a purged job
Proposalsdenied purged after PROPOSALS_RETENTION (90 days); approved (the audit trail) and submitted (a member still waiting) are kept

Proposals are purged before jobs, because proposals.job_id has no foreign key. Filler catalog sync removes clips that vanished from the media server. Diagnostic files and their rows are deleted together by the diagnostics module, not by the store.

Moving from SQLite to PostgreSQL

Settings → System → Database runs connect → preflight → backup → migrate and restart. Only this direction is supported; going back is the backup file plus reverting one setting.

  • The source is only ever read. After the live store closes, SQLite is reopened with mode=ro and query_only. Every failure ends with the operator still on the database they started on.
  • The copy runs outside the request and outside a writable generation. POST /v1/system/database/migrate queues it and returns; the generation drains, copies, verifies parity independently, writes the target into /data/bootstrap.json only after parity, and starts on PostgreSQL. If that first PostgreSQL generation cannot start, bootstrap reverts to SQLite.
  • Preflight requires a reachable, empty PostgreSQL 13+ target with UTF8 encoding and the needed privileges. An advisory lock serializes competing migrations; destination tables stay locked through parity and commit.
  • The backup gate is enforced by the server, which refuses to migrate unless it wrote a backup for this migration. That is why the backup is a server-written file, not a download.
  • Tables and copy order come from the destination’s catalog (a topological sort over its foreign keys), never a hand-kept list. goose_db_version is not copied.
  • Values are coerced by the destination column type; binary columns bypass string scanning.
  • An environment-pinned DATABASE_URL disables in-app migration, because environment always wins at boot.

Backup and restore

  • SQLite: GET /v1/backup (admin) streams a consistent VACUUM INTO snapshot, safe under WAL. Never copy a live SQLite file.
  • Server-written backups go to backup.dir (/data/backups) with mode 0600, because a backup holds every stored secret. The backup job writes one on backup.schedule (nightly) and then prunes to the newest backup.retain (7; 0 keeps all). Pruning only touches loomarr-<timestamp>.db files it wrote, and only after a successful write. The pre-migration backup is the same artifact.
  • PostgreSQL: /v1/backup returns 501 with a pointer, because the image ships no PostgreSQL client. The Compose path runs pg_dump --format=custom and pg_restore inside the Postgres service. The backup job does not register on PostgreSQL.
  • Restore is a CLI operation, with Loomarr stopped: it replaces the store the app runs on, including the sessions that would authorize an in-app button. make backup-restore-verify (SQLite) and make backup-restore-drill (PostgreSQL) prove the procedure in isolation.

Tests that pin this

Formerly design.md §19.

  • Store conformance: one suite runs against SQLite (private temp-file clones of a migrated, boot-seeded template) and Postgres (testcontainers, private database clones), including ClaimDue concurrency (no record claimed twice). Each clone opens through its production adapter without replaying migrations; dedicated migration and lifecycle tests use fresh databases. The race-enabled integration target carries an explicit 20-minute package timeout.
  • Database lifecycle certification: make test-db-lifecycle runs the Postgres gate, then drives the shipped image through Compose and Traefik: a fresh PostgreSQL install writes and restarts; a populated SQLite install preflights, backs up, drains, copies, verifies, switches, restarts, keeps auth and reads, accepts PostgreSQL writes and rolls back; target failures and a killed mid-copy recover on SQLite; an in-flight write drains into the snapshot. Direct SQL is limited to fixture setup and independent fidelity checks.
  • Lifecycle: the downgrade guard refuses a newer-schema database; an upgrade on SQLite first keeps a checked copy under backups/pre-migration/ beside the database and does not migrate when the copy fails (#4383); the janitor purges expired sessions and old jobs; GET /v1/backup on SQLite yields a snapshot that restores to a working instance.