Merge pull request 'txt-game: become a nox mesh module' (#6) from nox-mesh into main

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.

Dockerfile

@@ -1,4 +1,8 @@
-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
 

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.json

@@ -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"
+      }
+    ]
+  }
+}

server.mjs

@@ -7,6 +7,7 @@
 
 import { createServer } from "node:http";
 import { randomUUID } from "node:crypto";
+import { readFileSync } from "node:fs";
 import pg from "pg";
 
 const PORT = 3000;
@@ -14,14 +15,33 @@ const MAX_ATTEMPTS = 7;
 
 // ─── 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_PASSWORD",
   "TXT_GAME_DB_NAME",
 ];
-const hasDb = DB_ENV_KEYS.every((k) => process.env[k]);
+
+/** @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;
@@ -31,7 +51,7 @@ if (hasDb) {
     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,
+    password,
     database: process.env.TXT_GAME_DB_NAME,
     max: 5,
     idleTimeoutMillis: 30000,
@@ -44,10 +64,63 @@ if (hasDb) {
   );
 } else {
   console.warn(
-    "[txt-game] DB env vars missing — running in degraded mode (scores disabled)"
+    "[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 ──────────────────────────────────────────────────────────
 
 /**
@@ -542,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}`);
 });