Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The database system manages local storage for music library metadata, analysis results, and application state. It uses SQLite as the storage engine and implements a custom versioned migration system to manage schema evolution. The system prioritizes data integrity through foreign key enforcement and Write-Ahead Logging (WAL) for performance.
Sources: README.md, apps/desktop/src-tauri/src/db.rs:1-15
Database Architecture & Configuration
The application initializes the database using the Database struct, which wraps a rusqlite::Connection. It supports both file-backed storage for production and in-memory storage for testing.
The system applies three specific PRAGMAs to every connection to ensure reliability and performance:
- Foreign Keys: Explicitly enabled using
PRAGMA foreign_keys = ONto supportON DELETE CASCADEbehaviors. - Journal Mode: Set to
WAL(Write-Ahead Logging) to allow concurrent reads and writes. - Synchronous: Set to
NORMALto balance safety and write speed.
Sources: apps/desktop/src-tauri/src/db.rs:20-56, tests/schema/src/lib.rs:43-52
Database Initialization Flow
The diagram shows the sequence of steps the Database::new function performs to prepare the storage layer.
Sources: apps/desktop/src-tauri/src/db.rs:20-40, apps/desktop/src-tauri/src/db.rs:66-98
Migration System
The migration system follows an incremental approach where each migration is assigned a version number. Migrations are stored in the MIGRATIONS array and applied in order.
Version Tracking
A specialized table named schema_migrations tracks applied versions to prevent redundant execution.
| Column | Type | Description |
|---|---|---|
version | INTEGER | Primary key identifying the migration version. |
applied_at | TEXT | ISO timestamp of when the migration was executed. |
Sources: apps/desktop/src-tauri/src/db.rs:67-72, tests/schema/src/lib.rs:54-59
Resilience & Filtering
To prevent failures during crashes, the system includes a without_applied_alters utility. This function parses ALTER TABLE ... ADD COLUMN statements and verifies if the column exists before attempting to add it. This makes migrations idempotent and safe to retry.
Sources: apps/desktop/src-tauri/src/db.rs:113-149, tests/schema/src/lib.rs:104-124
Core Data Models
The schema is divided into several functional modules: core library, enrichment, and snapshots.
Entity Relationship Diagram
The diagram illustrates the primary relationships between library entities, focusing on how tracks relate to workspaces and metadata.
Sources: apps/desktop/src-tauri/migrations/001_initial_schema.sql:5-55
Library & Tracks
The tracks table is the central repository for audio file metadata. It includes physical file properties, extracted tags, and analysis results.
| Field | Type | Constraint | Description |
|---|---|---|---|
id | TEXT | PRIMARY KEY | Unique identifier for the track. |
file_hash | TEXT | NOT NULL | BLAKE3 hash for deduplication. |
workspace_id | TEXT | REFERENCES workspaces | Scopes track to a specific library. |
analysis_status | TEXT | DEFAULT 'pending' | Track processing state. |
vocal_presence | INTEGER | DEFAULT 0 | Analysis result for vocal detection. |
Sources: apps/desktop/src-tauri/migrations/001_initial_schema.sql:14-43
Cue Points
cue_points store specific time positions within a track. They support both hot cues (assigned to slots 1-8) and memory cues (where slot is NULL).
Sources: apps/desktop/src-tauri/migrations/001_initial_schema.sql:51-61, tests/schema/src/lib.rs:260-275
Enrichment & External Providers
Later migrations (003, 004) introduced tables for external metadata enrichment and provider management.
Enrichment Workflow
- Enrichment Runs: The
enrichment_runstable snapshots the library state before and after an external lookup. - Candidates:
enrichment_candidatesstore proposed changes (e.g., a new genre or higher quality artwork) with a confidence score. - External IDs: Tracks ISRCs and other provider-specific IDs in the
external_idstable.
Sources: apps/desktop/src-tauri/migrations/003_enrichment_providers.sql, apps/desktop/src-tauri/migrations/004_enrichment_candidates.sql:1-15
Chart Snapshots
The chart_snapshots table (Migration 005) stores versioned data from providers like Beatport or Traxsource. It uses a composite unique constraint on (provider, chart, genre, snapshot_date, position) to allow for daily or weekly snapshot imports without duplicates.
Sources: apps/desktop/src-tauri/migrations/005_chart_snapshots.sql:11-30
Summary of Migrations
| Version | File | Key Changes |
|---|---|---|
| 1 | 001_initial_schema.sql | Core library: workspaces, tracks, cue_points, skill_runs. |
| 2 | 002_track_tags.sql | Add tags: comments, artwork_hash, artwork_path, artwork_mime. |
| 3 | 003_enrichment_providers.sql | Metadata providers: isrc, external_ids, enrichment_runs, streaming_*. |
| 4 | 004_enrichment_candidates.sql | Enrichment candidates and proposal review logic. |
| 5 | 005_chart_snapshots.sql | Chart snapshots for external discovery. |
Sources: apps/desktop/src-tauri/src/db.rs:8-19, CHANGELOG.md, apps/desktop/src-tauri/migrations/005_chart_snapshots.sql:1-9
Conclusion
The Database Schema & Migrations module provides a robust, idempotent foundation for local-first data management. By combining SQLite's reliability with a custom migration runner that detects existing schema elements, the system ensures safe updates even in the event of application crashes or partial migrations.