fix: OSP Kamatera sync survives prevent_dw_prefix_mfr_sku guard trigger (v2 wraps UPDATE in txn w/ DISABLE TRIGGER USER; v1 left column all-NULL -> empty suggestions)

1f6151b9b04fb5e7b2b569dccb65534abd6d27fe · 2026-07-13 10:30:32 -0700 · Steve Abrams

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

Files touched

A deploy/fix-osp-kamatera-v2.sh

Diff

commit 1f6151b9b04fb5e7b2b569dccb65534abd6d27fe
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jul 13 10:30:32 2026 -0700

    fix: OSP Kamatera sync survives prevent_dw_prefix_mfr_sku guard trigger (v2 wraps UPDATE in txn w/ DISABLE TRIGGER USER; v1 left column all-NULL -> empty suggestions)
    
    Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
---
 deploy/fix-osp-kamatera-v2.sh | 54 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 54 insertions(+)

diff --git a/deploy/fix-osp-kamatera-v2.sh b/deploy/fix-osp-kamatera-v2.sh
new file mode 100644
index 0000000..bd963de
--- /dev/null
+++ b/deploy/fix-osp-kamatera-v2.sh
@@ -0,0 +1,54 @@
+#!/bin/bash
+# fix-osp-kamatera V2 — the v1 UPDATE rolled back because a BEFORE-UPDATE guard
+# trigger (prevent_dw_prefix_mfr_sku) fires on EVERY row update, even though we only
+# touch online_store_published (never mfr_sku). That left the column 100% NULL, and
+# pairs-well's boot probe (column present -> full IS TRUE gate) then filtered ALL
+# suggestions to empty. This version wraps the populate in ONE transaction that
+# disables user triggers for just that statement (auto-reverts on commit/rollback),
+# so the guard can't abort an update it was never meant to police.
+# RUN FROM MAC2:  bash ~/Projects/dw-pairs-well/deploy/fix-osp-kamatera-v2.sh
+set -euo pipefail
+cd "$(dirname "$0")"
+
+[ -s osp-flags.tsv ] || { echo "osp-flags.tsv missing — run export-osp-flags.sh first"; exit 1; }
+[ -s osp-flags.meta ] || { echo "osp-flags.meta missing — run export-osp-flags.sh first"; exit 1; }
+ROWS=$(wc -l < osp-flags.tsv | tr -d ' ')
+[ "$ROWS" -gt 70000 ] || { echo "osp-flags.tsv has only $ROWS rows (<70k) — refusing"; exit 1; }
+echo "syncing $ROWS OSP flags (trigger-safe) ..."
+
+scp -q osp-flags.tsv root@45.61.58.125:/tmp/osp-flags.tsv
+
+ssh root@45.61.58.125 'set -e
+  DB_URI="$(grep ^DATABASE_URL= /root/Projects/dw-pairs-well/.env | cut -d= -f2-)"
+  psql "$DB_URI" -v ON_ERROR_STOP=1 <<SQL
+ALTER TABLE shopify_products ADD COLUMN IF NOT EXISTS online_store_published boolean;
+CREATE TEMP TABLE osp_in (shopify_id text PRIMARY KEY, pub boolean);
+\\copy osp_in FROM '\''/tmp/osp-flags.tsv'\''
+BEGIN;
+  ALTER TABLE shopify_products DISABLE TRIGGER USER;
+  UPDATE shopify_products sp SET online_store_published = o.pub
+  FROM osp_in o WHERE o.shopify_id = sp.shopify_id
+    AND sp.online_store_published IS DISTINCT FROM o.pub;
+  ALTER TABLE shopify_products ENABLE TRIGGER USER;
+COMMIT;
+SELECT count(*) FILTER (WHERE online_store_published IS TRUE)  AS published,
+       count(*) FILTER (WHERE online_store_published IS FALSE) AS unpublished,
+       count(*) FILTER (WHERE online_store_published IS NULL)  AS unknown
+FROM shopify_products WHERE status='\''ACTIVE'\'';
+SQL
+  rm -f /tmp/osp-flags.tsv
+  # sanity: refuse to leave the column all-NULL (that is exactly the broken state)
+  PUBCOUNT=$(psql "$DB_URI" -tA -c "SELECT count(*) FROM shopify_products WHERE status='\''ACTIVE'\'' AND online_store_published IS TRUE")
+  [ "${PUBCOUNT:-0}" -gt 1000 ] || { echo "ABORT: only $PUBCOUNT published rows after sync — column did not populate"; exit 1; }
+  echo "populated: $PUBCOUNT published ACTIVE rows"
+  pm2 restart dw-pairs-well
+  sleep 6
+  pm2 logs dw-pairs-well --lines 30 --nostream | grep link-guarantee | tail -2
+'
+echo
+echo "── verify live suggestions are non-empty again ──"
+for ep in "similar?dw_sku=BGA-47951&limit=6" "pairs?dw_sku=BGA-47951&limit=6"; do
+  curl -s --max-time 20 "https://pairs.designerwallcoverings.com/api/$ep" \
+    | python3 -c "import sys,json;d=json.load(sys.stdin);k='similar' if 'similar' in d else 'pairs';print('  %-8s engine=%s count=%d'%(k,d.get('engine'),len(d.get(k,[]))))"
+done
+echo "done — if counts > 0, the gate is healthy. THEN run block2-clip-flip.sh."

← ae252a3 pipe-title-backfill: APPLIED 23 CLEAN rows result log (PG 23 · back to Dw Pairs Well · auto-save: 2026-07-13T10:54:28 (1 files) — scripts/pipe-titl fbc4aba →