[FEATURE] SQLite Metadata Layer & Background Enrichment

[safepay]

Feature Proposal: SQLite Database Migration

Problem

PiKaraoke currently scans the song directory on every startup:

• No persistence across restarts
• Renamed/moved files appear as deleted + new (losing any future metadata)
• Songs are just filenames with no structured metadata

Solution

Migrate to a persistent SQLite database with metadata enrichment.

Benefits

• Persistent library - No rescanning on restart once db established
• Move/rename detection - Files tracked by content hash, not path
• Rich metadata - Artist, title, year, genre instead of just filenames

Implementation

Stage 1: Database Foundation

A complete feature delivering:

• Persistent song library with hash-based file tracking
• Automatic detection of added, moved, and deleted files
• Admin UI for manual sync and database reset
• Zero breaking changes - all existing features work identically

Stage 2: Metadata Enrichment

Transform filenames into structured metadata:

• Filename parsing - Extract artist/title from common YouTube karaoke patterns
• API enrichment - Fetch year/genre from iTunes API
• Manual editor - UI for correcting/editing metadata

Rollback

• Stage 1: Revert to direct file scanning, delete database files
• Stage 2: Disable parsing, continue using filenames only

Success Criteria

• App behavior identical to pre-migration
• Move/rename detection works correctly
• No performance regression
• More than 99% of songs have accurate artist/title metadata

[jloutsenhizer]

Looks like this is a dupe of #103 although with a lot more design details

[safepay]

I have more planning in the docs folder here if you want to have a read.
Note that it's a work in progress.
upgrade-to-sqlite-and-naming-management

[safepay]

For those interested, a critical step towards this has already been released #643.

This sets a data directory on all platforms as the correct place to store the database, instead of using the user's home directory, which was unsuitable for this feature.

~/.pikaraoke on Mac/Linux and the appdata directory on Windows.

[cdytoby]

I generally like this idea. One question: will the user be able to add video without downloading from YT?

Currently user can to make their own karaoke mp4 file and just put it in the folder. With this database, what will be the workflow?

[safepay]

There would be no change to existing functionality. The proposal is simply to separate file names from songs. A bit like Plex.

[poeticize]

This capability, by adding the SQLite functionality, will ALSO allow adding add a distinctly more robust user and user history system than currently proposed in #790 without adding noticeable weight to the app once/after this feature is adopted.