← back to New Import Viewer
new-import-viewer: enforce 3-source NEVER-DUPLICATE in net-new predicate + hard server-side stage gate
1fb964461355a6957901e73449f7725167bda797 · 2026-06-10 17:42:12 -0700 · SteveStudio2
NETNEW_PRED now NOT-EXISTS against dw_sku_registry as well as shopify_products
(was shopify-only — 49,386 distinct mfr_skus were shopify-clean but already in
the 84k registry → would have imported as dups). Added dedupFilterIds() that
re-validates every staged/live candidate row's mfr_sku against shopify_products
AND dw_sku_registry on upper(trim()), dropping dups + un-dedupable rows, fail-safe
(query error aborts the whole batch). Applied in /api/action before BOTH staging
and live writes so a stale id list / raw curl can never push a known dup. /dtd-A 2026-06-10.
Files touched
Diff
commit 1fb964461355a6957901e73449f7725167bda797
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Wed Jun 10 17:42:12 2026 -0700
new-import-viewer: enforce 3-source NEVER-DUPLICATE in net-new predicate + hard server-side stage gate
NETNEW_PRED now NOT-EXISTS against dw_sku_registry as well as shopify_products
(was shopify-only — 49,386 distinct mfr_skus were shopify-clean but already in
the 84k registry → would have imported as dups). Added dedupFilterIds() that
re-validates every staged/live candidate row's mfr_sku against shopify_products
AND dw_sku_registry on upper(trim()), dropping dups + un-dedupable rows, fail-safe
(query error aborts the whole batch). Applied in /api/action before BOTH staging
and live writes so a stale id list / raw curl can never push a known dup. /dtd-A 2026-06-10.
---
server.js | 86 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++---
1 file changed, 83 insertions(+), 3 deletions(-)
diff --git a/server.js b/server.js
index 6b90c18..266cae1 100644
--- a/server.js
+++ b/server.js
@@ -84,8 +84,21 @@ const NEW_PRED = "(vc.sync_status='new' OR ((vc.on_shopify IS NOT TRUE) AND vc.s
// Case/trim-folded match: raw equality missed 3,346 rows already on Shopify under
// whitespace/case-differing mfr_sku, inflating NET NEW. (A functional index on
// shopify_products(upper(trim(mfr_sku))) would keep this fast — recommend adding.)
-const NETNEW_PRED = `(${NEW_PRED} AND vc.mfr_sku IS NOT NULL AND vc.mfr_sku <> '' AND NOT EXISTS (
- SELECT 1 FROM shopify_products sx WHERE upper(trim(sx.mfr_sku)) = upper(trim(vc.mfr_sku))))`;
+//
+// THREE-SOURCE NEVER-DUPLICATE (Steve HARD RULE, /dtd-A 2026-06-10): a scraped
+// mfr pattern number is a DUP — and must NOT count as net-new — if it appears in
+// ANY of (1) shopify_products (160k), (2) dw_sku_registry (84k, the assigned-SKU
+// ledger), (3) the vendor's own catalog table. Source (3) is vendor_catalog ITSELF
+// here (this viewer reads vendor_catalog), so the rows we'd stage already live in
+// it; the meaningful *additional* dedup source the old shopify-only predicate
+// missed is dw_sku_registry. Measured 2026-06-10: 49,386 distinct mfr_skus were
+// shopify-clean yet ALREADY in dw_sku_registry — they'd have imported as dups.
+// Match normalized upper(trim()), hyphens preserved. idx_sp_mfr_upper_trim +
+// idx_vc_mfr_upper_trim cover the shopify side; a functional registry index on
+// upper(trim(mfr_sku)) would speed the registry NOT EXISTS (recommend adding).
+const NETNEW_PRED = `(${NEW_PRED} AND vc.mfr_sku IS NOT NULL AND vc.mfr_sku <> ''
+ AND NOT EXISTS (SELECT 1 FROM shopify_products sx WHERE upper(trim(sx.mfr_sku)) = upper(trim(vc.mfr_sku)))
+ AND NOT EXISTS (SELECT 1 FROM dw_sku_registry rx WHERE upper(trim(rx.mfr_sku)) = upper(trim(vc.mfr_sku))))`;
// Resolve one shopify_products row per vendor_catalog row. shopify_id is a
// gid://shopify/Product/<n> string (UNIQUE index); vc.shopify_product_id is the
// bare bigint. Construct the gid on the vc side so the unique index on
@@ -154,7 +167,49 @@ function send(res, code, body, type) {
// data/launch-batches/<batchId>.json — queue lines before 2026-06-10 carried
// only counts, which made them un-actionable (a consumer had nothing to act
// on). Manifested batches are the authoritative, image-gated staged set.
+// HARD NEVER-DUPLICATE GATE for staging (Steve HARD RULE, /dtd-A 2026-06-10):
+// the /api/action launch path stages by raw row IDs — a client (UI bug, stale
+// id list, or raw curl) could pass IDs that are NOT net-new (already on Shopify
+// or already in dw_sku_registry). The NETNEW_PRED filter only governs what the UI
+// *lists*; it does NOT bind what gets staged. So re-validate every candidate row
+// here against the two external dedup sources (shopify_products + dw_sku_registry)
+// on normalized upper(trim(mfr_sku)) and DROP any row whose mfr_sku already exists.
+// Fail-safe: if the dedup query throws, we BLOCK the whole batch (let the error
+// propagate) rather than stage an unverified set. Returns {keep:Set<id>, blocked}.
+function dedupFilterIds(ids) {
+ const idList = ids.join(',');
+ // Candidate (id, normalized mfr_sku) for the requested rows.
+ const cand = q(`SELECT vc.id, upper(trim(vc.mfr_sku)) AS k
+ FROM vendor_catalog vc
+ WHERE vc.id IN (${idList})
+ AND vc.mfr_sku IS NOT NULL AND vc.mfr_sku <> ''`);
+ // Rows with a non-empty mfr_sku that ALSO exist on Shopify or in the registry.
+ // Set-based (DISTINCT CTE + IN) so it stays index/hash-join fast at 5k ids.
+ const dup = q(`WITH cand AS (
+ SELECT vc.id, upper(trim(vc.mfr_sku)) AS k FROM vendor_catalog vc
+ WHERE vc.id IN (${idList}) AND vc.mfr_sku IS NOT NULL AND vc.mfr_sku <> ''),
+ shop AS (SELECT DISTINCT upper(trim(mfr_sku)) k FROM shopify_products WHERE mfr_sku IS NOT NULL),
+ reg AS (SELECT DISTINCT upper(trim(mfr_sku)) k FROM dw_sku_registry WHERE mfr_sku IS NOT NULL)
+ SELECT c.id FROM cand c
+ WHERE c.k IN (SELECT k FROM shop) OR c.k IN (SELECT k FROM reg)`);
+ const dupIds = new Set(dup.map(r => +r[0]));
+ // Rows missing an mfr_sku entirely can't be dedup-verified → BLOCK them too
+ // (never stage an un-dedupable row; fix the scrape, don't import around it).
+ const haveSku = new Set(cand.map(r => +r[0]));
+ const keep = ids.filter(id => haveSku.has(id) && !dupIds.has(id));
+ const blocked = ids.length - keep.length;
+ return { keep, blocked, dupBlocked: dupIds.size, noSkuBlocked: ids.length - haveSku.size };
+}
+
function stageAction({ ids, action, mode }) {
+ // NEVER-DUPLICATE: drop any candidate already on Shopify / in the registry, or
+ // lacking a dedup-able mfr_sku, BEFORE building the staged manifest. Fail-safe —
+ // a throw here aborts the whole stage rather than staging an unverified batch.
+ const ded = dedupFilterIds(ids);
+ ids = ded.keep;
+ if (!ids.length) {
+ return { ok: false, error: `all ${ded.blocked} selected row(s) BLOCKED by NEVER-DUPLICATE gate (already on Shopify / in dw_sku_registry, or missing mfr_sku)`, staged: 0, action, mode, dupBlocked: ded.dupBlocked, noSkuBlocked: ded.noSkuBlocked, live: false };
+ }
const idList = ids.join(',');
const rows = q(`SELECT vc.id, vc.vendor_code, vc.mfr_sku, vc.dw_sku, vc.pattern_name, ${SHOP_STATUS} AS st
FROM vendor_catalog vc ${SP_JOIN}
@@ -169,7 +224,14 @@ function stageAction({ ids, action, mode }) {
fs.writeFileSync(manifest, JSON.stringify({ batchId, action, mode, at: new Date().toISOString(), count: rows.length, skus }));
const qf = path.join(__dirname, 'data', 'launch-queue.jsonl');
fs.appendFileSync(qf, JSON.stringify({ batchId, action, mode, count: rows.length, skus: rows.length, manifest: 'data/launch-batches/' + batchId + '.json', at: new Date().toISOString() }) + '\n');
- return { ok: true, staged: rows.length, action, mode, batchId, byVendor, live: false };
+ const out = { ok: true, staged: rows.length, action, mode, batchId, byVendor, live: false };
+ if (ded.blocked) {
+ out.dedupBlocked = ded.blocked;
+ out.dupBlocked = ded.dupBlocked;
+ out.noSkuBlocked = ded.noSkuBlocked;
+ out.dedupNote = `${ded.blocked} row(s) BLOCKED by NEVER-DUPLICATE gate (${ded.dupBlocked} already on Shopify/registry, ${ded.noSkuBlocked} missing mfr_sku)`;
+ }
+ return out;
}
// ── Local thumbnail proxy + disk cache ─────────────────────────────────────
@@ -352,11 +414,29 @@ const server = http.createServer((req, res) => {
if (!ids.length) return send(res, 400, JSON.stringify({
error: `all ${noImageBlocked} selected row(s) lack images — ALL PRODUCTS MUST HAVE IMAGES; fix the scrape, don't import around it`, noImageBlocked }));
}
+ // NEVER-DUPLICATE end-to-end (Steve HARD RULE, /dtd-A 2026-06-10): drop
+ // any selected row already on Shopify / in dw_sku_registry (or missing a
+ // dedup-able mfr_sku) BEFORE either staging OR a live write — so a stale
+ // id list / raw curl can't push a known dup down either path. Fail-safe:
+ // a query error throws and the outer catch returns 500 (nothing staged).
+ const ded = dedupFilterIds(ids);
+ const dedupBlocked = ids.length - ded.keep.length;
+ ids = ded.keep;
+ if (!ids.length) return send(res, 400, JSON.stringify({
+ error: `all ${dedupBlocked} selected row(s) BLOCKED by NEVER-DUPLICATE gate (already on Shopify / in dw_sku_registry, or missing mfr_sku)`,
+ dedupBlocked, dupBlocked: ded.dupBlocked, noSkuBlocked: ded.noSkuBlocked }));
const out = stageAction({ ids, action, mode });
+ if (!out.ok) return send(res, 400, JSON.stringify(out));
if (noImageBlocked) {
out.noImageBlocked = noImageBlocked;
out.note = `${noImageBlocked} no-image row(s) BLOCKED from launch (all products must have images)`;
}
+ if (dedupBlocked) {
+ out.dedupBlocked = dedupBlocked;
+ out.dupBlocked = ded.dupBlocked;
+ out.noSkuBlocked = ded.noSkuBlocked;
+ out.dedupNote = `${dedupBlocked} row(s) BLOCKED by NEVER-DUPLICATE gate before ${action} (${ded.dupBlocked} on Shopify/registry, ${ded.noSkuBlocked} missing mfr_sku)`;
+ }
// SAFETY: bulk requests (stageOnly) NEVER fire live writes — live store
// changes only ever happen one card at a time. The fat-finger blast
← 7be6207 index.html review fixes: map Shopify lowercase status (activ
·
back to New Import Viewer
·
9790: fix /api/stats death-spiral — statement_timeout guard 0d81ad3 →