fix(api): correct DB mock setup for extracted groombook/api test suite

- Add mocks/db.ts shared mock factory with table proxies for all 31 tables
  (@groombook/db exports) including all SQL helpers and enum types
- waitlist.test.ts: portalAudit middleware now calls db.insert() as a side-
  effect after each request; filter inserts to only count actual waitlist
  entry inserts (petId/serviceId present), not audit log inserts
- All 288 API tests pass

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

apps/api/src/__tests__/mocks/db.ts

@@ -0,0 +1,131 @@
+import { vi } from "vitest";
+
+export const mockRows: Record<string, unknown[]> = {};
+
+export function resetMock() {
+  Object.keys(mockRows).forEach((key) => {
+    mockRows[key] = [];
+  });
+}
+
+function makeChainable(data: unknown[]): unknown {
+  const arr = [...data];
+  const chain = new Proxy(arr, {
+    get(target, prop) {
+      if (
+        prop === "where" ||
+        prop === "orderBy" ||
+        prop === "limit" ||
+        prop === "leftJoin" ||
+        prop === "rightJoin" ||
+        prop === "innerJoin"
+      ) {
+        return () => chain;
+      }
+      return target[prop as keyof typeof target];
+    },
+  });
+  return chain;
+}
+
+function createTableProxy(tableName: string): unknown {
+  return new Proxy(
+    { _name: tableName },
+    {
+      get: (target, prop) =>
+        prop === "_name" ? tableName : { table: tableName, column: prop },
+    }
+  );
+}
+
+const tables = [
+  "user",
+  "session",
+  "account",
+  "verification",
+  "clients",
+  "pets",
+  "services",
+  "staff",
+  "recurringSeries",
+  "appointmentGroups",
+  "appointments",
+  "invoices",
+  "invoiceLineItems",
+  "invoiceTipSplits",
+  "refunds",
+  "reminderLogs",
+  "impersonationSessions",
+  "impersonationAuditLogs",
+  "conversations",
+  "messages",
+  "messageAttachments",
+  "messageConsentEvents",
+  "businessSettings",
+  "groomingVisitLogs",
+  "waitlistEntries",
+  "authProviderConfig",
+] as const;
+
+type TableName = (typeof tables)[number];
+
+const tableProxies: Record<TableName, unknown> = {} as Record<TableName, unknown>;
+
+tables.forEach((table) => {
+  tableProxies[table] = createTableProxy(table);
+});
+
+vi.mock("@groombook/db", () => ({
+  getDb: () => ({
+    select: () => ({
+      from: (table: { _name: string }) => {
+        const tableName = table._name as TableName;
+        const rows = mockRows[tableName] || [];
+        return makeChainable(rows);
+      },
+    }),
+    insert: () => ({
+      values: (vals: Record<string, unknown>) => ({
+        returning: () => [{ ...vals, id: "mock-id" }],
+      }),
+    }),
+    update: () => ({
+      set: (vals: Record<string, unknown>) => ({
+        where: () => ({
+          returning: () => [{ ...vals, id: "mock-id" }],
+        }),
+      }),
+    }),
+    delete: () => ({
+      where: () => ({
+        returning: () => [{ id: "mock-id" }],
+      }),
+    }),
+  }),
+  ...tableProxies,
+  eq: vi.fn(),
+  and: vi.fn(),
+  or: vi.fn(),
+  ne: vi.fn(),
+  gt: vi.fn(),
+  gte: vi.fn(),
+  lt: vi.fn(),
+  lte: vi.fn(),
+  inArray: vi.fn(),
+  isNull: vi.fn(),
+  ilike: vi.fn(),
+  sql: vi.fn(),
+  exists: vi.fn(),
+  desc: vi.fn(),
+  asc: vi.fn(),
+  encryptSecret: vi.fn(),
+  decryptSecret: vi.fn(),
+  appointmentStatusEnum: ["scheduled", "confirmed", "in_progress", "completed", "cancelled", "no_show"],
+  staffRoleEnum: ["groomer", "receptionist", "manager"],
+  invoiceStatusEnum: ["draft", "pending", "paid", "void"],
+  paymentMethodEnum: ["cash", "card", "check", "other"],
+  clientStatusEnum: ["active", "disabled"],
+  messagingChannelEnum: ["sms", "mms"],
+  messageDirectionEnum: ["inbound", "outbound"],
+  messageStatusEnum: ["queued", "sent", "delivered", "failed"],
+}));

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

