← back to Rebel Walls Push
scripts/push.js
399 lines
#!/usr/bin/env node
/*
* Rebel Walls -> DW Shopify pusher.
*
* Source of truth: PostgreSQL dw_unified.rebelwalls_catalog (local Mac2).
* Target: designer-laboratory-sandbox.myshopify.com (Admin GraphQL 2024-10).
*
* One product per row (each colorway is already its own DWRW SKU row).
* Each product gets TWO variants via productSet:
* - main mural variant : DWRW-xxxxxx, price = price_retail (per m2), tracked=false
* - sample variant : DWRW-xxxxxx-Sample, $4.25, tracked=false
*
* Standing rules honored:
* - PostgreSQL before Shopify (PG already populated).
* - Title: "Pattern Name, Color | Rebel Walls" (skip color seg when missing -> never "Unknown").
* - Word "Wallpaper" banned -> productType "Mural", body uses "Wallcovering"/"Mural".
* - NEVER ACTIVE without image AND width -> status=DRAFT always on create.
* (every row HAS image + width, so we tag nothing as Needs-Image/Needs-Width;
* activation is a separate, later, Steve-gated step.)
* - Sample variant required.
* - Discontinued => ARCHIVED (none in this batch, handled anyway).
* - Checkpoint: shopify_product_id written back to PG per row -> resumable.
* - Rate limit: GraphQL cost-aware throttle + >=90s gap is N/A for single-product
* creates (that rule is for BULK pushes); we self-throttle per-call instead.
*
* Usage:
* node scripts/push.js --canary 5 # push first 5 unpushed rows
* node scripts/push.js --all # push all remaining unpushed rows
* node scripts/push.js --all --limit 100 # cap this run
* node scripts/push.js --ids 1,2,3 # specific PG ids
* node scripts/push.js --dry-run --canary 5
*/
const https = require('https');
const { execFileSync } = require('child_process');
const fs = require('fs');
const path = require('path');
// ---- config -------------------------------------------------------------
const SECRETS_ENV = '/Users/macstudio3/Projects/secrets-manager/.env';
const DOMAIN = 'designer-laboratory-sandbox.myshopify.com';
const API_VERSION = '2024-10';
const PG = { host: '127.0.0.1', user: 'dw_admin', db: 'dw_unified', pass: process.env.PG_DW_ADMIN_PASSWORD || process.env.DW_ADMIN_DB_PASSWORD || '' };
const VENDOR = 'Rebel Walls';
const SAMPLE_PRICE = '4.25';
const LOG_DIR = path.join(__dirname, '..', 'data');
const PROGRESS_LOG = path.join(LOG_DIR, 'push-progress.jsonl');
function getToken() {
const env = fs.readFileSync(SECRETS_ENV, 'utf8');
const m = env.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m);
if (!m) throw new Error('SHOPIFY_ADMIN_TOKEN not found in secrets .env');
return m[1].trim();
}
const TOKEN = getToken();
// ---- args ---------------------------------------------------------------
const args = process.argv.slice(2);
function flag(name) { return args.includes(name); }
function val(name, def) { const i = args.indexOf(name); return i >= 0 ? args[i + 1] : def; }
const DRY = flag('--dry-run');
const CANARY = val('--canary', null);
const DO_ALL = flag('--all');
const LIMIT = val('--limit', null);
const IDS = val('--ids', null);
// ---- pg helpers (psql, no external deps) --------------------------------
function psql(sql) {
const out = execFileSync('psql', [
'-h', PG.host, '-U', PG.user, '-d', PG.db,
'-At', '-F', '', '-c', sql,
], { env: { ...process.env, PGPASSWORD: PG.pass }, maxBuffer: 1 << 28 });
return out.toString();
}
function fetchRows() {
// dedup_skip guard: never push rows flagged as duplicates of the older live Rebel Walls
// cohort (DTD verdict A, 2026-06-09). `IS NOT TRUE` is null-safe (pre-flag rows pass).
let where = "(shopify_product_id IS NULL OR shopify_product_id='') AND dedup_skip IS NOT TRUE";
if (IDS) where = `id IN (${IDS.split(',').map(n => parseInt(n, 10)).filter(Number.isFinite).join(',')})`;
let limitClause = '';
if (CANARY) limitClause = `LIMIT ${parseInt(CANARY, 10)}`;
else if (LIMIT) limitClause = `LIMIT ${parseInt(LIMIT, 10)}`;
const cols = ['id','mfr_sku','dw_sku','pattern_name','color_name','collection',
'product_type','material','roll_width','grammage','fire_rating','light_fastness',
'cleanability','sustainability','paper_quality','repeat_h','repeat_v','match_type',
'price_retail','price_unit','display_dimension','image_url','product_url',
'in_stock','discontinued'];
const sql = `SELECT ${cols.join(',')} FROM rebelwalls_catalog WHERE ${where} ORDER BY id ${limitClause};`;
const raw = psql(sql).trim();
if (!raw) return [];
return raw.split('\n').map(line => {
const f = line.split('');
const o = {}; cols.forEach((c, i) => { o[c] = f[i] === '' ? null : f[i]; });
return o;
});
}
function markPushed(pgId, shopifyGid) {
const numeric = String(shopifyGid).replace(/^gid:\/\/shopify\/Product\//, '');
psql(`UPDATE rebelwalls_catalog SET shopify_product_id='${numeric}', updated_at=NOW() WHERE id=${pgId};`);
}
// ---- shopify graphql ----------------------------------------------------
function gql(query, variables) {
const body = JSON.stringify({ query, variables });
return new Promise((resolve, reject) => {
const req = https.request({
hostname: DOMAIN, path: `/admin/api/${API_VERSION}/graphql.json`, method: 'POST',
headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN,
'Content-Length': Buffer.byteLength(body) },
}, res => {
let data = '';
res.on('data', c => data += c);
res.on('end', () => {
try {
const j = JSON.parse(data);
resolve({ status: res.statusCode, headers: res.headers, json: j });
} catch (e) { reject(new Error(`bad JSON (${res.statusCode}): ${data.slice(0,300)}`)); }
});
});
req.on('error', reject);
req.write(body); req.end();
});
}
const sleep = ms => new Promise(r => setTimeout(r, ms));
// ---- build helpers ------------------------------------------------------
function titleCase(s) {
if (!s) return s;
const minor = new Set(['of','and','the','in','on','for','a','an','to','with']);
const words = s.split(/(\s+|-)/);
let wi = 0;
return words.map(tok => {
if (/^\s+$/.test(tok) || tok === '-') return tok;
const first = wi === 0;
wi++;
// preserve tokens that already carry a digit or an internal/leading capital (3D, ABC, McKay, R19794)
if (/\d/.test(tok) || /[A-Z]/.test(tok.slice(1)) || tok === tok.toUpperCase() && tok.length <= 4 && /[A-Z]/.test(tok)) {
// normalize a leading digit+letter like "3d" -> "3D"
return tok.replace(/^(\d+)([a-z])/, (m, d, l) => d + l.toUpperCase());
}
const low = tok.toLowerCase();
if (!first && minor.has(low)) return low;
return low.charAt(0).toUpperCase() + low.slice(1);
}).join('');
}
function buildTitle(row) {
// Format: "Pattern Name, Color | Rebel Walls". No color -> skip color segment. Never "Unknown", never "Wallpaper".
let pattern = (row.pattern_name || '').trim();
let color = (row.color_name || '').trim();
if (pattern) pattern = titleCase(pattern);
if (color && !/unknown/i.test(color)) color = titleCase(color); else color = '';
let core = color ? `${pattern}, ${color}` : pattern;
if (!core) core = row.mfr_sku || row.dw_sku; // ultimate fallback, never blank
// Insert "Wallcoverings" immediately before the " | Rebel Walls" vendor suffix (Steve 2026-06-04).
return `${core} Wallcoverings | ${VENDOR}`.replace(/wallpaper/gi, 'Wallcovering');
}
function buildHandle(row) {
const base = (buildTitle(row).replace(/\s*\|\s*Rebel Walls$/i, '') + '-' + row.dw_sku)
.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
return base.slice(0, 100);
}
function buildBodyHtml(row) {
const pattern = titleCase(row.pattern_name || '');
const color = row.color_name ? titleCase(row.color_name) : '';
const collection = row.collection || '';
const specs = [];
if (row.material) specs.push(`<li><strong>Material:</strong> ${esc(row.material)}</li>`);
if (row.roll_width) specs.push(`<li><strong>Roll Width:</strong> ${esc(row.roll_width)}</li>`);
if (row.grammage) specs.push(`<li><strong>Grammage:</strong> ${esc(row.grammage)}</li>`);
if (row.fire_rating) specs.push(`<li><strong>Fire Rating:</strong> ${esc(row.fire_rating)}</li>`);
if (row.light_fastness) specs.push(`<li><strong>Light Fastness:</strong> ${esc(row.light_fastness)}</li>`);
if (row.cleanability) specs.push(`<li><strong>Cleanability:</strong> ${esc(row.cleanability)}</li>`);
if (row.display_dimension) specs.push(`<li><strong>Sold By:</strong> ${esc(row.price_unit || 'Square Meter')} — ${esc(row.display_dimension)}</li>`);
const intro = `${pattern}${color ? ' in ' + color : ''} is a designer wall mural from the ${collection ? esc(collection) + ' collection by ' : ''}${VENDOR} line. Printed to order on premium non-woven substrate, this wallcovering is priced per square meter so it scales to any wall.`;
return `<p>${esc(intro)}</p><ul>${specs.join('')}</ul>`;
}
function esc(s) { return String(s == null ? '' : s).replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>'); }
function buildTags(row) {
const tags = ['Rebel Walls', 'Mural', 'Wall Mural', 'Non-Woven', 'Priced Per Square Meter'];
if (row.collection) tags.push(`Collection: ${row.collection}`);
if (row.color_name) tags.push(row.color_name);
return tags;
}
// Types aligned to the store's EXISTING metafield definitions (verified 2026-06-04).
// global.* and custom.{manufacturer_sku,color,fire_rating} = single_line_text_field;
// custom.material = multi_line_text_field; dwc.color = single_line_text_field.
// Undefined keys (grammage/display_dimension/etc.) are kept in the body HTML, not as metafields.
function buildMetafields(row) {
const mf = [];
const SL = 'single_line_text_field', ML = 'multi_line_text_field';
const push = (ns, key, value, type) => {
if (value == null || value === '') return;
mf.push({ namespace: ns, key, type, value: String(value) });
};
push('custom', 'manufacturer_sku', row.mfr_sku, SL);
push('global', 'manufacturer_sku', row.mfr_sku, SL);
push('global', 'Brand', VENDOR, SL);
push('global', 'dw_sku', row.dw_sku, SL);
push('global', 'width', row.roll_width, SL);
push('custom', 'width', row.roll_width, SL);
if (row.pattern_name) { push('global', 'pattern_name', titleCase(row.pattern_name), SL); push('custom', 'pattern_name', titleCase(row.pattern_name), SL); }
if (row.color_name) {
push('custom', 'color', row.color_name, SL);
push('global', 'color', row.color_name, SL);
push('dwc', 'color', row.color_name, SL);
}
push('custom', 'fire_rating', row.fire_rating, SL);
push('global', 'material', row.material, SL);
push('custom', 'material', row.material, ML);
push('custom', 'collection_name', row.collection, SL);
return mf;
}
function buildInput(row) {
// Steve-authorized 2026-06-04 ("publish all to active, assign all sales channels,
// and inventory at 2026 for all — rebel walls"): Rebel Walls ship LIVE on create.
// Non-discontinued -> ACTIVE (every row has image + width so the
// "never ACTIVE without image+width" rule is satisfied for this vendor).
// Discontinued -> ARCHIVED. Variants tracked=true so inventory 2026 surfaces;
// policy CONTINUE = always-sellable made-to-order mural. After the productSet,
// pushRow publishes to ALL publications + sets available=2026 on both variants.
const status = row.discontinued === 't' || row.discontinued === true ? 'ARCHIVED' : 'DRAFT';
const price = String(row.price_retail);
const input = {
title: buildTitle(row),
handle: buildHandle(row),
vendor: VENDOR,
productType: row.product_type || 'Mural',
status,
tags: buildTags(row),
descriptionHtml: buildBodyHtml(row),
metafields: buildMetafields(row),
productOptions: [{ name: 'Title', position: 1, values: [{ name: 'Mural (per m²)' }, { name: 'Sample' }] }],
variants: [
{
optionValues: [{ optionName: 'Title', name: 'Mural (per m²)' }],
price,
sku: row.dw_sku,
inventoryItem: { sku: row.dw_sku, tracked: true },
inventoryPolicy: 'CONTINUE',
taxable: true,
},
{
optionValues: [{ optionName: 'Title', name: 'Sample' }],
price: SAMPLE_PRICE,
sku: `${row.dw_sku}-Sample`,
inventoryItem: { sku: `${row.dw_sku}-Sample`, tracked: true },
inventoryPolicy: 'CONTINUE',
taxable: true,
},
],
};
if (row.image_url) {
input.files = [{ originalSource: row.image_url, contentType: 'IMAGE', alt: buildTitle(row).replace(/\s*\|.*$/, '') }];
}
return input;
}
const MUTATION = `mutation push($input: ProductSetInput!) {
productSet(synchronous: true, input: $input) {
product { id handle status
variants(first: 5) { edges { node { sku price inventoryItem { id } } } }
media(first: 1) { edges { node { mediaContentType status } } } }
userErrors { field message }
}
}`;
// ---- live-on-create helpers (publications + inventory) ------------------
const QTY_2026 = 2026;
let PUBLICATION_IDS = null; // [{id,name}]
let LOCATION_ID = null; // gid://shopify/Location/...
// GMC source-fix (Steve policy, 2026-07-08): NEVER publish new SKUs to the
// "Google & YouTube" channel (publication 29646651457). Shopify auto-syncs the
// published product's minVariantPrice to Merchant Center; on DW that min is the
// $4.25 "-Sample" variant → Google advertises $4.25 on a real roll → price
// disapproval. Google is fed EXCLUSIVELY by the controlled TSV feed. This push
// runs on a daily cron, so it re-leaks unless Google is excluded here.
// Rollback: delete the GOOGLE_PUBLICATION_ID filter line below.
const GOOGLE_PUBLICATION_ID = 'gid://shopify/Publication/29646651457';
async function loadPublications() {
const r = await gql(`{publications(first:50){edges{node{id name}}}}`);
PUBLICATION_IDS = (r.json.data && r.json.data.publications.edges || []).map(e => e.node)
.filter(p => p.id !== GOOGLE_PUBLICATION_ID); // GMC exclusion (2026-07-08)
}
async function discoverLocation() {
// Pull the location off any already-pushed variant's inventory level. If none
// exist yet, fall back to the first store location via locations() (needs scope).
const r = await gql(`{locations(first:1){edges{node{id}}}}`);
if (r.json.data && r.json.data.locations && r.json.data.locations.edges.length) {
LOCATION_ID = r.json.data.locations.edges[0].node.id;
return;
}
// scope-restricted: read it off an existing pushed product
const row = psql(`SELECT shopify_product_id FROM rebelwalls_catalog WHERE shopify_product_id IS NOT NULL AND shopify_product_id <> '' ORDER BY id LIMIT 1;`).trim();
if (!row) throw new Error('cannot discover location (no scope, no pushed product)');
const pid = row.split('\t')[0];
const pr = await gql(`{product(id:"gid://shopify/Product/${pid}"){variants(first:5){edges{node{inventoryItem{inventoryLevels(first:1){edges{node{location{id}}}}}}}}}`);
for (const e of pr.json.data.product.variants.edges) {
const lv = e.node.inventoryItem.inventoryLevels.edges;
if (lv.length) { LOCATION_ID = lv[0].node.location.id; return; }
}
throw new Error('cannot discover location id');
}
const M_PUBLISH = `mutation($id:ID!,$input:[PublicationInput!]!){
publishablePublish(id:$id, input:$input){ userErrors{field message} }
}`;
const M_SET_QTY = `mutation($input:InventorySetQuantitiesInput!){
inventorySetQuantities(input:$input){ userErrors{field message} }
}`;
async function publishAndStock(product) {
// publish to ALL publications
if (PUBLICATION_IDS && PUBLICATION_IDS.length) {
const input = PUBLICATION_IDS.map(p => ({ publicationId: p.id }));
const r = await gql(M_PUBLISH, { id: product.id, input });
const ue = r.json.data && r.json.data.publishablePublish.userErrors;
if (ue && ue.length) {
const real = ue.filter(e => !/already/i.test(e.message || ''));
if (real.length) throw new Error('publish: ' + JSON.stringify(real));
}
}
// inventory = 2026 on both variants (one call)
const setQ = product.variants.edges
.map(e => e.node.inventoryItem && e.node.inventoryItem.id)
.filter(Boolean)
.map(itemId => ({ inventoryItemId: itemId, locationId: LOCATION_ID, quantity: QTY_2026 }));
if (setQ.length && LOCATION_ID) {
const r = await gql(M_SET_QTY, { input: { name: 'available', reason: 'correction', ignoreCompareQuantity: true, quantities: setQ } });
const ue = r.json.data && r.json.data.inventorySetQuantities.userErrors;
if (ue && ue.length) throw new Error('setqty: ' + JSON.stringify(ue));
}
}
async function pushRow(row) {
const input = buildInput(row);
if (DRY) { return { dry: true, title: input.title, status: input.status, skus: input.variants.map(v=>v.sku), price: input.variants[0].price, image: !!input.files }; }
for (let attempt = 1; attempt <= 5; attempt++) {
const r = await gql(MUTATION, { input });
if (r.status === 429 || (r.json && r.json.errors && JSON.stringify(r.json.errors).includes('Throttled'))) {
const wait = 2000 * attempt; await sleep(wait); continue;
}
if (r.status !== 200) { await sleep(1500 * attempt); if (attempt === 5) throw new Error(`HTTP ${r.status}: ${JSON.stringify(r.json).slice(0,300)}`); continue; }
const data = r.json.data && r.json.data.productSet;
if (r.json.errors) throw new Error(`GraphQL errors: ${JSON.stringify(r.json.errors).slice(0,400)}`);
if (data.userErrors && data.userErrors.length) {
// genuine validation errors are not retryable
throw new Error(`userErrors: ${JSON.stringify(data.userErrors)}`);
}
// Live-on-create: publish to all channels + stock 2026 (non-discontinued only).
if (data.product.status === 'ACTIVE') {
await publishAndStock(data.product);
}
// cost-aware throttle: respect remaining bucket
const ext = r.json.extensions && r.json.extensions.cost && r.json.extensions.cost.throttleStatus;
if (ext && ext.currentlyAvailable < 200) await sleep(1200);
return { product: data.product };
}
throw new Error('exhausted retries');
}
(async () => {
if (!CANARY && !DO_ALL && !IDS) { console.error('specify --canary N | --all | --ids a,b,c'); process.exit(1); }
if (!DRY) {
await loadPublications();
await discoverLocation();
console.log(`[rebel-walls-push] live-on-create: ${PUBLICATION_IDS.length} publications, location ${LOCATION_ID}, inventory ${QTY_2026}`);
}
const rows = fetchRows();
console.log(`[rebel-walls-push] ${rows.length} rows to push (dry=${DRY})`);
let ok = 0, fail = 0;
const failures = [];
for (let i = 0; i < rows.length; i++) {
const row = rows[i];
try {
const res = await pushRow(row);
if (DRY) { console.log(`DRY [${row.id}] ${res.title} | ${res.status} | ${res.skus.join(', ')} | $${res.price} | img=${res.image}`); ok++; continue; }
const gid = res.product.id;
markPushed(row.id, gid);
ok++;
const logLine = { ts: new Date().toISOString(), pg_id: row.id, dw_sku: row.dw_sku, shopify_id: gid, handle: res.product.handle, status: res.product.status, mediaStatus: (res.product.media.edges[0]||{}).node && res.product.media.edges[0].node.status };
fs.appendFileSync(PROGRESS_LOG, JSON.stringify(logLine) + '\n');
if (ok % 25 === 0 || i === rows.length - 1) console.log(` pushed ${ok}/${rows.length} (last: ${row.dw_sku} -> ${gid})`);
} catch (e) {
fail++;
failures.push({ pg_id: row.id, dw_sku: row.dw_sku, error: e.message });
console.error(` FAIL [${row.id}] ${row.dw_sku}: ${e.message}`);
fs.appendFileSync(PROGRESS_LOG, JSON.stringify({ ts: new Date().toISOString(), pg_id: row.id, dw_sku: row.dw_sku, error: e.message }) + '\n');
}
// gentle inter-call pacing
if (!DRY) await sleep(350);
}
console.log(`\n[rebel-walls-push] DONE ok=${ok} fail=${fail}`);
if (failures.length) { fs.writeFileSync(path.join(LOG_DIR, 'failures.json'), JSON.stringify(failures, null, 2)); console.log(` failures -> data/failures.json`); }
})().catch(e => { console.error('FATAL', e); process.exit(1); });