fix(GRO-1576): add provenance: false to all build-push-action steps

Docker Buildx v6 defaults to OCI attestation manifests (--attest
type=provenance,mode=max). These hit a Gitea registry bug when image
layers are pre-existing (blob mount), causing "unknown" errors on manifest
list push. API image succeeds because it pushes new layers; migrate/seed/
reset fail because their layers already exist.

Disabling provenance attestation on all four build-push-action steps
resolves the push failures. Addresses GRO-1575.

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

.gitea/workflows/ci.yml

@@ -0,0 +1,143 @@
+name: CI
+
+on:
+  push:
+    branches: [main, dev]
+  pull_request:
+    branches: [main, dev]
+  workflow_dispatch:
+    inputs:
+      ref:
+        description: "Branch or ref to run CI against"
+        required: false
+        default: "main"
+
+jobs:
+  lint-typecheck:
+    name: Lint & Typecheck
+    runs-on: ubuntu-latest
+    steps:
+      - uses: actions/checkout@v4
+
+      - uses: pnpm/action-setup@v4
+        with:
+          version: '9.15.4'
+
+      - uses: actions/setup-node@v4
+        with:
+          node-version: 22
+          cache: pnpm
+
+      - name: Install dependencies
+        run: pnpm install --frozen-lockfile
+
+      - name: Typecheck
+        run: pnpm --filter @groombook/api typecheck
+
+      - name: Lint
+        run: pnpm --filter @groombook/api lint
+
+  test:
+    name: Test
+    runs-on: ubuntu-latest
+    steps:
+      - uses: actions/checkout@v4
+
+      - uses: pnpm/action-setup@v4
+        with:
+          version: '9.15.4'
+
+      - uses: actions/setup-node@v4
+        with:
+          node-version: 22
+          cache: pnpm
+
+      - name: Install dependencies
+        run: pnpm install --frozen-lockfile
+
+      - name: Run tests
+        run: pnpm --filter @groombook/api test
+
+  docker:
+    name: Build & Push Docker Images
+    runs-on: ubuntu-latest
+    needs: [lint-typecheck, test]
+    steps:
+      - uses: actions/checkout@v4
+
+      - name: Generate image tag
+        id: version
+        run: |
+          if [ "${{ github.event_name }}" = "pull_request" ]; then
+            TAG="pr-${{ github.event.pull_request.number }}-${GITHUB_SHA::7}"
+          else
+            TAG="$(date -u +%Y.%m.%d)-${GITHUB_SHA::7}"
+          fi
+          echo "tag=$TAG" >> "$GITHUB_OUTPUT"
+          echo "Image tag: $TAG"
+
+      - name: Set up Docker Buildx
+        uses: docker/setup-buildx-action@v3
+
+      - name: Log in to Gitea Container Registry
+        uses: docker/login-action@v3
+        with:
+          registry: git.farh.net
+          username: ${{ gitea.actor }}
+          password: ${{ secrets.REGISTRY_TOKEN }}
+
+      - name: Build and push API image
+        uses: docker/build-push-action@v6
+        with:
+          context: .
+          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' || '' }}
+          cache-from: type=registry,ref=git.farh.net/groombook/cache:api
+          cache-to: type=registry,ref=git.farh.net/groombook/cache:api,mode=max
+
+      - name: Build and push Migrate image
+        uses: docker/build-push-action@v6
+        with:
+          context: .
+          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' || '' }}
+          cache-from: type=registry,ref=git.farh.net/groombook/cache:migrate
+          cache-to: type=registry,ref=git.farh.net/groombook/cache:migrate,mode=max
+
+      - name: Build and push Seed image
+        uses: docker/build-push-action@v6
+        with:
+          context: .
+          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' || '' }}
+          cache-from: type=registry,ref=git.farh.net/groombook/cache:seed
+          cache-to: type=registry,ref=git.farh.net/groombook/cache:seed,mode=max
+
+      - name: Build and push Reset image
+        uses: docker/build-push-action@v6
+        with:
+          context: .
+          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' || '' }}
+          cache-from: type=registry,ref=git.farh.net/groombook/cache:reset
+          cache-to: type=registry,ref=git.farh.net/groombook/cache:reset,mode=max

.github/workflows/ci.yml

@@ -25,7 +25,7 @@ jobs:
 
       - uses: actions/setup-node@v4
         with:
-          node-version: 20
+          node-version: 22
           cache: pnpm
 
       - name: Install dependencies
@@ -49,7 +49,7 @@ jobs:
 
       - uses: actions/setup-node@v4
         with:
-          node-version: 20
+          node-version: 22
           cache: pnpm
 
       - name: Install dependencies
@@ -71,7 +71,7 @@ jobs:
 
       - uses: actions/setup-node@v4
         with:
-          node-version: 20
+          node-version: 22
           cache: pnpm
 
       - name: Install dependencies
@@ -202,20 +202,20 @@ jobs:
           echo "Updating dev overlay image tags to: $TAG"
           echo "Updating migration/seed Job names with SHA: $SHORT_SHA"
           cd /tmp/infra
-          DEV_KUST="apps/groombook/overlays/dev/kustomization.yaml"
+          DEV_KUST="apps/overlays/dev/kustomization.yaml"
           yq -i '(.images[] | select(.name == "ghcr.io/groombook/api")).newTag = env(TAG)' "$DEV_KUST"
           yq -i '(.images[] | select(.name == "ghcr.io/groombook/migrate")).newTag = env(TAG)' "$DEV_KUST"
           yq -i '(.images[] | select(.name == "ghcr.io/groombook/seed")).newTag = env(TAG)' "$DEV_KUST"
           yq -i '(.images[] | select(.name == "ghcr.io/groombook/reset")).newTag = env(TAG)' "$DEV_KUST"
 
-          MIGRATE_JOB="apps/groombook/base/migrate-job.yaml"
+          MIGRATE_JOB="apps/base/migrate-job.yaml"
           if [ -f "$MIGRATE_JOB" ]; then
             yq -i '.metadata.name = "migrate-schema-" + env(SHORT_SHA)' "$MIGRATE_JOB"
             yq -i '.metadata.annotations."groombook.app/deploy-version" = env(TAG)' "$MIGRATE_JOB"
             yq -i '.spec.ttlSecondsAfterFinished = (.spec.ttlSecondsAfterFinished // 86400)' "$MIGRATE_JOB"
           fi
 
-          SEED_JOB="apps/groombook/base/seed-job.yaml"
+          SEED_JOB="apps/base/seed-job.yaml"
           if [ -f "$SEED_JOB" ]; then
             yq -i '.metadata.name = "seed-test-data-" + env(SHORT_SHA)' "$SEED_JOB"
             yq -i '.metadata.annotations."groombook.app/deploy-version" = env(TAG)' "$SEED_JOB"
@@ -237,7 +237,7 @@ jobs:
           git config user.name "groombook-engineer[bot]"
           git config user.email "3141748+groombook-engineer[bot]@users.noreply.github.com"
           git checkout -b "chore/update-image-tags-${TAG}"
-          git add apps/groombook/overlays/dev/ apps/groombook/base/migrate-job.yaml apps/groombook/base/seed-job.yaml
+          git add apps/overlays/dev/ apps/base/migrate-job.yaml apps/base/seed-job.yaml
           git commit -m "chore: update image tags and migration/seed Job names to ${TAG}"
 
           git push -u origin "chore/update-image-tags-${TAG}"

.gitignore

@@ -1,10 +1,10 @@
 node_modules/
 dist/
+.DS_Store
+*.log
 .env
 .env.local
 *.local
-.DS_Store
-*.log
 .turbo/
 coverage/
 minimax-output/

Dockerfile

@@ -1,38 +1,53 @@
-FROM node:20-alpine AS base
+FROM node:22-alpine AS base
 RUN corepack enable && corepack prepare pnpm@9.15.4 --activate
 WORKDIR /app
 
+# Install deps
 FROM base AS deps
-COPY package.json pnpm-lock.yaml ./
-COPY apps/api/package.json apps/api/
+COPY package.json pnpm-workspace.yaml pnpm-lock.yaml ./
+COPY packages/db/package.json packages/db/
+COPY packages/types/package.json packages/types/
 RUN pnpm install --frozen-lockfile
 
+# Build
 FROM deps AS builder
 RUN mkdir -p /home/node/.cache/node/corepack
-COPY apps/api/ apps/api/
-RUN pnpm --filter @groombook/api build
+COPY packages/ packages/
+COPY src/ src/
+COPY tsconfig.json ./
+RUN pnpm --filter @groombook/types build && \
+    pnpm --filter @groombook/db build && \
+    pnpm build
 
-FROM node:20-alpine AS runner
+# Runtime
+FROM node:22-alpine AS runner
 RUN corepack enable && corepack prepare pnpm@9.15.4 --activate
 WORKDIR /app
 ENV NODE_ENV=production
 
-COPY package.json pnpm-lock.yaml ./
-COPY --from=builder /app/apps/api/package.json apps/api/
-COPY --from=builder /app/apps/api/dist apps/api/dist
+COPY package.json pnpm-workspace.yaml pnpm-lock.yaml ./
+COPY --from=builder /app/package.json ./
+COPY --from=builder /app/dist dist/
+COPY --from=builder /app/packages/db/package.json packages/db/
+COPY --from=builder /app/packages/db/dist packages/db/dist
+COPY --from=builder /app/packages/types/package.json packages/types/
+COPY --from=builder /app/packages/types/dist packages/types/dist
 RUN pnpm install --frozen-lockfile --prod
 
 EXPOSE 3000
 RUN apk add --no-cache curl
 HEALTHCHECK --interval=30s --timeout=5s --start-period=10s --retries=3 \
   CMD curl -f http://localhost:3000/health || exit 1
-CMD ["node", "apps/api/dist/index.js"]
+CMD ["node", "dist/index.js"]
 
+# Migrate stage — runs drizzle-kit migrate against the database
 FROM builder AS migrate
-CMD ["pnpm", "--filter", "@groombook/api", "db:migrate"]
+CMD ["pnpm", "--filter", "@groombook/db", "migrate"]
 
+# Seed stage — populates the database with test data
 FROM builder AS seed
-CMD ["pnpm", "--filter", "@groombook/api", "db:seed"]
+CMD ["pnpm", "--filter", "@groombook/db", "seed"]
 
+# Reset stage — drops all tables, re-runs migrations, and re-seeds
 FROM builder AS reset
-CMD ["pnpm", "--filter", "@groombook/api", "db:reset"]
+CMD ["pnpm", "--filter", "@groombook/db", "reset"]

README.md

@@ -13,7 +13,7 @@ This repository contains the GroomBook API service, including:
 ## Structure
 
 ```
-apps/api/        # API service source
+src/             # API service source
 packages/db/     # Database schema, migrations, and utilities
 packages/types/  # Shared TypeScript types
 ```

UAT_PLAYBOOK.md

