← back to Designer Wallcoverings
sync-shopify-products: fetch variant price + descriptionHtml + 5-field rollup columns (mirror no longer blind to price/desc/variant-shape)
f494797979354279ec4bc442ef96c22457202547 · 2026-06-21 14:51:20 -0700 · steve
Files touched
M shopify/scripts/sync-shopify-products.js
Diff
commit f494797979354279ec4bc442ef96c22457202547
Author: steve <steve@designerwallcoverings.com>
Date: Sun Jun 21 14:51:20 2026 -0700
sync-shopify-products: fetch variant price + descriptionHtml + 5-field rollup columns (mirror no longer blind to price/desc/variant-shape)
---
shopify/scripts/sync-shopify-products.js | 62 ++++++++++++++++++++++++++++----
1 file changed, 56 insertions(+), 6 deletions(-)
diff --git a/shopify/scripts/sync-shopify-products.js b/shopify/scripts/sync-shopify-products.js
index 94d9dc09..d72a8389 100644
--- a/shopify/scripts/sync-shopify-products.js
+++ b/shopify/scripts/sync-shopify-products.js
@@ -49,6 +49,21 @@ async function syncProducts(quickMode = false) {
let totalSkipped = 0;
let page = 0;
+ // 5-field monitor columns (idempotent — added once). The mirror now records
+ // per-product variant price/shape + description presence so the recurring
+ // 5-field canary can run off the mirror instead of a separate live scan.
+ await pool.query(`
+ ALTER TABLE shopify_products
+ ADD COLUMN IF NOT EXISTS body_html text,
+ ADD COLUMN IF NOT EXISTS variant_count int,
+ ADD COLUMN IF NOT EXISTS min_variant_price numeric(10,2),
+ ADD COLUMN IF NOT EXISTS has_sample_variant boolean,
+ ADD COLUMN IF NOT EXISTS has_product_variant boolean,
+ ADD COLUMN IF NOT EXISTS has_description boolean;
+ `);
+ // classify a variant as a SAMPLE by title OR -sample sku OR (single variant at $4.25 leak)
+ const isSample = (v, only) => /sample/i.test(v.title || '') || /-sample$/i.test(v.sku || '') || (only && parseFloat(v.price) === 4.25 && !/sample/i.test(v.title || ''));
+
// Get last sync time for quick mode
let lastSync = null;
if (quickMode) {
@@ -61,9 +76,15 @@ async function syncProducts(quickMode = false) {
}
}
+ // 5-field rule (Steve 2026-06-21): the mirror must also SEE variant price +
+ // descriptionHtml + variant shape so it can monitor the sample/product/price
+ // completeness rule going forward (this sync was previously BLIND to all of
+ // those — only id/sku were fetched). descriptionHtml + variant price/title are
+ // added here; the per-product rollup (has_sample/has_product/variant_count/
+ // min_variant_price/has_desc) is computed below and stored on the product row.
const query = `
query getProducts($cursor: String) {
- products(first: 250, after: $cursor) {
+ products(first: 200, after: $cursor) {
pageInfo {
hasNextPage
endCursor
@@ -77,6 +98,7 @@ async function syncProducts(quickMode = false) {
productType
status
tags
+ descriptionHtml
createdAt
updatedAt
variants(first: 100) {
@@ -84,6 +106,8 @@ async function syncProducts(quickMode = false) {
node {
id
sku
+ title
+ price
}
}
}
@@ -108,9 +132,20 @@ async function syncProducts(quickMode = false) {
const pageInfo = result.data?.products?.pageInfo;
for (const { node: product } of products) {
- const variants = product.variants?.edges || [];
+ const variants = (product.variants?.edges || []).map(e => e.node);
+
+ // per-product 5-field rollup (computed once, stored on every variant row)
+ const only = variants.length === 1;
+ const sampleFlags = variants.map(v => isSample(v, only));
+ const hasSample = sampleFlags.some(Boolean);
+ const hasProduct = variants.some((v, i) => !sampleFlags[i]);
+ const prices = variants.map(v => (v.price == null ? null : parseFloat(v.price))).filter(p => p != null);
+ const minPrice = prices.length ? Math.min(...prices) : null;
+ const bodyHtml = product.descriptionHtml || '';
+ const hasDesc = bodyHtml.replace(/<[^>]*>/g, '').trim().length >= 5;
+ const variantCount = variants.length;
- for (const { node: variant } of variants) {
+ for (const variant of variants) {
if (!variant.sku) continue; // Skip variants without SKU
try {
@@ -118,8 +153,11 @@ async function syncProducts(quickMode = false) {
INSERT INTO shopify_products (
shopify_id, handle, title, vendor, product_type,
sku, variant_id, variant_sku, tags, status,
- created_at_shopify, updated_at_shopify, synced_at
- ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NOW())
+ created_at_shopify, updated_at_shopify, synced_at,
+ body_html, variant_count, min_variant_price,
+ has_sample_variant, has_product_variant, has_description
+ ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NOW(),
+ $13, $14, $15, $16, $17, $18)
ON CONFLICT (shopify_id) DO UPDATE SET
handle = EXCLUDED.handle,
title = EXCLUDED.title,
@@ -130,6 +168,12 @@ async function syncProducts(quickMode = false) {
tags = EXCLUDED.tags,
status = EXCLUDED.status,
updated_at_shopify = EXCLUDED.updated_at_shopify,
+ body_html = EXCLUDED.body_html,
+ variant_count = EXCLUDED.variant_count,
+ min_variant_price = EXCLUDED.min_variant_price,
+ has_sample_variant = EXCLUDED.has_sample_variant,
+ has_product_variant = EXCLUDED.has_product_variant,
+ has_description = EXCLUDED.has_description,
synced_at = NOW()
`, [
product.id,
@@ -143,7 +187,13 @@ async function syncProducts(quickMode = false) {
product.tags,
product.status,
product.createdAt,
- product.updatedAt
+ product.updatedAt,
+ bodyHtml.slice(0, 200000),
+ variantCount,
+ minPrice,
+ hasSample,
+ hasProduct,
+ hasDesc
]);
totalSynced++;
} catch (err) {
← f8f483e6 Osborne width: API sweep populated osborne_catalog 787->1410
·
back to Designer Wallcoverings
·
Harden Boost price hide/restore CSS: grid-scope hide + theme 195e6068 →