fix(test): fix petsExtendedFields vi.mock hoisting and invalid UUIDs

Two pre-existing bugs prevented petsExtendedFields.test.ts from running:

1. vi.mock factory referenced bare `and`, `eq`, `exists`, `or` variables
   that are undefined at hoist time — replaced with inline mock functions
2. CLIENT_ID/PET_ID used non-UUID strings but Zod schema requires uuid()

All 36 test files (521 tests) now pass.

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

.gitea/workflows/ci.yml

@@ -0,0 +1,99 @@
+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: 20
+          cache: pnpm
+
+      - name: Install dependencies
+        run: pnpm install --frozen-lockfile
+
+      - name: Typecheck
+        run: pnpm typecheck
+
+      - name: Lint
+        run: pnpm 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: 20
+          cache: pnpm
+
+      - name: Install dependencies
+        run: pnpm install --frozen-lockfile
+
+      - name: Run tests
+        run: pnpm test
+
+  docker:
+    name: Build & Push Docker Image
+    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
+          push: true
+          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

.github/workflows/ci.yml

@@ -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

@@ -2,37 +2,51 @@ FROM node:20-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/
+RUN pnpm --filter @groombook/types build && \
+    pnpm --filter @groombook/db build && \
+    pnpm --filter @groombook/api build
 
+# Runtime
 FROM node:20-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", "db:migrate"]
 
+# Seed stage — populates the database with test data
 FROM builder AS seed
-CMD ["pnpm", "--filter", "@groombook/api", "db:seed"]
+CMD ["pnpm", "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", "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

@@ -28,6 +28,12 @@ GroomBook API is a Hono-based REST service (TypeScript/Node.js) powering the pet
 | 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.2 | Session persistence | Make authenticated request, verify session token valid | 200 OK, request succeeds |
 | TC-API-1.3 | Logout | Call logout endpoint, verify token invalidated | 200 OK, subsequent requests return 401 |
+| 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 |
 
 ### 4.2 Client Management
 

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

@@ -21,8 +21,8 @@ const MANAGER: StaffRow = {
 
 // ─── Mutable mock state ───────────────────────────────────────────────────────
 
-const CLIENT_ID = "client-uuid-extended";
-const PET_ID = "pet-uuid-extended";
+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>[] = [];
@@ -163,10 +163,10 @@ vi.mock("../db", () => {
     }),
     pets,
     appointments,
-    and,
-    eq,
-    exists,
-    or,
+    and: (...conds: unknown[]) => conds,
+    eq: (col: unknown, val: unknown) => ({ col, val }),
+    exists: (q: unknown) => q,
+    or: (...conds: unknown[]) => conds,
   };
 });
 

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/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;
@@ -516,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.
@@ -1105,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,7 +22,7 @@ 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";

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 } from "../db/index.js";
 
 export type StaffRole = "groomer" | "receptionist" | "manager";
 export type StaffRow = typeof staff.$inferSelect;

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/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,

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();
 

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(

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,41 @@
   "version": "0.0.1",
   "private": true,
   "type": "module",
+  "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/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": {} }
+}

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

@@ -0,0 +1,103 @@
+{
+  "id": "0026_stripe_payment",
+  "version": "7",
+  "dialect": "postgresql",
+  "tables": {
+    "authProviderConfig": {
+      "name": "auth_provider_config",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "providerId": { "name": "provider_id", "type": "text", "isNullable": false },
+        "displayName": { "name": "display_name", "type": "text", "isNullable": false },
+        "issuerUrl": { "name": "issuer_url", "type": "text", "isNullable": false },
+        "internalBaseUrl": { "name": "internal_base_url", "type": "text", "isNullable": true },
+        "clientId": { "name": "client_id", "type": "text", "isNullable": false },
+        "clientSecret": { "name": "client_secret", "type": "text", "isNullable": false },
+        "scopes": { "name": "scopes", "type": "text", "isNullable": false, "default": "'openid profile email'" },
+        "enabled": { "name": "enabled", "type": "boolean", "isNullable": false, "default": "true" },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {}
+    },
+    "businessSettings": {
+      "name": "business_settings",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "businessName": { "name": "business_name", "type": "text", "isNullable": false, "default": "'GroomBook'" },
+        "logoBase64": { "name": "logo_base64", "type": "text", "isNullable": true },
+        "logoMimeType": { "name": "logo_mime_type", "type": "text", "isNullable": true },
+        "logoKey": { "name": "logo_key", "type": "text", "isNullable": true },
+        "primaryColor": { "name": "primary_color", "type": "text", "isNullable": false, "default": "'#4f8a6f'" },
+        "accentColor": { "name": "accent_color", "type": "text", "isNullable": false, "default": "'#8b7355'" },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {}
+    },
+    "clients": {
+      "name": "clients",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "name": { "name": "name", "type": "text", "isNullable": false },
+        "email": { "name": "email", "type": "text", "isNullable": true },
+        "phone": { "name": "phone", "type": "text", "isNullable": true },
+        "address": { "name": "address", "type": "text", "isNullable": true },
+        "notes": { "name": "notes", "type": "text", "isNullable": true },
+        "emailOptOut": { "name": "email_opt_out", "type": "boolean", "isNullable": false, "default": "false" },
+        "smsOptIn": { "name": "sms_opt_in", "type": "boolean", "isNullable": false, "default": "false" },
+        "smsConsentDate": { "name": "sms_consent_date", "type": "timestamp", "isNullable": true },
+        "smsOptOutDate": { "name": "sms_opt_out_date", "type": "timestamp", "isNullable": true },
+        "smsConsentText": { "name": "sms_consent_text", "type": "text", "isNullable": true },
+        "stripeCustomerId": { "name": "stripe_customer_id", "type": "text", "isNullable": true },
+        "status": { "name": "status", "type": "client_status", "isNullable": false, "default": "'active'" },
+        "disabledAt": { "name": "disabled_at", "type": "timestamp", "isNullable": true },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "idx_clients_stripe_customer_id": { "columns": ["stripe_customer_id"] } }
+    },
+    "invoices": {
+      "name": "invoices",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "appointmentId": { "name": "appointment_id", "type": "uuid", "isNullable": true },
+        "clientId": { "name": "client_id", "type": "uuid", "isNullable": false },
+        "subtotalCents": { "name": "subtotal_cents", "type": "integer", "isNullable": false },
+        "taxCents": { "name": "tax_cents", "type": "integer", "isNullable": false, "default": "0" },
+        "tipCents": { "name": "tip_cents", "type": "integer", "isNullable": false, "default": "0" },
+        "totalCents": { "name": "total_cents", "type": "integer", "isNullable": false },
+        "status": { "name": "status", "type": "invoice_status", "isNullable": false, "default": "'draft'" },
+        "paymentMethod": { "name": "payment_method", "type": "payment_method", "isNullable": true },
+        "paidAt": { "name": "paid_at", "type": "timestamp", "isNullable": true },
+        "stripePaymentIntentId": { "name": "stripe_payment_intent_id", "type": "text", "isNullable": true },
+        "stripeRefundId": { "name": "stripe_refund_id", "type": "text", "isNullable": true },
+        "paymentFailureReason": { "name": "payment_failure_reason", "type": "text", "isNullable": true },
+        "notes": { "name": "notes", "type": "text", "isNullable": true },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": { "idx_invoices_client_id": { "columns": ["client_id"] }, "idx_invoices_status": { "columns": ["status"] }, "idx_invoices_created_at": { "columns": ["created_at"] } },
+      "foreignKeys": { "invoices_appointment_id_fkey": { "columns": ["appointmentId"], "reference": { "table": "appointments", "columns": ["id"] } }, "invoices_client_id_fkey": { "columns": ["clientId"], "reference": { "table": "clients", "columns": ["id"] } } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "idx_invoices_stripe_payment_intent_id": { "columns": ["stripe_payment_intent_id"] } }
+    }
+  },
+  "enums": {
+    "appointment_status": { "name": "appointment_status", "values": ["scheduled", "confirmed", "in_progress", "completed", "cancelled", "no_show"] },
+    "client_status": { "name": "client_status", "values": ["active", "disabled"] },
+    "impersonation_session_status": { "name": "impersonation_session_status", "values": ["active", "ended", "expired"] },
+    "invoice_status": { "name": "invoice_status", "values": ["draft", "pending", "paid", "void"] },
+    "payment_method": { "name": "payment_method", "values": ["cash", "card", "check", "other"] },
+    "staff_role": { "name": "staff_role", "values": ["groomer", "receptionist", "manager"] },
+    "waitlist_status": { "name": "waitlist_status", "values": ["active", "notified", "expired", "cancelled"] }
+  },
+  "nativeEnums": {}
+}

packages/db/migrations/meta/_journal.json

