schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DB_CONNECTION_STRING")
  extensions = [citext(schema: "public"), pg_cron(schema: "extensions"), uuid_ossp(map: "uuid-ossp", schema: "extensions")]
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique @db.Citext
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  meta      Json?
  profile   Profile? @relation("UserProfile")

  @@index([email, id])
}

model Profile {
  userId                    String                 @id @unique
  createdAt                 DateTime               @default(now())
  updatedAt                 DateTime               @updatedAt
  username                  String                 @unique @db.Citext
  bannedUntil               DateTime?
  reasonForBan              String?
  verified                  Boolean
  lastUpdateInstant         DateTime?
  lastLoginInstant          DateTime?
  passwordLastUpdateInstant DateTime?
  memberships               String[]
  meta                      Json?
  comments                  Comment[]              @relation("CommentAuthor")
  reactedComments           CommentReaction[]      @relation("ReactedToComment")
  taggedComments            CommentTag[]           @relation("TaggedComment")
  commentVotes              CommentVote[]          @relation("CommentVoter")
  communities               Community[]            @relation("CommunityCreator")
  hasBanned                 CommunityBan[]         @relation("BanningUser")
  communityBans             CommunityBan[]         @relation("CommunityBan")
  moderates                 CommunityModerators[]  @relation("ModeratesCommunity")
  subscribes                CommunitySubscribers[] @relation("CommunitySubscriber")
  sentMessages              Message[]              @relation("SentMessage")
  receivedMessages          Message[]              @relation("ReceivedMessage")
  posts                     Post[]                 @relation("PostAuthor")
  reactedPosts              PostReaction[]         @relation("ReactedToPost")
  taggedPosts               PostTag[]              @relation("TaggedPost")
  Voter                     PostVote[]             @relation("PostVoter")
  user                      User                   @relation("UserProfile", fields: [userId], references: [id], onDelete: Cascade)

  @@index([username])
}

model Comment {
  id             Int               @id @unique @default(autoincrement())
  createdAt      DateTime          @default(now())
  updatedAt      DateTime          @updatedAt
  text           String
  parentId       Int?
  authorId       String
  isSticked      Boolean           @default(false)
  communityRoute String            @db.Citext
  postId         Int
  meta           Json?
  author         Profile           @relation("CommentAuthor", fields: [authorId], references: [userId], onDelete: Cascade)
  community      Community         @relation("CommentCommunity", fields: [communityRoute], references: [route], onDelete: Cascade)
  parent         Comment?          @relation("ParentComment", fields: [parentId], references: [id], onDelete: Cascade)
  childComments  Comment[]         @relation("ParentComment")
  post           Post              @relation("CommentPost", fields: [postId], references: [id])
  reactions      CommentReaction[] @relation("CommentHasReaction")
  tags           CommentTag[]      @relation("CommentHasTag")
  votes          CommentVote[]     @relation("CommentVote")
}

model CommentVote {
  voterId   String
  commentId Int
  value     Int
  meta      Json?
  comment   Comment @relation("CommentVote", fields: [commentId], references: [id], onDelete: Cascade)
  voter     Profile @relation("CommentVoter", fields: [voterId], references: [userId], onDelete: Cascade)

  @@id([commentId, voterId])
}

model PostVote {
  voterId String
  postId  Int
  value   Int
  meta    Json?
  post    Post    @relation("PostVote", fields: [postId], references: [id], onDelete: Cascade)
  voter   Profile @relation("PostVoter", fields: [voterId], references: [userId], onDelete: Cascade)

  @@id([postId, voterId])
}

model Post {
  id                     Int            @id @unique @default(autoincrement())
  createdAt              DateTime       @default(now())
  updatedAt              DateTime       @updatedAt
  title                  String         @db.VarChar(512)
  text                   String?
  embeds                 String?
  link                   String?
  authorId               String
  communityRoute         String         @db.Citext
  isSticked              Boolean        @default(false)
  meta                   Json?
  aggregatedHotness      Float          @default(0.0)
  aggregatedUpcomingness Float          @default(0.0)
  aggregatedVotes        Int            @default(0)
  lastAggregated         DateTime?
  comments               Comment[]      @relation("CommentPost")
  author                 Profile        @relation("PostAuthor", fields: [authorId], references: [userId], onDelete: Cascade)
  community              Community      @relation("PostedIn", fields: [communityRoute], references: [route], onDelete: Cascade)
  reactions              PostReaction[] @relation("PostHasReaction")
  tags                   PostTag[]      @relation("PostHasTag")
  postVotes              PostVote[]     @relation("PostVote")
}