@@ -0,0 +1,231 @@
+# UAT Playbook — GroomBook API
+
+## Overview
+
+GroomBook API is a Hono-based REST service (TypeScript/Node.js) powering the pet grooming management platform. Handles authentication, client/pet management, appointment scheduling, invoicing, payments, staff management, and the customer portal.
+
+## Environments
+
+| Environment | URL |
+|------------|-----|
+| Dev        | `dev.groombook.dev` |
+| UAT        | `uat.groombook.dev` |
+| Prod       | `demo.groombook.app` |
+
+## Pre-conditions
+
+- UAT environment accessible and healthy
+- Test accounts seeded (manager, staff, client personas)
+- OIDC authentication provider configured
+- Seed data present (clients, pets, services, staff)
+
+## Test Cases
+
+### 4.1 Authentication
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-1.1 | Login via OIDC | POST to OIDC provider callback, verify JWT token issued | 200 OK, JWT returned with valid claims |
+| TC-API-1.4 | Email+password login (UAT) | POST /api/auth/sign-in/email with uat-super@groombook.dev + SEED_UAT_SUPER_PASSWORD | 200 OK, session cookie returned |
+| TC-API-1.5 | Email+password login — groomer | POST /api/auth/sign-in/email with uat-groomer@groombook.dev + SEED_UAT_GROOMER_PASSWORD | 200 OK, session cookie returned |
+| TC-API-1.6 | Email+password login — customer | POST /api/auth/sign-in/email with uat-customer@groombook.dev + SEED_UAT_CUSTOMER_PASSWORD | 200 OK, session cookie returned |
+| TC-API-1.7 | Email+password login — tester | POST /api/auth/sign-in/email with uat-tester@groombook.dev + SEED_UAT_TESTER_PASSWORD | 200 OK, session cookie returned |
+| TC-API-1.8 | Email+password — invalid password | POST /api/auth/sign-in/email with wrong password | 400 Bad Request, error returned |
+| TC-API-1.9 | Email+password — unknown user | POST /api/auth/sign-in/email with non-existent email | 400 Bad Request, error returned |
+| TC-API-1.10 | Auto-provision on first OIDC login | First login as a Better-Auth user with no existing staff record | 200 OK, access granted; groomer staff record auto-created with name/email from user table |
+| TC-API-1.11 | Existing staff unaffected by OIDC login | Login as uat-groomer@groombook.dev (email+password), then GET /api/staff to find that record | 200 OK, staff record unchanged — no duplicate created, original role and isSuperUser preserved |
+| TC-API-1.12 | Auto-provisioned role and superUser flags | After TC-API-1.10, GET /api/staff and inspect the auto-created record | role = "groomer", isSuperUser = false, active = true |
+| TC-API-1.13 | Name fallback — user.name present | Auto-provision where Better-Auth user has name set | Staff name = user.name value from user table |
+| TC-API-1.14 | Name fallback — no name, email present | Auto-provision where Better-Auth user has name = null, email = "test@example.com" | Staff name = "test" (email prefix before @) |
+| TC-API-1.15 | Name fallback — no name, no email | Auto-provision where Better-Auth user has name = null, email = null | Staff name = "Unknown" |
+| TC-API-1.16 | OIDC login — Terraform-provisioned user | Initiate OIDC login as any UAT persona (uat-super, uat-groomer, uat-customer, uat-tester), complete authentik callback | 200 OK, session created — no account_not_linked error |
+
+### 4.2 Client Management
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-2.1 | List clients | GET /api/clients | 200 OK, list of active clients returned |
+| TC-API-2.2 | Get client details | GET /api/clients/{id} | 200 OK, client details returned |
+| TC-API-2.3 | Create client | POST /api/clients with valid data | 201 Created, client record created |
+| TC-API-2.4 | Update client | PATCH /api/clients/{id} with updated fields | 200 OK, client updated |
+| TC-API-2.5 | Disable client | PATCH /api/clients/{id} with status: "disabled" | 200 OK, client marked as disabled |
+| TC-API-2.6 | Delete client | DELETE /api/clients/{id}?confirm=true | 200 OK, client deleted (if no appointments) |
+
+### 4.3 Pet Management
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-3.1 | List pets | GET /api/pets | 200 OK, list of pets returned |
+| TC-API-3.2 | Get pet details | GET /api/pets/{id} | 200 OK, pet details including history returned |
+| TC-API-3.3 | Add pet | POST /api/pets with valid pet data | 201 Created, pet record created |
+| TC-API-3.4 | Update pet | PATCH /api/pets/{id} with updated fields | 200 OK, pet updated |
+| TC-API-3.5 | Delete pet | DELETE /api/pets/{id} | 200 OK, pet deleted |
+| TC-API-3.6 | Upload pet photo | POST /api/pets/{id}/photo/upload-url, then confirm | 200 OK, photo uploaded and key stored |
+| TC-API-3.7 | View pet photo | GET /api/pets/{id}/photo | 200 OK, presigned URL returned |
+| TC-API-3.8 | Create pet with extended fields | POST /api/pets with coatType, temperamentScore, temperamentFlags, medicalAlerts, preferredCuts | 201 Created, all extended fields stored and returned |
+| TC-API-3.9 | Update pet extended fields | PATCH /api/pets/{id} with coatType, temperamentScore, medicalAlerts | 200 OK, extended fields updated |
+| TC-API-3.10 | Reject invalid coatType | POST /api/pets with coatType: "smooth" | 400 Bad Request, invalid coatType rejected |
+| TC-API-3.11 | Reject out-of-range temperamentScore | POST /api/pets with temperamentScore: 0 or 6 | 400 Bad Request, score out of range rejected |
+| TC-API-3.12 | Reject invalid medicalAlert severity | POST /api/pets with medicalAlerts severity: "critical" | 400 Bad Request, invalid severity rejected |
+| 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 |
+
+### 4.4 Appointment Scheduling
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-4.1 | List appointments | GET /api/appointments | 200 OK, list of appointments returned |
+| TC-API-4.2 | Get appointment details | GET /api/appointments/{id} | 200 OK, appointment details returned |
+| TC-API-4.3 | Create single appointment | POST /api/appointments with valid data | 201 Created, appointment created |
+| TC-API-4.4 | Create recurring appointment | POST /api/appointments with recurrence object | 201 Created, series of appointments created |
+| TC-API-4.5 | Update appointment | PATCH /api/appointments/{id} with updated fields | 200 OK, appointment updated |
+| TC-API-4.6 | Reschedule with cascade | PATCH /api/appointments/{id} with cascadeMode: "this_and_future" | 200 OK, future appointments updated |
+| TC-API-4.7 | Cancel appointment | DELETE /api/appointments/{id} | 200 OK, appointment marked as cancelled |
+| TC-API-4.8 | Confirm appointment | POST /api/appointments/{id}/confirm | 200 OK, confirmation status set to confirmed |
+| TC-API-4.9 | Cancel confirmation | POST /api/appointments/{id}/cancel | 200 OK, confirmation cancelled |
+| TC-API-4.10 | Conflict detection | POST /api/appointments with conflicting time | 409 Conflict, error message returned |
+
+### 4.5 Services
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-5.1 | List services | GET /api/services | 200 OK, list of active services returned |
+| TC-API-5.2 | Get service details | GET /api/services/{id} | 200 OK, service details returned |
+| TC-API-5.3 | Create service | POST /api/services with valid data | 201 Created, service created |
+| TC-API-5.4 | Update service | PATCH /api/services/{id} with updated fields | 200 OK, service updated |
+| TC-API-5.5 | Delete service | DELETE /api/services/{id} | 200 OK, service deleted |
+
+### 4.6 Staff Management
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-6.1 | List staff | GET /api/staff | 200 OK, list of active staff returned |
+| TC-API-6.2 | Get staff details | GET /api/staff/{id} | 200 OK, staff details returned |
+| TC-API-6.3 | Create staff | POST /api/staff with valid data | 201 Created, staff created |
+| TC-API-6.4 | Update staff | PATCH /api/staff/{id} with updated fields | 200 OK, staff updated |
+| TC-API-6.5 | Delete staff | DELETE /api/staff/{id} | 200 OK, staff deleted (if no appointments) |
+| TC-API-6.6 | RBAC check | Access manager-only endpoint as groomer | 403 Forbidden, error message returned |
+
+### 4.7 Invoicing & Payments
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-7.1 | List invoices | GET /api/invoices | 200 OK, list of invoices returned |
+| TC-API-7.2 | Get invoice details | GET /api/invoices/{id} | 200 OK, invoice with line items returned |
+| TC-API-7.3 | Create invoice | POST /api/invoices with line items | 201 Created, invoice created |
+| TC-API-7.4 | Create from appointment | POST /api/invoices/from-appointment/{appointmentId} | 201 Created, invoice created from appointment |
+| TC-API-7.5 | Update invoice | PATCH /api/invoices/{id} with status and payment method | 200 OK, invoice updated |
+| TC-API-7.6 | Process payment via Stripe | POST /api/invoices/{id}/pay with Stripe data | 200 OK, payment intent created |
+| TC-API-7.7 | Save tip splits | POST /api/invoices/{id}/tip-splits with splits array | 201 Created, tip splits saved |
+| TC-API-7.8 | Process refund | POST /api/invoices/{id}/refund with amount | 200 OK, refund processed |
+
+### 4.8 Customer Portal
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-8.1 | Access portal | GET /api/portal/me with valid session token | 200 OK, client profile returned |
+| TC-API-8.2 | View portal appointments | GET /api/portal/appointments | 200 OK, list of client's appointments returned |
+| TC-API-8.3 | Confirm appointment via portal | POST /api/portal/appointments/{id}/confirm | 200 OK, appointment confirmed |
+| TC-API-8.4 | Cancel appointment via portal | POST /api/portal/appointments/{id}/cancel | 200 OK, appointment cancelled |
+| 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 |
+
+### 4.9 Waitlist
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-9.1 | List waitlist | GET /api/waitlist | 200 OK, list of waitlist entries returned |
+| TC-API-9.2 | Add to waitlist | POST /api/waitlist with client, pet, service | 201 Created, entry added |
+| TC-API-9.3 | Promote from waitlist | Create appointment from waitlist entry | 201 Created, appointment created, waitlist updated |
+
+### 4.10 Search
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-10.1 | Global search clients | GET /api/search?q={client_name} | 200 OK, matching clients returned |
+| TC-API-10.2 | Global search pets | GET /api/search?q={pet_name} | 200 OK, matching pets with owners returned |
+| TC-API-10.3 | Search by email | GET /api/search?q={email} | 200 OK, matching client returned |
+| TC-API-10.4 | Search by phone | GET /api/search?q={phone} | 200 OK, matching client returned |
+
+### 4.11 Reports
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-11.1 | Revenue summary | GET /api/reports/summary?from={date}&to={date} | 200 OK, revenue KPIs returned |
+| TC-API-11.2 | Revenue by period | GET /api/reports/revenue?groupBy=day | 200 OK, daily revenue breakdown returned |
+| TC-API-11.3 | Appointment analytics | GET /api/reports/appointments | 200 OK, appointment stats returned |
+| TC-API-11.4 | Service popularity | GET /api/reports/services | 200 OK, service usage stats returned |
+| TC-API-11.5 | Client retention | GET /api/reports/clients | 200 OK, new/returning/churn client data returned |
+| TC-API-11.6 | Tip splits report | GET /api/reports/tip-splits | 200 OK, tip earnings per staff returned |
+| TC-API-11.7 | Export revenue CSV | GET /api/reports/export.csv?type=revenue | 200 OK, CSV file downloaded |
+
+### 4.12 Impersonation
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-12.1 | Start impersonation session | POST /api/impersonation/sessions with clientId | 201 Created, session token returned |
+| TC-API-12.2 | Get session details | GET /api/impersonation/sessions/{id} | 200 OK, session details returned |
+| TC-API-12.3 | Extend session | POST /api/impersonation/sessions/{id}/extend | 200 OK, session expiry extended |
+| TC-API-12.4 | End session | POST /api/impersonation/sessions/{id}/end | 200 OK, session marked as ended |
+| TC-API-12.5 | Log audit entry | POST /api/impersonation/sessions/{id}/log | 201 Created, audit log entry created |
+| TC-API-12.6 | View audit log | GET /api/impersonation/sessions/{id}/audit-log | 200 OK, audit trail returned |
+
+### 4.13 Settings & Setup
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-13.1 | Get business settings | GET /api/admin/settings | 200 OK, business settings returned |
+| TC-API-13.2 | Update business settings | PATCH /api/admin/settings with updated values | 200 OK, settings updated |
+| TC-API-13.3 | Upload logo | POST /api/admin/settings/logo/upload with file | 200 OK, logo uploaded and stored |
+| TC-API-13.4 | View logo | GET /api/admin/settings/logo | 200 OK, logo image returned |
+| TC-API-13.5 | Delete logo | DELETE /api/admin/settings/logo | 200 OK, logo removed |
+| TC-API-13.6 | Check setup status | GET /api/setup/status | 200 OK, setup needs returned |
+| TC-API-13.7 | Complete setup | POST /api/setup with business name | 201 Created, super user created |
+| TC-API-13.8 | Configure auth provider | POST /api/setup/auth-provider with OIDC config | 201 Created, auth provider configured |
+| TC-API-13.9 | Test auth provider | POST /api/setup/auth-provider/test with issuer URL | 200 OK, OIDC discovery successful |
+
+### 4.14 Appointment Groups
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-14.1 | List appointment groups | GET /api/appointment-groups | 200 OK, list of groups returned |
+| TC-API-14.2 | Get group details | GET /api/appointment-groups/{id} | 200 OK, group with appointments returned |
+| TC-API-14.3 | Create group booking | POST /api/appointment-groups with client and pets | 201 Created, group and appointments created |
+| TC-API-14.4 | Update group notes | PATCH /api/appointment-groups/{id} with notes | 200 OK, notes updated |
+| TC-API-14.5 | Cancel group | DELETE /api/appointment-groups/{id} | 200 OK, all appointments cancelled |
+
+### 4.15 Buffer Rules
+
+| # | Scenario | Steps | Expected |
+|---|----------|-------|----------|
+| TC-API-15.1 | List buffer rules | GET /api/admin/buffer-rules | 200 OK, list of active buffer rules returned |
+| TC-API-15.2 | Create buffer rule | POST /api/admin/buffer-rules with service, species, sizeCategory, bufferMinutes | 201 Created, buffer rule created |
+| TC-API-15.3 | Update buffer rule | PATCH /api/admin/buffer-rules/{id} with updated bufferMinutes | 200 OK, buffer rule updated |
+| TC-API-15.4 | Delete buffer rule | DELETE /api/admin/buffer-rules/{id} | 200 OK, buffer rule removed |
+| TC-API-15.5 | Reject invalid bufferMinutes | POST /api/admin/buffer-rules with bufferMinutes: -5 | 400 Bad Request, invalid bufferMinutes rejected |
+| TC-API-15.6 | Reject missing required fields | POST /api/admin/buffer-rules with service only | 400 Bad Request, species and sizeCategory required |
+| TC-API-15.7 | Booking uses buffer | Book appointment for pet with sizeCategory; verify duration reflects buffer | 201 Created, appointment duration includes buffer time |
+
+## Pass/Fail Criteria
+
+**Pass:**
+- All test cases execute without errors
+- Expected results match actual results
+- No regressions in previously working features
+- API responses have correct status codes and data structures
+- Authentication and authorization enforced correctly
+- Business rules (conflicts, validations) work as expected
+
+**Fail:**
+- Any unexpected result or error
+- API returns incorrect status codes
+- Data integrity issues
+- Authentication/authorization bypass
+- Business rules not enforced
+- Severity documented with steps to reproduce and screenshot
+
+## Update Policy
+
+Any PR that changes user-facing behaviour MUST update this file. Test cases must be added, modified, or removed to reflect the new behaviour. The PR description must reference which playbook section was updated (e.g., "Updated UAT_PLAYBOOK.md §4.4 — new appointment rescheduling flow").

apps/api/drizzle.config.ts

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

apps/api/migrations/0030_extended_pet_profile.sql

@@ -0,0 +1,12 @@
+-- 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

@@ -0,0 +1,48 @@
+{
+  "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,6 +204,20 @@
       "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

@@ -0,0 +1,416 @@
+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";
+import { and, eq, exists, or } from "../db/index.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 = "a0000000-0000-4000-8000-000000000001";
+const PET_ID = "b0000000-0000-4000-8000-000000000002";
+
+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", async (importOriginal) => {
+  const db = await importOriginal<typeof import("../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" : {} });
+  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: vi.fn(),
+    eq: vi.fn(),
+    exists: vi.fn(),
+    or: vi.fn(),
+  };
+});
+
+// ─── 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: "double" }),
+    });
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.coatType).toBe("double");
+  });
+
+  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, {
@@ -67,6 +67,11 @@ vi.mock("./db", () => {
     { get: (t, p) => (p === "_name" ? "impersonationSessions" : { table: "impersonationSessions", column: p }) }
   );
 
+  const impersonationAuditLogs = new Proxy(
+    { _name: "impersonationAuditLogs" },
+    { get: (t, p) => (p === "_name" ? "impersonationAuditLogs" : { table: "impersonationAuditLogs", column: p }) }
+  );
+
   const appointments = new Proxy(
     { _name: "appointments" },
     { get: (t, p) => (p === "_name" ? "appointments" : { table: "appointments", column: p }) }
@@ -99,8 +104,12 @@ vi.mock("./db", () => {
           }),
         }),
       }),
+      insert: () => ({
+        values: () => ({ returning: () => [{}] }),
+      }),
     }),
     impersonationSessions,
+    impersonationAuditLogs,
     appointments,
     eq: vi.fn(),
     and: vi.fn(),

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