@@ -0,0 +1,223 @@
+{
+  "version": "7",
+  "dialect": "postgresql",
+  "entries": [
+    {
+      "idx": 0,
+      "version": "7",
+      "when": 1773771452946,
+      "tag": "0000_colossal_colossus",
+      "breakpoints": true
+    },
+    {
+      "idx": 1,
+      "version": "7",
+      "when": 1742241600000,
+      "tag": "0001_pet_health_alerts",
+      "breakpoints": true
+    },
+    {
+      "idx": 2,
+      "version": "7",
+      "when": 1773777600000,
+      "tag": "0002_invoices",
+      "breakpoints": true
+    },
+    {
+      "idx": 3,
+      "version": "7",
+      "when": 1742169600000,
+      "tag": "0003_recurring_series",
+      "breakpoints": true
+    },
+    {
+      "idx": 4,
+      "version": "7",
+      "when": 1773779939000,
+      "tag": "0004_reminder_logs",
+      "breakpoints": true
+    },
+    {
+      "idx": 5,
+      "version": "7",
+      "when": 1773783000000,
+      "tag": "0005_appointment_groups",
+      "breakpoints": true
+    },
+    {
+      "idx": 6,
+      "version": "7",
+      "when": 1773783600000,
+      "tag": "0006_pet_profile_attributes",
+      "breakpoints": true
+    },
+    {
+      "idx": 7,
+      "version": "7",
+      "when": 1773820800000,
+      "tag": "0007_tip_splitting",
+      "breakpoints": true
+    },
+    {
+      "idx": 8,
+      "version": "7",
+      "when": 1773907200000,
+      "tag": "0008_business_settings",
+      "breakpoints": true
+    },
+    {
+      "idx": 9,
+      "version": "7",
+      "when": 1773993600000,
+      "tag": "0009_client_soft_delete",
+      "breakpoints": true
+    },
+    {
+      "idx": 10,
+      "version": "7",
+      "when": 1742500800000,
+      "tag": "0010_impersonation_sessions",
+      "breakpoints": true
+    },
+    {
+      "idx": 11,
+      "version": "7",
+      "when": 1742587200000,
+      "tag": "0011_impersonation_indexes",
+      "breakpoints": true
+    },
+    {
+      "idx": 12,
+      "version": "7",
+      "when": 1774080000000,
+      "tag": "0012_pet_photo",
+      "breakpoints": true
+    },
+    {
+      "idx": 13,
+      "version": "7",
+      "when": 1774166400000,
+      "tag": "0013_appointment_confirmation",
+      "breakpoints": true
+    },
+    {
+      "idx": 14,
+      "version": "7",
+      "when": 1774252800000,
+      "tag": "0014_customer_notes",
+      "breakpoints": true
+    },
+    {
+      "idx": 15,
+      "version": "7",
+      "when": 1774339200000,
+      "tag": "0015_waitlist",
+      "breakpoints": true
+    },
+    {
+      "idx": 16,
+      "version": "7",
+      "when": 1774425600000,
+      "tag": "0016_ical_token",
+      "breakpoints": true
+    },
+    {
+      "idx": 17,
+      "version": "7",
+      "when": 1774512000000,
+      "tag": "0017_better_auth_tables",
+      "breakpoints": true
+    },
+    {
+      "idx": 18,
+      "version": "7",
+      "when": 1774598400000,
+      "tag": "0018_backfill_staff_user_id",
+      "breakpoints": true
+    },
+    {
+      "idx": 19,
+      "version": "7",
+      "when": 1774729055924,
+      "tag": "0019_concerned_sunfire",
+      "breakpoints": true
+    },
+    {
+      "idx": 20,
+      "version": "7",
+      "when": 1775050467192,
+      "tag": "0020_typical_daimon_hellstrom",
+      "breakpoints": true
+    },
+    {
+      "idx": 21,
+      "version": "7",
+      "when": 1775136867192,
+      "tag": "0021_pet_image",
+      "breakpoints": true
+    },
+    {
+      "idx": 22,
+      "version": "7",
+      "when": 1775223267192,
+      "tag": "0022_logo_key",
+      "breakpoints": true
+    },
+    {
+      "idx": 23,
+      "version": "7",
+      "when": 1775309667192,
+      "tag": "0023_auth_provider_config",
+      "breakpoints": true
+    },
+    {
+      "idx": 24,
+      "version": "7",
+      "when": 1775396067192,
+      "tag": "0024_invoice_indexes",
+      "breakpoints": true
+    },
+    {
+      "idx": 25,
+      "version": "7",
+      "when": 1775482467192,
+      "tag": "0025_rate_limit",
+      "breakpoints": true
+    },
+    {
+      "idx": 26,
+      "version": "7",
+      "when": 1775568867192,
+      "tag": "0026_stripe_payment",
+      "breakpoints": true
+    },
+    {
+      "idx": 27,
+      "version": "7",
+      "when": 1775655267192,
+      "tag": "0027_refunds",
+      "breakpoints": true
+    },
+    {
+      "idx": 28,
+      "version": "7",
+      "when": 1775741667192,
+      "tag": "0028_sms_reminders",
+      "breakpoints": true
+    },
+    {
+      "idx": 29,
+      "version": "7",
+      "when": 1775784467192,
+      "tag": "0029_db_indexes_constraints",
+      "breakpoints": true
+    },
+    {
+      "idx": 30,
+      "version": "7",
+      "when": 1775828067192,
+      "tag": "0030_messaging",
+      "breakpoints": true
+    }
+  ]
+}

packages/db/package.json

@@ -0,0 +1,38 @@
+{
+  "name": "@groombook/db",
+  "version": "0.0.1",
+  "private": true,
+  "type": "module",
+  "main": "./dist/index.js",
+  "types": "./src/index.ts",
+  "exports": {
+    ".": {
+      "default": "./dist/index.js",
+      "types": "./src/index.ts"
+    },
+    "./factories": {
+      "default": "./src/factories.ts",
+      "types": "./src/factories.ts"
+    }
+  },
+  "scripts": {
+    "build": "tsc --project .",
+    "generate": "drizzle-kit generate",
+    "migrate": "drizzle-kit migrate",
+    "seed": "tsx src/seed.ts",
+    "reset": "tsx src/reset.ts && drizzle-kit migrate && tsx src/seed.ts",
+    "studio": "drizzle-kit studio",
+    "typecheck": "tsc --noEmit"
+  },
+  "dependencies": {
+    "drizzle-orm": "^0.38.4",
+    "postgres": "^3.4.5"
+  },
+  "devDependencies": {
+    "@types/node": "^22.10.7",
+    "drizzle-kit": "^0.30.4",
+    "tsx": "^4.19.0",
+    "typescript": "^5.7.3"
+  },
+  "license": "AGPL-3.0-only"
+}

packages/db/src/crypto.ts

@@ -0,0 +1,94 @@
+import { createCipheriv, createDecipheriv, randomBytes, scryptSync } from "node:crypto";
+
+const ALGORITHM = "aes-256-gcm";
+const IV_LENGTH = 12; // 96-bit IV for GCM
+const AUTH_TAG_LENGTH = 16; // 128-bit auth tag
+const SALT_LENGTH = 16;
+
+/**
+ * Derives a 32-byte key from BETTER_AUTH_SECRET using scrypt.
+ * A unique random salt is generated per encryptSecret() call and prepended to the output.
+ */
+function deriveKey(secret: string, salt: Buffer): Buffer {
+  return scryptSync(secret, salt, 32);
+}
+
+/**
+ * Encrypts a plaintext string using AES-256-GCM.
+ * Returns a base64-encoded string in the format: salt:iv:ciphertext:authTag
+ */
+export function encryptSecret(plaintext: string): string {
+  const secret = process.env.BETTER_AUTH_SECRET;
+  if (!secret) {
+    throw new Error("BETTER_AUTH_SECRET environment variable is required");
+  }
+
+  const salt = randomBytes(SALT_LENGTH);
+  const key = deriveKey(secret, salt);
+  const iv = randomBytes(IV_LENGTH);
+
+  const cipher = createCipheriv(ALGORITHM, key, iv, {
+    authTagLength: AUTH_TAG_LENGTH,
+  });
+
+  let ciphertext = cipher.update(plaintext, "utf8");
+  ciphertext = Buffer.concat([ciphertext, cipher.final()]);
+
+  const authTag = cipher.getAuthTag();
+
+  // Format: base64(salt):base64(iv):base64(ciphertext):base64(authTag)
+  return [
+    salt.toString("base64"),
+    iv.toString("base64"),
+    ciphertext.toString("base64"),
+    authTag.toString("base64"),
+  ].join(":");
+}
+
+/**
+ * Decrypts a ciphertext string produced by encryptSecret.
+ * Supports both new format (salt:iv:ciphertext:authTag) and legacy format (iv:ciphertext:authTag).
+ */
+export function decryptSecret(encrypted: string): string {
+  const secret = process.env.BETTER_AUTH_SECRET;
+  if (!secret) {
+    throw new Error("BETTER_AUTH_SECRET environment variable is required");
+  }
+
+  const parts = encrypted.split(":");
+
+  let salt: Buffer;
+  let iv: Buffer;
+  let ciphertext: Buffer;
+  let authTag: Buffer;
+
+  if (parts.length === 4) {
+    // New format: salt:iv:ciphertext:authTag
+    salt = Buffer.from(parts[0]!, "base64");
+    iv = Buffer.from(parts[1]!, "base64");
+    ciphertext = Buffer.from(parts[2]!, "base64");
+    authTag = Buffer.from(parts[3]!, "base64");
+  } else if (parts.length === 3) {
+    // Legacy format: iv:ciphertext:authTag — use fixed package salt
+    salt = scryptSync("groombook-auth-provider-config", "", SALT_LENGTH);
+    iv = Buffer.from(parts[0]!, "base64");
+    ciphertext = Buffer.from(parts[1]!, "base64");
+    authTag = Buffer.from(parts[2]!, "base64");
+  } else {
+    throw new Error(
+      "Invalid encrypted value format: expected salt:iv:ciphertext:authTag or iv:ciphertext:authTag"
+    );
+  }
+
+  const key = deriveKey(secret, salt);
+
+  const decipher = createDecipheriv(ALGORITHM, key, iv, {
+    authTagLength: AUTH_TAG_LENGTH,
+  });
+  decipher.setAuthTag(authTag);
+
+  let plaintext = decipher.update(ciphertext);
+  plaintext = Buffer.concat([plaintext, decipher.final()]);
+
+  return plaintext.toString("utf8");
+}

packages/db/src/factories.ts

