Skip to content

jacob-balslev/skill-graph / examples/projects/saas-stripe-postgres/skills/migrate-orders-to-canonical-schema

migrate-orders-to-canonical-schema

Verified

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).

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

PhasePreconditionActionSuccess criterion
1. Add nullable columnsProduction schema has no provider or provider_order_idAdd provider TEXT, provider_order_id TEXT, provider_customer_id TEXT as nullableBuild passes; existing rows unaffected
2. BackfillPhase 1 deployedUPDATE orders SET provider = 'stripe', provider_order_id = stripe_session_id, provider_customer_id = stripe_customer_id WHERE provider IS NULLSELECT COUNT(*) FROM orders WHERE provider IS NULL returns 0
3. Validate and update applicationPhase 2 completeSearch codebase for stripe_session_id and stripe_customer_id references; update to provider_order_id / provider_customer_id; deploy the updated applicationZero reads of old column names in production logs for 24 hours
4. Drop legacy columnsPhase 3 observation period completeALTER TABLE orders DROP COLUMN stripe_session_id, DROP COLUMN stripe_customer_idSchema matches db/schema.sql; npm run verify passes

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

Use insteadWhen
postgres-rls-patternThe 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-routerThe task is updating the router to use provider_order_id after the migration
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall