Building a Project Management Tool from Scratch — Starting with the Prisma Schema

I'm currently a day into Task 3 of my CodeAlpha Full Stack internship. The task: build a collaborative project management tool — think Trello — using React, Express.js, PostgreSQL, and Socket.io for real-time updates.

Before touching Express routes or React components, the first thing I had to nail was the database schema. This post is a full breakdown of the schema I designed, the reasoning behind every model, and the specific Prisma patterns that tripped me up while writing it.

If you're learning Prisma or building anything with relational data, stick around, some of this took me longer to get right than I expected.

The Stack

Backend: Express.js (Node.js with ES modules)
ORM: Prisma
Database: PostgreSQL (hosted on Neon)
Frontend: React + Vite (coming later in the series)
Real-time: Socket.io (Task 3 specific)

What the App Needs to Do

Before writing any schema, I mapped out the features:
Users can create projects and invite other users by email. Projects have boards (columns like To Do, In Progress, Done). Boards contain tasks. Tasks can be assigned to project members, have priorities and due dates, and can be commented on. The app also sends notifications when someone is assigned a task or invited to a project.
That gave me six models: User, Project, ProjectMember, Board, Task, Comment, and Notification.

The User Model

prisma
model User {
  id        String   @id @default(cuid())
  name      String
  email     String   @unique
  password  String
  createdAt DateTime @default(now())

  memberships   ProjectMember[]
  assignedTasks Task[]          @relation("AssignedTo")
  createdTasks  Task[]          @relation("CreatedBy")
  comments      Comment[]
  notifications Notification[]
}

A few things worth noting here.

@default(cuid()) generates a collision-resistant unique ID as a string — something like clx3k2j0f0000.... This is preferable to auto-incrementing integers for publicly exposed IDs because it doesn't leak information about your record count and is safe to put in URLs.

The two Task relations — assignedTasks and createdTasks — both point to the Task model. Prisma needs you to name them explicitly with @relation("AssignedTo") and @relation("CreatedBy") because it can't automatically figure out which relation is which when two fields point at the same model. If you leave the names off, Prisma throws an error during migration. You'll use these same names on the Task model to connect both sides.

The Project Model

prisma
model Project {
  id          String   @id @default(cuid())
  name        String
  description String?
  createdAt   DateTime @default(now())

  members ProjectMember[]
  boards  Board[]
}

Simple. The ? after String makes description optional — Prisma will accept null for that field. Project doesn't have a direct relation to Task, tasks live inside boards, and boards live inside projects. The hierarchy is intentional and covered in the Board section below.

The ProjectMember Model (Junction Table)

prisma
model ProjectMember {
  id   String @id @default(cuid())
  role String @default("MEMBER")

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

  project   Project @relation(fields: [projectId], references: [id])
  projectId String

  @@unique([userId, projectId])
}

This is a junction table — it sits between User and Project to represent a many-to-many relationship. One user can be a member of many projects. One project can have many members. You can't store that on either table alone, so you need this middle model.
What makes it more than a basic junction table is the role field. It stores whether the user is an "OWNER" or "MEMBER". This is what you'll query later when checking permissions — only owners can delete a project or remove other members.

The @@unique([userId, projectId]) at the bottom is a model-level constraint that ensures a user can only be added to a project once. Without it, you could accidentally insert duplicate membership records. If you try to create a duplicate, Prisma throws a unique constraint error which you can catch and return a 409.

On the relation syntax, every relation in Prisma requires two things: the relation field itself and the foreign key field. For userId, that looks like:

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

The fields array contains the foreign key on this model. The references array contains the field it points to on the related model. Both arrays are required, leave one out and Prisma won't generate the migration.

The Board Model

prisma
model Board {
  id    String @id @default(cuid())
  name  String
  order Int

  project   Project @relation(fields: [projectId], references: [id])
  projectId String

  tasks Task[]
}

