CRCP: confirm unknown-geography brokers as CA via CA-DRE cross-ref (bridges cre.broker ↔ licensed-agents; $0 local, resumable)

482a169f86b7aacf80fd62e0062af3e10fc53266 · 2026-07-11 08:32:19 -0700 · Steve Abrams
Files touched

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

commit 482a169f86b7aacf80fd62e0062af3e10fc53266
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat Jul 11 08:32:19 2026 -0700

    CRCP: confirm unknown-geography brokers as CA via CA-DRE cross-ref (bridges cre.broker ↔ licensed-agents; $0 local, resumable)
---
 scripts/confirm-broker-ca-dre.js | 77 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 77 insertions(+)

diff --git a/scripts/confirm-broker-ca-dre.js b/scripts/confirm-broker-ca-dre.js
new file mode 100644
index 0000000..bd892b9
--- /dev/null
+++ b/scripts/confirm-broker-ca-dre.js
@@ -0,0 +1,77 @@
+#!/usr/bin/env node
+/*
+ * confirm-broker-ca-dre.js — positively confirm which unknown-geography brokers in
+ * cre.broker are CALIFORNIA by cross-referencing the CA DRE public license lookup.
+ *
+ * WHY: cre.broker (Crexi + Redfin, national) has 1,882 brokers with NO office_addr, so
+ * their state is unknown. CA DRE only licenses CA real-estate agents/brokers, so a DRE
+ * name-match is positive proof the broker is California. This turns "unknown" -> CA where
+ * confirmable; a non-match stays unknown (DRE cannot prove non-CA — could be a name
+ * variation or an out-of-state broker), and is stamped so we never re-check it.
+ *
+ * Reuses the vetted parsers from fetch-dre-licenses.js (toDreName / parseSearch / pick).
+ * Only the NAME-SEARCH step is needed (existence), so no detail GET — ~2x faster.
+ *
+ * Source : CA DRE public lookup  https://www2.dre.ca.gov/PublicASP/pplinfo.asp  ($0, no captcha)
+ * Target : cre.broker  (adds columns: state already exists; dre_license, dre_match, dre_checked_at)
+ * Resumable: processes only rows WHERE state IS NULL AND dre_checked_at IS NULL.
+ *
+ * Usage: node scripts/confirm-broker-ca-dre.js [--limit N] [--delay MS]
+ */
+'use strict';
+const { toDreName, parseSearch, pick } = 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);
+
+async function post(name) {
+  const body = new URLSearchParams({ LICENSEE_NAME: name, 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 dre_license text;
+    ALTER TABLE broker ADD COLUMN IF NOT EXISTS dre_match text;
+    ALTER TABLE broker ADD COLUMN IF NOT EXISTS dre_checked_at timestamptz;`);
+
+  const { rows } = await pool.query(
+    `SELECT id, name FROM broker
+      WHERE state IS NULL AND dre_checked_at IS NULL AND coalesce(name,'') <> ''
+      ORDER BY id`);
+  const todo = rows.slice(0, LIMIT === Infinity ? rows.length : LIMIT);
+  console.log(`unknown-geography brokers to check: ${rows.length}${todo.length < rows.length ? ` (this run: ${todo.length})` : ''}`);
+
+  let done = 0, ca = 0, verified = 0, probable = 0, still = 0, errors = 0;
+  for (const b of todo) {
+    const dreName = toDreName(b.name);
+    let dre_match = 'unmatched', dre_license = null, state = null;
+    try {
+      const cands = parseSearch(await post(dreName));
+      const { match, tier } = pick(cands, dreName);
+      if (match) { dre_match = tier; dre_license = match.license; state = 'CA'; ca++; tier === 'verified' ? verified++ : probable++; }
+      else { still++; }
+    } catch (e) { dre_match = 'error'; errors++; }
+    // state set only on a DRE hit; a miss leaves state NULL but stamps dre_checked_at (no re-check).
+    await pool.query(
+      `UPDATE broker SET state = COALESCE($2, state), dre_license = $3, dre_match = $4, dre_checked_at = now() WHERE id = $1`,
+      [b.id, state, dre_license, dre_match]);
+    done++;
+    process.stdout.write(`\r  checked ${done}/${todo.length}  CA-confirmed ${ca} (v${verified}/p${probable})  still-unknown ${still}  err ${errors}   `);
+    await sleep(jitter());
+  }
+  process.stdout.write('\n');
+  console.log(`✔ done. CA-confirmed ${ca} | still-unknown ${still} | errors ${errors} | cost: $0 (local)`);
+  await pool.end();
+}
+
+if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });
← abbed74 auto-save: 2026-07-11T06:42:06 (4 files) — data/condos-redfi · back to Commercialrealestate · CRCP: California-scope the broker DB — /api/brokers/all defa de954cf →