← back to Commercialrealestate
SFV $700k-$1.2M pool split: enrichment + /api/sfv-pool + details page + email report
548c019ca8d5e1ae9e870e6ef0c9c9ceb1ab8833 · 2026-08-26 08:26:42 -0700 · Steve Abrams
- enrich-sfr-pools.js: real-Chrome scrape of Redfin POOL_FEATURES into cre.sfr_pool (gis-csv feed has no pool data); resumable/incremental
- /api/sfv-pool endpoint (DB-first + snapshot fallback), 3-way with/without/pending split
- public/sfv-pool.html details page: pool + city filters, sort + density controls (standing rule)
- sfv-pool-report.js + run-sfv-pool-report.sh: the digest email to Steve's inboxes
Files touched
A public/sfv-pool.htmlA scripts/enrich-sfr-pools.jsA scripts/run-sfv-pool-report.shM scripts/serve.jsA scripts/sfv-pool-report.js
Diff
commit 548c019ca8d5e1ae9e870e6ef0c9c9ceb1ab8833
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 26 08:26:42 2026 -0700
SFV $700k-$1.2M pool split: enrichment + /api/sfv-pool + details page + email report
- enrich-sfr-pools.js: real-Chrome scrape of Redfin POOL_FEATURES into cre.sfr_pool (gis-csv feed has no pool data); resumable/incremental
- /api/sfv-pool endpoint (DB-first + snapshot fallback), 3-way with/without/pending split
- public/sfv-pool.html details page: pool + city filters, sort + density controls (standing rule)
- sfv-pool-report.js + run-sfv-pool-report.sh: the digest email to Steve's inboxes
---
public/sfv-pool.html | 168 +++++++++++++++++++++++++++++++++++++++++
scripts/enrich-sfr-pools.js | 111 +++++++++++++++++++++++++++
scripts/run-sfv-pool-report.sh | 32 ++++++++
scripts/serve.js | 56 ++++++++++++++
scripts/sfv-pool-report.js | 86 +++++++++++++++++++++
5 files changed, 453 insertions(+)
diff --git a/public/sfv-pool.html b/public/sfv-pool.html
new file mode 100644
index 0000000..7acf9d8
--- /dev/null
+++ b/public/sfv-pool.html
@@ -0,0 +1,168 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>SFV Homes $700k–$1.2M · Pool Filter</title>
+<style>
+ :root { --cols: 3; }
+ * { box-sizing: border-box; }
+ body { margin:0; background:#0e1116; color:#e6edf3;
+ font-family:-apple-system,BlinkMacSystemFont,Helvetica,Arial,sans-serif; padding:20px; }
+ .wrap { max-width:1400px; margin:0 auto; }
+ h1 { font-size:20px; margin:0 0 3px; }
+ .sub { color:#8b949e; font-size:12px; margin-bottom:16px; }
+ .sub a { color:#58a6ff; }
+ /* headline stats */
+ .stats { display:flex; gap:8px; flex-wrap:wrap; margin:0 0 16px; }
+ .stat { flex:1 1 120px; padding:12px 14px; background:#161b22; border:1px solid #2a313c; border-radius:10px; text-align:center; }
+ .stat .n { font-size:24px; font-weight:700; }
+ .stat .l { font-size:11px; color:#8b949e; text-transform:uppercase; letter-spacing:.5px; margin-top:2px; }
+ .stat.pool .n { color:#3fb950; } .stat.nopool .n { color:#58a6ff; } .stat.pending .n { color:#8b949e; }
+ /* controls */
+ .controls { display:flex; gap:14px; flex-wrap:wrap; align-items:center; background:#161b22;
+ border:1px solid #2a313c; border-radius:10px; padding:12px 14px; margin-bottom:16px; }
+ .controls label { font-size:11px; color:#8b949e; text-transform:uppercase; letter-spacing:.5px; margin-right:6px; }
+ .seg { display:inline-flex; border:1px solid #2a313c; border-radius:8px; overflow:hidden; }
+ .seg button { background:#0e1116; color:#c9d1d9; border:0; padding:7px 12px; font-size:13px; cursor:pointer; }
+ .seg button.on { background:#238636; color:#fff; }
+ .seg button.on[data-pool="false"] { background:#1f6feb; }
+ .seg button.on[data-pool="null"] { background:#57606a; }
+ select, input[type=range] { background:#0e1116; color:#e6edf3; border:1px solid #2a313c; border-radius:8px; padding:6px 8px; font-size:13px; }
+ input[type=range] { padding:0; vertical-align:middle; }
+ .grid { display:grid; grid-template-columns:repeat(var(--cols), minmax(0,1fr)); gap:12px; }
+ .card { background:#161b22; border:1px solid #2a313c; border-radius:10px; padding:12px 13px; display:flex; flex-direction:column; gap:5px; }
+ .card .addr { font-size:14px; font-weight:600; }
+ .card .addr a { color:#58a6ff; text-decoration:none; }
+ .card .price { font-size:18px; font-weight:700; color:#e6edf3; }
+ .card .meta { font-size:12px; color:#8b949e; }
+ .badge { display:inline-block; font-size:10px; font-weight:700; text-transform:uppercase; letter-spacing:.4px;
+ padding:2px 7px; border-radius:20px; }
+ .badge.pool { background:rgba(63,185,80,.15); color:#3fb950; border:1px solid rgba(63,185,80,.4); }
+ .badge.nopool { background:rgba(88,166,255,.12); color:#58a6ff; border:1px solid rgba(88,166,255,.35); }
+ .badge.pending { background:rgba(139,148,158,.12); color:#8b949e; border:1px solid rgba(139,148,158,.35); }
+ .feat { font-size:11px; color:#7a828c; }
+ .empty { color:#8b949e; padding:30px; text-align:center; }
+ .foot { color:#6e7681; font-size:11px; margin-top:20px; border-top:1px solid #2a313c; padding-top:10px; }
+</style>
+</head>
+<body>
+<div class="wrap">
+ <h1>🏊 San Fernando Valley Homes · $700k – $1.2M</h1>
+ <div class="sub">Active single-family listings, split by swimming pool ·
+ <a href="/mls.html">all LA listings →</a> · <span id="src"></span></div>
+
+ <div class="stats" id="stats"></div>
+
+ <div class="controls">
+ <div><label>Pool</label>
+ <span class="seg" id="poolSeg">
+ <button data-pool="all" class="on">All</button>
+ <button data-pool="true">🏊 With Pool</button>
+ <button data-pool="false">No Pool</button>
+ <button data-pool="null">Pending</button>
+ </span>
+ </div>
+ <div><label>City</label>
+ <select id="city"><option value="">All SFV</option></select>
+ </div>
+ <div><label>Sort</label>
+ <select id="sort">
+ <option value="price_desc">Price ↓</option>
+ <option value="price_asc">Price ↑</option>
+ <option value="dom_asc">Newest (DOM)</option>
+ <option value="ppsf_asc">$/sqft ↑</option>
+ <option value="sqft_desc">Sqft ↓</option>
+ <option value="city_az">City A→Z</option>
+ </select>
+ </div>
+ <div><label>Density</label>
+ <input type="range" id="density" min="2" max="6" step="1" value="3">
+ </div>
+ </div>
+
+ <div class="grid" id="grid"><div class="empty">Loading…</div></div>
+ <div class="foot" id="foot"></div>
+</div>
+
+<script>
+const KEY = 'sfvpool';
+const st = { pool:'all', city:'', sort: localStorage.getItem(KEY+':sort')||'price_desc',
+ cols:+(localStorage.getItem(KEY+':cols')||3) };
+let DATA = { withPool:[], withoutPool:[], unknown:[] };
+const $ = s => document.querySelector(s);
+const money = n => n>=1000 ? '$'+(+n).toLocaleString() : '—';
+
+function allRows() {
+ const tag = (arr,t)=>arr.map(r=>({...r, _pool:t}));
+ return [...tag(DATA.withPool,'true'), ...tag(DATA.withoutPool,'false'), ...tag(DATA.unknown,'null')];
+}
+function poolBadge(t){ return t==='true' ? '<span class="badge pool">🏊 Pool</span>'
+ : t==='false' ? '<span class="badge nopool">No Pool</span>'
+ : '<span class="badge pending">Pool: pending</span>'; }
+
+function render() {
+ let rows = allRows();
+ if (st.pool !== 'all') rows = rows.filter(r => r._pool === st.pool);
+ if (st.city) rows = rows.filter(r => r.city === st.city);
+ const ppsf = r => (r.sqft>0 ? r.price/r.sqft : Infinity);
+ const S = st.sort;
+ rows.sort((a,b) =>
+ S==='price_asc' ? a.price-b.price :
+ S==='dom_asc' ? (a.days_on_market||999)-(b.days_on_market||999) :
+ S==='ppsf_asc' ? ppsf(a)-ppsf(b) :
+ S==='sqft_desc' ? (b.sqft||0)-(a.sqft||0) :
+ S==='city_az' ? (a.city||'').localeCompare(b.city||'') || b.price-a.price :
+ b.price-a.price);
+ document.documentElement.style.setProperty('--cols', st.cols);
+ const g = $('#grid');
+ if (!rows.length) { g.innerHTML = '<div class="empty">No listings match this filter.</div>'; }
+ else g.innerHTML = rows.map(r => {
+ const pp = r.sqft>0 ? '$'+Math.round(r.price/r.sqft).toLocaleString()+'/sqft' : '';
+ const feat = r._pool==='true' && r.pool_features ? `<div class="feat">${r.pool_features}</div>` : '';
+ return `<div class="card">
+ <div class="addr"><a href="${r.source||'#'}" target="_blank" rel="noopener noreferrer">${r.address||'—'}</a></div>
+ <div class="price">${money(r.price)}</div>
+ <div class="meta">${r.city} · ${r.beds||'?'} bd / ${r.baths||'?'} ba · ${r.sqft?(+r.sqft).toLocaleString()+' sqft':'—'} ${pp?'· '+pp:''} · ${r.days_on_market!=null?r.days_on_market+'d':''}</div>
+ <div>${poolBadge(r._pool)}</div>${feat}
+ </div>`;
+ }).join('');
+ $('#foot').textContent = `${rows.length} shown · pool status enriched from Redfin listing detail (structured POOL_FEATURES). "Pending" = detail not yet checked or field absent.`;
+}
+
+function paintStats() {
+ const c = { p: DATA.withPool.length, n: DATA.withoutPool.length, u: DATA.unknown.length };
+ const t = c.p + c.n + c.u;
+ $('#stats').innerHTML =
+ `<div class="stat"><div class="n">${t.toLocaleString()}</div><div class="l">Total $700k–1.2M</div></div>
+ <div class="stat pool"><div class="n">${c.p.toLocaleString()}</div><div class="l">🏊 With Pool</div></div>
+ <div class="stat nopool"><div class="n">${c.n.toLocaleString()}</div><div class="l">No Pool</div></div>
+ <div class="stat pending"><div class="n">${c.u.toLocaleString()}</div><div class="l">Pool Pending</div></div>`;
+}
+
+async function load() {
+ const r = await fetch('/api/sfv-pool');
+ const j = await r.json();
+ DATA = { withPool:j.withPool||[], withoutPool:j.withoutPool||[], unknown:j.unknown||[] };
+ $('#src').textContent = 'source: ' + (j.source||'?');
+ // populate city dropdown
+ const cities = [...new Set(allRows().map(r=>r.city))].sort();
+ $('#city').insertAdjacentHTML('beforeend', cities.map(c=>`<option value="${c}">${c}</option>`).join(''));
+ paintStats(); render();
+}
+
+// wire controls
+$('#poolSeg').addEventListener('click', e => {
+ if (e.target.tagName!=='BUTTON') return;
+ st.pool = e.target.dataset.pool;
+ [...$('#poolSeg').children].forEach(b=>b.classList.toggle('on', b===e.target));
+ render();
+});
+$('#city').addEventListener('change', e => { st.city = e.target.value; render(); });
+$('#sort').addEventListener('change', e => { st.sort = e.target.value; localStorage.setItem(KEY+':sort', st.sort); render(); });
+$('#density').addEventListener('input', e => { st.cols = +e.target.value; localStorage.setItem(KEY+':cols', st.cols); render(); });
+$('#sort').value = st.sort; $('#density').value = st.cols;
+load();
+</script>
+</body>
+</html>
diff --git a/scripts/enrich-sfr-pools.js b/scripts/enrich-sfr-pools.js
new file mode 100644
index 0000000..c9b869d
--- /dev/null
+++ b/scripts/enrich-sfr-pools.js
@@ -0,0 +1,111 @@
+#!/usr/bin/env node
+// enrich-sfr-pools.js — pool enrichment for the SFV $700k–$1.2M active SFR set.
+//
+// WHY: the Redfin gis-csv feed we ingest into cre.sfr carries NO pool/amenity data (price/beds/baths/
+// sqft only). Pool status lives ONLY on each listing's Redfin DETAIL page, in an embedded structured
+// amenity blob: {"amenityName":"Pool Features","referenceName":"POOL_FEATURES","amenityValues":[...]}
+//
+// TRANSPORT: plain curl/fetch is soft-blocked by Redfin after ~8 rapid hits (200s go empty). So we drive
+// ONE warmed real-Chrome session (playwright-core + the installed Chrome app + the AutomationControlled
+// evasion flag) — the SAME transport fetch-sfr-redfin.js uses to beat Redfin's anti-bot. Run it with the
+// browserbase skill's node_modules on NODE_PATH (that's where playwright-core lives):
+// NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/enrich-sfr-pools.js
+//
+// We read the STRUCTURED POOL_FEATURES field (NOT free-text "swimming pool", which false-positives on
+// "community pool" / reviews) → has_pool = true / false / null(unknown: detail page had no such field).
+// Caches to cre.sfr_pool + data/sfr-pools.json (prod snapshot). Idempotent + resumable: only (re)checks
+// rows not seen within FRESH_DAYS, so it fills the band incrementally across scheduled runs.
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { Pool } = require('pg');
+const { chromium } = require('playwright-core');
+
+const ROOT = path.join(__dirname, '..');
+const CHROME_PATH = process.env.CHROME_PATH || '/Applications/Google Chrome.app/Contents/MacOS/Google Chrome';
+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 PRICE_MIN = +(process.env.PRICE_MIN || 700000);
+const PRICE_MAX = +(process.env.PRICE_MAX || 1200000);
+const CAP = +(process.env.CAP || 1500);
+const FRESH_DAYS = +(process.env.FRESH_DAYS || 21);
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+// Parse the structured POOL_FEATURES amenity. true=real pool value, false=explicit None, null=field absent.
+function classifyPool(html) {
+ const m = html.match(/"amenityName\\?":\\?"Pool Features\\?"[^}]*?"amenityValues\\?":\\?\[([^\]]*)\]/i)
+ || html.match(/POOL_FEATURES\\?"[^}]*?"amenityValues\\?":\\?\[([^\]]*)\]/i);
+ if (!m) return { hasPool: null, features: null };
+ const raw = m[1].replace(/\\"/g, '"');
+ const vals = (raw.match(/"([^"]+)"/g) || []).map(s => s.replace(/"/g, '').trim()).filter(Boolean);
+ const features = vals.join(', ') || null;
+ const joined = vals.join(' ').toLowerCase();
+ const positive = /(in ground|above ground|private|community|association|lap|infinity|heated|gunite|fenced|salt|solar|pool\/spa|pool spa)/.test(joined);
+ const negative = vals.length === 0 || (/\bnone\b/.test(joined) && !positive) || /^no\b/.test(joined.trim());
+ return { hasPool: positive ? true : (negative ? false : (vals.length ? true : null)), features };
+}
+
+async function main() {
+ await pool.query(`CREATE TABLE IF NOT EXISTS sfr_pool (
+ id text PRIMARY KEY, has_pool boolean, pool_features text, http_status int,
+ checked_at timestamptz NOT NULL DEFAULT now())`);
+
+ const cities = SFV_CITIES.map(c => `'${c.replace(/'/g, "''")}'`).join(',');
+ const { rows } = await pool.query(
+ `SELECT s.id, s.address, s.city, s.source
+ FROM sfr s LEFT JOIN sfr_pool p ON p.id=s.id
+ WHERE s.status='active' AND s.price::int BETWEEN $1 AND $2 AND s.city IN (${cities})
+ AND s.source IS NOT NULL
+ AND (p.id IS NULL OR p.has_pool IS NULL OR p.checked_at < now() - ($3 || ' days')::interval)
+ ORDER BY (p.id IS NULL) DESC, (p.has_pool IS NULL) DESC, s.price::int DESC
+ LIMIT $4`, [PRICE_MIN, PRICE_MAX, FRESH_DAYS, CAP]);
+ console.log(`[enrich-pools] real-Chrome; ${rows.length} listings to (re)check`);
+ if (!rows.length) { await snapshot(); await pool.end(); return; }
+
+ const browser = await chromium.launch({ executablePath: CHROME_PATH, args: ['--disable-blink-features=AutomationControlled'] });
+ const ctx = await browser.newContext({ userAgent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36', viewport: { width: 1440, height: 1000 } });
+ const page = await ctx.newPage();
+ // warm the session (get Redfin cookies before hammering detail pages)
+ await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles', { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(2500);
+
+ let done = 0, withPool = 0, without = 0, unknown = 0, failed = 0, blockedStreak = 0;
+ for (const row of rows) {
+ let hasPool = null, features = null, status = 0;
+ try {
+ const resp = await page.goto(row.source, { waitUntil: 'domcontentloaded', timeout: 25000 });
+ status = resp ? resp.status() : 0;
+ await page.waitForTimeout(400);
+ const html = await page.content();
+ if (html && html.length > 40000) { // real listing page (>40KB); stripped blocks are tiny
+ const c = classifyPool(html); hasPool = c.hasPool; features = c.features; blockedStreak = 0;
+ } else { blockedStreak++; } // soft-block / empty
+ } catch (_) { failed++; blockedStreak++; }
+ await pool.query(
+ `INSERT INTO sfr_pool(id,has_pool,pool_features,http_status,checked_at) VALUES($1,$2,$3,$4,now())
+ ON CONFLICT (id) DO UPDATE SET has_pool=$2, pool_features=$3, http_status=$4, checked_at=now()`,
+ [row.id, hasPool, features, status || null]);
+ hasPool === true ? withPool++ : hasPool === false ? without++ : unknown++;
+ done++;
+ if (done % 25 === 0) { console.log(`[enrich-pools] ${done}/${rows.length} pool:${withPool} no:${without} unk:${unknown} fail:${failed}`); await snapshot(); }
+ // if Redfin starts soft-blocking (many empty pages in a row), back off hard then keep trying
+ if (blockedStreak >= 6) { console.log(`[enrich-pools] soft-block streak ${blockedStreak} — backing off 60s`); await sleep(60000); blockedStreak = 0; }
+ else await sleep(1200 + Math.floor(Math.random() * 1600)); // 1.2–2.8s between pages
+ }
+ await browser.close();
+ await snapshot();
+ console.log(`[enrich-pools] DONE checked:${done} withPool:${withPool} without:${without} unknown:${unknown} failed:${failed}`);
+ await pool.end();
+}
+
+async function snapshot() {
+ const snap = (await pool.query(`SELECT id, has_pool, pool_features, checked_at FROM sfr_pool`)).rows;
+ fs.mkdirSync(path.join(ROOT, 'data'), { recursive: true });
+ fs.writeFileSync(path.join(ROOT, 'data', 'sfr-pools.json'),
+ JSON.stringify({ updated_at: new Date().toISOString(), pools: snap }, null, 0));
+}
+main().catch(e => { console.error('[enrich-pools] FATAL', e); process.exit(1); });
diff --git a/scripts/run-sfv-pool-report.sh b/scripts/run-sfv-pool-report.sh
new file mode 100755
index 0000000..34e4f3b
--- /dev/null
+++ b/scripts/run-sfv-pool-report.sh
@@ -0,0 +1,32 @@
+#!/bin/bash
+# run-sfv-pool-report.sh — build + email the "SFV Homes $700k–$1.2M · with & without pool" digest to
+# steve-office + steve-personal (Steve's own inboxes — NOT a list). Local + free ($0). Mirrors
+# run-residential-report.sh. Optional launchd: com.steve.crcp-sfv-pool-report (see docs).
+set -uo pipefail
+cd "$HOME/Projects/commercialrealestate" || exit 1
+export PATH="/usr/local/bin:/opt/homebrew/bin:$PATH"
+LOG=tmp/sfv-pool-report.log
+mkdir -p tmp
+D="$(date +%Y-%m-%d)"
+echo "===== [$(date '+%F %T')] sfv-pool report =====" >> "$LOG"
+
+node scripts/sfv-pool-report.js > data/sfv-pool-report.html 2>>"$LOG"
+BYTES=$(wc -c < data/sfv-pool-report.html 2>/dev/null || echo 0)
+if [ "$BYTES" -lt 2000 ]; then
+ echo "[$(date '+%T')] ABORT — report build too small ($BYTES bytes); not sending." >> "$LOG"; exit 2
+fi
+
+. "$HOME/.claude/skills/_shared/george-send.sh"; george_resolve_cfg
+TO="${SFV_POOL_REPORT_TO:-steve@designerwallcoverings.com, steveabramsdesigns@gmail.com}"
+# headline counts into the subject (with-pool / no-pool)
+WP=$(grep -oE 'color:#3fb950">[0-9,]+' data/sfv-pool-report.html | head -1 | grep -oE '[0-9,]+' || echo '')
+NP=$(grep -oE 'color:#58a6ff">[0-9,]+' data/sfv-pool-report.html | head -1 | grep -oE '[0-9,]+' || echo '')
+SUBJ="🏊 SFV Homes \$700k–\$1.2M — ${WP:-?} with pool · ${NP:-?} without ($D)"
+
+jq -Rn --arg account steve-office --arg to "$TO" --arg subject "$SUBJ" --rawfile body data/sfv-pool-report.html \
+ '{account:$account,to:$to,subject:$subject,body:$body}' > tmp/sfv-pool-payload.json
+RESP="$(curl -sS --max-time 45 "$GEORGE_URL$GEORGE_PFX/api/send" \
+ -H "Authorization: Basic $GEORGE_BASIC_AUTH" -H "Content-Type: application/json" --data @tmp/sfv-pool-payload.json)"
+echo "$RESP" | grep -q '"success":true' && echo "[$(date '+%T')] sent -> $TO" >> "$LOG" \
+ || echo "[$(date '+%T')] SEND FAILED: $RESP" >> "$LOG"
+echo "$RESP"
diff --git a/scripts/serve.js b/scripts/serve.js
index cd0a49d..4c65c2b 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -1276,6 +1276,62 @@ app.get('/api/residential', async (req, res) => {
}
});
+// SFV $700k–$1.2M active SFR, split by swimming pool. Pool status is enriched offline from Redfin detail
+// pages into cre.sfr_pool (see scripts/enrich-sfr-pools.js) — the gis-csv feed has none. has_pool is
+// true / false / null(unknown: detail page carried no structured POOL_FEATURES field). DB-first with a
+// snapshot fallback (data/sfr-redfin.json + data/sfr-pools.json) so it also serves on prod without PG.
+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 SFV_SET = new Set(SFV_CITIES.map(c => c.toLowerCase()));
+const SFV_PRICE_MIN = 700000, SFV_PRICE_MAX = 1200000;
+app.get('/api/sfv-pool', async (req, res) => {
+ const bucket = r => r.has_pool === true ? 'withPool' : r.has_pool === false ? 'withoutPool' : 'unknown';
+ const shape = r => ({ id: r.id, address: r.address, city: r.city, zip: r.zip, price: +r.price,
+ beds: r.beds, baths: r.baths, sqft: r.sqft, year_built: r.year_built, days_on_market: r.days_on_market,
+ source: r.source, lat: r.lat, lng: r.lng, has_pool: (r.has_pool === true || r.has_pool === false) ? r.has_pool : null,
+ pool_features: r.pool_features || null });
+ // 1) live DB
+ if (brokerdb) {
+ try {
+ const r = await brokerdb.pool.query(
+ `SELECT s.id, s.address, s.city, s.zip, s.price, s.beds, s.baths, s.sqft, s.year_built,
+ s.days_on_market, s.source, s.lat, s.lng, p.has_pool, p.pool_features
+ FROM sfr s LEFT JOIN sfr_pool p ON p.id = s.id
+ WHERE s.status='active' AND s.price::int BETWEEN $1 AND $2
+ AND lower(s.city) = ANY($3::text[])
+ ORDER BY s.price::int DESC`,
+ [SFV_PRICE_MIN, SFV_PRICE_MAX, Array.from(SFV_SET)]);
+ if (r.rows.length) {
+ const out = { withPool: [], withoutPool: [], unknown: [] };
+ r.rows.forEach(row => out[bucket(row)].push(shape(row)));
+ return res.json({ ...out, counts: { withPool: out.withPool.length, withoutPool: out.withoutPool.length,
+ unknown: out.unknown.length, total: r.rows.length }, priceBand: [SFV_PRICE_MIN, SFV_PRICE_MAX],
+ cities: SFV_CITIES, source: 'db' });
+ }
+ } catch (_) { /* fall through */ }
+ }
+ // 2) snapshot fallback (prod): join sfr-redfin.json with sfr-pools.json
+ try {
+ const { sfr = [] } = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'sfr-redfin.json'), 'utf8'));
+ let pools = {};
+ try { (JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'sfr-pools.json'), 'utf8')).pools || [])
+ .forEach(p => { pools[p.id] = p; }); } catch (_) {}
+ const out = { withPool: [], withoutPool: [], unknown: [] };
+ sfr.filter(r => r.status === 'active' && +r.price >= SFV_PRICE_MIN && +r.price <= SFV_PRICE_MAX
+ && SFV_SET.has(String(r.city).toLowerCase()))
+ .sort((a, b) => +b.price - +a.price)
+ .forEach(r => { const pr = pools[r.id] || {}; const row = { ...r, has_pool: pr.has_pool, pool_features: pr.pool_features };
+ out[bucket(row)].push(shape(row)); });
+ return res.json({ ...out, counts: { withPool: out.withPool.length, withoutPool: out.withoutPool.length,
+ unknown: out.unknown.length, total: out.withPool.length + out.withoutPool.length + out.unknown.length },
+ priceBand: [SFV_PRICE_MIN, SFV_PRICE_MAX], cities: SFV_CITIES, source: 'snapshot' });
+ } catch (e) {
+ return res.json({ withPool: [], withoutPool: [], unknown: [], counts: {}, error: String(e.message).split('\n')[0] });
+ }
+});
+
// Aggregated stats over the REAL scraped condo listings (cre.condo) — warrantable-vs-unwarranted
// breakdown + by-city, driving the graphics. Distinct from /api/warrantability (which charts the HUD
// FHA reference LIST, not the actual for-sale inventory we scraped).
diff --git a/scripts/sfv-pool-report.js b/scripts/sfv-pool-report.js
new file mode 100644
index 0000000..b42971a
--- /dev/null
+++ b/scripts/sfv-pool-report.js
@@ -0,0 +1,86 @@
+#!/usr/bin/env node
+// sfv-pool-report.js — build the "SFV Homes $700k–$1.2M · with & without pool" email (HTML → stdout).
+// Queries cre.sfr joined to cre.sfr_pool (pool status enriched by enrich-sfr-pools.js). Split into
+// 🏊 With Pool / No Pool / Pending, each a price-sorted table linking to Redfin, plus a big button to the
+// filterable details page (crcp.agentabrams.com/sfv-pool.html). Sends via run-sfv-pool-report.sh (George).
+'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;
+const DETAILS_URL = process.env.SFV_POOL_URL || 'https://crcp.agentabrams.com/sfv-pool.html';
+const TOP = +(process.env.TOP || 30); // rows per section in the email
+const esc = s => String(s ?? '').replace(/[&<>"]/g, c => ({ '&':'&','<':'<','>':'>','"':'"' }[c]));
+const money = n => '$' + Number(n).toLocaleString();
+
+function table(rows, kind) {
+ if (!rows.length) return `<div style="color:#8b949e;font-size:13px;padding:8px">None in this bucket right now.</div>`;
+ const featHead = kind === 'pool' ? '<th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">Pool</th>' : '';
+ const head = `<tr style="color:#8b949e;text-align:left">
+ <th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">Address</th>
+ <th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">City</th>
+ <th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">Price</th>
+ <th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">Bd/Ba</th>
+ <th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">Sqft</th>
+ <th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">DOM</th>${featHead}</tr>`;
+ const body = rows.slice(0, TOP).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 feat = kind === 'pool' ? `<td style="padding:6px 8px;border-bottom:1px solid #20262f;color:#3fb950;font-size:12px">${esc(r.pool_features || 'Yes')}</td>` : '';
+ return `<tr>
+ <td style="padding:6px 8px;border-bottom:1px solid #20262f">${a}</td>
+ <td style="padding:6px 8px;border-bottom:1px solid #20262f">${esc(r.city)}</td>
+ <td style="padding:6px 8px;border-bottom:1px solid #20262f">${money(r.price)}</td>
+ <td style="padding:6px 8px;border-bottom:1px solid #20262f">${esc(r.beds)}/${esc(r.baths)}</td>
+ <td style="padding:6px 8px;border-bottom:1px solid #20262f">${r.sqft ? Number(r.sqft).toLocaleString() : '—'}</td>
+ <td style="padding:6px 8px;border-bottom:1px solid #20262f">${r.days_on_market != null ? r.days_on_market + 'd' : ''}</td>${feat}</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 { rows } = await pool.query(
+ `SELECT s.id, s.address, s.city, s.price::int price, s.beds, s.baths, s.sqft, s.days_on_market,
+ s.source, p.has_pool, p.pool_features
+ FROM sfr s LEFT JOIN sfr_pool p ON p.id = s.id
+ WHERE s.status='active' AND s.price::int BETWEEN $1 AND $2 AND s.city IN (${cities})
+ ORDER BY s.price::int DESC`, [PMIN, PMAX]);
+ await pool.end();
+
+ const withPool = rows.filter(r => r.has_pool === true);
+ const noPool = rows.filter(r => r.has_pool === false);
+ const pending = rows.filter(r => r.has_pool == null);
+ 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:820px;margin:0 auto">
+ <h1 style="font-size:20px;margin:0 0 3px">🏊 SFV Homes $700k–$1.2M <span style="color:#3fb950">· ${D}</span></h1>
+ <div style="color:#8b949e;font-size:12px;margin-bottom:16px">San Fernando Valley · active single-family · split by swimming pool ·
+ <a href="${DETAILS_URL}" style="color:#58a6ff">see all + filter →</a></div>
+ <table style="width:100%;border-spacing:8px;margin:-8px 0 18px"><tr>
+ ${stat(rows.length, 'Total in band', '#e6edf3')}
+ ${stat(withPool.length, '🏊 With Pool', '#3fb950')}
+ ${stat(noPool.length, 'No Pool', '#58a6ff')}
+ ${stat(pending.length, 'Pool Pending', '#8b949e')}
+ </tr></table>
+ <div style="text-align:center;margin:0 0 22px">
+ <a href="${DETAILS_URL}" style="display:inline-block;background:#238636;color:#fff;text-decoration:none;font-weight:600;font-size:14px;padding:11px 22px;border-radius:8px">See every listing + pool / city / price filters →</a>
+ </div>
+ <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:18px 0 6px">🏊 With Pool <span style="color:#3fb950">(${withPool.length})</span> · top ${Math.min(TOP, withPool.length)} by price</h3>
+ ${table(withPool, 'pool')}
+ <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:22px 0 6px">🏠 Without Pool <span style="color:#58a6ff">(${noPool.length})</span> · top ${Math.min(TOP, noPool.length)} by price</h3>
+ ${table(noPool, 'nopool')}
+ <div style="color:#8b949e;font-size:11px;margin-top:22px;border-top:1px solid #2a313c;padding-top:10px">
+ Pool status is enriched from each Redfin listing's structured POOL_FEATURES field (real MLS amenity, not
+ free-text). "${pending.length} Pool Pending" = detail page not yet checked or carried no pool field; those fill
+ in on each enrichment sweep. Price band $700k–$1.2M, ${SFV_CITIES.length} SFV cities. Data: Redfin, last sweep.</div>
+ </div></body></html>`;
+ process.stdout.write(html);
+})().catch(e => { console.error(e); process.exit(1); });
← 9198a6e chore: lint + quality-gate fixes, v0.25.0 (session close)
·
back to Commercialrealestate
·
auto-data-snapshot: 2026-08-26T08:21:31 (3 data files) — dat edff366 →