← back to Nationalrealestate
scripts/enrich-firm-contacts.mjs
317 lines
#!/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 ''; } }
// Distinctive firm-name tokens (drop generic real-estate words) used to VERIFY a page is actually
// about this firm before trusting its phone — a wrong number is worse than none for a call directory.
const NAME_STOP = new Set([
'real','estate','realty','realtors','realtor','inc','corp','llc','group','properties','property',
'company','co','the','and','of','ii','iii','homes','home','brokers','broker','brokerage','associates',
'partners','enterprises','services','international','california','holdings','management','investments',
'investment','investors','investor','financial','finance','capital','ventures','venture','solutions',
// common geographic / directional / generic words that are too weak to verify a domain on their own
'south','north','east','west','central','pacific','valley','coast','coastal','bay','city','county',
'star','sun','gold','golden','blue','first','prime','elite','premier','best','hall','park','sales',
'network','team','national','american','united','global','metro','urban','land','house','living','usa']);
function firmTokens(name) { return String(name || '').toLowerCase().replace(/[^a-z0-9 ]/g, ' ').split(/\s+/).filter(w => w.length >= 4 && !NAME_STOP.has(w)); }
function pageMatchesFirm(page, tokens) {
if (!tokens.length) return false; // no distinctive token → unverifiable → don't trust
const host = (page.host || '').toLowerCase();
// Trust ONLY a domain that carries the firm's distinctive name token (e.g. colliers.com,
// axencareers.com, serhant.com). Text-only matching is too noisy for common-word firm names
// ("Park and Refer" hitting a city-parks page) — a call directory must not guess. The firm's own
// known website is trusted separately at the call site.
return tokens.some(t => t.length >= 4 && host.includes(t));
}
// NANP sanity: area/exchange codes start 2-9 (already in PHONE_RE); reject a few impossible ones.
function plausiblePhone(p) { const m = p.match(/\((\d{3})\)/); return m && /^[2-9]\d{2}$/.test(m[1]) && !/(^0|555$)/.test(m[1]); }
// These are all CA firms, so the office line is a CA area code. A non-CA number that domain-matched
// on a shared surname/word (payneglasses 412, douglascuddletoy TOYS 800, jacuzzi.com hot-tubs 844)
// is the WRONG business — reject it. Better no number than a wrong one for a call directory.
const CA_AREA = new Set(['209','213','279','310','323','341','350','408','415','424','442','510','530','559','562','619','626','628','650','657','661','669','707','714','747','760','805','818','820','831','840','858','909','916','925','949','951']);
function caPhone(p) { const m = p.match(/\((\d{3})\)/); return m && CA_AREA.has(m[1]); }
// ── free web discovery (DuckDuckGo HTML) ─────────────────────────────────────────────────────────
const AGG = /(zillow|redfin|realtor\.com|realty\.com|loopnet|crexi|yelp|mapquest|bbb\.org|facebook|linkedin|indeed|glassdoor|manta|dnb\.com|bizapedia|buzzfile|nestfully|mlslistings|homes\.com|point2homes|trulia)/i;
const TOLLFREE = /^\((?:800|833|844|855|866|877|888)\)/; // aggregator toll-free = almost always a lead-routing line, not the firm's desk
// Never a brokerage — tourism / gov / wiki / social / directory noise that a place-or-generic firm
// name (e.g. "Pasadena Market Center" → visitpasadena.com) false-matches on.
const NONBIZ = /(^|\.)(visit[a-z]+\.(com|org)|cityof[a-z]+|[a-z]+chamber|wikipedia\.org|tripadvisor|casino|hotels?\.com|whatsapp|youtube|reddit|pinterest|instagram|twitter|x\.com|tiktok|amazon|ebay|craigslist)|\.gov(\/|$|\.)|\.edu(\/|$)/i;
function decodeBing(u) {
const m = u.match(/[?&]u=a1([^&]+)/); if (!m) return u;
try { return Buffer.from(m[1].replace(/-/g, '+').replace(/_/g, '/'), 'base64').toString('utf8'); } catch { return u; }
}
async function searchBing(q) {
const html = await fetchText('https://www.bing.com/search?q=' + encodeURIComponent(q));
if (!html) return [];
const urls = [];
for (const m of html.matchAll(/href="(https?:\/\/www\.bing\.com\/ck\/a\?[^"]*&(?:amp;)?u=a1[^"]+)"/g)) {
const u = decodeBing(m[1].replace(/&/g, '&')); if (/^https?:\/\//.test(u) && !/bing\.com/.test(u)) urls.push(u);
}
for (const m of html.matchAll(/<h2><a[^>]+href="(https?:\/\/(?!www\.bing)[^"]+)"/g)) urls.push(m[1]);
return [...new Set(urls)];
}
async function searchDDG(q) {
const html = await fetchText('https://html.duckduckgo.com/html/?q=' + encodeURIComponent(q));
if (!html) return [];
const urls = [];
for (const m of html.matchAll(/<a[^>]+class="result__a"[^>]+href="([^"]+)"/gi)) {
let u = m[1]; const dd = u.match(/uddg=([^&]+)/); if (dd) { try { u = decodeURIComponent(dd[1]); } catch {} }
if (/^https?:\/\//.test(u)) urls.push(u);
}
return [...new Set(urls)];
}
async function discoverSite(name, city) {
const q = `${name} ${city || ''} California real estate brokerage`;
let urls = await searchBing(q); // Bing = most resilient to rate-limits
if (!urls.length) urls = await searchDDG(q); // DDG fallback
// own-site first (non-aggregator), then aggregator listings (a last resort — still carry a 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' };
// NAME-MATCH GATE (correctness > coverage): only trust a page that is actually ABOUT this firm.
// A page from the firm's own known website is trusted; a DISCOVERED page must carry the firm's
// distinctive name token(s) in its domain or text. This kills the baseball-reference / cabq.gov /
// random-quiz-site false matches Bing returns for generic firm names.
const tokens = firmTokens(firm.name);
const knownHost = firm.website ? hostOf(/^https?:/.test(firm.website) ? firm.website : 'https://' + firm.website) : '';
let scored = pages.map(p => ({ ...p, host: hostOf(p.url), agg: AGG.test(p.url), phones: phoneCandidates(p.text).filter(plausiblePhone) }));
scored = scored.filter(p => !NONBIZ.test(p.host)); // drop tourism/gov/wiki/social noise first
scored = scored.filter(p => (knownHost && p.host === knownHost) || pageMatchesFirm(p, tokens));
if (!scored.length) return { status: 'unverified' }; // found pages, but none provably this firm → don't guess
// Prefer the firm's OWN domain over aggregators — an aggregator page often shows a lead-routing
// number or a specific agent's cell, not the firm's desk. Own-domain phones win; only fall back to
// NON-toll-free aggregator numbers (toll-free from an aggregator ≈ a lead line, dropped).
const own = scored.filter(p => !p.agg).sort((a, b) => b.phones.length - a.phones.length);
const agg = scored.filter(p => p.agg).sort((a, b) => b.phones.length - a.phones.length);
// CA firms → require a CA area code (kills wrong-business domain matches on shared surnames).
const ownPhones = [...new Set(own.flatMap(p => p.phones))].filter(caPhone).slice(0, 12);
const aggPhones = [...new Set(agg.flatMap(p => p.phones))].filter(p => !TOLLFREE.test(p) && caPhone(p)).slice(0, 12);
const best = own.find(p => p.phones.length) || own[0] || agg.find(p => p.phones.length) || agg[0];
if (!best) return { status: 'unreachable' };
const confidence = best.agg ? 'agg' : 'own';
const phones = ownPhones.length ? ownPhones : aggPhones; // own-domain phones preferred
const emails = emailCandidates([...own, ...agg].map(p => p.text).join(' '), best.agg ? '' : best.host);
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' : (pick.email ? 'email_only' : 'no_contact'),
phone: pick.phone || null, email: pick.email || null, address: pick.address || null,
source_url: best.url, source_host: best.host, confidence,
};
}
// ── DB writes ─────────────────────────────────────────────────────────────────────────────────────
async function writeFirm(client, firm, r) {
const src = r.source_host ? `web-${r.confidence || 'own'}:${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' }; }
// Dedup: a phone already assigned to a DIFFERENT firm is a shared switchboard / lead line — drop
// it (don't stamp the same number onto many firms). Keeps the directory honest.
if (r.phone) {
const dup = await pool.query(`SELECT 1 FROM firm WHERE phone=$1 AND id<>$2 LIMIT 1`, [r.phone, f.id]);
if (dup.rowCount) { r.phone = null; r.status = r.email ? 'email_only' : 'dup_phone'; }
}
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); });