← back to Designer Wallcoverings

Fix vendor_registry total_products rollup: filter shared catalog tables per-vendor

08e84a2c634da447758f5f3f278fcd6c35ecfd9e · 2026-06-24 07:21:36 -0700 · Steve

syncVendorCounts only treated 'vendor_catalog' as shared; quadrille_house_catalog
(9 Quadrille-family vendors) counted the whole 1942-row table for each, so every
sub-brand reported total_products=1942. Generalized: any catalog_table referenced
by >1 vendor is filtered by a resolved discriminator (vendor_code, else brand/vendor
=vendor_name). Verified: Quadrille family now 615/434/318/235/211/83/41/5, Lulu DK=0.

Files touched

• M DW-Agents/vendor-command-center/server.js

Diff

commit 08e84a2c634da447758f5f3f278fcd6c35ecfd9e
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Jun 24 07:21:36 2026 -0700

    Fix vendor_registry total_products rollup: filter shared catalog tables per-vendor
    
    syncVendorCounts only treated 'vendor_catalog' as shared; quadrille_house_catalog
    (9 Quadrille-family vendors) counted the whole 1942-row table for each, so every
    sub-brand reported total_products=1942. Generalized: any catalog_table referenced
    by >1 vendor is filtered by a resolved discriminator (vendor_code, else brand/vendor
    =vendor_name). Verified: Quadrille family now 615/434/318/235/211/83/41/5, Lulu DK=0.
---
 DW-Agents/vendor-command-center/server.js | 41 ++++++++++++++++++++++++++++---
 1 file changed, 37 insertions(+), 4 deletions(-)

diff --git a/DW-Agents/vendor-command-center/server.js b/DW-Agents/vendor-command-center/server.js
index 696176c4..f230a02a 100644
--- a/DW-Agents/vendor-command-center/server.js
+++ b/DW-Agents/vendor-command-center/server.js
@@ -2152,6 +2152,30 @@ async function syncVendorCounts() {
     // Get all vendors with current counts for change detection
     const { rows: allVendors } = await pool.query('SELECT vendor_code, vendor_name, catalog_table, catalog_count as old_cat, shopify_count as old_shop, total_products as old_total FROM vendor_registry');
 
+    // Detect catalog_tables shared by >1 vendor — these must be filtered to THIS
+    // vendor's rows or every sharer gets the whole-table count (the quadrille_house_catalog
+    // bug: 9 vendors all reported 1942). Discriminator column resolved per table:
+    // vendor_code (value=vendor_code) preferred, else brand/vendor (value=vendor_name).
+    const tableVendorCounts = {};
+    for (const _v of allVendors) {
+      if (_v.catalog_table) tableVendorCounts[_v.catalog_table] = (tableVendorCounts[_v.catalog_table] || 0) + 1;
+    }
+    const sharedDiscriminatorCache = {};
+    async function resolveSharedDiscriminator(t) {
+      if (t in sharedDiscriminatorCache) return sharedDiscriminatorCache[t];
+      let col = null;
+      try {
+        const { rows } = await pool.query(
+          "SELECT column_name FROM information_schema.columns WHERE table_name=$1 AND column_name IN ('vendor_code','brand','vendor')",
+          [t]
+        );
+        const have = new Set(rows.map(r => r.column_name));
+        col = have.has('vendor_code') ? 'vendor_code' : have.has('brand') ? 'brand' : have.has('vendor') ? 'vendor' : null;
+      } catch (_) { /* table may not exist */ }
+      sharedDiscriminatorCache[t] = col;
+      return col;
+    }
+
     for (const v of allVendors) {
       const code = v.vendor_code;
       const catTable = v.catalog_table;
@@ -2167,10 +2191,19 @@ async function syncVendorCounts() {
         try {
           const cols = CATALOG_COLUMNS[catTable] || { price: null, image: 'image_url' };
 
-          // When table is vendor_catalog (shared), filter by vendor_code
-          const isShared = catTable === 'vendor_catalog';
-          const vendorFilter = isShared ? ` WHERE vendor_code = '${code.replace(/'/g, "''")}'` : '';
-          const vendorAnd = isShared ? ` AND vendor_code = '${code.replace(/'/g, "''")}'` : '';
+          // Shared catalog tables (>1 vendor) must be filtered to THIS vendor's rows,
+          // else every sharer is counted the whole table. Discriminator resolved per
+          // table: vendor_code (value=vendor_code) preferred, else brand/vendor (value=vendor_name).
+          const isShared = (tableVendorCounts[catTable] || 0) > 1;
+          let vendorFilter = '', vendorAnd = '';
+          if (isShared) {
+            const discCol = await resolveSharedDiscriminator(catTable);
+            if (discCol) {
+              const discVal = String((discCol === 'vendor_code' ? code : v.vendor_name) || '').replace(/'/g, "''");
+              vendorFilter = ` WHERE ${discCol} = '${discVal}'`;
+              vendorAnd = ` AND ${discCol} = '${discVal}'`;
+            }
+          }
 
           // Total catalog products
           const totalRes = await pool.query(`SELECT COUNT(*) as cnt FROM ${catTable}${vendorFilter}`);

← b973b625 auto-save: 2026-06-24T06:55:19 (4 files) — shopify/scripts/c  ·  back to Designer Wallcoverings  ·  auto-save: 2026-06-24T07:25:27 (2 files) — vendor-scrapers/c fa89fca7 →