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

91 lines
#!/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