From 5b1ea38522dbf6bf60db5a2270463de0c12d9de3 Mon Sep 17 00:00:00 2001
From: srdusr <99972264+srdusr@users.noreply.github.com>
Date: Sun, 7 Dec 2025 20:38:00 +0200
Subject: Move the server to PostgreSQL, harden the lyrics proxy, add a hacking
 mode

PostgreSQL
- sqlx switched from the sqlite feature to postgres; the server now runs on
  Postgres 18 and the SQLite file is gone.
- 95 placeholders renumbered from ? to $N.
- REAL widened to DOUBLE PRECISION: Postgres REAL is float4 and will not
  decode into the f64 the code reads.
- flagged and is_bot are real BOOLEANs rather than 0/1 integers, with the
  decode side reading bool.
- The leaderboard's derived table gained the alias Postgres requires, its
  flag comparisons became boolean predicates, and INSERT OR IGNORE became
  ON CONFLICT DO NOTHING.
- u32 binds cast to i64; Postgres has no unsigned integer types.
- Integration tests run against a real database - Postgres has no in-memory
  mode - each in a throwaway schema, with search_path set per connection
  because it is session state and the pool opens more than one.
- Timestamps stay TEXT for now and LISTEN/NOTIFY is still unused; both are
  recorded in TODO-postgres.md rather than left implied.

Custom text and lyrics, checked rather than assumed
- Custom files never reach the server: they are read in the browser through
  the File API, so there is no upload, no path handling and no remote file
  inclusion to have. Verified by driving a hostile file - markup in the body
  and in the filename - all the way onto the typing screen: it renders as
  literal characters, no nodes are created, nothing executes, and the
  filename is escaped in the attribution too.
- That test found a real regression: picking Custom from the new mode picker
  selected it without ever starting it, so the mode was unstartable.
- /api/lyrics fixes its upstream host, so it cannot be pointed elsewhere, but
  it was an unbounded relay: now rate limited per IP, with length caps on
  artist and track and a ceiling on the response body it will read.

Hacking mode
- 22 single-line drills across recon, web, memory safety, exploit
  development, crypto, post-exploitation and defence, each syntax
  highlighted and each explaining what the line actually does.

All 19 modes verified to start, render and be typable.
---
 crates/server/migrations/0002_test_results.sql | 8 ++++----
 crates/server/migrations/0005_fairness.sql     | 2 +-
 crates/server/migrations/0009_bot_players.sql  | 2 +-
 3 files changed, 6 insertions(+), 6 deletions(-)

(limited to 'crates/server/migrations')

diff --git a/crates/server/migrations/0002_test_results.sql b/crates/server/migrations/0002_test_results.sql
index aa876b5..2a4cf69 100644
--- a/crates/server/migrations/0002_test_results.sql
+++ b/crates/server/migrations/0002_test_results.sql
@@ -2,10 +2,10 @@ CREATE TABLE test_results (
     id TEXT PRIMARY KEY,
     user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
     mode_key TEXT NOT NULL,
-    wpm REAL NOT NULL,
-    raw_wpm REAL NOT NULL,
-    accuracy REAL NOT NULL,
-    time_seconds REAL NOT NULL,
+    wpm DOUBLE PRECISION NOT NULL,
+    raw_wpm DOUBLE PRECISION NOT NULL,
+    accuracy DOUBLE PRECISION NOT NULL,
+    time_seconds DOUBLE PRECISION NOT NULL,
     created_at TEXT NOT NULL
 );
 
diff --git a/crates/server/migrations/0005_fairness.sql b/crates/server/migrations/0005_fairness.sql
index 28152d6..6d55b52 100644
--- a/crates/server/migrations/0005_fairness.sql
+++ b/crates/server/migrations/0005_fairness.sql
@@ -1,2 +1,2 @@
 ALTER TABLE test_results ADD COLUMN device_type TEXT NOT NULL DEFAULT 'desktop';
-ALTER TABLE test_results ADD COLUMN flagged INTEGER NOT NULL DEFAULT 0;
+ALTER TABLE test_results ADD COLUMN flagged BOOLEAN NOT NULL DEFAULT FALSE;
diff --git a/crates/server/migrations/0009_bot_players.sql b/crates/server/migrations/0009_bot_players.sql
index 790eb85..72516c5 100644
--- a/crates/server/migrations/0009_bot_players.sql
+++ b/crates/server/migrations/0009_bot_players.sql
@@ -3,6 +3,6 @@
 - the same cosmetics. The flag exists so the client can label them, because a
 - board that mixes synthetic scores into human ones without saying so is
 - telling its users something untrue.
-ALTER TABLE users ADD COLUMN is_bot INTEGER NOT NULL DEFAULT 0;
+ALTER TABLE users ADD COLUMN is_bot BOOLEAN NOT NULL DEFAULT FALSE;
 
 CREATE INDEX idx_users_is_bot ON users(is_bot);
-- 
cgit v1.2.3