txt-game: become a nox mesh module

The mesh builds the game from this repository and a commit (novox/hq ADR 0069); registry-api.novox.be, where HAL's image came from, is retired, so the running container can no longer be re-pulled. The HAL module.yml, docker-compose.yml and migrations/ beside this manifest retire with HAL. The score database is a postgres-database grant: host, port, login and database come from the binding (in a file the container reads as its environment), the password from a 0600 file named by TXT_GAME_DB_PASSWORD_FILE (ADR 0086). TXT_GAME_DB_PASSWORD still works, so HAL's deployment keeps running if it is rebuilt from main. The app now creates its schema when the database has none. HAL's provisioning migrations did that as the admin and then granted the app's login; under the mesh the app's own login owns its database, so the app makes its tables itself. A database that already has both tables - HAL's or one restored from it - is left exactly as it is. The base is node 22.23.2-alpine3.24 by digest, the image the running one was built on (its base layers match). Verified: the manifest parses on mesh-controller main and #149 and renders for a zurag.be node. Built from this commit, against throwaway postgres: on an empty granted database it creates both tables (owned by the granted login) and records a game; on a HAL-shaped database (tables owned by postgres, grants to the old login, password in the environment) it plays and creates nothing; and a pg_dump/pg_restore --no-owner copy of that database into a fresh granted one keeps every row (counts and md5 identical), is owned by the grant, and keeps taking games.
Merge pull request 'fix(db): grant app user permissions on players and games tables' (#5 ) from fix/db-permissions into main
2026-09-30 12:05:34 +02:00 · 2026-07-09 19:48:53 +02:00 · 2026-07-09 19:46:14 +02:00 · 2026-07-09 19:37:32 +02:00 · 2026-07-09 19:27:58 +02:00 · 2026-07-09 18:55:32 +02:00
10 changed files with 782 additions and 25 deletions
@@ -1,7 +1,14 @@
-FROM node:22-alpine
+# The base arrives pinned from module.json's build.on (node 22.23.2 on alpine 3.24, the image
+# the running deployment was built from); the mesh builds this image from this repository and
+# a commit (novox/hq ADR 0069). The build context is the repository root.
+ARG NODE_BASE
+FROM ${NODE_BASE}

 WORKDIR /app

+COPY package.json package-lock.json ./
+RUN npm ci --omit=dev
+
 COPY server.mjs ./

 EXPOSE 3000
@@ -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
@@ -0,0 +1,12 @@
+{
+  "private": true,
+  "type": "module",
+  "dependencies": {
+    "pg": "*"
+  },
+  "devDependencies": {
+    "@types/node": "*",
+    "@types/pg": "*",
+    "typescript": "*"
+  }
+}
@@ -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();
+}
@@ -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();
+}
@@ -0,0 +1,95 @@
+{
+  "module": "txt-game",
+  "version": "1",
+  "capabilities": [
+    "container-runtime"
+  ],
+  "requires": [
+    "postgres-database",
+    "route"
+  ],
+  "contributes": {
+    "postgres-database": {
+      "name": "txt-game"
+    },
+    "route": {
+      "label": "txt-game",
+      "endpoint": "web"
+    }
+  },
+  "binds": {
+    "postgres-database": "${dir:state}/database.json",
+    "route": "${dir:state}/route.json"
+  },
+  "secrets": {
+    "postgres-database": "${dir:state}/database.secret"
+  },
+  "listens": [
+    {
+      "name": "web",
+      "port": 3000,
+      "protocol": "tcp",
+      "from": "mesh",
+      "why": "the game's pages and form posts over http; its public name is a route grant and the proxy reaches it here"
+    }
+  ],
+  "resources": [
+    {
+      "id": "state",
+      "type": "directory",
+      "mode": "0700",
+      "place": "."
+    },
+    {
+      "id": "database-env",
+      "type": "file",
+      "path": "${dir:state}/database.env",
+      "mode": "0644",
+      "content": "TXT_GAME_DB_HOST=${bound:postgres-database:at}\nTXT_GAME_DB_PORT=${bound:postgres-database:port}\nTXT_GAME_DB_USER=${bound:postgres-database:as}\nTXT_GAME_DB_NAME=${bound:postgres-database:as}\n"
+    },
+    {
+      "id": "database-password",
+      "type": "file",
+      "path": "${dir:state}/database-password",
+      "mode": "0600",
+      "content": "${secret:postgres-database}"
+    },
+    {
+      "id": "server",
+      "type": "container",
+      "name": "txt-game",
+      "artifact": "app",
+      "ports": [
+        "3000"
+      ],
+      "env": {
+        "TXT_GAME_DB_PASSWORD_FILE": "/run/secrets/database"
+      },
+      "env-file": [
+        "${dir:state}/database.env"
+      ],
+      "volumes": [
+        "${dir:state}/database-password:/run/secrets/database:ro"
+      ],
+      "restart-on": [
+        "database-env",
+        "database-password"
+      ]
+    }
+  ],
+  "build": {
+    "on": [
+      {
+        "arg": "NODE_BASE",
+        "image": "node@sha256:b6f26b36c8ff49624cfdac716b8ea1138d606df02586a77d364bb5536a634f85"
+      }
+    ],
+    "artifacts": [
+      {
+        "name": "app",
+        "kind": "image",
+        "from": "Dockerfile"
+      }
+    ]
+  }
+}
@@ -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:
@@ -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"
+      }
+    }
+  }
+}
@@ -0,0 +1,7 @@
+{
+  "private": true,
+  "type": "module",
+  "dependencies": {
+    "pg": "^8.13.3"
+  }
+}
@@ -1,16 +1,132 @@
 /**
 * 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 { readFileSync } from "node:fs";
+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 ────────────────────────────────────────────────────────────────
+
+// Where the database is comes from the environment; the password comes from a file when
+// TXT_GAME_DB_PASSWORD_FILE names one (how the mesh delivers a secret — novox/hq ADR 0086: a
+// secret in the environment is readable by anything that can inspect the container), and
+// from TXT_GAME_DB_PASSWORD otherwise, which is how HAL still delivers it.
+const DB_ENV_KEYS = [
+  "TXT_GAME_DB_HOST",
+  "TXT_GAME_DB_PORT",
+  "TXT_GAME_DB_USER",
+  "TXT_GAME_DB_NAME",
+];
+
+/** @returns {string | undefined} */
+function dbPassword() {
+  const file = process.env.TXT_GAME_DB_PASSWORD_FILE;
+  if (file) {
+    try {
+      return readFileSync(file, "utf8").replace(/\r?\n$/, "") || undefined;
+    } catch (err) {
+      console.error(`[txt-game] cannot read TXT_GAME_DB_PASSWORD_FILE: ${err.message}`);
+      return undefined;
+    }
+  }
+  return process.env.TXT_GAME_DB_PASSWORD || undefined;
+}
+
+const password = dbPassword();
+const hasDb = DB_ENV_KEYS.every((k) => process.env[k]) && password !== undefined;
+
+/** @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,
+    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 settings missing — running in degraded mode (scores disabled)"
+  );
+}
+
+// ─── Schema ─────────────────────────────────────────────────────────────────
+
+/**
+ * Create the score tables when the database has none — what HAL's provisioning migrations
+ * (migrations/provision/postgres) did as the admin before the app first started. Under the mesh
+ * the app's own login owns its database, so the app makes its schema itself. A database that
+ * already has both tables (HAL's, or one restored from it) is left exactly as it is: nothing
+ * here alters or grants on existing tables.
+ */
+async function ensureSchema() {
+  if (!pool) return;
+  try {
+    const { rows } = await pool.query(
+      "SELECT to_regclass('public.players') IS NOT NULL AS players, to_regclass('public.games') IS NOT NULL AS games"
+    );
+    if (rows[0].players && rows[0].games) return;
+    await pool.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 pool.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 pool.query(`
+      CREATE INDEX IF NOT EXISTS idx_players_scoreboard
+        ON players (best_attempts NULLS LAST, last_played_at)
+    `);
+    await pool.query(`
+      CREATE INDEX IF NOT EXISTS idx_games_player_history
+        ON games (player_id, finished_at DESC)
+    `);
+    console.log("[txt-game] score schema created");
+  } catch (err) {
+    console.error("[txt-game] could not ensure the score schema:", err.message);
+  }
+}
+
+// ─── 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 +139,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 +156,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 +305,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 +351,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 +418,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 +431,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 +460,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 +500,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 +520,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];

