← back to Commercialrealestate
scripts/fetch-dre-licenses.js
227 lines
#!/usr/bin/env node
/*
* fetch-dre-licenses.js — obtain a REAL California DRE license for every CRCP
* real-estate agent/broker from PUBLIC GOVERNMENT DATA ($0, no captcha, no paid API).
*
* Source: CA Dept. of Real Estate public license lookup
* https://www2.dre.ca.gov/PublicASP/pplinfo.asp (classic ASP form)
*
* Two-step pipeline (both plain HTTP, $0 local):
* 1) NAME SEARCH POST pplinfo.asp?start=1 LICENSEE_NAME="Last, First"
* -> result rows: <a href="pplinfo.asp?License_id=NNNNNNNN">NNN</a>
* <td> Name </td><td>Type</td><td>City</td>
* 2) DETAIL GET pplinfo.asp?License_id=NNNNNNNN
* -> License Type / Name / License ID / Expiration Date / License Status /
* Salesperson|Broker License Issued / Responsible Broker (name+id) / Comment
*
* Input : data/redfin-agents.json (agent NAMES + brokerage; no license #s persisted)
* Output: data/licensed-agents.json (snapshot the /api/agents/licensed route serves)
* Cache : data/dre-cache.json (per-name, resumable — safe to Ctrl-C and re-run)
*
* Honest labeling: unmatched / ambiguous / multi-match are recorded as such,
* never fabricated. Only records confirmed against DRE carry dre_verified=true.
*
* Usage: node scripts/fetch-dre-licenses.js [--limit N] [--delay MS] [--force]
* --limit N cap agents processed this run (bounded proof runs)
* --delay MS polite delay between requests (default 1200ms + jitter)
* --force ignore cache, re-fetch
*/
'use strict';
const fs = require('fs');
const path = require('path');
const ROOT = path.join(__dirname, '..');
const IN = path.join(ROOT, 'data', 'redfin-agents.json');
const OUT = path.join(ROOT, 'data', 'licensed-agents.json');
const CACHE = path.join(ROOT, 'data', 'dre-cache.json');
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 FORCE = process.argv.includes('--force');
const sleep = ms => new Promise(r => setTimeout(r, ms));
const jitter = () => DELAY + Math.floor(Math.random() * 500);
const clean = s => (s || '').replace(/ /g, ' ').replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();
// "First M Last Jr" (Redfin order) -> "Last, First M" (DRE order). Strips brokerage suffixes.
function toDreName(raw) {
let n = (raw || '').replace(/\s+/g, ' ').trim();
if (!n || n.includes(',')) return n; // already Last, First
n = n.replace(/\b(dre|lic|license)\s*#?\s*\d+/ig, '').trim();
const parts = n.split(' ').filter(Boolean);
if (parts.length < 2) return n;
const suffix = /^(jr|sr|ii|iii|iv)\.?$/i;
let last = parts.pop();
if (suffix.test(last) && parts.length >= 2) last = parts.pop(); // keep real surname before Jr/Sr
return `${last}, ${parts.join(' ')}`;
}
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 getDetail(id) {
const r = await fetch(`${BASE}?License_id=${encodeURIComponent(id)}`, { headers: { 'User-Agent': UA } });
return r.ok ? r.text() : '';
}
// Parse the name-search results table into candidate rows.
function parseSearch(html) {
const out = [];
const re = /<a[^>]*href="pplinfo\.asp\?License_id=(\d+)"[^>]*>[^<]*<\/a>\s*<\/td>\s*<td>([^<]*)<\/td>\s*<td>([^<]*)<\/td>\s*<td>([^<]*)<\/td>/gi;
let m;
while ((m = re.exec(html))) {
out.push({ license: m[1], name: clean(m[2]), type: clean(m[3]), city: clean(m[4]) });
}
return out;
}
// Parse the license-detail page into the fields Steve wants.
// The page renders each field as <strong>Label:</strong> ... </td><td><FONT>VALUE</FONT>,
// so we flatten all tags to spaces and read each label-to-next-label span.
function parseDetail(html) {
const flat = clean(html.replace(/ /g, ' '));
// value between `label` and the first of the `stops` labels (or end).
const span = (label, stops) => {
const re = new RegExp(label + '\\s*:?\\s*(.*?)\\s*(?:' + stops.map(s => s.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')).join('|') + '|$)', 'i');
const m = flat.match(re);
return m ? m[1].trim() : '';
};
const type = span('License Type', ['Name:', 'License ID', 'Expiration']);
const name = span('\\bName', ['Mailing Address', 'License ID', 'License Type', 'Expiration']);
const expiration = span('Expiration Date', ['License Status', 'Salesperson License', 'Broker License']);
const status = span('License Status', ['Salesperson License Issued', 'Broker License Issued', 'Former Name', 'Responsible Broker']);
const issued = span('Salesperson License Issued', ['Former Name', 'Responsible Broker', 'Comment'])
|| span('Broker License Issued', ['Former Name', 'Responsible Broker', 'Comment']);
const idm = flat.match(/License ID\s*:?\s*(\d{6,8})/i);
const licenseId = idm ? idm[1] : '';
// Responsible broker: first "Responsible Broker:" block -> its License ID + firm name.
let respBrokerId = '', respBrokerName = '';
const rb = flat.match(/Responsible Broker\s*:?\s*License ID\s*:?\s*(\d{6,8})\s*(.*?)\s*(?:Former Responsible Broker|From \d|Comment|$)/i);
if (rb) { respBrokerId = rb[1]; respBrokerName = rb[2].trim(); }
const discipline = /NO DISCIPLINARY ACTION/i.test(flat) ? 'none'
: (/(disciplin|revoked|suspend|barred)/i.test(flat) ? 'see-dre-record' : 'unknown');
return { detail_type: type, dre_name: name, detail_license_id: licenseId, status, expiration, issued,
responsible_broker_id: respBrokerId, responsible_broker: respBrokerName, discipline };
}
// Choose the best candidate for an agent name. Returns {match, ambiguity, tier}.
// tier drives dre_verified: ONLY a single exact full-name match is 'verified'.
// Weaker matches (>1 exact, or surname+first-name-only) are 'probable' — the
// license is still surfaced but NEVER badged verified (wrong-person guard).
function pick(cands, wantName) {
if (!cands.length) return { match: null, ambiguity: 'no-match', tier: 'unmatched' };
const norm = s => s.toLowerCase().replace(/[^a-z, ]/g, '').replace(/\s+/g, ' ').trim();
const want = norm(wantName);
const exact = cands.filter(c => norm(c.name) === want);
if (exact.length === 1) return { match: exact[0], ambiguity: 'exact', tier: 'verified' };
if (exact.length > 1) return { match: exact[0], ambiguity: `multi-exact(${exact.length})`, tier: 'probable' };
// fallthrough: exactly one candidate whose surname AND first token match the query
const [wLast, wFirst = ''] = want.split(',').map(s => s.trim());
const wFirstTok = wFirst.split(' ')[0];
const near = cands.filter(c => {
const [cLast, cFirst = ''] = norm(c.name).split(',').map(s => s.trim());
return cLast === wLast && cFirst.split(' ')[0] === wFirstTok && wFirstTok;
});
if (near.length === 1) return { match: near[0], ambiguity: 'first+last', tier: 'probable' };
return { match: null, ambiguity: `ambiguous(${cands.length})`, tier: 'unmatched' };
}
function loadCache() { try { return JSON.parse(fs.readFileSync(CACHE, 'utf8')); } catch { return {}; } }
async function main() {
const src = JSON.parse(fs.readFileSync(IN, 'utf8'));
// Unique agent names (+ a brokerage/city hint) from redfin-agents results.
const seen = new Map();
for (const r of (src.results || [])) {
if (!r.agent) continue;
const key = r.agent.trim();
if (!seen.has(key)) seen.set(key, { agent: key, brokerage: r.brokerage || '' });
}
const agents = [...seen.values()];
const cache = FORCE ? {} : loadCache();
let processed = 0, verified = 0, unmatched = 0, ambiguous = 0, fromCache = 0, errors = 0;
const results = [];
for (const a of agents) {
if (processed >= LIMIT) break;
const dreName = toDreName(a.agent);
if (cache[a.agent] && !FORCE) {
results.push(cache[a.agent]); fromCache++;
const c = cache[a.agent];
if (c.dre_verified) verified++; else if (c.status_label === 'unmatched') unmatched++; else ambiguous++;
continue;
}
processed++;
let rec = { agent: a.agent, dre_query: dreName, brokerage: a.brokerage };
try {
const html = await post(dreName);
const cands = parseSearch(html);
const { match, ambiguity, tier } = pick(cands, dreName);
if (!match) {
rec = { ...rec, dre_verified: false, dre_match: 'unmatched', status_label: 'unmatched', match_confidence: ambiguity, candidates: cands.length };
unmatched++;
} else {
await sleep(jitter());
const detail = parseDetail(await getDetail(match.license));
const isVerified = tier === 'verified'; // ONLY exact single-name match earns the badge
const live = /LICENSED/i.test(detail.status);
rec = {
...rec,
license: match.license,
license_type: match.type || detail.detail_type,
license_city: match.city,
status: detail.status,
expiration: detail.expiration,
issued: detail.issued,
responsible_broker: detail.responsible_broker,
responsible_broker_id: detail.responsible_broker_id,
discipline: detail.discipline,
match_confidence: ambiguity,
dre_match: tier, // 'verified' | 'probable'
dre_verified: isVerified,
status_label: isVerified ? (live ? 'active' : (detail.status ? detail.status.toLowerCase() : 'unknown')) : 'probable',
dre_url: `${BASE}?License_id=${match.license}`,
};
if (isVerified) verified++; else ambiguous++; // probable matches counted separately, NOT as verified
}
} catch (e) {
rec = { ...rec, dre_verified: false, status_label: 'error', error: String(e.message).split('\n')[0] };
errors++;
}
cache[a.agent] = rec;
results.push(rec);
fs.writeFileSync(CACHE, JSON.stringify(cache, null, 1)); // persist every step -> resumable
process.stdout.write(`\r processed ${processed} verified ${verified} unmatched ${unmatched} err ${errors} `);
await sleep(jitter());
}
process.stdout.write('\n');
const snapshot = {
meta: {
source: 'California Department of Real Estate — public license lookup (www2.dre.ca.gov/PublicASP/pplinfo.asp)',
note: 'Public government data. dre_verified=true ONLY for a single exact full-name DRE match. "probable" = license found but the name match was not unique (>1 exact, or first+last only) — shown but never badged verified. Unmatched agents labeled honestly, never fabricated.',
cost: '$0 (local plain-HTTP, no captcha, no paid API)',
fetched_at: new Date().toISOString(),
total_agents: agents.length,
is_proof_run: results.length < agents.length,
counts: { verified, probable: ambiguous, unmatched, errors, from_cache: fromCache, in_snapshot: results.length },
},
agents: results,
};
fs.writeFileSync(OUT, JSON.stringify(snapshot, null, 1));
console.log(`\n✔ wrote ${OUT}`);
console.log(` agents total ${agents.length} | verified ${verified} | probable ${ambiguous} | unmatched ${unmatched} | errors ${errors} | cached ${fromCache}`);
console.log(` cost: $0 (local)`);
}
module.exports = { toDreName, parseSearch, parseDetail, pick };
if (require.main === module) {
main().catch(e => { console.error('FATAL', e); process.exit(1); });
}