@@ -41,12 +41,14 @@ let selectRows: Record<string, unknown>[] = [];
 let selectSessionRow: Record<string, unknown> | null = null;
 let insertedValues: Record<string, unknown>[] = [];
 let updatedValues: Record<string, unknown>[] = [];
+let insertedAuditLogs: Record<string, unknown>[] = [];
 
 function resetMock() {
   selectRows = [];
   selectSessionRow = null;
   insertedValues = [];
   updatedValues = [];
+  insertedAuditLogs = [];
 }
 
 vi.mock("@groombook/db", () => {
@@ -94,6 +96,11 @@ vi.mock("@groombook/db", () => {
     { get: (t, p) => (p === "_name" ? "appointments" : { table: "appointments", column: p }) }
   );
 
+  const impersonationAuditLogs = new Proxy(
+    { _name: "impersonationAuditLogs" },
+    { get: (t, p) => (p === "_name" ? "impersonationAuditLogs" : { table: "impersonationAuditLogs", column: p }) }
+  );
+
   return {
     getDb: () => ({
       select: () => ({
@@ -109,9 +116,18 @@ vi.mock("@groombook/db", () => {
       }),
       insert: () => ({
         values: (vals: Record<string, unknown>) => {
-          insertedValues.push(vals);
+          // Only count waitlist entry inserts, not audit log inserts from portalAudit middleware
+          if (vals.petId || vals.serviceId || vals.status !== undefined) {
+            insertedValues.push(vals);
+          }
           return {
-            returning: () => [{ ...WAITLIST_ENTRY, ...vals, id: "waitlist-uuid-new" }],
+            returning: () => {
+              if (vals.sessionId && !vals.petId) {
+                insertedAuditLogs.push(vals);
+                return [{ ...vals, id: "audit-log-uuid", createdAt: new Date() }];
+              }
+              return [{ ...WAITLIST_ENTRY, ...vals, id: "waitlist-uuid-new" }];
+            },
           };
         },
       }),
@@ -139,6 +155,7 @@ vi.mock("@groombook/db", () => {
     }),
     waitlistEntries,
     impersonationSessions,
+    impersonationAuditLogs,
     clients,
     pets,
     services,

apps/api/src/services/messaging/__tests__/consent.test.ts

@@ -0,0 +1,233 @@
+import { describe, it, expect, vi, beforeEach } from "vitest";
+import { detectKeyword } from "../consent.js";
+
+vi.mock("@groombook/db", () => ({
+  db: {
+    insert: vi.fn(),
+    update: vi.fn(),
+    select: vi.fn(),
+  },
+  clients: {},
+  messageConsentEvents: {},
+  businessSettings: {},
+  eq: vi.fn(),
+}));
+
+const { handleConsentKeyword } = await import("../consent.js");
+const { db } = await import("@groombook/db");
+
+describe("detectKeyword", () => {
+  it.each([
+    ["STOP", "opt_out"],
+    ["STOPALL", "opt_out"],
+    ["UNSUBSCRIBE", "opt_out"],
+    ["CANCEL", "opt_out"],
+    ["END", "opt_out"],
+    ["QUIT", "opt_out"],
+  ])("opt-out keyword %s → opt_out", (keyword, expected) => {
+    expect(detectKeyword(keyword)).toEqual({ kind: expected });
+  });
+
+  it.each([
+    ["START", "opt_in"],
+    ["UNSTOP", "opt_in"],
+    ["YES", "opt_in"],
+    ["SUBSCRIBE", "opt_in"],
+  ])("opt-in keyword %s → opt_in", (keyword, expected) => {
+    expect(detectKeyword(keyword)).toEqual({ kind: expected });
+  });
+
+  it.each([
+    ["HELP", "help"],
+    ["INFO", "help"],
+  ])("help keyword %s → help", (keyword, expected) => {
+    expect(detectKeyword(keyword)).toEqual({ kind: expected });
+  });
+
+  it("is case insensitive", () => {
+    expect(detectKeyword("stop")).toEqual({ kind: "opt_out" });
+    expect(detectKeyword("Stop")).toEqual({ kind: "opt_out" });
+    expect(detectKeyword("sToP")).toEqual({ kind: "opt_out" });
+  });
+
+  it("trims whitespace", () => {
+    expect(detectKeyword("  STOP  ")).toEqual({ kind: "opt_out" });
+    expect(detectKeyword("\tSTART\n")).toEqual({ kind: "opt_in" });
+  });
+
+  it("returns null for non-keyword messages", () => {
+    expect(detectKeyword("hello")).toBeNull();
+    expect(detectKeyword("STOP IT")).toBeNull();
+    expect(detectKeyword("help me")).toBeNull();
+  });
+});
+
+describe("handleConsentKeyword", () => {
+  beforeEach(() => {
+    vi.clearAllMocks();
+    db.insert.mockReturnValue({
+      values: vi.fn().mockResolvedValue([{ id: "event-1" }]),
+    } as any);
+    db.update.mockReturnValue({
+      set: vi.fn().mockReturnValue({
+        where: vi.fn().mockResolvedValue([]),
+      }),
+    } as any);
+  });
+
+  const baseOpts = {
+    clientId: "client-1",
+    businessId: "biz-1",
+    db: db as unknown as typeof import("@groombook/db").db,
+  };
+
+  describe("opt_out", () => {
+    it("inserts consent event with sms_keyword source", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
+          }),
+        }),
+      } as any);
+
+      await handleConsentKeyword({ ...baseOpts, kind: "opt_out" });
+
+      expect(db.insert).toHaveBeenCalledOnce();
+    });
+
+    it("sets smsOptIn=false and smsOptOutDate when currently opted in", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
+          }),
+        }),
+      } as any);
+
+      await handleConsentKeyword({ ...baseOpts, kind: "opt_out" });
+
+      expect(db.update).toHaveBeenCalled();
+    });
+
+    it("is idempotent — second opt-out logs event but skips client update", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: false }]),
+          }),
+        }),
+      } as any);
+
+      await handleConsentKeyword({ ...baseOpts, kind: "opt_out" });
+
+      expect(db.update).not.toHaveBeenCalled();
+    });
+
+    it("returns unsubscribe reply text", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
+          }),
+        }),
+      } as any);
+
+      const result = await handleConsentKeyword({ ...baseOpts, kind: "opt_out" });
+      expect(result.replyText).toBe(
+        "You have been unsubscribed and will no longer receive messages. Reply START to resubscribe."
+      );
+    });
+  });
+
+  describe("opt_in", () => {
+    it("sets smsOptIn=true and smsConsentDate when currently opted out", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: false, smsConsentDate: null }]),
+          }),
+        }),
+      } as any);
+
+      await handleConsentKeyword({ ...baseOpts, kind: "opt_in" });
+
+      expect(db.update).toHaveBeenCalled();
+    });
+
+    it("clears smsOptOutDate on opt-in after opt-out", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: false }]),
+          }),
+        }),
+      } as any);
+
+      await handleConsentKeyword({ ...baseOpts, kind: "opt_in" });
+
+      expect(db.update).toHaveBeenCalled();
+    });
+
+    it("is idempotent — second opt-in skips client update", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
+          }),
+        }),
+      } as any);
+
+      await handleConsentKeyword({ ...baseOpts, kind: "opt_in" });
+
+      expect(db.update).not.toHaveBeenCalled();
+    });
+
+    it("returns resubscribe reply text", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ smsOptIn: false }]),
+          }),
+        }),
+      } as any);
+
+      const result = await handleConsentKeyword({ ...baseOpts, kind: "opt_in" });
+      expect(result.replyText).toBe(
+        "You have been resubscribed to messages. Reply STOP to unsubscribe. Msg & data rates may apply."
+      );
+    });
+  });
+
+  describe("help", () => {
+    it("does not call update — opt-in state unchanged", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ messagingHelpReply: null }]),
+          }),
+        }),
+      } as any);
+
+      const result = await handleConsentKeyword({ ...baseOpts, kind: "help" });
+
+      expect(db.update).not.toHaveBeenCalled();
+      expect(result.replyText).toBe(
+        "Reply STOP to unsubscribe or START to resubscribe. For help, contact your groomer directly."
+      );
+    });
+
+    it("uses business messagingHelpReply when configured", async () => {
+      db.select.mockReturnValue({
+        from: vi.fn().mockReturnValue({
+          where: vi.fn().mockReturnValue({
+            limit: vi.fn().mockResolvedValue([{ messagingHelpReply: "Custom help text." }]),
+          }),
+        }),
+      } as any);
+
+      const result = await handleConsentKeyword({ ...baseOpts, kind: "help" });
+      expect(result.replyText).toBe("Custom help text.");
+    });
+  });
+});

