chore: broker-extract helper + two-section pool/land report, v0.25.1 (session close)

68140d847e1944aee97c3331f464542c9c95086c · 2026-08-26 10:06:01 -0700 · Steve Abrams
Files touched

M package-lock.json
M package.json
M scripts/enrich-sfr-pools.js
A scripts/sfv-pool-land-report.js
Diff

commit 68140d847e1944aee97c3331f464542c9c95086c
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 26 10:06:01 2026 -0700

    chore: broker-extract helper + two-section pool/land report, v0.25.1 (session close)
---
 package-lock.json               |  4 +--
 package.json                    |  2 +-
 scripts/enrich-sfr-pools.js     |  9 ++++++
 scripts/sfv-pool-land-report.js | 71 +++++++++++++++++++++++++++++++++++++++++
 4 files changed, 83 insertions(+), 3 deletions(-)

diff --git a/package-lock.json b/package-lock.json
index bff47df..ba20102 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -1,12 +1,12 @@
 {
   "name": "commercialrealestate",
-  "version": "0.25.0",
+  "version": "0.25.1",
   "lockfileVersion": 3,
   "requires": true,
   "packages": {
     "": {
       "name": "commercialrealestate",
-      "version": "0.25.0",
+      "version": "0.25.1",
       "dependencies": {
         "better-sqlite3": "^12.11.1",
         "express": "^4.22.2",
diff --git a/package.json b/package.json
index 3960204..9fd9a67 100644
--- a/package.json
+++ b/package.json
@@ -1,6 +1,6 @@
 {
   "name": "commercialrealestate",
-  "version": "0.25.0",
+  "version": "0.25.1",
   "private": true,
   "description": "LA County CRE investment explorer — multi-firm sourced, Census + assessor enriched, Qwen-analyzed",
   "scripts": {
diff --git a/scripts/enrich-sfr-pools.js b/scripts/enrich-sfr-pools.js
index b7a7120..e206135 100644
--- a/scripts/enrich-sfr-pools.js
+++ b/scripts/enrich-sfr-pools.js
@@ -64,6 +64,15 @@ function extractLot(html) {
   return { lot_size: display, lot_sqft: sqft };
 }
 
+// Primary LISTING BROKER + agent from the MLS record Redfin surfaces (the primary listing broker who
+// holds the listing — what Steve wants over aggregator attribution). Structured JSON fields.
+function extractBroker(html) {
+  const b = html.match(/"listingBrokerName\\?":\s*\\?"([^"\\]{2,80})/i) || html.match(/"brokerName\\?":\s*\\?"([^"\\]{2,80})/i);
+  const a = html.match(/"listingAgentName\\?":\s*\\?"([^"\\]{2,60})/i) || html.match(/"agentName\\?":\s*\\?"([^"\\]{2,60})/i);
+  const clean = s => s ? s.replace(/\\u0026/g,'&').replace(/\\u002c/g,',').trim() : null;
+  return { broker: clean(b && b[1]), agent: clean(a && a[1]) };
+}
+
 // Primary listing photo from og:image (falls back to the first cdn-redfin bigphoto).
 function extractPhoto(html) {
   const og = html.match(/property=\\?"og:image\\?"[^>]*content=\\?"(https:\/\/[^"\\ >]{20,160})/i)
