Chapter 26: Prisma Schema Design

The Prisma schema is the single source of truth for your database structure. This chapter covers data modeling, relationships, indexing, and schema evolution patterns.

Schema Basics

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

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

The TaskForge Data Model

// === Core Entities ===

model User {
  id            String    @id @default(cuid())
  email         String    @unique
  name          String
  passwordHash  String    @map("password_hash")
  avatarUrl     String?   @map("avatar_url")
  emailVerified Boolean   @default(false) @map("email_verified")
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")

  // Relations
  memberships   OrganizationMember[]
  assignedTasks Task[]       @relation("AssignedTasks")
  createdTasks  Task[]       @relation("CreatedTasks")
  comments      Comment[]
  sessions      Session[]
  auditLogs     AuditLog[]

  @@map("users")
}

model Organization {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique
  logoUrl   String?  @map("logo_url")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  // Relations
  members   OrganizationMember[]
  projects  Project[]

  @@map("organizations")
}

model OrganizationMember {
  id             String           @id @default(cuid())
  userId         String           @map("user_id")
  organizationId String           @map("organization_id")
  role           OrganizationRole @default(MEMBER)
  joinedAt       DateTime         @default(now()) @map("joined_at")

  // Relations
  user           User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  organization   Organization     @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@unique([userId, organizationId])
  @@index([organizationId])
  @@map("organization_members")
}

enum OrganizationRole {
  OWNER
  ADMIN
  MEMBER
  VIEWER
}

// === Project Management ===

model Project {
  id             String        @id @default(cuid())
  name           String
  description    String?
  status         ProjectStatus @default(ACTIVE)
  priority       Priority      @default(MEDIUM)
  organizationId String        @map("organization_id")
  createdById    String        @map("created_by_id")
  dueDate        DateTime?     @map("due_date")
  createdAt      DateTime      @default(now()) @map("created_at")
  updatedAt      DateTime      @updatedAt @map("updated_at")
  deletedAt      DateTime?     @map("deleted_at") // Soft delete

  // Relations
  organization   Organization  @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  tasks          Task[]
  tags           ProjectTag[]

  @@index([organizationId, status])
  @@index([organizationId, createdAt])
  @@index([deletedAt])
  @@map("projects")
}

enum ProjectStatus {
  ACTIVE
  ARCHIVED
  COMPLETED
}

enum Priority {
  LOW
  MEDIUM
  HIGH
  URGENT
}

model Task {
  id          String     @id @default(cuid())
  title       String
  description String?
  status      TaskStatus @default(TODO)
  priority    Priority   @default(MEDIUM)
  projectId   String     @map("project_id")
  assigneeId  String?    @map("assignee_id")
  createdById String     @map("created_by_id")
  position    Int        @default(0) // For ordering within a column
  dueDate     DateTime?  @map("due_date")
  completedAt DateTime?  @map("completed_at")
  createdAt   DateTime   @default(now()) @map("created_at")
  updatedAt   DateTime   @updatedAt @map("updated_at")

  // Relations
  project     Project    @relation(fields: [projectId], references: [id], onDelete: Cascade)
  assignee    User?      @relation("AssignedTasks", fields: [assigneeId], references: [id], onDelete: SetNull)
  createdBy   User       @relation("CreatedTasks", fields: [createdById], references: [id])
  comments    Comment[]
  subtasks    Task[]     @relation("Subtasks")
  parentTask  Task?      @relation("Subtasks", fields: [parentTaskId], references: [id])
  parentTaskId String?   @map("parent_task_id")

  @@index([projectId, status])
  @@index([assigneeId])
  @@index([projectId, position])
  @@map("tasks")
}

enum TaskStatus {
  TODO
  IN_PROGRESS
  IN_REVIEW
  DONE
  CANCELLED
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  taskId    String   @map("task_id")
  authorId  String   @map("author_id")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

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

  @@index([taskId, createdAt])
  @@map("comments")
}

// === Tagging System ===

model Tag {
  id             String   @id @default(cuid())
  name           String
  color          String   @default("#6B7280")
  organizationId String   @map("organization_id")

  projects       ProjectTag[]

  @@unique([name, organizationId])
  @@map("tags")
}

