`scripts/ingest-gov-tx.js`

57 lines
#!/usr/bin/env node
/*
 * ingest-gov-tx.js — load ACTIVE Texas real-estate brokers + sales agents from the TEXAS
 * open-data API into gov_licensed_agent. STATEWIDE ONLY: TX law (SB510) withholds licensee
 * mailing addresses, so the public dataset has NO city/county — Dallas cannot be isolated.
 * Loaded as metro='texas' (statewide) with that caveat.
 *
 * Source: TREC via data.texas.gov dataset s7ft-44qi ("Broker and Sales Agent License Holder Info")
 *   SODA API https://data.texas.gov/resource/s7ft-44qi.json  (~321k rows; 188,932 active persons)
 * Usage: node scripts/ingest-gov-tx.js
 */
'use strict';
const { pool } = require('./db/brokers-db');
const BASE = 'https://data.texas.gov/resource/s7ft-44qi.json';
const METRO = 'texas';
const WHERE = `status='Active' AND (license_type='Sales Agent' OR license_type like '%Broker%')`;

async function page(offset, limit) {
  const url = `${BASE}?%24where=${encodeURIComponent(WHERE)}&%24limit=${limit}&%24offset=${offset}&%24order=license_number`;
  const r = await fetch(url, { headers: { 'User-Agent': 'crcp-gov-ingest' } });
  if (!r.ok) throw new Error(`SODA ${r.status}`);
  return r.json();
}

async function main() {
  const LIMIT = 2000; let offset = 0, total = 0, loaded = 0;
  for (;;) {
    const rows = await page(offset, LIMIT);
    if (!rows.length) break;
    const seen = new Set(); const vals = []; const ph = [];
    for (const r of rows) {
      const lic = (r.license_number || '').trim();
      const firm = (r.related_license_full_name || '').trim();   // supervising broker, if any
      const key = lic + '|' + firm; if (seen.has(key)) continue; seen.add(key);
      const exp = r.license_expiration_date ? r.license_expiration_date.slice(0, 10) : null;
      const b = vals.length;
      ph.push(`('TX','${METRO}',$${b+1},$${b+2},$${b+3},$${b+4},NULL,NULL,NULL,NULL,$${b+5},'tx-trec')`);
      vals.push((r.full_name || '').trim(), firm, lic, (r.license_type || '').trim(), exp);
    }
    if (ph.length) {
      await pool.query(
        `INSERT INTO gov_licensed_agent (state,metro,name,firm,license_number,license_type,city,addr,zip,county,expiration,source)
         VALUES ${ph.join(',')}
         ON CONFLICT (source,license_number,firm) DO UPDATE SET license_type=EXCLUDED.license_type, expiration=EXCLUDED.expiration, fetched_at=now()`, vals);
      loaded += ph.length;
    }
    total += rows.length; offset += LIMIT;
    process.stdout.write(`\r  fetched ${total}  loaded ${loaded}   `);
    if (rows.length < LIMIT) break;
  }
  process.stdout.write('\n');
  const { rows: [c] } = await pool.query(`SELECT count(*) n, count(*) FILTER (WHERE license_type ILIKE '%broker%') br FROM gov_licensed_agent WHERE source='tx-trec'`);
  console.log(`✔ texas (statewide): ${c.n} active licensees (${c.br} brokers). NOTE: SB510 withholds addresses, no city/Dallas filter. cost: $0`);
  await pool.end();
}
if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });