HealthLog/prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

// ─── User & Auth ───────────────────────────────────────────

model User {
  id        String   @id @default(cuid())
  username  String   @unique
  email     String?  @unique
  passwordHash String?  @map("password_hash") // argon2id, optional fallback
  role      String   @default("USER") // "ADMIN" | "USER"
  createdAt    DateTime @default(now()) @map("created_at")
  updatedAt    DateTime @updatedAt @map("updated_at")

  // Profile
  heightCm    Float?    @map("height_cm")
  dateOfBirth DateTime? @map("date_of_birth")
  gender      String?   @map("gender") // "MALE" | "FEMALE" | null = keine Angabe
  timezone    String    @default("Europe/Berlin")

  // Codex OAuth (ChatGPT subscription)
  codexAccessTokenEncrypted   String?   @map("codex_access_token_encrypted")
  codexRefreshTokenEncrypted  String?   @map("codex_refresh_token_encrypted")
  codexTokenExpiresAt         DateTime? @map("codex_token_expires_at")
  codexConnectedAt            DateTime? @map("codex_connected_at")
  codexConnectionStatus       String    @default("disconnected") @map("codex_connection_status")
  insightsPrivacyMode String   @default("aggregated") @map("insights_privacy_mode") // "aggregated" | "raw"
  insightsCachedAt    DateTime? @map("insights_cached_at")
  insightsCachedText  String?  @map("insights_cached_text") // cached JSON output

  // Telegram notifications (per-user)
  telegramBotToken  String?  @map("telegram_bot_token")   // AES-256-GCM encrypted
  telegramChatId    String?  @map("telegram_chat_id")
  telegramEnabled   Boolean  @default(false) @map("telegram_enabled")

  // Withings OAuth credentials (per-user, encrypted)
  withingsClientIdEncrypted     String? @map("withings_client_id_encrypted")
  withingsClientSecretEncrypted String? @map("withings_client_secret_encrypted")

  // moodLog integration (per-user, encrypted)
  moodLogUrlEncrypted    String? @map("mood_log_url_encrypted")
  moodLogApiKeyEncrypted String? @map("mood_log_api_key_encrypted")
  moodLogEnabled         Boolean @default(false) @map("mood_log_enabled")
  moodLogLastSyncedAt    DateTime? @map("mood_log_last_synced_at")
  moodLogWebhookSecret   String? @map("mood_log_webhook_secret")

  // Locale (null = use system/admin default)
  locale    String?  @map("locale") // "de" | "en" | null

  // Personalization
  // Per-user overrides for computed default thresholds. Shape:
  //   { "WEIGHT": { "min": 70, "max": 85 },
  //     "BLOOD_PRESSURE_SYS": { "min": 110, "max": 130 }, ... }
  // Null or missing metric → use computed default.
  thresholdsJson Json? @map("thresholds_json")
  // Per-user dashboard layout — widget visibility + ordering.
  // Shape: { "widgets": [{ "id": "weight", "visible": true, "order": 0 }, ...], "version": 1 }
  dashboardWidgetsJson Json? @map("dashboard_widgets_json")
  // Blood-glucose display unit preference. Canonical storage is mg/dL.
  glucoseUnit String? @map("glucose_unit") // "mg/dL" | "mmol/L" | null = default "mg/dL"
  // AI provider selection for insights. Null = OPENAI fallback.
  aiProvider String? @map("ai_provider") // "OPENAI" | "ANTHROPIC" | "LOCAL" | "CHATGPT_OAUTH"
  aiModel String? @map("ai_model")
  aiBaseUrl String? @map("ai_base_url")
  aiAnthropicKeyEncrypted String? @map("ai_anthropic_key_encrypted")
  aiLocalKeyEncrypted String? @map("ai_local_key_encrypted")
  // v1.4.3: per-user OpenAI API key for the OPENAI provider — previously
  // the schema only stored Anthropic and Local keys, leaving OPENAI without
  // a credential surface. Encrypted with the active ENCRYPTION_KEYS entry
  // before storage.
  aiOpenaiKeyEncrypted String? @map("ai_openai_key_encrypted")

  // v1.4.16 phase B5b — ordered fallback chain for multi-provider AI
  // redundancy. Each entry is `{ providerType, priority, enabled }`,
  // sorted by ascending priority at runtime. Credentials live in the
  // dedicated encrypted columns (codex* / aiAnthropicKeyEncrypted /
  // aiLocalKeyEncrypted / aiOpenaiKeyEncrypted) — the chain just
  // enumerates which providers to try and in what order. Null = legacy
  // behaviour (single-provider resolution via `aiProvider`).
  aiProviderChain Json? @map("ai_provider_chain")

  // Onboarding
  onboardingCompletedAt DateTime? @map("onboarding_completed_at")
  // v1.4.15 Phase B5: tracks whether the user has finished (or
  // explicitly dismissed) the spotlight tour that overlays the
  // dashboard on first arrival. Distinct from the wizard at
  // `/onboarding` (which is gated by `onboardingCompletedAt`); the
  // tour runs *after* the wizard, on the dashboard itself, walking
  // the user through the tile strip, quick-add menu, insights,
  // integrations, and achievements. Default false; flipped to true
  // on tour completion or dismiss; users can replay the tour from
  // Settings → Account.
  onboardingTourCompleted Boolean @default(false) @map("onboarding_tour_completed")

  // HealthKit (iOS) integration config — list of per-metric direction toggles.
  // Shape: { entries: [{ id, kind, direction, enabled }], lastSyncedAt }
  healthKitConfigJson Json?     @map("healthkit_config_json")
  healthKitLastSyncedAt DateTime? @map("healthkit_last_synced_at")

  // Display name (optional override of username for greetings).
  displayName String? @map("display_name")

  // Doctor-report personalization (v1.4.15 phase B6).
  // Persisted between report exports so the dialog can pre-fill the last-used
  // practice / clinic name. Free-text, hard-capped to 120 chars upstream by
  // `sanitisePracticeName()`. Null = user has never set one.
  lastReportPracticeName String? @map("last_report_practice_name")

  // Relations
  passkeys    Passkey[]
  sessions    Session[]
  challenges  AuthChallenge[]
  measurements Measurement[]
  medications  Medication[]
  intakeEvents MedicationIntakeEvent[]
  apiTokens   ApiToken[]
  refreshTokens RefreshToken[]
  auditLogs   AuditLog[]
  withingsConnection WithingsConnection?
  moodEntries  MoodEntry[]
  notificationChannels NotificationChannel[]
  pushSubscriptions    PushSubscription[]
  dataBackups          DataBackup[]
  achievements         UserAchievement[]
  feedback             Feedback[]
  recommendationFeedback RecommendationFeedback[]
  idempotencyKeys      IdempotencyKey[]
  devices              Device[]
  integrationStatuses  IntegrationStatus[]
  coachConversations   CoachConversation[]
  coachUsage           CoachUsage[]

  @@map("users")
}

