Building with Purpose 4: Adding the models and relations

With the database set up, we can now add more models. Thinking about it, we can see three models right now:

• User (which we already have).

• Feedback.

• Town.

model User {
    id       Int        @id @default(autoincrement())
    email    String     @unique
    password String
    Feedback Feedback[]
    Town     Town       @relation(fields: [townId], references: [id])
    townId   Int
}

model Feedback {
    id      Int    @id @default(autoincrement())
    content String
    user    User   @relation(fields: [userId], references: [id])
    userId  Int
    town    Town   @relation(fields: [townId], references: [id])
    townId  String
}

model Town {
    id       Int     @id @default(autoincrement())
    name     String
    zipCode  String
    users    User[]
    Feedback Feedback[]
}

This is what first comes to mind, so now let's get a Reddit post to see a similar example to add more things to it.

Seeing this, we can already add a Vote and a Comment model. We can also add the created, updated, and deleted timestamps to the new and existing models.

Regarding the user, we can also add a profile image, which will be the URL for the image stored somewhere else (probably Uploadthing). Speaking of images, each Feedback will have one or more images (or maybe none), and they’ll also be stored in Uploadthing.

When it comes to roles for the users, they’ll all have the role "CITIZEN" (as they are), but admins will be needed so the content is moderated somehow (there’s always someone trying to mess things up).

generator client {
    provider = "prisma-client-js"
}

datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
}

model User {
    id             Int        @id @default(autoincrement())
    email          String     @unique
    password       String
    name           String
    profileImageId Int?       @unique
    profileImage   Upload?    @relation("UserProfileImage", fields: [profileImageId], references: [id])
    role           String     @default("CITIZEN")
    createdAt      DateTime   @default(now())
    updatedAt      DateTime   @updatedAt
    deletedAt      DateTime?
    feedbacks      Feedback[]
    town           Town       @relation(fields: [townId], references: [id])
    townId         Int
    comments       Comment[]
    votes          Vote[]
    uploads        Upload[]   @relation("UserUploads")
}

model Feedback {
    id        Int       @id @default(autoincrement())
    title     String
    content   String
    category  String
    status    String    @default("PENDING")
    user      User      @relation(fields: [userId], references: [id])
    userId    Int
    town      Town      @relation(fields: [townId], references: [id])
    townId    Int
    createdAt DateTime  @default(now())
    updatedAt DateTime  @updatedAt
    deletedAt DateTime?
    comments  Comment[]
    votes     Vote[]
    priority  String    @default("LOW")
    images    Upload[]  @relation("FeedbackImages")
}

model Town {
    id          Int        @id @default(autoincrement())
    name        String
    zipCode     String
    state       String?
    country     String?
    population  Int?
    description String?
    website     String?
    users       User[]
    feedbacks   Feedback[]
    createdAt   DateTime   @default(now())
    updatedAt   DateTime   @updatedAt
    deletedAt   DateTime?
}

model Comment {
    id         Int       @id @default(autoincrement())
    title      String
    content    String
    user       User      @relation(fields: [userId], references: [id])
    userId     Int
    feedback   Feedback  @relation(fields: [feedbackId], references: [id])
    feedbackId Int
    createdAt  DateTime  @default(now())
    updatedAt  DateTime  @updatedAt
    deletedAt  DateTime?
}

model Vote {
    id         Int       @id @default(autoincrement())
    type       String
    user       User      @relation(fields: [userId], references: [id])
    userId     Int
    feedback   Feedback  @relation(fields: [feedbackId], references: [id])
    feedbackId Int
    createdAt  DateTime  @default(now())
    updatedAt  DateTime  @updatedAt
    deletedAt  DateTime?

    @@unique([userId, feedbackId]) // One vote per user per feedback
}

model Upload {
    id     Int    @id @default(autoincrement())
    name   String
    url    String
    userId Int
    user   User   @relation("UserUploads", fields: [userId], references: [id])

    feedbackId Int?
    feedback   Feedback? @relation("FeedbackImages", fields: [feedbackId], references: [id])

    userProfile User? @relation("UserProfileImage")

    createdAt DateTime  @default(now())
    updatedAt DateTime  @updatedAt
    deletedAt DateTime?
}

To make this clearer, I’ve generated a UML diagram from the prisma.schema. I had to generate it with an extension in Cursor because there is none available for Zed that does so. This is the extension I used (in case you are curious).

With this diagram, all relationships become clearer to see, but there is one key relationship that I’m going to explain:

@@unique([userId, feedbackId]) (Vote table)

This constraint ensures that a user can only vote once per feedback. It’s called a composite uniqueness constraint, and in this case, it means that the combination of a userId and a feedbackId must be unique in the votes table.

Why do we need this?

• First of all, to prevent vote manipulation.

• Also, we want voting to be fair by making sure each user has only one vote per feedback.

That's it for this part.

Salut, Jordi.

Check the repository HERE.