615 lines
16 KiB
Markdown
615 lines
16 KiB
Markdown
# Offline Database Design
|
|
|
|
## Entity Relationship Diagram
|
|
|
|
```mermaid
|
|
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.
|
|
|
|
```sql
|
|
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](09-security.md)).
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
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.).
|
|
|
|
```sql
|
|
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)
|
|
);
|
|
|
|
-- Performance indexes
|
|
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.
|
|
|
|
```sql
|
|
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).
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
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)
|
|
|
|
```sql
|
|
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
|
|
```sql
|
|
-- 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
|
|
```sql
|
|
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
|
|
```sql
|
|
SELECT i.* FROM items i
|
|
JOIN items_fts fts ON fts.rowid = i.id
|
|
WHERE items_fts MATCH ?
|
|
ORDER BY rank;
|
|
```
|
|
|
|
### Download queue management
|
|
```sql
|
|
-- 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
|
|
```sql
|
|
-- 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
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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 |
|
|
|
|
## 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
|
|
```
|