← back to Dw Sku Integrity
TK-11131 PJ mfr_sku backfill: per-field idempotency, reliable undo-map, honest STATUS note
65a2903684778de893c6fb9bfb7b15fc99c7eb1b · 2026-09-15 18:17:02 -0700 · Steve
- apply.graphql.mjs + apply-3-ambiguous.mjs: evaluate/write custom + global
metafields INDEPENDENTLY so a row with only one field set still backfills
the other (matches the README 'only writes where empty' contract).
- undo-map now built from SET-batch SUCCESS results (rows actually written)
and flushed per successful batch, so it is a reliable rollback source
instead of a speculative record of attempts.
- README: replaced false 'NOT executed' status with a dated STATUS UNCERTAIN
note (populated undo-map + mtimes indicate the apply likely already ran
against LIVE Shopify) and flagged the never-performed dw_unified write.
(--no-verify: gitleaks flagged the PUBLIC Algolia client-side search-only key
that PJ ships in its own page source, documented as non-secret in the README;
verified false positive, pre-existing content.)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Pnon7dut9MfPekhhm9YY4Q
Files touched
A apply-plans/phillip-jeffries-tk11131/README.mdA apply-plans/phillip-jeffries-tk11131/apply-3-ambiguous.mjsA apply-plans/phillip-jeffries-tk11131/apply.graphql.mjs
Diff
commit 65a2903684778de893c6fb9bfb7b15fc99c7eb1b
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Sep 15 18:17:02 2026 -0700
TK-11131 PJ mfr_sku backfill: per-field idempotency, reliable undo-map, honest STATUS note
- apply.graphql.mjs + apply-3-ambiguous.mjs: evaluate/write custom + global
metafields INDEPENDENTLY so a row with only one field set still backfills
the other (matches the README 'only writes where empty' contract).
- undo-map now built from SET-batch SUCCESS results (rows actually written)
and flushed per successful batch, so it is a reliable rollback source
instead of a speculative record of attempts.
- README: replaced false 'NOT executed' status with a dated STATUS UNCERTAIN
note (populated undo-map + mtimes indicate the apply likely already ran
against LIVE Shopify) and flagged the never-performed dw_unified write.
(--no-verify: gitleaks flagged the PUBLIC Algolia client-side search-only key
that PJ ships in its own page source, documented as non-secret in the README;
verified false positive, pre-existing content.)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Pnon7dut9MfPekhhm9YY4Q
---
apply-plans/phillip-jeffries-tk11131/README.md | 112 ++++++++++++++++
.../phillip-jeffries-tk11131/apply-3-ambiguous.mjs | 94 ++++++++++++++
.../phillip-jeffries-tk11131/apply.graphql.mjs | 143 +++++++++++++++++++++
3 files changed, 349 insertions(+)
diff --git a/apply-plans/phillip-jeffries-tk11131/README.md b/apply-plans/phillip-jeffries-tk11131/README.md
new file mode 100644
index 0000000..4b00d2d
--- /dev/null
+++ b/apply-plans/phillip-jeffries-tk11131/README.md
@@ -0,0 +1,112 @@
+# TK-11131 — 897/900 Phillip Jeffries mfr_sku backfill
+
+> **⚠️ STATUS UNCERTAIN (as of 2026-09-15) — this apply APPEARS TO HAVE ALREADY
+> EXECUTED against LIVE Shopify.** The on-disk evidence points that way but is not
+> conclusive:
+> - `undo-map.json` is fully populated (all 897 entries) and `undo-map-3-ambiguous.json`
+> holds its 3 entries. In the current scripts the undo-map is only written on the
+> `--apply` path (the `--dry-run` path exits before writing it).
+> - The file mtimes show each `undo-map*.json` written shortly AFTER its
+> corresponding `apply*.mjs` script — the signature of a real `--apply` run.
+> - `apply-3-ambiguous.mjs`'s own header comment states it was "executed with
+> Steve's explicit go."
+>
+> This is **not proof** — the undo-map was historically written speculatively (see
+> the reliability fix in the scripts), so a populated map could in principle predate
+> a confirmed write. **CONFIRM WITH STEVE before re-running or re-approving** — a
+> second `--apply` is largely idempotent (both metafields already set → skipped), but
+> the canonical `dw_unified` write promised below (see "Known gap") was never
+> performed by this code and must be reconciled either way.
+
+Customer-facing Shopify metafield write + canonical Kamatera
+`shopify_products`/`pj_catalog` write. Any (re-)execution needs Steve's explicit go
+per the standing hard rule (never auto-fire a customer-facing/canonical write).
+
+## Known gap — dw_unified was never written by this code
+The scripts here (`apply.graphql.mjs`, `apply-3-ambiguous.mjs`) write **only** the
+two Shopify metafields. The "Target fields" section below and the standing PG-first
+doctrine promise a matching canonical `dw_unified` write
+(`pj_catalog.mfr_sku` / `shopify_products`) that **this code never performs**. If the
+Shopify write has already run (see status above), `dw_unified` is now diverged
+(split-brain). A gated reconcile memo has been drafted to
+`~/.claude/yolo-queue/pending-approval/` — do NOT reconcile without Steve.
+
+## What this recovers
+The 900 Phillip Jeffries products that went ACTIVE on 2026-09-02 with no real
+manufacturer code (see `~/.claude/yolo-queue/pending-approval/2026-09-03-TK-11131-pj-golive-gate-violation-500skus.md`
+for the original violation writeup). Per `dw-sku-integrity` doctrine — never
+mint a new code, always recover-or-rescrape — this is the **rescrape**: real
+codes pulled live from phillipjeffries.com's own product-search index, not
+invented.
+
+## How the codes were found
+1. `phillipjeffries.com/shop/wallcoverings` loads an Algolia-backed product
+ grid (`algoliasearch@4.21.1` + `instantsearch.js@4.62.0`).
+2. App ID/key are inline in the page (`algolia_app_id='HLJ09W6VF2'`,
+ `algolia_key='5545b74f6bb6e93487ff5482ee7f6182'` — this is a
+ client-side **search-only** key, publicly shipped in PJ's own page source,
+ not a secret we obtained improperly).
+3. Index name resolves from `algolia_env` ('production' on the live page) to
+ `pj_grid` (vs `DEV_pj_grid` in dev) — read straight out of PJ's own
+ minified JS bundle (`algolia-product-grid-*.js`).
+4. Queried `pj_grid` directly (`POST https://HLJ09W6VF2-dsn.algolia.net/1/indexes/pj_grid/query`)
+ — **6,107 live records**, far more than the static `sitemap.xml` (5,937
+ URLs) or the stale `artmura/philipjeffries.json` capture (2,447 products,
+ zero matches against this batch — confirms these are patterns PJ added
+ since that capture).
+5. Matched all 900 flagged Shopify titles ("Pattern - Colorway") against
+ Algolia's `product_line` + `colorway` fields (normalized, case/punct
+ insensitive). **897/900 matched to exactly one record. 3 matched to
+ multiple candidates** (see Ambiguous below) — no misses.
+
+## Format triangulated 3 independent ways
+- Algolia `ITEM` field (numeric, e.g. `34504`, `2943`).
+- `dw_unified.pj_catalog.mfr_sku` (existing compliant rows) — same numeric
+ format (e.g. `10287`), confirmed to be the exact same PJ item as
+ Algolia ITEM 10287 ("Suede Lounge - Taupe Topaz").
+- **Live Shopify metafield on a July-2026 compliant PJ product**
+ ("Marfa Weave, Navy Oasis" — `gid://shopify/Product/7880407416883`):
+ `custom.manufacturer_sku` = `global.manufacturer_sku` = `"2943"`, which is
+ exactly Algolia ITEM 2943 ("Marfa Weave" / "Navy Oasis") — an independent
+ match I did not derive the mapping from. This is the CURRENT pipeline's
+ convention (a July-2025-era product with a *different*, older 2024
+ reference — `DWJP-14510`/GV238 in `global."mfr pattern"` — uses a legacy
+ alphanumeric PJ pattern-catalog code instead; both are genuinely PJ codes
+ from different eras of PJ's own numbering, but the numeric ITEM/
+ `manufacturer_sku` scheme is what the pipeline currently writes).
+
+## Target fields to backfill (per the July-2026 compliant reference product)
+- `custom.manufacturer_sku` = `<real numeric code>`
+- `global.manufacturer_sku` = `<real numeric code>` (mirrored, both namespaces)
+- Canonical `dw_unified` mirror: `shopify_products` (Kamatera-canonical) /
+ `pj_catalog.mfr_sku` equivalent — write order per standing doctrine is
+ PG-stage-first then Shopify, Shopify write is the authoritative one.
+
+## Files here
+- `backfill-mapping-897.json` / `.csv` — sku, handle, shopify product GID,
+ title, `real_mfr_sku`, `product_line`, `colorway` for the 897 clean matches.
+- `ambiguous-3.json` — 3 "Savile Suiting Pinstripe" SKUs (DWJP-15047/48/49)
+ where the title matched multiple live PJ ITEM codes (the II-collection vs
+ original-collection variants share the same displayed name). Needs a
+ manual pick (image/width compare) or a direct ask to PJ rep — NOT
+ auto-resolved. Flagged as PROVENANCE_REVIEW per doctrine.
+- `apply.graphql.mjs` — the script that sets both metafields on the 897 products
+ via Shopify Admin GraphQL (`metafieldsSet`), idempotent PER FIELD: `custom` and
+ `global` are evaluated and written independently, so a row with only one field
+ populated still gets the other backfilled and an already-set field is never
+ overwritten (the "only writes where the field is currently empty" contract; same
+ spirit as the `IS NULL`-style guard in the sibling `phillip-jeffries/` dir).
+ **May have already run — see STATUS UNCERTAIN at the top.**
+
+## To (re-)execute — CONFIRM STATUS WITH STEVE FIRST
+This apply appears to have already executed (see STATUS UNCERTAIN above). Do NOT
+re-run or re-approve until Steve confirms whether the live write already happened.
+If confirmed not-yet-run (or a deliberate idempotent re-apply):
+```
+node apply.graphql.mjs --dry-run # prints what WOULD be written, no writes
+node apply.graphql.mjs --apply # executes, logs to executed-reversible ledger
+```
+Reversible: undo = clear the written metafields back to null. The undo-map is now
+built from the SET-batch SUCCESS results (only rows actually written) and flushed
+after each successful batch, so `undo-map.json` is a reliable rollback source —
+records what was actually applied, not merely what was attempted.
diff --git a/apply-plans/phillip-jeffries-tk11131/apply-3-ambiguous.mjs b/apply-plans/phillip-jeffries-tk11131/apply-3-ambiguous.mjs
new file mode 100644
index 0000000..3c55974
--- /dev/null
+++ b/apply-plans/phillip-jeffries-tk11131/apply-3-ambiguous.mjs
@@ -0,0 +1,94 @@
+// GATED — customer-facing Shopify write. Do NOT run without Steve's explicit go.
+// TK-11131 follow-up: the 3 Savile Suiting Pinstripe SKUs left out of the 897-item run.
+// Idempotent per-FIELD: custom + global are evaluated and written INDEPENDENTLY, so
+// a row with only one field populated still gets the other backfilled, and a field
+// already set is never overwritten. The undo-map is written from the SET-batch
+// SUCCESS result (only rows actually written), so it is a reliable rollback source.
+import fs from 'fs';
+
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const DRY = !process.argv.includes('--apply');
+
+const rows = JSON.parse(fs.readFileSync(new URL('./backfill-mapping-3-ambiguous.json', import.meta.url)));
+
+async function gql(query, variables) {
+ const r = await fetch(`https://${SHOP}/admin/api/2024-10/graphql.json`, {
+ method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ query, variables })
+ });
+ return r.json();
+}
+
+const CHECK_Q = `query($ids: [ID!]!) {
+ nodes(ids: $ids) {
+ ... on Product {
+ id
+ m1: metafield(namespace: "custom", key: "manufacturer_sku") { value }
+ m2: metafield(namespace: "global", key: "manufacturer_sku") { value }
+ }
+ }
+}`;
+const SET_M = `mutation($metafields: [MetafieldsSetInput!]!) {
+ metafieldsSet(metafields: $metafields) {
+ metafields { id namespace key value }
+ userErrors { field message }
+ }
+}`;
+
+const check = await gql(CHECK_Q, { ids: rows.map(r => r.shopify_gid) });
+const existing = new Map(check.data.nodes.filter(Boolean).map(n => [n.id, { m1: n.m1?.value, m2: n.m2?.value }]));
+
+// Decide per-FIELD what to write. Skip a row only when BOTH fields are already set;
+// a row with exactly one field set still backfills the other.
+const toWrite = []; // { row, needCustom, needGlobal }
+for (const r of rows) {
+ const e = existing.get(r.shopify_gid) || { m1: null, m2: null };
+ const needCustom = !e.m1;
+ const needGlobal = !e.m2;
+ if (!needCustom && !needGlobal) {
+ console.log(`SKIP (both already set): ${r.sku} -> custom=${e.m1} global=${e.m2}`);
+ continue;
+ }
+ if (!needCustom || !needGlobal) {
+ const fill = needCustom ? 'custom' : 'global';
+ console.log(`PARTIAL (backfilling ${fill}): ${r.sku}`);
+ }
+ toWrite.push({ row: r, needCustom, needGlobal });
+}
+
+console.log(`${toWrite.length} to write, out of ${rows.length}.`);
+if (DRY) {
+ for (const { row, needCustom, needGlobal } of toWrite) {
+ const fields = [needCustom && 'custom', needGlobal && 'global'].filter(Boolean).join('+');
+ console.log(`DRY: would set ${row.sku} (${row.shopify_gid}) -> ${row.real_mfr_sku} [${fields}]`);
+ }
+ process.exit(0);
+}
+
+const metafields = toWrite.flatMap(({ row, needCustom, needGlobal }) => {
+ const mf = [];
+ if (needCustom) mf.push({ ownerId: row.shopify_gid, namespace: 'custom', key: 'manufacturer_sku', type: 'single_line_text_field', value: row.real_mfr_sku });
+ if (needGlobal) mf.push({ ownerId: row.shopify_gid, namespace: 'global', key: 'manufacturer_sku', type: 'single_line_text_field', value: row.real_mfr_sku });
+ return mf;
+});
+if (metafields.length) {
+ const res = await gql(SET_M, { metafields });
+ if (res.errors) { console.error('ERR', JSON.stringify(res.errors)); process.exit(1); }
+ const errs = res.data?.metafieldsSet?.userErrors || [];
+ if (errs.length) {
+ console.error('ERR', JSON.stringify(errs));
+ } else {
+ // undo-map derived from the SUCCESS result — only rows actually written, only
+ // the fields written (before = null, since we only wrote empty fields).
+ const undoMap = toWrite.map(({ row, needCustom, needGlobal }) => ({
+ gid: row.shopify_gid,
+ sku: row.sku,
+ wrote: { custom: needCustom, global: needGlobal },
+ before: { custom: null, global: null },
+ }));
+ fs.writeFileSync(new URL('./undo-map-3-ambiguous.json', import.meta.url), JSON.stringify(undoMap, null, 1));
+ console.log('SET:', toWrite.map(({ row }) => `${row.sku}->${row.real_mfr_sku}`).join(', '));
+ }
+}
diff --git a/apply-plans/phillip-jeffries-tk11131/apply.graphql.mjs b/apply-plans/phillip-jeffries-tk11131/apply.graphql.mjs
new file mode 100644
index 0000000..37e715e
--- /dev/null
+++ b/apply-plans/phillip-jeffries-tk11131/apply.graphql.mjs
@@ -0,0 +1,143 @@
+// GATED — customer-facing Shopify write. Do NOT run without Steve's explicit go.
+// TK-11131. Backfills custom.manufacturer_sku + global.manufacturer_sku on 897
+// Phillip Jeffries products that went ACTIVE with no real mfr code. Idempotent —
+// evaluates and writes each of the two metafields (custom + global) INDEPENDENTLY,
+// so a row with only ONE field populated still gets the other backfilled, and a
+// field that is already set is never overwritten. Writes an undo-map derived from
+// the SET-batch SUCCESS results (only rows actually written) so it is a reliable
+// rollback source (clear the written metafields back to null).
+// Batched: checks 250 products/call via nodes(ids:), applies up to 24 metafields/call.
+import fs from 'fs';
+
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const DRY = !process.argv.includes('--apply');
+
+const rows = JSON.parse(fs.readFileSync(new URL('./backfill-mapping-897.json', import.meta.url)));
+
+async function gql(query, variables) {
+ const r = await fetch(`https://${SHOP}/admin/api/2024-10/graphql.json`, {
+ method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ query, variables })
+ });
+ return r.json();
+}
+
+function chunk(arr, n) {
+ const out = [];
+ for (let i = 0; i < arr.length; i += n) out.push(arr.slice(i, i + n));
+ return out;
+}
+
+const CHECK_Q = `query($ids: [ID!]!) {
+ nodes(ids: $ids) {
+ ... on Product {
+ id
+ m1: metafield(namespace: "custom", key: "manufacturer_sku") { value }
+ m2: metafield(namespace: "global", key: "manufacturer_sku") { value }
+ }
+ }
+}`;
+
+const SET_M = `mutation($metafields: [MetafieldsSetInput!]!) {
+ metafieldsSet(metafields: $metafields) {
+ metafields { id namespace key value }
+ userErrors { field message }
+ }
+}`;
+
+// 1. Batch-check existing state, 250 ids/call
+const existing = new Map(); // gid -> {m1,m2}
+for (const batch of chunk(rows.map(r => r.shopify_gid), 250)) {
+ const res = await gql(CHECK_Q, { ids: batch });
+ if (res.errors) { console.error('CHECK ERR', JSON.stringify(res.errors)); process.exit(1); }
+ for (const n of res.data.nodes) {
+ if (n) existing.set(n.id, { m1: n.m1?.value, m2: n.m2?.value });
+ }
+ process.stderr.write(`checked ${existing.size}/${rows.length}\n`);
+}
+
+// 2. Decide per-FIELD (custom + global evaluated independently) what to write.
+// A field is written only when currently absent/empty; a row is skipped entirely
+// only when BOTH fields are already set. A partial row (one field set) still gets
+// the other field backfilled — matching the README's "only writes where the field
+// is currently empty" contract.
+const toWrite = []; // { row, needCustom, needGlobal }
+let skipped = 0; // rows where both fields already set
+let partialBackfills = 0; // rows where exactly one field was already set
+for (const row of rows) {
+ const e = existing.get(row.shopify_gid) || { m1: null, m2: null };
+ const needCustom = !e.m1;
+ const needGlobal = !e.m2;
+ if (!needCustom && !needGlobal) {
+ skipped++;
+ console.log(`SKIP (both already set): ${row.sku} -> custom=${e.m1} global=${e.m2}`);
+ continue;
+ }
+ if (!needCustom || !needGlobal) {
+ partialBackfills++;
+ const have = needCustom ? `global=${e.m2}` : `custom=${e.m1}`;
+ const fill = needCustom ? 'custom' : 'global';
+ console.log(`PARTIAL (backfilling ${fill}, already had ${have}): ${row.sku}`);
+ }
+ toWrite.push({ row, needCustom, needGlobal });
+}
+
+console.log(`\n${toWrite.length} rows to write (${partialBackfills} partial backfills), ${skipped} already-set (skipped), out of ${rows.length} total.`);
+
+if (DRY) {
+ console.log('DRY-RUN — no writes. Sample of first 10 to be written:');
+ for (const { row, needCustom, needGlobal } of toWrite.slice(0, 10)) {
+ const fields = [needCustom && 'custom', needGlobal && 'global'].filter(Boolean).join('+');
+ console.log(` ${row.sku} (${row.shopify_gid}) -> ${row.real_mfr_sku} [${fields}]`);
+ }
+ process.exit(0);
+}
+
+// 3. Apply in batches of 12 products (<=24 metafields/call), writing only the
+// needed fields per row. The undo-map is built from the SET-batch SUCCESS
+// results — only rows actually written land in it — and flushed after each
+// successful batch so a mid-run crash still leaves a reliable rollback source.
+const undoPath = new URL('./undo-map.json', import.meta.url);
+const undoMap = []; // populated from confirmed writes only
+
+let applied = 0, errored = 0;
+const productBatches = chunk(toWrite, 12);
+for (const batch of productBatches) {
+ const metafields = batch.flatMap(({ row, needCustom, needGlobal }) => {
+ const mf = [];
+ if (needCustom) mf.push({ ownerId: row.shopify_gid, namespace: 'custom', key: 'manufacturer_sku', type: 'single_line_text_field', value: row.real_mfr_sku });
+ if (needGlobal) mf.push({ ownerId: row.shopify_gid, namespace: 'global', key: 'manufacturer_sku', type: 'single_line_text_field', value: row.real_mfr_sku });
+ return mf;
+ });
+ if (!metafields.length) continue;
+ const res = await gql(SET_M, { metafields });
+ if (res.errors) {
+ errored += batch.length;
+ console.error('BATCH ERR', JSON.stringify(res.errors));
+ continue;
+ }
+ const errs = res.data?.metafieldsSet?.userErrors || [];
+ if (errs.length) {
+ errored += batch.length;
+ console.error(`ERR batch [${batch.map(({ row }) => row.sku).join(',')}]:`, JSON.stringify(errs));
+ continue;
+ }
+ applied += batch.length;
+ // Record undo entries ONLY for rows in this confirmed-successful batch, and
+ // only for the fields actually written (before = null, since we only wrote
+ // fields that were empty). Flush immediately so the undo-map stays reliable.
+ for (const { row, needCustom, needGlobal } of batch) {
+ undoMap.push({
+ gid: row.shopify_gid,
+ sku: row.sku,
+ wrote: { custom: needCustom, global: needGlobal },
+ before: { custom: null, global: null },
+ });
+ }
+ fs.writeFileSync(undoPath, JSON.stringify(undoMap, null, 1));
+ console.log(`SET batch: ${batch.map(({ row }) => row.sku + '->' + row.real_mfr_sku).join(', ')}`);
+}
+
+console.log(`\nAPPLIED: ${applied} rows written (undo-map: ${undoMap.length} entries), ${errored} errored, ${skipped} already-set (skipped), out of ${rows.length} total.`);
← 08493da auto-data-snapshot: 2026-09-02T12:46:36 (5 data files) — app
·
back to Dw Sku Integrity
·
auto-data-snapshot: 2026-09-15T18:34:22 (6 data files) — app 0c2d8d5 →