Customer Support X-ray v2 - From Broadcast to Sniper

v1's insight was that a teardown is only worth building if it's worth sharing - so every result got a URL and a social card. That's broadcast: cast wide, hope it spreads.

v2 asks the sharper question: which 200 stores should we actually talk to this week, and what do we say? Two engines answer it:

1. The distribution sniper - source candidate stores, cheaply qualify them for fit (no LLM spend), run the full Claude teardown only on the keepers, and work the ranked result as a sales queue.
2. Conversation listening - Python collectors watch Hacker News, Reddit, and competitor reviews for buying-intent signals, and surface named, in-market leads - sometimes a single click from a personalized teardown.

Everything writes into one Postgres database that a magic-link-gated team dashboard reads. That shared store is the whole reason v2 exists.

Stack: Next.js 16 (App Router, "Proxy") · TypeScript · Tailwind v4 / shadcn · Anthropic Claude (claude-sonnet-4-6) · Supabase (Postgres + Auth + RLS) · Python collectors · GitHub Actions · Vercel.

---

Why KV Had to Become Postgres

v1 stored everything in Vercel KV (Upstash Redis): a 24h analysis cache, a leads list, counters, a recency gallery. That was right for a single writer (the web app).

v2 has two writers - the web app and a fleet of Python collectors/batch jobs - plus a third requirement KV is bad at: teammates need to query, filter, and sort the data online. The moment you want where fit_verdict='good_fit' and 'Gorgias' = any(detected_stack) order by automatable_pct desc, you want SQL. So:

• Vercel KV → Supabase (Postgres) as the single source of truth.
• The 24h crawl cache became Next's unstable_cache (a request-dedupe layer) with Postgres as the store of record.
• Row-Level Security so the browser (holding only the publishable key) can read a table only if the logged-in email is on an allow-list; every write goes through the secret key, server-side.

The Migration Was the Careful Part

v1 was live on KV with real data, so this was a migration, not a fresh build. The build brief shipped a template migration script - and it was wrong for our codebase in two ways that fail silently:

• it assumed keys like cache:<domain>; v1 actually used xray:result:<domain>;
• it assumed flat fields (result.brand_name); v1 nested everything under result.analysis.*.

A wrong key pattern doesn't error - it migrates zero rows, or rows full of nulls. So the first step was an inventory: read every KV usage in the v1 code, list the real key patterns and value shapes, and only then write the backfill. The corrected, read-only script (SCAN/GET/LRANGE/SMEMBERS - never DEL) moved the real data cleanly: 5 teardowns (carrying their per-domain run counters), 1 email lead, 1 moderation blocklist entry - verified against the live KV before the app ever switched over. KV decommission was the last, manual step.

Lesson: for any "migrate the template assumes X" task, inventory the source first. The failure mode of scraping/migration is silence, not errors.

---

The Team Dashboard

/dashboard is team-only via Supabase magic-link auth:

• app/login requests a link; app/auth/callback verifies the token-hash OTP (the SSR flow - the default ConfirmationURL does not work server-side, the classic gotcha).
• Next 16 renamed Middleware → "Proxy" - a real breaking change. proxy.ts refreshes the session cookie and gates /dashboard.
• Access is an allow-list: team_members. A logged-in non-member sees a friendly "ask an admin" page; RLS independently blocks any direct read. Defense in depth.

The dashboard is the cockpit: a teardowns table (filter by verdict / source / stack / min automatable %, sort, expand a row into the full v1 card, export CSV) and an intent feed (separate tabs per source).

---

The Distribution Sniper

The expensive step is the Claude teardown. The trick is to never spend it on a bad-fit store.

seed / store-DB export ─► find_prospects.py ─► ranked shortlist (+ targets.csv)
   (any CSV with a            (cheap: 1 GET/row,        │
    `domain` column)           or 0 with a tech column) ▼
                                              batch_xray.py ─► Supabase teardowns
                                              (Claude, source='batch')   │
                                                                         ▼
                                              Dashboard: filter good_fit,
                                              sort automatable % desc = the queue

Cheap Qualification Before Any LLM Call

