WhatsApp‑Driven Appointment Scheduler & First‑Response Tracker in VibeCoding’s VS Monorepo

TL;DR: Added a full‑stack flow that assigns a prospect, records the first‑response timestamp, and creates a Google Calendar event (with Meet) from a WhatsApp message. The change touches controllers, services, DB migrations, and adds end‑to‑end tests, enabling real‑time scheduling without manual hand‑off.

---

The Problem

Our sales funnel relied on a manual hand‑off: a prospect messages us on WhatsApp, a support agent looks up the lead in the CRM, assigns it, and then manually creates a calendar invite. Two concrete pain points emerged:

1. No visibility of first‑response time – We could not tell how long it took from the inbound WhatsApp message to the first internal contact.
2. Scheduling friction – Agents had to copy‑paste dates into Google Calendar, leading to missed invites and duplicated meetings.

The symptom was a growing backlog of “prospect‑not‑assigned” records in prospects and a spike in the whatsapp_logs table showing null for first_response_at.

What I Tried First

My initial attempt was to add a simple flag in the whatsapp_logs table and let the front‑end UI toggle assignment. I added a column assigned_user_id via a raw SQL migration and updated the call-center.controller.ts to set it when an agent clicked “Assign”.

// call-center.controller.ts (initial attempt)
await db.query(
  `UPDATE prospects SET assigned_user_id = $1 WHERE id = $2`,
  [agentId, prospectId],
);

What went wrong?

• The endpoint was only reachable from the internal UI, so WhatsApp‑initiated contacts never triggered it.
• No timestamp was recorded, so we still couldn’t compute first‑response latency.
• The UI change introduced a race condition where two agents could assign the same prospect simultaneously, causing duplicate rows in the audit log.

Clearly we needed a tighter integration that starts at the WhatsApp inbound webhook and flows through to Google Calendar automatically.

The Implementation

Below is the concrete, file‑by‑file breakdown of the final solution. All code lives in the apps/api/src folder of the monorepo.

1. Database – New Index & Columns

apps/api/src/db/db.ts – added an index to speed up look‑ups for first‑response calculations and a nullable first_response_at column.

@@ -3208,5 +3208,17 @@
   alter table cfdi_documentos add column if not exists condo_fee_id uuid null references cond
+-- Index to quickly fetch the earliest WhatsApp log per prospect
+create index if not exists idx_whatsapp_logs_org on whatsapp_logs(organization_id, prospect_id, created_at);
+
+-- Add first response timestamp to prospects
+alter table prospects
+  add column if not exists first_response_at timestamptz null;

2. Prospect Assignment Logic

apps/api/src/prospects/assignment.ts – encapsulates the assignment and first‑response timestamp logic. This file is now the single source of truth for prospect ownership.

/**
 * assignment.ts — Responsable del prospecto y tiempo de primera respuesta
 */
import { query as db } from "../db/db.js";

export async function assignProspect(
  prospectId: string,
  agentId: string,
): Promise<void> {
  // 1️⃣ Update the owner
  await db(
    `UPDATE prospects SET assigned_user_id = $1 WHERE id = $2`,
    [agentId, prospectId],
  );

  // 2️⃣ Record first response only if it hasn't been set
  const { rows } = await db(
    `SELECT first_response_at FROM prospects WHERE id = $1`,
    [prospectId],
  );
  if (!rows[0].first_response_at) {
    await db(
      `UPDATE prospects SET first_response_at = now() WHERE id = $1`,
      [prospectId],
    );
  }
}

3. WhatsApp AI Service – Hooking Into the Flow

apps/api/src/whatsapp-ai/whatsapp-ai.service.ts – the inbound webhook now calls assignProspect and triggers calendar creation.

import { assignProspect } from "../prospects/assignment.js";
import { scheduleMeeting } from "./agenda-tools.js";

export async function handleIncomingMessage(payload) {
  const { prospectId, message, suggestedDate } = payload;

  // If the message contains a date suggestion, we treat it as a scheduling request
  if (suggestedDate) {
    const agentId = await pickBestAgent(); // internal helper
    await assignProspect(prospectId, agentId);
    await scheduleMeeting(prospectId, agentId, suggestedDate);
  }

  // ...other AI handling...
}

4. Agenda Tools – Google Calendar Integration

apps/api/src/whatsapp-ai/agenda-tools.ts – thin wrapper around the existing google-calendar.ts module.

import { createCalendarEvent } from "../booking/google-calendar.js";
import { getAgentEmail } from "../agents/agents.service.js";

export async function scheduleMeeting(
  prospectId: string,
  agentId: string,
  isoDate: string,
) {
  const agentEmail = await getAgentEmail(agentId);
  const startAt = new Date(isoDate);
  const endAt = new Date(startAt.getTime() + 30 * 60 * 1000); // 30‑min slot

  await createCalendarEvent({
    summary: `Prospect Call – ${prospectId}`,
    description: "`WhatsApp‑initiated meeting`,"
    startAt: startAt.toISOString(),
    endAt: endAt.toISOString(),
    attendeeEmail: agentEmail,
  });
}

5. Google Calendar Module – Minor Fix

apps/api/src/booking/google-calendar.ts – corrected a typo that broke the attendeeEmail field.

@@ -85,7 +85,7 @@ export async function createCalendarEvent(opts: {
   description: "string;"
   startAt:     string;   // ISO UTC
   endAt:       string;   // ISO UTC
-  attendeeEmail: string;
+  attendeeEmail: string;
 }) {
   // Google API call …
 }

6. Booking Controller – Expose a Public Endpoint

apps/api/src/booking/booking.controller.ts – added /booking/schedule that the WhatsApp service uses internally. The endpoint validates the payload and forwards to scheduleMeeting.

import { Body, Controller, Post } from "@nestjs/common";
import { scheduleMeeting } from "../whatsapp-ai/agenda-tools.js";

@Controller("booking")
export class BookingController {
  @Post("schedule")
  async schedule(@Body() dto: { prospectId: string; agentId: string; isoDate: string }) {
    const { prospectId, agentId, isoDate } = dto;
    await scheduleMeeting(prospectId, agentId, isoDate);
    return { status: "ok" };
  }
}

7. Call‑Center Controller – Mark First Contact

apps/api/src/call-center/call-center.controller.ts – now imports markFirstContact (renamed from the old inline logic) to keep the controller thin.

ts
import { markFirstContact } from "../

---

*Part of my [Build in Public](https://dev.to/zaerohell) series — sharing the real process of building Building PlayaMXCRM from Playa del Carmen, México.*

*Repo: `zaerohell/VS` · 2026-10-11*

\#playadev #buildinpublic