feat(GRO-653): add portal session middleware and server-side audit logging (#300 )

* feat(GRO-653): add portal session middleware and server-side audit logging - Add validatePortalSession middleware that reads X-Impersonation-Session-Id header, queries impersonationSessions, and sets portalClientId + portalSessionId on the context - Add portalAudit middleware that logs all portal requests to impersonationAuditLogs table - Apply both middlewares to the portalRouter - Replace all getClientIdFromSession() calls with c.get("portalClientId") - Remove getClientIdFromSession() helper and inline session checks in waitlist routes - Consistent session.expiry > new Date() check across all routes Co-Authored-By: Paperclip <noreply@paperclip.ing> * fix(GRO-653): remove unused sessionId variable and and import Fix lint errors flagged by QA: - Remove unused `sessionId` variable from PATCH waitlist handler - Remove unused `and` import from portal.ts 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Paperclip <noreply@paperclip.ing> --------- Co-authored-by: Flea Flicker <fleaflicker@groombook.farh.net> Co-authored-by: Paperclip <noreply@paperclip.ing> Co-authored-by: Flea Flicker <flea-flicker@groombook.ai>
fix(GRO-640): replace N+1 queries in sendConfirmationEmail with single JOIN query
2026-04-16 11:20:36 +00:00 · 2026-04-16 10:14:06 +00:00 · 2026-04-16 05:18:58 +00:00 · 2026-04-16 05:15:28 +00:00 · 2026-04-16 05:11:07 +00:00 · 2026-04-16 05:08:23 +00:00
29 changed files with 1117 additions and 430 deletions
@@ -23,6 +23,7 @@
    "node-cron": "^3.0.3",
    "nodemailer": "^6.9.16",
    "stripe": "^22.0.0",
+    "telnyx": "^1.23.0",

    "zod": "^4.3.6"
  },
@@ -27,12 +27,14 @@ const DISABLED_CLIENT = {
 // ─── 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;
@@ -58,10 +60,19 @@ vi.mock("@groombook/db", () => {
    { 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: () => makeChainable(selectRows),
+        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>) => {
@@ -95,8 +106,10 @@ vi.mock("@groombook/db", () => {
      }),
    }),
    clients,
+    appointments,
    eq: vi.fn(),
    and: vi.fn(),
+    or: vi.fn(),
  };
 });

@@ -182,10 +195,11 @@ describe("POST /clients", () => {
    expect(insertedValues[0]!.name).toBe("Charlie");
  });

-  it("creates a client with only required name field", async () => {
-    const res = await jsonRequest("POST", "/clients", { name: "Dana" });
+  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 () => {
@@ -68,6 +68,7 @@ vi.mock("@groombook/db", () => {
    }),
    appointments,
    eq: () => ({}),
+    and: (..._clauses: unknown[]) => ({}),
  };
 });

@@ -78,6 +78,7 @@ vi.mock("@groombook/db", () => {
    }),
    staff,
    eq: vi.fn((_col: unknown, _val: unknown) => ({ col: _col, val: _val })),
+    and: vi.fn((..._clauses: unknown[]) => ({})),
  };
 });

