← back to Designer Wallcoverings
Cole & Son: backfill spec metafields (874 products, 11362 mf) + fix audit metafield truncation bug
b8cb44d6d0103017d9a42f41b9a590d4b054041b · 2026-05-26 10:10:50 -0700 · SteveStudio2
Files touched
A shopify/scripts/coleson-audit.jsA shopify/scripts/coleson-backfill-metafields.js
Diff
commit b8cb44d6d0103017d9a42f41b9a590d4b054041b
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Tue May 26 10:10:50 2026 -0700
Cole & Son: backfill spec metafields (874 products, 11362 mf) + fix audit metafield truncation bug
---
shopify/scripts/coleson-audit.js | 156 +++++++++++++++++++++++++
shopify/scripts/coleson-backfill-metafields.js | 135 +++++++++++++++++++++
2 files changed, 291 insertions(+)
diff --git a/shopify/scripts/coleson-audit.js b/shopify/scripts/coleson-audit.js
new file mode 100644
index 00000000..922cc3ff
--- /dev/null
+++ b/shopify/scripts/coleson-audit.js
@@ -0,0 +1,156 @@
+#!/usr/bin/env node
+// Audit every Cole & Son product on DW Shopify for:
+// - Image presence (featuredImage non-null)
+// - Width metafield (global.width / custom.width)
+// - Length metafield (global.length)
+// - Unit-of-measure metafield (global.unit_of_measure)
+// - Manufacturer SKU metafield (custom.manufacturer_sku / dwc.manufacturer_sku)
+// - Color metafield (custom.color)
+// - Variant SKU sanity (Sample present, at least one non-Sample)
+//
+// Output: per-product defect rows + summary breakdown.
+
+const fs = require('fs');
+const SHOP = process.env.SHOPIFY_STORE;
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+const ENDPOINT = `https://${SHOP}/admin/api/2024-10/graphql.json`;
+
+async function gql(query, variables = {}) {
+ for (let i = 0; i < 3; i++) {
+ const r = await fetch(ENDPOINT, { method:'POST', headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json'}, body: JSON.stringify({query, variables}) });
+ const j = await r.json();
+ if (!j.errors) return j;
+ if (i === 2) throw new Error(JSON.stringify(j.errors).slice(0,400));
+ await new Promise(s => setTimeout(s, 600 * (i+1)));
+ }
+}
+
+const Q = `
+query Page($cursor:String){
+ products(first:50, after:$cursor, query:"vendor:\\"Cole & Son\\"") {
+ edges { cursor node {
+ id title handle status
+ featuredImage { url }
+ images(first:1) { edges { node { id } } }
+ variants(first:10) { edges { node { title sku price } } }
+ mf_width_lc:metafield(namespace:"global",key:"width"){value}
+ mf_width_uc:metafield(namespace:"global",key:"Width"){value}
+ mf_width_cu:metafield(namespace:"custom",key:"width"){value}
+ mf_length_lc:metafield(namespace:"global",key:"length"){value}
+ mf_length_yd:metafield(namespace:"global",key:"Wallcover-Length(YD)"){value}
+ mf_unit_lc:metafield(namespace:"global",key:"unit_of_measure"){value}
+ mf_unit_uc:metafield(namespace:"global",key:"Unit of measure"){value}
+ mf_csku:metafield(namespace:"custom",key:"manufacturer_sku"){value}
+ mf_dsku:metafield(namespace:"dwc",key:"manufacturer_sku"){value}
+ mf_item:metafield(namespace:"global",key:"Item"){value}
+ mf_color_cu:metafield(namespace:"custom",key:"color"){value}
+ mf_color_uc:metafield(namespace:"global",key:"Color 1"){value}
+ mf_pattern:metafield(namespace:"custom",key:"name_of_pattern"){value}
+ }}
+ pageInfo { hasNextPage endCursor }
+ }
+}`;
+
+function metaMap(p) {
+ const m = {};
+ const v = (x) => (x && x.value) ? x.value : null;
+ if (v(p.mf_width_lc) || v(p.mf_width_uc) || v(p.mf_width_cu)) m['global.width'] = v(p.mf_width_lc) || v(p.mf_width_uc) || v(p.mf_width_cu);
+ if (v(p.mf_length_lc) || v(p.mf_length_yd)) m['global.length'] = v(p.mf_length_lc) || v(p.mf_length_yd);
+ if (v(p.mf_unit_lc) || v(p.mf_unit_uc)) m['global.unit_of_measure'] = v(p.mf_unit_lc) || v(p.mf_unit_uc);
+ if (v(p.mf_csku) || v(p.mf_dsku) || v(p.mf_item)) m['custom.manufacturer_sku'] = v(p.mf_csku) || v(p.mf_dsku) || v(p.mf_item);
+ if (v(p.mf_color_cu) || v(p.mf_color_uc)) m['custom.color'] = v(p.mf_color_cu) || v(p.mf_color_uc);
+ if (v(p.mf_pattern)) m['custom.name_of_pattern'] = v(p.mf_pattern);
+ return m;
+}
+
+(async () => {
+ let cursor = null, page = 0, all = [];
+ while (true) {
+ page++;
+ const { data } = await gql(Q, { cursor });
+ for (const e of data.products.edges) all.push(e.node);
+ if (!data.products.pageInfo.hasNextPage) break;
+ cursor = data.products.pageInfo.endCursor;
+ if (page > 50) break;
+ }
+ console.log(`Fetched ${all.length} Cole & Son products in ${page} pages`);
+
+ const defects = [];
+ const summary = {
+ total: all.length,
+ active: 0, draft: 0, archived: 0,
+ no_image: 0,
+ no_width_mf: 0,
+ no_length_mf: 0,
+ no_unit_mf: 0,
+ no_mfr_sku_mf: 0,
+ no_color_mf: 0,
+ no_pattern_mf: 0,
+ no_sample_variant: 0,
+ no_bolt_or_panel_variant: 0,
+ sample_only: 0,
+ activable_blocked: 0, // active products that violate the activation rule
+ };
+
+ for (const p of all) {
+ const mf = metaMap(p);
+ const variants = p.variants.edges.map(e => e.node);
+ const hasImage = !!p.featuredImage?.url || (p.images.edges.length > 0);
+ const hasSampleVariant = variants.some(v => v.title === 'Sample' || (v.sku||'').endsWith('-Sample'));
+ const hasNonSampleVariant = variants.some(v => v.title !== 'Sample' && !(v.sku||'').endsWith('-Sample'));
+ const hasWidth = !!(mf['global.width'] || mf['custom.width']);
+ const hasLength = !!(mf['global.length']);
+ const hasUnit = !!(mf['global.unit_of_measure']);
+ const hasMfrSku = !!(mf['custom.manufacturer_sku'] || mf['dwc.manufacturer_sku']);
+ const hasColor = !!(mf['custom.color'] || mf['dwc.color']);
+ const hasPattern = !!(mf['custom.name_of_pattern'] || mf['custom.pattern_name'] || mf['dwc.pattern_name']);
+
+ summary[p.status === 'ACTIVE' ? 'active' : (p.status === 'DRAFT' ? 'draft' : 'archived')]++;
+ if (!hasImage) summary.no_image++;
+ if (!hasWidth) summary.no_width_mf++;
+ if (!hasLength) summary.no_length_mf++;
+ if (!hasUnit) summary.no_unit_mf++;
+ if (!hasMfrSku) summary.no_mfr_sku_mf++;
+ if (!hasColor) summary.no_color_mf++;
+ if (!hasPattern) summary.no_pattern_mf++;
+ if (!hasSampleVariant) summary.no_sample_variant++;
+ if (!hasNonSampleVariant) summary.no_bolt_or_panel_variant++;
+ if (!hasNonSampleVariant && hasSampleVariant) summary.sample_only++;
+ if (p.status === 'ACTIVE' && (!hasImage || !hasWidth)) summary.activable_blocked++;
+
+ if (!hasImage || !hasWidth || !hasMfrSku) {
+ defects.push({
+ id: p.id, title: p.title.slice(0,80), handle: p.handle, status: p.status,
+ mfr_sku: mf['custom.manufacturer_sku'] || mf['dwc.manufacturer_sku'] || '(none)',
+ missing: [
+ !hasImage && 'IMAGE',
+ !hasWidth && 'WIDTH',
+ !hasLength && 'LENGTH',
+ !hasUnit && 'UNIT',
+ !hasMfrSku && 'MFR_SKU',
+ !hasColor && 'COLOR',
+ !hasPattern && 'PATTERN',
+ !hasSampleVariant && 'NO_SAMPLE_VARIANT',
+ !hasNonSampleVariant && 'SAMPLE_ONLY',
+ ].filter(Boolean),
+ });
+ }
+ }
+
+ console.log('\n=== SUMMARY ===');
+ for (const [k,v] of Object.entries(summary)) console.log(` ${k.padEnd(28)} ${v}`);
+
+ console.log(`\n=== TOP DEFECTS BY MISSING-FIELD COMBO ===`);
+ const combos = {};
+ for (const d of defects) {
+ const key = d.missing.join('+');
+ combos[key] = (combos[key] || 0) + 1;
+ }
+ for (const [k,n] of Object.entries(combos).sort((a,b) => b[1]-a[1]).slice(0,15)) {
+ console.log(` ${n.toString().padStart(4)} ${k}`);
+ }
+
+ const stamp = new Date().toISOString().replace(/[:.]/g,'-');
+ fs.writeFileSync(`/tmp/cole-son-pricing/audit-${stamp}.json`, JSON.stringify({ summary, defects }, null, 2));
+ console.log(`\nFull defect list saved to /tmp/cole-son-pricing/audit-${stamp}.json (${defects.length} rows)`);
+})();
diff --git a/shopify/scripts/coleson-backfill-metafields.js b/shopify/scripts/coleson-backfill-metafields.js
new file mode 100644
index 00000000..62354742
--- /dev/null
+++ b/shopify/scripts/coleson-backfill-metafields.js
@@ -0,0 +1,135 @@
+#!/usr/bin/env node
+// Bulk-backfill spec metafields on Cole & Son DW Shopify products from PG.
+// Source: /tmp/cole-son-pricing/backfill-source.jsonl (one JSON per line from dw_unified.coleson_catalog)
+// Per product:
+// custom.manufacturer_sku (mfr_sku)
+// dwc.manufacturer_sku
+// custom.color (color_name)
+// custom.real_color_name
+// custom.name_of_pattern (pattern_name)
+// global.width (`{N} Inches`)
+// global.length (`{N} Feet`) — defaults to "33 Feet"
+// global.unit_of_measure ("Priced Per Single Roll")
+// global.color (color_name)
+// global.Brand ("Cole & Son")
+// global.Collection (collection)
+// global.Country ("United Kingdom")
+//
+// Strategy: use metafieldsSet, batch ~10 per call (capped at 25 by Shopify),
+// 150ms inter-call pacing.
+
+const fs = require('fs');
+const SHOP = process.env.SHOPIFY_STORE;
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+const ENDPOINT = `https://${SHOP}/admin/api/2024-10/graphql.json`;
+const LIVE = process.argv.includes('--live');
+const LIMIT_IDX = process.argv.indexOf('--limit');
+const LIMIT = LIMIT_IDX >= 0 ? parseInt(process.argv[LIMIT_IDX+1], 10) : null;
+
+const rows = fs.readFileSync('/tmp/cole-son-pricing/backfill-source.jsonl','utf8').trim().split('\n').map(JSON.parse);
+
+// Authoritative sku→live-productId map (PG's shopify_product_id can be stale).
+const SKU2PID = JSON.parse(fs.readFileSync('/tmp/cole-son-pricing/sku-to-pid.json','utf8'));
+function resolvePid(row) {
+ // Try PG dw_sku (the Sample variant SKU) first, then base+'-Sample'.
+ const candidates = [row.dw_sku, `${(row.dw_sku||'').replace(/-Sample$/,'')}-Sample`];
+ for (const c of candidates) { if (c && SKU2PID[c]) return SKU2PID[c]; }
+ return null;
+}
+
+async function gql(query, variables = {}) {
+ for (let i = 0; i < 3; i++) {
+ const r = await fetch(ENDPOINT, { method:'POST', headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json'}, body: JSON.stringify({query, variables}) });
+ const j = await r.json();
+ if (!j.errors) return j;
+ if (i === 2) throw new Error(JSON.stringify(j.errors).slice(0,400));
+ await new Promise(s => setTimeout(s, 600 * (i+1)));
+ }
+}
+
+const MUT = `mutation($mfs:[MetafieldsSetInput!]!){metafieldsSet(metafields:$mfs){metafields{id key namespace} userErrors{field message}}}`;
+
+function buildMetafields(row) {
+ const out = [];
+ const pid = resolvePid(row);
+ if (!pid) return out;
+ const push = (ns, key, val, type='single_line_text_field') => {
+ if (val === null || val === undefined) return;
+ const sval = String(val).trim();
+ if (!sval) return;
+ out.push({ ownerId: pid, namespace: ns, key, value: sval, type });
+ };
+ push('custom', 'manufacturer_sku', row.mfr_sku);
+ push('dwc', 'manufacturer_sku', row.mfr_sku);
+ push('custom', 'color', row.color_name);
+ push('custom', 'real_color_name', row.color_name);
+ push('custom', 'name_of_pattern', row.pattern_name);
+ if (row.width_inches) {
+ push('global', 'width', `${Number(row.width_inches)} Inches`);
+ } else if (row.width) {
+ push('global', 'width', String(row.width));
+ }
+ // Length: PG roll_length may be "11" (yards) or "33" (feet). Default to 33 Feet.
+ // Cole & Son spec is 10.05m × 52cm = ~33 ft × 20.5".
+ push('global', 'length', '33 Feet');
+ push('global', 'unit_of_measure', 'Priced Per Single Roll');
+ if (row.color_name) push('global', 'Color-Way', row.color_name);
+ push('global', 'Brand', 'Cole & Son');
+ if (row.collection) push('global', 'Collection', row.collection);
+ push('global', 'Country', 'United Kingdom');
+ push('global', 'Country-of-Origin', 'United Kingdom');
+ return out;
+}
+
+async function run() {
+ const work = LIMIT ? rows.slice(0, LIMIT) : rows;
+ console.log(`mode=${LIVE ? 'LIVE' : 'DRY-RUN'} rows=${work.length}`);
+ const stamp = new Date().toISOString().replace(/[:.]/g,'-');
+ const outPath = `/tmp/cole-son-pricing/backfill-${LIVE?'live':'dry'}-${stamp}.json`;
+ const results = [];
+ let mfWritten = 0, productsTouched = 0, failures = 0;
+
+ // 1 product per call: each product ≈13 metafields, well under Shopify's 25/call cap.
+ // (2 products = 26 metafields → exceeds the cap and partially fails.)
+ const BATCH = 1;
+ for (let i = 0; i < work.length; i += BATCH) {
+ const slice = work.slice(i, i + BATCH);
+ const mfsAll = [];
+ for (const r of slice) mfsAll.push(...buildMetafields(r));
+ if (!mfsAll.length) continue;
+
+ if (!LIVE) {
+ results.push({ batch_start: i, batch_size: slice.length, mf_count: mfsAll.length });
+ if (i === 0) console.log(` first batch plan: ${JSON.stringify(mfsAll.slice(0,4), null, 0).slice(0,400)}`);
+ continue;
+ }
+
+ try {
+ const d = await gql(MUT, { mfs: mfsAll });
+ const userErrs = d.data.metafieldsSet.userErrors || [];
+ const written = d.data.metafieldsSet.metafields || [];
+ mfWritten += written.length;
+ if (userErrs.length) {
+ failures += userErrs.length;
+ results.push({ batch_start: i, status: 'partial-error', errors: userErrs.slice(0,3), written: written.length, attempted: mfsAll.length });
+ } else {
+ productsTouched += slice.length;
+ results.push({ batch_start: i, status: 'ok', written: written.length });
+ }
+ } catch (e) {
+ failures += slice.length;
+ results.push({ batch_start: i, status: 'exception', error: String(e).slice(0,300) });
+ }
+
+ if (((i / BATCH) + 1) % 20 === 0 || i + BATCH >= work.length) {
+ process.stdout.write(`\r ${Math.min(i+BATCH, work.length)}/${work.length} done · ${mfWritten} metafields · ${failures} fails `);
+ }
+ if (LIVE) await new Promise(s => setTimeout(s, 150));
+ }
+ process.stdout.write('\n');
+ fs.writeFileSync(outPath, JSON.stringify({ summary: { products: productsTouched, metafields: mfWritten, failures }, results }, null, 2));
+ console.log(`saved → ${outPath}`);
+ console.log(`Summary: ${productsTouched} products touched, ${mfWritten} metafields written, ${failures} failures`);
+}
+
+run().catch(e => { console.error(e); process.exit(1); });
← ff2dcc58 chore(git-surgery): draft surgery plan for slimming .git (pr
·
back to Designer Wallcoverings
·
Cole & Son: add mandatory $4.25 Sample variant to 26 product ec49ab47 →