← back to La Socrata Ingester
yoloforever c6: contractor-market intelligence (scripts/contractor-intel.js)
e534fb9fe455f16040c7f603d140bf85f169549a · 2026-08-12 07:29:05 -0700 · steve
First use of the 243k CSLB dataset. Joins active LA-County contractors (by trade
classification) to permit activity: supply by trade (23,250 General Building etc.)
+ per-ZIP demand-vs-local-supply (Palisades 90272 = 33.5 permits/contractor, fire-
rebuild straining local supply). Honesty caveat labeled: contractor ZIP=business
address, supply proxy not work-location. INTERNAL (not public site).
Files touched
A scripts/contractor-intel.js
Diff
commit e534fb9fe455f16040c7f603d140bf85f169549a
Author: steve <steve@designerwallcoverings.com>
Date: Wed Aug 12 07:29:05 2026 -0700
yoloforever c6: contractor-market intelligence (scripts/contractor-intel.js)
First use of the 243k CSLB dataset. Joins active LA-County contractors (by trade
classification) to permit activity: supply by trade (23,250 General Building etc.)
+ per-ZIP demand-vs-local-supply (Palisades 90272 = 33.5 permits/contractor, fire-
rebuild straining local supply). Honesty caveat labeled: contractor ZIP=business
address, supply proxy not work-location. INTERNAL (not public site).
---
scripts/contractor-intel.js | 72 +++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 72 insertions(+)
diff --git a/scripts/contractor-intel.js b/scripts/contractor-intel.js
new file mode 100644
index 0000000..23b56f1
--- /dev/null
+++ b/scripts/contractor-intel.js
@@ -0,0 +1,72 @@
+// Contractor-market intelligence (READ-ONLY, $0 local, INTERNAL — not the public site).
+// Joins active CSLB contractors (LA County) to LA building-permit activity to characterize
+// supply (contractors by trade classification) vs demand (permit volume/value by area).
+//
+// HONESTY CAVEAT (baked into labels): a contractor's ZIP in CSLB is their BUSINESS
+// address, NOT where they work. Per-ZIP contractor counts are "business-located in ZIP",
+// a proxy for local supply — NOT "contractors serving that ZIP". County/classification
+// supply figures are robust; per-ZIP density is directional only.
+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();
+// CSLB classification code -> readable trade (common ones)
+const TRADE = {
+ B: 'General Building', 'B-2': 'Residential Remodeling', A: 'General Engineering',
+ 'C-8': 'Concrete', 'C-10': 'Electrical', 'C10': 'Electrical', 'C-36': 'Plumbing', 'C36': 'Plumbing',
+ 'C-20': 'HVAC', 'C20': 'HVAC', 'C-33': 'Painting', 'C33': 'Painting', 'C-27': 'Landscaping',
+ 'C-5': 'Framing/Carpentry', 'C-9': 'Drywall', 'C-4': 'Boiler', 'C-7': 'Low Voltage',
+};
+const trade = c => TRADE[c] || TRADE[c.replace('-', '')] || c;
+
+async function main() {
+ const stamp = (await q(`SELECT to_char(now(),'YYYY-MM-DD') d`)).rows[0].d;
+ fs.mkdirSync(OUT, { recursive: true });
+
+ // 1. SUPPLY — active LA County contractors by classification (split pipe-separated codes)
+ const classes = (await q(`
+ SELECT trim(c) AS class, count(*)::int AS contractors
+ FROM cslb_raw, unnest(string_to_array("Classifications(s)", '|')) AS c
+ WHERE "County"='Los Angeles' AND "PrimaryStatus"='CLEAR' AND trim(c) <> ''
+ GROUP BY 1 ORDER BY 2 DESC LIMIT 15`)).rows;
+
+ // 2. DEMAND vs SUPPLY per ZIP: permit activity (180d) + contractors business-located in ZIP
+ const zips = (await q(`
+ WITH demand AS (
+ SELECT zip_code AS zip, count(*)::int AS permits, round(sum(valuation))::bigint AS value
+ FROM la_building_permits_raw
+ WHERE dataset_id='pi9x-tg5x' AND status_desc='Issued'
+ AND issue_date > now()-interval '180 days' AND zip_code IS NOT NULL
+ GROUP BY 1
+ ),
+ supply AS (
+ SELECT left("ZIPCode",5) AS zip, count(*)::int AS local_contractors
+ FROM cslb_raw WHERE "County"='Los Angeles' AND "PrimaryStatus"='CLEAR' AND "ZIPCode" IS NOT NULL
+ GROUP BY 1
+ )
+ SELECT d.zip, d.permits, d.value, COALESCE(s.local_contractors,0) AS local_contractors,
+ round(d.permits::numeric / NULLIF(s.local_contractors,0), 2) AS permits_per_contractor
+ FROM demand d LEFT JOIN supply s USING (zip)
+ WHERE d.permits >= 20
+ ORDER BY permits_per_contractor DESC NULLS LAST LIMIT 60`)).rows;
+
+ fs.writeFileSync(path.join(OUT, `contractor-intel-classes-${stamp}.csv`),
+ ['class,trade,contractors', ...classes.map(r => `${r.class},"${trade(r.class)}",${r.contractors}`)].join('\n'));
+ fs.writeFileSync(path.join(OUT, `contractor-intel-zips-${stamp}.csv`),
+ ['zip,permits_180d,permit_value,local_contractors,permits_per_contractor',
+ ...zips.map(r => `${r.zip},${r.permits},${r.value},${r.local_contractors},${r.permits_per_contractor ?? ''}`)].join('\n'));
+
+ console.log(`\nContractor-market intelligence — ${stamp} (INTERNAL; contractor ZIP = business address, a supply proxy)\n`);
+ console.log(' ACTIVE LA COUNTY CONTRACTORS by trade (supply):');
+ classes.slice(0, 8).forEach(r => console.log(` ${String(r.class).padEnd(5)} ${trade(r.class).padEnd(22)} ${r.contractors.toLocaleString().padStart(7)}`));
+ console.log('\n HIGHEST DEMAND-PER-LOCAL-CONTRACTOR ZIPs (permit activity vs local contractor base — opportunity signal):');
+ zips.slice(0, 8).forEach(r => console.log(
+ ` ${r.zip} ${String(r.permits).padStart(5)} permits/180d ${money(r.value).padStart(15)} ${String(r.local_contractors).padStart(4)} local contractors → ${r.permits_per_contractor ?? 'n/a'} permits/contractor`));
+ console.log(`\n CSVs → tmp/leads/contractor-intel-classes-${stamp}.csv, contractor-intel-zips-${stamp}.csv\n`);
+}
+
+main().catch(e => { console.error('contractor-intel error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
← 00f8def yoloforever c5 FIX (Cody): gate teardown bonus on valuation>
·
back to La Socrata Ingester
·
yoloforever c6 FIX (Cody): drop invalid per-ZIP ratio, add v 4aad896 →