Database Schema

┌─────────────────┐       ┌─────────────────┐
│  Organization   │───────│ OrganizationMember│
└────────┬────────┘       └────────┬────────┘
         │                         │
         │                         ▼
         │                  ┌─────────────┐
         │                  │    User     │
         │                  └─────────────┘
         │
    ┌────┴─────────────────────────────────────┐
    │                                          │
    ▼                ▼               ▼         ▼
┌──────────┐   ┌──────────┐   ┌──────────┐ ┌──────────┐
│Component │   │ Incident │   │StatusPage│ │Integration│
└────┬─────┘   └────┬─────┘   └────┬─────┘ └────┬─────┘
     │              │              │            │
     │              ▼              ▼            ▼
     │        ┌──────────┐   ┌──────────┐ ┌──────────┐
     │        │ Update   │   │ Widget   │ │ Metrics  │
     │        └──────────┘   └──────────┘ │  Query   │
     │                                     └──────────┘
     ▼
┌──────────┐
│SubComps  │
└──────────┘

model Organization {
  id                  String   @id @default(cuid())
  name                String
  slug                String   @unique
  logo                String?
  logoDark            String?
  primaryColor        String   @default("#3b82f6")
 
  // Security
  require2FA          Boolean  @default(false)
  ssoEnforced         Boolean  @default(false)
  allowedEmailDomains String[]
 
  // Onboarding
  onboardingCompleted Boolean  @default(false)
 
  // Theme
  themePreset         String   @default("system")
 
  // Relations
  members             OrganizationMember[]
  components          Component[]
  incidents           Incident[]
  statusPages         StatusPage[]
  // ... many more
}

model User {
  id               String    @id @default(cuid())
  email            String    @unique
  name             String?
  password         String?   // Null for OAuth-only users
 
  // 2FA
  twoFactorEnabled Boolean   @default(false)
  twoFactorSecret  String?   // AES-256-GCM encrypted
 
  // Relations
  memberships      OrganizationMember[]
}

model OrganizationMember {
  id             String     @id @default(cuid())
  userId         String
  organizationId String
  role           MemberRole @default(MEMBER)
  teamId         String?
  customRoleId   String?
 
  // Relations
  user           User         @relation(...)
  organization   Organization @relation(...)
  team           Team?        @relation(...)
  customRole     CustomRole?  @relation(...)
}
 
enum MemberRole {
  OWNER
  ADMIN
  MEMBER
  VIEWER
  INCIDENT_MANAGER
  ONCALL_RESPONDER
  STATUS_EDITOR
  SUBSCRIBER_MANAGER
}

model Component {
  id               String        @id @default(cuid())
  name             String
  description      String?
  status           ComponentStatus @default(OPERATIONAL)
  type             ComponentType   @default(SERVICE)
  organizationId   String
 
  // Hierarchy
  parentComponentId String?
  displayOrder     Int           @default(0)
 
  // ENDPOINT type fields
  url              String?
  method           HttpMethod?
  checkInterval    Int?
  expectedStatus   Int?
  conditions       Json?
 
  // METRIC type fields
  integrationId    String?
  query            String?
  warningThreshold Float?
  criticalThreshold Float?
 
  // Relations
  subComponents    Component[]   @relation("ComponentHierarchy")
  incidents        Incident[]
  uptimeRecords    UptimeRecord[]
}
 
enum ComponentType {
  SERVICE          // Manual status control
  ENDPOINT         // HTTP health checks
  METRIC           // External metrics
  CALCULATED_METRIC // Computed metrics
}
 
enum ComponentStatus {
  OPERATIONAL
  DEGRADED_PERFORMANCE
  PARTIAL_OUTAGE
  MAJOR_OUTAGE
  UNDER_MAINTENANCE
  UNKNOWN
}

model Incident {
  id             String         @id @default(cuid())
  title          String
  message        String
  status         IncidentStatus @default(INVESTIGATING)
  severity       Severity       @default(MINOR)
  organizationId String
 
  // Relations
  updates        IncidentUpdate[]
  components     Component[]
  postmortem     Postmortem?
}
 
enum IncidentStatus {
  INVESTIGATING
  IDENTIFIED
  MONITORING
  RESOLVED
}
 
enum Severity {
  MINOR
  MAJOR
  CRITICAL
}

model Maintenance {
  id             String            @id @default(cuid())
  title          String
  message        String
  status         MaintenanceStatus @default(SCHEDULED)
  scheduledStart DateTime
  scheduledEnd   DateTime
  organizationId String
 
  // Relations
  updates        MaintenanceUpdate[]
  components     Component[]
}
 
