[object Object]

← back to Commercialrealestate

crcp: auto-rebuild assessor SQLite on ingest + refresh/push wrapper

7ffa8daa15e3464b3f3d9ea5bc419f84fdf65562 · 2026-07-07 13:16:39 -0700 · Steve

- ingest-assessor.js now rebuilds data/assessor.sqlite at the end of every ingest
  (event-driven, non-fatal) so the prod file can never drift from the DB
- run-assessor-refresh.sh: rebuild-from-DB -> sanity floor (>=2M rows) -> md5
  skip-if-unchanged -> data-only rsync to prod, George alert on failure (mirrors
  run-residential-refresh.sh)
- com.steve.crcp-assessor-refresh.plist: weekly (Sun 03:00) schedule, NOT loaded
  (auto-pushes to prod = Steve-gated); checksum guard makes an unchanged week a no-op

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

Diff

commit 7ffa8daa15e3464b3f3d9ea5bc419f84fdf65562
Author: Steve <steve@designerwallcoverings.com>
Date:   Tue Jul 7 13:16:39 2026 -0700

    crcp: auto-rebuild assessor SQLite on ingest + refresh/push wrapper
    
    - ingest-assessor.js now rebuilds data/assessor.sqlite at the end of every ingest
      (event-driven, non-fatal) so the prod file can never drift from the DB
    - run-assessor-refresh.sh: rebuild-from-DB -> sanity floor (>=2M rows) -> md5
      skip-if-unchanged -> data-only rsync to prod, George alert on failure (mirrors
      run-residential-refresh.sh)
    - com.steve.crcp-assessor-refresh.plist: weekly (Sun 03:00) schedule, NOT loaded
      (auto-pushes to prod = Steve-gated); checksum guard makes an unchanged week a no-op
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/com.steve.crcp-assessor-refresh.plist | 22 ++++++++
 scripts/ingest-assessor.js                    | 11 ++++
 scripts/run-assessor-refresh.sh               | 74 +++++++++++++++++++++++++++
 3 files changed, 107 insertions(+)

diff --git a/scripts/com.steve.crcp-assessor-refresh.plist b/scripts/com.steve.crcp-assessor-refresh.plist
new file mode 100644
index 0000000..54dadc6
--- /dev/null
+++ b/scripts/com.steve.crcp-assessor-refresh.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-assessor-refresh</string>
+  <key>ProgramArguments</key>
+  <array>
+    <string>/bin/bash</string>
+    <string>/Users/macstudio3/Projects/commercialrealestate/scripts/run-assessor-refresh.sh</string>
+  </array>
+  <!-- Weekly (Sun 03:00). The LA County roll is an annual snapshot, so this is deliberately
+       low-frequency; the script's md5 checksum guard makes an unchanged week a ~20s local no-op
+       (rebuild only, no 449MB rsync). -->
+  <key>StartCalendarInterval</key>
+  <array>
+    <dict><key>Weekday</key><integer>0</integer><key>Hour</key><integer>3</integer><key>Minute</key><integer>0</integer></dict>
+  </array>
+  <key>StandardOutPath</key><string>/Users/macstudio3/Projects/commercialrealestate/tmp/assessor-refresh-launchd.log</string>
+  <key>StandardErrorPath</key><string>/Users/macstudio3/Projects/commercialrealestate/tmp/assessor-refresh-launchd.log</string>
+  <key>RunAtLoad</key><false/>
+</dict>
+</plist>
diff --git a/scripts/ingest-assessor.js b/scripts/ingest-assessor.js
index f670939..ed73ebe 100644
--- a/scripts/ingest-assessor.js
+++ b/scripts/ingest-assessor.js
@@ -9,6 +9,7 @@
 const fs = require('fs');
 const path = require('path');
 const readline = require('readline');
+const { execFileSync } = require('child_process');
 const { pool } = require('./db/brokers-db');
 
 const FILE = process.argv[2] || path.join(__dirname, '..', 'data', 'raw', 'assessor-parcels-2025.ndjson');
