Planning: Lead drought alert (no leads in 30 minutes)

Status: Plan — ready for implementation.

Goal: Alert the team when no lead has been generated for 30 minutes so they can investigate (form down, tracking broken, Make.com issue, etc.).

---

Requirements (summary)

Item	Choice
Trigger	No row in Leads table with CreatedAt within the last 30 minutes
Check frequency	Every 10 minutes (cron)
Alert delivery	Optional configurable webhook URL (Slack, Make.com, PagerDuty, etc.); if unset, log only
Duplicate suppression	After sending an alert, do not send again for a configurable cooldown (e.g. 2 hours) — store last-alert time in KV
Scope	Production-relevant only; can be disabled per env (e.g. skip in development)

---

Current state

• Leads: Stored in D1 Leads table with CreatedAt (TEXT, datetime('now')).
• Scheduled jobs: One cron today: 0 3 * * * (daily 3 AM UTC) for R2 retention. Handler in src/index.ts → runRetentionCleanup(env).
• Webhooks: src/lib/webhook.ts provides sendWebhookWithRetry(url, payload, env, options) — reusable for alert payload.
• KV: TRACKING_KV exists; we can store lead_drought:last_alert_at (or a dedicated key) for cooldown.

---

Design

1. Definition of “lead” for the check

• Count: “Last lead” = most recent Leads.CreatedAt (main lead capture table only).
• Exclude: TestingLeads, BlockedIPLeads — we care about real lead flow.
• Query: SELECT MAX(CreatedAt) AS last_lead_at FROM Leads (or ORDER BY CreatedAt DESC LIMIT 1). If no rows, treat as “no leads ever” and optionally skip alert or alert once (see edge cases).

2. Cron and scheduled handler

• New cron: */10 * * * * (every 10 minutes, UTC).
• wrangler.toml: Add to [triggers] crons (and env-specific if we want different crons per env):
  crons = ["0 3 * * *", "*/10 * * * *"]
• Handler: In scheduled(event, env, ctx), branch on event.cron (or (event as { cron?: string }).cron):
  • "0 3 * * *" → existing retention job.
  • "*/10 * * * *" → new lead-drought check (see below).

3. Lead-drought check logic (pseudo-code)

1. If env.LEAD_DROUGHT_ALERT_ENABLED === 'false' (or unset in dev), return.
2. Query: last_lead_at = MAX(CreatedAt) FROM Leads.
3. If no rows or last_lead_at is null → decide: skip or treat as “very old” (e.g. alert once).
   Recommendation: If no rows, skip (first deploy / empty DB). If last_lead_at present but > 30 min ago, proceed.
4. If (now - last_lead_at) < 30 minutes → exit (no alert).
5. Cooldown: read KV key e.g. "lead_drought:last_alert_at".
   If last_alert_at exists and (now - last_alert_at) < COOLDOWN_MINUTES (e.g. 120), exit.
6. Send alert (webhook and/or log).
7. If webhook (or log) sent: write KV "lead_drought:last_alert_at" = now (ISO string or epoch).

4. Alert payload (webhook)

• URL: From env/secret, e.g. LEAD_DROUGHT_ALERT_WEBHOOK_URL (optional). If missing, only log.
• Body (JSON):
  { "event": "lead_drought", "last_lead_at": "<ISO>", "minutes_without_leads": <number>, "message": "No leads in the last 30 minutes. Last lead: <ISO>." }
• Slack: If URL is a Slack incoming webhook, they expect a different shape; we can either document “use Make.com to transform to Slack” or add an optional LEAD_DROUGHT_ALERT_SLACK_WEBHOOK_URL and send { "text": "..." } for that URL. Simpler: one webhook URL, document that Slack users can use Make.com or a small middleware to adapt. So: single webhook URL, one JSON payload.

5. Config (Settings page / AppSettings)

Configuration is stored in AppSettings (D1) and edited from the dashboard Settings page. No wrangler vars or secrets required.

Key	Purpose
lead_drought_alert_enabled	When true, the cron runs the check. When false, skip (default false).
lead_drought_alert_webhook_url	Make.com (or other) webhook URL to POST alert JSON. If empty, only log.
lead_drought_threshold_minutes	“No leads in X minutes” triggers the alert (default 30).
lead_drought_cooldown_minutes	Minutes between alerts (default 120).

