𝗕𝘂𝗶𝗹𝗱𝗶𝗻𝗴 𝗮 𝗣𝗿𝗼𝗷𝗲𝗰𝘁 𝗠𝗮𝗻𝗮𝗴𝗲𝗺𝗲𝗻𝘁 𝗧𝗼𝗼𝗹 𝘄𝗶𝘁𝗵 𝗣𝗿𝗶𝘀𝗺𝗮
I am building a collaborative project management tool like Trello.
I am using React, Express.js, PostgreSQL, and Socket.io. Before I write any backend routes, I must design the database schema.
The schema is the foundation. If the schema is wrong, the whole app fails. Here is my breakdown of the Prisma schema design.
𝗧𝗵𝗲 𝗠𝗼𝗱𝗲𝗹𝘀
• User: Stores names, emails, and passwords. I use cuid() for IDs. This creates long, unique strings. It is better than numbers because it does not leak your user count in the URL.
• Project: Holds the project name and description. I made the description optional using a question mark.
• ProjectMember: This is a junction table. It links Users to Projects. Since one user can join many projects, and one project has many users, you need this middle table to manage the many-to-many relationship. I added a unique constraint to prevent the same user from joining a project twice.
• Board: Tasks live inside boards. Boards live inside projects. This hierarchy makes drag-and-drop easy. Moving a task between columns is just a single field update.
• Task: This is the core model. It has two different relations to the User model:
- An assigned user (this is optional).
- A creator (this is required). I had to name these relations explicitly so Prisma knows which is which.
• Comment: Users can leave comments on tasks. I named the relation "author" instead of "user" to keep the code readable.
• Notification: A simple model to track messages for users.
𝗧𝗲𝗰𝗵𝗻𝗶𝗰𝗮𝗹 𝗟𝗲𝘀𝘀𝗼𝗻𝘀 𝗟𝗲𝗮𝗿𝗻𝘁
I ran into several errors while building this. Watch out for these:
- Relation Names: If two fields point to the same model, you must name the relations. If you do not, Prisma will throw an error.
- Nullable Fields: If a relation is optional, you must put a question mark on both the relation field and the foreign key field.
- Syntax Errors: Prisma requires double quotes for string defaults. Single quotes will fail.
- Relation Syntax: Always use the correct syntax: @relation(fields: [localField], references: [remoteField]).
The schema is now migrated. Next, I will build the Express backend and Socket.io setup for real-time updates.