`scripts/enrich-property.js`

165 lines
#!/usr/bin/env node
// enrich-property.js — per-property PUBLIC-RECORD dossier for CRCP ("figure each property out").
//
// Given an AIN (or a street address), pulls everything the FREE, non-proprietary public sources
// expose about the parcel and returns a normalized dossier. This is the free foundation of the
// per-property enrichment engine; the paid/slow DEED-PARTIES layer (grantor/grantee via the LA
// RR/CC recorded deed) is a separate, Steve-gated source decision (see docs memo) and is stubbed
// here as `deed: { available:false, path: ... }` rather than silently omitted.
//
// FREE SOURCES USED (all $0, public record, no key):
//   • LA County Assessor parcel roll (ArcGIS FeatureServer) — address, use, size, age, units,
//     beds/baths, assessed values, last recording date. Keyed by AIN; searchable by street.
//   • ULA price feed (data/ula-la-deals.json, already built) — exact sale price for LA City
//     $5.3M+ sales, matched by ZIP (best-effort; exact match needs the deed→AIN bridge).
//
// Usage:
//   node scripts/enrich-property.js --ain 4207016028
//   node scripts/enrich-property.js --address "4150 MADISON AVE"
//   const { enrichByAin } = require('./enrich-property');  // programmatic
'use strict';
const fs = require('fs');
const path = require('path');
const https = require('https');

const ASSESSOR = 'https://services.arcgis.com/RmCCgQtiZLDCtblq/arcgis/rest/services/Parcel_Data_2021_Table/FeatureServer/0/query';

function getJson(url) {
  return new Promise((resolve, reject) => {
    // 8s timeout — public gov ArcGIS hangs under load; without this a slow upstream would hang the
    // Express request forever (Cody Cycle-2 HIGH). https.get's `timeout` fires the event but does
    // NOT auto-abort, so destroy the request on timeout to fail fast with a 502.
    const req = https.get(url, { headers: { 'User-Agent': 'CRCP-enrich/1.0 (public-records)' }, timeout: 8000 }, res => {
      if (res.statusCode !== 200) { res.resume(); return reject(new Error('HTTP ' + res.statusCode)); }
      let b = ''; res.on('data', c => b += c); res.on('end', () => { try { resolve(JSON.parse(b)); } catch (e) { reject(e); } });
    });
    req.on('timeout', () => req.destroy(new Error('assessor timeout')));
    req.on('error', reject);
  });
}

const epochToDate = ms => (ms == null || isNaN(+ms)) ? null : new Date(+ms).toISOString().slice(0, 10);
const clean = v => (v == null || String(v).trim() === '') ? null : String(v).trim();

// Normalize one assessor parcel row → dossier shape.
function normalize(a) {
  return {
    ain: clean(a.AIN),
    roll_year: clean(a.RollYear),
    address: clean(a.PropertyLocation),
    street: [clean(a.SitusHouseNo), clean(a.SitusStreet), clean(a.SitusDirection)].filter(Boolean).join(' ') || null,
    city: clean(a.SitusCity),
    zip: clean(a.SitusZIP5) || clean(a.SitusZIP),   // field is ALL-CAPS SitusZIP5 (Cody-verified fix)
    use_type: clean(a.UseType),
    use_desc: clean(a.UseDescription) || clean(a.SpecificUseType) || clean(a.GeneralUseType),
    year_built: a.YearBuilt || null,
    effective_year: a.EffectiveYearBuilt || null,
    sqft: a.SQFTmain || null,
    units: a.Units || null,
    bedrooms: a.Bedrooms || null,
    bathrooms: a.Bathrooms || null,
    assessed_total: a.Roll_TotalValue ?? a.TotalValue ?? a.Roll_totLandImp ?? null,   // 2025 roll uses Roll_TotalValue
    assessed_land: a.LandValue ?? a.Roll_LandValue ?? null,
    assessed_improvement: a.ImpValue ?? a.Roll_ImpValue ?? null,
    assessed_as_of: clean(a.RollYear),   // which assessment roll these figures come from (data age)
    // NOTE: this is the last transfer the ASSESSOR processed that triggered a Prop-13 reassessment —
    // lagged, and NOT necessarily the most recent sale (exclusion-eligible transfers may not appear).
    last_transfer_recorded: epochToDate(a.RecordingDate),
    tax_rate_area: clean(a.TaxRateArea),
  };
}

// Pick the newest RollYear row for a parcel.
function pickLatest(features) {
  const rows = (features || []).map(f => f.attributes);
  rows.sort((x, y) => String(y.RollYear || '').localeCompare(String(x.RollYear || '')));
  return rows[0] || null;
}

async function assessorByAin(ain) {
  const url = `${ASSESSOR}?where=AIN%3D%27${encodeURIComponent(ain)}%27&outFields=*&f=json`;
  const d = await getJson(url);
  const row = pickLatest(d.features);
  return row ? normalize(row) : null;
}

