schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["interactiveTransactions"]
}

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

enum ConnectorStatus {
  REACHABLE
  UNREACHABLE
}

enum SourceType {
  MYSQL
  MARIADB
  POSTGRES
  COCKROACHDB
  REDSHIFT
  SNOWFLAKE
  MSSQL
}

model Webhook {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  url       String
  secretKey String   @unique
}

model Source {
  id         Int             @id @default(autoincrement())
  createdAt  DateTime        @default(now())
  updatedAt  DateTime        @updatedAt
  nickname   String?
  status     ConnectorStatus
  sourceType SourceType
  host       String
  port       Int
  schema     String?
  database   String
  username   String
  password   String?
  views      View[]
}

model View {
  id             Int             @id @default(autoincrement())
  createdAt      DateTime        @default(now())
  updatedAt      DateTime        @updatedAt
  tableName      String
  source         Source          @relation(fields: [sourceId], references: [id], onDelete: Cascade)
  sourceId       Int
  columns        ViewColumn[]
  configurations Configuration[]
}

model ViewColumn {
  id             Int                    @id @default(autoincrement())
  createdAt      DateTime               @default(now())
  updatedAt      DateTime               @updatedAt
  name           String
  dataType       String
  isPrimaryKey   Boolean
  isLastModified Boolean
  isTenantColumn Boolean
  resultColumns  ColumnTransformation[]
  view           View                   @relation(fields: [viewId], references: [id])
  viewId         Int
}

enum DestinationType {
  POSTGRES
  PROVISIONED_S3
  SNOWFLAKE
  REDSHIFT
  BIGQUERY
}

model Destination {
  id              Int             @id @default(autoincrement())
  createdAt       DateTime        @default(now())
  updatedAt       DateTime        @updatedAt
  nickname        String
  status          ConnectorStatus
  destinationType DestinationType
  warehouse       String?
  host            String?
  port            Int?
  database        String?
  schema          String?
  username        String?
  password        String?
  configurations  Configuration[]

  // BQ specific
  serviceAccountJson String?
  stagingBucket      StagingBucket?
}

model StagingBucket {
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  bucketName   String
  bucketRegion String?

  destination   Destination @relation(fields: [destinationId], references: [id])
  destinationId Int         @unique
}

model Configuration {
  id             Int      @id @default(autoincrement())
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
  nickname       String?
  warehouseId    String?
  tenantId       String
  lastModifiedAt DateTime @default("1970-01-01T00:00:00-00:00")

  destination   Destination            @relation(fields: [destinationId], references: [id])
  destinationId Int
  view          View                   @relation(fields: [viewId], references: [id], onDelete: Cascade)
  viewId        Int
  columns       ColumnTransformation[]
  transfers     Transfer[]
}

model ColumnTransformation {
  id                Int           @id @default(autoincrement())
  createdAt         DateTime      @default(now())
  updatedAt         DateTime      @updatedAt
  nameInSource      String
  nameInDestination String
  configuration     Configuration @relation(fields: [configurationId], references: [id], onDelete: Cascade)
  configurationId   Int
  viewColumn        ViewColumn    @relation(fields: [viewColumnId], references: [id])
  viewColumnId      Int

  @@unique([configurationId, nameInDestination]) // no duplicate configuration columns
}

enum TransferStatus {
  STARTED
  PENDING
  COMPLETE
  FAILED
  CANCELLED
}

model Transfer {
  id              Int             @id @default(autoincrement())
  createdAt       DateTime        @default(now())
  updatedAt       DateTime        @updatedAt
  status          TransferStatus
  result          TransferResult?
  configuration   Configuration   @relation(fields: [configurationId], references: [id])
  configurationId Int
}

model TransferResult {
  finalizedAt DateTime
  objectUrl   String?
  transfer    Transfer @relation(fields: [transferId], references: [id], onDelete: Cascade)
  transferId  Int      @unique

  @@id([transferId, finalizedAt])
}

enum LogDomain {
  SOURCE
  VIEW
  CONFIGURATION
  DESTINATION
  TRANSFER
}

enum LogAction {
  CREATE
  UPDATE
  DELETE
}

model Log {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  domain    LogDomain
  action    LogAction
  domainId  String
  meta      Json
}