@@ -0,0 +1,159 @@
+/**
+ * Test factories — build typed in-memory entities for unit tests.
+ *
+ * Each factory returns a fully-populated object with valid defaults.
+ * Pass an overrides object to customise specific fields.
+ *
+ * IDs are generated with a deterministic counter so tests produce stable,
+ * readable values (e.g. "staff-1", "client-2") without needing crypto.
+ *
+ * Usage:
+ *   import { buildStaff, buildClient, buildPet } from "@groombook/db/factories";
+ *
+ *   const manager = buildStaff({ role: "manager" });
+ *   const client  = buildClient({ name: "Alice Smith" });
+ *   const pet     = buildPet({ clientId: client.id });
+ */
+
+import type { staff, clients, pets, services, appointments } from "./schema.js";
+
+// ── Counter-based ID factory ─────────────────────────────────────────────────
+
+const counters: Record<string, number> = {};
+
+function nextId(prefix: string): string {
+  counters[prefix] = (counters[prefix] ?? 0) + 1;
+  return `${prefix}-${counters[prefix]}`;
+}
+
+/** Reset all counters. Call in beforeEach() to keep tests independent. */
+export function resetFactoryCounters(): void {
+  for (const key of Object.keys(counters)) {
+    delete counters[key];
+  }
+}
+
+// ── Type aliases ─────────────────────────────────────────────────────────────
+
+export type StaffRow        = typeof staff.$inferSelect;
+export type ClientRow       = typeof clients.$inferSelect;
+export type PetRow          = typeof pets.$inferSelect;
+export type ServiceRow      = typeof services.$inferSelect;
+export type AppointmentRow  = typeof appointments.$inferSelect;
+
+// ── Factories ────────────────────────────────────────────────────────────────
+
+export function buildStaff(overrides: Partial<StaffRow> = {}): StaffRow {
+  const id = nextId("staff");
+  return {
+    id,
+    name: `Staff Member ${id}`,
+    email: `${id}@groombook.test`,
+    oidcSub: `oidc-${id}`,
+    userId: null,
+    role: "groomer",
+    isSuperUser: false,
+    active: true,
+    icalToken: null,
+    createdAt: new Date("2025-01-01T00:00:00Z"),
+    updatedAt: new Date("2025-01-01T00:00:00Z"),
+    ...overrides,
+  };
+}
+
+export function buildClient(overrides: Partial<ClientRow> = {}): ClientRow {
+  const id = nextId("client");
+  return {
+    id,
+    name: `Client ${id}`,
+    email: `${id}@example.com`,
+    phone: "555-0100",
+    address: "1 Main St, Springfield, CA 90000",
+    notes: null,
+    emailOptOut: false,
+    smsOptIn: false,
+    smsConsentDate: null,
+    smsOptOutDate: null,
+    smsConsentText: null,
+    stripeCustomerId: null,
+    status: "active",
+    disabledAt: null,
+    createdAt: new Date("2025-01-01T00:00:00Z"),
+    updatedAt: new Date("2025-01-01T00:00:00Z"),
+    ...overrides,
+  };
+}
+
+export function buildPet(overrides: Partial<PetRow> & { clientId: string }): PetRow {
+  const id = nextId("pet");
+  const defaults: PetRow = {
+    id,
+    clientId: overrides.clientId,
+    name: `Pet ${id}`,
+    species: "Dog",
+    breed: "Mixed Breed",
+    weightKg: "15.00",
+    dateOfBirth: new Date("2020-06-15T00:00:00Z"),
+    healthAlerts: null,
+    groomingNotes: null,
+    cutStyle: null,
+    shampooPreference: null,
+    specialCareNotes: null,
+    coatType: null,
+    petSizeCategory: null,
+    customFields: {},
+    photoKey: null,
+    photoUploadedAt: null,
+    image: null,
+    createdAt: new Date("2025-01-01T00:00:00Z"),
+    updatedAt: new Date("2025-01-01T00:00:00Z"),
+  };
+  return { ...defaults, ...overrides };
+}
+
+export function buildService(overrides: Partial<ServiceRow> = {}): ServiceRow {
+  const id = nextId("service");
+  return {
+    id,
+    name: `Service ${id}`,
+    description: "A grooming service",
+    basePriceCents: 6500,
+    durationMinutes: 60,
+    active: true,
+    createdAt: new Date("2025-01-01T00:00:00Z"),
+    updatedAt: new Date("2025-01-01T00:00:00Z"),
+    ...overrides,
+  };
+}
+
+export function buildAppointment(
+  overrides: Partial<AppointmentRow> & { clientId: string; petId: string; serviceId: string; staffId: string }
+): AppointmentRow {
+  const id = nextId("appointment");
+  const startTime = new Date("2025-06-01T10:00:00Z");
+  const endTime = new Date("2025-06-01T11:00:00Z");
+  const defaults: AppointmentRow = {
+    id,
+    clientId: overrides.clientId,
+    petId: overrides.petId,
+    serviceId: overrides.serviceId,
+    staffId: overrides.staffId,
+    batherStaffId: null,
+    seriesId: null,
+    seriesIndex: null,
+    groupId: null,
+    status: "scheduled",
+    startTime,
+    endTime,
+    notes: null,
+    priceCents: null,
+    confirmationStatus: "pending",
+    confirmedAt: null,
+    cancelledAt: null,
+    confirmationToken: null,
+    customerNotes: null,
+    createdAt: new Date("2025-01-01T00:00:00Z"),
+    updatedAt: new Date("2025-01-01T00:00:00Z"),
+  };
+  return { ...defaults, ...overrides };
+}

packages/db/src/index.ts

@@ -0,0 +1,20 @@
+import { drizzle } from "drizzle-orm/postgres-js";
+import postgres from "postgres";
+import * as schema from "./schema.js";
+
+export * from "./schema.js";
+export { encryptSecret, decryptSecret } from "./crypto.js";
+export { and, asc, desc, eq, exists, gte, gt, ilike, inArray, isNull, lt, lte, ne, or, sql } from "drizzle-orm";
+
+let _db: ReturnType<typeof drizzle> | null = null;
+
+export function getDb() {
+  if (_db) return _db;
+  const url = process.env.DATABASE_URL;
+  if (!url) throw new Error("DATABASE_URL is not set");
+  const client = postgres(url, { max: 10 });
+  _db = drizzle(client, { schema });
+  return _db;
+}
+
+export type Db = ReturnType<typeof getDb>;

packages/db/src/reset.ts

@@ -0,0 +1,70 @@
+/**
+ * reset.ts — Drop all application tables and re-run migrations + seed.
+ *
+ * Intended for local development only. Never run against production.
+ *
+ * Usage:
+ *   DATABASE_URL=postgres://... npx tsx packages/db/src/reset.ts
+ */
+
+import postgres from "postgres";
+
+async function reset() {
+  const url = process.env.DATABASE_URL;
+  if (!url) {
+    console.error("DATABASE_URL is not set");
+    process.exit(1);
+  }
+
+  if (process.env.NODE_ENV === "production" && process.env.ALLOW_RESET !== "true") {
+    console.error("[FATAL] db:reset must not be run in production without ALLOW_RESET=true.");
+    process.exit(1);
+  }
+
+  const client = postgres(url, { max: 1 });
+
+  console.log("Dropping all application tables...\n");
+
+  // Drop in dependency order (children before parents)
+  await client`
+    DO $$ DECLARE
+      r RECORD;
+    BEGIN
+      FOR r IN (
+        SELECT tablename FROM pg_tables
+        WHERE schemaname = 'public'
+      ) LOOP
+        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
+      END LOOP;
+    END $$;
+  `;
+
+  // Drop custom enums
+  await client`
+    DO $$ DECLARE
+      r RECORD;
+    BEGIN
+      FOR r IN (
+        SELECT typname FROM pg_type
+        WHERE typtype = 'e' AND typnamespace = (
+          SELECT oid FROM pg_namespace WHERE nspname = 'public'
+        )
+      ) LOOP
+        EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.typname) || ' CASCADE';
+      END LOOP;
+    END $$;
+  `;
+
+  // Drop the drizzle migrations tracking table
+  await client`DROP TABLE IF EXISTS drizzle.__drizzle_migrations CASCADE`;
+  await client`DROP SCHEMA IF EXISTS drizzle CASCADE`;
+
+  console.log("✓ All tables and enums dropped\n");
+
+  await client.end();
+}
+
+reset().catch((err) => {
+  console.error("Reset failed:", err);
+  process.exit(1);
+});

packages/db/src/schema.ts