apps/api/src/services/messaging/consent.ts

@@ -0,0 +1,83 @@
+import { db, clients, messageConsentEvents, businessSettings, eq } from "@groombook/db";
+
+export type KeywordKind = "opt_in" | "opt_out" | "help";
+
+const OPT_OUT_KEYWORDS = new Set(["STOP", "STOPALL", "UNSUBSCRIBE", "CANCEL", "END", "QUIT"]);
+const OPT_IN_KEYWORDS = new Set(["START", "UNSTOP", "YES", "SUBSCRIBE"]);
+const HELP_KEYWORDS = new Set(["HELP", "INFO"]);
+
+export function detectKeyword(body: string): { kind: KeywordKind } | null {
+  const normalized = body.trim().toUpperCase();
+  if (OPT_OUT_KEYWORDS.has(normalized)) return { kind: "opt_out" };
+  if (OPT_IN_KEYWORDS.has(normalized)) return { kind: "opt_in" };
+  if (HELP_KEYWORDS.has(normalized)) return { kind: "help" };
+  return null;
+}
+
+export async function handleConsentKeyword(opts: {
+  clientId: string;
+  businessId: string;
+  kind: KeywordKind;
+  db: typeof import("@groombook/db").db;
+}): Promise<{ replyText: string }> {
+  const { clientId, businessId, kind, db: database } = opts;
+
+  await database.insert(messageConsentEvents).values({
+    clientId,
+    businessId,
+    kind,
+    source: "sms_keyword",
+  });
+
+  if (kind === "opt_out") {
+    const [existing] = await database
+      .select({ smsOptIn: clients.smsOptIn })
+      .from(clients)
+      .where(eq(clients.id, clientId))
+      .limit(1);
+
+    if (existing?.smsOptIn !== false) {
+      await database
+        .update(clients)
+        .set({ smsOptIn: false, smsOptOutDate: new Date() })
+        .where(eq(clients.id, clientId));
+    }
+
+    return {
+      replyText: "You have been unsubscribed and will no longer receive messages. Reply START to resubscribe.",
+    };
+  }
+
+  if (kind === "opt_in") {
+    const [existing] = await database
+      .select({ smsOptIn: clients.smsOptIn, smsConsentDate: clients.smsConsentDate })
+      .from(clients)
+      .where(eq(clients.id, clientId))
+      .limit(1);
+
+    if (existing?.smsOptIn !== true) {
+      await database
+        .update(clients)
+        .set({ smsOptIn: true, smsConsentDate: new Date(), smsOptOutDate: null })
+        .where(eq(clients.id, clientId));
+    }
+
+    return {
+      replyText:
+        "You have been resubscribed to messages. Reply STOP to unsubscribe. Msg & data rates may apply.",
+    };
+  }
+
+  // kind === "help"
+  const [settings] = await database
+    .select({ messagingHelpReply: businessSettings.messagingHelpReply })
+    .from(businessSettings)
+    .where(eq(businessSettings.id, businessId))
+    .limit(1);
+
+  const replyText =
+    settings?.messagingHelpReply ??
+    "Reply STOP to unsubscribe or START to resubscribe. For help, contact your groomer directly.";
+
+  return { replyText };
+}

apps/api/src/services/messaging/inbound.ts

@@ -1,5 +1,7 @@
 import { getDb, conversations, messages, businessSettings, clients, eq, and } from "@groombook/db";
 import { v4 as uuidv4 } from "uuid";
+import { detectKeyword, handleConsentKeyword } from "./consent.js";
+import { sendMessage } from "./outbound.js";
 
 export interface TelnyxMessageReceivedPayload {
   data: {
@@ -167,6 +169,22 @@ export async function handleMessageReceived(payload: TelnyxMessageReceivedPayloa
     "received"
   );
 
+  const keyword = detectKeyword(message.body ?? "");
+  if (keyword) {
+    const { replyText } = await handleConsentKeyword({
+      clientId,
+      businessId,
+      kind: keyword.kind,
+      db: getDb(),
+    });
+    await sendMessage({
+      businessId,
+      clientId,
+      body: replyText,
+      staffId: undefined,
+    });
+  }
+
   return { conversationId, messageId };
 }