`scripts/fetch-fdic-lenders.js`

186 lines
#!/usr/bin/env node
// fetch-fdic-lenders.js — CRCP "Lenders" (banks) builder for the lending-intelligence layer.
//
// Pulls REAL public data only ($0) from the FDIC BankFind Suite API (official, unauthenticated):
//   1. /banks/locations  — every FDIC-insured bank BRANCH physically in Los Angeles County.
//   2. /banks/institutions — institution metadata (assets, deposits, class, HQ, web, est date)
//      for the distinct banks (by FDIC CERT #) that operate those LA-County branches.
//
// The deliverable (data/fdic-lenders.json) is a DIRECTORY OF LENDERS with an LA-County presence —
// the national + regional + local banks a loan officer actually works alongside — each row joined
// to its authoritative FDIC institution facts and its LA-County branch count. A companion
// data/fdic-branches.json holds the branch-level rows.
//
// Every row is source-cited: source="FDIC BankFind", source_url=official per-institution deep link,
// retrieved_at=now. No personal data — institutions + branches only.
//
// Idempotent + re-runnable. Logs real source URLs + row counts. $0, public, no key.
//
// Run: node scripts/fetch-fdic-lenders.js

const fs = require('fs');
const path = require('path');
const { execFileSync } = require('child_process');

const ROOT = path.join(__dirname, '..');
const OUT_LENDERS = path.join(ROOT, 'data', 'fdic-lenders.json');
const OUT_BRANCHES = path.join(ROOT, 'data', 'fdic-branches.json');

// ---- real public source (FDIC BankFind Suite REST API, verified reachable 2026-07-31, $0) ----
const API = 'https://api.fdic.gov/banks';
const HUMAN = 'https://banks.data.fdic.gov/bankfind-suite/bankfind'; // public human lookup
const COUNTY = 'Los Angeles';
const STATE = 'CA';

function log(...a) { console.log('[fdic-lenders]', ...a); }

// GET a URL to a string via curl (-L follows the api.fdic.gov redirect, hard timeout). Public, $0.
function get(url) {
  return execFileSync('curl', ['-sL', '--max-time', '40', url], {
    encoding: 'utf8', maxBuffer: 64 * 1024 * 1024,
  });
}

// Page through an FDIC endpoint (limit/offset) until every row is collected.
// `total` is captured from the FIRST page only; a later page occasionally omits it, and letting
// that reset the bound truncated the pull at 1000 (the LA-branch set is 1414).
function fetchAll(endpoint, filters, fields, sortField) {
  const rows = [];
  const LIMIT = 1000;
  let offset = 0, total = null;
  const page = (off) => {
    const url = `${API}/${endpoint}?filters=${encodeURIComponent(filters)}`
      + `&fields=${encodeURIComponent(fields)}`
      + `&limit=${LIMIT}&offset=${off}&sort_by=${sortField}&sort_order=ASC&format=json`;
    return JSON.parse(get(url));
  };
  do {
    let d;
    try { d = page(offset); }
    catch (e) { throw new Error(`FDIC ${endpoint} parse failed at offset ${offset}: ${e.message}`); }
    const t = (d.meta && d.meta.total) != null ? d.meta.total
            : (d.totals && d.totals.count != null ? d.totals.count : null);
    if (total == null && t != null) total = t;
    let batch = (d.data || []).map(x => x.data || x);
    if (!batch.length && total != null && rows.length < total) {
      try { batch = (page(offset).data || []).map(x => x.data || x); } catch (_) {} // one retry on transient empty
    }
    rows.push(...batch);
    log(`${endpoint}: ${rows.length}/${total != null ? total : '?'}`);
    if (!batch.length) break;
    offset += LIMIT;
  } while (total != null && rows.length < total);
  return rows;
}

function atomicWrite(file, obj) {
  fs.writeFileSync(file + '.tmp', JSON.stringify(obj));
  fs.renameSync(file + '.tmp', file);
}

