Merge pull request 'fix(db): grant app user permissions on players and games tables' (#5) from fix/db-permissions into main

PostgreSQL 15+ revokes CREATE from non-superusers in public schema by default.
Child 1's migration created the tables as postgres superuser, leaving the
txt_game_scores app user with no privileges — causing "permission denied"
on every request in production.

Adds a numbered provision migration to GRANT SELECT/INSERT/UPDATE on players
and games, plus USAGE/SELECT on games_id_seq, to the app user.

Task: d1c49d59-57bd-4eba-9e22-f25a04157ad4

Dockerfile

@@ -2,6 +2,9 @@ FROM node:22-alpine
 
 WORKDIR /app
 
+COPY package.json package-lock.json ./
+RUN npm ci --omit=dev
+
 COPY server.mjs ./
 
 EXPOSE 3000

docker-compose.yml

@@ -5,6 +5,11 @@ services:
     restart: unless-stopped
     environment:
       - DOMAIN=${DOMAIN:-localhost}
+      - TXT_GAME_DB_HOST=${TXT_GAME_DB_HOST}
+      - TXT_GAME_DB_PORT=${TXT_GAME_DB_PORT}
+      - TXT_GAME_DB_USER=${TXT_GAME_DB_USER}
+      - TXT_GAME_DB_PASSWORD=${TXT_GAME_DB_PASSWORD}
+      - TXT_GAME_DB_NAME=${TXT_GAME_DB_NAME}
     labels:
       - "traefik.enable=true"
       - "traefik.http.routers.txt-game.rule=Host(`txt.${DOMAIN:-localhost}`)"
@@ -13,7 +18,10 @@ services:
       - "traefik.http.services.txt-game.loadbalancer.server.port=3000"
     networks:
       - proxy
+      - postgres_postgres
 
 networks:
   proxy:
     external: true
+  postgres_postgres:
+    external: true

migrations/package.json

@@ -0,0 +1,12 @@
+{
+  "private": true,
+  "type": "module",
+  "dependencies": {
+    "pg": "*"
+  },
+  "devDependencies": {
+    "@types/node": "*",
+    "@types/pg": "*",
+    "typescript": "*"
+  }
+}

migrations/provision/postgres/000-init-scores.ts

@@ -0,0 +1,52 @@
+import pg from "pg";
+
+const client = new pg.Client({
+  host: process.env.PROVISION_HOST,
+  port: parseInt(process.env.PROVISION_PORT ?? "5432"),
+  user: process.env.PROVISION_USER,
+  password: process.env.PROVISION_PASSWORD,
+  database: process.env.PROVISION_DATABASE,
+});
+
+await client.connect();
+
+try {
+  await client.query(`
+    CREATE TABLE IF NOT EXISTS players (
+      id TEXT PRIMARY KEY,
+      games_played INT NOT NULL DEFAULT 0,
+      games_won INT NOT NULL DEFAULT 0,
+      games_lost INT NOT NULL DEFAULT 0,
+      best_attempts INT,
+      current_streak INT NOT NULL DEFAULT 0,
+      best_streak INT NOT NULL DEFAULT 0,
+      last_played_at TIMESTAMPTZ,
+      created_at TIMESTAMPTZ NOT NULL DEFAULT now()
+    )
+  `);
+
+  await client.query(`
+    CREATE TABLE IF NOT EXISTS games (
+      id BIGSERIAL PRIMARY KEY,
+      player_id TEXT NOT NULL REFERENCES players(id) ON DELETE CASCADE,
+      target INT NOT NULL,
+      attempts INT NOT NULL,
+      won BOOLEAN NOT NULL,
+      finished_at TIMESTAMPTZ NOT NULL DEFAULT now()
+    )
+  `);
+
+  await client.query(`
+    CREATE INDEX IF NOT EXISTS idx_players_scoreboard
+      ON players (best_attempts NULLS LAST, last_played_at)
+  `);
+
+  await client.query(`
+    CREATE INDEX IF NOT EXISTS idx_games_player_history
+      ON games (player_id, finished_at DESC)
+  `);
+
+  console.log("[txt-game migration-000] score schema created");
+} finally {
+  await client.end();
+}

migrations/provision/postgres/001-grant-permissions.ts

@@ -0,0 +1,29 @@
+import pg from "pg";
+
+const client = new pg.Client({
+  host: process.env.PROVISION_HOST,
+  port: parseInt(process.env.PROVISION_PORT ?? "5432"),
+  user: process.env.PROVISION_USER,
+  password: process.env.PROVISION_PASSWORD,
+  database: process.env.PROVISION_DATABASE,
+});
+
+await client.connect();
+
+try {
+  // The HAL postgres provisioner names the app user identically to the database.
+  // PROVISION_DATABASE = "txt_game_scores" = the app user that server.mjs connects as.
+  const appUser = process.env.PROVISION_DATABASE as string;
+
+  await client.query(
+    `GRANT SELECT, INSERT, UPDATE ON TABLE players, games TO "${appUser}"`
+  );
+
+  await client.query(
+    `GRANT USAGE, SELECT ON SEQUENCE games_id_seq TO "${appUser}"`
+  );
+
+  console.log(`[txt-game migration-001] permissions granted to ${appUser}`);
+} finally {
+  await client.end();
+}

module.yml

@@ -11,7 +11,23 @@ docker:
     context: .
     dockerfile: Dockerfile
 
+requires:
+  - provider: postgres
+    type: database
+    name: txt_game_scores
+    env_map:
+      TXT_GAME_DB_HOST: host
+      TXT_GAME_DB_PORT: port
+      TXT_GAME_DB_USER: user
+      TXT_GAME_DB_PASSWORD: password
+      TXT_GAME_DB_NAME: database
+
 env:
   DOMAIN:
     from: node.domain
     default: localhost
+  TXT_GAME_DB_HOST:
+  TXT_GAME_DB_PORT:
+  TXT_GAME_DB_USER:
+  TXT_GAME_DB_PASSWORD:
+  TXT_GAME_DB_NAME:

package-lock.json

@@ -0,0 +1,158 @@
+{
+  "name": "txt-game-work",
+  "lockfileVersion": 3,
+  "requires": true,
+  "packages": {
+    "": {
+      "dependencies": {
+        "pg": "^8.13.3"
+      }
+    },
+    "node_modules/pg": {
+      "version": "8.22.0",
+      "resolved": "https://registry.npmjs.org/pg/-/pg-8.22.0.tgz",
+      "integrity": "sha512-8wih1vVIBMxoUM2oB4soJsD9tDnDpLv4OXBJ+EJzFsvycD+lfyIreC2gGHq78f8jbLLt+bvlPTFdFZfJkOuzAA==",
+      "license": "MIT",
+      "dependencies": {
+        "pg-connection-string": "^2.14.0",
+        "pg-pool": "^3.14.0",
+        "pg-protocol": "^1.15.0",
+        "pg-types": "2.2.0",
+        "pgpass": "1.0.5"
+      },
+      "engines": {
+        "node": ">= 16.0.0"
+      },
+      "optionalDependencies": {
+        "pg-cloudflare": "^1.4.0"
+      },
+      "peerDependencies": {
+        "pg-native": ">=3.0.1"
+      },
+      "peerDependenciesMeta": {
+        "pg-native": {
+          "optional": true
+        }
+      }
+    },
+    "node_modules/pg-cloudflare": {
+      "version": "1.4.0",
+      "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.4.0.tgz",
+      "integrity": "sha512-Vo7z/6rrQYxpNRylp4Tlob2elzbh+N/MOQbxFVWCxS7oEx6jF53GTJFxK2WWpKuBRkmiin4Mt+xofFDjx09R0A==",
+      "license": "MIT",
+      "optional": true
+    },
+    "node_modules/pg-connection-string": {
+      "version": "2.14.0",
+      "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.14.0.tgz",
+      "integrity": "sha512-XwWDGcLRGCXAR8F/AM5bG7Q+A3Wm2s6QeEjlOKZLlH3UYcguiqCWKyWXVag5TLTIjR7oOJUY8kcADaZgWPyLeg==",
+      "license": "MIT"
+    },
+    "node_modules/pg-int8": {
+      "version": "1.0.1",
+      "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+      "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+      "license": "ISC",
+      "engines": {
+        "node": ">=4.0.0"
+      }
+    },
+    "node_modules/pg-pool": {
+      "version": "3.14.0",
+      "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.14.0.tgz",
+      "integrity": "sha512-gKtPkFdQPU3DksooVLi9LsjZxrsBUZIpa+7aVx+LV5pNh0KzP4Zleud2po+ConrxbuXGBJ6Hfer6hdgpIBpBaw==",
+      "license": "MIT",
+      "peerDependencies": {
+        "pg": ">=8.0"
+      }
+    },
+    "node_modules/pg-protocol": {
+      "version": "1.15.0",
+      "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.15.0.tgz",
+      "integrity": "sha512-cq9sECI5s0+uPUXjbz8ioyPJni6RzsRib0US67i5IoTZKw8fNeYlVE7u8F4dG7vEJJtc5wdD1K189lCCUwqWTQ==",
+      "license": "MIT"
+    },
+    "node_modules/pg-types": {
+      "version": "2.2.0",
+      "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+      "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+      "license": "MIT",
+      "dependencies": {
+        "pg-int8": "1.0.1",
+        "postgres-array": "~2.0.0",
+        "postgres-bytea": "~1.0.0",
+        "postgres-date": "~1.0.4",
+        "postgres-interval": "^1.1.0"
+      },
+      "engines": {
+        "node": ">=4"
+      }
+    },
+    "node_modules/pgpass": {
+      "version": "1.0.5",
+      "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+      "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+      "license": "MIT",
+      "dependencies": {
+        "split2": "^4.1.0"
+      }
+    },
+    "node_modules/postgres-array": {
+      "version": "2.0.0",
+      "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+      "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=4"
+      }
+    },
+    "node_modules/postgres-bytea": {
+      "version": "1.0.1",
+      "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+      "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=0.10.0"
+      }
+    },
+    "node_modules/postgres-date": {
+      "version": "1.0.7",
+      "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+      "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=0.10.0"
+      }
+    },
+    "node_modules/postgres-interval": {
+      "version": "1.2.0",
+      "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+      "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+      "license": "MIT",
+      "dependencies": {
+        "xtend": "^4.0.0"
+      },
+      "engines": {
+        "node": ">=0.10.0"
+      }
+    },
+    "node_modules/split2": {
+      "version": "4.2.0",
+      "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+      "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+      "license": "ISC",
+      "engines": {
+        "node": ">= 10.x"
+      }
+    },
+    "node_modules/xtend": {
+      "version": "4.0.2",
+      "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
+      "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=0.4"
+      }
+    }
+  }
+}