model Passkey {
  id                  String   @id @default(cuid())
  userId              String   @map("user_id")
  name                String   @default("Passkey")
  credentialId        String   @unique @map("credential_id")
  credentialPublicKey Bytes    @map("credential_public_key")
  counter             BigInt   @default(0)
  credentialDeviceType String  @map("credential_device_type")
  credentialBackedUp  Boolean  @default(false) @map("credential_backed_up")
  transports          String[] // e.g. ["usb", "ble", "nfc", "internal"]
  createdAt           DateTime @default(now()) @map("created_at")

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

  @@map("passkeys")
}

model Session {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")
  ipAddress String?  @map("ip_address")
  userAgent String?  @map("user_agent")

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

  @@index([userId])
  @@map("sessions")
}

model AuthChallenge {
  id        String   @id @default(cuid())
  userId    String?  @map("user_id") // null for registration
  challenge String
  type      String   // "registration" | "authentication"
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")

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

  @@index([userId])
  @@map("auth_challenges")
}

// ─── Measurements ──────────────────────────────────────────

enum MeasurementType {
  WEIGHT
  BLOOD_PRESSURE_SYS
  BLOOD_PRESSURE_DIA
  PULSE
  BODY_FAT
  SLEEP_DURATION
  ACTIVITY_STEPS
  BLOOD_GLUCOSE // Canonical storage: mg/dL. Display unit (mg/dL or mmol/L) is user preference.
  TOTAL_BODY_WATER // Canonical storage: kilograms (kg of water). Matches Withings' hydration measure (type 77) and Health Connect's TotalBodyWaterRecord. Display can compute % from same-day weight if desired.
  BONE_MASS // Canonical storage: kilograms (kg). Matches Withings' bone mass measure (type 88).
  OXYGEN_SATURATION // Canonical storage: percent (0..100). Withings ScanWatch reports type 54. Health Connect's OxygenSaturationRecord uses Percentage (already 0..100). HealthKit uses HKQuantityTypeIdentifierOxygenSaturation as a fraction (0..1) — iOS DTO must multiply by 100 before submitting.

