Merge pull request 'feat(GRO-2359): add POST /api/portal/clients-from-auth for OOBE' (#212) from feature/2357-p2-portal-clients-from-auth into dev

GRO-2359 (api): feat(GRO-2359): add POST /api/portal/clients-from-auth for OOBE (#212)

packages/db/package.json

@@ -21,7 +21,7 @@
     "wait-for-db": "node ./scripts/wait-for-db.mjs",
     "migrate": "node ./scripts/wait-for-db.mjs && drizzle-kit migrate",
     "seed": "node ./scripts/wait-for-db.mjs && tsx src/seed.ts",
-    "reset": "node ./scripts/wait-for-db.mjs && tsx src/reset.ts && drizzle-kit migrate && tsx src/seed.ts",
+    "reset": "node ./scripts/wait-for-db.mjs && tsx src/reset.ts",
     "studio": "drizzle-kit studio",
     "typecheck": "tsc --noEmit"
   },

packages/db/src/reset.ts

@@ -1,13 +1,52 @@
 /**
- * reset.ts — Drop all application tables and re-run migrations + seed.
+ * reset.ts — Drop all application tables, re-run migrations, and re-seed.
  *
  * Intended for local development only. Never run against production.
  *
  * Usage:
  *   DATABASE_URL=postgres://... npx tsx packages/db/src/reset.ts
+ *
+ * GRO-2139: the entire drop→migrate→seed chain runs inside a single
+ * Postgres advisory lock (SEED_ADVISORY_LOCK_KEY) so a concurrent
+ * `seed.ts` (e.g. the dev `seed-test-data-*` Job being recreated at
+ * the top of the hour) cannot interleave between `reset.ts` (DROP)
+ * and `seed.ts` (TRUNCATE+insert) and collide on `invoices_pkey`.
+ *
+ * Why this matters: `seed.ts` derives every primary key from a single
+ * shared Mulberry32 PRNG seeded with 42 (see `createPrng(42)` and
+ * `uuid()` in seed.ts). Two concurrent same-profile seeders therefore
+ * emit *identical* ids for the same logical row, and any moment
+ * between a concurrent `seed.ts` TRUNCATE and INSERT is exactly the
+ * window in which the second seeder's INSERT can hit a pkey already
+ * taken by the first. Pre-GRO-2123 this raced unconditionally;
+ * GRO-2123 added the advisory lock around `runSeedBody` but left
+ * `reset.ts` and `drizzle-kit migrate` outside the lock. This script
+ * now wraps the *whole* chain in the same lock: `withSeedAdvisoryLock`
+ * pins the lock to one reserved session and the DROP → migrate → seed
+ * work runs on the rest of the pool, so the lock guarantees mutual
+ * exclusion against any concurrent seeder for the entire chain.
+ *
+ * See: groombook/infra `apps/base/reset-cronjob.yaml` (CronJob) and
+ * `apps/base/seed-job.yaml` (one-shot Job) — both invoke the same
+ * `seed.ts` code path on the same database in `groombook-dev`.
  */
-
 import postgres from "postgres";
+import { drizzle } from "drizzle-orm/postgres-js";
+import { migrate } from "drizzle-orm/postgres-js/migrator";
+import { fileURLToPath } from "node:url";
+import { dirname, resolve } from "node:path";
+import * as schema from "./schema.js";
+import {
+  SEED_ADVISORY_LOCK_KEY,
+  withSeedAdvisoryLock,
+  getProfile,
+  runSeedBody,
+  profiles,
+} from "./seed.js";
+
+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;
@@ -16,52 +55,88 @@ async function reset() {
     process.exit(1);
   }
 
-  if (process.env.NODE_ENV === "production" && process.env.ALLOW_RESET !== "true") {
+  if (
-    console.error("[FATAL] db:reset must not be run in production without ALLOW_RESET=true.");
+    process.env.NODE_ENV === "production" &&
+    process.env.ALLOW_RESET !== "true"
+  ) {
+    console.error(
+      "[FATAL] db:reset must not be run in production without ALLOW_RESET=true.",
+    );
     process.exit(1);
   }
 
-  const client = postgres(url, { max: 1 });
+  // Pool sizing is load-bearing here. `withSeedAdvisoryLock` does
+  // `pool.reserve()` to pin the advisory lock to one dedicated session
+  // (a session-level lock released on a *different* pooled connection is
+  // a no-op), and the DROP / migrate / seed work then runs on the
+  // *remaining* pooled connections. The lock provides mutual exclusion
+  // 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
+  // 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
+  // use `max: 6` to match `seed()`'s headroom (1 reserved + 5 work).
+  const client = postgres(url, { max: 6 });
+  const db = drizzle(client, { schema });
 
-  console.log("Dropping all application tables...\n");
+  try {
+    await withSeedAdvisoryLock(client, async () => {
+      console.log("Dropping all application tables...\n");
 
-  // Drop in dependency order (children before parents)
+      // Drop dependencies (tables) first
-  await client`
+      await client`
-    DO $$ DECLARE
+        DO $$ DECLARE
-      r RECORD;
+          r RECORD;
-    BEGIN
+        BEGIN
-      FOR r IN (
+          FOR r IN (
-        SELECT tablename FROM pg_tables
+            SELECT tablename FROM pg_tables
-        WHERE schemaname = 'public'
+            WHERE schemaname = 'public'
-      ) LOOP
+          ) LOOP
-        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
+            EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
-      END LOOP;
+          END LOOP;
-    END $$;
+        END $$;
-  `;
+      `;
 
-  // Drop custom enums
+      // Drop custom enums
-  await client`
+      await client`
-    DO $$ DECLARE
+        DO $$ DECLARE
-      r RECORD;
+          r RECORD;
-    BEGIN
+        BEGIN
-      FOR r IN (
+          FOR r IN (
-        SELECT typname FROM pg_type
+            SELECT typname FROM pg_type
-        WHERE typtype = 'e' AND typnamespace = (
+            WHERE typtype = 'e' AND typnamespace = (
-          SELECT oid FROM pg_namespace WHERE nspname = 'public'
+              SELECT oid FROM pg_namespace WHERE nspname = 'public'
-        )
+            )
-      ) LOOP
+          ) LOOP
-        EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.typname) || ' CASCADE';
+            EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.typname) || ' CASCADE';
-      END LOOP;
+          END LOOP;
-    END $$;
+        END $$;
-  `;
+      `;
 
-  // Drop the drizzle migrations tracking table
+      // Drop the drizzle migrations tracking table
-  await client`DROP TABLE IF EXISTS drizzle.__drizzle_migrations CASCADE`;
+      await client`DROP TABLE IF EXISTS drizzle.__drizzle_migrations CASCADE`;
-  await client`DROP SCHEMA IF EXISTS drizzle CASCADE`;
+      await client`DROP SCHEMA IF EXISTS drizzle CASCADE`;
 
-  console.log("✓ All tables and enums dropped\n");
+      console.log("✓ All tables and enums dropped\n");
 
-  await client.end();
+      console.log("Running migrations...");
+      await migrate(db, { migrationsFolder: MIGRATIONS_FOLDER });
+      console.log("✓ Migrations applied\n");
+
+      console.log("Seeding database...");
+      const profile = getProfile();
+      const cfg = profiles[profile];
+      await runSeedBody(client, db, profile, cfg);
+    });
+
+    console.log(
+      `\n✓ Reset complete (advisory lock key=0x${SEED_ADVISORY_LOCK_KEY.toString(16)})`,
+    );
+  } finally {
+    await client.end();
+  }
 }
 
 reset().catch((err) => {

packages/db/src/seed.ts

@@ -24,9 +24,9 @@ import type { MedicalAlert } from "@groombook/types";
 
 // ── Seed profile configuration ─────────────────────────────────────────────
 
-type SeedProfile = "dev" | "uat" | "demo";
+export type SeedProfile = "dev" | "uat" | "demo";
 
-interface ProfileConfig {
+export interface ProfileConfig {
   staffCount: { manager: number; receptionist: number; groomer: number; bather: number };
   clientCount: number;
   appointmentsBackDays: number;
@@ -35,7 +35,7 @@ interface ProfileConfig {
   includeUatClients: boolean;
 }
 
-const profiles: Record<SeedProfile, ProfileConfig> = {
+export const profiles: Record<SeedProfile, ProfileConfig> = {
   dev: {
     staffCount: { manager: 1, receptionist: 1, groomer: 2, bather: 0 },
     clientCount: 100,
@@ -70,6 +70,8 @@ function getProfile(): SeedProfile {
   return "uat";
 }
 
+export { getProfile };
+
 // ── Deterministic PRNG (Mulberry32) ──────────────────────────────────────────
 
 /**
@@ -1400,7 +1402,7 @@ async function seedKnownUsers() {
 // from runbooks without ambiguity and binds to the single-argument
 // `pg_advisory_lock(int)` form, which postgres-js serializes as a plain
 // number (no bigint type plumbing required).
-const SEED_ADVISORY_LOCK_KEY = 0x47524f4f; // "GROO" in ASCII — arbitrary, stable
+export const SEED_ADVISORY_LOCK_KEY = 0x47524f4f; // "GROO" in ASCII — arbitrary, stable
 
 /**
  * Reserve a dedicated connection from `pool`, take the seed advisory lock
@@ -1413,7 +1415,7 @@ const SEED_ADVISORY_LOCK_KEY = 0x47524f4f; // "GROO" in ASCII — arbitrary, sta
  * for the lock and release it from the same reserved connection. The
  * seed work itself still runs on the pooled connections.
  */
-async function withSeedAdvisoryLock<T>(
+export async function withSeedAdvisoryLock<T>(
   pool: ReturnType<typeof postgres>,
   fn: () => Promise<T>,
 ): Promise<T> {
@@ -1471,7 +1473,7 @@ async function seed() {
   await client.end();
 }
 
-async function runSeedBody(
+export async function runSeedBody(
   client: ReturnType<typeof postgres>,
   db: ReturnType<typeof drizzle>,
   profile: SeedProfile,

src/__tests__/portalClientsFromAuth.test.ts

@@ -0,0 +1,201 @@
+import { describe, it, expect, vi, beforeEach } from "vitest";
+import { Hono } from "hono";
+import { getAuth } from "../lib/auth.js";
+
+const NEW_USER_EMAIL = "new-sso-user@example.com";
+const NEW_USER_NAME = "New SSO User";
+const NEW_USER_ID = "11111111-2222-3333-4444-555555555555";
+
+const BETTER_AUTH_SESSION = {
+  user: {
+    id: "auth-user-new",
+    email: NEW_USER_EMAIL,
+    name: NEW_USER_NAME,
+  },
+  session: {
+    id: "ba-session-new",
+    expiresAt: new Date(Date.now() + 60 * 60 * 1000),
+  },
+};
+
+let mockGetAuth: ReturnType<typeof vi.fn>;
+let mockGetSession: ReturnType<typeof vi.fn>;
+let existingClientRow: Record<string, unknown> | null = null;
+let insertedClientValues: Record<string, unknown> | null = null;
+let insertShouldThrow: { code?: string } | null = null;
+
+function makeChainable(data: unknown[]): unknown {
+  const arr = [...data];
+  return new Proxy(arr, {
+    get(target, prop) {
+      if (prop === "where" || prop === "orderBy" || prop === "limit") {
+        return () => makeChainable(target);
+      }
+      // @ts-expect-error proxy
+      return target[prop];
+    },
+  });
+}
+
+vi.mock("@groombook/db", () => {
+  const clients = new Proxy(
+    { _name: "clients" },
+    { get: (t, p) => (p === "_name" ? "clients" : { table: "clients", column: p }) }
+  );
+
+  return {
+    getDb: () => ({
+      select: () => ({
+        from: (table: { _name: string }) => {
+          if (table._name === "clients") {
+            return makeChainable(existingClientRow ? [existingClientRow] : []);
+          }
+          return makeChainable([]);
+        },
+      }),
+      insert: (table: { _name: string }) => ({
+        values: (vals: Record<string, unknown>) => {
+          if (insertShouldThrow) {
+            const err = new Error("unique violation") as Error & { code?: string };
+            err.code = insertShouldThrow.code;
+            throw err;
+          }
+          return {
+            returning: () => {
+              if (table._name === "clients") {
+                insertedClientValues = { id: NEW_USER_ID, ...vals };
+                return [insertedClientValues];
+              }
+              return [];
+            },
+          };
+        },
+      }),
+    }),
+    clients,
+    eq: vi.fn(),
+    and: vi.fn(),
+    inArray: vi.fn(),
+  };
+});
+
+vi.mock("../lib/auth.js", () => ({
+  getAuth: vi.fn(),
+}));
+
+const { portalRouter } = await import("../routes/portal.js");
+
+const app = new Hono();
+app.route("/portal", portalRouter);
+
+describe("POST /portal/clients-from-auth (GRO-2359)", () => {
+  beforeEach(() => {
+    existingClientRow = null;
+    insertedClientValues = null;
+    insertShouldThrow = null;
+    mockGetSession = vi.fn();
+    mockGetAuth = vi.fn(() => ({
+      api: {
+        getSession: mockGetSession,
+      },
+    }));
+    vi.mocked(getAuth).mockImplementation(mockGetAuth);
+  });
+
+  it("returns 401 when no Better Auth session is present", async () => {
+    mockGetSession.mockResolvedValue(null);
+    const res = await app.request("/portal/clients-from-auth", {
+      method: "POST",
+      headers: { "Content-Type": "application/json" },
+      body: JSON.stringify({ name: "Test User" }),
+    });
+    expect(res.status).toBe(401);
+    const body = await res.json();
+    expect(body.error).toBe("Unauthorized");
+  });
+
+  it("returns 400 when body fails zod validation (empty name)", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    const res = await app.request("/portal/clients-from-auth", {
+      method: "POST",
+      headers: { "Content-Type": "application/json" },
+      body: JSON.stringify({ name: "" }),
+    });
+    expect(res.status).toBe(400);
+  });
+
+  it("creates a new client row bound to the auth user's email and returns 201", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    const res = await app.request("/portal/clients-from-auth", {
+      method: "POST",
+      headers: { "Content-Type": "application/json" },
+      body: JSON.stringify({
+        name: " New SSO User ",
+        phone: "555-1234",
+        address: "1 Main St",
+        notes: "test note",
+      }),
+    });
+    expect(res.status).toBe(201);
+    const body = await res.json();
+    expect(body).toMatchObject({
+      id: NEW_USER_ID,
+      name: "New SSO User",
+      email: NEW_USER_EMAIL,
+    });
+    // Trim must be applied to the persisted values.
+    expect(insertedClientValues).not.toBeNull();
+    expect((insertedClientValues as Record<string, unknown>).name).toBe("New SSO User");
+    expect((insertedClientValues as Record<string, unknown>).email).toBe(NEW_USER_EMAIL);
+    expect((insertedClientValues as Record<string, unknown>).phone).toBe("555-1234");
+  });
+
+  it("normalizes empty optional fields to null on insert", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    await app.request("/portal/clients-from-auth", {
+      method: "POST",
+      headers: { "Content-Type": "application/json" },
+      body: JSON.stringify({ name: "Test", phone: "", address: "   " }),
+    });
+    expect(insertedClientValues).not.toBeNull();
+    expect((insertedClientValues as Record<string, unknown>).phone).toBeNull();
+    expect((insertedClientValues as Record<string, unknown>).address).toBeNull();
+  });
+
+  it("returns 409 when a client row already exists for this email", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    existingClientRow = { id: "existing-client-id", email: NEW_USER_EMAIL };
+    const res = await app.request("/portal/clients-from-auth", {
+      method: "POST",
+      headers: { "Content-Type": "application/json" },
+      body: JSON.stringify({ name: "Test" }),
+    });
+    expect(res.status).toBe(409);
+    const body = await res.json();
+    expect(body.error).toMatch(/already exists/i);
+    expect(insertedClientValues).toBeNull();
+  });
+
+  it("returns 409 on unique constraint race (23505)", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    insertShouldThrow = { code: "23505" };
+    const res = await app.request("/portal/clients-from-auth", {
+      method: "POST",
+      headers: { "Content-Type": "application/json" },
+      body: JSON.stringify({ name: "Test" }),
+    });
+    expect(res.status).toBe(409);
+  });
+
+  it("returns 503 when auth is not configured", async () => {
+    mockGetAuth.mockImplementation(() => {
+      throw new Error("Auth not initialized");
+    });
+    const res = await app.request("/portal/clients-from-auth", {
+      method: "POST",
+      headers: { "Content-Type": "application/json" },
+      body: JSON.stringify({ name: "Test" }),
+    });
+    expect(res.status).toBe(503);
+  });
+});

src/routes/portal.ts

@@ -147,6 +147,114 @@ portalRouter.post("/session-from-auth", async (c) => {
   );
 });
 
+// GRO-2359 — register a brand-new SSO user. The post-auth handler in the
+// web portal redirects here when `session-from-auth` returns 404, so the
+// OOBE can complete a customer record for the new user. Auth is via the
+// Better Auth session (same shape as `session-from-auth`), so this is
+// registered BEFORE the `validatePortalSession` middleware.
+//
+// Contract:
+//   POST /api/portal/clients-from-auth
+//   Body: { name: string; phone?: string|null; address?: string|null; notes?: string|null }
+//   201:  { id, name, email }
+//   400:  invalid body (zod failure)
+//   401:  no Better Auth session
+//   409:  a `clients` row already exists for this email (portal selection case)
+//   500:  insert failed
+//
+// We do NOT auto-link the user's auth account to the new client row; the
+// existing `session-from-auth` endpoint re-resolves the row by email on the
+// next call, so the OOBE's success path just navigates the user back to
+// `/` and lets the bridge mint a portal session.
+const createClientFromAuthSchema = z.object({
+  name: z.string().min(1).max(200),
+  phone: z.string().max(50).nullish(),
+  address: z.string().max(500).nullish(),
+  notes: z.string().max(2000).nullish(),
+});
+
+portalRouter.post(
+  "/clients-from-auth",
+  zValidator("json", createClientFromAuthSchema),
+  async (c) => {
+    let auth;
+    try {
+      auth = getAuth();
+    } catch {
+      return c.json({ error: "Authentication not configured" }, 503);
+    }
+
+    const session = await auth.api.getSession({
+      headers: c.req.raw.headers,
+    });
+
+    if (!session) {
+      return c.json({ error: "Unauthorized" }, 401);
+    }
+
+    const body = c.req.valid("json");
+    const db = getDb();
+
+    // Pre-check: if a client already exists for this email, return 409 so
+    // the OOBE can render the "portal selection" message (the user needs
+    // to contact their groomer to link the new SSO identity to the
+    // pre-existing customer record). We don't return the existing row to
+    // avoid leaking PII about other accounts.
+    const [existing] = await db
+      .select({ id: clients.id })
+      .from(clients)
+      .where(eq(clients.email, session.user.email))
+      .limit(1);
+
+    if (existing) {
+      return c.json(
+        { error: "A customer record with this email already exists" },
+        409,
+      );
+    }
+
+    let row;
+    try {
+      [row] = await db
+        .insert(clients)
+        .values({
+          name: body.name.trim(),
+          email: session.user.email,
+          phone: body.phone?.trim() || null,
+          address: body.address?.trim() || null,
+          notes: body.notes?.trim() || null,
+        })
+        .returning();
+    } catch (err) {
+      // Concurrent insert from a parallel OOBE submit — treat as 409.
+      if (
+        err instanceof Error &&
+        "code" in err &&
+        (err as { code?: string }).code === "23505"
+      ) {
+        return c.json(
+          { error: "A customer record with this email already exists" },
+          409,
+        );
+      }
+      throw err;
+    }
+
+    if (!row) {
+      return c.json({ error: "Failed to create client" }, 500);
+    }
+
+    return c.json(
+      {
+        id: row.id,
+        name: row.name,
+        email: row.email,
+      },
+      201,
+    );
+  },
+);
+
 // Apply middleware to all portal routes
 portalRouter.use("/*", validatePortalSession, portalAudit);