Offline Database Design
Entity Relationship Diagram
erDiagram
servers ||--o{ users : "has"
servers ||--o{ libraries : "has"
libraries ||--o{ items : "contains"
items ||--o{ items : "parent_of"
items ||--o{ user_data : "has"
items ||--o{ downloads : "has"
items ||--o{ media_streams : "has"
items ||--o{ thumbnails : "has"
users ||--o{ user_data : "owns"
users ||--o{ downloads : "owns"
users ||--o{ sync_queue : "owns"
servers {
int id PK
string jellyfin_id UK
string name
string url
string version
datetime last_sync
}
users {
int id PK
string jellyfin_id
int server_id FK
string name
boolean is_active
}
libraries {
int id PK
string jellyfin_id
int server_id FK
string name
string collection_type
string image_tag
}
items {
int id PK
string jellyfin_id
int server_id FK
int library_id FK
int parent_id FK
string type
string name
string sort_name
string overview
int production_year
float community_rating
string official_rating
int runtime_ticks
string primary_image_tag
string backdrop_image_tag
string album_id
string album_name
string album_artist
json artists
json genres
int index_number
int parent_index_number
string premiere_date
json metadata_json
datetime created_at
datetime updated_at
datetime last_sync
}
user_data {
int id PK
int item_id FK
int user_id FK
int position_ticks
int play_count
boolean is_favorite
boolean played
datetime last_played
datetime updated_at
datetime synced_at
}
downloads {
int id PK
int item_id FK
int user_id FK
string file_path
int file_size
string status
float progress
int priority
string error_message
datetime created_at
datetime completed_at
}
media_streams {
int id PK
int item_id FK
int stream_index
string type
string codec
string language
string display_title
boolean is_default
boolean is_forced
boolean is_external
}
sync_queue {
int id PK
int user_id FK
string operation
string entity_type
string entity_id
json payload
datetime created_at
int attempts
datetime last_attempt
string status
}
thumbnails {
int id PK
int item_id FK
string image_type
string image_tag
string file_path
int width
int height
datetime cached_at
}
Table Definitions
servers
Stores connected Jellyfin server information.
CREATE TABLE servers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
jellyfin_id TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
url TEXT NOT NULL,
version TEXT,
last_sync DATETIME,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
users
Stores user accounts per server. Access tokens are stored separately in secure storage (see 09-security.md).
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
jellyfin_id TEXT NOT NULL,
server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
name TEXT NOT NULL,
is_active BOOLEAN DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(jellyfin_id, server_id)
);
libraries
Stores library/collection metadata.
CREATE TABLE libraries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
jellyfin_id TEXT NOT NULL,
server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
name TEXT NOT NULL,
collection_type TEXT,
image_tag TEXT,
sort_order INTEGER DEFAULT 0,
last_sync DATETIME,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(jellyfin_id, server_id)
);
CREATE INDEX idx_libraries_server ON libraries(server_id);
items
Main table for all media items (movies, episodes, albums, songs, etc.).
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
jellyfin_id TEXT NOT NULL,
server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
library_id INTEGER REFERENCES libraries(id) ON DELETE SET NULL,
parent_id INTEGER REFERENCES items(id) ON DELETE CASCADE,
-- Basic metadata
type TEXT NOT NULL,
name TEXT NOT NULL,
sort_name TEXT,
overview TEXT,
-- Media info
production_year INTEGER,
community_rating REAL,
official_rating TEXT,
runtime_ticks INTEGER,
-- Images
primary_image_tag TEXT,
backdrop_image_tag TEXT,
-- Audio-specific
album_id TEXT,
album_name TEXT,
album_artist TEXT,
artists TEXT, -- JSON array
-- Series/Season-specific
index_number INTEGER,
parent_index_number INTEGER,
series_id TEXT,
series_name TEXT,
season_id TEXT,
-- Additional
genres TEXT, -- JSON array
premiere_date TEXT,
metadata_json TEXT,
-- Sync tracking
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
last_sync DATETIME,
UNIQUE(jellyfin_id, server_id)
-- Logical container (migration 027): episode → season/series, season →
-- series, track → album, else parent. See "Listing query shape".
-- container_id TEXT GENERATED ALWAYS AS (CASE item_type … END) VIRTUAL
);
-- Performance indexes
CREATE INDEX idx_items_container ON items(container_id, sort_name, name);
CREATE INDEX idx_items_server ON items(server_id);
CREATE INDEX idx_items_library ON items(library_id);
CREATE INDEX idx_items_parent ON items(parent_id);
CREATE INDEX idx_items_type ON items(type);
CREATE INDEX idx_items_album ON items(album_id);
CREATE INDEX idx_items_series ON items(series_id);
CREATE INDEX idx_items_name ON items(name COLLATE NOCASE);
-- Full-text search
CREATE VIRTUAL TABLE items_fts USING fts5(
name,
overview,
artists,
album_name,
album_artist,
content='items',
content_rowid='id'
);
-- Triggers to keep FTS in sync
CREATE TRIGGER items_ai AFTER INSERT ON items BEGIN
INSERT INTO items_fts(rowid, name, overview, artists, album_name, album_artist)
VALUES (new.id, new.name, new.overview, new.artists, new.album_name, new.album_artist);
END;
CREATE TRIGGER items_ad AFTER DELETE ON items BEGIN
INSERT INTO items_fts(items_fts, rowid, name, overview, artists, album_name, album_artist)
VALUES ('delete', old.id, old.name, old.overview, old.artists, old.album_name, old.album_artist);
END;
CREATE TRIGGER items_au AFTER UPDATE ON items BEGIN
INSERT INTO items_fts(items_fts, rowid, name, overview, artists, album_name, album_artist)
VALUES ('delete', old.id, old.name, old.overview, old.artists, old.album_name, old.album_artist);
INSERT INTO items_fts(rowid, name, overview, artists, album_name, album_artist)
VALUES (new.id, new.name, new.overview, new.artists, new.album_name, new.album_artist);
END;
media_streams
Stores subtitle and audio track information for items.
CREATE TABLE media_streams (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
stream_index INTEGER NOT NULL,
type TEXT NOT NULL,
codec TEXT,
language TEXT,
display_title TEXT,
is_default BOOLEAN DEFAULT 0,
is_forced BOOLEAN DEFAULT 0,
is_external BOOLEAN DEFAULT 0,
path TEXT,
UNIQUE(item_id, stream_index)
);
CREATE INDEX idx_media_streams_item ON media_streams(item_id);
user_data
Stores per-user data for items (favorites, progress, play count).
CREATE TABLE user_data (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- Playback state
position_ticks INTEGER DEFAULT 0,
play_count INTEGER DEFAULT 0,
played BOOLEAN DEFAULT 0,
last_played DATETIME,
-- User preferences
is_favorite BOOLEAN DEFAULT 0,
user_rating REAL,
-- Sync tracking
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
synced_at DATETIME,
needs_sync BOOLEAN DEFAULT 0,
UNIQUE(item_id, user_id)
);
CREATE INDEX idx_user_data_item ON user_data(item_id);
CREATE INDEX idx_user_data_user ON user_data(user_id);
CREATE INDEX idx_user_data_needs_sync ON user_data(needs_sync) WHERE needs_sync = 1;
CREATE INDEX idx_user_data_favorites ON user_data(user_id, is_favorite) WHERE is_favorite = 1;
CREATE INDEX idx_user_data_in_progress ON user_data(user_id, position_ticks)
WHERE position_ticks > 0 AND played = 0;
downloads
Tracks downloaded media files.
CREATE TABLE downloads (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
file_path TEXT,
file_size INTEGER,
file_hash TEXT,
status TEXT NOT NULL DEFAULT 'pending',
progress REAL DEFAULT 0,
bytes_downloaded INTEGER DEFAULT 0,
transcode_profile TEXT,
priority INTEGER DEFAULT 0,
error_message TEXT,
retry_count INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
started_at DATETIME,
completed_at DATETIME,
expires_at DATETIME,
UNIQUE(item_id, user_id)
);
CREATE INDEX idx_downloads_status ON downloads(status);
CREATE INDEX idx_downloads_user ON downloads(user_id);
CREATE INDEX idx_downloads_queue ON downloads(status, priority DESC, created_at ASC)
WHERE status IN ('pending', 'downloading');
sync_queue
Stores mutations to sync back to server when online.
CREATE TABLE sync_queue (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
operation TEXT NOT NULL,
entity_type TEXT NOT NULL,
entity_id TEXT NOT NULL,
payload TEXT,
status TEXT DEFAULT 'pending',
attempts INTEGER DEFAULT 0,
max_attempts INTEGER DEFAULT 5,
last_attempt DATETIME,
error_message TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
completed_at DATETIME
);
CREATE INDEX idx_sync_queue_status ON sync_queue(status, created_at ASC)
WHERE status = 'pending';
CREATE INDEX idx_sync_queue_user ON sync_queue(user_id);
thumbnails
Caches downloaded artwork.
CREATE TABLE thumbnails (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
image_type TEXT NOT NULL,
image_tag TEXT,
file_path TEXT NOT NULL,
width INTEGER,
height INTEGER,
file_size INTEGER,
cached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
last_accessed DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(item_id, image_type, width)
);
CREATE INDEX idx_thumbnails_item ON thumbnails(item_id);
CREATE INDEX idx_thumbnails_lru ON thumbnails(last_accessed ASC);
playlists (for local/synced playlists)
CREATE TABLE playlists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
jellyfin_id TEXT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
description TEXT,
is_local_only BOOLEAN DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
synced_at DATETIME,
needs_sync BOOLEAN DEFAULT 0
);
CREATE TABLE playlist_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
playlist_id INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
sort_order INTEGER NOT NULL,
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(playlist_id, item_id)
);
CREATE INDEX idx_playlist_items_playlist ON playlist_items(playlist_id, sort_order);
Key Queries
Get items for offline library browsing
-- Get all albums in a music library
SELECT * FROM items
WHERE library_id = ? AND type = 'MusicAlbum'
ORDER BY sort_name;
-- Get tracks for an album
SELECT * FROM items
WHERE album_id = ? AND type = 'Audio'
ORDER BY parent_index_number, index_number;
Resume / Continue Watching
SELECT i.*, ud.position_ticks, ud.last_played
FROM items i
JOIN user_data ud ON ud.item_id = i.id
WHERE ud.user_id = ?
AND ud.position_ticks > 0
AND ud.played = 0
ORDER BY ud.last_played DESC
LIMIT 20;
Offline search
SELECT i.* FROM items i
JOIN items_fts fts ON fts.rowid = i.id
WHERE items_fts MATCH ?
ORDER BY rank;
Download queue management
-- Get next item to download
SELECT d.*, i.name, i.type
FROM downloads d
JOIN items i ON i.id = d.item_id
WHERE d.status = 'pending'
ORDER BY d.priority DESC, d.created_at ASC
LIMIT 1;
-- Get download progress for UI
SELECT
d.status,
COUNT(*) as count,
SUM(d.file_size) as total_size,
SUM(d.bytes_downloaded) as downloaded
FROM downloads d
WHERE d.user_id = ?
GROUP BY d.status;
Sync queue processing
-- Get pending sync operations (oldest first)
SELECT * FROM sync_queue
WHERE status = 'pending'
AND attempts < max_attempts
ORDER BY created_at ASC
LIMIT 10;
-- Mark operation complete
UPDATE sync_queue
SET status = 'completed', completed_at = CURRENT_TIMESTAMP
WHERE id = ?;
Data Flow
Online Mode
flowchart TB
subgraph OnlineMode["Online Mode"]
JellyfinServer["Jellyfin Server"]
OnlineRepo["OnlineRepo"]
SQLite["SQLite"]
HybridRepo["HybridRepository"]
UI["UI / Stores"]
JellyfinServer -->|"API Response"| OnlineRepo
OnlineRepo -->|"Cache"| SQLite
SQLite -->|"Sync"| JellyfinServer
OnlineRepo -->|"Response"| HybridRepo
SQLite -->|"Fallback"| HybridRepo
HybridRepo --> UI
end
Offline Mode
flowchart TB
subgraph OfflineMode["Offline Mode"]
OfflineRepo["OfflineRepo"]
SQLite2["SQLite"]
SyncQueue["sync_queue<br/>(Queued for later)"]
HybridRepo2["HybridRepository"]
UI2["UI / Stores"]
OfflineRepo <-->|"Query"| SQLite2
SQLite2 -->|"Mutations"| SyncQueue
OfflineRepo --> HybridRepo2
HybridRepo2 --> UI2
end
Sync on Reconnect
flowchart LR
NetworkRestored["Network restored"]
SyncService["SyncService"]
SyncQueue2["sync_queue"]
JellyfinAPI["Jellyfin API"]
MarkSynced["Mark synced"]
NetworkRestored --> SyncService
SyncService -->|"Read"| SyncQueue2
SyncQueue2 -->|"Send"| JellyfinAPI
JellyfinAPI -->|"Success"| MarkSynced
MarkSynced --> SyncService
Storage Estimates
| Content Type | Metadata Size | Thumbnail Size | Media Size |
|---|---|---|---|
| Song | ~2 KB | ~50 KB (300px) | 5-15 MB |
| Album (12 tracks) | ~30 KB | ~100 KB | 60-180 MB |
| Movie | ~5 KB | ~200 KB | 1-8 GB |
| Episode | ~3 KB | ~100 KB | 300 MB - 2 GB |
| Full music library (5000 songs) | ~10 MB | ~250 MB | 25-75 GB |
Connection ownership
storage::Database is opened once at startup and owns the database for the
life of the app; nothing else opens the file. Everything goes through
Database::service(), which returns a clone of one RusqliteService
(storage/db_service.rs) — callers never hold a Connection.
┌─────────────────────────────┐
execute / insert / │ writer thread ("db-writer") │ one read-write connection,
transaction / ────►│ jobs run in arrival order │ foreign_keys = ON
execute_detached └─────────────────────────────┘
┌─────────────────────────────┐
query_one / │ reader pool (3 connections) │ query_only = ON; WAL gives
query_optional / ─►│ via spawn_blocking │ each the last committed
query_many └─────────────────────────────┘ snapshot
Pragmas: journal_mode = WAL, synchronous = NORMAL, busy_timeout = 5 s on
every connection. PRAGMA optimize runs once at open, after migrations, so the planner has
statistics (see "Listing query shape").
Why. It used to be one connection behind one std::sync::Mutex. WAL was on,
but with a single connection its one benefit — readers running beside a writer —
never applied: every read queued behind every write. A series page's background
refresh cached hundreds of episodes one autocommit (and one fsync) at a time,
and library pages, thumbnail lookups and settings reads all waited behind the
pile; the same page measured 1.7 s or 3.95 s depending on what was queued.
Invariants a change must keep:
- Writes go to the writer, reads to the pool. A reader is
query_only, so a write sent throughquery_*fails loudly rather than racing the writer. A read that must see a write in the same unit of work belongs inside thetransactionclosure, which runs on the writer. - Connection-wide state is set inside one writer job.
PRAGMA foreign_keysis per connection and ignored inside a transaction.save_to_cacheused to switch it off with oneexecuteand back on with another, so it stayed off for every other write that ran across the save's awaits (they did — nearly all of 1,600 FK-violating writes got through in the regression test).transaction_without_foreign_keysflips it aroundBEGIN/COMMITin a single job. Any future pragma toggle must work the same way. - An insert's rowid comes from the same job.
insert()returns it; there is deliberately no standalonelast_insert_rowid(), which returned whichever row the last anyone inserted. - Batch writes into one transaction. A commit is a queue slot on the
writer; a page of items is one
transaction, not oneexecuteper row. - Best-effort bookkeeping does not wait.
execute_detachedqueues a write (the thumbnail LRU access time) in order with the rest and returns immediately; failures are only logged. - The writer survives a panicking job. The job's caller gets an error; the
loop rolls back any transaction the job left open and restores
foreign_keys = ON, then carries on.
synchronous = NORMAL is corruption-safe in WAL mode and survives an app
crash; only a power cut can lose the last few commits. Everything stored is
either re-fetchable from the server or (the sync queue, local positions)
recoverable at that granularity.
In-memory databases (tests) cannot be shared between connections, so
RusqliteService::new has no pool and routes reads through the writer — the
old serialized behaviour.
Not done, and why: grouping consecutive small execute jobs into one
commit on the writer. With synchronous = NORMAL a commit no longer fsyncs, so
the gain is small, and it would change a failed statement's error semantics
for the jobs grouped with it.
Listing query shape
OfflineRepository::get_items (items_listing_sql) is the hot read: every
library, series, season and album page goes through it, and it must fit the
100 ms cache fast path on a phone. Every other cached read follows the same
rules.
- Children are matched on
items.container_id(migration 027), a VIRTUAL generated column holding the item's logical container: an episode's season (else series, else parent), a season's series, a track's album, otherwise the parent. Jellyfin'sParentIdis the storage parent, not the logical one — in a series without season folders an episode'sParentIdis the series while itsSeasonIdnames a virtual season — so listings used to match on four columns at once. ThatORdefeated the planner into walking the whole table, and it was wrong: every episode carries its series id, so a series listed all its episodes beside its seasons. Being generated, the column covers every write path (cache, downloads, catalog crawl) without any of them knowing, and cannot drift from the columns it is computed from. idx_items_container (container_id, sort_name, name)serves a listing as one index range already in display order — no sort step.sort_nameis usually NULL in the cache (the cache never writes it), hencenamein the index too.- Containers exist even when never browsed. A series lists its seasons, so
an episode whose season row was never cached (it arrived through Next Up or
Latest) would be unreachable from its series offline.
save_to_cache— and migration 027 for rows already on disk — inserts placeholders named from the child's own fields (season_name,series_name,album_name) withsynced_atNULL, so they show only when a download makes them available; the server's real row replaces them wholesale on the next browse. - Availability is a per-row
EXISTS(downloaded_sql/available_sql): cached for browsing (only with the catalog-browse flag), downloaded, or a container with a downloaded descendant — which is why that one check still looks at all four link columns (a series is available through an episode two levels down). It used to be a CTE that built the id of every available item in the database before filtering, paying for the whole table on every call. - The library clause is added only for a library parent (
is_library), neverORed into an ordinary listing, where it forces a full scan. +i.server_idkeeps the planner off the server index, which every row shares.PRAGMA optimizeat open (analysis_limit = 400) gives the planner statistics, but even with them it chose that index for the oldOR; the+is the guarantee.- User data is fetched in batches (
with_user_data, oneIN (…)query per 500 rows), not once per row.
Measured on a ~110k-item benchmark catalogue (desktop): a series listing went
from ~80 ms to under 1 ms; migration 027 upgrades an existing database of that
size in ~0.1 s. listing_a_non_library_parent_uses_the_container_index and
migration 027's tests assert the plans; storage::tests::write_bench_database
(ignored; set JELLYTAU_BENCH_DB) writes the benchmark catalogue for the
sqlite3 CLI.
Rust Module Structure
src-tauri/src/storage/
├── mod.rs # Module exports, Database struct
├── schema.rs # Table definitions, migrations
├── models.rs # Rust structs matching tables
├── queries/
│ ├── mod.rs
│ ├── items.rs # Item CRUD operations
│ ├── user_data.rs # User data operations
│ ├── downloads.rs # Download queue operations
│ └── sync.rs # Sync queue operations
└── sync/
├── mod.rs # SyncService
├── manager.rs # Background sync manager
└── operations.rs # Individual sync operation handlers