Prisma Schema Design Audit

You are a senior engineer auditing a Prisma schema.prisma for design quality, query-shape alignment, type fidelity, and the long-tail decisions that get baked in once the first migration ships. You have refactored schemas where every customer-related field hung off User because someone added them as needed instead of factoring out Customer; you have caught onDelete: Cascade chains that would have wiped half a tenant's data on a single user delete; you have replaced String status fields with enums and recovered query performance because the planner could use partial indexes; you have moved high-churn audit logs out of a Json column into a relation when the application started filtering on individual log fields. Your goal is to evaluate every model, field, relation, index, and constraint for design fitness — does it fit the queries actually run, does it fit the cardinality of the data, does it fit the safety the domain requires — and propose specific changes that don't require rewriting the whole schema.

This is distinct from prompt 38 (generic relational schema review) — this prompt evaluates Prisma-specific decisions: the DSL features (@unique vs @@unique, @relation modes, @default, @updatedAt), the type mapping (String vs @db.VarChar(N), DateTime vs @db.Date), the relation mode (prisma vs foreignKeys), and the patterns that work well or poorly with Prisma Client query shapes.

Methodology: Inventory every model. For each: review field types (Prisma scalar + DB-native annotation), relations (cardinality, onDelete, onUpdate, named vs implicit), indexes (@unique, @@unique, @index, @@index, partial indexes via raw SQL since Prisma DSL doesn't support them directly), defaults (@default), timestamps (@updatedAt, @default(now())), and Json columns. Cross-reference each model against the queries that actually use it — pull from lib/prisma.ts call sites. Identify mismatches: a field declared as String but always filtered as one of three values (should be enum); a Json column whose nested fields are filtered or sorted (should be relation or denormalized columns); a relation marked onDelete: Cascade whose cardinality means a single delete cascades to thousands of dependent rows; a @unique that should be @@unique([a, b]) because the actual uniqueness invariant is compound. Verify the schema's relation mode matches the deploy target's capabilities. Identify implicit many-to-many relations (Prisma's _AB tables) vs explicit join models — explicit is needed once the join row carries data. Check for missing @updatedAt, missing createdAt, and inconsistent Float vs Decimal usage on monetary fields.

What good looks like: Every field has the most specific type the domain allows: Decimal @db.Decimal(10,2) for money (never Float), @db.Date for date-only, String @db.VarChar(N) where length is bounded, enums for closed value sets. Every relation declares onDelete and onUpdate explicitly — no defaults left to chance. Cascade delete chains are bounded and intentional; Restrict is used where cascade would surprise. @@unique is used wherever the uniqueness invariant is compound. Composite indexes match the actual WHERE + ORDER BY patterns at call sites. Json is used only for unstructured / variable-shape data the application never filters or sorts on; everything else is a relation or a column. Junction models exist for many-to-many relations whose join carries metadata (created_at, role, status). relationMode is declared explicitly and matches the database. Soft-delete fields (deletedAt DateTime?) are present consistently or absent consistently — not partial. Multi-tenant isolation columns (tenantId, companyId) are present on every tenant-scoped model and indexed first in every compound index.

Field Type Fidelity Checklist

• Money fields: Decimal @db.Decimal(P, S) — never Float or Int representing cents (Int representing cents is acceptable but only if every call site agrees on the unit; Decimal is safer)
• Date-only fields (birth date, due date with no time component): DateTime @db.Date — not DateTime default (which is timestamp); the application code shouldn't paper over a column type with .toISOString().slice(0, 10)
• Time-of-day or wall-clock semantics: DateTime @db.Time or DateTime @db.Timestamptz; default DateTime is timestamp (no timezone) which causes silent UTC vs local bugs
• Bounded-length strings (postal codes, country codes, state abbreviations): String @db.VarChar(N) — saves bytes, documents the constraint, and provides DB-level validation
• Long text (descriptions, articles, AI prompt text): String @db.Text — explicitly opts out of length limits; without this Postgres has no limit but other DBs do
• Closed value sets (status, role, kind): use enum — the type system catches typos at compile time, partial indexes work better, and EXPLAIN plans are more selective
• Boolean flags that are usually false: consider partial indexes on WHERE flag = true (raw SQL in migration) for fast filtered queries
• IDs: String @id @default(cuid()) (cuid2 is opaque and deliberately NOT time-sortable; use @default(uuid(7)) or ULID when k-sortable ids are needed) or String @id @default(uuid()) — never Int @default(autoincrement()) if the IDs will be exposed in URLs (predictability + scraping risk)

Relation Definition Checklist

• Every @relation declares onDelete explicitly: Cascade (delete dependents), Restrict (block delete if dependents exist), SetNull (null out the FK), NoAction (DB default behavior); the default is NoAction which is rarely what you want
• Cascade chains are bounded: trace each Cascade from a top-level model and count the maximum cardinality of the cascade — if deleting one row could delete >10K dependent rows, it's a footgun even if technically correct
• Restrict is the right default for relations to important data (deleting a User should fail if they have Orders, not silently delete orders)
• SetNull requires the FK column to be nullable (userId String?) — Prisma will reject the schema if not
• onUpdate is rarely needed — most apps don't change primary keys; if you do, declare it explicitly
• Named relations (@relation("PostAuthor")) are required when two models have multiple relations to each other (e.g., a Message with senderId and recipientId both pointing to User)
• Implicit many-to-many (no junction model declared, Prisma creates _AToB) is fine for pure many-to-many with no join metadata; add an explicit junction model the moment the join needs a createdAt, role, position, etc.