@@ -362,7 +363,7 @@ describe("requireRoleOrSuperUser", () => {
    const res = await app.request("/test");
    expect(res.status).toBe(403);
    const body = await res.json();
-    expect(body.error).toMatch(/super user privileges required/i);
+    expect(body.error).toMatch(/role.*not permitted/i);
  });

  it("blocks a non-super-user groomer from manager-only routes", async () => {
@@ -370,7 +371,7 @@ describe("requireRoleOrSuperUser", () => {
    const res = await app.request("/test");
    expect(res.status).toBe(403);
    const body = await res.json();
-    expect(body.error).toMatch(/super user privileges required/i);
+    expect(body.error).toMatch(/role.*not permitted/i);
  });

  it("allows a manager with multiple allowed roles", async () => {
@@ -33,11 +33,26 @@ import { webhooksRouter } from "./routes/stripe-webhooks.js";
 const app = new Hono();

 // Global middleware
+const TRUSTED_ORIGINS = (process.env.CORS_ORIGIN ?? "http://localhost:5173")
+  .split(",")
+  .map((o) => o.trim());
+
+const ALLOWED_ORIGIN = process.env.CORS_ORIGIN ?? "http://localhost:5173";
+
 app.use("*", logger());
 app.use(
  "/api/*",
  cors({
-    origin: process.env.CORS_ORIGIN ?? "http://localhost:5173",
+    origin: (origin, ctx) => {
+      if (!origin) {
+        return ALLOWED_ORIGIN;
+      }
+      if (TRUSTED_ORIGINS.includes(origin)) {
+        return origin;
+      }
+      ctx.status(403);
+      return null;
+    },
    credentials: true,
  })
 );
@@ -89,7 +89,7 @@ export async function initAuth(): Promise<void> {
      console.warn("[auth] AUTH_DISABLED=true — building placeholder auth instance");
      authInstance = betterAuth({
        database: drizzleAdapter(getDb(), { provider: "pg" }),
-        secret: BETTER_AUTH_SECRET ?? "placeholder-secret-do-not-use-in-prod",
+        secret: BETTER_AUTH_SECRET!,
        baseURL: BETTER_AUTH_URL,
        rateLimit: {
          enabled: true,
@@ -177,9 +177,9 @@ export async function initAuth(): Promise<void> {
    const hasGoogle = !!(process.env.GOOGLE_CLIENT_ID && process.env.GOOGLE_CLIENT_SECRET);
    const hasGitHub = !!(process.env.GITHUB_CLIENT_ID && process.env.GITHUB_CLIENT_SECRET);

-    // Fetch OIDC discovery document to derive canonical provider URLs.
-    // Replace the host of token/userinfo endpoints with internalBaseUrl when set,
-    // while keeping authorizationUrl public for browser redirects.
+    const issuerUrlObj = new URL(providerConfig.issuerUrl);
+    const issuerHostname = issuerUrlObj.hostname;
+
    const discoveryUrlStr = `${providerConfig.issuerUrl}/.well-known/openid-configuration`;
    let oidcConfig: Record<string, string> = {};
    try {
@@ -203,6 +203,14 @@ export async function initAuth(): Promise<void> {
        const tokenUrl = discovery.token_endpoint;
        const userInfoUrl = discovery.userinfo_endpoint;
        if (authzUrl && tokenUrl && userInfoUrl) {
+          const authzUrlObj = new URL(authzUrl);
+          // Only validate authorizationUrl hostname against issuer — token/userinfo
+          // may legitimately use internal hostnames (OIDC_INTERNAL_BASE) for server-to-server calls.
+          if (authzUrlObj.hostname !== issuerHostname) {
+            throw new Error(
+              `[FATAL] OIDC discovery URL hostname mismatch: expected '${issuerHostname}' but got '${authzUrlObj.hostname}'. This may indicate a man-in-the-middle attack.`
+            );
+          }
          oidcConfig = {
            authorizationUrl: authzUrl,
            tokenUrl: providerConfig.internalBaseUrl
@@ -0,0 +1,45 @@
+import type { MiddlewareHandler } from "hono";
+import { getDb, impersonationAuditLogs } from "@groombook/db";
+import type { PortalEnv } from "./portalSession.js";
+
+/**
+ * Server-side audit logging middleware for portal routes.
+ * Applied after validatePortalSession in the middleware chain.
+ *
+ * After the route handler completes (await next()), inserts an audit log entry
+ * into impersonationAuditLogs:
+ *   - sessionId: from c.get("portalSessionId")
+ *   - action: "{METHOD} {routePath}" (e.g., "GET /portal/appointments")
+ *   - pageVisited: c.req.path
+ *   - metadata: { method, statusCode: c.res.status }
+ *
+ * Log entries are written for both success and error responses.
+ * Does NOT throw if audit logging fails — errors are logged but the user's
+ * request is not affected.
+ */
+export const portalAudit: MiddlewareHandler<PortalEnv> = async (c, next) => {
+  await next();
+
+  const sessionId = c.get("portalSessionId");
+  if (!sessionId) return;
+
+  const method = c.req.method;
+  const routePath = c.req.path;
+  const pageVisited = c.req.path;
+  const statusCode = c.res.status;
+
+  try {
+    const db = getDb();
+    await db
+      .insert(impersonationAuditLogs)
+      .values({
+        sessionId,
+        action: `${method} ${routePath}`,
+        pageVisited,
+        metadata: { method, statusCode },
+      })
+      .returning();
+  } catch (err) {
+    console.error("[portalAudit] Failed to write audit log:", err);
+  }
+};
@@ -0,0 +1,40 @@
+import type { MiddlewareHandler } from "hono";
+import { and, eq, getDb, impersonationSessions } from "@groombook/db";
+
+export interface PortalEnv {
+  Variables: {
+    portalClientId: string;
+    portalSessionId: string;
+  };
+}
+
+/**
+ * Validates the X-Impersonation-Session-Id header against the impersonationSessions table.
+ * Must be applied to all portal routes.
+ *
+ * Reads x-session-id from request headers, queries impersonationSessions for a row where
+ * id = sessionId AND status = 'active', and checks session.expiresAt > new Date().
+ * Returns 401 if session is invalid/missing/expired.
+ * On success, sets c.set("portalClientId", session.clientId) and c.set("portalSessionId", session.id).
+ */
+export const validatePortalSession: MiddlewareHandler<PortalEnv> = async (c, next) => {
+  const sessionId = c.req.header("X-Impersonation-Session-Id");
+  if (!sessionId) {
+    return c.json({ error: "Unauthorized" }, 401);
+  }
+
+  const db = getDb();
+  const [session] = await db
+    .select()
+    .from(impersonationSessions)
+    .where(and(eq(impersonationSessions.id, sessionId), eq(impersonationSessions.status, "active")))
+    .limit(1);
+
+  if (!session || session.expiresAt <= new Date()) {
+    return c.json({ error: "Unauthorized" }, 401);
+  }
+
+  c.set("portalClientId", session.clientId);
+  c.set("portalSessionId", session.id);
+  await next();
+};
@@ -1,5 +1,5 @@
 import type { MiddlewareHandler } from "hono";
-import { eq, getDb, staff } from "@groombook/db";
+import { and, eq, getDb, sql, staff } from "@groombook/db";

 export type StaffRole = "groomer" | "receptionist" | "manager";
 export type StaffRow = typeof staff.$inferSelect;
@@ -89,14 +89,31 @@ export const resolveStaffMiddleware: MiddlewareHandler<AppEnv> = async (
    .select()
    .from(staff)
    .where(eq(staff.oidcSub, jwt.sub));
-  if (!fallbackRow) {
-    return c.json(
-      { error: "Forbidden: no staff record found for authenticated user" },
-      403
-    );
+  if (fallbackRow) {
+    c.set("staff", fallbackRow);
+    await next();
+    return;
  }
-  c.set("staff", fallbackRow);
-  await next();
+  // Auto-link by email: staff record exists with matching email but no userId
+  if (jwt.email) {
+    const [byEmail] = await db
+      .select()
+      .from(staff)
+      .where(and(eq(staff.email, jwt.email), sql`${staff.userId} IS NULL`));
+    if (byEmail) {
+      await db
+        .update(staff)
+        .set({ userId: jwt.sub, updatedAt: new Date() })
+        .where(eq(staff.id, byEmail.id));
+      c.set("staff", { ...byEmail, userId: jwt.sub });
+      await next();
+      return;
+    }
+  }
+  return c.json(
+    { error: "Forbidden: no staff record found for authenticated user" },
+    403
+  );
 };

 /**
@@ -149,9 +166,9 @@ export function requireRoleOrSuperUser(
    }
    return c.json(
      {
-        error: staffRow.isSuperUser
-          ? `Forbidden: role '${staffRow.role}' is not permitted`
-          : "Forbidden: super user privileges required",
+        error: hasAllowedRole
+          ? "Forbidden: super user privileges required"
+          : `Forbidden: role '${staffRow.role}' is not permitted`,
      },
      403
    );
@@ -23,6 +23,27 @@ import { buildConfirmationEmail, sendEmail } from "../services/email.js";
 import { notifyWaitlistForAppointment } from "../services/waitlistNotify.js";
 import type { AppEnv } from "../middleware/rbac.js";

+async function withRetry<T>(
+  fn: () => Promise<T>,
+  maxRetries: number,
+  delayMs: number,
+  context: string
+): Promise<void> {
+  let lastError: unknown;
+  for (let attempt = 0; attempt <= maxRetries; attempt++) {
+    try {
+      await fn();
+      return;
+    } catch (err) {
+      lastError = err;
+      if (attempt < maxRetries) {
+        await new Promise((resolve) => setTimeout(resolve, delayMs));
+      }
+    }
+  }
+  console.error(`[appointments] ${context}: ${lastError}`);
+}
+
 export const appointmentsRouter = new Hono<AppEnv>();

 const createAppointmentSchema = z.object({
@@ -41,6 +62,10 @@ const createAppointmentSchema = z.object({
      frequencyWeeks: z.number().int().min(1).max(52),
      count: z.number().int().min(2).max(52),
    })
+    .refine(
+      (r) => r.frequencyWeeks * r.count <= 52,
+      { message: "Recurrence series must not exceed 1 year" }
+    )
    .optional(),
 });

@@ -208,11 +233,54 @@ appointmentsRouter.post(
          recurrence.frequencyWeeks * 7 * 24 * 60 * 60 * 1000;

        let first: typeof appointments.$inferSelect | undefined;
+        const conflictingInstances: number[] = [];
        for (let i = 0; i < recurrence.count; i++) {
          const instanceStart = new Date(start.getTime() + i * intervalMs);
          const instanceEnd = new Date(
            instanceStart.getTime() + durationMs
          );
+
+          if (apptFields.staffId) {
+            const conflicts = await tx
+              .select({ id: appointments.id })
+              .from(appointments)
+              .where(
+                and(
+                  eq(appointments.staffId, apptFields.staffId),
+                  lt(appointments.startTime, instanceEnd),
+                  gte(appointments.endTime, instanceStart),
+                  ne(appointments.status, "cancelled"),
+                  ne(appointments.status, "no_show"),
+                )
+              )
+              .limit(1);
+            if (conflicts.length > 0) {
+              conflictingInstances.push(i);
+            }
+          }
+
+          if (apptFields.batherStaffId) {
+            const conflicts = await tx
+              .select({ id: appointments.id })
+              .from(appointments)
+              .where(
+                and(
+                  or(
+                    eq(appointments.staffId, apptFields.batherStaffId),
+                    eq(appointments.batherStaffId, apptFields.batherStaffId)
+                  ),
+                  lt(appointments.startTime, instanceEnd),
+                  gte(appointments.endTime, instanceStart),
+                  ne(appointments.status, "cancelled"),
+                  ne(appointments.status, "no_show"),
+                )
+              )
+              .limit(1);
+            if (conflicts.length > 0) {
+              conflictingInstances.push(i);
+            }
+          }
+
          const [inserted] = await tx
            .insert(appointments)
            .values({
@@ -223,9 +291,19 @@ appointmentsRouter.post(
              seriesIndex: i,
            })
            .returning();
+          if (!inserted) throw new Error(`Insert failed for occurrence ${i}`);
          if (i === 0) first = inserted;
        }

+        if (conflictingInstances.length > 0) {
+          throw Object.assign(
+            new Error(
+              `Conflicts detected at occurrence(s): ${conflictingInstances.join(", ")}`
+            ),
+            { statusCode: 409 }
+          );
+        }
+
        if (!first) throw new Error("No appointments created");
        return first;
      });
@@ -243,9 +321,12 @@ appointmentsRouter.post(
    }

    // Send confirmation email (fire-and-forget — never fails the request)
-    sendConfirmationEmail(db, firstRow).catch((err) => {
-      console.error("[appointments] Failed to send confirmation email:", err);
-    });
+    withRetry(
+      () => sendConfirmationEmail(db, firstRow),
+      2,
+      1000,
+      `Failed to send confirmation email for appointment ${firstRow.id}`
+    );

    return c.json(firstRow, 201);
  }
@@ -257,44 +338,35 @@ async function sendConfirmationEmail(
  db: ReturnType<typeof getDb>,
  appt: typeof appointments.$inferSelect
 ): Promise<void> {
-  const [client] = await db
-    .select({ name: clients.name, email: clients.email, emailOptOut: clients.emailOptOut })
-    .from(clients)
-    .where(eq(clients.id, appt.clientId))
+  const [row] = await db
+    .select({
+      clientName: clients.name,
+      clientEmail: clients.email,
+      clientEmailOptOut: clients.emailOptOut,
+      petName: pets.name,
+      serviceName: services.name,
+      groomerName: staff.name,
+    })
+    .from(appointments)
+    .innerJoin(clients, eq(clients.id, appointments.clientId))
+    .innerJoin(pets, eq(pets.id, appointments.petId))
+    .innerJoin(services, eq(services.id, appointments.serviceId))
+    .leftJoin(staff, eq(staff.id, appointments.staffId))
+    .where(eq(appointments.id, appt.id))
    .limit(1);

-  if (!client || !client.email || client.emailOptOut) return;
+  if (!row) return;
+  const { clientName, clientEmail, clientEmailOptOut, petName, serviceName, groomerName } = row;

-  const [pet] = await db
-    .select({ name: pets.name })
-    .from(pets)
-    .where(eq(pets.id, appt.petId))
-    .limit(1);
-
-  const [service] = await db
-    .select({ name: services.name })
-    .from(services)
-    .where(eq(services.id, appt.serviceId))
-    .limit(1);
-
-  let groomerName: string | null = null;
-  if (appt.staffId) {
-    const [groomer] = await db
-      .select({ name: staff.name })
-      .from(staff)
-      .where(eq(staff.id, appt.staffId))
-      .limit(1);
-    groomerName = groomer?.name ?? null;
-  }
-
-  if (!pet || !service) return;
+  if (!clientEmail || clientEmailOptOut) return;
+  if (!petName || !serviceName) return;

  const sent = await sendEmail(
-    buildConfirmationEmail(client.email, {
-      clientName: client.name,
-      petName: pet.name,
-      serviceName: service.name,
-      groomerName,
+    buildConfirmationEmail(clientEmail, {
+      clientName,
+      petName,
+      serviceName,
+      groomerName: groomerName ?? null,
      startTime: appt.startTime,
    })
  );
@@ -374,6 +446,76 @@ appointmentsRouter.patch(

          let firstUpdated: typeof appointments.$inferSelect | undefined;
          for (const appt of affected) {
+            const newStart =
+              startDeltaMs !== 0
+                ? new Date(appt.startTime.getTime() + startDeltaMs)
+                : appt.startTime;
+            const newEnd =
+              endDeltaMs !== 0
+                ? new Date(appt.endTime.getTime() + endDeltaMs)
+                : appt.endTime;
+            const newStaffId =
+              updateFields.staffId !== undefined
+                ? updateFields.staffId
+                : appt.staffId;
+            const newBatherStaffId =
+              updateFields.batherStaffId !== undefined
+                ? updateFields.batherStaffId
+                : appt.batherStaffId;
+
+            if (
+              newStaffId &&
+              (startDeltaMs !== 0 ||
+                endDeltaMs !== 0 ||
+                updateFields.staffId !== undefined)
+            ) {
+              const conflicts = await tx
+                .select({ id: appointments.id })
+                .from(appointments)
+                .where(
+                  and(
+                    eq(appointments.staffId, newStaffId),
+                    lt(appointments.startTime, newEnd),
+                    gte(appointments.endTime, newStart),
+                    ne(appointments.status, "cancelled"),
+                    ne(appointments.status, "no_show"),
+                    ne(appointments.id, appt.id),
+                  )
+                )
+                .limit(1);
+              if (conflicts.length > 0) {
+                throw Object.assign(new Error("conflict"), { statusCode: 409 });
+              }
+            }
+
+            if (
+              newBatherStaffId &&
+              (startDeltaMs !== 0 ||
+                endDeltaMs !== 0 ||
+                updateFields.batherStaffId !== undefined)
+            ) {
+              const conflicts = await tx
+                .select({ id: appointments.id })
+                .from(appointments)
+                .where(
+                  and(
+                    or(
+                      eq(appointments.staffId, newBatherStaffId),
+                      eq(appointments.batherStaffId, newBatherStaffId)
+                    ),
+                    lt(appointments.startTime, newEnd),
+                    gte(appointments.endTime, newStart),
+                    ne(appointments.status, "cancelled"),
+                    ne(appointments.status, "no_show"),
+                    ne(appointments.id, appt.id),
+                  )
+                )
+                .limit(1);
+              if (conflicts.length > 0) {
+                throw Object.assign(new Error("conflict"), { statusCode: 409 });
+              }
+            }
+
            const apptUpdate: Record<string, unknown> = {
              updatedAt: new Date(),
            };
@@ -409,6 +551,13 @@ appointmentsRouter.patch(
        if (statusCode === 404) return c.json({ error: "Not found" }, 404);
        if (statusCode === 422)
          return c.json({ error: "endTime must be after startTime" }, 422);
+        if (statusCode === 409)
+          return c.json(
+            {
+              error: "Staff member has a conflicting appointment at this time",
+            },
+            409
+          );
        throw err;
      }

@@ -586,9 +735,12 @@ appointmentsRouter.delete("/:id", async (c) => {

    const apptDate = current.startTime.toISOString().slice(0, 10);
    const apptTime = current.startTime.toLocaleTimeString("en-US", { hour: "2-digit", minute: "2-digit", hour12: true });
-    notifyWaitlistForAppointment(id, apptDate, apptTime, current.serviceId).catch((err) => {
-      console.error("[appointments] Failed to notify waitlist:", err);
-    });
+    withRetry(
+      () => notifyWaitlistForAppointment(id, apptDate, apptTime, current.serviceId),
+      2,
+      1000,
+      `Failed to notify waitlist for appointment ${id}`
+    );

    return c.json({ ok: true });
  }
@@ -611,9 +763,12 @@ appointmentsRouter.delete("/:id", async (c) => {
    .returning();
  if (!row) return c.json({ error: "Not found" }, 404);

-  notifyWaitlistForAppointment(id, apptDate, apptTime, current.serviceId).catch((err) => {
-    console.error("[appointments] Failed to notify waitlist:", err);
-  });
+  withRetry(
+    () => notifyWaitlistForAppointment(id, apptDate, apptTime, current.serviceId),
+    2,
+    1000,
+    `Failed to notify waitlist for appointment ${id}`
+  );

  return c.json({ ok: true });
 });
@@ -102,7 +102,10 @@ bookRouter.get("/availability", async (c) => {

 const bookingSchema = z.object({
  serviceId: z.string().uuid(),
-  startTime: z.string().datetime(),
+  startTime: z.string().datetime().refine(
+    (dt) => new Date(dt) > new Date(),
+    { message: "Appointment must be in the future" }
+  ),
  clientName: z.string().min(1).max(200),
  clientEmail: z.string().email(),
  clientPhone: z.string().max(50).optional(),
@@ -265,29 +268,36 @@ bookRouter.get("/confirm/:token", async (c) => {
    return c.redirect(`${BASE_URL()}/booking/error`);
  }

-  // Reject if appointment is in the past
  if (appt.startTime < new Date()) {
    return c.redirect(`${BASE_URL()}/booking/error`);
  }

-  // Idempotent confirm: if already confirmed, redirect to success
  if (appt.confirmationStatus === "confirmed") {
    return c.redirect(`${BASE_URL()}/booking/confirmed`);
  }

-  // Reject if already cancelled
  if (appt.confirmationStatus === "cancelled") {
    return c.redirect(`${BASE_URL()}/booking/error`);
  }

-  await db
+  const updated = await db
    .update(appointments)
    .set({
      confirmationStatus: "confirmed",
      confirmedAt: new Date(),
      updatedAt: new Date(),
    })
-    .where(eq(appointments.id, appt.id));
+    .where(
+      and(
+        eq(appointments.confirmationToken, token),
+        eq(appointments.confirmationStatus, "pending")
+      )
+    )
+    .returning();
+
+  if (updated.length === 0) {
+    return c.redirect(`${BASE_URL()}/booking/error`);
+  }

  return c.redirect(`${BASE_URL()}/booking/confirmed`);
 });
@@ -309,19 +319,15 @@ bookRouter.get("/cancel/:token", async (c) => {
    return c.redirect(`${BASE_URL()}/booking/error`);
  }

-  // Reject if appointment is in the past
  if (appt.startTime < new Date()) {
    return c.redirect(`${BASE_URL()}/booking/error`);
  }

-  // Reject if already cancelled (token was nullified — this path won't normally hit,
-  // but guard against edge cases where token lookup still works)
  if (appt.confirmationStatus === "cancelled") {
    return c.redirect(`${BASE_URL()}/booking/error`);
  }

-  // Single-use cancellation: nullify token after use
-  await db
+  const updated = await db
    .update(appointments)
    .set({
      confirmationStatus: "cancelled",
@@ -329,7 +335,17 @@ bookRouter.get("/cancel/:token", async (c) => {
      confirmationToken: null,
      updatedAt: new Date(),
    })
-    .where(eq(appointments.id, appt.id));
+    .where(
+      and(
+        eq(appointments.confirmationToken, token),
+        eq(appointments.confirmationStatus, "pending")
+      )
+    )
+    .returning();
+
+  if (updated.length === 0) {
+    return c.redirect(`${BASE_URL()}/booking/error`);
+  }

  return c.redirect(`${BASE_URL()}/booking/cancelled`);
 });
@@ -1,5 +1,5 @@
 import { Hono } from "hono";
-import { randomBytes } from "node:crypto";
+import { randomBytes, timingSafeEqual } from "node:crypto";
 import {
  and,
  eq,
@@ -84,7 +84,18 @@ calendarRouter.get("/:staffId.ics", async (c) => {
    .where(eq(staff.id, staffId))
    .limit(1);

-  if (!staffMember || staffMember.icalToken !== token) {
+  if (!staffMember || !staffMember.icalToken) {
+    return c.text("Unauthorized", 401);
+  }
+
+  const storedToken = staffMember.icalToken;
+  const incomingToken = token;
+  const storedBuf = Buffer.from(storedToken, "utf8");
+  const incomingBuf = Buffer.from(incomingToken, "utf8");
+  if (
+    storedBuf.length !== incomingBuf.length ||
+    !timingSafeEqual(storedBuf, incomingBuf)
+  ) {
    return c.text("Unauthorized", 401);
  }

@@ -8,10 +8,12 @@ export const clientsRouter = new Hono<AppEnv>();

 const createClientSchema = z.object({
  name: z.string().min(1).max(200),
-  email: z.string().email().optional(),
+  email: z.string().email(),
  phone: z.string().max(50).optional(),
  address: z.string().max(500).optional(),
  notes: z.string().max(2000).optional(),
+  smsOptIn: z.boolean().optional(),
+  smsConsentText: z.string().max(1000).optional(),
 });


@@ -95,6 +97,7 @@ clientsRouter.post("/", zValidator("json", createClientSchema), async (c) => {
 // Update a client (including status changes)
 const patchClientSchema = createClientSchema.partial().extend({
  status: z.enum(["active", "disabled"]).optional(),
+  smsOptOut: z.boolean().optional(),
 });

 clientsRouter.patch(
@@ -107,13 +110,19 @@ clientsRouter.patch(

    const setValues: Record<string, unknown> = { ...body, updatedAt: now };

-    // When disabling, set disabledAt; when re-enabling, clear it
    if (body.status === "disabled") {
      setValues.disabledAt = now;
    } else if (body.status === "active") {
      setValues.disabledAt = null;
    }

+    if (body.smsOptOut === true) {
+      setValues.smsOptIn = false;
+      setValues.smsOptOutDate = now;
+      delete setValues.smsOptOut;
+    }
+    delete setValues.smsOptOut;
+
    const [row] = await db
      .update(clients)
      .set(setValues)
@@ -135,9 +144,24 @@ clientsRouter.delete("/:id", async (c) => {
  }

  const db = getDb();
+  const clientId = c.req.param("id");
+
+  const [existingAppt] = await db
+    .select({ id: appointments.id })
+    .from(appointments)
+    .where(eq(appointments.clientId, clientId))
+    .limit(1);
+
+  if (existingAppt) {
+    return c.json(
+      { error: "Cannot delete client with existing appointments. Cancel or reassign appointments first." },
+      409
+    );
+  }
+
  const [row] = await db
    .delete(clients)
-    .where(eq(clients.id, c.req.param("id")))
+    .where(eq(clients.id, clientId))
    .returning();
  if (!row) return c.json({ error: "Not found" }, 404);
  return c.json({ ok: true });
@@ -8,6 +8,7 @@ import {
  invoices,
  invoiceLineItems,
  invoiceTipSplits,
+  refunds,
  appointments,
  services,
  clients,
@@ -44,53 +45,61 @@ const updateInvoiceSchema = z.object({
 });

 // List invoices
-invoicesRouter.get("/", async (c) => {
-  const db = getDb();
-  const clientId = c.req.query("clientId");
-  const appointmentId = c.req.query("appointmentId");
-  const status = c.req.query("status");
-  const limit = Math.min(parseInt(c.req.query("limit") || "50", 10), 200);
-  const offset = parseInt(c.req.query("offset") || "0", 10);
-
-  const conditions = [];
-  if (clientId) conditions.push(eq(invoices.clientId, clientId));
-  if (appointmentId) conditions.push(eq(invoices.appointmentId, appointmentId));
-  if (status) conditions.push(eq(invoices.status, status as "draft" | "pending" | "paid" | "void"));
-
-  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;
-
-  const [totalResult] = await db
-    .select({ count: sql<number>`count(*)` })
-    .from(invoices)
-    .where(whereClause);
-
-  const rows = await db
-    .select({
-      id: invoices.id,
-      appointmentId: invoices.appointmentId,
-      clientId: invoices.clientId,
-      clientName: clients.name,
-      subtotalCents: invoices.subtotalCents,
-      taxCents: invoices.taxCents,
-      tipCents: invoices.tipCents,
-      totalCents: invoices.totalCents,
-      status: invoices.status,
-      paymentMethod: invoices.paymentMethod,
-      paidAt: invoices.paidAt,
-      notes: invoices.notes,
-      createdAt: invoices.createdAt,
-      updatedAt: invoices.updatedAt,
-    })
-    .from(invoices)
-    .leftJoin(clients, eq(invoices.clientId, clients.id))
-    .where(whereClause)
-    .orderBy(invoices.createdAt)
-    .limit(limit)
-    .offset(offset);
-
-  return c.json({ data: rows, total: totalResult?.count ?? 0 });
+const listInvoicesQuerySchema = z.object({
+  clientId: z.string().uuid().optional(),
+  appointmentId: z.string().uuid().optional(),
+  status: z.enum(["draft", "pending", "paid", "void"]).optional(),
+  limit: z.coerce.number().int().min(1).max(200).default(50),
+  offset: z.coerce.number().int().min(0).default(0),
 });

+invoicesRouter.get(
+  "/",
+  zValidator("query", listInvoicesQuerySchema),
+  async (c) => {
+    const db = getDb();
+    const { clientId, appointmentId, status, limit, offset } = c.req.valid("query");
+
+    const conditions = [];
+    if (clientId) conditions.push(eq(invoices.clientId, clientId));
+    if (appointmentId) conditions.push(eq(invoices.appointmentId, appointmentId));
+    if (status) conditions.push(eq(invoices.status, status as "draft" | "pending" | "paid" | "void"));
+
+    const whereClause = conditions.length > 0 ? and(...conditions) : undefined;
+
+    const [totalResult] = await db
+      .select({ count: sql<number>`count(*)` })
+      .from(invoices)
+      .where(whereClause);
+
+    const rows = await db
+      .select({
+        id: invoices.id,
+        appointmentId: invoices.appointmentId,
+        clientId: invoices.clientId,
+        clientName: clients.name,
+        subtotalCents: invoices.subtotalCents,
+        taxCents: invoices.taxCents,
+        tipCents: invoices.tipCents,
+        totalCents: invoices.totalCents,
+        status: invoices.status,
+        paymentMethod: invoices.paymentMethod,
+        paidAt: invoices.paidAt,
+        notes: invoices.notes,
+        createdAt: invoices.createdAt,
+        updatedAt: invoices.updatedAt,
+      })
+      .from(invoices)
+      .leftJoin(clients, eq(invoices.clientId, clients.id))
+      .where(whereClause)
+      .orderBy(invoices.createdAt)
+      .limit(limit)
+      .offset(offset);
+
+    return c.json({ data: rows, total: totalResult?.count ?? 0 });
+  }
+);
+
 // Get single invoice with line items and tip splits
 invoicesRouter.get("/:id", async (c) => {
  const db = getDb();
@@ -117,8 +126,8 @@ const tipSplitSchema = z.object({
    })
  ).min(1).refine(
    (splits) => {
-      const total = splits.reduce((sum, s) => sum + s.sharePct, 0);
-      return Math.abs(total - 100) < 0.01;
+      const totalBps = splits.reduce((sum, s) => sum + Math.round(s.sharePct * 100), 0);
+      return totalBps === 10000;
    },
    { message: "Split percentages must sum to 100" }
  ),
@@ -162,12 +171,13 @@ invoicesRouter.post(
      }
    });

-    const splits = await db
-      .select()
-      .from(invoiceTipSplits)
-      .where(eq(invoiceTipSplits.invoiceId, id));
+    const [updatedInvoice] = await db.select().from(invoices).where(eq(invoices.id, id));
+    const [lineItems, tipSplits] = await Promise.all([
+      db.select().from(invoiceLineItems).where(eq(invoiceLineItems.invoiceId, id)),
+      db.select().from(invoiceTipSplits).where(eq(invoiceTipSplits.invoiceId, id)),
+    ]);

-    return c.json(splits, 201);
+    return c.json({ ...updatedInvoice, lineItems, tipSplits }, 201);
  }
 );

@@ -292,6 +302,13 @@ invoicesRouter.post("/from-appointment/:appointmentId", async (c) => {
  return c.json({ ...invoice, lineItems: [lineItem] }, 201);
 });

+const ALLOWED_TRANSITIONS: Record<string, string[]> = {
+  draft: ["pending", "void"],
+  pending: ["draft", "paid", "void"],
+  paid: ["void"],
+  void: [],
+};
+
 // Update invoice
 invoicesRouter.patch(
  "/:id",
@@ -307,8 +324,14 @@ invoicesRouter.patch(
      .where(eq(invoices.id, id));
    if (!current) return c.json({ error: "Not found" }, 404);

-    if (current.status === "void") {
-      return c.json({ error: "Cannot modify a voided invoice" }, 422);
+    if (body.status !== undefined) {
+      const allowed = ALLOWED_TRANSITIONS[current.status] ?? [];
+      if (!allowed.includes(body.status)) {
+        return c.json(
+          { error: `Invalid status transition from ${current.status} to ${body.status}` },
+          422
+        );
+      }
    }

    const update: Record<string, unknown> = { ...body, updatedAt: new Date() };
@@ -346,6 +369,7 @@ import { processRefund } from "../services/payment.js";

 const refundSchema = z.object({
  amountCents: z.number().int().nonnegative().optional(),
+  idempotencyKey: z.string().max(255).optional(),
 });

 invoicesRouter.post(
@@ -371,9 +395,28 @@ invoicesRouter.post(
      return c.json({ error: "No Stripe payment intent found for this invoice" }, 422);
    }

-    const result = await processRefund(id, body.amountCents);
-    if (!result) return c.json({ error: "Refund failed" }, 500);
+    return await db.transaction(async (tx) => {
+      if (body.idempotencyKey) {
+        const [existing] = await tx
+          .select()
+          .from(refunds)
+          .where(eq(refunds.idempotencyKey, body.idempotencyKey));
+        if (existing) {
+          return c.json({ refundId: existing.stripeRefundId });
+        }
+      }

-    return c.json({ refundId: result.refundId });
+      const result = await processRefund(id, body.amountCents);
+      if (!result) return c.json({ error: "Refund failed" }, 500);
+
+      await tx.insert(refunds).values({
+        invoiceId: id,
+        stripeRefundId: result.refundId,
+        idempotencyKey: body.idempotencyKey ?? null,
+        amountCents: body.amountCents ?? null,
+      });
+
+      return c.json({ refundId: result.refundId });
+    });
  }
 );
@@ -1,33 +1,22 @@
 import { Hono } from "hono";
 import { zValidator } from "@hono/zod-validator";
 import { z } from "zod/v3";
-import { and, eq, inArray } from "@groombook/db";
+import { eq, inArray } from "@groombook/db";
 import { getDb, appointments, impersonationSessions, waitlistEntries, clients, pets, services, staff, invoices, invoiceLineItems } from "@groombook/db";
-import type { AppEnv } from "../middleware/rbac.js";
+import { validatePortalSession } from "../middleware/portalSession.js";
+import { portalAudit } from "../middleware/portalAudit.js";
+import type { PortalEnv } from "../middleware/portalSession.js";

-export const portalRouter = new Hono<AppEnv>();
+export const portalRouter = new Hono<PortalEnv>();

-// ─── Session helper ───────────────────────────────────────────────────────────
-
-async function getClientIdFromSession(sessionId: string | null | undefined): Promise<string | null> {
-  if (!sessionId) return null;
-  const db = getDb();
-  const [session] = await db
-    .select()
-    .from(impersonationSessions)
-    .where(and(eq(impersonationSessions.id, sessionId), eq(impersonationSessions.status, "active")))
-    .limit(1);
-  if (!session || session.expiresAt <= new Date()) return null;
-  return session.clientId;
-}
+// Apply middleware to all portal routes
+portalRouter.use("/*", validatePortalSession, portalAudit);

 // ─── GET routes ──────────────────────────────────────────────────────────────

 portalRouter.get("/me", async (c) => {
  const db = getDb();
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+  const clientId = c.get("portalClientId");

  const [client] = await db.select().from(clients).where(eq(clients.id, clientId)).limit(1);
  if (!client) return c.json({ error: "Not found" }, 404);
@@ -49,9 +38,7 @@ portalRouter.get("/services", async (c) => {

 portalRouter.get("/appointments", async (c) => {
  const db = getDb();
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+  const clientId = c.get("portalClientId");

  const now = new Date();
  const allAppts = await db
@@ -101,9 +88,7 @@ portalRouter.get("/appointments", async (c) => {

 portalRouter.get("/pets", async (c) => {
  const db = getDb();
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+  const clientId = c.get("portalClientId");

  const clientPets = await db.select().from(pets).where(eq(pets.clientId, clientId));
  return c.json(clientPets.map(p => ({ id: p.id, name: p.name, breed: p.breed, weightKg: p.weightKg, dateOfBirth: p.dateOfBirth, photoKey: p.photoKey, groomingNotes: p.groomingNotes })));
@@ -111,9 +96,7 @@ portalRouter.get("/pets", async (c) => {

 portalRouter.get("/invoices", async (c) => {
  const db = getDb();
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+  const clientId = c.get("portalClientId");

  const clientInvoices = await db.select().from(invoices).where(eq(invoices.clientId, clientId));
  const invoiceIds = clientInvoices.map(i => i.id);
@@ -148,12 +131,7 @@ portalRouter.patch(
    const db = getDb();
    const id = c.req.param("id");
    const body = c.req.valid("json");
-
-    const sessionId = c.req.header("X-Impersonation-Session-Id");
-    const clientId = await getClientIdFromSession(sessionId);
-    if (!clientId) {
-      return c.json({ error: "Unauthorized" }, 401);
-    }
+    const clientId = c.get("portalClientId");

    const [appt] = await db
      .select()
@@ -196,12 +174,7 @@ portalRouter.patch(
 portalRouter.post("/appointments/:id/confirm", async (c) => {
  const db = getDb();
  const id = c.req.param("id");
-
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) {
-    return c.json({ error: "Unauthorized" }, 401);
-  }
+  const clientId = c.get("portalClientId");

  const [appt] = await db
    .select()
@@ -250,12 +223,7 @@ portalRouter.post("/appointments/:id/confirm", async (c) => {
 portalRouter.post("/appointments/:id/cancel", async (c) => {
  const db = getDb();
  const id = c.req.param("id");
-
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) {
-    return c.json({ error: "Unauthorized" }, 401);
-  }
+  const clientId = c.get("portalClientId");

  const [appt] = await db
    .select()
@@ -319,28 +287,7 @@ portalRouter.post(
  async (c) => {
    const db = getDb();
    const body = c.req.valid("json");
-    const sessionId = c.req.header("X-Impersonation-Session-Id");
-
-    let clientId: string | null = null;
-    if (sessionId) {
-      const [session] = await db
-        .select()
-        .from(impersonationSessions)
-        .where(
-          and(
-            eq(impersonationSessions.id, sessionId),
-            eq(impersonationSessions.status, "active")
-          )
-        )
-        .limit(1);
-      if (session && session.expiresAt > new Date()) {
-        clientId = session.clientId;
-      }
-    }
-
-    if (!clientId) {
-      return c.json({ error: "Unauthorized" }, 401);
-    }
+    const clientId = c.get("portalClientId");

    const [entry] = await db
      .insert(waitlistEntries)
@@ -364,26 +311,7 @@ portalRouter.patch(
    const db = getDb();
    const id = c.req.param("id");
    const body = c.req.valid("json");
-    const sessionId = c.req.header("X-Impersonation-Session-Id");
-
-    if (!sessionId) {
-      return c.json({ error: "Unauthorized" }, 401);
-    }
-
-    const [session] = await db
-      .select()
-      .from(impersonationSessions)
-      .where(
-        and(
-          eq(impersonationSessions.id, sessionId),
-          eq(impersonationSessions.status, "active")
-        )
-      )
-      .limit(1);
-
-    if (!session || session.expiresAt <= new Date()) {
-      return c.json({ error: "Unauthorized" }, 401);
-    }
+    const clientId = c.get("portalClientId");

    const [existing] = await db
      .select()
@@ -392,7 +320,7 @@ portalRouter.patch(
      .limit(1);

    if (!existing) return c.json({ error: "Not found" }, 404);
-    if (existing.clientId !== session.clientId) {
+    if (existing.clientId !== clientId) {
      return c.json({ error: "Forbidden" }, 403);
    }

@@ -414,26 +342,7 @@ portalRouter.patch(
 portalRouter.delete("/waitlist/:id", async (c) => {
  const db = getDb();
  const id = c.req.param("id");
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-
-  if (!sessionId) {
-    return c.json({ error: "Unauthorized" }, 401);
-  }
-
-  const [session] = await db
-    .select()
-    .from(impersonationSessions)
-    .where(
-      and(
-        eq(impersonationSessions.id, sessionId),
-        eq(impersonationSessions.status, "active")
-      )
-    )
-    .limit(1);
-
-  if (!session || session.expiresAt <= new Date()) {
-    return c.json({ error: "Unauthorized" }, 401);
-  }
+  const clientId = c.get("portalClientId");

  const [entry] = await db
    .select()
@@ -442,7 +351,7 @@ portalRouter.delete("/waitlist/:id", async (c) => {
    .limit(1);

  if (!entry) return c.json({ error: "Not found" }, 404);
-  if (entry.clientId !== session.clientId) {
+  if (entry.clientId !== clientId) {
    return c.json({ error: "Forbidden" }, 403);
  }

@@ -475,9 +384,7 @@ portalRouter.post(
  async (c) => {
    const db = getDb();
    const body = c.req.valid("json");
-    const sessionId = c.req.header("X-Impersonation-Session-Id");
-    const clientId = await getClientIdFromSession(sessionId);
-    if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+    const clientId = c.get("portalClientId");

    const invoiceRows = await db
      .select()
@@ -514,9 +421,7 @@ portalRouter.post(
 );

 portalRouter.get("/payment-methods", async (c) => {
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+  const clientId = c.get("portalClientId");

  const methods = await listPaymentMethods(clientId);
  if (methods === null) return c.json({ error: "Payment service unavailable" }, 503);
@@ -524,9 +429,7 @@ portalRouter.get("/payment-methods", async (c) => {
 });

 portalRouter.post("/payment-methods", async (c) => {
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+  const clientId = c.get("portalClientId");

  const stripePublishableKey = process.env.STRIPE_PUBLISHABLE_KEY ?? "";
  const customerId = await getOrCreateStripeCustomer(clientId);
@@ -539,9 +442,7 @@ portalRouter.post("/payment-methods", async (c) => {
 });

 portalRouter.delete("/payment-methods/:id", async (c) => {
-  const sessionId = c.req.header("X-Impersonation-Session-Id");
-  const clientId = await getClientIdFromSession(sessionId);
-  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+  const clientId = c.get("portalClientId");

  const paymentMethodId = c.req.param("id");

@@ -9,7 +9,7 @@ const createServiceSchema = z.object({
  name: z.string().min(1).max(200),
  description: z.string().max(2000).optional(),
  basePriceCents: z.number().int().positive(),
-  durationMinutes: z.number().int().positive(),
+  durationMinutes: z.number().int().positive().max(480),
  active: z.boolean().default(true),
 });

@@ -4,6 +4,24 @@ import { z } from "zod/v3";
 import { and, eq, getDb, sql, staff, businessSettings, authProviderConfig, encryptSecret } from "@groombook/db";
 import type { AppEnv } from "../middleware/rbac.js";

+const RATE_LIMIT_WINDOW_MS = 60_000;
+const RATE_LIMIT_MAX = 10;
+const rateLimitMap = new Map<string, { count: number; resetAt: number }>();
+
+function rateLimitByIp(ip: string): { allowed: boolean; remaining: number } {
+  const now = Date.now();
+  const entry = rateLimitMap.get(ip);
+  if (!entry || now > entry.resetAt) {
+    rateLimitMap.set(ip, { count: 1, resetAt: now + RATE_LIMIT_WINDOW_MS });
+    return { allowed: true, remaining: RATE_LIMIT_MAX - 1 };
+  }
+  if (entry.count >= RATE_LIMIT_MAX) {
+    return { allowed: false, remaining: 0 };
+  }
+  entry.count++;
+  return { allowed: true, remaining: RATE_LIMIT_MAX - entry.count };
+}
+
 export const setupRouter = new Hono<AppEnv>();

 // GET /api/setup/status — public (no auth), returns whether setup is needed
@@ -185,52 +203,74 @@ const authProviderTestSchema = z.object({
 * After setup completes, this endpoint permanently returns 403.
 */
 setupRouter.post("/auth-provider", async (c) => {
+  const ip = c.req.header("x-forwarded-for")?.split(",")[0]?.trim() ?? "unknown";
+  const { allowed, remaining } = rateLimitByIp(ip);
+  c.res.headers.set("x-rate-limit-remaining", String(remaining));
+  if (!allowed) {
+    return c.json({ error: "Too many requests. Please try again later." }, 429);
+  }
+
  const db = getDb();

-  // Guard: only allow during fresh install (no super user yet)
-  const [superUser] = await db
-    .select({ id: staff.id })
-    .from(staff)
-    .where(eq(staff.isSuperUser, true))
-    .limit(1);
+  let row: typeof authProviderConfig.$inferSelect;
+  try {
+    row = await db.transaction(async (tx) => {
+      const [superUser] = await tx
+        .select({ id: staff.id })
+        .from(staff)
+        .where(eq(staff.isSuperUser, true))
+        .limit(1);

-  if (superUser) {
-    // Setup already completed — lock this endpoint permanently
-    return c.json({ error: "Setup has already been completed. This endpoint is no longer available." }, 403);
-  }
+      if (superUser) {
+        throw Object.assign(new Error("setup-complete"), { code: 403 });
+      }

-  // Guard: ensure no DB config already exists (should be redundant with status check but defensive)
-  const [existingConfig] = await db
-    .select({ id: authProviderConfig.id })
-    .from(authProviderConfig)
-    .where(eq(authProviderConfig.enabled, true))
-    .limit(1);
+      const [existingConfig] = await tx
+        .select({ id: authProviderConfig.id })
+        .from(authProviderConfig)
+        .where(eq(authProviderConfig.enabled, true))
+        .limit(1);

-  if (existingConfig) {
-    return c.json({ error: "Auth provider is already configured." }, 409);
-  }
+      if (existingConfig) {
+        throw Object.assign(new Error("config-exists"), { code: 409 });
+      }

-  const body = authProviderBootstrapSchema.parse(await c.req.json());
+      const body = authProviderBootstrapSchema.parse(await c.req.json());

-  // Encrypt clientSecret before storing
-  const encryptedSecret = encryptSecret(body.clientSecret);
+      const encryptedSecret = encryptSecret(body.clientSecret);

-  const [row] = await db
-    .insert(authProviderConfig)
-    .values({
-      providerId: body.providerId,
-      displayName: body.displayName,
-      issuerUrl: body.issuerUrl,
-      internalBaseUrl: body.internalBaseUrl ?? null,
-      clientId: body.clientId,
-      clientSecret: encryptedSecret,
-      scopes: body.scopes,
-      enabled: true,
-    })
-    .returning();
+      const [configRow] = await tx
+        .insert(authProviderConfig)
+        .values({
+          providerId: body.providerId,
+          displayName: body.displayName,
+          issuerUrl: body.issuerUrl,
+          internalBaseUrl: body.internalBaseUrl ?? null,
+          clientId: body.clientId,
+          clientSecret: encryptedSecret,
+          scopes: body.scopes,
+          enabled: true,
+        })
+        .returning();

-  if (!row) {
-    return c.json({ error: "Failed to save auth provider configuration." }, 500);
+      if (!configRow) {
+        throw Object.assign(new Error("insert-failed"), { code: 500 });
+      }
+
+      return configRow;
+    });
+  } catch (err: unknown) {
+    const e = err as Error & { code?: number };
+    if (e.message === "setup-complete") {
+      return c.json({ error: "Setup has already been completed. This endpoint is no longer available." }, e.code as 403);
+    }
+    if (e.message === "config-exists") {
+      return c.json({ error: "Auth provider is already configured." }, e.code as 409);
+    }
+    if (e.message === "insert-failed") {
+      return c.json({ error: "Failed to save auth provider configuration." }, e.code as 500);
+    }
+    throw err;
  }

  return c.json({
@@ -254,6 +294,13 @@ setupRouter.post("/auth-provider", async (c) => {
 * Only available when needsSetup is true (no super user = fresh install).
 */
 setupRouter.post("/auth-provider/test", async (c) => {
+  const ip = c.req.header("x-forwarded-for")?.split(",")[0]?.trim() ?? "unknown";
+  const { allowed, remaining } = rateLimitByIp(ip);
+  c.res.headers.set("x-rate-limit-remaining", String(remaining));
+  if (!allowed) {
+    return c.json({ ok: false, error: "Too many requests. Please try again later." }, 429);
+  }
+
  const db = getDb();

  // Guard: only allow during fresh install (no super user yet)
@@ -1,5 +1,6 @@
 import { Hono } from "hono";
 import Stripe from "stripe";
+import { z } from "zod/v3";
 import { eq, getDb, invoices } from "@groombook/db";
 import { getStripeClient } from "../services/payment.js";

@@ -44,10 +45,13 @@ webhooksRouter.post("/stripe", async (c) => {
      const invoiceIds = pi.metadata.groombook_invoice_ids.split(",");
      for (const invoiceId of invoiceIds) {
        if (!invoiceId) continue;
+        const parsed = z.string().uuid().safeParse(invoiceId.trim());
+        if (!parsed.success) continue;
+        const invoiceIdTrimmed = invoiceId.trim();
        const [inv] = await db
          .select()
          .from(invoices)
-          .where(eq(invoices.id, invoiceId))
+          .where(eq(invoices.id, invoiceIdTrimmed))
          .limit(1);
        if (!inv) continue;
        if (inv.stripePaymentIntentId && inv.stripePaymentIntentId !== pi.id) continue;
@@ -60,7 +64,7 @@ webhooksRouter.post("/stripe", async (c) => {
            stripePaymentIntentId: pi.id,
            updatedAt: new Date(),
          })
-          .where(eq(invoices.id, invoiceId));
+          .where(eq(invoices.id, invoiceIdTrimmed));
      }
    }
  } else if (event.type === "payment_intent.payment_failed") {
@@ -69,13 +73,16 @@ webhooksRouter.post("/stripe", async (c) => {
      const invoiceIds = pi.metadata.groombook_invoice_ids.split(",");
      for (const invoiceId of invoiceIds) {
        if (!invoiceId) continue;
+        const parsed = z.string().uuid().safeParse(invoiceId.trim());
+        if (!parsed.success) continue;
+        const invoiceIdTrimmed = invoiceId.trim();
        await db
          .update(invoices)
          .set({
            paymentFailureReason: pi.last_payment_error?.message ?? "Payment failed",
            updatedAt: new Date(),
          })
-          .where(eq(invoices.id, invoiceId));
+          .where(eq(invoices.id, invoiceIdTrimmed));
      }
    }
  } else if (event.type === "charge.refunded") {
@@ -18,9 +18,10 @@ import {
  buildReminderEmail,
  sendEmail,
 } from "./email.js";
+import { smsSend } from "./sms.js";
+
+const TCPA_OPT_OUT = "Reply STOP to opt out. Msg & data rates may apply.";

-// How many hours before the appointment to send each reminder.
-// Override via env: REMINDER_HOURS_EARLY (default 24) and REMINDER_HOURS_LATE (default 2).
 function getReminderWindows(): { label: string; hours: number }[] {
  const early = Number(process.env.REMINDER_HOURS_EARLY ?? 24);
  const late = Number(process.env.REMINDER_HOURS_LATE ?? 2);
@@ -30,20 +31,14 @@ function getReminderWindows(): { label: string; hours: number }[] {
  ];
 }

-// Checks for upcoming appointments that need reminders and sends them.
-// Runs every minute — idempotent via reminder_logs unique constraint.
 export async function runReminderCheck(): Promise<void> {
  const db = getDb();
  const now = new Date();

  for (const window of getReminderWindows()) {
-    // Target window: appointments starting between (hours - 1) and hours from now.
-    // Running every minute means we check a 1-minute slice; the 1-hour window
-    // ensures we catch appointments that started between heartbeats.
    const windowStart = new Date(now.getTime() + (window.hours - 1) * 3600_000);
    const windowEnd = new Date(now.getTime() + window.hours * 3600_000);

-    // Find upcoming appointments in this time window that haven't been cancelled/completed
    const upcoming = await db
      .select({
        id: appointments.id,
@@ -65,23 +60,38 @@ export async function runReminderCheck(): Promise<void> {
      );

    for (const appt of upcoming) {
-      // Check if reminder already sent (unique constraint prevents double-send)
-      const existing = await db
+      const [emailLog] = await db
        .select({ id: reminderLogs.id })
        .from(reminderLogs)
        .where(
          and(
            eq(reminderLogs.appointmentId, appt.id),
-            eq(reminderLogs.reminderType, window.label)
+            eq(reminderLogs.reminderType, window.label),
+            eq(reminderLogs.channel, "email")
          )
        )
        .limit(1);

-      if (existing.length > 0) continue; // already sent
+      const [smsLog] = await db
+        .select({ id: reminderLogs.id })
+        .from(reminderLogs)
+        .where(
+          and(
+            eq(reminderLogs.appointmentId, appt.id),
+            eq(reminderLogs.reminderType, window.label),
+            eq(reminderLogs.channel, "sms")
+          )
+        )
+        .limit(1);

-      // Fetch related records for the email
      const [client] = await db
-        .select({ name: clients.name, email: clients.email, emailOptOut: clients.emailOptOut })
+        .select({
+          name: clients.name,
+          email: clients.email,
+          emailOptOut: clients.emailOptOut,
+          smsOptIn: clients.smsOptIn,
+          phone: clients.phone,
+        })
        .from(clients)
        .where(eq(clients.id, appt.clientId))
        .limit(1);
@@ -112,8 +122,6 @@ export async function runReminderCheck(): Promise<void> {

      if (!pet || !service) continue;

-      // Ensure the appointment has a confirmation token before sending the reminder.
-      // Generate one if it doesn't have one yet (e.g. pre-existing appointments).
      let confirmationToken = appt.confirmationToken;
      if (!confirmationToken) {
        confirmationToken = randomBytes(32).toString("hex");
@@ -123,35 +131,59 @@ export async function runReminderCheck(): Promise<void> {
          .where(eq(appointments.id, appt.id));
      }

-      const sent = await sendEmail(
-        buildReminderEmail(
-          client.email,
-          {
-            clientName: client.name,
-            petName: pet.name,
-            serviceName: service.name,
-            groomerName,
-            startTime: appt.startTime,
-          },
-          window.hours,
-          confirmationToken
-        )
-      );
+      if (!emailLog) {
+        const sent = await sendEmail(
+          buildReminderEmail(
+            client.email,
+            {
+              clientName: client.name,
+              petName: pet.name,
+              serviceName: service.name,
+              groomerName,
+              startTime: appt.startTime,
+            },
+            window.hours,
+            confirmationToken
+          )
+        );

-      if (sent) {
-        // Record send — ignore conflicts (race condition between instances)
-        await db
-          .insert(reminderLogs)
-          .values({ appointmentId: appt.id, reminderType: window.label })
-          .onConflictDoNothing();
+        if (sent) {
+          await db
+            .insert(reminderLogs)
+            .values({ appointmentId: appt.id, reminderType: window.label, channel: "email" })
+            .onConflictDoNothing();
+        }
+      }
+
+      if (!smsLog && client.smsOptIn && client.phone) {
+        const apiUrl = process.env.API_URL ?? "http://localhost:3000";
+        const confirmUrl = `${apiUrl}/api/book/confirm/${confirmationToken}`;
+        const cancelUrl = `${apiUrl}/api/book/cancel/${confirmationToken}`;
+        const when = window.hours >= 24 ? "tomorrow" : `in ${window.hours} hours`;
+        const smsBody = [
+          `Hi ${client.name}, just a reminder: ${pet.name}'s grooming appointment is ${when}.`,
+          `Service: ${service.name}${groomerName ? ` with ${groomerName}` : ""}`,
+          `Confirm: ${confirmUrl}`,
+          `Cancel: ${cancelUrl}`,
+          TCPA_OPT_OUT,
+        ].join(". ");
+        try {
+          const smsOk = await smsSend(client.phone, smsBody);
+          if (smsOk) {
+            await db
+              .insert(reminderLogs)
+              .values({ appointmentId: appt.id, reminderType: window.label, channel: "sms" })
+              .onConflictDoNothing();
+          }
+        } catch (err) {
+          console.error("[reminders] SMS send failed:", err);
+        }
      }
    }
  }
 }

-// Starts the cron scheduler. Call once at server startup.
 export function startReminderScheduler(): void {
-  // Run every minute
  cron.schedule("* * * * *", () => {
    runReminderCheck().catch((err) => {
      console.error("[reminders] Error during reminder check:", err);
@@ -163,8 +195,6 @@ export function startReminderScheduler(): void {
  console.log("[reminders] Reminder scheduler started");
 }

-// Deletes expired sessions from the database.
-// Runs every minute alongside reminder checks.
 export async function runSessionCleanup(): Promise<void> {
  const db = getDb();
  const now = new Date();
@@ -0,0 +1,142 @@
+import { Telnyx } from "telnyx";
+import { createHmac } from "crypto";
+
+export interface SmsProvider {
+  sendSms(to: string, body: string, mediaUrls?: string[]): Promise<{ messageId: string; status: string }>;
+  validateWebhookSignature(req: Request): boolean;
+}
+
+interface TelnyxSmsResult {
+  message_id: string;
+  status: string;
+}
+
+function createTelnyxClient(): Telnyx | null {
+  const apiKey = process.env.TELNYX_API_KEY;
+  if (!apiKey) return null;
+  return new Telnyx(apiKey);
+}
+
+let _client: Telnyx | null | undefined;
+
+function getClient(): Telnyx | null {
+  if (_client === undefined) _client = createTelnyxClient();
+  return _client;
+}
+
+function getFromNumber(): string | null {
+  return process.env.TELNYX_FROM_NUMBER ?? null;
+}
+
+function isE164(phone: string): boolean {
+  return /^\+[1-9]\d{7,14}$/.test(phone);
+}
+
+export async function sendSms(
+  to: string,
+  body: string,
+  mediaUrls?: string[]
+): Promise<{ messageId: string; status: string }> {
+  const client = getClient();
+  if (!client) throw new Error("Telnyx client not initialized. Set TELNYX_API_KEY.");
+
+  const from = getFromNumber();
+  if (!from) throw new Error("TELNYX_FROM_NUMBER is not set");
+
+  if (!isE164(to)) throw new Error(`Invalid recipient phone format: ${to}. Expected E.164.`);
+  if (!isE164(from)) throw new Error(`Invalid sender phone format: ${from}. Expected E.164.`);
+
+  const payload: Record<string, unknown> = {
+    from,
+    to,
+    body,
+  };
+
+  if (mediaUrls && mediaUrls.length > 0) {
+    payload.media_urls = mediaUrls;
+  }
+
+  const result = await client.messages.create(payload as Record<string, string | string[]>);
+  const smsResult = result.data as unknown as TelnyxSmsResult;
+  return {
+    messageId: smsResult.message_id,
+    status: smsResult.status,
+  };
+}
+
+export class TelnyxProvider implements SmsProvider {
+  async sendSms(
+    to: string,
+    body: string,
+    mediaUrls?: string[]
+  ): Promise<{ messageId: string; status: string }> {
+    return sendSms(to, body, mediaUrls);
+  }
+
+  validateWebhookSignature(req: Request): boolean {
+    const secret = process.env.TELNYX_WEBHOOK_SECRET;
+    if (!secret) return false;
+
+    const signature = req.headers.get("telnyx-signature");
+    if (!signature) return false;
+
+    const payload = JSON.stringify(req.body);
+
+    try {
+      const hmac = createHmac("sha256", secret);
+      const expected = `sha256=${hmac.update(payload).digest("hex")}`;
+
+      const sigBuf = Buffer.from(signature);
+      const expBuf = Buffer.from(expected);
+
+      if (sigBuf.length !== expBuf.length) return false;
+
+      let diff = 0;
+      for (let i = 0; i < sigBuf.length; i++) {
+        const sigByte = sigBuf[i] ?? 0;
+        const expByte = expBuf[i] ?? 0;
+        diff |= sigByte ^ expByte;
+      }
+      return diff === 0;
+    } catch {
+      return false;
+    }
+  }
+}
+
+let _provider: SmsProvider | null | undefined;
+
+export function createSmsProvider(): SmsProvider | null {
+  if (_provider === undefined) {
+    if (process.env.SMS_ENABLED !== "true") {
+      _provider = null;
+      return null;
+    }
+    switch (process.env.SMS_PROVIDER) {
+      case "telnyx": {
+        const client = getClient();
+        if (!client) {
+          _provider = null;
+          return null;
+        }
+        _provider = new TelnyxProvider();
+        break;
+      }
+      default:
+        _provider = null;
+    }
+  }
+  return _provider;
+}
+
+export async function smsSend(
+  to: string,
+  body: string,
+  mediaUrls?: string[]
+): Promise<boolean> {
+  const provider = createSmsProvider();
+  if (!provider) return false;
+
+  await provider.sendSms(to, body, mediaUrls);
+  return true;
+}
@@ -0,0 +1,19 @@
+declare module "telnyx" {
+  export interface MessageResult {
+    data: unknown;
+  }
+
+  export interface MessagesCreateParams {
+    from: string;
+    to: string;
+    body: string;
+    media_urls?: string[];
+  }
+
+  export class Telnyx {
+    constructor(apiKey: string);
+    messages: {
+      create(params: Record<string, string | string[]>): Promise<MessageResult>;
+    };
+  }
+}
@@ -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");
@@ -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");
@@ -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;
@@ -190,6 +190,20 @@
      "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
    }
  ]
 }
@@ -71,6 +71,10 @@ export function buildClient(overrides: Partial<ClientRow> = {}): ClientRow {
    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,
@@ -102,43 +102,55 @@ export const verification = pgTable("verification", {

 // ─── Tables ───────────────────────────────────────────────────────────────────

-export const clients = pgTable("clients", {
-  id: uuid("id").primaryKey().defaultRandom(),
-  name: text("name").notNull(),
-  email: text("email"),
-  phone: text("phone"),
-  address: text("address"),
-  notes: text("notes"),
-  emailOptOut: boolean("email_opt_out").notNull().default(false),
-  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(),
-});
+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"),
-  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(),
-});
+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"),
+    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(),
@@ -300,8 +312,28 @@ export const invoiceTipSplits = pgTable(
  (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",
  {
@@ -311,9 +343,11 @@ export const reminderLogs = pgTable(
      .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) => [unique().on(t.appointmentId, t.reminderType, t.channel)]
 );

 // ─── Impersonation ──────────────────────────────────────────────────────────
@@ -398,6 +398,8 @@ async function seedKnownUsers() {
        id: ADMIN_STAFF_ID,
        name: adminName,
        email: adminEmail,
+        oidcSub: adminEmail,
+        userId: adminEmail,
        role: "manager",
        isSuperUser: true,
        active: true,
@@ -424,6 +426,7 @@ async function seedKnownUsers() {
        name: "UAT Super User",
        email: "uat-super@groombook.dev",
        oidcSub: uatSuperOidcSub,
+        userId: uatSuperOidcSub,
        role: "manager",
        isSuperUser: true,
        active: true,
@@ -450,6 +453,7 @@ async function seedKnownUsers() {
        name: "UAT Staff Groomer",
        email: "uat-groomer@groombook.dev",
        oidcSub: uatStaffOidcSub,
+        userId: uatStaffOidcSub,
        role: "groomer",
        isSuperUser: false,
        active: true,
@@ -458,6 +462,37 @@ async function seedKnownUsers() {
    }
  }

+  // ── Staff: UAT Groomer Personas (SEED_UAT_GROOMER_EMAILS + SEED_UAT_GROOMER_NAMES) ──
+  const groomerEmails = process.env.SEED_UAT_GROOMER_EMAILS?.split(",").map((e) => e.trim()).filter(Boolean) ?? [];
+  const groomerNames = process.env.SEED_UAT_GROOMER_NAMES?.split(",").map((n) => n.trim()).filter(Boolean) ?? [];
+  const groomerCount = Math.min(groomerEmails.length, groomerNames.length);
+  for (let i = 0; i < groomerCount; i++) {
+    const email = groomerEmails[i]!;
+    const name = groomerNames[i]!;
+    // Use deterministic IDs in the 00000000-0000-0000-0000-000000000005+ range
+    const staffId = `00000000-0000-0000-0000-${String(5 + i).padStart(12, "0")}`;
+    const [existingGroomer] = await db
+      .select()
+      .from(schema.staff)
+      .where(eq(schema.staff.email, email))
+      .limit(1);
+
+    if (existingGroomer) {
+      console.log(`✓ Staff groomer '${existingGroomer.name}' already exists — skipping`);
+    } else {
+      await db.insert(schema.staff).values({
+        id: staffId,
+        name,
+        email,
+        oidcSub: email,
+        role: "groomer",
+        isSuperUser: false,
+        active: true,
+      });
+      console.log(`✓ Created staff groomer '${name}' (${email})`);
+    }
+  }
+
  // ── 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.
@@ -612,6 +647,8 @@ async function seed() {
        id: ADMIN_STAFF_ID,
        name: adminName,
        email: adminEmail,
+        oidcSub: adminEmail,
+        userId: adminEmail,
        role: "manager",
        isSuperUser: true,
        active: true,
@@ -623,6 +660,31 @@ async function seed() {
    console.log(`✓ Upserted admin staff '${adminName}' (${adminEmail})`);
  }

+  // ── UAT Groomer Personas (SEED_UAT_GROOMER_EMAILS + SEED_UAT_GROOMER_NAMES) ──
+  const groomerEmails = process.env.SEED_UAT_GROOMER_EMAILS?.split(",").map((e) => e.trim()).filter(Boolean) ?? [];
+  const groomerNames = process.env.SEED_UAT_GROOMER_NAMES?.split(",").map((n) => n.trim()).filter(Boolean) ?? [];
+  const groomerCount = Math.min(groomerEmails.length, groomerNames.length);
+  for (let i = 0; i < groomerCount; i++) {
+    const email = groomerEmails[i]!;
+    const name = groomerNames[i]!;
+    const staffId = `00000000-0000-0000-0000-${String(5 + i).padStart(12, "0")}`;
+    await db.insert(schema.staff)
+      .values({
+        id: staffId,
+        name,
+        email,
+        oidcSub: email,
+        role: "groomer",
+        isSuperUser: false,
+        active: true,
+      })
+      .onConflictDoUpdate({
+        target: schema.staff.email,
+        set: { id: staffId, name, role: "groomer", isSuperUser: false, active: true },
+      });
+    console.log(`✓ Upserted groomer '${name}' (${email})`);
+  }
+
  // ── Services ──
  // Upsert services using name as unique key. With deterministic IDs in
  // servicesDef and TRUNCATE clearing downstream tables first, this is
@@ -43,6 +43,9 @@ importers:
      stripe:
        specifier: ^22.0.0
        version: 22.0.1(@types/node@22.19.15)
+      telnyx:
+        specifier: ^1.23.0
+        version: 1.27.0
      zod:
        specifier: ^4.3.6
        version: 4.3.6
@@ -177,7 +180,7 @@ importers:
        version: 22.19.15
      drizzle-kit:
        specifier: ^0.30.4
-        version: 0.30.6
+        version: 0.30.4
      tsx:
        specifier: ^4.19.0
        version: 4.21.0
@@ -1696,9 +1699,6 @@ packages:
    resolution: {integrity: sha512-cifvXDhcqMwwTlTK04GBNeIe7yyo28Mfby85QXFe1Yk8nmi36Ab/5UQwptOx84SsoGNRg+EVSjwzfSZMy6pmlw==}
    engines: {node: '>=14'}

-  '@petamoriken/float16@3.9.3':
-    resolution: {integrity: sha512-8awtpHXCx/bNpFt4mt2xdkgtgVvKqty8VbjHI/WWWQuEw+KLzFot3f4+LkQY9YmOtq7A5GdOnqoIC8Pdygjk2g==}
-
  '@pkgjs/parseargs@0.11.0':
    resolution: {integrity: sha512-+1VkjdD0QBLPodGrJUeqarH8VAIvQODIbwh9XpP5Syisf7YoQgsJKPNFoqqLQlu+VQ/tVSshMR6loPMn8U+dPg==}
    engines: {node: '>=14'}
@@ -2830,8 +2830,8 @@ packages:
  dom-accessibility-api@0.6.3:
    resolution: {integrity: sha512-7ZgogeTnjuHbo+ct10G9Ffp0mif17idi0IyWNVA/wcwcm7NPOD/WEHVP3n7n3MhXqxoIYm8d6MuZohYWIZ4T3w==}

-  drizzle-kit@0.30.6:
-    resolution: {integrity: sha512-U4wWit0fyZuGuP7iNmRleQyK2V8wCuv57vf5l3MnG4z4fzNTjY/U13M8owyQ5RavqvqxBifWORaR3wIUzlN64g==}
+  drizzle-kit@0.30.4:
+    resolution: {integrity: sha512-B2oJN5UkvwwNHscPWXDG5KqAixu7AUzZ3qbe++KU9SsQ+cZWR4DXEPYcvWplyFAno0dhRJECNEhNxiDmFaPGyQ==}
    hasBin: true

  drizzle-orm@0.38.4:
@@ -2955,10 +2955,6 @@ packages:
    resolution: {integrity: sha512-aN97NXWF6AWBTahfVOIrB/NShkzi5H7F9r1s9mD3cDj4Ko5f2qhhVoYMibXF7GlLveb/D2ioWay8lxI97Ven3g==}
    engines: {node: '>=0.12'}

-  env-paths@3.0.0:
-    resolution: {integrity: sha512-dtJUTepzMW3Lm/NPxRf3wP4642UWhjL2sQxc+ym2YMj1m/H2zDNQOlezafzkHwn6sMstjHTwG6iQQsctDW/b1A==}
-    engines: {node: ^12.20.0 || ^14.13.1 || >=16.0.0}
-
  es-abstract@1.24.1:
    resolution: {integrity: sha512-zHXBLhP+QehSSbsS9Pt23Gg964240DPd6QCf8WpkqEXxQ7fhdZzYsocOr5u7apWonsS5EjZDmTF+/slGMyasvw==}
    engines: {node: '>= 0.4'}
@@ -3162,11 +3158,6 @@ packages:
  functions-have-names@1.2.3:
    resolution: {integrity: sha512-xckBUXyTIqT97tq2x2AMb+g163b5JFysYk0x4qxNFwbfQkmNZoiRHb6sPzI9/QV33WeuvVYBUIiD4NzNIyqaRQ==}

-  gel@2.2.0:
-    resolution: {integrity: sha512-q0ma7z2swmoamHQusey8ayo8+ilVdzDt4WTxSPzq/yRqvucWRfymRVMvNgmSC0XK7eNjjEZEcplxpgaNojKdmQ==}
-    engines: {node: '>= 18.0.0'}
-    hasBin: true
-
  generator-function@2.0.1:
    resolution: {integrity: sha512-SFdFmIJi+ybC0vjlHN0ZGVGHc3lgE0DxPAT0djjVg+kjOnSqclqmj0KQ7ykTOLP6YxoqOvuAODGdcHJn+43q3g==}
    engines: {node: '>= 0.4'}
@@ -3434,10 +3425,6 @@ packages:
  isexe@2.0.0:
    resolution: {integrity: sha512-RHxMLp9lnKHGHRng9QFhRCMbYAcVpn69smSGcq3f36xjgVVWThj4qqLbTLlq7Ssj8B+fIQ1EuCEGI2lKsyQeIw==}

-  isexe@3.1.5:
-    resolution: {integrity: sha512-6B3tLtFqtQS4ekarvLVMZ+X+VlvQekbe4taUkf/rhVO3d/h0M2rfARm/pXLcPEsjjMsFgrFgSrhQIxcSVrBz8w==}
-    engines: {node: '>=18'}
-
  istanbul-lib-coverage@3.2.2:
    resolution: {integrity: sha512-O8dpsF+r0WV/8MNRKfnmrtCWhuKjxrq2w+jpzBL5UZKTi2LeVWnWOmWRxFlesJONmc+wLAGvKQZEOanko0LFTg==}
    engines: {node: '>=8'}
@@ -3619,6 +3606,9 @@ packages:
  lodash.debounce@4.0.8:
    resolution: {integrity: sha512-FT1yDzDYEoYWhnSGnpE/4Kj1fLZkDFyqRb7fNt6FdYOSxlUWAtp42Eh6Wb0rGIv/m9Bgo7x4GhQbm5Ys4SG5ow==}

+  lodash.isplainobject@4.0.6:
+    resolution: {integrity: sha512-oSXzaWypCMHkPC3NvBEaPHf0KsA5mvPrOPgQWDsbg8n7orZ290M0BmC/jgRZ4vcJ6DTAhjrsSYgdsW/F+MFOBA==}
+
  lodash.merge@4.6.2:
    resolution: {integrity: sha512-0KpjqXRVvrYyCsX1swR/XTK0va6VQkQM6MNo7PqW77ByjAhoARA8EfrP1N4+KlKj8YS0ZUCtRT/YUuhyYDujIQ==}

@@ -3851,6 +3841,10 @@ packages:
    resolution: {integrity: sha512-vYt7UD1U9Wg6138shLtLOvdAu+8DsC/ilFtEVHcH+wydcSpNE20AfSOduf6MkRFahL5FY7X1oU7nKVZFtfq8Fg==}
    engines: {node: '>=6'}

+  qs@6.15.1:
+    resolution: {integrity: sha512-6YHEFRL9mfgcAvql/XhwTvf5jKcOiiupt2FiJxHkiX1z4j7WL8J/jRHYLluORvc1XxB5rV20KoeK00gVJamspg==}
+    engines: {node: '>=0.6'}
+
  randombytes@2.1.0:
    resolution: {integrity: sha512-vYl3iOX+4CKUWuxGi9Ukhie6fsqXqS9FE2Zaic4tNFD2N2QQaXOMFbuKK4QmDHC0JO6B1Zp41J0LpT0oR68amQ==}

@@ -4046,10 +4040,6 @@ packages:
    resolution: {integrity: sha512-7++dFhtcx3353uBaq8DDR4NuxBetBzC7ZQOhmTQInHEd6bSrXdiEyzCvG07Z44UYdLShWUyXt5M/yhz8ekcb1A==}
    engines: {node: '>=8'}

-  shell-quote@1.8.3:
-    resolution: {integrity: sha512-ObmnIF4hXNg1BqhnHmgbDETF8dLPCggZWBjkQfhZpbszZnYur5DUljTcCHii5LC3J5E0yeO/1LIMyH+UvHQgyw==}
-    engines: {node: '>= 0.4'}
-
  side-channel-list@1.0.0:
    resolution: {integrity: sha512-FCLHtRD/gnpCiCHEiJLOwdmFP+wzCmDEkc9y7NsYxeF4u7Btsn1ZuwgwJGxImImHicJArLP4R0yX4c2KCrMrTA==}
    engines: {node: '>= 0.4'}
@@ -4188,6 +4178,10 @@ packages:
    resolution: {integrity: sha512-g9ljZiwki/LfxmQADO3dEY1CbpmXT5Hm2fJ+QaGKwSXUylMybePR7/67YW7jOrrvjEgL1Fmz5kzyAjWVWLlucg==}
    engines: {node: '>=6'}

+  telnyx@1.27.0:
+    resolution: {integrity: sha512-cVbP3jEW4TbmNL5U0UbZc3OkLg+6dHRnMYByYfJnrGw5ZRn0XKb17Hx3fLMWmGgRFow7eqVP4hlCogbIB6T3+w==}
+    engines: {node: ^6 || >=8}
+
  temp-dir@2.0.0:
    resolution: {integrity: sha512-aoBAniQmmwtcKp/7BzsH8Cxzv8OL736p7v1ihGb5e9DJ9kTwGWHrQrVB5+lfVDzfGrdRzXch+ig7LHaY1JTOrg==}
    engines: {node: '>=8'}
@@ -4262,6 +4256,9 @@ packages:
    engines: {node: '>=18.0.0'}
    hasBin: true

+  tweetnacl@1.0.3:
+    resolution: {integrity: sha512-6rt+RN7aOi1nGMyC4Xa5DdYiukl2UWCbcJft7YhxReBGQD7OAM8Pbxw6YMo4r2diNEA8FEmu32YOn9rhaiE5yw==}
+
  type-check@0.4.0:
    resolution: {integrity: sha512-XleUoc9uwGXqjWwXaUTZAmzMcFZ5858QA2vvx1Ur5xIcixXIP+8LnFDgRplU30us6teqdlskFfu+ae4K79Ooew==}
    engines: {node: '>= 0.8.0'}
@@ -4351,6 +4348,10 @@ packages:
    resolution: {integrity: sha512-+NYs2QeMWy+GWFOEm9xnn6HCDp0l7QBD7ml8zLUmJ+93Q5NF0NocErnwkTkXVFNiX3/fpC6afS8Dhb/gz7R7eg==}
    hasBin: true

+  uuid@9.0.1:
+    resolution: {integrity: sha512-b+1eJOlsR9K8HJpow9Ok3fiWOWSIcIzXodvv0rQjVoOVNpWMpxf1wZNpt4y9h10odCNrqnYp1OBzRktckBe3sA==}
+    hasBin: true
+
  victory-vendor@37.3.6:
    resolution: {integrity: sha512-SbPDPdDBYp+5MJHhBCAyI7wKM3d5ivekigc2Dk2s7pgbZ9wIgIBYGVw4zGHBml/qTFbexrofXW6Gu4noGxrOwQ==}

@@ -4487,11 +4488,6 @@ packages:
    engines: {node: '>= 8'}
    hasBin: true

-  which@4.0.0:
-    resolution: {integrity: sha512-GlaYyEb07DPxYCKhKzplCWBJtvxZcZMrL+4UkrTSJHHPyZU4mYYTv3qaOe77H7EODLSSopAUFAc6W8U4yqvscg==}
-    engines: {node: ^16.13.0 || >=18.0.0}
-    hasBin: true
-
  why-is-node-running@2.3.0:
    resolution: {integrity: sha512-hUrmaWBdVDcxvYqnyh09zunKzROWjbZTiNy8dBEjkS7ehEDQibXJ7XvlmtbwuTclUiIyN+CyXQD4Vmko8fNm8w==}
    engines: {node: '>=8'}
@@ -6223,8 +6219,6 @@ snapshots:

  '@opentelemetry/semantic-conventions@1.40.0': {}

-  '@petamoriken/float16@3.9.3': {}
-
  '@pkgjs/parseargs@0.11.0':
    optional: true

@@ -7420,13 +7414,12 @@ snapshots:

  dom-accessibility-api@0.6.3: {}

-  drizzle-kit@0.30.6:
+  drizzle-kit@0.30.4:
    dependencies:
      '@drizzle-team/brocli': 0.10.2
      '@esbuild-kit/esm-loader': 2.6.5
      esbuild: 0.19.12
      esbuild-register: 3.6.0(esbuild@0.19.12)
-      gel: 2.2.0
    transitivePeerDependencies:
      - supports-color

@@ -7463,8 +7456,6 @@ snapshots:

  entities@6.0.1: {}

-  env-paths@3.0.0: {}
-
  es-abstract@1.24.1:
    dependencies:
      array-buffer-byte-length: 1.0.2
@@ -7826,17 +7817,6 @@ snapshots:

  functions-have-names@1.2.3: {}

-  gel@2.2.0:
-    dependencies:
-      '@petamoriken/float16': 3.9.3
-      debug: 4.4.3
-      env-paths: 3.0.0
-      semver: 7.7.4
-      shell-quote: 1.8.3
-      which: 4.0.0
-    transitivePeerDependencies:
-      - supports-color
-
  generator-function@2.0.1: {}

  gensync@1.0.0-beta.2: {}
@@ -8101,8 +8081,6 @@ snapshots:

  isexe@2.0.0: {}

-  isexe@3.1.5: {}
-
  istanbul-lib-coverage@3.2.2: {}

  istanbul-lib-report@3.0.1:
@@ -8271,6 +8249,8 @@ snapshots:

  lodash.debounce@4.0.8: {}

+  lodash.isplainobject@4.0.6: {}
+
  lodash.merge@4.6.2: {}

  lodash.sortby@4.7.0: {}
@@ -8469,6 +8449,10 @@ snapshots:

  punycode@2.3.1: {}

+  qs@6.15.1:
+    dependencies:
+      side-channel: 1.1.0
+
  randombytes@2.1.0:
    dependencies:
      safe-buffer: 5.2.1
@@ -8703,8 +8687,6 @@ snapshots:

  shebang-regex@3.0.0: {}

-  shell-quote@1.8.3: {}
-
  side-channel-list@1.0.0:
    dependencies:
      es-errors: 1.3.0
@@ -8858,6 +8840,14 @@ snapshots:

  tapable@2.3.0: {}

+  telnyx@1.27.0:
+    dependencies:
+      lodash.isplainobject: 4.0.6
+      qs: 6.15.1
+      safe-buffer: 5.2.1
+      tweetnacl: 1.0.3
+      uuid: 9.0.1
+
  temp-dir@2.0.0: {}

  tempy@0.6.0:
@@ -8928,6 +8918,8 @@ snapshots:
    optionalDependencies:
      fsevents: 2.3.3

+  tweetnacl@1.0.3: {}
+
  type-check@0.4.0:
    dependencies:
      prelude-ls: 1.2.1
@@ -9024,6 +9016,8 @@ snapshots:

  uuid@8.3.2: {}

+  uuid@9.0.1: {}
+
  victory-vendor@37.3.6:
    dependencies:
      '@types/d3-array': 3.2.2
@@ -9201,10 +9195,6 @@ snapshots:
    dependencies:
      isexe: 2.0.0

-  which@4.0.0:
-    dependencies:
-      isexe: 3.1.5
-
  why-is-node-running@2.3.0:
    dependencies:
      siginfo: 2.0.0
Author	SHA1	Message	Date
groombook-engineer[bot]	2577e33c50	feat(GRO-653): add portal session middleware and server-side audit logging (#300 ) * feat(GRO-653): add portal session middleware and server-side audit logging - Add validatePortalSession middleware that reads X-Impersonation-Session-Id header, queries impersonationSessions, and sets portalClientId + portalSessionId on the context - Add portalAudit middleware that logs all portal requests to impersonationAuditLogs table - Apply both middlewares to the portalRouter - Replace all getClientIdFromSession() calls with c.get("portalClientId") - Remove getClientIdFromSession() helper and inline session checks in waitlist routes - Consistent session.expiry > new Date() check across all routes Co-Authored-By: Paperclip <noreply@paperclip.ing> * fix(GRO-653): remove unused sessionId variable and and import Fix lint errors flagged by QA: - Remove unused `sessionId` variable from PATCH waitlist handler - Remove unused `and` import from portal.ts 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Paperclip <noreply@paperclip.ing> --------- Co-authored-by: Flea Flicker <fleaflicker@groombook.farh.net> Co-authored-by: Paperclip <noreply@paperclip.ing> Co-authored-by: Flea Flicker <flea-flicker@groombook.ai>	2026-04-16 11:20:36 +00:00
groombook-cto[bot]	b904418628	fix(GRO-640): replace N+1 queries in sendConfirmationEmail with single JOIN query CTO approved: clean perf fix replacing 4 sequential DB queries with a single JOIN. QA approved.	2026-04-16 10:14:06 +00:00
groombook-cto[bot]	5ff54ce8f9	fix(GRO-689): only validate authorizationUrl hostname, add OIDC_INTERNAL_BASE in dev (#302 ) fix(GRO-689): only validate authorizationUrl hostname, add OIDC_INTERNAL_BASE in dev	2026-04-16 05:18:58 +00:00
groombook-cto[bot]	a2cfdfef74	Merge branch 'main' into fix/gro-689-oidc-hostname-validation	2026-04-16 05:15:28 +00:00
groombook-cto[bot]	ab9384d38e	feat(GRO-690): add groomer persona seed support via env vars (#303 ) feat(GRO-690): add groomer persona seed support via env vars	2026-04-16 05:11:07 +00:00
groombook-cto[bot]	6ba6da08b2	Merge branch 'main' into fix/gro-689-oidc-hostname-validation	2026-04-16 05:08:23 +00:00
Flea Flicker	29a726fa3d	feat(GRO-690): add groomer persona seed support via env vars Extend seed.ts with SEED_UAT_GROOMER_EMAILS and SEED_UAT_GROOMER_NAMES env vars for persistent groomer personas (sam@sarah). Works in both SEED_KNOWN_USERS_ONLY=true and full seed modes. Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-16 05:04:52 +00:00
Flea Flicker	cdf4d6c4b1	fix(GRO-689): only validate authorizationUrl hostname, add OIDC_INTERNAL_BASE in dev - Move hostname validation to run AFTER OIDC_INTERNAL_BASE replacement (was checking raw discovery URLs before replacement caused false positives) - Only validate authorizationUrl hostname against issuer; token/userinfo are server-to-server and may legitimately use internal hostnames - Infra: add OIDC_INTERNAL_BASE env var to dev overlay (was missing, matches UAT) Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-16 04:55:17 +00:00
groombook-cto[bot]	ffb3cd139a	Merge pull request #296 from groombook/fix/gro-666-uat-seed-better-auth-user-id fix(seed): populate userId for UAT staff and SEED_ADMIN_EMAIL staff	2026-04-16 04:17:14 +00:00
Flea Flicker	85cff19c59	fix(GRO-666): make migration 0028 idempotent to resolve E2E failure - Add IF NOT EXISTS to all ADD COLUMN statements (schema already has these columns) - Use DROP CONSTRAINT IF EXISTS for both possible auto-generated constraint names - Idempotent: safe to re-run on databases that already have the schema changes cc @cpfarhood Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-15 15:25:20 +00:00
Flea Flicker	376180ab9d	fix: make email required in createClientSchema to match NOT NULL column Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>	2026-04-15 10:52:45 +00:00
Flea Flicker	da16ac8ac2	Add missing DB indexes, NOT NULL on clients.email, and S3 error handling - Add 4 indexes on appointments: client_id, staff_id, start_time, status - Add index on pets.client_id - Add index on clients.email - Change clients.email to NOT NULL with backfill migration - Wrap S3 deleteObject calls in try/catch in pets photo endpoints - Update POST /clients test to include required email field Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-15 10:09:57 +00:00
Flea Flicker	16dd513521	fix(seed): populate userId for UAT staff and SEED_ADMIN_EMAIL staff GRO-666: resolveStaffMiddleware returns 403 for UAT users because staff records have NULL userId after seed. This change populates userId (and oidcSub) for all staff created via seedKnownUsers() and the main seed path using the same value as the OIDC sub. Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-15 09:37:51 +00:00
Flea Flicker	66a6659ccd	feat(GRO-600): extend reminder scheduler to send SMS alongside email - Add SMS opt-in fields to clients schema (smsOptIn, smsConsentDate, smsOptOutDate, smsConsentText) - Add channel column to reminderLogs with per-channel idempotency - Create SMS service with Telnyx SDK integration and E.164 validation - Update reminders service to conditionally send SMS to opted-in clients - Add TCPA opt-out text to SMS reminders - Graceful degradation: catch SMS errors without blocking email - Fix: use clients.phone instead of non-existent clients.phoneE164 - Update clients route to expose SMS fields in API - Add telnyx dependency to API package - Create database migration 0028_sms_reminders Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-15 09:23:24 +00:00
groombook-cto[bot]	71c229f83b	fix(GRO-634): implement auth & authorization security hardening (8 findings) fix(GRO-634): implement auth & authorization security hardening (8 findings)	2026-04-15 07:00:23 +00:00
groombook-cto[bot]	1ef740c361	Merge branch 'main' into feature/gro-622-security-hardening	2026-04-15 06:53:50 +00:00
groombook-cto[bot]	d433c902b4	fix(GRO-637): invoice status transitions, tip-split validation, refund idempotency, and tip-split response format * Fix invoice status transitions, tip-split validation, refund idempotency, and tip-split response format - Add ALLOWED_TRANSITIONS state machine for invoice status changes (GRO-637) - Replace floating-point tip-split validation with integer basis-points math - Add idempotency key support to refund endpoint with new refunds table - Return full invoice shape from POST /:id/tip-splits matching GET response - All existing tests pass Co-Authored-By: Paperclip <noreply@paperclip.ing> * fix(invoices): wrap refund flow in transaction for idempotency safety - Wrap idempotency check + processRefund() + db.insert() in db.transaction() - This prevents duplicate Stripe refunds if the DB insert fails after Stripe processes the refund - Add migration 0027_refunds for the refunds table (was missing) - Removes out-of-scope changes from PR #278 (csrf.ts, appointmentGroups, appointments, book, groomingLogs, services, stripe-webhooks) Fixes GRO-637 per CTO review Co-Authored-By: Paperclip <noreply@paperclip.ing> * fix(api): wire up CSRF middleware for protected routes Register csrfMiddleware in the protected API routes after authMiddleware and resolveStaffMiddleware to protect against CSRF attacks on state- changing operations (POST, PUT, PATCH, DELETE). Addresses CTO review feedback on PR #278. * fix(api): remove CSRF middleware that breaks POST/PUT/PATCH/DELETE The CSRF middleware requires x-csrf-token header but the frontend never sends it, which would break all mutating operations with 403 errors. CSRF protection should be implemented in a separate coordinated PR with frontend changes. Co-Authored-By: Paperclip <noreply@paperclip.ing> --------- Co-authored-by: Paperclip <noreply@paperclip.ing> Co-authored-by: Flea Flicker <flea-flicker@groombook.ai>	2026-04-15 06:04:38 +00:00
groombook-cto[bot]	dc3b3ddcb7	fix(auth): add email-based staff auto-linking in resolveStaffMiddleware Auto-link staff records by email when userId is NULL on first authenticated request. Resolves GRO-667 UAT 403 blocker. Co-Authored-By: Flea Flicker <noreply@anthropic.com>	2026-04-15 05:50:59 +00:00
groombook-cto[bot]	31997e33c0	fix(GRO-638): appointment scheduling correctness and client deletion integrity fix(GRO-638): appointment scheduling correctness and client deletion integrity	2026-04-15 05:03:11 +00:00
groombook-cto[bot]	e118607fd6	Merge branch 'main' into fix/gro-627-scheduling-correctness	2026-04-15 05:00:12 +00:00
groombook-cto[bot]	e1e13d5091	fix(GRO-636): input validation fixes for 5 API routes Adds Zod validation across 5 API routes: 1. invoices GET / — query param validation (uuid, enum, int bounds) 2. book POST / — future-time refinement on startTime 3. appointments — recurrence series capped at 1 year 4. services — durationMinutes capped at 480 (8 hours) 5. stripe-webhooks — UUID validation on invoice IDs before DB lookup Closes GRO-636 Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-15 04:26:20 +00:00
groombook-ceo[bot]	80b66fe20c	fix(GRO-655): create corepack cache dir in builder stage Co-authored-by: groombook-cto[bot] <269737991+groombook-cto[bot]@users.noreply.github.com> Co-authored-by: Paperclip <noreply@paperclip.ing>	2026-04-15 02:08:54 +00:00
Flea Flicker	233e68769a	fix(GRO-634): rename unused 'clauses' param to _clauses in confirmation test Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-14 23:23:51 +00:00
Flea Flicker	f7b8b7e668	fix(GRO-634): atomic confirmation token in book.ts, correct RBAC error message - Replace SELECT-then-UPDATE with atomic UPDATE ... WHERE token=? AND status='pending' RETURNING * to prevent confirmation token replay attacks (TOCTOU race condition) - Fix requireRoleOrSuperUser() error message: swap the conditional branches so 'Forbidden: super user privileges required' is returned when user lacks role, and 'Forbidden: role X is not permitted' when user is not superuser - Add 'and' mock export to confirmation.test.ts and rbac.test.ts for new query patterns - Update test expectations to match corrected error message semantics	2026-04-14 23:23:48 +00:00
Flea Flicker	1cce354413	fix(GRO-622): security hardening for auth, authorization, and token handling - Remove placeholder secret fallback in AUTH_DISABLED mode (auth.ts) - Make auth-provider setup atomic via DB transaction (setup.ts) - Fix confirmation token replay with atomic UPDATE...WHERE (book.ts) - Add strict CORS origin allowlist validation (index.ts) - Validate OIDC discovery URL hostname matches issuer (auth.ts) - Use timingSafeEqual for iCal token comparison (calendar.ts) - Add in-memory rate limiting to setup endpoints (setup.ts) - Keep RBAC error message correct (rbac.ts - already correct in main) Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-14 23:23:48 +00:00
Paperclip	ab4b9fe6fc	fix(GRO-638): appointment scheduling correctness and client deletion integrity - Recurrence conflict checking: check ALL occurrences in recurrence loop - Cascade update transaction safety: add conflict checking for shifted appointments - Client deletion integrity: check for existing appointments before delete - Email notification error handling: add retry wrapper (max 2 retries, 1s delay) - Null guards on recurrence result: validate inserted after each insert Co-Authored-By: Paperclip <noreply@paperclip.ing>	2026-04-14 14:31:52 +00:00