function main() {
  const retrieved_at = new Date().toISOString();

  // 1) Every FDIC bank branch in LA County.
  const branchFilters = `STALP:${STATE} AND COUNTY:"${COUNTY}"`;
  const branchFields = 'NAME,CERT,UNINUM,ADDRESS,CITY,ZIP,COUNTY,STALP,SERVTYPE,ESTYMD,MAINOFF,LATITUDE,LONGITUDE';
  const rawBranches = fetchAll('locations', branchFilters, branchFields, 'NAME');
  log(`LA County branches fetched: ${rawBranches.length}`);

  // 2) Distinct institutions (by CERT) that own those branches.
  const branchCount = {};
  rawBranches.forEach(b => { if (b.CERT != null) branchCount[b.CERT] = (branchCount[b.CERT] || 0) + 1; });
  const certs = Object.keys(branchCount);
  log(`distinct lenders (CERT) with LA-County presence: ${certs.length}`);

  // Fetch institution metadata in CERT batches (OR-filter, chunked to keep the URL sane).
  const instFields = 'NAME,CERT,CITY,COUNTY,STALP,STNAME,ASSET,DEP,ESTYMD,BKCLASS,WEBADDR,OFFICES,ACTIVE,NETINC,ROA';
  const instByCert = {};
  const CHUNK = 50;
  for (let i = 0; i < certs.length; i += CHUNK) {
    const slice = certs.slice(i, i + CHUNK);
    const filt = 'CERT:(' + slice.join(' OR ') + ')';
    const url = `${API}/institutions?filters=${encodeURIComponent(filt)}`
      + `&fields=${encodeURIComponent(instFields)}&limit=1000&format=json`;
    let rows = [];
    for (let attempt = 0; attempt < 3 && !rows.length; attempt++) {
      try { rows = (JSON.parse(get(url)).data || []).map(x => x.data || x); }
      catch (e) { if (attempt === 2) throw new Error(`FDIC institutions parse failed (chunk ${i}): ${e.message}`); }
    }
    rows.forEach(r => { if (r.CERT != null) instByCert[r.CERT] = r; });
    log(`institution meta: ${Object.keys(instByCert).length}/${certs.length}`);
  }

  const BKCLASS = {
    N: 'National bank', SM: 'State member bank', NM: 'State non-member bank',
    SB: 'Savings bank', SA: 'Savings association', OI: 'Insured US branch of a foreign bank',
  };

  // Build the lenders directory: one row per institution with an LA-County branch.
  const lenders = certs.map((cert, idx) => {
    const m = instByCert[cert] || {};
    const web = m.WEBADDR ? m.WEBADDR.replace(/^https?:\/\//, '') : null;
    return {
      id: idx + 1,
      cert: Number(cert),
      name: m.NAME || (rawBranches.find(b => String(b.CERT) === String(cert)) || {}).NAME || `FDIC cert ${cert}`,
      bank_class: BKCLASS[m.BKCLASS] || m.BKCLASS || null,
      la_branches: branchCount[cert] || 0,
      total_offices: m.OFFICES != null ? Number(m.OFFICES) : null,
      total_assets_000: m.ASSET != null ? Number(m.ASSET) : null,   // $ thousands
      deposits_000: m.DEP != null ? Number(m.DEP) : null,           // $ thousands
      net_income_000: m.NETINC != null ? Number(m.NETINC) : null,
      roa: m.ROA != null ? Number(m.ROA) : null,
      hq_city: m.CITY || null,
      hq_county: m.COUNTY || null,
      hq_state: m.STALP || null,
      established: m.ESTYMD || null,
      active: m.ACTIVE === 1 || m.ACTIVE === '1',
      website: web,
      // provenance (package §16 evidence-first)
      source: 'FDIC BankFind',
      source_url: `https://banks.data.fdic.gov/bankfind-suite/bankfind/details/${cert}`,
      identifier: `FDIC CERT ${cert}`,
      retrieved_at,
    };
  }).sort((a, b) => (b.la_branches - a.la_branches) || ((b.total_assets_000 || 0) - (a.total_assets_000 || 0)));

  // Branch-level rows (business addresses only — public FDIC facts).
  const branches = rawBranches.map((b, idx) => ({
    id: idx + 1,
    cert: b.CERT != null ? Number(b.CERT) : null,
    bank: b.NAME || null,
    address: b.ADDRESS || null,
    city: b.CITY || null,
    zip: b.ZIP || null,
    county: b.COUNTY || null,
    state: b.STALP || null,
    branch_type: b.SERVTYPE || null,
    main_office: b.MAINOFF === 1 || b.MAINOFF === '1',
    established: b.ESTYMD || null,
    lat: b.LATITUDE != null ? Number(b.LATITUDE) : null,
    lng: b.LONGITUDE != null ? Number(b.LONGITUDE) : null,
    source: 'FDIC BankFind',
    source_url: b.CERT != null ? `https://banks.data.fdic.gov/bankfind-suite/bankfind/details/${b.CERT}` : HUMAN,
    retrieved_at,
  }));

  const meta = {
    source: 'FDIC BankFind Suite API',
    source_home: HUMAN,
    api: API,
    scope: `FDIC-insured banks with a branch in ${COUNTY} County, ${STATE}`,
    access_method: 'official_api',
    personal_data: false,
    retrieved_at,
  };

  atomicWrite(OUT_LENDERS, { meta: { ...meta, count: lenders.length }, lenders });
  atomicWrite(OUT_BRANCHES, { meta: { ...meta, count: branches.length }, branches });
  log(`wrote ${lenders.length} lenders → ${path.relative(ROOT, OUT_LENDERS)}`);
  log(`wrote ${branches.length} branches → ${path.relative(ROOT, OUT_BRANCHES)}`);
  log('source:', HUMAN, '($0, official FDIC public API)');
}

main();