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_URLscheme,sqlite:///data/loomarr.dborpostgres://…. An unknown scheme fails at boot. - SQLite uses
modernc.org/sqlite(pure Go, no cgo), opened with WAL,synchronous=NORMAL, a 15 sbusy_timeoutand_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 onbusy_timeoutinstead of failing mid-transaction. Until #2446 the pool had one connection, and every read queued behind every write. Open also runsPRAGMA optimizeso the planner has statistics. The file usesauto_vacuum=INCREMENTAL. A database from before #2446 is converted by oneVACUUMon its first open, and the nightly housekeeping pass ends withPRAGMA incremental_vacuumandPRAGMA optimize, so space freed by purges goes back to the disk instead of the file keeping its largest size. PostgreSQL usespgxthrough itsdatabase/sqlshim, 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:
goosemigrations 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 UPDATEworks 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.
_atis unix seconds,_at_msunix milliseconds and_at_nsunix nanoseconds, so a query never compares two of them by accident (#2319). An id names what it identifies:inventory_item_idis Loomarr’s inventory item, whileitem_idandlibrary_idon the media-server tables are the media server’s (#2320). - A state column lists its states in a
CHECK.titles.state,proposals.statusandproposal_jobs.statusrefuse a value the code does not know, so a misspelt state fails at the write. A new state needs a migration that widens theCHECK(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, never1/0, which both dialects read. A time column isBIGINT; a 32-bitINTEGERof unix seconds runs out in 2038. - Names for new tables. A table is a plural noun for what one row is (
proposal_jobs, notjobs). A time names its event (arrived_at, notavailable_at). The next time work is due isdue_at, and a claim’s lease ends atlease_expires_at. Older tables keepdeadline,next_runand 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 orUNIQUEalready 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:CASCADEwhen the child means nothing without the parent (a channel’s airings and favourites, a user’s sessions),SET NULLwhen the child outlives it (a crash report keeps its device and stack after the account goes). - No foreign key, on purpose:
clip_hashcolumns.clipsis 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 asproposal_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, asDeleteChanneldoes. proposals.job_id, because retention purges jobs and keeps the approved proposal as the audit trail.
- Keys without
ON DELETErestrict 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 … RENAMErewrites 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(ClaimDueTitlesand the otherClaimDue*), 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.dirmeans 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:
| Data | Rule |
|---|---|
| Sessions | sliding TTL SESSION_TTL (30 days); expired rows purged |
| Activity feed | purged after activity.retention (30 days) |
| Notifications | terminal intents and their attempts purged after 30 days; queued and sending work never |
| Diagnostics | purged 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 |
| Jobs | done 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 |
| Proposals | denied 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=roandquery_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/migratequeues it and returns; the generation drains, copies, verifies parity independently, writes the target into/data/bootstrap.jsononly 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_versionis not copied. - Values are coerced by the destination column type; binary columns bypass string scanning.
- An environment-pinned
DATABASE_URLdisables in-app migration, because environment always wins at boot.
Backup and restore
- SQLite:
GET /v1/backup(admin) streams a consistentVACUUM INTOsnapshot, safe under WAL. Never copy a live SQLite file. - Server-written backups go to
backup.dir(/data/backups) with mode0600, because a backup holds every stored secret. Thebackupjob writes one onbackup.schedule(nightly) and then prunes to the newestbackup.retain(7;0keeps all). Pruning only touchesloomarr-<timestamp>.dbfiles it wrote, and only after a successful write. The pre-migration backup is the same artifact. - PostgreSQL:
/v1/backupreturns 501 with a pointer, because the image ships no PostgreSQL client. The Compose path runspg_dump --format=customandpg_restoreinside the Postgres service. Thebackupjob 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) andmake 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
ClaimDueconcurrency (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-lifecycleruns 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/backupon SQLite yields a snapshot that restores to a working instance.