msgr/messenger-server/prisma/schema.prisma

generator client {
    provider = "prisma-client"
    output   = "../generated/prisma"
}

datasource db {
    provider = "sqlite"
}

enum SenderType {
    USER
    CHANNEL
    BOT
}

enum ChannelType {
    PUBLIC
    PRIVATE
}

enum ConversationType {
    DIRECT
    GROUP
    CHANNEL
}

enum ConversationRole {
    OWNER
    ADMIN
    MEMBER
}

enum GroupType {
    PUBLIC
    PRIVATE
}

model Sender {
    id   BigInt     @id
    type SenderType

    userId    BigInt? @unique
    channelId BigInt? @unique
    botId     BigInt? @unique

    user    User?    @relation(fields: [userId], references: [id], onDelete: Cascade)
    channel Channel? @relation(fields: [channelId], references: [id], onDelete: Cascade)

    messages Message[]
}

model User {
    id          BigInt  @id
    firstName   String  @default("")
    lastName    String?
    bio         String?
    username    String? @unique
    login       String  @unique
    password    String
    dateOfBirth BigInt?

    sender Sender?

    chats               Chat[]
    sessions            Session[]
    conversationMembers ConversationMember[]
    messageReads        MessageRead[]

    ownedGroups          Group[]
    groupMemberships     GroupMember[]
    groupBlackLists      GroupBlackList[]
    ownedChannels        Channel[]
    channelSubscriptions ChannelSubscriber[]

    privacySettings   PrivacySettings?
    channelBlackLists ChannelBlackList[]
    kanbanBoards      KanbanBoard[]
}

model Session {
    id          Int     @id @default(autoincrement())
    userId      BigInt
    token       String  @unique
    fcmToken    String?
    deviceModel String
    osVersion   String
    osName      String
    createdAt   BigInt

    user User @relation(fields: [userId], references: [id], onDelete: Cascade)

    @@unique([userId, token])
    @@index([userId])
}

model Message {
    id             Int     @id @default(autoincrement())
    sequenceId     BigInt
    conversationId Int
    text           String?
    sendTime       BigInt
    editedAt       BigInt?

    isRead            Boolean @default(false)
    deletedBySender   Boolean @default(false)
    deletedByReceiver Boolean @default(false)

    senderId BigInt
    sender   Sender @relation(fields: [senderId], references: [id], onDelete: Cascade)

    conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)

    readReceipts MessageRead[]
    files        File[]
    kanbanBoardId Int?
    kanbanBoard   KanbanBoard? @relation(fields: [kanbanBoardId], references: [id], onDelete: SetNull)
    kanbanTaskId  Int?
    kanbanTask    KanbanTask?  @relation(fields: [kanbanTaskId], references: [id], onDelete: SetNull)

    @@unique([conversationId, sequenceId])
    @@index([conversationId, sendTime])
    @@index([senderId])
    @@index([kanbanBoardId])
    @@index([kanbanTaskId])
}

model KanbanBoard {
    id        Int    @id @default(autoincrement())
    ownerId   BigInt
    title     String
    createdAt BigInt
    updatedAt BigInt

    owner    User           @relation(fields: [ownerId], references: [id], onDelete: Cascade)
    columns  KanbanColumn[]
    messages Message[]

    @@index([ownerId])
}

model KanbanColumn {
    id       Int    @id @default(autoincrement())
    boardId  Int
    title    String
    position Int

    board KanbanBoard @relation(fields: [boardId], references: [id], onDelete: Cascade)
    tasks KanbanTask[]

    @@index([boardId, position])
}

model KanbanTask {
    id          Int    @id @default(autoincrement())
    columnId    Int
    title       String
    description String?
    position    Int
    createdAt   BigInt
    updatedAt   BigInt

    column   KanbanColumn @relation(fields: [columnId], references: [id], onDelete: Cascade)
    messages Message[]

    @@index([columnId, position])
}