enum MaintenanceStatus {
  SCHEDULED
  IN_PROGRESS
  VERIFYING
  COMPLETED
  CANCELLED
}

model StatusPage {
  id             String           @id @default(cuid())
  name           String
  slug           String
  visibility     PageVisibility   @default(PUBLIC)
  accessToken    String?          // For PRIVATE pages
  organizationId String
 
  // Branding
  customDomain   String?
  logo           String?
  primaryColor   String?
 
  // Relations
  widgets        StatusPageWidget[]
}
 
enum PageVisibility {
  PUBLIC
  PRIVATE
  DRAFT
}

model StatusPageWidget {
  id           String     @id @default(cuid())
  statusPageId String
  type         WidgetType
  gridRow      Int        @default(0)
  gridCol      Int        @default(0)
  gridRowSpan  Int        @default(1)
  gridColSpan  Int        @default(4)
  settings     Json       @default("{}")
  title        String?
}
 
enum WidgetType {
  STATUS_OVERVIEW
  COMPONENT_LIST
  INCIDENT_FEED
  INCIDENT_HISTORY
  UPTIME_CHART
  METRIC_CARD
  RESPONSE_TIME_CHART
  INFRASTRUCTURE_TABLE
  // ... more types
}

model Integration {
  id             String          @id @default(cuid())
  name           String
  type           IntegrationType
  config         Json            // Encrypted sensitive fields
  isEnabled      Boolean         @default(true)
  organizationId String
 
  // Relations
  metricsQueries MetricsQuery[]
}
 
enum IntegrationType {
  PROMETHEUS
  DATADOG
  NEWRELIC
  GRAFANA
  PINGDOM
  CUSTOM_WEBHOOK
}

model MetricsQuery {
  id               String   @id @default(cuid())
  name             String
  query            String
  integrationId    String
  pollingInterval  Int      @default(60)
  organizationId   String
 
  // Multi-series support
  isMultiSeries    Boolean  @default(false)
  groupByTags      String[] @default([])
 
  // Relations
  series           MetricSeries[]
  dataPoints       MetricDataPoint[]
}

model NotificationChannel {
  id             String            @id @default(cuid())
  name           String
  type           NotificationChannelType
  config         Json              // Webhook URL, API keys, etc.
  isEnabled      Boolean           @default(true)
  organizationId String
}
 
enum NotificationChannelType {
  EMAIL
  SLACK
  DISCORD
  TEAMS
  SMS
  WEBHOOK
}

model Subscriber {
  id             String         @id @default(cuid())
  type           SubscriberType
  contact        String         // Email, phone, or webhook URL
  isVerified     Boolean        @default(false)
  organizationId String
}
 
enum SubscriberType {
  EMAIL
  SMS
  WEBHOOK
}

model OnCallSchedule {
  id             String       @id @default(cuid())
  name           String
  timezone       String       @default("UTC")
  rotationType   RotationType @default(WEEKLY)
  organizationId String
 
  // Relations
  participants   OnCallParticipant[]
  escalationPolicy EscalationPolicy?
}
 
enum RotationType {
  DAILY
  WEEKLY
  CUSTOM
}

model ConfigApply {
  id             String   @id @default(cuid())
  config         String   // YAML snapshot
  checksum       String   // SHA-256
  appliedAt      DateTime @default(now())
  appliedBy      String
  organizationId String
 
  resourcesCreated Int    @default(0)
  resourcesUpdated Int    @default(0)
}

model GitSyncConfig {
  id             String        @id @default(cuid())
  provider       GitProvider
  repositoryUrl  String
  branch         String        @default("main")
  configPath     String        @default("status-page.yaml")
  webhookSecret  String
  autoSync       Boolean       @default(true)
  organizationId String        @unique
}
 
enum GitProvider {
  GITHUB
  GITLAB
  BITBUCKET
  GENERIC
}

// Organization scoping (on most models)
@@index([organizationId])
 
// Component hierarchy
@@index([parentComponentId])
@@index([organizationId, parentComponentId])
 
// Time-based queries
@@index([createdAt])
@@index([recordedAt])
 
// Unique constraints
@@unique([organizationId, slug])  // StatusPage
@@unique([organizationId, name])  // Component, Team
@@unique([queryId, tagsHash])     // MetricSeries

# Create a new migration
npx prisma migrate dev --name add_feature
 
# Apply migrations in production
npx prisma migrate deploy
 
# Reset database (development only)
npx prisma migrate reset