usre: Tier-1 accurate firm-phone backfill from cre office lines (TK-10687)

88312fc72d0f969510149ecc0639ad04e5c41437 · 2026-08-18 13:47:11 -0700 · Steve Abrams

Promotes the SHARED office switchboard (a canonical phone 2+ brokers at a firm use,
per the vetted cre.broker/Crexi data) into usre.firm — no web, instant, high accuracy.
Guards: canonicalize phones before counting (format variants fragmented the share
count), skip toll-free + mega-brokerages (their corporate line must come from their
own site, not one agent's record). 16 verified LA office lines matched (Major
Properties 8-broker line, Lee & Associates 5, Oppenheim Group 3). COALESCE-only,
provenance 'cre-backfill'. $0.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

A scripts/backfill-firm-phones-from-cre.mjs

Diff

commit 88312fc72d0f969510149ecc0639ad04e5c41437
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Aug 18 13:47:11 2026 -0700

    usre: Tier-1 accurate firm-phone backfill from cre office lines (TK-10687)
    
    Promotes the SHARED office switchboard (a canonical phone 2+ brokers at a firm use,
    per the vetted cre.broker/Crexi data) into usre.firm — no web, instant, high accuracy.
    Guards: canonicalize phones before counting (format variants fragmented the share
    count), skip toll-free + mega-brokerages (their corporate line must come from their
    own site, not one agent's record). 16 verified LA office lines matched (Major
    Properties 8-broker line, Lee & Associates 5, Oppenheim Group 3). COALESCE-only,
    provenance 'cre-backfill'. $0.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/backfill-firm-phones-from-cre.mjs | 82 +++++++++++++++++++++++++++++++
 1 file changed, 82 insertions(+)