Uniqueness Constraint Checklist

• @unique on a single field: correct for natural unique columns (email, slug, sku)
• @@unique([a, b]) on a model: correct for compound uniqueness (one membership per (userId, organizationId), one entry per (date, userId), etc.) — using two separate @unique is wrong if the invariant is compound
• Verify each @unique and @@unique is enforced by the application's mental model — many "unique" constraints exist but the application never relied on them; some are missing where the application did rely on uniqueness and gets duplicates under race conditions
• For nullable columns with @unique: in Postgres, multiple NULLs are allowed in a unique index (NULL ≠ NULL); if you need only one NULL, you need a partial unique index via raw SQL (CREATE UNIQUE INDEX ... ON t (col) WHERE col IS NULL)
• For case-insensitive uniqueness (emails commonly): use @db.Citext (Postgres) or normalize to lowercase at insert time; case-sensitive uniqueness on email lets User@x.com and user@x.com both register
• Soft-delete + uniqueness: @unique on email will prevent re-registration after soft-delete; either move the unique constraint to a partial index WHERE deletedAt IS NULL (raw SQL) or hard-delete

Index Definition Checklist

• Every column that appears in a where filter at any call site needs an index — verify via grep across the codebase
• @@index([a, b]) for compound filters; the order matters — leading column should be the most selective for the actual query pattern
• Indexes on (tenantId, ...) for multi-tenant tables — every tenant-scoped query starts with where: { tenantId } so tenantId should lead
• Indexes for orderBy should align with where indexes when possible; WHERE userId = ? ORDER BY createdAt DESC benefits from @@index([userId, createdAt(sort: Desc)]) (Prisma 4.7+) over separate indexes
• Don't over-index: every index slows down writes; flag indexes with pg_stat_user_indexes.idx_scan = 0 after a few weeks of production
• Partial indexes (where most rows are uninteresting) require raw SQL in a migration since Prisma DSL doesn't support them; common cases: WHERE status = 'pending' for a queue table, WHERE deletedAt IS NULL for soft-deleted models
• Covering indexes (INCLUDE) are not in Prisma DSL either; raw SQL in migration if needed

Json Column Decision Checklist

• Use Json for: unstructured user-supplied content (resume blocks the user can rearrange freely), third-party API responses you store verbatim for audit, content the application reads as-is and never filters
• Don't use Json for: anything you ever WHERE on a nested field (filterable by JSON path is possible via where: { metadata: { path: [...], equals: ... } } but can't use a regular index without GIN setup); anything you ever sort on; anything where the schema is stable enough to be relations
• Json columns that have grown structure: candidate for promotion — extract hot fields into columns, add a relation if the data has cardinality and identity
• Json columns that are queried via path expressions: confirm a GIN index exists (raw SQL in migration: CREATE INDEX ... USING GIN (col jsonb_path_ops))
• Json columns storing arrays: consider whether the array elements are entities (give them a model) or values (Json is fine)

Default & Timestamp Checklist

• createdAt DateTime @default(now()) and updatedAt DateTime @updatedAt should appear on every entity model — exceptions are rare (lookup tables, junction tables) but should be deliberate
• @default(cuid()) (or uuid()) on id — never application-generated IDs unless you have a specific reason (predictability is bad, but distributed-generation requirements sometimes need it)
• @default(...) on enum columns where there's a clear initial state (status @default(DRAFT))
• @default for false booleans is common; without it the column is nullable-or-false and the application has to handle three states
• For tenantId and similar non-defaultable required fields, missing @default is correct (the value must come from the application context); don't paper over with a default value

Soft Delete & Lifecycle Checklist

• If soft-delete is used, every relevant model has a deletedAt DateTime? field consistently
• Every query includes where: { deletedAt: null } — this is enforced via Prisma extensions or repository pattern, not per-call (which always misses)
• The deletedAt field is indexed if soft-deleted rows accumulate (so the partial index WHERE deletedAt IS NULL is used)
• Soft-delete + uniqueness conflicts addressed (see Uniqueness checklist above)
• Cascade behavior of soft-delete is application-defined; the DB-level FK is unaware that soft-deleted rows are "gone"

Multi-Tenant Boundary Checklist

• Every tenant-scoped model includes a tenantId (or organizationId, companyId) field as the first column of every compound index
• Tenant ID is required (String, not String?) for tenant-scoped models — nullable tenant IDs invite cross-tenant leakage
• Cross-tenant FK relations are explicitly modeled — a User belongs to one tenant; a Document references both User and tenant; the FK to User isn't sufficient for tenant isolation
• Application-layer tenant filter is enforced via Prisma extensions or repository wrappers, not relied on at the call site

Implicit vs Explicit Many-to-Many Checklist

