← back to Commercialrealestate
SFV pool list: add lot size, price/sqft, photo to enrichment + endpoint + email + page
c6eda17479eb5375ca223658e772acc151361448 · 2026-08-26 08:38:21 -0700 · Steve Abrams
Files touched
M public/sfv-pool.htmlM scripts/enrich-sfr-pools.jsM scripts/serve.jsM scripts/sfv-pool-report.js
Diff
commit c6eda17479eb5375ca223658e772acc151361448
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 26 08:38:21 2026 -0700
SFV pool list: add lot size, price/sqft, photo to enrichment + endpoint + email + page
---
public/sfv-pool.html | 13 +++++++++---
scripts/enrich-sfr-pools.js | 48 +++++++++++++++++++++++++++++++++++++--------
scripts/serve.js | 10 +++++++---
scripts/sfv-pool-report.js | 28 +++++++++++---------------
4 files changed, 68 insertions(+), 31 deletions(-)
diff --git a/public/sfv-pool.html b/public/sfv-pool.html
index 7acf9d8..273e000 100644
--- a/public/sfv-pool.html
+++ b/public/sfv-pool.html
@@ -32,6 +32,8 @@
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; }
+ .photo { width:100%; aspect-ratio:16/10; object-fit:cover; border-radius:8px; display:block; margin-bottom:4px; background:#20262f; }
+ .photo.ph { display:flex; } .photo.ph::after { content:'no photo'; margin:auto; color:#57606a; font-size:11px; }
.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; }
@@ -118,12 +120,17 @@ function render() {
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 pp = r.ppsf ? '$'+(+r.ppsf).toLocaleString()+'/sqft' : (r.sqft>0 ? '$'+Math.round(r.price/r.sqft).toLocaleString()+'/sqft' : '');
+ const lot = r.lot_size ? String(r.lot_size).replace(/ square feet/i,' sf').replace(/ Sq\.? ?Ft\.?/i,' sf') : '';
const feat = r._pool==='true' && r.pool_features ? `<div class="feat">${r.pool_features}</div>` : '';
+ const photo = r.photo_url
+ ? `<a href="${r.source||'#'}" target="_blank" rel="noopener noreferrer"><img class="photo" src="${r.photo_url}" alt="" loading="lazy"></a>`
+ : `<div class="photo ph"></div>`;
return `<div class="card">
+ ${photo}
<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 class="price">${money(r.price)} <span style="font-size:12px;color:#8b949e;font-weight:400">${pp?'· '+pp:''}</span></div>
+ <div class="meta">${r.city} · ${r.beds||'?'} bd / ${r.baths||'?'} ba · ${r.sqft?(+r.sqft).toLocaleString()+' sqft':'—'}${lot?' · lot '+lot:''} · ${r.days_on_market!=null?r.days_on_market+'d':''}</div>
<div>${poolBadge(r._pool)}</div>${feat}
</div>`;
}).join('');
diff --git a/scripts/enrich-sfr-pools.js b/scripts/enrich-sfr-pools.js
index c9b869d..b7a7120 100644
--- a/scripts/enrich-sfr-pools.js
+++ b/scripts/enrich-sfr-pools.js
@@ -49,10 +49,37 @@ function classifyPool(html) {
return { hasPool: positive ? true : (negative ? false : (vals.length ? true : null)), features };
}
+// Lot size from the structured "Lot Size" amenity (display value) + a numeric lotSize field (sqft).
+function extractLot(html) {
+ let display = null, sqft = null;
+ const am = html.match(/"amenityName\\?":\\?"Lot Size\\?"[^}]*?"amenityValues\\?":\\?\[\\?"([^"\\]{1,40})/i);
+ if (am) display = am[1].replace(/\\u002c/gi, ',').trim();
+ const num = html.match(/"lotSize\\?":\s*(\d{3,9})/i);
+ if (num) sqft = +num[1];
+ // derive sqft from a "X square feet"/"X Sq. Ft." display if the numeric field was absent
+ if (sqft == null && display) { const m = display.replace(/,/g, '').match(/(\d{3,9})\s*(sq|square)/i); if (m) sqft = +m[1]; }
+ // acres display → sqft
+ if (sqft == null && display) { const a = display.replace(/,/g, '').match(/([\d.]+)\s*acre/i); if (a) sqft = Math.round(parseFloat(a[1]) * 43560); }
+ if (!display && sqft != null) display = sqft.toLocaleString() + ' sq ft';
+ return { lot_size: display, lot_sqft: sqft };
+}
+
+// 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)
+ || html.match(/content=\\?"(https:\/\/ssl\.cdn-redfin\.com\/photo\/[^"\\ >]{20,160})\\?"[^>]*property=\\?"og:image/i);
+ if (og) return og[1].replace(/\\u002f/gi, '/');
+ const cdn = html.match(/https:\/\/ssl\.cdn-redfin\.com\/photo\/[^"\\ >]{20,120}\.jpg/i);
+ return cdn ? cdn[0] : null;
+}
+
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,
+ lot_size text, lot_sqft int, photo_url text,
checked_at timestamptz NOT NULL DEFAULT now())`);
+ await pool.query(`ALTER TABLE sfr_pool ADD COLUMN IF NOT EXISTS lot_size text,
+ ADD COLUMN IF NOT EXISTS lot_sqft int, ADD COLUMN IF NOT EXISTS photo_url text`);
const cities = SFV_CITIES.map(c => `'${c.replace(/'/g, "''")}'`).join(',');
const { rows } = await pool.query(
@@ -60,8 +87,9 @@ async function main() {
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
+ AND (p.id IS NULL OR p.has_pool IS NULL OR p.photo_url IS NULL
+ OR p.checked_at < now() - ($3 || ' days')::interval)
+ ORDER BY (p.id IS NULL) DESC, (p.photo_url 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; }
@@ -75,20 +103,24 @@ async function main() {
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;
+ let hasPool = null, features = null, status = 0, lot = null, lotSqft = null, photo = null;
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;
+ const c = classifyPool(html); hasPool = c.hasPool; features = c.features;
+ const L = extractLot(html); lot = L.lot_size; lotSqft = L.lot_sqft;
+ photo = extractPhoto(html); 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]);
+ `INSERT INTO sfr_pool(id,has_pool,pool_features,http_status,lot_size,lot_sqft,photo_url,checked_at)
+ VALUES($1,$2,$3,$4,$5,$6,$7,now())
+ ON CONFLICT (id) DO UPDATE SET has_pool=$2, pool_features=$3, http_status=$4,
+ lot_size=$5, lot_sqft=$6, photo_url=$7, checked_at=now()`,
+ [row.id, hasPool, features, status || null, lot, lotSqft, photo]);
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(); }
@@ -103,7 +135,7 @@ async function main() {
}
async function snapshot() {
- const snap = (await pool.query(`SELECT id, has_pool, pool_features, checked_at FROM sfr_pool`)).rows;
+ const snap = (await pool.query(`SELECT id, has_pool, pool_features, lot_size, lot_sqft, photo_url, 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));
diff --git a/scripts/serve.js b/scripts/serve.js
index 4c65c2b..2b58fb3 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -1291,13 +1291,16 @@ app.get('/api/sfv-pool', async (req, res) => {
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 });
+ pool_features: r.pool_features || null, lot_size: r.lot_size || null, lot_sqft: r.lot_sqft || null,
+ photo_url: r.photo_url || null,
+ ppsf: (+r.price && +r.sqft) ? Math.round(+r.price / +r.sqft) : 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
+ s.days_on_market, s.source, s.lat, s.lng, p.has_pool, p.pool_features,
+ p.lot_size, p.lot_sqft, p.photo_url
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[])
@@ -1322,7 +1325,8 @@ app.get('/api/sfv-pool', async (req, res) => {
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 };
+ .forEach(r => { const pr = pools[r.id] || {}; const row = { ...r, has_pool: pr.has_pool, pool_features: pr.pool_features,
+ lot_size: pr.lot_size, lot_sqft: pr.lot_sqft, photo_url: pr.photo_url };
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 },
diff --git a/scripts/sfv-pool-report.js b/scripts/sfv-pool-report.js
index b42971a..d93c653 100644
--- a/scripts/sfv-pool-report.js
+++ b/scripts/sfv-pool-report.js
@@ -17,26 +17,20 @@ 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();
+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, extra = '') => `<td style="padding:6px 8px;border-bottom:1px solid #20262f;${extra}">${v}</td>`;
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 head = `<tr style="color:#8b949e">${TH('')}${TH('Address')}${TH('City')}${TH('Price')}${TH('$/sqft')}${TH('Bd/Ba')}${TH('Sqft')}${TH('Lot')}${kind === 'pool' ? TH('Pool') : ''}</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>`;
+ 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') : '—';
+ 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>';
+ const feat = kind === 'pool' ? TD(esc(r.pool_features || 'Yes'), 'color:#3fb950;font-size:12px') : '';
+ 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)}${feat}</tr>`;
}).join('');
return `<table style="width:100%;border-collapse:collapse;font-size:13px">${head}${body}</table>`;
}
@@ -45,7 +39,7 @@ function table(rows, kind) {
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
+ s.source, p.has_pool, p.pool_features, p.lot_size, p.lot_sqft, p.photo_url
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]);
← edff366 auto-data-snapshot: 2026-08-26T08:21:31 (3 data files) — dat
·
back to Commercialrealestate
·
SFV pool page: click any card -> detail modal with photo, sq 49751ea →