diff --git a/scripts/backfill-firm-phones-from-cre.mjs b/scripts/backfill-firm-phones-from-cre.mjs
new file mode 100644
index 0000000..23324e4
--- /dev/null
+++ b/scripts/backfill-firm-phones-from-cre.mjs
@@ -0,0 +1,82 @@
+#!/usr/bin/env node
+// backfill-firm-phones-from-cre.mjs — TK-10687 Tier-1 (accurate, instant, $0, no web).
+//
+// The commercialrealestate `cre` DB already holds vetted broker contact data (from Crexi/Redfin).
+// The strongest FIRM-level signal there: a phone SHARED by 2+ brokers at the same firm = the office
+// switchboard (not a personal cell). We aggregate that office line per firm and promote it into the
+// usre.firm registry (COALESCE-only, provenance 'cre-backfill'), so commercial firms — exactly the
+// ones RENTV / CRCP care about — get an accurate main line without any web scraping.
+//
+// Usage: node scripts/backfill-firm-phones-from-cre.mjs            (dry-run)
+//        node scripts/backfill-firm-phones-from-cre.mjs --apply    (write to usre.firm)
+'use strict';
+import pg from 'pg';
+
+const USRE = process.env.DATABASE_URL || 'postgresql:///usre?host=/tmp';
+const CRE = process.env.CRE_DATABASE_URL || 'postgresql:///cre?host=/tmp';
+const APPLY = process.argv.includes('--apply');
+const _mi = process.argv.indexOf('--min-share');
+const MIN_SHARE = _mi > -1 ? Number(process.argv[_mi + 1]) : 2;   // ≥N brokers on the number = office line
+// Mega-brokerages: their corporate line must come from their OWN site (web tier), never from one
+// agent's cre record — else thousands of their brokers would show a single agent's number.
+const MEGA = /(exp realty|compass|coldwell|keller williams|re\/?max|berkshire|century 21|sotheby|douglas elliman|redfin|opendoor|weichert|realty one|ehome)/i;
+
+const canon = p => { const d = String(p || '').replace(/\D/g, '').replace(/^1(?=\d{10}$)/, ''); return d.length === 10 ? `(${d.slice(0,3)}) ${d.slice(3,6)}-${d.slice(6)}` : null; };
+const norm = s => String(s || '').toLowerCase().replace(/\b(inc|llc|corp|corporation|co|company|the|and|&|ltd|lp|group)\b/g, ' ').replace(/[^a-z0-9]+/g, ' ').trim();
+const TOLLFREE = /^\(?(?:800|833|844|855|866|877|888)/;
+
+async function main() {
+  const cre = new pg.Pool({ connectionString: CRE });
+  const usre = new pg.Pool({ connectionString: USRE });
+
+  // Raw (firm, broker phone) pairs — one row per broker so we can count SHARED lines after
+  // normalizing away format variants ("(310) 529-2052" vs "310-529-2052").
+  const rows = (await cre.query(`
+    SELECT f.name AS firm, b.phone
+      FROM broker b JOIN firm f ON f.id=b.firm_id
+     WHERE coalesce(nullif(b.phone,''),'')<>'' AND b.is_test=false`)).rows;
+
+  // count distinct brokers per (normalized firm, canonical phone); the office line = the shared one.
+  const counts = new Map();   // key `${normFirm}|${canonPhone}` → {firm, phone, n}
+  for (const r of rows) {
+    if (MEGA.test(r.firm)) continue;                    // mega-brokerage corporate line ≠ one agent's record
+    const p = canon(r.phone); if (!p || TOLLFREE.test(p)) continue;
+    const nf = norm(r.firm); if (!nf) continue;
+    const k = nf + '|' + p; const c = counts.get(k);
+    if (c) c.n++; else counts.set(k, { firm: r.firm, nf, phone: p, n: 1 });
+  }
+  // per firm, keep the most-shared line that clears MIN_SHARE
+  const byFirm = new Map();
+  for (const v of counts.values()) {
+    if (v.n < MIN_SHARE) continue;
+    const cur = byFirm.get(v.nf);
+    if (!cur || v.n > cur.n) byFirm.set(v.nf, v);
+  }
+  console.log(`cre office-line candidates (≥${MIN_SHARE} brokers share the canonical number, non-toll-free, non-mega): ${byFirm.size} firms`);
+
+  // match to usre.firm (CA, missing phone) by normalized name
+  const targets = (await usre.query(`SELECT id, name FROM firm WHERE license_state='CA' AND coalesce(nullif(phone,''),'')=''`)).rows;
+  const idx = new Map(); for (const t of targets) idx.set(norm(t.name), t);
+
+  let matched = 0;
+  const client = APPLY ? await usre.connect() : null;
+  for (const [k, v] of byFirm) {
+    const t = idx.get(k); if (!t) continue;
+    matched++;
+    console.log(`  ${t.name} → 📞 ${v.phone}  (${v.n} brokers share it)`);
+    if (APPLY) {
+      await client.query(
+        `UPDATE firm SET phone=COALESCE(NULLIF(phone,''),$2),
+           phone_source=CASE WHEN COALESCE(NULLIF(phone,''),'')='' THEN 'cre-backfill' ELSE phone_source END,
+           contact_attempts=contact_attempts+1, contact_enriched_at=now(),
+           phone_status=CASE WHEN COALESCE(NULLIF(phone,''),'')='' THEN 'found' ELSE phone_status END
+         WHERE id=$1`, [t.id, v.phone]);
+      await client.query(`INSERT INTO firm_contacts (firm_id,kind,value,source_url) VALUES ($1,'phone',$2,'cre-backfill')
+                            ON CONFLICT (firm_id,kind,value) DO NOTHING`, [t.id, v.phone]);
+    }
+  }
+  if (client) client.release();
+  console.log(`\nDONE: ${matched} usre CA firms matched an accurate cre office line · ${APPLY ? 'WRITTEN' : 'dry-run'} · $0`);
+  await cre.end(); await usre.end();
+}
+main().catch(e => { console.error('FATAL', e); process.exit(1); });

← e72841d usre: harden firm-phone enrichment for accuracy (name-match · back to Nationalrealestate · usre: CA-area-code guard on firm-phone enrichment (TK-10687) 4855043 →