← back to Wallco Ai

docs: convergence migration plan — make prod spoon_all_designs a view over all_designs (kill catalog id churn)

031802d2372fabe98e3990146835eb0a066fe4e4 · 2026-06-01 17:17:29 -0700 · Steve Abrams

Files touched

• A docs/spoon-convergence-migration-plan.md

Diff

commit 031802d2372fabe98e3990146835eb0a066fe4e4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jun 1 17:17:29 2026 -0700

    docs: convergence migration plan — make prod spoon_all_designs a view over all_designs (kill catalog id churn)
---
 docs/spoon-convergence-migration-plan.md | 244 +++++++++++++++++++++++++++++++
 1 file changed, 244 insertions(+)

diff --git a/docs/spoon-convergence-migration-plan.md b/docs/spoon-convergence-migration-plan.md
new file mode 100644
index 0000000..8e1ef9c
--- /dev/null
+++ b/docs/spoon-convergence-migration-plan.md
@@ -0,0 +1,244 @@
+# 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.
+**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.
+
+---
+
+## 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.
+
+**Invariant we are buying:** a design's row and its catalog row can never disagree, because they are the same row. `id` becomes a single source of truth. The "same id → different art" churn (`/design/10171` flipping from "Crimson Studio" to a removed "Capybaras") becomes **structurally impossible**, not just mitigated.
+
+---
+
+## 2. Why this is the fix (root cause recap)
+
+The catalog (snapshot builder `scripts/refresh_designs_snapshot.py:235` → `FROM spoon_all_designs`, and the live PDP read `server.js` `/api/design/:id` → `FROM spoon_all_designs`) reads from **`spoon_all_designs`**. Generators/recolors/heals INSERT into **`all_designs`** (e.g. recolor `server.js` `RETURNING id`, `scripts/inspiration_generator.js:297`).
+
+Topology differs by machine:
+
+| | `spoon_all_designs` | id-spaces |
+|---|---|---|
+| Mac2 (dev) | **VIEW** `SELECT … FROM all_designs` (`migrations/20260601_prompt_match_columns.sql:36`) | one — cannot diverge |
+| **Prod** | **own base table** (`relkind=r`) — confirmed `migrations/20260601_prompt_match_columns_PROD.sql:7-12` | **two — must be synced** |
+
+On prod the two base tables drift; a rebuild/sync of the spoon table reassigns what each id points to. Compounding factors (do not block this fix, but note them):
+- **Two-master generation** — designs minted on both Mac2 *and* prod into the same id range with independent sequences → the 134 cross-DB collisions `scripts/resolve-id-collisions-20260529.py` cleaned up.
+- **Fragile id-minting** — `COALESCE(MAX(id),0)+1` in `scripts/heal-seam-region.py:146`, `scripts/reroll-tileable.js` (non-atomic); sequence falls behind `max(id)` so daemons `setval` to self-heal (`scripts/drunk_animals_daemon.js:22-37`).
+
+Converging the topology removes the **primary** driver. The two-master + minting issues are addressed as follow-ups (§10).
+
+---
+
+## 3. Target state
+
+```sql
+-- prod, after migration:
+--   all_designs            = the single base table (sequence-backed id PK)
+--   spoon_all_designs      = CREATE VIEW spoon_all_designs AS SELECT <cols> FROM all_designs;
+--   settlement_publish_check        -> BEFORE UPDATE ON all_designs
+--   trg_*_assign_dig                -> BEFORE INSERT ON all_designs
+```
+
+A simple single-table view (no joins/aggregates/DISTINCT) is **automatically updatable** in Postgres — the app's 171 `spoon_all_designs` write/read sites (`grep -c spoon_all_designs server.js` = 171) keep working unchanged, because INSERT/UPDATE/DELETE pass through to `all_designs`. This is already proven on Mac2 (`migrations/20260601_prompt_match_columns.sql:14`: "UPDATE spoon_all_designs SET user_prompt_* writes through to all_designs").
+
+---
+
+## 4. Phase 0 — Measurement (READ-ONLY, run first, decides the reconciliation branch)
+
+These need `sudo -u postgres psql dw_unified` on prod (gated — Steve runs, or approves). **Nothing is changed in Phase 0.** Capture all outputs into the migration ticket.
+
+```sql
+-- 0.1 confirm prod topology (expect spoon = 'r' table, all_designs = 'r' table)
+SELECT relname, relkind FROM pg_class
+ WHERE relname IN ('all_designs','spoon_all_designs');
+
+-- 0.2 row counts + id ranges in each
+SELECT 'all_designs'  t, count(*), min(id), max(id) FROM all_designs
+UNION ALL
+SELECT 'spoon_all_designs', count(*), min(id), max(id) FROM spoon_all_designs;
+
+-- 0.3 THE KEY QUESTION — do the two tables agree row-for-row on id?
+--     rows in spoon NOT in all_designs (orphans the view would drop):
+SELECT count(*) FROM spoon_all_designs s
+  LEFT JOIN all_designs a USING (id) WHERE a.id IS NULL;
+--     rows in all_designs NOT in spoon (designs the catalog can't currently see):
+SELECT count(*) FROM all_designs a
+  LEFT JOIN spoon_all_designs s USING (id) WHERE s.id IS NULL;
+--     same id, DIFFERENT art (the churn signature) — compare a strong key:
+SELECT count(*) FROM spoon_all_designs s JOIN all_designs a USING (id)
+ WHERE COALESCE(s.local_path,'') <> COALESCE(a.local_path,'')
+    OR COALESCE(s.dominant_hex,'') <> COALESCE(a.dominant_hex,'')
+    OR COALESCE(s.prompt,'')       <> COALESCE(a.prompt,'');
+
+-- 0.4 column delta — every column spoon has that all_designs lacks MUST be added
+SELECT column_name, data_type FROM information_schema.columns
+ WHERE table_name='spoon_all_designs'
+EXCEPT
+SELECT column_name, data_type FROM information_schema.columns
+ WHERE table_name='all_designs';
+
+-- 0.5 trigger + sequence inventory
+SELECT tgname, tgrelid::regclass FROM pg_trigger
+ WHERE tgrelid IN ('all_designs'::regclass,'spoon_all_designs'::regclass)
+   AND NOT tgisinternal;
+SELECT 'seq' k, last_value FROM spoon_all_designs_id_seq;       -- the all_designs seq
+SELECT pg_get_serial_sequence('all_designs','id');
+SELECT max(id) FROM all_designs;   -- compare to last_value (must be <=)
+
+-- 0.6 any FKs pointing at spoon_all_designs (must be re-pointed to all_designs)
+SELECT conrelid::regclass AS child, conname, confrelid::regclass AS parent
+  FROM pg_constraint
+ WHERE confrelid = 'spoon_all_designs'::regclass;
+```
+
+### Branch on 0.3 results
+- **Case A — spoon ⊆ all_designs, no diffs (ids align, art matches):** trivial. spoon is just a stale duplicate; drop + recreate as view. Lowest risk.
+- **Case B — spoon has rows missing from all_designs (orphans) and/or same-id-different-art:** the dangerous case. Those spoon rows are catalog-visible designs that don't exist (or mean something else) in all_designs. Reconciliation (§6) is mandatory and id-preservation is impossible for the conflicting subset — each conflict needs a NEW id in all_designs + a redirect, or a documented drop.
+- **Case C — all_designs has rows missing from spoon:** designs the catalog currently can't show; after convergence they all become visible. Verify none are intentionally hidden (`user_removed`/`is_published=false` will still be respected by the catalog's existing filters).
+
+---
+
+## 5. Pre-flight (always, before any write)
+
+1. **Full logical dump of both tables** (rollback artifact), to `/root/` (not web-served):
+   ```bash
+   sudo -u postgres pg_dump dw_unified -t all_designs -t spoon_all_designs \
+     -Fc -f /root/dw_unified_designs_pre_converge_$(date +%s).dump
+   ```
+2. **Snapshot the live `data/designs.json`** to `/root/` (already prod-authored, rsync-excluded).
+3. **Quiesce writers** for the window: stop generation daemons that INSERT into all_designs (`drunk_animals_daemon`, `stoned_animals_daemon`, `inspiration_generator`, generator_tick crons) and the room-backfill cron (`30 6 * * *`). Leave `wallco-ai` up until the cutover step.
+4. **Announce a short read-mostly window** — recolor/publish/admin writes pause during cutover (~minutes).
+
+---
+
+## 6. Reconciliation (only if Phase 0 = Case B/C)
+
+Goal: make `all_designs` a **superset** that already contains every id the catalog currently serves, with matching art, BEFORE the view replaces the table.
+
+1. **Add missing columns to `all_designs`** (from 0.4) — additive, idempotent:
+   ```sql
+   ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS dig_number text;
+   ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS user_prompt_match smallint;
+   ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS user_prompt_verdict text;
+   ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS user_prompt_rated_at timestamptz;
+   -- …plus every other column 0.4 reports (user_stars, user_color_good,
+   --   user_style_good, user_scale_good, user_rated_at, user_removed, …)
+   ```
+2. **Import spoon-only orphan rows** (0.3 first count) into all_designs:
+   - **If the id is free in all_designs** → straight `INSERT … SELECT` preserving id.
+   - **If the id is taken by different art** (the conflict subset) → insert with a **fresh** `nextval` id, and record `old_spoon_id → new_id` in an audit table so a 301 redirect map can be built for `/design/<old>`.
+   ```sql
+   -- free-id case (preserves id):
+   INSERT INTO all_designs (id, kind, category, dominant_hex, prompt, local_path, …)
+   SELECT s.id, s.kind, s.category, s.dominant_hex, s.prompt, s.local_path, …
+     FROM spoon_all_designs s
+     LEFT JOIN all_designs a USING (id) WHERE a.id IS NULL
+       AND NOT EXISTS (SELECT 1 FROM all_designs b WHERE b.id = s.id);
+   -- conflict case handled by a small per-row script (audited), NOT bulk.
+   ```
+3. **Carry user-rating + dig_number values** from spoon onto the matching all_designs rows (so curation/ratings survive):
+   ```sql
+   UPDATE all_designs a SET
+     user_stars = COALESCE(a.user_stars, s.user_stars),
+     dig_number = COALESCE(a.dig_number, s.dig_number),
+     user_prompt_match = COALESCE(a.user_prompt_match, s.user_prompt_match),
+     …
+   FROM spoon_all_designs s WHERE s.id = a.id;
+   ```
+4. **Advance the sequence** past the new max so future `nextval` never collides:
+   ```sql
+   SELECT setval('spoon_all_designs_id_seq', (SELECT max(id) FROM all_designs));
+   ```
+5. **Re-verify 0.3** — expect 0 orphans, 0 same-id-diff-art before proceeding.
+
+---
+
+## 7. Cutover (transactional)
+
+Do triggers + view swap in ONE transaction so there is never a window with no settlement gate.
+
+```sql
+BEGIN;
+
+-- 7.1 move triggers to all_designs (functions already exist; just re-point)
+DROP TRIGGER IF EXISTS settlement_publish_check        ON spoon_all_designs;
+DROP TRIGGER IF EXISTS trg_spoon_all_designs_assign_dig ON spoon_all_designs;
+
+CREATE TRIGGER settlement_publish_check
+  BEFORE UPDATE ON all_designs
+  FOR EACH ROW EXECUTE FUNCTION settlement_publish_check();
+CREATE TRIGGER trg_all_designs_assign_dig
+  BEFORE INSERT ON all_designs
+  FOR EACH ROW EXECUTE FUNCTION fn_spoon_all_designs_assign_dig();
+
+-- 7.2 retire the base table, recreate as a view
+ALTER TABLE spoon_all_designs RENAME TO spoon_all_designs_legacy_$(date);  -- keep, don't drop
+CREATE VIEW spoon_all_designs AS
+  SELECT * FROM all_designs;           -- mirror the Mac2 column list exactly
+                                       -- (use the explicit column list from
+                                       --  migrations/20260601_prompt_match_columns.sql:36
+                                       --  if `SELECT *` column order matters to any caller)
+
+-- 7.3 re-point any FKs found in 0.6 from the legacy table to all_designs
+
+COMMIT;
+```
+
+Notes:
+- **Keep the legacy table** (renamed) for at least one cycle — do **not** `DROP` it in the migration. It is the fastest rollback.
+- The settlement gate's `SET LOCAL settlement.allow_override='true'` override path is preserved unchanged (it's in the function body, table-agnostic).
+- If any caller does `INSERT INTO spoon_all_designs (…) RETURNING id`, confirm the updatable view returns the all_designs sequence id (it does for simple views).
+
+---
+
+## 8. App / artifact changes (deploy with the migration)
+
+- The 171 `spoon_all_designs` references keep working via the updatable view — **no bulk code change required**. (Contrast the half-baked `tomorrow-25min-fix.sh`, which hand-edits 2 endpoints from `spoon_all_designs`→`all_designs` via `vi` on prod; with the view, that becomes unnecessary, but leaving those two on `all_designs` is also harmless.)
+- `scripts/refresh_designs_snapshot.py` keeps reading `spoon_all_designs` (now the view) → snapshot unchanged in shape.
+- Ship the already-committed render-time naming + 404 guard (`lib/hex-colorname.js`, `server.js`) — they remain valid and complementary.
+- After cutover, **regenerate `data/designs.json` once** from the converged view and reload `wallco-ai` so the in-memory `DESIGNS` matches the new single id-space.
+
+---
+
+## 9. Verification (post-cutover, before re-enabling writers)
+
+1. `node -c server.js` (already deployed) and `wallco-ai` boots clean (watch ~20s boot; nginx 502 during boot is expected, then 200).
+2. `https://wallco.ai/health` → 200.
+3. **Settlement gate still bites:** attempt an audited `UPDATE all_designs SET is_published=TRUE` on a known Part-A+B prompt row in a throwaway tx (`ROLLBACK`) → must `RAISE EXCEPTION`.
+4. **Updatable-view write-through:** `UPDATE spoon_all_designs SET user_stars=5 WHERE id=<x>` then `SELECT user_stars FROM all_designs WHERE id=<x>` = 5.
+5. **dig auto-assign:** insert a test row into all_designs (rollback) → `dig_number` auto-populates.
+6. **No id churn:** the id a recolor returns (`RETURNING id`) is immediately visible at `/api/design/<that id>` with matching art.
+7. Spot-check 10 previously-divergent ids resolve to one consistent design across grid + PDP.
+8. Confirm sequence ≥ max(id): `SELECT last_value >= (SELECT max(id) FROM all_designs) FROM spoon_all_designs_id_seq;`
+
+---
+
+## 10. Follow-ups (separate tickets — do NOT bundle into cutover)
+
+1. **Kill `MAX(id)+1` minting** — `scripts/heal-seam-region.py`, `scripts/reroll-tileable.js`, `scripts/heal-region-inpaint.py`: switch to sequence-backed inserts (omit `id`, use `RETURNING id`; build `image_url`/`by-id` path FROM the returned id, not a pre-computed `MAX+1`).
+2. **End two-master generation** — decide ONE authoritative DB for new-design minting (prod, given the catalog lives there) and make Mac2 a read replica / one-way pull, OR namespace id ranges per machine so they cannot collide. This is the durable cure for the cross-DB collisions.
+3. **Make the sequence self-correct** — `ALTER SEQUENCE … OWNED BY all_designs.id` + a periodic guard, retiring the daemon `setval` band-aids.
+4. **Apply the same converge to any other env** (staging) so topology never drifts again; add a CI assertion that `spoon_all_designs.relkind = 'v'` on every deploy target.
+
+---
+
+## 11. Rollback
+
+- **Within the cutover tx:** any failure → `ROLLBACK`; nothing changed.
+- **After commit, pre-verify failure:** drop the view, `ALTER TABLE spoon_all_designs_legacy_<ts> RENAME TO spoon_all_designs`, re-point triggers back to it, reload. < 2 min.
+- **Catastrophic:** `pg_restore` the §5.1 dump of both tables.
+- The legacy renamed table is retained ≥ 1 cycle as the warm rollback.
+
+---
+
+## 12. Open questions for Steve (answer before scheduling)
+
+1. **Maintenance window** — OK to pause generation daemons + admin writes for ~15–30 min? (Catalog stays readable throughout; only writes pause during §7.)
+2. **Conflict policy (if Phase 0 = Case B)** — for spoon ids whose art differs from all_designs: **re-id + 301 redirect** the catalog version (preserves URLs), or **drop** the stale spoon version? (Recommend re-id + redirect.)
+3. **Two-master decision (follow-up §10.2)** — is prod the single minting master going forward, with Mac2 demoted to pull-only? Needed to stop the collisions recurring.
+4. **Run Phase 0 now?** It's read-only (gated sudo `SELECT`s). Approving just Phase 0 lets us measure the true blast radius (Case A vs B) before committing to a window.
+```

← 8a16967 murals: add sort dropdown + density slider (Steve standing r  ·  back to Wallco Ai  ·  me/saved: add sort dropdown (default Recently saved) + densi d919742 →