← back to La Socrata Ingester
yoloforever c2: per-CD + per-ZIP permit-activity rollups (scripts/rollups.js)
f57ce2c139a0fe856e3dc24947f8d1ae04c6df4e · 2026-08-11 14:33:40 -0700 · steve
Aggregates permits x lead_score joined to gis council-district NAMES; permits,
issued, new-builds, last-90d, total/avg value, avg lead score per CD + top-60 ZIPs.
CSV exports. New local analytics surface (not the live viewer). $0 local.
Files touched
Diff
commit f57ce2c139a0fe856e3dc24947f8d1ae04c6df4e
Author: steve <steve@designerwallcoverings.com>
Date: Tue Aug 11 14:33:40 2026 -0700
yoloforever c2: per-CD + per-ZIP permit-activity rollups (scripts/rollups.js)
Aggregates permits x lead_score joined to gis council-district NAMES; permits,
issued, new-builds, last-90d, total/avg value, avg lead score per CD + top-60 ZIPs.
CSV exports. New local analytics surface (not the live viewer). $0 local.
---
scripts/rollups.js | 71 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 71 insertions(+)
diff --git a/scripts/rollups.js b/scripts/rollups.js
new file mode 100644
index 0000000..b4cb03e
--- /dev/null
+++ b/scripts/rollups.js
@@ -0,0 +1,71 @@
+// Per-council-district + per-ZIP permit-activity rollups (READ-ONLY, $0 local).
+// Aggregates la_building_permits_raw (2020-present) with the shared permit_lead_score,
+// joined to the gis_features council-district NAMES loaded into the platform.
+// Writes CSVs to tmp/leads/ and prints top summaries. New local analytics surface —
+// does not touch the live public viewer.
+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 => '$' + Math.round(Number(v || 0)).toLocaleString();
+
+// CD number -> member name, from the gis council_district layer (name = "N - Member").
+async function cdNames() {
+ const rows = (await q(`SELECT name FROM la_gis_features WHERE layer='council_district' AND name IS NOT NULL`)).rows;
+ const map = {};
+ for (const r of rows) {
+ const m = String(r.name).match(/^\s*(\d+)\s*[-–]\s*(.+)$/);
+ if (m) map[m[1]] = m[2].trim();
+ }
+ return map;
+}
+
+const AGG = (groupExpr, groupAlias) => `
+ SELECT ${groupExpr} AS ${groupAlias},
+ count(*)::int AS permits,
+ count(*) FILTER (WHERE p.status_desc='Issued')::int AS issued,
+ 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,
+ 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
+ WHERE p.dataset_id='pi9x-tg5x' AND ${groupExpr} IS NOT NULL
+ GROUP BY 1`;
+
+function writeCsv(file, header, rows) {
+ const esc = v => { const s = v == null ? '' : String(v); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; };
+ fs.writeFileSync(file, [header.join(','), ...rows.map(r => header.map(h => esc(r[h])).join(','))].join('\n'));
+}
+
+async function main() {
+ const stamp = (await q(`SELECT to_char(now(),'YYYY-MM-DD') d`)).rows[0].d;
+ fs.mkdirSync(OUT, { recursive: true });
+ const names = await cdNames();
+
+ // --- 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'];
+ 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'];
+ writeCsv(path.join(OUT, `rollup-zip-${stamp}.csv`), zipHeader, zip);
+
+ // --- report ---
+ console.log(`\nPermit activity rollups (2020-present) — ${stamp}\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)`));
+ 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)`));
+ console.log(`\n CSVs → tmp/leads/rollup-council-district-${stamp}.csv, rollup-zip-${stamp}.csv\n`);
+}
+
+main().catch(e => { console.error('rollups error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
← 3687729 yoloforever c1 FIX (Cody gate): canary reads la_ingest_state
·
back to La Socrata Ingester
·
yoloforever c2 FIX (Cody): median_value + explicit scope lab b3681eb →