← back to Wallco Ai

docs/spoon-convergence-migration-plan.md

298 lines

# Migration Plan — Converge prod `spoon_all_designs` to a view over `all_designs`

**Status:** ✅ **CONVERGENCE EXECUTED 2026-06-01** (Option B per /dtd). Prod `spoon_all_designs` is now a VIEW over `all_designs` — one id-space. Remaining = follow-ups §10 only.
**Author:** drafted 2026-06-01 (root-cause dig into catalog id churn).

---

## EXECUTION LOG — 2026-06-01 evening (Option B, /dtd 2-0 valid votes, Qwen errored)

Reconciliation policy decided by /dtd = **Option B (view-only)** — Claude + Codex both B; the served catalog is Mac2-sourced so prod spoon's orphans/conflicts carry only secondary fields the PDP tolerates as null, and the renamed legacy table makes the deferral reversible.

Executed against prod `dw_unified`:
1. `pg_dump -t all_designs -t spoon_all_designs` → `/root/dw_designs_pre_converge_1780360566.dump` (2.3 MB rollback artifact).
2. `ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS` user_prompt_match/verdict/rated_at (user_stars already present). Column delta spoon−all_designs now **empty**; **0 dependent objects** on spoon.
3. **Cutover tx:** `ALTER TABLE spoon_all_designs RENAME TO spoon_all_designs_legacy_20260601` + `CREATE VIEW spoon_all_designs AS SELECT * FROM all_designs`.
4. Verified: spoon `relkind=v`, view count = all_designs (10,587); updatable-view write-through works (`UPDATE spoon… → all_designs`); dig trigger fires on insert via view (new id **56003**, `DIG_AI_056003` — confirms the sequence bump too); live health 200, grid **4,021** unchanged (public catalog is Mac2-sourced, unaffected); palette serves for all_designs rows (id 53713), and the null-palette ids were null before too (no regression).
5. Cleanup: marked `migrations/20260601_prompt_match_columns_PROD.sql` OBSOLETE (its `ALTER TABLE spoon` would now error). Confirmed server.js does NOT auto-run sql files on boot → no restart landmine.

**Rollback (if ever needed):** `DROP VIEW spoon_all_designs; ALTER TABLE spoon_all_designs_legacy_20260601 RENAME TO spoon_all_designs;` — or `pg_restore` the dump. Legacy table retained (1,579 rows).

### §10 FOLLOW-UPS — all ✅ DONE 2026-06-01 evening
1. **Two-master generation** → /dtd **Option B (disjoint ranges)**, 2-0 valid. Prod sequence `setval`→**10,000,000** (prod mints 10M+, Mac2 stays <10M; `bigint` id; invariant recorded as `COMMENT ON SEQUENCE spoon_all_designs_id_seq`). Collision-free regardless of sync direction. **Surfaced + fixed a latent bug:** the dig-number trigger used `lpad(id,6,'0')` which *truncates* ids >6 digits (10M+ → `DIG_AI_100000`, colliding); fixed to `lpad(id, GREATEST(6,length(id::text)), '0')` on prod + in `sql/200_dig_auto_assign.sql` (now matches the JS `padStart`). Verified: 10000002→`DIG_AI_10000002`, 11→`DIG_AI_000011`.
2. **Settlement DB trigger** → installed on prod `all_designs` (function was absent entirely). Verified: blocks a Part-A+B publish (`RAISE EXCEPTION`), passes a clean publish. Canonical `sql/settlement_publish_trigger.sql` repointed spoon→all_designs.
3. **`MAX(id)+1` minting** → `scripts/heal-seam-region.py` now mints via the sequence (`RETURNING id`) then sets `image_url` from the real id. Swept the repo: no other true minting-via-MAX+1 sites (the reroll/tick `MAX(id)` reads are detection-only and still correct post-convergence). `heal-region-inpaint.py` was already clean.
**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.

**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.
```