Lead Website Enrichment

Purpose

Given a lead's website URL (typed by an admin, or imported from a CSV/XLSX column that a human filled in), fetch the page and turn it into structured JSONB (company name, tagline, services, headings, keywords, plus a description/industry/raw-text bundle) that composition_service.compose_email reads to write the opening lines of a cold outreach email (composition_service.py 68 to 79). The invariant that makes it trustworthy is that the URL is never handed to httpx before its host is proven to resolve only to public addresses, and the same check runs again on every redirect hop, not just the first request, so a lead's website cannot be used to make Labs' own server fetch 169.254.169.254 or an internal service (scraper_service._fetch_and_parse docstring, scraper_service.py 104 to 111).

When to use

Read it before touching ScraperService.scrape_lead, scrape_batch, _fetch_and_parse, or utils/net.py's assert_public_url/is_blocked_host; before changing how a redirect is followed on a lead-supplied URL; when someone asks "why did the scrape fail", "why is this lead stuck on scraping", "can a lead's website reach our internal network", or "what does the AI email actually see about this company". Trigger phrases: "scrape the lead", "website enrichment", "SSRF guard", "scrape_status", "scraped_data", "assert_public_url", "redirect hop".

Not this recipe: POST /campaigns/{campaign_id}/scrape-and-generate's second half, composition_service.compose_email/compose_batch and the Mistral call (composition_service.py, a neighbouring recipe, this one stops at the JSONB, it does not write the email); campaign/lead CRUD (create_campaign, add_leads_to_campaign, import_csv, outreach_service.py 35 to 185) which is where a lead's website string first enters the system, read here only for context on who controls the input; sending, tracking and reply detection (send_email, process_queue, detect_replies, outreach_service.py 297 to 438); get_client_ip in the same utils/net.py file, an unrelated trusted-proxy concern that happens to live beside assert_public_url because both decide whether a network identity can be trusted (module docstring, net.py 1 to 18).

Stack assumptions

At 62ba4fc: fastapi==0.118.2, synchronous handlers on sqlalchemy.orm.Session (SQLAlchemy==2.0.43), httpx==0.28.1 for the outbound fetch, beautifulsoup4==4.12.3 with the lxml==5.3.0 parser, psycopg2-binary==2.9.10 (backend/requirements.txt). Foundations assumed: S4 (fastapi-project-layout) for the app and router mount; S5 (auth-dependency-pattern) for AdminUser, the dependency the one HTTP route in this area sits behind; S6 (workspace-scoping) for WorkspaceID and the explicit workspace_id filters the campaign lookup applies before any lead in it is touched; S8 (alembic-migrations) for the migration that added the three columns this recipe writes to.

