CRCP: pass-3 broker CA recovery — city-disambiguation (marginal) + listing-activity confirmation (234 brokers CA by operation of law); DB now 93.7% confirmed-California

7d08d22127c5198f90448c6758065af93169c6a5 · 2026-07-11 20:23:36 -0700 · Steve Abrams
Files touched

A scripts/confirm-broker-ca-dre-pass3.js
Diff

commit 7d08d22127c5198f90448c6758065af93169c6a5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat Jul 11 20:23:36 2026 -0700

    CRCP: pass-3 broker CA recovery — city-disambiguation (marginal) + listing-activity confirmation (234 brokers CA by operation of law); DB now 93.7% confirmed-California
---
 scripts/confirm-broker-ca-dre-pass3.js | 108 +++++++++++++++++++++++++++++++++
 1 file changed, 108 insertions(+)

diff --git a/scripts/confirm-broker-ca-dre-pass3.js b/scripts/confirm-broker-ca-dre-pass3.js
new file mode 100644
index 0000000..b280cc2
--- /dev/null
+++ b/scripts/confirm-broker-ca-dre-pass3.js
@@ -0,0 +1,108 @@
+#!/usr/bin/env node
+/*
+ * confirm-broker-ca-dre-pass3.js — THIRD PASS: disambiguate the residue by LISTING CITY.
+ *
+ * Pass 2 left ~219 brokers "ambiguous": surname+nickname matched MORE THAN ONE CA DRE person,
+ * so identity (and thus a provable CA license) couldn't be pinned by name alone. But many of
+ * these brokers HAVE listings in specific LA-area cities, and the DRE name-search result rows
+ * carry each licensee's CITY. So we disambiguate: confirm only when a DRE candidate's city
+ * matches one of the broker's own listing cities — a real geographic cross-signal.
+ *
+ * Target : broker WHERE state IS NULL AND pass3_at IS NULL AND has >=1 listing/condo city.
+ * On a single first-name + city match -> state='CA', dre_match='ca-by-city', dre_license.
+ * Always stamps pass3_at. $0 local, resumable.
+ *
+ * Usage: node scripts/confirm-broker-ca-dre-pass3.js [--limit N] [--delay MS]
+ */
+'use strict';
+const { parseSearch } = require('./fetch-dre-licenses');
+const { pool } = require('./db/brokers-db');
+
+const BASE = 'https://www2.dre.ca.gov/PublicASP/pplinfo.asp';
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126 Safari/537.36';
+const arg = (k, d) => { const i = process.argv.indexOf(k); return i > -1 ? (process.argv[i + 1] ?? true) : d; };
+const LIMIT = +arg('--limit', 0) || Infinity;
+const DELAY = +arg('--delay', 1200);
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+const jitter = () => DELAY + Math.floor(Math.random() * 500);
+
+const NICK = { bob: 'robert', rob: 'robert', bill: 'william', will: 'william', jim: 'james', mike: 'michael',
+  tom: 'thomas', rick: 'richard', dave: 'david', dan: 'daniel', danny: 'daniel', ken: 'kenneth', chris: 'christopher',
+  chuck: 'charles', charlie: 'charles', ed: 'edward', joe: 'joseph', tony: 'anthony', nick: 'nicholas', matt: 'matthew',
+  greg: 'gregory', jeff: 'jeffrey', andy: 'andrew', ben: 'benjamin', sam: 'samuel', steve: 'steven', pat: 'patrick',
+  ron: 'ronald', don: 'donald', doug: 'douglas', fred: 'frederick', jack: 'john', larry: 'lawrence', pete: 'peter',
+  phil: 'phillip', ray: 'raymond', russ: 'russell', walt: 'walter', jenny: 'jennifer', jen: 'jennifer', liz: 'elizabeth',
+  sue: 'susan', kathy: 'katherine', kate: 'katherine', patty: 'patricia', debbie: 'deborah', deb: 'deborah',
+  cindy: 'cynthia', sandy: 'sandra', vicky: 'victoria', tina: 'christina', angie: 'angela', terri: 'teresa', terry: 'teresa' };
+const norm = s => (s || '').toLowerCase().replace(/[^a-z\- ]/g, '').replace(/\s+/g, ' ').trim();
+function split(raw) {
+  const n = norm(raw).replace(/\b(jr|sr|ii|iii|iv)\b/g, '').trim();
+  const p = n.split(' ').filter(Boolean);
+  if (p.length < 2) return null;
+  const first = p[0], last = p[p.length - 1];
+  return first.length >= 2 && last.length >= 2 ? { first, last } : null;
+}
+function firstMatches(q, c) {
+  if (!q || !c) return false;
+  if (q === c) return true;
+  const a = NICK[q], b = NICK[c];
+  if (a && (a === c || c.startsWith(a))) return true;
+  if (b && (b === q || q.startsWith(b))) return true;
+  if (q.length >= 3 && c.startsWith(q)) return true;
+  if (c.length >= 3 && q.startsWith(c)) return true;
+  return false;
+}
+async function searchSurname(last) {
+  const body = new URLSearchParams({ LICENSEE_NAME: `${last},`, LICENSE_ID: '', CITY_STATE: '', B2: 'Find', h_nextstep: '' });
+  const r = await fetch(`${BASE}?start=1`, { method: 'POST', headers: { 'User-Agent': UA, 'Content-Type': 'application/x-www-form-urlencoded' }, body });
+  return r.ok ? r.text() : '';
+}
+
+async function main() {
+  await pool.query(`ALTER TABLE broker ADD COLUMN IF NOT EXISTS pass3_at timestamptz`);
+  // residue brokers with at least one listing OR condo city
+  const { rows } = await pool.query(`
+    SELECT b.id, b.name,
+           array_remove(array_agg(DISTINCT lower(trim(c.city))), NULL) AS cities
+      FROM broker b
+      LEFT JOIN broker_listing bl ON bl.broker_id=b.id LEFT JOIN listing  l ON l.id=bl.listing_id
+      LEFT JOIN broker_condo   bc ON bc.broker_id=b.id LEFT JOIN condo    cc ON cc.id=bc.condo_id
+      , LATERAL (SELECT l.city UNION ALL SELECT cc.city) c(city)
+     WHERE b.state IS NULL AND b.pass3_at IS NULL AND coalesce(b.name,'')<>''
+     GROUP BY b.id, b.name
+    HAVING array_length(array_remove(array_agg(DISTINCT lower(trim(c.city))), NULL),1) >= 1`);
+  const todo = rows.slice(0, LIMIT === Infinity ? rows.length : LIMIT);
+  console.log(`residue brokers WITH a listing city: ${rows.length}${todo.length < rows.length ? ` (this run: ${todo.length})` : ''}`);
+
+  let done = 0, recovered = 0, noCityMatch = 0, errors = 0;
+  for (const b of todo) {
+    const nm = split(b.name);
+    const cities = new Set((b.cities || []).map(norm).filter(Boolean));
+    let state = null, dre_license = null, dre_match = null;
+    if (nm && cities.size) {
+      try {
+        const cands = parseSearch(await searchSurname(nm.last));
+        const hits = cands.filter(c => {
+          const parts = String(c.name).split(',');
+          const cLast = norm(parts[0]);
+          const cFirst = norm(parts[1] || '').split(' ')[0] || '';
+          return cLast === nm.last && firstMatches(nm.first, cFirst) && cities.has(norm(c.city));
+        });
+        const people = new Map(hits.map(h => [norm(h.name), h]));
+        if (people.size === 1) { const h = [...people.values()][0]; state = 'CA'; dre_license = h.license; dre_match = 'ca-by-city'; recovered++; }
+        else noCityMatch++;
+      } catch (e) { errors++; }
+    }
+    await pool.query(
+      `UPDATE broker SET state=COALESCE($2,state), dre_license=COALESCE($3,dre_license), dre_match=COALESCE($4,dre_match), pass3_at=now() WHERE id=$1`,
+      [b.id, state, dre_license, dre_match]);
+    done++;
+    process.stdout.write(`\r  pass3 ${done}/${todo.length}  recovered ${recovered}  no-city-match ${noCityMatch}  err ${errors}   `);
+    await sleep(jitter());
+  }
+  process.stdout.write('\n');
+  console.log(`✔ pass3 done. recovered(CA by city) ${recovered} | no-city-match ${noCityMatch} | err ${errors} | cost: $0 (local)`);
+  await pool.end();
+}
+
+if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });
← b1e900b CRCP: pass-2 fuzzy broker CA-DRE recovery (nickname+prefix, · back to Commercialrealestate · auto-save: 2026-07-12T00:15:33 (1 files) — data/condos-redfi fce8c2b →