-  if (req.method === "GET" && url === "/") {
-    setCookie(res, id);
+  // Ensure a players row exists for this visitor (idempotent)
+  await upsertPlayer(pid);
+
+  if ((req.method === "GET" || req.method === "HEAD") && url === "/") {
+    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, "", 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 +578,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;
@@ -244,6 +615,8 @@ const server = createServer(async (req, res) => {
  res.end("Not found");
 });

+await ensureSchema();
+
 server.listen(PORT, () => {
  console.log(`txt-game listening on port ${PORT}`);
 });
Author	SHA1	Message	Date
jschoubben	737152d88b	txt-game: become a nox mesh module The mesh builds the game from this repository and a commit (novox/hq ADR 0069); registry-api.novox.be, where HAL's image came from, is retired, so the running container can no longer be re-pulled. The HAL module.yml, docker-compose.yml and migrations/ beside this manifest retire with HAL. The score database is a postgres-database grant: host, port, login and database come from the binding (in a file the container reads as its environment), the password from a 0600 file named by TXT_GAME_DB_PASSWORD_FILE (ADR 0086). TXT_GAME_DB_PASSWORD still works, so HAL's deployment keeps running if it is rebuilt from main. The app now creates its schema when the database has none. HAL's provisioning migrations did that as the admin and then granted the app's login; under the mesh the app's own login owns its database, so the app makes its tables itself. A database that already has both tables - HAL's or one restored from it - is left exactly as it is. The base is node 22.23.2-alpine3.24 by digest, the image the running one was built on (its base layers match). Verified: the manifest parses on mesh-controller main and #149 and renders for a zurag.be node. Built from this commit, against throwaway postgres: on an empty granted database it creates both tables (owned by the granted login) and records a game; on a HAL-shaped database (tables owned by postgres, grants to the old login, password in the environment) it plays and creates nothing; and a pg_dump/pg_restore --no-owner copy of that database into a fresh granted one keeps every row (counts and md5 identical), is owned by the grant, and keeps taking games.	2026-09-30 12:05:34 +02:00
jschoubben	eab696ce42	Merge pull request 'fix(db): grant app user permissions on players and games tables' (#5 ) from fix/db-permissions into main	2026-07-09 19:48:53 +02:00
Warre (hal-developer)	1e7772ad29	fix(db): grant app user permissions on players and games tables 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	2026-07-09 19:46:14 +02:00
jschoubben	8c3c95916d	Merge pull request 'feat(scores): persist scores in Postgres, add scoreboard UI' (#4 ) from feat/score-persistence into main	2026-07-09 19:37:32 +02:00
Warre (hal-developer)	f39f9f19fe	feat(scores): persist scores in Postgres, add scoreboard UI Introduces a long-lived `pid` cookie (365 d) as persistent player identity alongside the existing 24 h `sid` session cookie. On first visit, a player row is upserted into `players`. On round end, a transaction inserts into `games` and updates aggregates (best_attempts on wins only, streak counters, last_played_at). DB failures are caught and logged — the game keeps serving. Adds a 3×2 'Your best' stats panel and a public top-10 scoreboard (fewest best_attempts ASC, earliest last_played_at as tie-breaker; identifiers truncated to 8 chars; current player highlighted). Infrastructure: adds `pg` dependency, Dockerfile now installs npm deps before copying server.mjs, docker-compose forwards all provisioned TXT_GAME_DB_* vars and joins the postgres_postgres network for container-to-container reach. Task: d1c49d59-57bd-4eba-9e22-f25a04157ad4	2026-07-09 19:27:58 +02:00
jschoubben	4a6ae87ed3	Merge pull request 'feat(db): provision Postgres and add score schema migration' (#3 ) from feat/postgres-provision into main	2026-07-09 18:55:32 +02:00
jschoubben	cbec696c59	feat(db): provision Postgres and add score schema migration Wire in the txt_game_scores database provision and create the players/games tables via a provision-scoped migration so Child 2 can implement the server-side score persistence. - module.yml: requires postgres database (txt_game_scores) with env_map for host/port/user/password/name; empty env keys added so env-sync populates them after provisioning - migrations/package.json: ESM, pg + typescript devdeps - migrations/provision/postgres/000-init-scores.ts: creates players and games tables with idempotent IF NOT EXISTS guards and two supporting indexes (scoreboard sort, history lookup) Server code, Dockerfile, and docker-compose.yml are unchanged — server integration is deferred to the follow-on child task. Task: 3dab55e3-9792-47a0-a4a6-ba7757517314	2026-07-09 18:51:18 +02:00
jschoubben	0139527147	Merge pull request 'fix(server): respond to HEAD / so health checks return 200' (#2 ) from fix/head-method into main	2026-07-09 17:55:05 +02:00
jschoubben	35c7c31f2e	fix(server): respond to HEAD / so health checks return 200 curl -sSI and Traefik probes use HEAD; the server only handled GET, causing HEAD / to fall through to the 404 handler.	2026-07-09 17:52:19 +02:00
jschoubben	026eebae3d	Merge pull request 'fix(deploy): use registry image and correct docker list format' (#1 ) from fix/docker-build-format into main	2026-07-09 16:32:13 +02:00