Schema drift between tenant databases

Schema drift between tenant databases
Type: incident simulation · Topic: Migrations & Releases · Level: L4 · Duration: 10–15 min
Launch: ride postgres start stage-09/09-schema-drift-between-tenant-databases

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: tenant databases that should share a schema version drifted. tenant_a
and tenant_c were on 20260531_add_order_release_id (orders.release_id present and a
matching schema_migrations row); tenant_b had neither — a release skipped it, so its
schema and recorded version both lagged.

How it was found: comparing schema_migrations across tenants showed tenant_b missing
the expected version, and information_schema.columns confirmed orders.release_id was
absent there too (not just an untracked migration — a real schema gap).

The fix: bring tenant_b up to the expected version, schema first then record:
  \connect tenant_b
  ALTER TABLE orders ADD COLUMN release_id text;
  INSERT INTO schema_migrations (version) VALUES ('20260531_add_order_release_id');

Lesson: release tooling must verify schema-version consistency across every tenant,
and the check must compare the real schema, not just the migrations table. Marking a
version as applied without the schema change (or validating only the healthy tenant)
leaves the drift in place and lies about it.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Tenants are supposed to be on the same schema version. Compare schema_migrations across tenant_a / tenant_b / tenant_c: SELECT version FROM schema_migrations ORDER BY applied_at; — one tenant is behind.
2. tenant_a and tenant_c have the 20260531_add_order_release_id migration (and the orders.release_id column); tenant_b is missing both. Check the actual schema too (information_schema.columns), not only the migration table.
3. Repair tenant_b fully: \connect tenant_b; ALTER TABLE orders ADD COLUMN release_id text; INSERT INTO schema_migrations (version) VALUES ('20260531_add_order_release_id'); Marking the version without the schema change (or only validating tenant_a) is not a fix.