Auto-create staff records for OAuth users with no existing staff record

Fixes GRO-1118 - uat-tester receives HTTP 403 post-login

When a user authenticates via OAuth but has no corresponding staff record,
the RBAC middleware now auto-creates a staff record with a default
"receptionist" role instead of returning 403. This allows new OAuth
users to access the app immediately.

The middleware now checks for staff records in this order:
1. By userId (Better-Auth user ID)
2. By oidcSub (legacy OIDC subject)
3. By email (auto-link existing staff)
4. Create new staff record if authenticated user has email and name

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

apps/api/drizzle.config.ts

@@ -1,7 +1,7 @@
 import { defineConfig } from "drizzle-kit";
 
 export default defineConfig({
-  schema: "./src/db/schema.ts",
+  schema: "./src/schema.ts",
   out: "./migrations",
   dialect: "postgresql",
   dbCredentials: {

apps/api/migrations/0030_extended_pet_profile.sql

@@ -1,12 +0,0 @@
--- Migration: 0030_extended_pet_profile
--- Adds extended profile fields to the pets table
-
-BEGIN;
-
-ALTER TABLE pets ADD COLUMN coat_type text;
-ALTER TABLE pets ADD COLUMN temperament_score integer;
-ALTER TABLE pets ADD COLUMN temperament_flags jsonb DEFAULT '[]'::jsonb;
-ALTER TABLE pets ADD COLUMN medical_alerts jsonb DEFAULT '[]'::jsonb;
-ALTER TABLE pets ADD COLUMN preferred_cuts jsonb DEFAULT '[]'::jsonb;
-
-COMMIT;

apps/api/migrations/meta/0030_snapshot.json

@@ -1,48 +0,0 @@
-{
-  "id": "0030_extended_pet_profile",
-  "prevId": "0028_sms_reminders",
-  "version": "7",
-  "dialect": "postgresql",
-  "tables": {
-    "public.pets": {
-      "name": "pets",
-      "schema": "",
-      "columns": {
-        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
-        "client_id": { "name": "client_id", "type": "uuid", "isNullable": false },
-        "name": { "name": "name", "type": "text", "isNullable": false },
-        "species": { "name": "species", "type": "text", "isNullable": false },
-        "breed": { "name": "breed", "type": "text", "isNullable": true },
-        "weight_kg": { "name": "weight_kg", "type": "numeric(5, 2)", "isNullable": true },
-        "date_of_birth": { "name": "date_of_birth", "type": "timestamp", "isNullable": true },
-        "health_alerts": { "name": "health_alerts", "type": "text", "isNullable": true },
-        "grooming_notes": { "name": "grooming_notes", "type": "text", "isNullable": true },
-        "cut_style": { "name": "cut_style", "type": "text", "isNullable": true },
-        "shampoo_preference": { "name": "shampoo_preference", "type": "text", "isNullable": true },
-        "special_care_notes": { "name": "special_care_notes", "type": "text", "isNullable": true },
-        "custom_fields": { "name": "custom_fields", "type": "jsonb", "isNullable": false, "default": "'{}'::jsonb" },
-        "photo_key": { "name": "photo_key", "type": "text", "isNullable": true },
-        "photo_uploaded_at": { "name": "photo_uploaded_at", "type": "timestamp", "isNullable": true },
-        "image": { "name": "image", "type": "text", "isNullable": true },
-        "coat_type": { "name": "coat_type", "type": "text", "isNullable": true },
-        "temperament_score": { "name": "temperament_score", "type": "integer", "isNullable": true },
-        "temperament_flags": { "name": "temperament_flags", "type": "jsonb", "isNullable": true, "default": "'[]'::jsonb" },
-        "medical_alerts": { "name": "medical_alerts", "type": "jsonb", "isNullable": true, "default": "'[]'::jsonb" },
-        "preferred_cuts": { "name": "preferred_cuts", "type": "jsonb", "isNullable": true, "default": "'[]'::jsonb" },
-        "created_at": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
-        "updated_at": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
-      },
-      "indexes": { "idx_pets_client_id": { "name": "idx_pets_client_id", "columns": [{ "expression": "client_id", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false } },
-      "foreignKeys": { "pets_client_id_clients_id_fk": { "name": "pets_client_id_clients_id_fk", "tableFrom": "pets", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "cascade" } },
-      "compositePrimaryKeys": {},
-      "uniqueConstraints": {}
-    }
-  },
-  "enums": {},
-  "schemas": {},
-  "sequences": {},
-  "roles": {},
-  "policies": {},
-  "views": {},
-  "_meta": { "columns": {}, "schemas": {}, "tables": {} }
-}

apps/api/migrations/meta/_journal.json

@@ -204,20 +204,6 @@
       "when": 1775741667192,
       "tag": "0028_sms_reminders",
       "breakpoints": true
-    },
-    {
-      "idx": 29,
-      "version": "7",
-      "when": 1775828067192,
-      "tag": "0029_db_indexes_constraints",
-      "breakpoints": true
-    },
-    {
-      "idx": 30,
-      "version": "7",
-      "when": 1775914467192,
-      "tag": "0030_extended_pet_profile",
-      "breakpoints": true
     }
   ]
 }

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

@@ -5,7 +5,7 @@ let dbSelectResult: unknown[] = [];
 const mockEq = vi.fn((_col: unknown, _val: unknown) => ({ col: _col, val: _val }));
 const mockDecryptSecret = vi.fn((s: string) => `decrypted:${s}`);
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   const authProviderConfig = new Proxy(
     { _name: "auth_provider_config" },
     {

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

@@ -38,7 +38,7 @@ const mockGroomer: MockStaff = { id: "staff-3", role: "groomer", isSuperUser: fa
 
 // ─── Mock db module ───────────────────────────────────────────────────────────
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   const authProviderConfig = new Proxy(
     { _name: "auth_provider_config" },
     {

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

@@ -40,7 +40,7 @@ function resetMock() {
   deletedId = null;
 }
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   function makeChainable(data: unknown[]): unknown {
     const arr = [...data];
     const chain = new Proxy(arr, {

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

@@ -39,7 +39,7 @@ function resetMock() {
   lastUpdate = {};
 }
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   const appointments = new Proxy(
     { _name: "appointments" },
     { get: (t, p) => (p === "_name" ? "appointments" : { table: "appointments", column: p }) }

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

@@ -76,7 +76,7 @@ function makeChainableResult(data: unknown[]): unknown {
   });
 }
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   function makeTable(name: string) {
     return new Proxy(
       { _name: name },

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

@@ -40,7 +40,7 @@ function resetDb() {
 
 // ─── Module mocks ─────────────────────────────────────────────────────────────
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   const pets = new Proxy(
     { _name: "pets" },
     { get(t, p) { return p === "_name" ? "pets" : {}; } }

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

@@ -1,414 +0,0 @@
-import { describe, it, expect, vi, beforeEach } from "vitest";
-import { Hono } from "hono";
-import type { AppEnv, StaffRow } from "../middleware/rbac.js";
-import { petsRouter } from "../routes/pets.js";
-
-// ─── Mock staff fixtures ──────────────────────────────────────────────────────
-
-const MANAGER: StaffRow = {
-  id: "staff-manager-id",
-  oidcSub: "oidc-manager-sub",
-  userId: null,
-  role: "manager",
-  isSuperUser: true,
-  name: "Manager McManager",
-  email: "manager@example.com",
-  active: true,
-  icalToken: null,
-  createdAt: new Date(),
-  updatedAt: new Date(),
-};
-
-// ─── Mutable mock state ───────────────────────────────────────────────────────
-
-const CLIENT_ID = "client-uuid-extended";
-const PET_ID = "pet-uuid-extended";
-
-let petRows: Record<string, unknown>[] = [];
-let appointmentRows: Record<string, unknown>[] = [];
-let insertedValues: Record<string, unknown>[] = [];
-let updatedValues: Record<string, unknown>[] = [];
-let deletedId: string | null = null;
-
-function resetMock() {
-  petRows = [{
-    id: PET_ID,
-    clientId: CLIENT_ID,
-    name: "Biscuit",
-    species: "dog",
-    breed: "Golden Retriever",
-    weightKg: "30.00",
-    dateOfBirth: null,
-    healthAlerts: null,
-    groomingNotes: null,
-    cutStyle: null,
-    shampooPreference: null,
-    specialCareNotes: null,
-    customFields: {},
-    photoKey: null,
-    photoUploadedAt: null,
-    image: null,
-    coatType: null,
-    temperamentScore: null,
-    temperamentFlags: [],
-    medicalAlerts: [],
-    preferredCuts: [],
-    createdAt: new Date(),
-    updatedAt: new Date(),
-  }];
-  appointmentRows = [];
-  insertedValues = [];
-  updatedValues = [];
-  deletedId = null;
-}
-
-function makeSelectChainable(rows: unknown[]): unknown {
-  const chain = new Proxy([...rows], {
-    get(target, prop) {
-      if (prop === "where" || prop === "orderBy" || prop === "limit") {
-        return () => chain;
-      }
-      // @ts-expect-error proxy
-      return target[prop];
-    },
-  });
-  return chain;
-}
-
-function makeInsertChainable(): unknown {
-  let vals: Record<string, unknown> = {};
-  const chain = new Proxy({}, {
-    get(target, prop) {
-      if (prop === "values") {
-        return (v: Record<string, unknown>) => { vals = v; return chain; };
-      }
-      if (prop === "returning") {
-        return () => {
-          insertedValues.push(vals);
-          return [vals.id ? { ...vals, id: vals.id ?? PET_ID } : { ...vals, id: PET_ID }];
-        };
-      }
-      return chain;
-    },
-  });
-  return chain;
-}
-
-function makeUpdateChainable(): unknown {
-  let vals: Record<string, unknown> = {};
-  let whereId: string | null = null;
-  const chain = new Proxy({}, {
-    get(target, prop) {
-      if (prop === "set") {
-        return (v: Record<string, unknown>) => { vals = v; return chain; };
-      }
-      if (prop === "where") {
-        return (cond: unknown) => {
-          // Extract id from condition if it's an eq call
-          if (whereId) vals = { ...vals };
-          return chain;
-        };
-      }
-      if (prop === "returning") {
-        return () => {
-          const merged = { ...petRows[0], ...vals };
-          updatedValues.push(vals);
-          return [merged];
-        };
-      }
-      return chain;
-    },
-  });
-  return chain;
-}
-
-function makeDeleteChainable(): unknown {
-  let whereId: string | null = null;
-  const chain = new Proxy({}, {
-    get(target, prop) {
-      if (prop === "where") {
-        return (cond: unknown) => {
-          whereId = PET_ID;
-          return chain;
-        };
-      }
-      if (prop === "returning") {
-        return () => {
-          const row = petRows[0];
-          deletedId = row.id as string;
-          return [row];
-        };
-      }
-      return chain;
-    },
-  });
-  return chain;
-}
-
-vi.mock("../db", () => {
-  const pets = new Proxy({ _name: "pets" }, { get: (t, p) => p === "_name" ? "pets" : {} });
-  const appointments = new Proxy({ _name: "appointments" }, { get: (t, p) => p === "_name" ? "appointments" : {} });
-  return {
-    getDb: () => ({
-      select: () => ({
-        from: (table: unknown) => {
-          const name = (table as { _name?: string })._name;
-          if (name === "appointments") return makeSelectChainable(appointmentRows);
-          return makeSelectChainable(petRows);
-        },
-      }),
-      insert: () => makeInsertChainable(),
-      update: () => makeUpdateChainable(),
-      delete: () => makeDeleteChainable(),
-    }),
-    pets,
-    appointments,
-    and,
-    eq,
-    exists,
-    or,
-  };
-});
-
-// ─── Helpers ──────────────────────────────────────────────────────────────────
-
-function makeApp(staff: StaffRow = MANAGER) {
-  const app = new Hono<AppEnv>();
-  app.use("*", async (c, next) => {
-    c.set("staff", staff);
-    await next();
-  });
-  return app.route("/pets", petsRouter);
-}
-
-function createApp() {
-  const app = makeApp(MANAGER);
-  return app;
-}
-
-// ─── Tests ────────────────────────────────────────────────────────────────────
-
-describe("Extended pet profile fields — validation", () => {
-  beforeEach(resetMock);
-
-  it("rejects temperamentScore of 0 (below min)", async () => {
-    const app = createApp();
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ clientId: CLIENT_ID, name: "Test", species: "dog", temperamentScore: 0 }),
-    });
-    expect(res.status).toBe(400);
-    const body = await res.json();
-    expect(body.success).toBe(false);
-  });
-
-  it("rejects temperamentScore of 6 (above max)", async () => {
-    const app = createApp();
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ clientId: CLIENT_ID, name: "Test", species: "dog", temperamentScore: 6 }),
-    });
-    expect(res.status).toBe(400);
-    const body = await res.json();
-    expect(body.success).toBe(false);
-  });
-
-  it("rejects non-integer temperamentScore", async () => {
-    const app = createApp();
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ clientId: CLIENT_ID, name: "Test", species: "dog", temperamentScore: 3.5 }),
-    });
-    expect(res.status).toBe(400);
-  });
-
-  it("rejects invalid medicalAlert severity", async () => {
-    const app = createApp();
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({
-        clientId: CLIENT_ID,
-        name: "Test",
-        species: "dog",
-        medicalAlerts: [{ type: "seizure", description: "xyz", severity: "critical" }],
-      }),
-    });
-    expect(res.status).toBe(400);
-  });
-
-  it("accepts valid temperamentScore 1–5", async () => {
-    const app = createApp();
-    for (const score of [1, 2, 3, 4, 5]) {
-      resetMock();
-      const res = await app.request("/pets", {
-        method: "POST",
-        headers: { "Content-Type": "application/json" },
-        body: JSON.stringify({ clientId: CLIENT_ID, name: "Test", species: "dog", temperamentScore: score }),
-      });
-      expect(res.status).toBe(201);
-    }
-  });
-
-  it("accepts all valid medicalAlert severity values", async () => {
-    const app = createApp();
-    for (const severity of ["low", "medium", "high"] as const) {
-      resetMock();
-      const res = await app.request("/pets", {
-        method: "POST",
-        headers: { "Content-Type": "application/json" },
-        body: JSON.stringify({
-          clientId: CLIENT_ID,
-          name: "Test",
-          species: "dog",
-          medicalAlerts: [{ type: "allergy", description: "Sensitive to chicken", severity }],
-        }),
-      });
-      expect(res.status).toBe(201);
-    }
-  });
-});
-
-describe("Extended pet profile fields — create", () => {
-  beforeEach(resetMock);
-
-  it("accepts all extended fields on create", async () => {
-    const app = createApp();
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({
-        clientId: CLIENT_ID,
-        name: "Biscuit",
-        species: "dog",
-        breed: "Golden Retriever",
-        coatType: "double",
-        temperamentScore: 4,
-        temperamentFlags: ["anxious_with_dryers", "gentle"],
-        medicalAlerts: [
-          { type: "seizure", description: "Occasional episodes", severity: "medium" },
-        ],
-        preferredCuts: ["puppy cut", "teddy bear"],
-      }),
-    });
-    expect(res.status).toBe(201);
-    const body = await res.json();
-    expect(body.coatType).toBe("double");
-    expect(body.temperamentScore).toBe(4);
-    expect(body.temperamentFlags).toEqual(["anxious_with_dryers", "gentle"]);
-    expect(body.medicalAlerts).toEqual([{ type: "seizure", description: "Occasional episodes", severity: "medium" }]);
-    expect(body.preferredCuts).toEqual(["puppy cut", "teddy bear"]);
-  });
-
-  it("create without extended fields works (all optional)", async () => {
-    const app = createApp();
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ clientId: CLIENT_ID, name: "Basil", species: "cat" }),
-    });
-    expect(res.status).toBe(201);
-  });
-});
-
-describe("Extended pet profile fields — update", () => {
-  beforeEach(resetMock);
-
-  it("updates coatType", async () => {
-    const app = createApp();
-    const res = await app.request(`/pets/${PET_ID}`, {
-      method: "PATCH",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ coatType: "smooth" }),
-    });
-    expect(res.status).toBe(200);
-    const body = await res.json();
-    expect(body.coatType).toBe("smooth");
-  });
-
-  it("updates temperamentScore", async () => {
-    const app = createApp();
-    const res = await app.request(`/pets/${PET_ID}`, {
-      method: "PATCH",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ temperamentScore: 2 }),
-    });
-    expect(res.status).toBe(200);
-    const body = await res.json();
-    expect(body.temperamentScore).toBe(2);
-  });
-
-  it("rejects temperamentScore 0 on update", async () => {
-    const app = createApp();
-    const res = await app.request(`/pets/${PET_ID}`, {
-      method: "PATCH",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ temperamentScore: 0 }),
-    });
-    expect(res.status).toBe(400);
-  });
-
-  it("rejects invalid severity on update", async () => {
-    const app = createApp();
-    const res = await app.request(`/pets/${PET_ID}`, {
-      method: "PATCH",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({
-        medicalAlerts: [{ type: "x", description: "y", severity: "urgent" }],
-      }),
-    });
-    expect(res.status).toBe(400);
-  });
-
-  it("rejects too many temperamentFlags (>20)", async () => {
-    const app = createApp();
-    const flags = Array.from({ length: 21 }, (_, i) => `flag_${i}`);
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ clientId: CLIENT_ID, name: "Test", species: "dog", temperamentFlags: flags }),
-    });
-    expect(res.status).toBe(400);
-  });
-
-  it("rejects too many preferredCuts (>20)", async () => {
-    const app = createApp();
-    const cuts = Array.from({ length: 21 }, (_, i) => `cut_${i}`);
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ clientId: CLIENT_ID, name: "Test", species: "dog", preferredCuts: cuts }),
-    });
-    expect(res.status).toBe(400);
-  });
-
-  it("rejects too many medicalAlerts (>50)", async () => {
-    const app = createApp();
-    const alerts = Array.from({ length: 51 }, (_, i) => ({
-      type: `type_${i}`,
-      description: `desc_${i}`,
-      severity: "low" as const,
-    }));
-    const res = await app.request("/pets", {
-      method: "POST",
-      headers: { "Content-Type": "application/json" },
-      body: JSON.stringify({ clientId: CLIENT_ID, name: "Test", species: "dog", medicalAlerts: alerts }),
-    });
-    expect(res.status).toBe(400);
-  });
-
-  it("returns extended fields in GET response", async () => {
-    petRows = [{ ...petRows[0], coatType: "wire", temperamentScore: 3, temperamentFlags: ["gentle"], medicalAlerts: [], preferredCuts: ["scissor cut"] }];
-    const app = createApp();
-    const res = await app.request(`/pets/${PET_ID}`);
-    expect(res.status).toBe(200);
-    const body = await res.json();
-    expect(body.coatType).toBe("wire");
-    expect(body.temperamentScore).toBe(3);
-    expect(body.temperamentFlags).toEqual(["gentle"]);
-    expect(body.preferredCuts).toEqual(["scissor cut"]);
-  });
-});

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

