From a726d9f5fb56e1fd7983c5ac806d408ad78daa86 Mon Sep 17 00:00:00 2001 From: srdusr <99972264+srdusr@users.noreply.github.com> Date: Thu, 11 Sep 2025 21:53:00 +0200 Subject: Add multiplayer bots, typing languages, and rework the UI layout Multiplayer - Quick match: POST /api/multiplayer/quickmatch returns whichever room is still filling, or opens one. Players never see a room code; joining by code stays for racing specific people. - Bots fill quick-match rooms after a short wait so a new game is never an empty lobby. They only ever join quick-match rooms, never a room opened by code. One or two per room, drawn from separate ~40 and ~80 WPM tiers so two bots are never near each other's pace, and they stall to correct mistakes rather than typing a clean straight line. - Live player count via GET /api/multiplayer/online, shown on the Multiplayer control and under the main menu's Multiplayer button. - Per-racer colours: you are the theme accent, opponents take distinct hues that stay the same from lobby to race. - The countdown no longer holds the room lock for its full three seconds, which is what reset clients mid-countdown. Typing languages - 16 languages for the generated-word modes, each with its own high-frequency vocabulary rather than a translation of the English list. - Picker in the top-right rail; non-English uses its own list at every difficulty tier instead of falling back to English words. Fix UTF-8 accuracy in the game core - update_game_state mixed byte and character counts: total_characters_typed accumulated byte-length deltas while total_correct_characters compared a char index against that byte count. Equal on ASCII, so it went unnoticed; a correctly typed Spanish passage scored 6%. The old byte slicing would also have panicked if an index landed inside a multi-byte character. Rewritten char-based, with regression tests. Programming mode - Replaced prose about programming with real code: 26 syntax-highlighted snippets across JavaScript, Python, Rust, C/Go/Java and shell. Single-line by necessity, since the typing input is a single-line field. Layout and readability - One icon rail arrangement on every screen: Settings/Store under the wordmark, Language/Theme/Friends/Account top-right, Stats/Leaderboard/ Multiplayer bottom-right. - Main menu: mode picker moved out of the Single Player button, which it was notching a divider through and pushing the label off-centre. - Escape returns to the menu, closing any open popover first, and confirms before abandoning a live race. - Split --text-color and --sub-color per theme; they shared one value that measured 3.65:1 against the background, below the 4.5:1 body-text floor. - Semantic colours used in exactly one place each: gold for a personal best, amber for the race countdown and the mobile-result badge. - Passage now sits in the same place on the typing and end screens, and its column is a whole number of characters wide so wrapping cannot leave a permanent gap on the right. - End screen: keystrokes and a correct/wrong/extra/missed split, attribution carried over from the typing screen, and a graph with a separate error axis, axis titles including seconds, and smoothed lines. --- TODO-postgres.md | 65 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 65 insertions(+) create mode 100644 TODO-postgres.md (limited to 'TODO-postgres.md') diff --git a/TODO-postgres.md b/TODO-postgres.md new file mode 100644 index 0000000..5353ac1 --- /dev/null +++ b/TODO-postgres.md @@ -0,0 +1,65 @@ +# Migrate the server from SQLite to PostgreSQL + +## Why + +The server is a multi-user network service. It handles auth, sessions, +friendships, leaderboards, live multiplayer races, and rate limiting. SQLite +allows only one writer at a time. Races, leaderboard writes, and rate-limit +counters all write at the same time, so the single writer is the wrong shape +for this workload. + +PostgreSQL gives three things the server needs: + +- MVCC. Concurrent writers do not block each other. +- Real timestamp types. `created_at` and `expires_at` are `TEXT` today. +- `LISTEN`/`NOTIFY`. This maps onto multiplayer room pub/sub, and lets the + server run as more than one instance. + +Keep `mitmux` on SQLite. It is a single-user local tool, and its pure-Go +SQLite driver is what keeps cross-compilation free of CGO. + +## Scope + +Server only (`crates/server`). The TUI, WASM, and Steam builds do not touch +the database. + +## What makes this cheap + +- The migrations use no SQLite-only syntax. There is no `AUTOINCREMENT`, no + `PRAGMA`, no `strftime`, and no `WITHOUT ROWID`. +- Primary keys are `TEXT` UUIDs, not integer rowids. +- All 33 call sites use the runtime `sqlx::query` function, not the + `sqlx::query!` macro. There is no compile-time schema metadata, so no + `cargo sqlx prepare` step and no offline query cache to regenerate. + +## Steps + +1. Change the sqlx feature in `crates/server/Cargo.toml` from `sqlite` to + `postgres`. Keep `runtime-tokio-rustls`, `time`, and `uuid`. +2. Change the pool type from `SqlitePool` to `PgPool` in `state.rs`, and + change the connection string handling in `main.rs`. +3. Convert every placeholder from `?` to `$1`, `$2`, and so on. Postgres + numbers its placeholders. This is the largest mechanical change: 33 + queries across `auth.rs`, `stats.rs`, `friends.rs`, `multiplayer.rs`, + `cosmetics.rs`, `anticheat.rs`, `rate_limit.rs`, and `spotify.rs`. +4. Change `created_at TEXT` and `expires_at TEXT` to `TIMESTAMPTZ` in the + migrations. Update the Rust structs to `time::OffsetDateTime`. +5. Replace any `INSERT OR REPLACE` or `INSERT OR IGNORE` with + `INSERT ... ON CONFLICT`. Check `cosmetics.rs` and `rate_limit.rs` first. +6. Add a `docker-compose.yml` with a `postgres` service, so a contributor can + start a database with one command. +7. Run the full test suite against the new backend. + +## Verify + +- `cargo test --workspace` passes. +- Two clients can finish a multiplayer race at the same time, and both + results are written. +- A session expires at the correct time. This confirms the `TIMESTAMPTZ` + conversion. +- Rate limiting still rejects a burst from one user. + +## Follow-up + +Move multiplayer room pub/sub from in-process state to `LISTEN`/`NOTIFY`. +Only after that can the server run more than one instance. -- cgit v1.2.3