find_prospects.py fetches each candidate's homepage (or reads a technologies column if the source provides one) and scores ICP fit from tech fingerprints alone - no Claude:

A store running subscriptions and a helpdesk is the bullseye: recurring billing plus an existing support tool means real, automatable ticket volume and someone with budget. Those score 9 and float to the top.

The output is outreach-ready, not just domains: when fed a store-database export (e.g. Storeleads), it carries through rank, country, category, phones, and picks a best contact email (support@/help@ over generic). And if the export includes a technologies column, it qualifies with zero fetches - so you can rank thousands instantly and spend Claude only on the top N.

It Works on Real Verticals

Two runs, end to end, seed → qualify → teardown:

• Food & beverage (subscription DTC): top scorers mudwtr, drinkhydrant, bokksu (9 - Recharge + Gorgias). 12 teardowns, all good_fit, 60 - 72% automatable.
• Pet (the highest-volume support vertical): 7 of 24 scored 9 - wildone, fablepets, nativepet, thinkjinx, wildearth, earthrated, boccesbakery. 12 teardowns, all good_fit, 65 - 72%. (The qualifier correctly dropped The Farmer's Dog - it isn't on Shopify - before wasting a Claude call on it.)

The dashboard's "filter source=batch, good_fit, sort by automatable %, stack contains Gorgias" view is the sales queue.

---

Listening to Conversations

The other half: watch where operators talk and complain, and turn signals into leads. All collectors share one phrase vocabulary (tools/intent.py) and write to one table; the dashboard shows a tab per channel.

Hacker News - the Source That Just Works

hn_monitor.py queries the open HN Algolia API for the intent phrases. No auth, no approval, and - critically - reachable from GitHub Actions runners. It runs on a daily cron and is the durable CI source. (Audience skews dev/startup, so it's lower-signal than ecommerce subreddits, but it's live.)

Reddit - the Wall, and the Honest Answer

Reddit is the best audience (r/shopify, r/ecommerce) - and the hardest to reach:

• Reddit closed self-service Data API access in Nov 2025 (Responsible Builder Policy). You now apply for access (~2 - 4 weeks); the developers.reddit.com/Devvit flow is a different product and does not issue the OAuth creds PRAW needs.
• The "no-API" fallbacks don't survive CI either: Reddit's .json and .rss endpoints 403 datacenter IPs (verified). So Reddit-via-GitHub-Actions is simply not viable today.

The pragmatic call: keep the PRAW collector written and compliant, ship a ready-to-submit access application, and let HN + reviews carry the feed until approval lands. When it does, it's a one-file drop-in - same phrase list, same table.

Competitor Reviews - the Highest-Signal Source, via the Lead Loop

This is the best idea in v2. A 1★ Shopify App Store review of Gorgias names the reviewing store - an unhappy, in-market business paying a competitor. That's a prime target.

reviews_shopify.py mines public App Store reviews for 7 competitor CS apps (Gorgias, Re:amaze, Tidio, Zendesk, Help Scout, Chatra, Chatway), keeps the painful ones (≤3★ or pain keywords like billing, switching, refund, overpriced), and records the reviewing store as a lead. One run produced 59 review leads.

Then the lead loop closes:

