Migrate Orders To Canonical Schema

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

Philosophy of the skill

A column rename under live traffic is a non-trivial operation even on a small table. The temptation to write one migration that renames columns atomically and ships them fails because application code reads the old column names until the new application version deploys, and the deployment window is not instant. The four-phase procedure exists because the new column can be null during the window, the application can write both, and the old column can be dropped only after the new application version has been running for a full observation period with zero reads of the old column name in logs.

Workflow

Migration SQL

-- Phase 1: Add nullable canonical columns
ALTER TABLE orders
  ADD COLUMN IF NOT EXISTS provider TEXT,
  ADD COLUMN IF NOT EXISTS provider_order_id TEXT,
  ADD COLUMN IF NOT EXISTS provider_customer_id TEXT;

-- Phase 2: Backfill from Stripe-specific columns
UPDATE orders
SET
  provider = 'stripe',
  provider_order_id = stripe_session_id,
  provider_customer_id = stripe_customer_id
WHERE provider IS NULL;

-- Phase 4 (only after Phase 3 observation period):
-- ALTER TABLE orders
--   DROP COLUMN stripe_session_id,
--   DROP COLUMN stripe_customer_id;

Verification

• Phase 1 was deployed to production and verified (build passed, existing rows intact) before Phase 2 ran
• Phase 2 backfill was run with --dry-run first; the dry-run output is committed under db/migrations/0004-dry-run.log
• SELECT COUNT(*) FROM orders WHERE provider IS NULL returns 0 before Phase 3 begins
• Phase 3 application code audit found and updated every reference to stripe_session_id and stripe_customer_id
• The observation window (minimum 24 hours) elapsed with zero old-column reads in production logs before Phase 4 was run
• Phase 4 (DROP COLUMN) is in a separate migration file from Phases 1-2, deployed only after Phase 3 sign-off
• RLS policies were reviewed after the column rename (even if not updated — the review is recorded in the migration PR)

Do NOT Use When