← back to Rentv Licensed Targets
scrapers/ca_doi_title.mjs
329 lines
#!/usr/bin/env node
// ============================================================================
// CA Dept of Insurance (CDI) — Title-insurance registry scraper → CSV
// ----------------------------------------------------------------------------
// Authorized public-registry scrape for the RENTV CRE directory (TK-10310 /
// TK-10076 A2). Drives the CDI Oracle-APEX public license-lookup app with a
// real headless-Chromium browser (Playwright) because the app is stateful and
// checksum-protected — plain fetch can't produce valid session/cs tokens.
//
// TWO universes are harvested (both live under app 144):
// 1. Lines of Insurance Search (f?p=144:10) → Line of Insurance = "Title"
// → the licensed TITLE INSURERS (~18).
// 2. Other Insurance Entities (f?p=144:20) → "Underwritten Title Companies"
// → the licensed UTCs (~99, paginated 50/page).
//
// For every entity we open its checksum-signed Company Profile detail page
// (in-session — a hand-built P6 URL is rejected with "Session state protection
// violation") and read the real mailing address, CA license #, status, and
// domicile state exposed there.
//
// HONESTY (non-negotiable): every field is copied verbatim from the CDI page.
// The site exposes NO phone and NO website for these entities → those columns
// are ALWAYS EMPTY (blank by source, not by bug). County is derived from the
// address ZIP only when the ZIP is a known SoCal band; otherwise left blank —
// never invented. No fabricated license numbers, no guessed contacts, no
// hallucinated companies. Only entities the CDI lookup actually returns.
//
// Polite: headless, ONE browser session, ~1s pause between detail hits.
// Output CSV columns match dw_unified.rentv_licensed_targets.
// $0 (local Playwright, no paid API). Writes CSV only — NO database writes.
// ============================================================================
import { chromium } from 'playwright';
import { writeFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, resolve } from 'node:path';
const __dir = dirname(fileURLToPath(import.meta.url));
const OUT = resolve(__dir, '..', 'data', 'ca_doi_title.csv');
const BASE = 'https://interactive.web.insurance.ca.gov/apex_extprd';
const P10 = BASE + '/f?p=144:10'; // Lines of Insurance Search
const P20 = BASE + '/f?p=144:20'; // Other Insurance Entities
const sleep = ms => new Promise(r => setTimeout(r, ms));
const POLITE_MS = 1000; // ~1 req/s between detail-page hits
// --- market / geo derivation (per TK-10310 spec) ---------------------------
function market(zip) {
const z = parseInt(zip, 10);
if (!z) return 'CA (statewide)';
if (z >= 90001 && z <= 91899) return 'Greater LA';
if (z >= 91900 && z <= 92199) return 'San Diego';
if (z >= 92200 && z <= 92599) return 'Inland Empire';
if (z >= 92600 && z <= 92899) return 'Orange County';
if (z >= 93000 && z <= 93099) return 'Ventura';
if (z >= 90000 && z <= 96199) return 'SoCal (other)'; // other CA zips
return 'CA (statewide)'; // HQ out of CA
}
function isSoCal(zip) {
const z = parseInt(zip, 10);
return !!z && z >= 90001 && z <= 93099;
}
// --- CSV helpers -----------------------------------------------------------
const COLS = ['source','role','entity_name','contact_name','license_no','license_type',
'license_status','address','city','county','state','zip','phone','website','market',
'commercial_flag','within_300mi','source_url'];
const cell = v => '"' + String(v == null ? '' : v).replace(/"/g, '""') + '"';
// --- APEX interaction helpers ----------------------------------------------
// APEX pops a jQuery-UI "Confirm this search? / OK" dialog on submit.
async function clickConfirmOK(page) {
await sleep(1200);
try {
await page.getByRole('button', { name: /^OK$/i }).click({ timeout: 5000 });
} catch { /* no dialog present — fine */ }
await page.waitForLoadState('networkidle', { timeout: 60000 }).catch(() => {});
await sleep(1500);
}
// Set a native <select> AND fire the change event APEX listens for.
async function setSelect(page, id, label) {
await page.selectOption('#' + id, { label });
await page.evaluate(sid => {
document.getElementById(sid).dispatchEvent(new Event('change', { bubbles: true }));
}, id);
}
// Read the results Interactive-Report table on the current page.
async function readResultTable(page) {
return page.evaluate(() => {
const t = Array.from(document.querySelectorAll('table'))
.find(t => t.rows.length > 1 && /Name/i.test(t.rows[0].innerText) &&
/Company ID|EID/i.test(t.rows[0].innerText));
if (!t) return [];
const out = [];
for (let i = 1; i < t.rows.length; i++) {
const cells = Array.from(t.rows[i].cells);
const link = cells[0].querySelector('a');
out.push({
name: cells[0].innerText.trim(),
eid: (cells[1] || {}).innerText ? cells[1].innerText.trim() : '',
naic: (cells[2] || {}).innerText ? cells[2].innerText.trim() : '',
href: link ? link.getAttribute('href') : ''
});
}
return out;
});
}
// Is there an enabled "Next" pagination control?
async function hasNext(page) {
return page.evaluate(() => {
const b = Array.from(document.querySelectorAll('a,button'))
.find(a => /^\s*Next\s*$/i.test((a.innerText || '').trim()));
if (!b) return false;
return !b.classList.contains('is-disabled') && !b.disabled &&
b.getAttribute('aria-disabled') !== 'true';
});
}
// Parse the Company Profile detail page (currently loaded) into fields.
async function readDetail(page) {
return page.evaluate(() => {
const txt = document.body.innerText;
const lines = txt.split('\n').map(s => s.trim());
// labelled reference fields render as "<Label>\n<value>"
const after = (label) => {
for (let i = 0; i < lines.length - 1; i++) {
if (lines[i] === label) {
const v = lines[i + 1];
return (v && v !== '-') ? v : '';
}
}
return '';
};
// The company mailing address is the first non-empty line after the
// "Company Profile" H1 block that contains a comma+ZIP-ish pattern,
// sitting just before the "Show All" / section toggles.
let address = '';
const cpIdx = lines.map((l, i) => ({ l, i }))
.filter(o => o.l === 'Company Profile').map(o => o.i);
const startAt = cpIdx.length ? cpIdx[cpIdx.length - 1] : 0;
for (let i = startAt + 1; i < Math.min(lines.length, startAt + 12); i++) {
const l = lines[i];
if (!l || l === '-' || /^Show All$/i.test(l)) continue;
if (/,\s*[A-Z]{2}\s+\d{5}(-\d{4})?/.test(l)) { address = l; break; } // "…, ST 99999"
}
return {
address,
dba: after('DBA Name:') || (txt.match(/DBA Name:\s*([^\n]+)/) || [, ''])[1].trim(),
caNum: after('CA #'),
eid: after('Company ID (EID)'),
naic: after('NAIC'),
status: after('Status'),
categoryType: after('Category Type'),
stateName: after('State Name'),
origin: after('Origin'),
naicGroupName: after('NAIC Group Name'),
};
});
}
// Split "123 Main St, Suite 4, City, ST 99999-1234" → {street, city, state, zip}
function splitAddress(addr) {
if (!addr) return { street: '', city: '', st: '', zip: '' };
const m = addr.match(/^(.*),\s*([^,]+),\s*([A-Z]{2})\s+(\d{5})(?:-\d{4})?\s*$/);
if (!m) return { street: addr, city: '', st: '', zip: '' };
return { street: m[1].trim(), city: m[2].trim(), st: m[3].trim(), zip: m[4].trim() };
}
// ============================================================================
async function run() {
const browser = await chromium.launch({ headless: true });
const context = await browser.newContext();
context.setDefaultTimeout(60000);
const rows = [];
const seen = new Set(); // dedupe by eid|naic
const writeCsv = () => {
const header = COLS.join(',');
const body = rows.map(r => COLS.map(c => cell(r[c])).join(',')).join('\n');
writeFileSync(OUT, header + '\n' + body + '\n');
console.error(` → checkpoint: wrote ${rows.length} rows to ${OUT}`);
};
// 1. TITLE INSURERS (Lines of Insurance = Title) — the primary, reliable universe.
// List + harvest details + CHECKPOINT the CSV *before* touching the crash-prone
// UTC universe, so a later context crash can never lose these.
console.error('[list 1/2] Title insurers (f?p=144:10)…');
const lp = await context.newPage();
await lp.goto(P10, { waitUntil: 'networkidle' });
await setSelect(lp, 'P10_LINES_OF_INS', 'Title');
await setSelect(lp, 'P10_SORT_TYPE', 'Alphabetical');
await sleep(400);
await lp.click('#B451607695751751776'); // Get Companies
await clickConfirmOK(lp);
const insurers = await collectAllPages(lp);
console.error(` → ${insurers.length} title insurers listed`);
try { await lp.close(); } catch {}
console.error('[detail 1/2] Title insurers…');
await harvestDetails(context, insurers, 'Title Insurer', rows, seen);
writeCsv(); // insurers are now SAFE on disk regardless of what UTCs do
try { await browser.close(); } catch {}
// 2. UNDERWRITTEN TITLE COMPANIES — pure upside, isolated in its OWN fresh browser
// so its known context-crashing APEX pagination can't touch the insurers above.
try {
const b2 = await chromium.launch({ headless: true });
const c2 = await b2.newContext(); c2.setDefaultTimeout(60000);
const up = await c2.newPage();
console.error('[list 2/2] Underwritten Title Companies (f?p=144:20)…');
await up.goto(P20, { waitUntil: 'networkidle' });
await setSelect(up, 'P20_FILTER', 'Underwritten Title Companies');
await sleep(400);
await up.click('#B452459515237587778'); // Get Entities
await clickConfirmOK(up);
const utcs = await collectAllPages(up);
console.error(` → ${utcs.length} underwritten title companies listed`);
try { await up.close(); } catch {}
console.error('[detail 2/2] Underwritten Title Companies…');
await harvestDetails(c2, utcs, 'Underwritten Title Company', rows, seen);
writeCsv(); // rewrite with UTCs appended
try { await b2.close(); } catch {}
} catch (e) {
console.error(` ! UTC phase failed (${String(e.message).split('\n')[0]}) — CSV already holds the ${rows.length} title insurers`);
}
// ---- write CSV -----------------------------------------------------------
const header = COLS.join(',');
const body = rows.map(r => COLS.map(c => cell(r[c])).join(',')).join('\n');
writeFileSync(OUT, header + '\n' + body + '\n');
console.error(`\nWROTE ${OUT} — ${rows.length} rows`);
console.error('columns:', COLS.join(','));
return rows;
}
// Walk every result page (Next pagination) collecting listing rows.
async function collectAllPages(page) {
const all = [];
const seenNames = new Set();
let pageNo = 0;
while (true) {
pageNo++;
const listed = await readResultTable(page);
for (const l of listed) {
const key = l.name + '|' + l.eid;
if (!seenNames.has(key)) { seenNames.add(key); all.push(l); }
}
if (!(await hasNext(page)) || pageNo > 20) break;
await page.getByRole('link', { name: /^Next$/i }).first().click()
.catch(async () => { await page.click('button:has-text("Next")').catch(() => {}); });
await page.waitForLoadState('networkidle', { timeout: 60000 }).catch(() => {});
await sleep(1500);
}
return all;
}
// For each listing row, open its checksum-signed detail page (same session,
// so the &cs= token is valid), parse fields, push a normalized row. Uses its
// own page inside the shared context and reopens it if the page dies, so a
// single crash never aborts the whole run.
async function harvestDetails(context, listings, licenseType, rows, seen) {
let page = await context.newPage();
page.setDefaultTimeout(60000);
for (let i = 0; i < listings.length; i++) {
const item = listings[i];
const key = (item.eid || item.name) + '|' + (item.naic || '');
if (seen.has(key)) continue;
seen.add(key);
// Navigate to the checksum-signed detail page. The href is relative to
// /apex_extprd/ and carries a valid &cs= for THIS session.
const url = BASE + '/' + item.href;
let detail = {};
try {
if (page.isClosed()) { page = await context.newPage(); page.setDefaultTimeout(60000); }
await page.goto(url, { waitUntil: 'networkidle', timeout: 60000 });
await sleep(600);
const bodyTxt = await page.evaluate(() => document.body.innerText.slice(0, 200));
if (/Session state protection|error has occurred/i.test(bodyTxt)) {
console.error(` ! detail rejected for ${item.name} — recording listing-only`);
detail = {};
} else {
detail = await readDetail(page);
}
} catch (e) {
console.error(` ! detail fetch failed for ${item.name}: ${e.message.slice(0, 60)}`);
detail = {};
try { if (page.isClosed()) { page = await context.newPage(); page.setDefaultTimeout(60000); } } catch {}
}
const a = splitAddress(detail.address || '');
const zip = a.zip;
const st = a.st || (detail.stateName ? usStateAbbr(detail.stateName) : '') || '';
rows.push({
source: 'ca_doi_title',
role: 'Title',
entity_name: item.name,
contact_name: '', // registry lists no personal contact for these entities
license_no: detail.caNum || '', // CA license # from the profile; blank if not shown
license_type: licenseType, // "Title Insurer" | "Underwritten Title Company"
license_status: detail.status || '',
address: a.street || '',
city: a.city || '',
county: '', // CDI gives no county; not inventing one
state: 'CA', // CA registry role; domicile kept in raw notes
zip: zip || '',
phone: '', // NOT exposed by source
website: '', // NOT exposed by source
market: market(zip),
commercial_flag: 'true', // title entities serve CRE
within_300mi: isSoCal(zip) ? 'true' : 'false',
source_url: url,
});
console.error(` [${i + 1}/${listings.length}] ${item.name} — ${a.city || detail.stateName || '?'} ${zip || ''} ${detail.caNum ? '('+detail.caNum+')' : ''}`);
await sleep(POLITE_MS);
}
try { if (!page.isClosed()) await page.close(); } catch {}
}
// Minimal US state-name → abbr (only needed as a fallback; address parse is primary).
function usStateAbbr(name) {
const M = { California:'CA', Oklahoma:'OK', 'New York':'NY', Florida:'FL', Texas:'TX',
Nebraska:'NE', Ohio:'OH', Pennsylvania:'PA', Illinois:'IL', Arizona:'AZ' };
return M[name] || '';
}
run().catch(e => { console.error('FATAL:', e); process.exit(1); });