@@ -45,40 +45,76 @@ const GROOMER: StaffRow = {
 
 let staffLookupResult: StaffRow | null = null;
 let managerFallbackResult: StaffRow | null = MANAGER;
+let userLookupResult: { id: string; name: string | null; email: string | null } | null = null;
+let _insertedStaff: StaffRow | null = null;
 
-vi.mock("./db", () => {
-  const staff = new Proxy(
-    { _name: "staff" },
-    {
-      get(target, prop) {
-        if (prop === "_name") return "staff";
-        if (prop === "$inferSelect") return {};
-        return { table: "staff", column: prop };
-      },
-    }
-  );
+vi.mock("../db", () => {
+  const makeTableProxy = (name: string) =>
+    new Proxy(
+      { _name: name },
+      {
+        get(target, prop) {
+          if (prop === "_name") return name;
+          if (prop === "$inferSelect") return {};
+          return { table: name, column: prop };
+        },
+      }
+    );
+
+  const staff = makeTableProxy("staff");
+  const user = makeTableProxy("user");
+
+  const buildQuery = (result: unknown, fallback: unknown) => ({
+    [Symbol.iterator]: function* () {
+      if (result) yield result;
+    },
+    limit: (_n: number) => {
+      const item = result ?? fallback;
+      return {
+        [Symbol.iterator]: function* () { if (item) yield item; },
+        0: item,
+        length: item ? 1 : 0,
+      };
+    },
+  });
 
   return {
     getDb: () => ({
       select: () => ({
-        from: () => ({
-          where: () => ({
-            limit: () => {
-              // dev mode fallback to first manager
-              return managerFallbackResult ? [managerFallbackResult] : [];
-            },
-            [Symbol.iterator]: function* () {
-              if (staffLookupResult) yield staffLookupResult;
-            },
-            0: staffLookupResult,
-            length: staffLookupResult ? 1 : 0,
-          }),
+        from: (table: unknown) => ({
+          where: () => buildQuery(
+            table === staff ? staffLookupResult : userLookupResult,
+            table === staff ? managerFallbackResult : null
+          ),
+        }),
+      }),
+      insert: (_table: unknown) => ({
+        values: (vals: Record<string, unknown>) => ({
+          returning: () => {
+            const newStaff: StaffRow = {
+              id: "new-staff-id",
+              oidcSub: null,
+              userId: vals.userId as string,
+              role: vals.role as StaffRow["role"],
+              isSuperUser: false,
+              name: vals.name as string,
+              email: vals.email as string,
+              active: true,
+              icalToken: null,
+              createdAt: new Date(),
+              updatedAt: new Date(),
+            };
+            _insertedStaff = newStaff;
+            return [newStaff];
+          },
         }),
       }),
     }),
     staff,
+    user,
     eq: vi.fn((_col: unknown, _val: unknown) => ({ col: _col, val: _val })),
     and: vi.fn((..._clauses: unknown[]) => ({})),
+    sql: vi.fn((..._args: unknown[]) => ({})),
   };
 });
 
@@ -87,6 +123,8 @@ vi.mock("./db", () => {
 function resetMocks() {
   staffLookupResult = null;
   managerFallbackResult = MANAGER;
+  userLookupResult = null;
+  _insertedStaff = null;
 }
 
 /** Build a minimal Hono app with jwtPayload pre-set, then apply a middleware. */
@@ -96,7 +134,10 @@ function buildApp(
 ) {
   const app = new Hono<AppEnv>();
   app.use("*", async (c, next) => {
-    c.set("jwtPayload", { sub: staffLookupResult?.userId ?? "unknown-sub" });
+    c.set("jwtPayload", {
+      sub: userLookupResult?.id ?? staffLookupResult?.userId ?? "unknown-sub",
+      email: userLookupResult?.email,
+    });
     await next();
   });
   app.use("*", middleware);
@@ -202,6 +243,50 @@ describe("resolveStaffMiddleware", () => {
     const body = await res.json();
     expect(body.error).toMatch(/no staff records found/i);
   });
+
+  it("auto-provision: creates groomer staff record on first login when Better-Auth user exists", async () => {
+    staffLookupResult = null;
+    userLookupResult = { id: "ba-user-new", name: "New User", email: "newuser@example.com" };
+    let capturedStaff: StaffRow | null = null;
+    const app = buildApp(resolveStaffMiddleware, (c) => {
+      capturedStaff = c.get("staff");
+      return c.json({ ok: true });
+    });
+
+    const res = await app.request("/test");
+    expect(res.status).toBe(200);
+    expect(capturedStaff).not.toBeNull();
+    expect(capturedStaff!.role).toBe("groomer");
+    expect(capturedStaff!.userId).toBe("ba-user-new");
+    expect(capturedStaff!.name).toBe("New User");
+    expect(capturedStaff!.email).toBe("newuser@example.com");
+    expect(capturedStaff!.isSuperUser).toBe(false);
+  });
+
+  it("auto-provision: falls back to email prefix when user has no name", async () => {
+    staffLookupResult = null;
+    userLookupResult = { id: "ba-user-noname", name: null, email: "firstlogin@example.com" };
+    let capturedStaff: StaffRow | null = null;
+    const app = buildApp(resolveStaffMiddleware, (c) => {
+      capturedStaff = c.get("staff");
+      return c.json({ ok: true });
+    });
+
+    const res = await app.request("/test");
+    expect(res.status).toBe(200);
+    expect(capturedStaff!.name).toBe("firstlogin");
+  });
+
+  it("auto-provision: returns 403 when no staff record and no Better-Auth user exists", async () => {
+    staffLookupResult = null;
+    userLookupResult = null;
+    const app = buildApp(resolveStaffMiddleware);
+
+    const res = await app.request("/test");
+    expect(res.status).toBe(403);
+    const body = await res.json();
+    expect(body.error).toMatch(/no staff record found for authenticated user/i);
+  });
 });
 
 // ─── requireRole tests ────────────────────────────────────────────────────────

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__/seed-uat-credentials.test.ts

@@ -0,0 +1,431 @@
+import { describe, it, expect, vi, beforeEach, afterEach } from "vitest";
+
+// ─── Test configuration constants (must match seed.ts) ─────────────────────────
+
+const UAT_ACCOUNTS = [
+  {
+    email: "uat-super@groombook.dev",
+    name: "UAT Super User",
+    passwordEnv: "SEED_UAT_SUPER_PASSWORD",
+    staffEmail: "uat-super@groombook.dev",
+  },
+  {
+    email: "uat-groomer@groombook.dev",
+    name: "UAT Staff Groomer",
+    passwordEnv: "SEED_UAT_GROOMER_PASSWORD",
+    staffEmail: "uat-groomer@groombook.dev",
+  },
+  {
+    email: "uat-customer@groombook.dev",
+    name: "UAT Customer",
+    passwordEnv: "SEED_UAT_CUSTOMER_PASSWORD",
+    staffEmail: null,
+  },
+  {
+    email: "uat-tester@groombook.dev",
+    name: "UAT Tester",
+    passwordEnv: "SEED_UAT_TESTER_PASSWORD",
+    staffEmail: "uat-tester@groombook.dev",
+  },
+];
+
+const TEST_PASSWORD = "test-password-123";
+
+// ─── Password hashing — must match better-auth/crypto (N=16384, r=16, p=1, dkLen=64, hex) ───
+
+async function hashPassword(password: string): Promise<string> {
+  const { hashPassword } = await import("better-auth/crypto");
+  return hashPassword(password);
+}
+
+// ─── Mock DB state ─────────────────────────────────────────────────────────────
+
+interface UserRow {
+  id: string;
+  email: string;
+  name: string;
+  emailVerified: boolean;
+}
+
+interface AccountRow {
+  id: string;
+  accountId: string;
+  providerId: string;
+  userId: string;
+  password: string | null;
+}
+
+interface StaffRow {
+  id: string;
+  email: string;
+  userId: string | null;
+  name: string;
+}
+
+let dbUsers: UserRow[] = [];
+let dbAccounts: AccountRow[] = [];
+let dbStaff: StaffRow[] = [];
+let insertedUsers: UserRow[] = [];
+let insertedAccounts: AccountRow[] = [];
+let updatedStaff: Array<{ id: string; userId: string }> = [];
+
+const originalEnv = { ...process.env };
+
+function resetMock() {
+  dbUsers = [];
+  dbAccounts = [];
+  dbStaff = [];
+  insertedUsers = [];
+  insertedAccounts = [];
+  updatedStaff = [];
+  process.env = { ...originalEnv };
+}
+
+// ─── Mock schema ───────────────────────────────────────────────────────────────
+
+function makeSchemaMock() {
+  const user = new Proxy({ _name: "user" }, {
+    get(_t, p) {
+      if (p === "_name") return "user";
+      if (p === "$inferSelect") return {};
+      return { table: "user", column: p };
+    },
+  });
+
+  const account = new Proxy({ _name: "account" }, {
+    get(_t, p) {
+      if (p === "_name") return "account";
+      if (p === "$inferSelect") return {};
+      return { table: "account", column: p };
+    },
+  });
+
+  const staff = new Proxy({ _name: "staff" }, {
+    get(_t, p) {
+      if (p === "_name") return "staff";
+      if (p === "$inferSelect") return {};
+      return { table: "staff", column: p };
+    },
+  });
+
+  return { user, account, staff };
+}
+
+const { user: mockUser, account: mockAccount, staff: mockStaff } = makeSchemaMock();
+
+function eq(col: unknown, val: unknown) {
+  return { __type: "eq" as const, col, val };
+}
+
+function and(...conds: unknown[]) {
+  return { __type: "and" as const, conds };
+}
+
+// ─── Seed logic helper ─────────────────────────────────────────────────────────
+// Inline the credential provisioning logic under test so we can call it directly.
+// This is the same logic as seed.ts lines 514-598.
+
+interface SeedAccount {
+  email: string;
+  name: string;
+  passwordEnv: string;
+  staffEmail: string | null;
+}
+
+let uuidCounter = 0;
+function mockUuid(): string {
+  return `mock-uuid-${++uuidCounter}`;
+}
+
+async function seedUatCredentials(
+  accounts: SeedAccount[],
+  opts: {
+    users?: UserRow[];
+    accounts?: AccountRow[];
+    staff?: StaffRow[];
+  }
+) {
+  const { users = dbUsers, accounts: accts = dbAccounts, staff: staffRows = dbStaff } = opts;
+
+  for (const acct of accounts) {
+    const password = process.env[acct.passwordEnv];
+    if (!password) {
+      console.warn(`⚠ Skipping ${acct.email} — ${acct.passwordEnv} not set`);
+      continue;
+    }
+
+    // 1. Find or create the Better-Auth user
+    const existingUser = users.find((u) => u.email === acct.email);
+
+    let userId: string;
+    if (existingUser) {
+      userId = existingUser.id;
+    } else {
+      userId = mockUuid();
+      const newUser: UserRow = { id: userId, name: acct.name, email: acct.email, emailVerified: true };
+      insertedUsers.push(newUser);
+      dbUsers.push(newUser);
+    }
+
+    // 2. Check if credential account already exists
+    const existingAccount = accts.find(
+      (a) => a.userId === userId && a.providerId === "credential"
+    );
+
+    if (existingAccount) {
+      // skip — already has credential account
+    } else {
+      // Use Better-Auth's hashPassword so test helper matches production seed.ts
+      const { hashPassword } = await import("better-auth/crypto");
+      const passwordHash = await hashPassword(password);
+
+      const newAccount: AccountRow = {
+        id: mockUuid(),
+        accountId: userId,
+        providerId: "credential",
+        userId,
+        password: passwordHash,
+      };
+      insertedAccounts.push(newAccount);
+      dbAccounts.push(newAccount);
+    }
+
+    // 3. Link staff record to Better-Auth user
+    if (acct.staffEmail) {
+      const existingStaff = staffRows.find((s) => s.email === acct.staffEmail);
+      if (existingStaff && !existingStaff.userId) {
+        existingStaff.userId = userId;
+        updatedStaff.push({ id: existingStaff.id, userId });
+      }
+    }
+  }
+}
+
+// ─── Tests ─────────────────────────────────────────────────────────────────────
+
+describe("seedUatCredentials — credential provisioning logic", () => {
+  beforeEach(() => {
+    resetMock();
+    uuidCounter = 0;
+  });
+
+  afterEach(() => {
+    process.env = { ...originalEnv };
+  });
+
+  // ── AC-1: creates user + account when neither exists ──────────────────────
+
+  it("AC-1: creates user and account for each UAT account with password env var set", async () => {
+    process.env.SEED_UAT_SUPER_PASSWORD = TEST_PASSWORD;
+    process.env.SEED_UAT_GROOMER_PASSWORD = TEST_PASSWORD;
+    process.env.SEED_UAT_CUSTOMER_PASSWORD = TEST_PASSWORD;
+    process.env.SEED_UAT_TESTER_PASSWORD = TEST_PASSWORD;
+
+    await seedUatCredentials(UAT_ACCOUNTS, { users: [], accounts: [], staff: [] });
+
+    // 4 users created (customer + tester have no staff, super + groomer do)
+    expect(insertedUsers).toHaveLength(4);
+    expect(insertedUsers.find((u) => u.email === "uat-super@groombook.dev")).toBeDefined();
+    expect(insertedUsers.find((u) => u.email === "uat-groomer@groombook.dev")).toBeDefined();
+    expect(insertedUsers.find((u) => u.email === "uat-customer@groombook.dev")).toBeDefined();
+    expect(insertedUsers.find((u) => u.email === "uat-tester@groombook.dev")).toBeDefined();
+
+    // 4 accounts created
+    expect(insertedAccounts).toHaveLength(4);
+    for (const acct of insertedAccounts) {
+      expect(acct.providerId).toBe("credential");
+      // Better-Auth uses hex encoding: saltHex:keyHex (both lowercase hex)
+      expect(acct.password).toMatch(/^[a-f0-9]+:[a-f0-9]+$/);
+      // Verify the hash is scrypt with correct params (N=16384, r=16, p=1, dkLen=64)
+      const parts = acct.password!.split(":");
+      const saltHex = parts[0]!;
+      const keyHex = parts[1]!;
+      const salt = Buffer.from(saltHex, "hex");
+      const storedHash = Buffer.from(keyHex, "hex");
+      expect(salt).toHaveLength(16);
+      expect(storedHash).toHaveLength(64);
+    }
+  });
+
+  // ── AC-2: emailVerified = true ─────────────────────────────────────────────
+
+  it("AC-2: created users have emailVerified = true", async () => {
+    process.env.SEED_UAT_CUSTOMER_PASSWORD = TEST_PASSWORD;
+
+    await seedUatCredentials(
+      [UAT_ACCOUNTS[2]!], // customer only
+      { users: [], accounts: [], staff: [] }
+    );
+
+    expect(insertedUsers[0]!.emailVerified).toBe(true);
+  });
+
+  // ── AC-3: providerId = credential, password is hashed ──────────────────────
+
+  it("AC-3: account records use providerId='credential' with properly formatted hashed password", async () => {
+    process.env.SEED_UAT_CUSTOMER_PASSWORD = TEST_PASSWORD;
+
+    await seedUatCredentials(
+      [UAT_ACCOUNTS[2]!],
+      { users: [], accounts: [], staff: [] }
+    );
+
+    const acct = insertedAccounts[0]!;
+    expect(acct.providerId).toBe("credential");
+    // Better-Auth uses hex: saltHex (32 chars) : keyHex (128 chars)
+    expect(acct.password).toMatch(/^[a-f0-9]+:[a-f0-9]+$/);
+    const parts = acct.password!.split(":");
+    const saltHex = parts[0]!;
+    const keyHex = parts[1]!;
+    expect(() => Buffer.from(saltHex, "hex")).not.toThrow();
+    expect(() => Buffer.from(keyHex, "hex")).not.toThrow();
+    const salt = Buffer.from(saltHex, "hex");
+    const storedHash = Buffer.from(keyHex, "hex");
+    expect(salt).toHaveLength(16);
+    expect(storedHash).toHaveLength(64);
+  });
+
+  // ── AC-4: staff.userId is linked ────────────────────────────────────────────
+
+  it("AC-4: links staff.userId to the Better-Auth user when staff record exists", async () => {
+    process.env.SEED_UAT_SUPER_PASSWORD = TEST_PASSWORD;
+    const staffRows: StaffRow[] = [
+      { id: "staff-super-1", email: "uat-super@groombook.dev", userId: null, name: "UAT Super User" },
+    ];
+
+    await seedUatCredentials([UAT_ACCOUNTS[0]!], { users: [], accounts: [], staff: staffRows });
+
+    expect(updatedStaff).toHaveLength(1);
+    expect(updatedStaff[0]!.id).toBe("staff-super-1");
+    expect(updatedStaff[0]!.userId).toBe("mock-uuid-1");
+    expect(staffRows[0]!.userId).toBe("mock-uuid-1");
+  });
+
+  it("AC-4b: does not update staff.userId if already set", async () => {
+    process.env.SEED_UAT_GROOMER_PASSWORD = TEST_PASSWORD;
+    const staffRows: StaffRow[] = [
+      { id: "staff-groomer-1", email: "uat-groomer@groombook.dev", userId: "already-linked", name: "UAT Groomer" },
+    ];
+
+    await seedUatCredentials([UAT_ACCOUNTS[1]!], { users: [], accounts: [], staff: staffRows });
+
+    expect(updatedStaff).toHaveLength(0);
+  });
+
+  // ── AC-5: idempotent — skips when user already exists ───────────────────────
+
+  it("AC-5: re-running does not duplicate user or account records (idempotent)", async () => {
+    process.env.SEED_UAT_CUSTOMER_PASSWORD = TEST_PASSWORD;
+
+    const preExistingUsers: UserRow[] = [
+      { id: "pre-existing-user", email: "uat-customer@groombook.dev", name: "UAT Customer", emailVerified: true },
+    ];
+    const preExistingAccounts: AccountRow[] = [
+      {
+        id: "pre-existing-acct",
+        accountId: "pre-existing-user",
+        providerId: "credential",
+        userId: "pre-existing-user",
+        password: await hashPassword(TEST_PASSWORD),
+      },
+    ];
+
+    // First call — nothing inserted (user + account pre-exist)
+    await seedUatCredentials([UAT_ACCOUNTS[2]!], {
+      users: preExistingUsers,
+      accounts: preExistingAccounts,
+      staff: [],
+    });
+
+    expect(insertedUsers).toHaveLength(0);
+    expect(insertedAccounts).toHaveLength(0);
+
+    // Second call — still nothing inserted
+    await seedUatCredentials([UAT_ACCOUNTS[2]!], {
+      users: preExistingUsers,
+      accounts: preExistingAccounts,
+      staff: [],
+    });
+
+    expect(insertedUsers).toHaveLength(0);
+    expect(insertedAccounts).toHaveLength(0);
+  });
+
+  // ── AC-6: missing env var skips with warning ────────────────────────────────
+
+  it("AC-6: missing SEED_UAT_*_PASSWORD env var skips that account (no error)", async () => {
+    // No env vars set at all
+    delete process.env.SEED_UAT_SUPER_PASSWORD;
+    delete process.env.SEED_UAT_GROOMER_PASSWORD;
+    delete process.env.SEED_UAT_CUSTOMER_PASSWORD;
+    delete process.env.SEED_UAT_TESTER_PASSWORD;
+
+    const warnSpy = vi.spyOn(console, "warn").mockReturnValue(undefined);
+
+    await seedUatCredentials(UAT_ACCOUNTS, { users: [], accounts: [], staff: [] });
+
+    // Nothing created
+    expect(insertedUsers).toHaveLength(0);
+    expect(insertedAccounts).toHaveLength(0);
+    // Warning logged for each of the 4 accounts
+    expect(warnSpy).toHaveBeenCalledTimes(4);
+    expect(warnSpy).toHaveBeenCalledWith(
+      "⚠ Skipping uat-super@groombook.dev — SEED_UAT_SUPER_PASSWORD not set"
+    );
+
+    warnSpy.mockRestore();
+  });
+
+  // ── AC-7: partial env var coverage ─────────────────────────────────────────
+
+  it("AC-7: only accounts with password env var set are provisioned", async () => {
+    process.env.SEED_UAT_SUPER_PASSWORD = TEST_PASSWORD;
+    // Only super has password set
+
+    const warnSpy = vi.spyOn(console, "warn").mockReturnValue(undefined);
+
+    await seedUatCredentials(UAT_ACCOUNTS, { users: [], accounts: [], staff: [] });
+
+    expect(insertedUsers).toHaveLength(1);
+    expect(insertedUsers[0]!.email).toBe("uat-super@groombook.dev");
+    expect(insertedAccounts).toHaveLength(1);
+    expect(insertedAccounts[0]!.accountId).toBe("mock-uuid-1");
+
+    // 3 warnings for missing accounts
+    expect(warnSpy).toHaveBeenCalledTimes(3);
+
+    warnSpy.mockRestore();
+  });
+});
+
+// ─── Password hash format verification ───────────────────────────────────────
+
+describe("password hash format — scrypt parameters", () => {
+  it("hashes use salt:hash format with 16-byte salt and 64-byte output", async () => {
+    const hash = await hashPassword("test-password");
+    const parts = hash.split(":");
+    const saltHex = parts[0]!;
+    const keyHex = parts[1]!;
+
+    expect(hash).toMatch(/^[a-f0-9]+:[a-f0-9]+$/);
+    expect(Buffer.from(saltHex, "hex")).toHaveLength(16);
+    expect(Buffer.from(keyHex, "hex")).toHaveLength(64);
+  });
+
+  it("same password produces different hashes (due to random salt)", async () => {
+    const hash1 = await hashPassword("same-password");
+    const hash2 = await hashPassword("same-password");
+
+    expect(hash1).not.toBe(hash2);
+    // Both are valid Better-Auth hex format
+    expect(hash1).toMatch(/^[a-f0-9]+:[a-f0-9]+$/);
+    expect(hash2).toMatch(/^[a-f0-9]+:[a-f0-9]+$/);
+  });
+
+  it("different passwords produce different hashes", async () => {
+    const hash1 = await hashPassword("password1");
+    const hash2 = await hashPassword("password2");
+
+    expect(hash1).not.toBe(hash2);
+  });
+});

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/factories.ts

@@ -103,6 +103,11 @@ export function buildPet(overrides: Partial<PetRow> & { clientId: string }): Pet
     photoKey: null,
     photoUploadedAt: null,
     image: null,
+    coatType: null,
+    temperamentScore: null,
+    temperamentFlags: [],
+    medicalAlerts: [],
+    preferredCuts: [],
     createdAt: new Date("2025-01-01T00:00:00Z"),
     updatedAt: new Date("2025-01-01T00:00:00Z"),
   };

apps/api/src/db/schema.ts

@@ -12,6 +12,16 @@ 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", [
@@ -146,6 +156,12 @@ 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

@@ -18,7 +18,7 @@
 
 import postgres from "postgres";
 import { drizzle } from "drizzle-orm/postgres-js";
-import { eq, sql } from "drizzle-orm";
+import { eq, and, sql } from "drizzle-orm";
 import * as schema from "./schema.js";
 
 // ── Seed profile configuration ─────────────────────────────────────────────
@@ -94,11 +94,6 @@ function pick<T>(arr: T[]): T {
   return arr[Math.floor(rand() * arr.length)]!;
 }
 
-/** Return n distinct random elements from an array. */
-function pickN<T>(arr: T[], n: number): T[] {
-  const shuffled = [...arr].sort(() => rand() - 0.5);
-  return shuffled.slice(0, n);
-}
 
 function randInt(min: number, max: number): number {
   return Math.floor(rand() * (max - min + 1)) + min;
@@ -459,6 +454,32 @@ 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) ?? [];
@@ -490,6 +511,90 @@ async function seedKnownUsers() {
     }
   }
 
+  // ── Better-Auth email+password credentials for UAT accounts ──────────────────
+  // Provisions Better-Auth user + account records so UAT testers can log in
+  // via email+password (POST /api/auth/sign-in/email) instead of Authentik SSO.
+  const uatPasswordAccounts = [
+    { email: "uat-super@groombook.dev", name: "UAT Super User", passwordEnv: "SEED_UAT_SUPER_PASSWORD", staffEmail: "uat-super@groombook.dev" },
+    { email: "uat-groomer@groombook.dev", name: "UAT Staff Groomer", passwordEnv: "SEED_UAT_GROOMER_PASSWORD", staffEmail: "uat-groomer@groombook.dev" },
+    { email: "uat-customer@groombook.dev", name: "UAT Customer", passwordEnv: "SEED_UAT_CUSTOMER_PASSWORD", staffEmail: null },
+    { email: "uat-tester@groombook.dev", name: "UAT Tester", passwordEnv: "SEED_UAT_TESTER_PASSWORD", staffEmail: "uat-tester@groombook.dev" },
+  ];
+
+  for (const acct of uatPasswordAccounts) {
+    const password = process.env[acct.passwordEnv];
+    if (!password) {
+      console.warn(`⚠ Skipping ${acct.email} — ${acct.passwordEnv} not set`);
+      continue;
+    }
+
+    // 1. Find or create the Better-Auth user
+    const [existingUser] = await db
+      .select()
+      .from(schema.user)
+      .where(eq(schema.user.email, acct.email))
+      .limit(1);
+
+    let userId: string;
+    if (existingUser) {
+      userId = existingUser.id;
+      console.log(`✓ Better-Auth user '${acct.name}' already exists — skipping user creation`);
+    } else {
+      userId = uuid();
+      await db.insert(schema.user).values({
+        id: userId,
+        name: acct.name,
+        email: acct.email,
+        emailVerified: true,
+      });
+      console.log(`✓ Created Better-Auth user '${acct.name}' (${acct.email})`);
+    }
+
+    // 2. Check if credential account already exists
+    const [existingAccount] = await db
+      .select()
+      .from(schema.account)
+      .where(and(
+        eq(schema.account.userId, userId),
+        eq(schema.account.providerId, "credential")
+      ))
+      .limit(1);
+
+    if (existingAccount) {
+      console.log(`✓ Credential account for '${acct.email}' already exists — skipping`);
+    } else {
+      // Use Better-Auth's own hashPassword to guarantee parameter/encoding match.
+      // better-auth/crypto uses: N=16384, r=16, p=1, dkLen=64, salt as 16-byte random
+      // hex string, key hex-encoded, format saltHex:keyHex.
+      const { hashPassword } = await import("better-auth/crypto");
+      const passwordHash = await hashPassword(password);
+
+      await db.insert(schema.account).values({
+        id: uuid(),
+        accountId: userId,
+        providerId: "credential",
+        userId,
+        password: passwordHash,
+      });
+      console.log(`✓ Created credential account for '${acct.email}'`);
+    }
+
+    // 3. Link staff record to Better-Auth user (for accounts that have staff records)
+    if (acct.staffEmail) {
+      const [existingStaff] = await db
+        .select()
+        .from(schema.staff)
+        .where(eq(schema.staff.email, acct.staffEmail))
+        .limit(1);
+      if (existingStaff && !existingStaff.userId) {
+        await db.update(schema.staff)
+          .set({ userId })
+          .where(eq(schema.staff.id, existingStaff.id));
+        console.log(`✓ Linked staff '${acct.staffEmail}' → Better-Auth user`);
+      }
+    }
+  }
+
   // ── Services: idempotent upsert using name as unique key ─────────────────────
   // UNIQUE constraint on services.name (migration 0020) must exist first.
   // Uses b0000001-... IDs to match main seed servicesDef for same-named services.
@@ -1079,7 +1184,7 @@ async function seed() {
       const groomer = pick(groomers);
       const bather = bathers.length > 0 && rand() < 0.6 ? pick(bathers) : null;
 
-      let startTime = randDate(appointmentsBackDate, now);
+      const startTime = randDate(appointmentsBackDate, now);
       startTime.setHours(randInt(8, 16), pick([0, 15, 30, 45]), 0, 0);
       const endTime = new Date(startTime.getTime() + svc.dur * 60 * 1000);
       const effectivePrice = svc.price;

apps/api/src/index.ts

@@ -22,10 +22,11 @@ import { searchRouter } from "./routes/search.js";
 import { getObject } from "./lib/s3.js";
 import { calendarRouter } from "./routes/calendar.js";
 import { setupRouter } from "./routes/setup.js";
-import { getDb, businessSettings, eq, staff } from "./db";
+import { getDb, businessSettings, eq, staff } from "./db/index.js";
 import { authMiddleware } from "./middleware/auth.js";
 import { resolveStaffMiddleware, requireRole, requireRoleOrSuperUser, requireSuperUser } from "./middleware/rbac.js";
 import { devRouter } from "./routes/dev.js";
+import { bufferRulesRouter } from "./routes/buffer-rules.js";
 import { adminSeedRouter } from "./routes/admin/seed.js";
 import { startReminderScheduler } from "./services/reminders.js";
 import { webhooksRouter } from "./routes/stripe-webhooks.js";
@@ -211,6 +212,7 @@ api.on(["GET"], "/staff/*", requireRole("manager", "receptionist", "groomer"));
 // Staff write routes: manager OR super-user (combined guard — avoids AND stacking)
 api.on(["POST", "PATCH", "DELETE"], "/staff/*", requireRoleOrSuperUser("manager"));
 api.use("/admin/*", requireRoleOrSuperUser("manager"));
+api.use("/buffer-rules/*", requireRole("manager"));
 api.use("/admin/settings/*", requireSuperUser());
 api.use("/reports/*", requireRole("manager"));
 api.use("/invoices/*", requireRole("manager", "groomer"));
@@ -268,6 +270,7 @@ api.route("/impersonation", impersonationRouter);
 api.route("/admin/settings", settingsRouter);
 api.route("/admin/auth-provider", authProviderRouter);
 api.route("/admin/seed", adminSeedRouter);
+api.route("/buffer-rules", bufferRulesRouter);
 api.route("/search", searchRouter);
 
 const port = Number(process.env.PORT ?? 3000);

apps/api/src/lib/auth.ts

@@ -1,8 +1,8 @@
 import { betterAuth } from "better-auth";
 import { drizzleAdapter } from "better-auth/adapters/drizzle";
 import { genericOAuth } from "better-auth/plugins";
-import { getDb, authProviderConfig, eq } from "./db";
-import { decryptSecret } from "./db";
+import { getDb, authProviderConfig, eq } from "../db/index.js";
+import { decryptSecret } from "../db/index.js";
 import { sendEmail } from "../services/email.js";
 
 const BETTER_AUTH_SECRET = process.env.BETTER_AUTH_SECRET;
@@ -97,6 +97,9 @@ export async function initAuth(): Promise<void> {
           window: 10,
           storage: "memory",
           customRules: {
+            "/sign-in/social": { max: 10, window: 60 },
+            "/sign-in/email": { max: 10, window: 60 },
+            "/sign-up/email": { max: 5, window: 60 },
             "/get-session": false,
           },
         },
@@ -247,6 +250,9 @@ export async function initAuth(): Promise<void> {
         window: 10,
         storage: "memory",
         customRules: {
+          "/sign-in/social": { max: 10, window: 60 },
+          "/sign-in/email": { max: 10, window: 60 },
+          "/sign-up/email": { max: 5, window: 60 },
           "/get-session": false,
         },
       },

apps/api/src/middleware/portalAudit.ts

@@ -1,5 +1,5 @@
 import type { MiddlewareHandler } from "hono";
-import { getDb, impersonationAuditLogs } from "../db";
+import { getDb, impersonationAuditLogs } from "../db/index.js";
 import type { PortalEnv } from "./portalSession.js";
 
 /**

apps/api/src/middleware/portalSession.ts

@@ -1,5 +1,5 @@
 import type { MiddlewareHandler } from "hono";
-import { and, eq, getDb, impersonationSessions } from "../db";
+import { and, eq, getDb, impersonationSessions } from "../db/index.js";
 
 export interface PortalEnv {
   Variables: {

apps/api/src/middleware/rbac.ts

@@ -1,5 +1,5 @@
 import type { MiddlewareHandler } from "hono";
-import { and, eq, getDb, sql, staff } from "../db";
+import { and, eq, getDb, sql, staff, user } from "../db/index.js";
 
 export type StaffRole = "groomer" | "receptionist" | "manager";
 export type StaffRow = typeof staff.$inferSelect;
@@ -110,6 +110,33 @@ export const resolveStaffMiddleware: MiddlewareHandler<AppEnv> = async (
       return;
     }
   }
+  // Auto-provision: no staff record exists for this user at all, but a valid
+  // Better-Auth user session exists (jwt.sub = user.id from user table).
+  // Create a minimal groomer staff record on first login.
+  const [userRow] = await db
+    .select({ id: user.id, name: user.name, email: user.email })
+    .from(user)
+    .where(eq(user.id, jwt.sub))
+    .limit(1);
+  if (userRow) {
+    const [newStaff] = await db
+      .insert(staff)
+      .values({
+        name: userRow.name ?? jwt.email?.split("@")[0] ?? "Unknown",
+        email: userRow.email ?? jwt.email ?? "",
+        userId: jwt.sub,
+        role: "groomer",
+        isSuperUser: false,
+        active: true,
+      })
+      .returning();
+    if (!newStaff) {
+      return c.json({ error: "Internal error: staff record creation failed" }, 500);
+    }
+    c.set("staff", newStaff);
+    await next();
+    return;
+  }
   return c.json(
     { error: "Forbidden: no staff record found for authenticated user" },
     403

apps/api/src/routes/admin/seed.ts

@@ -10,7 +10,7 @@
  */
 
 import { Hono } from "hono";
-import { eq, getDb, staff, clients, pets, services } from "./db";
+import { eq, getDb, staff, clients, pets, services } from "../../db/index.js";
 
 export const adminSeedRouter = new Hono();
 

apps/api/src/routes/appointmentGroups.ts

@@ -15,7 +15,7 @@ import {
   pets,
   services,
   staff,
-} from "../db";
+} from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 export const appointmentGroupsRouter = new Hono<AppEnv>();

apps/api/src/routes/appointments.ts

@@ -18,7 +18,7 @@ import {
   reminderLogs,
   services,
   staff,
-} from "../db";
+} from "../db/index.js";
 import { buildConfirmationEmail, sendEmail } from "../services/email.js";
 import { notifyWaitlistForAppointment } from "../services/waitlistNotify.js";
 import type { AppEnv } from "../middleware/rbac.js";

apps/api/src/routes/authProvider.ts

@@ -1,7 +1,7 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { eq, getDb, authProviderConfig, encryptSecret } from "../db";
+import { eq, getDb, authProviderConfig, encryptSecret } from "../db/index.js";
 import { requireSuperUser } from "../middleware/rbac.js";
 import { reinitAuth } from "../lib/auth.js";
 

apps/api/src/routes/book.ts

@@ -14,7 +14,7 @@ import {
   appointments,
   clients,
   pets,
-} from "../db";
+} from "../db/index.js";
 import {
   generateAvailableSlots,
   BUSINESS_START_HOUR,

apps/api/src/routes/buffer-rules.ts

@@ -0,0 +1,124 @@
+import { Hono } from "hono";
+import { zValidator } from "@hono/zod-validator";
+import { z } from "zod/v3";
+import { and, eq, getDb, isNull } from "../db/index.js";
+import type { AppEnv } from "../middleware/rbac.js";
+import { bufferRules, services } from "../db/index.js";
+
+export const bufferRulesRouter = new Hono<AppEnv>();
+
+const createBufferRuleSchema = z.object({
+  serviceId: z.string().uuid(),
+  sizeCategory: z
+    .enum(["small", "medium", "large", "extra_large"])
+    .optional(),
+  coatType: z
+    .enum(["short", "medium", "long", "double", "wire", "silky", "curly", "hairless"])
+    .optional(),
+  bufferMinutes: z.number().int().positive(),
+});
+
+const updateBufferRuleSchema = z.object({
+  bufferMinutes: z.number().int().positive(),
+});
+
+// GET / — list all buffer rules, optionally filtered by serviceId
+bufferRulesRouter.get("/", async (c) => {
+  const db = getDb();
+  const serviceId = c.req.query("serviceId");
+
+  const conditions = [];
+  if (serviceId) conditions.push(eq(bufferRules.serviceId, serviceId));
+
+  const rows = await db
+    .select({
+      id: bufferRules.id,
+      serviceId: bufferRules.serviceId,
+      sizeCategory: bufferRules.sizeCategory,
+      coatType: bufferRules.coatType,
+      bufferMinutes: bufferRules.bufferMinutes,
+      createdAt: bufferRules.createdAt,
+      updatedAt: bufferRules.updatedAt,
+      serviceName: services.name,
+    })
+    .from(bufferRules)
+    .innerJoin(services, eq(bufferRules.serviceId, services.id))
+    .where(conditions.length > 0 ? and(...conditions) : undefined)
+    .orderBy(bufferRules.createdAt);
+
+  return c.json(rows);
+});
+
+// POST / — create a buffer rule
+bufferRulesRouter.post(
+  "/",
+  zValidator("json", createBufferRuleSchema),
+  async (c) => {
+    const db = getDb();
+    const body = c.req.valid("json");
+
+    // Validate serviceId exists
+    const [svc] = await db
+      .select({ id: services.id })
+      .from(services)
+      .where(eq(services.id, body.serviceId));
+    if (!svc) return c.json({ error: "Service not found" }, 404);
+
+    // Check for duplicate (service + size + coat)
+    const [existing] = await db
+      .select({ id: bufferRules.id })
+      .from(bufferRules)
+      .where(
+        and(
+          eq(bufferRules.serviceId, body.serviceId),
+          body.sizeCategory !== undefined
+            ? eq(bufferRules.sizeCategory, body.sizeCategory)
+            : isNull(bufferRules.sizeCategory),
+          body.coatType !== undefined
+            ? eq(bufferRules.coatType, body.coatType)
+            : isNull(bufferRules.coatType)
+        )
+      );
+    if (existing) return c.json({ error: "Duplicate rule for this service+size+coat combination" }, 409);
+
+    const [row] = await db
+      .insert(bufferRules)
+      .values({
+        serviceId: body.serviceId,
+        sizeCategory: body.sizeCategory ?? null,
+        coatType: body.coatType ?? null,
+        bufferMinutes: body.bufferMinutes,
+      })
+      .returning();
+
+    return c.json(row, 201);
+  }
+);
+
+// PATCH /:id — update bufferMinutes only
+bufferRulesRouter.patch(
+  "/:id",
+  zValidator("json", updateBufferRuleSchema),
+  async (c) => {
+    const db = getDb();
+    const body = c.req.valid("json");
+    const [row] = await db
+      .update(bufferRules)
+      .set({ bufferMinutes: body.bufferMinutes, updatedAt: new Date() })
+      .where(eq(bufferRules.id, c.req.param("id")))
+      .returning();
+    if (!row) return c.json({ error: "Not found" }, 404);
+    return c.json(row);
+  }
+);
+
+// DELETE /:id — delete a buffer rule
+bufferRulesRouter.delete("/:id", async (c) => {
+  const db = getDb();
+  const [row] = await db
+    .delete(bufferRules)
+    .where(eq(bufferRules.id, c.req.param("id")))
+    .returning();
+  if (!row) return c.json({ error: "Not found" }, 404);
+  return c.json({ ok: true });
+});

apps/api/src/routes/calendar.ts

@@ -10,7 +10,7 @@ import {
   pets,
   services,
   staff,
-} from "../db";
+} from "../db/index.js";
 
 export const calendarRouter = new Hono();
 

apps/api/src/routes/clients.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, clients, appointments } from "../db";
+import { and, eq, exists, getDb, or, clients, appointments } from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 export const clientsRouter = new Hono<AppEnv>();

apps/api/src/routes/dev.ts

@@ -1,5 +1,5 @@
 import { Hono } from "hono";
-import { getDb, staff, clients, eq, sql } from "../db";
+import { getDb, staff, clients, eq, sql } from "../db/index.js";
 
 const devRouter = new Hono();
 

apps/api/src/routes/groomingLogs.ts

@@ -1,7 +1,7 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { and, desc, eq, getDb, groomingVisitLogs, appointments, or } from "../db";
+import { and, desc, eq, getDb, groomingVisitLogs, appointments, or } from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 export const groomingLogsRouter = new Hono<AppEnv>();

apps/api/src/routes/impersonation.ts

@@ -9,7 +9,7 @@ import {
   impersonationAuditLogs,
   clients,
   desc,
-} from "../db";
+} from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 export const impersonationRouter = new Hono<AppEnv>();

