Skip to content

ADR-0005: SQLite as a Derived State Index

Status

Accepted

Date

2026-08-12

Supersedes

ADR-0003: Hybrid Filesystem-Based State Management

Context

The pre-rewrite design (ADR-0002, then ADR-0003) kept track-level state in file tags and playlist-level state (etags, output paths) in state.json. During the MR !4 rewrite, state.json was dropped entirely.

Two observations drove this:

  1. state.json and the filesystem duplicated the same information. Both recorded "which tracks have been downloaded". The JSON added no capability that the filesystem tags and a database did not already provide, and every duplicated copy is another thing that must be kept in sync and can drift.

  2. The rewrite's central goal is deduplication. A track added to multiple playlists (of the same or different providers) must be downloaded exactly once. This requires, for every candidate track, an answer to "do I already have this?" across the whole library, not just within one playlist.

A central database answers that query in one place and in SQL time, instead of scanning the filesystem and reading every file's tags on each sync cycle. The database is a runtime optimisation: it exists so the de-dup check is fast, not because it is the authoritative record.

This design is only safe if the database stays accurate. If it thinks a track is downloaded when it is not (a false positive), the track will never be downloaded again; if it thinks a track is missing when it is present (a false negative), the track is downloaded a second time. Both violate the dedup goal, so the database must always be reconciled with the filesystem.

Decision

  • Replace state.json with a SQLite database (state/database.py).
  • The filesystem is the source of truth. The database is a derived index, kept in agreement with the filesystem.
  • The Track table stores one row per downloaded file, with columns for each provider's ID (MusicBrainzId, YouTubeMusicId, SpotifyId) and FilePath, each UNIQUE.
  • The PlaylistEntry table records playlist membership (PlaylistProvider, PlaylistId, TrackId) as a many-to-many relation with a foreign key to Track (ON DELETE CASCADE).
  • Every startup runs a full scan (sync/full_sync.py) that reconciles the database with the download directory in both directions:
  • files on disk but absent from the database are inserted by reading the IDs embedded in their tags;
  • database rows whose file is missing from disk are deleted. This replaces ADR-0003's needs_full_scan flag: the scan is always on, not conditional on a crash flag.
  • Spotify snapshot IDs are held in memory only (a _snapshot_id_cache), never persisted. Persisting them would be actively harmful: if the process was in sync, the user deleted files, and the process restarted "knowing" the playlist was unchanged, the missing tracks would never be re-downloaded. The snapshot only avoids a cheap database check, so persisting it buys almost nothing.
  • The model operates on the assumption that tracks aren't being added to or removed from the output directory while the program is running. While this might not necessarily be the case, it is a reasonable enough assumption to justify the significant reduction in complexity.

Consequences

Positive

  • No duplicated state: one authoritative record (the filesystem) plus one derived index (the database).
  • Crash-safe: a track is only recorded after its download and tagging succeed; a failed partial sync leaves no corrupt state file.
  • Fast sync cycles: the de-dup check is a database lookup rather than a filesystem walk plus per-file tag reads.
  • Cross-playlist deduplication for free: the same provider track ID appearing in two playlists maps to one Track row and two PlaylistEntry rows.
  • Rebuildable: if the database is lost or corrupted, a full scan repopulates it from tagged files.

Negative

  • A startup scan is required on every boot, however this is a very acceptable trade off as it doesn't require any API calls. Scanning the download directory even if it is very large, is much faster than many calls to YouTube or MusicBrainz
  • If files are moved or renamed by the user, the FilePath mapping can go stale until the next startup scan reconciles it.
  • If the user moves a track and the full scan updates its Track record, the PlaylistEntry record is deleted and not replaced.

Neutral

  • Supersedes ADR-0003's hybrid model; the needs_full_scan flag and state.json no longer exist.
  • The Spotify snapshot cache is lost on restart, so the first cycle after a restart always re-checks each playlist against the database.

Alternatives Considered

Keep state.json (ADR-0003 hybrid)

Rejected: duplicated track-level information with no added capability, and an extra persistent structure to keep in sync with both the filesystem and the database.

Filesystem-only, no database

Rejected: every sync cycle would require walking the download directory and reading each file's tags to answer the de-dup query; the dedup goal requires library-wide lookups, which a scan does poorly at library scale.

Persist snapshot IDs in the database

Rejected: a stale snapshot after the user deleted files would wrongly mark playlists as unchanged and suppress re-downloads. The snapshot only saves a cheap database check, so there is no meaningful benefit to persisting it.

Keep a JSON index, rebuilt at startup

Rejected: same amount of work as SQLite, no query performance, and all the serialisation/atomicity code SQLite provides for free.