# Phase 1.8 — Workstream 3: Gated Data Migrations (007–009)

**Status:** ✅ COMPLETE — migrations `007`, `008`, `009` applied and validated  
**Date:** 2026-07-12  
**Branch:** `phase-1.7-reconciliation`  
**Scope:** Apply gated data/structural/integrity migrations only. **Migration `010` NOT executed** (per instruction).

---

## 1. Executive Summary

Workstream 3 applied the three approval-gated forward migrations to the live Supabase project, one at a time, with validation after each. All three succeeded after a single prerequisite hotfix (see §4.1).

| Migration | Result | Time |
|---|---|---|
| `007_data_normalization` | ✅ Applied (after hotfix) | 6.2 s |
| `008_structural_reconciliation` | ✅ Applied | 13.0 s |
| `009_product_integrity` | ✅ Applied | 10.5 s |

**Migration history is now `001`–`009` applied, `010` pending.**

Key outcomes:
- Role vocabulary normalized: `user` → `customer`; `profiles_role_check` enforced (validated).
- Orphan `products.category_id` cleared (garbage UUID from old default).
- Products PK changed from composite `(id, name)` to `(id)`.
- `"Description"` renamed to `description` (data preserved).
- Unused `order_status` enum dropped.
- Product FKs, category FK, and cart/wishlist UNIQUE constraints added.

**Migration `010` is now unblocked** — K-1 (FTS index dependency on `products.description`) is resolved by `008`.

Repository, frontend, and backend code are **unchanged**. Application gates pass (0 lint errors, 18/18 tests, build succeeded).

---

## 2. Backup Information

| Property | Value |
|---|---|
| **Backup identifier** | **`pre_ws3_20260712_195038`** |
| Location | `/tmp/babees_backups/` |
| Schema dump | `pre_ws3_20260712_195038_schema.sql` (15,124 B) |
| Data dump | `pre_ws3_20260712_195038_data.sql` (2,371 B) |
| Roles dump | `pre_ws3_20260712_195038_roles.sql` (358 B) |
| Prior backup (WS2B) | `pre_ws2b_20260712_183453` (superseded for rollback purposes) |

> Note: `db dump` intermittently failed on first attempt (pooler auth); all three dumps succeeded on immediate retry. Logical dumps are sufficient at current scale (max 4 rows per table).

---

## 3. Pre-Flight Audit (Step 1)

### 3.1 Migration History (pre-execution)

| Version | Local | Remote |
|---|---|---|
| 001–006 | applied | applied |
| 007–010 | pending | — |

### 3.2 Role Values (pre-007)

| role | count |
|---|---|
| admin | 2 |
| user | 1 |

### 3.3 Orphan Check (NOT VALID user FKs from 005)

| Table | Orphan rows |
|---|---|
| orders | 0 |
| cart_items | 0 |
| wishlists | 0 |

### 3.4 Row Counts (pre-execution)

| Table | Rows |
|---|---|
| profiles | 3 |
| products | 1 |
| categories | 0 |
| cart_items | 0 |
| wishlists | 0 |
| orders | 0 |
| order_items | 0 |

### 3.5 Migration Review (pre-execution — DO NOT EXECUTE yet)

#### 007 — `data_normalization.sql`

| Aspect | Detail |
|---|---|
| **Purpose** | Normalize live data so canonical constraints can be enforced |
| **Affected tables** | `profiles`, `products`, `cart_items`, `wishlists`, `orders` |
| **Expected row changes** | Backfill `profiles.full_name`; normalize `role` (`user`→`customer`); null orphan `category_id`; dedupe cart/wishlist (none to dedupe); add CHECK constraints |
| **Risks** | Medium–High — data mutation, not in-place reversible |
| **Prerequisites** | `002` (full_name column), `005` (defaults dropped); **discovered:** `products.updated_at` required by `003` trigger |

#### 008 — `structural_reconciliation.sql`

| Aspect | Detail |
|---|---|
| **Purpose** | Change products PK to `(id)`; rename `"Description"` → `description`; drop unused enum |
| **Affected tables** | `products`; type `order_status` |
| **Expected row changes** | None (schema only) |
| **Risks** | High — brief lock on products; aborts if duplicate `id` values |
| **Prerequisites** | `007`; verified `id` unique (1 row, no duplicates); no FK references products yet |

#### 009 — `product_integrity.sql`

| Aspect | Detail |
|---|---|
| **Purpose** | Add product FKs, category FK, cart/wishlist UNIQUE constraints |
| **Affected tables** | `cart_items`, `wishlists`, `order_items`, `products` |
| **Expected row changes** | None (constraints only) |
| **Risks** | Medium–High — UNIQUE fails if duplicates remain; product FKs added NOT VALID |
| **Prerequisites** | `007` (dedupe + category_id repair), `008` (single-column PK) |

---

## 4. Migration Execution & Validation

### 4.1 Migration 007 — Data Normalization

**First attempt:** ❌ FAILED  
```
ERROR: record "new" has no field "updated_at"
CONTEXT: PL/pgSQL function set_updated_at() ... trigger set_products_updated_at on products
```

**Root cause (chain defect):** Migration `003` created `set_products_updated_at` trigger on `products`, but `002` never added `products.updated_at`. Any UPDATE to `products` fails until the column exists.

**Prerequisite hotfix (ad-hoc, not a migration file edit):**
```sql
ALTER TABLE public.products ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();
```

**Second attempt:** ✅ SUCCESS — 6,164 ms  
**Recorded:** `supabase migration repair --status applied 007`

#### Post-007 Validation