apps/api/src/routes/invoices.ts

@@ -13,7 +13,7 @@ import {
   services,
   clients,
   sql,
-} from "../db";
+} from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 export const invoicesRouter = new Hono<AppEnv>();

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";
+import { and, eq, exists, getDb, or, pets, appointments } from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 import {
   getPresignedUploadUrl,
@@ -24,6 +24,16 @@ const createPetSchema = z.object({
   shampooPreference: z.string().max(500).optional(),
   specialCareNotes: z.string().max(2000).optional(),
   customFields: z.record(z.string(), z.string()).optional(),
+  sizeCategory: z.enum(["small", "medium", "large", "extra_large"]).optional(),
+  coatType: z.enum(["short", "medium", "long", "double", "wire", "silky", "curly", "hairless"]).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/routes/portal.ts

@@ -1,8 +1,8 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { eq, inArray } from "../db";
-import { getDb, appointments, impersonationSessions, waitlistEntries, clients, pets, services, staff, invoices, invoiceLineItems } from "../db";
+import { eq, inArray } from "../db/index.js";
+import { getDb, appointments, impersonationSessions, waitlistEntries, clients, pets, services, staff, invoices, invoiceLineItems } from "../db/index.js";
 import { validatePortalSession } from "../middleware/portalSession.js";
 import { portalAudit } from "../middleware/portalAudit.js";
 import type { PortalEnv } from "../middleware/portalSession.js";

apps/api/src/routes/reports.ts

@@ -12,7 +12,7 @@ import {
   invoiceTipSplits,
   services,
   staff,
-} from "../db";
+} from "../db/index.js";
 
 export const reportsRouter = new Hono();
 

apps/api/src/routes/search.ts

@@ -1,5 +1,5 @@
 import { Hono } from "hono";
-import { and, eq, getDb, clients, ilike, or, pets } from "../db";
+import { and, eq, getDb, clients, ilike, or, pets } from "../db/index.js";
 
 export const searchRouter = new Hono();
 

apps/api/src/routes/services.ts

@@ -1,7 +1,7 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { eq, getDb, services } from "../db";
+import { eq, getDb, services } from "../db/index.js";
 
 export const servicesRouter = new Hono();
 
@@ -13,7 +13,9 @@ const createServiceSchema = z.object({
   active: z.boolean().default(true),
 });
 
-const updateServiceSchema = createServiceSchema.partial();
+const updateServiceSchema = createServiceSchema.partial().extend({
+  defaultBufferMinutes: z.number().int().min(0).optional(),
+});
 
 servicesRouter.get("/", async (c) => {
   const db = getDb();

apps/api/src/routes/settings.ts

@@ -1,7 +1,7 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { eq, getDb, businessSettings } from "../db";
+import { eq, getDb, businessSettings } from "../db/index.js";
 import { getPresignedUploadUrl, deleteObject, putObject, getObject } from "../lib/s3.js";
 import { requireSuperUser } from "../middleware/rbac.js";
 

apps/api/src/routes/setup.ts

@@ -1,7 +1,7 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { and, eq, getDb, sql, staff, businessSettings, authProviderConfig, encryptSecret } from "../db";
+import { and, eq, getDb, sql, staff, businessSettings, authProviderConfig, encryptSecret } from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 const RATE_LIMIT_WINDOW_MS = 60_000;

apps/api/src/routes/staff.ts

@@ -2,7 +2,7 @@ import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
 import { randomBytes } from "node:crypto";
-import { and, eq, getDb, ne, staff, appointments } from "../db";
+import { and, eq, getDb, ne, staff, appointments } from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 export const staffRouter = new Hono<AppEnv>();

apps/api/src/routes/stripe-webhooks.ts

@@ -1,7 +1,7 @@
 import { Hono } from "hono";
 import Stripe from "stripe";
 import { z } from "zod/v3";
-import { eq, getDb, invoices } from "../db";
+import { eq, getDb, invoices } from "../db/index.js";
 import { getStripeClient } from "../services/payment.js";
 
 export const webhooksRouter = new Hono();

apps/api/src/routes/waitlist.ts

@@ -8,7 +8,7 @@ import {
   clients,
   pets,
   services,
-} from "../db";
+} from "../db/index.js";
 import type { AppEnv } from "../middleware/rbac.js";
 
 export const waitlistRouter = new Hono<AppEnv>();

apps/api/src/services/payment.ts

@@ -1,5 +1,5 @@
 import Stripe from "stripe";
-import { getDb, clients, eq, inArray, invoices } from "../db";
+import { getDb, clients, eq, inArray, invoices } from "../db/index.js";
 
 let _stripe: Stripe | null | undefined;
 

apps/api/src/services/reminders.ts

@@ -14,7 +14,7 @@ import {
   staff,
   reminderLogs,
   session,
-} from "../db";
+} from "../db/index.js";
 import {
   buildReminderEmail,
   sendEmail,

apps/api/src/services/waitlistNotify.ts

@@ -1,4 +1,4 @@
-import { and, eq, getDb, waitlistEntries, clients, pets, services } from "../db";
+import { and, eq, getDb, waitlistEntries, clients, pets, services } from "../db/index.js";
 import { buildWaitlistNotificationEmail, sendEmail } from "./email.js";
 
 export async function notifyWaitlistForAppointment(

apps/api/src/types/index.ts

@@ -26,6 +26,19 @@ export interface Client {
   updatedAt: string;
 }
 
+// ─── Medical Alerts ────────────────────────────────────────────────────────────
+
+export type AlertSeverity = "low" | "medium" | "high";
+
+export interface MedicalAlert {
+  type: string;
+  description: string;
+  severity: AlertSeverity;
+}
+
+// ─── Pet Profile Summary ────────────────────────────────────────────────────
+
+export type CoatType = "short" | "medium" | "long" | "double" | "wire" | "silky" | "curly" | "hairless";
 export interface Pet {
   id: string;
   clientId: string;
@@ -42,10 +55,23 @@ 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;

eslint.config.js

@@ -0,0 +1,11 @@
+import tseslint from "typescript-eslint";
+
+export default tseslint.config(
+  ...tseslint.configs.recommended,
+  {
+    rules: {
+      "@typescript-eslint/no-explicit-any": "warn",
+      "@typescript-eslint/no-unused-vars": ["error", { argsIgnorePattern: "^_" }],
+    },
+  }
+);

package.json

@@ -3,5 +3,42 @@
   "version": "0.0.1",
   "private": true,
   "type": "module",
+  "packageManager": "pnpm@9.15.4",
+  "scripts": {
+    "dev": "tsx watch src/index.ts",
+    "build": "tsc --project .",
+    "start": "node dist/index.js",
+    "lint": "eslint src --ext .ts",
+    "typecheck": "tsc --noEmit",
+    "test": "vitest run"
+  },
+  "dependencies": {
+    "@aws-sdk/client-s3": "^3.800.0",
+    "@aws-sdk/s3-request-presigner": "^3.800.0",
+    "@groombook/db": "workspace:*",
+    "@groombook/types": "workspace:*",
+    "@hono/node-server": "^1.13.7",
+    "@hono/zod-validator": "^0.7.6",
+    "better-auth": "^1.5.6",
+    "drizzle-orm": "^0.38.4",
+    "hono": "^4.6.17",
+    "node-cron": "^3.0.3",
+    "nodemailer": "^6.9.16",
+    "postgres": "^3.4.5",
+    "stripe": "^22.0.0",
+    "telnyx": "^1.23.0",
+    "zod": "^4.3.6"
+  },
+  "devDependencies": {
+    "@types/node": "^22.10.7",
+    "@types/node-cron": "^3.0.11",
+    "@types/nodemailer": "^6.4.17",
+    "@vitest/coverage-v8": "^3.2.4",
+    "eslint": "^9.18.0",
+    "tsx": "^4.19.2",
+    "typescript": "^5.7.3",
+    "typescript-eslint": "^8.20.0",
+    "vitest": "^3.2.4"
+  },
   "license": "AGPL-3.0-only"
 }

packages/db/drizzle.config.ts

@@ -0,0 +1,10 @@
+import { defineConfig } from "drizzle-kit";
+
+export default defineConfig({
+  schema: "./src/schema.ts",
+  out: "./migrations",
+  dialect: "postgresql",
+  dbCredentials: {
+    url: process.env.DATABASE_URL!,
+  },
+});

packages/db/migrations/0000_colossal_colossus.sql

@@ -0,0 +1,70 @@
+CREATE TYPE "public"."appointment_status" AS ENUM('scheduled', 'confirmed', 'in_progress', 'completed', 'cancelled', 'no_show');--> statement-breakpoint
+CREATE TYPE "public"."staff_role" AS ENUM('groomer', 'receptionist', 'manager');--> statement-breakpoint
+CREATE TABLE "appointments" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"client_id" uuid NOT NULL,
+	"pet_id" uuid NOT NULL,
+	"service_id" uuid NOT NULL,
+	"staff_id" uuid,
+	"status" "appointment_status" DEFAULT 'scheduled' NOT NULL,
+	"start_time" timestamp NOT NULL,
+	"end_time" timestamp NOT NULL,
+	"notes" text,
+	"price_cents" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "clients" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"name" text NOT NULL,
+	"email" text,
+	"phone" text,
+	"address" text,
+	"notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "pets" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"client_id" uuid NOT NULL,
+	"name" text NOT NULL,
+	"species" text NOT NULL,
+	"breed" text,
+	"weight_kg" numeric(5, 2),
+	"date_of_birth" timestamp,
+	"grooming_notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "services" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"name" text NOT NULL,
+	"description" text,
+	"base_price_cents" integer NOT NULL,
+	"duration_minutes" integer NOT NULL,
+	"active" boolean DEFAULT true NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "staff" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"name" text NOT NULL,
+	"email" text NOT NULL,
+	"oidc_sub" text,
+	"role" "staff_role" DEFAULT 'groomer' NOT NULL,
+	"active" boolean DEFAULT true NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "staff_email_unique" UNIQUE("email"),
+	CONSTRAINT "staff_oidc_sub_unique" UNIQUE("oidc_sub")
+);
+--> statement-breakpoint
+ALTER TABLE "appointments" ADD CONSTRAINT "appointments_client_id_clients_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."clients"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "appointments" ADD CONSTRAINT "appointments_pet_id_pets_id_fk" FOREIGN KEY ("pet_id") REFERENCES "public"."pets"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "appointments" ADD CONSTRAINT "appointments_service_id_services_id_fk" FOREIGN KEY ("service_id") REFERENCES "public"."services"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "appointments" ADD CONSTRAINT "appointments_staff_id_staff_id_fk" FOREIGN KEY ("staff_id") REFERENCES "public"."staff"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "pets" ADD CONSTRAINT "pets_client_id_clients_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."clients"("id") ON DELETE cascade ON UPDATE no action;

packages/db/migrations/0001_pet_health_alerts.sql

@@ -0,0 +1 @@
+ALTER TABLE "pets" ADD COLUMN "health_alerts" text;

packages/db/migrations/0002_invoices.sql

@@ -0,0 +1,31 @@
+CREATE TYPE "public"."invoice_status" AS ENUM('draft', 'pending', 'paid', 'void');--> statement-breakpoint
+CREATE TYPE "public"."payment_method" AS ENUM('cash', 'card', 'check', 'other');--> statement-breakpoint
+CREATE TABLE "invoices" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"appointment_id" uuid,
+	"client_id" uuid NOT NULL,
+	"subtotal_cents" integer NOT NULL,
+	"tax_cents" integer DEFAULT 0 NOT NULL,
+	"tip_cents" integer DEFAULT 0 NOT NULL,
+	"total_cents" integer NOT NULL,
+	"status" "invoice_status" DEFAULT 'draft' NOT NULL,
+	"payment_method" "payment_method",
+	"paid_at" timestamp,
+	"notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "invoice_line_items" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"invoice_id" uuid NOT NULL,
+	"description" text NOT NULL,
+	"quantity" integer DEFAULT 1 NOT NULL,
+	"unit_price_cents" integer NOT NULL,
+	"total_cents" integer NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+ALTER TABLE "invoices" ADD CONSTRAINT "invoices_appointment_id_appointments_id_fk" FOREIGN KEY ("appointment_id") REFERENCES "public"."appointments"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "invoices" ADD CONSTRAINT "invoices_client_id_clients_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."clients"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "invoice_line_items" ADD CONSTRAINT "invoice_line_items_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;

packages/db/migrations/0003_recurring_series.sql

@@ -0,0 +1,10 @@
+-- Add recurring_series table to store recurrence patterns
+CREATE TABLE "recurring_series" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"frequency_weeks" integer NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL
+);
+
+-- Extend appointments with series tracking
+ALTER TABLE "appointments" ADD COLUMN "series_id" uuid REFERENCES "recurring_series"("id") ON DELETE SET NULL;
+ALTER TABLE "appointments" ADD COLUMN "series_index" integer;

packages/db/migrations/0004_reminder_logs.sql

@@ -0,0 +1,11 @@
+-- Add email opt-out flag to clients
+ALTER TABLE "clients" ADD COLUMN "email_opt_out" boolean NOT NULL DEFAULT false;
+
+-- Track sent reminders to prevent duplicate sends
+CREATE TABLE "reminder_logs" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"appointment_id" uuid NOT NULL REFERENCES "appointments"("id") ON DELETE CASCADE,
+	"reminder_type" text NOT NULL,
+	"sent_at" timestamp DEFAULT now() NOT NULL,
+	UNIQUE ("appointment_id", "reminder_type")
+);

packages/db/migrations/0005_appointment_groups.sql

@@ -0,0 +1,12 @@
+-- Appointment groups: link multiple appointments from the same client visit.
+-- Each appointment in a group is for a different pet and may have a different groomer.
+CREATE TABLE appointment_groups (
+  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+  client_id UUID NOT NULL REFERENCES clients(id) ON DELETE RESTRICT,
+  notes TEXT,
+  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
+);
+
+-- Link appointments to a group (nullable — non-grouped appointments are unaffected)
+ALTER TABLE appointments ADD COLUMN group_id UUID REFERENCES appointment_groups(id) ON DELETE SET NULL;

packages/db/migrations/0006_pet_profile_attributes.sql

@@ -0,0 +1,30 @@
+-- Extend pet profiles with grooming-specific attributes (closes groombook/groombook#13)
+ALTER TABLE "pets"
+  ADD COLUMN "cut_style" text,
+  ADD COLUMN "shampoo_preference" text,
+  ADD COLUMN "special_care_notes" text,
+  ADD COLUMN "custom_fields" jsonb DEFAULT '{}' NOT NULL;
+--> statement-breakpoint
+CREATE TABLE "grooming_visit_logs" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+  "pet_id" uuid NOT NULL,
+  "appointment_id" uuid,
+  "staff_id" uuid,
+  "cut_style" text,
+  "products_used" text,
+  "notes" text,
+  "groomed_at" timestamp NOT NULL DEFAULT now(),
+  "created_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+ALTER TABLE "grooming_visit_logs"
+  ADD CONSTRAINT "grooming_visit_logs_pet_id_pets_id_fk"
+    FOREIGN KEY ("pet_id") REFERENCES "public"."pets"("id") ON DELETE cascade ON UPDATE no action;
+--> statement-breakpoint
+ALTER TABLE "grooming_visit_logs"
+  ADD CONSTRAINT "grooming_visit_logs_appointment_id_appointments_id_fk"
+    FOREIGN KEY ("appointment_id") REFERENCES "public"."appointments"("id") ON DELETE set null ON UPDATE no action;
+--> statement-breakpoint
+ALTER TABLE "grooming_visit_logs"
+  ADD CONSTRAINT "grooming_visit_logs_staff_id_staff_id_fk"
+    FOREIGN KEY ("staff_id") REFERENCES "public"."staff"("id") ON DELETE set null ON UPDATE no action;

packages/db/migrations/0007_tip_splitting.sql

@@ -0,0 +1,25 @@
+-- Add bather/assistant staff tracking to appointments and tip split ledger (closes groombook/groombook#12)
+
+-- Secondary staff member (e.g., bather) who assisted the primary groomer
+ALTER TABLE "appointments"
+  ADD COLUMN "bather_staff_id" uuid REFERENCES "public"."staff"("id") ON DELETE set null ON UPDATE no action;
+--> statement-breakpoint
+
+-- Stores per-staff tip allocations calculated when an invoice is paid
+CREATE TABLE "invoice_tip_splits" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+  "invoice_id" uuid NOT NULL,
+  "staff_id" uuid,
+  "staff_name" text NOT NULL,
+  "share_pct" numeric(5, 2) NOT NULL,
+  "share_cents" integer NOT NULL,
+  "created_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+ALTER TABLE "invoice_tip_splits"
+  ADD CONSTRAINT "invoice_tip_splits_invoice_id_invoices_id_fk"
+    FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;
+--> statement-breakpoint
+ALTER TABLE "invoice_tip_splits"
+  ADD CONSTRAINT "invoice_tip_splits_staff_id_staff_id_fk"
+    FOREIGN KEY ("staff_id") REFERENCES "public"."staff"("id") ON DELETE set null ON UPDATE no action;

packages/db/migrations/0008_business_settings.sql

@@ -0,0 +1,15 @@
+CREATE TABLE IF NOT EXISTS "business_settings" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+  "business_name" text DEFAULT 'GroomBook' NOT NULL,
+  "logo_base64" text,
+  "logo_mime_type" text,
+  "primary_color" text DEFAULT '#4f8a6f' NOT NULL,
+  "accent_color" text DEFAULT '#8b7355' NOT NULL,
+  "created_at" timestamp DEFAULT now() NOT NULL,
+  "updated_at" timestamp DEFAULT now() NOT NULL
+);
+
+-- Seed a default row so GET always returns something
+INSERT INTO "business_settings" ("business_name", "primary_color", "accent_color")
+VALUES ('GroomBook', '#4f8a6f', '#8b7355')
+ON CONFLICT DO NOTHING;

packages/db/migrations/0009_client_soft_delete.sql

@@ -0,0 +1,6 @@
+-- Add client status (soft-delete support)
+CREATE TYPE "client_status" AS ENUM ('active', 'disabled');
+
+ALTER TABLE "clients"
+  ADD COLUMN "status" "client_status" NOT NULL DEFAULT 'active',
+  ADD COLUMN "disabled_at" timestamp;

packages/db/migrations/0010_impersonation_sessions.sql

@@ -0,0 +1,26 @@
+-- Create impersonation_session_status enum and tables
+CREATE TYPE "impersonation_session_status" AS ENUM ('active', 'ended', 'expired');
+
+CREATE TABLE "impersonation_sessions" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"staff_id" uuid NOT NULL,
+	"client_id" uuid NOT NULL,
+	"reason" text,
+	"status" "impersonation_session_status" DEFAULT 'active' NOT NULL,
+	"started_at" timestamp DEFAULT now() NOT NULL,
+	"ended_at" timestamp,
+	"expires_at" timestamp NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "impersonation_sessions_staff_id_staff_id_fk" FOREIGN KEY ("staff_id") REFERENCES "staff"("id") ON DELETE restrict,
+	CONSTRAINT "impersonation_sessions_client_id_clients_id_fk" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE restrict
+);
+
+CREATE TABLE "impersonation_audit_logs" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"session_id" uuid NOT NULL,
+	"action" text NOT NULL,
+	"page_visited" text,
+	"metadata" jsonb,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "impersonation_audit_logs_session_id_impersonation_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "impersonation_sessions"("id") ON DELETE cascade
+);

