standing mac3->Kamatera catalog push: additive nightly sync of all *_catalog tables (keeps Kamatera grid enrichment — mfr-SKU search, stock — current; closes the cross-host staging-table gap)

59d5c2f8abe99b5700b21e3969a1347d44f04bef · 2026-07-08 12:33:32 -0700 · steve@designerwallcoverings.com

Files touched

A shopify/scripts/catalog-push-to-kamatera.sh

Diff

commit 59d5c2f8abe99b5700b21e3969a1347d44f04bef
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Wed Jul 8 12:33:32 2026 -0700

    standing mac3->Kamatera catalog push: additive nightly sync of all *_catalog tables (keeps Kamatera grid enrichment — mfr-SKU search, stock — current; closes the cross-host staging-table gap)
---
 shopify/scripts/catalog-push-to-kamatera.sh | 47 +++++++++++++++++++++++++++++
 1 file changed, 47 insertions(+)

diff --git a/shopify/scripts/catalog-push-to-kamatera.sh b/shopify/scripts/catalog-push-to-kamatera.sh
new file mode 100755
index 00000000..998debc7
--- /dev/null
+++ b/shopify/scripts/catalog-push-to-kamatera.sh
@@ -0,0 +1,47 @@
+#!/usr/bin/env bash
+# Standing mac3 → Kamatera catalog push (Steve 2026-07-08).
+#
+# WHY: mac3 is the canonical dw_unified where vendor *_catalog staging tables are
+# built. The all.designerwallcoverings.com grid runs on Kamatera and reads KAMATERA's
+# dw_unified, which is NOT replicated from mac3 (mac3 has 0 outgoing replication; the
+# shopify_products sync bridges products via Shopify-as-intermediary, but the *_catalog
+# staging tables have no bridge). Result: new vendor catalog data (Pierre Frey, etc.)
+# never reaches Kamatera, so grid enrichment (manufacturer-SKU search, stock, specs)
+# goes stale. This job keeps Kamatera's *_catalog tables current. ONE-WAY mac3→Kamatera.
+#
+# STRATEGY: ADDITIVE. Pass 1 = schema-only (creates any *_catalog table missing on
+# Kamatera; harmless "already exists" errors on the rest). Pass 2 = data-only with
+# ON CONFLICT DO NOTHING (adds new rows, never drops/overwrites → the grid never sees
+# an empty table mid-refresh). Row updates/deletes do NOT propagate (append-mostly
+# staging; run a manual --clean if a full re-sync is ever needed).
+set -uo pipefail
+export PATH="/usr/local/bin:/opt/homebrew/bin:/opt/homebrew/opt/postgresql@14/bin:$PATH"
+LOG="$HOME/.claude/logs/catalog-push.log"; mkdir -p "$(dirname "$LOG")"
+exec >> "$LOG" 2>&1
+echo "==== $(date '+%Y-%m-%d %H:%M:%S') catalog-push START ===="
+
+cd "$HOME/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL" || { echo "no cwd"; exit 1; }
+MAC3=$(grep -m1 '^DATABASE_URL=' .env | cut -d= -f2- | tr -d '"')
+KAM='postgresql://dw_admin@127.0.0.1:5432/dw_unified'
+
+# reachability guards — never half-run
+psql "$MAC3" -tAc 'select 1' >/dev/null 2>&1 || { echo "mac3 dw_unified unreachable — abort"; exit 1; }
+ssh -o ConnectTimeout=15 my-server "psql '$KAM' -tAc 'select 1'" >/dev/null 2>&1 || { echo "Kamatera unreachable — abort"; exit 1; }
+
+# every *_catalog table on mac3
+mapfile -t TABLES < <(psql "$MAC3" -tAc "select tablename from pg_tables where schemaname='public' and tablename ~ '^[a-z0-9_]+_catalog\$' order by 1")
+[ "${#TABLES[@]}" -eq 0 ] && { echo "no *_catalog tables on mac3 — abort"; exit 1; }
+ARGS=(); for t in "${TABLES[@]}"; do ARGS+=(-t "$t"); done
+echo "syncing ${#TABLES[@]} catalog tables"
+
+# pass 1 — ensure every table exists on Kamatera (schema-only; existing tables error harmlessly)
+pg_dump "$MAC3" "${ARGS[@]}" --schema-only --no-owner --no-privileges 2>>"$LOG" \
+  | ssh my-server "psql '$KAM' -q" 2>>"$LOG" || true
+
+# pass 2 — additive data (ON CONFLICT DO NOTHING; no drops, no overwrites)
+pg_dump "$MAC3" "${ARGS[@]}" --data-only --inserts --on-conflict-do-nothing --no-owner 2>>"$LOG" \
+  | ssh my-server "psql '$KAM' -q" 2>>"$LOG" || true
+
+# summary — spot-check a couple of key tables
+ssh my-server "psql '$KAM' -tAc \"select 'pierre_frey_catalog='||count(*) from pierre_frey_catalog union all select 'pierre_frey_fabric_catalog='||count(*) from pierre_frey_fabric_catalog\"" 2>>"$LOG" || true
+echo "==== $(date '+%Y-%m-%d %H:%M:%S') catalog-push DONE ===="

← 881cc056 auto-save: 2026-07-08T12:05:34 (11 files) — pending-approval · back to Designer Wallcoverings · auto-save: 2026-07-08T12:35:41 (6 files) — pending-approval/ 8db74464 →