Migrate Orders to Canonical Schema

Concept of the skill

What it is: The safe migration procedure for moving an orders table from Stripe-specific columns to provider-neutral columns. Mental model: Add the new shape beside the old one, backfill and observe, then remove the old shape only after application reads have moved. Why it exists: Billing tables sit under live traffic, so schema changes need compatibility windows and explicit validation. What it is NOT: It is not generic data modeling, a new provider router, or the steady-state RLS query pattern. Adjacent concepts: Expand-and-contract migrations, backfills, idempotency checks, RLS policy updates. One-line analogy: It is changing the rails under a moving train by laying the new track beside the old track first. Common misconception: A database rename is atomic enough by itself; application deploy timing makes old and new column reads coexist.

Coverage

• The four-phase safe migration procedure applied to the orders table: add nullable columns → backfill from existing → validate → drop legacy columns; why collapsing any two phases is unsafe under live traffic
• The canonical column mapping: stripe_session_id → provider_order_id, stripe_customer_id → provider_customer_id, with a new provider column set to 'stripe' for existing rows
• The RLS policy update — the existing orders_org_select policy must be updated in the same migration that renames the columns if the policy references them (it does not in this case, but the checklist step prevents future drift)
• Application code audit — grepping for stripe_session_id and stripe_customer_id in the codebase to find every reference that must be updated before the old columns are dropped
• The dry-run gate — scripts/migrate-orders.ts runs in --dry-run by default, printing the diff without committing; --apply is the explicit opt-in