← back to Nationalrealestate
Add Places-API firm->website resolver (resolve:firms): reliable structured alternative to anti-bot-blocked discover; agent_count-prioritized, quota-capped, resumable over all 90k firms, fail-soft. Live-proven 90/100 resolved $0
0846541748bee1d143de281607acdcce7b88537d · 2026-07-30 19:15:27 -0700 · steve@designerwallcoverings.com
Files touched
M package.jsonA src/enrich/firm_website_resolve.ts
Diff
commit 0846541748bee1d143de281607acdcce7b88537d
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date: Thu Jul 30 19:15:27 2026 -0700
Add Places-API firm->website resolver (resolve:firms): reliable structured alternative to anti-bot-blocked discover; agent_count-prioritized, quota-capped, resumable over all 90k firms, fail-soft. Live-proven 90/100 resolved $0
---
package.json | 1 +
src/enrich/firm_website_resolve.ts | 180 +++++++++++++++++++++++++++++++++++++
2 files changed, 181 insertions(+)
diff --git a/package.json b/package.json
index 516e249..622db49 100644
--- a/package.json
+++ b/package.json
@@ -15,6 +15,7 @@
"ingest:brokers": "tsx src/ingest/brokers/engine.ts",
"ingest:listings": "tsx src/ingest/listings/engine.ts",
"discover:firms": "tsx src/enrich/firm_website_discovery.ts",
+ "resolve:firms": "tsx src/enrich/firm_website_resolve.ts",
"crawl:firms": "tsx src/crawl/firm_front_page.ts",
"derive": "tsx src/ingest/derive_metrics.ts",
"score": "tsx src/score/opportunity.ts",
diff --git a/src/enrich/firm_website_resolve.ts b/src/enrich/firm_website_resolve.ts
new file mode 100644
index 0000000..50a9711
--- /dev/null
+++ b/src/enrich/firm_website_resolve.ts
@@ -0,0 +1,180 @@
+/**
+ * Firm→website resolver via the Google Places API — the reliable, STRUCTURED
+ * alternative to the anti-bot-throttled Brave/DDG `discover` scraper.
+ *
+ * License-board firms (state DRE/TREC/DOS/DBPR/…) arrive with a name + license but
+ * NO website. Places `searchText("<name> <city> <ST>")` returns `websiteUri`
+ * directly, so this fills firm.website + firm_site without scraping any SERP HTML.
+ *
+ * RESUMABLE + ALL: each run takes the next BATCH firms still missing a site,
+ * highest agent_count first, so successive runs progressively cover ALL ~213K
+ * license-board firms. Quota-capped against the SAME places_quota monthly cap as
+ * places-seed (one Google budget) so steady-state spend stays $0 (free tier).
+ *
+ * DRY by default; PLACES_SEED_LIVE=1 (or --live) makes real calls. Fail-soft:
+ * always closes its ingest_runs row (no zombie 'running').
+ *
+ * npm run resolve:firms # dry-run: plan + est cost
+ * npm run resolve:firms -- --live # real calls, quota-capped
+ * USRE_RESOLVE_BATCH=250 npm run resolve:firms -- --live
+ */
+import 'dotenv/config';
+import { appendFileSync, mkdirSync } from 'node:fs';
+import { join } from 'node:path';
+import { homedir } from 'node:os';
+import { pool, query } from '../../db/pool.ts';
+import { openRun, closeRun } from '../ingest/run.ts';
+
+const SOURCE = 'firm_resolve';
+const API = 'https://places.googleapis.com/v1/places:searchText';
+const KEY = process.env.GOOGLE_PLACES_API_KEY || '';
+const LIVE = process.env.PLACES_SEED_LIVE === '1' || process.argv.includes('--live');
+const CAP = Number(process.env.PLACES_MONTHLY_CAP || 1500);
+const BATCH = Number(process.env.USRE_RESOLVE_BATCH || 100);
+const RATE_PER_CALL = 0.032; // Text-Search Pro list price, ledger-only (free-tier = $0)
+const FIELD_MASK = 'places.id,places.displayName,places.websiteUri';
+
+// Portal/aggregator hosts that are never a firm's OWN site.
+const BLOCK = new Set([
+ 'zillow.com', 'realtor.com', 'redfin.com', 'trulia.com', 'homes.com', 'loopnet.com',
+ 'yelp.com', 'facebook.com', 'instagram.com', 'linkedin.com', 'indeed.com', 'google.com',
+ 'apartments.com', 'mapquest.com', 'bbb.org', 'yellowpages.com', 'crexi.com',
+]);
+
+function ym(): string {
+ const d = new Date();
+ return `${d.getUTCFullYear()}-${String(d.getUTCMonth() + 1).padStart(2, '0')}`;
+}
+function hostOf(u: string): string | null {
+ try { return new URL(u).host.replace(/^www\./, '').toLowerCase(); } catch { return null; }
+}
+function nameEchoesHost(name: string, host: string): boolean {
+ const tokens = name.toLowerCase().replace(/[^a-z0-9]+/g, ' ').split(' ').filter(t => t.length >= 4);
+ const stem = host.split('.').slice(-2, -1)[0] || host;
+ return tokens.some(t => stem.includes(t) || t.includes(stem));
+}
+
+function logCost(calls: number, note: string): void {
+ try {
+ const dir = join(homedir(), '.claude');
+ mkdirSync(dir, { recursive: true });
+ const entry = {
+ ts: new Date().toISOString(), skill: 'usre-firm-resolve', provider: 'google_places',
+ units: calls, unit: 'searchText_call', rate: RATE_PER_CALL,
+ cost: 0, list_cost_if_billed: +(calls * RATE_PER_CALL).toFixed(4),
+ note: `${note} (free-tier, capped ${CAP}/mo)`,
+ };
+ appendFileSync(join(dir, 'cost-ledger.jsonl'), JSON.stringify(entry) + '\n');
+ } catch { /* ledger best-effort */ }
+}
+async function callsUsed(): Promise<number> {
+ const r = await query<{ calls_used: number }>(
+ `SELECT calls_used FROM places_quota WHERE year_month = $1`, [ym()]);
+ return r.rows[0]?.calls_used ?? 0;
+}
+async function bumpQuota(n: number): Promise<void> {
+ await query(
+ `INSERT INTO places_quota (year_month, calls_used) VALUES ($1,$2)
+ ON CONFLICT (year_month) DO UPDATE
+ SET calls_used = places_quota.calls_used + $2, updated_at = NOW()`,
+ [ym(), n]);
+}
+
+async function searchText(q: string): Promise<Array<{ name: string; website?: string }>> {
+ const res = await fetch(API, {
+ method: 'POST',
+ headers: { 'Content-Type': 'application/json', 'X-Goog-Api-Key': KEY, 'X-Goog-FieldMask': FIELD_MASK },
+ body: JSON.stringify({ textQuery: q, maxResultCount: 5 }),
+ });
+ if (!res.ok) throw new Error(`places searchText ${res.status}: ${(await res.text()).slice(0, 160)}`);
+ const j: any = await res.json();
+ return (j.places || []).map((p: any) => ({ name: p.displayName?.text || '', website: p.websiteUri }));
+}
+
+/** Best own-site pick: skip aggregators; prefer a host that echoes the firm name. */
+function pickWebsite(hits: Array<{ name: string; website?: string }>, firmName: string): string | null {
+ const cands = hits
+ .map(h => h.website).filter((u): u is string => !!u)
+ .filter(u => { const h = hostOf(u); return h && !BLOCK.has(h); });
+ if (!cands.length) return null;
+ const named = cands.find(u => { const h = hostOf(u); return h && nameEchoesHost(firmName, h); });
+ const winner = named || cands[0];
+ const h = hostOf(winner);
+ return h ? 'https://' + h : null;
+}
+
+interface FirmRow { id: number; name: string; hq_city: string | null; license_state: string | null }
+
+async function main() {
+ const used = await callsUsed();
+ const budget = Math.max(0, CAP - used);
+ const take = Math.min(BATCH, budget || BATCH);
+
+ const r = await query<FirmRow>(`
+ SELECT f.id, f.name, f.hq_city, f.license_state
+ FROM firm f
+ WHERE f.source <> 'google_places'
+ AND f.agent_count IS NOT NULL
+ AND (f.website IS NULL OR f.website = '')
+ AND NOT EXISTS (SELECT 1 FROM firm_site s WHERE s.firm_id = f.id)
+ ORDER BY f.agent_count DESC NULLS LAST, f.id
+ LIMIT $1`, [take]);
+ const queue = r.rows;
+
+ const remaining = await query<{ n: number }>(`
+ SELECT COUNT(*)::int AS n FROM firm f
+ WHERE f.source <> 'google_places' AND f.agent_count IS NOT NULL
+ AND (f.website IS NULL OR f.website = '')
+ AND NOT EXISTS (SELECT 1 FROM firm_site s WHERE s.firm_id = f.id)`);
+ console.log(`[resolve] quota ${used}/${CAP} (${budget} left) · batch ${queue.length} · ${remaining.rows[0].n} firms still unresolved · live=${LIVE}`);
+
+ if (!LIVE) {
+ for (const f of queue.slice(0, 5)) {
+ console.log(` • "${`${f.name} ${f.hq_city || ''} ${f.license_state || ''}`.replace(/\s+/g, ' ').trim()}"`);
+ }
+ if (queue.length > 5) console.log(` … +${queue.length - 5} more`);
+ console.log(`[resolve] DRY-RUN — set PLACES_SEED_LIVE=1 (or --live). ${queue.length} calls, est $0 (free-tier; $${(queue.length * RATE_PER_CALL).toFixed(2)} if billed).`);
+ await pool.end();
+ return;
+ }
+ if (!KEY) { console.error('[resolve] live but GOOGLE_PLACES_API_KEY unset'); await pool.end(); process.exit(2); }
+ if (budget <= 0) { console.log(`[resolve] monthly cap ${CAP} reached — HARD STOP, $0 spent`); await pool.end(); return; }
+
+ const runId = await openRun(SOURCE, 'places-firm-resolve');
+ let calls = 0, resolved = 0, noSite = 0, fatal: any = null;
+ try {
+ for (const f of queue) {
+ if (calls >= budget) { console.log('[resolve] cap reached mid-run — stopping'); break; }
+ const q = `${f.name} ${f.hq_city || ''} ${f.license_state || ''}`.replace(/\s+/g, ' ').trim();
+ const hits = await searchText(q);
+ calls++; await bumpQuota(1); logCost(1, `resolve "${f.name.slice(0, 40)}"`);
+ const website = pickWebsite(hits, f.name);
+ if (website) {
+ resolved++;
+ await query(`UPDATE firm SET website = COALESCE(website, $2) WHERE id = $1`, [f.id, website]);
+ await query(
+ `INSERT INTO firm_site (firm_id, url, discovery_method) VALUES ($1,$2,'google_places_resolve')
+ ON CONFLICT (firm_id) DO NOTHING`, [f.id, website]);
+ } else {
+ noSite++;
+ await query(
+ `INSERT INTO firm_site (firm_id, url, discovery_method, crawl_status)
+ VALUES ($1, NULL, 'google_places_resolve', 'no_url') ON CONFLICT (firm_id) DO NOTHING`, [f.id]);
+ }
+ await new Promise(res => setTimeout(res, 250)); // polite gap
+ }
+ } catch (e: any) {
+ fatal = e;
+ console.error(`[resolve] aborted, recording partial: ${String(e?.message || e).slice(0, 120)}`);
+ }
+ // Fail-soft: always close the run (never leave a zombie 'running').
+ const status = calls > 0 ? 'ok' : 'failed';
+ await closeRun(runId, status, {
+ upserted: resolved, skipped: noSite,
+ notes: `${calls} Places calls, ${resolved} sites resolved, ${noSite} no-site${fatal ? ` · partial(${String(fatal?.message || fatal).slice(0, 40)})` : ''}`,
+ });
+ console.log(`[resolve] done: ${calls} calls · ${resolved} sites resolved · ${noSite} no-site · $0 (free-tier)${fatal ? ' (partial)' : ''}`);
+ await pool.end();
+}
+
+main().catch(async e => { console.error('[resolve] FATAL:', e); try { await pool.end(); } catch {} process.exit(1); });
← b569855 discover: mark run 'failed' when every firm errored (ok iff
·
back to Nationalrealestate
·
resolver: confidence-tier matches — only set firm.website wh 7241fde →