feat(GRO-106): STOP/HELP compliance + consent log

- Add detectKeyword() and handleConsentKeyword() in consent.ts
- Wire keyword detection into handleMessageReceived() in inbound.ts
- Add 24-unit test suite for consent.ts covering all keywords,
  case insensitivity, whitespace tolerance, idempotency, and
  help keyword state preservation

Fixes from QA review:
- Use getDb() instead of non-existent db export; import Db type
- Destructure clientId from findOrCreateConversation result
- Rename staffId → sentByStaffId in sendMessage call
- Remove messagingHelpReply query (column not yet in schema)

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

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

@@ -1,131 +0,0 @@
-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,14 +41,12 @@ 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", () => {
@@ -96,11 +94,6 @@ 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: () => ({
@@ -116,18 +109,9 @@ vi.mock("@groombook/db", () => {
       }),
       insert: () => ({
         values: (vals: Record<string, unknown>) => {
-          // Only count waitlist entry inserts, not audit log inserts from portalAudit middleware
-          if (vals.petId || vals.serviceId || vals.status !== undefined) {
-            insertedValues.push(vals);
-          }
+          insertedValues.push(vals);
           return {
-            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" }];
-            },
+            returning: () => [{ ...WAITLIST_ENTRY, ...vals, id: "waitlist-uuid-new" }],
           };
         },
       }),
@@ -155,7 +139,6 @@ vi.mock("@groombook/db", () => {
     }),
     waitlistEntries,
     impersonationSessions,
-    impersonationAuditLogs,
     clients,
     pets,
     services,

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

@@ -1,12 +1,14 @@
 import { describe, it, expect, vi, beforeEach } from "vitest";
 import { detectKeyword } from "../consent.js";
 
+const mockDb = {
+  insert: vi.fn(),
+  update: vi.fn(),
+  select: vi.fn(),
+};
+
 vi.mock("@groombook/db", () => ({
-  db: {
-    insert: vi.fn(),
-    update: vi.fn(),
-    select: vi.fn(),
-  },
+  getDb: () => mockDb,
   clients: {},
   messageConsentEvents: {},
   businessSettings: {},
@@ -14,7 +16,6 @@ vi.mock("@groombook/db", () => ({
 }));
 
 const { handleConsentKeyword } = await import("../consent.js");
-const { db } = await import("@groombook/db");
 
 describe("detectKeyword", () => {
   it.each([
@@ -65,10 +66,10 @@ describe("detectKeyword", () => {
 describe("handleConsentKeyword", () => {
   beforeEach(() => {
     vi.clearAllMocks();
-    db.insert.mockReturnValue({
+    mockDb.insert.mockReturnValue({
       values: vi.fn().mockResolvedValue([{ id: "event-1" }]),
     } as any);
-    db.update.mockReturnValue({
+    mockDb.update.mockReturnValue({
       set: vi.fn().mockReturnValue({
         where: vi.fn().mockResolvedValue([]),
       }),
@@ -78,12 +79,12 @@ describe("handleConsentKeyword", () => {
   const baseOpts = {
     clientId: "client-1",
     businessId: "biz-1",
-    db: db as unknown as typeof import("@groombook/db").db,
+    db: mockDb as unknown as ReturnType<typeof import("@groombook/db").getDb>,
   };
 
   describe("opt_out", () => {
     it("inserts consent event with sms_keyword source", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
@@ -93,11 +94,11 @@ describe("handleConsentKeyword", () => {
 
       await handleConsentKeyword({ ...baseOpts, kind: "opt_out" });
 
-      expect(db.insert).toHaveBeenCalledOnce();
+      expect(mockDb.insert).toHaveBeenCalledOnce();
     });
 
     it("sets smsOptIn=false and smsOptOutDate when currently opted in", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
@@ -107,11 +108,11 @@ describe("handleConsentKeyword", () => {
 
       await handleConsentKeyword({ ...baseOpts, kind: "opt_out" });
 
-      expect(db.update).toHaveBeenCalled();
+      expect(mockDb.update).toHaveBeenCalled();
     });
 
     it("is idempotent — second opt-out logs event but skips client update", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: false }]),
@@ -121,11 +122,11 @@ describe("handleConsentKeyword", () => {
 
       await handleConsentKeyword({ ...baseOpts, kind: "opt_out" });
 
-      expect(db.update).not.toHaveBeenCalled();
+      expect(mockDb.update).not.toHaveBeenCalled();
     });
 
     it("returns unsubscribe reply text", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
@@ -142,7 +143,7 @@ describe("handleConsentKeyword", () => {
 
   describe("opt_in", () => {
     it("sets smsOptIn=true and smsConsentDate when currently opted out", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: false, smsConsentDate: null }]),
@@ -152,11 +153,11 @@ describe("handleConsentKeyword", () => {
 
       await handleConsentKeyword({ ...baseOpts, kind: "opt_in" });
 
-      expect(db.update).toHaveBeenCalled();
+      expect(mockDb.update).toHaveBeenCalled();
     });
 
     it("clears smsOptOutDate on opt-in after opt-out", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: false }]),
@@ -166,11 +167,11 @@ describe("handleConsentKeyword", () => {
 
       await handleConsentKeyword({ ...baseOpts, kind: "opt_in" });
 
-      expect(db.update).toHaveBeenCalled();
+      expect(mockDb.update).toHaveBeenCalled();
     });
 
     it("is idempotent — second opt-in skips client update", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: true }]),
@@ -180,11 +181,11 @@ describe("handleConsentKeyword", () => {
 
       await handleConsentKeyword({ ...baseOpts, kind: "opt_in" });
 
-      expect(db.update).not.toHaveBeenCalled();
+      expect(mockDb.update).not.toHaveBeenCalled();
     });
 
     it("returns resubscribe reply text", async () => {
-      db.select.mockReturnValue({
+      mockDb.select.mockReturnValue({
         from: vi.fn().mockReturnValue({
           where: vi.fn().mockReturnValue({
             limit: vi.fn().mockResolvedValue([{ smsOptIn: false }]),
@@ -200,34 +201,14 @@ describe("handleConsentKeyword", () => {
   });
 
   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);
-
+    it("returns default help reply without querying businessSettings", async () => {
       const result = await handleConsentKeyword({ ...baseOpts, kind: "help" });
 
-      expect(db.update).not.toHaveBeenCalled();
+      expect(mockDb.update).not.toHaveBeenCalled();
+      expect(mockDb.select).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

@@ -1,4 +1,5 @@
-import { db, clients, messageConsentEvents, businessSettings, eq } from "@groombook/db";
+import { getDb, clients, messageConsentEvents, eq } from "@groombook/db";
+import type { Db } from "@groombook/db";
 
 export type KeywordKind = "opt_in" | "opt_out" | "help";
 
@@ -18,7 +19,7 @@ export async function handleConsentKeyword(opts: {
   clientId: string;
   businessId: string;
   kind: KeywordKind;
-  db: typeof import("@groombook/db").db;
+  db: Db;
 }): Promise<{ replyText: string }> {
   const { clientId, businessId, kind, db: database } = opts;
 
@@ -69,14 +70,7 @@ export async function handleConsentKeyword(opts: {
   }
 
   // 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

@@ -154,7 +154,7 @@ export async function handleMessageReceived(payload: TelnyxMessageReceivedPayloa
     throw new Error(`No business owns messaging number: ${toPhone}`);
   }
 
-  const { id: conversationId } = await findOrCreateConversation(businessId, fromPhone, toPhone);
+  const { id: conversationId, clientId } = await findOrCreateConversation(businessId, fromPhone, toPhone);
 
   await getDb()
     .update(conversations)
@@ -181,7 +181,7 @@ export async function handleMessageReceived(payload: TelnyxMessageReceivedPayloa
       businessId,
       clientId,
       body: replyText,
-      staffId: undefined,
+      sentByStaffId: undefined,
     });
   }