packages/db/migrations/0011_impersonation_indexes.sql

@@ -0,0 +1,6 @@
+-- Add indexes on impersonation tables to prevent full table scans
+-- Ref: GitHub #95
+
+CREATE INDEX "impersonation_sessions_staff_id_status_idx" ON "impersonation_sessions" USING btree ("staff_id","status");--> statement-breakpoint
+CREATE INDEX "impersonation_sessions_client_id_idx" ON "impersonation_sessions" USING btree ("client_id");--> statement-breakpoint
+CREATE INDEX "impersonation_audit_logs_session_id_idx" ON "impersonation_audit_logs" USING btree ("session_id");

packages/db/migrations/0012_pet_photo.sql

@@ -0,0 +1,5 @@
+-- Add photo storage columns to pets table
+-- Ref: GitHub #93
+
+ALTER TABLE "pets" ADD COLUMN "photo_key" text;--> statement-breakpoint
+ALTER TABLE "pets" ADD COLUMN "photo_uploaded_at" timestamp;

packages/db/migrations/0013_appointment_confirmation.sql

@@ -0,0 +1,7 @@
+ALTER TABLE appointments
+  ADD COLUMN confirmation_status TEXT NOT NULL DEFAULT 'pending',
+  ADD COLUMN confirmed_at TIMESTAMPTZ,
+  ADD COLUMN cancelled_at TIMESTAMPTZ,
+  ADD COLUMN confirmation_token TEXT UNIQUE;
+
+CREATE INDEX idx_appointments_confirmation_token ON appointments (confirmation_token) WHERE confirmation_token IS NOT NULL;

packages/db/migrations/0014_customer_notes.sql

@@ -0,0 +1,3 @@
+ALTER TABLE appointments ADD COLUMN customer_notes TEXT;
+
+CREATE INDEX idx_appointments_customer_notes ON appointments (client_id) WHERE customer_notes IS NOT NULL;

packages/db/migrations/0015_waitlist.sql

