← back to Commercialrealestate
residential report: LA Residential Pulse (SFR+condo) email — just listed / in escrow / just sold / fell out
eb0c10c1f271452a350a8dd8cbe81eef7745eed7 · 2026-07-07 08:03:06 -0700 · Steve Abrams
- residential-report.js builds the HTML market-pulse from the cre DB ($0 local): 5 headline tiles +
Just Listed / Just Sold / Fell Out tables + In-Escrow-by-city.
- run-residential-report.sh sends via george-send rawfile pattern (size-safe) to steve-office +
steve-personal, with a build-size sanity gate.
- launchd com.steve.crcp-residential-report fires 06:30/12:30/18:30/00:30 — :30 past so it runs
AFTER the :00 residential refresh sweep lands. $0/run.
Files touched
A deploy/launchd/com.steve.crcp-residential-report.plistA scripts/residential-report.jsA scripts/run-residential-report.sh
Diff
commit eb0c10c1f271452a350a8dd8cbe81eef7745eed7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 7 08:03:06 2026 -0700
residential report: LA Residential Pulse (SFR+condo) email — just listed / in escrow / just sold / fell out
- residential-report.js builds the HTML market-pulse from the cre DB ($0 local): 5 headline tiles +
Just Listed / Just Sold / Fell Out tables + In-Escrow-by-city.
- run-residential-report.sh sends via george-send rawfile pattern (size-safe) to steve-office +
steve-personal, with a build-size sanity gate.
- launchd com.steve.crcp-residential-report fires 06:30/12:30/18:30/00:30 — :30 past so it runs
AFTER the :00 residential refresh sweep lands. $0/run.
---
.../com.steve.crcp-residential-report.plist | 22 +++++++
scripts/residential-report.js | 73 ++++++++++++++++++++++
scripts/run-residential-report.sh | 32 ++++++++++
3 files changed, 127 insertions(+)
diff --git a/deploy/launchd/com.steve.crcp-residential-report.plist b/deploy/launchd/com.steve.crcp-residential-report.plist
new file mode 100644
index 0000000..44185f9
--- /dev/null
+++ b/deploy/launchd/com.steve.crcp-residential-report.plist
@@ -0,0 +1,22 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
+<plist version="1.0">
+<dict>
+ <key>Label</key><string>com.steve.crcp-residential-report</string>
+ <key>ProgramArguments</key>
+ <array>
+ <string>/bin/bash</string>
+ <string>/Users/macstudio3/Projects/commercialrealestate/scripts/run-residential-report.sh</string>
+ </array>
+ <key>StartCalendarInterval</key>
+ <array>
+ <dict><key>Hour</key><integer>6</integer><key>Minute</key><integer>30</integer></dict>
+ <dict><key>Hour</key><integer>12</integer><key>Minute</key><integer>30</integer></dict>
+ <dict><key>Hour</key><integer>18</integer><key>Minute</key><integer>30</integer></dict>
+ <dict><key>Hour</key><integer>0</integer><key>Minute</key><integer>30</integer></dict>
+ </array>
+ <key>StandardOutPath</key><string>/Users/macstudio3/Projects/commercialrealestate/tmp/report-launchd.log</string>
+ <key>StandardErrorPath</key><string>/Users/macstudio3/Projects/commercialrealestate/tmp/report-launchd.log</string>
+ <key>RunAtLoad</key><false/>
+</dict>
+</plist>
diff --git a/scripts/residential-report.js b/scripts/residential-report.js
new file mode 100644
index 0000000..fa2edba
--- /dev/null
+++ b/scripts/residential-report.js
@@ -0,0 +1,73 @@
+// residential-report.js — build the LA County RESIDENTIAL market pulse email (SFR + condo) from the
+// cre DB: Just Listed / In Escrow / Just Sold / Fell Out of Escrow + by-city breakdown. Prints HTML to
+// stdout. Local + free. Window (days) via REPORT_DAYS (default 7).
+'use strict';
+const { Pool } = require('pg');
+const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
+const DAYS = +(process.env.REPORT_DAYS || 7);
+const today = process.env.REPORT_DATE || new Date().toLocaleDateString('en-CA', { timeZone: 'America/Los_Angeles' });
+const money = n => n == null ? '—' : '$' + Math.round(+n).toLocaleString();
+const esc = s => (s == null ? '' : String(s)).replace(/&/g, '&').replace(/</g, '<');
+
+// UNION SFR + condo into one residential set with a type label.
+const UNI = `(
+ SELECT 'SFR' t, address, city, zip, price, beds, baths, sqft, status, market_status, prev_market_status,
+ days_on_market, listed_date, disposition, sold_price, sold_date, off_market_at, source FROM sfr
+ UNION ALL
+ SELECT 'Condo' t, address, city, zip, price, beds, baths, sqft, status, market_status, prev_market_status,
+ days_on_market, listed_date, disposition, sold_price, sold_date, off_market_at, source FROM condo
+)`;
+
+(async () => {
+ const q = (sql, a = []) => pool.query(sql, a).then(r => r.rows);
+ const one = async (sql, a = []) => (await q(sql, a))[0] || {};
+
+ const s = await one(`SELECT
+ count(*) FILTER (WHERE status='active') active,
+ count(*) FILTER (WHERE market_status IN ('Pending','Contingent')) in_escrow,
+ count(*) FILTER (WHERE status='active' AND days_on_market IS NOT NULL AND days_on_market<=$1) just_listed,
+ count(*) FILTER (WHERE disposition='sold' AND off_market_at > now()-($1||' days')::interval) just_sold,
+ count(*) FILTER (WHERE status='off_market' AND disposition='withdrawn') withdrawn
+ FROM ${UNI} u`, [DAYS]);
+
+ const justListed = await q(`SELECT t,address,city,price,beds,baths,days_on_market dom,source FROM ${UNI} u
+ WHERE status='active' AND days_on_market IS NOT NULL AND days_on_market<=$1
+ ORDER BY days_on_market ASC, price DESC LIMIT 20`, [DAYS]);
+ const justSold = await q(`SELECT t,address,city,sold_price,sold_date FROM ${UNI} u
+ WHERE disposition='sold' AND off_market_at > now()-($1||' days')::interval
+ ORDER BY sold_date DESC NULLS LAST LIMIT 20`, [DAYS]);
+ const escrowByCity = await q(`SELECT city, count(*) n FROM ${UNI} u
+ WHERE market_status IN ('Pending','Contingent') GROUP BY city ORDER BY n DESC LIMIT 12`);
+ const fellOut = await q(`SELECT t,address,city,price,beds,baths,source FROM ${UNI} u
+ WHERE status='active' AND prev_market_status IN ('Pending','Contingent') AND market_status NOT IN ('Pending','Contingent')
+ ORDER BY price DESC LIMIT 20`);
+
+ const tile = (label, val, 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}">${(+val).toLocaleString()}</div>
+ <div style="font-size:11px;color:#8b949e;text-transform:uppercase;letter-spacing:.5px;margin-top:2px">${label}</div></td>`;
+ const rowsTbl = (rows, cols) => rows.length ? `<table style="width:100%;border-collapse:collapse;font-size:13px">
+ <tr style="color:#8b949e;text-align:left">${cols.map(c => `<th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">${c[0]}</th>`).join('')}</tr>
+ ${rows.map(r => `<tr>${cols.map(c => `<td style="padding:6px 8px;border-bottom:1px solid #20262f">${c[1](r)}</td>`).join('')}</tr>`).join('')}
+ </table>` : '<div style="color:#8b949e;font-size:13px;padding:8px">None in the last ' + DAYS + ' days.</div>';
+
+ 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:760px;margin:0 auto">
+ <h1 style="font-size:20px;margin:0 0 3px">🏠 LA County Residential Pulse <span style="color:#3fb950">· ${today}</span></h1>
+ <div style="color:#8b949e;font-size:12px;margin-bottom:16px">Redfin for-sale + in-escrow · last ${DAYS} days · <a href="https://crcp.agentabrams.com/mls.html" style="color:#58a6ff">open the MLS →</a></div>
+ <table style="width:100%;border-spacing:8px;margin:-8px 0 18px"><tr>
+ ${tile('Active', s.active, '#e6edf3')}${tile('In Escrow', s.in_escrow, '#d29922')}${tile('Just Listed', s.just_listed, '#3fb950')}${tile('Just Sold', s.just_sold, '#58a6ff')}${tile('Withdrawn', s.withdrawn, '#f85149')}
+ </tr></table>
+ <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:18px 0 6px">🆕 Just Listed (newest ${DAYS}d)</h3>
+ ${rowsTbl(justListed, [['Address', r => `<a href="${esc(r.source)}" style="color:#58a6ff;text-decoration:none">${esc(r.address)}</a>`], ['City', r => esc(r.city)], ['Type', r => r.t], ['Price', r => money(r.price)], ['Bd/Ba', r => `${r.beds ?? '—'}/${r.baths ?? '—'}`], ['DOM', r => r.dom + 'd']])}
+ <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:20px 0 6px">✅ Just Sold (closed escrow, ${DAYS}d)</h3>
+ ${rowsTbl(justSold, [['Address', r => esc(r.address)], ['City', r => esc(r.city)], ['Type', r => r.t], ['Sold', r => money(r.sold_price)], ['Date', r => r.sold_date ? String(r.sold_date).slice(0, 10) : '—']])}
+ <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:20px 0 6px">↩️ Fell Out of Escrow (back on market)</h3>
+ ${rowsTbl(fellOut, [['Address', r => `<a href="${esc(r.source)}" style="color:#58a6ff;text-decoration:none">${esc(r.address)}</a>`], ['City', r => esc(r.city)], ['Type', r => r.t], ['Price', r => money(r.price)], ['Bd/Ba', r => `${r.beds ?? '—'}/${r.baths ?? '—'}`]])}
+ <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:20px 0 6px">🤝 In Escrow by City</h3>
+ ${rowsTbl(escrowByCity, [['City', r => esc(r.city)], ['Pending / Contingent', r => `<b>${r.n}</b>`]])}
+ <div style="color:#8b949e;font-size:11px;margin-top:22px;border-top:1px solid #2a313c;padding-top:10px">
+ Fell-Out accrues as listings transition Pending→Active across sweeps. Just-Sold cross-references recorded sales; escrow lag means more sales surface over the following weeks.</div>
+ </div></body></html>`;
+ process.stdout.write(html);
+ await pool.end();
+})().catch(e => { console.error(e.message); process.exit(1); });
diff --git a/scripts/run-residential-report.sh b/scripts/run-residential-report.sh
new file mode 100755
index 0000000..9497963
--- /dev/null
+++ b/scripts/run-residential-report.sh
@@ -0,0 +1,32 @@
+#!/bin/bash
+# run-residential-report.sh — build + email the LA County Residential Pulse (SFR + condo: just listed /
+# in escrow / just sold / fell out) to steve-office + steve-personal. Local + free ($0).
+# Scheduled 4x/day at :30 past (06:30/12:30/18:30/00:30) — AFTER the residential refresh (:00) finishes,
+# so the report reflects the fresh sweep. launchd: com.steve.crcp-residential-report.
+set -uo pipefail
+cd "$HOME/Projects/commercialrealestate" || exit 1
+export PATH="/usr/local/bin:/opt/homebrew/bin:$PATH"
+LOG=tmp/residential-report.log
+mkdir -p tmp
+D="$(date +%Y-%m-%d)"
+echo "===== [$(date '+%F %T')] residential report =====" >> "$LOG"
+
+REPORT_DATE="$D" node scripts/residential-report.js > data/residential-report.html 2>>"$LOG"
+BYTES=$(wc -c < data/residential-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="${RESIDENTIAL_REPORT_TO:-steve@designerwallcoverings.com, steveabramsdesigns@gmail.com}"
+# Pull the two headline counts into the subject (nice-to-have; falls back gracefully).
+ESC=$(grep -oE 'color:#d29922">[0-9,]+' data/residential-report.html | head -1 | grep -oE '[0-9,]+' || echo '')
+NEW=$(grep -oE 'color:#3fb950">[0-9,]+' data/residential-report.html | head -1 | grep -oE '[0-9,]+' || echo '')
+SUBJ="🏠 LA Residential Pulse — ${ESC:-?} in escrow, ${NEW:-?} just listed ($D)"
+
+jq -Rn --arg account steve-office --arg to "$TO" --arg subject "$SUBJ" --rawfile body data/residential-report.html \
+ '{account:$account,to:$to,subject:$subject,body:$body}' > tmp/res-report-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/res-report-payload.json)"
+echo "$RESP" | grep -q '"success":true' && echo "[$(date '+%T')] sent -> $TO" >> "$LOG" \
+ || echo "[$(date '+%T')] SEND FAILED: $RESP" >> "$LOG"
← 8488362 fix: raise /api/residential cap 12k->25k — SFR active grew p
·
back to Commercialrealestate
·
reports: refresh generated HTML (morning/afternoon/condos/re bf516b7 →