Promote GRO-2294 to UAT: Route Optimization security hardening (#194)

Promote dev→uat: GRO-2225 + GRO-2235 + GRO-2157 (atomic)

QA-approved on 37e42b3. CI green (Test, Lint & Typecheck, Build & Push).

.mcp.json

@@ -1,11 +0,0 @@
-{
-  "mcpServers": {
-    "gitea": {
-      "type": "http",
-      "url": "https://git-mcp.farh.net/mcp",
-      "headers": {
-        "Authorization": "Bearer ${GITEA_TOKEN}"
-      }
-    }
-  }
-}

UAT_PLAYBOOK.md

@@ -333,7 +333,7 @@ This means:
 | # | Scenario | Steps | Expected |
 |---|----------|-------|----------|
 | TC-API-13.1 | Get business settings | GET /api/admin/settings | 200 OK, business settings returned. Response body **must NOT include `googleMapsApiKey`** — the encrypted secret is redacted from the projection (GRO-2294, defense-in-depth); non-secret fields (`businessName`, colors, `routeOptimizationProvider`, etc.) are still present |
-| TC-API-13.2 | Update business settings | PATCH /api/admin/settings with updated values | 200 OK, settings updated. Response body **must NOT include `googleMapsApiKey`** — the encrypted secret is redacted from the PATCH response symmetrically with the GET projection (GRO-2299, defense-in-depth); non-secret updated fields are still returned |
+| TC-API-13.2 | Update business settings | PATCH /api/admin/settings with updated values | 200 OK, settings updated |
 | TC-API-13.3 | Upload logo | POST /api/admin/settings/logo/upload with file | 200 OK, logo uploaded and stored |
 | TC-API-13.4 | View logo | GET /api/admin/settings/logo | 200 OK, logo image returned |
 | TC-API-13.5 | Delete logo | DELETE /api/admin/settings/logo | 200 OK, logo removed |

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",
+    "reset": "node ./scripts/wait-for-db.mjs && tsx src/reset.ts && drizzle-kit migrate && tsx src/seed.ts",
     "studio": "drizzle-kit studio",
     "typecheck": "tsc --noEmit"
   },

packages/db/src/reset.ts

@@ -1,52 +1,13 @@
 /**
- * reset.ts — Drop all application tables, re-run migrations, and re-seed.
+ * reset.ts — Drop all application tables and re-run migrations + 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");
+import postgres from "postgres";
 
 async function reset() {
   const url = process.env.DATABASE_URL;
@@ -55,88 +16,52 @@ async function reset() {
     process.exit(1);
   }
 
-  if (
-    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.",
-    );
+  if (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);
   }
 
-  // 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 });
+  const client = postgres(url, { max: 1 });
 
-  try {
-    await withSeedAdvisoryLock(client, async () => {
-      console.log("Dropping all application tables...\n");
+  console.log("Dropping all application tables...\n");
 
-      // Drop dependencies (tables) first
-      await client`
-        DO $$ DECLARE
-          r RECORD;
-        BEGIN
-          FOR r IN (
-            SELECT tablename FROM pg_tables
-            WHERE schemaname = 'public'
-          ) LOOP
-            EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
-          END LOOP;
-        END $$;
-      `;
+  // Drop in dependency order (children before parents)
+  await client`
+    DO $$ DECLARE
+      r RECORD;
+    BEGIN
+      FOR r IN (
+        SELECT tablename FROM pg_tables
+        WHERE schemaname = 'public'
+      ) LOOP
+        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
+      END LOOP;
+    END $$;
+  `;
 
-      // Drop custom enums
-      await client`
-        DO $$ DECLARE
-          r RECORD;
-        BEGIN
-          FOR r IN (
-            SELECT typname FROM pg_type
-            WHERE typtype = 'e' AND typnamespace = (
-              SELECT oid FROM pg_namespace WHERE nspname = 'public'
-            )
-          ) LOOP
-            EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.typname) || ' CASCADE';
-          END LOOP;
-        END $$;
-      `;
+  // Drop custom enums
+  await client`
+    DO $$ DECLARE
+      r RECORD;
+    BEGIN
+      FOR r IN (
+        SELECT typname FROM pg_type
+        WHERE typtype = 'e' AND typnamespace = (
+          SELECT oid FROM pg_namespace WHERE nspname = 'public'
+        )
+      ) LOOP
+        EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.typname) || ' CASCADE';
+      END LOOP;
+    END $$;
+  `;
 
-      // Drop the drizzle migrations tracking table
-      await client`DROP TABLE IF EXISTS drizzle.__drizzle_migrations CASCADE`;
-      await client`DROP SCHEMA IF EXISTS drizzle CASCADE`;
+  // Drop the drizzle migrations tracking table
+  await client`DROP TABLE IF EXISTS drizzle.__drizzle_migrations 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");
 
-      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();
-  }
+  await client.end();
 }
 
 reset().catch((err) => {

packages/db/src/seed.ts

@@ -24,9 +24,9 @@ import type { MedicalAlert } from "@groombook/types";
 
 // ── Seed profile configuration ─────────────────────────────────────────────
 
-export type SeedProfile = "dev" | "uat" | "demo";
+type SeedProfile = "dev" | "uat" | "demo";
 
-export interface ProfileConfig {
+interface ProfileConfig {
   staffCount: { manager: number; receptionist: number; groomer: number; bather: number };
   clientCount: number;
   appointmentsBackDays: number;
@@ -35,7 +35,7 @@ export interface ProfileConfig {
   includeUatClients: boolean;
 }
 
-export const profiles: Record<SeedProfile, ProfileConfig> = {
+const profiles: Record<SeedProfile, ProfileConfig> = {
   dev: {
     staffCount: { manager: 1, receptionist: 1, groomer: 2, bather: 0 },
     clientCount: 100,
@@ -70,8 +70,6 @@ function getProfile(): SeedProfile {
   return "uat";
 }
 
-export { getProfile };
-
 // ── Deterministic PRNG (Mulberry32) ──────────────────────────────────────────
 
 /**
@@ -1196,7 +1194,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).
-export const SEED_ADVISORY_LOCK_KEY = 0x47524f4f; // "GROO" in ASCII — arbitrary, stable
+const SEED_ADVISORY_LOCK_KEY = 0x47524f4f; // "GROO" in ASCII — arbitrary, stable
 
 /**
  * Reserve a dedicated connection from `pool`, take the seed advisory lock
@@ -1209,7 +1207,7 @@ export const SEED_ADVISORY_LOCK_KEY = 0x47524f4f; // "GROO" in ASCII — arbitra
  * for the lock and release it from the same reserved connection. The
  * seed work itself still runs on the pooled connections.
  */
