CRCP brokers: nightly snapshot refresh (launchd com.steve.crcp-broker-snapshot, sanity-gated, no-restart) + harden brokers-db PG user so it never silently defaults to root on prod

715d28a655d91cc53b76d4454593530f7a5ce7ed · 2026-07-12 08:29:10 -0700 · Steve Abrams

Files touched

A scripts/com.steve.crcp-broker-snapshot.plist
M scripts/db/brokers-db.js
A scripts/refresh-broker-snapshot.sh

Diff

commit 715d28a655d91cc53b76d4454593530f7a5ce7ed
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 12 08:29:10 2026 -0700

    CRCP brokers: nightly snapshot refresh (launchd com.steve.crcp-broker-snapshot, sanity-gated, no-restart) + harden brokers-db PG user so it never silently defaults to root on prod
---
 scripts/com.steve.crcp-broker-snapshot.plist | 17 +++++++++++++++++
 scripts/db/brokers-db.js                     |  8 +++++++-
 scripts/refresh-broker-snapshot.sh           | 28 ++++++++++++++++++++++++++++
 3 files changed, 52 insertions(+), 1 deletion(-)

diff --git a/scripts/com.steve.crcp-broker-snapshot.plist b/scripts/com.steve.crcp-broker-snapshot.plist
new file mode 100644
index 0000000..b5dff12
--- /dev/null
+++ b/scripts/com.steve.crcp-broker-snapshot.plist
@@ -0,0 +1,17 @@
+<?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-broker-snapshot</string>
+  <key>ProgramArguments</key>
+  <array>
+    <string>/bin/bash</string>
+    <string>/Users/macstudio3/Projects/commercialrealestate/scripts/refresh-broker-snapshot.sh</string>
+  </array>
+  <key>StartCalendarInterval</key>
+  <dict><key>Hour</key><integer>4</integer><key>Minute</key><integer>15</integer></dict>
+  <key>StandardOutPath</key><string>/tmp/crcp-broker-snapshot.out</string>
+  <key>StandardErrorPath</key><string>/tmp/crcp-broker-snapshot.err</string>
+  <key>RunAtLoad</key><false/>
+</dict>
+</plist>
diff --git a/scripts/db/brokers-db.js b/scripts/db/brokers-db.js
index 7f4d785..77e6df3 100644
--- a/scripts/db/brokers-db.js
+++ b/scripts/db/brokers-db.js
@@ -3,7 +3,13 @@
 const { Pool } = require('pg');
 const pool = new Pool({
   host: process.env.CRE_PGHOST || '/tmp', port: +(process.env.CRE_PGPORT || 5432),
-  database: process.env.CRE_PGDATABASE || 'cre', user: process.env.CRE_PGUSER || process.env.USER || 'stevestudio2',
+  // The `cre` DB lives ONLY on the local dev/host box (owner = the mac user). On prod (crcp.agentabrams.com)
+  // there is NO cre DB, and the process runs as `root` — a role Postgres doesn't have. Guard that: never fall
+  // through to `root` (the old `process.env.USER` default silently became `root` on prod and 502'd every broker
+  // endpoint until the snapshot fallback caught it). On prod this connect just fails fast → serve.js serves the
+  // exported snapshot instead. See export/refresh-broker-snapshot + readBrokerSnap() in serve.js.
+  database: process.env.CRE_PGDATABASE || 'cre',
+  user: process.env.CRE_PGUSER || (process.env.USER && process.env.USER !== 'root' ? process.env.USER : 'macstudio3'),
   max: 6, idleTimeoutMillis: 10000, connectionTimeoutMillis: 4000
 });
 pool.on('error', () => {});
diff --git a/scripts/refresh-broker-snapshot.sh b/scripts/refresh-broker-snapshot.sh
new file mode 100755
index 0000000..afd017e
--- /dev/null
+++ b/scripts/refresh-broker-snapshot.sh
@@ -0,0 +1,28 @@
+#!/bin/bash
+# Nightly: regenerate the broker snapshot from the LOCAL cre DB (only place it lives) and push it
+# to prod (crcp.agentabrams.com has no cre DB → serves brokers from this snapshot). No prod restart
+# needed: serve.js readBrokerSnap() re-reads the file on mtime change. Keeps prod brokers fresh as
+# enrichment/new brokers land. Logs to /tmp/crcp-broker-snapshot.log.
+set -euo pipefail
+cd "$(dirname "$0")/.."
+LOG=/tmp/crcp-broker-snapshot.log
+ts() { date '+%Y-%m-%d %H:%M:%S'; }
+echo "[$(ts)] refresh start" >> "$LOG"
+
+# 1) regenerate locally (needs the cre DB on this host)
+if ! node scripts/export-brokers-snapshot.js >> "$LOG" 2>&1; then
+  echo "[$(ts)] EXPORT FAILED — leaving prod snapshot untouched" >> "$LOG"; exit 1
+fi
+
+# 2) sanity-gate: file exists, is JSON, has a plausible broker count (never push a truncated/empty snapshot)
+CNT=$(node -e 'try{const s=require("./data/brokers-snapshot.json");process.stdout.write(String((s.brokers||[]).length))}catch(e){process.stdout.write("0")}')
+if [ "${CNT:-0}" -lt 100 ]; then
+  echo "[$(ts)] SANITY FAIL — snapshot has only ${CNT} brokers (<100); NOT deploying" >> "$LOG"; exit 1
+fi
+
+# 3) push to prod (snapshot only; server auto-reloads on mtime)
+if rsync -az data/brokers-snapshot.json root@45.61.58.125:/root/public-projects/commercialrealestate/data/ >> "$LOG" 2>&1; then
+  echo "[$(ts)] refresh OK — ${CNT} brokers pushed to prod" >> "$LOG"
+else
+  echo "[$(ts)] RSYNC FAILED — prod keeps prior snapshot" >> "$LOG"; exit 1
+fi

← 3141270 CRCP: snapshot fallback for ALL broker endpoints (graph/top/ · back to Commercialrealestate · CRCP gov data: CA SF+Silicon Valley (13,714 from DRE bulk Cu a3f111a →