Claude Code for Prisma: Schema Design, Migrations, and Query Optimization

Prisma is the dominant TypeScript ORM for a reason: the schema language is readable, migrations are predictable, and the generated client is fully type-safe. Claude Code generates Prisma schemas from requirements, writes migrations that are backwards-compatible, and queries that avoid the N+1 patterns Prisma makes easy to accidentally introduce.

This guide covers Prisma with Claude Code: schema design, migrations, relations, query optimization, and moving from raw SQL to the Prisma client.

Schema Design

Design a Prisma schema for a multi-tenant SaaS application.
Entities: Organization, User, Project, Task, Comment.
Users belong to organizations, projects belong to organizations,
tasks belong to projects, users can be assigned tasks.

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Organization {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique  // For URL routing
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  users    User[]
  projects Project[]

  @@index([slug])
}

model User {
  id             String   @id @default(cuid())
  email          String   @unique
  name           String?
  hashedPassword String
  role           UserRole @default(MEMBER)
  createdAt      DateTime @default(now())

  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  assignedTasks  Task[]   @relation("TaskAssignee")
  comments       Comment[]

  @@index([organizationId])
  @@index([email])
}

enum UserRole {
  OWNER
  ADMIN
  MEMBER
}

model Project {
  id          String        @id @default(cuid())
  name        String
  description String?
  status      ProjectStatus @default(ACTIVE)
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  tasks Task[]

  @@index([organizationId])
  @@index([organizationId, status])  // Compound index for filtered queries
}

enum ProjectStatus {
  ACTIVE
  ARCHIVED
  COMPLETED
}

model Task {
  id          String      @id @default(cuid())
  title       String
  description String?
  status      TaskStatus  @default(TODO)
  priority    Priority    @default(MEDIUM)
  dueDate     DateTime?
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt

  projectId  String
  project    Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  
  assigneeId String?
  assignee   User?    @relation("TaskAssignee", fields: [assigneeId], references: [id], onDelete: SetNull)
  
  comments Comment[]

  @@index([projectId])
  @@index([assigneeId])
  @@index([projectId, status])  // For filtered task lists
}

enum TaskStatus {
  TODO
  IN_PROGRESS
  BLOCKED
  DONE
}

enum Priority {
  LOW
  MEDIUM
  HIGH
  URGENT
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  createdAt DateTime @default(now())

  taskId String
  task   Task   @relation(fields: [taskId], references: [id], onDelete: Cascade)
  
  authorId String
  author   User   @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@index([taskId])
}

Claude Code adds compound indexes for the query patterns that matter — filtering tasks by status within a project, finding projects by organization slug — not just single-column indexes.

CLAUDE.md for Prisma Projects

## Database (Prisma)
- Provider: PostgreSQL 15
- ORM: Prisma 5.x
- Client in src/lib/prisma.ts (singleton)
- Migrations: prisma/migrations/ — never edit generated migrations, create new ones
- Use prisma.$transaction() for multi-step mutations
- Always include organizationId in queries — multi-tenant isolation critical
- No raw SQL unless Prisma client can't express the query
- Generated types: import from @prisma/client

Migrations

Add a full-text search column to tasks.
Also add an audit log table to track all status changes.

# Add to schema first, then generate migration
npx prisma migrate dev --name add_task_search_and_audit_log

model Task {
  // ... existing fields ...
  
  // Full-text search vector — populated by trigger
  searchVector Unsupported("tsvector")?
  
  @@index([searchVector], type: Gin, map: "task_search_vector_idx")
}

model AuditLog {
  id         String   @id @default(cuid())
  entityType String   // "Task", "Project", etc.
  entityId   String
  action     String   // "STATUS_CHANGE", "ASSIGN", etc.
  oldValue   Json?
  newValue   Json?
  createdAt  DateTime @default(now())

  actorId String
  actor   User   @relation(fields: [actorId], references: [id])
  
  @@index([entityType, entityId])
  @@index([actorId])
  @@index([createdAt])
}

-- Migration includes custom SQL for the trigger
-- prisma/migrations/{timestamp}_add_task_search_and_audit_log/migration.sql

