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.
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)
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.
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[]
}
@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.
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.
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.
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.
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.
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.
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.
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.
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.
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.