← back to Designer Wallcoverings
Track shopify/scripts/lib operational source (registry+guards); was hidden by python venv lib/ ignore
6984f4c63a92339ddc5177679815909416cdf744 · 2026-07-28 07:25:18 -0700 · steve
Files touched
M shopify/.gitignoreA shopify/scripts/lib/USAGE-EXAMPLE.mdA shopify/scripts/lib/internal-guard.jsA shopify/scripts/lib/push-spec-metafields.jsA shopify/scripts/lib/sku-registry.js
Diff
commit 6984f4c63a92339ddc5177679815909416cdf744
Author: steve <steve@designerwallcoverings.com>
Date: Tue Jul 28 07:25:18 2026 -0700
Track shopify/scripts/lib operational source (registry+guards); was hidden by python venv lib/ ignore
---
shopify/.gitignore | 4 +
shopify/scripts/lib/USAGE-EXAMPLE.md | 182 +++++++++++++
shopify/scripts/lib/internal-guard.js | 67 +++++
shopify/scripts/lib/push-spec-metafields.js | 83 ++++++
shopify/scripts/lib/sku-registry.js | 390 ++++++++++++++++++++++++++++
5 files changed, 726 insertions(+)
diff --git a/shopify/.gitignore b/shopify/.gitignore
index 4daee461..fe68c839 100644
--- a/shopify/.gitignore
+++ b/shopify/.gitignore
@@ -12,6 +12,10 @@ eggs/
.eggs/
lib/
lib64/
+# scripts/lib holds operational DW source (registry, guards) — NOT a python venv; keep it tracked
+!scripts/lib/
+!scripts/lib/*.js
+!scripts/lib/*.md
parts/
sdist/
var/
diff --git a/shopify/scripts/lib/USAGE-EXAMPLE.md b/shopify/scripts/lib/USAGE-EXAMPLE.md
new file mode 100644
index 00000000..a555b755
--- /dev/null
+++ b/shopify/scripts/lib/USAGE-EXAMPLE.md
@@ -0,0 +1,182 @@
+# SKU Registry Integration Guide
+
+## Quick Start
+
+```javascript
+const { checkDuplicate, registerSku, updateShopifyInfo } = require('./lib/sku-registry');
+
+// Before importing ANY product:
+async function importProduct(vendorPrefix, vendorName, mfrSku, productData) {
+ // Step 1: Check for duplicate
+ const duplicate = await checkDuplicate(vendorPrefix, mfrSku);
+ if (duplicate.exists) {
+ console.log(`⚠️ SKIP: ${mfrSku} already exists as ${duplicate.existingSku}`);
+ return { skipped: true, reason: 'duplicate' };
+ }
+
+ // Step 2: Register BEFORE creating in Shopify
+ const dwSku = await registerSku(vendorPrefix, vendorName, mfrSku);
+ if (!dwSku) {
+ console.log(`❌ Failed to register SKU for ${mfrSku}`);
+ return { skipped: true, reason: 'registration_failed' };
+ }
+
+ // Step 3: Create product in Shopify
+ const shopifyProduct = await createShopifyProduct({
+ ...productData,
+ sku: dwSku // Use the DW SKU as the variant SKU
+ });
+
+ // Step 4: Update registry with Shopify info
+ await updateShopifyInfo(dwSku, shopifyProduct.id, shopifyProduct.handle);
+
+ return { success: true, dwSku, shopifyId: shopifyProduct.id };
+}
+```
+
+## Vendor Prefixes
+
+| Prefix | Vendor | Starting Range |
+|--------|--------|----------------|
+| DWK- | Koroseal | 30000 |
+| DWC-Arte- | Arte International | 040000 |
+| DWJS- | York | 043000 |
+| DWRR- | Ralph Lauren | 050000 |
+| DWQW- | WallQuest/Malibu | 056000 |
+| DWWG- | Wolf-Gordon | 060000 |
+| DWTT- | Thibaut | 070000 |
+| DWMR- | Maya Romanoff | 080000 |
+| DWCC- | Novasuede | 090000 |
+| DWKK- | Kravet | 100000 |
+| DWSC- | Schumacher | 110000 |
+| DWPJ- | Phillip Jeffries | 120000 |
+| DWCS- | Cole & Son | 130000 |
+| DWEL- | Elitis | 140000 |
+| DWCW- | Cowtan & Tout | 150000 |
+| DWIN- | Innovations | 160000 |
+| DWBR- | Brewster | 170000 |
+| DWWC- | Generic/Default | 500000 |
+
+## Product Line Import (SKUs spaced by 10)
+
+For vendors like **Elitis** where a pattern has multiple colorways, use `registerProductLine()` to space SKUs by 10. This leaves room for future colorway additions.
+
+```javascript
+const { registerProductLine } = require('./lib/sku-registry');
+
+// When scraping a pattern with multiple colorways:
+const colorways = ['RM-123-01', 'RM-123-02', 'RM-123-03'];
+
+const results = await registerProductLine('DWEL', 'Elitis', colorways);
+// Returns:
+// [
+// { mfrSku: 'RM-123-01', dwSku: 'DWEL-140000' },
+// { mfrSku: 'RM-123-02', dwSku: 'DWEL-140010' }, // +10
+// { mfrSku: 'RM-123-03', dwSku: 'DWEL-140020' } // +10
+// ]
+
+// Future colorway 'RM-123-04' could be manually assigned DWEL-140030
+// Or inserted between: DWEL-140005 (between 01 and 02)
+```
+
+### Why Space by 10?
+- Leaves room for future colorways to be inserted
+- Pattern variations can be grouped (140000-140009 = pattern 1)
+- Easier to identify product families by SKU
+
+---
+
+## API Reference
+
+### checkDuplicate(vendorPrefix, mfrSku)
+Check if manufacturer SKU already exists.
+```javascript
+const result = await checkDuplicate('DWK', 'TAK-AA01-01');
+// { exists: true, existingSku: 'DWK-30001', shopifyProductId: 'gid://...' }
+// or { exists: false }
+```
+
+### registerSku(vendorPrefix, vendorName, mfrSku)
+Register new SKU before Shopify creation. Returns null if duplicate.
+```javascript
+const dwSku = await registerSku('DWK', 'Koroseal', 'TAK-AA01-01');
+// 'DWK-30001' or null if duplicate
+```
+
+### updateShopifyInfo(dwSku, shopifyProductId, shopifyHandle)
+Update registry after Shopify product creation.
+```javascript
+await updateShopifyInfo('DWK-30001', 'gid://shopify/Product/123', 'dwk-30001-subtle-leaf');
+```
+
+### getNextSkuNumber(vendorPrefix)
+Get next available SKU number for a prefix.
+```javascript
+const nextNum = await getNextSkuNumber('DWK');
+// 30042
+```
+
+### getVendorSkus(vendorPrefix)
+Get all registered SKUs for a vendor.
+```javascript
+const skus = await getVendorSkus('DWK');
+// [{ dw_sku: 'DWK-30001', mfr_sku: 'TAK-AA01-01', ... }, ...]
+```
+
+### getStats()
+Get registry statistics.
+```javascript
+const stats = await getStats();
+// { byVendor: [{ vendor_prefix: 'DWK', count: 42 }, ...], total: 1500 }
+```
+
+## Integration Example: Koroseal Import
+
+```javascript
+const { checkDuplicate, registerSku, updateShopifyInfo, close } = require('./lib/sku-registry');
+
+async function processKorosealProducts(products) {
+ const results = { imported: 0, skipped: 0, failed: 0 };
+
+ for (const product of products) {
+ const mfrSku = product.manufacturerSku; // e.g., 'TAK-AA01-01'
+
+ // Check duplicate
+ const dup = await checkDuplicate('DWK', mfrSku);
+ if (dup.exists) {
+ console.log(`Skip: ${mfrSku} → ${dup.existingSku}`);
+ results.skipped++;
+ continue;
+ }
+
+ // Register
+ const dwSku = await registerSku('DWK', 'Koroseal', mfrSku);
+ if (!dwSku) {
+ results.failed++;
+ continue;
+ }
+
+ // Create in Shopify
+ try {
+ const shopifyProduct = await createInShopify(product, dwSku);
+ await updateShopifyInfo(dwSku, shopifyProduct.id, shopifyProduct.handle);
+ results.imported++;
+ } catch (err) {
+ console.error(`Failed ${mfrSku}:`, err.message);
+ results.failed++;
+ }
+ }
+
+ await close(); // Close pool when done
+ return results;
+}
+```
+
+## Database Table
+
+```sql
+-- Location: dw_unified database
+-- Table: dw_sku_registry
+
+SELECT * FROM dw_sku_registry WHERE vendor_prefix = 'DWK' ORDER BY dw_sku;
+```
diff --git a/shopify/scripts/lib/internal-guard.js b/shopify/scripts/lib/internal-guard.js
new file mode 100644
index 00000000..8354a59f
--- /dev/null
+++ b/shopify/scripts/lib/internal-guard.js
@@ -0,0 +1,67 @@
+'use strict';
+/**
+ * internal-guard.js — the SINGLE source of truth for "is this product INTERNAL?"
+ *
+ * INTERNAL = deliberately, permanently OFF every customer-facing surface (the public
+ * Online Store + the Google Merchant feed) while STILL visible on internal tools
+ * (all.dw, substitutefinder, internal viewers, the 401-gated microsites).
+ *
+ * Distinct from `draft` (a candidate to activate). `internal` is a DW-side overlay
+ * that HARD-BLOCKS any future publish/activate/Google-feed. Underlying Shopify status
+ * stays `draft` (keeps it off the storefront) but internal ALSO refuses activation.
+ *
+ * Two belt-and-suspenders signals (either one = internal):
+ * 1) VENDOR is in config/internal-lines.json's `vendors` array (the durable registry).
+ * 2) The product carries the `internal` product TAG.
+ *
+ * Registry is read once and cached. Vendor match is case-insensitive + trimmed.
+ */
+const fs = require('fs');
+const path = require('path');
+
+const REGISTRY_PATH = path.join(__dirname, '..', '..', 'config', 'internal-lines.json');
+
+let _cache = null;
+function load() {
+ if (_cache) return _cache;
+ let vendors = [];
+ let tag = 'internal';
+ try {
+ const j = JSON.parse(fs.readFileSync(REGISTRY_PATH, 'utf8'));
+ vendors = Array.isArray(j.vendors) ? j.vendors : [];
+ if (typeof j.tag === 'string' && j.tag.trim()) tag = j.tag.trim();
+ } catch (e) {
+ // Fail SAFE for a front-facing guard: if the registry can't be read we still
+ // block the four known luxury lines by name so a missing/corrupt file can never
+ // silently let an internal line leak to the storefront/Google feed.
+ vendors = ['Fromental', 'Gracie', 'Zuber', 'Crezana Design'];
+ }
+ const vendorSet = new Set(vendors.map(v => String(v).toLowerCase().trim()).filter(Boolean));
+ _cache = { vendors, vendorSet, tag: tag.toLowerCase() };
+ return _cache;
+}
+
+/** Normalize a tags value (array, comma/space string, or null) → lowercase token array. */
+function tagList(tags) {
+ if (!tags) return [];
+ let arr = tags;
+ if (typeof tags === 'string') arr = tags.split(',');
+ if (!Array.isArray(arr)) arr = [arr];
+ return arr.map(t => String(t || '').trim().toLowerCase()).filter(Boolean);
+}
+
+/** True when the product is INTERNAL (registry vendor OR `internal` tag). */
+function isInternal(vendor, tags) {
+ const { vendorSet, tag } = load();
+ if (vendor && vendorSet.has(String(vendor).toLowerCase().trim())) return true;
+ if (tagList(tags).includes(tag)) return true;
+ return false;
+}
+
+/** The registry vendor list (as configured). */
+function internalVendors() { return load().vendors.slice(); }
+
+/** The internal tag string (default "internal"). */
+function internalTag() { return load().tag; }
+
+module.exports = { isInternal, internalVendors, internalTag, tagList, REGISTRY_PATH };
diff --git a/shopify/scripts/lib/push-spec-metafields.js b/shopify/scripts/lib/push-spec-metafields.js
new file mode 100644
index 00000000..138b9bee
--- /dev/null
+++ b/shopify/scripts/lib/push-spec-metafields.js
@@ -0,0 +1,83 @@
+'use strict';
+/**
+ * push-spec-metafields.js — DRAFT (2026-06-20), not yet wired in.
+ *
+ * The activation gate (`cadence/activate-gated.js`) validates width/specs from the
+ * VENDOR CATALOG ROW (`c.width`, `c.roll_length`, …) and then flips the product
+ * ACTIVE — but it never writes those values back as Shopify METAFIELDS. Result:
+ * ~500 ACTIVE products with width KNOWN in PG but no `global.width` metafield on the
+ * storefront / Google feed, and `custom.manufacturer_sku` missing on 497/499.
+ *
+ * This helper turns a vendor row into the metafields the product SHOULD carry, so the
+ * gate can push-then-activate (the vp-dw-commerce-approved default). It only emits a
+ * metafield when (a) the vendor row has a real value, (b) it isn't a junk/Unknown/error
+ * value, and (c) the live product doesn't already have it — so it's idempotent and
+ * never overwrites curated data.
+ *
+ * Namespaces follow CLAUDE.md "Shopify Metafield Manager": width/length/repeat/material
+ * live in `global` (with `custom` mirrors where the codebase already reads them); mfr sku
+ * is `custom.manufacturer_sku` + `dwc.manufacturer_sku` (the mandatory vendor-lookup key).
+ */
+
+const BAD = /^(unknown|n\/?a|none|null|undefined|page not found|404|error|not found)$/i;
+const clean = v => {
+ const s = String(v == null ? '' : v).trim();
+ return (!s || BAD.test(s)) ? '' : s;
+};
+const has = (existing, ns, key) =>
+ (existing || []).some(m => m.namespace === ns && m.key === key && String(m.value || '').trim() !== '');
+
+// One logical spec -> the namespace/key pairs it should be written to.
+const SPEC_TARGETS = {
+ width: [['global', 'width'], ['custom', 'width']],
+ length: [['global', 'length']],
+ repeat: [['global', 'repeat']],
+ material: [['global', 'material'], ['custom', 'material']],
+ mfrSku: [['custom', 'manufacturer_sku'], ['dwc', 'manufacturer_sku']],
+};
+
+/**
+ * Build the metafields-to-write for one product from its vendor row.
+ * @param {object} row vendor catalog row in scope at the gate: {width, roll_length, pattern_repeat, material, mfr_sku, dw_sku}
+ * @param {Array} existing the live product's current metafields [{namespace,key,value}]
+ * @returns {Array} MetafieldsSetInput[] minus ownerId (caller adds it) — empty if nothing to push
+ */
+function buildSpecMetafields(row, existing) {
+ const vals = {
+ width: clean(row.width),
+ length: clean(row.roll_length),
+ repeat: clean(row.pattern_repeat),
+ material: clean(row.material),
+ mfrSku: clean(row.mfr_sku) || clean(row.dw_sku),
+ };
+ const out = [];
+ for (const [spec, value] of Object.entries(vals)) {
+ if (!value) continue;
+ for (const [ns, key] of SPEC_TARGETS[spec]) {
+ if (has(existing, ns, key)) continue; // never clobber existing/curated values
+ out.push({ namespace: ns, key, value, type: 'single_line_text_field' });
+ }
+ }
+ return out;
+}
+
+const METAFIELDS_SET = `mutation($mf:[MetafieldsSetInput!]!){
+ metafieldsSet(metafields:$mf){ metafields{ id namespace key } userErrors{ field message } } }`;
+
+/**
+ * Push the built metafields for a product. `gqlRetry` is the caller's existing
+ * rate-limited GraphQL fn (so we inherit its 429 backoff). DRY-RUN unless commit=true.
+ * @returns {{pushed:number, keys:string[], dryRun:boolean, errors?:any}}
+ */
+async function pushSpecMetafields(ownerId, row, existing, gqlRetry, commit) {
+ const mfs = buildSpecMetafields(row, existing).map(m => ({ ...m, ownerId }));
+ if (!mfs.length) return { pushed: 0, keys: [], dryRun: !commit };
+ const keys = mfs.map(m => `${m.namespace}.${m.key}`);
+ if (!commit) return { pushed: 0, keys, dryRun: true };
+ const r = await gqlRetry(METAFIELDS_SET, { mf: mfs });
+ const ue = r.json?.data?.metafieldsSet?.userErrors;
+ if (ue && ue.length) return { pushed: 0, keys, dryRun: false, errors: ue };
+ return { pushed: mfs.length, keys, dryRun: false };
+}
+
+module.exports = { buildSpecMetafields, pushSpecMetafields, METAFIELDS_SET };
diff --git a/shopify/scripts/lib/sku-registry.js b/shopify/scripts/lib/sku-registry.js
new file mode 100644
index 00000000..57878213
--- /dev/null
+++ b/shopify/scripts/lib/sku-registry.js
@@ -0,0 +1,390 @@
+/**
+ * DW SKU Registry - Duplicate Prevention System
+ *
+ * Shared utility for all vendor import/update scripts.
+ * Database: dw_unified → Table: dw_sku_registry
+ *
+ * SKU PREFIX RULES (as of 2026-03):
+ * - Format: DW{XX}-{NUMBER} — DW + 2 random letters + dash + sequential number
+ * - NEVER reference vendor name in prefix
+ * - NEVER use numbers in prefix — letters only
+ * - FORBIDDEN letters (hard to hear on phone): B, D, S, F, M, N, V
+ * - ALLOWED letters: A, C, E, G, H, I, J, K, L, O, P, Q, R, T, U, W, X, Y, Z
+ * - New prefixes auto-generated by VCC: generateCompliantPrefix() in vendor-command-center/server.js
+ * - Legacy prefixes (DWSC, DWTT, DWBR, etc.) — DO NOT CHANGE, leave as-is
+ *
+ * Usage:
+ * const { checkDuplicate, registerSku, getNextSku } = require('./lib/sku-registry');
+ *
+ * // Before importing any product:
+ * const result = await checkDuplicate('DWXG', 'TAK-AA01-01');
+ * if (result.exists) {
+ * console.log(`Skip: ${result.existingSku}`);
+ * return;
+ * }
+ *
+ * // Register new SKU before Shopify creation:
+ * const newSku = await registerSku('DWXG', 'VendorName', 'TAK-AA01-01');
+ * // newSku = 'DWXG-30001'
+ */
+
+const { Pool } = require('pg');
+
+const pool = new Pool({
+ connectionString: 'postgresql://dw_admin@127.0.0.1:5432/dw_unified'
+});
+
+// LEGACY vendor prefix starting ranges — DO NOT add new entries here.
+// New vendor prefixes are auto-generated by VCC's generateCompliantPrefix() and stored in vendor_registry.sku_prefix
+const VENDOR_RANGES = {
+ 'DWK': 30000, // Koroseal
+ 'DWAT': 40000, // Arte International
+ 'DWJS': 43000, // York
+ 'DWRR': 50000, // Ralph Lauren
+ 'DWQW': 56000, // WallQuest/Malibu
+ 'DWWG': 60000, // Wolf-Gordon
+ 'DWTT': 70000, // Thibaut
+ 'DWMR': 80000, // Maya Romanoff
+ 'DWCC': 90000, // Novasuede
+ 'DWKK': 100000, // Kravet
+ 'DWSC': 110000, // Schumacher
+ 'DWPJ': 120000, // Phillip Jeffries
+ 'DWCS': 130000, // Cole & Son
+ 'DWEL': 140000, // Elitis
+ 'DWCW': 150000, // Cowtan & Tout
+ 'DWIN': 160000, // Innovations
+ 'DWBR': 170000, // Brewster
+ 'DW18': 200000, // 1838 Wallcoverings
+ 'DWDD': 210000, // Dedar
+ 'DWDX': 220000, // Designtex
+ 'DWFC': 230000, // Fabricut
+ 'DWRM': 240000, // Romo
+ 'DWKN': 250000, // Knoll
+ 'DWHW': 260000, // Hygge & West
+ 'DWBN': 270000, // BN Walls
+ 'DWMG': 280000, // Mind the Gap
+ 'DWOL': 290000, // Osborne & Little
+ 'DWST': 300000, // Stout Textiles
+ 'DWCO': 310000, // Contrado
+ 'DWMS': 320000, // Mural Source
+ 'DWTB': 330000, // Timorous Beasties
+ 'DWAR': 340000, // Arteriors
+ 'DWFL': 350000, // Folia Fabrics
+ 'DWWC': 500000, // Generic/Default
+ 'RTE': 40000, // Arte alternate (legacy)
+ // Material-based prefixes (Steve directive 2026-07-06) — WallQuest naturals books under
+ // Phillipe Romano. Shared across books; getNextSkuNumber takes MAX(existing)+1 so these
+ // starting ranges only apply on a first-ever import. Registered so the DWWC-500000 fallback
+ // never fires for them.
+ 'GRS': 10000, // Grasscloth (naturals)
+ 'PWV': 10000, // Paperweave
+ 'RAF': 10000, // Raffia
+ 'SIS': 10000, // Sisal
+ 'LIN': 10000, // Linen
+ 'CORK': 10000, // Cork
+ 'ABA': 10000, // Abaca
+ 'MIC': 10000, // Mica
+ 'JUT': 10000, // Jute
+};
+
+// A DW-FAMILY SKU is a DW+2-letter SKU OR a material-prefixed natural SKU (GRS-/PWV-/etc).
+// Use this instead of s.startsWith('DW') when distinguishing our SKUs from manufacturer SKUs.
+const MATERIAL_PREFIX_RE = /^(GRS|PWV|RAF|SIS|LIN|CORK|ABA|MIC|JUT|BAM|NET)-\d/i;
+function isDwFamilySku(s) { return !!s && (s.startsWith('DW') || MATERIAL_PREFIX_RE.test(s)); }
+
+/**
+ * Check if a manufacturer SKU already exists for a vendor
+ * @param {string} vendorPrefix - e.g., 'DWK', 'DWMR'
+ * @param {string} mfrSku - Manufacturer SKU, e.g., 'TAK-AA01-01'
+ * @returns {Promise<{exists: boolean, existingSku?: string, shopifyProductId?: string}>}
+ */
+async function checkDuplicate(vendorPrefix, mfrSku) {
+ if (!mfrSku) {
+ return { exists: false };
+ }
+
+ try {
+ const result = await pool.query(`
+ SELECT dw_sku, shopify_product_id
+ FROM dw_sku_registry
+ WHERE vendor_prefix = $1 AND mfr_sku = $2
+ `, [vendorPrefix, mfrSku]);
+
+ if (result.rows.length > 0) {
+ return {
+ exists: true,
+ existingSku: result.rows[0].dw_sku,
+ shopifyProductId: result.rows[0].shopify_product_id
+ };
+ }
+ return { exists: false };
+ } catch (err) {
+ console.error('SKU Registry check error:', err.message);
+ return { exists: false, error: err.message };
+ }
+}
+
+/**
+ * Get the next available SKU number for a vendor prefix
+ * @param {string} vendorPrefix - e.g., 'DWK', 'DWMR'
+ * @param {number} increment - Gap between SKUs (default: 1, use 10 for product lines)
+ * @returns {Promise<number>}
+ */
+async function getNextSkuNumber(vendorPrefix, increment = 1) {
+ const startingRange = VENDOR_RANGES[vendorPrefix] || VENDOR_RANGES['DWWC'];
+
+ try {
+ const result = await pool.query(`
+ SELECT MAX(CAST(SUBSTRING(dw_sku FROM '[0-9]+$') AS INTEGER)) as max_num
+ FROM dw_sku_registry
+ WHERE vendor_prefix = $1
+ `, [vendorPrefix]);
+
+ const maxNum = result.rows[0]?.max_num;
+ if (maxNum) {
+ // Round up to next increment boundary
+ const nextNum = Math.ceil((maxNum + 1) / increment) * increment;
+ return nextNum;
+ }
+ return startingRange;
+ } catch (err) {
+ console.error('Get next SKU error:', err.message);
+ return startingRange;
+ }
+}
+
+/**
+ * Register a new SKU in the registry (BEFORE creating in Shopify)
+ * @param {string} vendorPrefix - e.g., 'DWK', 'DWMR'
+ * @param {string} vendorName - e.g., 'Koroseal', 'Maya Romanoff'
+ * @param {string} mfrSku - Manufacturer SKU
+ * @param {number} increment - Gap between SKUs (default: 1, use 10 for product lines)
+ * @returns {Promise<string>} The new DW SKU (e.g., 'DWK-030001')
+ */
+async function registerSku(vendorPrefix, vendorName, mfrSku, increment = 1) {
+ // First check for duplicate
+ const duplicate = await checkDuplicate(vendorPrefix, mfrSku);
+ if (duplicate.exists) {
+ console.log(`⚠️ DUPLICATE: ${mfrSku} already exists as ${duplicate.existingSku}`);
+ return null;
+ }
+
+ // Get next number with increment
+ const nextNum = await getNextSkuNumber(vendorPrefix, increment);
+ const newDwSku = `${vendorPrefix}-${String(nextNum).toString()}`;
+
+ try {
+ await pool.query(`
+ INSERT INTO dw_sku_registry (dw_sku, vendor_prefix, vendor_name, mfr_sku)
+ VALUES ($1, $2, $3, $4)
+ `, [newDwSku, vendorPrefix, vendorName, mfrSku]);
+
+ console.log(`✅ Registered: ${newDwSku} for ${mfrSku}`);
+ return newDwSku;
+ } catch (err) {
+ if (err.code === '23505') { // Unique violation
+ console.log(`⚠️ SKU already registered: ${mfrSku}`);
+ return null;
+ }
+ console.error('Register SKU error:', err.message);
+ throw err;
+ }
+}
+
+/**
+ * Register a batch of SKUs for a product line (pattern with colorways)
+ * SKUs are spaced by 10 to allow future insertions
+ *
+ * @param {string} vendorPrefix - e.g., 'DWEL' for Elitis
+ * @param {string} vendorName - e.g., 'Elitis'
+ * @param {Array<string>} mfrSkus - Array of manufacturer SKUs for the line
+ * @returns {Promise<Array<{mfrSku: string, dwSku: string}>>}
+ *
+ * Example: registerProductLine('DWEL', 'Elitis', ['RM-123-01', 'RM-123-02', 'RM-123-03'])
+ * Returns: [
+ * { mfrSku: 'RM-123-01', dwSku: 'DWEL-140000' },
+ * { mfrSku: 'RM-123-02', dwSku: 'DWEL-140010' },
+ * { mfrSku: 'RM-123-03', dwSku: 'DWEL-140020' }
+ * ]
+ */
+async function registerProductLine(vendorPrefix, vendorName, mfrSkus) {
+ const results = [];
+ const startingRange = VENDOR_RANGES[vendorPrefix] || VENDOR_RANGES['DWWC'];
+
+ // Get the highest existing number for this prefix
+ const maxResult = await pool.query(`
+ SELECT MAX(CAST(SUBSTRING(dw_sku FROM '[0-9]+$') AS INTEGER)) as max_num
+ FROM dw_sku_registry
+ WHERE vendor_prefix = $1
+ `, [vendorPrefix]);
+
+ let nextNum = maxResult.rows[0]?.max_num;
+ if (nextNum) {
+ // Round up to next 10 boundary
+ nextNum = Math.ceil((nextNum + 1) / 10) * 10;
+ } else {
+ nextNum = startingRange;
+ }
+
+ for (const mfrSku of mfrSkus) {
+ // Check for duplicate
+ const duplicate = await checkDuplicate(vendorPrefix, mfrSku);
+ if (duplicate.exists) {
+ console.log(`⚠️ SKIP: ${mfrSku} already exists as ${duplicate.existingSku}`);
+ results.push({ mfrSku, dwSku: duplicate.existingSku, skipped: true });
+ continue;
+ }
+
+ const newDwSku = `${vendorPrefix}-${String(nextNum).toString()}`;
+
+ try {
+ await pool.query(`
+ INSERT INTO dw_sku_registry (dw_sku, vendor_prefix, vendor_name, mfr_sku)
+ VALUES ($1, $2, $3, $4)
+ `, [newDwSku, vendorPrefix, vendorName, mfrSku]);
+
+ console.log(`✅ Line SKU: ${newDwSku} for ${mfrSku}`);
+ results.push({ mfrSku, dwSku: newDwSku });
+ nextNum += 10; // Increment by 10 for next colorway
+ } catch (err) {
+ if (err.code === '23505') {
+ console.log(`⚠️ Already registered: ${mfrSku}`);
+ results.push({ mfrSku, dwSku: null, error: 'duplicate' });
+ } else {
+ results.push({ mfrSku, dwSku: null, error: err.message });
+ }
+ }
+ }
+
+ return results;
+}
+
+/**
+ * FORWARD-ONLY COLLISION GUARD (TK-10002, 2026-07-28).
+ * Before creating a NEW product, verify the DW SKU we're about to stamp on the
+ * SELLABLE variant is not already carried by another NON-ARCHIVED product. The
+ * registry-based checkDuplicate() only answers "has this mfr been imported?" — it
+ * does NOT answer "is this dw_sku free as a live variant SKU?". When the registry
+ * drifts from live (observed: registry_max behind live_max), registerSku() can mint
+ * a dw_sku that already exists on an ACTIVE product → a hard SKU collision. This
+ * guard closes that gap by asking the local dw_unified mirror of live Shopify.
+ *
+ * Matches the base SKU with OR without a variant suffix (DWCC-112400 ≡
+ * DWCC-112400-Sample ≡ DWCC-112400-Per Yard), across variant_sku / sku / dw_sku.
+ * Returns { taken:boolean, byProductId?, byStatus?, matchedSku? }.
+ * NOTE: intentionally NOT a DB constraint (461 legacy violations still exist).
+ *
+ * @param {string} dwSku - The DW SKU base about to be assigned (e.g., 'DWAT-97913')
+ * @returns {Promise<{taken:boolean, byProductId?:number, byStatus?:string, matchedSku?:string}>}
+ */
+async function skuTakenOnActiveProduct(dwSku) {
+ if (!dwSku) return { taken: false };
+ try {
+ const { rows } = await pool.query(
+ `SELECT id, status, variant_sku, sku, dw_sku
+ FROM shopify_products
+ WHERE (variant_sku = $1 OR variant_sku LIKE $2
+ OR sku = $1 OR sku LIKE $2
+ OR dw_sku = $1 OR dw_sku LIKE $2)
+ AND UPPER(COALESCE(status,'')) <> 'ARCHIVED'
+ LIMIT 1`,
+ [dwSku, dwSku + '-%']);
+ if (rows.length) {
+ const r = rows[0];
+ return { taken: true, byProductId: r.id, byStatus: r.status,
+ matchedSku: r.variant_sku || r.sku || r.dw_sku };
+ }
+ return { taken: false };
+ } catch (err) {
+ // Fail OPEN would allow a collision; fail CLOSED (report taken) would block valid
+ // imports on a DB hiccup. Surface the error so the caller can decide; default to
+ // "unknown = don't block" but log loudly.
+ console.error('skuTakenOnActiveProduct error:', err.message);
+ return { taken: false, error: err.message };
+ }
+}
+
+/**
+ * Update registry with Shopify product ID after creation
+ * @param {string} dwSku - The DW SKU (e.g., 'DWK-030001')
+ * @param {string} shopifyProductId - Shopify product GID
+ * @param {string} shopifyHandle - Product handle
+ */
+async function updateShopifyInfo(dwSku, shopifyProductId, shopifyHandle) {
+ try {
+ await pool.query(`
+ UPDATE dw_sku_registry
+ SET shopify_product_id = $1, shopify_handle = $2, updated_at = NOW()
+ WHERE dw_sku = $3
+ `, [shopifyProductId, shopifyHandle, dwSku]);
+ } catch (err) {
+ console.error('Update Shopify info error:', err.message);
+ }
+}
+
+/**
+ * Get all registered SKUs for a vendor
+ * @param {string} vendorPrefix
+ * @returns {Promise<Array>}
+ */
+async function getVendorSkus(vendorPrefix) {
+ try {
+ const result = await pool.query(`
+ SELECT dw_sku, mfr_sku, shopify_product_id, status, created_at
+ FROM dw_sku_registry
+ WHERE vendor_prefix = $1
+ ORDER BY dw_sku
+ `, [vendorPrefix]);
+ return result.rows;
+ } catch (err) {
+ console.error('Get vendor SKUs error:', err.message);
+ return [];
+ }
+}
+
+/**
+ * Get registry stats
+ * @returns {Promise<Object>}
+ */
+async function getStats() {
+ try {
+ const result = await pool.query(`
+ SELECT vendor_prefix, COUNT(*) as count
+ FROM dw_sku_registry
+ GROUP BY vendor_prefix
+ ORDER BY vendor_prefix
+ `);
+
+ const total = await pool.query(`SELECT COUNT(*) as total FROM dw_sku_registry`);
+
+ return {
+ byVendor: result.rows,
+ total: parseInt(total.rows[0].total)
+ };
+ } catch (err) {
+ console.error('Get stats error:', err.message);
+ return { byVendor: [], total: 0 };
+ }
+}
+
+/**
+ * Close the pool connection
+ */
+async function close() {
+ await pool.end();
+}
+
+module.exports = {
+ checkDuplicate,
+ getNextSkuNumber,
+ registerSku,
+ registerProductLine, // For product lines with colorways (spaced by 10)
+ skuTakenOnActiveProduct, // TK-10002: forward-only variant-SKU collision guard
+ updateShopifyInfo,
+ getVendorSkus,
+ getStats,
+ close,
+ VENDOR_RANGES,
+ isDwFamilySku,
+ pool
+};
← f8969b83 TK-10002: forward-only variant-SKU collision guard (skuTaken
·
back to Designer Wallcoverings
·
auto-save: 2026-07-28T07:28:16 (4 files) — shopify/scripts/d dca7c0a4 →