feat(api): add DB-touching /health/ready readiness probe (GRO-2678)

Register GET /health/ready before the /api/* auth middleware so it is
public and reachable by K8s readinessProbe on port 3000 without auth.
On success → 200 {"status":"ready"}; on any DB/schema failure → 503
{"status":"degraded"}. Logs the pg error code; never leaks SQL in body.
A dropped schema (42P01) surfaces as non-200, closing the /health mask
that allowed the GRO-2678 incident to go undetected for ~43h.

Add health-ready.test.ts covering the 200 success path, 503 on schema
drop (42P01), and 503 on connection error; all assert no SQL leakage.

Co-Authored-By: Paperclip <noreply@paperclip.ing>

UAT_PLAYBOOK.md

@@ -65,8 +65,11 @@ Expected: one row, `role = 'groomer'`. If zero rows return, the request hit the
 | # | Scenario | Steps | Expected |
 |---|----------|-------|----------|
 | TC-API-0.1 | Unauthenticated health check | GET /api/health | 200 OK, `{"status":"ok"}` |
+| TC-API-0.2 | DB-touching readiness check — healthy (GRO-2678) | GET /api/readyz | 200 OK, `{"status":"ready"}` |
+| TC-API-0.3 | DB-touching readiness check — response body safe | GET /api/readyz and inspect body | Body contains only `status` and (on error) `check` fields — no raw SQL, driver messages, or stack traces |
 
 > **Note (GRO-1544):** Health endpoint registered on `api` basePath before auth middleware at `/api/health`. The old path `/health` was incorrect (routed to web pod via HTTPRoute `/*` rule).
+> **Note (GRO-2678):** `/api/readyz` is a separate DB-touching endpoint for monitoring. It is intentionally NOT used for K8s liveness/readiness probes — those remain DB-less to avoid pod cycling on transient DB blips.
 
 ### 4.1 Authentication
 
@@ -455,6 +458,22 @@ Verifies the API process does not crash on transient boot-time DB connection res
 | TC-API-19.7 | Normal sign-in still works end-to-end | Follow TC-WEB-SSO-3 (SSO sign-in) on UAT | Successful sign-in, staff list visible — no regression from resilience changes |
 | TC-API-19.8 | Public routes unaffected during auth retry | While auth is retrying (TC-API-19.2 setup), `GET /api/branding` | 200 with branding data — public routes bypass auth and serve normally |
 
+### 4.20 Portal OOBE — Create Client from Auth (GRO-2359)
+
+Verifies the `POST /api/portal/clients-from-auth` endpoint that creates a new `clients` row for a first-time SSO user (out-of-box-experience registration). This endpoint requires a valid Better Auth session but does NOT require a portal session; it is the pre-portal step in the new-user OOBE flow.
+
+| TC | Test Case | Steps | Expected Result |
+|----|-----------|-------|-----------------|
+| TC-API-20.1 | Successful client creation | 1. Sign in via SSO to obtain a Better Auth session<br>2. `POST /api/portal/clients-from-auth` with `{ "name": "Test User" }` | 201 `{ "id": "<uuid>", "name": "Test User", "email": "<sso-email>" }` — new `clients` row created |
+| TC-API-20.2 | All optional fields accepted | `POST /api/portal/clients-from-auth` with `{ "name": "Test User", "phone": "555-1234", "address": "1 Main St", "notes": "VIP" }` (authenticated) | 201 with `id`, `name`, `email`; row in DB has all four fields |
+| TC-API-20.3 | Invalid body — missing name | `POST /api/portal/clients-from-auth` with `{}` (authenticated) | 400 (Zod validation failure); no row created |
+| TC-API-20.4 | Invalid body — empty name | `POST /api/portal/clients-from-auth` with `{ "name": "" }` (authenticated) | 400 — name must be at least 1 character |
+| TC-API-20.5 | No session — 401 | `POST /api/portal/clients-from-auth` with a valid body but **no** Better Auth session cookie | 401 `{ "error": "Unauthorized" }` |
+| TC-API-20.6 | Existing email — 409 | 1. Create a client row whose email matches the signed-in SSO user's email<br>2. `POST /api/portal/clients-from-auth` as that user | 409 `{ "error": "A customer record with this email already exists" }` — no duplicate row |
+| TC-API-20.7 | Auth not configured — 503 | Temporarily disable auth (e.g., point `OIDC_ISSUER` to an invalid host) so `getAuth()` throws<br>2. `POST /api/portal/clients-from-auth` | 503 `{ "error": "Authentication not configured" }` — graceful degradation |
+| TC-API-20.8 | Concurrent insert race — 409 | Simulate two near-simultaneous requests from the same SSO user (before any row exists) | At most one request returns 201; the other returns 409 — no duplicate row, no 500 |
+
+
 ## Pass/Fail Criteria
 
 **Pass:**
@@ -476,3 +495,4 @@ Verifies the API process does not crash on transient boot-time DB connection res
 ## Update Policy
 
 Any PR that changes user-facing behaviour MUST update this file. Test cases must be added, modified, or removed to reflect the new behaviour. The PR description must reference which playbook section was updated (e.g., "Updated UAT_PLAYBOOK.md §4.4 — new appointment rescheduling flow").
+

apps/api/src/__tests__/auth.test.ts

@@ -69,9 +69,14 @@ describe("auth init", () => {
   beforeEach(() => {
     dbSelectResult = [];
     vi.clearAllMocks();
+    // Stub fetch so OIDC discovery requests resolve instantly during tests.
+    // Without this, AbortSignal.timeout(5000) in auth.ts races with vitest's
+    // 5000ms default test timeout and causes flaky failures.
+    vi.stubGlobal("fetch", vi.fn().mockResolvedValue({ ok: false, status: 503 }));
   });
 
   afterEach(() => {
+    vi.unstubAllGlobals();
     process.env = { ...originalEnv };
   });
 

packages/db/src/reset.ts

@@ -32,7 +32,7 @@
  */
 import postgres from "postgres";
 import { drizzle } from "drizzle-orm/postgres-js";
