diff options
| author | srdusr <[email protected]> | 2025-12-07 20:38:00 +0200 |
|---|---|---|
| committer | srdusr <[email protected]> | 2025-12-07 20:38:00 +0200 |
| commit | 5b1ea38522dbf6bf60db5a2270463de0c12d9de3 (patch) | |
| tree | c96e037c34230a05f2a457c5040843602dc7cf1f /TODO-postgres.md | |
| parent | 24f1eb6cc611f458c45f0ac4046efce51211d7d3 (diff) | |
| download | typerpunk-5b1ea38522dbf6bf60db5a2270463de0c12d9de3.tar.gz typerpunk-5b1ea38522dbf6bf60db5a2270463de0c12d9de3.zip | |
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.
Diffstat (limited to 'TODO-postgres.md')
| -rw-r--r-- | TODO-postgres.md | 24 |
1 files changed, 23 insertions, 1 deletions
diff --git a/TODO-postgres.md b/TODO-postgres.md index 5353ac1..6221891 100644 --- a/TODO-postgres.md +++ b/TODO-postgres.md @@ -1,4 +1,26 @@ -# Migrate the server from SQLite to PostgreSQL +# Migrate the server from SQLite to PostgreSQL - DONE + +Done on 2026-08-30. The server runs on Postgres 18: sqlx switched to the +`postgres` feature, 95 placeholders renumbered to `$N`, `REAL` columns +widened to `DOUBLE PRECISION` (Postgres REAL is float4 and will not decode +into f64), the `flagged` and `is_bot` flags made real BOOLEANs, the +leaderboard's derived table given the alias Postgres requires, and +`INSERT OR IGNORE` rewritten as `ON CONFLICT DO NOTHING`. Integration tests +run against a real database, each in its own throwaway schema, since +Postgres has no in-memory mode. + +## Still open + +- **Timestamps are still TEXT.** They are RFC3339 and sort correctly as + text, so this is not a correctness problem, but `TIMESTAMPTZ` would let + the database do date arithmetic instead of the application. +- **LISTEN/NOTIFY is unused.** Multiplayer rooms are still in-process, so + the server cannot yet run as more than one instance. + +--- + +## Original note + ## Why |