@@ -0,0 +1,20 @@
+CREATE TYPE waitlist_status AS ENUM ('active', 'notified', 'expired', 'cancelled');
+
+CREATE TABLE waitlist_entries (
+  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+  client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
+  pet_id UUID NOT NULL REFERENCES pets(id) ON DELETE CASCADE,
+  service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE,
+  preferred_date DATE NOT NULL,
+  preferred_time TIME NOT NULL,
+  status waitlist_status NOT NULL DEFAULT 'active',
+  notified_at TIMESTAMPTZ,
+  expires_at TIMESTAMPTZ,
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX idx_waitlist_client_id ON waitlist_entries (client_id);
+CREATE INDEX idx_waitlist_preferred_date ON waitlist_entries (preferred_date);
+CREATE INDEX idx_waitlist_status ON waitlist_entries (status) WHERE status = 'active';
+CREATE UNIQUE INDEX idx_waitlist_active_unique ON waitlist_entries (client_id, pet_id, service_id, preferred_date, preferred_time) WHERE status = 'active';

packages/db/migrations/0016_ical_token.sql

@@ -0,0 +1 @@
+ALTER TABLE staff ADD COLUMN ical_token TEXT UNIQUE;

packages/db/migrations/0017_better_auth_tables.sql

@@ -0,0 +1,49 @@
+-- Better-Auth required tables for session-based authentication
+CREATE TABLE "user" (
+  id TEXT PRIMARY KEY,
+  name TEXT NOT NULL,
+  email TEXT NOT NULL UNIQUE,
+  email_verified BOOLEAN NOT NULL DEFAULT false,
+  image TEXT,
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE "session" (
+  id TEXT PRIMARY KEY,
+  expires_at TIMESTAMPTZ NOT NULL,
+  token TEXT NOT NULL UNIQUE,
+  ip_address TEXT,
+  user_agent TEXT,
+  user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE "account" (
+  id TEXT PRIMARY KEY,
+  account_id TEXT NOT NULL,
+  provider_id TEXT NOT NULL,
+  user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
+  access_token TEXT,
+  refresh_token TEXT,
+  id_token TEXT,
+  access_token_expires_at TIMESTAMPTZ,
+  refresh_token_expires_at TIMESTAMPTZ,
+  scope TEXT,
+  password TEXT,
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE "verification" (
+  id TEXT PRIMARY KEY,
+  identifier TEXT NOT NULL,
+  value TEXT NOT NULL,
+  expires_at TIMESTAMPTZ NOT NULL,
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+-- Link staff records to auth identity
+ALTER TABLE staff ADD COLUMN user_id TEXT REFERENCES "user"(id) ON DELETE SET NULL;

packages/db/migrations/0018_backfill_staff_user_id.sql

@@ -0,0 +1,14 @@
+-- Backfill staff.user_id for staff records created before Better-Auth integration.
+-- Staff records that predate this migration have user_id = NULL; the resolveStaffMiddleware
+-- now falls back to staff.id (dev mode) and oidcSub (production) so these records still work.
+-- This migration populates user_id for the known demo/dev staff seeded by seed.ts.
+
+-- Create demo Better-Auth users for seeded staff (these match the ba-user-* IDs used in tests)
+INSERT INTO "user" (id, name, email, email_verified, created_at, updated_at)
+VALUES ('ba-user-manager', 'Demo Manager', 'demo-manager@groombook.dev', true, NOW(), NOW())
+ON CONFLICT (id) DO NOTHING;
+
+-- Link the demo manager staff record to the Better-Auth user
+UPDATE staff
+SET user_id = 'ba-user-manager', updated_at = NOW()
+WHERE oidc_sub = 'demo-manager-001' AND user_id IS NULL;

packages/db/migrations/0019_concerned_sunfire.sql

@@ -0,0 +1 @@
+ALTER TABLE "staff" ADD COLUMN "is_super_user" boolean DEFAULT false NOT NULL;

packages/db/migrations/0020_typical_daimon_hellstrom.sql

@@ -0,0 +1,7 @@
+-- Clean up existing duplicate services before adding unique constraint.
+-- Keep the row with the lowest id per name; delete all others.
+DELETE FROM services WHERE id NOT IN (
+  SELECT (MIN(id::text))::uuid FROM services GROUP BY name
+);
+
+ALTER TABLE "services" ADD CONSTRAINT "services_name_unique" UNIQUE("name");

packages/db/migrations/0021_pet_image.sql

@@ -0,0 +1,2 @@
+-- Add image field to pets table for demo pet image support
+ALTER TABLE "pets" ADD COLUMN "image" text;

packages/db/migrations/0022_logo_key.sql

@@ -0,0 +1,2 @@
+-- Add logo_key column to business_settings for S3-based logo storage
+ALTER TABLE "business_settings" ADD COLUMN "logo_key" text;

packages/db/migrations/0023_auth_provider_config.sql

@@ -0,0 +1,14 @@
+CREATE TABLE "auth_provider_config" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"provider_id" text NOT NULL,
+	"display_name" text NOT NULL,
+	"issuer_url" text NOT NULL,
+	"internal_base_url" text,
+	"client_id" text NOT NULL,
+	"client_secret" text NOT NULL,
+	"scopes" text DEFAULT 'openid profile email' NOT NULL,
+	"enabled" boolean DEFAULT true NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "auth_provider_config_provider_id_unique" UNIQUE("provider_id")
+);

packages/db/migrations/0024_invoice_indexes.sql

@@ -0,0 +1,5 @@
+CREATE INDEX idx_invoices_client_id ON invoices(client_id);
+CREATE INDEX idx_invoices_status ON invoices(status);
+CREATE INDEX idx_invoices_created_at ON invoices(created_at);
+CREATE INDEX idx_invoice_line_items_invoice_id ON invoice_line_items(invoice_id);
+CREATE INDEX idx_invoice_tip_splits_invoice_id ON invoice_tip_splits(invoice_id);

packages/db/migrations/0025_rate_limit.sql

@@ -0,0 +1,6 @@
+-- Better-Auth rate limiting table (GRO-574)
+CREATE TABLE "rate_limit" (
+  key TEXT NOT NULL PRIMARY KEY,
+  count INTEGER NOT NULL,
+  last_request BIGINT NOT NULL
+);

packages/db/migrations/0026_stripe_payment.sql

@@ -0,0 +1,6 @@
+ALTER TABLE "clients" ADD COLUMN "stripe_customer_id" text;
+ALTER TABLE "clients" ADD CONSTRAINT "idx_clients_stripe_customer_id" UNIQUE("stripe_customer_id");
+ALTER TABLE "invoices" ADD COLUMN "stripe_payment_intent_id" text;
+ALTER TABLE "invoices" ADD COLUMN "stripe_refund_id" text;
+ALTER TABLE "invoices" ADD COLUMN "payment_failure_reason" text;
+ALTER TABLE "invoices" ADD CONSTRAINT "idx_invoices_stripe_payment_intent_id" UNIQUE("stripe_payment_intent_id");

packages/db/migrations/0027_refunds.sql

@@ -0,0 +1,11 @@
+CREATE TABLE "refunds" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+  "invoice_id" uuid NOT NULL REFERENCES "invoices"("id") ON DELETE RESTRICT,
+  "stripe_refund_id" text NOT NULL,
+  "idempotency_key" text UNIQUE,
+  "amount_cents" integer,
+  "created_at" timestamp NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX "idx_refunds_invoice_id" ON "refunds"("invoice_id");
+CREATE INDEX "idx_refunds_idempotency_key" ON "refunds"("idempotency_key");

packages/db/migrations/0028_sms_reminders.sql

@@ -0,0 +1,15 @@
+-- SMS opt-in fields for clients (idempotent)
+ALTER TABLE "clients" ADD COLUMN IF NOT EXISTS "sms_opt_in" boolean NOT NULL DEFAULT false;
+ALTER TABLE "clients" ADD COLUMN IF NOT EXISTS "sms_consent_date" timestamp;
+ALTER TABLE "clients" ADD COLUMN IF NOT EXISTS "sms_opt_out_date" timestamp;
+ALTER TABLE "clients" ADD COLUMN IF NOT EXISTS "sms_consent_text" text;
+
+-- Add channel column to reminder_logs with default 'email' (idempotent)
+ALTER TABLE "reminder_logs" ADD COLUMN IF NOT EXISTS "channel" text NOT NULL DEFAULT 'email';
+
+-- Drop old unique constraints if they exist (idempotent)
+ALTER TABLE "reminder_logs" DROP CONSTRAINT IF EXISTS "reminder_logs_appointment_id_reminder_type_key";
+ALTER TABLE "reminder_logs" DROP CONSTRAINT IF EXISTS "reminder_logs_appointment_id_reminder_type_unique";
+
+-- Add new unique constraint with channel
+ALTER TABLE "reminder_logs" ADD CONSTRAINT "reminder_logs_appointment_id_reminder_type_channel_unique" UNIQUE ("appointment_id", "reminder_type", "channel");

packages/db/migrations/0029_db_indexes_constraints.sql

@@ -0,0 +1,20 @@
+-- Migration: 0029_db_indexes_constraints.sql
+-- Add missing indexes on appointments, pets, clients tables and NOT NULL constraint on clients.email
+
+-- Backfill NULL emails before setting NOT NULL
+UPDATE clients SET email = concat('unknown-', id::text, '@placeholder.local') WHERE email IS NULL;
+
+-- Add indexes on appointments table
+CREATE INDEX idx_appointments_client_id ON appointments(client_id);
+CREATE INDEX idx_appointments_staff_id ON appointments(staff_id);
+CREATE INDEX idx_appointments_start_time ON appointments(start_time);
+CREATE INDEX idx_appointments_status ON appointments(status);
+
+-- Add index on pets table
+CREATE INDEX idx_pets_client_id ON pets(client_id);
+
+-- Add index on clients table
+CREATE INDEX idx_clients_email ON clients(email);
+
+-- Set NOT NULL on clients.email (after backfill)
+ALTER TABLE clients ALTER COLUMN email SET NOT NULL;

packages/db/migrations/0030_messaging.sql

@@ -0,0 +1,72 @@
+-- Migration: 0030_messaging.sql
+-- Messaging schema: conversations, messages, attachments, consent events + business messaging settings
+
+-- ─── Enums ───────────────────────────────────────────────────────────────────
+
+CREATE TYPE "messaging_channel" AS ENUM ('sms', 'mms');
+CREATE TYPE "message_direction" AS ENUM ('inbound', 'outbound');
+CREATE TYPE "message_status" AS ENUM ('queued', 'sent', 'delivered', 'failed', 'received');
+CREATE TYPE "message_consent_kind" AS ENUM ('opt_in', 'opt_out', 'help');
+
+-- ─── Tables ───────────────────────────────────────────────────────────────────
+
+CREATE TABLE "conversations" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+  "business_id" uuid NOT NULL,
+  "client_id" uuid NOT NULL REFERENCES "clients"("id") ON DELETE CASCADE,
+  "channel" "messaging_channel" NOT NULL,
+  "external_number" text NOT NULL,
+  "business_number" text NOT NULL,
+  "last_message_at" timestamp,
+  "status" text NOT NULL DEFAULT 'active',
+  "created_at" timestamp NOT NULL DEFAULT now(),
+  "updated_at" timestamp NOT NULL DEFAULT now()
+);
+
+CREATE INDEX "idx_conversations_business_id_last_message_at" ON "conversations"("business_id", "last_message_at" DESC);
+CREATE UNIQUE INDEX "uq_conversations_business_client_number" ON "conversations"("business_id", "client_id", "business_number");
+
+CREATE TABLE "messages" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+  "conversation_id" uuid NOT NULL REFERENCES "conversations"("id") ON DELETE CASCADE,
+  "direction" "message_direction" NOT NULL,
+  "body" text,
+  "status" "message_status" NOT NULL DEFAULT 'queued',
+  "provider_message_id" text,
+  "error_code" text,
+  "error_message" text,
+  "sent_by_staff_id" uuid REFERENCES "staff"("id") ON DELETE SET NULL,
+  "created_at" timestamp NOT NULL DEFAULT now(),
+  "delivered_at" timestamp,
+  "read_by_client_at" timestamp
+);
+
+CREATE INDEX "idx_messages_conversation_id_created_at" ON "messages"("conversation_id", "created_at" DESC);
+CREATE UNIQUE INDEX "uq_messages_provider_message_id" ON "messages"("provider_message_id");
+
+CREATE TABLE "message_attachments" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+  "message_id" uuid NOT NULL REFERENCES "messages"("id") ON DELETE CASCADE,
+  "content_type" text NOT NULL,
+  "url" text NOT NULL,
+  "size" integer NOT NULL,
+  "provider_media_id" text
+);
+
+CREATE INDEX "idx_message_attachments_message_id" ON "message_attachments"("message_id");
+
+CREATE TABLE "message_consent_events" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+  "client_id" uuid NOT NULL REFERENCES "clients"("id") ON DELETE CASCADE,
+  "business_id" uuid NOT NULL,
+  "kind" "message_consent_kind" NOT NULL,
+  "source" text,
+  "created_at" timestamp NOT NULL DEFAULT now()
+);
+
+CREATE INDEX "idx_message_consent_events_client_id" ON "message_consent_events"("client_id");
+
+-- ─── Business Settings extensions ────────────────────────────────────────────
+
+ALTER TABLE "business_settings" ADD COLUMN "messaging_phone_number" text;
+ALTER TABLE "business_settings" ADD COLUMN "telnyx_messaging_profile_id" text;

packages/db/migrations/0031_buffer_rules.sql

@@ -0,0 +1,31 @@
+-- Migration: 0031_buffer_rules.sql
+-- Buffer rules CRUD: pet size/coat enums, bufferRules table, services.defaultBufferMinutes
+
+-- ─── Enums ───────────────────────────────────────────────────────────────────
+
+CREATE TYPE "pet_size_category" AS ENUM ('small', 'medium', 'large', 'xlarge');
+CREATE TYPE "coat_type" AS ENUM ('smooth', 'double', 'wire', 'curly', 'long', 'hairless');
+
+-- ─── Alter pets columns to use new enums ─────────────────────────────────────
+
+ALTER TABLE "pets" ALTER COLUMN "coat_type" TYPE "coat_type" USING "coat_type"::text::"coat_type";
+ALTER TABLE "pets" ALTER COLUMN "pet_size_category" TYPE "pet_size_category" USING "pet_size_category"::text::"pet_size_category";
+
+-- ─── Services: add defaultBufferMinutes ───────────────────────────────────────
+
+ALTER TABLE "services" ADD COLUMN "default_buffer_minutes" integer;
+
+-- ─── Buffer Rules table ───────────────────────────────────────────────────────
+
+CREATE TABLE "buffer_rules" (
+  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+  "service_id" uuid NOT NULL REFERENCES "services"("id") ON DELETE CASCADE,
+  "size_category" "pet_size_category",
+  "coat_type" "coat_type",
+  "buffer_minutes" integer NOT NULL,
+  "created_at" timestamp NOT NULL DEFAULT now(),
+  "updated_at" timestamp NOT NULL DEFAULT now(),
+  CONSTRAINT "uq_buffer_rules_service_size_coat" UNIQUE ("service_id", "size_category", "coat_type")
+);
+
+CREATE INDEX "idx_buffer_rules_service_id" ON "buffer_rules"("service_id");

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