| Check | Result |
|---|---|
| Migration succeeded | ✅ |
| Role counts | admin: 2, customer: 1 (was user: 1) |
| Unexpected role values | ✅ None — only `admin` and `customer` |
| Admins preserved | ✅ Both admin accounts unchanged |
| Customers preserved | ✅ `user1@gmail.com` now `customer` |
| `profiles_role_check` | ✅ validated |
| `orders_payment_status_check` | ✅ validated (0 orders) |
| `orders_status_check` | ✅ added NOT VALID (by design; 0 orders) |
| Product `category_id` | ✅ nulled (was orphan UUID) |
| Row counts | profiles: 3, products: 1 (unchanged) |

### 4.2 Migration 008 — Structural Reconciliation

**Execution:** ✅ SUCCESS — 12,966 ms  
**Recorded:** `supabase migration repair --status applied 008`

#### Post-008 Validation

| Check | Result |
|---|---|
| Products PK | ✅ `PRIMARY KEY (id)` (was `(id, name)`) |
| Column rename | ✅ `"Description"` → `description` |
| Data preserved | ✅ `"High quality kalimba for professionals"` |
| `order_status` enum | ✅ dropped |
| Row count | ✅ products: 1 (no data loss) |
| Indexes/constraints | ✅ unchanged except PK swap |

### 4.3 Migration 009 — Product Integrity

**Execution:** ✅ SUCCESS — 10,495 ms  
**Recorded:** `supabase migration repair --status applied 009`

#### Post-009 Validation

| Constraint | Type | Validated |
|---|---|---|
| `cart_items_product_id_fkey` | FK → products(id) CASCADE | ❌ NOT VALID |
| `wishlists_product_id_fkey` | FK → products(id) CASCADE | ❌ NOT VALID |
| `order_items_product_id_fkey` | FK → products(id) SET NULL | ❌ NOT VALID |
| `products_category_id_fkey` | FK → categories(id) SET NULL | ✅ validated |
| `cart_items_user_product_key` | UNIQUE (user_id, product_id) | ✅ |
| `wishlists_user_product_key` | UNIQUE (user_id, product_id) | ✅ |

---

## 5. Row-Count & Data Changes Summary

| Table | Pre-WS3 | Post-WS3 | Change |
|---|---|---|---|
| profiles | 3 | 3 | 1 row: role `user` → `customer` |
| products | 1 | 1 | `category_id` nulled; `Description` → `description`; `updated_at` added (hotfix) |
| categories | 0 | 0 | — |
| cart_items | 0 | 0 | — |
| wishlists | 0 | 0 | — |
| orders | 0 | 0 | — |
| order_items | 0 | 0 | — |

---

## 6. Migration History (post-execution)

| Version | Local | Remote |
|---|---|---|
| 001–009 | applied | applied |
| 010 | pending | — |

---

## 7. Application Validation

| Gate | Result |
|---|---|
| Lint | ✅ 0 errors |
| Tests | ✅ 18/18 passed |
| Build | ✅ succeeded (8.13 s) |
| Repository | ✅ no tracked file changes |
| Frontend/backend | ✅ untouched |

---

## 8. Remaining Risks

1. **Chain defect documented:** `003` trigger on `products` without `products.updated_at` in `002`. Resolved live via ad-hoc column add; repository migration chain should be reconciled in a future commit (add `updated_at` to `002` or `003`).
2. **NOT VALID FKs:** Three product FKs and three user FKs (from 005) remain NOT VALID. Manual `VALIDATE CONSTRAINT` after orphan checks.
3. **`orders_status_check` NOT VALID:** By design in `007`; validate after confirming historical status values when orders exist.
4. **Product without category:** Single product has `category='instruments'` but `category_id=NULL` (0 categories exist). Requires category seeding + backfill or admin assignment.
5. **`full_name` not backfilled:** All 3 profiles have `full_name=NULL`; auth metadata may also be empty.

---

## 9. Is Migration 010 Safe to Execute?

**YES — K-1 is resolved.**

| 010 Dependency | Status |
|---|---|
| `002` — `products.is_active` | ✅ exists (from WS2B) |
| `008` — `products.description` | ✅ exists (renamed in 008) |

Migration `010` adds indexes only (no data mutation). At current scale (≤4 rows), plain `CREATE INDEX` is acceptable. **010 was NOT executed** per workstream instructions.

Recommended next workstream: apply `010`, then run manual `VALIDATE CONSTRAINT` on all NOT VALID FKs.

---

## 10. Commands Executed (representative)

```bash
# Backup
supabase db dump --linked -s public            -f /tmp/babees_backups/pre_ws3_20260712_195038_schema.sql
supabase db dump --linked -s public --data-only -f /tmp/babees_backups/pre_ws3_20260712_195038_data.sql
supabase db dump --linked --role-only           -f /tmp/babees_backups/pre_ws3_20260712_195038_roles.sql

# Pre-flight
supabase migration list --linked
supabase db query --linked -o json "SELECT role, COUNT(*) FROM profiles GROUP BY role;"

# Hotfix (007 prerequisite)
supabase db query --linked "ALTER TABLE public.products ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();"

# Per migration (007..009)
supabase db query -f supabase/migrations/00X_*.sql --linked
supabase migration repair --status applied 00X --linked

# Post
supabase migration list --linked
cd frontend && npm run lint && npm test && npm run build
```

---

## 11. Final Validation Checklist

- ✅ Fresh backup created (`pre_ws3_20260712_195038`)
- ✅ `007` applied and validated
- ✅ `008` applied and validated
- ✅ `009` applied and validated
- ✅ `010` still pending (NOT executed)
- ✅ Live schema updated
- ✅ Migration history updated (`001`–`009`)
- ✅ Repository unchanged
- ✅ Frontend/backend untouched
- ✅ Lint, tests, build pass

**STOP.** Workstream 3 complete. Migration `010` deferred to next workstream.