← back to Designer Wallcoverings
backfill specs metafields + descriptions for 7/17 WallQuest naturals batch (136 products)
c65f22261c70895656157b9e51dfeac44bd0c590 · 2026-07-21 13:35:14 -0700 · Steve
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Files touched
A scripts/wallquest-refresh/backfill-specs-0717-batch.cjs
Diff
commit c65f22261c70895656157b9e51dfeac44bd0c590
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 21 13:35:14 2026 -0700
backfill specs metafields + descriptions for 7/17 WallQuest naturals batch (136 products)
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
---
.../backfill-specs-0717-batch.cjs | 207 +++++++++++++++++++++
1 file changed, 207 insertions(+)
diff --git a/scripts/wallquest-refresh/backfill-specs-0717-batch.cjs b/scripts/wallquest-refresh/backfill-specs-0717-batch.cjs
new file mode 100644
index 00000000..23cd36b9
--- /dev/null
+++ b/scripts/wallquest-refresh/backfill-specs-0717-batch.cjs
@@ -0,0 +1,207 @@
+// Backfill specs metafields + descriptions for the 136 ACTIVE Phillipe Romano
+// products created 2026-07-17 (WallQuest naturals batch). Steve go: 2026-07-21.
+//
+// Phase MAP: pull batch ids from the local mirror, GraphQL-fetch each product's
+// custom.manufacturer_sku, join to staging (daisy_bennett_v2_catalog,
+// daisy_bennett_catalog, phillipe_romano_catalog, wallquest_catalog).
+// Phase WRITE: metafieldsSet (global/custom/dwc/specs keys the theme reads) +
+// productUpdate descriptionHtml. Mirror stamped after each success.
+//
+// Usage: node backfill-specs-0717-batch.cjs [--map-only] [--limit N] [--sku GRS-801166]
+const { execSync } = require('child_process');
+const https = require('https');
+const fs = require('fs');
+
+const TOK = process.env.SHOPIFY_ADMIN_TOKEN;
+const DOMAIN = 'designer-laboratory-sandbox.myshopify.com';
+if (!TOK) { console.error('SHOPIFY_ADMIN_TOKEN missing'); process.exit(1); }
+
+const args = process.argv.slice(2);
+const MAP_ONLY = args.includes('--map-only');
+const LIMIT = args.includes('--limit') ? parseInt(args[args.indexOf('--limit') + 1], 10) : Infinity;
+const ONLY_SKU = args.includes('--sku') ? args[args.indexOf('--sku') + 1] : null;
+
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+function gql(query, variables) {
+ return new Promise((res, rej) => {
+ const data = JSON.stringify({ query, variables });
+ const r = https.request({
+ hostname: DOMAIN, path: '/admin/api/2024-10/graphql.json', method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOK, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) }
+ }, rs => { let d = ''; rs.on('data', c => d += c); rs.on('end', () => { try { res(JSON.parse(d)); } catch (e) { rej(new Error('bad json: ' + d.slice(0, 200))); } }); });
+ r.on('error', rej); r.write(data); r.end();
+ });
+}
+
+function psqlRows(sql) {
+ const out = execSync(`psql "host=/tmp dbname=dw_unified" -tA -F'\x1f' -c ${JSON.stringify(sql.replace(/\s+/g, ' ').trim())}`).toString().trim();
+ return out ? out.split('\n').map(l => l.split('\x1f')) : [];
+}
+
+const cleanWallpaper = s => s ? s.replace(/\bWallpapers\b/gi, 'Wallcoverings').replace(/\bWallpaper\b/gi, 'Wallcovering') : s;
+const esc = s => (s || '').replace(/'/g, "''");
+
+(async () => {
+ // ---- batch from mirror ----
+ const batch = psqlRows(`
+ SELECT DISTINCT replace(shopify_id,'gid://shopify/Product/',''), variant_sku, handle, title
+ FROM shopify_products
+ WHERE vendor='Phillipe Romano' AND created_at_shopify::date='2026-07-17' AND status='ACTIVE'
+ AND variant_sku NOT LIKE '%-Sample'
+ ORDER BY 2`);
+ console.log(`batch: ${batch.length} products`);
+
+ // ---- fetch custom.manufacturer_sku per product (chunks of 40) ----
+ const map = [];
+ for (let i = 0; i < batch.length; i += 40) {
+ const chunk = batch.slice(i, i + 40);
+ const ids = chunk.map(r => `gid://shopify/Product/${r[0]}`);
+ const q = `query($ids:[ID!]!){ nodes(ids:$ids){ ... on Product { id title
+ mfr: metafield(namespace:"custom", key:"manufacturer_sku"){value} } } }`;
+ const resp = await gql(q, { ids });
+ if (resp.errors) { console.error('GQL errors', JSON.stringify(resp.errors).slice(0, 300)); process.exit(1); }
+ for (const n of resp.data.nodes) {
+ if (!n) continue;
+ const row = chunk.find(r => n.id.endsWith('/' + r[0]));
+ map.push({ pid: r0(n.id), sku: row[1], handle: row[2], title: n.title, mfr: n.mfr ? n.mfr.value : null });
+ }
+ await sleep(300);
+ }
+ function r0(gid) { return gid.replace('gid://shopify/Product/', ''); }
+
+ // ---- join staging ----
+ const tables = [
+ { t: 'daisy_bennett_v2_catalog', cols: "mfr_sku, src_pattern, color_name, material, width, roll_length AS length, repeat_v, match_type, backing, specs::text, color_hex, ai_styles::text, coalesce(bolt_yards::text,'') AS bolt_yards, coalesce(settlement_flag,'') AS sflag" },
+ { t: 'daisy_bennett_catalog', cols: "mfr_sku, coalesce(src_pattern,pattern_name) AS src_pattern, color_name, material, width, length, repeat_v, match_type, backing, specs::text, color_hex, ai_styles::text, '' AS bolt_yards, coalesce(settlement_status,'') AS sflag" },
+ { t: 'phillipe_romano_catalog', cols: "mfr_sku, pattern_name AS src_pattern, coalesce(color_name,dw_color) AS color_name, material, width, length, repeat_v, match_type, '' AS backing, null::text AS specs, coalesce(color_hex,dominant_color_hex,'') AS color_hex, ai_styles::text, '' AS bolt_yards, '' AS sflag" },
+ { t: 'wallquest_catalog', cols: "mfr_sku, pattern_name AS src_pattern, color_name, material, width, length, repeat_v, match_type, '' AS backing, null::text AS specs, '' AS color_hex, null::text AS ai_styles, '' AS bolt_yards, '' AS sflag" },
+ ];
+ const staged = {};
+ for (const { t, cols } of tables) {
+ if (!cols) continue;
+ for (const r of psqlRows(`SELECT ${cols} FROM ${t}`)) {
+ const key = (r[0] || '').toUpperCase().trim();
+ if (key && !staged[key]) staged[key] = {
+ table: t, mfr: r[0], pattern: r[1], color: r[2], material: r[3], width: r[4], length: r[5],
+ repeat_v: r[6], match_type: r[7], backing: r[8], specs: safeJson(r[9]), color_hex: r[10],
+ ai_styles: safeJson(r[11]), bolt_yards: r[12], sflag: r[13]
+ };
+ }
+ }
+ function safeJson(s) { try { return s ? JSON.parse(s) : null; } catch { return null; } }
+
+ let matched = 0, unmatched = [];
+ for (const m of map) {
+ m.stage = m.mfr ? staged[m.mfr.toUpperCase().trim()] : null;
+ if (m.stage) matched++; else unmatched.push(`${m.sku} (mfr=${m.mfr || 'NONE'})`);
+ }
+ console.log(`matched to staging: ${matched}/${map.length}`);
+ if (unmatched.length) console.log('UNMATCHED:\n ' + unmatched.join('\n '));
+ fs.writeFileSync(__dirname + '/data/backfill-0717-map.json', JSON.stringify(map, null, 1));
+ if (MAP_ONLY) return;
+
+ // ---- metafield definition types (paginated) ----
+ const defType = {};
+ let cursor = null;
+ while (true) {
+ const resp = await gql(`query($c:String){ metafieldDefinitions(first:250, after:$c, ownerType:PRODUCT){
+ pageInfo{hasNextPage endCursor} edges{ node{ namespace key type{name} } } } }`, { c: cursor });
+ const conn = resp.data.metafieldDefinitions;
+ for (const e of conn.edges) defType[`${e.node.namespace}.${e.node.key}`] = e.node.type.name;
+ if (!conn.pageInfo.hasNextPage) break;
+ cursor = conn.pageInfo.endCursor;
+ await sleep(200);
+ }
+ console.log(`metafield definitions loaded: ${Object.keys(defType).length}`);
+
+ // ---- dedupe by product id ----
+ const seenPid = new Set();
+ const work = map.filter(m => !seenPid.has(m.pid) && seenPid.add(m.pid));
+
+ // ---- write phase ----
+ let done = 0, failed = 0, flagged = [];
+ for (const m of work) {
+ if (ONLY_SKU && m.sku !== ONLY_SKU) continue;
+ if (done >= LIMIT) break;
+ if (!m.stage) continue;
+ const s = m.stage;
+ if (s.sflag && /REVIEW|BLOCK/i.test(s.sflag)) flagged.push(`${m.sku}: ${s.sflag}`);
+
+ const sp = s.specs || {};
+ const material = cleanWallpaper(sp['Material'] || s.material || '');
+ const width = s.width || sp['Roll Width'] || '';
+ const length = s.length || sp['Roll Length'] || '';
+ const repeatV = s.repeat_v || sp['Design Repeat'] || '';
+ const match = s.match_type || sp['Design Match'] || '';
+ const matchLabel = match ? (/match/i.test(match) ? match : match + ' Match') : '';
+ const backing = s.backing || sp['Backing Material'] || '';
+ const pattern = cleanWallpaper(s.pattern || '');
+ const color = s.color || '';
+ const styles = Array.isArray(s.ai_styles) ? s.ai_styles : [];
+ const uom = 'Sold Per Single Roll';
+ const packaged = s.bolt_yards ? `${s.bolt_yards} Yard Bolt` : (length ? `Roll of ${length}` : '');
+
+ const gid = `gid://shopify/Product/${m.pid}`;
+ const mf = [];
+ const add = (ns, key, value) => {
+ if (!value || !String(value).trim()) return;
+ const t = defType[`${ns}.${key}`] || 'single_line_text_field';
+ if (!/text_field/.test(t)) return; // never write to reference/number-typed defs
+ mf.push({ ownerId: gid, namespace: ns, key, value: String(value).trim(), type: t });
+ };
+ add('global', 'width', width); add('custom', 'width', width); add('dwc', 'width', width);
+ add('global', 'length', length); add('custom', 'length', length);
+ add('global', 'repeat', repeatV); add('dwc', 'repeat', repeatV);
+ add('global', 'match', matchLabel); add('custom', 'pattern_repeat', matchLabel);
+ add('custom', 'material', material); add('dwc', 'contents', material);
+ add('custom', 'backing', backing);
+ add('global', 'unit_of_measure', uom); add('custom', 'unit_of_measure', uom);
+ add('global', 'packaged', packaged);
+ add('custom', 'pattern_name', pattern); add('dwc', 'pattern_name', pattern);
+ add('custom', 'supplier_name', 'Phillipe Romano'); add('dwc', 'real_vendor', 'Phillipe Romano');
+ add('custom', 'brand', 'Phillipe Romano'); add('dwc', 'brand', 'Phillipe Romano');
+ add('dwc', 'manufacturer_sku', s.mfr);
+ add('custom', 'color_hex', s.color_hex);
+ add('specs', 'style', styles[0]);
+ add('specs', 'pattern', sp['Design Style']);
+
+ // description: clean prose + spec list (specs visible even if a theme key mismatches)
+ const li = [];
+ if (material) li.push(`<li><strong>Material:</strong> ${material}</li>`);
+ if (width) li.push(`<li><strong>Width:</strong> ${width}</li>`);
+ if (length) li.push(`<li><strong>Roll Length:</strong> ${length}</li>`);
+ if (repeatV) li.push(`<li><strong>Pattern Repeat:</strong> ${repeatV}${matchLabel ? ` (${matchLabel})` : ''}</li>`);
+ if (backing) li.push(`<li><strong>Backing:</strong> ${backing}</li>`);
+ if (sp['Washability']) li.push(`<li><strong>Washability:</strong> ${sp['Washability']}</li>`);
+ if (sp['Removal Method']) li.push(`<li><strong>Removal:</strong> ${sp['Removal Method']}</li>`);
+ if (sp['Adhesive Application']) li.push(`<li><strong>Adhesive:</strong> ${sp['Adhesive Application']}</li>`);
+ const styleTxt = styles.length ? ` Its ${styles.map(x => x.toLowerCase()).join(' and ')} character suits residential and boutique commercial interiors alike.` : '';
+ const body =
+ `<p><strong>${pattern}${color ? ' in ' + color : ''}</strong> — a natural ${material ? material.toLowerCase() : 'fiber'} wallcovering from the Phillipe Romano collection, hand-selected for the Designer Wallcoverings trade catalog.${styleTxt}</p>` +
+ (li.length ? `<h4>Specifications</h4><ul>${li.join('')}</ul>` : '') +
+ `<p>Sold per single roll${packaged ? ` (${packaged.toLowerCase()})` : ''}. Order a sample to confirm color and texture before your project.</p>`;
+
+ // write: metafieldsSet (chunks of 25) then description
+ let ok = true;
+ for (let i = 0; i < mf.length; i += 25) {
+ const resp = await gql(`mutation($m:[MetafieldsSetInput!]!){ metafieldsSet(metafields:$m){ userErrors{field message} } }`, { m: mf.slice(i, i + 25) });
+ const errs = resp.data && resp.data.metafieldsSet ? resp.data.metafieldsSet.userErrors : [{ message: JSON.stringify(resp.errors || resp).slice(0, 200) }];
+ if (errs && errs.length) { console.error(` ❌ ${m.sku} metafields:`, JSON.stringify(errs).slice(0, 300)); ok = false; }
+ await sleep(250);
+ }
+ if (ok) {
+ const resp = await gql(`mutation($input:ProductInput!){ productUpdate(input:$input){ userErrors{field message} } }`, { input: { id: gid, descriptionHtml: body } });
+ const errs = resp.data && resp.data.productUpdate ? resp.data.productUpdate.userErrors : [{ message: JSON.stringify(resp.errors || resp).slice(0, 200) }];
+ if (errs && errs.length) { console.error(` ❌ ${m.sku} description:`, JSON.stringify(errs).slice(0, 300)); ok = false; }
+ }
+ if (ok) {
+ execSync(`psql "host=/tmp dbname=dw_unified" -q -c "UPDATE shopify_products SET fm_specs_at=now() WHERE shopify_id='gid://shopify/Product/${m.pid}'"`);
+ done++;
+ if (done <= 3 || done % 20 === 0) console.log(` ✅ ${m.sku} (${pattern} / ${color}) [${done}]`);
+ } else failed++;
+ await sleep(250);
+ }
+ console.log(`\nDONE: ${done} backfilled, ${failed} failed`);
+ if (flagged.length) console.log(`SETTLEMENT-FLAGGED (live, needs Steve review):\n ` + flagged.join('\n '));
+})().catch(e => { console.error('FATAL', e); process.exit(1); });
← 3632c282 cadence: create DRAFT (drop --activate) so cost-vendors flow
·
back to Designer Wallcoverings
·
auto-save: 2026-07-21T13:37:45 (1 files) — scripts/wallquest fe7c3722 →