fix(test): mock db to handle sql count(*) queries and async iteration

The petProfileSummary mock's sql tag returned a plain string instead of
a proper Drizzle SQL object, so count(*) queries via .as("count") failed.
Also added Symbol.asyncIterator support for for-await-of patterns used
in the pets router.

Fixes: GRO-1917

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

.ci-trigger

@@ -1 +1 @@
-GRO-1757 PR-based CI build trigger - 2026-05-26T00:15:41Z
+GRO-1757 direct push CI trigger - 2026-05-26T00:15:41Z

.gitea/workflows/ci.yml

@@ -2,9 +2,9 @@ name: CI
 
 on:
   push:
-    branches: [main, dev]
+    branches: [main, dev, uat]
   pull_request:
-    branches: [main, dev]
+    branches: [main, dev, uat]
   workflow_dispatch:
     inputs:
       ref:
@@ -32,7 +32,9 @@ jobs:
         run: pnpm install --frozen-lockfile
 
       - name: Typecheck
-        run: pnpm --filter @groombook/api typecheck
+        run: |
+          pnpm --filter @groombook/api typecheck
+          pnpm --filter @groombook/db typecheck
 
       - name: Lint
         run: pnpm --filter @groombook/api lint
@@ -96,7 +98,6 @@ jobs:
           file: Dockerfile
           target: runner
           push: true
-          provenance: false
           tags: |
             git.farh.net/groombook/api:${{ steps.version.outputs.tag }}
             ${{ github.ref == 'refs/heads/main' && 'git.farh.net/groombook/api:latest' || '' }}
@@ -111,7 +112,6 @@ jobs:
           file: Dockerfile
           target: migrate
           push: true
-          provenance: false
           tags: |
             git.farh.net/groombook/migrate:${{ steps.version.outputs.tag }}
             ${{ github.ref == 'refs/heads/main' && 'git.farh.net/groombook/migrate:latest' || '' }}
@@ -126,7 +126,6 @@ jobs:
           file: Dockerfile
           target: seed
           push: true
-          provenance: false
           tags: |
             git.farh.net/groombook/seed:${{ steps.version.outputs.tag }}
             ${{ github.ref == 'refs/heads/main' && 'git.farh.net/groombook/seed:latest' || '' }}
@@ -141,7 +140,6 @@ jobs:
           file: Dockerfile
           target: reset
           push: true
-          provenance: false
           tags: |
             git.farh.net/groombook/reset:${{ steps.version.outputs.tag }}
             ${{ github.ref == 'refs/heads/main' && 'git.farh.net/groombook/reset:latest' || '' }}

Dockerfile

@@ -50,4 +50,5 @@ CMD ["pnpm", "--filter", "@groombook/db", "seed"]
 
 # Reset stage — drops all tables, re-runs migrations, and re-seeds
 FROM builder AS reset
+RUN corepack enable && corepack prepare pnpm@9.15.4 --activate
 CMD ["pnpm", "--filter", "@groombook/db", "reset"]

UAT_PLAYBOOK.md

@@ -98,6 +98,22 @@ GroomBook API is a Hono-based REST service (TypeScript/Node.js) powering the pet
 | TC-API-3.13 | Reject too many temperamentFlags | POST /api/pets with 21 temperamentFlags | 400 Bad Request, max 20 flags enforced |
 | TC-API-3.14 | Reject too many preferredCuts | POST /api/pets with 21 preferredCuts | 400 Bad Request, max 20 cuts enforced |
 | TC-API-3.15 | Reject too many medicalAlerts | POST /api/pets with 51 medicalAlerts | 400 Bad Request, max 50 alerts enforced |
+| TC-API-3.16 | Get pet profile summary | GET /api/pets/{id}/profile-summary | 200 OK, aggregated profile with grooming history, visit count, upcoming appointment |
+| TC-API-3.17 | Get pet profile summary — groomer restricted | GET /api/pets/{id}/profile-summary as groomer with no pet linkage | 403 Forbidden |
+| TC-API-3.18 | Get pet profile summary — visitCount returns full count | GET /api/pets/{id}/profile-summary with 2+ completed appointments | visitCount >= 2 (not capped at 1) |
+| TC-API-3.19 | Get pet profile summary — upcomingAppointment excludes past | GET /api/pets/{id}/profile-summary with a past confirmed/scheduled appointment | upcomingAppointment is null (past appointments filtered by startTime >= now) |
+
+#### Seed Data Verification (GRO-1898)
+
+> As of PR #98, UAT seed data populates all 5 extended profile fields for every pet, including the 5 deterministic UAT test client pets (Alpha, Bravo, Charlie, Delta, Echo). This enables manual verification of extended profile rendering without requiring a DB reset.
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-3.20 | GET /api/clients returns seed data | GET /api/clients | 200 OK, array with 1+ clients (UAT seed creates 500 + 5 deterministic UAT clients) |
+| TC-API-3.21 | GET /api/pets/{id} returns extended fields for seed pet | Pick any pet ID from UAT test clients (uat-alpha through uat-echo pet names: TestBuddy, TestMax, TestCooper, TestRocky, TestDuke) and GET /api/pets/{id} | 200 OK; coatType, temperamentScore, temperamentFlags, medicalAlerts, preferredCuts all non-null |
+| TC-API-3.22 | Verify medicalAlerts shape | GET /api/pets/{id} for any pet with non-empty medicalAlerts | medicalAlerts is an array; each entry has type, description, severity |
+| TC-API-3.23 | Verify UAT test pet Charlie has behavioral alert | GET /api/pets/{id} where name = "TestCooper" (pet for uat-charlie@groombook.dev) | medicalAlerts includes an entry with type: "behavioral", severity: "low" or "high" |
+| TC-API-3.24 | Verify UAT test pet Delta has skin alert | GET /api/pets/{id} where name = "TestRocky" (pet for uat-delta@groombook.dev) | medicalAlerts includes an entry with type: "skin" |
 
 ### 4.4 Appointment Scheduling
 
@@ -159,6 +175,10 @@ GroomBook API is a Hono-based REST service (TypeScript/Node.js) powering the pet
 | TC-API-8.5 | Add waitlist entry | POST /api/portal/waitlist with pet and service | 201 Created, waitlist entry created |
 | TC-API-8.6 | View portal invoices | GET /api/portal/invoices | 200 OK, list of client's invoices returned |
 | TC-API-8.7 | Pay multiple invoices | POST /api/portal/invoices/pay-multiple with invoice IDs | 200 OK, payment intent created |
+| TC-API-8.8 | SSO bridge — valid Better Auth session | POST /api/portal/session-from-auth with valid Better Auth session cookie (authenticated SSO user with matching client email) | 201 Created, `{sessionId, clientId, clientName}` returned |
+| TC-API-8.9 | SSO bridge — no Better Auth session | POST /api/portal/session-from-auth without Better Auth session cookie | 401 Unauthorized |
+| TC-API-8.10 | SSO bridge — no matching client | POST /api/portal/session-from-auth with valid Better Auth session for a user with no client record | 404 Not Found, error "No client record found for this user" |
+| TC-API-8.11 | SSO bridge — returned session works on portal routes | After TC-API-8.8, use returned sessionId as `X-Impersonation-Session-Id` header on GET /api/portal/me | 200 OK, client profile returned |
 
 ### 4.9 Waitlist
 

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