-- Prisma generates the table DDL automatically
-- Add custom trigger for full-text search
CREATE OR REPLACE FUNCTION update_task_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW."searchVector" := to_tsvector('english',
    coalesce(NEW.title, '') || ' ' ||
    coalesce(NEW.description, '')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER task_search_vector_update
  BEFORE INSERT OR UPDATE ON "Task"
  FOR EACH ROW EXECUTE FUNCTION update_task_search_vector();

Safe Migration Patterns

Add a NOT NULL column to an existing table that has data.

Claude Code generates a three-step backwards-compatible migration:

-- Step 1: Add as nullable (safe to deploy)
ALTER TABLE "Task" ADD COLUMN "slug" TEXT;

-- Step 2: Backfill existing rows  
UPDATE "Task" SET "slug" = lower(replace(title, ' ', '-')) || '-' || id
WHERE "slug" IS NULL;

-- Step 3: Add NOT NULL constraint (safe after backfill)
ALTER TABLE "Task" ALTER COLUMN "slug" SET NOT NULL;

-- Step 4: Add unique constraint
ALTER TABLE "Task" ADD CONSTRAINT "Task_slug_key" UNIQUE ("slug");

Never add NOT NULL without a DEFAULT or a backfill step — it will fail on non-empty tables. Claude Code knows this and generates the split migration automatically.

Queries and Relations

Fetch all tasks for a user's assigned projects.
Include project name, assignee info, and open comment count.
Only tasks not yet done.

// src/lib/prisma.ts — singleton client
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as { prisma?: PrismaClient };

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query', 'warn', 'error'] : ['error'],
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

// Query
const userTasks = await prisma.task.findMany({
  where: {
    assigneeId: userId,
    status: { not: 'DONE' },
    project: {
      organizationId: session.user.organizationId, // Multi-tenant isolation
    },
  },
  select: {
    id: true,
    title: true,
    status: true,
    priority: true,
    dueDate: true,
    project: {
      select: { id: true, name: true },
    },
    assignee: {
      select: { id: true, name: true, email: true },
    },
    _count: {
      select: { comments: true },  // Count without loading all comments
    },
  },
  orderBy: [
    { priority: 'desc' },
    { dueDate: 'asc' },
  ],
});

Using select instead of include prevents fetching unnecessary fields. _count gets the comment count in a single query without loading comment records.

Avoiding N+1 Queries

Why is this code making 101 database queries for 100 tasks?

// N+1 — loads tasks, then one query per task for the project
const tasks = await prisma.task.findMany({ where: { assigneeId: userId } });
for (const task of tasks) {
  const project = await prisma.project.findUnique({ where: { id: task.projectId } });
  console.log(`${task.title} in ${project?.name}`);
}

// Fixed — include relation in initial query (single JOIN)
const tasks = await prisma.task.findMany({
  where: { assigneeId: userId },
  include: { project: { select: { name: true } } },
});

For complex aggregations, Prisma’s groupBy handles most cases:

// Task count by status per project — no N+1
const tasksByStatus = await prisma.task.groupBy({
  by: ['projectId', 'status'],
  where: { project: { organizationId: orgId } },
  _count: { id: true },
  orderBy: { projectId: 'asc' },
});

Transactions

When a user completes a task, mark it done,
award them XP points, and check if they unlocked a badge.
All three must succeed or none should.

async function completeTask(taskId: string, userId: string) {
  // Interactive transaction — can run arbitrary async logic
  return prisma.$transaction(async (tx) => {
    // 1. Mark task done
    const task = await tx.task.update({
      where: { id: taskId, assigneeId: userId }, // Verify ownership
      data: { status: 'DONE', completedAt: new Date() },
    });

    // 2. Award XP
    const user = await tx.user.update({
      where: { id: userId },
      data: { xpPoints: { increment: 100 } },
      select: { xpPoints: true },
    });

    // 3. Check badge threshold
    if (user.xpPoints >= 1000) {
      await tx.userBadge.upsert({
        where: { userId_badgeType: { userId, badgeType: 'FIRST_1000_XP' } },
        create: { userId, badgeType: 'FIRST_1000_XP', earnedAt: new Date() },
        update: {}, // Already earned — no-op
      });
    }

    return { task, xpEarned: 100, newTotal: user.xpPoints };
  });
}

For the broader database patterns including raw SQL optimization and pgvector for search, see the database and SQL guide. For Next.js with Prisma — server components querying the database directly — see the Next.js App Router guide. The Claude Skills 360 bundle includes Prisma skill sets for schema design, migration patterns, and query optimization. Start with the free tier to try Prisma schema generation.