@@ -47,7 +47,7 @@ function resetMock() {
   updatedValues = [];
 }
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   function makeChainable(data: unknown[]): unknown {
     const arr = [...data];
     const chain = new Proxy(arr, {

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

@@ -46,7 +46,7 @@ const GROOMER: StaffRow = {
 let staffLookupResult: StaffRow | null = null;
 let managerFallbackResult: StaffRow | null = MANAGER;
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   const staff = new Proxy(
     { _name: "staff" },
     {

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

@@ -23,7 +23,7 @@ const PET_ROW = {
 let clientResults: typeof ACTIVE_CLIENT[] = [];
 let petResults: typeof PET_ROW[] = [];
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   // Proxy objects for table/column references — values don't matter for tests
   const tableProxy = (name: string) =>
     new Proxy(

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

@@ -39,7 +39,7 @@ function clearAuthEnv() {
 
 // ─── Mock db module ───────────────────────────────────────────────────────────
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   const authProviderConfig = new Proxy(
     { _name: "auth_provider_config" },
     {

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

@@ -49,7 +49,7 @@ function resetMock() {
   updatedValues = [];
 }
 
-vi.mock("../db", () => {
+vi.mock("./db", () => {
   function makeChainable(data: unknown[]): unknown {
     const arr = [...data];
     const chain = new Proxy(arr, {

apps/api/src/db/schema.ts

@@ -12,16 +12,6 @@ import {
   uuid,
 } from "drizzle-orm/pg-core";
 
-// ─── Shared types ───────────────────────────────────────────────────────────────
-
-export type MedicalAlertSeverity = "low" | "medium" | "high";
-
-export interface MedicalAlert {
-  type: string;
-  description: string;
-  severity: MedicalAlertSeverity;
-}
-
 // ─── Enums ────────────────────────────────────────────────────────────────────
 
 export const appointmentStatusEnum = pgEnum("appointment_status", [
@@ -156,12 +146,6 @@ export const pets = pgTable(
     photoKey: text("photo_key"),
     photoUploadedAt: timestamp("photo_uploaded_at"),
     image: text("image"),
-    // Extended profile fields
-    coatType: text("coat_type"),
-    temperamentScore: integer("temperament_score"),
-    temperamentFlags: jsonb("temperament_flags").$type<string[]>().default([]),
-    medicalAlerts: jsonb("medical_alerts").$type<MedicalAlert[]>().default([]),
-    preferredCuts: jsonb("preferred_cuts").$type<string[]>().default([]),
     createdAt: timestamp("created_at").notNull().defaultNow(),
     updatedAt: timestamp("updated_at").notNull().defaultNow(),
   },

apps/api/src/db/seed.ts

@@ -459,32 +459,6 @@ async function seedKnownUsers() {
     }
   }
 
-  // ── Staff: UAT Tester (oidcSub from SEED_UAT_TESTER_OIDC_SUB env var) ──
-  const uatTesterOidcSub = process.env.SEED_UAT_TESTER_OIDC_SUB;
-  if (uatTesterOidcSub) {
-    const UAT_TESTER_STAFF_ID = "00000000-0000-0000-0000-000000000007";
-    const [existingUatTester] = await db
-      .select()
-      .from(schema.staff)
-      .where(eq(schema.staff.email, "uat-tester@groombook.dev"))
-      .limit(1);
-
-    if (existingUatTester) {
-      console.log(`✓ Staff 'UAT Tester' already exists — skipping`);
-    } else {
-      await db.insert(schema.staff).values({
-        id: UAT_TESTER_STAFF_ID,
-        name: "UAT Tester",
-        email: "uat-tester@groombook.dev",
-        oidcSub: uatTesterOidcSub,
-        role: "groomer",
-        isSuperUser: false,
-        active: true,
-      });
-      console.log(`✓ Created staff 'UAT Tester' (oidcSub: ${uatTesterOidcSub})`);
-    }
-  }
-
   // ── Staff: UAT Groomer Personas (SEED_UAT_GROOMER_EMAILS + SEED_UAT_GROOMER_NAMES) ──
   const groomerEmails = process.env.SEED_UAT_GROOMER_EMAILS?.split(",").map((e) => e.trim()).filter(Boolean) ?? [];
   const groomerNames = process.env.SEED_UAT_GROOMER_NAMES?.split(",").map((n) => n.trim()).filter(Boolean) ?? [];

apps/api/src/middleware/rbac.ts

@@ -1,7 +1,7 @@
 import type { MiddlewareHandler } from "hono";
-import { and, eq, getDb, sql, staff } from "../db";
+import { and, eq, getDb, sql, staff, staffRoleEnum } from "../db";
 
-export type StaffRole = "groomer" | "receptionist" | "manager";
+type StaffRole = typeof staffRoleEnum.enumValues[number];
 export type StaffRow = typeof staff.$inferSelect;
 
 export interface AppEnv {
@@ -110,6 +110,27 @@ export const resolveStaffMiddleware: MiddlewareHandler<AppEnv> = async (
       return;
     }
   }
+
+  // Auto-create staff record for authenticated OAuth users with no existing staff record
+  // This allows new OAuth users to access the app (defaults to receptionist role)
+  if (jwt.email && jwt.name) {
+    const [newStaff] = await db
+      .insert(staff)
+      .values({
+        email: jwt.email,
+        name: jwt.name,
+        userId: jwt.sub,
+        role: "receptionist",
+        active: true,
+      })
+      .returning();
+    if (newStaff) {
+      c.set("staff", newStaff);
+      await next();
+      return;
+    }
+  }
+
   return c.json(
     { error: "Forbidden: no staff record found for authenticated user" },
     403

apps/api/src/routes/pets.ts

@@ -24,15 +24,6 @@ const createPetSchema = z.object({
   shampooPreference: z.string().max(500).optional(),
   specialCareNotes: z.string().max(2000).optional(),
   customFields: z.record(z.string(), z.string()).optional(),
-  coatType: z.string().max(100).optional(),
-  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 });

apps/api/src/types/index.ts

@@ -42,23 +42,10 @@ export interface Pet {
   customFields: Record<string, string>;
   photoKey?: string;
   photoUploadedAt?: string;
-  coatType?: string | null;
-  temperamentScore?: number | null;
-  temperamentFlags?: string[];
-  medicalAlerts?: MedicalAlert[];
-  preferredCuts?: string[];
   createdAt: string;
   updatedAt: string;
 }
 
-export type MedicalAlertSeverity = "low" | "medium" | "high";
-
-export interface MedicalAlert {
-  type: string;
-  description: string;
-  severity: MedicalAlertSeverity;
-}
-
 export interface GroomingVisitLog {
   id: string;
   petId: string;

pnpm-lock.yaml

@@ -16,6 +16,12 @@ importers:
       '@aws-sdk/s3-request-presigner':
         specifier: ^3.800.0
         version: 3.1041.0
+      '@groombook/db':
+        specifier: workspace:*
+        version: link:../../packages/db
+      '@groombook/types':
+        specifier: workspace:*
+        version: link:../../packages/types
       '@hono/node-server':
         specifier: ^1.13.7
         version: 1.19.14(hono@4.12.16)
@@ -24,10 +30,7 @@ importers:
         version: 0.7.6(hono@4.12.16)(zod@4.4.2)
       better-auth:
         specifier: ^1.5.6
-        version: 1.6.9(drizzle-kit@0.30.6)(drizzle-orm@0.38.4(kysely@0.28.16)(postgres@3.4.9))(vitest@3.2.4(@types/node@22.19.17)(tsx@4.21.0))
-      drizzle-orm:
-        specifier: ^0.38.4
-        version: 0.38.4(kysely@0.28.16)(postgres@3.4.9)
+        version: 1.6.9(vitest@3.2.4(@types/node@22.19.17)(tsx@4.21.0))
       hono:
         specifier: ^4.6.17
         version: 4.12.16
@@ -37,9 +40,6 @@ importers:
       nodemailer:
         specifier: ^6.9.16
         version: 6.10.1
-      postgres:
-        specifier: ^3.4.5
-        version: 3.4.9
       stripe:
         specifier: ^22.0.0
         version: 22.1.0(@types/node@22.19.17)
@@ -62,9 +62,6 @@ importers:
       '@vitest/coverage-v8':
         specifier: ^3.2.4
         version: 3.2.4(vitest@3.2.4(@types/node@22.19.17)(tsx@4.21.0))
-      drizzle-kit:
-        specifier: ^0.30.4
-        version: 0.30.6
       eslint:
         specifier: ^9.18.0
         version: 9.39.4
@@ -81,6 +78,34 @@ importers:
         specifier: ^3.2.4
         version: 3.2.4(@types/node@22.19.17)(tsx@4.21.0)
 
+  packages/db:
+    dependencies:
+      drizzle-orm:
+        specifier: ^0.38.4
+        version: 0.38.4(kysely@0.28.16)(postgres@3.4.9)
+      postgres:
+        specifier: ^3.4.5
+        version: 3.4.9
+    devDependencies:
+      '@types/node':
+        specifier: ^22.10.7
+        version: 22.19.17
+      drizzle-kit:
+        specifier: ^0.30.4
+        version: 0.30.6
+      tsx:
+        specifier: ^4.19.0
+        version: 4.21.0
+      typescript:
+        specifier: ^5.7.3
+        version: 5.9.3
+
+  packages/types:
+    devDependencies:
+      typescript:
+        specifier: ^5.7.3
+        version: 5.9.3
+
 packages:
 
   '@ampproject/remapping@2.3.0':
@@ -2907,12 +2932,10 @@ snapshots:
       nanostores: 1.3.0
       zod: 4.4.2
 
-  '@better-auth/drizzle-adapter@1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)(drizzle-orm@0.38.4(kysely@0.28.16)(postgres@3.4.9))':
+  '@better-auth/drizzle-adapter@1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)':
     dependencies:
       '@better-auth/core': 1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0)
       '@better-auth/utils': 0.4.0
-    optionalDependencies:
-      drizzle-orm: 0.38.4(kysely@0.28.16)(postgres@3.4.9)
 
   '@better-auth/kysely-adapter@1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)(kysely@0.28.16)':
     dependencies:
@@ -3902,10 +3925,10 @@ snapshots:
 
   balanced-match@4.0.4: {}
 
-  better-auth@1.6.9(drizzle-kit@0.30.6)(drizzle-orm@0.38.4(kysely@0.28.16)(postgres@3.4.9))(vitest@3.2.4(@types/node@22.19.17)(tsx@4.21.0)):
+  better-auth@1.6.9(vitest@3.2.4(@types/node@22.19.17)(tsx@4.21.0)):
     dependencies:
       '@better-auth/core': 1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0)
-      '@better-auth/drizzle-adapter': 1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)(drizzle-orm@0.38.4(kysely@0.28.16)(postgres@3.4.9))
+      '@better-auth/drizzle-adapter': 1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)
       '@better-auth/kysely-adapter': 1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)(kysely@0.28.16)
       '@better-auth/memory-adapter': 1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)
       '@better-auth/mongo-adapter': 1.6.9(@better-auth/core@1.6.9(@better-auth/utils@0.4.0)(@better-fetch/fetch@1.1.21)(better-call@1.3.5(zod@4.4.2))(jose@6.2.3)(kysely@0.28.16)(nanostores@1.3.0))(@better-auth/utils@0.4.0)
@@ -3922,8 +3945,6 @@ snapshots:
       nanostores: 1.3.0
       zod: 4.4.2
     optionalDependencies:
-      drizzle-kit: 0.30.6
-      drizzle-orm: 0.38.4(kysely@0.28.16)(postgres@3.4.9)
       vitest: 3.2.4(@types/node@22.19.17)(tsx@4.21.0)
     transitivePeerDependencies:
       - '@cloudflare/workers-types'