1 files changed, 65 insertions, 0 deletions
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.