`scripts/status-brief.js`

91 lines
// LA permit platform — daily internal status brief (READ-ONLY, $0 local).
// Consolidates the platform's components into one dated brief: data footprint, canary
// verdict (read from its own latest.json — truthful to what it last reported), top
// current deals (from the deals feed), contractor supply, and the top market. Missing
// artifacts show as gaps, not silent successes.
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 DIR = path.resolve(__dirname, '../tmp/leads');
const CANARY = path.resolve(__dirname, '../tmp/canary/latest.json');
const money = v => '$' + Math.round(Number(v || 0)).toLocaleString();
const millions = v => '$' + (Number(v) / 1e6).toFixed(Number(v) >= 1e7 ? 0 : 1) + 'M';
const readJson = p => { try { return JSON.parse(fs.readFileSync(p, 'utf8')); } catch { return null; } };

function latestFeed() {
  if (!fs.existsSync(DIR)) return null;
  const f = fs.readdirSync(DIR).filter(x => /^deals-feed-.*\.json$/.test(x)).sort().pop();
  return f ? readJson(path.join(DIR, f)) : null;
}

async function main() {
  const stamp = (await q(`SELECT to_char(now(),'YYYY-MM-DD HH24:MI') d`)).rows[0].d;
  const L = [];
  L.push(`# LA Permit Platform — status brief`);
  L.push(`_${stamp} · buildingpermits.agentabrams.com_`);
  L.push('');

  // 1. Data footprint
  const counts = (await q(`
    SELECT 'building_permits' t, count(*) n FROM la_building_permits_raw
    UNION ALL SELECT 'assessor_parcels', count(*) FROM la_assessor_parcels_raw
    UNION ALL SELECT 'parcel_geometry', count(*) FROM la_parcel_geom
    UNION ALL SELECT 'code_enforcement', count(*) FROM la_code_enforcement_raw
    UNION ALL SELECT 'business_regs', count(*) FROM la_business_registrations_raw
    UNION ALL SELECT 'gis_features', count(*) FROM la_gis_features
    UNION ALL SELECT 'cslb_contractors', count(*) FROM cslb_raw`)).rows;
  const total = counts.reduce((s, r) => s + Number(r.n), 0);
  L.push(`## Data footprint — ${total.toLocaleString()} rows`);
  counts.forEach(r => L.push(`- ${r.t}: ${Number(r.n).toLocaleString()}`));
  L.push('');

  // 2. Canary health (from its own heartbeat)
  const can = readJson(CANARY);
  L.push('## Data health (canary)');
  if (!can) L.push('- ⚠ no canary run found — run `node scripts/canary-freshness.js`');
  else {
    L.push(`- Verdict: **${can.verdict}**`);
    const bad = (can.sources || []).filter(s => s.status !== 'OK');
    if (bad.length) bad.forEach(s => L.push(`- ${s.status === 'BROKEN' ? '⚠ known-broken' : s.status}: ${s.source}`));
    else L.push('- all sources healthy');
  }
  L.push('');

  // 3. Top current deals (from the deals feed)
  const feed = latestFeed();
  L.push('## Top current deals');
  if (!feed) L.push('- ⚠ no deals feed — run `node scripts/deals-feed.js`');
  else {
    if (feed.generated !== stamp.slice(0, 10)) L.push(`_feed from ${feed.generated}_`);
    feed.deals.slice(0, 5).forEach((d, i) => L.push(`${i + 1}. ${millions(d.project_value)} ${d.kind} — ${d.address}${d.council_member ? ' (CD ' + d.council_district + ' · ' + d.council_member + ')' : ''}${d.valuation_outlier ? ' ⚠' : ''}`));
  }
  L.push('');

  // 4. Top market (rollup) + contractor supply
  // Rank hottest district by permit VOLUME (outlier-immune) + show MEDIAN value, not a raw
  // sum a single unverified mega-permit dominates (Cody c8: don't re-import the killed metric).
  const topCd = (await q(`
    SELECT p.council_district cd, count(*) permits,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY p.valuation)::bigint AS median_val
    FROM la_building_permits_raw p WHERE p.dataset_id='pi9x-tg5x' AND p.status_desc='Issued'
      AND p.issue_date > now()-interval '90 days' AND p.council_district IS NOT NULL
    GROUP BY 1 ORDER BY count(*) DESC LIMIT 1`)).rows[0];
  const cdName = topCd ? (await q(`SELECT name FROM la_gis_features WHERE layer='council_district' AND name LIKE $1 LIMIT 1`, [topCd.cd + ' -%'])).rows[0]?.name : null;
  const bTrade = Number((await q(`SELECT count(*) c FROM cslb_raw, unnest(string_to_array("Classifications(s)",'|')) x WHERE "County"='Los Angeles' AND "PrimaryStatus"='CLEAR' AND trim(x)='B'`)).rows[0].c);
  L.push('## Market');
  if (topCd) L.push(`- Hottest district (90d, by permit volume): **${cdName || 'CD ' + topCd.cd}** — ${Number(topCd.permits).toLocaleString()} permits, ${money(topCd.median_val)} median/permit`);
  L.push(`- General-building contractor supply (active, LA County): ${bTrade.toLocaleString()}`);
  L.push('');

  const out = path.join(DIR, `status-brief-${stamp.slice(0, 10)}.md`);
  fs.mkdirSync(DIR, { recursive: true });
  fs.writeFileSync(out, L.join('\n'));
  console.log(L.join('\n'));
  console.log(`\n→ ${out}`);
}

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