← All tasks

v0.2 Slice D — Cashier↔shop data integrity PROD · GATE 2

First v0.2 slice with a real schema migration. node migrate.js applied to the prod Supabase pooler at 2026-05-15 (immediately after push — not relying on Render's deploy hook). 5 historical bad rows repaired, and a Postgres trigger now prevents future occurrences of the same shape.

8/8 prod checks green · 51/51 regression green — no regressions. Commit 330243f. Schema migration: 1 new knex file + migrate.js registry entry, idempotent.

Prod state — before / after the migration

Before (2026-05-15 pre-fix)

cashier rows pointing to inactive shops — invisible in the UI, structurally bound (Lumière TK + Riverside)

After (post-migrate.js)

all 5 historical bad rows repaired: shop_id=NULL + is_active=false, preserved for audit. Manager reassigns + reactivates manually if needed.

What's live on prod

H5.3Trigger on commerce.shops UPDATE OF is_active

Fires on the prod Supabase. true → false on any shop now orphans + deactivates its cashiers in a single statement; re-activation does NOT auto-rebind. Verified live on prod via a throwaway test tenant: created shop + cashier, flipped the shop inactive, observed the cashier go to shop_id=NULL + is_active=false; flipped back, cashier stayed severed; tenant DELETE-cascade cleaned up.

CREATE TRIGGER pin_identities_orphan_on_shop_disable_trg
AFTER UPDATE OF is_active ON commerce.shops
FOR EACH ROW EXECUTE FUNCTION commerce.pin_identities_orphan_on_shop_disable();

Checks — 8/8

✓ Trigger pin_identities_orphan_on_shop_disable_trg installed on commerce.shops
✓ Function commerce.pin_identities_orphan_on_shop_disable installed
✓ Historical sweep — 0 cashiers point to inactive shops (was 5)
✓ The 5 historical bad rows are NULL shop + inactive (preserved for audit)
✓ Setup — seed test tenant + active shop + active in-shop cashier on prod
✓ Trigger fires on prod — soft-delete the shop, cashier orphaned + deactivated
✓ Re-activating the shop on prod does NOT auto-rebind
✓ Cleanup — DELETE test tenant on prod (cascades to shop + cashier)

Raw: result.json. No screenshots — this is a DB migration; verification is pure SQL against the prod Supabase pooler.

Regression — 51/51, no regressions from this push

All prod suites green.

test-phase1-prod.mjs	11/11
test-phase2-sso-outdoor-prod.mjs	6/6
test-phase2-cafe-multishop-prod.mjs	6/6
test-m1-prod.mjs	10/10
test-r7-prod.mjs	14/14
test-r8-prod.mjs	4/4
+ test-v0-2-slice-d-prod.mjs	8/8
Total	59/59

Files changed (2, schema migration)

nix-outdoor-sales-backend:
migrations/20260515120000_pin_identities_shop_integrity.ts (new — knex)
migrate.js (append idempotent registry entry)

No nix-cafe code change. Drizzle's commercePinIdentities.shopId FK was already correct; the trigger is the new layer. The migration is idempotent — re-running migrate.js is a no-op (data sweep is a no-op once already run, function is CREATE OR REPLACE, trigger attachment is DROP IF EXISTS-guarded).