@@ -0,0 +1,402 @@
+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(),
+};
+
+const GROOMER: StaffRow = {
+  id: "staff-groomer-id",
+  oidcSub: "oidc-groomer-sub",
+  userId: null,
+  role: "groomer",
+  isSuperUser: false,
+  name: "Groomer McGroome",
+  email: "groomer@example.com",
+  active: true,
+  icalToken: null,
+  createdAt: new Date(),
+  updatedAt: new Date(),
+};
+
+// ─── Mutable mock state ───────────────────────────────────────────────────────
+
+const CLIENT_ID = "client-uuid-summary";
+const PET_ID = "pet-uuid-summary";
+
+interface MockState {
+  pets: Record<string, unknown>[];
+  appointments: Record<string, unknown>[];
+  groomingLogs: Record<string, unknown>[];
+  staffMembers: Record<string, unknown>[];
+  services: Record<string, unknown>[];
+}
+
+let mock: MockState;
+
+function resetMock() {
+  mock = {
+    pets: [{
+      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: "double",
+      temperamentScore: 3,
+      temperamentFlags: ["gentle"],
+      medicalAlerts: [],
+      preferredCuts: ["puppy cut"],
+      createdAt: new Date("2024-01-01"),
+      updatedAt: new Date("2024-01-01"),
+    }],
+    appointments: [
+      {
+        id: "appt-completed-1",
+        clientId: CLIENT_ID,
+        petId: PET_ID,
+        serviceId: "service-1",
+        staffId: "staff-groomer-id",
+        batherStaffId: null,
+        status: "completed",
+        startTime: new Date("2024-06-01T09:00:00Z"),
+        endTime: new Date("2024-06-01T11:00:00Z"),
+        notes: null,
+        priceCents: 6000,
+        seriesId: null,
+        seriesIndex: null,
+        groupId: null,
+        confirmationStatus: "confirmed",
+        confirmedAt: null,
+        cancelledAt: null,
+        confirmationToken: null,
+        customerNotes: null,
+        createdAt: new Date("2024-05-15"),
+        updatedAt: new Date("2024-05-15"),
+      },
+      {
+        id: "appt-upcoming-1",
+        clientId: CLIENT_ID,
+        petId: PET_ID,
+        serviceId: "service-2",
+        staffId: "staff-groomer-id",
+        batherStaffId: null,
+        status: "confirmed",
+        startTime: new Date("2024-12-01T09:00:00Z"),
+        endTime: new Date("2024-12-01T11:00:00Z"),
+        notes: null,
+        priceCents: 6500,
+        seriesId: null,
+        seriesIndex: null,
+        groupId: null,
+        confirmationStatus: "confirmed",
+        confirmedAt: null,
+        cancelledAt: null,
+        confirmationToken: null,
+        customerNotes: null,
+        createdAt: new Date("2024-11-01"),
+        updatedAt: new Date("2024-11-01"),
+      },
+    ],
+    groomingLogs: [
+      {
+        id: "log-1",
+        petId: PET_ID,
+        appointmentId: "appt-completed-1",
+        staffId: "staff-groomer-id",
+        cutStyle: "puppy cut",
+        productsUsed: "oatmeal shampoo",
+        notes: "Trimmed nails",
+        groomedAt: new Date("2024-06-01T10:00:00Z"),
+        createdAt: new Date("2024-06-01T10:00:00Z"),
+      },
+    ],
+    staffMembers: [
+      {
+        id: "staff-groomer-id",
+        name: "Groomer McGroome",
+        email: "groomer@example.com",
+        role: "groomer",
+        isSuperUser: false,
+        active: true,
+        oidcSub: "oidc-groomer-sub",
+        userId: null,
+        icalToken: null,
+        createdAt: new Date(),
+        updatedAt: new Date(),
+      },
+      {
+        id: "staff-manager-id",
+        name: "Manager McManager",
+        email: "manager@example.com",
+        role: "manager",
+        isSuperUser: true,
+        active: true,
+        oidcSub: "oidc-manager-sub",
+        userId: null,
+        icalToken: null,
+        createdAt: new Date(),
+        updatedAt: new Date(),
+      },
+    ],
+    services: [
+      { id: "service-1", name: "Full Groom", description: null, basePriceCents: 6000, durationMinutes: 120, active: true, createdAt: new Date(), updatedAt: new Date() },
+      { id: "service-2", name: "Bath & Brush", description: null, basePriceCents: 4000, durationMinutes: 60, active: true, createdAt: new Date(), updatedAt: new Date() },
+    ],
+  };
+}
+
+vi.mock("../db/index.js", () => {
+  const pets = new Proxy({ _name: "pets" }, { get: (t, p) => p === "_name" ? "pets" : {} });
+  const appointments = new Proxy({ _name: "appointments" }, { get: (t, p) => p === "_name" ? "appointments" : {} });
+  const groomingVisitLogs = new Proxy({ _name: "groomingVisitLogs" }, { get: (t, p) => p === "_name" ? "groomingVisitLogs" : {} });
+  const staff = new Proxy({ _name: "staff" }, { get: (t, p) => p === "_name" ? "staff" : {} });
+  const services = new Proxy({ _name: "services" }, { get: (t, p) => p === "_name" ? "services" : {} });
+
+  // Tracks { [tableName]: { [alias]: SQLExpression } } for the current select() call
+  let selectedColumns: Record<string, Record<string, unknown>> = {};
+
+  function makeChainable(rows: unknown[]) {
+    const arr = rows as unknown[];
+    return new Proxy(arr, {
+      get(target, prop) {
+        if (prop === "where" || prop === "orderBy" || prop === "limit" || prop === "leftJoin" || prop === "from") {
+          return () => makeChainable(target);
+        }
+        if (prop === Symbol.iterator) {
+          return function* () { for (const v of target) yield v; };
+        }
+        if (prop === Symbol.asyncIterator) {
+          return async function* () { for (const v of target) yield v; };
+        }
+        // @ts-expect-error proxy
+        return target[prop];
+      },
+    });
+  }
+
+  // sql mock: returns an object with .as() so drizzle's select() can alias it
+  function sqlMock(_strings: TemplateStringsArray, ..._params: unknown[]) {
+    const queryString = _strings[0];
+    const asFn = (alias: string) => ({
+      sql: { queryChunks: [_strings[0]] },
+      fieldAlias: alias,
+      getSQL() { return this.sql; },
+    });
+    return { queryChunks: [queryString], as: asFn };
+  }
+
+  return {
+    getDb: () => ({
+      select: (cols?: Record<string, unknown>) => {
+        selectedColumns = {};
+        if (cols) {
+          // Inspect cols to find sql-aliased expressions and their aliases
+          for (const [alias, expr] of Object.entries(cols)) {
+            if (expr && typeof expr === "object" && "as" in expr && typeof (expr as Record<string, unknown>).as === "function") {
+              const aliased = (expr as { as: (a: string) => { fieldAlias: string; sql: unknown } }).as(alias);
+              // Detect count(*) queries
+              if (typeof aliased.sql === "object" && aliased.sql !== null && "queryChunks" in (aliased.sql as Record<string, unknown>) && String((aliased.sql as { queryChunks?: unknown[] }).queryChunks).includes("count")) {
+                // Store count query intent — we'll resolve it in from()
+                if (!selectedColumns["appointments"]) selectedColumns["appointments"] = {};
+                selectedColumns["appointments"][alias] = { _isCountQuery: true };
+              }
+            }
+          }
+        }
+        return {
+          from: (table: unknown) => {
+            const name = (table as { _name?: string })._name;
+            const tableCols = selectedColumns[name] || {};
+            // If this table has a count query, return computed count result
+            const countQueryEntry = Object.entries(tableCols).find(([, v]) =>
+              typeof v === "object" && v !== null && "_isCountQuery" in v
+            );
+            if (countQueryEntry) {
+              const [countAlias] = countQueryEntry;
+              const count = (name === "appointments" ? mock.appointments : [])
+                .filter((row: Record<string, unknown>) => row.status === "completed").length;
+              return makeChainable([{ [countAlias]: count }]);
+            }
+            if (name === "pets") return makeChainable(mock.pets);
+            if (name === "appointments") return makeChainable(mock.appointments);
+            if (name === "groomingVisitLogs") return makeChainable(mock.groomingLogs);
+            if (name === "staff") return makeChainable(mock.staffMembers);
+            if (name === "services") return makeChainable(mock.services);
+            return makeChainable([]);
+          },
+        };
+      },
+      insert: () => ({ values: () => ({ returning: () => [{}] }) }),
+      update: () => ({ set: () => ({ where: () => ({ returning: () => [{}] }) }) }),
+      delete: () => ({ where: () => ({ returning: () => [{}] }) }),
+    }),
+    pets,
+    appointments,
+    groomingVisitLogs,
+    staff,
+    services,
+    and: vi.fn((a: unknown, b: unknown) => [a, b]),
+    desc: vi.fn((c: unknown) => c),
+    eq: vi.fn((_col: unknown, _val: unknown) => ({ col: _col, val: _val })),
+    exists: vi.fn(() => true),
+    gte: vi.fn((a: unknown, b: unknown) => ({ col: a, val: b })),
+    or: vi.fn((a: unknown, b: unknown) => [a, b]),
+    sql: sqlMock,
+  };
+});
+
+// ─── 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);
+}
+
+// ─── Tests ────────────────────────────────────────────────────────────────────
+
+describe("GET /:id/profile-summary", () => {
+  beforeEach(resetMock);
+
+  it("returns 404 for non-existent pet", async () => {
+    const app = makeApp();
+    mock.pets = [];
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(404);
+  });
+
+  it("returns 403 for groomer with no pet linkage", async () => {
+    const app = makeApp(GROOMER);
+    // Groomer has no linkage to this pet's client — clear appointments
+    mock.appointments = [];
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(403);
+  });
+
+  it("returns complete aggregated profile for manager", async () => {
+    const app = makeApp(MANAGER);
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.id).toBe(PET_ID);
+    expect(body.name).toBe("Biscuit");
+    expect(body.species).toBe("dog");
+    expect(body.recentGroomingHistory).toBeInstanceOf(Array);
+    expect(body.lastVisitDate).toBeTruthy();
+    expect(body.visitCount).toBeGreaterThanOrEqual(0);
+  });
+
+  it("groomer with pet linkage returns 200", async () => {
+    const app = makeApp(GROOMER);
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(200);
+  });
+
+  it("recentGroomingHistory is limited to 10 entries", async () => {
+    const app = makeApp(MANAGER);
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.recentGroomingHistory.length).toBeLessThanOrEqual(10);
+  });
+
+  it("returns null upcomingAppointment when none scheduled", async () => {
+    const app = makeApp(MANAGER);
+    mock.appointments = [];
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.upcomingAppointment).toBeNull();
+  });
+});
+
+describe("GET /:id/profile-summary — visitCount", () => {
+  beforeEach(resetMock);
+
+  it("returns visitCount >= 2 when pet has 2+ completed appointments", async () => {
+    const app = makeApp(MANAGER);
+    // Add a second completed appointment
+    mock.appointments = [
+      ...mock.appointments,
+      {
+        id: "appt-completed-2",
+        clientId: CLIENT_ID,
+        petId: PET_ID,
+        serviceId: "service-1",
+        staffId: "staff-groomer-id",
+        batherStaffId: null,
+        status: "completed",
+        startTime: new Date("2024-07-01T09:00:00Z"),
+        endTime: new Date("2024-07-01T11:00:00Z"),
+        notes: null,
+        priceCents: 6000,
+        seriesId: null,
+        seriesIndex: null,
+        groupId: null,
+        confirmationStatus: "confirmed",
+        confirmedAt: null,
+        cancelledAt: null,
+        confirmationToken: null,
+        customerNotes: null,
+        createdAt: new Date("2024-06-15"),
+        updatedAt: new Date("2024-06-15"),
+      },
+    ];
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.visitCount).toBeGreaterThanOrEqual(2);
+  });
+
+  it("returns visitCount = 0 when no completed appointments", async () => {
+    const app = makeApp(MANAGER);
+    mock.appointments = mock.appointments.map((a) => ({ ...a, status: "cancelled" }));
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.visitCount).toBe(0);
+  });
+});
+
+describe("GET /:id/profile-summary — empty history", () => {
+  beforeEach(resetMock);
+
+  it("returns empty history array when no grooming logs", async () => {
+    const app = makeApp(MANAGER);
+    mock.groomingLogs = [];
+    const res = await app.request(`/pets/${PET_ID}/profile-summary`);
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.recentGroomingHistory).toEqual([]);
+    expect(body.lastVisitDate).toBeNull();
+  });
+});