1. best-effort resolve each store name → domain (verify it's a live Shopify store): 33/59 resolved;
2. write them to a CSV ready for batch_xray;
3. surface a "Run X-ray → /x/{domain}" button on the dashboard review card.

One click turns "this store hates its helpdesk" into "here's a personalized teardown to send them." That is the whole funnel in a single gesture.

A debugging vignette worth keeping: the first review scrape returned 404 for every app and I nearly wrote off the App Store as IP-blocking too - until I checked a slug. Gorgias's listing is at /helpdesk, not /gorgias. The "block" was a wrong URL. Verify the trivial explanation before the dramatic one.

---

The Engineering That Made It Robust

The interesting work in v2 wasn't features - it was making them survive a messy real world.

• Datacenter-IP reality. Reddit 403s CI; Anthropic's edge drops connections from GitHub's shared runner IPs (APIConnectionError, while pip and store crawls on the same runner succeed). So the operating model is explicit and documented: HN/Reddit crons on Actions; batch_xray + review mining run locally / on-demand (or a self-hosted runner). We added a key-length + exception-type log specifically so a future failure says which wall it hit.

• Schema-optional dashboard. The "Run X-ray" feature needs a new lead_domain column - but a column-not-found select would break the live dashboard between deploy and migration. So the dashboard query tries lead_domain, and falls back to the base columns if it's missing; the collector upserts with lead_domain, and retries without it on a missing-column error. The feature lights up the moment the column exists, and nothing breaks before then.

• One table, many channels, no migration churn. Intent hits share reddit_hits, discriminated by the subreddit column doubling as a channel (HN, Reviews, real subreddits) with namespaced ids (hn_…, review:shopify:…). The dashboard partitions client-side into tabs. It kept the interim shippable without a schema rewrite, while leaving a clean path to a unified intent_hits table later.

• --dry-run everywhere. Every collector and both workflows can run without writing - so you validate secrets and the pipeline before producing data. (It also caught the Anthropic egress failure as a clean dry-run, not a half-written batch.)

• Typed Supabase access. The untyped client infers never and silently breaks selects/upserts; a hand-written Database type (in the generated shape) restores real Row/Insert types and future-proofs the eventual Reddit-API swap.

---

Guardrails & Ethics

• Read/analyze-only, always. No collector posts, comments, votes, or DMs. They read public pages and store metadata.
• Polite by design. Browser User-Agent, page caps, delays, back-off on 429. No proxy rotation or fingerprint spoofing to evade blocks - if a source blocks CI, we move the job, we don't cheat it.
• Respect terms. Shopify App Store reviews are public → scrape politely. G2/Capterra forbid scraping → CSV-import path only. Trustpilot → official API only.
• No training on Reddit data (their policy) - the monitor only substring-matches and stores thread metadata; no Reddit content is ever fed to a model.
• Minimal PII. A public review handle + store name is fine; we don't enrich with private contact data. Any future outreach is personalized B2B with opt-out (GDPR / Swiss FADP).
• Honesty rule preserved from v1: teardowns still separate facts (from the store's pages) from labelled estimates. v2 didn't soften it for nicer copy.

---

Architecture at a Glance

app/                      Next.js 16 (Vercel, Root Directory = app/)
  app/x/[domain]/         public tool — unstable_cache → crawl → Claude → upsert teardowns
  app/dashboard/          team-only: teardowns + intent tabs (Reddit/HN/Reviews)
  app/login, auth/callback, proxy.ts   magic-link + session gate
  lib/supabase/           browser / server / admin clients + hand-written Database type
supabase/                 schema.sql + migrations + RLS + config.toml (CLI workflow)
tools/                    intent.py · hn_monitor · reddit_monitor · reviews_shopify
                          find_prospects · batch_xray · db.py   (+ seeds/)
scripts/                  one-off read-only KV→Supabase backfill
.github/workflows/        hn-monitor (cron) · monitor (Reddit, pending) · batch (manual)

Security model in one line: the public tool and the scripts write with the secret key (server-only, bypasses RLS); the browser only ever holds the publishable key and can read nothing unless the logged-in email is in team_members.

---

Results

• Storage migrated KV → Supabase with a verified, read-only backfill; live tool persists every run.
• Team dashboard behind magic-link + RLS, with filterable teardowns and a multi-source intent feed.
• Distribution sniper proven on two verticals - 24 teardowns generated only after cheap fit-qualification; the score-9 (subscriptions + helpdesk) stores are the queue.
• Conversation listening live from Hacker News (CI cron) and competitor reviews (59 leads, 33 one-click-X-ray-ready), with Reddit queued behind its access application.
• A documented operating model that names every wall (Reddit 403, Anthropic egress, the App Store slug red herring) so the next engineer doesn't re-learn them.

---

What's Next

• Reddit official API the moment access is granted (drop-in collector, flip a flag).
• A unified intent_hits table with competitor/company/lead_domain first-class, and the review lead-loop wired straight into the batch queue.
• Domain resolution upgraded from best-effort name-matching to a search-backed resolver.
• Self-hosted/residential runner for the Anthropic-and-Reddit-dependent jobs, if/when the cadence warrants always-on automation.