@@ -0,0 +1,603 @@
+import {
+  boolean,
+  index,
+  integer,
+  jsonb,
+  numeric,
+  pgEnum,
+  pgTable,
+  text,
+  timestamp,
+  unique,
+  uuid,
+} from "drizzle-orm/pg-core";
+
+// ─── Enums ────────────────────────────────────────────────────────────────────
+
+export const appointmentStatusEnum = pgEnum("appointment_status", [
+  "scheduled",
+  "confirmed",
+  "in_progress",
+  "completed",
+  "cancelled",
+  "no_show",
+]);
+
+export const staffRoleEnum = pgEnum("staff_role", [
+  "groomer",
+  "receptionist",
+  "manager",
+]);
+
+export const invoiceStatusEnum = pgEnum("invoice_status", [
+  "draft",
+  "pending",
+  "paid",
+  "void",
+]);
+
+export const paymentMethodEnum = pgEnum("payment_method", [
+  "cash",
+  "card",
+  "check",
+  "other",
+]);
+
+export const clientStatusEnum = pgEnum("client_status", [
+  "active",
+  "disabled",
+]);
+
+// ─── Better-Auth Tables ──────────────────────────────────────────────────────
+
+export const user = pgTable("user", {
+  id: text("id").primaryKey(),
+  name: text("name").notNull(),
+  email: text("email").notNull().unique(),
+  emailVerified: boolean("email_verified").notNull().default(false),
+  image: text("image"),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+export const session = pgTable("session", {
+  id: text("id").primaryKey(),
+  expiresAt: timestamp("expires_at").notNull(),
+  token: text("token").notNull().unique(),
+  ipAddress: text("ip_address"),
+  userAgent: text("user_agent"),
+  userId: text("user_id")
+    .notNull()
+    .references(() => user.id, { onDelete: "cascade" }),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+export const account = pgTable("account", {
+  id: text("id").primaryKey(),
+  accountId: text("account_id").notNull(),
+  providerId: text("provider_id").notNull(),
+  userId: text("user_id")
+    .notNull()
+    .references(() => user.id, { onDelete: "cascade" }),
+  accessToken: text("access_token"),
+  refreshToken: text("refresh_token"),
+  idToken: text("id_token"),
+  accessTokenExpiresAt: timestamp("access_token_expires_at"),
+  refreshTokenExpiresAt: timestamp("refresh_token_expires_at"),
+  scope: text("scope"),
+  password: text("password"),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+export const verification = pgTable("verification", {
+  id: text("id").primaryKey(),
+  identifier: text("identifier").notNull(),
+  value: text("value").notNull(),
+  expiresAt: timestamp("expires_at").notNull(),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+// ─── Tables ───────────────────────────────────────────────────────────────────
+
+export const clients = pgTable(
+  "clients",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    name: text("name").notNull(),
+    email: text("email").notNull(),
+    phone: text("phone"),
+    address: text("address"),
+    notes: text("notes"),
+    emailOptOut: boolean("email_opt_out").notNull().default(false),
+    smsOptIn: boolean("sms_opt_in").notNull().default(false),
+    smsConsentDate: timestamp("sms_consent_date"),
+    smsOptOutDate: timestamp("sms_opt_out_date"),
+    smsConsentText: text("sms_consent_text"),
+    stripeCustomerId: text("stripe_customer_id"),
+    status: clientStatusEnum("status").notNull().default("active"),
+    disabledAt: timestamp("disabled_at"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+    updatedAt: timestamp("updated_at").notNull().defaultNow(),
+  },
+  (t) => [index("idx_clients_email").on(t.email)]
+);
+
+export const pets = pgTable(
+  "pets",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    clientId: uuid("client_id")
+      .notNull()
+      .references(() => clients.id, { onDelete: "cascade" }),
+    name: text("name").notNull(),
+    species: text("species").notNull(),
+    breed: text("breed"),
+    weightKg: numeric("weight_kg", { precision: 5, scale: 2 }),
+    dateOfBirth: timestamp("date_of_birth"),
+    healthAlerts: text("health_alerts"),
+    groomingNotes: text("grooming_notes"),
+    cutStyle: text("cut_style"),
+    shampooPreference: text("shampoo_preference"),
+    specialCareNotes: text("special_care_notes"),
+    coatType: text("coat_type"),
+    petSizeCategory: text("pet_size_category"),
+    customFields: jsonb("custom_fields").$type<Record<string, string>>().notNull().default({}),
+    photoKey: text("photo_key"),
+    photoUploadedAt: timestamp("photo_uploaded_at"),
+    image: text("image"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+    updatedAt: timestamp("updated_at").notNull().defaultNow(),
+  },
+  (t) => [index("idx_pets_client_id").on(t.clientId)]
+);
+
+export const services = pgTable("services", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  name: text("name").notNull().unique(),
+  description: text("description"),
+  basePriceCents: integer("base_price_cents").notNull(),
+  durationMinutes: integer("duration_minutes").notNull(),
+  active: boolean("active").notNull().default(true),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+export const staff = pgTable("staff", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  name: text("name").notNull(),
+  email: text("email").notNull().unique(),
+  // oidcSub links to the Authentik OIDC subject claim
+  oidcSub: text("oidc_sub").unique(),
+  // Better-Auth user ID — links staff business record to auth identity
+  userId: text("user_id").references(() => user.id, { onDelete: "set null" }),
+  role: staffRoleEnum("role").notNull().default("groomer"),
+  // Super users bypass appointment-booking restrictions and access admin panels
+  isSuperUser: boolean("is_super_user").notNull().default(false),
+  active: boolean("active").notNull().default(true),
+  // Token for iCal calendar feed subscription (no auth required)
+  icalToken: text("ical_token").unique(),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+export const recurringSeries = pgTable("recurring_series", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  // How many weeks between each appointment in the series
+  frequencyWeeks: integer("frequency_weeks").notNull(),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+});
+
+// appointmentGroups links multiple appointments from the same client visit.
+// Each pet in the group gets its own appointment row with its own groomer.
+export const appointmentGroups = pgTable("appointment_groups", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  clientId: uuid("client_id")
+    .notNull()
+    .references(() => clients.id, { onDelete: "restrict" }),
+  notes: text("notes"),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+export const appointments = pgTable(
+  "appointments",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    clientId: uuid("client_id")
+      .notNull()
+      .references(() => clients.id, { onDelete: "restrict" }),
+    petId: uuid("pet_id")
+      .notNull()
+      .references(() => pets.id, { onDelete: "restrict" }),
+    serviceId: uuid("service_id")
+      .notNull()
+      .references(() => services.id, { onDelete: "restrict" }),
+    staffId: uuid("staff_id").references(() => staff.id, {
+      onDelete: "set null",
+    }),
+    // Optional secondary staff (bather/assistant) for tip-split tracking
+    batherStaffId: uuid("bather_staff_id").references(() => staff.id, {
+      onDelete: "set null",
+    }),
+    status: appointmentStatusEnum("status").notNull().default("scheduled"),
+    startTime: timestamp("start_time").notNull(),
+    endTime: timestamp("end_time").notNull(),
+    notes: text("notes"),
+    // Override price at time of booking (null = use service base price)
+    priceCents: integer("price_cents"),
+    // Recurring series support
+    seriesId: uuid("series_id").references(() => recurringSeries.id, {
+      onDelete: "set null",
+    }),
+    seriesIndex: integer("series_index"),
+    // Multi-pet group booking: links this appointment to others in the same visit
+    groupId: uuid("group_id").references(() => appointmentGroups.id, {
+      onDelete: "set null",
+    }),
+    // Customer confirmation/cancellation tracking
+    // Values: "pending" | "confirmed" | "cancelled"
+    confirmationStatus: text("confirmation_status").notNull().default("pending"),
+    confirmedAt: timestamp("confirmed_at"),
+    cancelledAt: timestamp("cancelled_at"),
+    // Token for tokenized email confirm/cancel links (no auth required)
+    confirmationToken: text("confirmation_token").unique(),
+    // Customer-provided note visible to groomer (500 char max, editable until appointment starts)
+    customerNotes: text("customer_notes"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+    updatedAt: timestamp("updated_at").notNull().defaultNow(),
+  },
+  (t) => [
+    index("idx_appointments_client_id").on(t.clientId),
+    index("idx_appointments_staff_id").on(t.staffId),
+    index("idx_appointments_start_time").on(t.startTime),
+    index("idx_appointments_status").on(t.status),
+  ]
+);
+
+export const invoices = pgTable(
+  "invoices",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    appointmentId: uuid("appointment_id").references(() => appointments.id, {
+      onDelete: "restrict",
+    }),
+    clientId: uuid("client_id")
+      .notNull()
+      .references(() => clients.id, { onDelete: "restrict" }),
+    subtotalCents: integer("subtotal_cents").notNull(),
+    taxCents: integer("tax_cents").notNull().default(0),
+    tipCents: integer("tip_cents").notNull().default(0),
+    totalCents: integer("total_cents").notNull(),
+    status: invoiceStatusEnum("status").notNull().default("draft"),
+    paymentMethod: paymentMethodEnum("payment_method"),
+    paidAt: timestamp("paid_at"),
+    stripePaymentIntentId: text("stripe_payment_intent_id"),
+    stripeRefundId: text("stripe_refund_id"),
+    paymentFailureReason: text("payment_failure_reason"),
+    notes: text("notes"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+    updatedAt: timestamp("updated_at").notNull().defaultNow(),
+  },
+  (t) => [
+    index("idx_invoices_client_id").on(t.clientId),
+    index("idx_invoices_status").on(t.status),
+    index("idx_invoices_created_at").on(t.createdAt),
+    index("idx_invoices_stripe_payment_intent_id").on(t.stripePaymentIntentId),
+  ]
+);
+
+export const invoiceLineItems = pgTable(
+  "invoice_line_items",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    invoiceId: uuid("invoice_id")
+      .notNull()
+      .references(() => invoices.id, { onDelete: "cascade" }),
+    description: text("description").notNull(),
+    quantity: integer("quantity").notNull().default(1),
+    unitPriceCents: integer("unit_price_cents").notNull(),
+    totalCents: integer("total_cents").notNull(),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+  },
+  (t) => [index("idx_invoice_line_items_invoice_id").on(t.invoiceId)]
+);
+
+// Per-staff tip allocation calculated when an invoice is paid.
+// staff_name is snapshotted at calculation time so reports remain accurate if staff is deleted.
+export const invoiceTipSplits = pgTable(
+  "invoice_tip_splits",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    invoiceId: uuid("invoice_id")
+      .notNull()
+      .references(() => invoices.id, { onDelete: "cascade" }),
+    staffId: uuid("staff_id").references(() => staff.id, { onDelete: "set null" }),
+    staffName: text("staff_name").notNull(),
+    sharePct: numeric("share_pct", { precision: 5, scale: 2 }).notNull(),
+    shareCents: integer("share_cents").notNull(),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+  },
+  (t) => [index("idx_invoice_tip_splits_invoice_id").on(t.invoiceId)]
+);
+
+// Refund records with idempotency key support
+export const refunds = pgTable(
+  "refunds",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    invoiceId: uuid("invoice_id")
+      .notNull()
+      .references(() => invoices.id, { onDelete: "restrict" }),
+    stripeRefundId: text("stripe_refund_id").notNull(),
+    idempotencyKey: text("idempotency_key").unique(),
+    amountCents: integer("amount_cents"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+  },
+  (t) => [
+    index("idx_refunds_invoice_id").on(t.invoiceId),
+    index("idx_refunds_idempotency_key").on(t.idempotencyKey),
+  ]
+);
+
+// Tracks which reminder emails have been sent per appointment (prevents duplicates).
+// reminder_type values: "confirmation", "24h", "2h"
+// channel values: "email", "sms"
+export const reminderLogs = pgTable(
+  "reminder_logs",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    appointmentId: uuid("appointment_id")
+      .notNull()
+      .references(() => appointments.id, { onDelete: "cascade" }),
+    // "confirmation" | "24h" | "2h"
+    reminderType: text("reminder_type").notNull(),
+    // "email" | "sms"
+    channel: text("channel").notNull().default("email"),
+    sentAt: timestamp("sent_at").notNull().defaultNow(),
+  },
+  (t) => [unique().on(t.appointmentId, t.reminderType, t.channel)]
+);
+
+// ─── Impersonation ──────────────────────────────────────────────────────────
+
+export const impersonationSessionStatusEnum = pgEnum(
+  "impersonation_session_status",
+  ["active", "ended", "expired"]
+);
+
+export const impersonationSessions = pgTable(
+  "impersonation_sessions",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    staffId: uuid("staff_id")
+      .notNull()
+      .references(() => staff.id, { onDelete: "restrict" }),
+    clientId: uuid("client_id")
+      .notNull()
+      .references(() => clients.id, { onDelete: "restrict" }),
+    reason: text("reason"),
+    status: impersonationSessionStatusEnum("status")
+      .notNull()
+      .default("active"),
+    startedAt: timestamp("started_at").notNull().defaultNow(),
+    endedAt: timestamp("ended_at"),
+    expiresAt: timestamp("expires_at").notNull(),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+  },
+  (t) => [
+    index("impersonation_sessions_staff_id_status_idx").on(t.staffId, t.status),
+    index("impersonation_sessions_client_id_idx").on(t.clientId),
+  ]
+);
+
+export const impersonationAuditLogs = pgTable(
+  "impersonation_audit_logs",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    sessionId: uuid("session_id")
+      .notNull()
+      .references(() => impersonationSessions.id, { onDelete: "cascade" }),
+    action: text("action").notNull(),
+    pageVisited: text("page_visited"),
+    metadata: jsonb("metadata").$type<Record<string, unknown>>(),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+  },
+  (t) => [index("impersonation_audit_logs_session_id_idx").on(t.sessionId)]
+);
+
+// ─── Messaging ───────────────────────────────────────────────────────────────
+
+export const messagingChannelEnum = pgEnum("messaging_channel", ["sms", "mms"]);
+
+export const messageDirectionEnum = pgEnum("message_direction", [
+  "inbound",
+  "outbound",
+]);
+
+export const messageStatusEnum = pgEnum("message_status", [
+  "queued",
+  "sent",
+  "delivered",
+  "failed",
+  "received",
+]);
+
+export const messageConsentKindEnum = pgEnum("message_consent_kind", [
+  "opt_in",
+  "opt_out",
+  "help",
+]);
+
+export const conversations = pgTable(
+  "conversations",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    businessId: uuid("business_id").notNull(),
+    clientId: uuid("client_id")
+      .notNull()
+      .references(() => clients.id, { onDelete: "cascade" }),
+    channel: messagingChannelEnum("channel").notNull(),
+    externalNumber: text("external_number").notNull(),
+    businessNumber: text("business_number").notNull(),
+    lastMessageAt: timestamp("last_message_at"),
+    status: text("status").notNull().default("active"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+    updatedAt: timestamp("updated_at").notNull().defaultNow(),
+  },
+  (t) => [
+    index("idx_conversations_business_id_last_message_at").on(
+      t.businessId,
+      t.lastMessageAt.desc()
+    ),
+    unique("uq_conversations_business_client_number").on(
+      t.businessId,
+      t.clientId,
+      t.businessNumber
+    ),
+  ]
+);
+
+export const messages = pgTable(
+  "messages",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    conversationId: uuid("conversation_id")
+      .notNull()
+      .references(() => conversations.id, { onDelete: "cascade" }),
+    direction: messageDirectionEnum("direction").notNull(),
+    body: text("body"),
+    status: messageStatusEnum("status").notNull().default("queued"),
+    providerMessageId: text("provider_message_id"),
+    errorCode: text("error_code"),
+    errorMessage: text("error_message"),
+    sentByStaffId: uuid("sent_by_staff_id").references(() => staff.id, {
+      onDelete: "set null",
+    }),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+    deliveredAt: timestamp("delivered_at"),
+    readByClientAt: timestamp("read_by_client_at"),
+  },
+  (t) => [
+    index("idx_messages_conversation_id_created_at").on(
+      t.conversationId,
+      t.createdAt.desc()
+    ),
+    unique("uq_messages_provider_message_id").on(t.providerMessageId),
+  ]
+);
+
+export const messageAttachments = pgTable(
+  "message_attachments",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    messageId: uuid("message_id")
+      .notNull()
+      .references(() => messages.id, { onDelete: "cascade" }),
+    contentType: text("content_type").notNull(),
+    url: text("url").notNull(),
+    size: integer("size").notNull(),
+    providerMediaId: text("provider_media_id"),
+  },
+  (t) => [index("idx_message_attachments_message_id").on(t.messageId)]
+);
+
+export const messageConsentEvents = pgTable(
+  "message_consent_events",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    clientId: uuid("client_id")
+      .notNull()
+      .references(() => clients.id, { onDelete: "cascade" }),
+    businessId: uuid("business_id").notNull(),
+    kind: messageConsentKindEnum("kind").notNull(),
+    source: text("source"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+  },
+  (t) => [index("idx_message_consent_events_client_id").on(t.clientId)]
+);
+
+export const businessSettings = pgTable("business_settings", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  businessName: text("business_name").notNull().default("GroomBook"),
+  logoBase64: text("logo_base64"),
+  logoMimeType: text("logo_mime_type"),
+  logoKey: text("logo_key"),
+  primaryColor: text("primary_color").notNull().default("#4f8a6f"),
+  accentColor: text("accent_color").notNull().default("#8b7355"),
+  messagingPhoneNumber: text("messaging_phone_number"),
+  telnyxMessagingProfileId: text("telnyx_messaging_profile_id"),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});
+
+export const groomingVisitLogs = pgTable("grooming_visit_logs", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  petId: uuid("pet_id")
+    .notNull()
+    .references(() => pets.id, { onDelete: "cascade" }),
+  appointmentId: uuid("appointment_id").references(() => appointments.id, {
+    onDelete: "set null",
+  }),
+  staffId: uuid("staff_id").references(() => staff.id, {
+    onDelete: "set null",
+  }),
+  cutStyle: text("cut_style"),
+  productsUsed: text("products_used"),
+  notes: text("notes"),
+  groomedAt: timestamp("groomed_at").notNull().defaultNow(),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+});
+
+export const waitlistStatusEnum = pgEnum("waitlist_status", [
+  "active",
+  "notified",
+  "expired",
+  "cancelled",
+]);
+
+export const waitlistEntries = pgTable(
+  "waitlist_entries",
+  {
+    id: uuid("id").primaryKey().defaultRandom(),
+    clientId: uuid("client_id")
+      .notNull()
+      .references(() => clients.id, { onDelete: "cascade" }),
+    petId: uuid("pet_id")
+      .notNull()
+      .references(() => pets.id, { onDelete: "cascade" }),
+    serviceId: uuid("service_id")
+      .notNull()
+      .references(() => services.id, { onDelete: "cascade" }),
+    preferredDate: text("preferred_date").notNull(),
+    preferredTime: text("preferred_time").notNull(),
+    status: waitlistStatusEnum("status").notNull().default("active"),
+    notifiedAt: timestamp("notified_at"),
+    expiresAt: timestamp("expires_at"),
+    createdAt: timestamp("created_at").notNull().defaultNow(),
+    updatedAt: timestamp("updated_at").notNull().defaultNow(),
+  },
+  (t) => [
+    index("idx_waitlist_client_id").on(t.clientId),
+    index("idx_waitlist_preferred_date").on(t.preferredDate),
+    index("idx_waitlist_status").on(t.status),
+  ]
+);
+
+// ─── Auth Provider Config ──────────────────────────────────────────────────
+
+export const authProviderConfig = pgTable("auth_provider_config", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  providerId: text("provider_id").notNull().unique(), // e.g. "authentik", "okta", "entra-id"
+  displayName: text("display_name").notNull(), // shown on login button
+  issuerUrl: text("issuer_url").notNull(), // OIDC issuer/discovery URL
+  internalBaseUrl: text("internal_base_url"), // for hairpin NAT / K8s internal routing
+  clientId: text("client_id").notNull(),
+  clientSecret: text("client_secret").notNull(), // AES-256-GCM encrypted using BETTER_AUTH_SECRET
+  scopes: text("scopes").notNull().default("openid profile email"),
+  enabled: boolean("enabled").notNull().default(true),
+  createdAt: timestamp("created_at").notNull().defaultNow(),
+  updatedAt: timestamp("updated_at").notNull().defaultNow(),
+});