@@ -69,5 +70,15 @@ async function main() {
   const cnt = (await pool.query('SELECT count(*)::int c FROM assessor_parcel')).rows[0].c;
   console.log(`[ingest] DONE — ${n.toLocaleString()} rows ingested (${bad} skipped) · table now ${cnt.toLocaleString()} parcels · $0 (local)`);
   await pool.end();
+
+  // Rebuild the prod SQLite artifact so it can NEVER drift from the DB: every ingest -> fresh file.
+  // (Prod has no Postgres; data/assessor.sqlite is what /api/property-history falls back to.)
+  // Non-fatal: a failed export leaves the last good file in place and warns.
+  try {
+    console.log('[ingest] rebuilding data/assessor.sqlite …');
+    execFileSync(process.execPath, [path.join(__dirname, 'export-assessor-sqlite.js')], { stdio: 'inherit' });
+  } catch (e) {
+    console.error('[ingest] WARN — SQLite rebuild failed; prod file is now STALE:', e.message);
+  }
 }
 main().catch(e => { console.error('[ingest] FAILED:', e.message); process.exit(1); });
diff --git a/scripts/run-assessor-refresh.sh b/scripts/run-assessor-refresh.sh
new file mode 100755
index 0000000..24e6a9a
--- /dev/null
+++ b/scripts/run-assessor-refresh.sh
@@ -0,0 +1,74 @@
+#!/bin/bash
+# run-assessor-refresh.sh — rebuild the assessor SQLite artifact from the current local `cre` Postgres
+# and push it DATA-ONLY to Kamatera prod (crcp.agentabrams.com). This is the assessor analog of
+# run-residential-refresh.sh: no code deploy, no pm2 reload — /api/property-history reads the shipped
+# file, so a fresh file is live on the next request.
+#
+# The SQLite is DERIVED FROM THE DB, so this script re-materializes whatever is currently in
+# cre.assessor_parcel. Re-ingesting a new roll (scripts/ingest-assessor.js) already rebuilds the file
+# locally on its own; THIS script is what gets that fresh file onto prod.
+#
+# Guards: a row/size sanity floor (never push a truncated build) + a checksum skip (don't rsync 449MB
+# when nothing changed) + a George/Gmail alert on failure. Cost: $0 (local rebuild).
+#
+# Scheduled: intended low-frequency (the LA County roll is an annual snapshot). Loaded by
+# ~/Library/LaunchAgents/com.steve.crcp-assessor-refresh.plist if/when Steve installs it.
+set -uo pipefail
+cd "$HOME/Projects/commercialrealestate" || exit 1
+export PATH="/usr/local/bin:/opt/homebrew/bin:$HOME/.npm-global/bin:$PATH"  # launchd has a minimal PATH
+
+DEPLOY_HOST=45.61.58.125
+DEPLOY_PATH=/root/public-projects/commercialrealestate
+FILE=data/assessor.sqlite
+STAMP=tmp/.assessor-sqlite.last-push.md5
+MIN_ROWS=2000000          # sanity floor: the LA County roll is ~2.43M parcels; a build below this is broken
+LOG=tmp/assessor-refresh.log
+mkdir -p tmp
+TS=$(date '+%Y-%m-%d %H:%M:%S')
+echo "===== [$TS] assessor refresh (LOCAL rebuild, \$0) =====" >> "$LOG"
+
+ALERT_TO="${ASSESSOR_REFRESH_TO:-steve@designerwallcoverings.com}"
+send_alert() { # $1=subject  $2=html-body
+  [ -f "$HOME/.claude/skills/_shared/george-send.sh" ] || { echo "[$(date '+%H:%M:%S')] no george-send.sh — alert skipped" >> "$LOG"; return; }
+  . "$HOME/.claude/skills/_shared/george-send.sh"
+  local resp; resp="$(george_send steve-office "$ALERT_TO" "$1" "$2")"
+  echo "$resp" | grep -q '"success":true' && echo "[$(date '+%H:%M:%S')] alert emailed to $ALERT_TO" >> "$LOG" \
+    || echo "[$(date '+%H:%M:%S')] alert email FAILED: $resp" >> "$LOG"
+}
+
+# 1) Rebuild the SQLite from the current DB.
+echo "[$(date '+%H:%M:%S')] rebuilding $FILE …" >> "$LOG"
+BUILD_JSON=$(node scripts/export-assessor-sqlite.js 2>>"$LOG")
+BUILD_RC=$?
+ROWS=$(node -e "try{console.log(JSON.parse(process.argv[1]).rows)}catch(e){console.log(0)}" "$BUILD_JSON" 2>/dev/null || echo 0)
+echo "[$(date '+%H:%M:%S')] build rc=$BUILD_RC rows=$ROWS" >> "$LOG"
+
+# 2) Sanity gate — never push a truncated/failed build; keep prod as-is.
+if [ "$BUILD_RC" -ne 0 ] || [ "${ROWS:-0}" -lt "$MIN_ROWS" ]; then
+  echo "[$(date '+%H:%M:%S')] ABORT push — build failed or below floor ($ROWS < $MIN_ROWS). Prod untouched." >> "$LOG"
+  send_alert "⚠ CRCP assessor refresh ABORTED — bad build ($(date '+%Y-%m-%d %H:%M'))" \
+    "<div style=\"font-family:-apple-system,Helvetica,sans-serif;color:#222\"><h3 style=\"color:#b23b3b\">⚠ Assessor SQLite rebuild failed — prod left untouched</h3><div>The rebuild produced <b>$ROWS</b> rows (floor $MIN_ROWS, rc=$BUILD_RC). The push was blocked; the last good file is still live.</div><div style=\"font-size:13px;color:#444;margin-top:8px\">Check <code>tmp/assessor-refresh.log</code> and that cre.assessor_parcel is populated.</div></div>"
+  exit 2
+fi
+
+# 3) Checksum skip — the roll rarely changes; don't ship 449MB for an identical file.
+NEW_MD5=$(md5 -q "$FILE" 2>/dev/null || md5sum "$FILE" | awk '{print $1}')
+OLD_MD5=$(cat "$STAMP" 2>/dev/null || echo "")
+if [ "$NEW_MD5" = "$OLD_MD5" ]; then
+  echo "[$(date '+%H:%M:%S')] unchanged (md5 $NEW_MD5) — skip rsync. DONE · \$0." >> "$LOG"
+  exit 0
+fi
+
+# 4) Push DATA-ONLY to prod (one file). No code, no reload.
+echo "[$(date '+%H:%M:%S')] rsync $FILE ($(du -h "$FILE" | cut -f1)) -> prod …" >> "$LOG"
+rsync -az "$FILE" "root@${DEPLOY_HOST}:${DEPLOY_PATH}/data/" >> "$LOG" 2>&1
+RSYNC_RC=$?
+if [ "$RSYNC_RC" -eq 0 ]; then
+  echo "$NEW_MD5" > "$STAMP"
+  echo "[$(date '+%H:%M:%S')] DONE — pushed · rows=$ROWS · \$0 · live: https://crcp.agentabrams.com/mls.html" >> "$LOG"
+else
+  echo "[$(date '+%H:%M:%S')] rsync FAILED rc=$RSYNC_RC" >> "$LOG"
+  send_alert "⚠ CRCP assessor refresh — rsync to prod FAILED ($(date '+%Y-%m-%d %H:%M'))" \
+    "<div style=\"font-family:-apple-system,Helvetica,sans-serif;color:#222\"><h3 style=\"color:#b23b3b\">⚠ Fresh assessor file built but not pushed</h3><div>The rebuild succeeded (<b>$ROWS</b> rows) but the rsync to Kamatera <b>failed</b> (rc=$RSYNC_RC). Prod is showing the previous file.</div><div style=\"font-size:13px;color:#444;margin-top:8px\">Check SSH to root@$DEPLOY_HOST and <code>tmp/assessor-refresh.log</code>.</div></div>"
+fi
+exit $RSYNC_RC

← df42174 chore: v0.3.1 (session close)  ·  back to Commercialrealestate  ·  auto-save: 2026-07-07T13:30:45 (1 files) — data/last-refresh 18f673e →