--- title: Hunt Approval Bottleneck — Root Cause Analysis created: 2026-05-01 type: investigation status: read-only author: Claude Code --- # Hunt Approval Bottleneck — Root Cause Analysis ## TL;DR The "approval bottleneck" is a misdiagnosis. Two compounding issues: 1. **The "359 leads" figure was the wrong table.** That was the legacy `leads` table from the old Rivet sales engine (Jan 2026). The hunt's actual scraped-lead table is **`scraped_leads`**, which has **exactly 1 row** — a test scrape on 2026-04-28 from `admin@rateright.com.au` that successfully sent end-to-end to prove the pipeline works. 2. **Two separate gaps stop real leads ever flowing**: - **Scrapers extract 0 contact info** from real Gumtree pages (last logged run: Apr 28, every city returned `0 raw results with contact info`). The recent flurry of commits — playwright switch, ScrapingBee fallback, stealth_proxy, category URL fix — was fighting exactly this. Status: still broken at last run. - **No code anywhere transitions a lead from `'new'` → `'approved'`.** The CHECK constraint allows the state, the outreach-sender filters for it, but no script, route, UI, or trigger ever sets it. Even if scrapers found leads, they'd sit in `'new'` forever. So the headline framing "359 scraped, 1 sent, approval bottleneck" was wrong on every count. Real headline: **"the hunt has never produced a real lead, and even if it had, there's nothing to approve them."** Also worth correcting from earlier work today: the Supabase service-role key is **valid** — it's just the new `sb_sec_...` format (Supabase moved away from JWTs for service-role keys in late 2025). The 41-char length isn't a problem. --- ## Where the approval gate lives | Concern | File | Line(s) | Quote | |---|---|---|---| | Outreach gate | `/home/ccuser/rateright-growth/scripts/hunt/outreach-sender.js` | 49-51 | `getLeadQueue({ side: 'worker', status: 'approved', limit: BATCH_LIMIT })` | | Lead state machine | `supabase/migrations/20260428000000_marketplace_hunter.sql` | scraped_leads table | `status TEXT DEFAULT 'new' CHECK (status IN ('new', 'queued', 'approved', 'sent', 'replied', 'converted', 'rejected', 'skipped'))` | | Insert path | `scripts/hunt/db.js` | `insertLead()` line 232 | upserts to `scraped_leads` with default `status = 'new'` | | Status setter | `scripts/hunt/db.js` | `updateLeadStatus()` line 283 | called only by outreach-sender (`'sent'`) and reply-monitor (`'replied'`/`'bounced'`) | **Grep across the entire `scripts/hunt/` directory for `status.*=.*'approved'` or `'queued'`: zero hits.** The only hits for `approved` are the two filter calls in `outreach-sender.js`. ## Manual vs automatic — neither, that's the gap - **No manual UI**: searched `/home/ccuser/rateright-growth/` and `/home/ccuser/the-50-dollar-app/` for any route, page, or admin surface that approves scraped leads. The matches that came back are either the public sales playbook (unrelated, legacy `leads` table), the outreach-sender file itself, or pre-built JS bundles (`admin/dist/assets/SalesPlaybook-*.js`) — none of which target `scraped_leads`. - **No automatic transition**: no cron, no script, no DB trigger, no Supabase function migrates `'new'` → `'approved'`. - **The 1 'sent' row** got there because Rocky (or whoever was testing) inserted it directly with `status='approved'` already set, OR ran a one-off manual UPDATE. The lead's identity (Test Worker / admin@rateright.com.au) confirms it was a smoke test, not a real harvested lead. ## Last approval timestamp The 1 sent row's audit: ``` id: eb92937d-8627-4612-abb2-224dbd120e5b side: worker name: "Test Worker" email: admin@rateright.com.au trade: labourer city: sydney status: sent scraped_at: 2026-04-28T08:20:51.089175+00:00 outreach_sent_at: 2026-04-28T08:21:01.272+00:00 (10 seconds after scrape) outreach_message_id: 19dd32d655656b30 ``` The 10-second gap from scrape to send is unrealistic for a Gumtree-sourced lead — confirms it was a manual end-to-end test, not a production-discovered lead. ## Full lead lifecycle path (intended vs actual) ### Intended ``` [Gumtree page] │ ▼ scrapers (cron: 20:30 UTC workers, 21:00 UTC contractors) [scraper script runs] │ ├─ upsertContact(...) → contact_registry (status='new', contact_count=0) │ └─ insertLead({...}) → scraped_leads (status='new') │ ▼ ⚠ MISSING: someone/something sets status = 'approved' [scraped_leads.status='approved'] │ ▼ outreach-sender.runBatch() (every 30 min) │ ├─ canSendToday('email') → daily budget ok? │ ├─ getLeadQueue({status:'approved'}) → fetch up to BATCH_LIMIT │ ├─ for each: canContact(contact_registry_id) │ │ ├─ contact.status != 'unsubscribed' │ │ ├─ contact.status != 'bounced' │ │ ├─ contact.contact_count < MAX_CONTACTS (3) │ │ └─ contact.last_contacted_at < now - 7 days (cooldown) │ ├─ sendPlainText(...) via gmail-api │ ├─ updateLeadStatus(id, 'sent', {...}) → scraped_leads.status='sent' │ └─ recordContact(contact_registry_id) → contact_registry.contact_count++, last_contacted_at=now │ ▼ reply-monitor.js (continuous) [scraped_leads.status='replied' or 'bounced'] ``` ### Actual — where it stops ``` [Gumtree page] │ ▼ scrapers run on cron (last successful run window: 2026-04-28) [scraper script runs] │ ⚠ extracts 0 raw results with contact info │ log: "[contractor-hunter] brisbane: 0 raw results with contact info" │ log: "[contractor-hunter] Done. New leads: 0, Duplicates skipped: 0" │ ▼ no leads inserted [scraped_leads remains at 1 row — the Apr 28 test] │ ▼ outreach-sender (every 30 min, since 3d uptime) │ log: "Daily budget: 0/20 sent. No approved leads in queue." ``` **Two gaps stack**: scrapers produce nothing, and even if they did, no approval transition exists. ## Why the contact_registry has 327 rows (sanity check) Confirmed: `import-existing-leads.js` is a one-time seeder that copies the legacy `leads` table (359 rows, mostly contractor sales prospects from Jan-Feb 2026) into `contact_registry` to prevent re-contacting people the old Rivet system already reached. It does **not** create rows in `scraped_leads`. Different purpose. 327 rows in registry vs 359 in source: probably 32 leads in the legacy table had no usable identity key (no email, no phone, no name) and were skipped. ## Schedule reality `hunt-contractors`, `hunt-workers`, `hunt-aggregator`, `hunt-matchmaker` are configured with `cron_restart` and `autorestart: false`. They run **once a day, exit, and PM2 reports them as stopped between runs**. So "stopped" in `pm2 list` is the expected steady state — **not a bug**. | App | Cron (UTC) | Cron (AEST) | autorestart | |---|---|---|---| | `hunt-aggregator` | 20:00 | 06:00 | false (one-shot) | | `hunt-workers` | 20:30 | 06:30 | false | | `hunt-contractors` | 21:00 | 07:00 | false | | `hunt-matchmaker` | 21:30 | 07:30 | false | | `hunt-outreach` | continuous | — | true | | `hunt-monitor` | continuous | — | true | So my earlier "scrapers are stopped, queue not growing — turn them back on" was also imprecise. They're scheduled fine. The reason no leads accumulate is that the scrapers find zero contactable rows on Gumtree. ## Recommended fix paths (DO NOT IMPLEMENT — flagged for Rocky) Three options, increasing complexity: ### Option A — Auto-approve everything that passes basic gates (~10 lines) Add a hook at end of `db.js:insertLead()` (or as a separate `auto-approve.js` cron just before outreach) that promotes any new lead to `'approved'` if: - `email` is non-empty (so outreach-sender doesn't skip) - `contact_registry_id` is non-null - `contact.status !== 'bounced'` and `contact.status !== 'unsubscribed'` - `contact.contact_count < MAX_CONTACTS` **Pros**: tiny change, unblocks the pipeline immediately. Lets you iterate on quality once volume is flowing. **Cons**: removes Rocky's veto on individual sends. If the scrapers ever pull a sensitive contact (e.g. someone actively working for a competitor, or a personal email), it goes out without review. ### Option B — Manual approval UI in opsman-control-centre or admin (~1-2 days) Add an Admin → Leads page that lists `status='new'` rows, shows the source URL, name, email, phone, trade, city, and an Approve/Reject button per row. Bulk-approve by city/trade. **Pros**: keeps Rocky's veto. Better for compliance and quality. Surface for triage. **Cons**: build effort, requires opsman-control-centre or admin to be running and someone to actually click. Real-world: probably 80% of leads would be auto-approve-worthy, so spending the build effort is dubious. ### Option C — Auto-approve by trust tier, queue the rest for manual review (~half day) Auto-approve if **all** of: - Trade is in canonical TRADES list (not `'other'`) - Email looks like a personal Gmail/Hotmail/Yahoo (not corporate) - Contact has 0 prior contacts in registry - Source is Gumtree (not Facebook, lower trust) Otherwise mark `status='queued'` for human review (use the existing CHECK enum value that's currently unused). **Pros**: catches obvious sends without removing review. Uses an already-defined state. **Cons**: still requires building the review UI for the queued half. ### My recommendation **Option A first.** The leads will arrive (when scrapers actually work) at maybe 20-50/day on Gumtree alone. Reviewing each by hand is friction that won't scale. Add it as a one-line `await updateLeadStatus(lead.id, 'approved')` at end of `insertLead()` with the basic gates above. When you see real volume and a real false-positive, then build B or C. **But this is downstream of the real blocker**: the scrapers extract 0 leads from Gumtree right now. That's the actual hot fix. Approval logic is moot until scrapers produce. ## Recommended order of operations 1. **Find out why the scrapers return 0 raw results.** Read `scripts/hunt/scrapers/gumtree-jobs.js`, `gumtree-workers.js`, and the recent commits `c5a9b40b` / `06d8f687` / `1563ee3d` / `4c8066bb`. Likely Gumtree updated their HTML and the cheerio selectors broke. Run one scraper with `--dry-run` and see what it extracts vs what's on the live page. 2. **Add Option A auto-approval**, gated by the three basic checks above. Keep Rocky's veto via a `'rejected'` button later if needed. 3. **Watch the next cron-triggered scraper run** (06:30 AEST tomorrow). Confirm leads land in `scraped_leads.status='approved'` and outreach-sender picks them up. 4. **Manually trigger** `node scripts/hunt/contractor-hunter.js --dry-run` first to verify the scraper produces something, before relying on auto-cron. ## Out-of-scope but flagged - The `hostel-campaign` PM2 service still has 4 465 restarts. Independent issue. - `import-existing-leads.js` may be worth re-running occasionally as the legacy `leads` table grows (Susan still adds contractor prospects via Growth Engine). - `aggregated_jobs` has 3 rows. The aggregator runs at 20:00 UTC and probably has the same Gumtree extraction problem as the worker/contractor hunters. Worth checking together. --- *End of investigation. No behaviour was changed during analysis.*