money.sori.studio/prisma/schema.prisma

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

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

enum TransactionType {
  income
  expense
}

enum AssetBalanceType {
  cumulative
  monthly_reset
}

enum RecurrenceFrequency {
  none
  daily
  weekly
  monthly
  yearly
}

model User {
  id           String   @id @default(cuid())
  email        String   @unique
  name         String
  passwordHash String
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  assets                Asset[]
  categories            Category[]
  transactions          Transaction[]
  recurringTransactions RecurringTransaction[]
  shareLinks            ShareLink[]
  setting               UserSetting?
}

model UserSetting {
  id              String   @id @default(cuid())
  userId          String   @unique
  primaryCurrency String   @default("KRW")
  monthStartDay   Int      @default(1)
  weekStartDay    Int      @default(0)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

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

model Asset {
  id             String           @id @default(cuid())
  userId         String
  name           String
  balanceType    AssetBalanceType @default(cumulative)
  initialBalance Int              @default(0)
  active         Boolean          @default(true)
  sortOrder      Int              @default(0)
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt

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

  @@index([userId, active, sortOrder])
}

model Category {
  id        String          @id @default(cuid())
  userId    String
  type      TransactionType
  name      String
  icon      String          @default("circle")
  color     String          @default("#6b7673")
  parentId  String?
  sortOrder Int             @default(0)
  active    Boolean         @default(true)
  createdAt DateTime        @default(now())
  updatedAt DateTime        @updatedAt

  user         User          @relation(fields: [userId], references: [id], onDelete: Cascade)
  parent       Category?     @relation("CategoryTree", fields: [parentId], references: [id], onDelete: Restrict)
  children     Category[]    @relation("CategoryTree")
  transactions Transaction[]

  @@index([userId, type, active, sortOrder])
  @@index([parentId])
}

model Transaction {
  id         String          @id @default(cuid())
  userId     String
  type       TransactionType
  occurredAt DateTime
  assetId    String
  categoryId String
  amount     Int
  title      String
  latitude   Decimal?        @db.Decimal(10, 7)
  longitude  Decimal?        @db.Decimal(10, 7)
  memo       String?
  createdAt  DateTime        @default(now())
  updatedAt  DateTime        @updatedAt

  user        User                    @relation(fields: [userId], references: [id], onDelete: Cascade)
  asset       Asset                   @relation(fields: [assetId], references: [id], onDelete: Restrict)
  category    Category                @relation(fields: [categoryId], references: [id], onDelete: Restrict)
  attachments TransactionAttachment[]

  @@index([userId, occurredAt])
  @@index([assetId])
  @@index([categoryId])
}

model TransactionAttachment {
  id            String      @id @default(cuid())
  transactionId String
  fileName      String
  filePath      String
  mimeType      String
  size          Int
  createdAt     DateTime    @default(now())

  transaction Transaction @relation(fields: [transactionId], references: [id], onDelete: Cascade)

  @@index([transactionId])
}

model RecurringTransaction {
  id          String              @id @default(cuid())
  userId      String
  type        TransactionType
  assetId     String
  categoryId  String
  amount      Int
  title       String
  memo        String?
  frequency   RecurrenceFrequency @default(none)
  interval    Int                 @default(1)
  repeatCount Int?
  endDate     DateTime?
  nextRunAt   DateTime?
  active      Boolean             @default(true)
  createdAt   DateTime            @default(now())
  updatedAt   DateTime            @updatedAt

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

  @@index([userId, active, nextRunAt])
}

model ShareLink {
  id           String    @id @default(cuid())
  userId       String
  token        String    @unique
  name         String
  expiresAt    DateTime
  revokedAt    DateTime?
  lastViewedAt DateTime?
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt

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

  @@index([userId, createdAt])
  @@index([token, expiresAt, revokedAt])
}