Add idempotent nightly live-catalog refresh (SSH-tunnel upsert into prod; swaps in tracked links as CJ advertisers are approved)

c3798f4103d307b626a71d98bccba7d178d3371e · 2026-08-01 21:19:18 -0700 · Steve Abrams

Files touched

A scripts/refresh-live-catalog.sh

Diff

commit c3798f4103d307b626a71d98bccba7d178d3371e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat Aug 1 21:19:18 2026 -0700

    Add idempotent nightly live-catalog refresh (SSH-tunnel upsert into prod; swaps in tracked links as CJ advertisers are approved)
---
 scripts/refresh-live-catalog.sh | 39 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)

diff --git a/scripts/refresh-live-catalog.sh b/scripts/refresh-live-catalog.sh
new file mode 100755
index 0000000..759217e
--- /dev/null
+++ b/scripts/refresh-live-catalog.sh
@@ -0,0 +1,39 @@
+#!/bin/bash
+# Idempotent refresh of the LIVE catalog: re-runs the CJ ingests directly against
+# the production DB (over an SSH tunnel) so products upsert in place by their
+# (network, external_id) key — refreshing prices/stock AND swapping in TRACKED
+# affiliate links the moment each CJ advertiser gets approved. No duplicates, no
+# data loss, no truncate. Safe to run nightly.
+#
+# Run manually:   bash scripts/refresh-live-catalog.sh
+# Or via launchd (see com.steve.ids-catalog-refresh.plist).
+set -euo pipefail
+KAM=root@45.61.58.125
+PROJ="$HOME/Projects/interiordesignershowroom"
+TPORT=15432
+cd "$PROJ"
+
+echo "[$(date '+%H:%M:%S')] refresh-live-catalog starting"
+
+# 1) pull the live DB role password out of the live .env DATABASE_URL (never printed)
+LIVE_URL=$(ssh "$KAM" "grep -m1 '^DATABASE_URL=' /root/Projects/interiordesignershowroom/.env | cut -d= -f2-")
+LIVE_PW=$(printf '%s' "$LIVE_URL" | sed -E 's#.*://[^:]+:([^@]+)@.*#\1#')
+if [ -z "$LIVE_PW" ]; then echo "could not resolve live DB password"; exit 1; fi
+
+# 2) open an SSH tunnel to the live Postgres (localhost:5432 on Kamatera)
+ssh -f -N -o ExitOnForwardFailure=yes -L ${TPORT}:127.0.0.1:5432 "$KAM"
+TUNNEL_PID=$(pgrep -f "ssh -f -N -o ExitOnForwardFailure=yes -L ${TPORT}:127.0.0.1:5432 $KAM" | head -1)
+trap '[ -n "${TUNNEL_PID:-}" ] && kill "$TUNNEL_PID" 2>/dev/null || true' EXIT
+sleep 2
+
+# 3) run the ingests straight against LIVE (upsert by natural key -> tracked links update)
+export DATABASE_URL="postgresql://idshowroom:${LIVE_PW}@127.0.0.1:${TPORT}/idshowroom"
+node scripts/ingest-cj-catalog.js --per=70
+node scripts/ingest-brands.js
+node scripts/ingest-samplize.js
+
+# 4) restart the live app + report tracked-link coverage
+ssh "$KAM" "cd /root/Projects/interiordesignershowroom && pm2 restart interiordesignershowroom" >/dev/null 2>&1 || true
+TRACKED=$(PGPASSWORD="$LIVE_PW" psql "host=127.0.0.1 port=${TPORT} user=idshowroom dbname=idshowroom" -tAc \
+  "SELECT count(*) FILTER (WHERE affiliate_url ~ 'cj.com|dpbolvw|anrdoezrs|kqzyfj|tkqlhce|jdoqocy')||'/'||count(*) FROM products" 2>/dev/null || echo '?')
+echo "[$(date '+%H:%M:%S')] done. tracked-link coverage: ${TRACKED}"

← 1012ec8 Elevate site UI to steelcase.com-caliber editorial design · back to Interiordesignershowroom · Room Builder: right products = single image-forward column; 03357d1 →