package.json

@@ -0,0 +1,7 @@
+{
+  "private": true,
+  "type": "module",
+  "dependencies": {
+    "pg": "^8.13.3"
+  }
+}

server.mjs

@@ -1,16 +1,59 @@
 /**
  * txt-game — number guessing game
- * Single-file Node.js HTTP server using only stdlib.
+ * Single-file Node.js HTTP server using only stdlib + pg.
  * Listens on port 3000, serves HTML form UI, maintains per-session state via cookie.
+ * Persists scores to Postgres when DB env vars are present; degrades gracefully otherwise.
  */
 
 import { createServer } from "node:http";
 import { randomUUID } from "node:crypto";
+import pg from "pg";
 
 const PORT = 3000;
 const MAX_ATTEMPTS = 7;
 
-/** @type {Map<string, { target: number, attempts: number, won: boolean, lost: boolean, history: number[] }>} */
+// ─── DB Pool ────────────────────────────────────────────────────────────────
+
+const DB_ENV_KEYS = [
+  "TXT_GAME_DB_HOST",
+  "TXT_GAME_DB_PORT",
+  "TXT_GAME_DB_USER",
+  "TXT_GAME_DB_PASSWORD",
+  "TXT_GAME_DB_NAME",
+];
+const hasDb = DB_ENV_KEYS.every((k) => process.env[k]);
+
+/** @type {pg.Pool | null} */
+let pool = null;
+
+if (hasDb) {
+  pool = new pg.Pool({
+    host: process.env.TXT_GAME_DB_HOST,
+    port: parseInt(process.env.TXT_GAME_DB_PORT ?? "5432", 10),
+    user: process.env.TXT_GAME_DB_USER,
+    password: process.env.TXT_GAME_DB_PASSWORD,
+    database: process.env.TXT_GAME_DB_NAME,
+    max: 5,
+    idleTimeoutMillis: 30000,
+  });
+  pool.on("error", (err) => {
+    console.error("[txt-game] pg pool error:", err.message);
+  });
+  console.log(
+    `[txt-game] DB pool initialised → ${process.env.TXT_GAME_DB_HOST}:${process.env.TXT_GAME_DB_PORT}/${process.env.TXT_GAME_DB_NAME}`
+  );
+} else {
+  console.warn(
+    "[txt-game] DB env vars missing — running in degraded mode (scores disabled)"
+  );
+}
+
+// ─── Session Store ──────────────────────────────────────────────────────────
+
+/**
+ * @typedef {{ target: number, attempts: number, won: boolean, lost: boolean, history: number[] }} Session
+ * @type {Map<string, Session>}
+ */
 const sessions = new Map();
 
 function newGame() {
@@ -23,11 +66,16 @@ function newGame() {
   };
 }
 