  @@map("measurement_type")
}

/// Context for glucose measurements — affects which target range applies.
/// NULL for non-glucose measurements.
enum GlucoseContext {
  FASTING      // ≥8h without food, typically morning
  POSTPRANDIAL // 2h after meal
  RANDOM       // any time, non-fasting, non-post-meal
  BEDTIME      // before sleep

  @@map("glucose_context")
}

enum MeasurementSource {
  MANUAL
  WITHINGS
  IMPORT

  @@map("measurement_source")
}

model Measurement {
  id         String            @id @default(cuid())
  userId     String            @map("user_id")
  type       MeasurementType
  value      Float
  unit       String            // kg, mmHg, bpm, %, hours, steps, mg/dL
  source     MeasurementSource @default(MANUAL)
  measuredAt DateTime          @map("measured_at")
  notes      String?           // encrypted at app level if sensitive
  externalId String?           @map("external_id") // Withings grpid for dedup
  // Only set for BLOOD_GLUCOSE measurements. NULL for all others.
  glucoseContext GlucoseContext? @map("glucose_context")
  createdAt  DateTime          @default(now()) @map("created_at")
  updatedAt  DateTime          @updatedAt @map("updated_at")

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

  @@unique([userId, type, measuredAt, source])
  @@index([userId, type, measuredAt])
  @@index([externalId])
  @@map("measurements")
}

// ─── Medications ───────────────────────────────────────────

model Medication {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  name      String
  dose      String   // e.g. "10mg"
  active    Boolean  @default(true)
  notificationsEnabled Boolean @default(true) @map("notifications_enabled")
  pausedAt     DateTime? @map("paused_at")
  snoozedUntil DateTime? @map("snoozed_until")
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  schedules    MedicationSchedule[]
  intakeEvents MedicationIntakeEvent[]
  phaseConfig        ReminderPhaseConfig?
  telegramMessages   TelegramReminderMessage[]

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

  @@index([userId])
  @@map("medications")
}

model MedicationSchedule {
  id           String  @id @default(cuid())
  medicationId String  @map("medication_id")
  windowStart  String  // HH:mm format, e.g. "08:00"
  windowEnd    String  // HH:mm format, e.g. "10:00"
  label        String? // e.g. "Morning", "Evening"
  dose         String? // Per-schedule dose override; null = use Medication.dose
  daysOfWeek   String? @map("days_of_week") // Comma-separated day numbers (0=Sun..6=Sat), null = daily

  medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)
  telegramMessages TelegramReminderMessage[]

  @@map("medication_schedules")
}

enum IntakeSource {
  WEB
  API
  REMINDER
  IMPORT

  @@map("intake_source")
}

enum ReminderPhase {
  GREEN
  YELLOW
  ORANGE
  RED

  @@map("reminder_phase")
}

enum PhaseMode {
  MINUTES
  PERCENT

  @@map("phase_mode")
}

model MedicationIntakeEvent {
  id             String       @id @default(cuid())
  userId         String       @map("user_id")
  medicationId   String       @map("medication_id")
  scheduledFor   DateTime     @map("scheduled_for")
  takenAt        DateTime?    @map("taken_at")
  skipped        Boolean      @default(false)
  source         IntakeSource @default(WEB)
  idempotencyKey String?      @unique @map("idempotency_key")
  createdAt      DateTime     @default(now()) @map("created_at")

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

  @@index([userId, medicationId, scheduledFor])
  @@map("medication_intake_events")
}

model ReminderPhaseConfig {
  id           String    @id @default(cuid())
  medicationId String    @unique @map("medication_id")
  greenValue   Int       @default(60) @map("green_value")
  greenMode    PhaseMode @default(MINUTES) @map("green_mode")
  yellowValue  Int       @default(30) @map("yellow_value")
  yellowMode   PhaseMode @default(MINUTES) @map("yellow_mode")
  orangeValue  Int       @default(0) @map("orange_value")
  orangeMode   PhaseMode @default(MINUTES) @map("orange_mode")
  redValue     Int       @default(240) @map("red_value")
  redMode      PhaseMode @default(MINUTES) @map("red_mode")

  medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)

  @@map("reminder_phase_configs")
}

