Schema drift between tenant databases

Schema drift between tenant databases
Type: incident simulation · Topic: Multi-Database · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-06/06-schema-drift-between-tenant-databases

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: two tenant databases that should share a schema drifted — tenant_a's
orders table had the external_id column, tenant_b's did not. A migration was
applied to one tenant database but not the other, so the same application code
worked for tenant_a and failed for tenant_b.

How it was found: comparing information_schema.columns for orders across tenant_a
and tenant_b showed tenant_b missing external_id.

The mitigation: apply the missing migration to tenant_b
(ALTER TABLE orders ADD COLUMN external_id text), leaving tenant_a alone.

Lesson: per-tenant (per-database) schemas drift when migrations aren't applied
uniformly. Diff the schemas across databases and apply the migration to the one
that's behind — not the one that's already correct. An index is unrelated.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. tenant_a works, tenant_b errors on the same code path. Compare their schemas: in each database run SELECT column_name FROM information_schema.columns WHERE table_name='orders' ORDER BY 1; tenant_b.orders is missing a column tenant_a has.
2. tenant_a.orders has external_id; tenant_b.orders does not — schema drift. The migration landed on one tenant but not the other.
3. Apply the missing migration to tenant_b (not tenant_a): \connect tenant_b then ALTER TABLE orders ADD COLUMN external_id text; Don't migrate tenant_a again and don't add an index.