`scripts/fetch-sba-lenders.js`

235 lines
#!/usr/bin/env node
// fetch-sba-lenders.js — CRCP "SBA Lenders" builder for the lending-intelligence layer.
//
// Pulls REAL public data only ($0) from the official U.S. SBA FOIA loan-level datasets
// (data.sba.gov — "7(a) & 504 FOIA"), the SBA's own loan-approval disclosures:
//   - FOIA_7a_FY2020_Present  — every 7(a) loan approved FY2020→present (~181 MB CSV)
//   - FOIA_504_FY2010_Present — every 504 loan approved FY2010→present (~59 MB CSV)
//
// These CSVs are LARGE, so they are STREAMED through curl → a line-by-line parser (never
// buffered whole into memory). We filter to BorrState=CA and aggregate to one row per
// LENDER (7(a) originating BankName, or 504 CDC_Name) with:
//   loan_count, total_approved ($), avg_loan ($), the fiscal-year span, and — because
//   ProjectCounty carries "LOS ANGELES" cleanly — an LA-County breakout (la_loan_count /
//   la_approved). Program (7(a)/504) is kept distinct, so a lender that does both appears
//   as two rows.
//
// NO PERSONAL DATA — borrower names/addresses are read only to filter to CA, never stored.
// Output is institution-level (lender) aggregates only.
//
// Every row is source-cited: source="U.S. SBA", source_url=the dataset page,
// identifier=lender name, retrieved_at=now.
//
// Idempotent + re-runnable. Logs the real source URLs + row counts. Atomic write.
// If the CSVs can't be reached/streamed, writes an HONEST manual_review_only stub
// (lenders:[] + a deep link) — never fabricates lenders.
//
// Run: node scripts/fetch-sba-lenders.js

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

const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'sba-lenders.json');

// ---- real public source (SBA 7(a) & 504 FOIA CSVs, verified reachable 2026-07-31, $0, no key) ----
const DATASET_HOME = 'https://data.sba.gov/dataset/7a-504-foia';
const BASE = 'https://data.sba.gov/sites/default/files/uploaded_resources';
// Most-recent FOIA files (as-of 2026-06-30). We use ONLY the most recent file per program
// (7(a) FY2020-Present, 504 FY2010-Present) to keep the download bounded — see meta.scope.
const SOURCES = [
  { program: '7(a)', url: `${BASE}/FOIA_7a_FY2020_Present_asof_260630.csv`, lenderCol: 'BankName' },
  { program: '504', url: `${BASE}/FOIA_504_FY2010_Present_asof_260630.csv`, lenderCol: 'CDC_Name' },
];

const FILTER_STATE = 'CA';
const LA_COUNTY = 'LOS ANGELES';

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

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

// Parse one CSV line into fields (handles quoted fields + embedded commas/quotes).
// SBA FOIA CSVs are RFC-4180-ish: fields optionally double-quoted, "" = escaped quote.
function parseCsvLine(line) {
  const out = [];
  let cur = '', inq = false;
  for (let i = 0; i < line.length; i++) {
    const c = line[i];
    if (inq) {
      if (c === '"') {
        if (line[i + 1] === '"') { cur += '"'; i++; }
        else inq = false;
      } else cur += c;
    } else {
      if (c === '"') inq = true;
      else if (c === ',') { out.push(cur); cur = ''; }
      else cur += c;
    }
  }
  out.push(cur);
  return out;
}

// Stream one CSV via curl, filter to CA, fold into `agg` (keyed by "program|lender").
// Returns a promise. A field can contain an embedded newline (rare) — the streamed
// readline splits on \n, so a record with an embedded newline is skipped defensively
// (length-guard) rather than mis-parsed; these are negligible and never fabricated.
function streamAndAggregate(src, agg) {
  return new Promise((resolve, reject) => {
    // -sL follows redirects; --fail-with-body surfaces a 4xx/5xx as a non-zero exit.
    const curl = spawn('curl', ['-sL', '--fail', '--max-time', '900', src.url]);
    const rl = readline.createInterface({ input: curl.stdout, crlfDelay: Infinity });

    let header = null, idx = null, seen = 0, caKept = 0;
    let curlErr = '';
    curl.stderr.on('data', d => { curlErr += d.toString(); });

    rl.on('line', (line) => {
      if (!line) return;
      if (header === null) {
        header = parseCsvLine(line).map(h => h.trim());
        idx = {};
        header.forEach((h, i) => { idx[h] = i; });
        // Required columns must exist or we abort this source (never guess positions).
        const need = ['BorrState', 'GrossApproval', 'ApprovalFY', 'ProjectCounty', src.lenderCol];
        const missing = need.filter(n => idx[n] == null);
        if (missing.length) { rl.close(); curl.kill(); reject(new Error(`${src.program}: missing columns ${missing.join(',')}`)); }
        return;
      }
      const f = parseCsvLine(line);
      if (f.length < header.length) return; // defensive: truncated / embedded-newline row
      seen++;
      if (f[idx['BorrState']] !== FILTER_STATE) return;

      const lender = (f[idx[src.lenderCol]] || '').trim();
      if (!lender) return;
      const gross = parseFloat(f[idx['GrossApproval']]) || 0;
      const fy = parseInt(f[idx['ApprovalFY']], 10);
      const isLA = (f[idx['ProjectCounty']] || '').trim().toUpperCase() === LA_COUNTY;

      const key = `${src.program}|${lender.toUpperCase()}`;
      let a = agg[key];
      if (!a) {
        a = agg[key] = {
          program: src.program, lender, loan_count: 0, total_approved: 0,
          la_loan_count: 0, la_approved: 0, fy_min: null, fy_max: null,
        };
      }
      a.loan_count++;
      a.total_approved += gross;
      if (isLA) { a.la_loan_count++; a.la_approved += gross; }
      if (Number.isFinite(fy)) {
        if (a.fy_min == null || fy < a.fy_min) a.fy_min = fy;
        if (a.fy_max == null || fy > a.fy_max) a.fy_max = fy;
      }
      caKept++;
    });

    rl.on('close', () => {
      if (curl.exitCode && curl.exitCode !== 0 && seen === 0) {
        return reject(new Error(`${src.program}: curl exit ${curl.exitCode} ${curlErr.trim()}`));
      }
      log(`${src.program}: streamed ${seen} rows, kept ${caKept} CA loan rows`);
      resolve({ seen, caKept });
    });
    curl.on('error', reject);
  });
}

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

  try {
    for (const src of SOURCES) {
      log(`streaming ${src.program} ← ${src.url}`);
      await streamAndAggregate(src, agg);
    }
  } catch (e) {
    // HONEST stub — never invent lenders.
    log('ERROR streaming SBA CSVs:', e.message);
    atomicWrite(OUT, {
      meta: {
        source: 'U.S. SBA',
        source_url: DATASET_HOME,
        access_method: 'manual_review_only',
        personal_data: false,
        scope: `SBA 7(a) + 504 FOIA loan-level data, aggregated by lender, BorrState=${FILTER_STATE} (LA-County breakout). SOURCE UNREACHABLE at build time — see source_url.`,
        fiscal_years: null,
        count: 0,
        error: e.message,
        retrieved_at,
      },
      lenders: [],
    });
    log(`wrote HONEST stub (0 lenders) → ${path.relative(ROOT, OUT)}`);
    log('deep link:', DATASET_HOME);
    return;
  }

  const keys = Object.keys(agg);
  let fyMin = null, fyMax = null;
  const lenders = keys.map((k, i) => {
    const a = agg[k];
    if (a.fy_min != null && (fyMin == null || a.fy_min < fyMin)) fyMin = a.fy_min;
    if (a.fy_max != null && (fyMax == null || a.fy_max > fyMax)) fyMax = a.fy_max;
    const round = (n) => Math.round(n);
    const fiscal_years = (a.fy_min != null && a.fy_max != null)
      ? (a.fy_min === a.fy_max ? `FY${a.fy_min}` : `FY${a.fy_min}–FY${a.fy_max}`)
      : null;
    return {
      id: i + 1,
      lender: a.lender,
      program: a.program,
      loan_count: a.loan_count,
      total_approved: round(a.total_approved),           // $ dollars
      avg_loan: a.loan_count ? round(a.total_approved / a.loan_count) : 0, // $ dollars
      la_loan_count: a.la_loan_count,
      la_approved: round(a.la_approved),                 // $ dollars
      fy_min: a.fy_min,
      fy_max: a.fy_max,
      fiscal_years,
      // provenance (evidence-first) — institution-level only, NO borrower data.
      // source_url points at the ACTUAL program FOIA CSV this row aggregates (the record-level
      // source), not the generic dataset landing page (kept separately as dataset_page).
      source: 'U.S. SBA',
      source_url: (SOURCES.find(s => s.program === a.program) || {}).url || DATASET_HOME,
      dataset_page: DATASET_HOME,
      identifier: a.lender,
      retrieved_at,
    };
  }).sort((x, y) => (y.total_approved - x.total_approved) || (y.loan_count - x.loan_count));

  // Reassign ids in sorted order so the top lender is id 1 (stable, unique numeric id).
  lenders.forEach((r, i) => { r.id = i + 1; });

  const fiscal_years = (fyMin != null && fyMax != null) ? `FY${fyMin}–FY${fyMax}` : null;

  atomicWrite(OUT, {
    meta: {
      source: 'U.S. SBA',
      source_url: DATASET_HOME,
      access_method: 'official_csv_stream',
      personal_data: false,
      scope: `SBA 7(a) + 504 FOIA loan-level approvals aggregated to one row per lender+program, filtered to BorrState=${FILTER_STATE}, with a Los Angeles-County breakout (la_loan_count / la_approved via ProjectCounty). Most-recent FOIA file per program only: 7(a)=FY2020-Present, 504=FY2010-Present (bounded download; older FOIA files at source_url).`,
      window_note: 'Unequal windows: 504 covers FY2010–present (~16 yrs), 7(a) covers FY2020–present (~6 yrs). Totals are NOT time-normalized — do not rank a 504 CDC against a 7(a) bank on total_approved without accounting for the window (filter by Program, or compare within a program).',
      fiscal_years,
      count: lenders.length,
      retrieved_at,
    },
    lenders,
  });

  log(`wrote ${lenders.length} SBA lenders → ${path.relative(ROOT, OUT)}`);
  log(`fiscal years covered: ${fiscal_years}`);
  log('source:', DATASET_HOME, '($0, official SBA FOIA CSVs)');
}

main().catch(e => { console.error('[sba-lenders] fatal', e); process.exit(1); });