schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider             = "mysql"
  url                  = env("DATABASE_URL")
  referentialIntegrity = "prisma"
}

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

model User {
  id                    String    @id @default(cuid())
  name                  String?
  email                 String?   @unique
  emailVerified         DateTime?
  image                 String?
  stripeId              String?   @unique // Stripe customer ID
  usage                 Int       @default(0)
  usageLimit            Int       @default(1000)
  billingCycleStart     Int?      // day of the month when the billing cycle starts
  accounts              Account[]
  sessions              Session[]
  projects              ProjectUsers[]
  sentEmails            SentEmail[]
  links                 Link[]
  createdAt             DateTime  @default(now())
  usageUpdatedAt        DateTime  @default(now())
}

model Account {
  id                 String  @id @default(cuid())
  userId             String
  type               String
  provider           String
  providerAccountId  String
  refresh_token      String?  @db.Text
  access_token       String?  @db.Text
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String?  @db.Text
  session_state      String?

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

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

model Project {
  id                          String   @id @default(cuid())
  name                        String
  slug                        String   @unique
  logo                        String?
  domain                      String   @unique
  domainVerified              Boolean  @default(false)
  createdAt                   DateTime @default(now())
  updatedAt                   DateTime @updatedAt
  users                       ProjectUsers[]
  domainLastChecked           DateTime @default(now())
  usage                       Int      @default(0)
  ownerUsageLimit             Int      @default(1000)
  ownerExceededUsage          Boolean  @default(false)
  sentEmails                  SentEmail[]
  links                       Link[]
}

model ProjectUsers {
  id        String   @id @default(cuid())
  role      String   @default("member")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    String
  project   Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  projectId String

  @@unique([userId, projectId])
}

enum EmailType {
  firstDomainInvalidEmail
  secondDomainInvalidEmail
  firstUsageLimitEmail
  secondUsageLimitEmail
}

model SentEmail {
  id        String   @id @default(cuid())
  type      EmailType
  createdAt DateTime @default(now())
  user      User?    @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    String?
  project   Project? @relation(fields: [projectId], references: [id], onDelete: Cascade)
  projectId String?
}

model Link {
  id              String   @id @default(cuid())
  domain          String   // domain of the link (e.g. dub.sh) – also stored on Redis
  key             String   // key of the link (e.g. /github) – also stored on Redis
  url             String   @db.LongText // target url (e.g. https://github.com/steven-tey/dub) – also stored on Redis
  archived        Boolean  @default(false) // whether the link is archived or not
  expiresAt       DateTime? // when the link expires – stored on Redis via ttl
  password        String?  // password to access the link – also stored on Redis

  // Custom OG tags – if not set, will use OG tags from target url
  title           String?  // OG title for the link (e.g. Dub - Open-Source Bitly Alternative)
  description     String?  // OG description for the link (e.g. An open-source link shortener SaaS with built-in analytics and free custom domains.)
  image           String?  // OG image for the link (e.g. https://dub.sh/og-image.png)

  // UTM parameters
  utm_source      String?   // UTM source for the link (e.g. youtube.com)
  utm_medium      String?   // UTM medium for the link (e.g. social)
  utm_campaign    String?   // UTM campaign for the link (e.g. summer-sale)
  utm_term        String?   // UTM term for the link (e.g. dub)
  utm_content     String?   // UTM content for the link (e.g. description)
  
  clicks          Int       @default(0) // number of clicks (updated every day via cron + real-time when user views links)
  clicksUpdatedAt  DateTime @default(now()) // last time clicks was updated

  // User who created the link
  user            User?    @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId          String?

  // Project that the link belongs to
  project         Project? @relation(fields: [domain], references: [domain], onUpdate: Cascade, onDelete: Cascade)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  @@unique([domain, key])

  // indices for links per domain/project
  @@index([domain, archived, expiresAt, createdAt(sort: Desc)])
  @@index([domain, archived, expiresAt, clicks(sort: Desc)])

  // indices for links per domain/project specific to a user
  @@index([domain, archived, expiresAt, userId, createdAt(sort: Desc)])
  @@index([domain, archived, expiresAt, userId, clicks(sort: Desc)])

  // TODO: might wanna add indices for utm_source, utm_medium, utm_campaign
}