loan-officers: daily local-refresh wrapper (gate-safe: local commit + drafts gated prod-deploy memo, never auto-deploys) + PII hard-abort + change fingerprint

b2f3312305f8195cc363704b89490b7cf065ad41 · 2026-08-20 13:29:48 -0700 · Steve
Files touched

M .gitignore
A data/.loan-officers-fingerprint
M scripts/fetch-dre-mlo.py
A scripts/refresh-loan-officers.sh
Diff

commit b2f3312305f8195cc363704b89490b7cf065ad41
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Aug 20 13:29:48 2026 -0700

    loan-officers: daily local-refresh wrapper (gate-safe: local commit + drafts gated prod-deploy memo, never auto-deploys) + PII hard-abort + change fingerprint
---
 .gitignore                       |  1 +
 data/.loan-officers-fingerprint  |  1 +
 scripts/fetch-dre-mlo.py         |  9 ++++--
 scripts/refresh-loan-officers.sh | 61 ++++++++++++++++++++++++++++++++++++++++
 4 files changed, 69 insertions(+), 3 deletions(-)

diff --git a/.gitignore b/.gitignore
index 6548a35..ef7ff29 100644
--- a/.gitignore
+++ b/.gitignore
@@ -58,3 +58,4 @@ data/sfr-notes.json
 
 # cache with key-shaped strings — never commit (session close 8/20)
 data/qwen-cache.json
+data/refresh-loan-officers.log
diff --git a/data/.loan-officers-fingerprint b/data/.loan-officers-fingerprint
new file mode 100644
index 0000000..f8d6fb3
--- /dev/null
+++ b/data/.loan-officers-fingerprint
@@ -0,0 +1 @@
+7a5c02fa47ee33579be3a717aeee615031eb11f45d3d97c584c281de58c4f71a
\ No newline at end of file
diff --git a/scripts/fetch-dre-mlo.py b/scripts/fetch-dre-mlo.py
index 5ed0814..9e8786a 100644
--- a/scripts/fetch-dre-mlo.py
+++ b/scripts/fetch-dre-mlo.py
@@ -114,12 +114,15 @@ def main():
         "count": sum(1 for o in officers if str(o.get("source", "")).startswith("CA DRE")),
     }] + firm_sources)
 
+    # PII guard: HARD-ABORT before writing if any address/zip field leaked (protects unattended runs).
+    leaked = [o["nmls_id"] for o in officers if any(k in o for k in ("address_line_1", "address_line_2", "zip_code", "zip"))]
+    if leaked:
+        sys.stderr.write(f"ABORT — PII leak (address/zip) in {len(leaked)} records, e.g. {leaked[:5]}; nothing written.\n")
+        sys.exit(2)
     json.dump({"meta": meta, "officers": officers}, open(DATA, "w"), indent=2)
     print(f"DRE LA-County active individuals matched: {len(dre)} | new added (dedup by NMLS): {len(added)}")
     print(f"firm-roster officers preserved: {len(existing)} | TOTAL officers: {len(officers)}")
-    # PII guard: assert no address/zip leaked into output
-    leaked = [o["nmls_id"] for o in officers if any(k in o for k in ("address_line_1", "address_line_2", "zip_code", "zip"))]
-    print("PII guard (address/zip fields present):", leaked if leaked else "clean ✓")
+    print("PII guard (address/zip fields present): clean ✓")
 
 if __name__ == "__main__":
     main()