packages/db/tsconfig.json

@@ -0,0 +1,13 @@
+{
+  "compilerOptions": {
+    "target": "ES2022",
+    "module": "NodeNext",
+    "moduleResolution": "NodeNext",
+    "strict": true,
+    "noUncheckedIndexedAccess": true,
+    "skipLibCheck": true,
+    "outDir": "./dist",
+    "rootDir": "./src"
+  },
+  "include": ["src"]
+}

packages/types/package.json

@@ -0,0 +1,22 @@
+{
+  "name": "@groombook/types",
+  "version": "0.0.1",
+  "private": true,
+  "type": "module",
+  "main": "./dist/index.js",
+  "types": "./src/index.ts",
+  "exports": {
+    ".": {
+      "default": "./dist/index.js",
+      "types": "./src/index.ts"
+    }
+  },
+  "scripts": {
+    "build": "tsc --project .",
+    "typecheck": "tsc --noEmit"
+  },
+  "devDependencies": {
+    "typescript": "^5.7.3"
+  },
+  "license": "AGPL-3.0-only"
+}

packages/types/src/index.ts

@@ -0,0 +1,227 @@
+// Shared domain types for Groom Book
+
+export type AppointmentStatus =
+  | "scheduled"
+  | "confirmed"
+  | "in_progress"
+  | "completed"
+  | "cancelled"
+  | "no_show";
+
+export type ConfirmationStatus = "pending" | "confirmed" | "cancelled";
+
+export type ClientStatus = "active" | "disabled";
+
+export interface Client {
+  id: string;
+  name: string;
+  email: string | null;
+  phone: string | null;
+  address: string | null;
+  notes: string | null;
+  emailOptOut: boolean;
+  status: ClientStatus;
+  disabledAt: string | null;
+  createdAt: string;
+  updatedAt: string;
+}
+
+export interface Pet {
+  id: string;
+  clientId: string;
+  name: string;
+  species: string;
+  breed: string | null;
+  weightKg: number | null;
+  dateOfBirth: string | null;
+  healthAlerts: string | null;
+  groomingNotes: string | null;
+  cutStyle: string | null;
+  shampooPreference: string | null;
+  specialCareNotes: string | null;
+  coatType: string | null;
+  petSizeCategory: string | null;
+  preferredCuts: string[];
+  medicalAlerts: MedicalAlert[];
+  temperamentScore?: number;
+  temperamentFlags?: string[];
+  customFields: Record<string, string>;
+  photoKey?: string;
+  photoUploadedAt?: string;
+  createdAt: string;
+  updatedAt: string;
+}
+
+export interface GroomingVisitLog {
+  id: string;
+  petId: string;
+  appointmentId: string | null;
+  staffId: string | null;
+  cutStyle: string | null;
+  productsUsed: string | null;
+  notes: string | null;
+  groomedAt: string;
+  createdAt: string;
+}
+
+export interface Service {
+  id: string;
+  name: string;
+  description: string | null;
+  basePriceCents: number;
+  durationMinutes: number;
+  active: boolean;
+  createdAt: string;
+  updatedAt: string;
+}
+
+export interface Staff {
+  id: string;
+  name: string;
+  email: string;
+  role: "groomer" | "receptionist" | "manager";
+  isSuperUser: boolean;
+  active: boolean;
+  createdAt: string;
+  updatedAt: string;
+}
+
+export interface RecurringSeries {
+  id: string;
+  frequencyWeeks: number;
+  createdAt: string;
+}
+
+export interface AppointmentGroup {
+  id: string;
+  clientId: string;
+  notes: string | null;
+  createdAt: string;
+  updatedAt: string;
+}
+
+export interface Appointment {
+  id: string;
+  clientId: string;
+  petId: string;
+  serviceId: string;
+  staffId: string | null;
+  batherStaffId: string | null;
+  status: AppointmentStatus;
+  startTime: string;
+  endTime: string;
+  notes: string | null;
+  priceCents: number | null;
+  seriesId: string | null;
+  seriesIndex: number | null;
+  groupId: string | null;
+  confirmationStatus: ConfirmationStatus;
+  confirmedAt: string | null;
+  cancelledAt: string | null;
+  confirmationToken: string | null;
+  customerNotes: string | null;
+  createdAt: string;
+  updatedAt: string;
+}
+
+export interface InvoiceTipSplit {
+  id: string;
+  invoiceId: string;
+  staffId: string | null;
+  staffName: string;
+  sharePct: string;
+  shareCents: number;
+  createdAt: string;
+}
+
+export type InvoiceStatus = "draft" | "pending" | "paid" | "void";
+export type PaymentMethod = "cash" | "card" | "check" | "other";
+
+export interface InvoiceLineItem {
+  id: string;
+  invoiceId: string;
+  description: string;
+  quantity: number;
+  unitPriceCents: number;
+  totalCents: number;
+  createdAt: string;
+}
+
+export interface Invoice {
+  id: string;
+  appointmentId: string | null;
+  clientId: string;
+  subtotalCents: number;
+  taxCents: number;
+  tipCents: number;
+  totalCents: number;
+  status: InvoiceStatus;
+  paymentMethod: PaymentMethod | null;
+  paidAt: string | null;
+  stripePaymentIntentId: string | null;
+  stripeRefundId: string | null;
+  paymentFailureReason: string | null;
+  notes: string | null;
+  createdAt: string;
+  updatedAt: string;
+  lineItems?: InvoiceLineItem[];
+  // Transient fields populated from Stripe API (not stored in DB)
+  cardLast4?: string | null;
+  paymentStatus?: string | null;
+  tipSplits?: InvoiceTipSplit[];
+}
+
+// ─── Impersonation ──────────────────────────────────────────────────────────
+
+export type ImpersonationSessionStatus = "active" | "ended" | "expired";
+
+export interface ImpersonationSession {
+  id: string;
+  staffId: string;
+  clientId: string;
+  reason: string | null;
+  status: ImpersonationSessionStatus;
+  startedAt: string;
+  endedAt: string | null;
+  expiresAt: string;
+  createdAt: string;
+}
+
+export interface ImpersonationAuditLog {
+  id: string;
+  sessionId: string;
+  action: string;
+  pageVisited: string | null;
+  metadata: Record<string, unknown> | null;
+  createdAt: string;
+}
+
+export interface BusinessSettings {
+  id: string;
+  businessName: string;
+  logoBase64: string | null;
+  logoMimeType: string | null;
+  primaryColor: string;
+  accentColor: string;
+  createdAt: string;
+  updatedAt: string;
+}
+
+// Paginated list response
+export interface PaginatedList<T> {
+  items: T[];
+  total: number;
+  page: number;
+  pageSize: number;
+}
+
+export type AlertSeverity = "low" | "medium" | "high";
+
+export interface MedicalAlert {
+  id: string;
+  type: string;
+  description: string;
+  severity: AlertSeverity;
+}
+
+export type CoatType = "smooth" | "double" | "curly" | "wire" | "long" | "hairless";