-import { migrate } from "drizzle-orm/postgres-js/migrator";
+import { execSync } from "node:child_process";
 import { fileURLToPath } from "node:url";
 import { dirname, resolve } from "node:path";
 import * as schema from "./schema.js";
@@ -46,7 +46,6 @@ import {
 
 const __filename = fileURLToPath(import.meta.url);
 const __dirname = dirname(__filename);
-const MIGRATIONS_FOLDER = resolve(__dirname, "../migrations");
 
 async function reset() {
   const url = process.env.DATABASE_URL;
@@ -73,7 +72,7 @@ async function reset() {
   // across processes regardless of how many connections the work uses —
   // it does NOT require the work to share the lock's session.
   //
-  // Therefore `max` must be ≥ 2: 1 reserved for the lock + ≥1 free for
+  // Therefore `max` must be >= 2: 1 reserved for the lock + >=1 free for
   // the work. `max: 1` would let `reserve()` consume the only connection
   // and every query inside the callback would block forever waiting for
   // a connection that never frees (connection-starvation deadlock). We
@@ -122,7 +121,15 @@ async function reset() {
       console.log("✓ All tables and enums dropped\n");
 
       console.log("Running migrations...");
-      await migrate(db, { migrationsFolder: MIGRATIONS_FOLDER });
+      // GRO-2672: drizzle-orm's migrate() has a high-water-mark bug that skips
+      // migrations with stale `when` timestamps (0001, 0003, 0010, 0011). Use
+      // drizzle-kit instead -- it applies migrations by hash, matching the K8s
+      // migrate Job behaviour exactly.
+      execSync("pnpm exec drizzle-kit migrate", {
+        stdio: "inherit",
+        env: { ...process.env },
+        cwd: resolve(__dirname, ".."),
+      });
       console.log("✓ Migrations applied\n");
 
       console.log("Seeding database...");

src/__tests__/auth.test.ts

@@ -69,9 +69,14 @@ describe("auth init", () => {
   beforeEach(() => {
     dbSelectResult = [];
     vi.clearAllMocks();
+    // Stub fetch so OIDC discovery requests resolve instantly during tests.
+    // Without this, AbortSignal.timeout(5000) in auth.ts races with vitest's
+    // 5000ms default test timeout and causes flaky failures.
+    vi.stubGlobal("fetch", vi.fn().mockResolvedValue({ ok: false, status: 503 }));
   });
 
   afterEach(() => {
+    vi.unstubAllGlobals();
     process.env = { ...originalEnv };
   });
 

src/__tests__/health-ready.test.ts

@@ -0,0 +1,102 @@
+import { describe, it, expect, vi, beforeEach } from "vitest";
+import { Hono } from "hono";
+
+// ─── Mock db module ───────────────────────────────────────────────────────────
+
+let selectImpl: () => Promise<unknown>;
+
+vi.mock("@groombook/db", () => {
+  const staff = new Proxy(
+    { _name: "staff" },
+    {
+      get(_target, prop) {
+        if (prop === "_name") return "staff";
+        return { table: "staff", column: prop };
+      },
+    }
+  );
+
+  return {
+    getDb: () => ({
+      select: (_fields: unknown) => ({
+        from: (_table: unknown) => ({
+          limit: (_n: number) => selectImpl(),
+        }),
+      }),
+    }),
+    staff,
+  };
+});
+
+// ─── Build test app ───────────────────────────────────────────────────────────
+
+async function makeApp() {
+  const { getDb, staff } = await import("@groombook/db");
+
+  const app = new Hono();
+  app.get("/health/ready", async (c) => {
+    try {
+      await getDb().select({ id: staff.id }).from(staff).limit(1);
+      return c.json({ status: "ready" }, 200);
+    } catch (err) {
+      const pgCode = (err as Record<string, unknown>).code ?? "unknown";
+      console.error("[health/ready] DB check failed:", pgCode);
+      return c.json({ status: "degraded" }, 503);
+    }
+  });
+  return app;
+}
+
+// ─── Tests ────────────────────────────────────────────────────────────────────
+
+describe("GET /health/ready", () => {
+  beforeEach(() => {
+    vi.restoreAllMocks();
+  });
+
+  it("returns 200 {status:'ready'} when DB query succeeds", async () => {
+    selectImpl = () => Promise.resolve([{ id: "staff-1" }]);
+
+    const app = await makeApp();
+    const res = await app.request("/health/ready", { method: "GET" });
+    const body = (await res.json()) as Record<string, unknown>;
+
+    expect(res.status).toBe(200);
+    expect(body.status).toBe("ready");
+  });
+
+  it("returns 503 {status:'degraded'} when DB query throws (schema dropped)", async () => {
+    const schemaErr = Object.assign(new Error("relation \"staff\" does not exist"), { code: "42P01" });
+    selectImpl = () => Promise.reject(schemaErr);
+
+    const consoleSpy = vi.spyOn(console, "error").mockImplementation(() => {});
+
+    const app = await makeApp();
+    const res = await app.request("/health/ready", { method: "GET" });
+    const body = (await res.json()) as Record<string, unknown>;
+
+    expect(res.status).toBe(503);
+    expect(body.status).toBe("degraded");
+
+    // Must not leak SQL error details in the response body
+    expect(JSON.stringify(body)).not.toContain("42P01");
+    expect(JSON.stringify(body)).not.toContain("relation");
+
+    consoleSpy.mockRestore();
+  });
+
+  it("returns 503 {status:'degraded'} on any DB connection error", async () => {
+    selectImpl = () => Promise.reject(new Error("ECONNREFUSED"));
+
+    const consoleSpy = vi.spyOn(console, "error").mockImplementation(() => {});
+
+    const app = await makeApp();
+    const res = await app.request("/health/ready", { method: "GET" });
+    const body = (await res.json()) as Record<string, unknown>;
+
+    expect(res.status).toBe(503);
+    expect(body.status).toBe("degraded");
+
+    consoleSpy.mockRestore();
+  });
+});

src/__tests__/readyz.test.ts

@@ -0,0 +1,87 @@
+import { describe, it, expect, vi, beforeEach } from "vitest";
+import { Hono } from "hono";
+
+// ─── Mock db module ───────────────────────────────────────────────────────────
+
+let selectImpl: () => Promise<unknown>;
+
+vi.mock("@groombook/db", () => {
+  const staff = new Proxy(
+    { _name: "staff" },
+    {
+      get(_target, prop) {
+        if (prop === "_name") return "staff";
+        return { table: "staff", column: prop };
+      },
+    }
+  );
+
+  return {
+    getDb: () => ({
+      select: (_fields: unknown) => ({
+        from: (_table: unknown) => ({
+          limit: (_n: number) => selectImpl(),
+        }),
+      }),
+    }),
+    staff,
+  };
+});
+
+// ─── Build test app ───────────────────────────────────────────────────────────
+
+async function makeApp() {
+  // Import after mocks are in place
+  const { getDb, staff } = await import("@groombook/db");
+
+  const app = new Hono();
+  app.get("/api/readyz", async (c) => {
+    try {
+      await getDb().select({ id: staff.id }).from(staff).limit(1);
+      return c.json({ status: "ready" }, 200);
+    } catch (err) {
+      console.error("[readyz] DB check failed:", err);
+      return c.json({ status: "degraded", check: "db" }, 503);
+    }
+  });
+  return app;
+}
+
+// ─── Tests ────────────────────────────────────────────────────────────────────
+
+describe("GET /api/readyz", () => {
+  beforeEach(() => {
+    vi.restoreAllMocks();
+  });
+
+  it("returns 200 {status:'ready'} when DB query succeeds", async () => {
+    selectImpl = () => Promise.resolve([{ id: "staff-1" }]);
+
+    const app = await makeApp();
+    const res = await app.request("/api/readyz", { method: "GET" });
+    const body = (await res.json()) as Record<string, unknown>;
+
+    expect(res.status).toBe(200);
+    expect(body.status).toBe("ready");
+  });
+
+  it("returns 503 {status:'degraded',check:'db'} when DB query throws", async () => {
+    selectImpl = () => Promise.reject(new Error("42P01: relation staff does not exist"));
+
+    const consoleSpy = vi.spyOn(console, "error").mockImplementation(() => {});
+
+    const app = await makeApp();
+    const res = await app.request("/api/readyz", { method: "GET" });
+    const body = (await res.json()) as Record<string, unknown>;
+
+    expect(res.status).toBe(503);
+    expect(body.status).toBe("degraded");
+    expect(body.check).toBe("db");
+
+    // Raw SQL / driver error must NOT appear in the response body
+    expect(JSON.stringify(body)).not.toContain("42P01");
+    expect(JSON.stringify(body)).not.toContain("relation");
+
+    consoleSpy.mockRestore();
+  });
+});

src/index.ts

@@ -65,6 +65,28 @@ app.use(
 app.get("/health", (c) => c.json({ status: "ok" }));
 // /api/health: used by Gateway HTTPRoute (/api/* → API pod)
 app.get("/api/health", (c) => c.json({ status: "ok" }));
+// /health/ready: DB-touching readiness probe — K8s removes pod from endpoints when schema is dropped (GRO-2689)
+app.get("/health/ready", async (c) => {
+  try {
+    await getDb().select({ id: staff.id }).from(staff).limit(1);
+    return c.json({ status: "ready" }, 200);
+  } catch (err) {
+    const pgCode = (err as Record<string, unknown>).code ?? "unknown";
+    console.error("[health/ready] DB check failed:", pgCode);
+    return c.json({ status: "degraded" }, 503);
+  }
+});
+// /api/readyz: DB-touching deep health check consumed by monitoring (not K8s probes)
+// Distinct from /health so a dropped schema triggers an alert without cycling pods (GRO-2678)
+app.get("/api/readyz", async (c) => {
+  try {
+    await getDb().select({ id: staff.id }).from(staff).limit(1);
+    return c.json({ status: "ready" }, 200);
+  } catch (err) {
+    console.error("[readyz] DB check failed:", err);
+    return c.json({ status: "degraded", check: "db" }, 503);
+  }
+});
 
 // Public booking routes — no auth required, must be registered before auth middleware
 app.route("/api/book", bookRouter);

src/lib/auth.ts

@@ -329,5 +329,10 @@ export async function initAuth(): Promise<void> {
     });
   })();
 
-  await authInitPromise;
+  try {
+    await authInitPromise;
+  } catch (err) {
+    authInitPromise = null; // allow retry on next call
+    throw err;
+  }
 }