# Migrate Orders To Canonical Schema

> Use when running migration 0004 that normalizes the orders table from a Stripe-specific shape (stripe_session_id, stripe_customer_id as top-level columns) to a canonical provider-agnostic shape (provider, provider_order_id, provider_customer_id). Covers the four-phase safe migration procedure — add nullable columns, backfill from existing data, validate, drop legacy columns — and the RLS policy update that must accompany the column rename. Do NOT use for unrelated schema migrations (write a fresh skill anchored to that migration's number), for designing a new canonical schema from scratch, or for the ongoing orgQuery access pattern (use postgres-rls-pattern).

- Skill: `jacob-balslev/migrate-orders-to-canonical-schema` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add jacob-balslev/migrate-orders-to-canonical-schema`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jacob-balslev/migrate-orders-to-canonical-schema/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Integrations & APIs
- License: MIT
- Author: jacob-balslev (https://skillmd.com/u/jacob-balslev)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/jacob-balslev/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

| Phase | Precondition | Action | Success criterion |
|---|---|---|---|
| 1. Add nullable columns | Production schema has no `provider` or `provider_order_id` | Add `provider TEXT`, `provider_order_id TEXT`, `provider_customer_id TEXT` as nullable | Build passes; existing rows unaffected |
| 2. Backfill | Phase 1 deployed | `UPDATE orders SET provider = 'stripe', provider_order_id = stripe_session_id, provider_customer_id = stripe_customer_id WHERE provider IS NULL` | `SELECT COUNT(*) FROM orders WHERE provider IS NULL` returns 0 |
| 3. Validate and update application | Phase 2 complete | Search codebase for `stripe_session_id` and `stripe_customer_id` references; update to `provider_order_id` / `provider_customer_id`; deploy the updated application | Zero reads of old column names in production logs for 24 hours |
| 4. Drop legacy columns | Phase 3 observation period complete | `ALTER TABLE orders DROP COLUMN stripe_session_id, DROP COLUMN stripe_customer_id` | Schema matches `db/schema.sql`; `npm run verify` passes |

## Migration SQL

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

| Use instead | When |
|---|---|
| `postgres-rls-pattern` | The task is the ongoing RLS access pattern, not the one-time migration |
| (a fresh migration skill) | The task is a different migration with no relation to the 0004 orders canonicalization |
| `payment-provider-router` | The task is updating the router to use `provider_order_id` after the migration |