← back to Sister Parish Onboarding
add shopify push + backfill scripts; CSV import path (Shopify daily variant cap workaround)
b1825fc7f99c1ab37e41845727c7c8457c286190 · 2026-05-11 14:30:20 -0700 · Steve
Files touched
A output/created.jsonA output/failed.jsonA scripts/backfill_shopify_ids.jsA scripts/push_shopify.js
Diff
commit b1825fc7f99c1ab37e41845727c7c8457c286190
Author: Steve <steve@designerwallcoverings.com>
Date: Mon May 11 14:30:20 2026 -0700
add shopify push + backfill scripts; CSV import path (Shopify daily variant cap workaround)
---
output/created.json | 1 +
output/failed.json | 7 ++
scripts/backfill_shopify_ids.js | 92 ++++++++++++++++++++
scripts/push_shopify.js | 184 ++++++++++++++++++++++++++++++++++++++++
4 files changed, 284 insertions(+)
diff --git a/output/created.json b/output/created.json
new file mode 100644
index 0000000..0637a08
--- /dev/null
+++ b/output/created.json
@@ -0,0 +1 @@
+[]
\ No newline at end of file
diff --git a/output/failed.json b/output/failed.json
new file mode 100644
index 0000000..d332052
--- /dev/null
+++ b/output/failed.json
@@ -0,0 +1,7 @@
+[
+ {
+ "sp_id": 8974291435741,
+ "title": "Raffia Wallpaper Serendipity",
+ "err": "Shopify POST /products.json → 429: {\"errors\":{\"product\":[\"Daily variant creation limit reached. Please try again later. See https:\\/\\/help.shopify.com\\/api\\/getting-started\\/api-call-limit for more information about rate limits and how to avoid them.\"]}}"
+ }
+]
\ No newline at end of file
diff --git a/scripts/backfill_shopify_ids.js b/scripts/backfill_shopify_ids.js
new file mode 100644
index 0000000..6023a21
--- /dev/null
+++ b/scripts/backfill_shopify_ids.js
@@ -0,0 +1,92 @@
+#!/usr/bin/env node
+/**
+ * After Steve uploads the CSV, every variant in DW Shopify has SKU = `DWSP-<orig sku>`.
+ * Walk all Sister Parish products in DW Shopify, match each variant's SKU back to
+ * its `sp_product_id` in dw_unified.sisterparish_catalog, and write
+ * shopify_product_id + shopify_handle into that table.
+ *
+ * Idempotent — safe to re-run.
+ */
+require('dotenv').config({ path: require('path').join(__dirname,'..','.env') });
+const { execSync } = require('child_process');
+
+const SHOP = process.env.SHOPIFY_STORE;
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+if (!SHOP || !TOKEN) { console.error('Need SHOPIFY_STORE + SHOPIFY_ADMIN_TOKEN'); process.exit(1); }
+
+const BASE = `https://${SHOP}/admin/api/2024-10`;
+
+async function shopify(path) {
+ const res = await fetch(`${BASE}${path}`, { headers: { 'X-Shopify-Access-Token': TOKEN } });
+ const text = await res.text();
+ if (!res.ok) throw new Error(`${res.status}: ${text.slice(0,300)}`);
+ return { json: JSON.parse(text), link: res.headers.get('link') || '' };
+}
+
+function nextPageInfo(link) {
+ // Shopify cursor pagination — extract rel="next" page_info
+ const m = link.match(/<[^>]*[?&]page_info=([^&>]+)[^>]*>;\s*rel="next"/);
+ return m ? m[1] : null;
+}
+
+(async () => {
+ // Walk all Sister Parish products (paginated)
+ const all = [];
+ let path = '/products.json?vendor=Sister+Parish&status=any&limit=250&fields=id,handle,title,variants';
+ for (;;) {
+ const r = await shopify(path);
+ all.push(...r.json.products);
+ const next = nextPageInfo(r.link);
+ if (!next) break;
+ path = `/products.json?limit=250&fields=id,handle,title,variants&page_info=${next}`;
+ }
+ console.log(`Sister Parish products in DW Shopify: ${all.length}`);
+
+ if (all.length === 0) {
+ console.log('Nothing to backfill yet — re-run after CSV import finishes.');
+ return;
+ }
+
+ // Map: source_product_id (parsed from DWSP-<orig sku> back to sp_product_id) — we need a join.
+ // We'll use the PG catalog and look up sp_product_id by variant.sku (stripped of DWSP-).
+ // First, write all (shopify_product_id, shopify_handle, variant_sku_list) to /tmp, then JOIN in SQL.
+ const fs = require('fs');
+ const lines = ['shopify_product_id\tshopify_handle\torig_sku'];
+ for (const p of all) {
+ for (const v of p.variants) {
+ const orig = (v.sku || '').replace(/^DWSP-/, '');
+ lines.push(`${p.id}\t${p.handle}\t${orig}`);
+ }
+ }
+ fs.writeFileSync('/tmp/sp_shopify_map.tsv', lines.join('\n'));
+
+ const sql = `
+ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_product_id BIGINT;
+ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_handle TEXT;
+ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_synced_at TIMESTAMPTZ;
+
+CREATE TEMP TABLE sp_map (shopify_product_id BIGINT, shopify_handle TEXT, orig_sku TEXT);
+\\copy sp_map FROM '/tmp/sp_shopify_map.tsv' WITH (FORMAT csv, DELIMITER E'\\t', HEADER true);
+
+-- Match: for each catalog row, find a variant in sp_map whose orig_sku matches any variant in JSONB.
+UPDATE sisterparish_catalog c
+SET shopify_product_id = m.shopify_product_id,
+ shopify_handle = m.shopify_handle,
+ shopify_synced_at = NOW()
+FROM (
+ SELECT DISTINCT ON (c2.sp_product_id) c2.sp_product_id, m.shopify_product_id, m.shopify_handle
+ FROM sisterparish_catalog c2
+ JOIN jsonb_array_elements(c2.variants) AS v ON true
+ JOIN sp_map m ON m.orig_sku = v->>'sku'
+) m
+WHERE m.sp_product_id = c.sp_product_id;
+
+SELECT
+ COUNT(*) FILTER (WHERE shopify_product_id IS NOT NULL) AS matched,
+ COUNT(*) FILTER (WHERE shopify_product_id IS NULL) AS unmatched,
+ COUNT(*) AS total
+FROM sisterparish_catalog;
+`;
+ fs.writeFileSync('/tmp/sp_backfill.sql', sql);
+ execSync('psql dw_unified -f /tmp/sp_backfill.sql', { stdio: 'inherit' });
+})().catch(e => { console.error('FATAL', e); process.exit(1); });
diff --git a/scripts/push_shopify.js b/scripts/push_shopify.js
new file mode 100644
index 0000000..2233593
--- /dev/null
+++ b/scripts/push_shopify.js
@@ -0,0 +1,184 @@
+#!/usr/bin/env node
+/**
+ * Push Sister Parish prints to DW Shopify as DRAFT.
+ * - vendor=Sister Parish, product_type=Wallpaper, status=draft
+ * - compare_at_price = SP retail; price = SP retail / 0.85
+ * - inventory: qty=2026, tracked, policy=continue (per Steve's standing rules)
+ * - SKU prefix DWSP-
+ * - Image uploaded from src URL (Shopify pulls + rehosts)
+ * - Records shopify_product_id back into dw_unified.sisterparish_catalog
+ */
+require('dotenv').config({ path: require('path').join(__dirname,'..','.env') });
+const fs = require('fs');
+const path = require('path');
+const { execSync } = require('child_process');
+
+const SHOP = process.env.SHOPIFY_STORE;
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+if (!SHOP || !TOKEN) { console.error('Need SHOPIFY_STORE + SHOPIFY_ADMIN_TOKEN in .env'); process.exit(1); }
+
+const BASE = `https://${SHOP}/admin/api/2024-10`;
+const RATE_DELAY_MS = 350; // ~3 req/s, well under DW Plus 4 req/s
+
+const data = JSON.parse(fs.readFileSync(path.join(__dirname,'..','output','normalized.json'),'utf8'));
+
+const ARGS = new Set(process.argv.slice(2));
+const DRY = ARGS.has('--dry-run');
+const LIMIT = (() => {
+ const i = process.argv.indexOf('--limit');
+ return i > -1 ? parseInt(process.argv[i+1],10) : null;
+})();
+
+async function sleep(ms) { return new Promise(r=>setTimeout(r,ms)); }
+
+async function shopify(method, pathSuffix, body) {
+ const url = `${BASE}${pathSuffix}`;
+ const res = await fetch(url, {
+ method,
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
+ body: body ? JSON.stringify(body) : undefined
+ });
+ const text = await res.text();
+ let json;
+ try { json = JSON.parse(text); } catch { json = { raw: text }; }
+ if (!res.ok) {
+ const err = new Error(`Shopify ${method} ${pathSuffix} → ${res.status}: ${text.slice(0,400)}`);
+ err.status = res.status; err.body = json;
+ throw err;
+ }
+ return json;
+}
+
+function buildProductPayload(p) {
+ // Option axes — collapse to (Pattern, Material, Size) like the source.
+ const allOpts = p.variants.map(v => ({ o1: v.option1, o2: v.option2, o3: v.option3 }));
+ const hasOpt1 = allOpts.some(v => v.o1);
+ const hasOpt2 = allOpts.some(v => v.o2);
+ const hasOpt3 = allOpts.some(v => v.o3);
+ const options = [];
+ if (hasOpt1) options.push({ name: 'Pattern' });
+ if (hasOpt2) options.push({ name: 'Material' });
+ if (hasOpt3) options.push({ name: 'Size' });
+
+ const variants = p.variants.map(v => {
+ const variant = {
+ sku: `DWSP-${v.sku || ''}`,
+ price: v.dw_retail.toFixed(2),
+ compare_at_price: v.vendor_retail.toFixed(2),
+ inventory_management: 'shopify',
+ inventory_policy: 'continue',
+ requires_shipping: true,
+ taxable: true,
+ weight: (v.grams || 0) / 1000,
+ weight_unit: 'kg',
+ };
+ if (hasOpt1) variant.option1 = v.option1 || null;
+ if (hasOpt2) variant.option2 = v.option2 || null;
+ if (hasOpt3) variant.option3 = v.option3 || null;
+ return variant;
+ });
+
+ const tags = [...new Set([
+ ...(p.tags || []),
+ 'Sister Parish',
+ 'Wallcovering',
+ 'Wallpaper',
+ 'Print'
+ ])].join(', ');
+
+ return {
+ product: {
+ title: p.title,
+ body_html: p.body_html || '',
+ vendor: 'Sister Parish',
+ product_type: 'Wallpaper',
+ status: 'draft', // standing rule: never ACTIVE without image-gate review
+ tags,
+ handle: `sp-${p.handle}`,
+ options: options.length ? options : undefined,
+ variants,
+ images: (p.images || []).slice(0, 10).map(i => ({ src: i.src, position: i.position, alt: i.alt || p.title })),
+ metafields: [
+ { namespace: 'dw', key: 'source_url', type: 'single_line_text_field', value: p.url },
+ { namespace: 'dw', key: 'source_product_id', type: 'single_line_text_field', value: String(p.sp_product_id) },
+ { namespace: 'dw', key: 'pricing_rule', type: 'single_line_text_field', value: 'vendor_retail / 0.85' },
+ ]
+ }
+ };
+}
+
+async function setVariantInventory(variantId, qty) {
+ // Variant inventory item → set qty at DW's default location.
+ // 1) Get variant → inventory_item_id
+ const vRes = await shopify('GET', `/variants/${variantId}.json`);
+ const inventoryItemId = vRes.variant.inventory_item_id;
+ // 2) Get default location
+ const locs = await shopify('GET', '/locations.json');
+ const locationId = locs.locations[0].id;
+ // 3) Connect inventory_item to location (idempotent)
+ try {
+ await shopify('POST', '/inventory_levels/connect.json', { location_id: locationId, inventory_item_id: inventoryItemId });
+ } catch (e) { /* already connected — ignore */ }
+ // 4) Set qty
+ await shopify('POST', '/inventory_levels/set.json', {
+ location_id: locationId, inventory_item_id: inventoryItemId, available: qty
+ });
+}
+
+(async () => {
+ const products = LIMIT ? data.slice(0, LIMIT) : data;
+ console.log(`${DRY ? '[DRY-RUN] ' : ''}Pushing ${products.length} Sister Parish products as DRAFT…`);
+
+ const created = [];
+ const failed = [];
+
+ for (let i = 0; i < products.length; i++) {
+ const p = products[i];
+ const payload = buildProductPayload(p);
+ const stamp = `[${String(i+1).padStart(3,'0')}/${products.length}]`;
+ if (DRY) {
+ console.log(`${stamp} would create: ${p.title} (${payload.product.variants.length} variants, ${payload.product.images.length} images)`);
+ continue;
+ }
+ try {
+ const r = await shopify('POST', '/products.json', payload);
+ const prod = r.product;
+ created.push({ sp_id: p.sp_product_id, sf_id: prod.id, handle: prod.handle, variants: prod.variants.length });
+ console.log(`${stamp} ✓ ${p.title} → SF #${prod.id} (${prod.variants.length} variants, ${prod.images.length} images)`);
+
+ // Set inventory qty=2026 on every variant (per standing rule)
+ for (const v of prod.variants) {
+ try {
+ await setVariantInventory(v.id, 2026);
+ await sleep(RATE_DELAY_MS);
+ } catch (invErr) {
+ console.log(` · variant ${v.id} inventory set failed: ${invErr.message.slice(0,140)}`);
+ }
+ }
+ } catch (e) {
+ failed.push({ sp_id: p.sp_product_id, title: p.title, err: e.message });
+ console.log(`${stamp} ✗ ${p.title}: ${e.message.slice(0,200)}`);
+ }
+ await sleep(RATE_DELAY_MS);
+ }
+
+ console.log(`\nCreated: ${created.length} Failed: ${failed.length}`);
+ fs.writeFileSync(path.join(__dirname,'..','output','created.json'), JSON.stringify(created, null, 2));
+ if (failed.length) fs.writeFileSync(path.join(__dirname,'..','output','failed.json'), JSON.stringify(failed, null, 2));
+
+ // Write back shopify_product_id into PG.
+ if (created.length) {
+ const lines = created.map(c =>
+ `UPDATE sisterparish_catalog SET source_url = COALESCE(source_url, '') WHERE sp_product_id=${c.sp_id};`
+ );
+ // Add shopify_product_id column if not exists, then update.
+ const sql = `
+ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_product_id BIGINT;
+ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_handle TEXT;
+${created.map(c => `UPDATE sisterparish_catalog SET shopify_product_id=${c.sf_id}, shopify_handle='${c.handle.replace(/'/g,"''")}' WHERE sp_product_id=${c.sp_id};`).join('\n')}
+SELECT COUNT(*) AS with_shopify FROM sisterparish_catalog WHERE shopify_product_id IS NOT NULL;
+`;
+ fs.writeFileSync('/tmp/sp_pg_update.sql', sql);
+ execSync('psql dw_unified -f /tmp/sp_pg_update.sql', { stdio: 'inherit' });
+ }
+})().catch(e => { console.error('FATAL', e); process.exit(1); });
← e59d98a initial scaffold: Sister Parish wallcoverings onboarding (73
·
back to Sister Parish Onboarding
·
crawl Sister Parish PDPs → enrich catalog with width/repeat/ 968aee9 →