model File {
    id        String     @id @default(uuid())
    name      String
    size      BigInt
    mimeType  String
    path      String
    status    FileStatus @default(PENDING)
    createdAt BigInt
    updatedAt BigInt

    messageId Int?
    message   Message? @relation(fields: [messageId], references: [id], onDelete: Cascade)

    @@index([messageId])
    @@index([status])
}

enum FileStatus {
    PENDING
    COMPLETED
    FAILED
}

model MessageRead {
    id        Int    @id @default(autoincrement())
    messageId Int
    userId    BigInt
    readAt    BigInt

    message Message @relation(fields: [messageId], references: [id], onDelete: Cascade)
    user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)

    @@unique([messageId, userId])
    @@index([userId, readAt])
}

model Conversation {
    id        Int              @id @default(autoincrement())
    type      ConversationType
    createdAt BigInt

    groupId   BigInt? @unique
    channelId BigInt? @unique

    group   Group?   @relation(fields: [groupId], references: [id], onDelete: Cascade)
    channel Channel? @relation(fields: [channelId], references: [id], onDelete: Cascade)

    members     ConversationMember[]
    messages    Message[]
    chats       Chat[]
    inviteLinks InviteLink[]
}

model ConversationMember {
    id             Int              @id @default(autoincrement())
    conversationId Int
    userId         BigInt
    role           ConversationRole @default(MEMBER)
    joinedAt       BigInt

    conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
    user         User         @relation(fields: [userId], references: [id], onDelete: Cascade)

    @@unique([conversationId, userId])
    @@index([userId])
    @@index([conversationId])
}

model Chat {
    id             Int    @id @default(autoincrement())
    userId         BigInt
    conversationId Int

    isPinned   Boolean @default(false)
    isArchived Boolean @default(false)

    user         User         @relation(fields: [userId], references: [id], onDelete: Cascade)
    conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)

    @@unique([userId, conversationId])
    @@index([userId])
    @@index([conversationId])
}

model Channel {
    id          BigInt      @id
    name        String
    bio         String?
    ownerId     BigInt
    channelType ChannelType @default(PRIVATE)
    username    String?     @unique

    sender Sender?
    owner  User    @relation(fields: [ownerId], references: [id], onDelete: Cascade)

    subscribers  ChannelSubscriber[]
    blockedUsers ChannelBlackList[]

    conversations Conversation[]
}

model ChannelSubscriber {
    id        Int    @id @default(autoincrement())
    userId    BigInt
    channelId BigInt

    user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)
    channel Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)

    @@unique([userId, channelId])
    @@index([userId])
    @@index([channelId])
}

model ChannelBlackList {
    id        Int    @id @default(autoincrement())
    userId    BigInt
    channelId BigInt

    user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)
    channel Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)

    @@unique([userId, channelId])
}

model Group {
    id        BigInt    @id
    ownerId   BigInt
    name      String
    username  String?   @unique
    bio       String?
    groupType GroupType @default(PRIVATE)

    owner   User             @relation(fields: [ownerId], references: [id], onDelete: Cascade)
    members GroupMember[]
    blocked GroupBlackList[]

    conversations Conversation[]
}

model GroupMember {
    id      Int    @id @default(autoincrement())
    groupId BigInt
    userId  BigInt

    user  User  @relation(fields: [userId], references: [id], onDelete: Cascade)
    group Group @relation(fields: [groupId], references: [id], onDelete: Cascade)

    @@unique([groupId, userId])
    @@index([userId])
    @@index([groupId])
}

model GroupBlackList {
    id      Int    @id @default(autoincrement())
    userId  BigInt
    groupId BigInt

    user  User  @relation(fields: [userId], references: [id], onDelete: Cascade)
    group Group @relation(fields: [groupId], references: [id], onDelete: Cascade)

    @@unique([userId, groupId])
}

model InviteLink {
    id             BigInt  @id
    code           String  @unique
    conversationId Int
    creatorId      BigInt
    maxUses        Int?
    uses           Int     @default(0)
    expiresAt      BigInt?

    conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
}

model PrivacySettings {
    id     Int    @id @default(autoincrement())
    userId BigInt @unique

    lastSeen    Int
    messages    Int
    bio         Int
    dateOfBirth Int
    invites     Int

    user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}