← back to Nationalrealestate
usre: firm-phone-first contact enrichment driver (agents+local model, $0) (TK-10687)
4cd353fc8b00cba5ac2badd0b5cd4cd60c7157eb · 2026-08-18 13:26:16 -0700 · Steve Abrams
Steve: 'every firm business must have a phone' + 'every broker callable'. Resumable,
idempotent, cron-friendly driver over the ~21.7k CA firms that have an active broker
but no phone (ordered by active-broker count desc = biggest leverage first). Per firm:
Tier A known website + /contact + /about, Tier B free DuckDuckGo discovery; fetch =
plain HTTP -> local headless Chrome fallback for anti-bot; extraction = regex
candidates + LOCAL Ollama qwen3:14b picking the business's main phone/email/address
(never trusts a phone outside the candidate list). Writes firm.phone/email/
street_address + *_source + firm_contacts + phone_status (COALESCE-only, provenance
tagged, --apply gated; dry-run default). $0 (local model + local fetch + free search).
Proven: top-3 firms (eXp 4971, Real 4955, Compass 3683 active brokers) all got phones
-> ~13.6k broker cards callable via the firm fallback.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A scripts/enrich-firm-contacts.mjs
Diff
commit 4cd353fc8b00cba5ac2badd0b5cd4cd60c7157eb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Aug 18 13:26:16 2026 -0700
usre: firm-phone-first contact enrichment driver (agents+local model, $0) (TK-10687)
Steve: 'every firm business must have a phone' + 'every broker callable'. Resumable,
idempotent, cron-friendly driver over the ~21.7k CA firms that have an active broker
but no phone (ordered by active-broker count desc = biggest leverage first). Per firm:
Tier A known website + /contact + /about, Tier B free DuckDuckGo discovery; fetch =
plain HTTP -> local headless Chrome fallback for anti-bot; extraction = regex
candidates + LOCAL Ollama qwen3:14b picking the business's main phone/email/address
(never trusts a phone outside the candidate list). Writes firm.phone/email/
street_address + *_source + firm_contacts + phone_status (COALESCE-only, provenance
tagged, --apply gated; dry-run default). $0 (local model + local fetch + free search).
Proven: top-3 firms (eXp 4971, Real 4955, Compass 3683 active brokers) all got phones
-> ~13.6k broker cards callable via the firm fallback.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
scripts/enrich-firm-contacts.mjs | 243 +++++++++++++++++++++++++++++++++++++++
1 file changed, 243 insertions(+)
diff --git a/scripts/enrich-firm-contacts.mjs b/scripts/enrich-firm-contacts.mjs
new file mode 100644
index 0000000..cf3b501
--- /dev/null
+++ b/scripts/enrich-firm-contacts.mjs
@@ -0,0 +1,243 @@
+#!/usr/bin/env node
+// enrich-firm-contacts.mjs — TK-10687 firm-phone-first contact enrichment ($0, local-only).
+//
+// Steve: "every firm business must have a phone number" + "every broker callable" (firm fallback).
+// The CA DRE registry ships name + license + city only — no phone/email/street. This driver finds
+// them for the ~21.7k CA firms that have an active broker but no phone, so every affiliated active
+// broker inherits a callable number via the /api/brokers COALESCE fallback.
+//
+// Tiers per firm (cheapest first, stop as soon as a phone is found):
+// A. firm.website (if known) → fetch homepage + /contact + /about
+// B. free web discovery — DuckDuckGo HTML search "<name> <city> CA real estate" → top results
+// (fetch = plain HTTP first, then LOCAL headless Chrome for anti-bot 403s — $0, residential IP)
+// Extraction = regex candidates + a LOCAL model (Ollama qwen3:14b) that picks the business's MAIN
+// phone / email / street address from the page text. No paid APIs, no cloud.
+//
+// Writes (only with --apply): firm.phone/email/street_address (+ *_source), firm_contacts rows,
+// and always stamps contact_attempts / contact_enriched_at / phone_status so it's idempotent +
+// resumable + cron-friendly (skips firms already phoned or attempted within --retry-days).
+//
+// Usage:
+// node scripts/enrich-firm-contacts.mjs --limit 20 # dry-run (no writes), print findings
+// node scripts/enrich-firm-contacts.mjs --limit 200 --apply # write to usre.firm + firm_contacts
+// node scripts/enrich-firm-contacts.mjs --apply --retry-days 30
+'use strict';
+import pg from 'pg';
+import path from 'path';
+import os from 'os';
+
+const DB = process.env.DATABASE_URL || 'postgresql:///usre?host=/tmp';
+const OLLAMA = process.env.OLLAMA_URL || 'http://localhost:11434';
+const MODEL = process.env.ENRICH_MODEL || 'qwen3:14b';
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36';
+
+const arg = (k, d) => { const i = process.argv.indexOf(k); return i > -1 ? (process.argv[i + 1] ?? true) : d; };
+const APPLY = process.argv.includes('--apply');
+const LIMIT = +arg('--limit', 50) || 50;
+const RETRY_DAYS = +arg('--retry-days', 21);
+const DELAY = +arg('--delay', 400);
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+// ── active-broker predicate (per-state DRE status strings are messy) ────────────────────────────
+const ACTIVE = `(license_status ILIKE 'active%' OR license_status ILIKE 'licensed%' OR license_status ILIKE 'current / active%')`;
+
+// ── phone / email extraction ────────────────────────────────────────────────────────────────────
+const PHONE_RE = /(?:\+?1[\s.\-]?)?\(?([2-9]\d{2})\)?[\s.\-]?(\d{3})[\s.\-]?(\d{4})\b/g;
+const EMAIL_RE = /\b[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}\b/gi;
+function normPhone(m) { return `(${m[1]}) ${m[2]}-${m[3]}`; }
+function phoneCandidates(text) {
+ const out = new Set(); let m;
+ PHONE_RE.lastIndex = 0;
+ while ((m = PHONE_RE.exec(text))) { const p = normPhone(m); if (!/^\(?(000|111|123|555)/.test(m[1] + m[2])) out.add(p); }
+ return [...out].slice(0, 12);
+}
+function emailCandidates(text, domain) {
+ const out = new Set();
+ for (const e of (text.match(EMAIL_RE) || [])) {
+ const lo = e.toLowerCase();
+ if (/\.(png|jpg|jpeg|gif|webp|svg|css|js)$/.test(lo)) continue;
+ if (/(example|sentry|wix|squarespace|godaddy|@2x|domain\.com)/.test(lo)) continue;
+ if (domain && !lo.endsWith('@' + domain) && !/^(info|contact|hello|sales|admin|office|team)@/.test(lo)) continue;
+ out.add(lo);
+ }
+ return [...out].slice(0, 8);
+}
+
+// ── fetch: plain HTTP → local headless Chrome fallback (anti-bot) ────────────────────────────────
+let _chromium = null;
+async function getChromium() {
+ if (_chromium) return _chromium;
+ const tries = [
+ () => import('playwright-core').then(m => m.chromium),
+ () => import(path.join(os.homedir(), '.claude/skills/browserbase/node_modules/playwright-core/index.js')).then(m => m.chromium),
+ ];
+ for (const t of tries) { try { _chromium = await t(); if (_chromium) return _chromium; } catch { /* next */ } }
+ return null;
+}
+async function fetchText(url) {
+ try {
+ const ctl = new AbortController(); const to = setTimeout(() => ctl.abort(), 12000);
+ const r = await fetch(url, { headers: { 'User-Agent': UA, 'Accept': 'text/html,*/*' }, redirect: 'follow', signal: ctl.signal });
+ clearTimeout(to);
+ if (r.ok) { const t = await r.text(); if (t && t.length > 400 && !/just a moment|enable javascript and cookies/i.test(t)) return t; }
+ } catch { /* fall through to Chrome */ }
+ // anti-bot / JS-only → local headless Chrome
+ const chromium = await getChromium();
+ if (!chromium) return '';
+ let b;
+ try {
+ b = await chromium.launch({ channel: 'chrome', headless: true });
+ const page = await (await b.newContext({ userAgent: UA })).newPage();
+ page.setDefaultTimeout(20000);
+ await page.goto(url, { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(1500);
+ return await page.content();
+ } catch { return ''; }
+ finally { try { await b?.close(); } catch {} }
+}
+function stripHtml(html) {
+ return String(html || '')
+ .replace(/<script[\s\S]*?<\/script>/gi, ' ').replace(/<style[\s\S]*?<\/style>/gi, ' ')
+ .replace(/<[^>]+>/g, ' ').replace(/ /g, ' ').replace(/&/g, '&').replace(/\s+/g, ' ').trim();
+}
+function hostOf(u) { try { return new URL(u).hostname.replace(/^www\./, ''); } catch { return ''; } }
+
+// ── free web discovery (DuckDuckGo HTML) ─────────────────────────────────────────────────────────
+const AGG = /(zillow|redfin|realtor\.com|loopnet|crexi|yelp|mapquest|bbb\.org|facebook|linkedin|indeed|glassdoor|manta|dnb\.com|bizapedia|buzzfile)/i;
+async function discoverSite(name, city) {
+ const q = encodeURIComponent(`${name} ${city || ''} California real estate brokerage`);
+ const html = await fetchText(`https://html.duckduckgo.com/html/?q=${q}`);
+ if (!html) return [];
+ const urls = [];
+ const re = /<a[^>]+class="result__a"[^>]+href="([^"]+)"/gi; let m;
+ while ((m = re.exec(html)) && urls.length < 6) {
+ let u = m[1];
+ const dd = u.match(/uddg=([^&]+)/); if (dd) { try { u = decodeURIComponent(dd[1]); } catch {} }
+ if (/^https?:\/\//.test(u)) urls.push(u);
+ }
+ // own-site first (non-aggregator), then aggregator listings (still carry the phone)
+ return [...urls.filter(u => !AGG.test(u)), ...urls.filter(u => AGG.test(u))].slice(0, 4);
+}
+
+// ── local-model pick (qwen3): choose the business's MAIN phone/email/address ─────────────────────
+async function modelPick(name, city, text, phones, emails) {
+ const prompt = `You are extracting the MAIN business contact for a real-estate brokerage.
+Brokerage: "${name}"${city ? ` in ${city}, California` : ''}.
+From the page text and candidate lists, return the brokerage's primary OFFICE phone, a business email, and the street address IF clearly present.
+Rules: pick a phone ONLY from the candidates; prefer a main/office line over an agent cell; email must belong to this business; address = street line + city/state/zip if shown, else "".
+Candidate phones: ${JSON.stringify(phones)}
+Candidate emails: ${JSON.stringify(emails)}
+Page text (truncated): ${text.slice(0, 2600)}
+Reply with ONLY compact JSON: {"phone":"","email":"","address":""}`;
+ try {
+ const ctl = new AbortController(); const to = setTimeout(() => ctl.abort(), 30000);
+ const r = await fetch(`${OLLAMA}/api/generate`, {
+ method: 'POST', headers: { 'Content-Type': 'application/json' }, signal: ctl.signal,
+ body: JSON.stringify({ model: MODEL, prompt, stream: false, think: false, options: { temperature: 0 } }),
+ });
+ clearTimeout(to);
+ const d = await r.json();
+ const j = JSON.parse((d.response || '').match(/\{[\s\S]*\}/)?.[0] || '{}');
+ // never trust a phone the model invented — it must be in the candidate list
+ if (j.phone && !phones.includes(j.phone)) j.phone = phones[0] || '';
+ if (j.email && !emails.includes(String(j.email).toLowerCase())) j.email = emails[0] || '';
+ return { phone: j.phone || (phones[0] || ''), email: (j.email || emails[0] || '').toLowerCase(), address: (j.address || '').trim() };
+ } catch {
+ return { phone: phones[0] || '', email: (emails[0] || '').toLowerCase(), address: '' };
+ }
+}
+
+// ── per-firm enrichment ──────────────────────────────────────────────────────────────────────────
+async function enrichFirm(firm) {
+ const pages = []; // {url, text}
+ const seen = new Set();
+ const addPage = async (u) => {
+ if (!u || seen.has(u)) return; seen.add(u);
+ const html = await fetchText(u); if (!html) return;
+ pages.push({ url: u, text: stripHtml(html) });
+ };
+ // Tier A: known website + its likely contact pages
+ if (firm.website) {
+ const base = /^https?:/.test(firm.website) ? firm.website : 'https://' + firm.website;
+ await addPage(base);
+ const h = hostOf(base);
+ for (const p of ['/contact', '/contact-us', '/about']) { if (pages.length < 3) await addPage(base.replace(/\/$/, '') + p); }
+ void h;
+ }
+ // Tier B: discovery when no phone yet
+ if (!pages.length || !phoneCandidates(pages.map(p => p.text).join(' ')).length) {
+ for (const u of await discoverSite(firm.name, firm.hq_city)) { if (pages.length < 4) await addPage(u); }
+ }
+ if (!pages.length) return { status: 'unreachable' };
+
+ // rank the pages: the one with the most phone candidates likely IS the contact page
+ const scored = pages.map(p => ({ ...p, phones: phoneCandidates(p.text) })).sort((a, b) => b.phones.length - a.phones.length);
+ const best = scored[0];
+ const domain = hostOf(best.url);
+ const phones = [...new Set(scored.flatMap(p => p.phones))].slice(0, 12);
+ const emails = emailCandidates(scored.map(p => p.text).join(' '), AGG.test(best.url) ? '' : domain);
+ if (!phones.length && !emails.length) return { status: 'no_contact', source_url: best.url };
+
+ const pick = await modelPick(firm.name, firm.hq_city, best.text, phones, emails);
+ return {
+ status: pick.phone ? 'found' : (emails.length ? 'email_only' : 'no_contact'),
+ phone: pick.phone || null, email: pick.email || null, address: pick.address || null,
+ source_url: best.url, source_host: domain,
+ };
+}
+
+// ── DB writes ─────────────────────────────────────────────────────────────────────────────────────
+async function writeFirm(client, firm, r) {
+ const src = r.source_host ? `web:${r.source_host}` : 'web';
+ await client.query(
+ `UPDATE firm SET
+ phone = COALESCE(NULLIF(phone,''), $2),
+ phone_source = CASE WHEN COALESCE(NULLIF(phone,''),'')='' AND $2 IS NOT NULL THEN $3 ELSE phone_source END,
+ email = COALESCE(NULLIF(email,''), $4),
+ email_source = CASE WHEN COALESCE(NULLIF(email,''),'')='' AND $4 IS NOT NULL THEN $3 ELSE email_source END,
+ street_address = COALESCE(NULLIF(street_address,''), $5),
+ address_source = CASE WHEN COALESCE(NULLIF(street_address,''),'')='' AND $5 IS NOT NULL THEN $3 ELSE address_source END,
+ contact_attempts = contact_attempts + 1,
+ contact_enriched_at = now(),
+ phone_status = $6
+ WHERE id = $1`,
+ [firm.id, r.phone, src, r.email, r.address, r.status]);
+ for (const [kind, val] of [['phone', r.phone], ['email', r.email]]) {
+ if (val) await client.query(
+ `INSERT INTO firm_contacts (firm_id, kind, value, source_url) VALUES ($1,$2,$3,$4)
+ ON CONFLICT (firm_id, kind, value) DO NOTHING`, [firm.id, kind, val, r.source_url || null]);
+ }
+}
+
+async function main() {
+ const pool = new pg.Pool({ connectionString: DB });
+ const firms = (await pool.query(
+ `WITH af AS (
+ SELECT firm_id, count(*) n FROM broker
+ WHERE firm_id IS NOT NULL AND license_state='CA' AND ${ACTIVE}
+ GROUP BY firm_id)
+ SELECT f.id, f.name, f.hq_city, f.website, af.n active_brokers
+ FROM firm f JOIN af ON af.firm_id=f.id
+ WHERE coalesce(nullif(f.phone,''),'')=''
+ AND (f.contact_enriched_at IS NULL OR f.contact_enriched_at < now() - ($1||' days')::interval)
+ ORDER BY af.n DESC, f.id
+ LIMIT $2`, [String(RETRY_DAYS), LIMIT])).rows;
+
+ console.log(`enrich-firm-contacts: ${firms.length} CA firms (active broker, no phone) · ${APPLY ? 'APPLY' : 'DRY-RUN'} · model ${MODEL} · $0 local`);
+ let found = 0, email = 0, attempted = 0;
+ const client = APPLY ? await pool.connect() : null;
+ for (const f of firms) {
+ attempted++;
+ let r; try { r = await enrichFirm(f); } catch (e) { r = { status: 'error' }; }
+ if (r.phone) found++; if (r.email) email++;
+ const tag = r.phone ? '📞 ' + r.phone : (r.email ? '✉ ' + r.email : '· ' + r.status);
+ console.log(` [${attempted}/${firms.length}] ${f.name} (${f.active_brokers} active) → ${tag}${r.address ? ' 📍 ' + r.address : ''}${r.source_host ? ' <' + r.source_host + '>' : ''}`);
+ if (APPLY && (r.phone || r.email || r.address)) { try { await writeFirm(client, f, r); } catch (e) { console.log(' write err:', e.message.split('\n')[0]); } }
+ else if (APPLY) { try { await client.query(`UPDATE firm SET contact_attempts=contact_attempts+1, contact_enriched_at=now(), phone_status=$2 WHERE id=$1`, [f.id, r.status]); } catch {} }
+ await sleep(DELAY);
+ }
+ if (client) client.release();
+ console.log(`\nDONE: phones ${found}/${attempted} · emails ${email}/${attempted} · ${APPLY ? 'written' : 'dry-run (no writes)'} · cost $0 (local model + local fetch + free search)`);
+ await pool.end();
+}
+main().catch(e => { console.error('FATAL', e); process.exit(1); });
← 7d88094 usre: resolved broker phone + firm-fallback tier + email/add
·
back to Nationalrealestate
·
usre: firm-phone coverage canary + incremental enrichment cr a05bd0f →