model TelegramReminderMessage {
  id           String        @id @default(cuid())
  medicationId String        @map("medication_id")
  scheduleId   String        @map("schedule_id")
  chatId       String        @map("chat_id")
  messageId    Int           @map("message_id")
  phase        ReminderPhase
  sentAt       DateTime      @default(now()) @map("sent_at")
  date         String        // YYYY-MM-DD for day grouping

  medication Medication         @relation(fields: [medicationId], references: [id], onDelete: Cascade)
  schedule   MedicationSchedule @relation(fields: [scheduleId], references: [id], onDelete: Cascade)

  @@unique([medicationId, scheduleId, date, phase])
  @@index([medicationId, date])
  @@map("telegram_reminder_messages")
}

model TelegramScheduledDeletion {
  id          String   @id @default(cuid())
  userId      String   @map("user_id")
  chatId      String   @map("chat_id")
  messageId   Int      @map("message_id")
  deleteAfter DateTime @map("delete_after")

  @@index([deleteAfter])
  @@map("telegram_scheduled_deletions")
}

// ─── API Tokens (for external ingest) ──────────────────────

model ApiToken {
  id          String   @id @default(cuid())
  userId      String   @map("user_id")
  name        String
  tokenHash   String   @unique @map("token_hash") // SHA-256 of the token
  permissions String[] @default(["medication:ingest"])
  lastUsedAt  DateTime? @map("last_used_at")
  expiresAt   DateTime? @map("expires_at")
  createdAt   DateTime @default(now()) @map("created_at")
  revoked     Boolean  @default(false)

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

  @@index([userId])
  @@map("api_tokens")
}

// ─── Refresh Tokens (native client short-lived access + rotating refresh) ─

model RefreshToken {
  id          String    @id @default(cuid())
  userId      String    @map("user_id")
  tokenHash   String    @unique @map("token_hash")
  /// Optional device fingerprint — links the refresh token to a specific
  /// `Device` row when the caller advertised one (X-Device-Id header). Used
  /// to revoke all tokens issued for a stolen/lost device.
  deviceId    String?   @map("device_id")
  /// HMAC-SHA-256 (matching `ApiToken.tokenHash` format, keyed by
  /// `API_TOKEN_HMAC_KEY`) of the access token issued in the same response.
  /// When the refresh token is rotated we revoke the matching access token
  /// via `ApiToken.tokenHash`. Null if the access token was issued
  /// out-of-band.
  accessTokenHash String? @map("access_token_hash")
  expiresAt   DateTime  @map("expires_at")
  createdAt   DateTime  @default(now()) @map("created_at")
  revokedAt   DateTime? @map("revoked_at")
  /// When this token was used (one-time-use). Set during rotation; a second
  /// use of the same token is a hard fail and triggers reuse-detection.
  usedAt      DateTime? @map("used_at")
  /// Pointer to the row that replaced this one during rotation. Helps audit
  /// trail and reuse-detection ("this token was already exchanged into X").
  replacedById String? @map("replaced_by_id")
  userAgent   String?  @map("user_agent")
  ipAddress   String?  @map("ip_address")

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

  @@index([userId])
  /// Composite index — every reuse-detection / device-scoped lookup filters
  /// on (userId, deviceId), so a single-column device_id index would never
  /// be used while costing writes. Multiple live rows per (userId, deviceId)
  /// are intentional: rotated rows linger in `revokedAt != null` state for
  /// audit and reuse-detection.
  @@index([userId, deviceId])
  @@index([expiresAt])
  @@map("refresh_tokens")
}

// ─── Withings ──────────────────────────────────────────────

model WithingsConnection {
  id             String   @id @default(cuid())
  userId         String   @unique @map("user_id")
  withingsUserId String   @map("withings_user_id")
  accessToken    String   @map("access_token")  // encrypted at app level
  refreshToken   String   @map("refresh_token") // encrypted at app level
  tokenExpiresAt DateTime @map("token_expires_at")
  lastSyncedAt   DateTime? @map("last_synced_at")
  createdAt      DateTime @default(now()) @map("created_at")
  updatedAt      DateTime @updatedAt @map("updated_at")

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

  @@map("withings_connections")
}

// ─── Mood Entries (moodLog) ───────────────────────────────────

model MoodEntry {
  id           String   @id @default(cuid())
  userId       String   @map("user_id")
  date         String   // YYYY-MM-DD
  mood         String   // SUPER_GUT, GUT, OKAY, SCHLECHT, LAUSIG
  score        Int      // 1-5
  tags         String?  // JSON array of tag keys
  source       String   @default("MOODLOG") // WEB, TELEGRAM, DAYLIO
  moodLoggedAt DateTime @map("mood_logged_at")
  syncedAt     DateTime @default(now()) @map("synced_at")
  createdAt    DateTime @default(now()) @map("created_at")
  updatedAt    DateTime @updatedAt @map("updated_at")

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

  @@unique([userId, date, moodLoggedAt])
  @@index([userId, date])
  @@map("mood_entries")
}

