← back to Commercialrealestate
CRCP: pass-2 fuzzy broker CA-DRE recovery (nickname+prefix, surname-search, single-person guard); fix norm-strips-comma parse bug
b1e900b26269dfa0362e96864fcce068bf43c046 · 2026-07-11 20:07:54 -0700 · Steve Abrams
Files touched
A scripts/confirm-broker-ca-dre-pass2.js
Diff
commit b1e900b26269dfa0362e96864fcce068bf43c046
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat Jul 11 20:07:54 2026 -0700
CRCP: pass-2 fuzzy broker CA-DRE recovery (nickname+prefix, surname-search, single-person guard); fix norm-strips-comma parse bug
---
scripts/confirm-broker-ca-dre-pass2.js | 120 +++++++++++++++++++++++++++++++++
1 file changed, 120 insertions(+)
diff --git a/scripts/confirm-broker-ca-dre-pass2.js b/scripts/confirm-broker-ca-dre-pass2.js
new file mode 100644
index 0000000..9a2d589
--- /dev/null
+++ b/scripts/confirm-broker-ca-dre-pass2.js
@@ -0,0 +1,120 @@
+#!/usr/bin/env node
+/*
+ * confirm-broker-ca-dre-pass2.js — SECOND PASS over the brokers pass-1 left unmatched.
+ *
+ * Pass 1 required an exact first-name match, so every NICKNAME missed (Dan≠Daniel,
+ * Ken≠Kenneth, Nick≠Nicholas...). This pass searches CA DRE by SURNAME ONLY, then matches
+ * the first name locally with nickname-expansion + prefix logic — but stays wrong-person-safe:
+ * it only confirms when exactly ONE distinct person (by name) matches the relaxed rule.
+ *
+ * Target rows : broker WHERE state IS NULL AND dre_match='unmatched' AND pass2_at IS NULL,
+ * skipping obvious company/team names and too-short surnames.
+ * On a confirmed hit : state='CA', dre_match='probable-fuzzy', dre_license=<id>.
+ * Always stamps pass2_at so the row is never re-checked. $0 local, resumable.
+ *
+ * Usage: node scripts/confirm-broker-ca-dre-pass2.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);
+
+// common US nickname -> formal first name (only the non-prefix ones; prefix cases handled separately)
+const NICK = {
+ bob: 'robert', rob: 'robert', bobby: 'robert', bill: 'william', will: 'william', billy: 'william',
+ jim: 'james', jimmy: 'james', mike: 'michael', mickey: 'michael', tom: 'thomas', tommy: 'thomas',
+ dick: 'richard', rick: 'richard', ricky: 'richard', dave: 'david', dan: 'daniel', danny: 'daniel',
+ ken: 'kenneth', kenny: 'kenneth', chris: 'christopher', chuck: 'charles', charlie: 'charles',
+ ed: 'edward', eddie: 'edward', ted: 'theodore', joe: 'joseph', joey: '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', gene: 'eugene', hank: 'henry', jack: 'john', larry: 'lawrence', marty: 'martin',
+ pete: 'peter', phil: 'phillip', ray: 'raymond', russ: 'russell', stan: 'stanley', walt: 'walter',
+ wes: 'wesley', jenny: 'jennifer', jen: 'jennifer', liz: 'elizabeth', beth: 'elizabeth', betty: 'elizabeth',
+ sue: 'susan', kathy: 'katherine', cathy: 'catherine', kate: 'katherine', katie: 'katherine',
+ patty: 'patricia', trish: 'patricia', debbie: 'deborah', deb: 'deborah', cindy: 'cynthia',
+ sandy: 'sandra', mandy: 'amanda', val: 'valerie', vicky: 'victoria', tina: 'christina',
+ angie: 'angela', terri: 'teresa', terry: 'teresa', becky: 'rebecca', peggy: 'margaret', meg: 'margaret',
+};
+const norm = s => (s || '').toLowerCase().replace(/[^a-z\- ]/g, '').replace(/\s+/g, ' ').trim();
+
+// person name -> {first, last}; strips suffixes. Returns null for non-person shapes.
+function split(raw) {
+ let 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 last = p[p.length - 1];
+ const first = p[0];
+ if (last.length < 2 || first.length < 2) return null;
+ return { first, last };
+}
+// does DRE candidate first name plausibly equal the query first name?
+function firstMatches(qFirst, cFirst) {
+ if (!qFirst || !cFirst) return false;
+ if (qFirst === cFirst) return true;
+ const a = NICK[qFirst], b = NICK[cFirst];
+ if (a && (a === cFirst || cFirst.startsWith(a))) return true; // dan -> daniel
+ if (b && (b === qFirst || qFirst.startsWith(b))) return true; // reverse
+ if (qFirst.length >= 3 && cFirst.startsWith(qFirst)) return true; // deb -> deborah
+ if (cFirst.length >= 3 && qFirst.startsWith(cFirst)) 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 pass2_at timestamptz`);
+ const { rows } = await pool.query(`
+ SELECT id, name FROM broker
+ WHERE state IS NULL AND dre_match = 'unmatched' AND pass2_at IS NULL
+ AND coalesce(name,'') <> ''
+ AND name !~* '(team|group|realty|properties|associates|& |inc|llc|corp| co\\.| and )'
+ ORDER BY id`);
+ const todo = rows.slice(0, LIMIT === Infinity ? rows.length : LIMIT);
+ console.log(`pass-1 unmatched, person-shaped: ${rows.length}${todo.length < rows.length ? ` (this run: ${todo.length})` : ''}`);
+
+ let done = 0, recovered = 0, ambiguous = 0, none = 0, skipped = 0, errors = 0;
+ for (const b of todo) {
+ const nm = split(b.name);
+ if (!nm) { await pool.query(`UPDATE broker SET pass2_at=now() WHERE id=$1`, [b.id]); skipped++; done++; continue; }
+ let state = null, dre_license = null, dre_match = null;
+ try {
+ const cands = parseSearch(await searchSurname(nm.last));
+ // candidates whose surname is exact and first name plausibly matches.
+ // c.name is "LAST, FIRST MIDDLE" — split on the RAW comma first, then norm each part
+ // (norm strips commas, so norming before the split would collapse the whole name).
+ 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);
+ });
+ const people = new Map(hits.map(h => [norm(h.name).replace(/\s+/g, ' '), h]));
+ if (people.size === 1) { const h = [...people.values()][0]; state = 'CA'; dre_license = h.license; dre_match = 'probable-fuzzy'; recovered++; }
+ else if (people.size > 1) { ambiguous++; }
+ else { none++; }
+ } 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), pass2_at=now() WHERE id=$1`,
+ [b.id, state, dre_license, dre_match]);
+ done++;
+ process.stdout.write(`\r pass2 ${done}/${todo.length} recovered ${recovered} ambiguous ${ambiguous} no-match ${none} err ${errors} `);
+ await sleep(jitter());
+ }
+ process.stdout.write('\n');
+ console.log(`✔ pass2 done. recovered(CA) ${recovered} | ambiguous ${ambiguous} | no-match ${none} | skipped ${skipped} | err ${errors} | cost: $0 (local)`);
+ await pool.end();
+}
+
+if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });
← f9af543 auto-save: 2026-07-11T18:44:33 (4 files) — data/condos-redfi
·
back to Commercialrealestate
·
CRCP: pass-3 broker CA recovery — city-disambiguation (margi 7d08d22 →