https://www.prisma.io/ logo
#orm-help
Title
# orm-help
h

Harris Rothaermel

09/23/2022, 3:17 AM
My prisma schema has gotten complex over the past few days as I try to make it more open to extending in the future. I’m using “meta tables” that act as essentially a base class. So for example there is the Bot model, but then the bot can contain a reference to a DiscordBot model, SlackBot model (for example), or other future bots. The conversations these chatbots engage in then reference the base “bot” model so it can maintain 1 structure for many different platforms. Does this kind of schema make sense? I’ve attached the whole schema with comments in this thread and would love y’alls thoughts
1
Copy code
// This is your Prisma schema file,
// learn more about it in the docs: <https://pris.ly/d/prisma-schema>

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

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

// The User model is for information such as the user's name and email address.
// Email address is optional, but if one is specified for a User then it must be unique.
model User {
  id            String    @id @default(uuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]

  bots Bot[]

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

// The Account model is for information about OAuth accounts associated with a User.
// It will usually contain access_token, id_token and other OAuth specific data.
// TokenSet from openid-client might give you an idea of all the fields.
model Account {
  id                String    @id @default(uuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?   @db.Text
  access_token      String?   @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?   @db.Text
  session_state     String?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt
  deletedAt         DateTime?

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

  @@unique([provider, providerAccountId])
}

// The Session model is used for database sessions. It is not used if JSON Web Tokens are enabled.
// Keep in mind, that you can use a database to persist Users and Accounts, and still use JWT for sessions.
// See the session.strategy option. A single User can have multiple Sessions, each Session can only have one User.
// When a Session is read, we check if it's expires field indicates an invalid session, and delete it from the database.
model Session {
  id           String   @id @default(uuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

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

// The Verification Token model is used to store tokens for passwordless sign in.
// A single User can have multiple open Verification Tokens (e.g. to sign in to different devices).
// It has been designed to be extendable for other verification purposes in the future (e.g. 2FA / short codes).
// NextAuth.js makes sure that every token is usable only once, and by default has a short (1 day, can be configured by maxAge) lifetime.
// If your user did not manage to finish the sign-in flow in time, they will have to start the sign-in process again.
model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  @@unique([identifier, token])
}

model Conversation {
  id String @id @default(uuid())

  channelId String
  active    Boolean @default(true)

  botId        String
  messages     Message[]
  participants Participant[]
  summary      String?       @db.Text

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

  channel Channel @relation(fields: [channelId], references: [id])
  bot     Bot     @relation(fields: [botId], references: [id])
}

// Channels represent the different modalities in which a conversation can be held in
// discord, slack, etc.
// The channel model is acting as an intermediate "base class" for all channels
// hopefully allowing for a more flexible and extensible design in the future
model Channel {
  id String @id @default(uuid())

  discordChannelId String? @unique
  // slackChannelId String? @unique
  // websiteChannelId String? @unique

  conversations Conversation[]

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

  discordChannel DiscordChannel?
}

model DiscordChannel {
  id        String @id @default(uuid())
  channelid String @unique

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

  channel Channel? @relation(fields: [channelid], references: [discordChannelId])
}

model Message {
  id String @id @default(uuid())

  botId         String? // The message would only ever have one of these. It's the owner of the message.
  participantId String?

  conversationId String

  content String @db.Text

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

  conversation Conversation @relation(fields: [conversationId], references: [id])
  bot          Bot?         @relation(fields: [botId], references: [id])
  participant  Participant? @relation(fields: [participantId], references: [id])

  @@index([botId])
  @@index([participantId])
  @@index([conversationId])
}

// Bots are the main entity in the system. They are the ones that are created by a user and are the ones that carry
// ongoing conversations with participants.
// The bot model is acting as an intermediate "base class" for all bots
// hopefully allowing for a more flexible and extensible design in the future
model Bot {
  id          String  @id @default(uuid())
  userId      String  @unique
  characterId String?

  discordBotId String? @unique // create as many interfaces as needed for different platforms (discord, slack, website, etc)
  // slackBotId String? @unique
  // websiteBotId String? @unique

  active Boolean @default(true)

  character     Character?     @relation(fields: [characterId], references: [id])
  messages      Message[]
  conversations Conversation[]
  generations   MeteredEvent[]

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

  user       User        @relation(fields: [userId], references: [id])
  discordBot DiscordBot? @relation(fields: [discordBotId], references: [id])
}

model DiscordBot {
  id    String @id @default(uuid())
  botId String

  discordId     String @unique
  discordSecret String @unique
  username      String

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

  bot Bot?
}

// Participants are non-registered users of the application that interact with bots. e.g. discord users in your server
// The participant model is acting as an intermediate "base class" for all participants
// hopefully allowing for a more flexible and extensible design in the future.
model Participant {
  id String @id @default(uuid())

  discordUserId String? @unique // create as many interfaces as needed for different platforms (discord, slack, website, etc)
  // slackUserId   String? @unique
  // websiteUserId String? @unique

  conversations Conversation[]

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

  discordUser  DiscordUser?   @relation(fields: [discordUserId], references: [id])
  Message      Message[]
  MeteredEvent MeteredEvent[]
}

model DiscordUser {
  id        String @id @default(uuid())
  discordId String @unique
  username  String

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

  Participant Participant?
}

// Metered events are events that are metered by the system. e.g. messages sent, characters generated, etc.
// that will than be used to calculate the cost of the bot to the end user
model MeteredEvent {
  id String @id @default(uuid())

  botId         String
  participantId String? // Null for summarization events since it's not triggered by a user

  promptTokens     Int
  completionTokens Int
  totalTokens      Int

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

  participant Participant? @relation(fields: [participantId], references: [id])
  bot         Bot          @relation(fields: [botId], references: [id])

  @@index([botId])
  @@index([participantId])
}

// Characters hold all the information about a character that is provided by the user.
// It is the "persona" of the bot.
model Character {
  id     String @id @default(uuid())
  userId String @unique

  name           String
  pronoun        String
  background     String             @db.Text
  traits         PersonalityTrait[]
  worldLore      WorldLore[]
  examplePhrases ExamplePhrase[]

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

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

  @@index([userId])
}

model ExamplePhrase {
  id          String @id @default(uuid())
  characterId String

  content String

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

  character Character @relation(fields: [characterId], references: [id])

  @@index([characterId])
}

model PersonalityTrait {
  id          String  @id @default(uuid())
  characterId String?

  content String @db.Text

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

  character Character? @relation(fields: [characterId], references: [id])

  @@index([characterId])
}

model WorldLore {
  id          String  @id @default(uuid())
  characterId String?

  content String @db.Text

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

  character Character? @relation(fields: [characterId], references: [id])

  @@index([characterId])
}
j

Jarupong

09/23/2022, 3:20 AM
Could you move your schema to gist.github.com it will help us a lot to see
h

Harris Rothaermel

09/23/2022, 3:25 AM
@Jarupong thank you for moving it to gist, definitely a lot easier to understand with the syntax highlighting
j

Jarupong

09/23/2022, 3:30 AM
I would say your schema is very simple and readable this is my suggestions • it should have one parent belong to multiple bots
This diagram might help you understand hopefully
let say you have one bot that can manage multiple platform eg discord, slack. so you assign multiple roles to a user who can manage those bots platform
If some bot platforms have some additional fields you can use Json format to store it https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields
h

Harris Rothaermel

09/23/2022, 3:39 AM
@Jarupong the Bot model is the parent to DiscordBot and eventually SlackBot, WebsiteBot, etc
j

Jarupong

09/23/2022, 3:42 AM
So it make sense in my opinion. As I mentioned if you need to add more additional fields please use Json type instead of create new column 😃
Because there is a lot of costs when you change your existing schema
h

Harris Rothaermel

09/23/2022, 3:54 AM
@Jarupong I’ll definitely look into JSON fields, I’ve never used them before. What costs would be associated with changing the existing schema? My thought was as long as the changes remain in their subclasses than all that’s needed would be adding another column to the base class to reference another entity potentially
j

Jarupong

09/23/2022, 3:57 AM
let say you already have 100k records then you want to add more fields. it will take a lot of CPU and Memory resources to proceed it
h

Harris Rothaermel

09/23/2022, 4:00 AM
@Jarupong gotcha, that makes sense. so then the idea would be that each Bot base class has a JSON field that I can put sub-class data into?
meaning I don’t need any new columns to support other models
j

Jarupong

09/23/2022, 4:02 AM
Correct
Because many bots have many different fields so I personally use Json field instead
it's lightweight and nothing change for DDL of database
h

Harris Rothaermel

09/23/2022, 4:08 AM
@Jarupong interesting, what would be the cons of such an approach? It limits the bots from having more complicated relations, right? I don’t think I’d necessarily need it though because all my subclass data is just strings so far
👍 1
oh, and it’d also lose type safety, right?
j

Jarupong

09/23/2022, 4:10 AM
Yeah it will lose type safety
I have to create types for them
It does not take effort much
5 Views