docs: Phase 0 results (Case B) + interim fixes applied (sequence bump 56000, designs.json rebuild/ship, render-time naming + 404 guard)

a88d48682bad000d8fac6073508b6003397303d8 · 2026-06-01 17:31:58 -0700 · Steve Abrams
Files touched

M docs/spoon-convergence-migration-plan.md
Diff

commit a88d48682bad000d8fac6073508b6003397303d8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jun 1 17:31:58 2026 -0700

    docs: Phase 0 results (Case B) + interim fixes applied (sequence bump 56000, designs.json rebuild/ship, render-time naming + 404 guard)
---
 docs/spoon-convergence-migration-plan.md | 35 +++++++++++++++++++++++++++++++-
 1 file changed, 34 insertions(+), 1 deletion(-)

diff --git a/docs/spoon-convergence-migration-plan.md b/docs/spoon-convergence-migration-plan.md
index 8e1ef9c..eba3c82 100644
--- a/docs/spoon-convergence-migration-plan.md
+++ b/docs/spoon-convergence-migration-plan.md
@@ -1,11 +1,44 @@
 # Migration Plan — Converge prod `spoon_all_designs` to a view over `all_designs`
 
-**Status:** PLAN ONLY — not executed. Requires Steve sign-off + a maintenance window.
+**Status:** Phase 0 MEASURED + interim fixes APPLIED (2026-06-01). Full convergence (§6–§7) still PENDING Steve sign-off + a maintenance window.
 **Author:** drafted 2026-06-01 (root-cause dig into catalog id churn).
 **Risk class:** prod schema migration on `dw_unified` (live catalog). Data-reconciliation + settlement-trigger + live `designs.json` sync all in scope.
 
 ---
 
+## Phase 0 RESULTS (measured on prod 2026-06-01) — this is **Case B**
+
+| Measure | Value |
+|---|---|
+| topology | `all_designs` = table (r), `spoon_all_designs` = **table (r)** — two id-spaces confirmed |
+| `all_designs` rows | 10,587 (id 11–53,713) |
+| `spoon_all_designs` rows | 1,577 (id 11–50,586) |
+| spoon orphans (not in all_designs) | **25** |
+| all_designs not in spoon | 9,035 |
+| **same id, different art** | **59** ← churn signature |
+| columns to add to `all_designs` | `user_stars int`, `user_prompt_match smallint`, `user_prompt_verdict text`, `user_prompt_rated_at timestamptz` |
+| triggers | `trg_spoon_all_designs_assign_dig` on BOTH tables; **`settlement_publish_check` NOT present on either** (prod relies on the JS gate only) |
+| FK to re-point | `spoon_all_designs_parent_design_id_fkey` (self-ref) |
+| **shared sequence** | both tables DEFAULT `nextval('spoon_all_designs_id_seq')`; `all_designs` has NO own sequence |
+
+### The churn engine (root cause, confirmed)
+Both base tables draw from ONE sequence, `spoon_all_designs_id_seq`, which was at **10,256** while `all_designs.max(id)=53,713` and `spoon.max(id)=50,586` — **~43,000 behind**. Every new design (`nextval`) drew an id in the already-used 10k–53k range; because the two are *separate tables* sharing the counter, the same id landed on different art in each → the 59 same-id-diff-art rows and the `/design/10171` Crimson↔Capybaras flip.
+
+### Authoritative-source clarification
+The served catalog is built **on Mac2** from Mac2's PG (45,155 designs; `spoon` there is a 1:1 VIEW) and shipped to prod as `designs.json` (~35.4k rows = the full working set; the server filters `is_published` to ~4k publicly). PROD's PG is the *sparse* side — the snapshot builder's CATASTROPHE GUARD (`scripts/refresh_designs_snapshot.py`) exists precisely to stop a prod-side regen from collapsing the catalog. **Never rebuild `designs.json` on prod.**
+
+## Interim fixes APPLIED 2026-06-01 (do NOT re-do)
+1. **Sequence advanced** `spoon_all_designs_id_seq` → **56,000** (`setval`), above both prod max (53,713) and Mac2 max (55,427). Stops new prod inserts colliding with existing ids AND clears the Mac2-overlap window. (First set to 53,713, then bumped to 56,000.)
+2. **`designs.json` rebuilt on Mac2** (35,423 rows, guards passed) and shipped to prod; reloaded `wallco-ai`. Diff was +4 / 0 changed / 0 removed — catalog was already consistent. Live grid serves 4,021 published.
+3. **Render-time naming + 404 guard** shipped earlier (`lib/hex-colorname.js`, `server.js`) — names derive from `dominant_hex`; missing/`user_removed` ids return clean 404 instead of a broken PDP.
+
+### Residual after interim fixes (why full convergence is still needed)
+- The **two id-spaces still exist** on prod — interim #1 only stops *new* collisions; the 59 existing same-id-diff-art rows + 25 orphans remain until §6 reconciliation.
+- **Two-master generation persists** — Mac2 and prod both mint into the same logical id space; the sequence bump buys a buffer (~573 ids until Mac2 catches 56,000) but is not a cure. Durable fix = §10.2 (single minting master) or disjoint per-machine id ranges.
+- Recommend **disjoint ranges** as the simplest permanent guard if a single-master move is too invasive: e.g. prod mints from 1,000,000+, Mac2 stays < 1,000,000 — they can then never collide regardless of sync direction.
+
+---
+
 ## 1. Objective & invariant
 
 Make prod's `spoon_all_designs` a **simple, updatable VIEW over `all_designs`** — exactly as it already is on the Mac2 dev DB — so there is **one** id-space for designs instead of two.
← 7fa37ed gitignore: stop tracking large regeneratable data blobs (ets · back to Wallco Ai · snapshot WIP + untrack regeneratable image dirs (data/genera 7879c7b →