TK-12162: mint checks vendor catalog reservations + confirms the number is free LIVE (FileMaker + Shopify)

67566bdba43c8a5a7fa6ac42b379eb46a16bcada · 2026-09-24 17:19:56 -0700 · Steve Abrams

The mint's sources are all mirrors (shopify_products last synced 09-23) and none read the
vendor scrape catalog, which pre-assigns dw_skus (tres_tintas_catalog: DWTS-900001..901506).
So a mint could hand out a reserved number, or the same number twice before a sync.

- adds vendor_registry.catalog_table reservations to the used-number set
- JS Pattern is TEXT in FileMaker (no reliable max), so the chosen number is verified free by an
  exact live FileMaker find + Shopify SKU prefix search, stepping past taken numbers (max 25);
  if a live source can't answer, the mint still returns but carries a flag
- tested read-only on prod: 901507 taken=true, 987654 taken=false, DWTT-72290 taken=true,
  Tres Tintas mint -> DWTS-901514, clear of both reserved ranges

Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>

Files touched

M server.js

Diff

commit 67566bdba43c8a5a7fa6ac42b379eb46a16bcada
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Sep 24 17:19:56 2026 -0700

    TK-12162: mint checks vendor catalog reservations + confirms the number is free LIVE (FileMaker + Shopify)
    
    The mint's sources are all mirrors (shopify_products last synced 09-23) and none read the
    vendor scrape catalog, which pre-assigns dw_skus (tres_tintas_catalog: DWTS-900001..901506).
    So a mint could hand out a reserved number, or the same number twice before a sync.
    
    - adds vendor_registry.catalog_table reservations to the used-number set
    - JS Pattern is TEXT in FileMaker (no reliable max), so the chosen number is verified free by an
      exact live FileMaker find + Shopify SKU prefix search, stepping past taken numbers (max 25);
      if a live source can't answer, the mint still returns but carries a flag
    - tested read-only on prod: 901507 taken=true, 987654 taken=false, DWTT-72290 taken=true,
      Tres Tintas mint -> DWTS-901514, clear of both reserved ranges
    
    Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
---
 server.js | 38 +++++++++++++++++++++++++++++++++++++-
 1 file changed, 37 insertions(+), 1 deletion(-)

diff --git a/server.js b/server.js
index ff90349..aa9f3c0 100644
--- a/server.js
+++ b/server.js
@@ -2757,10 +2757,46 @@ async function mintDwSku(vreg) {
   if (fmq.ok) for (const r of fmq.rows) { const n = parseInt(r[0], 10); if (Number.isFinite(n)) used.add(n); }
   const stg = await pgQuery(`select regexp_replace(dw_sku,'^${esc}','')::text from new_items_staging where to_regclass('public.new_items_staging') is not null and dw_sku ~ '^${esc}[0-9]+$'`);
   if (stg.ok) for (const r of stg.rows) { const n = parseInt(r[0], 10); if (Number.isFinite(n)) used.add(n); }
+  // the vendor's own scrape catalog pre-assigns dw_skus to products not yet on Shopify/FM
+  // (tres_tintas_catalog reserves DWTS-900001..901506) — those numbers are taken too.
+  const cat = await pgQuery(`select catalog_table from vendor_registry where vendor_code='${String(vreg.vid || '').replace(/'/g, "''")}' limit 1`);
+  const catTable = cat.ok && cat.rows[0] && /^[a-z0-9_]+$/.test(cat.rows[0][0] || '') ? cat.rows[0][0] : null;
+  if (catTable) {
+    const cq = await pgQuery(`select regexp_replace(dw_sku,'^${esc}','')::text from ${catTable} where to_regclass('public.${catTable}') is not null and dw_sku ~ '^${esc}[0-9]+$'`);
+    if (cq.ok) for (const r of cq.rows) { const n = parseInt(r[0], 10); if (Number.isFinite(n)) used.add(n); }
+  }
   const maxUsed = used.size ? Math.max(...used) : 0;
   let n = Math.max(vreg.sku_range_start || 0, maxUsed + 1, 1);
   while (used.has(n)) n++;
-  return { dw_sku: `${prefix}${n}`, series: pfxNoDash, js_pattern: String(n), provisional: false, flag: null };
+  // Every source above is a mirror that can lag the live systems by a day. JS Pattern is a TEXT field
+  // in FileMaker, so "max" can't be asked of it reliably — instead confirm the chosen number is free
+  // LIVE (exact FileMaker match + Shopify SKU prefix search) and step past any number that is taken.
+  let liveFlag = null;
+  for (let tries = 0; tries < 25; tries++) {
+    const taken = await liveNumberTaken(pfxNoDash, n);
+    if (taken === null) { liveFlag = `could not confirm ${prefix}${n} is free in live FileMaker/Shopify — verify before go-live`; break; }
+    if (!taken) break;
+    n++; while (used.has(n)) n++;
+  }
+  return { dw_sku: `${prefix}${n}`, series: pfxNoDash, js_pattern: String(n), provisional: false, flag: liveFlag };
+}
+
+// true = the number is already used live, false = free, null = a live source could not answer.
+async function liveNumberTaken(series, n) {
+  let fmTaken, shopTaken;
+  try {
+    const r = await FM.fmFind(FM_DB, FM_WP_CREATE_LAYOUT, [{ 'Series': '==' + series, 'JS Pattern': '==' + n }], { limit: 1, portal: [] });
+    fmTaken = (r.records || r.data || []).length > 0;
+  } catch (e) { fmTaken = /\b401\b|no records match/i.test(e.message) ? false : null; }
+  try {
+    const q = `{ productVariants(first: 10, query: "sku:${series}-${n}*") { edges { node { sku } } } }`;
+    const b = await gql(q);
+    if (!b.data) shopTaken = null;
+    else shopTaken = b.data.productVariants.edges.some(e => new RegExp(`^${series}-${n}(\\D|$)`).test(e.node.sku || ''));
+  } catch (e) { shopTaken = null; }
+  if (fmTaken === true || shopTaken === true) return true;
+  if (fmTaken === null || shopTaken === null) return null;
+  return false;
 }
 
 // ── FileMaker WALLPAPER master fieldData builder ─────────────────────────────

← 2bc55d4 TK-12162: 582 FileMaker masters created for live Tres Tintas · back to Dw Photo Capture · auto-data-snapshot: 2026-09-24T20:29:39 (2 data files) — .cl e99e554 →