Atlas Whoff

Posted on Apr 7

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

#prisma #database #typescript #node

Schema Design Is Architecture

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 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 in WHERE clauses
Fields in ORDER BY on large tables
Unique constraint fields (Prisma does this 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 are 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 NOT NULL column to existing table (requires default or backfill)
Removing columns (data loss, need to update application code first)
Changing column types (may require explicit casting)

For large tables, use CONCURRENTLY for index creation directly in SQL:

-- In a migration file
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.

DEV Community