Skip to content

Database reference

Every table and column Loomarr keeps, in plain words, grouped by the part of the app that uses it. It’s for anyone looking inside the database: to understand what Loomarr remembers, answer a question the app doesn’t show, or report a problem precisely.

Looking without breaking anything

Look at a copy, never the live file. make db-view opens a copy of this database in a browser-based viewer and deletes the copy when you stop it (see the dev loop). For a quick question about a SQLite install, a read-only shell on a copy or a nightly backup works too:

Terminal window
sqlite3 -readonly loomarr-copy.db '.tables'

Don’t change rows by hand. Loomarr checks what it writes, and a hand edit skips those checks. Change things in the app, or open an issue if the app can’t do what you need.

On Postgres, these descriptions are also stored as table and column comments, so most SQL clients show them beside the data.

Which table answers “what titles do we have”

library_items. A title is in the library when one of its library_files rows is present (missing_at_ms is 0). The other title tables are layers around it, each with one job:

  1. inventory_*: what each importer reported. One item per movie, show or episode, however many importers (a folder scan, an Emby or Jellyfin server) report it, with the files that play it. An item’s id is fixed by its first report, so a title that comes back gets the same id.
  2. library_*: the answer. One library_items row per title, with the same id as its inventory item, written in the same transaction as each inventory write, plus its details, files, artwork and collections. When the sources disagree, this is the one answer the app shows.
  3. titles: what channels asked for. A movie or show a channel wants, tracked from wanted to available through Seerr or the arr apps. Once it arrives, titles.library_id says which item it became. Until the switch to Loomarr’s own scanner (#2151) that is the media server’s id, which the switch rewrites to the library’s.

Every layer is pruned: the nightly housekeeping deletes reports, files and library rows long gone (each table’s retention says when).

Which tables are growing

Every night, after its clean-up, the housekeeping task records how many rows each table holds in database_table_rows, and keeps 400 days of counts. A table that keeps growing month after month is one nothing trims. This lists the ten that grew most over the last 30 days, on SQLite or Postgres:

SELECT latest.table_name, latest.row_count AS rows_now,
latest.row_count - COALESCE(earlier.row_count, 0) AS grew_by
FROM database_table_rows latest
LEFT JOIN database_table_rows earlier
ON earlier.table_name = latest.table_name
AND earlier.recorded_at = latest.recorded_at - 30 * 86400
WHERE latest.recorded_at = (SELECT MAX(recorded_at) FROM database_table_rows)
ORDER BY grew_by DESC
LIMIT 10;

If Prometheus scrapes Loomarr, the latest counts are also the loomarr_database_table_rows gauge, one series per table, so delta(loomarr_database_table_rows[30d]) graphs the same growth.

Reading the values

  • Times are whole numbers counting from 1 January 1970 UTC. The name says the unit: a column ending in _ms counts milliseconds, _ns nanoseconds, and any other counts seconds. In SQLite, datetime(col, 'unixepoch') turns seconds into a date and datetime(col / 1000, 'unixepoch') does the same for milliseconds.
  • Yes/no columns hold 1 for yes and 0 for no.
  • Columns ending in _json hold a JSON document; their description says what’s inside.
  • May be empty in the Type column means the column can be NULL. A key column identifies the row. An arrow (→) names the table and column a value points at.
  • Types are shown as SQLite declares them. Postgres uses its own equivalent for each.

Contents

People and sign-in

Who can use Loomarr and how they prove it: accounts, browser sign-ins, paired TVs and phones, invitations, password recovery and email addresses, plus each person’s own channel lists. Every secret here (passwords, sign-in cookies, device tokens, invitation and recovery links) is stored only as a one-way hash, which can’t be turned back into a working credential.

users

One row per person allowed into Loomarr. Rows are made by the first-admin setup, by an admin creating a local account, by importing a media-server account, or by someone redeeming an invitation. Accounts are never deleted; an admin disables them instead.

Kept: Kept forever: accounts are never deleted, only disabled.

ColumnWhat it holdsType
idIdentifies the person. For a media-server account it is that server’s user id; for a local account Loomarr makes one up. Other tables point here.TEXT · key
nameThe person’s name, which is also the username a local account signs in with.TEXT
roleWhat the person may do. admin manages the household (people, settings, approvals); member watches and makes requests.TEXT
disabled1 when an admin has turned the account off. A disabled person can’t sign in, and their sign-ins and paired devices are deleted.BOOLEAN
quotaHow many of the person’s approved requests may be waiting to arrive in the library at once. 0 means use the household default setting.INTEGER
auto_approve1 when the person’s requests skip the admin queue, as long as they stay within their quota.BOOLEAN
created_atWhen the account was made, in Unix seconds.BIGINT
updated_atWhen the account was last changed, in Unix seconds.BIGINT
password_hashA one-way Argon2id hash of the local password, used only to check a password at sign-in; the password can’t be recovered from it. Empty or NULL when the person has no local password.TEXT · may be empty
media_server_linked1 when the account comes from the media server and signs in with that server’s password, rather than being a Loomarr-only local account.BOOLEAN
last_seen_atWhen the person last used Loomarr from any sign-in or paired device, in Unix seconds, updated at most once a minute. 0 means not seen yet.BIGINT

sessions

One row per browser sign-in. Made at sign-in or when an invitation is redeemed; deleted at sign-out, when an admin revokes it, when the password is reset or the account disabled, and by a regular sweep once it has expired.

Kept: Kept until sign-out, revocation or a password reset; the nightly housekeeping task deletes expired ones.

ColumnWhat it holdsType
token_hashSHA-256 hash of the sign-in cookie. Only the browser holds the cookie itself; the hash can’t be turned back into a working sign-in.TEXT · key
user_idThe person signed in, from users.TEXT · → users.id
created_atWhen the person signed in, in Unix seconds.BIGINT
expires_atWhen the sign-in stops working, in Unix seconds. Use pushes it forward by the sign-in lifetime setting, at most once a minute.BIGINT
last_seen_atWhen the sign-in was last used, in Unix seconds, updated at most once a minute. 0 means not used since it was made.BIGINT
client_labelA rough description of the browser, such as “Firefox on macOS”, shown in the person’s list of sign-ins. Empty when the browser didn’t say.TEXT

device_pairings

One row per pairing code shown on a TV or other device that is asking to be signed in. A signed-in person types the code to approve it; the device then swaps it for a device token and the row is deleted. Codes expire after 10 minutes.

Kept: Deleted when redeemed; a code that expires first is deleted by the nightly housekeeping task.

ColumnWhat it holdsType
device_codeSHA-256 hash of the secret the device uses while it waits for approval. The device keeps the secret; the hash can’t be used in its place.TEXT · key
user_codeThe short code shown on the device’s screen for a person to type, such as BCDF-GHJK.TEXT
user_idThe person who approved the pairing, from users. NULL while still waiting.TEXT · may be empty · → users.id
device_nameThe name the device gave itself, shown when approving it. Empty if it gave none.TEXT
created_atWhen the code was shown, in Unix seconds.BIGINT
expires_atWhen the code stops working, in Unix seconds (10 minutes after it was made).BIGINT
approved_atWhen a person approved the code, in Unix seconds. 0 means still waiting.BIGINT

device_tokens

One row per paired device, such as a TV app. The device acts as the person who approved it, with that person’s current role. A row lasts until the person removes the device or the account is disabled; it has no expiry.

Kept: Kept until the person removes the device or the account is disabled.

ColumnWhat it holdsType
token_hashSHA-256 hash of the device’s token. Only the device holds the token; the hash can’t be turned back into a working sign-in.TEXT · key
user_idThe person who approved the device, from users.TEXT · → users.id
device_nameThe device’s name, shown in the person’s list of devices. Empty if it gave none.TEXT
created_atWhen the device was paired, in Unix seconds.BIGINT
last_seen_atWhen the device last used Loomarr, in Unix seconds, updated at most once a minute.BIGINT

invitations

One row per invitation an admin sends to let a new person in. It reserves a username or a media-server account for 7 days. Finished invitations (redeemed, expired or revoked) are deleted 30 days after they finished.

Kept: The nightly housekeeping task deletes redeemed, expired or revoked invitations 30 days after they finished.

ColumnWhat it holdsType
idIdentifies the invitation. invitation_grants and contact_addresses point here.TEXT · key
kindlocal invites someone to make a Loomarr-only account with the reserved username; library invites the owner of an existing media-server account.TEXT
usernameThe username reserved for a local invitation. Empty for a library invitation.TEXT
library_user_idThe media-server account invited by a library invitation. Empty for a local invitation.TEXT
display_nameThe media-server account’s name at the time of inviting. Empty for a local invitation.TEXT
identity_keyWhat the invitation reserves, used to stop two pending invitations for the same person. The lowercased, trimmed username for local, or the media-server user id for library.TEXT
roleThe role the new account gets, member or admin.TEXT
statuspending while it can still be used; redeemed once the person made their account; expired when 7 days passed unused; revoked when an admin cancelled it.TEXT
created_atWhen the admin made the invitation, in Unix seconds.BIGINT
expires_atWhen it stops working, in Unix seconds (always 7 days after created_at).BIGINT
terminal_atWhen it was redeemed, revoked or expired, in Unix seconds. 0 while still pending.BIGINT
redeemed_byThe id of the account created from it, in users. Empty unless redeemed.TEXT

invitation_grants

One row per invitation link or QR code handed out for an invitation. When an admin makes a new link, the earlier ones are cancelled; an emailed link is added alongside them. Deleted with their invitation.

Kept: Goes with its invitation (deleted when that is).

ColumnWhat it holdsType
token_hashSHA-256 hash (64 hex characters) of the secret in the link. The link itself is never stored; the hash can’t be turned back into a working link.TEXT · key
invitation_idThe invitation this link opens, from invitations.TEXT · → invitations.id
grant_kindWhat the link does. Always activation (making the account).TEXT
conveyanceHow the link reached the person. email means Loomarr sent it to the invited address; copy means an admin copied the link; qr means an admin showed it as a QR code.TEXT
created_atWhen the link was made, in Unix seconds.BIGINT
expires_atWhen the link stops working, in Unix seconds. Never later than its invitation’s expiry.BIGINT
consumed_atWhen the link was used to make the account, in Unix seconds. 0 means not used.BIGINT
revoked_atWhen the link was cancelled, in Unix seconds, because a newer link replaced it, the invitation was revoked, or another link was used first. 0 means not cancelled.BIGINT

password_recoveries

One row per “forgot password” request for a local account. Each lasts 30 minutes, and a new request cancels the person’s earlier pending one. Finished requests are deleted 30 days after they finished.

Kept: The nightly housekeeping task deletes used, cancelled or expired requests 30 days after they finished.

ColumnWhat it holdsType
idIdentifies the request. password_recovery_grants point here.TEXT · key
user_idThe local account being recovered, from users.TEXT · → users.id
statuspending while the link can still be used; redeemed once a new password was set; expired when 30 minutes passed unused; revoked when a newer request replaced it.TEXT
created_atWhen the request was made, in Unix seconds.BIGINT
expires_atWhen it stops working, in Unix seconds (always 30 minutes after created_at).BIGINT
terminal_atWhen it was redeemed, revoked or expired, in Unix seconds. 0 while still pending.BIGINT

password_recovery_grants

One row per password-reset link emailed for a recovery request. Using one sets the new password, signs the person out everywhere and cancels the request’s other links. Deleted with their request.

Kept: Goes with its password reset request (deleted when that is).

ColumnWhat it holdsType
token_hashSHA-256 hash (64 hex characters) of the secret in the reset link. The link itself is never stored; the hash can’t be turned back into a working link.TEXT · key
recovery_idThe request this link belongs to, from password_recoveries.TEXT · → password_recoveries.id
created_atWhen the link was made, in Unix seconds.BIGINT
expires_atWhen the link stops working, in Unix seconds. At most 30 minutes after it was made.BIGINT
consumed_atWhen the link was used to set a new password, in Unix seconds. 0 means not used.BIGINT
revoked_atWhen the link was cancelled, in Unix seconds. 0 means not cancelled.BIGINT

contact_addresses

Email addresses for people and pending invitations. A person has at most one verified address (the one password recovery goes to) and one pending replacement. An invitation’s address moves to the new account when it is redeemed.

Kept: Kept while its person or invitation exists (at most two per person); an invitation’s address is deleted with the invitation.

ColumnWhat it holdsType
owner_kindWhose address it is. user for a person in users; invitation for a pending invitation.TEXT · key
owner_idThe person’s id in users, or the invitation’s id in invitations.TEXT · key
emailThe address as entered, for display and sending.TEXT
normalizedThe lowercased address. No two rows may share it, so one address belongs to one owner.TEXT
statuspending until the owner has shown they receive mail there; verified once they have. Only a verified address is used for password recovery.TEXT · key
provenanceHow Loomarr learned the address. admin means an admin entered it; invitation and self are allowed values that current code does not write. It never implies the address was verified.TEXT
created_atWhen the address was added, in Unix seconds.BIGINT
verified_atWhen the address was verified, in Unix seconds. NULL while pending.BIGINT · may be empty

user_favourite_channels

One row per channel a person starred as a favourite, used for the guide’s Favourites filter. Deleted when the person un-stars it or the channel or person is deleted.

Kept: Kept until un-starred; goes with its channel or person (deleted when that is).

ColumnWhat it holdsType
user_idThe person, from users.TEXT · key · → users.id
channel_idThe starred channel, from channels.TEXT · key · → channels.id
added_at_msWhen the person starred it, in Unix milliseconds.BIGINT

user_recent_channels

One row per channel a person recently tuned to, for the guide’s Recent filter. Only each person’s 20 most recent channels are kept.

Kept: Each person’s 20 most recent channels are kept; rows go with their channel or person.

ColumnWhat it holdsType
user_idThe person, from users.TEXT · key · → users.id
channel_idThe channel tuned to, from channels.TEXT · key · → channels.id
tuned_at_msWhen the person last tuned to it, in Unix milliseconds.BIGINT

user_hidden_ideas

One row per channel idea a person hid on Home. Deleted when they undo the hide or the person is deleted.

Kept: Kept until the person undoes the hide; goes with the person.

ColumnWhat it holdsType
user_idThe person, from users.TEXT · key · → users.id
idea_idThe hidden idea’s stable name, such as genre:comedy. It isn’t a link to another table, so the hide survives the library changing.TEXT · key
hidden_at_msWhen the person hid it, in Unix milliseconds.BIGINT

viewings

The watch history: one row per person watching one programme on one channel on one device in the Loomarr apps, from when their player started to when it stopped. Only admins can read it, and the person isn’t told. Watching through the media server’s Live TV isn’t recorded.

Kept: Kept until an admin clears it, for one person or everyone; goes with the person.

ColumnWhat it holdsType
idIdentifies the row.TEXT · key
user_idThe person watching, from users.TEXT · → users.id
deviceWhat the household calls the device, such as a paired TV’s name or the browser and system.TEXT
channel_idThe channel watched. It isn’t a link to channels, so the history survives the channel being deleted.TEXT
channel_nameThe channel’s name when the viewing began.TEXT
programmeThe programme watched; a new row starts when the next programme does. Empty when the guide had nothing for it.TEXT
started_atWhen the player started, in Unix seconds.BIGINT
ended_atWhen the player was last seen, in Unix seconds.BIGINT

Channels and the guide

The channels Loomarr runs, what they have aired, and the holidays that decide what is in season for seasonal channels and seasonal filler. The requests that create and refine channels are under Suggestions.

channels

One row per channel. A channel is created by approving a channel request or by hand. Detaching a channel keeps its row with status detached; deleting it removes the row, its airing history and its channel feedback.

Kept: Kept until an admin deletes the channel; a detached channel keeps its row.

ColumnWhat it holdsType
idIdentifies the channel. Other tables point here.TEXT · key
nameThe channel’s display name.TEXT
numberThe channel number, which is its position in the guide. Unique.INTEGER
grpThe group the channel is listed under in the guide. Empty for none.TEXT
logoThe channel icon’s URL, either one of this Loomarr’s own image URLs or an outside link. Empty for no icon.TEXT
strategyHow programmes are ordered onto the timeline. sequential plays them in lineup order, shuffle rotates them in a shuffled order, and time_slot pins them to blocks of the day.TEXT
tunarr_idThe id of the channel’s copy in Tunarr, an outside playout server, kept from the first time Loomarr pushed the channel there. Empty for a channel that never went to Tunarr.TEXT
statusThe channel’s state. building (being set up), live (ready to play), drifted (a scheduled programme left the library and was replaced), empty (every lineup entry was filtered out, so nothing can air), paused (taken off air by an admin but kept), or detached (no longer managed by Loomarr).TEXT
shuffle_seedThe number a shuffled channel’s order is drawn from, so it repeats the same way. 0 means it is worked out from the channel id.BIGINT
desired_jsonThe last computed schedule, a list of slots. Each slot is a programme, a pending title not yet in the library, a filler break, or flexible padding, with its title, length and episode numbers.TEXT
reconcile_deadlineWhen the channel is next due to be checked and brought up to date, in Unix seconds. Moved forward while one check holds it. 0 means due now.BIGINT
updated_atWhen the row last changed, in Unix seconds.BIGINT
policy_jsonThe channel’s programming rules. What may air (era, genres), who it is for (the audience rating ceiling), how often things may repeat, the ordering, seasonal behaviour and time-of-day rules, plus the rules an admin pinned and the loosening the last check had to apply. {} means all defaults.TEXT
broadcast_codecThe video codec the channel streams in, h264 or hevc, chosen from what most of its titles are encoded in. Every programme and filler clip is converted to it.TEXT
revisionCounts changes to the row, starting at 1. A change is only saved if it was made from the latest revision, so two edits can’t overwrite each other.BIGINT
playout_anchorWhen the channel’s current broadcast cycle started, in Unix seconds. The schedule is laid out from this moment. 0 for a channel that has not gone on air yet.BIGINT
created_atWhen the channel was created, in Unix seconds. Never changes. 0 when unknown (a hand-made channel older than the column).BIGINT
intent_jsonThe approved channel request the channel was made from (description, era, tone, titles to include or leave out, the chosen name), without one-off refinements. A refine or a scheduled re-curation starts from it. Empty for a hand-made channel.TEXT · may be empty
requested_byThe person who asked for the channel (users.id). Set when the channel is created and never changed. Null when no member asked, or once that person is deleted.TEXT · may be empty · → users.id
from_requestWhether the channel was made from a member’s request rather than by hand. Refine and auto-curate re-run that request; it outlives the job intent_ref names.BOOLEAN
intent_refThe channel request job (proposal_jobs.id) the channel was created from, while that job is kept; null once the job is purged, and for a channel made by hand. No two channels share one.TEXT · may be empty · → proposal_jobs.id
dealt_fromTwo fingerprints, joined by a dot: what an operator had decided when the current cycle was dealt (the lineup, the channel’s rules, break and window settings), and the deal itself. While the first is unchanged, a change in the library does not move the window on air; a change to it re-deals the schedule after the programme on air. Empty until the channel is next scheduled.TEXT
playout_anchor_msThe milliseconds past playout_anchor’s second, 0 to 999. A cycle that starts at the programme on air rarely starts on a whole second, and the schedule is laid out from the exact moment.INTEGER

channel_lineup

A channel’s approved lineup, one row per title in order: what should play on it. Whether each title becomes a programme or waits as “coming soon” is decided when the channel is checked.

Kept: Rewritten whenever the channel is saved; goes with its channel.

ColumnWhat it holdsType
channel_idThe channel (a channels id).TEXT · key · → channels.id
positionWhere the title sits in the lineup, from 0.INTEGER · key
title_keyThe title (a titles key, such as movie:tmdb:603). A title added by hand that nothing has asked for yet has a pending titles row.TEXT · → titles.key
entryThe rest of the entry: its display name, known runtime, any season range or episode selection, the rating, genres and year the channel rules filter on, and its media-server box sets.TEXT

growth_reviews

The model’s verdict on a title for a channel that grows by itself: whether a title growth added, or one it held back as an unclear fit, belongs on the channel. One row per channel and title, asked once and asked again when the channel’s line changes.

Kept: Replaced when the title is reviewed again; goes with its channel.

ColumnWhat it holdsType
channel_idThe channel (a channels id).TEXT · key · → channels.id
title_keyThe title (a titles key, such as movie:tmdb:603).TEXT · key
lineThe channel’s line the verdict was given against, in growth’s own words.TEXT
verdictfits (an unclear title may join) or does_not_fit (a title growth added is taken off and declined).TEXT
reasonThe model’s few words for its verdict, shown with the title.TEXT
modelThe model that gave the verdict.TEXT
reviewed_atWhen the verdict was given (Unix seconds).INTEGER

airings

What a channel actually broadcast: one row each time an episode or film started playing on a channel. Written when the programme is really streamed, not when it is scheduled. The schedule uses it to prefer what hasn’t aired recently. Rows older than 8 days are pruned down to the latest airing of each episode or film; rows go when their channel is deleted.

Kept: Airings older than 8 days are pruned to the latest one per episode or film on each channel; goes with its channel (deleted when that is).

ColumnWhat it holdsType
channel_idThe channel it aired on (channels.id).TEXT · key · → channels.id
library_item_idThe library item id of the episode or film that aired.TEXT · key
aired_atWhen the programme started, in Unix seconds.BIGINT · key
recorded_atWhen Loomarr first recorded the airing, in Unix seconds.BIGINT
keyThe key of the lineup title it belongs to, such as series:tvdb:81189 for an episode of a show. Kept for diagnosis.TEXT

scheduled_airings

What a channel’s schedule put on air: one row each time an episode or film started on a channel’s timeline, whether or not anyone watched. Kept apart from airings, which records only what was actually streamed. The schedule reads both, so a channel nobody watches still rotates evenly instead of re-airing something it showed hours ago. Pruned and deleted like airings.

Kept: Rows older than 8 days are pruned to the latest one per episode or film on each channel; goes with its channel (deleted when that is).

ColumnWhat it holdsType
channel_idThe channel it aired on (channels.id).TEXT · key · → channels.id
library_item_idThe library item id of the episode or film.TEXT · key
aired_atWhen the programme started, in Unix seconds.BIGINT · key
recorded_atWhen Loomarr first recorded the airing, in Unix seconds.BIGINT
keyThe key of the lineup title it belongs to, such as series:tvdb:81189 for an episode of a show. Kept for diagnosis.TEXT

holidays

One row per holiday: when each country keeps it and the words that show a title or clip is about it. Seasonal channel rules and seasonal filler tags read them. Each holiday owns the seasonal tag with the same slug. Rows are added at startup from Loomarr’s shipped list and when a seasonal tag is made. An undated holiday is removed when its tag stops being seasonal.

Kept: Kept while its seasonal tag exists; an undated holiday is deleted when its tag stops being seasonal.

ColumnWhat it holdsType
idThe holiday’s slug, which is also the slug of its seasonal tag (taxa.slug).TEXT · key · → taxa.slug
labelThe holiday’s display name.TEXT
positionThe display order. It also settles ties when two holidays are in season at once.INTEGER
wordsJSON with the phrases that tie a title to the holiday. Words that name it in a channel request, its names, look-alike phrases that are not about it (such as “santa monica”), mood words, and genre sets that back up a mood word.TEXT
keptJSON list of when countries keep the holiday. Each entry lists countries, the day rule (fixed date, nth or last weekday, or relative to Easter) and the season window around it. [] means undated, so its clips air all year.TEXT
air_windowJSON rule for the time of year when a TV episode first airing then was made for the holiday. Empty when there is none.TEXT · may be empty
membersJSON list of holiday ids for a group holiday (such as “happy holidays”), which is in season whenever any member is. [] for an ordinary holiday.TEXT
name_needs_corroboration1 when the holiday’s name alone is not enough to tie a title to it, because the name is also an ordinary title word.BOOLEAN
updated_atWhen the row last changed, in Unix seconds.INTEGER

holiday_seed_revisions

One row per revision of Loomarr’s shipped holiday list that startup has applied. Each revision applies once, so a later change or removal by an admin is never undone. Kept forever.

Kept: Kept forever: one small row per shipped list revision, so no revision is applied twice.

ColumnWhat it holdsType
versionThe revision number of the shipped holiday list.INTEGER · key
applied_atWhen it was applied, in Unix seconds.INTEGER

Suggestions

How Loomarr suggests channels and titles: the requests people make, each run of the suggester, the suggestions waiting for an admin, the Keep and Never choices that steer future picks, and the counts and timings that measure how well the suggestion pipeline works.

proposal_jobs

One row per run of the channel suggester: a person’s channel request, a refine of an existing channel, or a scheduled re-curation. The job’s result is a proposal. Finished (done or failed) jobs are deleted after the jobs.retention setting (30 days by default), unless their proposal is still waiting for a decision.

Kept: Finished jobs are deleted after jobs.retention (30 days by default), unless their proposal still awaits a decision.

ColumnWhat it holdsType
idIdentifies the job. Proposals and channels point here.TEXT · key
kindsuggest for a request a person made (including a refine), or recurate for a scheduled refresh of a channel that allows automatic curation.TEXT
statusqueued (waiting for a worker), running, done (produced a proposal), or failed.TEXT
intent_jsonThe request: its description and options such as era, tone, titles to include or leave out, and for a refine the change asked for and the channel’s current lineup.TEXT
intent_hashA fingerprint of the normalised request. A new identical request can reuse a recent done job’s result instead of running again.TEXT
created_byThe person who submitted the request (users.id). Empty when no person did.TEXT
last_errorThe detailed error from the last failure, for the logs. Not shown to the requester.TEXT
deadlineWhen a queued job is due to run, or when a running job’s hold expires and another worker may take it over, in Unix seconds.BIGINT
attemptsHow many times a worker has started the job.INTEGER
created_atWhen the job was submitted, in Unix seconds.BIGINT
updated_atWhen the job last changed, in Unix seconds.BIGINT
failure_codeWhy the job failed, when it did. no_grounded_titles (nothing matched titles that exist), generation_failed (the AI step failed), selection_empty (no titles were chosen), or budget_exhausted (it ran out of its allowed work). Empty otherwise.TEXT
workflow_versionWhich version of the job process ran it. 0 is a job from before per-attempt history was kept; 1 is the current process, which records each attempt in proposal_job_attempts.INTEGER
reached_live1 once a channel made from this job has gone live. It stays 1 even if the channel is later paused.BOOLEAN
failure_trace_jsonThe bounded record of the candidate titles a failed run considered and why each was kept or dropped, used to explain the failure. Empty when there is none.TEXT · may be empty
channel_idThe existing channel a refine or re-curation job changes (channels.id); the job goes with that channel. Null for a job that creates a new channel.TEXT · may be empty · → channels.id

proposal_job_attempts

One row each time a worker started a channel suggester job, so a request’s history shows every try. Deleted with its job.

Kept: Goes with its job (deleted when that is).

ColumnWhat it holdsType
job_idThe job this attempt ran (proposal_jobs.id).TEXT · key · → proposal_jobs.id
attemptThe attempt’s number within its job, counting from 1.INTEGER · key
workflow_versionWhich version of the job process ran the attempt.INTEGER
statusrunning, succeeded, failed, or interrupted (the worker’s hold expired while it was still running and the job was taken over).TEXT
started_atWhen the attempt started, in Unix seconds.BIGINT
completed_atWhen the attempt ended, in Unix seconds. 0 while it is running.BIGINT
failure_codeWhy the attempt failed, using the same codes as proposal_jobs.failure_code. Empty otherwise.TEXT

proposals

A suggested channel lineup produced by a channel suggester job, waiting for an admin to approve or deny it (shown in the app as a request). Approving it creates or changes a channel.

Kept: Denied and superseded proposals are deleted after proposals.retention (90 days by default). Approved ones are kept on purpose, one per approval, as the record of who approved each channel; submitted ones wait for an answer.

ColumnWhat it holdsType
idIdentifies the proposal.TEXT · key
job_idThe job that produced it (proposal_jobs.id).TEXT
statussubmitted (waiting for a decision), approved, denied, or superseded (replaced by a newer proposal from the same job before anyone decided).TEXT
created_byThe person who asked for it (users.id).TEXT
decided_byThe admin who approved or denied it (users.id). Empty until decided.TEXT
deny_reasonThe reason given for a denial. Empty otherwise.TEXT
proposal_jsonThe suggestion itself. The request, the proposed channel name, the lineup of titles already in the library, titles to acquire, ranked backups, scores, the rationale and the channel rules.TEXT
created_atWhen the proposal was made, in Unix seconds.BIGINT
updated_atWhen the row last changed, in Unix seconds.BIGINT
mod_summaryWhat the admin changed before approving, written by Loomarr, such as “dropped 2, added 1”. Empty when approved unchanged.TEXT
noteThe admin’s message to the requester about the decision. Empty for none.TEXT
approved_atWhen the proposal was approved, in Unix seconds. 0 if it never was.BIGINT

discovery_feedback

The Keep, Less like this, Never and Surprise me choices people make about titles, for the whole household or for one channel. Each choice is a new row; the newest per title wins, and a channel’s choice overrides the household’s. The channel suggester and re-curation read them. A channel’s rows are deleted with the channel.

Kept: One row per title and scope: the nightly housekeeping task deletes a choice once a newer one replaces it, and a clear once nothing is left under it. A channel’s rows go with the channel.

ColumnWhat it holdsType
idIdentifies the choice.TEXT · key
actor_idThe person who made it (users.id), or api-token for a choice sent with the API token.TEXT
scopeWhere it applies. household for every channel, or channel for one channel.TEXT
scope_idThe channel it applies to (channels.id) when the scope is channel, and goes with that channel; null for household.TEXT · may be empty · → channels.id
target_keyThe title it is about, by its key, such as movie:tmdb:603.TEXT
actionkeep (favour this title), less (less like this, which also lowers titles of its genres), never (leave it out), surprise (Surprise me, favouring picks unlike it), or clear (remove an earlier choice).TEXT
reasonAn optional reason the person gave.TEXT
created_at_nsWhen the choice was made, in Unix nanoseconds.BIGINT

quality_receipts

One measured step of the pipeline that suggests and adds titles (retrieval, generation, grounding, approval, acquisition, scheduling) and how it went. Holds counts and timings only, no titles or people. Receipts older than 30 days are rolled into quality_daily_aggregates and deleted.

Kept: The nightly housekeeping task rolls receipts older than 30 days into daily totals and deletes them.

ColumnWhat it holdsType
idempotency_keyAn opaque hash identifying the step, so the same step is never counted twice.TEXT · key
occurred_atWhen the step happened, in Unix seconds.BIGINT
stageThe pipeline step. retrieval, generation, grounding, approval, acquisition or scheduling.TEXT
outcomeHow it went, from a set fixed per stage. retrieval is succeeded, empty or failed; generation is succeeded, abstained or failed; grounding is accepted or rejected; approval is approved or declined; acquisition is playable or failed; scheduling is scheduled or failed.TEXT
duration_msHow long the step took, in milliseconds. 0 when not measured.BIGINT
tool_callsHow many tool calls the model made during the step.INTEGER
candidate_countHow many candidate titles the step surfaced.INTEGER
cost_nanosWhat the step cost, in billionths of a US dollar.BIGINT
run_snapshot_idThe evaluation run it belongs to (quality_run_snapshots.id). Empty for ordinary use of the app.TEXT · may be empty · → quality_run_snapshots.id

quality_daily_aggregates

Daily totals of suggestion-quality receipts, one row per day, stage, outcome and evaluation run. Kept for two years. This is what the admin discovery-quality export downloads.

Kept: The nightly housekeeping task deletes days older than two years.

ColumnWhat it holdsType
dayThe UTC day, as YYYY-MM-DD.TEXT · key
stageThe pipeline step, with the same values as quality_receipts.stage.TEXT · key
outcomeHow the steps went, with the same values as quality_receipts.outcome.TEXT · key
run_snapshot_idThe evaluation run (quality_run_snapshots.id), or empty for ordinary use.TEXT · key
observation_countHow many receipts were rolled into the row.BIGINT
duration_msTotal time of those steps, in milliseconds.BIGINT
tool_callsTotal tool calls.BIGINT
candidate_countTotal candidate titles surfaced.BIGINT
cost_nanosTotal cost, in billionths of a US dollar.BIGINT

quality_run_snapshots

The fixed facts about one suggestion-quality evaluation run (the model, provider and test set it used), so receipts and daily totals can be compared across runs. No part of the running app writes rows today. Snapshots no longer referenced are trimmed to the newest 256.

Kept: The nightly housekeeping task keeps the newest 256 snapshots that nothing refers to any more.

ColumnWhat it holdsType
idIdentifies the snapshot, derived from its facts (eval-). Receipts and daily totals point here.TEXT · key
schema_versionVersion of the snapshot format.INTEGER
corpus_versionVersion of the test set the run was scored on.TEXT
requested_modelThe model the run asked for.TEXT
resolved_modelThe model that actually answered, when every call reported the same one. Empty otherwise.TEXT
providerThe AI provider. unknown, ollama, openrouter or custom.TEXT
budget_profileThe spending profile the run used.TEXT
application_versionThe Loomarr version (and commit) that ran it.TEXT
accounting_available1 when the provider reported costs for the run.BOOLEAN
created_atWhen the run was made, in Unix seconds.BIGINT

Your library

Loomarr scans the household’s media folders itself, matches each movie and show it finds to a TMDB title, and asks a person when it can’t tell (Needs you). These tables hold the folders, the files found in them, the titles they make up and their details, the household’s collections, the movies and shows requested through Seerr or the arr apps, and the artwork shown for all of them.

library_roots

One row per local folder Loomarr may walk for media. A folder is added when a media server or arr reports it or when someone types it in. A folder that is not in use, not typed by hand and named by no path mapping is dropped.

Kept: Kept while the folder is in use, typed by hand or named by a path mapping; dropped otherwise, when discovery runs or someone stops using a hand-typed folder.

ColumnWhat it holdsType
pathThe folder as Loomarr opens it, an absolute path.TEXT · key
in_use1 when the folder is confirmed for scanning. A discovered folder starts at 0; one typed by hand starts at 1.BOOLEAN
readable1 when the last check could open the folder.BOOLEAN
checked_at_msWhen the folder was last checked for readability, in Unix milliseconds; 0 means never.INTEGER
by_hand1 when someone typed the folder in rather than a media server or arr reporting it.BOOLEAN
added_at_msWhen the folder was added, in Unix milliseconds.INTEGER
scanned_at_msWhen a walk of the folder last completed, in Unix milliseconds; 0 means never.INTEGER
empty1 when the last check opened the folder and found nothing in it, which usually means the share behind a mount point isn’t mounted. Always 0 when the folder isn’t readable.BOOLEAN

library_root_paths

The path map. One row per folder a media server or arr reports, saying which local folder it is. Discovery rewrites rows unless someone set them by hand; a row the app stops reporting is dropped once that app answers without it.

Kept: Kept while the app keeps reporting the folder or someone set it by hand; dropped when a full discovery from that app no longer lists it.

ColumnWhat it holdsType
authorityThe app that reported the folder, one of emby, jellyfin, sonarr or radarr.TEXT · key
authority_pathThe folder as that app names it.TEXT · key
root_pathThe local folder it maps to (a library_roots path), or empty when no local folder was found for it.TEXT
by_hand1 when someone set the mapping by hand. Discovery never rewrites such a row.BOOLEAN
seen_at_msWhen the app last reported the folder, or the mapping was last set, in Unix milliseconds.INTEGER

library_files

One row per media file the scanner found under an in-use folder. A file the scanner no longer finds keeps its row with a missing time; all of a folder’s rows go when the folder is forgotten.

Kept: Kept while its folder is in use. A file a walk stopped finding is deleted by the nightly housekeeping 180 days later, unless its removal is still waiting for a person; every row of a folder is deleted when the folder stops being in use.

ColumnWhat it holdsType
pathThe file as Loomarr opens it, an absolute path.TEXT · key
rootThe folder it was found under (a library_roots path).TEXT
size_bytesThe file’s size in bytes at the last walk.INTEGER
modified_nsThe file’s modification time at the last walk, in Unix nanoseconds. With the size it marks the file’s revision, so an unchanged file is never opened again.INTEGER
identityA digest of what said what the file is (its arr record, ids in its name, its NFO sidecar) when it was last written to the library. Cleared to make the scanner write the file again.TEXT
kindWhat the file was last written as, one of movie, episode or extra; empty for a problem file.TEXT
headerWhether the file’s container header has been read. One of pending (not yet), read, or failed (it can’t be read, so the file can’t be played).TEXT
problemWhy the file is skipped, or empty for none. One of disc_image (a disc image), identity_conflict (what names the file disagrees), invalid_facts (the library refused what was read from it), broken_link (a symlink whose target is gone), link_outside (a symlink to a path outside the root, which the scan never follows), or empty_file (a video file with no bytes, which makes no title until it is filled).TEXT
held1 when a walk no longer found the file and that walk’s removals are waiting for a person to confirm them. A held file still counts as present.BOOLEAN
seen_at_msWhen a walk last found the file, in Unix milliseconds.INTEGER
missing_at_msWhen a walk stopped finding the file, in Unix milliseconds; 0 means it is present.INTEGER
inventory_item_idThe title the file was last written as (a library_items id); null until known.TEXT · may be empty · → library_items.id
inventory_series_idFor an episode, its show (a library_items id); null otherwise or until known.TEXT · may be empty · → library_items.id

library_items

One row per movie, show and episode the scanner wrote, with the details shown on the Library pages. Rewritten each time the title is written; a title whose files have gone keeps its row, so it finds its collections and answer again if it comes back. Whether it is present comes from library_files.

Kept: A title that leaves the library keeps its row, so it finds its details and collections again if it comes back. The nightly housekeeping deletes a row a year after it last changed once nothing is left of it: no importer report, no file, no episode, no collection, and no title, airing, channel or open request naming it. Its candidates and images go with it; a person’s answer for it is kept.

The title. What every list and search shows for a title.

ColumnWhat it holdsType
idIdentifies the title. library_files, collections and the rows below point here.TEXT · key
kindOne of movie, series or episode.TEXT
nameThe title’s name.TEXT
sort_nameThe lower-case name it sorts by.TEXT
yearThe year, or 0 when unknown.INTEGER
updated_at_msWhen the row was last written, in Unix milliseconds.INTEGER

Where it was found. One scan origin per title, which a person’s answer is kept against.

ColumnWhat it holdsType
origin_authorityThe scanned folder the title was found in, as scan: followed by a digest of the folder’s path. With origin_key it is the title’s scan origin, which a person’s answer is kept against.TEXT
origin_keyWhere in that folder the title was found, a digest of a movie’s file (file:…) or a show’s folder (series:dir:…).TEXT

Which title it is. The one match the library settled on and how. Candidates and answers that compete for it live in library_candidates and library_answers.

ColumnWhat it holdsType
catalogThe library_catalog row its details come from, written provider:kind:id (for example tmdb:movie:603); empty for none.TEXT
grounded1 when something gives the title an id (match_basis is not empty). A movie or show at 0 with no answer is listed in Needs you.BOOLEAN
searchedThe name, year and evidence the title was last searched for on TMDB to find candidates; empty when never searched. A title is searched again only when this would change.TEXT
match_basisWhat said which title it is. One of manager (an arr tracks it), embedded_id (an id in the file or folder name), sidecar (an NFO file), link (a person’s answer), or search (one TMDB result agreed); empty for nothing. Set for movies and shows only.TEXT
match_appThe app that said it, one of sonarr, radarr, nfo or tmdb; empty for evidence Loomarr finds itself.TEXT
match_pageThe title’s page in that app, for a person to open; empty when it has none.TEXT
needs_noticed1 once a daily Needs you notice has counted the title as new, so a restart neither repeats nor loses a notice.BOOLEAN
set_keyFor a movie, the library_catalog row of the TMDB collection it belongs to (for example tmdb_collection:collection:10); empty for none. Lets the Collections page say “you have 3 of 4”.TEXT

Details. The single value shown for each detail. Lists (genres, people, artwork) live in their own tables.

ColumnWhat it holdsType
original_titleThe title in its original language, or empty.TEXT
overviewThe description, or empty.TEXT
premiere_dateThe release or first-air date as the source gave it, such as 2013-05-01; empty when unknown.TEXT
ratingThe content rating, such as TV-PG; empty when unknown. An episode with no rating of its own takes its show’s.TEXT
scoreThe community score from 0 to 10; 0 when unknown.REAL
runtime_msThe running time in milliseconds; 0 when unknown.INTEGER
taglineThe one-line pitch printed on a poster; empty when unknown.TEXT
trailerThe web page that plays the title’s trailer; empty when it has none.TEXT

An episode’s place. An episode belongs to one show at one season and number; movies and shows leave these empty.

ColumnWhat it holdsType
seasonAn episode’s season number; 0 for movies and shows.INTEGER
episodeAn episode’s episode number; 0 for movies and shows.INTEGER
episode_endThe last episode number a multi-episode file covers; 0 for a single episode.INTEGER
series_authorityAn episode’s show, by the scan folder part of the show’s scan origin; empty otherwise.TEXT
series_keyAn episode’s show, by the folder part of the show’s scan origin; empty otherwise.TEXT
series_idAn episode’s show (a library_items id), filled once both the episode and the show are written; null otherwise.TEXT · may be empty · → library_items.id
unnumbered1 for an episode its show’s catalog could not number, because the catalog numbers the show in more than one order or its seasons end elsewhere than the disk’s, and nobody has said which order the show’s files follow. It keeps its file’s name and number, and its show is in Needs you. 0 otherwise.BOOLEAN

Which app supplied each detail. One source per detail above, so the detail page can say where each one came from.

ColumnWhat it holdsType
name_fromThe app that supplied the name (sonarr, radarr, nfo or tmdb); empty when unknown.TEXT
year_fromThe app that supplied the year; empty when unknown.TEXT
overview_fromThe app that supplied the description; empty when unknown.TEXT
premiere_fromThe app that supplied the premiere date; empty when unknown.TEXT
rating_fromThe app that supplied the rating, or show when an episode’s rating is its show’s; empty when unknown.TEXT
score_fromThe app that supplied the score; empty when unknown.TEXT
runtime_fromThe app that supplied the running time; empty when unknown.TEXT

library_ids

A title’s external ids, one row each. Replaced whenever the title is written; deleted with the title.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
namespaceWhich id it is, such as tmdb, tvdb or imdb.TEXT · key
valueThe id in that namespace.TEXT · key

library_genres

A title’s genres, in the order the source gave them. Replaced whenever the title is written; deleted with the title. An episode has none of its own; its show’s apply.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
nameThe genre.TEXT · key
positionWhere the genre came in the source’s list, from 0.INTEGER

library_studios

A title’s studio or network, in the order the source gave them. Replaced whenever the title is written; deleted with the title. An episode has none of its own; its show’s apply.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
nameThe studio or network.TEXT · key
positionWhere it came in the source’s list, from 0.INTEGER

library_countries

A title’s production countries, as the source names them, in the order it gave them. Replaced whenever the title is written; deleted with the title. An episode has none of its own; its show’s apply.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
nameThe country.TEXT · key
positionWhere it came in the source’s list, from 0.INTEGER

library_people

Everyone credited on a title in the library. A person with a TMDB id is one row wherever they are credited; one a source names without it is kept by name, unlinked. A person’s name and photo are the latest any title gave. A row outlives the titles that credited it.

Kept: Kept while some title credits the person; people no title credits are deleted on the next filmography pass.

ColumnWhat it holdsType
idThe person: : for a person a catalog knows (tmdb: for TMDB), name: for one kept by name.TEXT · key
nameTheir name.TEXT
external_idTheir id in that catalog; empty when unlinked.TEXT
photoThe web address of their headshot; empty when no source had one.TEXT
filmography_at_msWhen their filmography was last fetched, in Unix milliseconds; 0 means never.INTEGER
filmography_retry_at_msAfter a failed filmography fetch, the time before which it is not tried again, in Unix milliseconds; 0 for none.INTEGER
namespaceThe catalog that knows them, such as tmdb; empty when unlinked.TEXT

library_filmography

The movies and series of the people who directed or created a title, are among a title’s ten top-billed actors, or are credited on three or more titles, as the catalog that knows them lists them, newest first. Fetched when a person first qualifies and monthly after; deleted with the person. A title the library owns is found by its namespace and id in library_ids, with library_items.kind matching kind.

Kept: Replaced whenever the person’s filmography is fetched again; goes with its person (deleted once no title credits them).

ColumnWhat it holdsType
person_idThe person (a library_people id).TEXT · key · → library_people.id
positionWhere the title comes in their filmography, newest first, from 0.INTEGER · key
kindmovie or series.TEXT
external_idThe title’s id in that catalog.TEXT
titleThe title’s name, in the household’s language when the catalog has it.TEXT
yearThe year it came out; 0 when unknown.INTEGER
roleWhat they did on it, one of actor, creator, director or writer.TEXT
character_nameAn actor’s part; empty for crew or when unknown.TEXT
namespaceThe catalog the title’s id is from, such as tmdb.TEXT

library_credits

A title’s credits: its cast in billing order, then its key crew (creators, directors, writers). Replaced whenever the title is written; deleted with the title. An episode has none of its own; its show’s apply.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
positionWhere the credit came in the list, from 0.INTEGER · key
person_idThe person (a library_people id).TEXT · → library_people.id
roleWhat they did, one of actor, creator, director or writer (or what a media server calls it).TEXT
character_nameAn actor’s part; empty for crew or when unknown.TEXT

library_tags

A title’s tags (the source’s keywords), in the order the source gave them. Replaced whenever the title is written; deleted with the title.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
nameThe tag.TEXT · key
positionWhere the tag came in the source’s list, from 0.INTEGER

library_sources

A title’s present files, one row each, with the copy that plays marked. Replaced whenever the title is written; deleted with the title.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
pathThe file’s full path.TEXT · key
containerThe file format its header names, such as matroska; empty until the header is read.TEXT
size_bytesThe file’s size in bytes.INTEGER
bitrateThe file’s overall bitrate in bits per second; 0 when unknown.INTEGER
duration_msThe file’s length in milliseconds from its header; 0 until the header is read.INTEGER
editionWhich version of the title this file is, such as a director’s cut; empty for the usual one.TEXT
playsTrue for the one copy that plays when the title has several (the one its arr lists, else the sharpest, else the biggest), or for each part of a movie in parts, which play in part order.BOOLEAN
partWhich part of a movie split over several files (“cd1”, “cd2”) this file is, from 1; 0 for a whole file.INTEGER

library_streams

Each track of a title’s files (video, audio, subtitles), one row each: those inside the file and the subtitle and audio files beside it. Labels are worked out once so every reader uses the same words. Replaced whenever the title is written.

Kept: Replaced whenever the title is rescanned; goes with its file in library_sources.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_sources.item_id
pathThe file the track belongs to (a library_sources path).TEXT · key · → library_sources.path
stream_indexThe track’s position among all the file’s streams, as the header numbers it; an external track comes after the file’s own.INTEGER · key
kindvideo, audio or subtitle.TEXT
codecThe codec the header names, such as hevc or eac3.TEXT
profileThe codec profile, such as Main 10 or DTS-HD MA; empty when the header gives none.TEXT
titleThe track’s own title, if the file gives one.TEXT
is_defaultTrue when the file marks the track to play by default.BOOLEAN
forcedTrue for a forced subtitle track.BOOLEAN
languageThe track’s language as a two-letter code where one exists (en), else as the file gives it; empty when unknown.TEXT
language_nameThe language’s English name, such as English; empty when unknown.TEXT
channelsAn audio track’s channel count; 0 otherwise.INTEGER
channel_layoutAn audio track’s channel layout, such as 5.1(side).TEXT
sample_rateAn audio track’s sample rate in Hz.INTEGER
audio_formatAn audio track’s format in plain words, such as Dolby Digital Plus.TEXT
widthA video track’s width in pixels.INTEGER
heightA video track’s height in pixels.INTEGER
frame_rateA video track’s frame rate as the header gives it, such as 24000/1001.TEXT
pixel_formatA video track’s pixel format, such as yuv420p10le.TEXT
color_transferA video track’s transfer characteristic, such as smpte2084.TEXT
resolutionA video track’s resolution label, such as 1080p or 4K (reading the width too, so a wide film is not undersold).TEXT
hdrA video track’s dynamic range, such as Dolby Vision, HDR10 or HLG; empty for standard range.TEXT
commentaryTrue for a commentary track.BOOLEAN
hearing_impairedTrue for a track for the deaf and hard of hearing, such as SDH subtitles.BOOLEAN
descriptiveTrue for an audio track that describes the picture.BOOLEAN
external_refFor a track in its own file beside the title’s file (such as Film.en.forced.srt), where that file is, as an absolute path; empty for a track inside the file.TEXT
bitrateThe track’s own bitrate in bits per second, as its container states it (a Matroska BPS tag) or an MP4’s sample table measures it; 0 when the file says nothing.INTEGER
bit_depthBits per sample: a video track’s depth (8, 10 or 12), or a lossless or PCM audio track’s sample size; 0 for lossy audio or when unknown.INTEGER

library_chapters

A file’s chapter marks, as its container lists them, one row each in order. Replaced whenever the title is written.

Kept: Replaced whenever the title is rescanned; goes with its file in library_sources.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_sources.item_id
pathThe file the chapters are in (a library_sources path).TEXT · key · → library_sources.path
positionThe chapter’s order, from 0.INTEGER · key
start_msWhere the chapter starts, in milliseconds.INTEGER
end_msWhere it ends, in milliseconds; -1 when the container does not say.INTEGER
titleThe chapter’s name, if the file gives one.TEXT

library_markers

The spans an .edl file beside a title’s file marks, one row each in order. Replaced whenever the title is written.

Kept: Replaced whenever the title is rescanned; goes with its file in library_sources.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_sources.item_id
pathThe file the markers are for (a library_sources path).TEXT · key · → library_sources.path
positionThe span’s order in the .edl, from 0.INTEGER · key
start_msWhere the span starts, in milliseconds.INTEGER
end_msWhere it ends, in milliseconds.INTEGER
kindWhat the line asks for; cut (skip it), mute, scene (a scene marker) or commercial (a commercial break).TEXT

library_trickplay

The trickplay files beside a title’s file, one row each: seek thumbnails another media server made, such as Emby’s .bif. Only referenced, never imported. Replaced whenever the title is written.

Kept: Replaced whenever the title is rescanned; goes with its file in library_sources.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_sources.item_id
pathThe file the thumbnails are for (a library_sources path).TEXT · key · → library_sources.path
refWhere the trickplay file is, as an absolute path.TEXT · key
formatIts format; bif, a Roku BIF of thumbnails at a fixed interval.TEXT

library_art

A title’s artwork references, one row each. Replaced whenever the title is written; deleted with the title.

Kept: Replaced whenever the title is rescanned; goes with its title.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
kindWhat the image is: poster, fanart, clearlogo, banner, landscape, clearart, discart, thumb (an episode’s still), or season-poster for one season’s poster. Each source’s own names are mapped onto these.TEXT · key
seasonFor a season-poster, the season it belongs to (0 is the specials); 0 for every other kind.INTEGER · key
positionThe order among images of the same kind and season, from 0.INTEGER · key
refWhere the image is, a file beside the media or an address.TEXT

library_catalog

What TMDB says about a title or a TMDB collection, cached so a scan never has to ask it. A row is added when a title names it, and refreshed in the background.

Kept: Kept while a library title names it. One no title names is deleted by the nightly housekeeping a year after it was fetched, or after a year of failed fetches; one asked for and not yet tried is kept until it is.

ColumnWhat it holdsType
providerThe catalog, tmdb for a movie or show, tmdb_collection for a collection.TEXT · key
kindWhat the record is fetched as, one of movie, series or collection.TEXT · key
external_idThe id the catalog knows it by.TEXT · key
recordThe catalog’s record as JSON (names, description, ids, artwork and so on). Empty until fetched, and empty after the catalog said it has no such title.TEXT
fetched_at_msWhen the record was last fetched, in Unix milliseconds; 0 means never.INTEGER
retry_at_msAfter a failed fetch, the time before which it is not tried again, in Unix milliseconds; 0 for none.INTEGER
versionThe shape of record it was fetched in. A record of an older shape than its kind is read in now is fetched again first, as if it had never been fetched.INTEGER
languageThe language the record was fetched in, such as en-US; empty before languages were noted. A record in another language than the household’s is fetched again first.TEXT
changed1 when the catalog’s nightly change feed named the record after it was fetched; it is fetched again ahead of the refresh, which sets it back to 0.BOOLEAN

library_catalog_feeds

When each catalog’s change feed was last read, so the nightly read asks only for what changed since and picks up where it left off after a restart.

Kept: One row per catalog, kept and overwritten each night.

ColumnWhat it holdsType
providerThe catalog, such as tmdb.TEXT · key
read_at_msWhen the feed was last read through, in Unix milliseconds.INTEGER

library_candidates

The catalog search results for a movie or show Loomarr can’t identify, offered in Needs you. A title’s rows are replaced each time it is searched.

Kept: Replaced each time the title is searched again; a title’s last results are kept.

ColumnWhat it holdsType
inventory_item_idThe title searched for (a library_items id).TEXT · key · → library_items.id
rankThe result’s place in the list, from 0 for the best.INTEGER · key
kindOne of movie or series.TEXT
external_idThe result’s id in that catalog.TEXT
nameThe result’s name.TEXT
yearThe result’s year, or 0 when unknown.INTEGER
overviewThe result’s description, or empty.TEXT
posterThe result’s poster URL at its catalog, or empty.TEXT
name_match1 when the result’s name agrees with what is on disk.BOOLEAN
year_match1 when the result’s year agrees with what is on disk.BOOLEAN
length_match1 when the rest of what is on disk agrees: a movie’s running time, or a show’s seasons and episodes.BOOLEAN
namespaceThe catalog that found the result, such as tmdb.TEXT

library_answers

The answer for a movie or show nothing else identifies, either the catalog title it is or “not a title”. Kept against the title’s scan origin, so it outlives the title being rewritten; deleted when the answer is taken back.

Kept: Kept until someone takes the answer back, even after the title leaves the library.

ColumnWhat it holdsType
origin_authorityThe scanned folder of the title’s scan origin, as in library_items.TEXT · key
origin_keyWhere in that folder the title is, as in library_items.TEXT · key
kindOne of movie or series.TEXT
external_idThe title’s id in that catalog; empty when ignored.TEXT
ignored1 when a person said it is not a title.BOOLEAN
answered_at_msWhen the answer was given, in Unix milliseconds.INTEGER
answered_byWho gave it. you is a person, in Needs you or Wrong title?; search is Loomarr itself, when exactly one catalog result agreed on name, year and length. A person’s answer replaces a search answer.TEXT
namespaceThe catalog of the title it is linked to, such as tmdb; empty when ignored.TEXT

library_episode_orders

Which order a show’s episode files follow, as a person answered in Needs you, for a show whose catalog numbers it in more than one order or whose seasons end elsewhere than the disk’s. Kept against the show’s catalog row rather than in the catalog cache, which retention clears, so it outlives the records.

Kept: Kept until someone answers again for the show; one row per show.

ColumnWhat it holdsType
series_catalogThe show’s catalog row, as provider:series:id (library_items.catalog).TEXT · key
choiceOne of catalog (the catalog’s own seasons, as first aired), order (one of the catalog’s other orders, in order_id) or files (the name and number in each file).TEXT
order_idThe catalog’s id for the order picked; empty unless choice is order.TEXT
answered_at_msWhen the answer was given, in Unix milliseconds.INTEGER

library_collections

The household’s own collections, each an ordered list of titles. Made in Loomarr or brought over from a media server; deleted when someone deletes the collection.

Kept: Kept until someone deletes the collection.

ColumnWhat it holdsType
idIdentifies the collection. library_collection_members points here.TEXT · key
nameThe collection’s name.TEXT
descriptionThe collection’s description, or empty.TEXT
imported_fromThe media server it was brought over from, emby or jellyfin; empty when made in Loomarr.TEXT
created_at_msWhen the collection was made, in Unix milliseconds.INTEGER
updated_at_msWhen the collection was last changed, in Unix milliseconds.INTEGER
imported_idThe media server’s id for the collection it was brought over from; empty when made in Loomarr.TEXT

library_collection_members

The titles in a collection, in order. A member whose title has left the library is skipped when shown but kept, so the title returns to its place if it comes back.

Kept: Replaced whenever the collection is edited; goes with its collection (deleted when someone deletes it).

ColumnWhat it holdsType
collection_idThe collection (a library_collections id).TEXT · key · → library_collections.id
inventory_item_idThe title (a library_items id).TEXT · key · → library_items.id
positionThe title’s place in the collection, from 1.INTEGER

titles

One row per movie or show a channel wants that has gone through Seerr or the arr apps, tracking it from wanted to in the library (or given up on). A title added to a lineup by hand that nothing has asked for yet has a pending row, so every lineup entry names a row here.

Kept: A title in the library or given up on that no channel’s lineup and no open request names is deleted by the nightly housekeeping 180 days after it last changed; picked again, it is requested afresh. A title still on its way is kept. A pending row goes as soon as no lineup names it.

ColumnWhat it holdsType
keyIdentifies the title, as series:tvdb:id for a show with a TVDB id, otherwise movie:tmdb:id or series:tmdb:id.TEXT · key
title_jsonThe title as JSON, with its media type (movie or series), TMDB and TVDB ids, name, year and, for a show, the seasons wanted (none listed means all).TEXT
stateWhere it is. One of pending (on a lineup, but nothing has asked for it; read as no record), wanted (not yet accepted by Seerr or an arr), requested (accepted, waiting for a release), downloading (a release was grabbed), available (in the library), or unavailable (given up on).TEXT
library_idThe media server’s id for the title once it is in the library; empty before.TEXT
requested_atWhen Seerr or an arr first accepted the request, in Unix seconds; 0 means not yet.BIGINT
deadlineWhen a requested or downloading title is given up on, in Unix seconds; it is also pushed forward briefly while a check is working on the title. 0 for none.BIGINT
attemptsHow many times sending the request has failed.INTEGER
last_errorThe last error sending or tracking the request, or empty.TEXT
updated_atWhen the row last changed state, in Unix seconds.BIGINT
progressHow much of the download is done, from 0 to 1.REAL
eta_textThe download’s time left, as the arr words it; empty when unknown.TEXT
download_statusThe arr’s queue status, such as downloading, warning or stalled; empty when none.TEXT
arrived_atWhen the title arrived in the library after being requested, in Unix seconds. 0 when it never arrived or was already in the library when picked.BIGINT
episodes_haveFor a downloading show, how many wanted episodes are on disk; 0 otherwise.INTEGER
episodes_wantedFor a downloading show, how many episodes are wanted; 0 otherwise.INTEGER

images

One row per piece of artwork (posters, channel icons, clip stills and so on). The image bytes are files on disk named by their hash, never in the database. TMDB artwork’s files are deleted six months after it was fetched, as TMDB’s terms require, and the row waits to be fetched again.

Kept: Kept while something uses the image; the daily image clean-up deletes images nothing refers to, and a re-fetched image with new bytes replaces its old row.

ColumnWhat it holdsType
hashThe SHA-256 of the original bytes, in hex. Its identity and file name. Artwork not yet downloaded is keyed by a hash of its URL until the bytes arrive.TEXT · key
originWhere the bytes came from, which decides whether they can be got back after a restore. One of upload (a person uploaded it; not recoverable), remote (downloaded from TMDB or a media server; recoverable from source_url), extracted (made from local media, such as a clip still or a poster beside a file; recoverable), or generated (made by Loomarr; treated as not recoverable).TEXT
source_urlThe URL a remote image was downloaded from; empty for every other origin.TEXT
visibilityWho may fetch it. public needs no sign-in (a channel icon other apps fetch); member needs a signed-in person.TEXT
roleWhat the image is for, which sets its sizes and shape. One of poster, backdrop, icon, thumb (a clip still), watermark (a channel’s on-screen logo) or logo (a title’s clear logo, kept with its transparency, so it has no JPEG size).TEXT
mimeThe original’s file type, such as image/png.TEXT
widthThe original’s width in pixels.INTEGER
heightThe original’s height in pixels.INTEGER
bytesThe original’s size in bytes.INTEGER
animated1 when the original moves (a clip’s hover loop).BOOLEAN
placeholderA tiny blurred preview (a ThumbHash, base64) shown while the image loads.TEXT
dominant_hexThe image’s average colour, asTEXT
metaA JSON object of free-form notes about the image, such as attribution, licence or upstream ids. {} when there are none.TEXT
origin_fetched_atWhen a remote image’s bytes were downloaded, in Unix seconds; 0 means not downloaded yet. Drives the six-month expiry for TMDB artwork.INTEGER
created_atWhen the image was first stored, in Unix seconds.INTEGER
updated_atWhen the row last changed, in Unix seconds.INTEGER
last_used_atWhen the image was last used, in Unix seconds. Older images lose their resized copies first when space runs short.INTEGER
frame_countHow many frames the original has; 1 for a still image.INTEGER
duration_msAn animation’s length in milliseconds; 0 for a still image.INTEGER
loop_countHow many times an animation plays. Empty (NULL) for a still image, 0 for an endless loop.INTEGER · may be empty

clip_images

A clip’s images, such as its thumbnail. One of five tables saying what each image is used for, one per kind of owner, so each owner is a real reference; an image with no row in any of them is cleaned up.

Kept: Goes with its clip or its image; a clip re-keyed to new bytes keeps its rows.

ColumnWhat it holdsType
clip_hashThe clip (a clips hash).TEXT · key · → clips.hash
roleWhat the image is for, such as thumb.TEXT · key
image_hashThe image (an images hash).TEXT · key · → images.hash

filler_split_proposal_images

The stills a proposed split shows, one per cut.

Kept: Goes with its proposal or its image.

ColumnWhat it holdsType
proposal_idThe proposal (a filler_split_proposals id).TEXT · key · → filler_split_proposals.id
roleWhat the image is for, such as thumb.TEXT · key
image_hashThe image (an images hash).TEXT · key · → images.hash

channel_images

A channel’s icon and watermark.

Kept: Goes with its channel or its image; replaced when the icon or watermark changes.

ColumnWhat it holdsType
channel_idThe channel (a channels id).TEXT · key · → channels.id
roleWhat the image is for, icon or watermark.TEXT · key
image_hashThe image (an images hash).TEXT · key · → images.hash

library_item_images

A title’s poster, backdrop and logo, the one record of which image each is.

Kept: Goes with its title or its image; a kind replaces only its own role’s row.

ColumnWhat it holdsType
item_idThe title (a library_items id).TEXT · key · → library_items.id
roleWhat the image is for, one of poster, backdrop or logo.TEXT · key
image_hashThe image (an images hash).TEXT · key · → images.hash

library_candidate_images

The posters of what a search found for a title that Needs you.

Kept: Goes with its title or its image.

ColumnWhat it holdsType
item_idThe title the candidates are for (a library_items id).TEXT · key · → library_items.id
roleWhat the image is for, poster.TEXT · key
image_hashThe image (an images hash).TEXT · key · → images.hash

image_derivatives

One resized copy of an image in one format, stored as a file on disk. Made on demand or in the background; any row can be deleted to free space and costs only a re-encode. Deleted with the image.

Kept: Goes with its image; the least recently used copies are deleted when the image cache is over its size budget, and all of an image’s copies when its bytes expire.

ColumnWhat it holdsType
image_hashThe original image (an images hash).TEXT · key · → images.hash
recipeThe encoder settings it was made with, such as loomarr-rendition-v2. Changed settings make new copies rather than reusing old ones.TEXT · key
formatThe file format, one of avif, webp or jpeg.TEXT · key
widthThe copy’s width in pixels.INTEGER · key
bytesThe copy’s size in bytes.INTEGER
output_hashThe SHA-256 of the copy’s bytes, in hex; empty for copies made before it was recorded.TEXT
pathWhere the copy’s file is on disk.TEXT
animated1 when the copy keeps the original’s motion.BOOLEAN
created_atWhen the copy was made, in Unix seconds.INTEGER

Media inventory

Loomarr’s record of every movie, show and episode its importers have reported, and the files that play them. An importer is a library scan of one folder or an Emby or Jellyfin server. When two importers report the same title (they share an id such as a TMDB number), their reports join one item. The measurements Loomarr takes of each file are kept here too.

inventory_items

One row per movie, show or episode, however many importers report it. Created by the first report. Items are kept when an importer stops seeing them (the report is marked missing instead).

Kept: Deleted when its last report moves to another item, or by the nightly housekeeping once its last report was deleted for being missing 180 days.

ColumnWhat it holdsType
idIdentifies the item. Other inventory tables point here, and the library’s tables name it as their inventory item.TEXT · key
kindWhat the item is, as the importer names it, lowercased. The library scan writes movie, series or episode, matching the media server’s names for them.TEXT
created_atWhen the item was first reported, in Unix seconds.BIGINT
updated_atWhen a report last updated the item, in Unix seconds.BIGINT

inventory_item_origins

One importer’s latest report of one item. Replaced each time that importer reports the item again. Kept when the importer no longer sees it, with missing_at set.

Kept: Marked missing when the importer stops seeing it, and cleared if it comes back. The nightly housekeeping deletes it 180 days after it went missing, with its source reports.

ColumnWhat it holdsType
authority_idThe importer that reported it. scan: and a fingerprint of the scanned folder, or library:emby: or library:jellyfin: and a fingerprint of the server’s address.TEXT · key
external_item_idThe importer’s own id for the item, unique only within that importer. The media server’s item id, or for a library scan a fingerprint of the file’s path (file:) or the show’s folder (series:dir:).TEXT · key
item_idThe item the report belongs to (inventory_items.id).TEXT · → inventory_items.id
observation_jsonWhat the importer reported: names, overview, genres, tags, studios, people, year, season and episode numbers, premiere date, rating, score, runtime, artwork and related ids. A coverage map tells a field that is known to be empty apart from one that was not reported.TEXT
observed_atWhen this report was taken, in Unix seconds.BIGINT
last_seen_atWhen the importer last reported the item, in Unix seconds.BIGINT
missing_atWhen the importer stopped seeing the item, in Unix seconds. Empty while it is present. Cleared if the item comes back.BIGINT · may be empty

inventory_external_ids

The outside ids one importer’s report gives an item, such as its TMDB, TVDB or IMDb number. Matching ids are how reports of the same title from different importers join one item. Replaced with each new report; deleted with the report or the item.

Kept: Replaced with each new report; a report marked missing keeps its last ids.

ColumnWhat it holdsType
item_idThe item the id belongs to (inventory_items.id).TEXT · → inventory_items.id
authority_idThe importer whose report gave the id (part of inventory_item_origins).TEXT · key · → inventory_item_origins.authority_id
external_item_idThe importer’s id for the item (part of inventory_item_origins).TEXT · key · → inventory_item_origins.external_item_id
namespaceWhich id system the id comes from, lowercased, such as tmdb, tvdb or imdb.TEXT · key
external_idThe id within that system.TEXT · key

inventory_sources

One playable file (or media server version) of an item. Created by the first report that lists it; it moves with its report if the report joins another item.

Kept: Goes with its item. A source left with no report is deleted by the nightly housekeeping, with its measurement and analysis, once it is 180 days old.

ColumnWhat it holdsType
idIdentifies the source. Other inventory tables and playout point here.TEXT · key
item_idThe item the source plays (inventory_items.id).TEXT · → inventory_items.id
kindlocal_file for a file the library scan found, or library_original for a media server’s own copy of the item.TEXT
revisionIdentifies the exact bytes of the source. For a scanned file, its size and modified time (stat:); for a media server copy, its ETag (etag:) or a fingerprint of its details (observed:). When it changes, the source’s measurement and analysis are thrown away.TEXT
created_atWhen the source was first reported, in Unix seconds.BIGINT
updated_atWhen a report last updated the source, in Unix seconds.BIGINT

inventory_source_origins

One importer’s latest report of one source of an item. Replaced each time that importer reports it again. Kept when the importer no longer sees it, with missing_at set. Deleted with the source or the item’s report.

Kept: Marked missing when the importer stops seeing it. The nightly housekeeping deletes it 180 days after it went missing; it also goes with its source or its item’s report.

ColumnWhat it holdsType
authority_idThe importer that reported it (part of inventory_item_origins).TEXT · key · → inventory_item_origins.authority_id
external_item_idThe importer’s id for the item (part of inventory_item_origins).TEXT · key · → inventory_item_origins.external_item_id
external_source_idThe importer’s id for this source within the item. The media server’s source id, or file for a scanned file.TEXT · key
source_idThe source the report belongs to (inventory_sources.id).TEXT · → inventory_sources.id
locator_jsonWhere to find the source. A scanned file’s path, or the media server item and source ids (never a URL or credentials).TEXT
observation_jsonWhat the importer reported about the file. Container, size, bitrate, length, modified time and each video, audio and subtitle stream’s details, with a coverage map of what was reported.TEXT
observed_atWhen this report was taken, in Unix seconds.BIGINT
last_seen_atWhen the importer last reported the source, in Unix seconds.BIGINT
missing_atWhen the importer stopped seeing the source, in Unix seconds. Empty while it is present.BIGINT · may be empty

inventory_source_measurements

The stream details Loomarr measured itself by reading the file, for the source’s current revision. Written the first time the source plays or by the measurement job, and used instead of what an importer reported when it is newer. Deleted when the source’s revision changes or the source goes.

Kept: At most one row per source; thrown away when the source’s file changes.

ColumnWhat it holdsType
source_idThe source measured (inventory_sources.id).TEXT · key · → inventory_sources.id
source_revisionThe source revision that was measured.TEXT
observation_jsonThe measured details. Container, length, bitrate and each stream (codec, language, channels, size, frame rate, colour, HDR), with a coverage map that also says when the file has no audio.TEXT
observed_atWhen it was measured, in Unix seconds.BIGINT

inventory_source_analysis

Loomarr’s deeper analysis of a source’s current revision. The seek points, loudness, natural break points and the picture area. Written by the measurement job and read by playout and the schedule. Replaced on re-analysis; deleted when the revision changes or the source goes.

Kept: At most one row per source; replaced on re-analysis, dropped when the file changes.

ColumnWhat it holdsType
source_idThe source analysed (inventory_sources.id).TEXT · key · → inventory_sources.id
source_revisionThe source revision that was analysed.TEXT
schema_versionThe version of the analysis format. A row written by any other version is treated as not analysed, and an older one is analysed again.INTEGER
keyframesThe keyframe index (the points playback can start or cut at), packed as binary: each keyframe’s time in milliseconds and byte position.BLOB
keyframe_countHow many keyframes the index holds.INTEGER
integrated_lufsThe overall loudness of the audio, in LUFS. Empty when the source has no audio.DOUBLE PRECISION · may be empty
true_peak_dbtpThe loudest audio peak, in dBTP. Empty when the source has no audio or no peak was found.DOUBLE PRECISION · may be empty
breaks_jsonThe places an ad break could go without cutting into a scene. Each has its time, where it came from (chapter or fade), the nearest keyframe, how long the black and silent stretch lasted, and a confidence from 0 to 1.TEXT
analyzed_atWhen it was analysed, in Unix seconds.BIGINT
active_picture_jsonThe part of the frame that is picture, without letterbox or pillarbox bars, as {x, y, w, h} in source pixels. The channel logo is placed by its corner. Empty when unknown (no video, or every sampled frame was black).TEXT · may be empty

Filler clips

Filler is the short clips (vintage ads, station promos, bumpers, PSAs) that play in the breaks between shows. These tables remember the clip catalog and its tags, where clips come from and how they were downloaded, how each clip was prepared and checked, what aired, and the directory of TV stations a clip can name.

clips

One row per filler clip, identified by a hash of its bytes. The folder scan and intake add rows; a row goes away when its file is no longer in any clip folder, unless its recording was deliberately reclaimed (reaped_at), in which case the row stays. A clip’s category is not stored: it is the most specific product tag in clip_tags.

Kept: Kept while its file is in a clip folder; the folder scan deletes the rest, except reclaimed compilations, which stay. A copy merged into a better copy is deleted too.

The file. One file per clip, and every read of a clip needs it; the hash is the clip’s identity.

ColumnWhat it holdsType
hashThe clip’s identity: a 64-character hex hash sampled from its bytes. Two copies of the same file are one clip. Other filler tables point here.TEXT · key
pathWhere the file is, relative to the clip folder (for example a3/f9/.mp4). A location, not an identity.TEXT
tunarr_program_idThe Tunarr program id for this clip when Tunarr knows it; NULL on installs without Tunarr.TEXT · may be empty
duration_msLength of the clip in milliseconds, measured from the file.INTEGER
sourceWhere the clip came from: the id of the registered filler source that downloaded it (such as archive:), or filler-dir for a file dropped into the clip folder.TEXT
qualityResolution label such as 1080p or 480p, from the measured video height. Empty for an audio-only clip. Shown only; it does not affect selection by default.TEXT
licenseThe licence URL the source declared for the clip. Empty means unknown, never public domain.TEXT
updated_atWhen the row last changed, in Unix seconds.INTEGER
created_atWhen the clip was first added to the catalog, in Unix seconds.INTEGER

What it is. The facts selection and research read for every clip, one value each. Repeated facts (product tags, defects, research) already live in their own tables.

ColumnWhat it holdsType
nameDisplay name: a title from the source or its sidecar file, a name built from grounded evidence, one a person wrote, or the filename.TEXT
roleWhat the clip is: unclassified (not yet known), commercial, bumper, station_id, psa, trailer, interstitial, political or news.TEXT
eraThe year the clip is from, such as 1994; a year ending in 0 means the decade. 0 means not known.INTEGER
audienceWho the clip suits: kids, family, general or late_night. Empty when not yet tagged.TEXT
languageSpoken language detected in the clip, as a language code such as en. Empty means not checked yet; none means checked and there is no speech.TEXT
transcriptThe clip’s spoken words, once transcribed. Empty when not transcribed or wordless.TEXT
brandThe advertiser the clip is for, such as a cereal or car maker. Kept only when the name appears in the clip’s text, transcript or on-screen text; empty otherwise.TEXT
visible_textText a vision model read off the clip’s frames, such as a logo, product name or year. Brands and eras from vision are kept only when supported by this text.TEXT
enrichment_revisionA counter that goes up when the clip’s name, role, source, transcript or on-screen text changes. Research and tagging results record the revision they were made from, so a change makes them run again.INTEGER
name_sourceWhere the name came from: source (a title inside the media), sidecar (the downloaded info file), grounded (built from evidence such as the brand), curated (written by a person), filename (never shown to viewers), or unknown.TEXT

Where and when it aired. One place and date per clip, filtered on together with the clip when channels pick filler for their region.

ColumnWhat it holdsType
geographic_scopeHow widely the clip aired: national, local (one station’s own material) or unknown.TEXT
countryThe country the clip aired in, as a two-letter ISO code. Empty when not known.TEXT
networkThe broadcast network the clip is tied to, when known from evidence.TEXT
stationThe broadcast station the clip belongs to, as an id in Loomarr’s station directory. A local clip is local to this station.TEXT
air_dateThe date the clip was broadcast, as YYYY-MM-DD, when known from evidence.TEXT
geo_evidenceThe source text that supports the clip’s geography, or operator when a person set it.TEXT

Whether it can air. Every read of the pool checks these first, so they stay beside the clip rather than behind a join.

ColumnWhat it holdsType
removed_atWhen the clip was removed from the catalog, in Unix seconds; 0 means present. The file is kept; a removed clip is not listed or aired.INTEGER
held1 while the clip is recorded but not yet playable: every new clip starts held and is released when its preparation finishes and it is ready.BOOLEAN
reaped_atWhen the clip’s file was deleted on purpose while its row was kept, in Unix seconds: a composite whose recording was reclaimed after splitting, or a copy retired for failing the defect check. NULL means the file is still there.INTEGER · may be empty
placementWhere a ready clip may be scheduled: break_body (inside a break), bookend (opening or closing a break) or not_playable. Set when the clip becomes ready.TEXT

Composites. A clip cut from a longer recording names that one recording; a recording names none.

ColumnWhat it holdsType
is_composite1 when the file is a recorded break holding many ads. A composite never airs itself; the clips split out of it do.BOOLEAN
parent_hashThe composite clip this clip was split out of. Empty for a clip that was not split from one.TEXT

Rotation totals. Running totals rotation reads with the pool. Each airing itself is kept in filler_airings.

ColumnWhat it holdsType
play_countHow many times Loomarr’s own playout has aired the clip. Channels played through Tunarr never report plays, so their clips stay at 0.INTEGER
last_played_atWhen Loomarr’s playout last aired the clip, in Unix seconds; 0 means never.INTEGER

Pictures. One still and one hover preview per clip, shown wherever the clip is listed.

ColumnWhat it holdsType
thumbnailStill image file, relative to the thumbnail cache folder. Empty when not generated yet.TEXT
previewShort animated preview shown on hover, relative to the preview cache folder. Empty when not generated yet.TEXT
thumb_image_hashThe image service’s id for the still image. Empty when not stored there yet.TEXT
hover_image_hashThe image service’s id for the hover preview. Empty when not stored there yet.TEXT

clip_tags

Each tag a clip carries from the taxonomy (taxa). Rows are rewritten whenever a clip is tagged or the taxonomy changes.

Kept: Rewritten on each retag and removed with a deleted tag or a merged copy. Tags of a clip the folder scan removed are deleted by the nightly housekeeping, unless the catalog is empty (an unmounted drive).

ColumnWhat it holdsType
clip_hashThe clip that carries the tag (clips.hash).TEXT · key
taxonThe tag (taxa.slug).TEXT · key
leaf1 when the tag was set directly on the clip by a person or the tagger. 0 when it was added automatically because the clip carries a more specific tag beneath it (beer adds alcohol and drinks).BOOLEAN

taxa

The tag vocabulary for clips, one row per tag, arranged as trees on separate axes. Loomarr seeds it on first start and adds new shipped tags over time; operators can edit and delete tags.

Kept: Kept until an operator deletes the tag.

ColumnWhat it holdsType
slugThe tag’s stable id, such as beer. Tag rows elsewhere point here.TEXT · key
labelThe tag’s display name.TEXT
parentThe slug of the broader tag above this one on the same axis. Empty for a top-level tag.TEXT
axisWhich kind of tag it is: product (what the clip sells), format (what the clip is, such as a PSA or trailer), seasonal (when it belongs, such as Christmas), audience-cue (a hint about who it suits) or presentation (such as animated or live action).TEXT
synonymsJSON array of other words that mean this tag, so a tagger saying lager is read as beer.TEXT
aliasesJSON array of former slugs of this tag, so clips tagged under an old name still match after a rename.TEXT
updated_atWhen the tag last changed, in Unix seconds.INTEGER

taxa_closure

Every ancestor and descendant pair in the tag trees, including each tag paired with itself. Rebuilt from taxa whenever the trees change, so “everything under drinks” is one lookup.

Kept: Cleared and rebuilt in full from taxa whenever the tag trees change.

ColumnWhat it holdsType
ancestorThe broader tag (taxa.slug).TEXT · key
descendantA tag at or below it (taxa.slug).TEXT · key

taxonomy_seed_revisions

Which shipped batches of new tags this install has already received. Each batch is applied once, so a tag the operator later deletes is not added back.

Kept: Kept forever, so a shipped tag the operator deleted never comes back.

ColumnWhat it holdsType
versionThe shipped tag batch’s number.INTEGER · key
applied_atWhen the batch was applied, in Unix seconds.INTEGER

filler_sources

The remote collections clips are downloaded from: archive.org collections and YouTube channels or playlists, added by an operator or offered from Loomarr’s shipped list. A row stays until the operator deletes it; switching it off keeps its history.

Kept: Kept until an operator deletes the source.

ColumnWhat it holdsType
idThe source’s stable id, separate from its address so re-pointing a source keeps its history.TEXT · key
kindThe provider, archive (archive.org) or youtube.TEXT
uriThe archive.org identifier or URL, or the YouTube channel or playlist URL.TEXT
labelThe source’s display name; when empty the address is shown instead.TEXT
licenseThe licence URL the source declares. Empty means unknown, never public domain.TEXT
last_fetched_atWhen the source last had at least one item queued for download, in Unix seconds; 0 means never.INTEGER
created_atWhen the source was added, in Unix seconds.INTEGER
enabled1 when the source is switched on. A switched-off source is not checked, searched or downloaded from.BOOLEAN
fetch_every_secondsHow often to check this source for new clips, in seconds. NULL uses the global setting; 0 means never check it automatically.INTEGER · may be empty
fetch_max_per_runThe most items to download from this source in one check. NULL uses the global setting.INTEGER · may be empty
countryThe country the source serves, as a two-letter ISO code. Empty means it follows the install’s own location.TEXT
last_checked_atWhen the last check of the source finished, even if it found nothing new, in Unix seconds; 0 means never.INTEGER
check_failure_countHow many checks in a row have failed since the last success. Sets how long to wait before retrying.INTEGER
check_retry_atEarliest time to retry after a failed check, in Unix seconds; 0 means no wait.INTEGER
check_lease_untilWhile a check is running, the time its claim on the source expires, in Unix seconds; 0 means no check is running.INTEGER
scan_cursorThe provider’s id for the last item examined in the sweep in progress, so a check resumes where the previous one stopped. Empty when no sweep is in progress.TEXT
scan_watermarkThe provider’s id for the newest item seen by the last completed sweep.TEXT
scan_pending_watermarkThe provider’s id for the newest item seen when the sweep in progress began. It becomes scan_watermark when that sweep completes.TEXT

filler_providers

One on/off switch per provider. A remote source downloads only when both it and its provider are switched on.

Kept: Kept forever; there is one fixed row per provider.

ColumnWhat it holdsType
kindThe provider, archive or youtube.TEXT · key
enabled1 when the provider is switched on.BOOLEAN

filler_source_check_outcomes

Counts of what source checks found, per source, for the sweep in progress or the last completed one. Cleared when a new sweep begins; deleted with the source.

Kept: Replaced by each new check of the source; goes with its source (deleted when that is).

ColumnWhat it holdsType
source_idThe source checked (filler_sources.id).TEXT · key · → filler_sources.id
dispositionWhat happened to the items: queued (sent for download), already_known, too_short, too_long, live (a live stream), upcoming (not yet available), private, unavailable or metadata_incomplete.TEXT · key
item_countHow many items had that outcome.INTEGER

filler_pulls

A proposed batch of clips to download, which an operator approves or dismisses. Decided pulls are kept as history of what was agreed to.

Kept: Pending pulls are kept. The nightly housekeeping task deletes a dismissed pull 180 days after it was dismissed, and an approved one with its download run.

ColumnWhat it holdsType
idIdentifies the pull.TEXT · key
titleA short summary of what the pull is for.TEXT
reasonWhy Loomarr proposed it, such as the coverage gap it fills. Shown above the plan.TEXT
proposed_byWho or what proposed it, such as a user id or a job name.TEXT
statuspending, approved or dismissed.TEXT
noteThe operator’s note narrowing the pull, given at approval.TEXT
plan_jsonJSON document of the plan: what was asked for, the items selected (each with its source, address, estimate and why it was chosen, including any the operator dropped), the candidates turned down and why, and how each source was treated.TEXT
created_atWhen the pull was proposed, in Unix seconds.INTEGER
decided_atWhen it was approved or dismissed, in Unix seconds; 0 while pending.INTEGER
decided_byWho approved or dismissed it. Empty while pending.TEXT

filler_pull_commits

Links an approved pull to the one download run its approval started. Written in the same step as the approval.

Kept: One per approved pull; deleted with its download run.

ColumnWhat it holdsType
pull_idThe approved pull (filler_pulls.id).TEXT · key · → filler_pulls.id
acquisition_idThe download run it started (filler_acquisition_runs.id).TEXT · → filler_acquisition_runs.id

filler_acquisition_runs

One row per download run: a source check, an approved pull or URLs an admin typed in. Shows what was asked for and how the downloads went. Runs left unfinished by a restart are marked as errors.

Kept: The nightly housekeeping task deletes a finished run 90 days after it finished once nothing points at it: no downloaded file and no clip pipeline row. A run whose files are kept is kept.

ColumnWhat it holdsType
idIdentifies the run. Downloaded files and prepared clips point here.TEXT · key
triggerWhat started it: source (a registered source’s check), pull (an approved pull) or manual (URLs typed by an admin). scheduled is also allowed.TEXT
source_idThe registered source every item came from (filler_sources.id). Empty when items came from several sources or none.TEXT
pull_idThe approved pull that started it (filler_pulls.id). Empty for other triggers.TEXT
statusqueued, running, success or error.TEXT
requestedHow many items were asked for.INTEGER
fetchedHow many files were downloaded.INTEGER
skippedHow many items were skipped, mostly because they were already downloaded.INTEGER
failedHow many items failed to download.INTEGER
empty_countHow many items returned no clips and no error, usually a mistyped id or an empty source.INTEGER
errorThe error that ended the run. Empty when none.TEXT
started_atWhen the run started, in Unix seconds.INTEGER
completed_atWhen the run finished, in Unix seconds; 0 while still going.INTEGER
updated_atWhen the row last changed, in Unix seconds.INTEGER

filler_acquisition_artifacts

One row per file a download produced, tracking it from download to the clip folder to the catalog. Rows that need repair are kept until someone deals with them.

Kept: A file with the provider’s id is kept on purpose: it is how the fetcher knows it already has that item, one row per item ever downloaded. One without, catalogued into a clip that has since left, is deleted by the nightly housekeeping task after 90 days. Staged, published and repair files are kept.

ColumnWhat it holdsType
idIdentifies the downloaded file.TEXT · key
acquisition_idThe download run that produced it (filler_acquisition_runs.id).TEXT · → filler_acquisition_runs.id
source_idThe registered source it came from (filler_sources.id). Empty for a typed URL.TEXT
providerThe provider that downloaded it, such as archive or youtube.TEXT
source_urlThe address that was downloaded.TEXT
staging_pathWhere the file was put while it was being checked, relative to the clip folder. Empty when not staged.TEXT
media_pathWhere the media file is published, relative to the clip folder.TEXT
sidecar_pathThe info file downloaded beside the media, relative to the clip folder. Empty when there is none.TEXT
media_sha256The SHA-256 hash of the whole file, in hex.TEXT
media_bytesThe file’s size in bytes.INTEGER
clip_hashThe clip it became (clips.hash). Empty until it is catalogued.TEXT
statestaged (downloaded, not yet in the clip folder), published (in the clip folder), consumed (catalogued as a clip) or repair (held back for a person to fix).TEXT
repair_reasonWhy the file needs repair. Empty otherwise.TEXT
completed_atWhen the download finished, in Unix seconds.INTEGER
updated_atWhen the row last changed, in Unix seconds.INTEGER
provider_archive_entryThe line the downloader records so it never downloads this item again. Empty when the provider gives none.TEXT
provider_archive_committed1 once that line has been saved to the downloader’s record.BOOLEAN
remote_idThe provider’s id for the item. Empty for older downloads and typed URLs.TEXT
gapThe channel coverage gap the download was for, such as era:1990-1999. Empty when it was not chosen for a gap.TEXT

filler_download_failures

One row per item whose download has not worked yet. A failed download is tried three times over a day, then rests 30 days; an item that waits for room is kept here too, with no failure counted. The row has what a retry needs, so an item no source listing offers any more is still retried.

Kept: Deleted when the item downloads. The nightly housekeeping task also deletes the rows of a source that was deleted. An item that keeps failing stays, tried once every 30 days.

ColumnWhat it holdsType
item_keyIdentifies the item: the provider’s id for it within its source, or its address for a typed URL.TEXT · key
providerThe provider that downloads it, such as archive or youtube.TEXT
source_idThe registered source it came from (filler_sources.id). Empty for a typed URL.TEXT
remote_idThe provider’s id for the item. Empty for a typed URL.TEXT
urlThe address to download.TEXT
duration_msThe length the listing gave, in milliseconds; 0 when it gave none.INTEGER
heightThe picture height the listing gave, in pixels; 0 when it gave none.INTEGER
gapThe channel coverage gap the download was for, such as era:1990-1999. Empty when it was not chosen for a gap.TEXT
failuresHow many attempts have failed. Waiting for room does not count.INTEGER
first_failed_atWhen the first attempt failed, in Unix seconds; 0 if none has.INTEGER
last_failed_atWhen the latest attempt failed, in Unix seconds; 0 if none has.INTEGER
retry_atThe earliest the item is tried again, in Unix seconds.INTEGER
last_errorWhy the latest attempt failed or waited.TEXT

filler_clip_pipeline

Each clip’s preparation: which step it is at, how that step is going, and the clip’s outcome (ready, rejected, waiting for review). One row per clip, deleted with the clip.

Kept: Kept while the clip exists; removed when the folder scan drops it or it is merged.

ColumnWhat it holdsType
clip_hashThe clip being prepared (clips.hash).TEXT · key
stageThe step the clip is at, in order: probe, transcode, split, screen, defects, language, transcribe, vision, finish.TEXT
statusHow the current step is going: queued, running, done, failed (retried after a wait) or skipped (did not apply).TEXT
progressPercent done within the current step, 0 to 100.INTEGER
dispositionThe clip’s outcome: running, review (waiting for a person), ready, complete (a composite that has been split), rejected (refused by a check) or dismissed (a person said no).TEXT
reject_reasonWhy the clip was rejected, as a code: unprobeable, too_short, no_audio, no_video, black_content, silent_content, too_quiet, unplayable, defect, language, duplicate, screening, sliver, undecided or unidentified. Empty when not rejected.TEXT
reject_detailThe measurement behind the rejection, such as a duration under the minimum or the language heard.TEXT
attemptsHow many times the current step has been tried. Resets when the clip moves on.INTEGER
next_runWhen the row may next be worked on, in Unix seconds; 0 means now.INTEGER
stages_jsonJSON array of the finished steps, each with its status, an explanatory note for a skip or failure, attempts, and start and finish times.TEXT
enrolled_atWhen the clip entered preparation, in Unix seconds.INTEGER
updated_atWhen the row last changed, in Unix seconds.INTEGER
force_run1 when a person asked to re-run the current step, so it runs even if it would normally be skipped. Cleared once the step runs.BOOLEAN
acquisition_idThe download run that brought the clip in (filler_acquisition_runs.id). Empty for clips dropped into the folder by hand.TEXT
preparation_attemptWhich attempt at preparing the clip this is, counting from 1. 0 for clips enrolled before this was tracked.INTEGER
preparation_started_atWhen the current preparation attempt began, in Unix seconds.INTEGER
preparation_start_reasonWhy the current attempt began: enrollment (the clip was new) or restart.TEXT
preparation_progressPercent done across the whole preparation attempt; -1 means unknown.INTEGER
stage_queued_atWhen the current step was queued, in Unix seconds.INTEGER
stage_started_atWhen the current step started running, in Unix seconds.INTEGER
files_deleted_atWhen Loomarr deleted the files of a clip it fetched and rejected a week earlier, in Unix seconds; the row stays as the record of why. 0 while the files are kept.INTEGER

filler_clip_variants

Pairs of clips that are different versions of the same ad, such as a cut-down or another ending. A break never carries two versions, and airing one rests the others.

Kept: Moved to the better copy when a clip is merged. A pair whose clips have both left the catalog is deleted by the nightly housekeeping, unless the catalog is empty (an unmounted drive).

ColumnWhat it holdsType
a_hashOne clip of the pair (clips.hash); always the smaller hash.TEXT · key
b_hashThe other clip of the pair (clips.hash).TEXT · key

filler_clip_fingerprints

A saved picture-and-sound fingerprint of each clip, used to spot copies and versions of the same ad. Recomputed when the fingerprint method changes; removed when the clip is.

Kept: Kept while the clip exists; removed when the folder scan drops it or it is merged.

ColumnWhat it holdsType
clip_hashThe clip fingerprinted (clips.hash).TEXT · key
algorithmThe fingerprint method and its version.TEXT · key
fingerprint_jsonJSON document with the clip’s duration and the hashes of its sampled video frames and audio.TEXT

filler_clip_defects

Each clip’s latest picture and sound defect check, against a quality bar set by the clip’s era, and the verdict it earned. One row per clip, replaced on each new measurement.

Kept: Kept while the clip exists; removed when the folder scan drops it or it is merged.

ColumnWhat it holdsType
clip_hashThe clip checked (clips.hash).TEXT · key
measurement_versionThe version of the measuring method. Clips measured under an older one are measured again.TEXT
floor_versionThe version of the quality bars the clip was judged against.TEXT
eraThe clip era the verdict was judged against.INTEGER
outcomeWhat the detectors decided: pass, fail, borderline (between the bars) or undecided (too little picture to judge).TEXT
readings_jsonJSON document of the raw measurements, such as blockiness, blur, dropouts, interlacing, upscaling and audio clipping.TEXT
assessment_jsonJSON document of the era bar used and each check’s outcome, measured value and bar.TEXT
measured_atWhen the clip was measured, in Unix seconds.INTEGER
judgement_jsonJSON document of a vision model’s ruling on a borderline clip and its reason. Empty until there is one.TEXT · may be empty
judge_failuresHow many times asking the vision model brought back no ruling since the last one.INTEGER
verdictThe final standing: pass, fail, undecided (too little picture; allowed to air) or pending (a borderline clip waiting for a ruling).TEXT
verdict_reasonWhat decided the verdict, such as the detectors or the judge’s reason.TEXT
labelA maintainer’s own pass or fail label for the clip, used to tune the bars. Empty when unlabelled.TEXT
label_eraThe clip era the label was given against; 0 when unlabelled.INTEGER
labelled_atWhen the label was given, in Unix seconds; 0 when unlabelled.INTEGER

filler_inferior_copies

Copies of an ad that lost to a better copy of the same ad and were merged into it. Kept so the same file is never catalogued again.

Kept: Kept forever, so a copy merged away is never catalogued again.

ColumnWhat it holdsType
clip_hashThe losing copy’s former clip hash.TEXT · key
source_hashThe hash of the file the losing copy came from, so the same file offered again is skipped. Equal to clip_hash when it was not re-encoded.TEXT
survivor_hashThe copy it lost to (clips.hash).TEXT
decided_atWhen the copies were merged, in Unix seconds.INTEGER

filler_ready_events

A record written when a clip becomes ready to air, at the same moment it joins the playable catalog. One per clip.

Kept: One per clip, kept while the clip exists; the nightly housekeeping task deletes it 30 days after it was written once its clip has left the catalog.

ColumnWhat it holdsType
idIdentifies the event, as ready:.TEXT · key
clip_hashThe clip that became ready (clips.hash).TEXT
acquisition_idThe download run that brought the clip in (filler_acquisition_runs.id). Empty for clips from a folder.TEXT
enrollment_kindWhat let the clip into filler: acquisition (a download run) or source (a clip folder or source the operator chose).TEXT
enrollment_refThe download run id or source id behind enrollment_kind.TEXT
placementWhere it may be scheduled, break_body or bookend.TEXT
created_atWhen the clip became ready, in Unix seconds.INTEGER

filler_curated_offers

Which entries from Loomarr’s shipped list of curated sources this install has been offered. An offer adds the source switched off, and the offer is kept after the source is deleted so a deleted source stays deleted.

Kept: Kept forever, so a curated source the operator deleted stays deleted.

ColumnWhat it holdsType
source_idThe offered source (filler_sources.id).TEXT · key
list_versionThe version of the shipped list that first offered it.TEXT
offered_atWhen it was offered, in Unix seconds.INTEGER
stationsComma-separated station ids the shipped list ties the source to, refreshed on every offer.TEXT
local_stateWhat Loomarr did about nearby stations: empty (nothing), on (switched it on for a nearby place), operator (it was already on, so it is left to the operator) or off (the operator switched it back off, so it stays off).TEXT

filler_airings

Each time a clip aired on a channel, used to rest clips between airings across the household. Airings from the last eight weeks are kept, plus each clip’s newest few beyond that.

Kept: Trimmed each time a clip airs to its last eight weeks plus its newest four airings. Once the clip has left the catalog, its airings older than eight weeks are deleted by the nightly housekeeping, unless the catalog is empty (an unmounted drive).

ColumnWhat it holdsType
channel_idThe channel it aired on.TEXT · key
clip_hashThe clip that aired (clips.hash).TEXT · key
aired_at_msWhen the clip was scheduled to start, in Unix milliseconds.INTEGER · key
recorded_at_msWhen playout recorded the airing, in Unix milliseconds.INTEGER

filler_breaks

Each commercial break Loomarr has planned for a channel. Reconcile assembles a break once, ahead of time, and cuts the break’s slot to the media its pod holds, so the programme resumes when the break’s own clips end. The guide, the previews and the stream read the stored pod, so what the guide shows is what airs.

Kept: Reconcile keeps the breaks of the window on air and deletes a channel’s other breaks, including those behind the playout anchor and those whose start moved. A channel’s breaks go with it.

ColumnWhat it holdsType
channel_idThe channel the break is on (channels.id).TEXT · key · → channels.id
start_msWhen the break starts on the channel’s timeline, in Unix milliseconds.INTEGER · key
ceiling_msThe slot length the pod was assembled to, in milliseconds.INTEGER
length_msThe media the pod holds, in milliseconds. The break slot is this long once planned.INTEGER
pod_jsonThe break’s clips in order, as the pod assembler returned them.TEXT
planned_atWhen the break was planned, in Unix seconds.INTEGER

filler_context_reports

Suggestions about a clip’s year, country or station found by researching it online, with the pages cited, plus Loomarr’s own uncited era guesses. Only a confident, cited geography finding updates the clip; the rest are shown as suggestions.

Kept: The nightly housekeeping task deletes a report once a newer one of the same kind (cited, or a guess) exists for an older revision of the clip, and every report 30 days after it was written once the clip has left the catalog.

ColumnWhat it holdsType
clip_hashThe clip researched (clips.hash).TEXT · key
producerWhat made the suggestion: the research model (context-model:), or observation for Loomarr’s own uncited era guess.TEXT · key
producer_versionThe producer’s version; for an observation, what the guess came from (vision or split).TEXT · key
adapterThe search service used to find pages, such as web. Empty for an observation.TEXT · key
adapter_versionThe search service’s version. Empty for an observation.TEXT · key
input_revisionThe clip’s enrichment_revision the research was based on.INTEGER · key
report_jsonJSON document of the report: the suggested year or decade, country, scope, station, confidence and explanation, and the search query and pages it cites.TEXT
completed_atWhen the research finished, in Unix seconds.INTEGER

broadcast_stations

A directory of over-the-air TV stations that filler clips can name, so a clip titled with a station’s call sign is placed in that station’s town. Shipped with Loomarr and replaced whole at startup when a newer revision ships. Not related to Loomarr’s own channels.

Kept: Replaced whole at startup whenever a newer shipped directory arrives.

ColumnWhat it holdsType
idIdentifies the station. fcc: and the FCC facility id, or wd: and the Wikidata id for a station only Wikidata knows.TEXT · key
countryThe two-letter country code (US territories have their own, such as PR).TEXT
cityThe station’s community of license.TEXT
regionThe state or territory postal code.TEXT
latitudeLatitude of the community of license.REAL
longitudeLongitude of the community of license.REAL

broadcast_station_call_signs

Every call sign a broadcast station has held, with the dates it was used. Replaced with the station directory.

Kept: Replaced with the station directory; goes with its station (deleted when that is).

ColumnWhat it holdsType
station_idThe station (broadcast_stations.id).TEXT · key · → broadcast_stations.id
call_signThe base call sign without suffixes such as -TV, for example WEWS.TEXT · key
from_dateThe first day the station used it, as YYYY-MM-DD. Empty when unknown.TEXT · key
to_dateThe day it stopped (not included), as YYYY-MM-DD. Empty when it is still in use or unknown.TEXT

broadcast_station_seed_revisions

One row per revision of the shipped station directory that startup has applied. Kept forever.

Kept: Kept forever, as the record of which shipped directory revisions were applied.

ColumnWhat it holdsType
versionThe revision number of the shipped station directory.INTEGER · key
applied_atWhen it was applied, in Unix seconds.INTEGER

How filler clips are checked

Filler clips (vintage ads, promos and bumpers between shows) are examined by AI models and other checks before and after they air. These tables remember each model call and what it cost, the descriptive facts the checks filled in, web-research allowances, and the reel-splitting journals.

filler_inference_evaluations

One row per model call made about a filler clip, written before the call (as a spend reservation) and updated once the call finishes with what it actually used and cost. Callers include the defect judge (a vision model ruling on borderline picture defects) and the long-reel structure check.

Kept: Settled calls are deleted by the nightly housekeeping task after 180 days, once no structure-window call points at them; reserved ones (in flight) are kept. An unused budget hold is removed at once so its retry can take its place.

The call. One row is one call to a model; these say which check made it and how it ended.

ColumnWhat it holdsType
idIdentifies the call. Spoken-safety events and the structure call journal point here.TEXT · key
cache_keySHA-256 of every input that could change the answer (clip, role, model, media sizes and all the version columns), so identical retries can be recognised. Run, times and cost are not part of it.TEXT
clip_hashThe filler clip the call was about (clips.hash). For a long-reel structure check it is the SHA-256 of the reel’s source file.TEXT
run_idThe batch run the call belonged to, used for the per-run spend ceiling. Empty when the call was not part of a run.TEXT
roleWhich check made the call, for example defect_judge or complete_timeline_structure.TEXT
rungThe step within that check, for example defects or window_video. Empty when the check has no steps.TEXT
stateWhere the call stands. reserved means spend is set aside and the call is under way; completed means it answered; failed means it did not; held_budget means a spend ceiling (per clip, per day or per run) stopped it, or the provider charged more than was reserved.TEXT
latency_msHow long the call took, in milliseconds.INTEGER
attemptsHow many times the request was sent to the provider.INTEGER
outcomewindow_complete once a structure-window call has answered; empty for every other call.TEXT
failure_reasonWhy the call did not complete, for example a spend ceiling or a failure class such as transport. Empty on success.TEXT
created_atWhen the call was reserved, in Unix seconds. Also decides which day’s ceiling it counts against (UTC).INTEGER
updated_atWhen the row last changed (reserved or settled), in Unix seconds.INTEGER

Who answered. One provider and model asked, and one that answered, per call. Comparing them is how a routing service’s substitutions show up.

ColumnWhat it holdsType
requested_providerThe AI provider Loomarr asked, such as ollama or openrouter.TEXT
requested_modelThe model Loomarr asked for.TEXT
resolved_providerThe provider that actually answered, as reported back. Empty until the call settles.TEXT
resolved_modelThe model that actually answered, as reported back. Empty until the call settles.TEXT
upstream_providerThe provider behind a routing service (such as OpenRouter) that served the call. Empty when not known.TEXT
generation_idThe provider’s own ID for the response, for matching against its billing. Empty when none was given.TEXT
capability_snapshotIdentity of the model endpoint and its capabilities at the time of the call.TEXT

What was sent. One media excerpt per call; its size is what the cost estimate is worked out from.

ColumnWhat it holdsType
modalities_jsonJSON list of the kinds of input sent, such as text, image, audio or video.TEXT
derivative_bytesSize in bytes of the media excerpt sent to the model. 0 when none was sent.INTEGER
derivative_duration_msLength of the media excerpt sent, in milliseconds. 0 when none was sent.INTEGER
derivative_pixelsPixel count of the image or frames sent. 0 when none was sent.INTEGER

Tokens. The usage the provider reported for this call, one count per kind, kept to check its bill against.

ColumnWhat it holdsType
prompt_tokensInput tokens the provider reported.INTEGER
completion_tokensOutput tokens the provider reported.INTEGER
reasoning_tokensReasoning tokens the provider reported.INTEGER
cached_tokensInput tokens served from the provider’s prompt cache.INTEGER
cache_write_tokensInput tokens written into the provider’s prompt cache.INTEGER
image_tokensTokens the provider counted for images.INTEGER
audio_tokensTokens the provider counted for audio.INTEGER
video_tokensTokens the provider counted for video.INTEGER

Cost. The spend ceilings (per clip, per day, per run) add up reserved_nano_usd across calls in one statement, which needs it on the call’s own row.

ColumnWhat it holdsType
charged_amountThe charge exactly as the provider reported it, as a decimal string. Empty when no charge was reported.TEXT
charged_currencyCurrency of the reported charge, USD when known. Empty when no charge was reported.TEXT
charged_nano_usdThe reported charge in billionths of a US dollar. 0 when nothing was charged or reported.INTEGER
estimated_nano_usdLoomarr’s own estimate of the cost, in billionths of a US dollar.INTEGER
reserved_nano_usdThe amount counted against the spend ceilings, in billionths of a US dollar. It starts as the pre-call reservation and becomes the actual charge when the call settles; a held call counts 0.INTEGER
price_snapshotFingerprint of the provider price list the cost was worked out from. Empty when none was recorded.TEXT

Versions. The cache key hashes every one of these, so an answer is reused only when nothing that shaped it changed; they describe this call and no other.

ColumnWhat it holdsType
evidence_versionVersion of the evidence (measurements or media set) the call was based on.TEXT
extractor_versionVersion of the step that prepared the media excerpt.TEXT
prompt_versionVersion or fingerprint of the prompt sent.TEXT
schema_versionVersion or fingerprint of the answer format the model was asked to follow.TEXT
taxonomy_versionVersion of the vocabulary or bar the answer was judged against.TEXT
admission_policy_versionVersion of the rules that decide what the answer is allowed to unlock.TEXT
role_policy_versionVersion of the check’s own rules.TEXT

filler_inference_budget_guards

One lock row per spend-ceiling scope. Reserving a model call locks the matching rows first, so two calls cannot both slip under the same ceiling at once. Rows are created on first use and never removed.

Kept: Kept forever; there is one small lock row per spend-ceiling scope.

ColumnWhat it holdsType
scopeThe ceiling being guarded, written as clip:, day: or run:.TEXT · key

filler_enrichment_axes

The accepted answer for one descriptive fact (“axis”) about one filler clip, such as its era, brand or role, with where the answer came from. A stronger or more confident answer replaces a weaker one; no row means the fact is still unknown. Rows follow the clip if its identity changes.

Kept: Kept while the clip exists. Once it has left the catalog, rows not updated for 30 days are deleted by the nightly housekeeping, unless the catalog is empty (an unmounted drive).

ColumnWhat it holdsType
clip_hashThe filler clip described (clips.hash).TEXT · key
axisWhich fact. role (commercial, bumper, station_id, psa, trailer, interstitial, political or news), era, audience, brand, geography, language, product, format, seasonal, audience-cue or presentation.TEXT · key
statecomplete means the answer stands (an empty value is an honest “checked, none applies”); unsupported means the producer withdrew or has no answer; stale means the answer may be refreshed by a new result of the same strength.TEXT
value_jsonThe answer as JSON, using one of text, year, tags (taxonomy slugs) or geography (scope, country, network, station, airDate), depending on the axis.TEXT
evidence_kindWhere the answer came from, weakest to strongest: inference (a model’s guess), source_default (the source’s usual value), trusted_mapping, content_observation (seen or heard in the clip), item_metadata (the item’s own listing) or operator (an admin’s edit).TEXT
evidence_rankThe strength of evidence_kind as a number, 1 (inference) to 6 (operator).INTEGER
evidence_referenceWhat the answer was based on, such as the source listing or the admin screen an edit came through.TEXT
confidenceHow sure the producer was, from 0 to 100. Operator edits are 100.INTEGER
producerThe pass that gave the answer, for example deterministic-metadata, a text-model pass or operator.TEXT
producer_versionVersion of that producer’s rules or prompt.TEXT
taxonomy_versionVersion of the tag vocabulary the answer was checked against. Empty when the axis does not use tags.TEXT
observed_atWhen the producer made the observation, in Unix seconds.INTEGER
updated_atWhen this answer was accepted into the row, in Unix seconds.INTEGER

filler_enrichment_passes

Marks that one enrichment producer, at one version, has finished with one clip. It is what stops the same work (and its model spend) being repeated; a clip is picked again when the producer version or taxonomy changes, or, for free passes, when the clip’s descriptive inputs change.

Kept: Kept while the clip exists (they stop a paid pass from running twice). The nightly housekeeping task deletes a pass 30 days after it ran once its clip has left the catalog.

ColumnWhat it holdsType
clip_hashThe filler clip the pass ran on (clips.hash).TEXT · key
producerThe pass that ran, for example deterministic-metadata or a text-model pass.TEXT · key
producer_versionVersion of that pass’s rules or prompt.TEXT · key
taxonomy_versionVersion of the tag vocabulary the pass used. Empty when it used none.TEXT · key
completed_atWhen the pass finished, in Unix seconds.INTEGER
input_revisionThe clip’s enrichment revision (clips.enrichment_revision) the pass saw. When the clip’s revision moves past it, free passes run again.INTEGER

filler_research_web_attempts

One row per web search made to research a filler clip, so the same clip at the same description revision is never searched twice with the same search setup. Rows go when the clip is deleted.

Kept: Goes with its clip (deleted when that is).

ColumnWhat it holdsType
clip_hashThe filler clip researched (clips.hash).TEXT · key · → clips.hash
input_revisionThe clip’s enrichment revision the search was made for.INTEGER · key
adapter_versionIdentity of the web-search setup used, including the provider (and, for SearXNG, a short fingerprint of its address).TEXT · key
providerThe web-search provider. brave (Brave Search) or searxng (a SearXNG server).TEXT
reserved_atWhen the search was booked, in Unix seconds.INTEGER

filler_research_web_usage

One row per calendar month counting web searches against the monthly limit set in settings. Searches are refused once the count reaches the limit.

Kept: The last 12 months; the nightly housekeeping task deletes older ones.

ColumnWhat it holdsType
monthThe month counted, as YYYY-MM in UTC.TEXT · key
request_countWeb searches made that month, including an admin’s connection test.INTEGER
last_providerThe provider used for the most recent search, brave or searxng. Empty before the first.TEXT
last_success_atWhen a search last succeeded that month, in Unix seconds. 0 means none yet.INTEGER
last_failure_atWhen a search last failed that month, in Unix seconds. 0 means none yet. A failure newer than the last success shows web search as degraded.INTEGER

filler_structure_window_call_ledger

One row per model call in the long-reel structure check, which sends a reel to a video model in windows to find where each separate clip starts and ends. The row is booked before the call and completed with the answer; a call that got no answer is retried as a new attempt.

Kept: Settled calls are deleted by the nightly housekeeping task after 180 days; open and budget-held ones are kept. An attempt held back by the spend ceiling is replaced by its retry.

ColumnWhat it holdsType
request_sha256SHA-256 of the exact request. With attempt, identifies the row.TEXT · key
attemptWhich try of this request, from 1. A retry after a lost answer adds the next attempt; a retry after a budget hold replaces the held row.INTEGER · key
evaluation_idThe matching cost record (filler_inference_evaluations.id).TEXT · → filler_inference_evaluations.id
source_sha256SHA-256 of the reel’s source file.TEXT
media_set_sha256SHA-256 of the full set of windows planned for the reel.TEXT
window_ordinalWhich window of the reel this call covers, from 0.INTEGER
assessor_idIdentity of the model setup that assessed the window.TEXT
stateopen means the call is booked and has no answer yet; held_budget means a spend ceiling stopped it; settled means the outcome is recorded.TEXT
reservation_jsonThe full booked request as JSON, including the media set, window, model, prompt and schema fingerprints, and the spend requested.TEXT
record_sha256SHA-256 of the settled record. Empty until settled.TEXT
record_jsonThe settled record as JSON, including the model that answered, tokens, charge, result state (accepted, failed, unsettled, held_budget or over_reservation) and fingerprints of the response. Empty until settled.TEXT · may be empty
requested_atWhen the call was booked, in Unix seconds.INTEGER
assessed_atWhen the answer was recorded, in Unix seconds. 0 until settled.INTEGER

filler_split_proposals

A proposed cut list for a compilation reel waiting for an admin’s review, one per reel. Confirming it turns the segments into separate clips and removes the proposal; dismissing it also removes it, and it goes when the reel leaves the catalog.

Kept: Kept until an admin confirms or dismisses it, or its reel leaves the catalog.

ColumnWhat it holdsType
idIdentifies the proposal.TEXT · key
clip_hashThe compilation reel being split (clips.hash).TEXT
segments_jsonJSON list of proposed segments, each with its index, start and end in milliseconds, proposed name and where the name came from, and suggested era, audience and tags. The admin’s edits are saved here.TEXT
created_atWhen the proposal was made, in Unix seconds.INTEGER
claim_tokenA lock held while a confirm is publishing the segments, so two confirms cannot run at once. Empty when unlocked.TEXT
claim_expires_atWhen the lock lapses if not renewed, in Unix seconds. 0 when unlocked.INTEGER

filler_split_shadow_decisions

A side-by-side record, made while a new splitting rule is being trialled, of what the current rule and the new certified rule would each do with one split proposal. Kept after the proposal itself is gone; rows are never changed.

Kept: Kept while the proposal exists; once it is gone, the nightly housekeeping task deletes the decision 180 days after it was made.

ColumnWhat it holdsType
idIdentifies the decision, derived from its content.TEXT · key
proposal_idThe split proposal it was made for (filler_split_proposals.id). The proposal may since have been removed.TEXT
clip_hashThe compilation reel (clips.hash).TEXT
source_sha256SHA-256 of the reel’s source file. Empty when not known.TEXT
assessment_sha256SHA-256 of the structure assessment the new rule used. Empty when there was none.TEXT
policy_versionVersion of the splitting policy compared.TEXT
decision_jsonThe full decision as JSON. For each rule (legacy and certified) it holds a verdict and the spans it would confirm, hold (with a reason) or discard.TEXT
observed_atWhen the comparison was made, in Unix seconds.INTEGER

Notifications

Messages Loomarr sends outside the app (email, chat services, push apps, Browser Push) and where they go. A notification is recorded once, then sent to each matching destination, with every send attempt kept as a record. Message bodies and plaintext secrets are never stored here.

notification_destinations

One row per place notifications can be sent, such as a Discord webhook, an ntfy topic or one person’s browser for Browser Push. Admins set up household-wide destinations; a person can add their own. A row lasts until it is deleted.

Kept: Kept until an admin, or the person who added it, deletes it.

ColumnWhat it holdsType
idIdentifies the destination. Delivery attempts refer to it by this id.TEXT · key
meansThe kind of service it sends through. One of email, webhook, discord, ntfy, gotify, apprise, pushover, telegram, mattermost, matrix, web_push (Browser Push), mqtt or slack.TEXT
labelThe name shown for the destination, also shown in delivery history in place of its address.TEXT
scopeinstallation for a destination an admin set up for the whole household; person for one that belongs to a single person.TEXT
owner_idThe person a person-scoped destination belongs to, from users. Empty for installation scope.TEXT
audienceWho the destination is for. person means its owner’s own notifications; approvers means the admins who review requests; operators means the admins who look after the server and library.TEXT
topics_jsonA JSON list of the notification topics the destination receives (the same values as notification_intents.topic). Topics not in the list are never sent here.TEXT
enabled1 when the destination is switched on. A disabled destination receives nothing.BOOLEAN
configuration_jsonA JSON object of the destination’s non-secret settings, such as a server address or topic name.TEXT
credentials_encryptedThe destination’s secret settings (tokens, passwords, webhook URLs, push keys), encrypted with Loomarr’s encryption key. Unreadable without that key.TEXT
created_atWhen the destination was added, in Unix seconds.BIGINT
updated_atWhen the destination was last changed, in Unix seconds.BIGINT
credential_keys_jsonA JSON list naming which secret settings are filled in, without their values, so the app can show them as set without decrypting. NULL for rows made before this was recorded.TEXT · may be empty
subscription_fingerprintFor Browser Push, a one-way hash of the browser’s push address, so one browser is subscribed only once per person. The address can’t be recovered from it. Empty for other kinds.TEXT

notification_intents

One row per notification Loomarr decided to send, such as “your request was approved” or “a media folder went offline”. Its delivery attempts hang off it. Deleted, with its attempts, 30 days after its last attempt finished.

Kept: The nightly housekeeping task deletes a notification 30 days after its last attempt finished; ones still sending are kept.

ColumnWhat it holdsType
idIdentifies the notification. notification_delivery_attempts point here.TEXT · key
topicWhat happened. account_invitation (an invitation email); local_password_recovery (a password-reset email); proposal_submitted, proposal_approved, proposal_denied (a request was made, approved or denied); acquisition_available, acquisition_gave_up (a requested title arrived, or Loomarr stopped trying); channel_live, channel_degraded (a channel went on air or stopped playing properly); delivery_test (a test sent from a destination’s settings); library_folder_offline, library_folder_back (a media folder stopped or started opening again); library_changes_degraded (an arr can no longer report new files); library_needs_you (titles in the library need a person’s help).TEXT
recipient_kindWho it is for. invitation means the address on a pending invitation; person means one person; approvers means the admins who review requests; operators means the admins who look after the server.TEXT
recipient_idThe invitation’s id, the person’s id, or the literal word approvers or operators. For a delivery test, the destination’s id.TEXT
reference_kindThe kind of thing it is about. One of invitation, recovery (a password-recovery request), proposal (a request), title, channel, destination or library (a media folder or arr).TEXT
reference_idThe id of the thing it is about, in the table that reference_kind names.TEXT
recipient_policymandatory_account for account and security email that always goes out; configurable for everything else, which only reaches destinations that chose the topic.TEXT
template_jsonA JSON object with the few names the message needs (the recipient’s name, the subject’s name, a short summary), captured when it happened. Never the rendered message or any link secret.TEXT
idempotency_keyA unique key built from the event and recipient, so the same event never makes two notifications for the same recipient.TEXT
created_atWhen the notification was recorded, in Unix seconds.BIGINT
terminal_atWhen its last delivery attempt finished, in Unix seconds. 0 while any attempt is still queued or sending.BIGINT

notification_delivery_attempts

One row per try at sending a notification to one destination. A failure that may succeed later queues a new row for the next try, up to 5 tries. Deleted with its notification.

Kept: Goes with its notification (deleted when that is).

ColumnWhat it holdsType
idIdentifies the attempt.TEXT · key
intent_idThe notification being sent, from notification_intents.TEXT · → notification_intents.id
meansThe kind of service used, with the same values as notification_destinations.means.TEXT
destination_refWhich destination it went to. A notification_destinations id, or for account email, invitation: or person: followed by that id. Never the address itself.TEXT
destination_redactedA safe name for the destination to show in delivery history, such as its label. Never the address or a secret.TEXT
statusqueued waiting to be sent; sending while a worker has it; delivered when the service accepted it; failed when it didn’t go through; suppressed when it was deliberately not sent (destination off or gone, or the person turned the topic off).TEXT
attempt_numberWhich try this is, from 1 to 5.INTEGER
available_atWhen the attempt may be sent, in Unix seconds. Later for retries, which wait before trying again.BIGINT
lease_ownerWhich worker is sending it. Empty unless status is sending.TEXT
lease_expires_atWhen the worker’s hold runs out, in Unix seconds. An attempt still sending after this is closed as failed with an unknown outcome. 0 unless sending.BIGINT
started_atWhen a worker started sending it, in Unix seconds. 0 if never started.BIGINT
finished_atWhen it was delivered, failed or suppressed, in Unix seconds. 0 while queued or sending.BIGINT
provider_message_idThe id the service gave the sent message, when it gave one. Empty otherwise.TEXT
failure_classWhy a failed attempt failed. transient_pre_acceptance (a temporary problem before the service took it, so it is retried); permanent (won’t work if tried again); ambiguous_acceptance (the service may or may not have taken it, so it is not retried); cancelled. Empty unless failed.TEXT
outcome_codeWhat went wrong, in terms an admin can act on. delivery_disabled, destination_unavailable, preference_disabled (reasons for suppressing); means_unavailable, recipient_rejected, configuration_invalid, transport_unavailable, acceptance_ambiguous, cancelled, worker_interrupted (reasons for failing). Empty when delivered or still in progress.TEXT
created_atWhen the attempt was queued, in Unix seconds.BIGINT

title_requesters

One row per person who asked for a title, recorded when an admin approves their request, so later news about it (the title arrived) reaches them.

Kept: Goes with the title or the person.

ColumnWhat it holdsType
title_keyThe title asked for (a titles key).TEXT · key · → titles.key
person_idThe person who asked for it, from users.TEXT · key · → users.id
created_atWhen their request was approved, in Unix seconds.BIGINT

channel_requesters

One row per person who asked for a channel, recorded when an admin approves their request, so later news about it (the channel went live) reaches them.

Kept: Goes with the channel or the person.

ColumnWhat it holdsType
channel_idThe channel asked for (a channels id).TEXT · key · → channels.id
person_idThe person who asked for it, from users.TEXT · key · → users.id
created_atWhen their request was approved, in Unix seconds.BIGINT

Settings and housekeeping

How this installation is configured and what it remembers about its own running: saved settings and the keys that encrypt their secrets, background tasks, the Dashboard’s recent activity, and the Diagnostics and crash-report logs that are cleaned up on a schedule.

settings

One saved setting value. Every setting resolves environment variable, then this table, then the built-in default, so a key with no row uses its default. A row is created when someone saves the setting (or Loomarr records internal state such as its instance id) and deleted when it is reset.

Kept: Kept until the setting is reset to its default.

ColumnWhat it holdsType
keyThe setting’s name, such as activity.retention, as listed in the configuration reference. A few keys hold Loomarr’s own state, such as instance_id.TEXT · key
valueThe stored value as text. Secret settings (API keys, tokens) are stored encrypted, beginning with loomarr-secret:v1:, and can only be read with the installation key.TEXT
updated_atWhen the value was last written, in Unix seconds. 0 means unknown (written before this was tracked).BIGINT
updated_byThe id of the person who last changed it, pointing at users. Empty for values Loomarr wrote itself.TEXT · may be empty
env_override1 when an admin has taken the setting back from its environment variable, so the saved value wins even while the variable is still set. 0 means a set environment variable wins.BOOLEAN

secret_protection_metadata

A single row that records which installation key encrypts this database’s secrets. The key itself is never stored here; it comes from LOOMARR_ENCRYPTION_KEY or the encryption.key file. Loomarr refuses to start if the supplied key doesn’t match.

Kept: Kept forever: a single row Loomarr checks the installation key against at startup.

ColumnWhat it holdsType
singletonAlways 1; the table holds at most one row.INTEGER · key
installation_key_fingerprintA non-secret fingerprint of the installation key (sha256: followed by the start of its hash), used to detect the wrong key. Updated when the installation key is replaced.TEXT

secret_data_keys

The data-encryption keys that encrypt secret settings and other stored credentials, each kept encrypted by the installation key. Rotating the key (Security settings) adds a new active key; older keys are kept forever so values they encrypted can still be read.

Kept: Kept forever: one row per key rotation, so values encrypted with an older key can still be read.

ColumnWhat it holdsType
idIdentifies the key. Each encrypted value names the key that sealed it.TEXT · key
wrapped_keyThe data key, encrypted with the installation key (begins with loomarr-dek:v1:).TEXT
active1 for the one key used to encrypt new secrets; 0 for retired keys kept for reading.BOOLEAN
created_atWhen the key was created, in Unix seconds.BIGINT

scheduled_jobs

The running state of one recurring background task shown on the Tasks page. Which tasks exist and when they run come from the code and the job schedule settings; a row is created the first time a task is seen and kept forever, even if the task later disappears from the code.

Kept: Kept forever: one row per background task, kept even after a task is removed from Loomarr.

ColumnWhat it holdsType
nameThe task’s name, such as housekeeping.TEXT · key
last_runWhen the task last started, in Unix seconds. 0 means it has never run.BIGINT
last_resultHow the last run ended. ok means it succeeded, error means it failed, empty means it hasn’t run yet.TEXT
last_errorThe error message from the last run, or empty if it succeeded.TEXT
next_runWhen the task is next due, in Unix seconds. Advanced when a run is claimed, so the same run doesn’t start twice.BIGINT
updated_atWhen the row was last written, in Unix seconds.BIGINT
paused1 when an admin has paused the task, so it no longer runs on its schedule until resumed.BOOLEAN

interactive_operations

Progress of one long action someone started in the app (downloading an AI model, or detecting where a filler reel should be split), so the page can pick it up again after a reconnect. Anything still queued or running when Loomarr restarts is marked as an error.

Kept: Finished ones are deleted by the nightly housekeeping task 7 days after they finish; queued and running ones are kept.

ColumnWhat it holdsType
idIdentifies the operation; the app polls it by this id.TEXT · key
kindWhat the operation is. llm_pull downloads an AI model; filler_split detects split points in a filler clip.TEXT
subjectWhat it acts on. The model name for llm_pull, the filler clip’s id for filler_split.TEXT
statusqueued (accepted, not started), running, success, or error.TEXT
percentHow far along it is, 0 to 100. For a model download, -1 means the size isn’t known yet.INTEGER
completedFor a model download, bytes downloaded so far of the current layer. 0 otherwise.BIGINT
totalFor a model download, total bytes of the current layer. 0 otherwise.BIGINT
result_idFor filler_split, the id of the split proposal it produced. Empty otherwise.TEXT
errorWhy it failed, or empty.TEXT
started_atWhen it was started, in Unix seconds.BIGINT
completed_atWhen it finished, in Unix seconds. 0 while it is still queued or running.BIGINT
updated_atWhen its progress was last saved, in Unix seconds.BIGINT

activity

One line in the Dashboard’s Recent activity feed, written when something notable happens, such as a title arriving or a channel changing. Purged by the nightly housekeeping task once older than the activity.retention setting (30 days by default).

Kept: The nightly housekeeping task deletes lines older than activity.retention (30 days by default).

ColumnWhat it holdsType
idIdentifies the entry.TEXT · key
atWhen it happened, in Unix seconds.BIGINT
kindWhich part of Loomarr wrote it: title, channel, proposal (a suggested lineup), or system.TEXT
levelHow serious it is, which sets the coloured dot. info, warn, or error.TEXT
textThe sentence shown in the feed.TEXT
subject_idThe id of the channel, title or proposal it concerns, or empty.TEXT

diagnostic_events

One technical event recorded for Diagnostics, from the server’s own logs or reported by the web, TV and phone apps. The nightly housekeeping task deletes events older than the diagnostics.retention setting (7 days by default), then the oldest ones while the total exceeds diagnostics.max_storage_mb (512 MiB by default).

Kept: The nightly housekeeping task deletes events older than diagnostics.retention (7 days by default), then the oldest while over diagnostics.max_storage_mb.

ColumnWhat it holdsType
idIdentifies the event.TEXT · key
occurred_at_msWhen it happened, in Unix milliseconds (by the sender’s clock for app events).BIGINT
received_at_msWhen the server recorded it, in Unix milliseconds.BIGINT
levelHow serious it is. debug, info, warn, or error.TEXT
sourceWhich program observed it. server, web, android_tv, apple_tv, ios, or android.TEXT
subsystemThe part of Loomarr it came from, or empty.TEXT
eventThe event’s name, such as player.media_error for an app playback error.TEXT
messageThe human-readable message, or empty.TEXT
request_idThe API request it belongs to, or empty.TEXT
playback_session_idThe app playback session it belongs to, or empty.TEXT
channel_idThe channel it concerns, or empty.TEXT
schedule_block_idThe schedule block it concerns, or empty.TEXT
job_idThe background job it belongs to, or empty.TEXT
process_run_idThe process run it belongs to, pointing at diagnostic_process_runs, or empty.TEXT
actor_idWho caused it, usually a user id. App events sent with an API token say api_token. Empty when no one is involved.TEXT
instance_idWhich Loomarr server recorded it, when known.TEXT
attributes_jsonExtra details as a JSON object of named values (secrets removed before storage), such as the app version and platform for app events.TEXT
size_bytesThe event’s estimated size, counted toward the Diagnostics storage budget.BIGINT

diagnostic_process_runs

One run of an external media program, usually ffmpeg (playout, filler transcodes, encoder probes), with its outcome. Its output is kept in a file under the diagnostics folder. The nightly housekeeping task deletes finished runs, and their output files, once they ended longer ago than diagnostics.retention (7 days by default), then the oldest finished ones while Diagnostics is over its storage budget. Runs still going are never deleted.

Kept: The nightly housekeeping task deletes finished runs older than diagnostics.retention (7 days by default), then the oldest while over the storage budget; running ones are kept.

ColumnWhat it holdsType
idIdentifies the run.TEXT · key
purposeWhat the program was run for, such as playout_program, filler_transcode or capacity_probe.TEXT
parent_run_idThe run that started this one, pointing at another row here, or empty.TEXT
instance_idWhich Loomarr server ran it.TEXT
channel_idThe channel it was working for, or empty.TEXT
targetWhat it was working on, such as an encoder name or output profile, or empty.TEXT
schedule_block_idThe schedule block it was playing, or empty.TEXT
job_idThe background job it belongs to, or empty.TEXT
executableThe program that ran.TEXT
executable_versionThe program’s reported version, or empty if not detected.TEXT
command_summaryThe command line it was run with, shortened and with secrets removed.TEXT
started_at_msWhen it started, in Unix milliseconds.BIGINT
ended_at_msWhen it ended, in Unix milliseconds. 0 while it is still running.BIGINT
statusrunning, succeeded, failed, cancelled, or interrupted (Loomarr stopped before recording how it ended, so the outcome is unknown).TEXT
exit_codeThe program’s exit code when it failed with one; empty otherwise.INTEGER · may be empty
termination_reasonWhy the run ended or was stopped, when Loomarr knows, or empty.TEXT
first_errorThe first line the program printed, or the error if it printed nothing.TEXT
last_errorThe last line the program printed, or the error it failed with.TEXT
output_refThe name of its output file in the diagnostics folder, or empty if output wasn’t saved.TEXT
output_bytesThe size of the output file, in bytes.BIGINT
discarded_linesHow many output lines were dropped to keep the output file within its size cap.BIGINT
updated_at_msWhen the row was last updated, in Unix milliseconds.BIGINT
size_bytesThe run’s estimated size including its output file, counted toward the Diagnostics storage budget.BIGINT

diagnostic_retained_bytes

Running totals of how much Diagnostics is keeping, updated with every insert and delete so the storage budget can be checked without adding up the big tables. There are two rows, and they are never deleted.

Kept: Kept forever: just two running-total rows.

ColumnWhat it holdsType
scopeWhich table the total covers. events for diagnostic_events, process_runs for diagnostic_process_runs.TEXT · key
bytesThe sum of size_bytes across that table.BIGINT

client_crash_reports

One crash report sent by the Loomarr TV or phone app, cleaned of secrets before storage. The nightly housekeeping task deletes reports older than 30 days and keeps at most the newest 1,000.

Kept: The nightly housekeeping task deletes reports older than 30 days and keeps at most the newest 1,000.

ColumnWhat it holdsType
idIdentifies the report.TEXT · key
received_at_msWhen the server received it, in Unix milliseconds.BIGINT
occurred_at_msWhen the crash happened by the device’s clock, in Unix milliseconds.BIGINT
user_idThe person signed in on the app that sent it, pointing at users. Empty if unknown or the person has been deleted.TEXT · may be empty · → users.id
device_idThe paired device that sent it, matching device_tokens.token_hash. Empty when not sent from a paired device.TEXT
device_nameThe paired device’s name at the time, or empty.TEXT
kindWhat crashed. js-fatal is an app error that closed the app, js-error is an app error that was caught, native is a crash of the app’s native code.TEXT
platformWhich app sent it. ios, android, tvos (Apple TV), or android-tv.TEXT
app_versionThe app’s version.TEXT
app_buildThe app’s build number.TEXT
os_versionThe device’s operating system version.TEXT
device_modelThe device’s model.TEXT
messageThe error message.TEXT
stackThe stack trace, or empty.TEXT
breadcrumbs_jsonA JSON list of up to 50 things the app did just before the crash, each with a time, a category (navigation, http, lifecycle or log) and a short message.TEXT
native_payloadFor native crashes, the crash report from Apple’s MetricKit as JSON. Empty otherwise.TEXT

database_table_rows

How many rows each table held, counted once a day by the nightly housekeeping task after its clean-up. A table whose count only ever rises is one that nothing trims; “Which tables are growing” near the top of this page shows how to compare the latest count with a month ago.

Kept: The nightly housekeeping task forgets counts older than 400 days.

ColumnWhat it holdsType
recorded_atThe day counted, as the Unix time in seconds of that day’s midnight (UTC).INTEGER · key
table_nameThe table counted. Every table except goose_db_version and this one.TEXT · key
row_countHow many rows the table held after that night’s clean-up.INTEGER

Tables other libraries own

These come from the tools Loomarr is built on, which document them. Leave them alone.

  • goose_db_version: which database migrations have run (goose).