schema.prisma

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

generator kysely {
  provider = "prisma-kysely"
}

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

// Don't use unique constaint on primitive type, because the data is
// designed for multiple-user usecase. For example: budget name can't
// be the same for the same user. But there could be two user which have
// different budgets, with the same budget name.
// Solution: Use multi-fields unique constaint with @@unique([...])
//
// On the other hand, unique constraint for reference is allowed.

model User {
  id                  String    @id
  name                String?
  email               String    @unique
  hashedPassword      String    @map("hashed_password")
  salt                String
  resetToken          String?   @map("reset_token")
  resetTokenExpiresAt DateTime? @map("reset_token_expires_at")
  budgets             Budget[]

  @@map("user")
}

model Account {
  id       String @id
  nickname String

  budget   Budget @relation(fields: [budgetId], references: [id], onDelete: Cascade)
  budgetId String @map("budget_id")
  payee    Payee  @relation(fields: [payeeId], references: [id], onDelete: Cascade)
  payeeId  String @unique @map("payee_id")

  transactions Transaction[]

  @@unique([budgetId, nickname]) // don't use the same name of account inside a same budget
  @@index([budgetId])
  @@map("account")
}

model Payee {
  id   String @id
  name String

  budget   Budget @relation(fields: [budgetId], references: [id], onDelete: Cascade)
  budgetId String @map("budget_id")

  account      Account?
  transactions Transaction[]

  @@index([budgetId])
  @@map("payee")
}

model Transaction {
  id          String   @id
  description String
  date        DateTime @default(now()) @db.Date
  outflow     BigInt   @default(0)
  inflow      BigInt   @default(0)
  cleared     Boolean  @default(false)
  updatedAt   DateTime @updatedAt @map("updated_at")

  account                    Account                  @relation(fields: [accountId], references: [id], onDelete: Cascade)
  accountId                  String                   @map("account_id")
  payee                      Payee?                   @relation(fields: [payeeId], references: [id], onDelete: SetNull)
  payeeId                    String?                  @map("payee_id")
  monthlyBudgetPerCategory   MonthlyBudgetPerCategory @relation(fields: [monthlyBudgetPerCategoryId], references: [id], onDelete: Cascade)
  monthlyBudgetPerCategoryId String                   @map("monthly_budget_per_category_id")

  @@index([accountId])
  @@map("transaction")
}

model Budget {
  id   String @id
  name String

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

  accounts             Account[]
  budgetCategoryGroups BudgetCategoryGroup[]
  payees               Payee[]

  @@unique([userId, name]) // a user can't have budget with the same name
  @@index([userId])
  @@map("budget")
}

model MonthlyBudgetPerCategory {
  id       String @id
  month    Int
  year     Int
  assigned BigInt @default(0)

  budgetCategory   BudgetCategory @relation(fields: [budgetCategoryId], references: [id], onDelete: Cascade)
  budgetCategoryId String         @map("budget_category_id")

  transactions Transaction[]

  @@unique([month, year, budgetCategoryId]) // In a month there shouldn't be two same categories
  @@index([budgetCategoryId])
  @@map("monthly_budget_per_category")
}

model BudgetCategoryGroup {
  id        String @id
  name      String
  sortOrder Int    @map("sort_order")

  budget   Budget @relation(fields: [budgetId], references: [id], onDelete: Cascade)
  budgetId String @map("budget_id")

  budgetCategories BudgetCategory[]

  @@unique([name, budgetId])
  @@index([budgetId])
  @@map("budget_category_group")
}

model BudgetCategory {
  id        String @id
  name      String
  sortOrder Int    @map("sort_order")

  budgetCategoryGroup   BudgetCategoryGroup @relation(fields: [budgetCategoryGroupId], references: [id], onDelete: Cascade)
  budgetCategoryGroupId String              @map("budget_category_group_id")

  monthlyBudgetPerCategories MonthlyBudgetPerCategory[]

  @@unique([name, budgetCategoryGroupId])
  @@index([budgetCategoryGroupId])
  @@map("budget_category")
}