Schema drift is the bug that survives code review.

A DBA fixes prod, no one updates the schema file

The most classic drift. An on-call engineer runs ALTER TABLE in psql at 2am to unblock a deploy. The fix works. Nobody opens a PR to mirror the change in schema.prisma or src/schema.ts. The next migration the team writes, three weeks later, generates a diff that silently reverts the prod fix.

psql=# ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz;
ALTER TABLE
# (schema.prisma still says: createdAt DateTime @default(now()))

Two developers, two migration timelines

Alice writes a migration on her branch that adds a column. Bob writes a different migration on his branch that adds a different column. Both branches merge to main but the migrations apply in different order on different environments. Staging ends up with one column order, prod with another. Indexes that depend on column order get rebuilt differently.

// migrations/0042_alice.sql: ALTER TABLE users ADD COLUMN tier TEXT;
// migrations/0042_bob.sql:   ALTER TABLE users ADD COLUMN region TEXT;
// (same number, different content, different envs)

ORM defaults that quietly differ from the DB

Prisma's DateTime maps to TIMESTAMP(3) by default. If your DB has TIMESTAMPTZ (which the PostgreSQL wiki recommends), there is drift. Your code reads UTC, the DB stores 'whatever the session timezone is'. Test data lines up because both write at midnight UTC. Real users in Tokyo break.

// schema.prisma
model Event {
  id        Int      @id
  createdAt DateTime @default(now())  // TIMESTAMP(3), not TIMESTAMPTZ
}

An index added in prod for performance, never in the file

Slow query in prod, SRE adds CREATE INDEX CONCURRENTLY. Performance issue fixed. The index never makes it into the schema file. Six months later a refactor regenerates the migrations and the index disappears on the next deploy. The slow query comes back, but now it is your problem at 3am.

psql=# CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);
# (drift: schema file knows nothing about this index)

Schema-name qualifiers and partial indexes the tools ignore

Prisma migrate diff treats some Postgres features as out of scope: WHERE clauses on partial indexes, multi-schema setups, custom collations. The tool reports 'in sync' when the schemas are not in sync, just on dimensions it does not look at. The team learns to trust 'in sync' even when it is wrong.

// Index in DB:    CREATE INDEX ... ON orders(user_id) WHERE status = 'active';
// Schema file:    @@index([userId])
// prisma migrate diff says: 'No differences'