landing-manager/docs/database.md

DB 설계(1차)

핵심 엔티티

• Campaign(종목), LandingPage, LandingRoute, RouteCondition, Lead, User
• 역할: ADMIN / LEAD_MANAGER

엔티티 관계

• Campaign 1:N LandingPage
• Campaign 1:N LandingRoute
• LandingRoute 1:N RouteCondition
• LandingRoute N:1 LandingPage(기본 페이지)
• RouteCondition N:1 LandingPage(조건 매칭 페이지)
• LandingPage 1:N Lead
• Campaign 1:N Lead

권한 정책

• ADMIN: 모든 관리자 기능
• LEAD_MANAGER: 리드 조회만 가능

Prisma 스키마 (SQLite 기준)

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

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

enum UserRole {
  ADMIN
  LEAD_MANAGER
}

model Campaign {
  id          String         @id @default(cuid())
  name        String
  description String?
  isActive    Boolean        @default(true)
  createdAt   DateTime       @default(now())
  updatedAt   DateTime       @updatedAt

  landingPages LandingPage[]
  routes      LandingRoute[]
  leads       Lead[]
}

model LandingPage {
  id            String           @id @default(cuid())
  campaignId    String
  campaign      Campaign         @relation(fields: [campaignId], references: [id], onDelete: Cascade)
  name          String
  slug          String?
  blocks        Json
  isDefault     Boolean          @default(false)
  isActive      Boolean          @default(true)
  createdAt     DateTime         @default(now())
  updatedAt     DateTime         @updatedAt

  defaultRoutes LandingRoute[] @relation("RouteDefaultPage")
  conditions    RouteCondition[]
  leads         Lead[]

  @@index([campaignId, isActive])
}

model LandingRoute {
  id            String        @id @default(cuid())
  campaignId    String
  campaign      Campaign      @relation(fields: [campaignId], references: [id], onDelete: Cascade)
  host          String
  path          String
  defaultPageId String
  defaultPage   LandingPage   @relation("RouteDefaultPage", fields: [defaultPageId], references: [id], onDelete: Restrict)
  isActive      Boolean       @default(true)
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt

  conditions    RouteCondition[]
  leads         Lead[]

  @@unique([host, path])
}

model RouteCondition {
  id         String       @id @default(cuid())
  routeId    String
  route      LandingRoute @relation(fields: [routeId], references: [id], onDelete: Cascade)
  pageId     String
  page       LandingPage  @relation(fields: [pageId], references: [id], onDelete: Restrict)
  label      String
  priority   Int          @default(0)
  isActive   Boolean      @default(true)

  startDate  DateTime?
  endDate    DateTime?
  timezone   String       @default("Asia/Seoul")
  weekMask   String       @default("0000000")
  startMinute Int?
  endMinute   Int?

  createdAt  DateTime     @default(now())
  updatedAt  DateTime     @updatedAt
}

model Lead {
  id          String      @id @default(cuid())
  campaignId  String
  campaign    Campaign    @relation(fields: [campaignId], references: [id], onDelete: Cascade)
  pageId      String
  page        LandingPage @relation(fields: [pageId], references: [id], onDelete: Restrict)
  routeId     String?
  route       LandingRoute? @relation(fields: [routeId], references: [id], onDelete: SetNull)
  payload     Json
  sourceMeta  Json?
  submittedAt DateTime    @default(now())

  @@index([campaignId, submittedAt])
}

model User {
  id        String    @id @default(cuid())
  email     String    @unique
  name      String?
  password  String
  role      UserRole  @default(ADMIN)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

참고

• defaultRoutes는 @relation("RouteDefaultPage")로 라우트 기본 페이지 역참조입니다.
• 동일 엔티티에서 기본 라우트를 찾을 때는 LandingRoute.defaultPageId가 FK입니다.
• PostgreSQL 전환 시 datasource provider만 교체하지 않고 nullable/시간대 처리 포함 마이그레이션 검증이 필요하다.