6. KV key

• Key: lead_drought:last_alert_at
• Value: ISO timestamp string (e.g. new Date().toISOString()).
• TTL: Optional; if we don’t set TTL, key lives forever (we only overwrite on next alert). Prefer no TTL so cooldown is purely time-based.

7. Edge cases

Case	Behaviour
Empty Leads table	Skip alert (no “last lead” to report).
Last lead exactly 30 min ago	Next run (e.g. 10 min later) will see 40 min → alert.
First run after long outage	Alert once; cooldown prevents spam.
Webhook fails	Use existing sendWebhookWithRetry; on final failure, log and do not write KV (so next run will retry alert). Optionally write KV anyway to avoid repeated failures every 10 min — recommend: do not update KV on webhook failure, so we retry next run.
Cron drift / double execution	Idempotent: we only write KV after a successful send; cooldown prevents duplicate alerts.

8. Observability

• Log: On each run, log at debug: lead_drought_check with last_lead_at, minutes_without_leads, alert_sent: boolean. On alert: log at warn/error with same + webhook_sent / webhook_failed.
• No new metrics in this phase (can add later if needed).

---

Implementation outline

1. Config (AppSettings + Settings page)

   • Add to AnalyticsSettings and AppSettings table (key-value): lead_drought_alert_enabled, lead_drought_alert_webhook_url, lead_drought_threshold_minutes, lead_drought_cooldown_minutes.
   • Defaults: enabled false, webhook URL empty, threshold 30, cooldown 120.
   • Dashboard Settings page: new card “Lead drought alert” with toggle, webhook URL input (e.g. Make.com), threshold and cooldown number inputs. Save via existing PATCH /analytics/settings.

2. Service module

   • New file: src/lib/leadDroughtAlert.ts:
     • getLastLeadAt(env: Env): Promise<string | null> — D1 query SELECT MAX(CreatedAt) AS last_lead_at FROM Leads.
     • runLeadDroughtCheck(env: Env, log: Logger): Promise<void> — full flow: load settings via getAppSettings(env); if !lead_drought_alert_enabled return; query last lead; threshold check; KV cooldown; if webhook URL set, POST via sendWebhookWithRetry; on success write KV.

3. Scheduled handler

   • In src/index.ts, update scheduled(event, env, ctx):
     • Read cron from event (e.g. (event as { cron?: string }).cron).
     • If cron === "*/10 * * * *" → ctx.waitUntil(runLeadDroughtCheck(env, log).catch(...)).
     • If cron === "0 3 * * *" → existing retention.

4. wrangler.toml

   • Add second cron: crons = ["0 3 * * *", "*/10 * * * *"]. No new env vars; config is from Settings/AppSettings.

5. Tests

   • Unit tests: getLastLeadAt with empty DB and with one row; runLeadDroughtCheck with mock env (no alert when recent lead, alert when old lead, cooldown prevents second alert, no webhook when URL unset).
   • Optional: scheduled handler test with Miniflare (trigger scheduled with cron */10 * * * *).

6. Documentation

   • docs/features/lead-drought-alert.md — behaviour, config, webhook payload, Slack/Make.com setup.
   • docs/changelog.md — added.
   • PROJECT_CONTEXT.md / ARCHITECTURE.md — mention second cron and alert module.

---

Optional later enhancements

• Business hours only: Done. Settings: lead_drought_business_hours_enabled, lead_drought_business_hours_start, lead_drought_business_hours_end, lead_drought_business_hours_timezone. Alerts only sent when current time (in that timezone) is within start–end.
• Slack-specific payload: If LEAD_DROUGHT_ALERT_SLACK_WEBHOOK_URL is set, POST { "text": "..." } for easier Slack integration without Make.com.
• Dashboard: Small “Last lead” and “Last drought alert” in admin dashboard (read from D1 + KV or from logs).

---

Checklist (done)

• [x] Config from Settings page (AppSettings); Make.com webhook URL provided by user.
• [x] Threshold 30 min, check every 10 min, cooldown 2 hours default.
• [x] Single webhook URL (Make.com); user configures from Settings.