packages/types/tsconfig.json

@@ -0,0 +1,13 @@
+{
+  "compilerOptions": {
+    "target": "ES2022",
+    "module": "NodeNext",
+    "moduleResolution": "NodeNext",
+    "strict": true,
+    "noUncheckedIndexedAccess": true,
+    "skipLibCheck": true,
+    "outDir": "./dist",
+    "rootDir": "./src"
+  },
+  "include": ["src"]
+}

pnpm-workspace.yaml

@@ -1,3 +1,2 @@
 packages:
-  - "apps/*"
   - "packages/*"

renovate.json

@@ -0,0 +1,10 @@
+{
+  "$schema": "https://docs.renovatebot.com/renovate-schema.json",
+  "extends": ["config:recommended", ":pinAllExceptPeerDependencies", "helpers:pinGitHubActionDigests"],
+  "labels": ["dependencies"],
+  "prConcurrentLimit": 5,
+  "packageRules": [
+    {"matchUpdateTypes": ["minor", "patch"], "groupName": "minor and patch dependencies", "automerge": false},
+    {"matchDepTypes": ["devDependencies"], "matchUpdateTypes": ["minor", "patch"], "automerge": true, "automergeType": "pr"}
+  ]
+}

src/__tests__/auth.test.ts

@@ -0,0 +1,152 @@
+import { describe, it, expect, vi, beforeEach, afterEach } from "vitest";
+
+// Mutable state to control mock behavior per test
+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("@groombook/db", () => {
+  const authProviderConfig = new Proxy(
+    { _name: "auth_provider_config" },
+    {
+      get(target, prop) {
+        if (prop === "_name") return "auth_provider_config";
+        if (prop === "$inferSelect") return {};
+        return { table: "auth_provider_config", column: prop };
+      },
+    }
+  );
+
+  return {
+    getDb: () => ({
+      select: () => ({
+        from: () => ({
+          where: () => ({
+            limit: () => dbSelectResult,
+            [Symbol.iterator]: function* () {
+              for (const item of dbSelectResult) yield item;
+            },
+            0: dbSelectResult[0],
+            length: dbSelectResult.length,
+          }),
+        }),
+      }),
+    }),
+    authProviderConfig,
+    eq: mockEq,
+    decryptSecret: mockDecryptSecret,
+  };
+});
+
+async function reimportAuth() {
+  vi.resetModules();
+  vi.doMock("@groombook/db", () => ({
+    getDb: () => ({
+      select: () => ({
+        from: () => ({
+          where: () => ({
+            limit: () => dbSelectResult,
+            [Symbol.iterator]: function* () {
+              for (const item of dbSelectResult) yield item;
+            },
+            0: dbSelectResult[0],
+            length: dbSelectResult.length,
+          }),
+        }),
+      }),
+    }),
+    authProviderConfig: {},
+    eq: mockEq,
+    decryptSecret: mockDecryptSecret,
+  }));
+  const mod = await import("../lib/auth.js");
+  return mod;
+}
+
+describe("auth init", () => {
+  const originalEnv = { ...process.env };
+
+  beforeEach(() => {
+    dbSelectResult = [];
+    vi.clearAllMocks();
+  });
+
+  afterEach(() => {
+    process.env = { ...originalEnv };
+  });
+
+  it("falls back to env vars when DB returns empty", async () => {
+    process.env = {
+      ...originalEnv,
+      OIDC_ISSUER: "https://issuer.example.com",
+      OIDC_CLIENT_ID: "test-client-id",
+      OIDC_CLIENT_SECRET: "test-client-secret",
+      BETTER_AUTH_SECRET: "test-secret",
+      BETTER_AUTH_URL: "http://localhost:3000",
+      NODE_ENV: "test",
+    };
+
+    const { initAuth, getAuth } = await reimportAuth();
+    await initAuth();
+    expect(getAuth()).toBeDefined();
+  });
+
+  it("uses DB config and decrypts clientSecret when DB has enabled provider", async () => {
+    const dbConfig = {
+      id: "config-id",
+      providerId: "okta",
+      displayName: "Okta",
+      issuerUrl: "https://okta.example.com",
+      internalBaseUrl: null,
+      clientId: "okta-client-id",
+      clientSecret: "encrypted:okta-secret",
+      scopes: "openid profile email",
+      enabled: true,
+      createdAt: new Date(),
+      updatedAt: new Date(),
+    };
+    dbSelectResult = [dbConfig];
+
+    process.env = {
+      ...originalEnv,
+      BETTER_AUTH_SECRET: "test-secret",
+      BETTER_AUTH_URL: "http://localhost:3000",
+      NODE_ENV: "test",
+    };
+
+    const { initAuth, getAuth } = await reimportAuth();
+    await initAuth();
+    expect(getAuth()).toBeDefined();
+    expect(mockDecryptSecret).toHaveBeenCalledWith("encrypted:okta-secret");
+  });
+
+  it("throws when BETTER_AUTH_SECRET is missing and AUTH_DISABLED is not set", async () => {
+    process.env = {
+      ...originalEnv,
+      OIDC_ISSUER: "",
+      OIDC_CLIENT_ID: "",
+      OIDC_CLIENT_SECRET: "",
+      NODE_ENV: "test",
+    };
+    delete process.env.BETTER_AUTH_SECRET;
+    delete process.env.AUTH_DISABLED;
+
+    const { initAuth } = await reimportAuth();
+    await expect(initAuth()).rejects.toThrow(
+      "[FATAL] BETTER_AUTH_SECRET environment variable is required when auth is enabled"
+    );
+  });
+
+  it("builds placeholder auth when AUTH_DISABLED=true without throwing", async () => {
+    process.env = {
+      ...originalEnv,
+      AUTH_DISABLED: "true",
+      NODE_ENV: "test",
+    };
+    delete process.env.BETTER_AUTH_SECRET;
+
+    const { initAuth, getAuth } = await reimportAuth();
+    await expect(initAuth()).resolves.toBeUndefined();
+    expect(getAuth()).toBeDefined();
+  });
+});

