scripts: Kamatera contractor data-load + deploy runbook (TK-10488) — transfer/migrate/load/verify + phase-2 deploy guards

6d30f1fc2a458225f7ae79f2cff7d714813c0f30 · 2026-08-12 10:59:45 -0700 · Steve Abrams

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

Files touched

A scripts/deploy-contractors-to-kamatera.sh

Diff

commit 6d30f1fc2a458225f7ae79f2cff7d714813c0f30
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 12 10:59:45 2026 -0700

    scripts: Kamatera contractor data-load + deploy runbook (TK-10488) — transfer/migrate/load/verify + phase-2 deploy guards
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/deploy-contractors-to-kamatera.sh | 90 +++++++++++++++++++++++++++++++
 1 file changed, 90 insertions(+)

diff --git a/scripts/deploy-contractors-to-kamatera.sh b/scripts/deploy-contractors-to-kamatera.sh
new file mode 100755
index 0000000..708ffe6
--- /dev/null
+++ b/scripts/deploy-contractors-to-kamatera.sh
@@ -0,0 +1,90 @@
+#!/usr/bin/env bash
+# TK-10488 — ship the CA CSLB contractor registry from Mac2 (staging/engine) to Kamatera
+# (prod-serving). RUN THIS ON MAC2. It transfers the data + migrations to Kamatera, applies
+# the schema, loads the 231k dated+geocoded rows, and verifies. Prod deploys of the build
+# CODE are Phase 2 (bottom of file) — do them only after Phase 1 verifies clean.
+#
+# Safe to re-run: migrations are idempotent (IF NOT EXISTS / ON CONFLICT / CREATE OR REPLACE);
+# the data load TRUNCATEs the two contractor tables before COPY so re-runs don't duplicate.
+# It writes ONLY the three ca_contractor* tables — it never touches parcels/deals/other usre data.
+set -euo pipefail
+
+KAM=root@45.61.58.125
+DUMP=/tmp/ca_contractors_data.sql.gz
+MIG_DIR="$HOME/Projects/nationalrealestate/db/migrations"
+M018="$MIG_DIR/018_ca_contractors.sql"
+M019="$MIG_DIR/019_ca_contractors_dating.sql"
+
+echo "### Pre-flight (Mac2) ###"
+for f in "$DUMP" "$M018" "$M019"; do [ -s "$f" ] || { echo "MISSING $f — regenerate the dump first"; exit 1; }; done
+echo "dump $(ls -lh "$DUMP" | awk '{print $5}'), sha $(shasum -a256 "$DUMP" | cut -c1-12)"
+
+echo "### 1. Transfer to Kamatera:/tmp ###"
+scp -q "$DUMP" "$M018" "$M019" "$KAM:/tmp/"
+
+echo "### 2-4. Detect DB, apply migrations, load data, verify (on Kamatera) ###"
+ssh "$KAM" 'bash -s' <<'REMOTE'
+set -euo pipefail
+
+# --- detect the usre project dir ---
+USRE_DIR=""
+for d in /root/public-projects/nationalrealestate /var/www/nationalrealestate /root/Projects/nationalrealestate; do
+  [ -d "$d" ] && USRE_DIR="$d" && break
+done
+echo "usre dir: ${USRE_DIR:-<not found>}"
+
+# --- resolve how to reach the usre DB (prefer the app's own DATABASE_URL, else postgres/usre) ---
+PSQL=""
+if [ -n "$USRE_DIR" ] && [ -f "$USRE_DIR/.env" ] && grep -q '^DATABASE_URL=' "$USRE_DIR/.env"; then
+  URL=$(grep '^DATABASE_URL=' "$USRE_DIR/.env" | head -1 | cut -d= -f2- | tr -d '"'"'"'"')
+  PSQL="psql $URL"
+  echo "using app DATABASE_URL"
+elif sudo -u postgres psql -tAc "select 1 from pg_database where datname='usre'" 2>/dev/null | grep -q 1; then
+  PSQL="sudo -u postgres psql -d usre"
+  echo "using sudo postgres -d usre"
+else
+  echo "!! could not resolve usre DB — inspect $USRE_DIR/.env and set PSQL manually"; exit 1
+fi
+
+echo "--- apply migrations 018 + 019 (idempotent) ---"
+$PSQL -v ON_ERROR_STOP=1 -f /tmp/018_ca_contractors.sql
+$PSQL -v ON_ERROR_STOP=1 -f /tmp/019_ca_contractors_dating.sql
+
+echo "--- load data (truncate then COPY; preserves Mac2 timestamps + geocode) ---"
+$PSQL -v ON_ERROR_STOP=1 -c "TRUNCATE ca_contractors, ca_contractor_classification RESTART IDENTITY;"
+gunzip -c /tmp/ca_contractors_data.sql.gz | $PSQL -v ON_ERROR_STOP=1
+# bump the id sequence past the loaded ids so future inserts don't collide
+$PSQL -c "SELECT setval(pg_get_serial_sequence('ca_contractors','id'), (SELECT max(id) FROM ca_contractors));" >/dev/null
+
+echo "--- VERIFY (expect: 230964 total / 220108 CLEAR / 222710 geocoded / all dated) ---"
+$PSQL -tAc "select 'rows='||count(*)||' CLEAR='||count(*) filter(where license_status='CLEAR')||' geocoded='||count(lat)||' dated='||count(source_as_of)||' classlinks='||(select count(*) from ca_contractor_classification) from ca_contractors;"
+$PSQL -tAc "select 'trigger='||tgenabled from pg_trigger where tgname='trg_ca_contractors_touch';"
+echo "--- cleanup transferred temp files ---"
+rm -f /tmp/ca_contractors_data.sql.gz /tmp/018_ca_contractors.sql /tmp/019_ca_contractors_dating.sql
+echo "PHASE 1 DONE."
+REMOTE
+
+cat <<'NEXT'
+
+########################################################################
+# PHASE 2 — DEPLOY BUILD CODE (run after Phase 1 verifies clean)
+# These are separate prod deploys; do them one at a time and smoke-test each.
+#
+# usre engine code (the /api/contractors route + relaxed auth on the browse page):
+#   - sync the repo to $USRE_DIR on Kamatera (same mechanism you normally use for usre),
+#     then: cd $USRE_DIR && npm ci --omit=dev && pm2 restart <usre-proc> --update-env
+#   - smoke: curl -s -o /dev/null -w '%{http_code}\n' https://usrealestate.agentabrams.com/api/contractors?limit=1   # expect 200
+#
+# CRCP (has .deploy.conf → use the /deploy skill):
+#   cd ~/Projects/commercialrealestate && /deploy      # then set CONTRACTORS_API_BASE to the Kamatera usre URL
+#
+# HomesOnSpec (Kamatera web:9975 — use its normal deploy path):
+#   deploy the web app, set CONTRACTORS_API_BASE + any Basic-auth creds for the usre API
+#
+# RENTV: DO NOT deploy from here — claude-rentv owns it (stand-down). Ping them to mount
+#   rentv-v1/contrib/contractors/ per its README and deploy on their pass.
+#
+# Each build reads the usre API via env CONTRACTORS_API_BASE (default http://localhost:9913).
+# On Kamatera set it to the reachable usre URL (+ Basic-auth if the API stays gated).
+########################################################################
+NEXT

← 2f6c9cd contractors API: default license_status filter to CLEAR (CSL · back to Nationalrealestate · ca_contractors: recon script + v0.18.0 (session close, TK-10 ca8f556 →