← back to Nationalrealestate
auto-save: 2026-07-21T14:07:54 (8 files) — package-lock.json package.json public/brokers.html public/crcp-grid.js src/server/index.ts
31f2bbc6e023ef0088cbd2f6e657f2a96ae25c25 · 2026-07-21 14:07:55 -0700 · Steve Abrams
Files touched
A db/migrations/002_firm_site.sqlM package-lock.jsonM package.jsonM public/brokers.htmlM public/crcp-grid.jsA src/crawl/firm_front_page.tsA src/enrich/firm_website_discovery.tsM src/server/index.ts
Diff
commit 31f2bbc6e023ef0088cbd2f6e657f2a96ae25c25
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 14:07:55 2026 -0700
auto-save: 2026-07-21T14:07:54 (8 files) — package-lock.json package.json public/brokers.html public/crcp-grid.js src/server/index.ts
---
db/migrations/002_firm_site.sql | 28 +++++
package-lock.json | 45 ++++++++
package.json | 3 +
public/brokers.html | 88 +++++++++++++-
public/crcp-grid.js | 4 +-
src/crawl/firm_front_page.ts | 216 +++++++++++++++++++++++++++++++++++
src/enrich/firm_website_discovery.ts | 195 +++++++++++++++++++++++++++++++
src/server/index.ts | 48 +++++++-
8 files changed, 621 insertions(+), 6 deletions(-)
diff --git a/db/migrations/002_firm_site.sql b/db/migrations/002_firm_site.sql
new file mode 100644
index 0000000..088abdf
--- /dev/null
+++ b/db/migrations/002_firm_site.sql
@@ -0,0 +1,28 @@
+-- M-B2: firm website discovery + front-page crawl.
+-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
+
+CREATE TABLE firm_site (
+ id SERIAL PRIMARY KEY,
+ firm_id INT NOT NULL UNIQUE REFERENCES firm(id),
+ url TEXT,
+ discovery_method TEXT NOT NULL,
+ discovered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ http_status INT,
+ title TEXT,
+ has_idx_listings BOOLEAN,
+ crawl_status TEXT,
+ crawled_at TIMESTAMPTZ,
+ screenshot_path TEXT
+);
+
+CREATE TABLE firm_contacts (
+ id SERIAL PRIMARY KEY,
+ firm_id INT NOT NULL REFERENCES firm(id),
+ kind TEXT NOT NULL CHECK (kind IN ('email', 'phone', 'contact_url')),
+ value TEXT NOT NULL,
+ source_url TEXT,
+ found_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (firm_id, kind, value)
+);
+
+CREATE INDEX idx_firm_contacts_firm ON firm_contacts (firm_id);
diff --git a/package-lock.json b/package-lock.json
index f64180b..68d63f2 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -11,6 +11,7 @@
"dotenv": "^16.4.5",
"express": "^4.21.1",
"pg": "^8.13.1",
+ "playwright": "^1.59.1",
"tsx": "^4.19.2"
},
"devDependencies": {
@@ -1255,6 +1256,50 @@
"split2": "^4.1.0"
}
},
+ "node_modules/playwright": {
+ "version": "1.59.1",
+ "resolved": "https://registry.npmjs.org/playwright/-/playwright-1.59.1.tgz",
+ "integrity": "sha512-C8oWjPR3F81yljW9o5OxcWzfh6avkVwDD2VYdwIGqTkl+OGFISgypqzfu7dOe4QNLL2aqcWBmI3PMtLIK233lw==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "playwright-core": "1.59.1"
+ },
+ "bin": {
+ "playwright": "cli.js"
+ },
+ "engines": {
+ "node": ">=18"
+ },
+ "optionalDependencies": {
+ "fsevents": "2.3.2"
+ }
+ },
+ "node_modules/playwright-core": {
+ "version": "1.59.1",
+ "resolved": "https://registry.npmjs.org/playwright-core/-/playwright-core-1.59.1.tgz",
+ "integrity": "sha512-HBV/RJg81z5BiiZ9yPzIiClYV/QMsDCKUyogwH9p3MCP6IYjUFu/MActgYAvK0oWyV9NlwM3GLBjADyWgydVyg==",
+ "license": "Apache-2.0",
+ "bin": {
+ "playwright-core": "cli.js"
+ },
+ "engines": {
+ "node": ">=18"
+ }
+ },
+ "node_modules/playwright/node_modules/fsevents": {
+ "version": "2.3.2",
+ "resolved": "https://registry.npmjs.org/fsevents/-/fsevents-2.3.2.tgz",
+ "integrity": "sha512-xiqMQR4xAeHTuB9uWm+fFRcIOgKBMiOBP+eXiyT7jsgVCq1bkVygt00oASowB7EdtpOHaaPgKt812P9ab+DDKA==",
+ "hasInstallScript": true,
+ "license": "MIT",
+ "optional": true,
+ "os": [
+ "darwin"
+ ],
+ "engines": {
+ "node": "^8.16.0 || ^10.6.0 || >=11.0.0"
+ }
+ },
"node_modules/postgres-array": {
"version": "2.0.0",
"resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
diff --git a/package.json b/package.json
index e43c84d..89f8fcf 100644
--- a/package.json
+++ b/package.json
@@ -13,6 +13,8 @@
"ingest:acs": "tsx src/ingest/census_acs.ts",
"ingest:fhfa": "tsx src/ingest/fhfa_hpi.ts",
"ingest:brokers": "tsx src/ingest/brokers/engine.ts",
+ "discover:firms": "tsx src/enrich/firm_website_discovery.ts",
+ "crawl:firms": "tsx src/crawl/firm_front_page.ts",
"derive": "tsx src/ingest/derive_metrics.ts",
"score": "tsx src/score/opportunity.ts",
"refresh": "tsx src/jobs/refresh_all.ts",
@@ -22,6 +24,7 @@
"dotenv": "^16.4.5",
"express": "^4.21.1",
"pg": "^8.13.1",
+ "playwright": "^1.59.1",
"tsx": "^4.19.2"
},
"devDependencies": {
diff --git a/public/brokers.html b/public/brokers.html
index 87ab91d..1dc3a09 100644
--- a/public/brokers.html
+++ b/public/brokers.html
@@ -36,6 +36,22 @@
#pager button:not(:disabled):hover{border-color:var(--gold);color:var(--gold);}
#loading{padding:60px;text-align:center;color:var(--muted);}
.lic{font-family:ui-monospace,Menlo,monospace;font-size:12px;}
+ /* M-B2 inline firm detail (opens under the clicked row — Steve's standing pattern) */
+ .cg-tbl tr[data-ri], .cg-card[data-ri]{cursor:pointer;}
+ .usre-fd{background:#0d1119;border:1px solid var(--line);border-left:3px solid var(--gold);
+ border-radius:8px;margin:6px 0;padding:12px 14px;font-size:13px;}
+ td > .usre-fd{margin:2px 0;}
+ .usre-fd h3{margin:0 0 6px;font-size:14px;color:var(--gold);}
+ .usre-fd .fd-muted{color:var(--muted);}
+ .usre-fd .fd-sec{margin-top:8px;}
+ .usre-fd .fd-sec b{color:var(--fg);font-size:12px;text-transform:uppercase;letter-spacing:.04em;}
+ .usre-fd a{color:var(--gold);text-decoration:none;} .usre-fd a:hover{text-decoration:underline;}
+ .usre-fd .fd-pill{display:inline-block;border:1px solid var(--line);border-radius:999px;
+ padding:1px 9px;font-size:11px;margin-left:6px;color:var(--muted);}
+ .usre-fd .fd-pill.yes{color:#5fd38a;border-color:#2b4d38;}
+ .usre-fd .fd-pill.no{color:#d97d7d;border-color:#4d2b2b;}
+ .usre-fd .fd-brokers{display:flex;flex-wrap:wrap;gap:4px 12px;margin-top:4px;max-height:120px;overflow:auto;}
+ .usre-fd .fd-brokers span{color:var(--muted);font-size:12px;white-space:nowrap;}
</style>
</head>
<body>
@@ -88,13 +104,17 @@ let grid = null;
async function loadStats() {
const d = await fetch('/api/broker-stats').then(r => r.json());
const box = document.getElementById('coverage');
+ const sc = d.site_coverage || {};
+ const covChip = sc.firms_with_url
+ ? `<span class="st-chip" title="${(sc.firms_crawled||0).toLocaleString()} crawled · ${(sc.firms_with_idx||0).toLocaleString()} with IDX · ${(sc.contact_rows||0).toLocaleString()} contacts"><b>sites</b><span class="n">${sc.firms_with_url.toLocaleString()}</span></span>`
+ : '';
box.innerHTML = '<span class="cov-lbl">coverage:</span>' +
`<span class="st-chip${S.state === '' ? ' active' : ''}" data-st=""><b>ALL</b><span class="n">${(d.total_brokers || 0).toLocaleString()}</span></span>` +
(d.states || []).map(s =>
`<span class="st-chip${S.state === s.state ? ' active' : ''}" data-st="${s.state}" title="${s.firms.toLocaleString()} firms"><b>${s.state}</b><span class="n">${s.brokers.toLocaleString()}</span></span>`
- ).join('');
+ ).join('') + covChip;
box.addEventListener('click', e => {
- const c = e.target.closest('.st-chip'); if (!c) return;
+ const c = e.target.closest('.st-chip'); if (!c || c.dataset.st === undefined) return;
S.state = c.dataset.st; S.offset = 0;
LS.set('usreBrk.state', S.state);
box.querySelectorAll('.st-chip').forEach(x => x.classList.toggle('active', x.dataset.st === S.state));
@@ -119,6 +139,7 @@ async function loadPage() {
document.getElementById('pgNext').disabled = S.offset + PAGE >= S.total;
if (grid) { grid.setData(rows); return; }
grid = CrcpGrid.mount({
+ onRowClick: toggleFirmDetail,
data: rows,
fields: FIELDS,
mount: '#cgMain',
@@ -139,6 +160,69 @@ async function loadPage() {
});
}
+// ---- M-B2 inline firm detail (site + IDX flag + contacts + top brokers) ----
+const esc = s => String(s == null ? '' : s).replace(/&/g, '&').replace(/</g, '<').replace(/"/g, '"');
+const firmCache = new Map();
+
+function firmDetailHtml(d) {
+ const f = d.firm, s = d.site;
+ const idxPill = s && s.has_idx_listings != null
+ ? `<span class="fd-pill ${s.has_idx_listings ? 'yes' : 'no'}">${s.has_idx_listings ? 'IDX / listing search' : 'no IDX detected'}</span>` : '';
+ const siteLine = s && s.url
+ ? `<a href="${esc(s.url)}" target="_blank" rel="noopener noreferrer">${esc(s.url)}</a>` +
+ (s.http_status ? ` <span class="fd-muted">(${esc(s.http_status)})</span>` : '') + idxPill +
+ (s.title ? `<div class="fd-muted">“${esc(s.title)}”</div>` : '')
+ : `<span class="fd-muted">${s ? 'no website found (' + esc(s.discovery_method) + ')' : 'not yet searched'}</span>`;
+ const contacts = (d.contacts || []).map(c =>
+ c.kind === 'email' ? `<a href="mailto:${esc(c.value)}">${esc(c.value)}</a>` :
+ c.kind === 'phone' ? `<a href="tel:${esc(c.value)}">${esc(c.value)}</a>` :
+ `<a href="${esc(c.value)}" target="_blank" rel="noopener noreferrer">contact page</a>`
+ ).join(' · ') || '<span class="fd-muted">none extracted</span>';
+ const brokers = (d.brokers || []).map(b =>
+ `<span>${esc(b.name)}${b.city ? ' · ' + esc(b.city) : ''}</span>`).join('') || '<span class="fd-muted">none</span>';
+ return `<div class="usre-fd">
+ <h3>${esc(f.name)} <span class="fd-pill">${esc(f.license_state)}</span>
+ ${f.agent_count != null ? `<span class="fd-pill">${Number(f.agent_count).toLocaleString()} agents</span>` : ''}</h3>
+ <div>${siteLine}</div>
+ <div class="fd-sec"><b>Contacts</b><div>${contacts}</div></div>
+ <div class="fd-sec"><b>Brokers</b> <span class="fd-muted">(first ${(d.brokers || []).length})</span>
+ <div class="fd-brokers">${brokers}</div></div>
+ </div>`;
+}
+
+async function toggleFirmDetail(row, el) {
+ if (!row || !row.firm_id || !el) return;
+ // Already open under this element? Close it.
+ const next = el.nextElementSibling;
+ if (next && next.dataset && next.dataset.fdFor === String(row.firm_id)) { next.remove(); return; }
+ document.querySelectorAll('[data-fd-for]').forEach(x => x.remove());
+ let holder;
+ if (el.tagName === 'TR') {
+ holder = document.createElement('tr');
+ holder.dataset.fdFor = row.firm_id;
+ const td = document.createElement('td');
+ td.colSpan = el.children.length;
+ td.innerHTML = '<div class="usre-fd fd-muted">loading firm…</div>';
+ holder.appendChild(td);
+ } else {
+ holder = document.createElement('div');
+ holder.dataset.fdFor = row.firm_id;
+ holder.style.gridColumn = '1 / -1';
+ holder.innerHTML = '<div class="usre-fd fd-muted">loading firm…</div>';
+ }
+ el.after(holder);
+ try {
+ let d = firmCache.get(row.firm_id);
+ if (!d) { d = await fetch('/api/firm/' + row.firm_id).then(r => r.json()); firmCache.set(row.firm_id, d); }
+ const target = holder.tagName === 'TR' ? holder.firstElementChild : holder;
+ if (d.error) target.innerHTML = `<div class="usre-fd fd-muted">${esc(d.error)}</div>`;
+ else target.innerHTML = firmDetailHtml(d);
+ } catch (e) {
+ const target = holder.tagName === 'TR' ? holder.firstElementChild : holder;
+ target.innerHTML = '<div class="usre-fd fd-muted">failed to load firm detail</div>';
+ }
+}
+
const deb = (fn, ms) => { let t; return (...a) => { clearTimeout(t); t = setTimeout(() => fn(...a), ms); }; };
document.getElementById('srvQ').addEventListener('input', deb(e => { S.q = e.target.value.trim(); S.offset = 0; loadPage(); }, 350));
document.getElementById('srvFirm').addEventListener('input', deb(e => { S.firm = e.target.value.trim(); S.offset = 0; loadPage(); }, 350));
diff --git a/public/crcp-grid.js b/public/crcp-grid.js
index 38cae2a..a303a90 100644
--- a/public/crcp-grid.js
+++ b/public/crcp-grid.js
@@ -232,9 +232,9 @@
}
if (O.onRowClick && !e.target.closest('a')) {
const tr = e.target.closest('tr[data-ri]');
- if (tr) { O.onRowClick(lastShown[+tr.dataset.ri]); return; }
+ if (tr) { O.onRowClick(lastShown[+tr.dataset.ri], tr); return; }
const card = e.target.closest('.cg-card[data-ri]');
- if (card) O.onRowClick(lastShown[+card.dataset.ri]);
+ if (card) O.onRowClick(lastShown[+card.dataset.ri], card);
}
});
}
diff --git a/src/crawl/firm_front_page.ts b/src/crawl/firm_front_page.ts
new file mode 100644
index 0000000..9752364
--- /dev/null
+++ b/src/crawl/firm_front_page.ts
@@ -0,0 +1,216 @@
+/**
+ * Firm front-page crawler — Playwright chromium, concurrency 5.
+ *
+ * For firm_site rows with a url and crawl_status IS NULL: check robots.txt
+ * (skip domains that disallow all for *), load the homepage (15s timeout),
+ * record http_status + <title> + IDX listing-search heuristic + screenshot,
+ * and extract mailto:/tel:/contact-page links into firm_contacts.
+ *
+ * Run: npm run crawl:firms # default 100
+ * tsx src/crawl/firm_front_page.ts -- --limit=250
+ *
+ * $0 — local Playwright, public homepages only.
+ */
+import 'dotenv/config';
+import { mkdirSync } from 'node:fs';
+import { join } from 'node:path';
+import { chromium, type Browser } from 'playwright';
+import { pool, query } from '../../db/pool.ts';
+
+const ROOT = new URL('../..', import.meta.url).pathname;
+const SCREEN_DIR = join(ROOT, 'data', 'screenshots');
+mkdirSync(SCREEN_DIR, { recursive: true });
+
+const CONCURRENCY = parseInt(process.env.CRAWL_CONCURRENCY || '5', 10);
+const TIMEOUT_MS = parseInt(process.env.CRAWL_TIMEOUT_MS || '15000', 10);
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36 usrealestate/0.1 (front-page-audit; +local)';
+
+interface SiteRow { site_id: number; firm_id: number; url: string }
+
+const robotsCache = new Map<string, boolean>(); // host -> allowed
+
+async function robotsAllows(url: string): Promise<boolean> {
+ let h: string;
+ try { h = new URL(url).hostname; } catch { return false; }
+ const cached = robotsCache.get(h);
+ if (cached !== undefined) return cached;
+ let allowed = true;
+ try {
+ const res = await fetch(`https://${h}/robots.txt`, {
+ headers: { 'User-Agent': UA },
+ signal: AbortSignal.timeout(8000),
+ redirect: 'follow',
+ });
+ if (res.ok) {
+ const txt = await res.text();
+ // Only honor the wildcard group; we just need "is the homepage off-limits".
+ const lines = txt.split(/\r?\n/).map(l => l.replace(/#.*$/, '').trim());
+ let inStar = false;
+ for (const line of lines) {
+ const ua = line.match(/^user-agent:\s*(.+)$/i);
+ if (ua) { inStar = ua[1].trim() === '*'; continue; }
+ if (!inStar) continue;
+ const dis = line.match(/^disallow:\s*(.*)$/i);
+ if (dis && dis[1].trim() === '/') { allowed = false; break; }
+ }
+ }
+ } catch {} // unreachable robots.txt -> assume allowed (public homepage)
+ robotsCache.set(h, allowed);
+ return allowed;
+}
+
+interface Capture {
+ httpStatus: number | null;
+ title: string | null;
+ hasIdx: boolean | null;
+ screenshotPath: string | null;
+ emails: string[];
+ phones: string[];
+ contactUrls: string[];
+ finalUrl: string | null;
+ error: string | null;
+}
+
+async function captureOne(browser: Browser, s: SiteRow): Promise<Capture> {
+ const out: Capture = { httpStatus: null, title: null, hasIdx: null, screenshotPath: null, emails: [], phones: [], contactUrls: [], finalUrl: null, error: null };
+ const ctx = await browser.newContext({
+ ignoreHTTPSErrors: true,
+ userAgent: UA,
+ viewport: { width: 1280, height: 900 },
+ });
+ const page = await ctx.newPage();
+ try {
+ const resp = await page.goto(s.url, { waitUntil: 'domcontentloaded', timeout: TIMEOUT_MS });
+ if (!resp) { out.error = 'no response'; return out; }
+ await page.waitForTimeout(1500); // let lazy-rendered nav/search widgets appear
+ out.httpStatus = resp.status();
+ out.finalUrl = page.url();
+ out.title = ((await page.title()) || '').trim().slice(0, 500) || null;
+
+ // Passed as a string so tsx doesn't inject __name() helpers into the page.
+ const meta: any = await page.evaluate(`(function(){
+ var text = (document.body && document.body.innerText || '').slice(0, 40000);
+ var idxText = /search homes|search properties|property search|properties for sale|homes for sale|view listings|our listings|all listings|featured listings|find your (dream )?home|idx/i.test(text);
+ var links = Array.prototype.slice.call(document.querySelectorAll('a[href]'));
+ var emails = [], phones = [], contacts = [];
+ var idxHref = false;
+ for (var i = 0; i < links.length; i++) {
+ var href = links[i].getAttribute('href') || '';
+ if (/^mailto:/i.test(href)) {
+ var em = href.replace(/^mailto:/i, '').split('?')[0].trim().toLowerCase();
+ if (em && emails.indexOf(em) < 0) emails.push(em);
+ } else if (/^tel:/i.test(href)) {
+ var ph = href.replace(/^tel:/i, '').split('?')[0].trim();
+ if (ph && phones.indexOf(ph) < 0) phones.push(ph);
+ } else {
+ try {
+ var u = new URL(links[i].href, location.href);
+ if (/\\/(contact|contact-us|contactus)\\b/i.test(u.pathname) && u.hostname === location.hostname) {
+ if (contacts.indexOf(u.href) < 0) contacts.push(u.href);
+ }
+ if (/idx|property-search|propertysearch|search.*(home|propert|listing)|listings?\\b|homes-for-sale/i.test(u.pathname + u.search)) idxHref = true;
+ } catch (e) {}
+ }
+ }
+ var idxWidget = !!document.querySelector('[class*="idx" i], [id*="idx" i], [class*="property-search" i], input[placeholder*="search" i][placeholder*="address" i], input[placeholder*="city" i]');
+ return { hasIdx: !!(idxText || idxHref || idxWidget),
+ emails: emails.slice(0, 10), phones: phones.slice(0, 10), contacts: contacts.slice(0, 3) };
+ })()`);
+
+ out.hasIdx = meta.hasIdx;
+ out.emails = meta.emails;
+ out.phones = meta.phones;
+ out.contactUrls = meta.contacts;
+
+ const shotName = `firm-${s.firm_id}.jpg`;
+ await page.screenshot({ path: join(SCREEN_DIR, shotName), type: 'jpeg', quality: 70, fullPage: false });
+ out.screenshotPath = `screenshots/${shotName}`;
+ } catch (e: any) {
+ out.error = (e?.message || String(e)).slice(0, 300);
+ } finally {
+ await ctx.close().catch(() => {});
+ }
+ return out;
+}
+
+async function persist(s: SiteRow, c: Capture) {
+ const status = c.error ? 'error:' + c.error.slice(0, 120) : 'ok';
+ await query(
+ `UPDATE firm_site SET http_status = $2, title = $3, has_idx_listings = $4,
+ screenshot_path = $5, crawl_status = $6, crawled_at = NOW()
+ WHERE id = $1`,
+ [s.site_id, c.httpStatus, c.title, c.hasIdx, c.screenshotPath, status],
+ );
+ const src = c.finalUrl || s.url;
+ const rows: [string, string][] = [
+ ...c.emails.map(v => ['email', v] as [string, string]),
+ ...c.phones.map(v => ['phone', v] as [string, string]),
+ ...c.contactUrls.map(v => ['contact_url', v] as [string, string]),
+ ];
+ for (const [kind, value] of rows) {
+ await query(
+ `INSERT INTO firm_contacts (firm_id, kind, value, source_url) VALUES ($1, $2, $3, $4)
+ ON CONFLICT (firm_id, kind, value) DO NOTHING`,
+ [s.firm_id, kind, value.slice(0, 500), src.slice(0, 600)],
+ );
+ }
+ return rows.length;
+}
+
+async function main() {
+ const argLimit = process.argv.find(a => a.startsWith('--limit='));
+ const limit = argLimit ? parseInt(argLimit.split('=')[1], 10) : 100;
+
+ const run = await query<{ id: number }>(
+ `INSERT INTO ingest_runs (source, notes) VALUES ('firm_crawl', $1) RETURNING id`,
+ [`front-page crawl, batch limit ${limit}, concurrency ${CONCURRENCY}`],
+ );
+ const runId = run.rows[0].id;
+
+ const r = await query<SiteRow>(`
+ SELECT s.id AS site_id, s.firm_id, s.url
+ FROM firm_site s JOIN firm f ON f.id = s.firm_id
+ WHERE s.url IS NOT NULL AND s.crawl_status IS NULL
+ ORDER BY f.agent_count DESC NULLS LAST, s.id
+ LIMIT $1
+ `, [limit]);
+ const queue = r.rows.slice();
+ console.log(`[crawl] run #${runId} · ${queue.length} sites · concurrency ${CONCURRENCY} · timeout ${TIMEOUT_MS}ms`);
+
+ const browser = await chromium.launch({ headless: true });
+ let done = 0, ok = 0, fail = 0, robots = 0, contacts = 0, idx = 0;
+
+ await Promise.all(Array.from({ length: CONCURRENCY }).map(async () => {
+ while (queue.length) {
+ const s = queue.shift()!;
+ if (!(await robotsAllows(s.url))) {
+ robots++;
+ await query(`UPDATE firm_site SET crawl_status = 'robots_disallow', crawled_at = NOW() WHERE id = $1`, [s.site_id]);
+ console.log(`[crawl] ⛔ robots disallow: ${s.url}`);
+ continue;
+ }
+ const c = await captureOne(browser, s);
+ contacts += await persist(s, c);
+ done++;
+ if (c.error) fail++; else { ok++; if (c.hasIdx) idx++; }
+ if (done % 10 === 0 || !queue.length) {
+ console.log(`[crawl] ${done} done · ok=${ok} fail=${fail} robots=${robots} idx=${idx} contacts=${contacts} · last: ${s.url} ${c.error ? '⚠ ' + c.error.slice(0, 60) : '(' + c.httpStatus + ')'}`);
+ }
+ }
+ }));
+
+ await browser.close();
+ await query(
+ `UPDATE ingest_runs SET finished_at = NOW(), status = 'ok', rows_upserted = $2, rows_skipped = $3,
+ notes = notes || $4 WHERE id = $1`,
+ [runId, ok, fail + robots, ` · ${ok} ok, ${fail} errors, ${robots} robots-skipped, ${idx} idx, ${contacts} contacts`],
+ );
+ console.log(`[crawl] done · ${ok} ok · ${fail} errors · ${robots} robots-skipped · ${idx} with IDX · ${contacts} contact rows`);
+ await pool.end();
+}
+
+main().catch(async (e) => {
+ console.error('[crawl]', e);
+ try { await pool.end(); } catch {}
+ process.exit(1);
+});
diff --git a/src/enrich/firm_website_discovery.ts b/src/enrich/firm_website_discovery.ts
new file mode 100644
index 0000000..75fcb2e
--- /dev/null
+++ b/src/enrich/firm_website_discovery.ts
@@ -0,0 +1,195 @@
+/**
+ * Firm website discovery via DuckDuckGo HTML search (free, no API key).
+ *
+ * For firms with no firm_site row yet (biggest by agent_count first), query
+ * html.duckduckgo.com for "<name> <city> <state> real estate", take the first
+ * organic result whose domain isn't an aggregator (prefer one whose domain
+ * contains a firm-name token), and store it in firm_site (discovery_method='ddg').
+ * Misses are recorded too (url NULL, crawl_status='no_url') so reruns advance
+ * down the list instead of re-querying the same firms.
+ *
+ * Run: npm run discover:firms # default 200
+ * tsx src/enrich/firm_website_discovery.ts -- --limit=500
+ *
+ * Polite: 2.5-4s jittered gap per query (1-2s trips DDG anomaly detection), 10s
+ * timeout, custom UA, escalating backoff + abort on persistent throttle. $0.
+ */
+import 'dotenv/config';
+import { pool, query } from '../../db/pool.ts';
+
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36 usrealestate/0.1 (firm-website-discovery; +local)';
+const TIMEOUT_MS = 10_000;
+
+const BLOCK = new Set([
+ // Listing aggregators / portals
+ 'zillow.com', 'realtor.com', 'redfin.com', 'homes.com', 'trulia.com',
+ 'apartments.com', 'homesnap.com', 'movoto.com', 'estately.com',
+ 'homelight.com', 'realtytrac.com', 'auction.com', 'point2homes.com',
+ 'coldwellbankerhomes.com', // brand-level portal, not the local firm's site
+ 'har.com', 'onekeymls.com', 'streeteasy.com', 'homefinder.com',
+ 'landwatch.com', 'land.com', 'loopnet.com', 'crexi.com',
+ // Social
+ 'facebook.com', 'instagram.com', 'linkedin.com', 'twitter.com', 'x.com',
+ 'youtube.com', 'tiktok.com', 'pinterest.com', 'reddit.com',
+ // Reviews / directories / data brokers
+ 'yelp.com', 'bbb.org', 'yellowpages.com', 'whitepages.com', 'manta.com',
+ 'bizapedia.com', 'opencorporates.com', 'dnb.com', 'zoominfo.com',
+ 'crunchbase.com', 'glassdoor.com', 'indeed.com', 'mapquest.com',
+ 'wikipedia.org', 'wikidata.org', 'google.com', 'duckduckgo.com',
+ 'ratemyagent.com', 'fastexpert.com', 'realtyrates.com',
+]);
+
+function host(u: string): string | null {
+ try { return new URL(u).hostname.replace(/^www\./, '').toLowerCase(); } catch { return null; }
+}
+
+function blocked(h: string): boolean {
+ for (const root of BLOCK) if (h === root || h.endsWith('.' + root)) return true;
+ if (h.endsWith('.gov')) return true;
+ return false;
+}
+
+const STOP = new Set(['the', 'and', 'for', 'inc', 'llc', 'llp', 'ltd', 'corp', 'co', 'of', 'real', 'estate', 'realty', 'group', 'company', 'properties', 'homes', 'brokerage', 'team']);
+
+function nameTokens(name: string): string[] {
+ return name.toLowerCase().replace(/[^a-z0-9 ]/g, ' ').split(/\s+/)
+ .filter(t => t.length >= 4 && !STOP.has(t));
+}
+
+function domainMatchesName(h: string, name: string): boolean {
+ const stem = h.split('.').slice(0, -1).join('').replace(/[^a-z0-9]/g, '');
+ return nameTokens(name).some(t => stem.includes(t));
+}
+
+class ThrottleError extends Error {}
+
+async function ddgSearch(q: string): Promise<string[]> {
+ const url = 'https://html.duckduckgo.com/html/?q=' + encodeURIComponent(q);
+ const res = await fetch(url, {
+ headers: { 'User-Agent': UA, 'Accept': 'text/html', 'Accept-Language': 'en-US,en;q=0.9' },
+ redirect: 'follow',
+ signal: AbortSignal.timeout(TIMEOUT_MS),
+ });
+ // DDG's bot-challenge page comes back as HTTP 202 with an "anomaly" body and
+ // zero results — must NOT be treated as a legitimate no-result miss.
+ if (res.status === 202) throw new ThrottleError('ddg 202 anomaly');
+ if (!res.ok) throw new Error(`ddg ${res.status}`);
+ const html = await res.text();
+ if (/anomaly-modal|anomaly\.js|challenge-form/i.test(html)) throw new ThrottleError('ddg anomaly page');
+ // Organic results: <a rel="nofollow" class="result__a" href="...">. Targets are
+ // usually wrapped as /l/?uddg=<encoded>. Regex parse — no cheerio dep here.
+ const out: string[] = [];
+ const tagRe = /<a[^>]+class="[^"]*result__a[^"]*"[^>]*>/g;
+ let m: RegExpExecArray | null;
+ while ((m = tagRe.exec(html)) !== null) {
+ const hrefM = m[0].match(/href="([^"]+)"/);
+ if (!hrefM) continue;
+ let abs = hrefM[1].replace(/&/g, '&');
+ try {
+ const u = new URL(abs, 'https://duckduckgo.com');
+ const uddg = u.searchParams.get('uddg');
+ if (uddg) abs = decodeURIComponent(uddg);
+ } catch {}
+ if (/^https?:\/\//i.test(abs)) out.push(abs);
+ }
+ return out.slice(0, 10);
+}
+
+function pickWinner(urls: string[], firmName: string): string | null {
+ const credible = urls.filter(u => { const h = host(u); return h && !blocked(h); });
+ if (!credible.length) return null;
+ // Prefer a result whose domain echoes the firm name; else first credible.
+ const named = credible.find(u => domainMatchesName(host(u)!, firmName));
+ const winner = named || credible[0];
+ return 'https://' + host(winner);
+}
+
+interface FirmRow { id: number; name: string; hq_city: string | null; license_state: string }
+
+async function main() {
+ const argLimit = process.argv.find(a => a.startsWith('--limit='));
+ const limit = argLimit ? parseInt(argLimit.split('=')[1], 10) : 200;
+
+ const run = await query<{ id: number }>(
+ `INSERT INTO ingest_runs (source, notes) VALUES ('firm_discovery', $1) RETURNING id`,
+ [`ddg website discovery, batch limit ${limit}`],
+ );
+ const runId = run.rows[0].id;
+
+ const r = await query<FirmRow>(`
+ SELECT f.id, f.name, f.hq_city, f.license_state
+ FROM firm f
+ WHERE f.agent_count IS NOT NULL
+ AND NOT EXISTS (SELECT 1 FROM firm_site s WHERE s.firm_id = f.id)
+ ORDER BY f.agent_count DESC, f.id
+ LIMIT $1
+ `, [limit]);
+ const queue = r.rows;
+ console.log(`[discover] run #${runId} · ${queue.length} firms · jittered 2.5-4s/query`);
+
+ let ok = 0, miss = 0, err = 0, consecThrottle = 0;
+ for (let i = 0; i < queue.length; i++) {
+ const f = queue[i];
+ const q = `${f.name} ${f.hq_city || ''} ${f.license_state} real estate`.replace(/\s+/g, ' ').trim();
+ let urls: string[] | null = null;
+ // Up to 3 attempts with escalating backoff on throttle; a firm is only
+ // recorded as a miss when DDG actually returned a results page.
+ for (let attempt = 0; attempt < 3 && urls === null; attempt++) {
+ try {
+ urls = await ddgSearch(q);
+ consecThrottle = 0;
+ } catch (e: any) {
+ if (e instanceof ThrottleError || /ddg (403|429)/.test(e.message || '')) {
+ const wait = [30_000, 90_000, 180_000][attempt];
+ console.log(` [throttle] ${e.message} — backing off ${wait / 1000}s (attempt ${attempt + 1}/3)`);
+ await new Promise(res => setTimeout(res, wait));
+ } else {
+ break; // transport error — skip this firm, don't record
+ }
+ }
+ }
+ if (urls === null) {
+ err++;
+ consecThrottle++;
+ console.log(` [${i + 1}/${queue.length}] ⚠ ${f.name.slice(0, 44)} (search failed, not recorded)`);
+ if (consecThrottle >= 3) {
+ console.log('[discover] 3 consecutive throttled firms — aborting run to avoid hammering DDG');
+ break;
+ }
+ } else {
+ const winner = pickWinner(urls, f.name);
+ if (winner) {
+ ok++;
+ await query(
+ `INSERT INTO firm_site (firm_id, url, discovery_method) VALUES ($1, $2, 'ddg')
+ ON CONFLICT (firm_id) DO NOTHING`,
+ [f.id, winner],
+ );
+ console.log(` [${i + 1}/${queue.length}] ✓ ${f.name.padEnd(44).slice(0, 44)} → ${winner}`);
+ } else {
+ miss++;
+ await query(
+ `INSERT INTO firm_site (firm_id, url, discovery_method, crawl_status) VALUES ($1, NULL, 'ddg', 'no_url')
+ ON CONFLICT (firm_id) DO NOTHING`,
+ [f.id],
+ );
+ console.log(` [${i + 1}/${queue.length}] · ${f.name.slice(0, 44)} (no credible result)`);
+ }
+ }
+ await new Promise(res => setTimeout(res, 2500 + Math.random() * 1500));
+ }
+
+ await query(
+ `UPDATE ingest_runs SET finished_at = NOW(), status = 'ok', rows_upserted = $2, rows_skipped = $3,
+ notes = notes || $4 WHERE id = $1`,
+ [runId, ok, miss, ` · ${ok} found, ${miss} no-result, ${err} search-errors`],
+ );
+ console.log(`[discover] done · ${ok} found · ${miss} no-result · ${err} errors`);
+ await pool.end();
+}
+
+main().catch(async (e) => {
+ console.error('[discover]', e);
+ try { await pool.end(); } catch {}
+ process.exit(1);
+});
diff --git a/src/server/index.ts b/src/server/index.ts
index d48c1af..71e6757 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -236,20 +236,64 @@ app.get('/api/firms', async (req, res) => {
}
});
-// per-state coverage counts for the brokers.html header bar
+// per-state coverage counts for the brokers.html header bar (+ M-B2 site coverage)
app.get('/api/broker-stats', async (_req, res) => {
try {
- const [b, f] = await Promise.all([
+ const [b, f, s] = await Promise.all([
query<{ license_state: string; n: string }>(`SELECT license_state, COUNT(*)::text AS n FROM broker GROUP BY 1 ORDER BY 1`),
query<{ license_state: string; n: string }>(`SELECT license_state, COUNT(*)::text AS n FROM firm GROUP BY 1 ORDER BY 1`),
+ query<{ discovered: string; with_url: string; crawled: string; idx: string; contacts: string }>(
+ `SELECT COUNT(*)::text AS discovered,
+ COUNT(url)::text AS with_url,
+ COUNT(*) FILTER (WHERE http_status IS NOT NULL)::text AS crawled,
+ COUNT(*) FILTER (WHERE has_idx_listings)::text AS idx,
+ (SELECT COUNT(*) FROM firm_contacts)::text AS contacts
+ FROM firm_site`),
]);
const firms: Record<string, number> = {};
for (const row of f.rows) firms[row.license_state] = Number(row.n);
const states = b.rows.map(row => ({ state: row.license_state, brokers: Number(row.n), firms: firms[row.license_state] || 0 }));
+ const sc = s.rows[0];
res.json({
states,
total_brokers: states.reduce((s, x) => s + x.brokers, 0),
total_firms: Object.values(firms).reduce((s, x) => s + x, 0),
+ site_coverage: {
+ firms_discovered: Number(sc.discovered),
+ firms_with_url: Number(sc.with_url),
+ firms_crawled: Number(sc.crawled),
+ firms_with_idx: Number(sc.idx),
+ contact_rows: Number(sc.contacts),
+ },
+ });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
+// M-B2 firm detail: firm + discovered site + contacts + its brokers (limit 50)
+app.get('/api/firm/:id', async (req, res) => {
+ try {
+ const id = Number(req.params.id);
+ if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad firm id' });
+ const [firm, site, contacts, brokers] = await Promise.all([
+ query(`SELECT id, name, website, phone, hq_city, hq_state, license_no, license_state,
+ source, agent_count, created_at
+ FROM firm WHERE id = $1`, [id]),
+ query(`SELECT url, discovery_method, discovered_at, http_status, title, has_idx_listings,
+ crawl_status, crawled_at, screenshot_path
+ FROM firm_site WHERE firm_id = $1`, [id]),
+ query(`SELECT kind, value, source_url, found_at FROM firm_contacts
+ WHERE firm_id = $1 ORDER BY kind, value`, [id]),
+ query(`SELECT id, name, license_no, license_type, license_status, city, state_code
+ FROM broker WHERE firm_id = $1 ORDER BY name LIMIT 50`, [id]),
+ ]);
+ if (!firm.rows.length) return res.status(404).json({ error: 'firm not found: ' + id });
+ res.json({
+ firm: firm.rows[0],
+ site: site.rows[0] || null,
+ contacts: contacts.rows,
+ brokers: brokers.rows,
});
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
← d8824b4 CCK#2 verified fixes: HTML-escape admin provenance fields, g
·
back to Nationalrealestate
·
M-P1 property-level detail layer: 13-section property.html + d86921e →