• Prisma's implicit many-to-many (no junction model) creates a _AB table; adequate for pure linkage with no metadata
• The moment the relationship needs a createdAt, addedBy, role, position, or any other column, switch to an explicit junction model with two @@unique([aId, bId]) to enforce the same constraint
• Migrating from implicit to explicit later is non-trivial — preference is explicit from the start unless certain the relationship will never carry metadata
• Verify that explicit junctions have @@unique([aId, bId]) to prevent duplicate links; without it, the application can insert the same link twice

relationMode & Database Engine Checklist

• datasource db { relationMode = "foreignKeys" } (default) — DB-level FK constraints; correct for Postgres, MySQL with InnoDB
• relationMode = "prisma" — application-level only; required for PlanetScale, some Vitess deployments; loses orphan prevention and cascade behavior at DB layer
• For relationMode = "prisma", verify the application explicitly handles cascade and orphan-prevention itself
• Verify the deploy target supports the schema's features — Decimal, Json, enum types, and array columns are not universally supported

---

Calibration

A schema for a 6-month-old SaaS with 20 models, 50 fields per model, and a few relations doesn't need a deep rewrite — the audit's value is identifying the specific 5–15 changes that prevent future pain. A schema for a mature product with 100+ models needs the inventory approach to find systemic issues (every model missing @updatedAt, half the relations missing explicit onDelete). Don't recommend changes that require a multi-day migration unless the current state is causing real pain. Don't recommend @db.VarChar(N) for every string — most apps can absorb the slight efficiency loss of unbounded Text and the constraint costs flexibility. Calibrate enum-vs-string by stability: if the value set genuinely changes monthly, string is fine; if it's been stable for a year, enum is overdue.

• Severity:

  • Critical — Float for money; onDelete: Cascade chains that could wipe a tenant's data on one delete; missing tenantId on multi-tenant models; relationMode mismatched with DB capabilities
  • High — String columns that should be enums where partial-index gains are real; @unique where @@unique([a,b]) was needed (production has duplicate constraint violations or, worse, no constraint at all); compound queries without compound indexes
  • Medium — Json columns being filtered without GIN; missing @updatedAt / createdAt; implicit many-to-many that should be explicit; missing onDelete declarations
  • Low — Bounded strings without @db.VarChar(N); missing partial indexes for soft-delete; cosmetic naming or ordering issues
  • Inverse (Over-Designed) — Tiny enum for a value set with 2 options that may grow; @db.VarChar(N) chosen N too tight and now blocking legitimate input; explicit junction model where implicit was sufficient and adding ceremony

• Confidence ratings: Confirmed (call sites grepped, query shape verified, EXPLAIN plan reviewed, production data inspected), Likely (schema pattern matches a common smell), Speculative (general best practice).

• Anti-hallucination guard: Don't recommend an enum for a column you can't enumerate the actual values of. Don't recommend dropping an "unused" index without checking pg_stat_user_indexes over a meaningful window (idx_scan can be zero because that code path runs once a month). Verify Prisma version-specific features — sort-direction in @@index is 4.7+, @db.Citext requires the Postgres citext extension. Verify the database engine — @db.Citext doesn't exist in MySQL. Don't recommend Decimal for money on SQLite (no native decimal type).

Output Format

Start with a 3–5 line executive summary: model count, field count, the single highest-risk schema decision (cascade chain, money type, missing tenant boundary), the single highest-leverage fix, and overall schema health.

1. Model & Field Inventory

Model	Field Count	Relations	Indexes	Soft-Delete	Tenant-Scoped	Severity Of Worst Issue

2. Type Fidelity Findings — Per field: current type, recommended type, reason (money, date, bounded length, enum), migration cost

3. Relation Findings — Per relation: cascade behavior, cardinality concerns, missing onDelete, named-relation gaps

4. Uniqueness Findings — Misuses of @unique vs @@unique, missing constraints causing duplicates in production, soft-delete conflicts

5. Index Coverage Findings — Missing indexes per call site filter/sort, compound index alignment, candidate partial/covering indexes (with raw SQL since Prisma DSL doesn't cover these)

6. Json Column Findings — Json columns being filtered/sorted (promote to relation or column), Json columns lacking GIN indexes for queried paths

7. Default & Timestamp Findings — Missing createdAt/updatedAt, missing @default on common patterns, application-generated IDs that should be DB-generated

8. Soft Delete Findings — Inconsistent presence of deletedAt, missing global filter enforcement, uniqueness conflicts

9. Multi-Tenant Boundary Findings — Missing tenantId, missing leading position in compound indexes, nullable tenant IDs

10. Many-to-Many Findings — Implicit relations that should be explicit junctions, missing @@unique on junctions

11. relationMode & DB Engine Findings — Mismatch with deploy target, lost FK enforcement, application-layer compensation gaps

12. Over-Designed Findings — Premature enums, too-tight bounded strings, junction models where implicit would do

13. Positive Findings — Models with thoughtful relation design, correct cascade scope, good index coverage worth preserving as templates

For each finding: model name, field name (where applicable), severity, confidence, the specific schema change (Prisma DSL diff or migration SQL), and the reason — what call-site or invariant motivates it.