// ─── Integration Status (v1.4.15) ─────────────────────────
//
// Per-(user, integration) row that captures the most recent sync attempt
// outcome so the Settings → Integrations UI can show:
//   - state ("connected", "error_reauth", "error_transient", "disconnected")
//   - last successful sync timestamp
//   - last attempted sync timestamp
//   - last error message (encrypted at rest if non-null — see crypto.ts)
//   - consecutive failure counter (resets on success)
//
// `recordSyncSuccess` / `recordSyncFailure` helpers in
// `src/lib/integrations/status.ts` are the only writers. The
// `consecutiveFailures` counter feeds the admin-Telegram alerting
// threshold (default 3). State `error_reauth` parks scheduled syncs
// until the user reconnects (the sync entry-points consult this row
// before doing any external work).
//
// `integration` values are bare strings ("withings", "moodlog") rather
// than an enum so future integrations don't require a migration.
model IntegrationStatus {
  id                   String   @id @default(cuid())
  userId               String   @map("user_id")
  integration          String   // "withings" | "moodlog"
  state                String   @default("connected") // "connected" | "error_reauth" | "error_transient" | "disconnected"
  lastSuccessAt        DateTime? @map("last_success_at")
  lastAttemptAt        DateTime? @map("last_attempt_at")
  lastError            String?  @map("last_error") // AES-256-GCM encrypted (may contain token-fragment metadata)
  consecutiveFailures  Int      @default(0) @map("consecutive_failures")
  alertedAt            DateTime? @map("alerted_at") // last time admin Telegram alert fired (idempotency guard)
  createdAt            DateTime @default(now()) @map("created_at")
  updatedAt            DateTime @updatedAt @map("updated_at")

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

  @@unique([userId, integration])
  @@index([userId])
  @@index([state])
  @@map("integration_statuses")
}

// ─── App Settings ─────────────────────────────────────────

model AppSettings {
  id                  String  @id @default("singleton")
  registrationEnabled Boolean @default(true) @map("registration_enabled")
  defaultLocale       String  @default("de") @map("default_locale") // "de" | "en"
  telegramGlobal      Boolean @default(true) @map("telegram_global") // global toggle
  ntfyGlobal          Boolean @default(true) @map("ntfy_global")     // global toggle
  webPushGlobal       Boolean @default(true) @map("web_push_global") // global toggle
  webPushVapidPublicKey String? @map("web_push_vapid_public_key")
  webPushVapidPrivateKeyEncrypted String? @map("web_push_vapid_private_key_encrypted")
  webPushVapidSubject String? @map("web_push_vapid_subject")
  apiGlobal           Boolean @default(true) @map("api_global")      // global toggle
  moodLogGlobal       Boolean @default(true) @map("mood_log_global")
  umamiEnabled        Boolean @default(false) @map("umami_enabled")
  umamiScriptUrl      String? @map("umami_script_url")
  umamiWebsiteId      String? @map("umami_website_id")
  glitchtipEnabled    Boolean @default(false) @map("glitchtip_enabled")
  glitchtipDsn        String? @map("glitchtip_dsn")
  glitchtipEnvironment String? @map("glitchtip_environment")
  githubIssueTokenEncrypted String? @map("github_issue_token_encrypted")
  githubIssueRepo     String? @map("github_issue_repo")
  // v1.4.3: explicit toggle so an admin can hide the bug-report
  // surface without nuking the token+repo config. Default true so
  // existing instances with credentials don't accidentally lose
  // the feature on upgrade.
  bugReportEnabled    Boolean @default(true) @map("bug_report_enabled")

  // Medication reminder thresholds (minutes after windowEnd)
  reminderLateMinutes     Int @default(120) @map("reminder_late_minutes")
  reminderMissedMinutes   Int @default(240) @map("reminder_missed_minutes")

  // Admin AI Provider (fallback for users without Codex OAuth)
  adminAiKeyEncrypted   String? @map("admin_ai_key_encrypted")
  adminAiModel          String  @default("gpt-4o-mini") @map("admin_ai_model")
  adminAiBaseUrl        String  @default("https://api.openai.com/v1") @map("admin_ai_base_url")

  // v1.4.16 phase B5e — daily-aggregated rec-feedback summary written
  // by `feedback-aggregator.ts`. Shape:
  //   {
  //     "generatedAt": "2026-05-09T04:00:00.000Z",
  //     "windowDays": 30,
  //     "buckets": [
  //       { "severity": "important", "metricSourceType": "bloodPressure",
  //         "providerType": "codex", "promptVersion": "4.16.0",
  //         "helpful": 12, "notHelpful": 3, "total": 15, "helpfulRate": 0.8 }
  //     ]
  //   }
  // Null = aggregator hasn't run yet OR there's no feedback in the window.
  adminAiInsightsFeedbackSummary Json? @map("admin_ai_insights_feedback_summary")

  @@map("app_settings")
}