diff --git a/scripts/sfv-pool-land-report.js b/scripts/sfv-pool-land-report.js
new file mode 100644
index 0000000..365332d
--- /dev/null
+++ b/scripts/sfv-pool-land-report.js
@@ -0,0 +1,71 @@
+#!/usr/bin/env node
+// sfv-pool-land-report.js — one email, two sections for SFV $700k–$1.2M active SFR:
+//   A) ALL homes WITH a pool
+//   B) homes WITHOUT a pool that have >= LAND_MIN (default 7,500) sq ft of LOT (room to add one)
+// Each row: photo, price, $/sqft, beds/baths, interior sqft, lot size. HTML → stdout (sent by runner).
+'use strict';
+const { Pool } = require('pg');
+const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
+const SFV_CITIES = ['Encino','Tarzana','Woodland Hills','Sherman Oaks','Van Nuys','North Hollywood',
+  'Studio City','Reseda','Northridge','Granada Hills','Canoga Park','Winnetka','West Hills','Chatsworth',
+  'Panorama City','Sun Valley','Valley Village','Valley Glen','North Hills','Porter Ranch','Arleta',
+  'Pacoima','Sylmar','Mission Hills','Lake Balboa','Toluca Lake','Sunland','Tujunga','Shadow Hills'];
+const PMIN = 700000, PMAX = 1200000, LAND_MIN = +(process.env.LAND_MIN || 7500);
+const CRCP_URL = 'https://crcp.agentabrams.com/mls.html?shortlist=sfv-pool';
+const DETAILS_URL = 'https://crcp.agentabrams.com/sfv-pool.html';
+const esc = s => String(s ?? '').replace(/[&<>"]/g, c => ({ '&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;' }[c]));
+const money = n => '$' + Number(n).toLocaleString();
+const TH = t => `<th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase;text-align:left">${t}</th>`;
+const TD = (v, e = '') => `<td style="padding:6px 8px;border-bottom:1px solid #20262f;${e}">${v}</td>`;
+
+function table(rows) {
+  if (!rows.length) return `<div style="color:#8b949e;font-size:13px;padding:8px">None.</div>`;
+  const head = `<tr style="color:#8b949e">${TH('')}${TH('Address')}${TH('City')}${TH('Price')}${TH('$/sqft')}${TH('Bd/Ba')}${TH('Sqft')}${TH('Lot')}</tr>`;
+  const body = rows.map(r => {
+    const a = r.source ? `<a href="${esc(r.source)}" style="color:#58a6ff;text-decoration:none">${esc(r.address)}</a>` : esc(r.address);
+    const ppsf = (r.price && r.sqft) ? '$' + Math.round(r.price / r.sqft).toLocaleString() : '—';
+    const lot = r.lot_size ? esc(r.lot_size).replace(/ square feet/i, ' sf').replace(/ Sq\.? ?Ft\.?/i, ' sf') : (r.lot_sqft ? Number(r.lot_sqft).toLocaleString() + ' sf' : '—');
+    const img = r.photo_url
+      ? `<a href="${esc(r.source||'#')}"><img src="${esc(r.photo_url)}" width="72" height="52" alt="" style="width:72px;height:52px;object-fit:cover;border-radius:6px;display:block;border:1px solid #2a313c"></a>`
+      : '<div style="width:72px;height:52px;border-radius:6px;background:#20262f"></div>';
+    return `<tr>${TD(img,'width:72px')}${TD(a)}${TD(esc(r.city))}${TD(money(r.price))}${TD(ppsf)}${TD(esc(r.beds)+'/'+esc(r.baths))}${TD(r.sqft?Number(r.sqft).toLocaleString():'—')}${TD(lot)}</tr>`;
+  }).join('');
+  return `<table style="width:100%;border-collapse:collapse;font-size:13px">${head}${body}</table>`;
+}
+
+(async () => {
+  const cities = SFV_CITIES.map(c => `'${c.replace(/'/g, "''")}'`).join(',');
+  const base = `FROM sfr s LEFT JOIN sfr_pool p ON p.id = s.id
+    WHERE s.status='active' AND s.price::int BETWEEN ${PMIN} AND ${PMAX} AND s.city IN (${cities})`;
+  const cols = `s.id, s.address, s.city, s.price::int price, s.beds, s.baths, s.sqft, s.source, p.lot_size, p.lot_sqft, p.photo_url`;
+  const withPool = (await pool.query(`SELECT ${cols} ${base} AND p.has_pool IS TRUE ORDER BY s.price::int DESC`)).rows;
+  const noPoolLand = (await pool.query(`SELECT ${cols} ${base} AND p.has_pool IS FALSE AND p.lot_sqft >= ${LAND_MIN} ORDER BY p.lot_sqft DESC`)).rows;
+  await pool.end();
+
+  const D = new Date().toISOString().slice(0, 10);
+  const stat = (n, label, color) => `<td style="padding:14px 16px;background:#161b22;border:1px solid #2a313c;border-radius:10px;text-align:center">
+    <div style="font-size:26px;font-weight:700;color:${color}">${n.toLocaleString()}</div>
+    <div style="font-size:11px;color:#8b949e;text-transform:uppercase;letter-spacing:.5px;margin-top:2px">${label}</div></td>`;
+
+  const html = `<!doctype html><html><body style="margin:0;background:#0e1116;color:#e6edf3;font-family:-apple-system,Helvetica,Arial,sans-serif;padding:22px">
+  <div style="max-width:860px;margin:0 auto">
+    <h1 style="font-size:20px;margin:0 0 3px">🏊 SFV $700k–$1.2M — Pools + Big-Lot Options <span style="color:#3fb950">· ${D}</span></h1>
+    <div style="color:#8b949e;font-size:12px;margin-bottom:16px">San Fernando Valley · active single-family ·
+      <a href="${CRCP_URL}" style="color:#58a6ff">open on CRCP (checked off) →</a> · <a href="${DETAILS_URL}" style="color:#58a6ff">photo view →</a></div>
+    <table style="width:100%;border-spacing:8px;margin:-8px 0 18px"><tr>
+      ${stat(withPool.length, '🏊 With Pool', '#3fb950')}
+      ${stat(noPoolLand.length, `No Pool · Lot ≥ ${LAND_MIN.toLocaleString()} sf`, '#58a6ff')}
+    </tr></table>
+
+    <h3 style="font-size:14px;text-transform:uppercase;color:#3fb950;margin:20px 0 6px">🏊 With Pool — all ${withPool.length} <span style="color:#8b949e;font-weight:400">(by price)</span></h3>
+    ${table(withPool)}
+
+    <h3 style="font-size:14px;text-transform:uppercase;color:#58a6ff;margin:26px 0 6px">🏡 No Pool · Lot ≥ ${LAND_MIN.toLocaleString()} sq ft — all ${noPoolLand.length} <span style="color:#8b949e;font-weight:400">(by lot size, room to add a pool)</span></h3>
+    ${table(noPoolLand)}
+
+    <div style="color:#8b949e;font-size:11px;margin-top:22px;border-top:1px solid #2a313c;padding-top:10px">
+      Pool + lot enriched from each Redfin listing's structured fields. "With Pool" = confirmed POOL_FEATURES.
+      "No Pool · Lot ≥ ${LAND_MIN.toLocaleString()} sf" = no pool listed AND lot size at or above the threshold. Price band $700k–$1.2M, ${SFV_CITIES.length} SFV cities.</div>
+  </div></body></html>`;
+  process.stdout.write(html);
+})().catch(e => { console.error(e); process.exit(1); });
← 28b3bb2 sfv-pool map: Zillow-style price pins colored by pool + keyl · back to Commercialrealestate · corner-nav: reserve header corner space in CSS (timing-indep 3c0d974 →