`scripts/ingest-public-deals.mjs`

55 lines
#!/usr/bin/env node
// TK-10708  Pull CLOSED DEALS from PUBLIC RECORDS (deeds / documentary-transfer-tax) into
// the rolling deal store — the real volume (tens of thousands) vs the ~40 newsworthy ones.
// Source = usre.parcel_event (recorded sale events) JOIN commercial_parcel, amount>250k.
// Each = a real closed commercial sale: address, price, date, county. $0 (local usre reads).
//
// Usage: node scripts/ingest-public-deals.mjs [--n 8000]

import { execFileSync } from 'node:child_process';
import { readFileSync, writeFileSync, existsSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';

const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
const STORE = join(ROOT, 'data', 'deal-store.json');
const _ni = process.argv.indexOf('--n');   // guard: when --n is absent, indexOf=-1 → argv[0] (node path) → NaN LIMIT
const N = _ni >= 0 ? (parseInt(process.argv[_ni + 1], 10) || 8000) : 8000;
const CAP = 12000;

const money = n => n >= 1e9 ? '$' + (n / 1e9).toFixed(1) + 'B' : n >= 1e6 ? '$' + Math.round(n / 1e6) + 'M' : '$' + Math.round(n / 1e3) + 'K';
const tc = s => (s || '').toLowerCase().replace(/\b\w/g, c => c.toUpperCase());

// most-recent closed commercial sales from the public record (deeds/DTT)
const sql = `SELECT row_to_json(t) FROM (
  SELECT pe.event_date AS date, pe.amount::bigint AS price, cp.ctype, cp.address, cp.city,
         r.name AS county, cp.county_fips, pe.doc_number, pe.source AS provenance
  FROM parcel_event pe
  JOIN commercial_parcel cp ON cp.county_fips=pe.county_fips AND cp.ain=pe.source_id
  LEFT JOIN region r ON r.fips=pe.county_fips AND r.region_type='county'
  WHERE pe.event_type='sale' AND pe.amount>250000 AND pe.county_fips LIKE '06%'
  ORDER BY pe.event_date DESC, pe.amount DESC
  LIMIT ${N}) t`;
const rows = execFileSync('psql', ['usre', '-t', '-A', '-c', sql], { encoding: 'utf8' })
  .trim().split('\n').filter(Boolean).map(l => JSON.parse(l));

let store = existsSync(STORE) ? JSON.parse(readFileSync(STORE, 'utf8')) : [];
const sig = d => `${d.price}|${(d.title || '').toLowerCase().replace(/[^a-z0-9]+/g, ' ').trim().slice(0, 34)}`;
const have = new Set(store.map(sig));
let added = 0;
for (const r of rows) {
  if (!r.address) continue;
  const src = r.provenance === 'assessor_dtt_inferred' ? `${tc(r.county || 'LA')} County · DTT` : `${tc(r.county || 'County')} · Deeds`;
  const rec = {
    type: 'Sale', price: r.price, label: money(r.price),
    market: tc(r.city || r.county || 'US'),
    title: `${tc(r.address)}${r.city ? ', ' + tc(r.city) : ''} — ${tc(r.ctype || 'commercial')}`,
    source: src, link: null, date: r.date, buyer: null, seller: null, doc: r.doc_number
  };
  const s = sig(rec); if (have.has(s)) continue; have.add(s); store.push(rec); added++;
}
store.sort((a, b) => (b.date || '').localeCompare(a.date || '') || (b.price || 0) - (a.price || 0));
if (store.length > CAP) store = store.slice(0, CAP);
writeFileSync(STORE, JSON.stringify(store, null, 0));
console.log(`Public-record deals: pulled ${rows.length}, added ${added} new. Store now ${store.length}.`);