Prisma Schema Design: Relationships, Enums, and Indexes That Scale

Source: Dev.to

Your Prisma schema isn’t just ORM config—it’s your data architecture. Bad decisions here compound as your app grows.

Core Relationship Patterns

One‑to‑Many

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  posts     Post[]   // one user has many posts
  createdAt DateTime @default(now())
}

model Post {
  id        String   @id @default(cuid())
  title     String
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())

  @@index([userId]) // always index foreign keys
}

Many‑to‑Many (implicit)

model Post {
  id   String @id @default(cuid())
  tags Tag[]
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}
// Prisma creates a join table automatically

Many‑to‑Many (explicit — when you need metadata)

model User {
  id           String        @id @default(cuid())
  memberships  Membership[]
}

model Organization {
  id           String        @id @default(cuid())
  memberships  Membership[]
}

model Membership {
  id        String   @id @default(cuid())
  userId    String
  orgId     String
  role      Role     @default(MEMBER)
  joinedAt  DateTime @default(now())

  user      User         @relation(fields: [userId], references: [id])
  org       Organization @relation(fields: [orgId], references: [id])

  @@unique([userId, orgId]) // one membership per user per org
  @@index([orgId])
}

enum Role {
  OWNER
  ADMIN
  MEMBER
}

Enums

enum SubscriptionStatus {
  TRIALING
  ACTIVE
  PAST_DUE
  CANCELED
  PAUSED
}

enum PlanType {
  FREE
  PRO
  ENTERPRISE
}

model Subscription {
  id        String             @id @default(cuid())
  userId    String             @unique
  status    SubscriptionStatus @default(TRIALING)
  plan      PlanType           @default(FREE)
  user      User               @relation(fields: [userId], references: [id])
}

Enums are validated at the database level, not just the application level. Prefer enums over string fields for fixed sets of values.

Indexes That Matter

model Event {
  id        String   @id @default(cuid())
  userId    String
  type      String
  createdAt DateTime @default(now())

  @@index([userId])                // for user event feeds
  @@index([type, createdAt])       // for filtering by type + time
  @@index([userId, createdAt])    // for user activity sorted by time
}

Always index:

• Foreign‑key fields
• Fields used in WHERE clauses
• Fields used in ORDER BY on large tables
• Unique‑constraint fields (Prisma adds these automatically)

Composite indexes: order matters. Put the most selective field first, or the field used in equality checks before the field used in range checks.

JSON Fields for Flexible Data

model AuditLog {
  id        String   @id @default(cuid())
  userId    String
  action    String
  metadata  Json     // flexible schema for action‑specific data
  createdAt DateTime @default(now())

  @@index([userId, createdAt])
  @@index([action])
}

// Type‑safe JSON access
const log = await prisma.auditLog.findFirst();
const meta = log.metadata as { ip: string; userAgent: string };

Soft Deletes

model Post {
  id        String    @id @default(cuid())
  title     String
  deletedAt DateTime? // null = active, timestamp = deleted

  @@index([deletedAt]) // filter active records efficiently
}

// Only fetch non‑deleted posts
const posts = await prisma.post.findMany({
  where: { deletedAt: null },
});

// Soft delete
await prisma.post.update({
  where: { id },
  data: { deletedAt: new Date() },
});

Multi‑Tenancy Pattern

model Organization {
  id       String  @id @default(cuid())
  name     String
  slug     String  @unique
  users    User[]
  projects Project[]
}

model Project {
  id    String @id @default(cuid())
  name  String
  orgId String
  org   Organization @relation(fields: [orgId], references: [id], onDelete: Cascade)

  @@index([orgId])
  @@unique([orgId, name]) // unique project names within org
}

Every tenant‑scoped query includes orgId in the where clause. The index ensures these queries stay fast even with millions of rows.

Migration Best Practices

# Development: create + apply migration
npx prisma migrate dev --name add-subscription-table

# Production: apply pending migrations
npx prisma migrate deploy

# Never edit existing migrations
# Create new ones to fix mistakes

Dangerous migrations to review carefully:

• Adding a NOT NULL column to an existing table (requires a default or back‑fill)
• Removing columns (data loss; update application code first)
• Changing column types (may require explicit casting)

For large tables, create indexes concurrently directly in SQL:

-- In a migration file
CREATE INDEX CONCURRENTLY idx_user_email ON "User" (email);

CREATE INDEX CONCURRENTLY "Event_userId_createdAt_idx"
ON "Event" ("userId", "createdAt");

Your schema is a contract with your database. Changes are cheap early and expensive late. Think through relationships and indexes before you have data.

Complete Prisma schema with auth, billing, multi‑tenancy, and audit logs: Whoff Agents AI SaaS Starter Kit.