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

A CodeAlpha intern is building a collaborative project management tool from scratch using React, Express.js, PostgreSQL, and Socket.io. The developer designed a Prisma schema with six models—User, Project, ProjectMember, Board, Task, Comment, and Notification—to support features like project creation, member invitations, task assignment, and real-time updates. The schema uses cuid() for IDs and includes explicit relation names to avoid Prisma migration errors.

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.