src/__tests__/authProvider.test.ts

@@ -0,0 +1,273 @@
+import { describe, it, expect, vi, beforeEach } from "vitest";
+import { Hono } from "hono";
+import { authProviderRouter } from "../routes/authProvider.js";
+
+// ─── Mock auth module ─────────────────────────────────────────────────────────
+
+vi.mock("../lib/auth.js", () => ({
+  reinitAuth: vi.fn().mockResolvedValue(undefined),
+}));
+
+// ─── Types ────────────────────────────────────────────────────────────────────
+
+interface MockStaff {
+  id: string;
+  role: string;
+  isSuperUser: boolean;
+}
+
+// ─── Mock DB state ────────────────────────────────────────────────────────────
+
+let dbRows: Record<string, unknown>[] = [];
+let deletedRows: string[] = [];
+let insertedRows: Record<string, unknown>[] = [];
+let encryptCalls: string[] = [];
+
+function resetMock() {
+  dbRows = [];
+  deletedRows = [];
+  insertedRows = [];
+  encryptCalls = [];
+}
+
+// ─── Mock staff context ───────────────────────────────────────────────────────
+
+const mockSuperUser: MockStaff = { id: "staff-1", role: "manager", isSuperUser: true };
+const mockManager: MockStaff = { id: "staff-2", role: "manager", isSuperUser: false };
+const mockGroomer: MockStaff = { id: "staff-3", role: "groomer", isSuperUser: false };
+
+// ─── Mock db module ───────────────────────────────────────────────────────────
+
+vi.mock("@groombook/db", () => {
+  const authProviderConfig = new Proxy(
+    { _name: "auth_provider_config" },
+    {
+      get(_target, prop) {
+        if (prop === "_name") return "auth_provider_config";
+        if (prop === "$inferSelect") return {};
+        return { table: "auth_provider_config", column: prop };
+      },
+    }
+  );
+
+  return {
+    getDb: () => ({
+      select: () => ({
+        from: () => ({
+          where: () => ({
+            limit: () => [...dbRows],
+            [Symbol.iterator]: function* () {
+              for (const item of dbRows) yield item;
+            },
+            0: dbRows[0],
+            length: dbRows.length,
+          }),
+        }),
+      }),
+      insert: () => ({
+        values: (vals: Record<string, unknown>) => {
+          insertedRows.push(vals);
+          return {
+            returning: () => [{ ...vals, id: "new-id-1", createdAt: new Date(), updatedAt: new Date() }],
+          };
+        },
+      }),
+      delete: () => {
+        // Execute immediately - route doesn't chain .returning()
+        deletedRows.push("all");
+        return Promise.resolve([]);
+      },
+      transaction: <T>(fn: (tx: {
+        delete: () => Promise<unknown>;
+        insert: () => { values: (v: Record<string, unknown>) => { returning: () => T[] } };
+      }) => Promise<T>) => {
+        const tx = {
+          delete: () => { deletedRows.push("all"); return Promise.resolve([]); },
+          insert: () => ({
+            values: (vals: Record<string, unknown>) => ({
+              returning: () => [{ ...vals, id: "new-id-1", createdAt: new Date(), updatedAt: new Date() }] as T[],
+            }),
+          }),
+        };
+        return fn(tx);
+      },
+    }),
+    authProviderConfig,
+    eq: (_col: unknown, _val: unknown) => ({ col: _col, val: _val }),
+    encryptSecret: (val: string) => {
+      encryptCalls.push(val);
+      return `encrypted:${val}`;
+    },
+  };
+});
+
+// ─── Build test app ───────────────────────────────────────────────────────────
+
+function makeApp(staff: MockStaff | null) {
+  const app = new Hono();
+  // Inject staff context + super user guard per route
+  // Must match both exact path and wildcard subpaths
+  app.use(
+    "/admin/auth-provider/*",
+    async (c, next) => {
+      if (!staff) {
+        return c.json({ error: "Forbidden: no staff record resolved" }, 403);
+      }
+      if (!staff.isSuperUser) {
+        return c.json({ error: "Forbidden: super user privileges required" }, 403);
+      }
+      (c as any).set("staff", staff);
+      await next();
+    }
+  );
+  app.route("/admin/auth-provider", authProviderRouter as unknown as Hono);
+  return app;
+}
+
+// ─── Helpers ──────────────────────────────────────────────────────────────────
+
+async function get<T extends Hono = Hono>(app: T, path: string, staff: MockStaff | null) {
+  const res = await app.request(path, { method: "GET" }, { allCtx: { staff } as { staff: MockStaff } });
+  return { status: res.status, body: await res.json() };
+}
+
+async function put<T extends Hono = Hono>(app: T, path: string, body: unknown, staff: MockStaff | null) {
+  const res = await app.request(path, {
+    method: "PUT",
+    headers: { "Content-Type": "application/json" },
+    body: JSON.stringify(body),
+  }, { allCtx: { staff } as { staff: MockStaff } });
+  return { status: res.status, body: await res.json() };
+}
+
+async function post<T extends Hono = Hono>(app: T, path: string, body: unknown, staff: MockStaff | null) {
+  const res = await app.request(path, {
+    method: "POST",
+    headers: { "Content-Type": "application/json" },
+    body: JSON.stringify(body),
+  }, { allCtx: { staff } as { staff: MockStaff } });
+  return { status: res.status, body: await res.json() };
+}
+
+async function del<T extends Hono = Hono>(app: T, path: string, staff: MockStaff | null) {
+  const res = await app.request(path, { method: "DELETE" }, { allCtx: { staff } as { staff: MockStaff } });
+  return { status: res.status, body: await res.json() };
+}
+
+// ─── Tests ────────────────────────────────────────────────────────────────────
+
+describe("GET /admin/auth-provider", () => {
+  beforeEach(resetMock);
+
+  it("returns 404 when no provider configured", async () => {
+    dbRows = [];
+    const app = makeApp(mockSuperUser);
+    const { status, body } = await get(app, "/admin/auth-provider", mockSuperUser);
+    expect(status).toBe(404);
+    expect(body.error).toBe("No auth provider configured");
+  });
+
+  it("returns config with secret redacted", async () => {
+    dbRows = [{
+      id: "prov-1",
+      providerId: "authentik",
+      displayName: "Authentik",
+      issuerUrl: "https://auth.example.com",
+      internalBaseUrl: null,
+      clientId: "client-123",
+      clientSecret: "encrypted:secret",
+      scopes: "openid profile email",
+      enabled: true,
+      createdAt: new Date(),
+      updatedAt: new Date(),
+    }];
+    const app = makeApp(mockSuperUser);
+    const { status, body } = await get(app, "/admin/auth-provider", mockSuperUser);
+    expect(status).toBe(200);
+    expect(body.clientSecret).toBe("••••••••");
+    expect(body.providerId).toBe("authentik");
+  });
+
+  it("returns 403 when not super user", async () => {
+    dbRows = [];
+    const app = makeApp(mockManager);
+    const { status } = await get(app, "/admin/auth-provider", mockManager);
+    expect(status).toBe(403);
+  });
+});
+
+describe("PUT /admin/auth-provider", () => {
+  beforeEach(resetMock);
+
+  it("stores encrypted secret", async () => {
+    const app = makeApp(mockSuperUser);
+    const { status, body } = await put(app, "/admin/auth-provider", {
+      providerId: "authentik",
+      displayName: "Authentik SSO",
+      issuerUrl: "https://auth.example.com",
+      clientId: "my-client",
+      clientSecret: "my-secret",
+      scopes: "openid profile email",
+    }, mockSuperUser);
+    expect(status).toBe(200);
+    expect(encryptCalls).toContain("my-secret");
+    expect(body.clientSecret).toBe("••••••••");
+    expect(body.providerId).toBe("authentik");
+  });
+
+  it("returns 400 for invalid schema", async () => {
+    const app = makeApp(mockSuperUser);
+    const { status } = await put(app, "/admin/auth-provider", {
+      providerId: "",
+      issuerUrl: "not-a-url",
+    }, mockSuperUser);
+    expect(status).toBe(400);
+  });
+});
+
+describe("POST /admin/auth-provider/test", () => {
+  beforeEach(resetMock);
+
+  it("returns ok=false for unreachable issuer", async () => {
+    const app = makeApp(mockSuperUser);
+    const { status, body } = await post(app, "/admin/auth-provider/test", {
+      providerId: "authentik",
+      displayName: "Authentik",
+      issuerUrl: "https://192.0.2.1/", // TEST-NET, never reachable
+      clientId: "client",
+      scopes: "openid profile email",
+    }, mockSuperUser);
+    expect(status).toBe(200);
+    expect(body.ok).toBe(false);
+    expect(body.error).toBeTruthy();
+  }, 15000); // timeout must exceed the 10s fetch timeout in the route handler
+
+  it("returns 400 for missing clientSecret (not required for test)", async () => {
+    const app = makeApp(mockSuperUser);
+    const { status } = await post(app, "/admin/auth-provider/test", {
+      providerId: "authentik",
+      displayName: "Authentik",
+      issuerUrl: "https://auth.example.com",
+      clientId: "client",
+    }, mockSuperUser);
+    expect(status).toBe(200); // clientSecret omitted intentionally for test
+  });
+});
+
+describe("DELETE /admin/auth-provider", () => {
+  beforeEach(resetMock);
+
+  it("deletes all config rows", async () => {
+    const app = makeApp(mockSuperUser);
+    const { status, body } = await del(app, "/admin/auth-provider", mockSuperUser);
+    expect(status).toBe(200);
+    expect(body.ok).toBe(true);
+    expect(deletedRows).toContain("all");
+  });
+
+  it("returns 403 when not super user", async () => {
+    const app = makeApp(mockGroomer);
+    const { status } = await del(app, "/admin/auth-provider", mockGroomer);
+    expect(status).toBe(403);
+  });
+});

