`scripts/deals-feed.js`

100 lines
// Newsworthy permit "deals" feed (READ-ONLY, $0 local). Surfaces the biggest recent
// high-value LA building permits as human-readable headlines + a structured JSON feed
// for RE-news / RENTV synergy. Enriched with parcel value + council-member name.
import { q, pool } from '../src/db.js';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const OUT = path.resolve(__dirname, '../tmp/leads');
const money = v => v == null ? null : '$' + Math.round(Number(v)).toLocaleString();
const millions = v => '$' + (Number(v) / 1e6).toFixed(Number(v) >= 1e7 ? 0 : 1) + 'M';

const DAYS = Number(process.env.DEALS_DAYS || 90);
const MIN_VALUE = Number(process.env.DEALS_MIN || 1_000_000);

// permit_type -> a readable "deal kind"
const KIND = {
  'Bldg-New': 'ground-up construction',
  'Bldg-Addition': 'major addition',
  'Bldg-Alter/Repair': 'major renovation',
  'Swimming-Pool/Spa': 'pool/spa',
  'Bldg-Demolition': 'demolition',
  'Grading': 'site grading',
};
const useKind = (t) => KIND[t] || (t || 'project').replace(/^Bldg-/, '').toLowerCase();

async function cdNames() {
  const rows = (await q(`SELECT name FROM la_gis_features WHERE layer='council_district' AND name IS NOT NULL`)).rows;
  const m = {};
  for (const r of rows) { const x = String(r.name).match(/^\s*(\d+)\s*[-–]\s*(.+)$/); if (x) m[x[1]] = x[2].trim(); }
  return m;
}

async function main() {
  const stamp = (await q(`SELECT to_char(now(),'YYYY-MM-DD') d`)).rows[0].d;
  const names = await cdNames();
  fs.mkdirSync(OUT, { recursive: true });

  // Dedup to one permit per address+zip (highest valuation), then rank by lead_score
  // (encodes recency/type/status newsworthiness), valuation as tiebreak (Cody c4:
  // raw-max-valuation surfaces the same airport 3x + institutional infra, not deals).
  const rows = (await q(`
    WITH ranked AS (
      SELECT DISTINCT ON (p.primary_address, p.zip_code)
             p.permit_nbr, to_char(p.issue_date,'YYYY-MM-DD') AS issued, p.primary_address AS address, p.zip_code AS zip,
             p.council_district AS cd, p.permit_type AS work_type, p.permit_sub_type AS building_type,
             p.use_desc, p.valuation AS project_value, p.work_desc AS scope, p.lat, p.lon,
             a.total_value AS assessed_value, a.year_built, a.sqft_main AS sqft,
             permit_lead_score(p.issue_date,p.valuation,p.permit_type,p.permit_sub_type,p.status_desc) AS lead_score
      FROM la_building_permits_raw p
      LEFT JOIN la_assessor_parcels_raw a ON a.ain=p.apn AND a.roll_year='2025'
      WHERE p.dataset_id='pi9x-tg5x' AND p.status_desc='Issued'
        AND p.issue_date >= now() - ($1||' days')::interval
        AND p.valuation >= $2 AND p.primary_address IS NOT NULL
      ORDER BY p.primary_address, p.zip_code, p.valuation DESC
    )
    SELECT * FROM ranked ORDER BY lead_score DESC, project_value DESC LIMIT 60
  `, [DAYS, MIN_VALUE])).rows;

  const deals = rows.map(r => {
    const member = names[r.cd];
    const cdTag = r.cd ? `CD ${r.cd}${member ? ' · ' + member : ''}` : '';
    const pv = Number(r.project_value);
    const assessed = r.assessed_value == null ? null : Number(r.assessed_value);
    // Flag suspicious valuations for editorial verification (Cody c4: LADBS valuation is
    // self-reported): institutional-scale (>$100M) or wildly > parcel assessed value.
    const outlier = pv > 100e6 || (assessed && assessed > 0 && pv / assessed > 150);
    const headline = `${millions(pv)} ${useKind(r.work_type)} — ${r.address}${r.zip ? ', ' + r.zip : ''}${cdTag ? ' (' + cdTag + ')' : ''}${outlier ? ' ⚠verify' : ''}`;
    return {
      headline, permit_nbr: r.permit_nbr, issued: r.issued, address: r.address, zip: r.zip,
      council_district: r.cd, council_member: member || null, kind: useKind(r.work_type),
      work_type: r.work_type, building_type: r.building_type, project_value: pv,
      assessed_value: assessed, valuation_outlier: outlier,
      year_built: (r.year_built && String(r.year_built) !== '0') ? r.year_built : null,
      sqft: r.sqft == null ? null : Number(r.sqft),
      lead_score: r.lead_score, scope: r.scope,
      map: r.lat && r.lon ? `https://www.google.com/maps?q=${r.lat},${r.lon}` : null,
    };
  });

  const feed = { generated: stamp, window_days: DAYS, min_value: MIN_VALUE, count: deals.length, deals };
  fs.writeFileSync(path.join(OUT, `deals-feed-${stamp}.json`), JSON.stringify(feed, null, 2));

  // human digest
  console.log(`\nLA permit deals — top high-value permits issued in the last ${DAYS} days (>= ${money(MIN_VALUE)})\n`);
  deals.slice(0, 15).forEach((d, i) => {
    console.log(`  ${String(i + 1).padStart(2)}. ${d.headline}`);
    const bits = [];
    if (d.assessed_value) bits.push(`parcel assessed ${millions(d.assessed_value)}`);
    if (d.year_built) bits.push(`built ${d.year_built}`);
    if (d.sqft) bits.push(`${Number(d.sqft).toLocaleString()} sqft`);
    bits.push(`issued ${d.issued}`); bits.push(`lead ${d.lead_score}`);
    console.log(`      ${bits.join(' · ')}`);
  });
  console.log(`\n  ${deals.length} deals → tmp/leads/deals-feed-${stamp}.json\n`);
}

main().catch(e => { console.error('deals-feed error:', e.message); process.exitCode = 1; }).finally(() => pool.end());