ADR-002: Library storage — SQLite

ADR-002: Library storage — SQLite

Status

Accepted

Context

The platform needs persistent local storage for:

• Library: saved content UUIDs, playlists, favorites, playback history (FR-CORE-3)
• Content resolution table: maps content UUIDs to (plugin_id, foreign_id) tuples (FR-ID-1)
• Queue: ordered list of content items (optional persistence — NFR-REL-3 is Should, not Must)
• Plugin state: plugin-managed data directories (C6 says no cloud, everything local)

The storage backend must be embedded (no server process), cross-platform (C5), and queryable. Options:

• SQLite: Embedded SQL database. Mature Rust bindings (rusqlite, sqlx). Cross-platform. Tauri already bundles it via tauri::api::path or direct dependency.
• sled: Embedded key-value store in Rust. Good for simple lookups, poor for relational queries (joins, filter by multiple columns).
• File-based JSON: Read entire file, deserialize, mutate, serialize, write. Simple but no query capability, no atomicity, no concurrent access safety.
• RocksDB: LSM-tree key-value store. Overkill for v1 schema complexity. Larger binary.

Decision

Use SQLite via the rusqlite crate for all persistent storage. A single database file at the platform data directory (~/.rymflux/data/library.db on Linux, platform-appropriate path on macOS/Windows managed by Tauri’s path API).

Schema sketch (final schema in system-design/database):

-- Core library
CREATE TABLE library_items (
    content_id TEXT PRIMARY KEY,  -- UUIDv4
    title TEXT NOT NULL,
    type TEXT NOT NULL,           -- 'track' | 'album' | 'artist'
    artist TEXT,
    duration_seconds INTEGER,
    added_at TEXT NOT NULL        -- ISO 8601
);

-- Resolution table (plugin → content mapping)
CREATE TABLE content_resolution (
    content_id TEXT NOT NULL REFERENCES library_items(content_id),
    plugin_id TEXT NOT NULL,
    foreign_id TEXT NOT NULL,
    PRIMARY KEY (content_id, plugin_id)
);

-- Playlists
CREATE TABLE playlists (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE playlist_items (
    playlist_id TEXT NOT NULL REFERENCES playlists(id),
    content_id TEXT NOT NULL REFERENCES library_items(content_id),
    position INTEGER NOT NULL,
    added_at TEXT NOT NULL
);

-- History
CREATE TABLE playback_history (
    content_id TEXT NOT NULL REFERENCES library_items(content_id),
    played_at TEXT NOT NULL,       -- ISO 8601
    position_seconds INTEGER,     -- where they stopped
    plugin_id TEXT                 -- which plugin resolved it
);

Alternatives considered

• sled: Would force query logic into application code (filtering, joining, ordering). Acceptable for a key-value cache, but the resolution table requires join queries (find all plugins for content_id, find all content for plugin_id).
• File-based JSON: Works for a toy but fails on atomic writes, concurrent access (Tauri backend is async, multiple commands could write simultaneously), and incremental reads. A 10,000-item library means serializing the entire file on every change.
• RocksDB: Significantly larger compiled library, more complex build (C++ dependency), and the column-family model is less natural for the relational queries the library needs.

Consequences

Positive:

• SQL is the right query model for the library’s access patterns (filter by type, join resolution table, order by date).
• rusqlite is pure Rust, no C dependency beyond what SQLite already is.
• Migrations can be applied versioned (schema version stored in a _meta table).
• Tauri’s tauri::path API provides correct platform paths without manual handling.

Negative:

• SQLite requires a schema migration strategy from day one (use rusqlite’s pragma user_version).
• WAL mode should be enabled for concurrent read access during writes.
• File-based locking: if the platform ever supports multiple instances (not in v1), SQLite’s file-level locking would be a bottleneck.

References

• Constraint C4, C6 (constraints-assumptions.md)
• FR-CORE-3, FR-ID-1 (functional.md)
• NFR-REL-3, NFR-REL-4 (non-functional.md)