← back to Nationalrealestate
M5 (partial): refresh orchestrator, alerts checker, monthly launchd plist, watchlist + admin pages (server endpoints pending broker-agent merge)
f42ca122158e85c1afc39ab089b52a1d077aa6d2 · 2026-07-21 13:14:23 -0700 · Steve Abrams
Files touched
A launchd/com.steve.usre-refresh.plistA public/admin.htmlA public/watchlist.htmlA src/jobs/alerts_check.tsA src/jobs/refresh_all.ts
Diff
commit f42ca122158e85c1afc39ab089b52a1d077aa6d2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 13:14:23 2026 -0700
M5 (partial): refresh orchestrator, alerts checker, monthly launchd plist, watchlist + admin pages (server endpoints pending broker-agent merge)
---
launchd/com.steve.usre-refresh.plist | 20 +++++
public/admin.html | 102 ++++++++++++++++++++++
public/watchlist.html | 160 +++++++++++++++++++++++++++++++++++
src/jobs/alerts_check.ts | 52 ++++++++++++
src/jobs/refresh_all.ts | 37 ++++++++
5 files changed, 371 insertions(+)
diff --git a/launchd/com.steve.usre-refresh.plist b/launchd/com.steve.usre-refresh.plist
new file mode 100644
index 0000000..74980c3
--- /dev/null
+++ b/launchd/com.steve.usre-refresh.plist
@@ -0,0 +1,20 @@
+<?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.usre-refresh</string>
+ <key>ProgramArguments</key>
+ <array>
+ <string>/bin/bash</string>
+ <string>-lc</string>
+ <string>cd ~/Projects/nationalrealestate && npx tsx src/jobs/refresh_all.ts >> ~/Library/Logs/usre-refresh.log 2>&1</string>
+ </array>
+ <key>StartCalendarInterval</key>
+ <dict>
+ <key>Day</key><integer>25</integer>
+ <key>Hour</key><integer>3</integer>
+ <key>Minute</key><integer>30</integer>
+ </dict>
+ <key>StandardErrorPath</key><string>/tmp/usre-refresh.err</string>
+</dict>
+</plist>
diff --git a/public/admin.html b/public/admin.html
new file mode 100644
index 0000000..01b5d7c
--- /dev/null
+++ b/public/admin.html
@@ -0,0 +1,102 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>USRealEstate — Admin · Ingest Provenance</title>
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;--red:#f85149;--green:#3fb950;--amber:#d29922;}
+ *{box-sizing:border-box;} body{margin:0;background:var(--bg);color:var(--fg);font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;}
+ #topbar{position:sticky;top:0;z-index:10;display:flex;align-items:center;gap:18px;padding:10px 18px;
+ background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);backdrop-filter:blur(6px);}
+ #topbar .brand{font-size:16px;font-weight:700;} #topbar .brand b{color:var(--gold);}
+ nav{display:flex;gap:14px;font-size:13px;} nav a{color:var(--muted);text-decoration:none;}
+ nav a:hover,nav a.active{color:var(--gold);}
+ main{max-width:1200px;margin:0 auto;padding:22px 18px;}
+ h2{font-size:15px;color:var(--gold);margin:20px 0 10px;}
+ .runbtns{display:flex;gap:8px;flex-wrap:wrap;margin-bottom:6px;}
+ .runbtns button{background:var(--panel);border:1px solid var(--line);color:var(--fg);border-radius:8px;
+ padding:7px 14px;cursor:pointer;font:13px -apple-system,Segoe UI,Roboto,sans-serif;}
+ .runbtns button:hover{border-color:var(--gold);color:var(--gold);}
+ .runbtns button:disabled{opacity:.45;cursor:default;}
+ #runmsg{font-size:12px;color:var(--muted);margin-bottom:12px;}
+ .grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(330px,1fr));gap:12px;}
+ .card{background:var(--panel);border:1px solid var(--line);border-radius:12px;padding:13px 15px;}
+ .card .hd{display:flex;align-items:center;gap:8px;}
+ .card .src{font-weight:700;} .card .st{margin-left:auto;font-size:11px;font-weight:700;border-radius:8px;padding:2px 9px;}
+ .st.ok{background:rgba(63,185,80,.15);color:var(--green);} .st.failed{background:rgba(248,81,73,.15);color:var(--red);}
+ .st.running{background:rgba(210,153,34,.15);color:var(--amber);}
+ .card .when{font-size:12px;color:var(--muted);margin:5px 0 7px;}
+ .card .rows{display:flex;gap:14px;font-size:12px;color:var(--muted);flex-wrap:wrap;}
+ .card .rows b{color:var(--fg);font-variant-numeric:tabular-nums;}
+ .card .notes{font-size:11px;color:var(--muted);margin-top:7px;word-break:break-word;}
+ .card .hash{font-size:10px;color:var(--muted);margin-top:5px;font-family:ui-monospace,Menlo,monospace;word-break:break-all;}
+ .empty{color:var(--muted);padding:18px;text-align:center;}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — Admin</div>
+ <nav>
+ <a href="/">Map</a>
+ <a href="/markets.html">Markets</a>
+ <a href="/brokers.html">Brokers</a>
+ <a href="/watchlist.html">Watchlist</a>
+ <a href="/admin.html" class="active">Admin</a>
+ </nav>
+</div>
+<main>
+ <h2>Run now</h2>
+ <div class="runbtns" id="runbtns"></div>
+ <div id="runmsg"></div>
+
+ <h2>Ingest runs</h2>
+ <div class="grid" id="cards"><div class="empty">loading…</div></div>
+</main>
+<script>
+const JOBS = ['zillow','redfin','fhfa','acs','derive','score','alerts','brokers'];
+// standing rule: every admin card shows the record's created date AND time, visible on-card
+const fmtWhen = iso => new Date(iso).toLocaleString(undefined,
+ { year:'numeric', month:'short', day:'numeric', hour:'numeric', minute:'2-digit' });
+
+function renderBtns(busy){
+ const el = document.getElementById('runbtns');
+ el.innerHTML = '';
+ for (const j of JOBS){
+ const b = document.createElement('button');
+ b.textContent = '▶ ' + j;
+ b.disabled = !!busy;
+ b.onclick = async () => {
+ renderBtns(true);
+ document.getElementById('runmsg').textContent = 'running ' + j + '…';
+ const r = await fetch('/api/admin/run/' + j, { method:'POST' }).then(r=>r.json()).catch(e=>({error:String(e)}));
+ document.getElementById('runmsg').textContent = r.error ? (j+' failed: '+r.error) : (j+' finished: '+(r.summary||'ok'));
+ renderBtns(false);
+ loadRuns();
+ };
+ el.appendChild(b);
+ }
+}
+
+async function loadRuns(){
+ const d = await fetch('/api/admin/runs').then(r=>r.json());
+ const el = document.getElementById('cards');
+ if (!d.runs.length){ el.innerHTML = '<div class="empty">no ingest runs yet</div>'; return; }
+ el.innerHTML = d.runs.map(r => `
+ <div class="card">
+ <div class="hd"><span class="src">${r.source}</span><span class="st ${r.status}">${r.status}</span></div>
+ <div class="when" title="${r.started_at}">🕓 ${fmtWhen(r.started_at)}${r.finished_at ? ' → '+fmtWhen(r.finished_at) : ''}</div>
+ <div class="rows"><span>upserted <b>${(+r.rows_upserted||0).toLocaleString()}</b></span>
+ <span>skipped <b>${(+r.rows_skipped||0).toLocaleString()}</b></span>
+ <span>run <b>#${r.id}</b></span></div>
+ ${r.notes ? `<div class="notes">${r.notes}</div>` : ''}
+ ${r.file_hash ? `<div class="hash">sha256 ${r.file_hash.slice(0,20)}…</div>` : ''}
+ </div>`).join('');
+}
+
+renderBtns(false);
+loadRuns();
+setInterval(loadRuns, 30000);
+</script>
+</body>
+</html>
diff --git a/public/watchlist.html b/public/watchlist.html
new file mode 100644
index 0000000..a3dd6b8
--- /dev/null
+++ b/public/watchlist.html
@@ -0,0 +1,160 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>USRealEstate — Watchlist</title>
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;--red:#f85149;--green:#3fb950;}
+ *{box-sizing:border-box;} body{margin:0;background:var(--bg);color:var(--fg);font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;}
+ #topbar{position:sticky;top:0;z-index:10;display:flex;align-items:center;gap:18px;padding:10px 18px;
+ background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);backdrop-filter:blur(6px);}
+ #topbar .brand{font-size:16px;font-weight:700;} #topbar .brand b{color:var(--gold);}
+ nav{display:flex;gap:14px;font-size:13px;} nav a{color:var(--muted);text-decoration:none;}
+ nav a:hover,nav a.active{color:var(--gold);}
+ main{max-width:1100px;margin:0 auto;padding:22px 18px;}
+ h2{font-size:15px;color:var(--gold);margin:22px 0 10px;}
+ .addrow{display:flex;gap:8px;flex-wrap:wrap;align-items:center;background:var(--panel);
+ border:1px solid var(--line);border-radius:12px;padding:12px 14px;}
+ .addrow input,.addrow select{background:var(--bg);border:1px solid var(--line);color:var(--fg);
+ border-radius:8px;padding:7px 10px;font:13px -apple-system,Segoe UI,Roboto,sans-serif;}
+ .addrow input#q{flex:1;min-width:220px;}
+ .addrow button{background:var(--gold);color:var(--bg);border:0;border-radius:8px;padding:8px 16px;font-weight:700;cursor:pointer;}
+ #sugg{position:relative;} #sugglist{position:absolute;top:100%;left:0;right:0;z-index:20;background:var(--panel);
+ border:1px solid var(--line);border-radius:0 0 10px 10px;max-height:260px;overflow:auto;display:none;}
+ #sugglist div{padding:7px 12px;cursor:pointer;font-size:13px;} #sugglist div:hover{background:var(--bg);color:var(--gold);}
+ .card{background:var(--panel);border:1px solid var(--line);border-radius:12px;padding:14px 16px;margin:10px 0;
+ display:flex;gap:16px;align-items:center;flex-wrap:wrap;}
+ .card .nm{font-weight:700;min-width:220px;} .card .nm a{color:var(--fg);text-decoration:none;} .card .nm a:hover{color:var(--gold);}
+ .card .stat{text-align:center;} .card .stat .v{font-weight:700;color:var(--gold);font-variant-numeric:tabular-nums;}
+ .card .stat .l{font-size:10px;color:var(--muted);text-transform:uppercase;letter-spacing:.4px;}
+ .card .thr{font-size:12px;color:var(--muted);}
+ .card .del{margin-left:auto;background:none;border:1px solid var(--line);color:var(--red);border-radius:8px;padding:5px 12px;cursor:pointer;}
+ .fired{border-color:var(--gold);}
+ .alert-row{background:var(--panel);border:1px solid var(--line);border-left:3px solid var(--gold);border-radius:10px;
+ padding:10px 14px;margin:6px 0;font-size:13px;display:flex;gap:12px;align-items:center;}
+ .alert-row .when{color:var(--muted);font-size:11px;margin-left:auto;white-space:nowrap;}
+ .empty{color:var(--muted);padding:18px;text-align:center;}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — Watchlist</div>
+ <nav>
+ <a href="/">Map</a>
+ <a href="/markets.html">Markets</a>
+ <a href="/brokers.html">Brokers</a>
+ <a href="/watchlist.html" class="active">Watchlist</a>
+ <a href="/admin.html">Admin</a>
+ </nav>
+</div>
+<main>
+ <h2>Add a market</h2>
+ <div class="addrow">
+ <div id="sugg" style="flex:1;min-width:240px">
+ <input id="q" placeholder="Search county or metro… (e.g. Los Angeles)" autocomplete="off">
+ <div id="sugglist"></div>
+ </div>
+ <select id="thr-metric">
+ <option value="">no alert threshold</option>
+ <option value="zhvi_yoy">ZHVI YoY</option>
+ <option value="median_sale_price_yoy">Sale Price YoY</option>
+ <option value="months_of_supply">Months Supply</option>
+ <option value="dom">DOM</option>
+ <option value="rent_yield">Rent Yield</option>
+ </select>
+ <select id="thr-op"><option>></option><option><</option><option>>=</option><option><=</option></select>
+ <input id="thr-val" type="number" step="any" placeholder="value" style="width:90px">
+ <button id="add" disabled>Watch</button>
+ </div>
+
+ <h2>Watched markets</h2>
+ <div id="list"><div class="empty">loading…</div></div>
+
+ <h2>Recent alerts</h2>
+ <div id="alerts"><div class="empty">loading…</div></div>
+</main>
+<script>
+let regions = [];
+let selected = null;
+
+const fmt = {
+ score: v => v==null?'—':v.toFixed(1),
+ usd: v => v==null?'—':'$'+Math.round(v).toLocaleString(),
+ pct: v => v==null?'—':(v*100).toFixed(1)+'%',
+ num: v => v==null?'—':(+v).toLocaleString(),
+};
+
+async function loadRegions(){
+ const [c, m] = await Promise.all([
+ fetch('/api/regions?type=county').then(r=>r.json()),
+ fetch('/api/regions?type=metro').then(r=>r.json()),
+ ]);
+ regions = [...c.regions, ...m.regions];
+}
+
+const q = document.getElementById('q'), sl = document.getElementById('sugglist'), addBtn = document.getElementById('add');
+q.addEventListener('input', () => {
+ selected = null; addBtn.disabled = true;
+ const s = q.value.trim().toLowerCase();
+ if (s.length < 2){ sl.style.display='none'; return; }
+ const hits = regions.filter(r => r.name.toLowerCase().includes(s)).slice(0, 12);
+ sl.innerHTML = hits.map(r =>
+ `<div data-key="${r.canonical_key}">${r.name}${r.state_code?', '+r.state_code:''} <span style="color:var(--muted)">· ${r.region_type}</span></div>`).join('');
+ sl.style.display = hits.length ? 'block' : 'none';
+ [...sl.children].forEach(div => div.onclick = () => {
+ selected = div.dataset.key;
+ q.value = div.textContent.trim();
+ sl.style.display = 'none';
+ addBtn.disabled = false;
+ });
+});
+
+addBtn.onclick = async () => {
+ const metric = document.getElementById('thr-metric').value;
+ const val = parseFloat(document.getElementById('thr-val').value);
+ const thresholds = metric && Number.isFinite(val)
+ ? { metric, op: document.getElementById('thr-op').value, value: val } : null;
+ await fetch('/api/watchlist', { method:'POST', headers:{'Content-Type':'application/json'},
+ body: JSON.stringify({ key: selected, thresholds }) });
+ q.value=''; selected=null; addBtn.disabled=true;
+ loadList();
+};
+
+async function loadList(){
+ const d = await fetch('/api/watchlist').then(r=>r.json());
+ const el = document.getElementById('list');
+ if (!d.items.length){ el.innerHTML = '<div class="empty">Nothing watched yet — search above to add a market.</div>'; return; }
+ el.innerHTML = d.items.map(w => `
+ <div class="card">
+ <div class="nm"><a href="/market.html?key=${w.key}">${w.name}${w.state?', '+w.state:''}</a>
+ <div style="font-size:11px;color:var(--muted)">${w.region_type}</div></div>
+ <div class="stat"><div class="v">${fmt.score(w.opportunity_score)}</div><div class="l">Score</div></div>
+ <div class="stat"><div class="v">${fmt.usd(w.zhvi)}</div><div class="l">ZHVI</div></div>
+ <div class="stat"><div class="v">${fmt.pct(w.zhvi_yoy)}</div><div class="l">YoY</div></div>
+ <div class="stat"><div class="v">${fmt.num(w.dom)}</div><div class="l">DOM</div></div>
+ <div class="thr">${w.thresholds ? 'alert: '+w.thresholds.metric+' '+w.thresholds.op+' '+w.thresholds.value : 'no threshold'}</div>
+ <button class="del" data-id="${w.id}">remove</button>
+ </div>`).join('');
+ [...el.querySelectorAll('.del')].forEach(b => b.onclick = async () => {
+ await fetch('/api/watchlist/' + b.dataset.id, { method:'DELETE' });
+ loadList();
+ });
+}
+
+async function loadAlerts(){
+ const d = await fetch('/api/alerts').then(r=>r.json());
+ const el = document.getElementById('alerts');
+ if (!d.alerts.length){ el.innerHTML = '<div class="empty">No alerts fired.</div>'; return; }
+ el.innerHTML = d.alerts.map(a => `
+ <div class="alert-row">
+ <b>${a.name||'?'}</b> ${a.metric} hit ${(+a.observed).toLocaleString()}
+ <span style="color:var(--muted)">(threshold ${a.detail?.threshold?.op} ${a.detail?.threshold?.value})</span>
+ <span class="when" title="${a.fired_at}">🕓 ${new Date(a.fired_at).toLocaleString(undefined,{year:'numeric',month:'short',day:'numeric',hour:'numeric',minute:'2-digit'})}</span>
+ </div>`).join('');
+}
+
+loadRegions().then(()=>{ loadList(); loadAlerts(); });
+</script>
+</body>
+</html>
diff --git a/src/jobs/alerts_check.ts b/src/jobs/alerts_check.ts
new file mode 100644
index 0000000..3fe50e7
--- /dev/null
+++ b/src/jobs/alerts_check.ts
@@ -0,0 +1,52 @@
+/**
+ * Evaluates every watchlist threshold against the latest metric value for its
+ * region; fires a row into alerts_log when tripped. Re-fire suppression: skips
+ * if an alert for the same watchlist+metric already fired in the last 25 days
+ * (monthly cadence means one alert per period, not one per run).
+ * Threshold shape: {metric:'zhvi_yoy', op:'>'|'<'|'>='|'<=', value:number}
+ */
+import { query, pool } from '../../db/pool.ts';
+
+const OPS: Record<string, (a: number, b: number) => boolean> = {
+ '>': (a, b) => a > b,
+ '<': (a, b) => a < b,
+ '>=': (a, b) => a >= b,
+ '<=': (a, b) => a <= b,
+};
+
+async function main() {
+ const wl = await query<{ id: number; region_id: number; label: string | null; thresholds: any }>(
+ `SELECT id, region_id, label, thresholds FROM watchlist WHERE thresholds IS NOT NULL`,
+ );
+ let fired = 0;
+ for (const w of wl.rows) {
+ const t = w.thresholds;
+ if (!t || !t.metric || !OPS[t.op] || typeof t.value !== 'number') continue;
+ const m = await query<{ value: string; period: string }>(
+ `SELECT value::text AS value, to_char(period,'YYYY-MM-DD') AS period
+ FROM metric_series WHERE region_id = $1 AND metric = $2
+ ORDER BY period DESC LIMIT 1`,
+ [w.region_id, t.metric],
+ );
+ if (!m.rows.length) continue;
+ const observed = Number(m.rows[0].value);
+ if (!OPS[t.op](observed, t.value)) continue;
+ const recent = await query(
+ `SELECT 1 FROM alerts_log WHERE watchlist_id = $1 AND metric = $2
+ AND fired_at > NOW() - INTERVAL '25 days' LIMIT 1`,
+ [w.id, t.metric],
+ );
+ if (recent.rows.length) continue;
+ await query(
+ `INSERT INTO alerts_log (watchlist_id, metric, observed, detail)
+ VALUES ($1, $2, $3, $4)`,
+ [w.id, t.metric, observed, JSON.stringify({ threshold: t, period: m.rows[0].period, label: w.label })],
+ );
+ fired++;
+ console.log(`[alerts] FIRED watchlist#${w.id} ${t.metric} ${t.op} ${t.value} (observed ${observed})`);
+ }
+ console.log(`[alerts] checked ${wl.rows.length} watches, fired ${fired}`);
+ await pool.end();
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
diff --git a/src/jobs/refresh_all.ts b/src/jobs/refresh_all.ts
new file mode 100644
index 0000000..669d2bc
--- /dev/null
+++ b/src/jobs/refresh_all.ts
@@ -0,0 +1,37 @@
+/**
+ * Monthly refresh orchestrator (launchd com.steve.usre-refresh):
+ * zillow -> redfin -> fhfa -> acs -> derive -> score -> alerts.
+ * Every job is an idempotent upsert, so re-running any month is safe; jobs run
+ * as child processes because each script owns its pool lifecycle (pool.end()).
+ * A failed job logs + continues (per-source failure shows red in ingest_runs;
+ * one dead source must not starve the others).
+ */
+import { execFileSync } from 'node:child_process';
+import { join, dirname } from 'node:path';
+import { fileURLToPath } from 'node:url';
+
+const __dirname = dirname(fileURLToPath(import.meta.url));
+const ROOT = join(__dirname, '..', '..');
+
+const JOBS: Array<[string, string]> = [
+ ['zillow', 'src/ingest/zillow_research.ts'],
+ ['redfin', 'src/ingest/redfin_tracker.ts'],
+ ['fhfa', 'src/ingest/fhfa_hpi.ts'],
+ ['acs', 'src/ingest/census_acs.ts'],
+ ['derive', 'src/ingest/derive_metrics.ts'],
+ ['score', 'src/score/opportunity.ts'],
+ ['alerts', 'src/jobs/alerts_check.ts'],
+];
+
+let failed = 0;
+for (const [name, script] of JOBS) {
+ console.log(`\n[refresh] ── ${name} ──`);
+ try {
+ execFileSync('npx', ['tsx', script], { cwd: ROOT, stdio: 'inherit', timeout: 45 * 60_000 });
+ } catch (e: any) {
+ failed++;
+ console.error(`[refresh] ${name} FAILED: ${e.message}`);
+ }
+}
+console.log(`\n[refresh] complete, ${failed} job(s) failed`);
+process.exit(failed > 0 ? 1 : 0);
← c188ec3 CCK review fixes: legacy CT counties seeded (CT choropleth h
·
back to Nationalrealestate
·
M5: watchlist/alerts/admin routes as mountable module (2-lin 439b0e1 →