Prisma Schema Design Review

Review Prisma schema files for structural decisions that become expensive to fix after data reaches production. Focus on relation modeling, index strategy, enum usage, and migration safety. This is not about query patterns (covered elsewhere) but about the schema definition itself.

Relation Modeling

Check every relation for explicit foreign key fields. Prisma allows implicit many-to-many relations that hide a join table you cannot customize. Flag implicit many-to-many when the join table will eventually need metadata (timestamps, ordering, soft-delete). Prefer explicit join models in those cases.

Example of the problem:

model Post {
  tags Tag[]
}
model Tag {
  posts Post[]
}

Preferred when join metadata is likely:

model Post {
  postTags PostTag[]
}
model PostTag {
  id     String @id @default(cuid())
  post   Post   @relation(fields: [postId], references: [id])
  postId String
  tag    Tag    @relation(fields: [tagId], references: [id])
  tagId  String
  order  Int    @default(0)
  @@unique([postId, tagId])
}

Flag one-to-one relations where the foreign key side is not obvious. Prisma picks one side arbitrarily if both models declare the relation without fields/references. Always make the owning side explicit.

Index Strategy

Every foreign key field that appears in a @relation(fields: [...]) should have a corresponding @@index unless it is already part of a @@unique or @id. Prisma does not auto-create indexes on foreign keys for most databases. Missing indexes on foreign keys cause full table scans on joins and cascading deletes.

Flag models with more than 5 fields and no @@index or @@unique beyond the primary key. At minimum, columns used in WHERE clauses or ORDER BY in known query patterns need coverage.

For composite indexes, field order matters. Place the highest-cardinality column first unless the dominant query pattern filters on a low-cardinality column exclusively.

Enum Usage

Prisma enums map to database-level enums on PostgreSQL and to plain strings on MySQL/SQLite. Flag enums that are likely to change frequently (e.g., status workflows with more than 6 values). Database-level enum alteration on PostgreSQL requires a migration that adds values but cannot remove them without workarounds. Suggest a String field with application-level validation when the set of values is unstable.

Flag enums used as discriminators for polymorphic patterns. Prisma has no union/inheritance support; a discriminator enum with type-specific nullable fields is a known pain point. Suggest separate models or a JSON field with Zod validation at the application layer when the polymorphic branches diverge significantly.

Default Values and Lifecycle Fields

Every table should have createdAt with @default(now()) and updatedAt with @updatedAt unless there is a documented reason to omit them. Flag models missing both.

Flag @default(autoincrement()) on id fields in multi-tenant or distributed systems. Prefer @default(cuid()) or @default(uuid()) to avoid enumeration attacks and replication conflicts.

Cascade Behavior

Prisma defaults onDelete and onUpdate to database defaults (usually Restrict/NoAction). Flag relations where a parent deletion would logically orphan children but no onDelete: Cascade or onDelete: SetNull is specified. Conversely, flag Cascade on relations where accidental parent deletion could wipe critical data; suggest Restrict with explicit application-level soft-delete.

Migration Safety

Flag renaming a column or dropping a column in a schema change. Prisma migrate generates destructive SQL for these. Suggest a two-step migration: add new column, backfill, then drop old column in a subsequent deploy.

Flag adding a required field without @default to an existing model. This generates an ALTER TABLE that fails if rows exist. Always add with a default or make it optional first, backfill, then tighten.

Severity Levels

Critical: Missing foreign key indexes on tables with expected row counts above 10k. Required field added without default on existing table. Cascade delete on a relation pointing to a high-value model (users, orders, payments).

Warning: Implicit many-to-many on a relation likely to need join metadata. Enum with more than 6 values on PostgreSQL. Missing createdAt/updatedAt. Ambiguous one-to-one ownership.

Suggestion: Consider cuid/uuid over autoincrement for id fields. Composite index field ordering. Explicit join model even when metadata is not yet needed but the domain suggests it will be.