+/**
+ * Get or create the 24 h session keyed by the `sid` cookie.
+ * @param {string | undefined} cookies
+ * @returns {{ id: string, session: Session }}
+ */
 function getSession(cookies) {
-  const match = (cookies || "").match(/sid=([a-f0-9-]{36})/);
+  const match = (cookies ?? "").match(/sid=([a-f0-9-]{36})/);
   if (match) {
     const id = match[1];
-    if (sessions.has(id)) return { id, session: sessions.get(id) };
+    if (sessions.has(id)) return { id, session: /** @type {Session} */ (sessions.get(id)) };
   }
   const id = randomUUID();
   const session = newGame();
@@ -35,6 +83,140 @@ function getSession(cookies) {
   return { id, session };
 }
 
+/**
+ * Extract the persistent player ID from cookies, or generate a fresh UUID.
+ * The caller is responsible for setting a new `pid` cookie when the value is new.
+ * @param {string | undefined} cookies
+ * @returns {string}
+ */
+function getPid(cookies) {
+  const match = (cookies ?? "").match(/pid=([a-f0-9-]{36})/);
+  return match ? match[1] : randomUUID();
+}
+
+// ─── DB Helpers ─────────────────────────────────────────────────────────────
+
+/**
+ * Insert a player row if it doesn't exist yet. Silently no-ops when DB is unavailable.
+ * @param {string} pid
+ */
+async function upsertPlayer(pid) {
+  if (!pool) return;
+  try {
+    await pool.query(
+      `INSERT INTO players (id) VALUES ($1) ON CONFLICT (id) DO NOTHING`,
+      [pid]
+    );
+  } catch (err) {
+    console.error("[txt-game] upsertPlayer error:", /** @type {Error} */ (err).message);
+  }
+}
+
+/**
+ * Transactionally record a finished round: insert into games + update player aggregates.
+ * Logs + rolls back on any DB error — never propagates.
+ * @param {string} pid
+ * @param {Session} session
+ */
+async function recordRoundEnd(pid, session) {
+  if (!pool) return;
+  const client = await pool.connect().catch((err) => {
+    console.error("[txt-game] recordRoundEnd connect error:", /** @type {Error} */ (err).message);
+    return null;
+  });
+  if (!client) return;
+
+  try {
+    await client.query("BEGIN");
+
+    await client.query(
+      `INSERT INTO games (player_id, target, attempts, won) VALUES ($1, $2, $3, $4)`,
+      [pid, session.target, session.attempts, session.won]
+    );
+
+    if (session.won) {
+      // Update win aggregates; best_attempts is only set/updated on wins.
+      await client.query(
+        `UPDATE players SET
+           games_played   = games_played + 1,
+           games_won      = games_won + 1,
+           best_attempts  = LEAST(COALESCE(best_attempts, $1::int), $1::int),
+           current_streak = current_streak + 1,
+           best_streak    = GREATEST(best_streak, current_streak + 1),
+           last_played_at = now()
+         WHERE id = $2`,
+        [session.attempts, pid]
+      );
+    } else {
+      // Loss: reset streak, leave best_attempts unchanged.
+      await client.query(
+        `UPDATE players SET
+           games_played   = games_played + 1,
+           games_lost     = games_lost + 1,
+           current_streak = 0,
+           last_played_at = now()
+         WHERE id = $1`,
+        [pid]
+      );
+    }
+
+    await client.query("COMMIT");
+  } catch (err) {
+    console.error("[txt-game] recordRoundEnd error:", /** @type {Error} */ (err).message);
+    try {
+      await client.query("ROLLBACK");
+    } catch {
+      // ignore rollback error
+    }
+  } finally {
+    client.release();
+  }
+}
+
+/**
+ * Fetch the current player's stats row. Returns null when DB is unavailable or player not found.
+ * @param {string} pid
+ * @returns {Promise<{games_played:number, games_won:number, games_lost:number, best_attempts:number|null, current_streak:number, best_streak:number} | null>}
+ */
+async function fetchPlayerStats(pid) {
+  if (!pool) return null;
+  try {
+    const result = await pool.query(
+      `SELECT games_played, games_won, games_lost, best_attempts, current_streak, best_streak
+         FROM players WHERE id = $1`,
+      [pid]
+    );
+    return result.rows[0] ?? null;
+  } catch (err) {
+    console.error("[txt-game] fetchPlayerStats error:", /** @type {Error} */ (err).message);
+    return null;
+  }
+}
+
+/**
+ * Fetch the global top-10 scoreboard ordered by fewest wins-required attempts, then earliest last_played.
+ * Excludes players who have never won (best_attempts IS NULL).
+ * @returns {Promise<Array<{id:string, best_attempts:number, games_won:number, last_played_at:Date}>>}
+ */
+async function fetchScoreboard() {
+  if (!pool) return [];
+  try {
+    const result = await pool.query(
+      `SELECT id, best_attempts, games_won, last_played_at
+         FROM players
+        WHERE best_attempts IS NOT NULL
+        ORDER BY best_attempts ASC, last_played_at ASC
+        LIMIT 10`
+    );
+    return result.rows;
+  } catch (err) {
+    console.error("[txt-game] fetchScoreboard error:", /** @type {Error} */ (err).message);
+    return [];
+  }
+}
+
+// ─── Rendering ──────────────────────────────────────────────────────────────
+
 function hint(guess, target) {
   if (guess < target) return "Too low ↑";
   if (guess > target) return "Too high ↓";
@@ -50,7 +232,15 @@ function clue(guess, target) {
   return "🧊 Cold";
 }
 
-function renderPage(session, message, guess) {
+/**
+ * @param {Session} session
+ * @param {string} message
+ * @param {number | null} guess
+ * @param {string} pid
+ * @param {{games_played:number, games_won:number, games_lost:number, best_attempts:number|null, current_streak:number, best_streak:number} | null} playerStats
+ * @param {Array<{id:string, best_attempts:number, games_won:number, last_played_at:Date}>} scoreboard
+ */
+function renderPage(session, message, guess, pid, playerStats, scoreboard) {
   const attemptsLeft = MAX_ATTEMPTS - session.attempts;
   const historyRows = session.history
     .map(
@@ -88,10 +278,59 @@ function renderPage(session, message, guess) {
     statusSection = `<p class="attempts">Attempts left: <strong>${attemptsLeft}</strong></p>`;
   }
 
-  const messageHtml = message
-    ? `<p class="message">${message}</p>`
+  const messageHtml = message ? `<p class="message">${message}</p>` : "";
+
+  // ── "Your best" stats panel ──
+  const sp = playerStats;
+  const statsBestAttempts = sp?.best_attempts != null ? String(sp.best_attempts) : "—";
+  const statsPlayed = sp ? String(sp.games_played) : "0";
+  const statsWon    = sp ? String(sp.games_won)    : "0";
+  const statsLost   = sp ? String(sp.games_lost)   : "0";
+  const statsCurStreak  = sp ? String(sp.current_streak) : "0";
+  const statsBestStreak = sp ? String(sp.best_streak)    : "0";
+
+  const statsPanel = pool
+    ? `<div class="stats-panel">
+        <h2>Your best</h2>
+        <div class="stats-grid">
+          <div class="stat"><span class="stat-val">${statsPlayed}</span><span class="stat-lbl">Played</span></div>
+          <div class="stat"><span class="stat-val">${statsWon}</span><span class="stat-lbl">Won</span></div>
+          <div class="stat"><span class="stat-val">${statsLost}</span><span class="stat-lbl">Lost</span></div>
+          <div class="stat"><span class="stat-val">${statsBestAttempts}</span><span class="stat-lbl">Best attempts</span></div>
+          <div class="stat"><span class="stat-val">${statsCurStreak}</span><span class="stat-lbl">Streak</span></div>
+          <div class="stat"><span class="stat-val">${statsBestStreak}</span><span class="stat-lbl">Best streak</span></div>
+        </div>
+      </div>`
     : "";
 
+  // ── Scoreboard ──
+  let scoreboardHtml = "";
+  if (pool && scoreboard.length > 0) {
+    const pidPrefix = pid.slice(0, 8);
+    const rows = scoreboard
+      .map((row, i) => {
+        const rowPrefix = row.id.slice(0, 8);
+        const isMe = row.id === pid;
+        const cls = isMe ? ` class="me"` : "";
+        const meLabel = isMe ? " ◀" : "";
+        const date = row.last_played_at
+          ? new Date(row.last_played_at).toLocaleDateString(undefined, { month: "short", day: "numeric" })
+          : "—";
+        return `<tr${cls}><td>${i + 1}</td><td>${rowPrefix}${meLabel}</td><td>${row.best_attempts}</td><td>${row.games_won}</td><td>${date}</td></tr>`;
+      })
+      .join("");
+
+    scoreboardHtml = `<div class="scoreboard">
+      <h2>Scoreboard</h2>
+      <table>
+        <thead><tr><th>#</th><th>Player</th><th>Best</th><th>Wins</th><th>Last played</th></tr></thead>
+        <tbody>${rows}</tbody>
+      </table>
+    </div>`;
+  } else if (pool) {
+    scoreboardHtml = `<div class="scoreboard"><h2>Scoreboard</h2><p class="no-scores">No scores yet — win a game to appear here!</p></div>`;
+  }
+
   return `<!DOCTYPE html>
 <html lang="en">
 <head>
@@ -106,7 +345,7 @@ function renderPage(session, message, guess) {
       color: #e0e0e0;
       min-height: 100vh;
       display: flex;
-      align-items: center;
+      align-items: flex-start;
       justify-content: center;
       padding: 2rem;
     }
@@ -119,6 +358,7 @@ function renderPage(session, message, guess) {
       width: 100%;
     }
     h1 { color: #7ec8e3; margin-bottom: 0.5rem; font-size: 1.6rem; }
+    h2 { color: #7ec8e3; font-size: 1rem; margin-bottom: 0.75rem; margin-top: 0; }
     p.subtitle { color: #888; margin-bottom: 1.5rem; font-size: 0.9rem; }
     form { display: flex; gap: 0.75rem; align-items: center; margin-bottom: 1rem; flex-wrap: wrap; }
     input[type="number"] {
@@ -147,11 +387,36 @@ function renderPage(session, message, guess) {
     .failure { color: #e05252; margin: 0.75rem 0; font-weight: bold; }
     .attempts { color: #aaa; margin: 0.75rem 0; }
     label { color: #aaa; font-size: 0.9rem; display: block; margin-bottom: 0.5rem; }
-    table { width: 100%; border-collapse: collapse; margin-top: 1rem; font-size: 0.9rem; }
+    table { width: 100%; border-collapse: collapse; margin-top: 0.5rem; font-size: 0.9rem; }
     th, td { text-align: left; padding: 0.4rem 0.6rem; border-bottom: 1px solid #2a2a2a; }
     th { color: #7ec8e3; }
     td:first-child { color: #666; }
     td:nth-child(2) { font-weight: bold; color: #e0e0e0; }
+    .divider { border: none; border-top: 1px solid #2a2a2a; margin: 1.5rem 0; }
+    /* Stats panel */
+    .stats-panel { margin-top: 1.5rem; }
+    .stats-grid {
+      display: grid;
+      grid-template-columns: repeat(3, 1fr);
+      gap: 0.75rem;
+    }
+    .stat {
+      background: #111;
+      border: 1px solid #2a2a2a;
+      border-radius: 6px;
+      padding: 0.6rem 0.75rem;
+      display: flex;
+      flex-direction: column;
+      align-items: center;
+    }
+    .stat-val { font-size: 1.4rem; font-weight: bold; color: #7ec8e3; }
+    .stat-lbl { font-size: 0.75rem; color: #666; margin-top: 0.2rem; }
+    /* Scoreboard */
+    .scoreboard { margin-top: 1.5rem; }
+    .scoreboard table td:nth-child(2) { color: #aaa; font-weight: normal; font-family: monospace; font-size: 0.85rem; }
+    .scoreboard table tr.me td { background: #1e2d1e; }
+    .scoreboard table tr.me td:nth-child(2) { color: #5dbb63; }
+    .no-scores { color: #555; font-size: 0.9rem; }
   </style>
 </head>
 <body>
@@ -162,11 +427,15 @@ function renderPage(session, message, guess) {
     ${messageHtml}
     ${formSection}
     ${historySection}
+    ${statsPanel}
+    ${scoreboardHtml}
   </div>
 </body>
 </html>`;
 }
 
+// ─── Utilities ───────────────────────────────────────────────────────────────
+
 function parseBody(req) {
   return new Promise((resolve) => {
     let body = "";
@@ -178,32 +447,52 @@ function parseBody(req) {
   });
 }
 
-function setCookie(res, id) {
-  res.setHeader(
-    "Set-Cookie",
-    `sid=${id}; Path=/; HttpOnly; SameSite=Strict; Max-Age=86400`
-  );
+/**
+ * Set both the session cookie (24 h) and the persistent player cookie (365 d).
+ * @param {import("node:http").ServerResponse} res
+ * @param {string} sid
+ * @param {string} pid
+ */
+function setCookies(res, sid, pid) {
+  res.setHeader("Set-Cookie", [
+    `sid=${sid}; Path=/; HttpOnly; SameSite=Strict; Max-Age=86400`,
+    `pid=${pid}; Path=/; HttpOnly; SameSite=Strict; Max-Age=31536000`,
+  ]);
 }
 
+// ─── Server ──────────────────────────────────────────────────────────────────
+
 const server = createServer(async (req, res) => {
   // Forward x-forwarded-host so URLs resolve correctly behind Traefik
   const fwdHost = req.headers["x-forwarded-host"];
   if (fwdHost) req.headers.host = fwdHost;
 
-  const { id, session } = getSession(req.headers["cookie"]);
-  const url = req.url.split("?")[0];
+  const cookies = req.headers["cookie"];
+  const { id, session } = getSession(cookies);
+  const pid = getPid(cookies);
+  const url = (req.url ?? "/").split("?")[0];
+
+  // Ensure a players row exists for this visitor (idempotent)
+  await upsertPlayer(pid);
 
   if ((req.method === "GET" || req.method === "HEAD") && url === "/") {
-    setCookie(res, id);
+    const [playerStats, scoreboard] = await Promise.all([
+      fetchPlayerStats(pid),
+      fetchScoreboard(),
+    ]);
+    setCookies(res, id, pid);
     res.writeHead(200, { "Content-Type": "text/html; charset=utf-8" });
-    res.end(req.method === "HEAD" ? "" : renderPage(session, "", null));
+    res.end(
+      req.method === "HEAD"
+        ? ""
+        : renderPage(session, "", null, pid, playerStats, scoreboard)
+    );
     return;
   }
 
   if (req.method === "POST" && url === "/guess") {
     const body = await parseBody(req);
     const guess = parseInt(body.guess, 10);
-
     let message = "";
 
     if (session.won || session.lost) {
@@ -216,25 +505,34 @@ const server = createServer(async (req, res) => {
 
       if (guess === session.target) {
         session.won = true;
-        // Fix last history row hint (show "Correct!")
       } else if (session.attempts >= MAX_ATTEMPTS) {
         session.lost = true;
         message = `${hint(guess, session.target)} — ${clue(guess, session.target)}`;
       } else {
         message = `${hint(guess, session.target)} — ${clue(guess, session.target)}`;
       }
+
+      // Persist completed round
+      if (session.won || session.lost) {
+        await recordRoundEnd(pid, session);
+      }
     }
 
-    setCookie(res, id);
+    const [playerStats, scoreboard] = await Promise.all([
+      fetchPlayerStats(pid),
+      fetchScoreboard(),
+    ]);
+
+    setCookies(res, id, pid);
     res.writeHead(200, { "Content-Type": "text/html; charset=utf-8" });
-    res.end(renderPage(session, message, guess));
+    res.end(renderPage(session, message, guess, pid, playerStats, scoreboard));
     return;
   }
 
   if (req.method === "POST" && url === "/reset") {
     const fresh = newGame();
     sessions.set(id, fresh);
-    setCookie(res, id);
+    setCookies(res, id, pid);
     res.writeHead(303, { Location: "/" });
     res.end();
     return;