@@ -0,0 +1,485 @@
+{
+  "id": "477cddf9-970f-41c5-9cad-c1ed48c2bedf",
+  "prevId": "00000000-0000-0000-0000-000000000000",
+  "version": "7",
+  "dialect": "postgresql",
+  "tables": {
+    "public.appointments": {
+      "name": "appointments",
+      "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
+        },
+        "pet_id": {
+          "name": "pet_id",
+          "type": "uuid",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "service_id": {
+          "name": "service_id",
+          "type": "uuid",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "staff_id": {
+          "name": "staff_id",
+          "type": "uuid",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "status": {
+          "name": "status",
+          "type": "appointment_status",
+          "typeSchema": "public",
+          "primaryKey": false,
+          "notNull": true,
+          "default": "'scheduled'"
+        },
+        "start_time": {
+          "name": "start_time",
+          "type": "timestamp",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "end_time": {
+          "name": "end_time",
+          "type": "timestamp",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "notes": {
+          "name": "notes",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "price_cents": {
+          "name": "price_cents",
+          "type": "integer",
+          "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": {
+        "appointments_client_id_clients_id_fk": {
+          "name": "appointments_client_id_clients_id_fk",
+          "tableFrom": "appointments",
+          "tableTo": "clients",
+          "columnsFrom": [
+            "client_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "restrict",
+          "onUpdate": "no action"
+        },
+        "appointments_pet_id_pets_id_fk": {
+          "name": "appointments_pet_id_pets_id_fk",
+          "tableFrom": "appointments",
+          "tableTo": "pets",
+          "columnsFrom": [
+            "pet_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "restrict",
+          "onUpdate": "no action"
+        },
+        "appointments_service_id_services_id_fk": {
+          "name": "appointments_service_id_services_id_fk",
+          "tableFrom": "appointments",
+          "tableTo": "services",
+          "columnsFrom": [
+            "service_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "restrict",
+          "onUpdate": "no action"
+        },
+        "appointments_staff_id_staff_id_fk": {
+          "name": "appointments_staff_id_staff_id_fk",
+          "tableFrom": "appointments",
+          "tableTo": "staff",
+          "columnsFrom": [
+            "staff_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "set null",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.clients": {
+      "name": "clients",
+      "schema": "",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "uuid",
+          "primaryKey": true,
+          "notNull": true,
+          "default": "gen_random_uuid()"
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "email": {
+          "name": "email",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "phone": {
+          "name": "phone",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "address": {
+          "name": "address",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "notes": {
+          "name": "notes",
+          "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "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
+        },
+        "grooming_notes": {
+          "name": "grooming_notes",
+          "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
+    },
+    "public.services": {
+      "name": "services",
+      "schema": "",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "uuid",
+          "primaryKey": true,
+          "notNull": true,
+          "default": "gen_random_uuid()"
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "description": {
+          "name": "description",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "base_price_cents": {
+          "name": "base_price_cents",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "duration_minutes": {
+          "name": "duration_minutes",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "active": {
+          "name": "active",
+          "type": "boolean",
+          "primaryKey": false,
+          "notNull": true,
+          "default": true
+        },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.staff": {
+      "name": "staff",
+      "schema": "",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "uuid",
+          "primaryKey": true,
+          "notNull": true,
+          "default": "gen_random_uuid()"
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "email": {
+          "name": "email",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true
+        },
+        "oidc_sub": {
+          "name": "oidc_sub",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false
+        },
+        "role": {
+          "name": "role",
+          "type": "staff_role",
+          "typeSchema": "public",
+          "primaryKey": false,
+          "notNull": true,
+          "default": "'groomer'"
+        },
+        "active": {
+          "name": "active",
+          "type": "boolean",
+          "primaryKey": false,
+          "notNull": true,
+          "default": true
+        },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {
+        "staff_email_unique": {
+          "name": "staff_email_unique",
+          "nullsNotDistinct": false,
+          "columns": [
+            "email"
+          ]
+        },
+        "staff_oidc_sub_unique": {
+          "name": "staff_oidc_sub_unique",
+          "nullsNotDistinct": false,
+          "columns": [
+            "oidc_sub"
+          ]
+        }
+      },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    }
+  },
+  "enums": {
+    "public.appointment_status": {
+      "name": "appointment_status",
+      "schema": "public",
+      "values": [
+        "scheduled",
+        "confirmed",
+        "in_progress",
+        "completed",
+        "cancelled",
+        "no_show"
+      ]
+    },
+    "public.staff_role": {
+      "name": "staff_role",
+      "schema": "public",
+      "values": [
+        "groomer",
+        "receptionist",
+        "manager"
+      ]
+    }
+  },
+  "schemas": {},
+  "sequences": {},
+  "roles": {},
+  "policies": {},
+  "views": {},
+  "_meta": {
+    "columns": {},
+    "schemas": {},
+    "tables": {}
+  }
+}

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

@@ -0,0 +1,504 @@
+{
+  "id": "b3a381ca-f7a4-450f-aa7e-fdc2d652dc97",
+  "prevId": "5983a2e9-f185-4f8a-a73f-5a7c0a0eea9c",
+  "version": "7",
+  "dialect": "postgresql",
+  "tables": {
+    "public.account": {
+      "name": "account",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "account_id": { "name": "account_id", "type": "text", "primaryKey": false, "notNull": true },
+        "provider_id": { "name": "provider_id", "type": "text", "primaryKey": false, "notNull": true },
+        "user_id": { "name": "user_id", "type": "text", "primaryKey": false, "notNull": true },
+        "access_token": { "name": "access_token", "type": "text", "primaryKey": false, "notNull": false },
+        "refresh_token": { "name": "refresh_token", "type": "text", "primaryKey": false, "notNull": false },
+        "id_token": { "name": "id_token", "type": "text", "primaryKey": false, "notNull": false },
+        "access_token_expires_at": { "name": "access_token_expires_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "refresh_token_expires_at": { "name": "refresh_token_expires_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "scope": { "name": "scope", "type": "text", "primaryKey": false, "notNull": false },
+        "password": { "name": "password", "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": { "account_user_id_user_id_fk": { "name": "account_user_id_user_id_fk", "tableFrom": "account", "tableTo": "user", "columnsFrom": ["user_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.appointment_groups": {
+      "name": "appointment_groups",
+      "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 },
+        "notes": { "name": "notes", "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": { "appointment_groups_client_id_clients_id_fk": { "name": "appointment_groups_client_id_clients_id_fk", "tableFrom": "appointment_groups", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.appointments": {
+      "name": "appointments",
+      "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 },
+        "pet_id": { "name": "pet_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "service_id": { "name": "service_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "bather_staff_id": { "name": "bather_staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "status": { "name": "status", "type": "appointment_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'scheduled'" },
+        "start_time": { "name": "start_time", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "end_time": { "name": "end_time", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "notes": { "name": "notes", "type": "text", "primaryKey": false, "notNull": false },
+        "price_cents": { "name": "price_cents", "type": "integer", "primaryKey": false, "notNull": false },
+        "series_id": { "name": "series_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "series_index": { "name": "series_index", "type": "integer", "primaryKey": false, "notNull": false },
+        "group_id": { "name": "group_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "confirmation_status": { "name": "confirmation_status", "type": "text", "primaryKey": false, "notNull": true, "default": "'pending'" },
+        "confirmed_at": { "name": "confirmed_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "cancelled_at": { "name": "cancelled_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "confirmation_token": { "name": "confirmation_token", "type": "text", "primaryKey": false, "notNull": false },
+        "customer_notes": { "name": "customer_notes", "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": {
+        "appointments_client_id_clients_id_fk": { "name": "appointments_client_id_clients_id_fk", "tableFrom": "appointments", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "appointments_pet_id_pets_id_fk": { "name": "appointments_pet_id_pets_id_fk", "tableFrom": "appointments", "tableTo": "pets", "columnsFrom": ["pet_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "appointments_service_id_services_id_fk": { "name": "appointments_service_id_services_id_fk", "tableFrom": "appointments", "tableTo": "services", "columnsFrom": ["service_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "appointments_staff_id_staff_id_fk": { "name": "appointments_staff_id_staff_id_fk", "tableFrom": "appointments", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "appointments_bather_staff_id_staff_id_fk": { "name": "appointments_bather_staff_id_staff_id_fk", "tableFrom": "appointments", "tableTo": "staff", "columnsFrom": ["bather_staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "appointments_series_id_recurring_series_id_fk": { "name": "appointments_series_id_recurring_series_id_fk", "tableFrom": "appointments", "tableTo": "recurring_series", "columnsFrom": ["series_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "appointments_group_id_appointment_groups_id_fk": { "name": "appointments_group_id_appointment_groups_id_fk", "tableFrom": "appointments", "tableTo": "appointment_groups", "columnsFrom": ["group_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "appointments_confirmation_token_unique": { "name": "appointments_confirmation_token_unique", "nullsNotDistinct": false, "columns": ["confirmation_token"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.business_settings": {
+      "name": "business_settings",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "business_name": { "name": "business_name", "type": "text", "primaryKey": false, "notNull": true, "default": "'GroomBook'" },
+        "logo_base64": { "name": "logo_base64", "type": "text", "primaryKey": false, "notNull": false },
+        "logo_mime_type": { "name": "logo_mime_type", "type": "text", "primaryKey": false, "notNull": false },
+        "primary_color": { "name": "primary_color", "type": "text", "primaryKey": false, "notNull": true, "default": "'#4f8a6f'" },
+        "accent_color": { "name": "accent_color", "type": "text", "primaryKey": false, "notNull": true, "default": "'#8b7355'" },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.clients": {
+      "name": "clients",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "email": { "name": "email", "type": "text", "primaryKey": false, "notNull": false },
+        "phone": { "name": "phone", "type": "text", "primaryKey": false, "notNull": false },
+        "address": { "name": "address", "type": "text", "primaryKey": false, "notNull": false },
+        "notes": { "name": "notes", "type": "text", "primaryKey": false, "notNull": false },
+        "email_opt_out": { "name": "email_opt_out", "type": "boolean", "primaryKey": false, "notNull": true, "default": false },
+        "status": { "name": "status", "type": "client_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'active'" },
+        "disabled_at": { "name": "disabled_at", "type": "timestamp", "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.grooming_visit_logs": {
+      "name": "grooming_visit_logs",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "pet_id": { "name": "pet_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "appointment_id": { "name": "appointment_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "cut_style": { "name": "cut_style", "type": "text", "primaryKey": false, "notNull": false },
+        "products_used": { "name": "products_used", "type": "text", "primaryKey": false, "notNull": false },
+        "notes": { "name": "notes", "type": "text", "primaryKey": false, "notNull": false },
+        "groomed_at": { "name": "groomed_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {
+        "grooming_visit_logs_pet_id_pets_id_fk": { "name": "grooming_visit_logs_pet_id_pets_id_fk", "tableFrom": "grooming_visit_logs", "tableTo": "pets", "columnsFrom": ["pet_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "grooming_visit_logs_appointment_id_appointments_id_fk": { "name": "grooming_visit_logs_appointment_id_appointments_id_fk", "tableFrom": "grooming_visit_logs", "tableTo": "appointments", "columnsFrom": ["appointment_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "grooming_visit_logs_staff_id_staff_id_fk": { "name": "grooming_visit_logs_staff_id_staff_id_fk", "tableFrom": "grooming_visit_logs", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.impersonation_audit_logs": {
+      "name": "impersonation_audit_logs",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "session_id": { "name": "session_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "action": { "name": "action", "type": "text", "primaryKey": false, "notNull": true },
+        "page_visited": { "name": "page_visited", "type": "text", "primaryKey": false, "notNull": false },
+        "metadata": { "name": "metadata", "type": "jsonb", "primaryKey": false, "notNull": false },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": { "impersonation_audit_logs_session_id_idx": { "name": "impersonation_audit_logs_session_id_idx", "columns": [{ "expression": "session_id", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} } },
+      "foreignKeys": { "impersonation_audit_logs_session_id_impersonation_sessions_id_fk": { "name": "impersonation_audit_logs_session_id_impersonation_sessions_id_fk", "tableFrom": "impersonation_audit_logs", "tableTo": "impersonation_sessions", "columnsFrom": ["session_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.impersonation_sessions": {
+      "name": "impersonation_sessions",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "client_id": { "name": "client_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "reason": { "name": "reason", "type": "text", "primaryKey": false, "notNull": false },
+        "status": { "name": "status", "type": "impersonation_session_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'active'" },
+        "started_at": { "name": "started_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" },
+        "ended_at": { "name": "ended_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {
+        "impersonation_sessions_staff_id_status_idx": { "name": "impersonation_sessions_staff_id_status_idx", "columns": [{ "expression": "staff_id", "isExpression": false, "asc": true, "nulls": "last" }, { "expression": "status", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} },
+        "impersonation_sessions_client_id_idx": { "name": "impersonation_sessions_client_id_idx", "columns": [{ "expression": "client_id", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} }
+      },
+      "foreignKeys": {
+        "impersonation_sessions_staff_id_staff_id_fk": { "name": "impersonation_sessions_staff_id_staff_id_fk", "tableFrom": "impersonation_sessions", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "impersonation_sessions_client_id_clients_id_fk": { "name": "impersonation_sessions_client_id_clients_id_fk", "tableFrom": "impersonation_sessions", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.invoice_line_items": {
+      "name": "invoice_line_items",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "invoice_id": { "name": "invoice_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "description": { "name": "description", "type": "text", "primaryKey": false, "notNull": true },
+        "quantity": { "name": "quantity", "type": "integer", "primaryKey": false, "notNull": true, "default": 1 },
+        "unit_price_cents": { "name": "unit_price_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "total_cents": { "name": "total_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": { "invoice_line_items_invoice_id_invoices_id_fk": { "name": "invoice_line_items_invoice_id_invoices_id_fk", "tableFrom": "invoice_line_items", "tableTo": "invoices", "columnsFrom": ["invoice_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.invoice_tip_splits": {
+      "name": "invoice_tip_splits",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "invoice_id": { "name": "invoice_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "staff_name": { "name": "staff_name", "type": "text", "primaryKey": false, "notNull": true },
+        "share_pct": { "name": "share_pct", "type": "numeric(5, 2)", "primaryKey": false, "notNull": true },
+        "share_cents": { "name": "share_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {
+        "invoice_tip_splits_invoice_id_invoices_id_fk": { "name": "invoice_tip_splits_invoice_id_invoices_id_fk", "tableFrom": "invoice_tip_splits", "tableTo": "invoices", "columnsFrom": ["invoice_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "invoice_tip_splits_staff_id_staff_id_fk": { "name": "invoice_tip_splits_staff_id_staff_id_fk", "tableFrom": "invoice_tip_splits", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.invoices": {
+      "name": "invoices",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "appointment_id": { "name": "appointment_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "client_id": { "name": "client_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "subtotal_cents": { "name": "subtotal_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "tax_cents": { "name": "tax_cents", "type": "integer", "primaryKey": false, "notNull": true, "default": 0 },
+        "tip_cents": { "name": "tip_cents", "type": "integer", "primaryKey": false, "notNull": true, "default": 0 },
+        "total_cents": { "name": "total_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "status": { "name": "status", "type": "invoice_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'draft'" },
+        "payment_method": { "name": "payment_method", "type": "payment_method", "typeSchema": "public", "primaryKey": false, "notNull": false },
+        "paid_at": { "name": "paid_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "notes": { "name": "notes", "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": {
+        "invoices_appointment_id_appointments_id_fk": { "name": "invoices_appointment_id_appointments_id_fk", "tableFrom": "invoices", "tableTo": "appointments", "columnsFrom": ["appointment_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "invoices_client_id_clients_id_fk": { "name": "invoices_client_id_clients_id_fk", "tableFrom": "invoices", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "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 },
+        "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
+    },
+    "public.recurring_series": {
+      "name": "recurring_series",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "frequency_weeks": { "name": "frequency_weeks", "type": "integer", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.reminder_logs": {
+      "name": "reminder_logs",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "appointment_id": { "name": "appointment_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "reminder_type": { "name": "reminder_type", "type": "text", "primaryKey": false, "notNull": true },
+        "sent_at": { "name": "sent_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": { "reminder_logs_appointment_id_appointments_id_fk": { "name": "reminder_logs_appointment_id_appointments_id_fk", "tableFrom": "reminder_logs", "tableTo": "appointments", "columnsFrom": ["appointment_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "reminder_logs_appointment_id_reminder_type_unique": { "name": "reminder_logs_appointment_id_reminder_type_unique", "nullsNotDistinct": false, "columns": ["appointment_id", "reminder_type"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.services": {
+      "name": "services",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "description": { "name": "description", "type": "text", "primaryKey": false, "notNull": false },
+        "base_price_cents": { "name": "base_price_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "duration_minutes": { "name": "duration_minutes", "type": "integer", "primaryKey": false, "notNull": true },
+        "active": { "name": "active", "type": "boolean", "primaryKey": false, "notNull": true, "default": true },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "services_name_unique": { "name": "services_name_unique", "nullsNotDistinct": false, "columns": ["name"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.session": {
+      "name": "session",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "token": { "name": "token", "type": "text", "primaryKey": false, "notNull": true },
+        "ip_address": { "name": "ip_address", "type": "text", "primaryKey": false, "notNull": false },
+        "user_agent": { "name": "user_agent", "type": "text", "primaryKey": false, "notNull": false },
+        "user_id": { "name": "user_id", "type": "text", "primaryKey": false, "notNull": true },
+        "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": { "session_user_id_user_id_fk": { "name": "session_user_id_user_id_fk", "tableFrom": "session", "tableTo": "user", "columnsFrom": ["user_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "session_token_unique": { "name": "session_token_unique", "nullsNotDistinct": false, "columns": ["token"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.staff": {
+      "name": "staff",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "email": { "name": "email", "type": "text", "primaryKey": false, "notNull": true },
+        "oidc_sub": { "name": "oidc_sub", "type": "text", "primaryKey": false, "notNull": false },
+        "user_id": { "name": "user_id", "type": "text", "primaryKey": false, "notNull": false },
+        "role": { "name": "role", "type": "staff_role", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'groomer'" },
+        "is_super_user": { "name": "is_super_user", "type": "boolean", "primaryKey": false, "notNull": true, "default": false },
+        "active": { "name": "active", "type": "boolean", "primaryKey": false, "notNull": true, "default": true },
+        "ical_token": { "name": "ical_token", "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": { "staff_user_id_user_id_fk": { "name": "staff_user_id_user_id_fk", "tableFrom": "staff", "tableTo": "user", "columnsFrom": ["user_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {
+        "staff_email_unique": { "name": "staff_email_unique", "nullsNotDistinct": false, "columns": ["email"] },
+        "staff_oidc_sub_unique": { "name": "staff_oidc_sub_unique", "nullsNotDistinct": false, "columns": ["oidc_sub"] },
+        "staff_ical_token_unique": { "name": "staff_ical_token_unique", "nullsNotDistinct": false, "columns": ["ical_token"] }
+      },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.user": {
+      "name": "user",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "email": { "name": "email", "type": "text", "primaryKey": false, "notNull": true },
+        "email_verified": { "name": "email_verified", "type": "boolean", "primaryKey": false, "notNull": true, "default": 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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "user_email_unique": { "name": "user_email_unique", "nullsNotDistinct": false, "columns": ["email"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.verification": {
+      "name": "verification",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "identifier": { "name": "identifier", "type": "text", "primaryKey": false, "notNull": true },
+        "value": { "name": "value", "type": "text", "primaryKey": false, "notNull": true },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.waitlist_entries": {
+      "name": "waitlist_entries",
+      "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 },
+        "pet_id": { "name": "pet_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "service_id": { "name": "service_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "preferred_date": { "name": "preferred_date", "type": "text", "primaryKey": false, "notNull": true },
+        "preferred_time": { "name": "preferred_time", "type": "text", "primaryKey": false, "notNull": true },
+        "status": { "name": "status", "type": "waitlist_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'active'" },
+        "notified_at": { "name": "notified_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "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": {
+        "idx_waitlist_client_id": { "name": "idx_waitlist_client_id", "columns": [{ "expression": "client_id", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} },
+        "idx_waitlist_preferred_date": { "name": "idx_waitlist_preferred_date", "columns": [{ "expression": "preferred_date", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} },
+        "idx_waitlist_status": { "name": "idx_waitlist_status", "columns": [{ "expression": "status", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} }
+      },
+      "foreignKeys": {
+        "waitlist_entries_client_id_clients_id_fk": { "name": "waitlist_entries_client_id_clients_id_fk", "tableFrom": "waitlist_entries", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "waitlist_entries_pet_id_pets_id_fk": { "name": "waitlist_entries_pet_id_pets_id_fk", "tableFrom": "waitlist_entries", "tableTo": "pets", "columnsFrom": ["pet_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "waitlist_entries_service_id_services_id_fk": { "name": "waitlist_entries_service_id_services_id_fk", "tableFrom": "waitlist_entries", "tableTo": "services", "columnsFrom": ["service_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    }
+  },
+  "enums": {
+    "public.appointment_status": { "name": "appointment_status", "schema": "public", "values": ["scheduled", "confirmed", "in_progress", "completed", "cancelled", "no_show"] },
+    "public.client_status": { "name": "client_status", "schema": "public", "values": ["active", "disabled"] },
+    "public.impersonation_session_status": { "name": "impersonation_session_status", "schema": "public", "values": ["active", "ended", "expired"] },
+    "public.invoice_status": { "name": "invoice_status", "schema": "public", "values": ["draft", "pending", "paid", "void"] },
+    "public.payment_method": { "name": "payment_method", "schema": "public", "values": ["cash", "card", "check", "other"] },
+    "public.staff_role": { "name": "staff_role", "schema": "public", "values": ["groomer", "receptionist", "manager"] },
+    "public.waitlist_status": { "name": "waitlist_status", "schema": "public", "values": ["active", "notified", "expired", "cancelled"] }
+  },
+  "schemas": {},
+  "sequences": {},
+  "roles": {},
+  "policies": {},
+  "views": {},
+  "_meta": { "columns": {}, "schemas": {}, "tables": {} }
+}

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

@@ -0,0 +1,505 @@
+{
+  "id": "9e8d3f2a-1c7b-4a6d-8f0e-5c2b9a3d7e1f",
+  "prevId": "b3a381ca-f7a4-450f-aa7e-fdc2d652dc97",
+  "version": "7",
+  "dialect": "postgresql",
+  "tables": {
+    "public.account": {
+      "name": "account",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "account_id": { "name": "account_id", "type": "text", "primaryKey": false, "notNull": true },
+        "provider_id": { "name": "provider_id", "type": "text", "primaryKey": false, "notNull": true },
+        "user_id": { "name": "user_id", "type": "text", "primaryKey": false, "notNull": true },
+        "access_token": { "name": "access_token", "type": "text", "primaryKey": false, "notNull": false },
+        "refresh_token": { "name": "refresh_token", "type": "text", "primaryKey": false, "notNull": false },
+        "id_token": { "name": "id_token", "type": "text", "primaryKey": false, "notNull": false },
+        "access_token_expires_at": { "name": "access_token_expires_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "refresh_token_expires_at": { "name": "refresh_token_expires_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "scope": { "name": "scope", "type": "text", "primaryKey": false, "notNull": false },
+        "password": { "name": "password", "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": { "account_user_id_user_id_fk": { "name": "account_user_id_user_id_fk", "tableFrom": "account", "tableTo": "user", "columnsFrom": ["user_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.appointment_groups": {
+      "name": "appointment_groups",
+      "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 },
+        "notes": { "name": "notes", "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": { "appointment_groups_client_id_clients_id_fk": { "name": "appointment_groups_client_id_clients_id_fk", "tableFrom": "appointment_groups", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.appointments": {
+      "name": "appointments",
+      "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 },
+        "pet_id": { "name": "pet_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "service_id": { "name": "service_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "bather_staff_id": { "name": "bather_staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "status": { "name": "status", "type": "appointment_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'scheduled'" },
+        "start_time": { "name": "start_time", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "end_time": { "name": "end_time", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "notes": { "name": "notes", "type": "text", "primaryKey": false, "notNull": false },
+        "price_cents": { "name": "price_cents", "type": "integer", "primaryKey": false, "notNull": false },
+        "series_id": { "name": "series_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "series_index": { "name": "series_index", "type": "integer", "primaryKey": false, "notNull": false },
+        "group_id": { "name": "group_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "confirmation_status": { "name": "confirmation_status", "type": "text", "primaryKey": false, "notNull": true, "default": "'pending'" },
+        "confirmed_at": { "name": "confirmed_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "cancelled_at": { "name": "cancelled_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "confirmation_token": { "name": "confirmation_token", "type": "text", "primaryKey": false, "notNull": false },
+        "customer_notes": { "name": "customer_notes", "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": {
+        "appointments_client_id_clients_id_fk": { "name": "appointments_client_id_clients_id_fk", "tableFrom": "appointments", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "appointments_pet_id_pets_id_fk": { "name": "appointments_pet_id_pets_id_fk", "tableFrom": "appointments", "tableTo": "pets", "columnsFrom": ["pet_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "appointments_service_id_services_id_fk": { "name": "appointments_service_id_services_id_fk", "tableFrom": "appointments", "tableTo": "services", "columnsFrom": ["service_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "appointments_staff_id_staff_id_fk": { "name": "appointments_staff_id_staff_id_fk", "tableFrom": "appointments", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "appointments_bather_staff_id_staff_id_fk": { "name": "appointments_bather_staff_id_staff_id_fk", "tableFrom": "appointments", "tableTo": "staff", "columnsFrom": ["bather_staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "appointments_series_id_recurring_series_id_fk": { "name": "appointments_series_id_recurring_series_id_fk", "tableFrom": "appointments", "tableTo": "recurring_series", "columnsFrom": ["series_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "appointments_group_id_appointment_groups_id_fk": { "name": "appointments_group_id_appointment_groups_id_fk", "tableFrom": "appointments", "tableTo": "appointment_groups", "columnsFrom": ["group_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "appointments_confirmation_token_unique": { "name": "appointments_confirmation_token_unique", "nullsNotDistinct": false, "columns": ["confirmation_token"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.business_settings": {
+      "name": "business_settings",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "business_name": { "name": "business_name", "type": "text", "primaryKey": false, "notNull": true, "default": "'GroomBook'" },
+        "logo_base64": { "name": "logo_base64", "type": "text", "primaryKey": false, "notNull": false },
+        "logo_mime_type": { "name": "logo_mime_type", "type": "text", "primaryKey": false, "notNull": false },
+        "logo_key": { "name": "logo_key", "type": "text", "primaryKey": false, "notNull": false },
+        "primary_color": { "name": "primary_color", "type": "text", "primaryKey": false, "notNull": true, "default": "'#4f8a6f'" },
+        "accent_color": { "name": "accent_color", "type": "text", "primaryKey": false, "notNull": true, "default": "'#8b7355'" },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.clients": {
+      "name": "clients",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "email": { "name": "email", "type": "text", "primaryKey": false, "notNull": false },
+        "phone": { "name": "phone", "type": "text", "primaryKey": false, "notNull": false },
+        "address": { "name": "address", "type": "text", "primaryKey": false, "notNull": false },
+        "notes": { "name": "notes", "type": "text", "primaryKey": false, "notNull": false },
+        "email_opt_out": { "name": "email_opt_out", "type": "boolean", "primaryKey": false, "notNull": true, "default": false },
+        "status": { "name": "status", "type": "client_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'active'" },
+        "disabled_at": { "name": "disabled_at", "type": "timestamp", "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.grooming_visit_logs": {
+      "name": "grooming_visit_logs",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "pet_id": { "name": "pet_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "appointment_id": { "name": "appointment_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "cut_style": { "name": "cut_style", "type": "text", "primaryKey": false, "notNull": false },
+        "products_used": { "name": "products_used", "type": "text", "primaryKey": false, "notNull": false },
+        "notes": { "name": "notes", "type": "text", "primaryKey": false, "notNull": false },
+        "groomed_at": { "name": "groomed_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {
+        "grooming_visit_logs_pet_id_pets_id_fk": { "name": "grooming_visit_logs_pet_id_pets_id_fk", "tableFrom": "grooming_visit_logs", "tableTo": "pets", "columnsFrom": ["pet_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "grooming_visit_logs_appointment_id_appointments_id_fk": { "name": "grooming_visit_logs_appointment_id_appointments_id_fk", "tableFrom": "grooming_visit_logs", "tableTo": "appointments", "columnsFrom": ["appointment_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" },
+        "grooming_visit_logs_staff_id_staff_id_fk": { "name": "grooming_visit_logs_staff_id_staff_id_fk", "tableFrom": "grooming_visit_logs", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.impersonation_audit_logs": {
+      "name": "impersonation_audit_logs",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "session_id": { "name": "session_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "action": { "name": "action", "type": "text", "primaryKey": false, "notNull": true },
+        "page_visited": { "name": "page_visited", "type": "text", "primaryKey": false, "notNull": false },
+        "metadata": { "name": "metadata", "type": "jsonb", "primaryKey": false, "notNull": false },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": { "impersonation_audit_logs_session_id_idx": { "name": "impersonation_audit_logs_session_id_idx", "columns": [{ "expression": "session_id", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} } },
+      "foreignKeys": { "impersonation_audit_logs_session_id_impersonation_sessions_id_fk": { "name": "impersonation_audit_logs_session_id_impersonation_sessions_id_fk", "tableFrom": "impersonation_audit_logs", "tableTo": "impersonation_sessions", "columnsFrom": ["session_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.impersonation_sessions": {
+      "name": "impersonation_sessions",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "client_id": { "name": "client_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "reason": { "name": "reason", "type": "text", "primaryKey": false, "notNull": false },
+        "status": { "name": "status", "type": "impersonation_session_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'active'" },
+        "started_at": { "name": "started_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" },
+        "ended_at": { "name": "ended_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {
+        "impersonation_sessions_staff_id_status_idx": { "name": "impersonation_sessions_staff_id_status_idx", "columns": [{ "expression": "staff_id", "isExpression": false, "asc": true, "nulls": "last" }, { "expression": "status", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} },
+        "impersonation_sessions_client_id_idx": { "name": "impersonation_sessions_client_id_idx", "columns": [{ "expression": "client_id", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} }
+      },
+      "foreignKeys": {
+        "impersonation_sessions_staff_id_staff_id_fk": { "name": "impersonation_sessions_staff_id_staff_id_fk", "tableFrom": "impersonation_sessions", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "impersonation_sessions_client_id_clients_id_fk": { "name": "impersonation_sessions_client_id_clients_id_fk", "tableFrom": "impersonation_sessions", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.invoice_line_items": {
+      "name": "invoice_line_items",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "invoice_id": { "name": "invoice_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "description": { "name": "description", "type": "text", "primaryKey": false, "notNull": true },
+        "quantity": { "name": "quantity", "type": "integer", "primaryKey": false, "notNull": true, "default": 1 },
+        "unit_price_cents": { "name": "unit_price_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "total_cents": { "name": "total_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": { "invoice_line_items_invoice_id_invoices_id_fk": { "name": "invoice_line_items_invoice_id_invoices_id_fk", "tableFrom": "invoice_line_items", "tableTo": "invoices", "columnsFrom": ["invoice_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.invoice_tip_splits": {
+      "name": "invoice_tip_splits",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "invoice_id": { "name": "invoice_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "staff_id": { "name": "staff_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "staff_name": { "name": "staff_name", "type": "text", "primaryKey": false, "notNull": true },
+        "share_pct": { "name": "share_pct", "type": "numeric(5, 2)", "primaryKey": false, "notNull": true },
+        "share_cents": { "name": "share_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {
+        "invoice_tip_splits_invoice_id_invoices_id_fk": { "name": "invoice_tip_splits_invoice_id_invoices_id_fk", "tableFrom": "invoice_tip_splits", "tableTo": "invoices", "columnsFrom": ["invoice_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "invoice_tip_splits_staff_id_staff_id_fk": { "name": "invoice_tip_splits_staff_id_staff_id_fk", "tableFrom": "invoice_tip_splits", "tableTo": "staff", "columnsFrom": ["staff_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.invoices": {
+      "name": "invoices",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "appointment_id": { "name": "appointment_id", "type": "uuid", "primaryKey": false, "notNull": false },
+        "client_id": { "name": "client_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "subtotal_cents": { "name": "subtotal_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "tax_cents": { "name": "tax_cents", "type": "integer", "primaryKey": false, "notNull": true, "default": 0 },
+        "tip_cents": { "name": "tip_cents", "type": "integer", "primaryKey": false, "notNull": true, "default": 0 },
+        "total_cents": { "name": "total_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "status": { "name": "status", "type": "invoice_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'draft'" },
+        "payment_method": { "name": "payment_method", "type": "payment_method", "typeSchema": "public", "primaryKey": false, "notNull": false },
+        "paid_at": { "name": "paid_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "notes": { "name": "notes", "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": {
+        "invoices_appointment_id_appointments_id_fk": { "name": "invoices_appointment_id_appointments_id_fk", "tableFrom": "invoices", "tableTo": "appointments", "columnsFrom": ["appointment_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" },
+        "invoices_client_id_clients_id_fk": { "name": "invoices_client_id_clients_id_fk", "tableFrom": "invoices", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "restrict", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "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 },
+        "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
+    },
+    "public.recurring_series": {
+      "name": "recurring_series",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "frequency_weeks": { "name": "frequency_weeks", "type": "integer", "primaryKey": false, "notNull": true },
+        "created_at": { "name": "created_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.reminder_logs": {
+      "name": "reminder_logs",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "appointment_id": { "name": "appointment_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "reminder_type": { "name": "reminder_type", "type": "text", "primaryKey": false, "notNull": true },
+        "sent_at": { "name": "sent_at", "type": "timestamp", "primaryKey": false, "notNull": true, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": { "reminder_logs_appointment_id_appointments_id_fk": { "name": "reminder_logs_appointment_id_appointments_id_fk", "tableFrom": "reminder_logs", "tableTo": "appointments", "columnsFrom": ["appointment_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "reminder_logs_appointment_id_reminder_type_unique": { "name": "reminder_logs_appointment_id_reminder_type_unique", "nullsNotDistinct": false, "columns": ["appointment_id", "reminder_type"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.services": {
+      "name": "services",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "description": { "name": "description", "type": "text", "primaryKey": false, "notNull": false },
+        "base_price_cents": { "name": "base_price_cents", "type": "integer", "primaryKey": false, "notNull": true },
+        "duration_minutes": { "name": "duration_minutes", "type": "integer", "primaryKey": false, "notNull": true },
+        "active": { "name": "active", "type": "boolean", "primaryKey": false, "notNull": true, "default": true },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "services_name_unique": { "name": "services_name_unique", "nullsNotDistinct": false, "columns": ["name"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.session": {
+      "name": "session",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "token": { "name": "token", "type": "text", "primaryKey": false, "notNull": true },
+        "ip_address": { "name": "ip_address", "type": "text", "primaryKey": false, "notNull": false },
+        "user_agent": { "name": "user_agent", "type": "text", "primaryKey": false, "notNull": false },
+        "user_id": { "name": "user_id", "type": "text", "primaryKey": false, "notNull": true },
+        "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": { "session_user_id_user_id_fk": { "name": "session_user_id_user_id_fk", "tableFrom": "session", "tableTo": "user", "columnsFrom": ["user_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "session_token_unique": { "name": "session_token_unique", "nullsNotDistinct": false, "columns": ["token"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.staff": {
+      "name": "staff",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "notNull": true, "default": "gen_random_uuid()" },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "email": { "name": "email", "type": "text", "primaryKey": false, "notNull": true },
+        "oidc_sub": { "name": "oidc_sub", "type": "text", "primaryKey": false, "notNull": false },
+        "user_id": { "name": "user_id", "type": "text", "primaryKey": false, "notNull": false },
+        "role": { "name": "role", "type": "staff_role", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'groomer'" },
+        "is_super_user": { "name": "is_super_user", "type": "boolean", "primaryKey": false, "notNull": true, "default": false },
+        "active": { "name": "active", "type": "boolean", "primaryKey": false, "notNull": true, "default": true },
+        "ical_token": { "name": "ical_token", "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": { "staff_user_id_user_id_fk": { "name": "staff_user_id_user_id_fk", "tableFrom": "staff", "tableTo": "user", "columnsFrom": ["user_id"], "columnsTo": ["id"], "onDelete": "set null", "onUpdate": "no action" } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {
+        "staff_email_unique": { "name": "staff_email_unique", "nullsNotDistinct": false, "columns": ["email"] },
+        "staff_oidc_sub_unique": { "name": "staff_oidc_sub_unique", "nullsNotDistinct": false, "columns": ["oidc_sub"] },
+        "staff_ical_token_unique": { "name": "staff_ical_token_unique", "nullsNotDistinct": false, "columns": ["ical_token"] }
+      },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.user": {
+      "name": "user",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "name": { "name": "name", "type": "text", "primaryKey": false, "notNull": true },
+        "email": { "name": "email", "type": "text", "primaryKey": false, "notNull": true },
+        "email_verified": { "name": "email_verified", "type": "boolean", "primaryKey": false, "notNull": true, "default": 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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "user_email_unique": { "name": "user_email_unique", "nullsNotDistinct": false, "columns": ["email"] } },
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.verification": {
+      "name": "verification",
+      "schema": "",
+      "columns": {
+        "id": { "name": "id", "type": "text", "primaryKey": true, "notNull": true },
+        "identifier": { "name": "identifier", "type": "text", "primaryKey": false, "notNull": true },
+        "value": { "name": "value", "type": "text", "primaryKey": false, "notNull": true },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "primaryKey": false, "notNull": true },
+        "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": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    },
+    "public.waitlist_entries": {
+      "name": "waitlist_entries",
+      "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 },
+        "pet_id": { "name": "pet_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "service_id": { "name": "service_id", "type": "uuid", "primaryKey": false, "notNull": true },
+        "preferred_date": { "name": "preferred_date", "type": "text", "primaryKey": false, "notNull": true },
+        "preferred_time": { "name": "preferred_time", "type": "text", "primaryKey": false, "notNull": true },
+        "status": { "name": "status", "type": "waitlist_status", "typeSchema": "public", "primaryKey": false, "notNull": true, "default": "'active'" },
+        "notified_at": { "name": "notified_at", "type": "timestamp", "primaryKey": false, "notNull": false },
+        "expires_at": { "name": "expires_at", "type": "timestamp", "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": {
+        "idx_waitlist_client_id": { "name": "idx_waitlist_client_id", "columns": [{ "expression": "client_id", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} },
+        "idx_waitlist_preferred_date": { "name": "idx_waitlist_preferred_date", "columns": [{ "expression": "preferred_date", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} },
+        "idx_waitlist_status": { "name": "idx_waitlist_status", "columns": [{ "expression": "status", "isExpression": false, "asc": true, "nulls": "last" }], "isUnique": false, "concurrently": false, "method": "btree", "with": {} }
+      },
+      "foreignKeys": {
+        "waitlist_entries_client_id_clients_id_fk": { "name": "waitlist_entries_client_id_clients_id_fk", "tableFrom": "waitlist_entries", "tableTo": "clients", "columnsFrom": ["client_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "waitlist_entries_pet_id_pets_id_fk": { "name": "waitlist_entries_pet_id_pets_id_fk", "tableFrom": "waitlist_entries", "tableTo": "pets", "columnsFrom": ["pet_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" },
+        "waitlist_entries_service_id_services_id_fk": { "name": "waitlist_entries_service_id_services_id_fk", "tableFrom": "waitlist_entries", "tableTo": "services", "columnsFrom": ["service_id"], "columnsTo": ["id"], "onDelete": "cascade", "onUpdate": "no action" }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {},
+      "policies": {},
+      "checkConstraints": {},
+      "isRLSEnabled": false
+    }
+  },
+  "enums": {
+    "public.appointment_status": { "name": "appointment_status", "schema": "public", "values": ["scheduled", "confirmed", "in_progress", "completed", "cancelled", "no_show"] },
+    "public.client_status": { "name": "client_status", "schema": "public", "values": ["active", "disabled"] },
+    "public.impersonation_session_status": { "name": "impersonation_session_status", "schema": "public", "values": ["active", "ended", "expired"] },
+    "public.invoice_status": { "name": "invoice_status", "schema": "public", "values": ["draft", "pending", "paid", "void"] },
+    "public.payment_method": { "name": "payment_method", "schema": "public", "values": ["cash", "card", "check", "other"] },
+    "public.staff_role": { "name": "staff_role", "schema": "public", "values": ["groomer", "receptionist", "manager"] },
+    "public.waitlist_status": { "name": "waitlist_status", "schema": "public", "values": ["active", "notified", "expired", "cancelled"] }
+  },
+  "schemas": {},
+  "sequences": {},
+  "roles": {},
+  "policies": {},
+  "views": {},
+  "_meta": { "columns": {}, "schemas": {}, "tables": {} }
+}