// Address search: match SitusStreet + house number (ArcGIS LIKE). Returns up to `limit` candidates.
async function assessorByAddress(addr, limit = 8) {
  const m = String(addr).trim().match(/^(\d+)\s+(.*)$/);
  const house = m ? m[1] : null;
  // Strip to letters/digits/space only — removes ArcGIS-SQL injection chars (quotes, --, ;) AND
  // makes the single-quote-escape moot (Cody Cycle-2). Then guard against roll-wide '%%' scans:
  // require a house number OR a street term of >=4 chars before hitting the 2.4M-row service.
  const street = (m ? m[2] : addr).toUpperCase().replace(/[^A-Z0-9 ]/g, ' ').replace(/\s+/g, ' ').trim();
  if (!house && street.length < 4) return [];
  let where = `UPPER(SitusStreet) LIKE '%${street}%'`;
  if (house) where = `SitusHouseNo='${house}' AND ${where}`;
  const url = `${ASSESSOR}?where=${encodeURIComponent(where)}&outFields=*&f=json&resultRecordCount=${limit * 5}`;
  const d = await getJson(url);
  // dedupe by AIN, keep latest roll per AIN
  const byAin = new Map();
  for (const f of (d.features || [])) { const a = f.attributes; const k = a.AIN; if (!byAin.has(k) || String(a.RollYear) > String(byAin.get(k).RollYear)) byAin.set(k, a); }
  return [...byAin.values()].slice(0, limit).map(normalize);
}

// ULA sales in the same ZIP — a COARSE, ZIP-level candidate list, NOT a confirmed match for this
// parcel. A precise instrument→AIN match needs the gated RR/CC deed lookup (the assessor layer has
// no instrument field to join on — Cody-verified). Each candidate carries its doc_number so a user
// can pull the actual recorded deed to confirm whether it's THIS property.
function ulaZipCandidates(zip) {
  if (!zip) return [];
  try {
    const u = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'data', 'ula-la-deals.json'), 'utf8'));
    return (u.deals || []).filter(d => d.zip && String(d.zip) === String(zip))
      .slice(0, 12).map(d => ({ date: d.date, price: d.price, use: d.use, instrument: d.doc_number, basis: d.price_basis }));
  } catch (_) { return []; }
}

// The deed-parties layer is a gated source decision — declare the gap explicitly, never silently omit.
function deedStub(dossier) {
  return {
    available: false,
    reason: 'grantor/grantee + recorded sale price live in the LA RR/CC recorded deed, which is not free online (Gov. Code §6254.21).',
    paths: [
      { name: 'CPRA bulk index request', cost: '$0 (weeks)', gives: 'grantor, grantee, instrument, recording date, AIN' },
      { name: 'NETR per-record', cost: '~$3.50–8.00/property (metered, gated)', gives: 'transfer detail + document image' },
      { name: 'Assessor data-sales change-of-ownership file', cost: '~$500–5,000 bulk (gated)', gives: 'price + parties + AIN, all-county' },
    ],
    bridge_hint: dossier && dossier.last_transfer_recorded ? `assessor shows last reassessment-triggering transfer recorded ${dossier.last_transfer_recorded} (lagged; may not be the latest sale)` : null,
  };
}

async function enrichByAin(ain) {
  const parcel = await assessorByAin(ain);
  if (!parcel) return { ain, found: false };
  const cands = ulaZipCandidates(parcel.zip);
  return {
    ain, found: true, parcel,
    ula_zip_candidates: cands,   // COARSE ZIP-level candidate sales — confirm via each instrument's deed
    ula_note: cands.length ? `${cands.length} ULA $5.3M+ sale(s) recorded in ZIP ${parcel.zip} — candidates, not parcel-confirmed (precise match needs the gated deed lookup)` : null,
    deed: deedStub(parcel),
    sources: ['LA County Assessor parcel roll (public)', 'LA City Measure ULA (public)'],
  };
}

async function enrichByAddress(addr) {
  const candidates = await assessorByAddress(addr);
  if (!candidates.length) return { query: addr, found: false };
  if (candidates.length === 1) return enrichByAin(candidates[0].ain);
  return { query: addr, found: true, multiple: true, candidates };
}

module.exports = { enrichByAin, enrichByAddress, assessorByAin, assessorByAddress };

if (require.main === module) {
  (async () => {
    const args = process.argv.slice(2);
    const ainI = args.indexOf('--ain'); const addrI = args.indexOf('--address');
    let out;
    if (ainI >= 0) out = await enrichByAin(args[ainI + 1]);
    else if (addrI >= 0) out = await enrichByAddress(args[addrI + 1]);
    else { console.error('usage: --ain <AIN> | --address "<street>"'); process.exit(1); }
    console.log(JSON.stringify(out, null, 2));
  })().catch(e => { console.error('enrich failed:', e.message); process.exit(1); });
}