apps/api/src/db/seed.ts

@@ -20,6 +20,7 @@ import postgres from "postgres";
 import { drizzle } from "drizzle-orm/postgres-js";
 import { eq, and, sql } from "drizzle-orm";
 import * as schema from "./schema.js";
+import type { MedicalAlert, MedicalAlertSeverity } from "./schema.js";
 
 // ── Seed profile configuration ─────────────────────────────────────────────
 
@@ -252,6 +253,38 @@ const appointmentNotes = [
   "Client running late, pushed start by 15min",
 ];
 
+const temperamentScores = [3, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 7, 8, 8, 8, 9];
+
+const temperamentFlags = [
+  [], ["anxious"], ["friendly"], ["nippy"], ["anxious", "sensitive"],
+  ["friendly", "calm"], ["nippy", "territorial"], ["calm"], ["sensitive"],
+  ["friendly", "nippy"], ["anxious", "territorial"],
+];
+
+const medicalAlertsList = [
+  [] as MedicalAlert[],
+  [] as MedicalAlert[],
+  [{ type: "skin", description: "Sensitive skin — avoid harsh shampoos", severity: "medium" as MedicalAlertSeverity }],
+  [{ type: "ear", description: "Ear infection prone — dry ears thoroughly", severity: "medium" as MedicalAlertSeverity }],
+  [{ type: "mobility", description: "Hip dysplasia — handle with care", severity: "high" as MedicalAlertSeverity }],
+  [{ type: "behavioral", description: "Anxious — needs slow approach", severity: "low" as MedicalAlertSeverity }],
+  [{ type: "medical", description: "Seizure history — avoid stress triggers", severity: "high" as MedicalAlertSeverity }],
+  [{ type: "skin", description: "Skin allergies — use hypoallergenic products only", severity: "medium" as MedicalAlertSeverity }],
+  [{ type: "behavioral", description: "Aggressive when nails trimmed — muzzle required", severity: "high" as MedicalAlertSeverity }],
+  [{ type: "cardiac", description: "Heart murmur — monitor during grooming", severity: "high" as MedicalAlertSeverity }],
+  [{ type: "dietary", description: "Diabetic — owner brings treats", severity: "medium" as MedicalAlertSeverity }],
+];
+
+const preferredCutsList = [
+  [], ["Puppy Cut"], ["Teddy Bear Cut"], ["Breed Standard"],
+  ["Puppy Cut", "Sanitary Trim"], ["Full Groom"], ["Lion Cut"],
+  ["Kennel Cut", "Face & Feet Trim"], ["Teddy Bear Cut", "Sanitary Trim"],
+  ["Breed Standard", "Sanitary Trim"], ["Summer Shave"],
+  ["Puppy Cut", "Face & Feet Trim", "Sanitary Trim"],
+];
+
+const coatTypes: string[] = ["short", "medium", "long", "curly", "wire", "double", "silky"];
+
 const visitLogNotes = [
   null, null,
   "Coat in great condition",
@@ -872,6 +905,11 @@ async function seed() {
           cutStyle: pick(cutStyles),
           shampooPreference: pick(shampoos),
           specialCareNotes: rand() < 0.1 ? "Vet clearance required before grooming" : null,
+          coatType: pick(coatTypes),
+          temperamentScore: pick(temperamentScores),
+          temperamentFlags: pick(temperamentFlags),
+          medicalAlerts: pick(medicalAlertsList),
+          preferredCuts: pick(preferredCutsList),
           customFields: {},
           image: petIndex < 250 ? pick(puggleImages) : pick(demoPetImages),
         });
