Catalog-wide Finish/Collection cosmetic cleanup: Title-Case 2,780 all-caps Collections + normalize 358 semicolon Finishes across all vendors (Shopify + mirror), 0 fail

3fae184fc06c0cd2e2d311ea84110e506df986f8 · 2026-07-30 12:10:01 -0700 · Steve Abrams

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

Files touched

A tmp-catalog-finish-collection-clean.mjs

Diff

commit 3fae184fc06c0cd2e2d311ea84110e506df986f8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Jul 30 12:10:01 2026 -0700

    Catalog-wide Finish/Collection cosmetic cleanup: Title-Case 2,780 all-caps Collections + normalize 358 semicolon Finishes across all vendors (Shopify + mirror), 0 fail
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 tmp-catalog-finish-collection-clean.mjs | 82 +++++++++++++++++++++++++++++++++
 1 file changed, 82 insertions(+)

diff --git a/tmp-catalog-finish-collection-clean.mjs b/tmp-catalog-finish-collection-clean.mjs
new file mode 100644
index 0000000..7d355f4
--- /dev/null
+++ b/tmp-catalog-finish-collection-clean.mjs
@@ -0,0 +1,82 @@
+#!/usr/bin/env node
+// Catalog-wide Finish/Collection cosmetic cleanup (ALL vendors), driven from the fresh mirror.
+// Title-Case all-caps global.Collection + normalize semicolon global.Finish. Writes Shopify AND the mirror.
+// Ledger-resumable. Only touches products whose value actually changes.
+import fs from 'node:fs';
+import { execFileSync } from 'node:child_process';
+const env=Object.fromEntries(fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/.env','utf8').split('\n').filter(l=>l.includes('=')).map(l=>{const i=l.indexOf('=');return[l.slice(0,i).trim(),l.slice(i+1).trim()];}));
+const DOMAIN=(env.SHOPIFY_STORE_DOMAIN||env.SHOPIFY_STORE).replace(/^https?:\/\//,'').replace(/\/$/,'');
+const GQL=`https://${DOMAIN}/admin/api/2024-10/graphql.json`;
+const H={'X-Shopify-Access-Token':env.SHOPIFY_ADMIN_TOKEN,'Content-Type':'application/json'};
+const sleep=ms=>new Promise(r=>setTimeout(r,ms));
+const DRY=process.argv.includes('--dry');
+const LIMIT=(()=>{const i=process.argv.indexOf('--limit');return i>=0?Number(process.argv[i+1]):null;})();
+
+const SMALL=new Set(["and","or","the","of","in","a","an","for","to","with","by"]);
+const ROMAN=/^(?:I{1,3}|IV|VI{0,3}|IX|XI{0,3})$/;
+const capWord=p=>!p?p:(/^\d/.test(p)?p:p[0].toUpperCase()+p.slice(1).toLowerCase());
+function titleCase(s){
+  return String(s).split(/\s+/).map((w,i)=>{
+    if(!w) return w;
+    const low=w.toLowerCase();
+    if(i>0 && SMALL.has(low)) return low;
+    if(ROMAN.test(w)) return w.toUpperCase();
+    if(w.includes('&')) return w.split(/([&\-])/).map(part=>(part==='&'||part==='-')?part:/^[A-Z0-9]{1,3}$/.test(part)?part:capWord(part)).join('');
+    return w.split('-').map(capWord).join('-');
+  }).join(" ").replace(/\s+/g," ").trim();
+}
+const cleanFinish=v=>String(v).split(";").map(t=>t.trim()).filter(Boolean).map(titleCase).join(", ");
+const isAllCaps=s=>/[A-Za-z]/.test(s)&&s===s.toUpperCase();
+
+async function gql(q,v,t=0){
+  const r=await fetch(GQL,{method:'POST',headers:H,body:JSON.stringify({query:q,variables:v})});
+  if(r.status===429||r.status>=500){if(t<6){await sleep(1500*(t+1));return gql(q,v,t+1);}}
+  const j=await r.json();
+  if(j.errors){if(t<6&&JSON.stringify(j.errors).includes('Throttled')){await sleep(2000*(t+1));return gql(q,v,t+1);}throw new Error(JSON.stringify(j.errors).slice(0,180));}
+  return j.data;
+}
+const psql=sql=>execFileSync('psql',['-h','/tmp','-d','dw_unified','-tA','-F','\t','-c',sql],{maxBuffer:1<<28}).toString();
+
+// targets: active products where Collection is all-caps OR Finish has ';'
+const rows=psql(`
+  SELECT replace(shopify_id,'gid://shopify/Product/','') AS id, sku,
+         coalesce(metafields->'global'->'Collection'->>'value',''),
+         coalesce(metafields->'global'->'Finish'->>'value','')
+  FROM shopify_products
+  WHERE status='ACTIVE' AND metafields->'global' IS NOT NULL
+    AND ( (metafields->'global'->'Collection'->>'value') ~ '[A-Za-z]' AND (metafields->'global'->'Collection'->>'value')=upper(metafields->'global'->'Collection'->>'value')
+          OR (metafields->'global'->'Finish'->>'value') LIKE '%;%' )
+  ORDER BY sku ${LIMIT?`LIMIT ${LIMIT}`:''};`).trim().split('\n').filter(Boolean).map(l=>{const[id,sku,coll,fin]=l.split('\t');return{id,sku,coll,fin};});
+
+const LEDGER='/Users/macstudio3/kravet-feed-pull/catalog-fincoll-done.txt';
+const done=new Set(fs.existsSync(LEDGER)?fs.readFileSync(LEDGER,'utf8').trim().split('\n').filter(Boolean):[]);
+const todo=rows.filter(r=>!done.has(r.sku));
+console.log(`candidates:${rows.length}  todo:${todo.length}  ${DRY?'[DRY]':''}`);
+
+let ok=0,cSet=0,fSet=0,fail=0,shown=0;
+for(const {id,sku,coll,fin} of todo){
+  try{
+    let newColl,newFin;
+    if(coll && isAllCaps(coll)){ const t=titleCase(coll); if(t&&t!==coll) newColl=t.slice(0,255); }
+    if(fin && fin.includes(';')){ const t=cleanFinish(fin); if(t&&t!==fin) newFin=t.slice(0,255); }
+    if(!newColl && !newFin){ if(!DRY)fs.appendFileSync(LEDGER,sku+'\n'); ok++; continue; }
+    if(DRY){ if(shown<15){console.log(`  ${sku}: ${newColl?`Coll "${coll}"->"${newColl}"`:''} ${newFin?`Fin "${fin}"->"${newFin}"`:''}`);shown++;} if(newColl)cSet++; if(newFin)fSet++; ok++; continue; }
+    const mfs=[];
+    if(newColl) mfs.push({ownerId:`gid://shopify/Product/${id}`,namespace:'global',key:'Collection',type:'single_line_text_field',value:newColl});
+    if(newFin)  mfs.push({ownerId:`gid://shopify/Product/${id}`,namespace:'global',key:'Finish',type:'single_line_text_field',value:newFin});
+    const d=await gql(`mutation($m:[MetafieldsSetInput!]!){metafieldsSet(metafields:$m){userErrors{message}}}`,{m:mfs});
+    if(d.metafieldsSet.userErrors.length) throw new Error(JSON.stringify(d.metafieldsSet.userErrors).slice(0,160));
+    // keep the mirror consistent too
+    const sets=[]; if(newColl)sets.push(`jsonb_set(metafields,'{global,Collection,value}',to_jsonb('${newColl.replace(/'/g,"''")}'::text))`);
+    if(newColl&&newFin){ execFileSync('psql',['-h','/tmp','-d','dw_unified','-c',
+      `UPDATE shopify_products SET metafields=jsonb_set(jsonb_set(metafields,'{global,Collection,value}',to_jsonb('${newColl.replace(/'/g,"''")}'::text)),'{global,Finish,value}',to_jsonb('${newFin.replace(/'/g,"''")}'::text)) WHERE shopify_id='gid://shopify/Product/${id}';`]); }
+    else if(newColl){ execFileSync('psql',['-h','/tmp','-d','dw_unified','-c',
+      `UPDATE shopify_products SET metafields=jsonb_set(metafields,'{global,Collection,value}',to_jsonb('${newColl.replace(/'/g,"''")}'::text)) WHERE shopify_id='gid://shopify/Product/${id}';`]); }
+    else if(newFin){ execFileSync('psql',['-h','/tmp','-d','dw_unified','-c',
+      `UPDATE shopify_products SET metafields=jsonb_set(metafields,'{global,Finish,value}',to_jsonb('${newFin.replace(/'/g,"''")}'::text)) WHERE shopify_id='gid://shopify/Product/${id}';`]); }
+    if(newColl)cSet++; if(newFin)fSet++; ok++; fs.appendFileSync(LEDGER,sku+'\n');
+    if(ok%200===0) console.log(`  ...${ok}/${todo.length}  (Collection:${cSet} Finish:${fSet} fail:${fail})`);
+    await sleep(90);
+  }catch(e){ fail++; if(fail<=20)console.error(`  FAIL ${sku}: ${e.message}`); }
+}
+console.log(`\n${DRY?'DRY':'CATALOG FIN/COLL'} DONE. products:${ok}  Collection:${cSet}  Finish:${fSet}  fail:${fail}`);

← 43a400e auto-save: 2026-07-30T10:16:54 (1 files) — tmp-kravet-finish · back to Contractwallpaper · contractwallpaper: adopt href-to-deeper-data storefront prim 1ab1552 →