src/__tests__/calendar.test.ts

@@ -0,0 +1,16 @@
+import { describe, it, expect } from "vitest";
+import { generateIcalToken } from "../routes/calendar.js";
+
+describe("generateIcalToken", () => {
+  it("generates a 64-character hex token", () => {
+    const token = generateIcalToken();
+    expect(token).toHaveLength(64);
+    expect(token).toMatch(/^[a-f0-9]+$/);
+  });
+
+  it("generates unique tokens", () => {
+    const token1 = generateIcalToken();
+    const token2 = generateIcalToken();
+    expect(token1).not.toBe(token2);
+  });
+});

src/__tests__/clients.test.ts

@@ -0,0 +1,294 @@
+import { describe, it, expect, vi, beforeEach } from "vitest";
+import { Hono } from "hono";
+
+// ─── Mock data ────────────────────────────────────────────────────────────────
+
+const ACTIVE_CLIENT = {
+  id: "client-uuid-1",
+  name: "Alice",
+  email: "alice@example.com",
+  phone: "555-1234",
+  address: "1 Main St",
+  notes: null,
+  status: "active",
+  disabledAt: null,
+  createdAt: new Date(),
+  updatedAt: new Date(),
+};
+
+const DISABLED_CLIENT = {
+  ...ACTIVE_CLIENT,
+  id: "client-uuid-2",
+  name: "Bob",
+  status: "disabled",
+  disabledAt: new Date(),
+};
+
+// ─── Queue-based mock DB ──────────────────────────────────────────────────────
+
+let selectRows: 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() {
+  selectRows = [];
+  appointmentRows = [];
+  insertedValues = [];
+  updatedValues = [];
+  deletedId = null;
+}
+
+vi.mock("@groombook/db", () => {
+  function makeChainable(data: unknown[]): unknown {
+    const arr = [...data];
+    const chain = new Proxy(arr, {
+      get(target, prop) {
+        if (prop === "where" || prop === "orderBy" || prop === "limit") {
+          return () => chain;
+        }
+        // @ts-expect-error proxy
+        return target[prop];
+      },
+    });
+    return chain;
+  }
+
+  const clients = new Proxy(
+    { _name: "clients" },
+    { get: (t, p) => (p === "_name" ? "clients" : { table: "clients", column: p }) }
+  );
+
+  const appointments = new Proxy(
+    { _name: "appointments" },
+    { get: (t, p) => (p === "_name" ? "appointments" : { table: "appointments", column: p }) }
+  );
+
+  return {
+    getDb: () => ({
+      select: () => ({
+        from: (table: unknown) => {
+          const tableName = (table as { _name?: string })._name;
+          const rows = tableName === "appointments" ? appointmentRows : selectRows;
+          return makeChainable(rows);
+        },
+      }),
+      insert: () => ({
+        values: (vals: Record<string, unknown>) => {
+          insertedValues.push(vals);
+          return {
+            returning: () => [{ ...ACTIVE_CLIENT, ...vals, id: "client-uuid-new" }],
+          };
+        },
+      }),
+      update: () => ({
+        set: (vals: Record<string, unknown>) => ({
+          where: () => {
+            updatedValues.push(vals);
+            return {
+              returning: () =>
+                selectRows.length > 0
+                  ? [{ ...selectRows[0], ...vals }]
+                  : [],
+            };
+          },
+        }),
+      }),
+      delete: () => ({
+        where: () => {
+          deletedId = "client-uuid-1";
+          return {
+            returning: () =>
+              selectRows.length > 0 ? [selectRows[0]] : [],
+          };
+        },
+      }),
+    }),
+    clients,
+    appointments,
+    eq: vi.fn(),
+    and: vi.fn(),
+    or: vi.fn(),
+  };
+});
+
+// ─── App setup ────────────────────────────────────────────────────────────────
+
+const { clientsRouter } = await import("../routes/clients.js");
+
+const app = new Hono();
+app.route("/clients", clientsRouter);
+
+function jsonRequest(method: string, path: string, body?: unknown) {
+  return app.request(path, {
+    method,
+    headers: { "Content-Type": "application/json" },
+    body: body !== undefined ? JSON.stringify(body) : undefined,
+  });
+}
+
+beforeEach(() => resetMock());
+
+// ─── GET / ────────────────────────────────────────────────────────────────────
+
+describe("GET /clients", () => {
+  it("returns active clients", async () => {
+    selectRows = [ACTIVE_CLIENT];
+    const res = await app.request("/clients");
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(Array.isArray(body)).toBe(true);
+    expect(body).toHaveLength(1);
+  });
+
+  it("returns all clients when includeDisabled=true", async () => {
+    selectRows = [ACTIVE_CLIENT, DISABLED_CLIENT];
+    const res = await app.request("/clients?includeDisabled=true");
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body).toHaveLength(2);
+  });
+
+  it("returns empty array when no clients exist", async () => {
+    selectRows = [];
+    const res = await app.request("/clients");
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body).toEqual([]);
+  });
+});
+
+// ─── GET /:id ─────────────────────────────────────────────────────────────────
+
+describe("GET /clients/:id", () => {
+  it("returns a single client", async () => {
+    selectRows = [ACTIVE_CLIENT];
+    const res = await app.request("/clients/client-uuid-1");
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.id).toBe("client-uuid-1");
+    expect(body.name).toBe("Alice");
+  });
+
+  it("returns 404 for a nonexistent client", async () => {
+    selectRows = [];
+    const res = await app.request("/clients/nonexistent");
+    expect(res.status).toBe(404);
+    const body = await res.json();
+    expect(body.error).toMatch(/not found/i);
+  });
+});
+
+// ─── POST / ───────────────────────────────────────────────────────────────────
+
+describe("POST /clients", () => {
+  it("creates a client with valid data", async () => {
+    const res = await jsonRequest("POST", "/clients", {
+      name: "Charlie",
+      email: "charlie@example.com",
+    });
+    expect(res.status).toBe(201);
+    const body = await res.json();
+    expect(body.name).toBe("Charlie");
+    expect(insertedValues).toHaveLength(1);
+    expect(insertedValues[0]!.name).toBe("Charlie");
+  });
+
+  it("creates a client with name and email", async () => {
+    const res = await jsonRequest("POST", "/clients", { name: "Dana", email: "dana@example.com" });
+    expect(res.status).toBe(201);
+    expect(insertedValues[0]!.name).toBe("Dana");
+    expect(insertedValues[0]!.email).toBe("dana@example.com");
+  });
+
+  it("rejects empty name", async () => {
+    const res = await jsonRequest("POST", "/clients", { name: "" });
+    expect(res.status).toBe(400);
+  });
+
+  it("rejects invalid email format", async () => {
+    const res = await jsonRequest("POST", "/clients", {
+      name: "Eve",
+      email: "not-an-email",
+    });
+    expect(res.status).toBe(400);
+  });
+
+  it("rejects missing body", async () => {
+    const res = await app.request("/clients", { method: "POST" });
+    expect(res.status).toBe(400);
+  });
+});
+
+// ─── PATCH /:id ───────────────────────────────────────────────────────────────
+
+describe("PATCH /clients/:id", () => {
+  it("updates client fields", async () => {
+    selectRows = [ACTIVE_CLIENT];
+    const res = await jsonRequest("PATCH", "/clients/client-uuid-1", {
+      name: "Alice Updated",
+    });
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.name).toBe("Alice Updated");
+    expect(updatedValues[0]!.name).toBe("Alice Updated");
+  });
+
+  it("sets disabledAt when status is set to disabled", async () => {
+    selectRows = [ACTIVE_CLIENT];
+    await jsonRequest("PATCH", "/clients/client-uuid-1", {
+      status: "disabled",
+    });
+    expect(updatedValues[0]!.status).toBe("disabled");
+    expect(updatedValues[0]!.disabledAt).toBeDefined();
+  });
+
+  it("clears disabledAt when re-enabling", async () => {
+    selectRows = [DISABLED_CLIENT];
+    await jsonRequest("PATCH", "/clients/client-uuid-2", {
+      status: "active",
+    });
+    expect(updatedValues[0]!.disabledAt).toBeNull();
+  });
+
+  it("returns 404 when client not found", async () => {
+    selectRows = [];
+    const res = await jsonRequest("PATCH", "/clients/nonexistent", {
+      name: "Ghost",
+    });
+    expect(res.status).toBe(404);
+  });
+});
+
+// ─── DELETE /:id ──────────────────────────────────────────────────────────────
+
+describe("DELETE /clients/:id", () => {
+  it("requires ?confirm=true", async () => {
+    const res = await app.request("/clients/client-uuid-1", {
+      method: "DELETE",
+    });
+    expect(res.status).toBe(400);
+    const body = await res.json();
+    expect(body.error).toMatch(/confirm/i);
+  });
+
+  it("deletes a client with ?confirm=true", async () => {
+    selectRows = [ACTIVE_CLIENT];
+    const res = await app.request("/clients/client-uuid-1?confirm=true", {
+      method: "DELETE",
+    });
+    expect(res.status).toBe(200);
+    const body = await res.json();
+    expect(body.ok).toBe(true);
+    expect(deletedId).toBe("client-uuid-1");
+  });
+
+  it("returns 404 when client not found", async () => {
+    selectRows = [];
+    const res = await app.request("/clients/nonexistent?confirm=true", {
+      method: "DELETE",
+    });
+    expect(res.status).toBe(404);
+  });
+});