@@ -907,6 +945,11 @@ async function seed() {
             cutStyle: pet.cutStyle,
             shampooPreference: pet.shampooPreference,
             specialCareNotes: pet.specialCareNotes,
+            coatType: pet.coatType,
+            temperamentScore: pet.temperamentScore,
+            temperamentFlags: pet.temperamentFlags,
+            medicalAlerts: pet.medicalAlerts,
+            preferredCuts: pet.preferredCuts,
             customFields: pet.customFields,
             image: pet.image,
           },
@@ -929,13 +972,18 @@ async function seed() {
       petId: string;
       petName: string;
       petBreed: string;
+      petCoatType: string;
+      petTemperamentScore: number;
+      petTemperamentFlags: string[];
+      petMedicalAlerts: MedicalAlert[];
+      petPreferredCuts: string[];
     }
     const uatClients: UatClient[] = [
-    { id: uuid(), name: "UAT Test Alpha", email: "uat-alpha@groombook.dev", phone: "(555) 100-0001", address: "100 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestBuddy", petBreed: "Golden Retriever" },
-    { id: uuid(), name: "UAT Test Bravo", email: "uat-bravo@groombook.dev", phone: "(555) 100-0002", address: "200 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestMax", petBreed: "Labrador Retriever" },
-    { id: uuid(), name: "UAT Test Charlie", email: "uat-charlie@groombook.dev", phone: "(555) 100-0003", address: "300 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestCooper", petBreed: "Poodle" },
-    { id: uuid(), name: "UAT Test Delta", email: "uat-delta@groombook.dev", phone: "(555) 100-0004", address: "400 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestRocky", petBreed: "French Bulldog" },
-    { id: uuid(), name: "UAT Test Echo", email: "uat-echo@groombook.dev", phone: "(555) 100-0005", address: "500 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestDuke", petBreed: "Beagle" },
+    { id: uuid(), name: "UAT Test Alpha", email: "uat-alpha@groombook.dev", phone: "(555) 100-0001", address: "100 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestBuddy", petBreed: "Golden Retriever", petCoatType: "double", petTemperamentScore: 7, petTemperamentFlags: ["calm", "friendly"], petMedicalAlerts: [] as MedicalAlert[], petPreferredCuts: ["Breed Standard"] },
+    { id: uuid(), name: "UAT Test Bravo", email: "uat-bravo@groombook.dev", phone: "(555) 100-0002", address: "200 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestMax", petBreed: "Labrador Retriever", petCoatType: "short", petTemperamentScore: 8, petTemperamentFlags: ["friendly"], petMedicalAlerts: [] as MedicalAlert[], petPreferredCuts: ["Bath & Brush", "Sanitary Trim"] },
+    { id: uuid(), name: "UAT Test Charlie", email: "uat-charlie@groombook.dev", phone: "(555) 100-0003", address: "300 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestCooper", petBreed: "Poodle", petCoatType: "curly", petTemperamentScore: 9, petTemperamentFlags: ["calm"], petMedicalAlerts: [{ type: "behavioral", description: "Anxious — needs slow approach", severity: "low" as MedicalAlertSeverity }], petPreferredCuts: ["Teddy Bear Cut"] },
+    { id: uuid(), name: "UAT Test Delta", email: "uat-delta@groombook.dev", phone: "(555) 100-0004", address: "400 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestRocky", petBreed: "French Bulldog", petCoatType: "short", petTemperamentScore: 6, petTemperamentFlags: ["nippy"], petMedicalAlerts: [{ type: "skin", description: "Sensitive skin — avoid harsh shampoos", severity: "medium" as MedicalAlertSeverity }], petPreferredCuts: ["Puppy Cut"] },
+    { id: uuid(), name: "UAT Test Echo", email: "uat-echo@groombook.dev", phone: "(555) 100-0005", address: "500 Test Lane, Springfield, CA 90210", petId: uuid(), petName: "TestDuke", petBreed: "Beagle", petCoatType: "short", petTemperamentScore: 7, petTemperamentFlags: ["friendly", "energetic"], petMedicalAlerts: [] as MedicalAlert[], petPreferredCuts: ["Full Groom", "Nail Trim"] },
   ];
 
   for (const uc of uatClients) {
@@ -943,8 +991,26 @@ async function seed() {
       .values({ id: uc.id, name: uc.name, email: uc.email, phone: uc.phone, address: uc.address })
       .onConflictDoUpdate({ target: schema.clients.id, set: { name: uc.name, email: uc.email, phone: uc.phone, address: uc.address } });
     await db.insert(schema.pets)
-      .values({ id: uc.petId, clientId: uc.id, name: uc.petName, species: "Dog", breed: uc.petBreed, weightKg: "25.00", dateOfBirth: new Date("2021-03-15T00:00:00Z"), image: pick(demoPetImages) })
-      .onConflictDoUpdate({ target: schema.pets.id, set: { clientId: uc.id, name: uc.petName, species: "Dog", breed: uc.petBreed, weightKg: "25.00", dateOfBirth: new Date("2021-03-15T00:00:00Z"), image: pick(demoPetImages) } });
+      .values({
+        id: uc.petId, clientId: uc.id, name: uc.petName, species: "Dog", breed: uc.petBreed,
+        weightKg: "25.00", dateOfBirth: new Date("2021-03-15T00:00:00Z"),
+        coatType: uc.petCoatType,
+        temperamentScore: uc.petTemperamentScore,
+        temperamentFlags: uc.petTemperamentFlags,
+        medicalAlerts: uc.petMedicalAlerts,
+        preferredCuts: uc.petPreferredCuts,
+        image: pick(demoPetImages),
+      })
+      .onConflictDoUpdate({ target: schema.pets.id, set: {
+        clientId: uc.id, name: uc.petName, species: "Dog", breed: uc.petBreed,
+        weightKg: "25.00", dateOfBirth: new Date("2021-03-15T00:00:00Z"),
+        coatType: uc.petCoatType,
+        temperamentScore: uc.petTemperamentScore,
+        temperamentFlags: uc.petTemperamentFlags,
+        medicalAlerts: uc.petMedicalAlerts,
+        preferredCuts: uc.petPreferredCuts,
+        image: pick(demoPetImages),
+      } });
     // Create one completed appointment for this client
     const apptId = uuid();
     const svcIdx = 0;

apps/api/src/routes/pets.ts

@@ -1,7 +1,7 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { and, eq, exists, getDb, or, pets, appointments } from "../db/index.js";
+import { and, desc, eq, exists, getDb, gte, groomingVisitLogs, or, pets, appointments, staff, services, sql } from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 import {
   getPresignedUploadUrl,
@@ -283,3 +283,133 @@ petsRouter.get("/:petId/photo", async (c) => {
   const url = await getPresignedGetUrl(pet.photoKey);
   return c.json({ url, photoKey: pet.photoKey, photoUploadedAt: pet.photoUploadedAt });
 });
+
+// ─── Profile Summary ───────────────────────────────────────────────────────────
+
+async function groomerLinkageCheck(
+  db: ReturnType<typeof getDb>,
+  clientId: string,
+  staffRow: NonNullable<AppEnv["Variables"]["staff"]>
+): Promise<boolean> {
+  const [linkage] = await db
+    .select({ id: appointments.id })
+    .from(appointments)
+    .where(
+      and(
+        eq(appointments.clientId, clientId),
+        or(
+          eq(appointments.staffId, staffRow.id),
+          eq(appointments.batherStaffId, staffRow.id)
+        )
+      )
+    )
+    .limit(1);
+  return !!linkage;
+}
+
+/**
+ * GET /:id/profile-summary
+ * Returns aggregated profile: basic pet fields + grooming history + visit stats + upcoming appointment.
+ * Groomer RBAC: same visibility rules as GET /:id.
+ */
+petsRouter.get("/:id/profile-summary", async (c) => {
+  const db = getDb();
+  const petId = c.req.param("id");
+  const staffRow = c.get("staff");
+  const isGroomer = staffRow?.role === "groomer";
+
+  const [row] = await db.select().from(pets).where(eq(pets.id, petId));
+  if (!row) return c.json({ error: "Not found" }, 404);
+
+  if (isGroomer) {
+    const hasLinkage = await groomerLinkageCheck(db, row.clientId, staffRow);
+    if (!hasLinkage) return c.json({ error: "Forbidden" }, 403);
+  }
+
+  // Recent grooming history: last 10, with staff name join
+  const historyRows = await db
+    .select({
+      id: groomingVisitLogs.id,
+      petId: groomingVisitLogs.petId,
+      appointmentId: groomingVisitLogs.appointmentId,
+      staffId: groomingVisitLogs.staffId,
+      staffName: staff.name,
+      cutStyle: groomingVisitLogs.cutStyle,
+      productsUsed: groomingVisitLogs.productsUsed,
+      notes: groomingVisitLogs.notes,
+      groomedAt: groomingVisitLogs.groomedAt,
+      createdAt: groomingVisitLogs.createdAt,
+    })
+    .from(groomingVisitLogs)
+    .leftJoin(staff, eq(staff.id, groomingVisitLogs.staffId))
+    .where(eq(groomingVisitLogs.petId, petId))
+    .orderBy(desc(groomingVisitLogs.groomedAt))
+    .limit(10);
+
+  const recentGroomingHistory = historyRows.map((r) => ({
+    id: r.id,
+    petId: r.petId,
+    appointmentId: r.appointmentId,
+    staffId: r.staffId,
+    staffName: r.staffName,
+    cutStyle: r.cutStyle,
+    productsUsed: r.productsUsed,
+    notes: r.notes,
+    groomedAt: r.groomedAt?.toISOString() ?? null,
+    createdAt: r.createdAt?.toISOString() ?? null,
+  }));
+
+  const lastVisitDate = historyRows[0]?.groomedAt?.toISOString() ?? null;
+
+  // Completed appointment count for this pet
+  const [{ count: visitCount }] = await db
+    .select({ count: sql<number>`count(*)::int` })
+    .from(appointments)
+    .where(and(eq(appointments.petId, petId), eq(appointments.status, "completed")));
+
+  // Upcoming appointment: next scheduled or confirmed
+  const [nextAppt] = await db
+    .select({
+      id: appointments.id,
+      serviceId: appointments.serviceId,
+      staffId: appointments.staffId,
+      startTime: appointments.startTime,
+      endTime: appointments.endTime,
+      status: appointments.status,
+      serviceName: services.name,
+      staffName: staff.name,
+    })
+    .from(appointments)
+    .leftJoin(services, eq(services.id, appointments.serviceId))
+    .leftJoin(staff, eq(staff.id, appointments.staffId))
+    .where(
+      and(
+        eq(appointments.petId, petId),
+        or(eq(appointments.status, "scheduled"), eq(appointments.status, "confirmed")),
+        gte(appointments.startTime, new Date())
+      )
+    )
+    .orderBy(appointments.startTime)
+    .limit(1);
+
+  const upcomingAppointment = nextAppt
+    ? {
+        id: nextAppt.id,
+        serviceId: nextAppt.serviceId,
+        serviceName: nextAppt.serviceName,
+        staffId: nextAppt.staffId,
+        staffName: nextAppt.staffName,
+        startTime: nextAppt.startTime?.toISOString() ?? null,
+        endTime: nextAppt.endTime?.toISOString() ?? null,
+        status: nextAppt.status,
+      }
+    : null;
+
+  return c.json({
+    ...row,
+    recentGroomingHistory,
+    lastVisitDate,
+    visitCount,
+    upcomingAppointment,
+  });
+});

packages/db/migrations/0034_extend_pet_profile_columns.sql

@@ -0,0 +1,8 @@
+-- Migration: 0034_extend_pet_profile_columns.sql
+-- GRO-1850: Adds temperament_score, temperament_flags, medical_alerts,
+-- and preferred_cuts columns to the pets table.
+
+ALTER TABLE "pets" ADD COLUMN "temperament_score" integer;
+ALTER TABLE "pets" ADD COLUMN "temperament_flags" jsonb DEFAULT '[]';
+ALTER TABLE "pets" ADD COLUMN "medical_alerts" jsonb DEFAULT '[]';
+ALTER TABLE "pets" ADD COLUMN "preferred_cuts" jsonb DEFAULT '[]';

packages/db/migrations/meta/0034_snapshot.json

@@ -0,0 +1,210 @@
+{
+  "id": "0034_extend_pet_profile_columns",
+  "prevId": "b3a381ca-f7a4-450f-aa7e-fdc2d652dc97",
+  "version": "7",
+  "dialect": "postgresql",
+  "tables": {
+    "public.pets": {
+      "name": "pets",
+      "schema": "",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "uuid",
+          "primaryKey": true,
+          "notNull": true,
+          "default": "gen_random_uuid()"
+        },
+        "client_id": {
+          "name": "client_id",
+          "type": "uuid",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "species": {
+          "name": "species",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "breed": {
+          "name": "breed",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "weight_kg": {
+          "name": "weight_kg",
+          "type": "numeric(5, 2)",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "date_of_birth": {
+          "name": "date_of_birth",
+          "type": "timestamp",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "health_alerts": {
+          "name": "health_alerts",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "grooming_notes": {
+          "name": "grooming_notes",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "cut_style": {
+          "name": "cut_style",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "shampoo_preference": {
+          "name": "shampoo_preference",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "special_care_notes": {
+          "name": "special_care_notes",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "coat_type": {
+          "name": "coat_type",
+          "type": "coat_type",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "pet_size_category": {
+          "name": "pet_size_category",
+          "type": "pet_size_category",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "temperament_score": {
+          "name": "temperament_score",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "temperament_flags": {
+          "name": "temperament_flags",
+          "type": "jsonb",
+          "primaryKey": false,
+          "notNull": false,
+          "default": "'[]'::jsonb"
+        },
+        "medical_alerts": {
+          "name": "medical_alerts",
+          "type": "jsonb",
+          "primaryKey": false,
+          "notNull": false,
+          "default": "'[]'::jsonb"
+        },
+        "preferred_cuts": {
+          "name": "preferred_cuts",
+          "type": "jsonb",
+          "primaryKey": false,
+          "notNull": false,
+          "default": "'[]'::jsonb"
+        },
+        "custom_fields": {
+          "name": "custom_fields",
+          "type": "jsonb",
+          "primaryKey": false,
+          "notNull": true,
+          "default": "'{}'::jsonb"
+        },
+        "photo_key": {
+          "name": "photo_key",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "photo_uploaded_at": {
+          "name": "photo_uploaded_at",
+          "type": "timestamp",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "image": {
+          "name": "image",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "timestamp",
+          "primaryKey": false,
+          "notNull": true,
+          "default": "now()"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "timestamp",
+          "primaryKey": false,
+          "notNull": true,
+          "default": "now()"
+        }
+      },
+      "indexes": {},
+      "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",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    }
+  },
+  "enums": {
+    "coat_type": {
+      "name": "coat_type",
+      "values": [
+        "short",
+        "medium",
+        "long",
+        "wire",
+        "double",
+        "hairless",
+        "curly"
+      ]
+    },
+    "pet_size_category": {
+      "name": "pet_size_category",
+      "values": [
+        "small",
+        "medium",
+        "large",
+        "extra_large"
+      ]
+    }
+  },
+  "nativeEnums": {}
+}

packages/db/migrations/meta/_journal.json

@@ -239,6 +239,13 @@
       "when": 1779500000000,
       "tag": "0033_add_services_default_buffer_minutes",
       "breakpoints": true
+    },
+    {
+      "idx": 34,
+      "version": "7",
+      "when": 1751140800000,
+      "tag": "0034_extend_pet_profile_columns",
+      "breakpoints": true
     }
   ]
 }

packages/db/src/seed.ts

@@ -20,6 +20,7 @@ import postgres from "postgres";
 import { drizzle } from "drizzle-orm/postgres-js";
 import { eq, and, sql } from "drizzle-orm";
 import * as schema from "./schema.js";
+import type { MedicalAlert } from "@groombook/types";
 
 // ── Seed profile configuration ─────────────────────────────────────────────
 
@@ -243,6 +244,55 @@ const groomingNotes = [
   "Previous clipper burn — be gentle on belly",
 ];
 
+// ── Extended pet profile pools ─────────────────────────────────────────────────
+
+const temperamentFlagPool: string[] = [
+  "friendly",
+  "anxious-with-strangers",
+  "good-with-kids",
+  "leash-reactive",
+  "vocal",
+  "high-energy",
+  "calm-on-table",
+  "treat-motivated",
+];
+
+const medicalAlertPool: MedicalAlert[] = [
+  { id: "", type: "allergies", description: "Seasonal allergies — monitor skin", severity: "low" },
+  { id: "", type: "allergies", description: "Chicken allergy — avoid poultry-based treats", severity: "high" },
+  { id: "", type: "joint", description: "Hip dysplasia — handle with care", severity: "medium" },
+  { id: "", type: "joint", description: "Arthritis — anti-inflammatory medication on file", severity: "medium" },
+  { id: "", type: "dental", description: "Dental disease — extractions in history", severity: "medium" },
+  { id: "", type: "dental", description: "Baby teeth retained — vet monitor", severity: "low" },
+  { id: "", type: "heart", description: "Heart murmur grade II — avoid stress", severity: "high" },
+  { id: "", type: "heart", description: "Murmur cleared by vet last year", severity: "low" },
+  { id: "", type: "other", description: "Eye ulcer history — be careful around face", severity: "medium" },
+  { id: "", type: "other", description: "Seizure history — avoid flashing lights", severity: "high" },
+  { id: "", type: "other", description: "Luxating patella — short walks only", severity: "medium" },
+  { id: "", type: "other", description: "Ear infections — dry thoroughly after bath", severity: "low" },
+];
+
+const preferredCutPool: string[] = [
+  "Puppy Cut",
+  "Teddy Bear Cut",
+  "Lion Cut",
+  "Breed Standard",
+  "Summer Shave",
+  "Kennel Cut",
+  "Lamb Cut",
+  "Continental Clip",
+  "Sporting Clip",
+  "Sanitary Trim",
+  "Face & Feet Trim",
+  "Full Groom",
+];
+
+type CoatType = (typeof schema.coatTypeEnum.enumValues)[number];
+type PetSizeCategory = (typeof schema.petSizeCategoryEnum.enumValues)[number];
+
+const coatTypePool: CoatType[] = ["short", "medium", "long", "double", "wire", "silky", "curly", "hairless"];
+const petSizeCategoryPool: PetSizeCategory[] = ["small", "medium", "large", "extra_large"];
+
 const appointmentNotes = [
   null, null, null, null,
   "Client requested extra brushing",
@@ -853,6 +903,18 @@ async function seed() {
           specialCareNotes: rand() < 0.1 ? "Vet clearance required before grooming" : null,
           customFields: {},
           image: petIndex < 250 ? pick(puggleImages) : pick(demoPetImages),
+          temperamentScore: randInt(1, 5),
+          temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
+          medicalAlerts: (() => {
+            if (rand() < 0.3) {
+              const count = rand() < 0.7 ? 1 : 2;
+              return pickN(medicalAlertPool, count).map((a) => ({ ...a, id: uuid() }));
+            }
+            return [];
+          })(),
+          preferredCuts: pickN(preferredCutPool, randInt(1, 2)),
+          coatType: pick(coatTypePool),
+          petSizeCategory: pick(petSizeCategoryPool),
         });
 
         petRecords.push({ id: petId, clientId });
@@ -888,6 +950,12 @@ async function seed() {
             specialCareNotes: pet.specialCareNotes,
             customFields: pet.customFields,
             image: pet.image,
+            temperamentScore: pet.temperamentScore,
+            temperamentFlags: pet.temperamentFlags,
+            medicalAlerts: pet.medicalAlerts,
+            preferredCuts: pet.preferredCuts,
+            coatType: pet.coatType,
+            petSizeCategory: pet.petSizeCategory,
           },
         });
     }
@@ -922,8 +990,52 @@ async function seed() {
       .values({ id: uc.id, name: uc.name, email: uc.email, phone: uc.phone, address: uc.address })
       .onConflictDoUpdate({ target: schema.clients.id, set: { name: uc.name, email: uc.email, phone: uc.phone, address: uc.address } });
     await db.insert(schema.pets)
-      .values({ id: uc.petId, clientId: uc.id, name: uc.petName, species: "Dog", breed: uc.petBreed, weightKg: "25.00", dateOfBirth: new Date("2021-03-15T00:00:00Z"), image: pick(demoPetImages) })
-      .onConflictDoUpdate({ target: schema.pets.id, set: { clientId: uc.id, name: uc.petName, species: "Dog", breed: uc.petBreed, weightKg: "25.00", dateOfBirth: new Date("2021-03-15T00:00:00Z"), image: pick(demoPetImages) } });
+      .values({
+        id: uc.petId,
+        clientId: uc.id,
+        name: uc.petName,
+        species: "Dog",
+        breed: uc.petBreed,
+        weightKg: "25.00",
+        dateOfBirth: new Date("2021-03-15T00:00:00Z"),
+        image: pick(demoPetImages),
+        temperamentScore: randInt(1, 5),
+        temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
+        medicalAlerts: (() => {
+          if (rand() < 0.3) {
+            const count = rand() < 0.7 ? 1 : 2;
+            return pickN(medicalAlertPool, count).map((a) => ({ ...a, id: uuid() }));
+          }
+          return [];
+        })(),
+        preferredCuts: pickN(preferredCutPool, randInt(1, 2)),
+        coatType: pick(coatTypePool),
+        petSizeCategory: pick(petSizeCategoryPool),
+      })
+      .onConflictDoUpdate({
+        target: schema.pets.id,
+        set: {
+          clientId: uc.id,
+          name: uc.petName,
+          species: "Dog",
+          breed: uc.petBreed,
+          weightKg: "25.00",
+          dateOfBirth: new Date("2021-03-15T00:00:00Z"),
+          image: pick(demoPetImages),
+          temperamentScore: randInt(1, 5),
+          temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
+          medicalAlerts: (() => {
+            if (rand() < 0.3) {
+              const count = rand() < 0.7 ? 1 : 2;
+              return pickN(medicalAlertPool, count).map((a) => ({ ...a, id: uuid() }));
+            }
+            return [];
+          })(),
+          preferredCuts: pickN(preferredCutPool, randInt(1, 2)),
+          coatType: pick(coatTypePool),
+          petSizeCategory: pick(petSizeCategoryPool),
+        },
+      });
     // Create one completed appointment for this client
     const apptId = uuid();
     const svcIdx = 0;

packages/types/src/index.ts

@@ -225,3 +225,34 @@ export interface MedicalAlert {
 }
 
 export type CoatType = "smooth" | "double" | "curly" | "wire" | "long" | "hairless";
+
+export interface GroomingHistoryEntry {
+  id: string;
+  petId: string;
+  appointmentId: string | null;
+  staffId: string | null;
+  staffName: string | null;
+  cutStyle: string | null;
+  productsUsed: string | null;
+  notes: string | null;
+  groomedAt: string;
+  createdAt: string;
+}
+
+export interface UpcomingAppointment {
+  id: string;
+  serviceId: string;
+  serviceName: string;
+  staffId: string | null;
+  staffName: string | null;
+  startTime: string;
+  endTime: string;
+  status: AppointmentStatus;
+}
+
+export interface PetProfileSummary extends Pet {
+  recentGroomingHistory: GroomingHistoryEntry[];
+  lastVisitDate: string | null;
+  visitCount: number;
+  upcomingAppointment: UpcomingAppointment | null;
+}

pnpm-lock.yaml

@@ -970,66 +970,79 @@ packages:
     resolution: {integrity: sha512-DV6fJoxEYWJOvaZIsok7KrYl0tPvga5OZ2yvKHNNYyk/2roMLqQAbGhr78EQ5YhHpnhLKJD3S1WFusAkmUuV5g==}
     cpu: [arm]
     os: [linux]
+    libc: [glibc]
 
   '@rollup/rollup-linux-arm-musleabihf@4.60.3':
     resolution: {integrity: sha512-mQKoJAzvuOs6F+TZybQO4GOTSMUu7v0WdxEk24krQ/uUxXoPTtHjuaUuPmFhtBcM4K0ons8nrE3JyhTuCFtT/w==}
     cpu: [arm]
     os: [linux]
+    libc: [musl]
 
   '@rollup/rollup-linux-arm64-gnu@4.60.3':
     resolution: {integrity: sha512-Whjj2qoiJ6+OOJMGptTYazaJvjOJm+iKHpXQM1P3LzGjt7Ff++Tp7nH4N8J/BUA7R9IHfDyx4DJIflifwnbmIA==}
     cpu: [arm64]
     os: [linux]
+    libc: [glibc]
 
   '@rollup/rollup-linux-arm64-musl@4.60.3':
     resolution: {integrity: sha512-4YTNHKqGng5+yiZt3mg77nmyuCfmNfX4fPmyUapBcIk+BdwSwmCWGXOUxhXbBEkFHtoN5boLj/5NON+u5QC9tg==}
     cpu: [arm64]
     os: [linux]
+    libc: [musl]
 
   '@rollup/rollup-linux-loong64-gnu@4.60.3':
     resolution: {integrity: sha512-SU3kNlhkpI4UqlUc2VXPGK9o886ZsSeGfMAX2ba2b8DKmMXq4AL7KUrkSWVbb7koVqx41Yczx6dx5PNargIrEA==}
     cpu: [loong64]
     os: [linux]
+    libc: [glibc]
 
   '@rollup/rollup-linux-loong64-musl@4.60.3':
     resolution: {integrity: sha512-6lDLl5h4TXpB1mTf2rQWnAk/LcXrx9vBfu/DT5TIPhvMhRWaZ5MxkIc8u4lJAmBo6klTe1ywXIUHFjylW505sg==}
     cpu: [loong64]
     os: [linux]
+    libc: [musl]
 
   '@rollup/rollup-linux-ppc64-gnu@4.60.3':
     resolution: {integrity: sha512-BMo8bOw8evlup/8G+cj5xWtPyp93xPdyoSN16Zy90Q2QZ0ZYRhCt6ZJSwbrRzG9HApFabjwj2p25TUPDWrhzqQ==}
     cpu: [ppc64]
     os: [linux]
+    libc: [glibc]
 
   '@rollup/rollup-linux-ppc64-musl@4.60.3':
     resolution: {integrity: sha512-E0L8X1dZN1/Rph+5VPF6Xj2G7JJvMACVXtamTJIDrVI44Y3K+G8gQaMEAavbqCGTa16InptiVrX6eM6pmJ+7qA==}
     cpu: [ppc64]
     os: [linux]
+    libc: [musl]
 
   '@rollup/rollup-linux-riscv64-gnu@4.60.3':
     resolution: {integrity: sha512-oZJ/WHaVfHUiRAtmTAeo3DcevNsVvH8mbvodjZy7D5QKvCefO371SiKRpxoDcCxB3PTRTLayWBkvmDQKTcX/sw==}
     cpu: [riscv64]
     os: [linux]
+    libc: [glibc]
 
   '@rollup/rollup-linux-riscv64-musl@4.60.3':
     resolution: {integrity: sha512-Dhbyh7j9FybM3YaTgaHmVALwA8AkUwTPccyCQ79TG9AJUsMQqgN1DDEZNr4+QUfwiWvLDumW5vdwzoeUF+TNxQ==}
     cpu: [riscv64]
     os: [linux]
+    libc: [musl]
 
   '@rollup/rollup-linux-s390x-gnu@4.60.3':
     resolution: {integrity: sha512-cJd1X5XhHHlltkaypz1UcWLA8AcoIi1aWhsvaWDskD1oz2eKCypnqvTQ8ykMNI0RSmm7NkTdSqSSD7zM0xa6Ig==}
     cpu: [s390x]
     os: [linux]
+    libc: [glibc]
 
   '@rollup/rollup-linux-x64-gnu@4.60.3':
     resolution: {integrity: sha512-DAZDBHQfG2oQuhY7mc6I3/qB4LU2fQCjRvxbDwd/Jdvb9fypP4IJ4qmtu6lNjes6B531AI8cg1aKC2di97bUxA==}
     cpu: [x64]
     os: [linux]
+    libc: [glibc]
 
   '@rollup/rollup-linux-x64-musl@4.60.3':
     resolution: {integrity: sha512-cRxsE8c13mZOh3vP+wLDxpQBRrOHDIGOWyDL93Sy0Ga8y515fBcC2pjUfFwUe5T7tqvTvWbCpg1URM/AXdWIXA==}
     cpu: [x64]
     os: [linux]
+    libc: [musl]
 
   '@rollup/rollup-openbsd-x64@4.60.3':
     resolution: {integrity: sha512-QaWcIgRxqEdQdhJqW4DJctsH6HCmo5vHxY0krHSX4jMtOqfzC+dqDGuHM87bu4H8JBeibWx7jFz+h6/4C8wA5Q==}

src/__tests__/portalSessionFromAuth.test.ts

@@ -0,0 +1,175 @@
+import { describe, it, expect, vi, beforeEach } from "vitest";
+import { Hono } from "hono";
+import { getAuth } from "../lib/auth.js";
+
+const CLIENT_ID = "550e8400-e29b-41d4-a716-446655440001";
+const CLIENT_EMAIL = "alice@example.com";
+const CLIENT_NAME = "Alice Smith";
+
+const BETTER_AUTH_SESSION = {
+  user: {
+    id: "auth-user-001",
+    email: CLIENT_EMAIL,
+    name: CLIENT_NAME,
+  },
+  session: {
+    id: "ba-session-001",
+    expiresAt: new Date(Date.now() + 60 * 60 * 1000),
+  },
+};
+
+const MOCK_CLIENT = {
+  id: CLIENT_ID,
+  email: CLIENT_EMAIL,
+  name: CLIENT_NAME,
+};
+
+let mockGetAuth: ReturnType<typeof vi.fn>;
+let mockGetSession: ReturnType<typeof vi.fn>;
+let insertedSession: Record<string, unknown> | null = null;
+let mockClientRow: Record<string, unknown> | null = null;
+let mockStaffRow: Record<string, unknown> | 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 impersonationSessions = new Proxy(
+    { _name: "impersonationSessions" },
+    { get: (t, p) => (p === "_name" ? "impersonationSessions" : { table: "impersonationSessions", column: p }) }
+  );
+
+  const clients = new Proxy(
+    { _name: "clients" },
+    { get: (t, p) => (p === "_name" ? "clients" : { table: "clients", column: p }) }
+  );
+
+  const staff = new Proxy(
+    { _name: "staff" },
+    { get: (t, p) => (p === "_name" ? "staff" : { table: "staff", column: p }) }
+  );
+
+  return {
+    getDb: () => ({
+      select: () => ({
+        from: (table: { _name: string }) => {
+          if (table._name === "clients") {
+            return makeChainable(mockClientRow ? [mockClientRow] : []);
+          }
+          if (table._name === "staff") {
+            return makeChainable(mockStaffRow ? [mockStaffRow] : []);
+          }
+          return makeChainable([]);
+        },
+      }),
+      insert: (table: { _name: string }) => ({
+        values: (vals: Record<string, unknown>) => ({
+          returning: () => {
+            if (table._name === "impersonationSessions") {
+              insertedSession = { id: "new-session-001", ...vals };
+              return [insertedSession];
+            }
+            return [];
+          },
+        }),
+      }),
+    }),
+    impersonationSessions,
+    clients,
+    staff,
+    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/session-from-auth", () => {
+  beforeEach(() => {
+    insertedSession = null;
+    mockClientRow = null;
+    mockStaffRow = null;
+    mockGetSession = vi.fn();
+    mockGetAuth = vi.fn(() => ({
+      api: {
+        getSession: mockGetSession,
+      },
+    }));
+    vi.mocked(getAuth).mockImplementation(mockGetAuth);
+  });
+
+  it("returns 401 when no Better Auth session", async () => {
+    mockGetSession.mockResolvedValue(null);
+    const res = await app.request("/portal/session-from-auth", {
+      method: "POST",
+    });
+    expect(res.status).toBe(401);
+    const body = await res.json();
+    expect(body.error).toBe("Unauthorized");
+  });
+
+  it("returns 404 when authenticated user has no client record", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    mockClientRow = null;
+    const res = await app.request("/portal/session-from-auth", {
+      method: "POST",
+    });
+    expect(res.status).toBe(404);
+    const body = await res.json();
+    expect(body.error).toBe("No client record found for this user");
+  });
+
+  it("returns a portal session with sessionId, clientId, clientName when client is found", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    mockClientRow = MOCK_CLIENT;
+    mockStaffRow = { id: "00000000-0000-0000-0000-000000000001" };
+    const res = await app.request("/portal/session-from-auth", {
+      method: "POST",
+    });
+    expect(res.status).toBe(201);
+    const body = await res.json();
+    expect(body).toHaveProperty("sessionId");
+    expect(body).toHaveProperty("clientId", CLIENT_ID);
+    expect(body).toHaveProperty("clientName", CLIENT_NAME);
+  });
+
+  it("creates a portal session with reason sso-bridge", async () => {
+    mockGetSession.mockResolvedValue(BETTER_AUTH_SESSION);
+    mockClientRow = MOCK_CLIENT;
+    mockStaffRow = { id: "00000000-0000-0000-0000-000000000001" };
+    const res = await app.request("/portal/session-from-auth", {
+      method: "POST",
+    });
+    expect(res.status).toBe(201);
+    expect(insertedSession).not.toBeNull();
+    expect((insertedSession as Record<string, unknown>).reason).toBe("sso-bridge");
+  });
+
+  it("returns 503 when auth is not configured", async () => {
+    mockGetAuth.mockImplementation(() => {
+      throw new Error("Auth not initialized");
+    });
+    const res = await app.request("/portal/session-from-auth", {
+      method: "POST",
+    });
+    expect(res.status).toBe(503);
+  });
+});

src/lib/auth.ts

@@ -172,7 +172,7 @@ export async function initAuth(): Promise<void> {
         clientSecret: oidcClientSecret,
         issuerUrl: oidcIssuer,
         internalBaseUrl: process.env.OIDC_INTERNAL_BASE,
-        scopes: "openid profile email",
+        scopes: "openid profile email role",
       };
       console.log("[auth] Using env var config (no DB config found)");
     }

src/middleware/rbac.ts

@@ -22,7 +22,7 @@ export const resolveStaffMiddleware: MiddlewareHandler<AppEnv> = async (
   c,
   next
 ) => {
-  // Better-Auth's own routes handle their own auth — skip staff resolution
+  // Better-Auth\'s own routes handle their own auth — skip staff resolution
   // OOBE setup routes also handle their own auth — staff record is created during setup
   if (c.req.path.startsWith("/api/auth/") || c.req.path.startsWith("/api/setup")) {
     await next();
@@ -120,28 +120,31 @@ export const resolveStaffMiddleware: MiddlewareHandler<AppEnv> = async (
       .where(
         and(
           eq(account.userId, jwt.sub),
-          sql`${account.providerId} IN ('authentik', 'google', 'github')`
+          sql`${account.providerId} IN (\'authentik\', \'google\', \'github\')`
         )
       )
       .limit(1);
 
     if (oidcAccount) {
       // Derive name: prefer jwt.name, fall back to email prefix, then "Unknown"
-      const name =
-        jwt.name?.trim() ||
-        (jwt.email ? jwt.email.split("@")[0] : "Unknown");
+      const emailPrefix = jwt.email ? jwt.email.split("@")[0] : "Unknown";
+      const name = jwt.name?.trim() || emailPrefix;
 
       const [newStaff] = await db
         .insert(staff)
         .values({
           userId: jwt.sub,
-          email: jwt.email ?? "",
+          email: (jwt.email ?? "") as string,
           name,
           role: "groomer",
           isSuperUser: false,
           active: true,
-        })
-        .returning();
+        } as Parameters<typeof db.insert>[0] extends { values: infer V } ? V : never)
+        .returning()!;
+
+      if (!newStaff) {
+        return c.json({ error: "Forbidden: auto-provision failed" }, 500);
+      }
 
       console.log(
         `[rbac] auto-provisioned staff record for OIDC user: ${jwt.sub} -> staff:${newStaff.id} (${name})`
@@ -177,7 +180,7 @@ export function requireRole(
     if (!(allowedRoles as string[]).includes(staffRow.role)) {
       return c.json(
         {
-          error: `Forbidden: role '${staffRow.role}' is not permitted to access this resource`,
+          error: `Forbidden: role \'${staffRow.role}\' is not permitted to access this resource`,
         },
         403
       );
@@ -210,7 +213,7 @@ export function requireRoleOrSuperUser(
       {
         error: hasAllowedRole
           ? "Forbidden: super user privileges required"
-          : `Forbidden: role '${staffRow.role}' is not permitted`,
+          : `Forbidden: role \'${staffRow.role}\' is not permitted`,
       },
       403
     );

src/routes/portal.ts

@@ -36,7 +36,7 @@ portalRouter.post(
       return c.json({ error: "Client not found" }, 404);
     }
 
-    const DEMO_STAFF_ID = "00000000-0000-0000-0000-000000000001";
+    const DEMO_STAFF_ID = process.env.DEMO_STAFF_ID ?? "00000000-0000-0000-0000-000000000001";
 
     let staffId = DEMO_STAFF_ID;
     const [demoStaff] = await db
@@ -71,6 +71,82 @@ portalRouter.post(
   }
 );
 
+// Bridge Better Auth session → portal session for real SSO customers (GRO-1866).
+// Registered BEFORE the /* middleware so it is NOT subject to validatePortalSession.
+import { getAuth } from "../lib/auth.js";
+
+portalRouter.post("/session-from-auth", 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 db = getDb();
+  const [client] = await db
+    .select()
+    .from(clients)
+    .where(eq(clients.email, session.user.email))
+    .limit(1);
+
+  if (!client) {
+    return c.json({ error: "No client record found for this user" }, 404);
+  }
+
+  const DEMO_STAFF_ID = process.env.DEMO_STAFF_ID ?? "00000000-0000-0000-0000-000000000001";
+
+  let staffId = DEMO_STAFF_ID;
+  const [demoStaff] = await db
+    .select({ id: staff.id })
+    .from(staff)
+    .where(eq(staff.id, DEMO_STAFF_ID))
+    .limit(1);
+
+  if (!demoStaff) {
+    const [firstStaff] = await db
+      .select({ id: staff.id })
+      .from(staff)
+      .where(eq(staff.active, true))
+      .limit(1);
+    if (!firstStaff) {
+      return c.json({ error: "No staff records found" }, 500);
+    }
+    staffId = firstStaff.id;
+  }
+
+  const [portalSession] = await db
+    .insert(impersonationSessions)
+    .values({
+      staffId,
+      clientId: client.id,
+      reason: "sso-bridge",
+      expiresAt: new Date(Date.now() + 24 * 60 * 60 * 1000),
+    })
+    .returning();
+
+  if (!portalSession) {
+    return c.json({ error: "Failed to create session" }, 500);
+  }
+
+  return c.json(
+    {
+      sessionId: portalSession.id,
+      clientId: client.id,
+      clientName: client.name,
+    },
+    201
+  );
+});
+
 // Apply middleware to all portal routes
 portalRouter.use("/*", validatePortalSession, portalAudit);