Environment: none. Neither scraper_service.py nor net.py reads a config key or env var (grep -n "settings\." backend/services/outreach/scraper_service.py backend/utils/net.py matches nothing in scraper_service.py and only the docstring/comment for get_client_ip's unrelated TRUSTED_PROXY_COUNT, net.py 11, 33, not read by the SSRF path). SCRAPE_TIMEOUT = 10 (seconds) and USER_AGENT = "Mozilla/5.0 (compatible; AlchemyBot/1.0; +https://alchemy.zuerich)" are hardcoded module constants (scraper_service.py 19 to 20), not configurable per deploy. Fail behaviour: scrape_lead catches every exception from _fetch_and_parse, sets lead.scrape_status = "failed", stores {"error": str(e)} into scraped_data, commits, and returns the same error dict rather than raising past itself (scraper_service.py 44 to 72); a blocked URL surfaces through this same path as a ValueError message prefixed "Refused to fetch non-public URL: " (125 to 126), there is no separate status for "blocked" versus "timed out" versus "4xx", all three land in scrape_status = "failed" with the exception text as the only distinguishing detail.

Data model

No new table. Three columns on the existing leads table (models/crm_model.py 101 to 103; migrated in 20260225_add_outreach_tables.py 37 to 41, part of outreach_001, ancestor metal_tiers_001):

leads (
  ...
  scraped_data   jsonb,             -- structured extraction, shape below
  scrape_status  varchar,           -- pending | scraping | done | failed (string column, no CHECK/enum)
  scraped_at     timestamptz,
  ...
)

scraped_data shape, exactly the dict _fetch_and_parse returns (scraper_service.py 163 to 173):

{
  "company_name": "og:title, else <title>, else \"\"",
  "description":  "og:description, else meta description, else \"\"",
  "tagline":      "first <h1> text, else \"\"",
  "services":     "up to 10 <li> texts whose length is strictly between 10 and 100 chars",
  "headings":     "up to 3 <h1> texts followed by up to 5 <h2> texts, concatenated",
  "keywords":     "up to 10 comma-split values from the <meta name=\"keywords\"> tag",
  "industry":     "\"\", a placeholder the scraper never fills; a comment marks it \"Will be inferred by AI\" (line 170)",
  "raw_text":     "page text with <script>/<style>/<nav>/<footer>/<header>/<aside> removed, joined with spaces, truncated to 2000 chars",
  "url":          "the ORIGINAL url passed to scrape_lead, not the final URL after redirects"
}

On failure scraped_data is instead {"error": "<exception message>"} (scraper_service.py 69), the same column, no separate error table.

What is NOT stored: the response body beyond the 2000-char stripped raw_text (no full HTML is kept); any URL from the redirect chain other than the caller's original one (url in the returned dict is always the pre-redirect input, scraper_service.py 172, the final current the loop landed on is discarded once parsing starts); no email addresses or other PII are extracted from the page, _fetch_and_parse reads only <title>, meta tags, <h1>/<h2>, <li> text and the stripped body text, there is no email/phone regex anywhere in this file.

API surface

There is no dedicated scrape endpoint. One HTTP route reaches this code, as the first half of a combined scrape-and-compose action; the scrape itself is otherwise called only from Python (the same route's second half, and nowhere else in the repo per grep -rn "scraper_service\." backend --include="*.py", which matches only scraper_service.py itself and outreach_service.py 211).

Route	Auth	Tenancy	Codes	Notes
POST /api/crm/outreach/campaigns/{campaign_id}/scrape-and-generate (outreach_router.py 82 to 85, mounted at /api/crm in app_fastapi.py 878)	AdminUser (api/dependencies.py 568, get_current_admin_user 364 to 375 raises 403 if not current_user.is_admin; no/invalid credentials raise 401 upstream in get_current_user, 195 to 198)	WorkspaceID header-derived (_get_workspace_id, api/dependencies.py 680 to 720+) and re-checked by outreach_service.get_campaign's own .filter(OutreachCampaign.id == campaign_id, OutreachCampaign.workspace_id == workspace_id) (43 to 51); no match (wrong workspace or wrong id, indistinguishable) raises 404 "Campaign not found" (53)	200 the combined stats dict; 401 no/bad credentials; 403 not an admin; 404 campaign not in this workspace; unguarded exceptions from the loop (a DB error, say) propagate as a 500, there is no route-level try/except	Runs scrape_and_generate synchronously in the request thread, one lead's scrape_lead at a time, no batching/queueing at the route (outreach_service.py 189 to 238); a campaign with many pending leads makes this one request slow, there is no progress endpoint

The two Python entrypoints this route (and nothing else, per the grep above) calls, not routes but the actual surface a rebuild reimplements:

Function	Owns its own transactions	Never raises past itself	Returns
ScraperService.scrape_lead(db, lead) (27 to 72)	yes (db.commit() on every path)	yes, wraps _fetch_and_parse in try/except Exception (44 to 72)	the scraped_data dict on success, {"error": str(e)} on failure
ScraperService.scrape_batch(db, leads) (74 to 101)	delegates to scrape_lead per item	yes, same reason	{"success": int, "failed": int, "skipped": int}; not called from outreach_service.py at 62ba4fc, scrape_and_generate loops leads itself rather than calling this method (a genuine gap between the two call sites, not determinable further without asking the author which one is meant to be canonical)

Application logic

scrape_lead (27 to 72), in commit order:

1. No lead.website ⇒ scrape_status = "failed", commit, return {"error": "No website URL"} (32 to 35), before anything is fetched.
2. Normalize: prepend https:// if the stored value has no http prefix (37 to 39), a bare example.com becomes https://example.com, there is no validation here yet, assert_public_url is the actual gate, inside _fetch_and_parse.
3. scrape_status = "scraping", db.flush() (41 to 42, not commit(), so a crash between here and the next commit leaves the row mid-flight only within the current transaction, not durably "scraping" for another process to see).
4. _fetch_and_parse(url) runs the SSRF-checked fetch and the BeautifulSoup extraction (see below). On success: lead.scraped_data = data, scrape_status = "done", scraped_at = now(UTC); if lead.company_name was empty, backfill it from data["company_name"] (51 to 52); write one ClientActivityLog row (activity_type=site_activity, description truncated to the first 100 chars of data["description"], 55 to 60); commit.
5. On any exception: scrape_status = "failed", scraped_data = {"error": str(e)}, commit, log, return the error dict (67 to 72). No retry, no backoff, a failed lead stays "failed" until scrape_and_generate is invoked again, and its own guard (outreach_service.py 210, if lead.scrape_status != "done") will re-attempt it since "failed" != "done".

_fetch_and_parse (103 to 173), the SSRF-hardened core, redirects followed manually so each hop is re-validated (module note, 104 to 111):

1. Open one httpx.Client(timeout=10, follow_redirects=False, headers={"User-Agent": ...}) for the whole hop sequence (117 to 121).
2. Loop up to MAX_REDIRECTS + 1 = 6 times (114, 122): call assert_public_url(current) first; a BlockedURLError is caught and re-raised as ValueError(f"Refused to fetch non-public URL: {e}") (123 to 126); only then client.get(current).
3. If the response is_redirect and carries a location header, advance current to response.next_request.url (httpx's own resolved absolute URL) or, failing that, the raw location header, and loop again (128 to 130), so hop 2's assert_public_url call runs on the URL the redirect actually points to, not the original.
4. Otherwise break. After the loop, response is None (only reachable if the loop range were empty, which it never is at MAX_REDIRECTS=5) raises ValueError("No response") (132 to 133); response.raise_for_status() raises on a non-2xx final response (134).
5. Parse with BeautifulSoup(response.text, "lxml") and build the dict described under Data model (136 to 173).

Invariants

• No network request is ever issued to a URL that has not just passed assert_public_url in this same loop iteration: the check runs first inside the for loop, immediately before client.get, on every iteration including redirect hops (_fetch_and_parse 122 to 127). No test exercises this claim at 62ba4fc (see Tests), it is verified here by reading the code, not by a passing suite.
• A hostname is blocked if ANY of its resolved addresses is private, loopback, link-local, reserved, multicast or unspecified, not only the first one returned by getaddrinfo (is_blocked_host, net.py 100 to 109, iterates every info in infos).
• DNS resolution failure is fail-closed: socket.gaierror makes is_blocked_host return True (blocked), not False (net.py 103 to 104); an unparseable IP literal is likewise treated as blocked (_is_blocked_ip 74 to 76).
• follow_redirects=False on the httpx.Client is load-bearing (119): httpx following redirects itself would fetch the redirect target before this module's loop ever saw it, silently reintroducing the SSRF this recipe exists to close. A rebuild that flips this flag back to True while keeping the rest of the loop defeats the whole recipe without any visible error.
• scrape_lead never raises past itself (44 to 72) and scrape_batch never raises past a single lead's failure (scrape_lead's own catch already absorbs it, so nothing in scrape_batch's loop needs its own try/except, and it has none, 90 to 94), a caller looping many leads cannot have one bad website abort the batch.

Frontend

The module that would render this, by id:

prospecting/ui/outreach-campaign-workspace   the admin campaign screen this recipe's effect surfaces
                                              in: "Scrapen & Generieren" triggers the route above
                                              (alchemy_labs frontend/src/pages/AdminOutreachCampaign.tsx
                                              lines 90, 95, 280-288 at 62ba4fc); it also renders
                                              scrape_status badges including scrape_failed (line 20)
                                              and shows the composed email whose opening lines came
                                              from this recipe's scraped_data. This recipe has no
                                              admin/debug UI of its own beyond that campaign screen.

Minimum admin/debug surface (no dedicated route or query exists in this repo at 62ba4fc; this is the query a human runs by hand): SELECT id, website, scrape_status, scraped_at FROM leads WHERE scrape_status = 'failed' ORDER BY updated_at DESC (the stuck-lead list); SELECT id, website, scraped_data->>'error' FROM leads WHERE scrape_status = 'failed' AND scraped_data ? 'error' (why each one failed, since the error text is the only detail kept).

Security & tenancy

Auth dependency: AdminUser on the one HTTP route (api/dependencies.py 568); no separate auth exists for the scrape step itself, it runs inside the request that already passed AdminUser. Tenancy: enforced at the campaign lookup, not at the scrape or the Lead query inside scrape_and_generate, get_campaign filters by workspace_id (43 to 51), but the subsequent db.query(Lead).filter(Lead.id == email.lead_id).first() in outreach_service.py (205) carries no workspace_id filter of its own; it relies on email.lead_id having been set, at import time, to a lead already scoped to the campaign's workspace (add_leads_to_campaign, Lead.workspace_id == ws_id on both the lookup and the create, 91 to 99, 94, 99). leads is on the RLS-policy table list (20260504d_workspace_rls_policies.py 37), but this router's DatabaseSession dependency is the plain get_db (no app.workspace_id GUC set, api/dependencies.py 572), and the migration's own docstring says its policies pass through (no-op) when that GUC is unset (20260504d_workspace_rls_policies.py 8 to 16), so RLS provides no defense-in-depth on this particular path, tenancy here is application-level filtering only.

Fail direction: closed. assert_public_url raises on an unresolvable host, a non-http(s) scheme, or any resolved address that is private/loopback/link-local/reserved/multicast/unspecified (net.py 116 to 133); is_blocked_host fails closed on a DNS error or an unparseable IP (103 to 104, 74 to 76); the fetch loop re-runs that same closed check on every redirect hop, not only the first request.

Secrets and PII at rest: none of this recipe's own, scraped_data holds only page-derived text (title, meta tags, headings, list items), truncated, from a page the admin's own campaign chose to scrape; no credential or token is read or written here. Attacker-controlled input: lead.website, sourced from a CSV/XLSX column an admin uploads (import_csv, outreach_service.py 151 to 185) or from LeadImportRequest/add_leads_to_campaign's item.get("website"), an arbitrary string, normalized only by prepending https:// if it lacks an http prefix (scraper_service.py 38 to 39), with no scheme/format validation before assert_public_url sees it; this is the exact input the SSRF hardening exists to constrain, since the string reaches an outbound httpx.Client.get call made by Labs' own server. Rate limits: none dedicated to this path; a large campaign's scrape runs synchronously inside one admin-authenticated request, bounded only by however many leads are pending_scrape in that campaign.

Known gap, not fixed in this recipe's code: assert_public_url resolves and checks the host at validation time; client.get performs its own separate DNS resolution afterward inside httpx/the OS resolver. Between those two resolutions a DNS record could change (a TOCTOU/DNS-rebinding window), net.py's own docstring acknowledges the class of problem generally ("DNS can change and redirects can retarget", 121 to 123) but the mitigation described is redirect re-validation, not pinning the resolved IP from the check to the actual connection; nothing in this file pins the connection to the validated address. Whether this is exploitable in practice against Labs' actual DNS/hosting setup is not determinable from the source alone.

Tests

No test file exists for this code at 62ba4fc. grep -rln "scraper_service\|ScraperService\|assert_public_url\|is_blocked_host\|BlockedURLError" backend/tests --include="*.py" (and without the include flag) returns nothing; find backend/tests -iname "*scrap*" and -iname "*net*" find nothing under backend/tests/outreach/ or any other test directory. The only test file that touches this recipe's domain at all is backend/tests/integration/test_workspace_isolation.py, which builds a generic Lead factory (importing Lead from models.crm_model, line 25) and asserts workspace isolation via assert_isolated(db_session, model=Lead, factory=factory) (108 to 117), this exercises Lead row-level tenancy in general, not the scraper or the SSRF guard specifically, and does not call scrape_lead, _fetch_and_parse, or any function in net.py.

This is a genuine gap: the SSRF-hardening invariants stated above (redirect re-validation, fail-closed DNS resolution, follow_redirects=False being load-bearing) are verified here by reading scraper_service.py and net.py directly, not by a passing test suite, because no such suite exists in this repo at the measured commit.

Deploy & ops

Config keys: none (Stack assumptions). Migrations: 20260225_add_outreach_tables.py (outreach_001, down-revision metal_tiers_001) adds scraped_data/scrape_status/scraped_at to leads (37 to 41) alongside the outreach_campaigns/outreach_emails tables this recipe's caller uses; the same three columns are also present in baseline_prod_schema_001.py's CREATE TABLE public.leads (1175 to 1197), a schema-only pg_dump of the live staging database taken 2026-08-02, confirming the migration had already been applied there well before this recipe's measured commit. Scheduler: none, nothing in scheduled_jobs.py calls scrape_lead, scrape_batch, or scrape_and_generate (grep -n "outreach\|scraper" backend/services/scheduled_jobs.py matches only the unrelated send-queue and reply-detection jobs, lines 248 to 262, 1839 to 1870); the scrape only ever runs inside the synchronous HTTP request above. Rollback: no data migration risk, the three columns are additive and nullable; rolling back the code leaves any lead already scrape_status = "done" with its scraped_data intact.

Live-in-app evidence for maturity: prod (verify-rather-than-assume, per this recipe's authoring instructions): the route is mounted unconditionally (app_fastapi.py 878, no feature flag, grep -n "OUTREACH\|SCRAPE" backend/core/config.py finds nothing); the outreach system has its own nav entry (frontend/src/components/nav.tsx 322, 330, /admin/outreach) and a campaign detail page that calls this exact route (AdminOutreachCampaign.tsx 90, 95, 280 to 288); the outreach tables/columns are part of the captured staging baseline (above). No separate confirmation of the production database specifically (as opposed to staging) was made; this recipe reports what the repo shows rather than asserting a production database was inspected directly.

Gotchas

• 2026-02-26 (dcba4d4, "Add cold outreach system"): _fetch_and_parse originally called client.get(url) directly with follow_redirects=True and no host validation at all, the scraper shipped with no SSRF guard for about five months.
• 2026-07-27 (3d39ef2, "audit(2026-07-22): security + scalability fixes", commit message's own words: "SSRF guards (outreach scraper, DB connections) via new backend/utils/net.py"): net.py was added and _fetch_and_parse was rewritten to the manual-redirect-loop shape described above; git show 3d39ef2 -- backend/services/outreach/scraper_service.py shows the exact diff, follow_redirects=True becoming False and the bare client.get(url) becoming the six-iteration loop with assert_public_url first. This recipe documents the code as it stands after that fix, not the original.
• 2026-09-12 (measured at 62ba4fc): scrape_batch exists and is fully SSRF-safe (it only calls scrape_lead per item) but is dead code from outreach_service.py's point of view, scrape_and_generate (the only caller path reachable from the API) loops leads and calls scrape_lead directly instead (outreach_service.py 204 to 217), never scrape_batch. scrape_batch's own BATCH_DELAY_SECONDS = 2 rate-limit sleep (scraper_service.py 21, 97 to 98) is consequently not exercised by the one code path that actually runs in the app.
• 2026-09-12 (measured at 62ba4fc): the url field written into scraped_data is always the pre-redirect input the caller passed in, never the final URL after following redirects (scraper_service.py 172, "url": url, the outer parameter, not current, which is discarded once the loop exits). A lead whose website redirects to a different domain will show the original domain in its own scraped data.

Reference implementation

Paths relative to the repo root; all at main 62ba4fc41009b454e1c033e5036c7e8577db2061 unless noted.

• alchemy_labs/backend/services/outreach/scraper_service.py (189 lines, whole file in scope): module docstring (1 to 3); constants (19 to 21); ScraperService.scrape_lead (27 to 72); scrape_batch (74 to 101); _fetch_and_parse (103 to 173, the SSRF-hardened core, its own docstring 104 to 111); _get_text/_get_meta (175 to 186); module-level singleton scraper_service (189).
• alchemy_labs/backend/utils/net.py (133 lines, whole file in scope, though get_client_ip 29 to 67 is out of this recipe's subject per When to use): module docstring (1 to 18); _is_blocked_ip (71 to 84); is_blocked_host (87 to 109); BlockedURLError (112 to 113); assert_public_url (116 to 133).
• alchemy_labs/backend/models/crm_model.py: Lead (70 to 112, scraped_data/scrape_status/scraped_at at 101 to 103); ClientActivityLog (183 to 197, the site_activity row scrape_lead writes on success); LeadSource/LeadStatus enums (16 to 35), read for context, not written by this recipe.
• alchemy_labs/backend/services/outreach/outreach_service.py: scrape_and_generate (189 to 238, the only caller of scrape_lead); add_leads_to_campaign (75 to 150, where lead.website first enters the system and where workspace scoping on Lead actually happens); get_campaign (43 to 54, the 404-on-wrong-workspace check).
• alchemy_labs/backend/api/routers/outreach_router.py: scrape_and_generate route (82 to 85); router prefix and imports (1 to 21).
• alchemy_labs/backend/services/outreach/composition_service.py: compose_email (47 to 116, specifically 68 to 79, where scraped_data's fields are read into the AI prompt context), cited for context only, out of this recipe's scope.
• alchemy_labs/backend/api/dependencies.py: AdminUser (568), get_current_admin_user (364 to 375), get_current_user (187 to 226, the 401 path), DatabaseSession (572, plain get_db), WorkspaceID/_get_workspace_id (680 to 720+, 779).
• alchemy_labs/backend/app_fastapi.py: the router mount (878).
• alchemy_labs/backend/migrations/versions/20260225_add_outreach_tables.py: the leads column additions (37 to 41).
• alchemy_labs/backend/migrations/versions/20260504d_workspace_rls_policies.py: leads on the RLS table list (37); the soft-policy/GUC-unset-passthrough design (module docstring, 8 to 16).
• alchemy_labs/backend/migrations/versions/baseline_prod_schema_001.py: leads table including the three scrape columns, captured from a staging pg_dump (1175 to 1197; provenance, 1 to 20).
• alchemy_labs/backend/tests/integration/test_workspace_isolation.py: the generic Lead isolation test (25, 108 to 117), the only test touching this recipe's domain.
• Frontend (cited under Frontend and Deploy & ops, out of this recipe's own scope): alchemy_labs/frontend/src/pages/AdminOutreachCampaign.tsx (18 to 20 status badges, 90 to 95, 280 to 288 the trigger button); alchemy_labs/frontend/src/components/nav.tsx (322, 330).

Deviations of the reference from an ideal implementation, all dated 2026-09-12: no test file exercises the SSRF guard or the scraper at all (Tests); scrape_batch is dead code, the real call path bypasses it and its rate-limit sleep (Gotchas); the validated-then-fetched host is not pinned across the gap between assert_public_url's resolution and httpx's own (Security & tenancy); the Lead query inside scrape_and_generate carries no workspace_id filter of its own, relying entirely on import-time scoping (Security & tenancy); errors of every kind (blocked URL, timeout, 4xx/5xx, parse failure) are collapsed into one scrape_status = "failed" with only the exception string to distinguish them (Stack assumptions).

Acceptance checks

No automated suite exists for this recipe at 62ba4fc (Tests), so these are commands a verifier can run directly against the source, not a pytest invocation.

1. grep -n "follow_redirects=False" alchemy_labs/backend/services/outreach/scraper_service.py prints exactly one match, inside _fetch_and_parse's httpx.Client(...) construction (line 119), confirms redirects are not auto-followed by httpx itself.
2. grep -n "assert_public_url(current)" alchemy_labs/backend/services/outreach/scraper_service.py prints exactly one match (line 124), inside the for _ in range(MAX_REDIRECTS + 1) loop, i.e. the check runs on every hop, not once before the loop.
3. grep -n "is_private\|is_loopback\|is_link_local\|is_reserved\|is_multicast\|is_unspecified" alchemy_labs/backend/utils/net.py prints six matches, all inside _is_blocked_ip (77 to 83), confirms every one of the six non-public categories this recipe claims is actually checked.
4. grep -rln "scraper_service\|assert_public_url\|is_blocked_host" alchemy_labs/backend/tests --include="*.py" prints nothing, confirms the Tests section's "no test file exists" claim rather than assuming it.
5. grep -n "scraped_data\|scrape_status\|scraped_at" alchemy_labs/backend/migrations/versions/baseline_prod_schema_001.py prints three matches inside the leads table definition (1195 to 1197), confirms the columns are part of the captured baseline schema, not only a pending migration.
6. git -C alchemy_labs log --oneline -- backend/utils/net.py prints exactly one commit, 3d39ef2, confirming the file's entire history is the one security-audit commit cited in Gotchas.