# Demo no-show recovery — n8n bundle

## What this flow does

Recovers demo no-shows automatically. A HubSpot Workflow POSTs to a webhook the moment a meeting is marked `no_show`; n8n pulls the meeting and contact, decides whether to use a "we missed you" or "let's reschedule" tone based on whether the contact has a completed meeting in the last 90 days, asks Claude to write one personalized opener line (with a hard fallback if context is thin), then sends a same-day reschedule email from the AE's delegated Gmail with two pre-picked time slots and the AE's scheduling link. A separate cron sweep advances active recoveries to a step-2 value-forward email after two days and a step-3 soft close after a week. A third independent trigger watches the AE inbox for replies, classifies them (opt-out, reschedule, human reply), exits the sequence, and writes the exit reason back to the HubSpot contact for reporting.

## Import

1. In n8n, open **Settings → Import from File** (top-right menu in the workflow list).
2. Select `demo-no-show-recovery-n8n.json` from this bundle.
3. Open workflow **Settings** (gear icon in the editor) and confirm `Timezone` is set to your AE timezone — the imported default is `America/New_York`. Both the cron node and the date math in `Compose Step 1 Email` rely on this being correct.
4. Save the workflow but leave it **inactive** until credentials are wired and the first-run verification below passes.

## Credentials

You need four credentials. Each placeholder ID in the JSON has a `name` that helps n8n match them on import — re-create them with those names and the bindings reattach.

### `PLACEHOLDER_HUBSPOT_CRED_ID` — HubSpot Private App token

Used by the three HubSpot HTTP nodes (`HubSpot — Pull Meeting + Owner`, `HubSpot — Pull Contact`, `HubSpot — Pull AE Owner`, `HubSpot — Tag Exit on Contact`).

- HubSpot → **Settings → Integrations → Private Apps → Create private app**.
- Scopes required: `crm.objects.contacts.read`, `crm.objects.contacts.write`, `crm.objects.meetings.read`, `crm.objects.owners.read`.
- Copy the access token. In n8n create a **Header Auth** credential named `HubSpot — Private App token` with header `Authorization` and value `Bearer <token>`.

### `PLACEHOLDER_ANTHROPIC_CRED_ID` — Anthropic x-api-key

Used by `Claude — Opener Line`.

- Get a key at **console.anthropic.com → API Keys**.
- In n8n create a **Header Auth** credential named `Anthropic — x-api-key` with header `x-api-key` and the key value. The model is pinned to `claude-sonnet-4-6` in the JSON body — change the body if you want a different model.

### `PLACEHOLDER_GMAIL_CRED_ID` — Gmail OAuth2 (AE delegated mailbox)

Used by `Gmail — Send Step 1 (delegated)`, `Gmail — Send Step 2/3`, and `Reply Trigger — AE Inbox`.

- In Google Cloud Console create OAuth credentials for n8n (**OAuth client ID → Web application**) and add `https://<your-n8n-host>/rest/oauth2-credential/callback` as the redirect URI.
- In n8n create a **Gmail OAuth2** credential named `Gmail — AE delegated mailbox` and authenticate as the AE whose mailbox should send.
- For multi-AE setups, duplicate the workflow per AE or replace the single credential with a credential-selector expression keyed off `ae_email`.

### `PLACEHOLDER_POSTGRES_CRED_ID` — Postgres (recovery-state)

Used by all five Postgres nodes.

- Any reachable Postgres 13+. The bundle assumes two tables; create them once before the first run:

```sql
CREATE TABLE recovery_state (
  meeting_id        text PRIMARY KEY,
  contact_id        text NOT NULL,
  owner_id          text,
  email             text NOT NULL,
  ae_email          text,
  tone              text,
  current_step      int NOT NULL DEFAULT 0,
  status            text NOT NULL DEFAULT 'active',
  exit_reason       text,
  step1_sent_at     timestamptz,
  step1_opener      text,
  started_at        timestamptz NOT NULL DEFAULT now(),
  last_touched_at   timestamptz,
  next_due_at       timestamptz,
  exited_at         timestamptz
);

CREATE INDEX recovery_state_due_idx ON recovery_state (status, next_due_at);
CREATE INDEX recovery_state_email_idx ON recovery_state (email);

-- Read-only mirror used by the 90-day prior-meeting lookup.
-- Replace with your own meetings sync (HubSpot → warehouse) if you have one.
CREATE TABLE hubspot_meetings_raw (
  meeting_id   text PRIMARY KEY,
  contact_id   text NOT NULL,
  outcome      text NOT NULL,
  completed_at timestamptz
);
CREATE INDEX hubspot_meetings_contact_idx ON hubspot_meetings_raw (contact_id, completed_at DESC);
```

- In n8n create a **Postgres** credential named `Postgres — recovery-state` with host, db, user, password.

## First-run verification

Run these in order. Each step exercises a specific branch — if one fails, fix before moving on.

1. **Webhook reachability.** With the workflow inactive, click **Execute Workflow** on `Webhook — HubSpot no-show event` and grab the test URL. From a terminal: `curl -X POST <test-url> -H 'content-type: application/json' -d '{"meetingId":"<real-meeting>","contactId":"<real-contact>","ownerId":"<real-owner>"}'`. You should see a 202 in the response and the meeting + contact pulls light up in the editor.
2. **Eligibility guard — happy path.** Use a contact with `hs_email_optout = false`, a real email, and a meeting whose `hs_meeting_start_time` is more than 5 minutes in the past. The IF node should take the true branch.
3. **Eligibility guard — opt-out exit.** Set `hs_email_optout = true` on the contact and re-fire the webhook. The IF node should take the false branch and the flow should stop with no email sent.
4. **Tone branch — `we_missed_you`.** Insert a row into `hubspot_meetings_raw` for the contact with `outcome = 'completed'` and `completed_at = now()`. Re-fire. `Build Personalization Context` should emit `tone: "we_missed_you"`.
5. **Tone branch — `lets_reschedule`.** Delete that row. Re-fire. `Build Personalization Context` should emit `tone: "lets_reschedule"`.
6. **Claude fallback.** Empty the contact's `form_submission_summary` and `recent_form_fill`. The Claude call should return the literal `FALLBACK` and `Compose Step 1 Email` should swap in the safe opener.
7. **Activate the workflow.** Once steps 1-6 pass on test data, point the HubSpot Workflow at the production webhook URL and switch the n8n workflow to **Active**.
8. **Cron sweep.** Wait two days (or hand-edit `next_due_at` to `now() - interval '1 minute'` on a test row) and confirm `Cron — Step 2/3 Sweep` picks it up and advances `current_step` from 1 to 2.
9. **Reply exit.** Send a reply from a different inbox to the AE mailbox containing the word `STOP`. `Reply Trigger — AE Inbox` should classify it as `opt_out`, `Postgres — Exit Sequence` should set status to `opted_out`, and `HubSpot — Tag Exit on Contact` should write `no_show_recovery_status = 'opt_out'` back to the contact.