Prisma 7 Database Modelling (PostgreSQL)

Activation cues

• design/modify Prisma models, relations, enums
• choose keys, constraints, timestamps, soft delete
• add indexes, unique constraints, relation fields
• model multi-tenancy or join tables

Non-negotiable principles

1. Database enforces integrity: prefer real foreign keys and constraints in Postgres (avoid app-only integrity unless explicitly required).
2. Schema is the source of truth: treat

   schema.prisma

   + migrations as canonical.
3. Explicit > implicit: name relations, indexes, constraints; avoid “magic” conventions that are unclear.
4. Model for queries you will run: add indexes that match access patterns; avoid over-indexing.

Standard conventions (use unless user overrides)

• Primary keys:

  id String @id @default(cuid())

  for app-level IDs, or

  id BigInt @id @default(autoincrement())

  for DB-generated numeric IDs.
• Timestamps:

  • createdAt DateTime @default(now())

  • updatedAt DateTime @updatedAt

• Soft delete (optional, only if asked):

  deletedAt DateTime?

  + indexes on

  (deletedAt)

  and hot query fields.
• Naming:
  • Prisma model names:

    PascalCase

  • Fields:

    camelCase

  • DB naming: map to

    snake_case

    using

    @map

    and

    @@map

    only if the project standard requires it.
• Use

  @@index

  for common filters/sorts and foreign keys; use

  @@unique

  for business uniqueness.

Relations (Postgres)

• 1:N: foreign key on the “many” side. Add index on the FK.
• 1:1: FK with

  @unique

  on FK field (or

  @@unique

  composite).
• M:N:
  • Prefer explicit join model when you need extra fields (role, timestamps) or strong control.
  • Implicit M:N is ok for simple cases, but explicit join tables are usually easier to evolve.

• Use

  onDelete: Cascade

  only when deletion should delete children.
• Prefer

  Restrict

  /

  NoAction

  when deletion should be blocked until children are handled.
• Use

  SetNull

  only if nullable and you want orphaning.

references/PRISMA7_CORE_REFERENCES.md

Indexing checklist

• Index all foreign key columns.
• Add composite indexes that match:
  • tenant scoping (

    tenantId, createdAt

    )
  • common filters + sort (

    status, createdAt DESC

    — in Prisma you express this as

    @@index([status, createdAt])

    and query with

    orderBy

    ).
• Use unique constraints for natural keys:

  • (tenantId, slug)

    or

    (workspaceId, email)

    .

Migration-safe modeling

• Add columns as nullable first, backfill, then make required.
• Avoid dropping/renaming columns without data migration.
• Prefer

  @map

  /

  @@map

  when renaming in Prisma but keeping DB column stable.
• Never “edit” deployed migrations—create new migrations.

Output format

1. Updated Prisma schema snippets (models/enums).
2. Rationale (constraints, relations, indexes).
3. Migration plan (steps if change is non-trivial).

Examples

Example: 1:N + tenant scoping + indexes

model Workspace {
  id        String   @id @default(cuid())
  name      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  projects  Project[]
}

model Project {
  id          String    @id @default(cuid())
  workspaceId String
  name        String
  slug        String
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  workspace   Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade)

  @@unique([workspaceId, slug])
  @@index([workspaceId, createdAt])
  @@index([workspaceId])
}

Example: explicit M:N join model with metadata

model User {
  id    String @id @default(cuid())
  email String @unique

  projectMembers ProjectMember[]
}

model Project {
  id            String @id @default(cuid())
  name          String
  projectMembers ProjectMember[]
}

model ProjectMember {
  projectId String
  userId    String
  role      String
  joinedAt  DateTime @default(now())

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

  @@id([projectId, userId])
  @@index([userId])
}

Additional resources

• For complete Prisma docs details, see reference.md