model ProjectTag {
  projectId String @map("project_id")
  tagId     String @map("tag_id")

  project   Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
  tag       Tag     @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([projectId, tagId])
  @@map("project_tags")
}

// === Auth & Audit ===

model Session {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  token     String   @unique
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")
  userAgent String?  @map("user_agent")
  ipAddress String?  @map("ip_address")

  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([token])
  @@index([expiresAt])
  @@map("sessions")
}

model AuditLog {
  id         String   @id @default(cuid())
  action     String   // "project:created", "task:updated", etc.
  userId     String   @map("user_id")
  resourceId String?  @map("resource_id")
  details    Json?
  ipAddress  String?  @map("ip_address")
  createdAt  DateTime @default(now()) @map("created_at")

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

  @@index([userId, createdAt])
  @@index([resourceId])
  @@index([action, createdAt])
  @@map("audit_logs")
}

Schema Design Best Practices

1. Use `@@map` for Snake Case in Database

Prisma uses camelCase in TypeScript but most databases prefer snake_case:

model OrganizationMember {
  userId String @map("user_id")  // TypeScript: userId, DB column: user_id
  @@map("organization_members")   // TypeScript: OrganizationMember, DB table: organization_members
}

2. Strategic Indexes

Index columns used in WHERE, ORDER BY, and JOIN clauses:

// Composite index for common query patterns
@@index([organizationId, status])      // "List active projects for org X"
@@index([projectId, position])         // "Get tasks ordered by position"
@@index([userId, createdAt])           // "User's recent activity"

// Unique constraints double as indexes
@@unique([userId, organizationId])     // "One membership per user per org"

3. Soft Deletes

Instead of permanently deleting records, mark them as deleted:

model Project {
  deletedAt DateTime? @map("deleted_at")
  @@index([deletedAt])
}

// In queries, exclude soft-deleted records
const projects = await prisma.project.findMany({
  where: {
    organizationId: orgId,
    deletedAt: null, // Only non-deleted projects
  },
});

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

4. Timestamps on Every Table

createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")

@updatedAt automatically updates when the record is modified through Prisma.

5. Use `cuid()` for IDs

id String @id @default(cuid())

CUIDs are:

Collision-resistant
Roughly time-sortable
URL-safe
No coordination needed (unlike auto-increment)

Migrations

# Development: create and apply migration
pnpm prisma migrate dev --name add_project_tags

# Production: apply pending migrations
pnpm prisma migrate deploy

# Reset database (development only!)
pnpm prisma migrate reset

Migration Best Practices

Name migrations descriptively: add_project_tags, add_soft_delete_to_projects
Review generated SQL before applying in production
Never edit a migration that has been applied to production
Create separate migrations for schema changes and data migrations
Test migrations against a copy of production data

Seeding

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // Create test organization
  const org = await prisma.organization.upsert({
    where: { slug: "taskforge-demo" },
    update: {},
    create: {
      name: "TaskForge Demo",
      slug: "taskforge-demo",
    },
  });

  // Create test user
  const user = await prisma.user.upsert({
    where: { email: "[email protected]" },
    update: {},
    create: {
      email: "[email protected]",
      name: "Demo User",
      passwordHash: await hashPassword("demo-password"),
      emailVerified: true,
    },
  });

  // Create membership
  await prisma.organizationMember.upsert({
    where: {
      userId_organizationId: { userId: user.id, organizationId: org.id },
    },
    update: {},
    create: {
      userId: user.id,
      organizationId: org.id,
      role: "OWNER",
    },
  });

  console.log("Seed data created successfully");
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Summary

✅ Descriptive schema with @@map for clean DB column names
✅ Strategic indexes on frequently queried columns
✅ Soft deletes with deletedAt for data retention
✅ CUID IDs for distributed-safe, sortable identifiers
✅ Timestamps on every table with @updatedAt for automatic updates
✅ Named migrations with reviewed SQL
✅ Seed data for development environment setup

Schema Basics​

The TaskForge Data Model​

Schema Design Best Practices​

1. Use @@map for Snake Case in Database​

2. Strategic Indexes​

3. Soft Deletes​

4. Timestamps on Every Table​

5. Use cuid() for IDs​

Migrations​

Migration Best Practices​

Seeding​

Summary​