← back to Designer Wallcoverings
Phase 3: Shopify-CDN image-URL refresh script for hollywood/ralph_lauren stale-URL runbook
63d21512edc64288634a7806d994f37fd6b8433f · 2026-05-18 17:21:06 -0700 · SteveStudio2
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
A phase3-shopify-cdn-refresh.js
Diff
commit 63d21512edc64288634a7806d994f37fd6b8433f
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Mon May 18 17:21:06 2026 -0700
Phase 3: Shopify-CDN image-URL refresh script for hollywood/ralph_lauren stale-URL runbook
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
phase3-shopify-cdn-refresh.js | 236 ++++++++++++++++++++++++++++++++++++++++++
1 file changed, 236 insertions(+)
diff --git a/phase3-shopify-cdn-refresh.js b/phase3-shopify-cdn-refresh.js
new file mode 100644
index 00000000..db8e23ff
--- /dev/null
+++ b/phase3-shopify-cdn-refresh.js
@@ -0,0 +1,236 @@
+#!/usr/bin/env node
+/*
+ * Phase 3 — DW stale-URL runbook.
+ * Refresh broken Shopify-CDN image URLs for hollywood + ralph_lauren vendor_catalog rows.
+ *
+ * Mode:
+ * node phase3-shopify-cdn-refresh.js sample -> verify 10 affected products, no writes
+ * node phase3-shopify-cdn-refresh.js run -> process ALL affected rows, write UPDATEs
+ *
+ * HARD RULES:
+ * - Only UPDATE vendor_catalog.image_url
+ * - Only affected hollywood/ralph_lauren http_404 rows
+ * - Only to a URL verified HTTP 200 + image content-type
+ * - Never null an image_url
+ * - Read-only against Shopify
+ */
+'use strict';
+const fs = require('fs');
+const { Client } = require('pg');
+
+const ENV = fs.readFileSync('/Users/stevestudio2/Projects/secrets-manager/.env', 'utf8');
+function envGet(k) {
+ const m = ENV.match(new RegExp('^' + k + '=(.*)$', 'm'));
+ return m ? m[1].trim() : null;
+}
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const TOKEN = envGet('SHOPIFY_ADMIN_TOKEN');
+const GQL = `https://${SHOP}/admin/api/2024-07/graphql.json`;
+const LOG = '/Users/stevestudio2/Projects/Designer-Wallcoverings/phase3-shopify-cdn-refresh.log';
+
+if (!TOKEN) { console.error('No SHOPIFY_ADMIN_TOKEN'); process.exit(1); }
+
+const MODE = process.argv[2];
+if (!['sample', 'run'].includes(MODE)) {
+ console.error('usage: phase3-shopify-cdn-refresh.js sample|run [vendor]');
+ process.exit(1);
+}
+// Optional 3rd arg restricts to one vendor. RL is excluded from `run` because
+// its Shopify products were deleted (not re-versioned) — no current image exists.
+const ONLY_VENDOR = process.argv[3] || null;
+
+function log(msg) {
+ const line = `[${new Date().toISOString()}] ${msg}`;
+ console.log(line);
+ fs.appendFileSync(LOG, line + '\n');
+}
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+// ---- Shopify GraphQL with throttle/retry ----
+async function shopifyGQL(query, variables, attempt = 0) {
+ const res = await fetch(GQL, {
+ method: 'POST',
+ headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN },
+ body: JSON.stringify({ query, variables }),
+ });
+ if (res.status === 429) {
+ const wait = 2000 * (attempt + 1);
+ log(` HTTP 429 from Shopify, backing off ${wait}ms`);
+ await sleep(wait);
+ return shopifyGQL(query, variables, attempt + 1);
+ }
+ const body = await res.json();
+ if (body.errors) {
+ const throttled = JSON.stringify(body.errors).includes('THROTTLED');
+ if (throttled && attempt < 6) {
+ const wait = 2000 * (attempt + 1);
+ log(` THROTTLED, backing off ${wait}ms`);
+ await sleep(wait);
+ return shopifyGQL(query, variables, attempt + 1);
+ }
+ throw new Error('GraphQL errors: ' + JSON.stringify(body.errors));
+ }
+ // throttle-aware pacing
+ const cost = body.extensions && body.extensions.cost;
+ if (cost && cost.throttleStatus) {
+ const ts = cost.throttleStatus;
+ if (ts.currentlyAvailable < 200) await sleep(600);
+ }
+ return body.data;
+}
+
+const Q_PRODUCT = `query($id: ID!) {
+ product(id: $id) {
+ id
+ status
+ featuredImage { url }
+ media(first: 5) { edges { node { ... on MediaImage { image { url } } } } }
+ }
+}`;
+
+// pick current image url from a product node
+function imageFromProduct(p) {
+ if (!p) return null;
+ if (p.featuredImage && p.featuredImage.url) return p.featuredImage.url;
+ if (p.media && p.media.edges) {
+ for (const e of p.media.edges) {
+ if (e.node && e.node.image && e.node.image.url) return e.node.image.url;
+ }
+ }
+ return null;
+}
+
+// ---- verify a URL is live image ----
+async function verifyImage(url) {
+ try {
+ const res = await fetch(url, { method: 'GET', headers: { 'User-Agent': 'DW-Phase3/1.0' } });
+ if (res.status !== 200) return { ok: false, reason: 'http_' + res.status };
+ const ct = res.headers.get('content-type') || '';
+ if (!ct.startsWith('image/')) return { ok: false, reason: 'content-type:' + ct };
+ return { ok: true, ct };
+ } catch (e) {
+ return { ok: false, reason: 'fetch_err:' + e.message };
+ }
+}
+
+// ---- resolve a vendor_catalog row to a Shopify product GID ----
+// hollywood: direct shopify_product_id. ralph_lauren: best shopify_products row by mfr_sku.
+async function resolveGID(pg, row) {
+ if (row.shopify_product_id) {
+ return 'gid://shopify/Product/' + row.shopify_product_id;
+ }
+ // fall back to shopify_products match on mfr_sku (RL path)
+ const r = await pg.query(
+ `SELECT shopify_id, status FROM shopify_products
+ WHERE mfr_sku = $1 AND shopify_id LIKE 'gid://shopify/Product/%'
+ ORDER BY CASE upper(status)
+ WHEN 'ACTIVE' THEN 0 WHEN 'DRAFT' THEN 1
+ WHEN 'ARCHIVED' THEN 2 ELSE 3 END,
+ updated_at_shopify DESC NULLS LAST
+ LIMIT 1`,
+ [row.mfr_sku]
+ );
+ if (r.rows.length) return r.rows[0].shopify_id;
+ return null;
+}
+
+const SELECT_AFFECTED = `
+ SELECT vc.id, vc.vendor_code, vc.mfr_sku, vc.dw_sku, vc.shopify_product_id, vc.image_url
+ FROM vendor_catalog vc
+ JOIN image_hashes ih ON ih.url = vc.image_url
+ WHERE vc.vendor_code IN ('hollywood','ralph_lauren')
+ AND ih.status = 'http_404'
+ ORDER BY vc.vendor_code, vc.id`;
+
+async function main() {
+ // connect via local unix socket as current OS user (same path psql uses)
+ const pg = new Client({
+ database: 'dw_unified',
+ host: '/tmp',
+ user: process.env.USER || 'stevestudio2',
+ });
+ await pg.connect();
+ log(`=== Phase 3 ${MODE.toUpperCase()} start ===`);
+
+ let rows = (await pg.query(SELECT_AFFECTED)).rows;
+ log(`Affected rows total (both vendors): ${rows.length}`);
+ if (ONLY_VENDOR) {
+ rows = rows.filter((r) => r.vendor_code === ONLY_VENDOR);
+ log(`Restricted to vendor=${ONLY_VENDOR}: ${rows.length} rows`);
+ }
+
+ if (MODE === 'sample') {
+ // 5 hollywood + 5 ralph_lauren
+ const hw = rows.filter((r) => r.vendor_code === 'hollywood').slice(0, 5);
+ const rl = rows.filter((r) => r.vendor_code === 'ralph_lauren').slice(0, 5);
+ rows = [...hw, ...rl];
+ log(`Sample set: ${rows.length} (5 hollywood + 5 ralph_lauren)`);
+ }
+
+ const stats = {
+ hollywood: { updated: 0, skip_no_img: 0, skip_no_gid: 0, skip_verify_fail: 0, err: 0 },
+ ralph_lauren: { updated: 0, skip_no_img: 0, skip_no_gid: 0, skip_verify_fail: 0, err: 0 },
+ };
+
+ let i = 0;
+ for (const row of rows) {
+ i++;
+ const s = stats[row.vendor_code];
+ try {
+ const gid = await resolveGID(pg, row);
+ if (!gid) {
+ s.skip_no_gid++;
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} mfr_sku=${row.mfr_sku} -> SKIP no shopify GID`);
+ continue;
+ }
+ const data = await shopifyGQL(Q_PRODUCT, { id: gid });
+ const p = data.product;
+ if (!p) {
+ s.skip_no_img++;
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} ${gid} -> SKIP product not found (deleted)`);
+ continue;
+ }
+ const newUrl = imageFromProduct(p);
+ if (!newUrl) {
+ s.skip_no_img++;
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} ${gid} status=${p.status} -> SKIP no image on Shopify`);
+ continue;
+ }
+ const v = await verifyImage(newUrl);
+ if (!v.ok) {
+ s.skip_verify_fail++;
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} -> SKIP new url failed verify (${v.reason}) url=${newUrl}`);
+ continue;
+ }
+ if (MODE === 'sample') {
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} status=${p.status} VERIFIED 200 ${v.ct} -> ${newUrl}`);
+ s.updated++; // counts as "would update"
+ } else {
+ const upd = await pg.query(
+ `UPDATE vendor_catalog SET image_url=$1 WHERE id=$2 AND image_url=$3`,
+ [newUrl, row.id, row.image_url]
+ );
+ if (upd.rowCount === 1) {
+ s.updated++;
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} status=${p.status} UPDATED -> ${newUrl}`);
+ } else {
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} -> no-op (rowCount=${upd.rowCount}, already changed)`);
+ }
+ }
+ } catch (e) {
+ s.err++;
+ log(`[${i}/${rows.length}] id=${row.id} ${row.vendor_code} -> ERROR ${e.message}`);
+ await sleep(1000);
+ }
+ }
+
+ log(`=== Phase 3 ${MODE.toUpperCase()} summary ===`);
+ for (const v of ['hollywood', 'ralph_lauren']) {
+ const s = stats[v];
+ log(` ${v}: ${MODE === 'sample' ? 'verified' : 'updated'}=${s.updated} skip_no_image=${s.skip_no_img} skip_no_gid=${s.skip_no_gid} skip_verify_fail=${s.skip_verify_fail} errors=${s.err}`);
+ }
+ await pg.end();
+ log(`=== Phase 3 ${MODE.toUpperCase()} done ===`);
+}
+
+main().catch((e) => { log('FATAL ' + e.stack); process.exit(1); });
← f3c52e53 scrub last plaintext basic-auth password from progress doc +
·
back to Designer Wallcoverings
·
snapshot — gitify backup 2026-05-19 a52d295a →