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:
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:
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.library_*: the answer. Onelibrary_itemsrow 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.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_idsays 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_byFROM database_table_rows latestLEFT JOIN database_table_rows earlier ON earlier.table_name = latest.table_name AND earlier.recorded_at = latest.recorded_at - 30 * 86400WHERE latest.recorded_at = (SELECT MAX(recorded_at) FROM database_table_rows)ORDER BY grew_by DESCLIMIT 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
_mscounts milliseconds,_nsnanoseconds, and any other counts seconds. In SQLite,datetime(col, 'unixepoch')turns seconds into a date anddatetime(col / 1000, 'unixepoch')does the same for milliseconds. - Yes/no columns hold
1for yes and0for no. - Columns ending in
_jsonhold 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:
users,sessions,device_pairings,device_tokens,invitations,invitation_grants,password_recoveries,password_recovery_grants,contact_addresses,user_favourite_channels,user_recent_channels,user_hidden_ideas,viewings - Channels and the guide:
channels,channel_lineup,growth_reviews,airings,scheduled_airings,holidays,holiday_seed_revisions - Suggestions:
proposal_jobs,proposal_job_attempts,proposals,discovery_feedback,quality_receipts,quality_daily_aggregates,quality_run_snapshots - Your library:
library_roots,library_root_paths,library_files,library_items,library_ids,library_genres,library_studios,library_countries,library_people,library_filmography,library_credits,library_tags,library_sources,library_streams,library_chapters,library_markers,library_trickplay,library_art,library_catalog,library_catalog_feeds,library_candidates,library_answers,library_episode_orders,library_collections,library_collection_members,titles,images,clip_images,filler_split_proposal_images,channel_images,library_item_images,library_candidate_images,image_derivatives - Media inventory:
inventory_items,inventory_item_origins,inventory_external_ids,inventory_sources,inventory_source_origins,inventory_source_measurements,inventory_source_analysis - Filler clips:
clips,clip_tags,taxa,taxa_closure,taxonomy_seed_revisions,filler_sources,filler_providers,filler_source_check_outcomes,filler_pulls,filler_pull_commits,filler_acquisition_runs,filler_acquisition_artifacts,filler_download_failures,filler_clip_pipeline,filler_clip_variants,filler_clip_fingerprints,filler_clip_defects,filler_inferior_copies,filler_ready_events,filler_curated_offers,filler_airings,filler_breaks,filler_context_reports,broadcast_stations,broadcast_station_call_signs,broadcast_station_seed_revisions - How filler clips are checked:
filler_inference_evaluations,filler_inference_budget_guards,filler_enrichment_axes,filler_enrichment_passes,filler_research_web_attempts,filler_research_web_usage,filler_structure_window_call_ledger,filler_split_proposals,filler_split_shadow_decisions - Notifications:
notification_destinations,notification_intents,notification_delivery_attempts,title_requesters,channel_requesters - Settings and housekeeping:
settings,secret_protection_metadata,secret_data_keys,scheduled_jobs,interactive_operations,activity,diagnostic_events,diagnostic_process_runs,diagnostic_retained_bytes,client_crash_reports,database_table_rows - Tables other libraries own
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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies 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 |
name | The person’s name, which is also the username a local account signs in with. | TEXT |
role | What the person may do. admin manages the household (people, settings, approvals); member watches and makes requests. | TEXT |
disabled | 1 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 |
quota | How 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_approve | 1 when the person’s requests skip the admin queue, as long as they stay within their quota. | BOOLEAN |
created_at | When the account was made, in Unix seconds. | BIGINT |
updated_at | When the account was last changed, in Unix seconds. | BIGINT |
password_hash | A 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_linked | 1 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_at | When 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.
| Column | What it holds | Type |
|---|---|---|
token_hash | SHA-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_id | The person signed in, from users. | TEXT · → users.id |
created_at | When the person signed in, in Unix seconds. | BIGINT |
expires_at | When 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_at | When 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_label | A 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.
| Column | What it holds | Type |
|---|---|---|
device_code | SHA-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_code | The short code shown on the device’s screen for a person to type, such as BCDF-GHJK. | TEXT |
user_id | The person who approved the pairing, from users. NULL while still waiting. | TEXT · may be empty · → users.id |
device_name | The name the device gave itself, shown when approving it. Empty if it gave none. | TEXT |
created_at | When the code was shown, in Unix seconds. | BIGINT |
expires_at | When the code stops working, in Unix seconds (10 minutes after it was made). | BIGINT |
approved_at | When 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.
| Column | What it holds | Type |
|---|---|---|
token_hash | SHA-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_id | The person who approved the device, from users. | TEXT · → users.id |
device_name | The device’s name, shown in the person’s list of devices. Empty if it gave none. | TEXT |
created_at | When the device was paired, in Unix seconds. | BIGINT |
last_seen_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the invitation. invitation_grants and contact_addresses point here. | TEXT · key |
kind | local invites someone to make a Loomarr-only account with the reserved username; library invites the owner of an existing media-server account. | TEXT |
username | The username reserved for a local invitation. Empty for a library invitation. | TEXT |
library_user_id | The media-server account invited by a library invitation. Empty for a local invitation. | TEXT |
display_name | The media-server account’s name at the time of inviting. Empty for a local invitation. | TEXT |
identity_key | What 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 |
role | The role the new account gets, member or admin. | TEXT |
status | pending 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_at | When the admin made the invitation, in Unix seconds. | BIGINT |
expires_at | When it stops working, in Unix seconds (always 7 days after created_at). | BIGINT |
terminal_at | When it was redeemed, revoked or expired, in Unix seconds. 0 while still pending. | BIGINT |
redeemed_by | The 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).
| Column | What it holds | Type |
|---|---|---|
token_hash | SHA-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_id | The invitation this link opens, from invitations. | TEXT · → invitations.id |
grant_kind | What the link does. Always activation (making the account). | TEXT |
conveyance | How 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_at | When the link was made, in Unix seconds. | BIGINT |
expires_at | When the link stops working, in Unix seconds. Never later than its invitation’s expiry. | BIGINT |
consumed_at | When the link was used to make the account, in Unix seconds. 0 means not used. | BIGINT |
revoked_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the request. password_recovery_grants point here. | TEXT · key |
user_id | The local account being recovered, from users. | TEXT · → users.id |
status | pending 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_at | When the request was made, in Unix seconds. | BIGINT |
expires_at | When it stops working, in Unix seconds (always 30 minutes after created_at). | BIGINT |
terminal_at | When 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).
| Column | What it holds | Type |
|---|---|---|
token_hash | SHA-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_id | The request this link belongs to, from password_recoveries. | TEXT · → password_recoveries.id |
created_at | When the link was made, in Unix seconds. | BIGINT |
expires_at | When the link stops working, in Unix seconds. At most 30 minutes after it was made. | BIGINT |
consumed_at | When the link was used to set a new password, in Unix seconds. 0 means not used. | BIGINT |
revoked_at | When 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.
| Column | What it holds | Type |
|---|---|---|
owner_kind | Whose address it is. user for a person in users; invitation for a pending invitation. | TEXT · key |
owner_id | The person’s id in users, or the invitation’s id in invitations. | TEXT · key |
email | The address as entered, for display and sending. | TEXT |
normalized | The lowercased address. No two rows may share it, so one address belongs to one owner. | TEXT |
status | pending until the owner has shown they receive mail there; verified once they have. Only a verified address is used for password recovery. | TEXT · key |
provenance | How 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_at | When the address was added, in Unix seconds. | BIGINT |
verified_at | When 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).
| Column | What it holds | Type |
|---|---|---|
user_id | The person, from users. | TEXT · key · → users.id |
channel_id | The starred channel, from channels. | TEXT · key · → channels.id |
added_at_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
user_id | The person, from users. | TEXT · key · → users.id |
channel_id | The channel tuned to, from channels. | TEXT · key · → channels.id |
tuned_at_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
user_id | The person, from users. | TEXT · key · → users.id |
idea_id | The 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_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the row. | TEXT · key |
user_id | The person watching, from users. | TEXT · → users.id |
device | What the household calls the device, such as a paired TV’s name or the browser and system. | TEXT |
channel_id | The channel watched. It isn’t a link to channels, so the history survives the channel being deleted. | TEXT |
channel_name | The channel’s name when the viewing began. | TEXT |
programme | The programme watched; a new row starts when the next programme does. Empty when the guide had nothing for it. | TEXT |
started_at | When the player started, in Unix seconds. | BIGINT |
ended_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the channel. Other tables point here. | TEXT · key |
name | The channel’s display name. | TEXT |
number | The channel number, which is its position in the guide. Unique. | INTEGER |
grp | The group the channel is listed under in the guide. Empty for none. | TEXT |
logo | The channel icon’s URL, either one of this Loomarr’s own image URLs or an outside link. Empty for no icon. | TEXT |
strategy | How 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_id | The 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 |
status | The 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_seed | The 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_json | The 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_deadline | When 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_at | When the row last changed, in Unix seconds. | BIGINT |
policy_json | The 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_codec | The 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 |
revision | Counts 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_anchor | When 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_at | When the channel was created, in Unix seconds. Never changes. 0 when unknown (a hand-made channel older than the column). | BIGINT |
intent_json | The 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_by | The 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_request | Whether 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_ref | The 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_from | Two 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_ms | The 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.
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel (a channels id). | TEXT · key · → channels.id |
position | Where the title sits in the lineup, from 0. | INTEGER · key |
title_key | The 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 |
entry | The 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.
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel (a channels id). | TEXT · key · → channels.id |
title_key | The title (a titles key, such as movie:tmdb:603). | TEXT · key |
line | The channel’s line the verdict was given against, in growth’s own words. | TEXT |
verdict | fits (an unclear title may join) or does_not_fit (a title growth added is taken off and declined). | TEXT |
reason | The model’s few words for its verdict, shown with the title. | TEXT |
model | The model that gave the verdict. | TEXT |
reviewed_at | When 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).
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel it aired on (channels.id). | TEXT · key · → channels.id |
library_item_id | The library item id of the episode or film that aired. | TEXT · key |
aired_at | When the programme started, in Unix seconds. | BIGINT · key |
recorded_at | When Loomarr first recorded the airing, in Unix seconds. | BIGINT |
key | The 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).
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel it aired on (channels.id). | TEXT · key · → channels.id |
library_item_id | The library item id of the episode or film. | TEXT · key |
aired_at | When the programme started, in Unix seconds. | BIGINT · key |
recorded_at | When Loomarr first recorded the airing, in Unix seconds. | BIGINT |
key | The 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.
| Column | What it holds | Type |
|---|---|---|
id | The holiday’s slug, which is also the slug of its seasonal tag (taxa.slug). | TEXT · key · → taxa.slug |
label | The holiday’s display name. | TEXT |
position | The display order. It also settles ties when two holidays are in season at once. | INTEGER |
words | JSON 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 |
kept | JSON 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_window | JSON 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 |
members | JSON 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_corroboration | 1 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_at | When 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.
| Column | What it holds | Type |
|---|---|---|
version | The revision number of the shipped holiday list. | INTEGER · key |
applied_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the job. Proposals and channels point here. | TEXT · key |
kind | suggest for a request a person made (including a refine), or recurate for a scheduled refresh of a channel that allows automatic curation. | TEXT |
status | queued (waiting for a worker), running, done (produced a proposal), or failed. | TEXT |
intent_json | The 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_hash | A fingerprint of the normalised request. A new identical request can reuse a recent done job’s result instead of running again. | TEXT |
created_by | The person who submitted the request (users.id). Empty when no person did. | TEXT |
last_error | The detailed error from the last failure, for the logs. Not shown to the requester. | TEXT |
deadline | When 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 |
attempts | How many times a worker has started the job. | INTEGER |
created_at | When the job was submitted, in Unix seconds. | BIGINT |
updated_at | When the job last changed, in Unix seconds. | BIGINT |
failure_code | Why 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_version | Which 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_live | 1 once a channel made from this job has gone live. It stays 1 even if the channel is later paused. | BOOLEAN |
failure_trace_json | The 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_id | The 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).
| Column | What it holds | Type |
|---|---|---|
job_id | The job this attempt ran (proposal_jobs.id). | TEXT · key · → proposal_jobs.id |
attempt | The attempt’s number within its job, counting from 1. | INTEGER · key |
workflow_version | Which version of the job process ran the attempt. | INTEGER |
status | running, succeeded, failed, or interrupted (the worker’s hold expired while it was still running and the job was taken over). | TEXT |
started_at | When the attempt started, in Unix seconds. | BIGINT |
completed_at | When the attempt ended, in Unix seconds. 0 while it is running. | BIGINT |
failure_code | Why 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the proposal. | TEXT · key |
job_id | The job that produced it (proposal_jobs.id). | TEXT |
status | submitted (waiting for a decision), approved, denied, or superseded (replaced by a newer proposal from the same job before anyone decided). | TEXT |
created_by | The person who asked for it (users.id). | TEXT |
decided_by | The admin who approved or denied it (users.id). Empty until decided. | TEXT |
deny_reason | The reason given for a denial. Empty otherwise. | TEXT |
proposal_json | The 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_at | When the proposal was made, in Unix seconds. | BIGINT |
updated_at | When the row last changed, in Unix seconds. | BIGINT |
mod_summary | What the admin changed before approving, written by Loomarr, such as “dropped 2, added 1”. Empty when approved unchanged. | TEXT |
note | The admin’s message to the requester about the decision. Empty for none. | TEXT |
approved_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the choice. | TEXT · key |
actor_id | The person who made it (users.id), or api-token for a choice sent with the API token. | TEXT |
scope | Where it applies. household for every channel, or channel for one channel. | TEXT |
scope_id | The 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_key | The title it is about, by its key, such as movie:tmdb:603. | TEXT |
action | keep (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 |
reason | An optional reason the person gave. | TEXT |
created_at_ns | When 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.
| Column | What it holds | Type |
|---|---|---|
idempotency_key | An opaque hash identifying the step, so the same step is never counted twice. | TEXT · key |
occurred_at | When the step happened, in Unix seconds. | BIGINT |
stage | The pipeline step. retrieval, generation, grounding, approval, acquisition or scheduling. | TEXT |
outcome | How 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_ms | How long the step took, in milliseconds. 0 when not measured. | BIGINT |
tool_calls | How many tool calls the model made during the step. | INTEGER |
candidate_count | How many candidate titles the step surfaced. | INTEGER |
cost_nanos | What the step cost, in billionths of a US dollar. | BIGINT |
run_snapshot_id | The 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.
| Column | What it holds | Type |
|---|---|---|
day | The UTC day, as YYYY-MM-DD. | TEXT · key |
stage | The pipeline step, with the same values as quality_receipts.stage. | TEXT · key |
outcome | How the steps went, with the same values as quality_receipts.outcome. | TEXT · key |
run_snapshot_id | The evaluation run (quality_run_snapshots.id), or empty for ordinary use. | TEXT · key |
observation_count | How many receipts were rolled into the row. | BIGINT |
duration_ms | Total time of those steps, in milliseconds. | BIGINT |
tool_calls | Total tool calls. | BIGINT |
candidate_count | Total candidate titles surfaced. | BIGINT |
cost_nanos | Total 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the snapshot, derived from its facts (eval- | TEXT · key |
schema_version | Version of the snapshot format. | INTEGER |
corpus_version | Version of the test set the run was scored on. | TEXT |
requested_model | The model the run asked for. | TEXT |
resolved_model | The model that actually answered, when every call reported the same one. Empty otherwise. | TEXT |
provider | The AI provider. unknown, ollama, openrouter or custom. | TEXT |
budget_profile | The spending profile the run used. | TEXT |
application_version | The Loomarr version (and commit) that ran it. | TEXT |
accounting_available | 1 when the provider reported costs for the run. | BOOLEAN |
created_at | When 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.
| Column | What it holds | Type |
|---|---|---|
path | The folder as Loomarr opens it, an absolute path. | TEXT · key |
in_use | 1 when the folder is confirmed for scanning. A discovered folder starts at 0; one typed by hand starts at 1. | BOOLEAN |
readable | 1 when the last check could open the folder. | BOOLEAN |
checked_at_ms | When the folder was last checked for readability, in Unix milliseconds; 0 means never. | INTEGER |
by_hand | 1 when someone typed the folder in rather than a media server or arr reporting it. | BOOLEAN |
added_at_ms | When the folder was added, in Unix milliseconds. | INTEGER |
scanned_at_ms | When a walk of the folder last completed, in Unix milliseconds; 0 means never. | INTEGER |
empty | 1 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.
| Column | What it holds | Type |
|---|---|---|
authority | The app that reported the folder, one of emby, jellyfin, sonarr or radarr. | TEXT · key |
authority_path | The folder as that app names it. | TEXT · key |
root_path | The local folder it maps to (a library_roots path), or empty when no local folder was found for it. | TEXT |
by_hand | 1 when someone set the mapping by hand. Discovery never rewrites such a row. | BOOLEAN |
seen_at_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
path | The file as Loomarr opens it, an absolute path. | TEXT · key |
root | The folder it was found under (a library_roots path). | TEXT |
size_bytes | The file’s size in bytes at the last walk. | INTEGER |
modified_ns | The 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 |
identity | A 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 |
kind | What the file was last written as, one of movie, episode or extra; empty for a problem file. | TEXT |
header | Whether 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 |
problem | Why 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 |
held | 1 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_ms | When a walk last found the file, in Unix milliseconds. | INTEGER |
missing_at_ms | When a walk stopped finding the file, in Unix milliseconds; 0 means it is present. | INTEGER |
inventory_item_id | The title the file was last written as (a library_items id); null until known. | TEXT · may be empty · → library_items.id |
inventory_series_id | For 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the title. library_files, collections and the rows below point here. | TEXT · key |
kind | One of movie, series or episode. | TEXT |
name | The title’s name. | TEXT |
sort_name | The lower-case name it sorts by. | TEXT |
year | The year, or 0 when unknown. | INTEGER |
updated_at_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
origin_authority | The 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_key | Where 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.
| Column | What it holds | Type |
|---|---|---|
catalog | The library_catalog row its details come from, written provider:kind:id (for example tmdb:movie:603); empty for none. | TEXT |
grounded | 1 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 |
searched | The 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_basis | What 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_app | The app that said it, one of sonarr, radarr, nfo or tmdb; empty for evidence Loomarr finds itself. | TEXT |
match_page | The title’s page in that app, for a person to open; empty when it has none. | TEXT |
needs_noticed | 1 once a daily Needs you notice has counted the title as new, so a restart neither repeats nor loses a notice. | BOOLEAN |
set_key | For 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.
| Column | What it holds | Type |
|---|---|---|
original_title | The title in its original language, or empty. | TEXT |
overview | The description, or empty. | TEXT |
premiere_date | The release or first-air date as the source gave it, such as 2013-05-01; empty when unknown. | TEXT |
rating | The content rating, such as TV-PG; empty when unknown. An episode with no rating of its own takes its show’s. | TEXT |
score | The community score from 0 to 10; 0 when unknown. | REAL |
runtime_ms | The running time in milliseconds; 0 when unknown. | INTEGER |
tagline | The one-line pitch printed on a poster; empty when unknown. | TEXT |
trailer | The 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.
| Column | What it holds | Type |
|---|---|---|
season | An episode’s season number; 0 for movies and shows. | INTEGER |
episode | An episode’s episode number; 0 for movies and shows. | INTEGER |
episode_end | The last episode number a multi-episode file covers; 0 for a single episode. | INTEGER |
series_authority | An episode’s show, by the scan folder part of the show’s scan origin; empty otherwise. | TEXT |
series_key | An episode’s show, by the folder part of the show’s scan origin; empty otherwise. | TEXT |
series_id | An 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 |
unnumbered | 1 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.
| Column | What it holds | Type |
|---|---|---|
name_from | The app that supplied the name (sonarr, radarr, nfo or tmdb); empty when unknown. | TEXT |
year_from | The app that supplied the year; empty when unknown. | TEXT |
overview_from | The app that supplied the description; empty when unknown. | TEXT |
premiere_from | The app that supplied the premiere date; empty when unknown. | TEXT |
rating_from | The app that supplied the rating, or show when an episode’s rating is its show’s; empty when unknown. | TEXT |
score_from | The app that supplied the score; empty when unknown. | TEXT |
runtime_from | The 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
namespace | Which id it is, such as tmdb, tvdb or imdb. | TEXT · key |
value | The 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
name | The genre. | TEXT · key |
position | Where 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
name | The studio or network. | TEXT · key |
position | Where 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
name | The country. | TEXT · key |
position | Where 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.
| Column | What it holds | Type |
|---|---|---|
id | The person: | TEXT · key |
name | Their name. | TEXT |
external_id | Their id in that catalog; empty when unlinked. | TEXT |
photo | The web address of their headshot; empty when no source had one. | TEXT |
filmography_at_ms | When their filmography was last fetched, in Unix milliseconds; 0 means never. | INTEGER |
filmography_retry_at_ms | After a failed filmography fetch, the time before which it is not tried again, in Unix milliseconds; 0 for none. | INTEGER |
namespace | The 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).
| Column | What it holds | Type |
|---|---|---|
person_id | The person (a library_people id). | TEXT · key · → library_people.id |
position | Where the title comes in their filmography, newest first, from 0. | INTEGER · key |
kind | movie or series. | TEXT |
external_id | The title’s id in that catalog. | TEXT |
title | The title’s name, in the household’s language when the catalog has it. | TEXT |
year | The year it came out; 0 when unknown. | INTEGER |
role | What they did on it, one of actor, creator, director or writer. | TEXT |
character_name | An actor’s part; empty for crew or when unknown. | TEXT |
namespace | The 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
position | Where the credit came in the list, from 0. | INTEGER · key |
person_id | The person (a library_people id). | TEXT · → library_people.id |
role | What they did, one of actor, creator, director or writer (or what a media server calls it). | TEXT |
character_name | An 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
name | The tag. | TEXT · key |
position | Where 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
path | The file’s full path. | TEXT · key |
container | The file format its header names, such as matroska; empty until the header is read. | TEXT |
size_bytes | The file’s size in bytes. | INTEGER |
bitrate | The file’s overall bitrate in bits per second; 0 when unknown. | INTEGER |
duration_ms | The file’s length in milliseconds from its header; 0 until the header is read. | INTEGER |
edition | Which version of the title this file is, such as a director’s cut; empty for the usual one. | TEXT |
plays | True 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 |
part | Which 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_sources.item_id |
path | The file the track belongs to (a library_sources path). | TEXT · key · → library_sources.path |
stream_index | The 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 |
kind | video, audio or subtitle. | TEXT |
codec | The codec the header names, such as hevc or eac3. | TEXT |
profile | The codec profile, such as Main 10 or DTS-HD MA; empty when the header gives none. | TEXT |
title | The track’s own title, if the file gives one. | TEXT |
is_default | True when the file marks the track to play by default. | BOOLEAN |
forced | True for a forced subtitle track. | BOOLEAN |
language | The track’s language as a two-letter code where one exists (en), else as the file gives it; empty when unknown. | TEXT |
language_name | The language’s English name, such as English; empty when unknown. | TEXT |
channels | An audio track’s channel count; 0 otherwise. | INTEGER |
channel_layout | An audio track’s channel layout, such as 5.1(side). | TEXT |
sample_rate | An audio track’s sample rate in Hz. | INTEGER |
audio_format | An audio track’s format in plain words, such as Dolby Digital Plus. | TEXT |
width | A video track’s width in pixels. | INTEGER |
height | A video track’s height in pixels. | INTEGER |
frame_rate | A video track’s frame rate as the header gives it, such as 24000/1001. | TEXT |
pixel_format | A video track’s pixel format, such as yuv420p10le. | TEXT |
color_transfer | A video track’s transfer characteristic, such as smpte2084. | TEXT |
resolution | A video track’s resolution label, such as 1080p or 4K (reading the width too, so a wide film is not undersold). | TEXT |
hdr | A video track’s dynamic range, such as Dolby Vision, HDR10 or HLG; empty for standard range. | TEXT |
commentary | True for a commentary track. | BOOLEAN |
hearing_impaired | True for a track for the deaf and hard of hearing, such as SDH subtitles. | BOOLEAN |
descriptive | True for an audio track that describes the picture. | BOOLEAN |
external_ref | For 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 |
bitrate | The 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_depth | Bits 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_sources.item_id |
path | The file the chapters are in (a library_sources path). | TEXT · key · → library_sources.path |
position | The chapter’s order, from 0. | INTEGER · key |
start_ms | Where the chapter starts, in milliseconds. | INTEGER |
end_ms | Where it ends, in milliseconds; -1 when the container does not say. | INTEGER |
title | The 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_sources.item_id |
path | The file the markers are for (a library_sources path). | TEXT · key · → library_sources.path |
position | The span’s order in the .edl, from 0. | INTEGER · key |
start_ms | Where the span starts, in milliseconds. | INTEGER |
end_ms | Where it ends, in milliseconds. | INTEGER |
kind | What 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_sources.item_id |
path | The file the thumbnails are for (a library_sources path). | TEXT · key · → library_sources.path |
ref | Where the trickplay file is, as an absolute path. | TEXT · key |
format | Its 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
kind | What 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 |
season | For a season-poster, the season it belongs to (0 is the specials); 0 for every other kind. | INTEGER · key |
position | The order among images of the same kind and season, from 0. | INTEGER · key |
ref | Where 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.
| Column | What it holds | Type |
|---|---|---|
provider | The catalog, tmdb for a movie or show, tmdb_collection for a collection. | TEXT · key |
kind | What the record is fetched as, one of movie, series or collection. | TEXT · key |
external_id | The id the catalog knows it by. | TEXT · key |
record | The 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_ms | When the record was last fetched, in Unix milliseconds; 0 means never. | INTEGER |
retry_at_ms | After a failed fetch, the time before which it is not tried again, in Unix milliseconds; 0 for none. | INTEGER |
version | The 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 |
language | The 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 |
changed | 1 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.
| Column | What it holds | Type |
|---|---|---|
provider | The catalog, such as tmdb. | TEXT · key |
read_at_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
inventory_item_id | The title searched for (a library_items id). | TEXT · key · → library_items.id |
rank | The result’s place in the list, from 0 for the best. | INTEGER · key |
kind | One of movie or series. | TEXT |
external_id | The result’s id in that catalog. | TEXT |
name | The result’s name. | TEXT |
year | The result’s year, or 0 when unknown. | INTEGER |
overview | The result’s description, or empty. | TEXT |
poster | The result’s poster URL at its catalog, or empty. | TEXT |
name_match | 1 when the result’s name agrees with what is on disk. | BOOLEAN |
year_match | 1 when the result’s year agrees with what is on disk. | BOOLEAN |
length_match | 1 when the rest of what is on disk agrees: a movie’s running time, or a show’s seasons and episodes. | BOOLEAN |
namespace | The 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.
| Column | What it holds | Type |
|---|---|---|
origin_authority | The scanned folder of the title’s scan origin, as in library_items. | TEXT · key |
origin_key | Where in that folder the title is, as in library_items. | TEXT · key |
kind | One of movie or series. | TEXT |
external_id | The title’s id in that catalog; empty when ignored. | TEXT |
ignored | 1 when a person said it is not a title. | BOOLEAN |
answered_at_ms | When the answer was given, in Unix milliseconds. | INTEGER |
answered_by | Who 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 |
namespace | The 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.
| Column | What it holds | Type |
|---|---|---|
series_catalog | The show’s catalog row, as provider:series:id (library_items.catalog). | TEXT · key |
choice | One 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_id | The catalog’s id for the order picked; empty unless choice is order. | TEXT |
answered_at_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the collection. library_collection_members points here. | TEXT · key |
name | The collection’s name. | TEXT |
description | The collection’s description, or empty. | TEXT |
imported_from | The media server it was brought over from, emby or jellyfin; empty when made in Loomarr. | TEXT |
created_at_ms | When the collection was made, in Unix milliseconds. | INTEGER |
updated_at_ms | When the collection was last changed, in Unix milliseconds. | INTEGER |
imported_id | The 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).
| Column | What it holds | Type |
|---|---|---|
collection_id | The collection (a library_collections id). | TEXT · key · → library_collections.id |
inventory_item_id | The title (a library_items id). | TEXT · key · → library_items.id |
position | The 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.
| Column | What it holds | Type |
|---|---|---|
key | Identifies the title, as series:tvdb:id for a show with a TVDB id, otherwise movie:tmdb:id or series:tmdb:id. | TEXT · key |
title_json | The 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 |
state | Where 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_id | The media server’s id for the title once it is in the library; empty before. | TEXT |
requested_at | When Seerr or an arr first accepted the request, in Unix seconds; 0 means not yet. | BIGINT |
deadline | When 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 |
attempts | How many times sending the request has failed. | INTEGER |
last_error | The last error sending or tracking the request, or empty. | TEXT |
updated_at | When the row last changed state, in Unix seconds. | BIGINT |
progress | How much of the download is done, from 0 to 1. | REAL |
eta_text | The download’s time left, as the arr words it; empty when unknown. | TEXT |
download_status | The arr’s queue status, such as downloading, warning or stalled; empty when none. | TEXT |
arrived_at | When 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_have | For a downloading show, how many wanted episodes are on disk; 0 otherwise. | INTEGER |
episodes_wanted | For 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.
| Column | What it holds | Type |
|---|---|---|
hash | The 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 |
origin | Where 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_url | The URL a remote image was downloaded from; empty for every other origin. | TEXT |
visibility | Who may fetch it. public needs no sign-in (a channel icon other apps fetch); member needs a signed-in person. | TEXT |
role | What 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 |
mime | The original’s file type, such as image/png. | TEXT |
width | The original’s width in pixels. | INTEGER |
height | The original’s height in pixels. | INTEGER |
bytes | The original’s size in bytes. | INTEGER |
animated | 1 when the original moves (a clip’s hover loop). | BOOLEAN |
placeholder | A tiny blurred preview (a ThumbHash, base64) shown while the image loads. | TEXT |
dominant_hex | The image’s average colour, as | TEXT |
meta | A JSON object of free-form notes about the image, such as attribution, licence or upstream ids. {} when there are none. | TEXT |
origin_fetched_at | When 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_at | When the image was first stored, in Unix seconds. | INTEGER |
updated_at | When the row last changed, in Unix seconds. | INTEGER |
last_used_at | When the image was last used, in Unix seconds. Older images lose their resized copies first when space runs short. | INTEGER |
frame_count | How many frames the original has; 1 for a still image. | INTEGER |
duration_ms | An animation’s length in milliseconds; 0 for a still image. | INTEGER |
loop_count | How 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.
| Column | What it holds | Type |
|---|---|---|
clip_hash | The clip (a clips hash). | TEXT · key · → clips.hash |
role | What the image is for, such as thumb. | TEXT · key |
image_hash | The 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.
| Column | What it holds | Type |
|---|---|---|
proposal_id | The proposal (a filler_split_proposals id). | TEXT · key · → filler_split_proposals.id |
role | What the image is for, such as thumb. | TEXT · key |
image_hash | The 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.
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel (a channels id). | TEXT · key · → channels.id |
role | What the image is for, icon or watermark. | TEXT · key |
image_hash | The 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title (a library_items id). | TEXT · key · → library_items.id |
role | What the image is for, one of poster, backdrop or logo. | TEXT · key |
image_hash | The 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The title the candidates are for (a library_items id). | TEXT · key · → library_items.id |
role | What the image is for, poster. | TEXT · key |
image_hash | The 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.
| Column | What it holds | Type |
|---|---|---|
image_hash | The original image (an images hash). | TEXT · key · → images.hash |
recipe | The encoder settings it was made with, such as loomarr-rendition-v2. Changed settings make new copies rather than reusing old ones. | TEXT · key |
format | The file format, one of avif, webp or jpeg. | TEXT · key |
width | The copy’s width in pixels. | INTEGER · key |
bytes | The copy’s size in bytes. | INTEGER |
output_hash | The SHA-256 of the copy’s bytes, in hex; empty for copies made before it was recorded. | TEXT |
path | Where the copy’s file is on disk. | TEXT |
animated | 1 when the copy keeps the original’s motion. | BOOLEAN |
created_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the item. Other inventory tables point here, and the library’s tables name it as their inventory item. | TEXT · key |
kind | What 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_at | When the item was first reported, in Unix seconds. | BIGINT |
updated_at | When 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.
| Column | What it holds | Type |
|---|---|---|
authority_id | The 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_id | The 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_id | The item the report belongs to (inventory_items.id). | TEXT · → inventory_items.id |
observation_json | What 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_at | When this report was taken, in Unix seconds. | BIGINT |
last_seen_at | When the importer last reported the item, in Unix seconds. | BIGINT |
missing_at | When 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.
| Column | What it holds | Type |
|---|---|---|
item_id | The item the id belongs to (inventory_items.id). | TEXT · → inventory_items.id |
authority_id | The importer whose report gave the id (part of inventory_item_origins). | TEXT · key · → inventory_item_origins.authority_id |
external_item_id | The importer’s id for the item (part of inventory_item_origins). | TEXT · key · → inventory_item_origins.external_item_id |
namespace | Which id system the id comes from, lowercased, such as tmdb, tvdb or imdb. | TEXT · key |
external_id | The 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the source. Other inventory tables and playout point here. | TEXT · key |
item_id | The item the source plays (inventory_items.id). | TEXT · → inventory_items.id |
kind | local_file for a file the library scan found, or library_original for a media server’s own copy of the item. | TEXT |
revision | Identifies 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_at | When the source was first reported, in Unix seconds. | BIGINT |
updated_at | When 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.
| Column | What it holds | Type |
|---|---|---|
authority_id | The importer that reported it (part of inventory_item_origins). | TEXT · key · → inventory_item_origins.authority_id |
external_item_id | The importer’s id for the item (part of inventory_item_origins). | TEXT · key · → inventory_item_origins.external_item_id |
external_source_id | The importer’s id for this source within the item. The media server’s source id, or file for a scanned file. | TEXT · key |
source_id | The source the report belongs to (inventory_sources.id). | TEXT · → inventory_sources.id |
locator_json | Where to find the source. A scanned file’s path, or the media server item and source ids (never a URL or credentials). | TEXT |
observation_json | What 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_at | When this report was taken, in Unix seconds. | BIGINT |
last_seen_at | When the importer last reported the source, in Unix seconds. | BIGINT |
missing_at | When 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.
| Column | What it holds | Type |
|---|---|---|
source_id | The source measured (inventory_sources.id). | TEXT · key · → inventory_sources.id |
source_revision | The source revision that was measured. | TEXT |
observation_json | The 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_at | When 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.
| Column | What it holds | Type |
|---|---|---|
source_id | The source analysed (inventory_sources.id). | TEXT · key · → inventory_sources.id |
source_revision | The source revision that was analysed. | TEXT |
schema_version | The 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 |
keyframes | The keyframe index (the points playback can start or cut at), packed as binary: each keyframe’s time in milliseconds and byte position. | BLOB |
keyframe_count | How many keyframes the index holds. | INTEGER |
integrated_lufs | The overall loudness of the audio, in LUFS. Empty when the source has no audio. | DOUBLE PRECISION · may be empty |
true_peak_dbtp | The loudest audio peak, in dBTP. Empty when the source has no audio or no peak was found. | DOUBLE PRECISION · may be empty |
breaks_json | The 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_at | When it was analysed, in Unix seconds. | BIGINT |
active_picture_json | The 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.
| Column | What it holds | Type |
|---|---|---|
hash | The 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 |
path | Where the file is, relative to the clip folder (for example a3/f9/ | TEXT |
tunarr_program_id | The Tunarr program id for this clip when Tunarr knows it; NULL on installs without Tunarr. | TEXT · may be empty |
duration_ms | Length of the clip in milliseconds, measured from the file. | INTEGER |
source | Where the clip came from: the id of the registered filler source that downloaded it (such as archive: | TEXT |
quality | Resolution 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 |
license | The licence URL the source declared for the clip. Empty means unknown, never public domain. | TEXT |
updated_at | When the row last changed, in Unix seconds. | INTEGER |
created_at | When 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.
| Column | What it holds | Type |
|---|---|---|
name | Display name: a title from the source or its sidecar file, a name built from grounded evidence, one a person wrote, or the filename. | TEXT |
role | What the clip is: unclassified (not yet known), commercial, bumper, station_id, psa, trailer, interstitial, political or news. | TEXT |
era | The year the clip is from, such as 1994; a year ending in 0 means the decade. 0 means not known. | INTEGER |
audience | Who the clip suits: kids, family, general or late_night. Empty when not yet tagged. | TEXT |
language | Spoken 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 |
transcript | The clip’s spoken words, once transcribed. Empty when not transcribed or wordless. | TEXT |
brand | The 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_text | Text 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_revision | A 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_source | Where 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.
| Column | What it holds | Type |
|---|---|---|
geographic_scope | How widely the clip aired: national, local (one station’s own material) or unknown. | TEXT |
country | The country the clip aired in, as a two-letter ISO code. Empty when not known. | TEXT |
network | The broadcast network the clip is tied to, when known from evidence. | TEXT |
station | The broadcast station the clip belongs to, as an id in Loomarr’s station directory. A local clip is local to this station. | TEXT |
air_date | The date the clip was broadcast, as YYYY-MM-DD, when known from evidence. | TEXT |
geo_evidence | The 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.
| Column | What it holds | Type |
|---|---|---|
removed_at | When 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 |
held | 1 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_at | When 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 |
placement | Where 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.
| Column | What it holds | Type |
|---|---|---|
is_composite | 1 when the file is a recorded break holding many ads. A composite never airs itself; the clips split out of it do. | BOOLEAN |
parent_hash | The 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.
| Column | What it holds | Type |
|---|---|---|
play_count | How 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_at | When 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.
| Column | What it holds | Type |
|---|---|---|
thumbnail | Still image file, relative to the thumbnail cache folder. Empty when not generated yet. | TEXT |
preview | Short animated preview shown on hover, relative to the preview cache folder. Empty when not generated yet. | TEXT |
thumb_image_hash | The image service’s id for the still image. Empty when not stored there yet. | TEXT |
hover_image_hash | The 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).
| Column | What it holds | Type |
|---|---|---|
clip_hash | The clip that carries the tag (clips.hash). | TEXT · key |
taxon | The tag (taxa.slug). | TEXT · key |
leaf | 1 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.
| Column | What it holds | Type |
|---|---|---|
slug | The tag’s stable id, such as beer. Tag rows elsewhere point here. | TEXT · key |
label | The tag’s display name. | TEXT |
parent | The slug of the broader tag above this one on the same axis. Empty for a top-level tag. | TEXT |
axis | Which 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 |
synonyms | JSON array of other words that mean this tag, so a tagger saying lager is read as beer. | TEXT |
aliases | JSON array of former slugs of this tag, so clips tagged under an old name still match after a rename. | TEXT |
updated_at | When 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.
| Column | What it holds | Type |
|---|---|---|
ancestor | The broader tag (taxa.slug). | TEXT · key |
descendant | A 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.
| Column | What it holds | Type |
|---|---|---|
version | The shipped tag batch’s number. | INTEGER · key |
applied_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | The source’s stable id, separate from its address so re-pointing a source keeps its history. | TEXT · key |
kind | The provider, archive (archive.org) or youtube. | TEXT |
uri | The archive.org identifier or URL, or the YouTube channel or playlist URL. | TEXT |
label | The source’s display name; when empty the address is shown instead. | TEXT |
license | The licence URL the source declares. Empty means unknown, never public domain. | TEXT |
last_fetched_at | When the source last had at least one item queued for download, in Unix seconds; 0 means never. | INTEGER |
created_at | When the source was added, in Unix seconds. | INTEGER |
enabled | 1 when the source is switched on. A switched-off source is not checked, searched or downloaded from. | BOOLEAN |
fetch_every_seconds | How 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_run | The most items to download from this source in one check. NULL uses the global setting. | INTEGER · may be empty |
country | The country the source serves, as a two-letter ISO code. Empty means it follows the install’s own location. | TEXT |
last_checked_at | When the last check of the source finished, even if it found nothing new, in Unix seconds; 0 means never. | INTEGER |
check_failure_count | How many checks in a row have failed since the last success. Sets how long to wait before retrying. | INTEGER |
check_retry_at | Earliest time to retry after a failed check, in Unix seconds; 0 means no wait. | INTEGER |
check_lease_until | While a check is running, the time its claim on the source expires, in Unix seconds; 0 means no check is running. | INTEGER |
scan_cursor | The 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_watermark | The provider’s id for the newest item seen by the last completed sweep. | TEXT |
scan_pending_watermark | The 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.
| Column | What it holds | Type |
|---|---|---|
kind | The provider, archive or youtube. | TEXT · key |
enabled | 1 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).
| Column | What it holds | Type |
|---|---|---|
source_id | The source checked (filler_sources.id). | TEXT · key · → filler_sources.id |
disposition | What 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_count | How 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the pull. | TEXT · key |
title | A short summary of what the pull is for. | TEXT |
reason | Why Loomarr proposed it, such as the coverage gap it fills. Shown above the plan. | TEXT |
proposed_by | Who or what proposed it, such as a user id or a job name. | TEXT |
status | pending, approved or dismissed. | TEXT |
note | The operator’s note narrowing the pull, given at approval. | TEXT |
plan_json | JSON 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_at | When the pull was proposed, in Unix seconds. | INTEGER |
decided_at | When it was approved or dismissed, in Unix seconds; 0 while pending. | INTEGER |
decided_by | Who 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.
| Column | What it holds | Type |
|---|---|---|
pull_id | The approved pull (filler_pulls.id). | TEXT · key · → filler_pulls.id |
acquisition_id | The 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the run. Downloaded files and prepared clips point here. | TEXT · key |
trigger | What 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_id | The registered source every item came from (filler_sources.id). Empty when items came from several sources or none. | TEXT |
pull_id | The approved pull that started it (filler_pulls.id). Empty for other triggers. | TEXT |
status | queued, running, success or error. | TEXT |
requested | How many items were asked for. | INTEGER |
fetched | How many files were downloaded. | INTEGER |
skipped | How many items were skipped, mostly because they were already downloaded. | INTEGER |
failed | How many items failed to download. | INTEGER |
empty_count | How many items returned no clips and no error, usually a mistyped id or an empty source. | INTEGER |
error | The error that ended the run. Empty when none. | TEXT |
started_at | When the run started, in Unix seconds. | INTEGER |
completed_at | When the run finished, in Unix seconds; 0 while still going. | INTEGER |
updated_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the downloaded file. | TEXT · key |
acquisition_id | The download run that produced it (filler_acquisition_runs.id). | TEXT · → filler_acquisition_runs.id |
source_id | The registered source it came from (filler_sources.id). Empty for a typed URL. | TEXT |
provider | The provider that downloaded it, such as archive or youtube. | TEXT |
source_url | The address that was downloaded. | TEXT |
staging_path | Where the file was put while it was being checked, relative to the clip folder. Empty when not staged. | TEXT |
media_path | Where the media file is published, relative to the clip folder. | TEXT |
sidecar_path | The info file downloaded beside the media, relative to the clip folder. Empty when there is none. | TEXT |
media_sha256 | The SHA-256 hash of the whole file, in hex. | TEXT |
media_bytes | The file’s size in bytes. | INTEGER |
clip_hash | The clip it became (clips.hash). Empty until it is catalogued. | TEXT |
state | staged (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_reason | Why the file needs repair. Empty otherwise. | TEXT |
completed_at | When the download finished, in Unix seconds. | INTEGER |
updated_at | When the row last changed, in Unix seconds. | INTEGER |
provider_archive_entry | The line the downloader records so it never downloads this item again. Empty when the provider gives none. | TEXT |
provider_archive_committed | 1 once that line has been saved to the downloader’s record. | BOOLEAN |
remote_id | The provider’s id for the item. Empty for older downloads and typed URLs. | TEXT |
gap | The 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.
| Column | What it holds | Type |
|---|---|---|
item_key | Identifies the item: the provider’s id for it within its source, or its address for a typed URL. | TEXT · key |
provider | The provider that downloads it, such as archive or youtube. | TEXT |
source_id | The registered source it came from (filler_sources.id). Empty for a typed URL. | TEXT |
remote_id | The provider’s id for the item. Empty for a typed URL. | TEXT |
url | The address to download. | TEXT |
duration_ms | The length the listing gave, in milliseconds; 0 when it gave none. | INTEGER |
height | The picture height the listing gave, in pixels; 0 when it gave none. | INTEGER |
gap | The channel coverage gap the download was for, such as era:1990-1999. Empty when it was not chosen for a gap. | TEXT |
failures | How many attempts have failed. Waiting for room does not count. | INTEGER |
first_failed_at | When the first attempt failed, in Unix seconds; 0 if none has. | INTEGER |
last_failed_at | When the latest attempt failed, in Unix seconds; 0 if none has. | INTEGER |
retry_at | The earliest the item is tried again, in Unix seconds. | INTEGER |
last_error | Why 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.
| Column | What it holds | Type |
|---|---|---|
clip_hash | The clip being prepared (clips.hash). | TEXT · key |
stage | The step the clip is at, in order: probe, transcode, split, screen, defects, language, transcribe, vision, finish. | TEXT |
status | How the current step is going: queued, running, done, failed (retried after a wait) or skipped (did not apply). | TEXT |
progress | Percent done within the current step, 0 to 100. | INTEGER |
disposition | The 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_reason | Why 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_detail | The measurement behind the rejection, such as a duration under the minimum or the language heard. | TEXT |
attempts | How many times the current step has been tried. Resets when the clip moves on. | INTEGER |
next_run | When the row may next be worked on, in Unix seconds; 0 means now. | INTEGER |
stages_json | JSON 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_at | When the clip entered preparation, in Unix seconds. | INTEGER |
updated_at | When the row last changed, in Unix seconds. | INTEGER |
force_run | 1 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_id | The download run that brought the clip in (filler_acquisition_runs.id). Empty for clips dropped into the folder by hand. | TEXT |
preparation_attempt | Which attempt at preparing the clip this is, counting from 1. 0 for clips enrolled before this was tracked. | INTEGER |
preparation_started_at | When the current preparation attempt began, in Unix seconds. | INTEGER |
preparation_start_reason | Why the current attempt began: enrollment (the clip was new) or restart. | TEXT |
preparation_progress | Percent done across the whole preparation attempt; -1 means unknown. | INTEGER |
stage_queued_at | When the current step was queued, in Unix seconds. | INTEGER |
stage_started_at | When the current step started running, in Unix seconds. | INTEGER |
files_deleted_at | When 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).
| Column | What it holds | Type |
|---|---|---|
a_hash | One clip of the pair (clips.hash); always the smaller hash. | TEXT · key |
b_hash | The 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.
| Column | What it holds | Type |
|---|---|---|
clip_hash | The clip fingerprinted (clips.hash). | TEXT · key |
algorithm | The fingerprint method and its version. | TEXT · key |
fingerprint_json | JSON 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.
| Column | What it holds | Type |
|---|---|---|
clip_hash | The clip checked (clips.hash). | TEXT · key |
measurement_version | The version of the measuring method. Clips measured under an older one are measured again. | TEXT |
floor_version | The version of the quality bars the clip was judged against. | TEXT |
era | The clip era the verdict was judged against. | INTEGER |
outcome | What the detectors decided: pass, fail, borderline (between the bars) or undecided (too little picture to judge). | TEXT |
readings_json | JSON document of the raw measurements, such as blockiness, blur, dropouts, interlacing, upscaling and audio clipping. | TEXT |
assessment_json | JSON document of the era bar used and each check’s outcome, measured value and bar. | TEXT |
measured_at | When the clip was measured, in Unix seconds. | INTEGER |
judgement_json | JSON document of a vision model’s ruling on a borderline clip and its reason. Empty until there is one. | TEXT · may be empty |
judge_failures | How many times asking the vision model brought back no ruling since the last one. | INTEGER |
verdict | The final standing: pass, fail, undecided (too little picture; allowed to air) or pending (a borderline clip waiting for a ruling). | TEXT |
verdict_reason | What decided the verdict, such as the detectors or the judge’s reason. | TEXT |
label | A maintainer’s own pass or fail label for the clip, used to tune the bars. Empty when unlabelled. | TEXT |
label_era | The clip era the label was given against; 0 when unlabelled. | INTEGER |
labelled_at | When 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.
| Column | What it holds | Type |
|---|---|---|
clip_hash | The losing copy’s former clip hash. | TEXT · key |
source_hash | The 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_hash | The copy it lost to (clips.hash). | TEXT |
decided_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the event, as ready: | TEXT · key |
clip_hash | The clip that became ready (clips.hash). | TEXT |
acquisition_id | The download run that brought the clip in (filler_acquisition_runs.id). Empty for clips from a folder. | TEXT |
enrollment_kind | What let the clip into filler: acquisition (a download run) or source (a clip folder or source the operator chose). | TEXT |
enrollment_ref | The download run id or source id behind enrollment_kind. | TEXT |
placement | Where it may be scheduled, break_body or bookend. | TEXT |
created_at | When 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.
| Column | What it holds | Type |
|---|---|---|
source_id | The offered source (filler_sources.id). | TEXT · key |
list_version | The version of the shipped list that first offered it. | TEXT |
offered_at | When it was offered, in Unix seconds. | INTEGER |
stations | Comma-separated station ids the shipped list ties the source to, refreshed on every offer. | TEXT |
local_state | What 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).
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel it aired on. | TEXT · key |
clip_hash | The clip that aired (clips.hash). | TEXT · key |
aired_at_ms | When the clip was scheduled to start, in Unix milliseconds. | INTEGER · key |
recorded_at_ms | When 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.
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel the break is on (channels.id). | TEXT · key · → channels.id |
start_ms | When the break starts on the channel’s timeline, in Unix milliseconds. | INTEGER · key |
ceiling_ms | The slot length the pod was assembled to, in milliseconds. | INTEGER |
length_ms | The media the pod holds, in milliseconds. The break slot is this long once planned. | INTEGER |
pod_json | The break’s clips in order, as the pod assembler returned them. | TEXT |
planned_at | When 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.
| Column | What it holds | Type |
|---|---|---|
clip_hash | The clip researched (clips.hash). | TEXT · key |
producer | What made the suggestion: the research model (context-model: | TEXT · key |
producer_version | The producer’s version; for an observation, what the guess came from (vision or split). | TEXT · key |
adapter | The search service used to find pages, such as web. Empty for an observation. | TEXT · key |
adapter_version | The search service’s version. Empty for an observation. | TEXT · key |
input_revision | The clip’s enrichment_revision the research was based on. | INTEGER · key |
report_json | JSON 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_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the station. fcc: and the FCC facility id, or wd: and the Wikidata id for a station only Wikidata knows. | TEXT · key |
country | The two-letter country code (US territories have their own, such as PR). | TEXT |
city | The station’s community of license. | TEXT |
region | The state or territory postal code. | TEXT |
latitude | Latitude of the community of license. | REAL |
longitude | Longitude 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).
| Column | What it holds | Type |
|---|---|---|
station_id | The station (broadcast_stations.id). | TEXT · key · → broadcast_stations.id |
call_sign | The base call sign without suffixes such as -TV, for example WEWS. | TEXT · key |
from_date | The first day the station used it, as YYYY-MM-DD. Empty when unknown. | TEXT · key |
to_date | The 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.
| Column | What it holds | Type |
|---|---|---|
version | The revision number of the shipped station directory. | INTEGER · key |
applied_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the call. Spoken-safety events and the structure call journal point here. | TEXT · key |
cache_key | SHA-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_hash | The 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_id | The batch run the call belonged to, used for the per-run spend ceiling. Empty when the call was not part of a run. | TEXT |
role | Which check made the call, for example defect_judge or complete_timeline_structure. | TEXT |
rung | The step within that check, for example defects or window_video. Empty when the check has no steps. | TEXT |
state | Where 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_ms | How long the call took, in milliseconds. | INTEGER |
attempts | How many times the request was sent to the provider. | INTEGER |
outcome | window_complete once a structure-window call has answered; empty for every other call. | TEXT |
failure_reason | Why the call did not complete, for example a spend ceiling or a failure class such as transport. Empty on success. | TEXT |
created_at | When the call was reserved, in Unix seconds. Also decides which day’s ceiling it counts against (UTC). | INTEGER |
updated_at | When 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.
| Column | What it holds | Type |
|---|---|---|
requested_provider | The AI provider Loomarr asked, such as ollama or openrouter. | TEXT |
requested_model | The model Loomarr asked for. | TEXT |
resolved_provider | The provider that actually answered, as reported back. Empty until the call settles. | TEXT |
resolved_model | The model that actually answered, as reported back. Empty until the call settles. | TEXT |
upstream_provider | The provider behind a routing service (such as OpenRouter) that served the call. Empty when not known. | TEXT |
generation_id | The provider’s own ID for the response, for matching against its billing. Empty when none was given. | TEXT |
capability_snapshot | Identity 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.
| Column | What it holds | Type |
|---|---|---|
modalities_json | JSON list of the kinds of input sent, such as text, image, audio or video. | TEXT |
derivative_bytes | Size in bytes of the media excerpt sent to the model. 0 when none was sent. | INTEGER |
derivative_duration_ms | Length of the media excerpt sent, in milliseconds. 0 when none was sent. | INTEGER |
derivative_pixels | Pixel 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.
| Column | What it holds | Type |
|---|---|---|
prompt_tokens | Input tokens the provider reported. | INTEGER |
completion_tokens | Output tokens the provider reported. | INTEGER |
reasoning_tokens | Reasoning tokens the provider reported. | INTEGER |
cached_tokens | Input tokens served from the provider’s prompt cache. | INTEGER |
cache_write_tokens | Input tokens written into the provider’s prompt cache. | INTEGER |
image_tokens | Tokens the provider counted for images. | INTEGER |
audio_tokens | Tokens the provider counted for audio. | INTEGER |
video_tokens | Tokens 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.
| Column | What it holds | Type |
|---|---|---|
charged_amount | The charge exactly as the provider reported it, as a decimal string. Empty when no charge was reported. | TEXT |
charged_currency | Currency of the reported charge, USD when known. Empty when no charge was reported. | TEXT |
charged_nano_usd | The reported charge in billionths of a US dollar. 0 when nothing was charged or reported. | INTEGER |
estimated_nano_usd | Loomarr’s own estimate of the cost, in billionths of a US dollar. | INTEGER |
reserved_nano_usd | The 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_snapshot | Fingerprint 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.
| Column | What it holds | Type |
|---|---|---|
evidence_version | Version of the evidence (measurements or media set) the call was based on. | TEXT |
extractor_version | Version of the step that prepared the media excerpt. | TEXT |
prompt_version | Version or fingerprint of the prompt sent. | TEXT |
schema_version | Version or fingerprint of the answer format the model was asked to follow. | TEXT |
taxonomy_version | Version of the vocabulary or bar the answer was judged against. | TEXT |
admission_policy_version | Version of the rules that decide what the answer is allowed to unlock. | TEXT |
role_policy_version | Version 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.
| Column | What it holds | Type |
|---|---|---|
scope | The ceiling being guarded, written as clip: | 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).
| Column | What it holds | Type |
|---|---|---|
clip_hash | The filler clip described (clips.hash). | TEXT · key |
axis | Which 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 |
state | complete 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_json | The answer as JSON, using one of text, year, tags (taxonomy slugs) or geography (scope, country, network, station, airDate), depending on the axis. | TEXT |
evidence_kind | Where 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_rank | The strength of evidence_kind as a number, 1 (inference) to 6 (operator). | INTEGER |
evidence_reference | What the answer was based on, such as the source listing or the admin screen an edit came through. | TEXT |
confidence | How sure the producer was, from 0 to 100. Operator edits are 100. | INTEGER |
producer | The pass that gave the answer, for example deterministic-metadata, a text-model pass or operator. | TEXT |
producer_version | Version of that producer’s rules or prompt. | TEXT |
taxonomy_version | Version of the tag vocabulary the answer was checked against. Empty when the axis does not use tags. | TEXT |
observed_at | When the producer made the observation, in Unix seconds. | INTEGER |
updated_at | When 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.
| Column | What it holds | Type |
|---|---|---|
clip_hash | The filler clip the pass ran on (clips.hash). | TEXT · key |
producer | The pass that ran, for example deterministic-metadata or a text-model pass. | TEXT · key |
producer_version | Version of that pass’s rules or prompt. | TEXT · key |
taxonomy_version | Version of the tag vocabulary the pass used. Empty when it used none. | TEXT · key |
completed_at | When the pass finished, in Unix seconds. | INTEGER |
input_revision | The 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).
| Column | What it holds | Type |
|---|---|---|
clip_hash | The filler clip researched (clips.hash). | TEXT · key · → clips.hash |
input_revision | The clip’s enrichment revision the search was made for. | INTEGER · key |
adapter_version | Identity of the web-search setup used, including the provider (and, for SearXNG, a short fingerprint of its address). | TEXT · key |
provider | The web-search provider. brave (Brave Search) or searxng (a SearXNG server). | TEXT |
reserved_at | When 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.
| Column | What it holds | Type |
|---|---|---|
month | The month counted, as YYYY-MM in UTC. | TEXT · key |
request_count | Web searches made that month, including an admin’s connection test. | INTEGER |
last_provider | The provider used for the most recent search, brave or searxng. Empty before the first. | TEXT |
last_success_at | When a search last succeeded that month, in Unix seconds. 0 means none yet. | INTEGER |
last_failure_at | When 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.
| Column | What it holds | Type |
|---|---|---|
request_sha256 | SHA-256 of the exact request. With attempt, identifies the row. | TEXT · key |
attempt | Which 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_id | The matching cost record (filler_inference_evaluations.id). | TEXT · → filler_inference_evaluations.id |
source_sha256 | SHA-256 of the reel’s source file. | TEXT |
media_set_sha256 | SHA-256 of the full set of windows planned for the reel. | TEXT |
window_ordinal | Which window of the reel this call covers, from 0. | INTEGER |
assessor_id | Identity of the model setup that assessed the window. | TEXT |
state | open 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_json | The full booked request as JSON, including the media set, window, model, prompt and schema fingerprints, and the spend requested. | TEXT |
record_sha256 | SHA-256 of the settled record. Empty until settled. | TEXT |
record_json | The 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_at | When the call was booked, in Unix seconds. | INTEGER |
assessed_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the proposal. | TEXT · key |
clip_hash | The compilation reel being split (clips.hash). | TEXT |
segments_json | JSON 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_at | When the proposal was made, in Unix seconds. | INTEGER |
claim_token | A lock held while a confirm is publishing the segments, so two confirms cannot run at once. Empty when unlocked. | TEXT |
claim_expires_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the decision, derived from its content. | TEXT · key |
proposal_id | The split proposal it was made for (filler_split_proposals.id). The proposal may since have been removed. | TEXT |
clip_hash | The compilation reel (clips.hash). | TEXT |
source_sha256 | SHA-256 of the reel’s source file. Empty when not known. | TEXT |
assessment_sha256 | SHA-256 of the structure assessment the new rule used. Empty when there was none. | TEXT |
policy_version | Version of the splitting policy compared. | TEXT |
decision_json | The 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_at | When 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the destination. Delivery attempts refer to it by this id. | TEXT · key |
means | The 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 |
label | The name shown for the destination, also shown in delivery history in place of its address. | TEXT |
scope | installation for a destination an admin set up for the whole household; person for one that belongs to a single person. | TEXT |
owner_id | The person a person-scoped destination belongs to, from users. Empty for installation scope. | TEXT |
audience | Who 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_json | A 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 |
enabled | 1 when the destination is switched on. A disabled destination receives nothing. | BOOLEAN |
configuration_json | A JSON object of the destination’s non-secret settings, such as a server address or topic name. | TEXT |
credentials_encrypted | The destination’s secret settings (tokens, passwords, webhook URLs, push keys), encrypted with Loomarr’s encryption key. Unreadable without that key. | TEXT |
created_at | When the destination was added, in Unix seconds. | BIGINT |
updated_at | When the destination was last changed, in Unix seconds. | BIGINT |
credential_keys_json | A 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_fingerprint | For 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the notification. notification_delivery_attempts point here. | TEXT · key |
topic | What 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_kind | Who 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_id | The invitation’s id, the person’s id, or the literal word approvers or operators. For a delivery test, the destination’s id. | TEXT |
reference_kind | The 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_id | The id of the thing it is about, in the table that reference_kind names. | TEXT |
recipient_policy | mandatory_account for account and security email that always goes out; configurable for everything else, which only reaches destinations that chose the topic. | TEXT |
template_json | A 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_key | A unique key built from the event and recipient, so the same event never makes two notifications for the same recipient. | TEXT |
created_at | When the notification was recorded, in Unix seconds. | BIGINT |
terminal_at | When 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).
| Column | What it holds | Type |
|---|---|---|
id | Identifies the attempt. | TEXT · key |
intent_id | The notification being sent, from notification_intents. | TEXT · → notification_intents.id |
means | The kind of service used, with the same values as notification_destinations.means. | TEXT |
destination_ref | Which 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_redacted | A safe name for the destination to show in delivery history, such as its label. Never the address or a secret. | TEXT |
status | queued 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_number | Which try this is, from 1 to 5. | INTEGER |
available_at | When the attempt may be sent, in Unix seconds. Later for retries, which wait before trying again. | BIGINT |
lease_owner | Which worker is sending it. Empty unless status is sending. | TEXT |
lease_expires_at | When 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_at | When a worker started sending it, in Unix seconds. 0 if never started. | BIGINT |
finished_at | When it was delivered, failed or suppressed, in Unix seconds. 0 while queued or sending. | BIGINT |
provider_message_id | The id the service gave the sent message, when it gave one. Empty otherwise. | TEXT |
failure_class | Why 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_code | What 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_at | When 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.
| Column | What it holds | Type |
|---|---|---|
title_key | The title asked for (a titles key). | TEXT · key · → titles.key |
person_id | The person who asked for it, from users. | TEXT · key · → users.id |
created_at | When 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.
| Column | What it holds | Type |
|---|---|---|
channel_id | The channel asked for (a channels id). | TEXT · key · → channels.id |
person_id | The person who asked for it, from users. | TEXT · key · → users.id |
created_at | When 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.
| Column | What it holds | Type |
|---|---|---|
key | The 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 |
value | The 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_at | When the value was last written, in Unix seconds. 0 means unknown (written before this was tracked). | BIGINT |
updated_by | The id of the person who last changed it, pointing at users. Empty for values Loomarr wrote itself. | TEXT · may be empty |
env_override | 1 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.
| Column | What it holds | Type |
|---|---|---|
singleton | Always 1; the table holds at most one row. | INTEGER · key |
installation_key_fingerprint | A 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the key. Each encrypted value names the key that sealed it. | TEXT · key |
wrapped_key | The data key, encrypted with the installation key (begins with loomarr-dek:v1:). | TEXT |
active | 1 for the one key used to encrypt new secrets; 0 for retired keys kept for reading. | BOOLEAN |
created_at | When 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.
| Column | What it holds | Type |
|---|---|---|
name | The task’s name, such as housekeeping. | TEXT · key |
last_run | When the task last started, in Unix seconds. 0 means it has never run. | BIGINT |
last_result | How the last run ended. ok means it succeeded, error means it failed, empty means it hasn’t run yet. | TEXT |
last_error | The error message from the last run, or empty if it succeeded. | TEXT |
next_run | When the task is next due, in Unix seconds. Advanced when a run is claimed, so the same run doesn’t start twice. | BIGINT |
updated_at | When the row was last written, in Unix seconds. | BIGINT |
paused | 1 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the operation; the app polls it by this id. | TEXT · key |
kind | What the operation is. llm_pull downloads an AI model; filler_split detects split points in a filler clip. | TEXT |
subject | What it acts on. The model name for llm_pull, the filler clip’s id for filler_split. | TEXT |
status | queued (accepted, not started), running, success, or error. | TEXT |
percent | How far along it is, 0 to 100. For a model download, -1 means the size isn’t known yet. | INTEGER |
completed | For a model download, bytes downloaded so far of the current layer. 0 otherwise. | BIGINT |
total | For a model download, total bytes of the current layer. 0 otherwise. | BIGINT |
result_id | For filler_split, the id of the split proposal it produced. Empty otherwise. | TEXT |
error | Why it failed, or empty. | TEXT |
started_at | When it was started, in Unix seconds. | BIGINT |
completed_at | When it finished, in Unix seconds. 0 while it is still queued or running. | BIGINT |
updated_at | When 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).
| Column | What it holds | Type |
|---|---|---|
id | Identifies the entry. | TEXT · key |
at | When it happened, in Unix seconds. | BIGINT |
kind | Which part of Loomarr wrote it: title, channel, proposal (a suggested lineup), or system. | TEXT |
level | How serious it is, which sets the coloured dot. info, warn, or error. | TEXT |
text | The sentence shown in the feed. | TEXT |
subject_id | The 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the event. | TEXT · key |
occurred_at_ms | When it happened, in Unix milliseconds (by the sender’s clock for app events). | BIGINT |
received_at_ms | When the server recorded it, in Unix milliseconds. | BIGINT |
level | How serious it is. debug, info, warn, or error. | TEXT |
source | Which program observed it. server, web, android_tv, apple_tv, ios, or android. | TEXT |
subsystem | The part of Loomarr it came from, or empty. | TEXT |
event | The event’s name, such as player.media_error for an app playback error. | TEXT |
message | The human-readable message, or empty. | TEXT |
request_id | The API request it belongs to, or empty. | TEXT |
playback_session_id | The app playback session it belongs to, or empty. | TEXT |
channel_id | The channel it concerns, or empty. | TEXT |
schedule_block_id | The schedule block it concerns, or empty. | TEXT |
job_id | The background job it belongs to, or empty. | TEXT |
process_run_id | The process run it belongs to, pointing at diagnostic_process_runs, or empty. | TEXT |
actor_id | Who caused it, usually a user id. App events sent with an API token say api_token. Empty when no one is involved. | TEXT |
instance_id | Which Loomarr server recorded it, when known. | TEXT |
attributes_json | Extra details as a JSON object of named values (secrets removed before storage), such as the app version and platform for app events. | TEXT |
size_bytes | The 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the run. | TEXT · key |
purpose | What the program was run for, such as playout_program, filler_transcode or capacity_probe. | TEXT |
parent_run_id | The run that started this one, pointing at another row here, or empty. | TEXT |
instance_id | Which Loomarr server ran it. | TEXT |
channel_id | The channel it was working for, or empty. | TEXT |
target | What it was working on, such as an encoder name or output profile, or empty. | TEXT |
schedule_block_id | The schedule block it was playing, or empty. | TEXT |
job_id | The background job it belongs to, or empty. | TEXT |
executable | The program that ran. | TEXT |
executable_version | The program’s reported version, or empty if not detected. | TEXT |
command_summary | The command line it was run with, shortened and with secrets removed. | TEXT |
started_at_ms | When it started, in Unix milliseconds. | BIGINT |
ended_at_ms | When it ended, in Unix milliseconds. 0 while it is still running. | BIGINT |
status | running, succeeded, failed, cancelled, or interrupted (Loomarr stopped before recording how it ended, so the outcome is unknown). | TEXT |
exit_code | The program’s exit code when it failed with one; empty otherwise. | INTEGER · may be empty |
termination_reason | Why the run ended or was stopped, when Loomarr knows, or empty. | TEXT |
first_error | The first line the program printed, or the error if it printed nothing. | TEXT |
last_error | The last line the program printed, or the error it failed with. | TEXT |
output_ref | The name of its output file in the diagnostics folder, or empty if output wasn’t saved. | TEXT |
output_bytes | The size of the output file, in bytes. | BIGINT |
discarded_lines | How many output lines were dropped to keep the output file within its size cap. | BIGINT |
updated_at_ms | When the row was last updated, in Unix milliseconds. | BIGINT |
size_bytes | The 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.
| Column | What it holds | Type |
|---|---|---|
scope | Which table the total covers. events for diagnostic_events, process_runs for diagnostic_process_runs. | TEXT · key |
bytes | The 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.
| Column | What it holds | Type |
|---|---|---|
id | Identifies the report. | TEXT · key |
received_at_ms | When the server received it, in Unix milliseconds. | BIGINT |
occurred_at_ms | When the crash happened by the device’s clock, in Unix milliseconds. | BIGINT |
user_id | The 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_id | The paired device that sent it, matching device_tokens.token_hash. Empty when not sent from a paired device. | TEXT |
device_name | The paired device’s name at the time, or empty. | TEXT |
kind | What 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 |
platform | Which app sent it. ios, android, tvos (Apple TV), or android-tv. | TEXT |
app_version | The app’s version. | TEXT |
app_build | The app’s build number. | TEXT |
os_version | The device’s operating system version. | TEXT |
device_model | The device’s model. | TEXT |
message | The error message. | TEXT |
stack | The stack trace, or empty. | TEXT |
breadcrumbs_json | A 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_payload | For 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.
| Column | What it holds | Type |
|---|---|---|
recorded_at | The day counted, as the Unix time in seconds of that day’s midnight (UTC). | INTEGER · key |
table_name | The table counted. Every table except goose_db_version and this one. | TEXT · key |
row_count | How 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).