← back to Designer Wallcoverings
phase3-shopify-cdn-refresh.js
237 lines
#!/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/macstudio3/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/macstudio3/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); });