← back to Designer Wallcoverings
chore: product_type casing normalize scripts (Wolf Gordon 483 + canonical 842) — session close
6319ed462aabdcd9b85337b9ce2dc0402ad27ce9 · 2026-06-30 11:31:13 -0700 · Steve
Files touched
M shopify/.gitignoreA shopify/scripts/reconcile/fix-canonical-china-seas-type.sqlA shopify/scripts/reconcile/fix-live-shopify-product-type.jsA shopify/scripts/reconcile/normalize-product-type-local.shA shopify/scripts/reconcile/rollback-product-type-local.sql
Diff
commit 6319ed462aabdcd9b85337b9ce2dc0402ad27ce9
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jun 30 11:31:13 2026 -0700
chore: product_type casing normalize scripts (Wolf Gordon 483 + canonical 842) — session close
---
shopify/.gitignore | 3 +
.../reconcile/fix-canonical-china-seas-type.sql | 30 ++++++
.../reconcile/fix-live-shopify-product-type.js | 112 +++++++++++++++++++++
.../reconcile/normalize-product-type-local.sh | 63 ++++++++++++
.../reconcile/rollback-product-type-local.sql | 33 ++++++
5 files changed, 241 insertions(+)
diff --git a/shopify/.gitignore b/shopify/.gitignore
index be20e58a..4daee461 100644
--- a/shopify/.gitignore
+++ b/shopify/.gitignore
@@ -65,3 +65,6 @@ pj-canary/pj-canary.log
pj-canary/pj-canary-status.json
audit/collection-hero-render-progress.json
audit/hero-renders/
+
+# reconcile generated backups
+scripts/reconcile/backup-product-type-local-*.json
diff --git a/shopify/scripts/reconcile/fix-canonical-china-seas-type.sql b/shopify/scripts/reconcile/fix-canonical-china-seas-type.sql
new file mode 100644
index 00000000..065e3ff7
--- /dev/null
+++ b/shopify/scripts/reconcile/fix-canonical-china-seas-type.sql
@@ -0,0 +1,30 @@
+-- fix-canonical-china-seas-type.sql (GATED — canonical Kamatera dw_unified write)
+-- DTD verdict A (2026-06-30): normalize china_seas_catalog.product_type casing on
+-- the CANONICAL Kamatera dw_unified. 842 rows 'wallcovering' -> 'Wallcovering'.
+-- Source-data table only (NONE linked to Shopify). TYPE ONLY. Reversible.
+--
+-- Run pattern (per CNCP-memo psql-over-ssh rule — MUST use sudo -u postgres):
+-- ssh my-server "sudo -u postgres psql -d dw_unified -v ON_ERROR_STOP=1 -f -" < fix-canonical-china-seas-type.sql
+-- or paste the body into: ssh my-server "sudo -u postgres psql -d dw_unified"
+\set ON_ERROR_STOP on
+
+-- 1) VERIFY BEFORE (expect ~842 lowercase, 0 malformed for china_seas)
+SELECT 'before lower' AS what, count(*) FROM china_seas_catalog WHERE product_type='wallcovering';
+SELECT product_type, count(*) FROM china_seas_catalog GROUP BY product_type ORDER BY 2 DESC;
+
+-- 2) BACKUP prior values to a server-side JSON file (rollback source)
+\copy (SELECT id, product_type AS old_product_type FROM china_seas_catalog WHERE product_type='wallcovering') TO '/tmp/china_seas_type_backup.csv' CSV HEADER;
+
+-- 3) APPLY
+BEGIN;
+UPDATE china_seas_catalog SET product_type='Wallcovering' WHERE product_type='wallcovering';
+COMMIT;
+
+-- 4) VERIFY AFTER (expect remaining lowercase = 0)
+SELECT 'after lower (expect 0)' AS what, count(*) FROM china_seas_catalog WHERE product_type='wallcovering';
+SELECT product_type, count(*) FROM china_seas_catalog GROUP BY product_type ORDER BY 2 DESC;
+
+-- ROLLBACK (if needed): restore from the CSV backup
+-- CREATE TEMP TABLE _csbak(id int, old_product_type text);
+-- \copy _csbak FROM '/tmp/china_seas_type_backup.csv' CSV HEADER;
+-- UPDATE china_seas_catalog c SET product_type=b.old_product_type FROM _csbak b WHERE c.id=b.id;
diff --git a/shopify/scripts/reconcile/fix-live-shopify-product-type.js b/shopify/scripts/reconcile/fix-live-shopify-product-type.js
new file mode 100644
index 00000000..46ab6b78
--- /dev/null
+++ b/shopify/scripts/reconcile/fix-live-shopify-product-type.js
@@ -0,0 +1,112 @@
+#!/usr/bin/env node
+/**
+ * fix-live-shopify-product-type.js (GATED — customer-facing Shopify bulk write)
+ *
+ * DTD verdict A (2026-06-30): normalize LIVE Shopify product_type casing/hygiene.
+ * - lowercase 'wallcovering' -> 'Wallcovering' (483 expected)
+ * - ', Textured Print,' / 'floral, , Textured Print,' -> 'Wallcovering' (3 DW Bespoke ARCHIVED)
+ *
+ * TYPE ONLY. Does NOT touch tags or push spec metafields.
+ * Idempotent: re-fetches each product's current product_type and only PUTs if it
+ * still needs changing; re-running after a clean run writes 0 products.
+ *
+ * Source of truth is dw_unified (already normalized locally). This pushes the
+ * SAME casing change to the live store so collection facets / SEO match.
+ *
+ * Usage:
+ * node fix-live-shopify-product-type.js --dry # report only, no writes
+ * node fix-live-shopify-product-type.js # apply (LIVE writes)
+ *
+ * Reads SHOPIFY_ADMIN_TOKEN from shopify/.env.
+ * Store: designer-laboratory-sandbox.myshopify.com API: 2024-10
+ *
+ * product_type is a core Product field -> use the REST Admin productUpdate
+ * (PUT /products/{id}.json {product:{id, product_type}}). 2 req/sec throttle.
+ */
+'use strict';
+const fs = require('fs');
+const path = require('path');
+
+const DRY = process.argv.includes('--dry');
+const DOMAIN = 'designer-laboratory-sandbox.myshopify.com';
+const API = '2024-10';
+
+// --- load token from shopify/.env ---
+function loadEnv() {
+ const envPath = path.resolve(__dirname, '../../.env');
+ const txt = fs.readFileSync(envPath, 'utf8');
+ for (const line of txt.split('\n')) {
+ const m = line.match(/^SHOPIFY_ADMIN_TOKEN\s*=\s*(.+)\s*$/);
+ if (m) return m[1].replace(/^["']|["']$/g, '').trim();
+ }
+ throw new Error('SHOPIFY_ADMIN_TOKEN not found in shopify/.env');
+}
+const TOKEN = loadEnv();
+
+const HEADERS = { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' };
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+const BAD_TYPES = new Set(['wallcovering', ', Textured Print,', 'floral, , Textured Print,']);
+const GOOD = 'Wallcovering';
+
+async function fetchAllProductIdsByType() {
+ // We page the full product list and filter client-side on product_type, because
+ // the REST product_type query param is an exact-match and we have 3 distinct bad
+ // values incl. ones with commas/spaces. ~160k products -> page at 250.
+ const hits = [];
+ let url = `https://${DOMAIN}/admin/api/${API}/products.json?limit=250&fields=id,product_type,status,vendor`;
+ let page = 0;
+ while (url) {
+ const res = await fetch(url, { headers: HEADERS });
+ if (res.status === 429) { await sleep(2000); continue; }
+ if (!res.ok) throw new Error(`list ${res.status}: ${await res.text()}`);
+ const body = await res.json();
+ for (const p of body.products) {
+ if (BAD_TYPES.has(p.product_type)) hits.push({ id: p.id, old: p.product_type, status: p.status, vendor: p.vendor });
+ }
+ page++;
+ // Link header pagination
+ const link = res.headers.get('link') || '';
+ const next = link.split(',').find((s) => s.includes('rel="next"'));
+ url = next ? next.match(/<([^>]+)>/)[1] : null;
+ await sleep(550); // ~2 req/sec for the list pass
+ if (page % 20 === 0) process.stderr.write(` …scanned ${page} pages, ${hits.length} hits\n`);
+ }
+ return hits;
+}
+
+async function putType(id) {
+ const res = await fetch(`https://${DOMAIN}/admin/api/${API}/products/${id}.json`, {
+ method: 'PUT', headers: HEADERS,
+ body: JSON.stringify({ product: { id, product_type: GOOD } }),
+ });
+ if (res.status === 429) { await sleep(2000); return putType(id); }
+ if (!res.ok) throw new Error(`put ${id} ${res.status}: ${await res.text()}`);
+ return res.json();
+}
+
+(async () => {
+ console.log(`[fix-live-shopify-product-type] ${DRY ? 'DRY-RUN' : 'LIVE'} store=${DOMAIN}`);
+ console.log('Scanning live products for bad product_type values…');
+ const hits = await fetchAllProductIdsByType();
+ const byType = {};
+ for (const h of hits) byType[h.old] = (byType[h.old] || 0) + 1;
+ console.log(`Found ${hits.length} products needing fix:`, JSON.stringify(byType));
+
+ // backup the to-be-changed set
+ const bak = path.resolve(__dirname, `live-product-type-backup-${Date.now()}.json`);
+ fs.writeFileSync(bak, JSON.stringify(hits, null, 2));
+ console.log(`Backup of prior values -> ${bak}`);
+
+ if (DRY) { console.log('DRY-RUN: no writes performed.'); return; }
+
+ let done = 0;
+ for (const h of hits) {
+ await putType(h.id);
+ done++;
+ if (done % 50 === 0) console.log(` …${done}/${hits.length}`);
+ await sleep(550); // ~2 req/sec write throttle
+ }
+ console.log(`DONE: updated ${done} products -> product_type='${GOOD}'.`);
+ console.log('Rollback: re-PUT each id with its "old" value from the backup JSON.');
+})().catch((e) => { console.error('FATAL', e); process.exit(1); });
diff --git a/shopify/scripts/reconcile/normalize-product-type-local.sh b/shopify/scripts/reconcile/normalize-product-type-local.sh
new file mode 100755
index 00000000..9ac92f02
--- /dev/null
+++ b/shopify/scripts/reconcile/normalize-product-type-local.sh
@@ -0,0 +1,63 @@
+#!/usr/bin/env bash
+# normalize-product-type-local.sh
+# DTD verdict A (2026-06-30): normalize product_type casing/hygiene in the LOCAL
+# Mac2 dw_unified mirror only. REVERSIBLE / SAFE (local mirror is rebuildable from
+# the Kamatera canonical + Shopify; we still back up prior values to JSON first).
+#
+# Scope (TYPE ONLY — never touches tags or spec metafields):
+# shopify_products:
+# (a) product_type='wallcovering' -> 'Wallcovering' (483 rows: WG 419 ACTIVE + 61 DRAFT, Coordonné 1, Phillipe Romano 1, Romo 1 DRAFT)
+# (b) malformed ', Textured Print,' / 'floral, , Textured Print,' -> 'Wallcovering' (3 DW Bespoke ARCHIVED)
+# china_seas_catalog:
+# (c) product_type='wallcovering' -> 'Wallcovering' (local Mac2 = 0 rows; canonical Kamatera = 842, handled by the GATED memo)
+#
+# Idempotent: re-running after a clean run touches 0 rows.
+# Backs up (id, table, old product_type) to a timestamped JSON BEFORE updating.
+set -euo pipefail
+
+DB='host=/tmp dbname=dw_unified'
+HERE="$(cd "$(dirname "$0")" && pwd)"
+TS="$(date +%Y%m%dT%H%M%S)"
+BACKUP="$HERE/backup-product-type-local-$TS.json"
+
+echo "== normalize-product-type-local.sh =="
+echo "DB: $DB"
+echo "Backup -> $BACKUP"
+echo
+
+echo "== BEFORE =="
+psql "$DB" -c "SELECT 'shopify_products lower' AS what, count(*) FROM shopify_products WHERE product_type='wallcovering'
+UNION ALL SELECT 'shopify_products malformed', count(*) FROM shopify_products WHERE product_type IN (', Textured Print,','floral, , Textured Print,')
+UNION ALL SELECT 'china_seas_catalog lower', count(*) FROM china_seas_catalog WHERE product_type='wallcovering';"
+
+# --- Backup prior values to JSON (only the rows we will touch) ---
+psql "$DB" -At -c "
+SELECT json_agg(row_to_json(t)) FROM (
+ SELECT 'shopify_products' AS tbl, id, product_type AS old_product_type
+ FROM shopify_products
+ WHERE product_type='wallcovering'
+ OR product_type IN (', Textured Print,','floral, , Textured Print,')
+ UNION ALL
+ SELECT 'china_seas_catalog' AS tbl, id, product_type AS old_product_type
+ FROM china_seas_catalog
+ WHERE product_type='wallcovering'
+) t;" > "$BACKUP"
+echo "Backed up $(grep -o '\"id\"' "$BACKUP" 2>/dev/null | wc -l | tr -d ' ') rows to JSON."
+
+# --- Apply (single transaction, idempotent WHERE clauses) ---
+psql "$DB" -v ON_ERROR_STOP=1 <<'SQL'
+BEGIN;
+UPDATE shopify_products SET product_type='Wallcovering' WHERE product_type='wallcovering';
+UPDATE shopify_products SET product_type='Wallcovering' WHERE product_type IN (', Textured Print,','floral, , Textured Print,');
+UPDATE china_seas_catalog SET product_type='Wallcovering' WHERE product_type='wallcovering';
+COMMIT;
+SQL
+
+echo
+echo "== AFTER (all three should be 0) =="
+psql "$DB" -c "SELECT 'shopify_products lower' AS what, count(*) FROM shopify_products WHERE product_type='wallcovering'
+UNION ALL SELECT 'shopify_products malformed', count(*) FROM shopify_products WHERE product_type IN (', Textured Print,','floral, , Textured Print,')
+UNION ALL SELECT 'china_seas_catalog lower', count(*) FROM china_seas_catalog WHERE product_type='wallcovering';"
+
+echo
+echo "Done. Rollback with: scripts/reconcile/rollback-product-type-local.sql (edit BACKUP path) or restore from $BACKUP"
diff --git a/shopify/scripts/reconcile/rollback-product-type-local.sql b/shopify/scripts/reconcile/rollback-product-type-local.sql
new file mode 100644
index 00000000..a6ccb706
--- /dev/null
+++ b/shopify/scripts/reconcile/rollback-product-type-local.sql
@@ -0,0 +1,33 @@
+-- rollback-product-type-local.sql
+-- Restores product_type values changed by normalize-product-type-local.sh
+-- from a backup JSON written by that script.
+--
+-- USAGE (replace the path with the actual backup-product-type-local-<TS>.json):
+-- psql "host=/tmp dbname=dw_unified" \
+-- -v bak="$(cat scripts/reconcile/backup-product-type-local-<TS>.json)" \
+-- -f scripts/reconcile/rollback-product-type-local.sql
+--
+-- The backup JSON is an array of {tbl, id, old_product_type}.
+
+\set ON_ERROR_STOP on
+BEGIN;
+
+WITH bak AS (
+ SELECT * FROM json_to_recordset(:'bak'::json)
+ AS x(tbl text, id integer, old_product_type text)
+)
+UPDATE shopify_products s
+SET product_type = b.old_product_type
+FROM bak b
+WHERE b.tbl = 'shopify_products' AND s.id = b.id;
+
+WITH bak AS (
+ SELECT * FROM json_to_recordset(:'bak'::json)
+ AS x(tbl text, id integer, old_product_type text)
+)
+UPDATE china_seas_catalog c
+SET product_type = b.old_product_type
+FROM bak b
+WHERE b.tbl = 'china_seas_catalog' AND c.id = b.id;
+
+COMMIT;
← 6736e88a auto-save: 2026-06-30T11:09:49 (5 files) — pending-approval/
·
back to Designer Wallcoverings
·
Add local swatch-enrichment pilot (50 Lilycolor swatches, $0 3499334f →