-export async function withSeedAdvisoryLock<T>(
+async function withSeedAdvisoryLock<T>(
   pool: ReturnType<typeof postgres>,
   fn: () => Promise<T>,
 ): Promise<T> {
@@ -1267,7 +1265,7 @@ async function seed() {
   await client.end();
 }
 
-export async function runSeedBody(
+async function runSeedBody(
   client: ReturnType<typeof postgres>,
   db: ReturnType<typeof drizzle>,
   profile: SeedProfile,

src/__tests__/settings.test.ts

@@ -7,7 +7,6 @@ import { Hono } from "hono";
 
 let selectRows: Record<string, unknown>[] = [];
 let insertReturning: Record<string, unknown>[] = [];
-let updateReturning: Record<string, unknown>[] = [];
 
 function makeChainable(data: unknown[]): unknown {
   const arr = [...data];
@@ -34,9 +33,6 @@ vi.mock("@groombook/db", () => {
       insert: () => ({
         values: () => ({ returning: () => insertReturning }),
       }),
-      update: () => ({
-        set: () => ({ where: () => ({ returning: () => updateReturning }) }),
-      }),
     }),
     businessSettings,
     eq: vi.fn(),
@@ -55,17 +51,6 @@ const { settingsRouter } = await import("../routes/settings.js");
 const app = new Hono();
 app.route("/settings", settingsRouter);
 
-// PATCH /settings is guarded by requireSuperUser(), which reads the staff record
-// from context. Inject a super-user staff row so the handler runs.
-const patchApp = new Hono<{
-  Variables: { staff: { id: string; isSuperUser: boolean } };
-}>();
-patchApp.use("*", async (c, next) => {
-  c.set("staff", { id: "staff-1", isSuperUser: true });
-  await next();
-});
-patchApp.route("/settings", settingsRouter);
-
 const FULL_ROW = {
   id: "settings-uuid-1",
   businessName: "GroomBook",
@@ -104,42 +89,3 @@ describe("GET /settings — googleMapsApiKey redaction (GRO-2294)", () => {
     expect(body.id).toBe("settings-uuid-new");
   });
 });
-
-describe("PATCH /settings — googleMapsApiKey redaction (GRO-2299)", () => {
-  beforeEach(() => {
-    selectRows = [];
-    insertReturning = [];
-    updateReturning = [];
-  });
-
-  function patchRequest(body: Record<string, unknown>) {
-    return patchApp.request("/settings", {
-      method: "PATCH",
-      headers: { "content-type": "application/json" },
-      body: JSON.stringify(body),
-    });
-  }
-
-  it("omits googleMapsApiKey from the PATCH response", async () => {
-    selectRows = [{ ...FULL_ROW }];
-    updateReturning = [{ ...FULL_ROW, businessName: "Updated Name" }];
-    const res = await patchRequest({ businessName: "Updated Name" });
-    expect(res.status).toBe(200);
-    const body = (await res.json()) as Record<string, unknown>;
-    expect(body).not.toHaveProperty("googleMapsApiKey");
-    // Non-secret updated fields are still returned.
-    expect(body.businessName).toBe("Updated Name");
-    expect(body.routeOptimizationProvider).toBe("google");
-  });
-
-  it("omits googleMapsApiKey on the auto-create-then-update branch", async () => {
-    selectRows = [];
-    insertReturning = [{ ...FULL_ROW, id: "settings-uuid-new" }];
-    updateReturning = [{ ...FULL_ROW, id: "settings-uuid-new" }];
-    const res = await patchRequest({ primaryColor: "#123456" });
-    expect(res.status).toBe(200);
-    const body = (await res.json()) as Record<string, unknown>;
-    expect(body).not.toHaveProperty("googleMapsApiKey");
-    expect(body.id).toBe("settings-uuid-new");
-  });
-});

src/routes/pets.ts

@@ -57,23 +57,6 @@ const createPetSchema = z.object({
   customFields: z.record(z.string(), z.string()).optional(),
   petSizeCategory: z.enum(["small", "medium", "large", "extra_large"]).optional(),
   coatType: z.enum(["short", "medium", "long", "double", "wire", "silky", "curly", "hairless"]).optional(),
-  // Extended pet profile fields (api/#39, GRO-1178).
-  // GRO-2172: these were missing from the schema, causing POST/PATCH to
-  // silently drop them even though migrations 0034/0036 and seed data
-  // populate them. GRO-1472 was the original UAT regression.
-  temperamentScore: z.number().int().min(1).max(5).optional(),
-  temperamentFlags: z.array(z.string().max(100)).max(20).optional(),
-  medicalAlerts: z
-    .array(
-      z.object({
-        type: z.string().max(100),
-        description: z.string().max(1000),
-        severity: z.enum(["low", "medium", "high"]),
-      })
-    )
-    .max(50)
-    .optional(),
-  preferredCuts: z.array(z.string().max(200)).max(20).optional(),
 });
 
 const updatePetSchema = createPetSchema.partial().omit({ clientId: true });
@@ -350,8 +333,7 @@ petsRouter.get("/:id/profile-summary", async (c) => {
 
 petsRouter.post("/", zValidator("json", createPetSchema), async (c) => {
   const db = getDb();
-  const { weightKg, dateOfBirth, customFields, medicalAlerts, ...rest } =
-    c.req.valid("json");
+  const { weightKg, dateOfBirth, customFields, ...rest } = c.req.valid("json");
   const [row] = await db
     .insert(pets)
     .values({
@@ -359,10 +341,6 @@ petsRouter.post("/", zValidator("json", createPetSchema), async (c) => {
       weightKg: weightKg?.toString(),
       dateOfBirth: dateOfBirth ? new Date(dateOfBirth) : undefined,
       customFields: customFields ?? {},
-      // GRO-2172: medicalAlerts shape from the API request is
-      // { type, description, severity } — the @groombook/types MedicalAlert
-      // has an optional server-generated `id`, so cast for the jsonb column.
-      medicalAlerts: medicalAlerts as never,
     })
     .returning();
   return c.json(row, 201);
@@ -373,8 +351,7 @@ petsRouter.patch(
   zValidator("json", updatePetSchema),
   async (c) => {
     const db = getDb();
-    const { weightKg, dateOfBirth, customFields, medicalAlerts, ...rest } =
-      c.req.valid("json");
+    const { weightKg, dateOfBirth, customFields, ...rest } = c.req.valid("json");
     const [row] = await db
       .update(pets)
       .set({
@@ -382,7 +359,6 @@ petsRouter.patch(
         weightKg: weightKg?.toString(),
         dateOfBirth: dateOfBirth ? new Date(dateOfBirth) : undefined,
         ...(customFields !== undefined ? { customFields } : {}),
-        medicalAlerts: medicalAlerts as never,
         updatedAt: new Date(),
       })
       .where(eq(pets.id, c.req.param("id")))

src/routes/settings.ts

@@ -65,8 +65,7 @@ settingsRouter.patch(
       .where(eq(businessSettings.id, settingsId))
       .returning();
 
-    if (!updated) throw new Error("Failed to update settings");
-    return c.json(redactSettings(updated));
+    return c.json(updated);
   }
 );