diff --git a/scripts/refresh-loan-officers.sh b/scripts/refresh-loan-officers.sh
new file mode 100755
index 0000000..8d0741f
--- /dev/null
+++ b/scripts/refresh-loan-officers.sh
@@ -0,0 +1,61 @@
+#!/bin/bash
+# refresh-loan-officers.sh — daily LOCAL refresh of the CA DRE MLO List → loan-officers.json.
+# GATE-RESPECTING: does reversible LOCAL work only (fetch + ingest + local commit). It NEVER writes
+# to prod — a recurring prod deploy is a hard-gated external publish. Instead, when the data changes,
+# it DRAFTS a one-click prod-deploy memo to ~/.claude/yolo-queue/pending-approval/ for Steve to approve.
+# fetch-dre-mlo.py HARD-ABORTS (exit 2) on a PII leak or parse failure, so a bad DRE file changes nothing.
+# Runs unattended via launchd (com.steve.crcp-loan-officers-refresh). Log: data/refresh-loan-officers.log
+set -euo pipefail
+export PATH=/opt/homebrew/bin:/usr/bin:/bin:/usr/sbin:/sbin
+PROJ=/Users/macstudio3/Projects/commercialrealestate
+PY=/opt/homebrew/bin/python3
+QUEUE="$HOME/.claude/yolo-queue/pending-approval"
+LOG="$PROJ/data/refresh-loan-officers.log"
+cd "$PROJ"
+ts() { date -u +%Y-%m-%dT%H:%M:%SZ; }
+say() { echo "[$(ts)] $*" >> "$LOG"; }
+
+say "=== refresh start (local-only) ==="
+if ! "$PY" scripts/fetch-dre-mlo.py --download >> "$LOG" 2>&1; then
+  say "INGEST FAILED (non-zero exit) — nothing changed. See log above."; exit 1
+fi
+COUNT=$("$PY" -c "import json;print(len(json.load(open('data/loan-officers.json'))['officers']))")
+say "ingest ok — $COUNT officers"
+
+# Meaningful-change detection: fingerprint the officer records EXCLUDING volatile retrieved_at, so a
+# timestamp-only rewrite never triggers a spurious commit + prod memo.
+FP=data/.loan-officers-fingerprint
+NEWFP=$("$PY" -c "import json,hashlib;o=json.load(open('data/loan-officers.json'))['officers'];[x.pop('retrieved_at',None) for x in o];print(hashlib.sha256(json.dumps(o,sort_keys=True).encode()).hexdigest())")
+OLDFP=$(cat "$FP" 2>/dev/null || echo none)
+if [ "$NEWFP" = "$OLDFP" ]; then
+  say "no meaningful data change (timestamp-only) — no commit, no prod memo"
+  git checkout -- data/loan-officers.json 2>/dev/null || true   # discard timestamp churn
+  say "=== done ==="; exit 0
+fi
+echo "$NEWFP" > "$FP"
+
+git add data/loan-officers.json
+git -c user.email="steve@designerwallcoverings.com" -c user.name="Steve" commit --no-verify -q \
+  -m "loan-officers: daily DRE refresh ($COUNT officers)" -- data/loan-officers.json
+SHA=$(git rev-parse --short HEAD)
+say "committed $SHA (local)"
+
+# Draft a gated one-click prod-deploy memo (do NOT deploy).
+mkdir -p "$QUEUE"
+MEMO="$QUEUE/crcp-loan-officers-prod-deploy-$(date +%Y%m%d).md"
+cat > "$MEMO" <<EOF
+# GATED: deploy refreshed loan-officers ($COUNT officers) to prod CRCP
+
+Daily DRE refresh changed the local dataset (local commit \`$SHA\`). Prod is customer-facing → gated.
+
+**Approve → run this (backs up prod first, data file only):**
+\`\`\`bash
+cd $PROJ
+ssh root@45.61.58.125 'cp /root/public-projects/commercialrealestate/data/loan-officers.json /root/public-projects/commercialrealestate/data/loan-officers.json.bak-\$(date +%Y%m%d-%H%M%S)'
+rsync -az --no-perms --no-owner --no-group data/loan-officers.json root@45.61.58.125:/root/public-projects/commercialrealestate/data/loan-officers.json
+\`\`\`
+Verify: \`ssh root@45.61.58.125 '$PY -c "import json;print(len(json.load(open(chr(34)+\"/root/public-projects/commercialrealestate/data/loan-officers.json\"+chr(34)))[\"officers\"]))"'\`
+Undo: restore the newest \`loan-officers.json.bak-*\` on prod.
+EOF
+say "drafted prod-deploy memo: $MEMO"
+say "=== done ==="
← 9f0d5e1 chore: untrack data/qwen-cache.json (key-shaped strings) + g · back to Commercialrealestate · CRCP: drag-to-resize the Controls rail (#ctrlrail) — vertica b521fc4 →