Skip to content

POSTGRES_BACKEND.md

Latest commit

 

History

History
117 lines (97 loc) · 8.74 KB

POSTGRES_BACKEND.md

File metadata and controls

117 lines (97 loc) · 8.74 KB
Raw

postgres-backend — resume notes

This file lives on the postgres-backend branch. It captures branch state at a pause point so the next session can pick up cleanly. Plan file: ~/.claude/plans/export-async-function-staritems-snappy-snail.md.

Goal recap

Two pieces of work landed together on this branch:

  1. Optional Postgres backend alongside SQLite, chosen by URL scheme (sqlite:// or postgresql://). User wanted to keep both behind a config flag.
  2. MusicBrainz ID support end-to-end — scan/store/serve.

Plus a bonus: the migration sequence was squashed into a fresh schema.sql baseline because the user is happy with a fresh install and 17 micro-migrations were paying no rent.

Done on this branch

# Commit subject What it did
1 config: add database_url setting + psycopg3 dependency New database_url (sqlite:// or postgresql://) plus a resolved_database_url() helper that synthesises from legacy database_path. psycopg[binary,pool]>=3.1.0 added. ensure_directories() only mkdirs for sqlite.
2 db/connection: dialect-aware factory + Postgres param translator _dialect flag + get_dialect() accessor. Two paths: _new_sqlite_connection (unchanged behaviour) and _new_pg_connection (psycopg3 with dict_row factory). _PgConnection wrapper translates :name → %(name)s on every execute/executemany.
3 db/errors: expose IntegrityError etc. as a (sqlite3, psycopg) tuple Each name is now a tuple of both drivers' exceptions; except accepts that natively. Falls back to single-driver form if psycopg isn't installed.
4 queries: convert lastrowid usage to RETURNING id Three sites (add_music_folder, create_playlist, create_user) switched off cur.lastrowid (psycopg doesn't have it) to INSERT … RETURNING id.
5 db: replace INSERT OR IGNORE / REPLACE with portable ON CONFLICT Three sites updated: star_item, save_play_queue, _migration_003_seed_music_folders. Portable syntax works on both SQLite ≥3.24 and Postgres.
6 db: squash migrations 1-16 into a single fresh schema.sql baseline New comprehensive schema.sql includes every column / index / FTS5 setup from the old migration sequence. migrations.py reduced to 001 (apply schema.sql), 002 (seed admin), 003 (seed music folders).
7 scanner: extract MusicBrainz IDs and persist them through upserts TrackMetadata gains 5 MBID fields, _try_mutagen reads them. upsert_artist / upsert_album / upsert_track accept MBID kwargs with COALESCE-preserve semantics. _parse_one returns them as underscored keys; _commit threads them through.
8 api: expose musicBrainzId on track, album, and artist responses Every track/album/artist SELECT pulls the MBID columns; track_to_subsonic / _album_to_directory_child / get_indexes / get_music_directory emit musicBrainzId when populated. getAlbumInfo[2] and getArtistInfo[2] plumb the real value. /api/artist/{id} exposes it on the envelope and per-album entries.
9 db: add Postgres schema baseline + dialect-aware loader New schema.postgres.sql with INTEGER GENERATED BY DEFAULT AS IDENTITY, CITEXT for case-insensitive columns, BIGINT epoch fields, and a tsvector + GIN + BEFORE INSERT/UPDATE trigger replacing FTS5. _PgConnection.executescript() added so migration 001 works on both backends; _strip_sqlite_isms removes COLLATE NOCASE from incoming SQL (CITEXT makes it redundant). _migration_001_initial picks the schema file by dialect; _current_version branches between sqlite_master and information_schema.tables.
10 db: branch search3 + maintenance onto dialect for Postgres compatibility search3 grows a Postgres branch that queries tracks.search_tsv via @@ websearch_to_tsquery('simple', :query) and ranks by ts_rank. maintenance.run_gc gates _wal_checkpoint, VACUUM, and .db file-size reporting on get_dialect() — Postgres VACUUM is online, flipping autocommit on the underlying psycopg connection. Final 4 positional row accesses converted to named, ? placeholders in maintenance.py converted to :name / generated :p0..pN.
11 infra: document database_url in config example + add optional Postgres service config.example.yaml grows a database_url block with both URL schemes documented. docker-compose.yml gains a postgres service guarded by the postgres Compose profile (default stack still runs SQLite-only). Postgres 18-alpine with tuned config: shared_buffers=256MB, work_mem=21845kB, max_connections=40, effective_cache_size=768MB, synchronous_commit=off. Muse depends_on postgres with required: false so it still works without the profile.

State: 225 passed, 6 xfailed (identical to main).

Still TODO (Postgres port)

All code-side dialect work and packaging is done. The Postgres path should be functional end-to-end. What remains is verification:

  • Smoke test on real Postgres — docker compose --profile postgres up -d, set MUSE_DATABASE_URL=postgresql://muse:$PW@postgres:5432/muse, run a scan, verify search/browse/star/play-queue all work. CITEXT and the tsvector trigger need to actually create cleanly before we trust the schema file. This is the highest-value next step — it'll surface anything the code-side work missed.
  • pytest Postgres wiring — once the smoke test passes, add a --postgres flag or PYTEST_POSTGRES_URL env that re-runs the suite against a Postgres test DB. Probably testcontainers (auto-managed throwaway container per pytest session) — simpler than wiring a long-lived test DB into CI.

Resume next session

git checkout postgres-backend
cat POSTGRES_BACKEND.md     # this file
git log --oneline main..HEAD

The docker-compose Postgres profile is now live, so the smoke test is the next concrete step. Recommended:

export POSTGRES_PASSWORD=$(openssl rand -hex 16)
docker compose --profile postgres up -d postgres
# wait for healthy; check with: docker compose ps
# then uncomment MUSE_DATABASE_URL in docker-compose.yml and bring muse up:
docker compose --profile postgres up -d
# tail the logs and watch for the schema apply step:
docker compose logs -f muse

Verification checklist for the smoke test:

  1. Migration 001 applies cleanly (no errors about citext, IDENTITY, tsvector, or the trigger function).
  2. Admin login works (the seed admin path is the same code on both dialects).
  3. Scan a small library (10-20 tracks) — confirms upsert_artist/ _album/_track all use ON CONFLICT correctly and that RETURNING id returns the right shape.
  4. Browse the artist/album/track lists in the web UI — confirms COLLATE NOCASE stripping works and CITEXT ordering matches what SQLite produced.
  5. Star a few items — confirms the polymorphic starred table works.
  6. Search via the magnifying glass — exercises the tsvector path and the websearch_to_tsquery ranking.
  7. /api/scan/gc with ?vacuum=true — confirms the online Postgres VACUUM branch works.

Out of scope

  • Migrating existing SQLite data to Postgres. User chose fresh install.
  • ListenBrainz / sonicSimilarity. Out of scope; separate feature.
  • Removing SQLite entirely. User wants both backends.

Notes from this session

  • We confirmed psycopg3 doesn't accept :name natively — that drove the connection wrapper's regex translation approach. Don't switch to positional %s thinking it's "more native"; the named form keeps queries.py readable and the regex is tiny.
  • The INTEGER PRIMARY KEY AUTOINCREMENT vs IDENTITY difference is why we need separate schema files rather than a single portable one. SQLite has special rowid semantics that don't map cleanly.
  • COLLATE NOCASE was resolved via option 2 from the original plan: CITEXT on Postgres side, leave SQLite alone, and have the connection wrapper strip the now-redundant COLLATE NOCASE clauses from incoming SQL via regex. Zero changes to queries.py needed.
  • psycopg3's dict_row factory doesn't support positional indexing (row[0] raises KeyError because it's looking for a column named 0). Four sites in queries/migrations/maintenance had to be aliased (SELECT COUNT(*) AS n) and switched to named access.
  • FTS5 → tsvector turned out to be smaller than estimated — under an evening — because the trigger model is a near-direct port: BEFORE INSERT/UPDATE on tracks, look up artist/album names from joined rows, assemble a weighted tsvector. The 3-4 evening estimate assumed maintaining a parallel side-table; storing on tracks itself is simpler.
  • VACUUM on Postgres is online (no exclusive lock) when invoked without FULL, so the maintenance UI's "compact database" button is actually cheaper on Postgres than SQLite. We toggle autocommit=True for the duration because VACUUM can't run inside a transaction.