// ─── Built-in Feedback ───────────────────────────────────────

enum FeedbackCategory {
  BUG
  FEATURE_REQUEST
  QUESTION
  OTHER

  @@map("feedback_category")
}

enum FeedbackStatus {
  OPEN
  ACKNOWLEDGED
  RESOLVED
  ARCHIVED

  @@map("feedback_status")
}

/// Stores user-submitted feedback without requiring external GitHub config.
/// Admin inbox lives at /admin#feedback; optional escalation publishes to a
/// GitHub issue reusing the existing integration when configured.
model Feedback {
  id             String           @id @default(cuid())
  userId         String?          @map("user_id") // Null after user deletion; email snapshot remains.
  email          String?          // Snapshot at submission time for triage continuity.
  category       FeedbackCategory @default(BUG)
  subject        String
  description    String
  screenshotBase64 String?        @map("screenshot_base64") // Optional, <5MB, PNG/JPG.
  metadata       Json?            // URL, userAgent, locale, appVersion, …
  status         FeedbackStatus   @default(OPEN)
  adminNote      String?          @map("admin_note")
  gitHubIssueUrl String?          @map("github_issue_url")
  notifiedEmail  Boolean          @default(false) @map("notified_email")
  createdAt      DateTime         @default(now()) @map("created_at")
  updatedAt      DateTime         @updatedAt @map("updated_at")

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

  @@index([status, createdAt])
  @@index([userId])
  @@map("feedback")
}

// ─── Audit Log ─────────────────────────────────────────────

model AuditLog {
  id        String   @id @default(cuid())
  userId    String?  @map("user_id")
  action    String   // e.g. "auth.login", "measurement.create", "export.csv"
  details   String?  // JSON string, no sensitive data
  ipAddress String?  @map("ip_address")
  location  String?  // e.g. "Berlin, DE" — resolved from IP at log time
  createdAt DateTime @default(now()) @map("created_at")

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

  @@index([userId, createdAt])
  @@index([action, createdAt])
  @@map("audit_logs")
}

// ─── Notification System ──────────────────────────────────────

model NotificationChannel {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  type      String   // "TELEGRAM" | "NTFY" | "WEB_PUSH"
  enabled   Boolean  @default(true)
  config    String   // AES-256-GCM encrypted JSON (channel-specific config)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  // ── v1.4.15 Phase B3: reliability fields ─────────────────────
  // Reason the channel was auto-disabled (e.g. "hard_reject_410",
  // "telegram_blocked_by_user", "telegram_chat_not_found",
  // "give_up_after_5_failures"). NULL when the channel is healthy
  // OR was disabled manually (in which case `enabled=false` alone
  // is enough — no automated process should treat that as
  // re-enable-able).
  disabledReason       String?   @map("disabled_reason")
  consecutiveFailures  Int       @default(0) @map("consecutive_failures")
  lastSuccessAt        DateTime? @map("last_success_at")
  lastFailureAt        DateTime? @map("last_failure_at")
  lastFailureReason    String?   @map("last_failure_reason")
  // The next time the dispatcher is allowed to attempt a send for
  // this channel after a transient failure. NULL = no cooldown.
  // Hard rejects flip `enabled=false` instead of writing this.
  nextRetryAt          DateTime? @map("next_retry_at")

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

  @@unique([userId, type])
  @@index([userId])
  @@map("notification_channels")
}

model NotificationPreference {
  id        String  @id @default(cuid())
  channelId String  @map("channel_id")
  eventType String  @map("event_type") // "MEDICATION_REMINDER" | "MEASUREMENT_ANOMALY" | etc.
  enabled   Boolean @default(true)

  channel NotificationChannel @relation(fields: [channelId], references: [id], onDelete: Cascade)

  @@unique([channelId, eventType])
  @@map("notification_preferences")
}

