Prisma schema design tips: a NestJS guide for developers

Over five years of building production systems, I've watched good schema decisions compound into maintainable platforms, and poor ones turn into migration nightmares that cost weeks of engineering time. Prisma schema design tips aren't just about syntax—they're about encoding your business rules, query patterns, and growth assumptions into the database layer where they'll live for years. This article covers the decisions that matter most.

Compound uniques enforce business logic at the database layer

A compound unique constraint like @@unique([slug, site]) is one of the most underrated features in Prisma schemas. Most developers reach for application-level validation first, but database constraints are your source of truth—they prevent bad data from ever entering your system, even if you have a bug in your code or import data through a backdoor.

Consider a multi-tenant blogging platform where each site can have posts with unique slugs, but the same slug can exist across different sites. Without a compound unique, you might accidentally allow two posts with slug getting-started on the same site. Your application code might prevent it 99% of the time, but a race condition during concurrent requests, a data migration, or a direct database script could violate that rule.

model Post {
  id        Int     @id @default(autoincrement())
  siteId    Int
  slug      String
  title     String
  content   String
  site      Site    @relation(fields: [siteId], references: [id])

  @@unique([siteId, slug])
  @@index([siteId])
}

model Site {
  id    Int     @id @default(autoincrement())
  name  String
  posts Post[]
}

When a duplicate slug attempt occurs on the same site, Prisma throws a clear error you can catch and handle gracefully. Without the constraint, you'd discover the issue through customer complaints or data audits months later.

Compound uniques also work for other patterns: @@unique([email, tenantId]) for multi-tenant user emails, or @@unique([sku, warehouseId]) for inventory across locations. The key is identifying which combinations of fields must never repeat in your domain.

Index what you filter on in every common query

Indexes are your secret weapon against slow queries as your data grows. Many developers add indexes reactively—only after a query becomes slow in production. By that time, you're reindexing tables with millions of rows and your users are waiting.

The pattern I use: for every WHERE clause you write in your application queries, add an index. If you filter by tenantId and status together frequently, index both. If you also sort by createdAt, add it to the index too. Most list queries in SaaS follow this shape: filter by tenant, then by some status or category, then sort by recency.

model Lead {
  id        Int      @id @default(autoincrement())
  tenantId  Int
  status    Status   @default(NEW)
  createdAt DateTime @default(now())
  name      String
  email     String

  @@index([tenantId, status, createdAt])
  @@index([tenantId, createdAt])
}

That schema indexes two patterns:

• Finding leads for a tenant filtered by status and sorted by date—common for dashboards.
• Finding all recent leads for a tenant—the fallback query when no status filter is applied.

In NestJS, when you write queries like this, they'll be fast even with 10 million leads:

async getLeadsByTenant(tenantId: number, status?: Status) {
  return this.prisma.lead.findMany({
    where: {
      tenantId,
      ...(status && { status }),
    },
    orderBy: { createdAt: 'desc' },
    take: 50,
  });
}

The query planner will use your @@index([tenantId, status, createdAt]) and return results in milliseconds. Without it, it performs a full table scan—acceptable at 10,000 rows, catastrophic at 10 million.

Note that indexes have a cost: they slow down writes slightly and consume disk space. But for any production system, the read performance gain far outweighs it. Index generously on fields you filter and sort by.

Enums for fixed states are self-documenting and database-validated

Status fields, priority levels, user roles—these are fixed sets of values that rarely change. Using Prisma enums instead of string columns makes your schema self-documenting and prevents invalid values from ever existing in your database.

Without enums, you might store status as a string field and rely on application code to validate it. A developer might use 'PENDING' in one service and 'pending' in another. A data script might insert 'UNKNOWN'. Your application crashes or behaves unpredictably when it encounters an unexpected value.

With enums, the database rejects anything outside your defined set:

enum LeadStatus {
  NEW
  CONTACTED
  QUALIFIED
  CLOSED_WON
  CLOSED_LOST
}

model Lead {
  id        Int         @id @default(autoincrement())
  tenantId  Int
  status    LeadStatus  @default(NEW)
  createdAt DateTime    @default(now())
}

Now your code can't accidentally insert an invalid status. Prisma generates TypeScript types automatically, so your IDE autocompletes the valid options. And if you need to query leads by status, you're guaranteed type safety:

const hotLeads = await this.prisma.lead.findMany({
  where: {
    status: LeadStatus.QUALIFIED, // TypeScript error if you typo this
  },
});

One caveat: enum changes require a migration. If you add a new status, you need to coordinate the database migration with your application deployment. Plan your enums thoughtfully, but don't avoid them because of this—the type safety is worth it.

Optional relations and onDelete behavior must be deliberate

Nullable foreign keys are powerful but dangerous if you don't think through the consequences. A field like managerId on a Lead might be optional—a lead starts unassigned. But what happens when you delete a manager? If you don't specify onDelete behavior, you might orphan records or accidentally set fields to NULL and lose important context.

Be explicit about every foreign key relationship:

model Lead {
  id        Int      @id @default(autoincrement())
  tenantId  Int
  managerId Int?
  status    Status   @default(NEW)
  createdAt DateTime @default(now())

  tenant    Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  manager   User?    @relation(fields: [managerId], references: [id], onDelete: SetNull)

  @@index([tenantId])
}

model User {
  id    Int    @id @default(autoincrement())
  name  String
  leads Lead[]
}

model Tenant {
  id    Int    @id @default(autoincrement())
  name  String
  leads Lead[]
}

Here, deleting a tenant cascades and removes all its leads—that's correct for multi-tenant isolation. But deleting a manager sets their leads' managerId to NULL rather than orphaning them. The lead remains in the system, just unassigned.

The choices are:

• Cascade: Delete dependent records too. Use for parent-child relationships where child records have no business value without the parent.
• SetNull: Set the foreign key to NULL. Only works if the field is optional. Use when the relationship is descriptive but not essential.
• Restrict: Prevent deletion if dependents exist. Use when you want to enforce referential integrity and require explicit cleanup first.

The default behavior varies by database and Prisma version—don't rely on defaults. State your intent explicitly.

Common mistakes

Over-indexing: Adding an index to every column wastes disk space and slows writes. Index only the fields you actually filter or sort by in your queries. Monitor slow query logs to guide index decisions.

Forgetting tenantId indexes: In multi-tenant systems, almost every query filters by tenantId. If it's not indexed, queries become slow fast. Add it to compound indexes even if it seems obvious.

Making required fields that should be optional: A nullable managerId or notes field is fine. Forcing every field to be required creates friction. Distinguish between required business data (e.g., email for a user) and optional context (e.g., phone number).

Ignoring onDelete consequences: A cascading delete that removes more data than you intended can be catastrophic. Think through what happens to dependent records before choosing cascade behavior.

Using strings for enums: Storing status as a VARCHAR gives you flexibility but loses type safety and database validation. Use Prisma enums for fixed sets of values.

Key takeaways

• Compound unique constraints enforce business rules in the database, not just in application code—use them to prevent impossible states.
• Index every field or combination of fields you filter or sort by in common queries; this decision matters more as your data grows.
• Use enums for fixed states (status, priority, role) to get type safety, database validation, and self-documenting schemas.
• Be explicit about onDelete behavior for every foreign key; cascade, SetNull, and Restrict have different implications for data integrity.
• Schema decisions compound over years—invest the time upfront to get them right, because migrations on large tables are expensive.