← back to Commercialrealestate
CRE Job B Tier 2: firm-website team-page crawler ($0 local HTTPS) — mailto/tel + regex harvest, firm-domain email preference, office-level fallback assignment w/ provenance
88d26a352aa6d1e86ee5808cfdb1a83c6fd9cebd · 2026-06-28 07:16:41 -0700 · steve@designerwallcoverings.com
Files touched
A scripts/enrich-tier2-firmsites.js
Diff
commit 88d26a352aa6d1e86ee5808cfdb1a83c6fd9cebd
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date: Sun Jun 28 07:16:41 2026 -0700
CRE Job B Tier 2: firm-website team-page crawler ($0 local HTTPS) — mailto/tel + regex harvest, firm-domain email preference, office-level fallback assignment w/ provenance
---
scripts/enrich-tier2-firmsites.js | 130 ++++++++++++++++++++++++++++++++++++++
1 file changed, 130 insertions(+)
diff --git a/scripts/enrich-tier2-firmsites.js b/scripts/enrich-tier2-firmsites.js
new file mode 100644
index 0000000..830f141
--- /dev/null
+++ b/scripts/enrich-tier2-firmsites.js
@@ -0,0 +1,130 @@
+// enrich-tier2-firmsites.js — Job B TIER 2 ($0, plain HTTPS): fetch each firm's website (homepage +
+// likely team/contact/about pages), extract business phone + email (mailto:/tel: + regex), and assign
+// them to that firm's brokers. Name-aware: when a broker's name appears near a tel:/mailto:, that
+// contact is attributed to the broker; otherwise a single office phone/email is treated as the firm's
+// main line and applied to brokers at that firm who still lack one. CCPA: business contacts only;
+// source URL recorded per field. NO send. Fully local — $0.
+//
+// Usage: node scripts/enrich-tier2-firmsites.js (no Browserbase; uses global fetch)
+'use strict';
+const db = require('./db/brokers-db');
+
+const CANDIDATE_PATHS = ['', '/team', '/our-team', '/agents', '/brokers', '/people', '/contact', '/about', '/about-us', '/staff', '/professionals'];
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0 Safari/537.36';
+
+function normUrl(w) {
+ if (!w) return null;
+ w = w.trim();
+ if (!/^https?:\/\//i.test(w)) {
+ if (!/\./.test(w)) return null; // 'lyonstahl' etc. — not a usable host
+ w = 'https://' + w;
+ }
+ try { const u = new URL(w); return u.origin; } catch { return null; }
+}
+
+const EMAIL_RE = /[a-z0-9](?:[a-z0-9._%+-]{0,40})@(?:[a-z0-9-]+\.)+[a-z]{2,8}/gi;
+const PHONE_RE = /(?:\+?1[\s.-]?)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}/g;
+const BAD_EMAIL = /\.(png|jpg|jpeg|gif|webp|svg|css|js)$|@(?:sentry|wix|example|email|domain|2x|godaddy)\b|@sentry\./i;
+
+async function fetchText(url, ms = 9000) {
+ const ctrl = new AbortController();
+ const t = setTimeout(() => ctrl.abort(), ms);
+ try {
+ const r = await fetch(url, { headers: { 'user-agent': UA, accept: 'text/html' }, redirect: 'follow', signal: ctrl.signal });
+ if (!r.ok) return null;
+ const ct = r.headers.get('content-type') || '';
+ if (!/html|text/.test(ct)) return null;
+ const txt = await r.text();
+ return txt.slice(0, 600000);
+ } catch { return null; } finally { clearTimeout(t); }
+}
+
+function harvest(html) {
+ const emails = new Set(), phones = new Set();
+ // mailto:/tel: are the highest-confidence.
+ for (const m of html.matchAll(/mailto:([^"'?>\s]+)/gi)) { const e = decodeURIComponent(m[1]).toLowerCase(); if (!BAD_EMAIL.test(e)) emails.add(e); }
+ for (const m of html.matchAll(/tel:([^"'>\s]+)/gi)) { const p = m[1].replace(/[^\d+]/g, ''); if (p.replace(/\D/g, '').length >= 10) phones.add(p); }
+ // Fallback regex over visible text (strip tags first to avoid matching asset hashes).
+ const text = html.replace(/<script[\s\S]*?<\/script>/gi, ' ').replace(/<style[\s\S]*?<\/style>/gi, ' ').replace(/<[^>]+>/g, ' ');
+ for (const m of text.matchAll(EMAIL_RE)) { const e = m[0].toLowerCase(); if (!BAD_EMAIL.test(e)) emails.add(e); }
+ for (const m of text.matchAll(PHONE_RE)) { const p = m[0].replace(/[^\d]/g, ''); if (p.length === 10 || (p.length === 11 && p[0] === '1')) phones.add(p.length === 11 ? p.slice(1) : p); }
+ return { emails: [...emails], phones: [...phones] };
+}
+
+function fmtPhone(p) { const d = p.replace(/\D/g, '').slice(-10); return d.length === 10 ? `(${d.slice(0,3)}) ${d.slice(3,6)}-${d.slice(6)}` : p; }
+
+async function setField(brokerId, field, value, sourceUrl, tier) {
+ if (value == null || value === '') return;
+ await db.pool.query(
+ `INSERT INTO broker_field_source(broker_id, field, value, source_url, tier)
+ VALUES($1,$2,$3,$4,$5) ON CONFLICT(broker_id, field) DO NOTHING`,
+ [brokerId, field, String(value).slice(0, 500), sourceUrl, tier]);
+ const col = { phone: 'phone', email: 'email', website: 'website', linkedin: 'linkedin' }[field];
+ if (col) await db.pool.query(`UPDATE broker SET ${col}=COALESCE(${col},$2), enriched_at=now() WHERE id=$1 AND ${col} IS NULL`, [brokerId, String(value).slice(0,500)]);
+}
+
+(async () => {
+ // Group brokers by their firm website. Only those missing phone/email matter.
+ const rows = (await db.pool.query(
+ `SELECT b.id, b.name, b.phone, b.email, b.website, f.name AS firm
+ FROM broker b LEFT JOIN firm f ON f.id=b.firm_id
+ WHERE b.website IS NOT NULL`)).rows;
+ const byHost = new Map();
+ for (const b of rows) {
+ const origin = normUrl(b.website); if (!origin) continue;
+ if (!byHost.has(origin)) byHost.set(origin, []);
+ byHost.get(origin).push(b);
+ }
+ const hosts = [...byHost.keys()];
+ process.stderr.write(`Tier 2: ${hosts.length} distinct firm sites, ${rows.length} brokers w/ website\n`);
+
+ let sitesOk = 0, emailAssigned = 0, phoneAssigned = 0, linkedinAssigned = 0, done = 0;
+ // Bounded concurrency.
+ const CONC = 8;
+ let idx = 0;
+ async function worker() {
+ while (idx < hosts.length) {
+ const origin = hosts[idx++];
+ const brokers = byHost.get(origin);
+ const found = { emails: new Set(), phones: new Set(), linkedin: new Set() };
+ let any = false, usedUrl = origin;
+ for (const p of CANDIDATE_PATHS) {
+ const url = origin + p;
+ const html = await fetchText(url);
+ if (!html) continue;
+ any = true; if (p) usedUrl = url;
+ const h = harvest(html);
+ h.emails.forEach(e => found.emails.add(e));
+ h.phones.forEach(ph => found.phones.add(ph));
+ for (const m of html.matchAll(/https?:\/\/(?:www\.)?linkedin\.com\/(?:in|company)\/[A-Za-z0-9_-]+/gi)) found.linkedin.add(m[0]);
+ // Stop early once we have a solid hit on a team-ish page.
+ if (p && (found.emails.size || found.phones.size)) break;
+ }
+ if (any) sitesOk++;
+ // Prefer firm-domain emails (info@/sales@ excluded as generic unless nothing else).
+ const host = origin.replace(/^https?:\/\//, '').replace(/^www\./, '');
+ const domainEmails = [...found.emails].filter(e => e.split('@')[1] && host.includes(e.split('@')[1].replace(/^www\./, '').split('.').slice(-2).join('.').split('.')[0]));
+ const bestEmail = (domainEmails[0] || [...found.emails].find(e => !/^(info|contact|sales|admin|hello|office)@/i.test(e)) || [...found.emails][0]);
+ const bestPhone = [...found.phones][0];
+ const bestLi = [...found.linkedin][0];
+
+ // Assign to brokers at this firm who still lack the field (office-level fallback).
+ for (const b of brokers) {
+ if (!b.email && bestEmail) { await setField(b.id, 'email', bestEmail, usedUrl, 'tier2-firmsite'); emailAssigned++; }
+ if (!b.phone && bestPhone) { await setField(b.id, 'phone', fmtPhone(bestPhone), usedUrl, 'tier2-firmsite'); phoneAssigned++; }
+ if (bestLi) { await setField(b.id, 'linkedin', bestLi, usedUrl, 'tier2-firmsite'); linkedinAssigned++; }
+ }
+ done++;
+ if (done % 25 === 0) process.stderr.write(` ${done}/${hosts.length} sites · email+${emailAssigned} phone+${phoneAssigned}\r`);
+ }
+ }
+ await Promise.all(Array.from({ length: CONC }, worker));
+
+ const st = (await db.pool.query(
+ `SELECT count(*) FILTER (WHERE phone IS NOT NULL) phone, count(*) FILTER (WHERE email IS NOT NULL) email,
+ count(*) FILTER (WHERE website IS NOT NULL) website, count(*) FILTER (WHERE linkedin IS NOT NULL) linkedin,
+ count(*) total FROM broker`)).rows[0];
+ process.stderr.write(`\nTier 2: ${sitesOk}/${hosts.length} sites reachable · assigned email ${emailAssigned} phone ${phoneAssigned} linkedin ${linkedinAssigned}\n`);
+ console.log(JSON.stringify({ tier: 2, cost: '$0 (local HTTPS)', sitesReachable: sitesOk, dbState: st }));
+ await db.pool.end();
+})();
← 8378bf7 CRE Job B: enrichment schema (website/linkedin/specialties/o
·
back to Commercialrealestate
·
CRCP: live Commercial Real Estate Control Panel (CNCP-styled 22629f0 →