model PushSubscription {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  endpoint  String
  p256dh    String   // encrypted
  auth      String   // encrypted
  userAgent String?  @map("user_agent")
  createdAt DateTime @default(now()) @map("created_at")

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

  @@unique([userId, endpoint])
  @@index([userId])
  @@map("push_subscriptions")
}

// ─── Data Backups ────────────────────────────────────────────

model DataBackup {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  type      String   @default("WEEKLY_AUTO") // WEEKLY_AUTO, MANUAL
  data      String   // JSON export blob
  createdAt DateTime @default(now()) @map("created_at")

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

  @@unique([userId, type], map: "data_backups_user_id_type_key")
  @@index([userId])
  @@map("data_backups")
}

// ─── Rate Limiting ──────────────────────────────────────────

model RateLimit {
  key     String   @id
  count   Int      @default(1)
  resetAt DateTime @map("reset_at")

  @@index([resetAt])
  @@map("rate_limits")
}

// ─── Idempotency Keys (mobile-client replay protection) ──

/// Caches the response of a POST/PUT/PATCH/DELETE request keyed by
/// (userId, key, method, path) so retries within the TTL replay the
/// original outcome instead of producing a duplicate side-effect.
model IdempotencyKey {
  id             String   @id @default(cuid())
  userId         String   @map("user_id")
  key            String
  method         String
  path           String
  responseStatus Int      @map("response_status")
  responseBody   String   @map("response_body") // serialized JSON envelope
  expiresAt      DateTime @map("expires_at")
  createdAt      DateTime @default(now()) @map("created_at")

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

  @@unique([userId, key, method, path])
  @@index([expiresAt])
  @@map("idempotency_keys")
}

// ─── Devices (APNs / mobile push targets) ────────────────

/// Registered native-client device for push delivery. Token is unique
/// across users; re-registering on a different account moves ownership.
model Device {
  id          String   @id @default(cuid())
  userId      String   @map("user_id")
  platform    String   // "ios"
  token       String   @unique
  bundleId    String   @map("bundle_id")
  locale      String?
  appVersion  String?  @map("app_version")
  model       String?
  lastSeen    DateTime @default(now()) @map("last_seen")
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

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

  @@index([userId])
  @@map("devices")
}

// ─── User Achievements ───────────────────────────────────────

model UserAchievement {
  id            String   @id @default(cuid())
  userId        String   @map("user_id")
  achievementId String   @map("achievement_id")
  unlockedAt    DateTime @map("unlocked_at")
  createdAt     DateTime @default(now()) @map("created_at")

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

  @@unique([userId, achievementId])
  @@index([userId])
  @@map("user_achievements")
}

// ─── Host Metrics (admin system-status host-load chart) ──────

/// Per-minute snapshot of host-process load + memory captured by the
/// pg-boss `host-metric-sample` job. Read by `/api/admin/host-metrics`
/// to render the host-load trend above the system-status section.
/// 7-day retention is enforced inside the sampler — we keep the table
/// small enough that a single `findMany` over the last 2 hours stays
/// well under 200 rows.
model HostMetric {
  id             String   @id @default(cuid())
  capturedAt     DateTime @default(now()) @map("captured_at")
  loadAvg1       Float    @map("load_avg_1")
  loadAvg5       Float    @map("load_avg_5")
  loadAvg15      Float    @map("load_avg_15")
  memUsedBytes   BigInt   @map("mem_used_bytes")
  memTotalBytes  BigInt   @map("mem_total_bytes")
  // Optional rolling-window byte counters from /proc/diskstats. Null
  // outside Linux (macOS dev machines, Windows). The API endpoint
  // converts deltas into bytes-per-second client-side.
  diskReadBytes  BigInt?  @map("disk_read_bytes")
  diskWriteBytes BigInt?  @map("disk_write_bytes")

  @@index([capturedAt])
  @@map("host_metrics")
}

