← back to All Designerwallcoverings
Full Shopify image galleries: bulk-sync product_images + serve images[] in feed
08c62370a73e210f7e7ca57ddc204549eb3224ab · 2026-07-08 12:32:55 -0700 · Steve
- scripts/shopify-images-sync.mjs: Shopify Bulk Operations export of every non-archived
product's full image set -> dw_unified.product_images (201,568 images / 87,599 products)
- server.js: load product_images by product_id, add images[] + image_count to every row,
gap-fill the primary image from the gallery when featured image_url is null
- flows to /api/catalog-full so all./substitute/new inherit galleries; No-image dropped to 477
Files touched
A scripts/shopify-images-sync.mjsM server.js
Diff
commit 08c62370a73e210f7e7ca57ddc204549eb3224ab
Author: Steve <steve@designerwallcoverings.com>
Date: Wed Jul 8 12:32:55 2026 -0700
Full Shopify image galleries: bulk-sync product_images + serve images[] in feed
- scripts/shopify-images-sync.mjs: Shopify Bulk Operations export of every non-archived
product's full image set -> dw_unified.product_images (201,568 images / 87,599 products)
- server.js: load product_images by product_id, add images[] + image_count to every row,
gap-fill the primary image from the gallery when featured image_url is null
- flows to /api/catalog-full so all./substitute/new inherit galleries; No-image dropped to 477
---
scripts/shopify-images-sync.mjs | 162 ++++++++++++++++++++++++++++++++++++++++
server.js | 29 ++++++-
2 files changed, 189 insertions(+), 2 deletions(-)
diff --git a/scripts/shopify-images-sync.mjs b/scripts/shopify-images-sync.mjs
new file mode 100644
index 0000000..b439cf9
--- /dev/null
+++ b/scripts/shopify-images-sync.mjs
@@ -0,0 +1,162 @@
+#!/usr/bin/env node
+// Shopify product-images sync → dw_unified.product_images
+// ─────────────────────────────────────────────────────────────────────────────
+// The dw_unified.shopify_products mirror keeps only the FEATURED image_url; the full
+// per-product galleries live in Shopify. Steve: "need all images for unified except
+// archived or disco in all. / substitutes / new." This pulls every non-archived
+// product's COMPLETE image set via Shopify's Bulk Operations API (one server-side
+// export → a single JSONL, minutes not hours) and loads it into product_images, keyed
+// by the numeric Shopify product_id so the all-dw feed can join galleries in at serve time.
+//
+// COST: Shopify Admin API = free (rate-limited). SAFE: writes ONLY to product_images.
+// Full-refresh: rebuilds the table each run (TRUNCATE + reload) so removed images drop out.
+//
+// Usage: node scripts/shopify-images-sync.mjs
+
+import pg from 'pg';
+import fs from 'node:fs';
+import { pathToFileURL } from 'node:url';
+
+const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
+const SHOPIFY_LIB = [
+ process.env.SHOPIFY_LIB,
+ '/Users/macstudio3/Projects/designerwallcoverings/scripts/lib/shopify.mjs',
+ new URL('./lib/shopify.mjs', import.meta.url).pathname,
+].find((p) => p && fs.existsSync(p));
+if (!SHOPIFY_LIB) { console.error('Shopify client lib not found (set SHOPIFY_LIB)'); process.exit(1); }
+const { gql, ENDPOINT, TOKEN } = await import(pathToFileURL(SHOPIFY_LIB).href);
+
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+const pidOf = (gid) => (String(gid || '').match(/(\d+)\s*$/) || [])[1] || null;
+
+const DDL = `
+CREATE TABLE IF NOT EXISTS product_images (
+ id bigserial PRIMARY KEY,
+ product_id text NOT NULL,
+ handle text,
+ status text,
+ position int,
+ image_url text NOT NULL,
+ alt text,
+ width int,
+ height int,
+ synced_at timestamptz DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS product_images_pid_idx ON product_images (product_id);
+`;
+
+// One bulk query exports every non-archived product with its full image list. Shopify runs
+// it server-side and returns a JSONL where each product node and each nested image node is a
+// line; image lines carry __parentId = the product's gid.
+const BULK_QUERY = `
+{
+ products(query: "status:active OR status:draft") {
+ edges { node {
+ id handle status
+ images { edges { node { url altText width height } } }
+ } }
+ }
+}`;
+
+async function startBulk() {
+ const m = `mutation {
+ bulkOperationRunQuery(query: ${JSON.stringify(BULK_QUERY)}) {
+ bulkOperation { id status }
+ userErrors { field message }
+ }
+ }`;
+ const d = await gql(m);
+ const ue = d?.bulkOperationRunQuery?.userErrors;
+ if (ue?.length) throw new Error('bulk start userErrors: ' + JSON.stringify(ue));
+ const op = d?.bulkOperationRunQuery?.bulkOperation;
+ if (!op) throw new Error('bulk start failed: ' + JSON.stringify(d));
+ return op.id;
+}
+
+async function pollBulk() {
+ for (let i = 0; i < 600; i++) { // up to ~50 min (5s poll)
+ const d = await gql(`{ currentBulkOperation { id status errorCode objectCount url } }`);
+ const op = d?.currentBulkOperation;
+ if (!op) throw new Error('no currentBulkOperation');
+ if (op.status === 'COMPLETED') return op;
+ if (['FAILED', 'CANCELED', 'EXPIRED'].includes(op.status)) throw new Error('bulk ' + op.status + ' ' + (op.errorCode || ''));
+ if (i % 6 === 0) console.log(` bulk ${op.status}… objects=${op.objectCount || 0}`);
+ await sleep(5000);
+ }
+ throw new Error('bulk poll timed out');
+}
+
+async function downloadJsonl(url, dest) {
+ const r = await fetch(url);
+ if (!r.ok) throw new Error('download failed ' + r.status);
+ const buf = Buffer.from(await r.arrayBuffer());
+ fs.writeFileSync(dest, buf);
+ return buf.length;
+}
+
+async function main() {
+ const t0 = Date.now();
+ const pool = new pg.Pool({ connectionString: DSN, max: 3 });
+ await pool.query(DDL);
+
+ console.log('starting Shopify bulk export of product images…');
+ await startBulk();
+ const op = await pollBulk();
+ console.log(`bulk COMPLETED: ${op.objectCount} objects`);
+ if (!op.url) { console.log('no url (0 products?) — nothing to load'); await pool.end(); return; }
+
+ const jsonl = '/tmp/shopify-images-bulk.jsonl';
+ const bytes = await downloadJsonl(op.url, jsonl);
+ console.log(`downloaded ${(bytes / 1e6).toFixed(1)} MB → ${jsonl}`);
+
+ // Parse: product lines have id+handle+status; image lines have url+__parentId (the product gid).
+ // Assign position per product in the order images appear.
+ const prodMeta = new Map(); // gid → { handle, status }
+ const posByProd = new Map(); // gid → running position counter
+ const lines = fs.readFileSync(jsonl, 'utf8').split('\n');
+ let images = [], loaded = 0;
+
+ await pool.query('TRUNCATE product_images');
+ const flush = async () => {
+ if (!images.length) return;
+ // multi-row insert
+ const vals = [], params = [];
+ images.forEach((im, i) => {
+ const b = i * 8;
+ vals.push(`($${b + 1},$${b + 2},$${b + 3},$${b + 4},$${b + 5},$${b + 6},$${b + 7},$${b + 8})`);
+ params.push(im.product_id, im.handle, im.status, im.position, im.image_url, im.alt, im.width, im.height);
+ });
+ await pool.query(
+ `INSERT INTO product_images (product_id, handle, status, position, image_url, alt, width, height) VALUES ${vals.join(',')}`,
+ params);
+ loaded += images.length; images = [];
+ };
+
+ for (const line of lines) {
+ if (!line.trim()) continue;
+ let o; try { o = JSON.parse(line); } catch { continue; }
+ if (o.id && o.id.includes('/Product/')) { // product line
+ prodMeta.set(o.id, { handle: o.handle || null, status: o.status || null });
+ continue;
+ }
+ if (o.url && o.__parentId) { // image line
+ const meta = prodMeta.get(o.__parentId) || {};
+ const pid = pidOf(o.__parentId);
+ const pos = (posByProd.get(o.__parentId) || 0) + 1;
+ posByProd.set(o.__parentId, pos);
+ images.push({ product_id: pid, handle: meta.handle, status: meta.status,
+ position: pos, image_url: o.url, alt: o.altText || null, width: o.width || null, height: o.height || null });
+ if (images.length >= 500) await flush();
+ }
+ }
+ await flush();
+
+ const { rows: [c] } = await pool.query(
+ `SELECT count(*) imgs, count(distinct product_id) prods, round(avg(n),2) avg_per FROM
+ (SELECT product_id, count(*) n FROM product_images GROUP BY product_id) t`);
+ console.log(`\nimages sync DONE in ${((Date.now() - t0) / 1000).toFixed(1)}s`);
+ console.log(` ${(+c.imgs).toLocaleString()} images across ${(+c.prods).toLocaleString()} products (avg ${c.avg_per}/product)`);
+ await pool.end();
+}
+
+main().catch((e) => { console.error('images sync FAILED:', e.message); process.exit(1); });
diff --git a/server.js b/server.js
index 09ca616..ae2f6eb 100644
--- a/server.js
+++ b/server.js
@@ -192,6 +192,7 @@ let pool = null;
let FM = new Map(); // fmKey(dw_sku) → FileMaker WALLPAPER mirror row (source of truth)
let FM_MATCHED = new Set(); // fmKeys that joined a shopify_products row this snapshot
let FM_STATS = { rows: 0, matched: 0, discontinued_hidden: 0, mismatch: 0, fm_only_live: 0 };
+let IMAGES = new Map(); // product_id → [image urls] full Shopify gallery (from product_images sync)
let STOCK = new Map(); // upper(sku) → public-safe stored stock snapshot (the /livestock FREE path)
let MFR = new Map(); // upper(bare dw_sku) → manufacturer SKU, backfilled from *_catalog tables
let VENDOR_VIEWER = new Map(); // slugify(vendor) / slug → internal line-viewer URL (the vendor's live microsite)
@@ -284,6 +285,20 @@ async function loadFileMaker() {
FM_STATS.rows = map.size;
}
+// Full Shopify galleries (dw_unified.product_images, written by scripts/shopify-images-sync.mjs).
+// Steve: every non-archived/non-disco product should carry ALL its images across all./substitute/new.
+// Keyed by numeric product_id (the shopify_id tail) so the join needs no SKU normalization. A missing/
+// empty table just means single-image behavior (fully graceful).
+async function loadImages() {
+ const map = new Map();
+ try {
+ const { rows } = await pool.query(
+ `SELECT product_id, image_url FROM product_images ORDER BY product_id, position`);
+ for (const r of rows) { const a = map.get(r.product_id); if (a) a.push(r.image_url); else map.set(r.product_id, [r.image_url]); }
+ } catch (e) { console.error('product_images load failed (non-fatal — single image only):', e.message); }
+ IMAGES = map;
+}
+
// Internal line viewer per product line = the vendor's live microsite (astek-landing style).
// Resolved from the crawl artifact so ANY microsite that exists resolves with no code change;
// vendors without a live line viewer degrade gracefully (the control dims).
@@ -345,6 +360,13 @@ function deriveRow(r) {
// mfr_mismatch: both sides present AND their normalized tokens disagree → the grid flags it
// (the mfr-number verification workflow Steve does by hand in FileMaker's "verification" layouts).
const mfrMismatch = !!(fm && fmMfr && shopMfr && cleanMfr(fmMfr) !== cleanMfr(shopMfr));
+ // Full Shopify gallery (joined by numeric product_id). The primary is the mirror's featured
+ // image_url, gap-filled from the gallery when the featured is missing; `images` is the deduped
+ // set (primary first) so every surface can show all images.
+ const pid = (String(r.shopify_id || '').match(/(\d+)\s*$/) || [])[1] || null;
+ const gallery = (pid && IMAGES.get(pid)) || [];
+ const primaryImg = r.image_url || gallery[0] || null;
+ const images = primaryImg ? [primaryImg, ...gallery.filter((u) => u && u !== primaryImg)] : [];
return {
id: r.id,
sku,
@@ -370,13 +392,15 @@ function deriveRow(r) {
mfr_mismatch: mfrMismatch,
status: r.status || null,
lifecycle,
- image: r.image_url || null,
+ image: primaryImg,
+ images,
+ image_count: images.length,
price: price && price > 0 ? price : null,
price_band: priceBand(price),
colors: [...new Set(colors)],
styles: [...new Set(styles)],
materials: [...new Set(materials)],
- image_state: r.image_url ? 'Has image' : 'No image',
+ image_state: primaryImg ? 'Has image' : 'No image',
// Website = customer-facing storefront page (only when actually live on the Online Store).
url: (r.online_store_published && r.handle) ? `${STOREFRONT}/products/${r.handle}` : null,
// Internal cluster — three destinations per SKU, each resolves or the control dims:
@@ -419,6 +443,7 @@ async function loadSnapshot() {
try { await loadStock(); } catch (e) { console.error('stock load failed (non-fatal):', e.message); }
try { await loadMfr(); } catch (e) { console.error('mfr load failed (non-fatal):', e.message); }
try { await loadFileMaker(); } catch (e) { console.error('filemaker load failed (non-fatal):', e.message); }
+ try { await loadImages(); } catch (e) { console.error('images load failed (non-fatal):', e.message); }
FM_MATCHED = new Set(); // repopulated by deriveRow as it joins each row this cycle
const res = await pool.query(await snapSql());
ROWS = res.rows
← 75328f4 all.dw grid: manufacturer-SKU search — backfill mfr_sku from
·
back to All Designerwallcoverings
·
all. cards: gallery count badge + hover-cycle through full S fe345ea →