Prisma, and catching schema drift before it catches you

schema.prisma is the single source of truth for the database shape — models, relations, indexes, enums in one file — and Prisma generates a typed client and manages migrations from it.

prisma migrate dev      # dev: edit the schema, get + apply a migration
prisma migrate deploy   # prod/CI: apply existing migrations only
prisma generate         # regenerate the typed client
prisma migrate diff     # compare two schema states — drift detection

const app = await prisma.applications.findUnique({
  where: { id },
  include: { children: true, programs: true },
});

Schema drift is the failure mode that got me

The database no longer matches what schema.prisma says — because someone edited the schema and forgot to generate the migration, or made a manual change directly. It fails silently at first; local dev still works. Then it surfaces later, usually in production, as a confusing error or the wrong data shape somewhere unrelated.

The fix that actually works: make CI verify migrations reproduce the schema on every run.

prisma migrate diff --from-config-datasource --to-schema database/schema.prisma --exit-code

If the applied migrations don't exactly reconstruct the schema file, CI fails immediately — before drift reaches staging.

A few practical things I hit:

• I keep the schema at Backend/database/schema.prisma with prisma.config.ts pointing there, not the default prisma/schema.prisma. Fully supported, worth writing down clearly because every tutorial assumes the default.
• With pgBouncer in front, Prisma needs the pooled URL for runtime and the direct URL for migrations, because transaction pooling doesn't support the session-level features DDL needs.
• id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid keeps UUID generation consistent for rows inserted outside Prisma — raw SQL, seed scripts, another service.

One thing I noticed reconstructing this project's real history: every migration file is effectively a dated commit message for the database. Migration names describe what changed rather than being wrapped in a feature narrative, so reading the migrations folder chronologically is often more precise than the git log.