// ─── Recommendation Feedback (v1.4.16 phase B5e) ───────────────
//
// Per-recommendation thumbs-up / thumbs-down rating. Captured client-
// side under each rec card; persisted with provider attribution
// (providerType + promptVersion) so the daily aggregator can slice
// helpful-rate per (severity x provider x prompt) for the admin AI
// quality dashboard.
//
// Default-on for the single-user case (only the user's ratings shape
// the user's future prompts); cross-user aggregate stays admin-only
// (no prompt-tuning flow yet — that ratchet is v1.4.17).
//
// Privacy: server-side fills providerType + promptVersion from the
// user's most recent cached payload (User.insightsCachedText) so the
// client can't tamper with the attribution.
//
// `(userId, recommendationId, recommendationText)` is the dedup key.
// recommendationText snapshots the rec at submission time so a re-
// generation that rewrites the same id with new text counts as a
// different row — that's intentional, the aggregator wants per-text
// signal not per-id signal.
model RecommendationFeedback {
  id                     String   @id @default(cuid())
  userId                 String   @map("user_id")
  recommendationId       String   @map("recommendation_id")
  recommendationText     String   @map("recommendation_text")
  recommendationSeverity String   @map("recommendation_severity")
  metricSourceType       String   @map("metric_source_type")
  metricSourceTimeRange  String   @map("metric_source_time_range")
  helpful                Boolean
  providerType           String   @map("provider_type")
  promptVersion          String   @map("prompt_version")
  createdAt              DateTime @default(now()) @map("created_at")

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

  @@unique([userId, recommendationId, recommendationText], map: "rec_feedback_user_rec_text_key")
  @@index([userId, createdAt])
  @@index([recommendationSeverity, helpful, createdAt])
  @@map("recommendation_feedback")
}

// ─── AI Coach (v1.4.20) ────────────────────────────────────
//
// Conversation thread between a user and the AI Coach. Messages are
// stored encrypted at rest via AES-256-GCM (`src/lib/crypto.ts`) under
// the active `ENCRYPTION_KEYS` entry — the Coach inherits the same
// key-rotation tooling as the rest of the app. GDPR cascade through
// User keeps the model deletion-clean.
//
// `title` is a short snippet of the first user message (≤80 chars,
// non-encrypted) so the history rail can render without paying the
// per-message decrypt cost. The snippet is plain text; do not put PII
// the user explicitly opted out of into it. The summarisation helper
// in `src/lib/ai/coach/persistence.ts` truncates with an ellipsis.
model CoachConversation {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  title     String
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

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

  @@index([userId, updatedAt])
  @@map("coach_conversations")
}

// One message in a Coach conversation. `role` is "user" or "assistant"
// — same shape as the OpenAI/Anthropic chat-completion APIs.
//
// `encryptedContent` holds the AES-256-GCM ciphertext of the message
// body. The encrypt/decrypt helpers stamp the active key id into the
// ciphertext so rotation works without a backfill.
//
// `metricSourceJson` is the provenance envelope ("based on 14d HRV +
// 6 BP readings") attached to assistant messages — labels only, NEVER
// raw values. The frontend renders source-chip rows from this field.
// It is intentionally NOT encrypted: the labels are window+metric
// names ("last30days", "bloodPressure", "n=12") and carry no PII; the
// stable shape lets future analytics queries slice without a key.
//
// `providerType` and `promptVersion` mirror the recommendation-
// feedback attribution columns so the cross-feature aggregator can
// slice helpful-rate per (provider × prompt) for Coach replies too.
model CoachMessage {
  id               String   @id @default(cuid())
  conversationId   String   @map("conversation_id")
  role             String   // "user" | "assistant"
  encryptedContent Bytes    @map("encrypted_content")
  metricSourceJson String?  @map("metric_source_json")
  providerType     String?  @map("provider_type")
  promptVersion    String?  @map("prompt_version")
  createdAt        DateTime @default(now()) @map("created_at")

  conversation CoachConversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)

  @@index([conversationId, createdAt])
  @@map("coach_messages")
}

// Per-user, per-day token-spend ledger for the AI Coach. The dispatcher
// reads `totalTokens` against `MAX_TOKENS_PER_USER_PER_DAY` before
// hitting any provider, so a runaway client cannot drain the operator's
// LLM budget. `dateKey` is the UTC `YYYY-MM-DD` bucket; the unique
// constraint enforces one row per (user, day). `messageCount` is a
// secondary signal used by the frontend to render a "≈ N replies left"
// hint without exposing raw token figures.
model CoachUsage {
  id           String   @id @default(cuid())
  userId       String   @map("user_id")
  dateKey      String   @map("date_key")
  totalTokens  Int      @default(0) @map("total_tokens")
  messageCount Int      @default(0) @map("message_count")
  updatedAt    DateTime @updatedAt @map("updated_at")
  createdAt    DateTime @default(now()) @map("created_at")

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

  @@unique([userId, dateKey])
  @@index([userId, dateKey])
  @@map("coach_usage")
}