portable snapshot SQL: probe optional mirror-only columns (min_variant_price, online_store_published) so the same code runs on Kamatera canonical

86857a5fb004494b4d96c0ac9076ab8bdf313108 · 2026-07-02 16:13:36 -0700 · Steve Abrams

Files touched

M server.js

Diff

commit 86857a5fb004494b4d96c0ac9076ab8bdf313108
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Jul 2 16:13:36 2026 -0700

    portable snapshot SQL: probe optional mirror-only columns (min_variant_price, online_store_published) so the same code runs on Kamatera canonical
---
 server.js | 19 +++++++++++++++----
 1 file changed, 15 insertions(+), 4 deletions(-)

diff --git a/server.js b/server.js
index 529e30e..9563791 100644
--- a/server.js
+++ b/server.js
@@ -52,14 +52,25 @@ const BANNED = /brewster|york|wallquest|wall\s*quest|newwall|new\s*wall\b|comman
 
 // ── snapshot load ────────────────────────────────────────────────────────────────
 // Public-safe columns ONLY — cost_price / net_price / cost never selected.
-const SNAP_SQL = `
+// The Mac2 mirror carries rollup columns (min_variant_price, online_store_published)
+// that the Kamatera canonical doesn't — probe once and build a portable SELECT.
+async function snapSql() {
+  const { rows } = await pool.query(
+    `SELECT column_name FROM information_schema.columns
+     WHERE table_name='shopify_products' AND column_name = ANY($1)`,
+    [['min_variant_price', 'online_store_published']]);
+  const has = new Set(rows.map((r) => r.column_name));
+  const priceExpr = has.has('min_variant_price') ? 'coalesce(price, min_variant_price)' : 'price';
+  const pubExpr = has.has('online_store_published') ? 'online_store_published' : "(upper(coalesce(status,'')) = 'ACTIVE')";
+  return `
   SELECT id, handle, title, vendor, product_type, dw_sku, variant_sku, sku, pattern_name,
          tags, upper(coalesce(status,'')) AS status, image_url,
-         coalesce(price, min_variant_price) AS price,
-         online_store_published,
+         ${priceExpr} AS price,
+         ${pubExpr} AS online_store_published,
          created_at_shopify, updated_at_shopify
   FROM shopify_products
   WHERE coalesce(status,'') <> 'DELETED_FROM_SHOPIFY'`;
+}
 
 let ROWS = [];
 let LOADED_AT = null;
@@ -102,7 +113,7 @@ function deriveRow(r) {
 async function loadSnapshot() {
   if (!pool) pool = new Pool({ connectionString: DSN, max: 2 });
   const t0 = Date.now();
-  const res = await pool.query(SNAP_SQL);
+  const res = await pool.query(await snapSql());
   ROWS = res.rows
     .filter((r) => !BANNED.test(r.vendor || '') && !BANNED.test(r.title || ''))
     .map(deriveRow);

← c8c8cd7 banned front-facing vendor scrub on the public snapshot (Bre · back to All Designerwallcoverings · amazon-style left filter rail + grid/list toggle + sortable bb41e31 →