sp/apps/web/prisma/schema.prisma

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

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

model User {
  id                 String    @id
  name               String
  email              String    @unique
  emailVerified      Boolean   @default(false)
  image              String?
  twoFactorEnabled   Boolean   @default(false)
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt
  sessions           Session[]
  accounts           Account[]
  twoFactors         TwoFactor[]
}

model Session {
  id        String   @id
  userId    String
  token     String   @unique
  expiresAt DateTime
  ipAddress String?
  userAgent String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Account {
  id                    String    @id
  userId                String
  accountId             String
  providerId            String
  accessToken           String?
  refreshToken          String?
  accessTokenExpiresAt  DateTime?
  refreshTokenExpiresAt DateTime?
  scope                 String?
  idToken               String?
  password              String?
  createdAt             DateTime  @default(now())
  updatedAt             DateTime  @updatedAt
  user                  User      @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Verification {
  id         String   @id
  identifier String
  value      String
  expiresAt  DateTime
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
}

model TwoFactor {
  id          String @id
  userId      String
  secret      String
  backupCodes String
  user        User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Project {
  id          String   @id @default(cuid())
  key         String   @unique
  name        String
  description String?
  /// planned | wired | active
  status      String   @default("planned")
  active      Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

model Event {
  id          String   @id @default(cuid())
  streamId    String   @unique
  projectKey  String
  eventType   String
  version     Int      @default(1)
  payload     Json
  occurredAt  DateTime
  receivedAt  DateTime @default(now())

  @@index([projectKey, occurredAt])
  @@index([eventType])
}

model AuditLog {
  id              String   @id @default(cuid())
  actorUserId     String?
  projectKey      String?
  endpoint        String
  method          String
  requestHash     String?
  responseStatus  Int?
  durationMs      Int?
  sourceIp        String?
  userAgent       String?
  createdAt       DateTime @default(now())

  @@index([createdAt])
  @@index([actorUserId])
  @@index([projectKey])
}

// ---------- Phase 6a: Behavioral Insight Pipeline ----------

model SessionMeta {
  id                String   @id
  projectKey        String
  userIdHash        String?
  posthogDistinctId String?
  isAuthenticated   Boolean  @default(false)
  subscriptionTier  String?
  groupKey          String?
  startedAt         DateTime
  durationMs        Int
  pageviewCount     Int      @default(0)
  clickCount        Int      @default(0)
  errorCount        Int      @default(0)
  rageClickCount    Int      @default(0)
  deadClickCount    Int      @default(0)
  network5xxCount   Int      @default(0)
  network4xxCount   Int      @default(0)
  startUrl          String?
  promotionReasons  String[]
  tags              String[]
  severity          String?
  score             Int?
  customEventCount  Int      @default(0)
  rawMetadataUrl    String?
  status            String   @default("pending_signal")
  fingerprint       String?
  processedAt       DateTime?
  createdAt         DateTime @default(now())

  // Raw rrweb recording preservation (archived to MinIO before PostHog's ~30-day deletion).
  rrwebArchivedAt   DateTime?
  rrwebArchiveKey   String?

  compressed        CompressedSession?

  @@index([status, createdAt])
  @@index([fingerprint])
  @@index([projectKey, status])
  @@index([userIdHash, startedAt])
  @@map("sessions_meta")
}

model CompressedSession {
  sessionId                String   @id
  fingerprint              String
  semanticTimelineMinioKey String
  tokenCountInput          Int
  tokenCountEstOutput      Int
  compressionRatio         Float?
  sanitizationMatchCount   Int      @default(0)
  sanitizationBreakdown    Json?
  isBundle                 Boolean  @default(false)
  bundleSessionIds         String[]
  readyForAnalysisAt       DateTime @default(now())
  createdAt                DateTime @default(now())

  session SessionMeta @relation(fields: [sessionId], references: [id], onDelete: Cascade)

  @@index([fingerprint])
  @@map("compressed_sessions")
}

model IngestionWatermark {
  projectKey     String   @id
  lastPolledAt   DateTime
  posthogCursor  String?
  updatedAt      DateTime @updatedAt

  @@map("ingestion_watermarks")
}

// PostHog identify() person properties cache (24h TTL).
model PosthogPersonCache {
  distinctId   String   @id
  properties   Json
  groups       Json?
  refreshedAt  DateTime
  ttlAt        DateTime

  @@index([ttlAt])
  @@map("posthog_person_cache")
}

// PostHog group analytics (e.g. company) cache (24h TTL).
model PosthogGroupCache {
  groupType    String
  groupKey     String
  properties   Json
  refreshedAt  DateTime
  ttlAt        DateTime

  @@id([groupType, groupKey])
  @@index([ttlAt])
  @@map("posthog_group_cache")
}

// Per-session custom events fetched from PostHog (joined by $session_id).
// Stored so compression can interleave them with rrweb timeline.
model SessionCustomEvent {
  id          BigInt   @id @default(autoincrement())
  sessionId   String
  eventName   String
  timestamp   DateTime
  properties  Json

  @@index([sessionId, timestamp])
  @@map("session_custom_events")
}

// ---------- Phase 6b: LLM Analysis ----------

model PromptTemplate {
  id                 String   @id @default(cuid())
  tag                String
  version            Int
  name               String
  systemPrompt       String   @db.Text
  userPromptTemplate String   @db.Text
  outputSchemaJson   Json
  modelTier          String   // "flash" | "pro"
  maxOutputTokens    Int      @default(1500)
  temperature        Float    @default(0.3)
  active             Boolean  @default(true)
  performanceStats   Json     @default("{}")
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt

  @@unique([tag, version])
  @@map("prompt_templates")
}

model Insight {
  id                    String    @id @default(cuid())
  projectKey            String
  type                  String
  severity              String
  status                String    @default("new")
  fingerprint           String
  // Additional fingerprints semantically merged into this insight by the dedup
  // gate: a new session whose own fingerprint differs but which the LLM judged
  // to be the SAME underlying problem gets its fingerprint aliased here, so the
  // next identical session fast-paths (no re-analysis, no duplicate row).
  aliasFingerprints     String[]  @default([])
  title                 String
  body                  Json
  relatedSessionIds     String[]
  occurrenceCount       Int       @default(1)
  uniqueUserCount       Int       @default(1)
  firstSeenAt           DateTime
  lastSeenAt            DateTime
  confidence            Float?
  priorityScore         Int       @default(0)
  sourcePromptTag       String
  sourcePromptVersion   Int
  sourceModel           String
  sourceCostUsd         Float
  githubIssueUrl        String?
  githubIssueId         BigInt?
  githubIssueNumber     Int?
  githubIssueState      String?
  shippedAt             DateTime?
  validationStartedAt   DateTime?
  validationPeriodDays  Int       @default(14)
  regressionDetected    Boolean   @default(false)
  validatedAt           DateTime?
  founderNotes          String?   @db.Text
  founderSeverityOverride String?
  founderPriority       Int?
  createdAt             DateTime  @default(now())
  updatedAt             DateTime  @updatedAt

  @@unique([projectKey, fingerprint])
  @@index([status, projectKey])
  @@index([severity, status])
  @@index([lastSeenAt(sort: Desc)])
  @@index([priorityScore(sort: Desc)])
  @@map("insights")
}

model CostLedger {
  id                       String   @id @default(cuid())
  insightId                String?
  sessionId                String?
  projectKey               String
  promptTag                String?
  promptVersion            Int?
  provider                 String   // "deepseek" | "anthropic" | "openrouter"
  model                    String
  tier                     String   // "flash" | "pro"
  tokensInputCacheMiss     Int
  tokensInputCacheHit      Int
  tokensOutput             Int
  costInputCacheMissUsd    Float
  costInputCacheHitUsd     Float
  costOutputUsd            Float
  costTotalUsd             Float
  cacheHitRatio            Float
  callDurationMs           Int?
  wasFallback              Boolean  @default(false)
  wasRetry                 Boolean  @default(false)
  errorCode                String?
  createdAt                DateTime @default(now())

  @@index([createdAt])
  @@index([projectKey, createdAt])
  @@index([insightId])
  @@map("cost_ledger")
}

model BudgetSetting {
  id            String   @id @default(cuid())
  projectKey    String?  // null = global
  settingKey    String
  settingValue  Json
  updatedAt     DateTime @updatedAt

  @@unique([projectKey, settingKey])
  @@map("budget_settings")
}

// ---------- Phase 6e: Eval framework ----------

model EvalSet {
  id          String     @id @default(cuid())
  promptTag   String
  name        String
  description String?
  cases       Json       // [{ id, timeline, expected: {...}, rubric: {severity, hypothesis, ...} }]
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
  runs        EvalRun[]

  @@index([promptTag])
  @@map("eval_sets")
}

model EvalRun {
  id              String   @id @default(cuid())
  evalSetId       String
  promptTag       String
  promptVersion   Int
  model           String
  totalCases      Int
  passedSchema    Int      // JSON parse + schema validate succeeded
  passedSeverity  Int      // severity matched expected
  passedRubric    Int      // overall rubric pass (subjective fields)
  totalCostUsd    Float
  totalDurationMs Int
  avgInputTokens  Int
  avgOutputTokens Int
  results         Json     // per-case: { caseId, ok, errors[], output }
  createdAt       DateTime @default(now())

  evalSet EvalSet @relation(fields: [evalSetId], references: [id], onDelete: Cascade)

  @@index([evalSetId, createdAt])
  @@index([promptTag, promptVersion])
  @@map("eval_runs")
}

// ---------- Phase 8a: Content generation ----------

// A topic/brief for content generation. Either auto-generated by the
// content-topics job (LLM idea generation) or entered manually. One topic
// fans out into one ContentDraft per selected channel.
model ContentTopic {
  id                  String   @id @default(cuid())
  projectKey          String
  title               String
  brief               String   @db.Text
  angle               String?  @db.Text
  channels            String[] // ["blog","linkedin","x","instagram"]
  keywords            String[]
  /// queued | generating | drafted | archived
  status              String   @default("queued")
  /// auto | manual
  source              String   @default("auto")
  fingerprint         String?  // dedupe near-identical auto topics
  sourcePromptTag     String?
  sourcePromptVersion Int?
  sourceModel         String?
  sourceCostUsd       Float    @default(0)
  createdAt           DateTime @default(now())
  updatedAt           DateTime @updatedAt

  drafts ContentDraft[]

  @@index([projectKey, status])
  @@index([fingerprint])
  @@map("content_topics")
}

// One generated piece of content for a single channel. Lives as a draft in
// the panel; founder reviews/edits, then publishes via the n8n webhook
// (Phase 8c). bodyJson holds the channel-specific generated structure;
// founderEdits holds any human overrides applied before publish.
model ContentDraft {
  id                  String   @id @default(cuid())
  topicId             String
  projectKey          String
  /// blog | linkedin | x | instagram
  channel             String
  /// draft | approved | publishing | published | failed | rejected
  status              String   @default("draft")
  bodyJson            Json
  founderEdits        Json?
  publishedUrl        String?
  n8nExecutionId      String?
  publishError        String?
  sourcePromptTag     String
  sourcePromptVersion Int
  sourceModel         String
  sourceCostUsd       Float    @default(0)
  publishedAt         DateTime?
  createdAt           DateTime @default(now())
  updatedAt           DateTime @updatedAt

  topic ContentTopic @relation(fields: [topicId], references: [id], onDelete: Cascade)

  @@index([projectKey, status])
  @@index([topicId])
  @@index([channel, status])
  @@map("content_drafts")
}

// ---------- Phase 7a: Sase user-management ----------

// Founder-only notes pinned to a Sase user. Stored panel-side (KVKK minimize:
// the spoke is never written to). Append-only from the UI — delete is allowed
// while we're early; can tighten to audit-only soft-delete later.
model SaseUserNote {
  id           String   @id @default(cuid())
  saseUserId   String   @map("sase_user_id")
  authorUserId String   @map("author_user_id") // panel User.id
  body         String
  pinned       Boolean  @default(false)
  createdAt    DateTime @default(now()) @map("created_at")

  @@index([saseUserId, pinned, createdAt])
  @@map("sase_user_notes")
}

// ---------- Phase 9: External Observability Archive ----------

// PostHog raw event stream mirrored to our DB permanently (free tier deletes
// events on a rolling window). Lossless: full properties kept as JSONB, with
// hot fields promoted to indexed columns. Also cold-dumped to MinIO as gzip JSONL.
model PosthogEvent {
  uuid        String   @id            // PostHog event uuid → natural dedup key
  projectKey  String                  // "sase"
  event       String
  distinctId  String
  personId    String?
  sessionId   String?                 // $session_id — joins to SessionMeta.id
  timestamp   DateTime                // PostHog event time
  properties  Json                    // full properties — lossless
  ingestedAt  DateTime @default(now())
  dumpedAt    DateTime?               // set once written to MinIO cold partition

  @@index([projectKey, timestamp])
  @@index([event, timestamp])
  @@index([distinctId, timestamp])
  @@index([dumpedAt])
  @@map("posthog_events")
}

// Append-only history of PostHog person properties. Free tier overwrites the
// live person; a new row is written only when properties change (propertiesHash),
// so this is a compact timeline of how each person evolved.
model PosthogPersonSnapshot {
  id             String   @id @default(cuid())
  projectKey     String
  personId       String                  // PostHog person uuid (persons.id) — joins PosthogEvent.personId
  distinctIds    String[]                // all distinct_ids merged into this person
  propertiesHash String                  // change-detection key
  properties     Json
  capturedAt     DateTime @default(now())

  @@index([personId, capturedAt])
  @@index([projectKey, capturedAt])
  @@map("posthog_person_snapshots")
}

// Append-only history of cohort definitions + membership counts. New row only
// when count or filters change (stateHash).
model PosthogCohortSnapshot {
  id          String   @id @default(cuid())
  projectKey  String
  cohortId    Int
  name        String
  count       Int?
  isStatic    Boolean  @default(false)
  stateHash   String                     // hash of count + filters → change-detection
  filters     Json
  capturedAt  DateTime @default(now())

  @@index([cohortId, capturedAt])
  @@index([projectKey, capturedAt])
  @@map("posthog_cohort_snapshots")
}

// ---------- Phase C: Sentry archive ----------

// Sentry issue (group) aggregate state. Upserted to the latest snapshot — the
// raw history lives in SentryEvent; the issue row is the current grouping/metadata.
model SentryIssue {
  id                  String   @id          // Sentry issue/group id
  projectKey          String                // "sase" — panel-side project key
  sentrySourceProject String?              // Sentry project slug (e.g. "python", "sase-web") — distinguishes browser vs server errors when one panel project has multiple Sentry projects
  shortId             String?
  title               String?
  culprit             String?
  level               String?
  status              String?
  type                String?
  count               Int?                  // total events as of lastSyncedAt
  userCount           Int?
  firstSeen           DateTime?
  lastSeen            DateTime?
  permalink           String?
  metadata            Json?
  ingestedAt          DateTime @default(now())
  lastSyncedAt        DateTime @default(now())

  @@index([projectKey, lastSeen])
  @@index([projectKey, sentrySourceProject, lastSeen])
  @@map("sentry_issues")
}

// Individual Sentry events (the at-risk raw occurrences; free tier prunes ~30d).
// Lossless full payload as JSONB; also cold-dumped to MinIO.
model SentryEvent {
  eventId             String   @id          // Sentry event id (32 hex)
  issueId             String?               // parent issue/group id
  projectKey          String                // panel-side project key (e.g. "sase")
  sentrySourceProject String?              // Sentry project slug the event came from ("python", "sase-web")
  title               String?
  message             String?
  level               String?
  platform            String?
  timestamp           DateTime               // event occurrence time
  tags                Json?
  payload             Json                   // full event payload — lossless
  ingestedAt          DateTime @default(now())
  dumpedAt            DateTime?

  @@index([projectKey, timestamp])
  @@index([projectKey, sentrySourceProject, timestamp])
  @@index([issueId, timestamp])
  @@index([dumpedAt])
  @@map("sentry_events")
}