Design and implement the shared database schema crate (shanty-db) #2

New Issue

connor · 2026-03-17T13:55:18-04:00

connor commented

2026-03-17 13:55:18 -04:00

Shanty uses a shared database to store all music metadata, library state, watchlist entries, and more. Multiple crates need to read from and write to this database. To avoid tight coupling and schema drift, there should be a dedicated shanty-db crate that owns the schema, migrations, and provides a typed access layer.

This crate should:

Choose and integrate an ORM/query builder — recommend diesel or sea-orm with SQLite as the default backend (SQLite is lightweight, file-based, and appropriate for a self-hosted music app). The choice should support migrations and be ergonomic for other crates to use.
Define the core schema covering at minimum:
- tracks — file path, title, artist, album, album artist, track number, disc number, duration, genre, year, codec/format, bitrate, file size, fingerprint (nullable), added_at, updated_at, musicbrainz_id (nullable)
- albums — name, album artist, year, genre, cover art path (nullable), musicbrainz_id (nullable)
- artists — name, musicbrainz_id (nullable), added_at, top_songs, similar_artists
- wanted_items — references to artist/album/track the user wants to monitor, with status (wanted/available/downloaded)
- download_queue — items pending download, with status, source URL, error info
- search_cache — optional cache for online search results to avoid excessive API calls
Provide migrations so the schema can evolve over time without losing data
Expose a connection pool and typed query functions that other crates import

Design Considerations

The schema must be versioned and migratable. Other crates depend on shanty-db but should not define their own tables.
Consider using r2d2 or the ORM's built-in connection pooling.
Keep the schema normalized but pragmatic — e.g., a track belongs to an album which belongs to an artist, but allow nullable foreign keys for partially-tagged files.
The crate should re-export relevant types (models, enums) so other crates don't need to depend on the ORM directly for basic type usage.

Acceptance Criteria

shanty-db crate compiles and is usable as a dependency by other workspace crates
SQLite database is created automatically if it doesn't exist
Migrations can be run programmatically (and via CLI if using diesel)
Core tables (tracks, albums, artists, watch_items, download_queue) exist after migration
Typed Rust structs exist for each table (insertable and queryable variants)
Connection pooling is set up
At least basic integration tests exist — create a DB, run migrations, insert a track, query it back
Schema version is tracked so future migrations can be applied incrementally

Dependencies

Issue #1 (workspace scaffolding)

Shanty uses a shared database to store all music metadata, library state, watchlist entries, and more. Multiple crates need to read from and write to this database. To avoid tight coupling and schema drift, there should be a dedicated `shanty-db` crate that owns the schema, migrations, and provides a typed access layer. This crate should: 1. **Choose and integrate an ORM/query builder** — recommend `diesel` or `sea-orm` with SQLite as the default backend (SQLite is lightweight, file-based, and appropriate for a self-hosted music app). The choice should support migrations and be ergonomic for other crates to use. 2. **Define the core schema** covering at minimum: - `tracks` — file path, title, artist, album, album artist, track number, disc number, duration, genre, year, codec/format, bitrate, file size, fingerprint (nullable), added_at, updated_at, musicbrainz_id (nullable) - `albums` — name, album artist, year, genre, cover art path (nullable), musicbrainz_id (nullable) - `artists` — name, musicbrainz_id (nullable), added_at, top_songs, similar_artists - `wanted_items` — references to artist/album/track the user wants to monitor, with status (wanted/available/downloaded) - `download_queue` — items pending download, with status, source URL, error info - `search_cache` — optional cache for online search results to avoid excessive API calls 3. **Provide migrations** so the schema can evolve over time without losing data 4. **Expose a connection pool and typed query functions** that other crates import ### Design Considerations - The schema must be versioned and migratable. Other crates depend on `shanty-db` but should not define their own tables. - Consider using `r2d2` or the ORM's built-in connection pooling. - Keep the schema normalized but pragmatic — e.g., a track belongs to an album which belongs to an artist, but allow nullable foreign keys for partially-tagged files. - The crate should re-export relevant types (models, enums) so other crates don't need to depend on the ORM directly for basic type usage. ### Acceptance Criteria - [ ] `shanty-db` crate compiles and is usable as a dependency by other workspace crates - [ ] SQLite database is created automatically if it doesn't exist - [ ] Migrations can be run programmatically (and via CLI if using diesel) - [ ] Core tables (`tracks`, `albums`, `artists`, `watch_items`, `download_queue`) exist after migration - [ ] Typed Rust structs exist for each table (insertable and queryable variants) - [ ] Connection pooling is set up - [ ] At least basic integration tests exist — create a DB, run migrations, insert a track, query it back - [ ] Schema version is tracked so future migrations can be applied incrementally ### Dependencies - Issue #1 (workspace scaffolding)

connor added the MVP HighPriority labels 2026-03-17 13:55:18 -04:00

connor started working 2026-03-17 13:58:09 -04:00

connor referenced this issue

2026-03-17 14:09:40 -04:00

Implement music file scanning in `shanty-index` #3

connor referenced this issue

2026-03-17 14:17:57 -04:00

Implement online metadata lookup in `shanty-tag` #4

connor closed this issue

2026-03-17 14:20:26 -04:00

connor worked for 22 minutes

2026-03-17 14:20:26 -04:00