schema.prisma

// https://pris.ly/d/prisma-schema

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

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

model User {
  id        String   @id @default(cuid(2))
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)

  email    String @unique
  password String

  bookReviews   BookReview[]
  authorReviews AuthorReview[]
  bookPrompts   BookPrompt[]
}

model Book {
  id        String   @id @default(cuid(2))
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)

  /// true iff we're able to confirm this is a real book (AI hallucination)
  confirmedExists Boolean
  /// the ISBN13 number of the book if confirmedExists, else hash of title+authors
  isbn13          String  @unique
  title           String
  imageUrl        String?

  authors             Author[]
  bookReviews         BookReview[]
  bookRecommendations BookRecommendation[]
}

model Author {
  id        String   @id @default(cuid(2))
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)

  name String @unique

  books         Book[]
  authorReviews AuthorReview[]
}

model BookReview {
  id        String   @id @default(cuid(2))
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)

  /// The user's rating of the book (1-5)
  rating Int // includes constraint bounding from 1-5, inclusive

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

  book   Book   @relation(fields: [bookId], references: [id], onDelete: Cascade)
  bookId String
}

model AuthorReview {
  id        String   @id @default(cuid(2))
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)

  /// The user's rating of the author (1-5)
  rating Int // includes constraint bounding from 1-5, inclusive

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

  author   Author @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId String
}

model BookPrompt {
  id        String   @id @default(cuid(2))
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)

  /// The user defined prompt text about the book recommendations
  promptText       String
  promptGenreId    String?
  promptGenre      Genre?  @relation("genreBookPrompts", fields: [promptGenreId], references: [id])
  promptSubgenreId String?
  promptSubgenre   Genre?  @relation("subgenreBookPrompts", fields: [promptSubgenreId], references: [id])

  /// For record keeping, the AI model which generated this recommendation
  aiModel String

  bookRecommendations BookRecommendation[]

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

model BookRecommendation {
  id        String   @id @default(cuid(2))
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)

  /// how confident the AI was in generating this recommendation (0 to 1)
  confidenceScore Decimal @db.Decimal(3, 2) // includes constraint bounding from 0-1, inclusive
  /// explanation from the AI as to why this was recommended
  explanation     String

  bookPrompt   BookPrompt @relation(fields: [bookPromptId], references: [id], onDelete: Cascade)
  bookPromptId String

  book   Book   @relation(fields: [bookId], references: [id], onDelete: Cascade)
  bookId String
}

model Genre {
  id        String    @id @default(cuid(2))
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  displayName String @unique

  genreBookPrompts    BookPrompt[] @relation("genreBookPrompts")
  subgenreBookPrompts BookPrompt[] @relation("subgenreBookPrompts")
}