Tracking issue: SQL migration

[goto-bus-stop]

üWave is moving away from MongoDB to simplify operations (and probably get a performance boost to boot). For a small self-hosted service operating on relational data, MongoDB was a poor choice.

Initially, the focus will be on SQLite, which will have no trouble handling a typical server with a few dozen simultaneous users despite being synchronous. We could use worker threads to improve concurrency later if needed.

#637 contains a large chunk of the work, migrating all MongoDB uses to SQLite.

Follow up items, roughly in order:

• Typed JSON
• Set up LiteFS on fly.io for the demo server
• Redis is randomly used for some booth / waitlist / online users related storage. probably just stuff that into a simple key-value table in the database to make the whole program easier to run.
• Use SQL transactions where necessary--with mongodb there are no transactions so a bunch of code YOLO'd consistency, but with a SQL database we can actually guarantee transactions easily.
• put the migration code into a standalone migration script so I can release the next version without depending on mongo (you would probably run npx u-wave/core#migrate to move over your database, or something?)
• performance test it with https://wlk.yt/ data. I expect this will be dramatically faster...
• Support for other SQL databases, probably Postgres and/or MariaDB, which have relatively simple deployment options and which many self-hosters have experience with

[goto-bus-stop]

Need to kind of figure out what to do for the waitlist and booth. I think the other stuff that's in Redis right now can be separate tables, but we only have one waitlist and we only have one booth.

One idea is to just use a waitlist table a la:

• User
• Position
• History ID
• Remove after next play

Where position is unique, position = 0 is the current booth, history ID is only populated if position = 0.
Moving things around in the waitlist will require updating every row to adjust each position, but a waitlist is unlikely to have many entries, and it would surely perform fine up to a few hundred.
It's not really super nice though.

[goto-bus-stop]

Using a transaction for the booth would have one negative effect with the current simple setup. Kysely + SQLite only uses a single database connection. You wouldn't be able to handle any requests while an advance is in progress, and an advance includes potential async work (fetching a stream URL for soundcloud).

It could be aided by using a threadpool to handle requests, with one connection each, and a dedicated thread for handling advances.

[goto-bus-stop]

Initial perf test for WLK looks … decent (playlists and history load in dozens of milliseconds instead of 500-1500 ms, including network latency), but the single-threadedness is definitely a problem.