SQLite schema

Database file

~/.rymflux/data/library.db (managed by Tauri’s path API).

WAL mode

PRAGMA journal_mode = WAL;

Enabled at connection time for concurrent read access during writes.

Schema versioning

PRAGMA user_version stores the current schema version as an integer. On startup, read user_version, run pending migrations in order, update user_version.

Migrations live in src-tauri/migrations/NNN_description.sql.

Tables

library_items

Core library: tracks that the user has saved.

CREATE TABLE IF NOT EXISTS library_items (
    id              TEXT PRIMARY KEY,          -- core UUIDv4
    plugin_id       TEXT NOT NULL,             -- which plugin provided this
    content_id      TEXT NOT NULL,             -- plugin-scoped foreign ID
    title           TEXT NOT NULL,
    artist_name     TEXT NOT NULL,
    album_title     TEXT,
    duration_ms     INTEGER,
    cover_art_url   TEXT,
    explicit        INTEGER NOT NULL DEFAULT 0, -- boolean
    added_at        TEXT NOT NULL,              -- ISO 8601
    favorited_at    TEXT                        -- ISO 8601, NULL if not favorited
);

CREATE INDEX IF NOT EXISTS idx_library_plugin
    ON library_items(plugin_id, content_id);

CREATE INDEX IF NOT EXISTS idx_library_title
    ON library_items(title COLLATE NOCASE);

CREATE INDEX IF NOT EXISTS idx_library_favorited
    ON library_items(favorited_at)
    WHERE favorited_at IS NOT NULL;

content_resolution

Maps a core UUID to plugin-specific IDs. Supports multiple plugins for the same content.

CREATE TABLE IF NOT EXISTS content_resolution (
    content_id  TEXT NOT NULL REFERENCES library_items(id) ON DELETE CASCADE,
    plugin_id   TEXT NOT NULL,
    foreign_id  TEXT NOT NULL,
    PRIMARY KEY (content_id, plugin_id)
);

playlists

User-created playlists.

CREATE TABLE IF NOT EXISTS playlists (
    id          TEXT PRIMARY KEY,    -- core UUIDv4
    name        TEXT NOT NULL,
    description TEXT,
    created_at  TEXT NOT NULL        -- ISO 8601
);

playlist_tracks

Membership table for tracks within a playlist.

CREATE TABLE IF NOT EXISTS playlist_tracks (
    playlist_id TEXT NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
    track_id    TEXT NOT NULL REFERENCES library_items(id) ON DELETE CASCADE,
    position    INTEGER NOT NULL,
    added_at    TEXT NOT NULL,        -- ISO 8601
    PRIMARY KEY (playlist_id, track_id)
);

CREATE INDEX IF NOT EXISTS idx_playlist_tracks_playlist
    ON playlist_tracks(playlist_id);

playback_history

Record of track playbacks for saved library tracks.

CREATE TABLE IF NOT EXISTS playback_history (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    track_id        TEXT NOT NULL REFERENCES library_items(id) ON DELETE CASCADE,
    played_at       TEXT NOT NULL,        -- ISO 8601
    position_seconds INTEGER,             -- where they stopped (seconds from start)
    completed       INTEGER NOT NULL DEFAULT 0  -- boolean
);

CREATE INDEX IF NOT EXISTS idx_history_played
    ON playback_history(played_at DESC);

played_tracks

Record of track playbacks for unsaved tracks (tracks not in the library). Stores metadata inline so no FK to library_items is needed.

CREATE TABLE IF NOT EXISTS played_tracks (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    plugin_id       TEXT,
    content_id      TEXT,
    title           TEXT,
    artist_name     TEXT,
    album_title     TEXT,
    played_at       TEXT NOT NULL,        -- ISO 8601
    position_seconds INTEGER,             -- where they stopped (seconds from start)
    completed       INTEGER NOT NULL DEFAULT 0  -- boolean
);

CREATE INDEX IF NOT EXISTS idx_played_tracks_played
    ON played_tracks(played_at DESC);

queue_items

Future queue persistence table. Added in v1 schema but unused (S5 is Should). Zero runtime cost. Saves a DDL migration when queue persistence becomes a Must.

CREATE TABLE IF NOT EXISTS queue_items (
    position    INTEGER NOT NULL PRIMARY KEY,
    plugin_id   TEXT NOT NULL,
    content_id  TEXT NOT NULL,
    metadata    TEXT NOT NULL     -- JSON blob of TrackStub
);

No indices yet — wait until the table is actively queried.

_meta

Internal metadata key-value store.

CREATE TABLE IF NOT EXISTS _meta (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

View

CREATE VIEW IF NOT EXISTS favorites AS
    SELECT * FROM library_items
    WHERE favorited_at IS NOT NULL
    ORDER BY favorited_at DESC;

No triggers in v1. Views beyond favorites add indirection with no benefit at SQLite scale.

References

ADR-002 (SQLite for library storage)
ADR-003 (UUIDv4 content IDs)
FR-CORE-3 (unified library)
FR-ID-1 (content resolution)