This is one of the more important design decisions in the schema. Tasks don't belong directly to a project — they belong to a board, which belongs to a project. The reason: moving a task between columns (To Do → In Progress) is just updating the task's boardId to point at a different board. That's the entire drag-and-drop mechanic on the backend. One field update. No complex logic.

The order field is an integer that controls column rendering order. When you create a project, you auto-create three boards: To Do (order 0), In Progress (order 1), Done (order 2). The frontend sorts them by order: 'asc' and renders them left to right.

The Task Model

prisma
model Task {
  id          String    @id @default(cuid())
  title       String
  description String?
  dueDate     DateTime?
  priority    String    @default("MEDIUM")
  createdAt   DateTime  @default(now())

  board   Board  @relation(fields: [boardId], references: [id])
  boardId String

  assignedTo   User?   @relation("AssignedTo", fields: [assignedToId], references: [id])
  assignedToId String?

  createdBy   User   @relation("CreatedBy", fields: [createdById], references: [id])
  createdById String

  comments Comment[]
}

The two user relations here are the most interesting part. A task has a creator (required — every task must have been created by someone) and an assignee (optional — tasks don't always need to be assigned).
For the optional relation:

prisma
assignedTo   User?   @relation("AssignedTo", fields: [assignedToId], references: [id])
assignedToId String?

The ? on User? and String? both matter. User? tells Prisma the relation itself is nullable. String? tells Prisma the foreign key column in the database can be null. You need both — one without the other causes a validation error.
For the required relation:

prisma
createdBy   User   @relation("CreatedBy", fields: [createdById], references: [id])
createdById String

No ?. Every task must have a creator.
The named relations "AssignedTo" and "CreatedBy" here must exactly match the names used on the User model. Prisma uses these names to link both sides of the relation together.

The Comment Model

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

  task   Task   @relation(fields: [taskId], references: [id])
  taskId String

  author   User   @relation(fields: [authorId], references: [id])
  authorId String
}

Nothing complex here. Comments belong to a task and have an author. The relation field is named author (not user) to make intent clear and avoid ambiguity — there's already a user relation on other models. Consistent, readable naming matters especially when you're querying nested includes.

The Notification Model

prisma
model Notification {
  id        String   @id @default(cuid())
  message   String
  read      Boolean  @default(false)
  createdAt DateTime @default(now())

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

Intentionally simple. A notification is just a message for a user, with a read boolean to track whether they've seen it. Notifications get created in two places: when a user is assigned a task, and when a user is invited to a project. The API has endpoints to mark individual notifications as read and to bulk-mark all as read with updateMany.

The Full Schema Together

Once all six models are written, running the migration is:

bash
npx prisma migrate dev --name init

Prisma reads the schema, diffs it against the current database state, generates SQL, and runs it. It also regenerates the Prisma Client so your type-safe queries stay in sync with the schema.
If you want to inspect the data visually:

bash
npx prisma studio

This opens a browser GUI at localhost:5555 where you can browse and edit records directly — useful when you're testing routes and want to verify data is being written correctly.

Mistakes I Made While Writing This

A few errors I ran into that are worth documenting:
Using @default(now()) on an id field. That's a DateTime default, id fields need @default(cuid()) or @default(uuid()). Easy to confuse if you're writing fast.
Forgetting the colon in references: [id]. I wrote references[id] twice before catching it. The full syntax is @relation(fields: [fieldName], references: [id]) — both keys require the colon.

Using single quotes for string defaults. Prisma requires double quotes. @default('MEMBER') throws. @default("MEMBER") works.
Forgetting relation names when two fields on a model point to the same model. If User has both assignedTasks and createdTasks, and both are Task[], Prisma can't figure out the mapping without the @relation("name") on both sides.

What's Next

Schema is done and migrated. Next up is the Express backend — auth routes, project routes, task routes, comment routes, and the Socket.io setup for real-time task updates and comments.
I'll be posting each stage as I go. If you're building something similar or just learning Prisma, the relation naming and junction table patterns in this post will come up in pretty much every relational project you build.

推荐订阅源

DEV Community