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. --- web/tests/README.md | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'web/tests') diff --git a/web/tests/README.md b/web/tests/README.md index fadc5b9..a76033a 100644 --- a/web/tests/README.md +++ b/web/tests/README.md @@ -15,8 +15,8 @@ can be re-run for regressions. `cd web && node serve.mjs` - The backend running on port 8787, pointed at a throwaway database (do not run tests against a production database): - `DATABASE_URL="sqlite:///tmp/typerpunk_test.db" PORT=8787 COOKIE_SECURE=0 \ - cargo run --package typerpunk-server` + `DATABASE_URL="postgres://typerpunk:typerpunk_dev@127.0.0.1/typerpunk_test" \ + PORT=8787 COOKIE_SECURE=0 cargo run --package typerpunk-server` Both servers must already be running before starting the suite - it does not launch or manage them itself. -- cgit v1.2.3