yoloforever c2 FIX (Cody): median_value + explicit scope label + CD-join diagnostic

b3681ebe3b86d33509ee3a2debe5ea784a632d02 · 2026-08-11 14:36:57 -0700 · steve

Cody: total_value is right-skewed vanity without a central figure. Added median
(reveals CD11 $8.05B total vs $9k median = a few mega-projects). Scope label now
states building-only/current-dataset (excludes 1.17M historical+trade). Join
diagnostic prints parsed/matched (15/15) so a blank member column can't ship silently.

Files touched

M scripts/rollups.js

Diff

commit b3681ebe3b86d33509ee3a2debe5ea784a632d02
Author: steve <steve@designerwallcoverings.com>
Date:   Tue Aug 11 14:36:57 2026 -0700

    yoloforever c2 FIX (Cody): median_value + explicit scope label + CD-join diagnostic
    
    Cody: total_value is right-skewed vanity without a central figure. Added median
    (reveals CD11 $8.05B total vs $9k median = a few mega-projects). Scope label now
    states building-only/current-dataset (excludes 1.17M historical+trade). Join
    diagnostic prints parsed/matched (15/15) so a blank member column can't ship silently.
---
 scripts/rollups.js | 15 ++++++++++-----
 1 file changed, 10 insertions(+), 5 deletions(-)

diff --git a/scripts/rollups.js b/scripts/rollups.js
index b4cb03e..1527fa1 100644
--- a/scripts/rollups.js
+++ b/scripts/rollups.js
@@ -20,6 +20,8 @@ async function cdNames() {
     const m = String(r.name).match(/^\s*(\d+)\s*[-–]\s*(.+)$/);
     if (m) map[m[1]] = m[2].trim();
   }
+  const matched = Object.keys(map).length;
+  console.log(`  [join] council_district gis rows: ${rows.length}, name-parsed: ${matched}${matched === 0 ? '  ⚠ member column will be BLANK' : ''}`);
   return map;
 }
 
@@ -30,6 +32,7 @@ const AGG = (groupExpr, groupAlias) => `
          count(*) FILTER (WHERE p.permit_type='Bldg-New')::int AS new_builds,
          round(sum(p.valuation))::bigint AS total_value,
          round(avg(p.valuation))::bigint AS avg_value,
+         percentile_cont(0.5) WITHIN GROUP (ORDER BY p.valuation)::bigint AS median_value,
          round(avg(permit_lead_score(p.issue_date,p.valuation,p.permit_type,p.permit_sub_type,p.status_desc)),1) AS avg_lead_score,
          count(*) FILTER (WHERE p.issue_date >= now()-interval '90 days')::int AS last_90d
   FROM la_building_permits_raw p
@@ -49,22 +52,24 @@ async function main() {
   // --- Council district ---
   const cd = (await q(`${AGG('p.council_district', 'cd')} ORDER BY total_value DESC NULLS LAST`)).rows
     .map(r => ({ ...r, council_member: names[r.cd] || '' }));
-  const cdHeader = ['cd', 'council_member', 'permits', 'issued', 'new_builds', 'last_90d', 'total_value', 'avg_value', 'avg_lead_score'];
+  const cdHeader = ['cd', 'council_member', 'permits', 'issued', 'new_builds', 'last_90d', 'total_value', 'median_value', 'avg_value', 'avg_lead_score'];
   writeCsv(path.join(OUT, `rollup-council-district-${stamp}.csv`), cdHeader, cd);
 
   // --- ZIP (top by value) ---
   const zip = (await q(`${AGG('p.zip_code', 'zip')} ORDER BY total_value DESC NULLS LAST LIMIT 60`)).rows;
-  const zipHeader = ['zip', 'permits', 'issued', 'new_builds', 'last_90d', 'total_value', 'avg_value', 'avg_lead_score'];
+  const zipHeader = ['zip', 'permits', 'issued', 'new_builds', 'last_90d', 'total_value', 'median_value', 'avg_value', 'avg_lead_score'];
   writeCsv(path.join(OUT, `rollup-zip-${stamp}.csv`), zipHeader, zip);
 
   // --- report ---
-  console.log(`\nPermit activity rollups (2020-present) — ${stamp}\n`);
+  console.log(`\nPermit activity rollups — ${stamp}`);
+  console.log('  Scope: BUILDING permits only (dataset pi9x-tg5x, 2020-present). Excludes 2010-2019, pre-2010, and electrical/mechanical trade permits.');
+  console.log('  Note: total_value sums raw valuations across years (not inflation-adjusted, not project-deduped); median_value is the skew-robust central figure.\n');
   console.log('  TOP COUNCIL DISTRICTS by total project value:');
   cd.slice(0, 8).forEach(r => console.log(
-    `   CD ${String(r.cd).padStart(2)} ${(r.council_member || '').padEnd(22).slice(0, 22)} ${String(r.permits).padStart(7)} permits  ${money(r.total_value).padStart(16)}  avg score ${r.avg_lead_score}  (${r.new_builds} new-builds, ${r.last_90d} last 90d)`));
+    `   CD ${String(r.cd).padStart(2)} ${(r.council_member || '').padEnd(22).slice(0, 22)} ${String(r.permits).padStart(7)} permits  ${money(r.total_value).padStart(16)}  median ${money(r.median_value).padStart(9)}  score ${r.avg_lead_score}  (${r.new_builds} new)`));
   console.log('\n  TOP ZIPs by total project value:');
   zip.slice(0, 8).forEach(r => console.log(
-    `   ${r.zip}  ${String(r.permits).padStart(7)} permits  ${money(r.total_value).padStart(16)}  avg score ${r.avg_lead_score}  (${r.new_builds} new-builds)`));
+    `   ${r.zip}  ${String(r.permits).padStart(7)} permits  ${money(r.total_value).padStart(16)}  median ${money(r.median_value).padStart(9)}  score ${r.avg_lead_score}  (${r.new_builds} new)`));
   console.log(`\n  CSVs → tmp/leads/rollup-council-district-${stamp}.csv, rollup-zip-${stamp}.csv\n`);
 }

← f57ce2c yoloforever c2: per-CD + per-ZIP permit-activity rollups (sc · back to La Socrata Ingester · yoloforever c3: close Cody-c1 canary gaps — CSLB tracking + f794850 →