model Community {
  route                String                 @id @unique @db.Citext
  createdAt            DateTime               @default(now())
  updatedAt            DateTime               @updatedAt
  createdById          String
  name                 String                 @unique
  headerImage          String?
  description          String?
  isPublic             Boolean                @default(true)
  meta                 Json?
  comments             Comment[]              @relation("CommentCommunity")
  createdBy            Profile                @relation("CommunityCreator", fields: [createdById], references: [userId], onDelete: Cascade)
  communityBans        CommunityBan[]         @relation("BannedFrom")
  moderators           CommunityModerators[]  @relation("ModeratedCommunity")
  communitySubscribers CommunitySubscribers[] @relation("SubscribedCommunity")
  posts                Post[]                 @relation("PostedIn")

  @@index([name])
}

model CommunityBan {
  id             Int       @id @unique @default(autoincrement())
  communityRoute String    @db.Citext
  bannedUserId   String
  bannedById     String
  banReason      String?
  meta           Json?
  bannedBy       Profile   @relation("BanningUser", fields: [bannedById], references: [userId], onDelete: Cascade)
  bannedUser     Profile   @relation("CommunityBan", fields: [bannedUserId], references: [userId], onDelete: Cascade)
  community      Community @relation("BannedFrom", fields: [communityRoute], references: [route], onDelete: Cascade)
}

model CommunityModerators {
  id             Int       @id @unique @default(autoincrement())
  communityRoute String    @db.Citext
  moderatorId    String
  meta           Json?
  community      Community @relation("ModeratedCommunity", fields: [communityRoute], references: [route], onDelete: Cascade)
  moderator      Profile   @relation("ModeratesCommunity", fields: [moderatorId], references: [userId], onDelete: Cascade)
}

model CommunitySubscribers {
  id             Int       @id @unique @default(autoincrement())
  communityRoute String    @db.Citext
  subscriberId   String
  meta           Json?
  community      Community @relation("SubscribedCommunity", fields: [communityRoute], references: [route], onDelete: Cascade)
  subscriber     Profile   @relation("CommunitySubscriber", fields: [subscriberId], references: [userId], onDelete: Cascade)
}

model Message {
  id            Int       @id @unique @default(autoincrement())
  fromId        String
  toId          String
  parentId      Int?
  text          String
  createdAt     DateTime  @default(now())
  read          Boolean   @default(false)
  meta          Json?
  from          Profile   @relation("SentMessage", fields: [fromId], references: [userId], onDelete: Cascade)
  parent        Message?  @relation("ParentMessage", fields: [parentId], references: [id], onDelete: Cascade)
  childMessages Message[] @relation("ParentMessage")
  to            Profile   @relation("ReceivedMessage", fields: [toId], references: [userId], onDelete: Cascade)
}

model Reaction {
  name     String            @id @unique
  meta     Json?
  comments CommentReaction[] @relation("CommentReactionType")
  posts    PostReaction[]    @relation("PostReactionType")
}

model Tag {
  name       String       @id @unique
  meta       Json?
  CommentTag CommentTag[] @relation("CommentTagIdentity")
  PostTag    PostTag[]    @relation("PostTagIdentity")
}

model PostTag {
  id       Int     @id @unique @default(autoincrement())
  postId   Int
  tagName  String
  taggerId String
  meta     Json?
  post     Post    @relation("PostHasTag", fields: [postId], references: [id], onDelete: Cascade)
  tag      Tag     @relation("PostTagIdentity", fields: [tagName], references: [name], onDelete: Cascade)
  tagger   Profile @relation("TaggedPost", fields: [taggerId], references: [userId], onDelete: Cascade)
}

model CommentTag {
  id        Int     @id @unique @default(autoincrement())
  commentId Int
  tagName   String
  taggerId  String
  meta      Json?
  comment   Comment @relation("CommentHasTag", fields: [commentId], references: [id], onDelete: Cascade)
  tag       Tag     @relation("CommentTagIdentity", fields: [tagName], references: [name], onDelete: Cascade)
  tagger    Profile @relation("TaggedComment", fields: [taggerId], references: [userId], onDelete: Cascade)
}

model PostReaction {
  id           Int      @id @unique @default(autoincrement())
  reactionName String
  postId       Int
  reactorId    String
  meta         Json?
  post         Post     @relation("PostHasReaction", fields: [postId], references: [id], onDelete: Cascade)
  reaction     Reaction @relation("PostReactionType", fields: [reactionName], references: [name], onDelete: Cascade)
  reactor      Profile  @relation("ReactedToPost", fields: [reactorId], references: [userId], onDelete: Cascade)
}

model CommentReaction {
  id           Int      @id @unique @default(autoincrement())
  reactionName String
  commentId    Int
  reactorId    String
  meta         Json?
  comment      Comment  @relation("CommentHasReaction", fields: [commentId], references: [id], onDelete: Cascade)
  reaction     Reaction @relation("CommentReactionType", fields: [reactionName], references: [name], onDelete: Cascade)
  reactor      Profile  @relation("ReactedToComment", fields: [reactorId], references: [userId], onDelete: Cascade)
}