tom/HealthLog
1
0 Fork 0
mirror of https://github.com/MBombeck/HealthLog.git synced 2026-11-08 12:32:43 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
v1.9.2
HealthLog/prisma/schema.prisma
T
Marc-André Bombeck 0a8c6e307c feat(medications): add optional ATC + RxNorm codes to the medication row 
Add two nullable, additive columns to `Medication` (`atc_code`,
`rxnorm_code`) so a user or clinician can assert a drug-classification
code that the health-record FHIR export emits on the medication concept.
Codes are taken verbatim, never machine-guessed: a wrong code is a
clinical-safety problem, an absent one is merely a (conformant)
text-only concept.

- Migration 0103 — additive, nullable, no backfill. Pre-existing rows
  read NULL, which the exporter already treats as "no code".
- Validate on write — ATC `^[A-Z]\d{2}[A-Z]{2}\d{2}$`, RxCUI `^\d+$`;
  422 on a malformed value. Wired field-by-field through the create +
  update routes (no mass assignment); `null` clears a code on update.
- Surface both codes in the medication advanced-settings sheet's Data
  group with a self-saving input, and echo them on the GET/POST/PUT
  response shape. OpenAPI regenerated.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
(cherry picked from commit 2e0051d42826fa3f043c32e78a34dab7c8050165)
2026-06-02 16:45:23 +02:00

2465 lines
120 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
// v1.4.34 web-freeze. Schema is locked additive-only until iOS launch
// + v1.5.0 marker. See .planning/v15-strategic-plan.md §2.
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
// v1.4.36 W3 T3 — per-user AI Insights exclude-metrics list, mirrors
// `coachPrefsJson.excludeMetrics`. The /api/insights/generate route
// filters matching keys off `features` before JSON.stringify so the
// model never sees data the user opted out of. Default `[]` = legacy
// behaviour for every existing row.
insightsExcludeMetrics String[] @default([]) @map("insights_exclude_metrics")
// v1.8.5 — user-level injection-site deny-list. Sites listed here are
// never offered for ANY medication's injection-site picker and the
// intake write path 422s a submitted site that lands on this list,
// even when the per-medication `allowedInjectionSites` lists it as
// preferred (deny always wins). Default `[]` = no global exclusion.
globalExcludedInjectionSites InjectionSite[] @default([]) @map("global_excluded_injection_sites")
// 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")
// Per-user insights tile layout — tile visibility + ordering for the
// `/insights` surface. Mirrors `dashboardWidgetsJson` so the iOS
// client and the web Settings UI can persist insights tile order +
// visibility through the same shape. Null = default layout (see
// `src/lib/insights-layout.ts:DEFAULT_INSIGHTS_LAYOUT`). The GET
// endpoint never lazy-writes — the column only carries data once
// the user has explicitly saved a layout.
// Shape: { "tiles": [{ "id": "blutdruck", "visible": true, "order": 0 }, ...], "version": 1 }
insightsLayoutJson Json? @map("insights_layout_json")
// Blood-glucose display unit preference. Canonical storage is mg/dL.
glucoseUnit String? @map("glucose_unit") // "mg/dL" | "mmol/L" | null = default "mg/dL"
// v1.7.0 unit pref — global metric/imperial display preference.
// Canonical storage stays SI; this only selects the display-time
// transform branch (km/h vs mph, km vs mi). Null = default "metric".
unitPreference String? @map("unit_preference") // "metric" | "imperial" | null = default "metric"
// 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.25 W14b — last persisted onboarding wizard step.
// Encodes resume-from-where-you-left-off semantics for the rebuilt
// multi-step wizard at `/onboarding/[step]`. Values mirror the
// wizard's step indices:
// 0 = welcome
// 1 = goals
// 2 = source
// 3 = baseline
// 4 = done (triggers `onboardingCompletedAt` flip on the same
// round-trip — the column stays at 4 for completed users)
// Null = legacy pre-W14b user; the wizard treats null as 0 on mount.
onboardingStep Int @default(0) @map("onboarding_step")
// 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")
// v1.4.23 H4 — Coach drawer per-user prompt-tuning preferences.
// Shape:
// { "tone": "warm" | "neutral" | "concise",
// "verbosity": "brief" | "default" | "detailed",
// "excludeMetrics": ("bp" | "weight" | "pulse" | "mood" |
// "compliance" | "hrv" | "sleep" |
// "resting_hr" | "steps")[],
// "showEvidenceByDefault": boolean }
// Null = legacy v1.4.22 defaults (tone=warm, verbosity=default, no
// metrics excluded, evidence closed). The Coach prompt builder reads
// this column and the snapshot builder filters on `excludeMetrics`
// BEFORE the snapshot lands in the system prompt — otherwise the
// model would still see the data the user excluded.
coachPrefsJson Json? @map("coach_prefs_json")
// v1.4.25 W5e — per-user, per-metric-class source priority for
// cross-source dedup. Shape mirrors
// `src/lib/validations/source-priority.ts`:
// { "steps": ["APPLE_HEALTH", "WITHINGS", "MANUAL"],
// "weight": ["WITHINGS", "APPLE_HEALTH", "MANUAL"],
// ... }
// Null = use `DEFAULT_SOURCE_PRIORITY`. The cumulative-metric
// aggregators (steps, calories, distance, flights climbed, sleep
// duration) consult this column to pick ONE source per day when
// multiple sources report the same metric for the same day. Point
// measurements (weight, BP, pulse, body-fat, body-temp, SpO2, HRV,
// RHR, VO2 max) keep every source's row — the priority list only
// controls display preference.
//
// Today (v1.4.25) only WITHINGS + MANUAL exist, so the function
// effectively no-ops for every user. The architecture lands now so
// v1.5's Apple Health passthrough drops in without extra schema
// work.
sourcePriorityJson Json? @map("source_priority_json")
// v1.4.25 W19c — Research Mode acknowledgment for the opt-in GLP-1
// PK research-view chart. The chart is hidden behind a per-user
// flag that is set to `true` only after the user has dismissed an
// undismissable MDR-disclaimer dialog. The `researchModeAcknowledged*`
// pair records exactly *which version* of the disclaimer copy the
// user has read; when the disclaimer copy changes (e.g. EMA guidance
// update, new drug added), the Settings surface re-prompts the user
// and refuses to surface the chart until the new version is
// acknowledged. Keeping the version on the user row (instead of an
// append-only audit table) is intentional — the Coach refusal layer
// and the chart-render guard both need a cheap synchronous read.
//
// `researchModeEnabled = false` is the safe default — the chart
// never renders without an explicit opt-in. The DELETE branch of
// the API endpoint sets all three columns back to NULL/false so a
// user who turns Research Mode off must re-acknowledge the
// disclaimer if they ever turn it back on, even if the version
// hasn't changed in the interim.
researchModeEnabled Boolean @default(false) @map("research_mode_enabled")
researchModeAcknowledgedAt DateTime? @map("research_mode_acknowledged_at")
researchModeAcknowledgedVersion String? @map("research_mode_acknowledged_version")
// v1.4.30 — iOS SyncMode foundation (R-E C-2). Last-known
// synchronization checkpoint per user. iOS reads it via
// `GET /api/sync/state` to decide whether to drain its local
// SwiftData backlog before opening a fresh anchored-query window.
// NULL = never synced; the SyncMode store treats that as "force
// a full refresh on the next pair".
lastSyncedAt DateTime? @map("last_synced_at")
// v0.5.4 ios-coord — daily mood-reminder opt-in.
//
// When `true` the reminder worker dispatches a `MOOD_REMINDER`
// notification at 22:00 in the user's local timezone if no
// `MoodEntry` row exists for the local date yet. Default `false`
// because mood capture is an emotional surface — the user has to
// explicitly opt in from Settings → Notifications before the
// server starts nudging them. The dedup is handled by the
// `MoodReminderDispatch` table (one row per user + local date).
moodReminderEnabled Boolean @default(false) @map("mood_reminder_enabled")
// v1.4.25 W6c — Doctor-report section toggles.
// Shape:
// { "bp": boolean,
// "weight": boolean,
// "pulse": boolean,
// "bmi": boolean,
// "mood": boolean, // privacy-sensitive; default OFF
// "compliance": boolean,
// "sleep": boolean }
// Null = documented defaults (every section ON except mood). Mood is
// opt-in by default — the API layer drops mood entirely from the
// payload when the toggle is off so the data never leaves the DB row.
doctorReportPrefsJson Json? @map("doctor_report_prefs_json")
// v1.4.49 M-DOUBLE-REMINDER — per-user notification preferences blob.
//
// Opt-in suppression for redundant server-side pushes. Shape:
// { "medication": { "clientManaged": boolean } }
//
// When `medication.clientManaged === true`, the reminder cron skips
// the APNs `MEDICATION_REMINDER` for this user — iOS v0.6.0.7+
// schedules local SpeziScheduler banners that cover the same dose.
// The iOS app flips the flag to true on first launch after it has
// confirmed (a) APNs permission = `.authorized`, (b) local Spezi
// reminders are actively scheduled, and (c) the binary is
// >= v0.6.0.7. Default `null` (resolved as `clientManaged: false`)
// so existing installs see zero regression — server reminders flow
// unchanged until the iOS client explicitly opts the user in.
//
// Only `MEDICATION_REMINDER` is gated by this flag; MOOD_REMINDER,
// PERSONAL_RECORD, SYSTEM_ALERT, and anomaly pushes flow unchanged.
//
// Persisted via `PATCH /api/auth/me/notification-prefs` with
// deep-merge semantics so future categories (e.g. mood,
// personal_records) slot in next to `medication` without
// overwriting siblings.
notificationPrefs Json? @map("notification_prefs")
// v1.4.47 W3 — per-user Coach opt-out (privacy-leaning users).
//
// When `true`, every Coach mount point on the web client renders
// nothing — `<LayoutCoachFab>`, `<LayoutCoachMount>`, the inline
// `<CoachLaunchButton>` pill, the `/targets` page CTA, etc. The
// gate sits BELOW the operator-level `flags.coach` check: both
// must agree to render the affordance. Default `false` so the
// upgrade from v1.4.46 is invisible to anyone who hasn't toggled
// the new switch.
//
// Persistence point is the Settings → Insights `<Switch>` labelled
// "Hide Coach" / "Coach ausblenden"; the toggle writes through
// `PATCH /api/auth/me/disable-coach` and the next /api/auth/me
// round-trip surfaces the new value to every Coach gate via the
// `useAuth()` hook.
disableCoach Boolean @default(false) @map("disable_coach")
// v1.5.5 — self-hosted avatar storage.
//
// Replaces the Gravatar third-party leak (the old `getGravatarUrl`
// path hashed the email with SHA-256 and shipped the digest to
// www.gravatar.com on every authenticated page load, which let
// Automattic correlate the digest against its own table of known
// emails). The bytes live on the User row so the standard pg_dump
// backup path already covers them — no new filesystem volume to
// mount, no new Docker compose change.
//
// `avatarBytes` carries the raw image (JPEG / PNG / WebP), hard-
// capped at 2 MiB by the upload route and at 2048×2048 by the
// post-decode dimension check. `avatarContentType` carries the
// sniffed MIME (`image/jpeg`, `image/png`, `image/webp`); the
// value is never trusted from the multipart header — it is
// resolved from the file's magic bytes. `avatarUpdatedAt` is the
// cache-bust anchor: the /api/auth/me profile response appends
// the unix-ms suffix to the avatar URL so a re-upload paints
// fresh client-side without an HTTP cache flush.
//
// Null on all three columns = the user has not uploaded an
// avatar and the profile response returns `avatarUrl: null`. The
// client falls back to the username initials.
avatarBytes Bytes? @map("avatar_bytes")
avatarContentType String? @map("avatar_content_type")
avatarUpdatedAt DateTime? @map("avatar_updated_at")
// Relations
passkeys Passkey[]
sessions Session[]
challenges AuthChallenge[]
measurements Measurement[]
medications Medication[]
intakeEvents MedicationIntakeEvent[]
apiTokens ApiToken[]
refreshTokens RefreshToken[]
auditLogs AuditLog[]
withingsConnection WithingsConnection?
// v1.4.47 W6 — short-lived `(nonce → userId)` ledger backing the
// Withings OAuth state check. The connect route mints a row with a
// 10-minute TTL; the callback resolves the user via this relation
// instead of parsing the cookie value, closing the L-1 audit gap
// where the legacy `${user.id}:${nonce}` shape leaked the user id
// into request logs / network captures.
withingsOAuthStates WithingsOAuthState[]
moodEntries MoodEntry[]
notificationChannels NotificationChannel[]
pushSubscriptions PushSubscription[]
dataBackups DataBackup[]
achievements UserAchievement[]
feedback Feedback[]
recommendationFeedback RecommendationFeedback[]
idempotencyKeys IdempotencyKey[]
devices Device[]
integrationStatuses IntegrationStatus[]
coachConversations CoachConversation[]
coachUsage CoachUsage[]
// v1.4.25 W8d — workout passthrough (Apple HKWorkout / Withings
// getworkouts / future Strava). Schema lands now so the v1.5 iOS
// session can build against a stable DTO. Routes hang off Workout
// (1:1), not User — the User → WorkoutRoute path is two hops by
// design.
workouts Workout[]
// v1.4.25 W8d — personal records (schema only; detection worker
// lands in a later release). The GET /api/personal-records route
// ships with v1.4.25 so the iOS app can build its query path
// against a stable contract.
personalRecords PersonalRecord[]
// v1.4.25 W19b — per-pen / per-vial inventory entities, the
// companion to the running-sum MedicationInventoryEvent ledger.
// Carries the EMA §6.3 30-day in-use clock per opened pen.
inventoryItems MedicationInventoryItem[]
// v1.4.25 W19d — GLP-1 side-effect logbook. 21-entry × 5-category
// EMA EPAR §4.8-derived taxonomy with 1-5 Likert severity. Daily
// capture (not per-shot) so delayed-onset symptoms map to their
// actual occurrence time.
medicationSideEffects MedicationSideEffect[]
// v1.4.34 — Apple Health `export.zip` import jobs. Mirrors the
// pg-boss job state so the polling endpoint can read live per-
// phase progress without round-tripping through pg-boss internals.
importJobs ImportJob[]
// v1.5.0 — persistent measurement rollup table. Carries the
// count/min/max/mean/sd/slope/r2 stats per (type, granularity,
// bucketStart) so the analytics + comprehensive read paths bypass
// the trailing-window row walk and survive process restarts +
// multi-instance fan-out. Populated synchronously on the DAY
// granularity from the measurement write hooks; WEEK/MONTH/YEAR
// are enqueued through pg-boss for the worker to fold.
measurementRollups MeasurementRollup[]
// v0.5.4 ios-coord — per-day dedup ledger for MOOD_REMINDER pushes.
// One row per (user, local date) the worker has nudged. Prevents
// a second push when the cron rechecks the same hour later in the
// 22:00 window (the cron fires every 15 min — without the ledger
// a user could see three pushes back-to-back).
moodReminderDispatches MoodReminderDispatch[]
// v1.4.39 W-MOOD — per-(user, granularity, bucket) mood rollup. New
// tier behind the mood-analytics read path; per-write hooks fold
// MoodEntry mutations into the DAY granularity inline so the next
// read serves from this table instead of the unbounded
// MoodEntry.findMany walk.
moodEntryRollups MoodEntryRollup[]
// v1.4.39 W-MED — per-(user, medication, day) compliance rollup.
// Per-write hook on MedicationIntakeEvent mutations + the scheduler
// keep the row in sync so `/api/medications/intake?scope=compliance`
// and `computeUserHealthScoreFastPath` skip the per-event findMany.
medicationComplianceRollups MedicationComplianceRollup[]
// v1.4.40 SB-10 — AI consent receipts the iOS app POSTs when the user
// grants AI processing during onboarding. Persisted audit trail
// covers App-Store Guideline 5.1.2(i) ("data-collection consent must
// be verifiable") plus the operator's GDPR Art. 7 documentation
// burden. Append-only — a user revoking + re-granting consent mints
// a fresh row so the timeline reads chronologically.
consentReceipts ConsentReceipt[]
// v1.4.49 — per-attempt push-delivery ledger. Every sender (APNS,
// WEB_PUSH, TELEGRAM, NTFY) writes one fire-and-forget row per
// dispatch so the admin diagnostic endpoint can return the trailing
// 20 attempts ordered by recency. Daily prune at 03:35 Europe/Berlin
// retires rows older than 90 days (see `handlePushAttemptCleanup` in
// `reminder-worker.ts`).
pushAttempts PushAttempt[]
// v1.7.0 profile — optional patient-identity fields surfaced on the
// health-record export cover (PDF) and the FHIR `Patient` resource.
// `fullName` + `insurerName` are plaintext (consistent with the other
// plaintext profile columns: dateOfBirth, gender, heightCm). The German
// KVNR (`insuranceNumber`) is a quasi-identifier, so it is stored
// AES-256-GCM encrypted under the active key via `src/lib/crypto.ts`
// (column suffix `*Encrypted` per the project convention). All three are
// optional — an unset field collapses its cover line / FHIR slot exactly
// like the existing `practiceName`.
fullName String? @map("full_name")
insurerName String? @map("insurer_name")
insuranceNumberEncrypted String? @map("insurance_number_encrypted")
// v1.8.6 profile — optional German insurer institution number (IKNR,
// 9 digits). Surfaced on the FHIR `Coverage` resource's contained
// payor `Organization` (system `http://fhir.de/sid/arge-ik/iknr`).
// Stored plaintext, matching `insurerName` — the IKNR is a public
// registry identifier, not a quasi-identifier like the KVNR, so it
// does not warrant the encrypted column treatment. Optional; an unset
// field collapses the Coverage payor identifier exactly like an
// unset `insurerName` collapses its name.
insurerIkNumber String? @map("insurer_ik_number")
@@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 // v1.4.23: unit semantics shifted from hours to minutes so HealthKit category samples (per-stage rows) survive without precision loss.
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.
// ── v1.4.23 Apple Health additions ──
HEART_RATE_VARIABILITY // ms — HealthKit `heartRateVariabilitySDNN` (typically one sample per night).
RESTING_HEART_RATE // bpm — HealthKit `restingHeartRate` (one sample per day; distinct from spot PULSE).
ACTIVE_ENERGY_BURNED // kcal — HealthKit `activeEnergyBurned`, cumulative; daily-rollup pattern.
FLIGHTS_CLIMBED // count — HealthKit `flightsClimbed`, cumulative.
WALKING_RUNNING_DISTANCE // metres — HealthKit `distanceWalkingRunning`; SI metres in DB, UI converts per locale.
VO2_MAX // mL/(kg·min) — HealthKit `vo2Max`; one sample every few days at most.
BODY_TEMPERATURE // celsius — HealthKit `bodyTemperature`; iOS DTO already encodes this enum value.
// ── v1.4.25 W5d Withings full coverage ──
FAT_FREE_MASS // kg — Withings meastype 5 (Body+, Body Cardio, Body Comp, Body Scan). Algorithmically = weight − fat mass.
FAT_MASS // kg — Withings meastype 8. Mass form of BODY_FAT (kg, not %).
MUSCLE_MASS // kg — Withings meastype 76. Body+ family.
SKIN_TEMPERATURE // celsius — Withings meastype 73 (ScanWatch dermal reading). DISTINCT from BODY_TEMPERATURE — surface temps run ~32 °C, core ~37 °C; sharing a bucket would corrupt analytics.
PULSE_WAVE_VELOCITY // m/s — Withings meastype 91 (Body Cardio / Body Scan). Indicator of arterial stiffness.
VASCULAR_AGE // years — Withings meastype 155 (Body Scan). Composite of PWV + age.
VISCERAL_FAT // rating — Withings meastype 170 (Body Comp / Body Scan). Withings' rating scale (1–12); not directly comparable to a percent.
// ── v1.4.25 W8d Apple Health server-prep ──
AUDIO_EXPOSURE_ENV // dBA — HealthKit `environmentalAudioExposure`; ambient noise sample (e.g. concert, traffic). Watch + iPhone microphone.
AUDIO_EXPOSURE_HEADPHONE // dBA — HealthKit `headphoneAudioExposure`; AirPods listening-volume sampling.
TIME_IN_DAYLIGHT // minutes — HealthKit `timeInDaylight` (iOS 17+). Sun-exposure daily rollup; mood + sleep correlate.
// ── v1.4.30 R-F T1.4 + T1.5 Tier-1 additions ──
WALKING_STEADINESS // percent (0-100, scaled ×100 from Apple's 0..1 fraction) — HealthKit `appleWalkingSteadiness` (iOS 15+). Daily rollup. Falls + mobility-risk signal Apple's own Health app shows prominently in the Mobility section.
AUDIO_EXPOSURE_EVENT // count (event flag: 1 per fired loud-listening event) — HealthKit `environmentalAudioExposureEvent` + `headphoneAudioExposureEvent` category-events. Apple fires these when the rolling 7-day average crosses the WHO 80-dBA threshold. The notes field carries the source identifier (env vs headphone).
// ── v1.5.5 iOS-coord additions — six identifiers the deferred list refused to map. ──
// Each one was emitted by the iOS app, dropped server-side with a
// per-entry `skipped:"unmappable_identifier"`, and ack-200'd to the
// client. iOS read 200 as success, advanced its sync anchor, and the
// samples were lost forever. Now mapped end-to-end.
RESPIRATORY_RATE // count/min — HealthKit `respiratoryRate`. Watch + iPhone sleep + workout sampling.
BODY_MASS_INDEX // kg/m² — HealthKit `bodyMassIndex`. iOS computes it from weight + height before upload; we still store it as a first-class metric for trend display.
LEAN_BODY_MASS // kg — HealthKit `leanBodyMass`. Body-composition counterpart to FAT_MASS; pairs with WEIGHT − FAT_MASS for sanity checks.
WALKING_HEART_RATE_AVERAGE // bpm — HealthKit `walkingHeartRateAverage`. Daily rollup; cardio-fitness signal distinct from RESTING_HEART_RATE and spot PULSE.
WALKING_ASYMMETRY // percent (0-100, scaled ×100 from Apple's 0..1 fraction) — HealthKit `walkingAsymmetryPercentage`. Mobility section sibling to WALKING_STEADINESS.
WALKING_DOUBLE_SUPPORT // percent (0-100, scaled ×100 from Apple's 0..1 fraction) — HealthKit `walkingDoubleSupportPercentage`. Mobility section sibling.
// ── v1.5.5 iOS-coord follow-up — gait raw-SI pair ──
// Both identifiers flow over the wire in raw SI units (no ×100
// scaling). The percent gait metrics above use the server-side
// scaling path because Apple ships them as 0..1 fractions; the two
// below are already in metres and metres-per-second respectively
// and pass through `convertToDbUnit` as identity.
WALKING_STEP_LENGTH // metres — HealthKit `walkingStepLength`. Mobility section gait metric; adult walking typically 0.5–0.8 m.
WALKING_SPEED // metres per second — HealthKit `walkingSpeed`. Mobility section gait metric; healthy adult casual gait ≈ 1.2–1.4 m/s.
@@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
APPLE_HEALTH // v1.4.23 — HealthKit batch ingest. iOS DTO mirrors this exact spelling (not "HEALTHKIT").
@@map("measurement_source")
}
/// Per-stage sleep label for HealthKit `HKCategoryValueSleepAnalysis`
/// samples. Non-NULL only when `Measurement.type = SLEEP_DURATION`,
/// mirroring the `GlucoseContext` pattern. A single night produces one
/// row per stage (analytics sums them for `total_minutes`).
enum SleepStage {
IN_BED
AWAKE
ASLEEP // legacy iOS 15- `asleepUnspecified` — pre-iOS-16 sleep tracking with no stage breakdown.
REM
CORE
DEEP
@@map("sleep_stage")
}
model Measurement {
id String @id @default(cuid())
userId String @map("user_id")
type MeasurementType
value Float
unit String // kg, mmHg, bpm, %, minutes (sleep), steps, mg/dL, kcal, m, ms, mL/(kg·min), celsius
source MeasurementSource @default(MANUAL)
measuredAt DateTime @map("measured_at")
notes String? // encrypted at app level if sensitive
// External-system dedup key. Withings used `grpid` (numeric); v1.4.23
// adds Apple Health which uses `HKSample.uuid` (string). NULL for
// manual entries — Postgres treats NULLs as distinct in unique
// indexes, so two manual rows with the same (user, type, source) are
// still allowed.
externalId String? @map("external_id")
// v1.4.23 — opaque marker for the source's schema version (HealthKit
// sample's `metadata.HKWasUserEntered`, Withings model code, …).
// Stored as a string so the ingest path can replay it back unchanged.
externalSourceVersion String? @map("external_source_version")
// Only set for BLOOD_GLUCOSE measurements. NULL for all others.
glucoseContext GlucoseContext? @map("glucose_context")
// v1.4.23 — per-stage sleep label. NULL for non-sleep rows; non-NULL
// only for `type = SLEEP_DURATION`. Mirrors the glucoseContext
// pattern. A single night produces ~5 rows (one per stage).
sleepStage SleepStage? @map("sleep_stage")
// v1.4.25 W8c — device-type tag for the cross-source canonical
// picker. Stored as a free-text string (kept simple — Prisma enum
// would require coordinated client + server bumps every time we
// gain a new device class). The canonical values are documented in
// `deviceTypeEnum` (src/lib/validations/source-priority.ts):
// `watch | band | ring | phone | scale | other | unknown`. Legacy
// rows + every pre-W8c Measurement stays NULL; the picker treats
// NULL as `unknown`. Unique constraints intentionally do not
// include `deviceType` so two ingest paths from the same source
// still dedup on (type, source, externalId).
deviceType String? @map("device_type")
// v1.4.30 — iOS SyncMode foundation (R-E C-2). Monotonically
// incremented on every server-side mutation of the row. iOS uses
// it for last-writer-wins reconciliation when two devices write
// the same logical reading. Default = 1 for the create path; the
// update path is responsible for bumping it. Existing rows
// start at the default after the additive migration so legacy
// ingest keeps round-tripping unchanged.
syncVersion Int @default(1) @map("sync_version")
// v1.4.30 — soft-delete column. The DELETE path on the
// by-external-ids endpoint flips this rather than removing the row
// so paired iOS clients can reconcile deletions without losing the
// audit trail. NULL = live row; non-NULL = tombstoned at the
// recorded instant. The unique-by-externalId index keeps both
// states distinguishable for the bulk-backfill UPSERT path.
deletedAt DateTime? @map("deleted_at")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
// Original measuredAt-based dedup — kept for legacy Withings ingest
// and the manual UI's "no duplicate at the same wall-clock" guard.
//
// v1.4.25 W17b/c — `sleepStage` is the fifth axis so per-stage sleep
// segments from a single night (one row per stage, same measuredAt)
// can co-exist. Migration 0055 recreated the index with
// `NULLS NOT DISTINCT` so non-sleep rows (sleepStage IS NULL) still
// collide on the first four columns — same effective dedup as before
// for every meastype other than SLEEP_DURATION. Future regenerations
// must preserve the NULLS NOT DISTINCT clause (Prisma 7 still emits
// NULLS DISTINCT by default).
@@unique([userId, type, measuredAt, source, sleepStage])
// v1.4.23 — Apple Health batch dedup key. NULL externalId is distinct
// (Postgres semantics) so manual entries don't collide here.
@@unique([userId, type, source, externalId])
@@index([userId, type, measuredAt])
@@index([externalId])
// v1.7.0 — supports the `/api/sync/changes` keyset feed, which walks
// `where userId` ordered by `(updatedAt asc, id asc)` with `take
// limit+1`. Without this index Postgres filters by `userId` then sorts
// the user's entire measurement set per page; the delta drain allows
// 120 pulls/min/user. See migration 0096.
@@index([userId, updatedAt, id])
// `measurements_legacy_step_consolidation_idx` is a partial index
// over `type = 'ACTIVITY_STEPS' AND deleted_at IS NULL` — see
// migration 0087. It accelerates the v1.5.6 legacy step-consolidation
// discovery scan. Prisma can't express a partial-predicate index, so
// it lives in raw migration SQL and is intentionally omitted here.
@@map("measurements")
}
// ─── Measurement rollups (v1.5.0) ──────────────────────────
//
// Persistent rollup table — second cache tier behind the 60-s LRU.
// One row per `(userId, type, granularity, bucketStart)`. The DAY
// granularity is recomputed inline with every measurement write so
// the next read is correct; WEEK / MONTH / YEAR granularities are
// recomputed via the pg-boss `rollup-recompute` queue so the write
// path stays sub-ms. The Apple Health import worker skips the per-
// row hook and calls `recomputeUserRollups` once at the end of the
// import to avoid quadratic write costs on 100k-row backfills.
//
// Stats semantics mirror the v1.4.34.1 comprehensive aggregator:
// - `sd` = `STDDEV_POP(value)` (population SD, /N)
// - `slope` = `REGR_SLOPE(value, EXTRACT(EPOCH FROM measured_at) / 86400.0)`
// - `r2` = `REGR_R2(value, EXTRACT(EPOCH FROM measured_at) / 86400.0)`
// so cached + live aggregates agree byte-for-byte.
enum RollupGranularity {
DAY
WEEK
MONTH
YEAR
@@map("measurement_rollup_granularity")
}
model MeasurementRollup {
userId String @map("user_id")
type MeasurementType
granularity RollupGranularity
bucketStart DateTime @map("bucket_start") @db.Timestamptz(3)
count Int
mean Float
minValue Float @map("min_value")
maxValue Float @map("max_value")
sd Float?
slope Float?
r2 Float?
/// v1.4.39 W-SUM — cumulative-metric sum for the bucket. Populated
/// alongside `mean / count` for every type; cumulative read paths
/// (steps, flights, distance, daylight, active-energy) consume this
/// directly instead of re-deriving from raw measurements. Nullable
/// to keep the migration additive — the boot backfill fills NULLs
/// for existing rows; new rows always carry the sum.
sumValue Float? @map("sum_value")
computedAt DateTime @default(now()) @map("computed_at") @db.Timestamptz(3)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@id([userId, type, granularity, bucketStart])
@@index([userId, type, granularity, bucketStart(sort: Desc)])
@@map("measurement_rollups")
}
// ─── Workouts (v1.4.25 W8d) ────────────────────────────────
//
// HKWorkout-aligned typed columns for the analytics hot path + an
// open `metadata Json?` blob for the source-specific tail (HK
// metadata, Withings hr_zone_0..3, device bundle id). `sportType` is
// a TEXT column rather than a Postgres enum so adding a new sport is
// a Zod-only change — Apple alone carries 70+ HKWorkoutActivityType
// values and the long tail will only grow.
model Workout {
id String @id @default(cuid())
userId String @map("user_id")
/// Canonical sport string. See WorkoutSportType union in
/// src/lib/validations/workout.ts when that ships. Free-text at the
/// DB layer so a new sport is one Zod refinement away.
sportType String @map("sport_type")
startedAt DateTime @map("started_at")
endedAt DateTime @map("ended_at")
/// Denormalised seconds count — query convenience for the
/// "longest run / cycle" PR detector + the Coach evidence chips.
durationSec Int @map("duration_sec")
/// Apple `totalEnergyBurned` / Withings `calories`. NULL when the
/// source did not report it.
totalEnergyKcal Float? @map("total_energy_kcal")
/// Apple `totalDistance` / Withings `distance`. Metres.
totalDistanceM Float? @map("total_distance_m")
/// Apple computes the average post-hoc; Withings reports it
/// directly as `hr_average`.
avgHeartRate Int? @map("avg_heart_rate")
maxHeartRate Int? @map("max_heart_rate")
minHeartRate Int? @map("min_heart_rate")
stepCount Int? @map("step_count")
elevationM Float? @map("elevation_m")
/// Withings `pause_duration`. Apple does not report this directly
/// — derived from HKWorkoutEvent markers in the metadata blob.
pauseDurationSec Int? @map("pause_duration_sec")
source MeasurementSource @default(MANUAL)
/// HK `HKWorkout.uuid` or Withings workout id. NULL for manual
/// entries (Postgres NULL-distinct semantics keep them unique).
externalId String? @map("external_id")
externalSourceVersion String? @map("external_source_version")
/// HKWorkoutEvent markers, Withings hr_zone_0..3, device bundle id.
/// JSON only — never duck-typed at runtime; consumers run their
/// own Zod schema against the slice they care about.
metadata Json?
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
route WorkoutRoute?
/// Ingest dedup. NULL externalId is distinct so two manual
/// entries on the same day never collide here.
///
/// TODO(v1.5 iOS): cross-source workout dedup. The current key is
/// (userId, source, externalId) — so the SAME workout ingested from
/// MANUAL plus the iOS HealthKit passthrough lands two rows because
/// `source` differs. Measurements already solved this via
/// `pickCanonicalSourceRows()` (the read-time canonical-source
/// picker in src/lib/analytics/source-priority.ts); workouts need
/// the symmetric helper before the dashboard "recent workouts" tile
/// can show one row per workout instead of one per source. Tracked
/// for v1.5 P1 alongside the iOS app.
@@unique([userId, source, externalId])
/// Dashboard "recent workouts" query path.
@@index([userId, startedAt])
/// "Show me my last 10 runs" — per-sport history.
@@index([userId, sportType, startedAt])
@@map("workouts")
}
model WorkoutRoute {
id String @id @default(cuid())
workoutId String @unique @map("workout_id")
/// GeoJSON LineString — `{ "type": "LineString", coordinates:
/// [[lon, lat, alt?], ...] }`. JSONB rather than PostGIS so the
/// Coolify Postgres template stays extension-free (same decision
/// v1.4.20 made for correlations); GPX import + Leaflet / MapLibre
/// consume GeoJSON natively.
geometry Json
/// Optional per-sample timestamps (ISO strings) and speeds —
/// parallel array to `geometry.coordinates`. NULL when the source
/// only ships static GPX (e.g. Withings).
sampleTimestamps Json? @map("sample_timestamps")
createdAt DateTime @default(now()) @map("created_at")
workout Workout @relation(fields: [workoutId], references: [id], onDelete: Cascade)
@@map("workout_routes")
}
// ─── Personal records (v1.4.25 W8d — schema only) ──────────
//
// MAX vs MIN direction is stored on the row so the future detection
// worker doesn't have to re-resolve a per-metric lookup at query time.
// Some metric types deliberately have no PR (blood pressure, glucose,
// raw weight without a goal direction); those never produce rows.
// See src/lib/personal-records/pr-direction.ts for the lookup table.
enum PersonalRecordDirection {
MAX // higher is the record (steps, VO2 max, HRV, distance, daylight)
MIN // lower is the record (resting HR, body fat, audio exposure)
@@map("personal_record_direction")
}
model PersonalRecord {
id String @id @default(cuid())
userId String @map("user_id")
metricType MeasurementType @map("metric_type")
/// Per-sport-type bucket for workout-driven PRs (e.g.
/// "running_5km_time"). NULL for plain measurement-driven PRs
/// where the metric type alone is the dimension.
metricSlot String? @map("metric_slot")
direction PersonalRecordDirection
value Float
unit String
achievedAt DateTime @map("achieved_at")
/// FK to the Measurement row that achieved the record. SET NULL
/// on delete so the historical fact survives even if the
/// underlying measurement is later removed.
sourceMeasurementId String? @map("source_measurement_id")
source MeasurementSource @default(MANUAL)
externalId String? @map("external_id")
createdAt DateTime @default(now()) @map("created_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
/// Idempotency guard for the detection worker — re-running it on
/// the same (user, metric, slot, achievedAt) triple is a no-op.
@@unique([userId, metricType, metricSlot, achievedAt])
/// Hot read pattern — "current PR for this metric":
/// ORDER BY value DESC LIMIT 1 (MAX direction) /
/// ASC LIMIT 1 (MIN direction). Index covers both.
@@index([userId, metricType, value])
@@map("personal_records")
}
// ─── Medications ───────────────────────────────────────────
/// Treatment-class discriminator (v1.4.25 W4d). GLP1 turns on the
/// specialist surfaces — body-map injection-site picker, weekly-cadence
/// preset, titration history, GLP-1-aware Coach context, plateau
/// detection. GENERIC keeps existing meds rendering exactly as before.
/// Future categories (INSULIN, BIOLOGIC, FERTILITY, ...) drop into this
/// enum without churning the side-table `medication_categories` that
/// holds the clinical taxonomy (BLOOD_PRESSURE / VITAMIN / ...).
enum MedicationCategory {
GENERIC
GLP1
@@map("medication_category")
}
/// v1.6.0 — route of administration, decoupled from `treatmentClass`.
/// Drives the injection-site picker (available for any INJECTION, not
/// only GLP-1) and the one-time-injection shape (`oneShot = true` +
/// `deliveryForm = INJECTION`). ORAL is the backfilled default for every
/// pre-v1.6.0 row. OTHER is the catch-all for topical / inhaled / nasal
/// routes that don't need injection-site tracking.
enum MedicationDeliveryForm {
ORAL
INJECTION
OTHER
@@map("medication_delivery_form")
}
/// v1.7.0 — schedule-type discriminator. SCHEDULED is the default (rrule
/// / rolling / legacy cadence as today). PRN = as-needed: never
/// projected, never reminded, excluded from compliance expected-count,
/// still loggable. CYCLIC = N weeks on / M weeks off from the anchor;
/// within an on-week the rrule / legacy cadence applies as usual.
enum MedicationScheduleType {
SCHEDULED
PRN
CYCLIC
@@map("medication_schedule_type")
}
model Medication {
id String @id @default(cuid())
userId String @map("user_id")
name String
dose String // e.g. "10mg"
/// v1.4.25 W4d — treatment-class discriminator. GENERIC for the
/// vast majority of meds; GLP1 unlocks injection-site rotation,
/// dose-titration history, pen inventory, plateau detection, and
/// the Coach's GLP-1-aware reply mode. Field name is intentionally
/// not `category` to avoid clashing with the existing
/// `medication_categories` side-table (clinical taxonomy) that the
/// UI continues to read.
treatmentClass MedicationCategory @default(GENERIC) @map("treatment_class")
/// Doses per pen/vial — only meaningful for inventory-tracked meds
/// (Mounjaro KwikPen = 4, Ozempic 0.25/0.5 mg = 4, Wegovy single-dose
/// pen = 1). NULL = inventory tracking off.
dosesPerUnit Int? @map("doses_per_unit")
active Boolean @default(true)
notificationsEnabled Boolean @default(true) @map("notifications_enabled")
pausedAt DateTime? @map("paused_at")
snoozedUntil DateTime? @map("snoozed_until")
/// v1.5 — date the medication course begins. Anchors RRULE BYDAY /
/// BYMONTHDAY patterns and the rolling-interval countdown's first
/// window. NULL = "active from creation" (the legacy implicit
/// behaviour preserved for backwards compatibility). Required when
/// `oneShot` is true.
startsOn DateTime? @map("starts_on") @db.Date
/// v1.5 — date the medication course ends. Reminder worker stops
/// minting slots past this date. NULL = no end date (chronic).
/// When `oneShot` is true, `endsOn` always equals `startsOn`.
endsOn DateTime? @map("ends_on") @db.Date
/// v1.5 — single-administration medication (flu shot, vaccine,
/// post-op single dose, etc.). When true the medication has at
/// most one MedicationIntakeEvent and `active` flips to false once
/// that intake is logged. Mutually exclusive with rolling or
/// recurring schedules; the form layer enforces the constraint and
/// the migration backfills `oneShot = false` for every existing
/// row.
oneShot Boolean @default(false) @map("one_shot")
/// v1.6.0 — route of administration. Decoupled from `treatmentClass`
/// so the injection-site picker can surface for any INJECTION dose.
/// A one-time injection is `oneShot = true` + `deliveryForm =
/// INJECTION`. The migration backfills ORAL for every existing row.
deliveryForm MedicationDeliveryForm @default(ORAL) @map("delivery_form")
/// v1.8.5 — per-medication injection-site tracking opt-in. Only
/// meaningful (and only offered in the UI) when `deliveryForm ===
/// INJECTION`. Default false: after marking a dose taken the client
/// prompts for the site only when this is true, and the prompt is
/// always skippable (never blocking). Deactivatable at any time.
trackInjectionSites Boolean @default(false) @map("track_injection_sites")
/// v1.8.5 — per-medication allowed / preferred injection sites. Empty
/// = no per-medication restriction (every site is offered). The
/// effective pickable set is this list MINUS the user-level
/// `globalExcludedInjectionSites` deny-list (deny always wins). The
/// intake write path validates a submitted site against the effective
/// set and 422s on a disallowed value.
allowedInjectionSites InjectionSite[] @default([]) @map("allowed_injection_sites")
/// v1.7.0 — iOS Live Activity opt-in for this medication's reminders.
/// Default false; existing rows backfill false. The server stores +
/// echoes the flag; the iOS client owns the ActivityKit lifecycle. No
/// server-side behaviour hangs off it.
liveActivityEnabled Boolean @default(false) @map("live_activity_enabled")
/// v1.7.0 — iOS 26 AlarmKit critical-reminder opt-in. Critical alarms
/// bypass the device mute switch / Focus. Default false; the server
/// stores the preference only — it does not itself authorise anything
/// server-side.
criticalAlarmEnabled Boolean @default(false) @map("critical_alarm_enabled")
/// v1.9.0 — optional ATC classification code (active-substance class,
/// e.g. `A10BX10`). User/clinician-asserted; never machine-guessed on
/// the export path. NULL = no code (the FHIR exporter emits a text-only
/// `medicationCodeableConcept`, the pre-v1.9.0 behaviour). The WHO URI
/// `http://www.whocc.no/atc` is the emitted system. Validated on write
/// against `^[A-Z]\d{2}[A-Z]{2}\d{2}$`.
atcCode String? @map("atc_code")
/// v1.9.0 — optional RxNorm RxCUI (US; secondary coding). Same NULL
/// semantics as `atcCode`. Validated on write against `^\d+$`. Emitted
/// under `http://www.nlm.nih.gov/research/umls/rxnorm` alongside any
/// ATC code, never instead of the free-text `.text` anchor.
rxNormCode String? @map("rxnorm_code")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
schedules MedicationSchedule[]
intakeEvents MedicationIntakeEvent[]
phaseConfig ReminderPhaseConfig?
telegramMessages TelegramReminderMessage[]
doseChanges MedicationDoseChange[]
inventoryEvents MedicationInventoryEvent[]
/// v1.4.25 W19b — per-pen / per-vial entities (state machine +
/// 30-day in-use clock). Complements the running-sum
/// MedicationInventoryEvent ledger.
inventoryItems MedicationInventoryItem[]
/// v1.4.25 W19d — structured GLP-1 side-effect logs. Surfaced on
/// the medication detail page for GLP-1 treatmentClass only; the
/// taxonomy follows EMA EPAR §4.8 with HealthLog's 5-category
/// roll-up (GI / METABOLIC / INJECTION_SITE / COGNITIVE /
/// GLP1_SPECIFIC) and 1-5 Likert severity per entry.
sideEffects MedicationSideEffect[]
/// v1.4.39 W-MED — per-(medication, day) compliance rollup. Mutations
/// on the linked MedicationIntakeEvent rows refresh the row inline;
/// the read paths consume this instead of the per-event findMany.
complianceRollups MedicationComplianceRollup[]
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId])
@@index([treatmentClass])
@@map("medications")
}
model MedicationSchedule {
id String @id @default(cuid())
medicationId String @map("medication_id")
/// v1.4.25 W4e — schema-convention fix. Prior to this release the DB
/// columns were the camelCase identifiers `"windowStart"` / `"windowEnd"`
/// because the original 0001_init migration omitted `@map`. The
/// Prisma client surface stays unchanged (`windowStart` /
/// `windowEnd`); only the underlying SQL identifier is renamed to
/// match the snake_case convention every other column on this model
/// already follows.
windowStart String @map("window_start") // HH:mm format, e.g. "08:00"
windowEnd String @map("window_end") // HH:mm format, e.g. "10:00"
label String? // e.g. "Morning", "Evening"
dose String? // Per-schedule dose override; null = use Medication.dose
/// Legacy string encoding of the cadence ("null | 1,3,5 | i2;1,3,5").
/// Kept for reads of pre-v1.5 rows. v1.5 writes go to `rrule` or
/// `rollingIntervalDays`; the migration backfills `rrule` for every
/// existing row and v1.5.1 flips the readers to consult the new
/// fields first. v1.6.0 drops this column.
daysOfWeek String? @map("days_of_week")
/// v1.5 — one or more `HH:mm` strings naming the times the dose is
/// taken (in the user's timezone). Replaces the legacy single
/// `windowStart` mental model — patients think in points-in-time,
/// not in windows. Migration backfills `[windowStart]` for every
/// pre-v1.5 row.
timesOfDay String[] @default([]) @map("times_of_day")
/// v1.5 — reminder grace window in minutes. Replaces the implicit
/// `windowEnd - windowStart` computation for late-dose
/// classification. NULL falls back to the legacy math (the
/// reminder worker honours `windowEnd` when this is null).
reminderGraceMinutes Int? @map("reminder_grace_minutes")
/// v1.5 — RFC 5545 RRULE string for calendar-anchored cadences
/// (every N weeks, monthly, quarterly, yearly). Examples:
/// FREQ=DAILY
/// FREQ=WEEKLY;BYDAY=MO,WE,FR
/// FREQ=WEEKLY;INTERVAL=2;BYDAY=WE
/// FREQ=MONTHLY;BYMONTHDAY=1
/// FREQ=MONTHLY;INTERVAL=3;BYMONTHDAY=10
/// FREQ=YEARLY;BYMONTH=1;BYMONTHDAY=1
/// Mutually exclusive with `rollingIntervalDays` — enforced by the
/// `medication_schedules_rrule_xor_rolling` CHECK constraint added
/// in the v1.5 migration.
rrule String? @map("rrule")
/// v1.5 — flexible-rolling interval in days, counted forward from
/// the latest `MedicationIntakeEvent.takenAt` for the parent
/// medication. NULL when the schedule is calendar-anchored.
/// Mutually exclusive with `rrule`. Range 1..365 enforced at the
/// Zod layer; the column itself is unconstrained at the DB so
/// future extensions don't need a new migration.
rollingIntervalDays Int? @map("rolling_interval_days")
/// v1.7.0 — schedule-type discriminator. SCHEDULED = the existing rrule
/// / rolling / legacy cadence. PRN = as-needed (never projected,
/// reminded, or counted in compliance expected; still loggable).
/// CYCLIC = N weeks on / M weeks off, gating whichever inner cadence
/// the rrule / legacy fields describe. Backfills SCHEDULED.
scheduleType MedicationScheduleType @default(SCHEDULED) @map("schedule_type")
/// v1.7.0 — cyclic "on" weeks. Only meaningful when
/// `scheduleType = CYCLIC`. The phase repeats (cyclicOnWeeks on, then
/// cyclicOffWeeks off) from the medication's `startsOn ?? createdAt`.
cyclicOnWeeks Int? @map("cyclic_on_weeks")
/// v1.7.0 — cyclic "off" weeks. Only meaningful when
/// `scheduleType = CYCLIC`.
cyclicOffWeeks Int? @map("cyclic_off_weeks")
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")
}
/// v1.4.25 W4d — eight self-injection zones for GLP-1 receptor
/// agonists. Front-of-body view used by the picker.
/// - Abdomen quadrants avoid a 2-inch radius around the navel per
/// Eli Lilly's Mounjaro IFU + Novo Nordisk's Ozempic/Wegovy PI.
/// - Thighs cover the anterior / outer surface; user-injectable.
/// - Upper arms typically require assistance per the same PIs; the
/// picker surfaces them but flags the constraint via i18n.
enum InjectionSite {
ABDOMEN_LEFT
ABDOMEN_RIGHT
ABDOMEN_UPPER_LEFT
ABDOMEN_UPPER_RIGHT
THIGH_LEFT
THIGH_RIGHT
UPPER_ARM_LEFT
UPPER_ARM_RIGHT
@@map("injection_site")
}
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")
/// v1.4.25 W4d — only populated for GLP-1 intake events (the picker
/// is the only surface that writes this column). Existing rows stay
/// NULL forever and the read paths treat NULL as "no site recorded".
injectionSite InjectionSite? @map("injection_site")
// v1.7.0 sync — server-set mutation timestamp. The model had no
// `updatedAt`; the offline-sync keyset feed orders on it, so it is
// added here. `@updatedAt` makes Prisma maintain it on every write.
updatedAt DateTime @updatedAt @map("updated_at")
// v1.7.0 sync — reconciliation counter. An intake is an immutable
// fact, so a "correction" is a tombstone + re-insert rather than an
// in-place edit; syncVersion still bumps on the soft-delete write so
// the feed echoes a monotonic value. Default 0 for legacy rows.
syncVersion Int @default(0) @map("sync_version")
// v1.7.0 sync — soft-delete tombstone. The user-facing per-event
// DELETE route flips this (and bumps syncVersion) rather than removing
// the row, so `/api/sync/changes` surfaces the deletion as a tombstone
// keyed on the server `id`. NULL = live; non-NULL = tombstoned.
// Read paths (today / compliance / lists) filter `deletedAt: null`.
deletedAt DateTime? @map("deleted_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)
/// v1.4.39 W-SERVER-FIX-2 — both the intake route and the dashboard
/// summary route project active schedules through `expandTodayIntakes`
/// and idempotently `createMany`-backfill missing rows. Without a
/// unique constraint the two reads could race a duplicate REMINDER
/// row in for the same `(medicationId, scheduledFor)` between the
/// existence probe and the insert. `source` is in the key so the
/// reminder worker (REMINDER) doesn't collide with manual user
/// inserts (WEB / TELEGRAM / IMPORT) on the same slot.
@@unique([userId, medicationId, scheduledFor, source])
@@index([userId, medicationId, scheduledFor])
// v1.7.0 sync — keyset support for the `/api/sync/changes` intake feed
// (`where userId` ordered by `(updatedAt asc, id asc)`).
@@index([userId, updatedAt, id])
@@map("medication_intake_events")
}
/// v1.4.25 W4d — titration history for any medication, optimised for
/// GLP-1 (Mounjaro 2.5 → 5 → 7.5 → 10 → 12.5 → 15 mg with min 4-week
/// steps per Eli Lilly's PI). Append-only; current dose = latest row
/// by effectiveFrom. The Medication.dose string field is the
/// convenience pointer for legacy reads; this table is the history of
/// record.
model MedicationDoseChange {
id String @id @default(cuid())
medicationId String @map("medication_id")
/// When this dose started. Stored UTC; the UI renders in the user's
/// timezone via the existing display helpers.
effectiveFrom DateTime @map("effective_from")
/// Numeric value — easier titration math (does this user step up by
/// 2.5 mg every 4 weeks?). Pair with doseUnit for display.
doseValue Float @map("dose_value")
/// Free-text unit ("mg", "µg", "IE", etc.). Flexible because GLP-1
/// is "mg" but the column is reused for future categories.
doseUnit String @map("dose_unit")
/// User-entered titration note ("stepped up after 4 weeks on 5 mg,
/// tolerated 7.5 mg with mild nausea day 2").
note String?
createdAt DateTime @default(now()) @map("created_at")
medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)
@@index([medicationId, effectiveFrom])
@@map("medication_dose_changes")
}
/// v1.4.25 W4d — append-only inventory ledger. Positive delta when
/// stock arrives ("purchased"), negative when consumed ("consumed",
/// fired automatically on intake-event creation for meds with
/// Medication.dosesPerUnit set), or marked out of usable stock
/// ("expired", "damaged"). Current inventory is the running sum.
model MedicationInventoryEvent {
id String @id @default(cuid())
medicationId String @map("medication_id")
delta Int // positive = added, negative = consumed/lost
reason String // "purchased" / "consumed" / "expired" / "damaged"
occurredAt DateTime @default(now()) @map("occurred_at")
medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)
@@index([medicationId, occurredAt])
@@map("medication_inventory_events")
}
/// v1.4.25 W19b — per-pen / per-vial inventory entity. The EMA EPAR
/// §6.3 30-day in-use clock lives here as `firstUseAt` + `expiresAt`;
/// the state machine (see `src/lib/medications/inventory/state-machine.ts`)
/// transitions ACTIVE → IN_USE → EXPIRED | USED_UP based on first-use,
/// printed expiry, and dose depletion.
///
/// Coexists with `MedicationInventoryEvent`: that ledger is the
/// running-sum consumption stream; this model is the entity-level
/// view that the UI tile renders ("Pen #2 of 3 — 12 days left").
enum MedicationInventoryState {
ACTIVE
IN_USE
EXPIRED
USED_UP
@@map("medication_inventory_state")
}
model MedicationInventoryItem {
id String @id @default(cuid())
userId String @map("user_id")
medicationId String @map("medication_id")
state MedicationInventoryState @default(ACTIVE)
/// Doses the pen / vial shipped with. Mounjaro KwikPen = 4,
/// Trulicity single-dose = 1, Byetta = 60.
dosesTotal Int @map("doses_total")
/// Remaining doses; decremented by the intake hook.
dosesRemaining Int @map("doses_remaining")
/// NULL until the pen is first opened. Sets the 30-day in-use
/// clock per EMA EPAR §6.3.
firstUseAt DateTime? @map("first_use_at")
/// Persisted = MIN(firstUseAt + 30 days, printedExpiry). Persisting
/// rather than deriving keeps the daily "expire-stale" cron a
/// single indexed scan.
expiresAt DateTime? @map("expires_at")
/// The carton-printed expiry date the user enters at creation.
printedExpiry DateTime? @map("printed_expiry")
purchasedAt DateTime? @map("purchased_at")
/// Free-text note, application-layer max 200 chars.
notes String?
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)
@@index([userId, medicationId, state])
@@index([userId, expiresAt])
@@map("medication_inventory_items")
}
/// v1.4.25 W19d — structured GLP-1 side-effect log.
///
/// Categorical 21-entry × 5-category taxonomy derived from EMA EPAR
/// §4.8 ("Undesirable effects") clustered into the five surfaces the
/// HealthLog UI uses (GI / METABOLIC / INJECTION_SITE / COGNITIVE /
/// GLP1_SPECIFIC). Severity is a 1-5 Likert — chosen over the EMA
/// 3-level (mild / moderate / severe) convention to give the user
/// finer-grained signal for trend charting; the UI maps each integer
/// to a translated semantic label (mild → moderate → significant →
/// severe → very-severe).
///
/// Daily capture, not per-shot. Delayed-onset symptoms (nausea on
/// day 4 post-tirzepatide is common) are captured at their actual
/// onset time, not back-filled onto the dose record. The Coach reads
/// these timestamps against MedicationDoseChange + MedicationIntakeEvent
/// to surface patterns ("nausea has clustered around day 2-3 post-shot
/// for the last 4 weeks") without making a clinical claim.
///
/// Surface gating: the medication detail page mounts the logbook only
/// for `Medication.treatmentClass = GLP1`. The schema deliberately
/// allows non-GLP-1 medications to carry side-effect rows for future
/// drug classes (e.g. insulin hypoglycaemia symptoms) without a
/// migration; UI gating keeps the v1.4.25 release scope tight.
///
/// Indexes:
/// - (userId, medicationId, occurredAt DESC) — drug-detail timeline
/// read path; the dominant query is "last N entries for this med".
/// - (userId, occurredAt DESC) — Coach snapshot aggregation read
/// path; correlation across medications is per-user, not per-drug.
model MedicationSideEffect {
id String @id @default(cuid())
userId String @map("user_id")
medicationId String @map("medication_id")
occurredAt DateTime @default(now()) @map("occurred_at")
category MedicationSideEffectCategory
entry MedicationSideEffectEntry
/// 1 = mild, 2 = moderate, 3 = significant, 4 = severe, 5 = very-severe.
/// Constrained to [1, 5] at the application layer (Zod) and the DB
/// (CHECK constraint via migration). Not an enum to keep cardinality
/// trivially numeric for chart aggregation.
severity Int
/// Optional free-text note, hard-capped to 280 chars at the
/// application layer. The DB column is unbounded TEXT for
/// consistency with sibling "notes" columns on this schema.
notes String?
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, occurredAt])
@@index([userId, occurredAt])
@@map("medication_side_effects")
}
/// Five rolled-up surface categories. The pure-mapping helper at
/// src/lib/medications/side-effects/taxonomy.ts is the single source
/// of truth for which `MedicationSideEffectEntry` belongs to which
/// `MedicationSideEffectCategory`; the DB stores the category
/// denormalised so range / category-filter queries don't need a JOIN.
enum MedicationSideEffectCategory {
GI
METABOLIC
INJECTION_SITE
COGNITIVE
GLP1_SPECIFIC
@@map("medication_side_effect_category")
}
/// 21 EMA-EPAR-§4.8-derived entries grouped by surface category.
/// Order in this enum matches the picker UI order; rename / reorder
/// is a breaking change for serialised rows. New entries append.
enum MedicationSideEffectEntry {
// GI (5)
NAUSEA
VOMITING
DIARRHEA
CONSTIPATION
ABDOMINAL_PAIN
// Metabolic (4)
HYPOGLYCEMIA_SYMPTOMS
DEHYDRATION
ANOREXIA
ELECTROLYTE_FATIGUE
// Injection-site (4)
INJECTION_REDNESS
INJECTION_SWELLING
INJECTION_BRUISING
INJECTION_INDURATION
// Cognitive (4)
BRAIN_FOG
DIZZINESS
LOW_MOOD
LOW_ENERGY
// GLP-1 specific (4)
EARLY_SATIETY
GASTROPARESIS_LIKE
DYSGEUSIA
GALLBLADDER_DISCOMFORT
@@map("medication_side_effect_entry")
}
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
/// v1.7.0 — time-of-day this reminder belongs to (HH:mm, user-local).
/// Empty string = the single legacy window (pre-v1.7 rows + single-time
/// schedules). Part of the dedup key so a multi-time schedule dispatches
/// once per time-of-day per phase per day instead of once per day.
timeOfDay String @default("") @map("time_of_day")
medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)
schedule MedicationSchedule @relation(fields: [scheduleId], references: [id], onDelete: Cascade)
@@unique([medicationId, scheduleId, date, phase, timeOfDay], map: "telegram_reminder_messages_med_sched_date_phase_tod_key")
@@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")
/// v1.4.25 W5d — comma-separated OAuth scope string returned by the
/// Withings authorisation flow. Legacy v1.4.24- connections sit on
/// `user.metrics` only; v1.4.25 starts requesting
/// `user.metrics,user.activity` for new + reconnecting users so the
/// activity / sleep endpoints unlock. Null = legacy connection that
/// has never re-authed since v1.4.25 — the UI surfaces a banner
/// inviting them to reconnect.
scope String? @map("scope")
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")
}
/// v1.4.47 W6 — short-lived ledger of in-flight OAuth state nonces.
///
/// The legacy state shape `${user.id}:${random16}` (v1.4.42-) persisted
/// the user id in plaintext on both the URL state param and the
/// `withings_state` cookie. The cookie is httpOnly + Secure +
/// sameSite:lax today so XSS exfiltration is closed, but the user id
/// is still recoverable from any request-log entry where the cookie
/// isn't redacted and from a network-traffic capture of the redirect.
/// The v1.4.43 security audit (L-1) recommended decoupling the two by
/// switching the state to a fully-random 16-byte nonce backed by this
/// short-lived row — the callback resolves the user via the row's
/// `userId` instead of the cookie value.
///
/// Lifecycle:
/// * `connect` mints a row with `expiresAt = now() + 10 min` and
/// sets the cookie to JUST the nonce (no user id).
/// * `callback` looks up the row, asserts `expiresAt > now()`, then
/// DELETEs it in the same transaction — replay of the same nonce
/// fails the second time because the row is gone.
/// * A daily cleanup cron sweeps any abandoned rows whose
/// `expiresAt` has passed (user closed the Withings tab without
/// completing the handshake).
model WithingsOAuthState {
nonce String @id
userId String @map("user_id")
createdAt DateTime @default(now()) @map("created_at")
expiresAt DateTime @map("expires_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([expiresAt])
@@map("withings_oauth_states")
}
// ─── Mood Entries (moodLog) ───────────────────────────────────
model MoodEntry {
id String @id @default(cuid())
userId String @map("user_id")
date String // YYYY-MM-DD — anchored to `tz` when not null, else legacy Europe/Berlin
mood String // SUPER_GUT, GUT, OKAY, SCHLECHT, LAUSIG
score Int // 1-5
tags String? // JSON array of tag keys
/// v1.4.30 — first-class free-text note. Replaces the
/// `tags: ["note:<text>"]` workaround the iOS app shipped through
/// v0.4.1.1 (per R-E H-5); the prior pattern contaminated the
/// tags axis with prose the Coach evidence shelf then read as
/// taxonomic labels. Nullable for the additive-only migration
/// path; the bulk-backfill endpoint pulls existing `note:...` tag
/// rows into this column via the migration script.
note String? @db.Text
source String @default("MOODLOG") // WEB, TELEGRAM, DAYLIO
moodLoggedAt DateTime @map("mood_logged_at")
/// v1.4.25 W7b — per-row IANA timezone that the `date` string is
/// anchored to. Nullable for backward compatibility: legacy rows
/// (`tz IS NULL`) were written under the Europe/Berlin assumption
/// and read-paths interpret them as such. New rows always carry the
/// user's `displayTimezone` at write time.
tz String?
syncedAt DateTime @default(now()) @map("synced_at")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
// v1.7.0 sync — LWW reconciliation counter for the offline-sync feed.
// Monotonically bumped on every server-side mutation (update +
// soft-delete). Mood is last-writer-wins by syncVersion: the higher
// value wins. Default 0 for the additive migration so legacy rows
// round-trip unchanged.
syncVersion Int @default(0) @map("sync_version")
// v1.7.0 sync — soft-delete tombstone. The user-facing DELETE route
// flips this (and bumps syncVersion) rather than removing the row, so
// the `/api/sync/changes` feed surfaces the deletion as a tombstone
// keyed on the server `id` to paired clients offline at delete time.
// NULL = live row; non-NULL = tombstoned at the recorded instant.
// Every list / detail / analytics / rollup read filters
// `deletedAt: null` so tombstoned rows never surface in normal reads.
deletedAt DateTime? @map("deleted_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
// v1.8.5 — structured-tag links. Additive alongside the legacy flat
// `tags` JSON string: an entry can carry both (a free-text tag the user
// typed AND a structured tag they picked from the taxonomy). The
// aggregates read whichever is present; existing rows keep their flat
// `tags` untouched.
tagLinks MoodEntryTagLink[]
@@unique([userId, date, moodLoggedAt])
@@index([userId, date])
// v1.7.0 sync — keyset support for the `/api/sync/changes` mood feed
// (`where userId` ordered by `(updatedAt asc, id asc)`).
@@index([userId, updatedAt, id])
@@map("mood_entries")
}
// ─── Mood Tag Taxonomy (v1.8.5 W6-tax) ────────────────────────
//
// Structured, icon-bearing tag catalog modelled on the standalone mood
// diary's Category → TagItem shape. Global reference data (one shared
// catalog for the deployment, not per-user): a self-hoster's account
// inherits the seeded categories the same way the metric-type enum is
// shared. `MoodEntryTagLink` is the per-entry join.
//
// Backward-compat: this is purely ADDITIVE. The legacy flat
// `MoodEntry.tags` (a JSON string array of free-text keys) keeps working
// unchanged — the GLP-1 quick-tag chips, the Telegram/MoodLog sync, and
// every existing row stay on the flat axis. The structured taxonomy is a
// second, richer capture surface that the tag-frequency / lift breakdown
// folds in next to the flat tags (`mood-aggregates.ts`).
//
// `labelKey` carries an i18n key (resolved client-side against all six
// locales) rather than a hard-coded German label, so the seeded catalog
// renders localised everywhere. `icon` is a Lucide icon name rendered by
// the capture UI + breakdown.
model MoodTagCategory {
id String @id @default(cuid())
/// Stable machine key (e.g. `feelings`). Unique across the catalog so
/// the seed migration can upsert idempotently.
key String @unique
/// i18n message key for the category label (`mood.tagCategory.<key>`).
labelKey String @map("label_key")
/// Lucide icon name for the category header.
icon String?
sortOrder Int @default(0) @map("sort_order")
/// Soft-hide a category without deleting its historical links.
isActive Boolean @default(true) @map("is_active")
tags MoodTag[]
@@map("mood_tag_categories")
}
model MoodTag {
id String @id @default(cuid())
categoryId String @map("category_id")
/// Stable machine key (e.g. `happy`). Unique across the catalog.
key String @unique
/// i18n message key for the tag label (`mood.tag.<key>`).
labelKey String @map("label_key")
/// Lucide icon name for the tag chip.
icon String?
sortOrder Int @default(0) @map("sort_order")
isActive Boolean @default(true) @map("is_active")
category MoodTagCategory @relation(fields: [categoryId], references: [id], onDelete: Cascade)
links MoodEntryTagLink[]
@@index([categoryId, sortOrder])
@@map("mood_tags")
}
// Join: which structured tags a mood entry carries. Composite PK keeps
// the pairing unique; cascade on both sides so deleting an entry (or a
// catalog tag) cleans up its links. Distinct from the flat `tags` JSON
// column on `MoodEntry`.
model MoodEntryTagLink {
moodEntryId String @map("mood_entry_id")
moodTagId String @map("mood_tag_id")
createdAt DateTime @default(now()) @map("created_at")
moodEntry MoodEntry @relation(fields: [moodEntryId], references: [id], onDelete: Cascade)
moodTag MoodTag @relation(fields: [moodTagId], references: [id], onDelete: Cascade)
@@id([moodEntryId, moodTagId])
@@index([moodTagId])
@@map("mood_entry_tag_links")
}
// ─── Mood Reminder Dispatch Ledger (v0.5.4 ios-coord) ─────
//
// Idempotency guard for the daily 22:00 MOOD_REMINDER push. The
// reminder cron fires every 15 min — when it lands inside the 22:00
// window the handler looks up `(userId, date)` here and bails if a row
// already exists, so the user sees exactly one nudge per local day.
// `date` is a YYYY-MM-DD string anchored to the user's timezone at
// dispatch time (same encoding convention as MoodEntry.date).
model MoodReminderDispatch {
id String @id @default(cuid())
userId String @map("user_id")
date String // YYYY-MM-DD anchored to user.timezone
dispatchedAt DateTime @default(now()) @map("dispatched_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([userId, date])
@@index([userId, dispatchedAt])
@@map("mood_reminder_dispatches")
}
// ─── Mood Entry Rollup (v1.4.39 W-MOOD) ───────────────────────
//
// Second cache tier behind the 60-s LRU on `/api/mood/analytics`.
// One row per `(userId, granularity, bucketStart)` carrying the
// `count` + `mean` + min/max + population SD over the bucket window.
// `RollupGranularity` from the measurement rollup tier is reused so
// the worker / future cross-granularity readers share a single enum.
//
// Write path: every MoodEntry create/update/delete fires
// `recomputeMoodBucketsForEntry` which upserts the DAY bucket for
// the entry's `(userId, dayKey)` inline. WEEK / MONTH / YEAR are
// enqueued through pg-boss for the worker to fold so the write path
// stays sub-ms.
//
// Read path: `/api/mood/analytics` reads `prisma.moodEntryRollup.
// findMany({ where: { userId, granularity: 'DAY' } })` and runs the
// existing summarize() logic against the rollup instead of pulling
// every MoodEntry into Node.
//
// Stats semantics (population, not sample): `sd = STDDEV_POP(score)`
// over the bucket window so two-tier cached / live reads agree
// byte-for-byte the same way the MeasurementRollup tier does.
model MoodEntryRollup {
userId String @map("user_id")
granularity RollupGranularity
bucketStart DateTime @map("bucket_start") @db.Timestamptz(3)
count Int
mean Float
minScore Int @map("min_score")
maxScore Int @map("max_score")
sd Float?
computedAt DateTime @default(now()) @map("computed_at") @db.Timestamptz(3)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@id([userId, granularity, bucketStart])
@@index([userId, granularity, bucketStart(sort: Desc)])
@@map("mood_entry_rollups")
}
// ─── Medication Compliance Rollup (v1.4.39 W-MED) ─────────────
//
// Per-(userId, medicationId, day) compliance ledger. Replaces the
// unbounded MedicationIntakeEvent findMany on
// `/api/medications/intake?scope=compliance` and the equivalent
// fan-out inside `computeUserHealthScoreFastPath`.
//
// Day-key is anchored to the user's IANA timezone (TEXT, encoded as
// `YYYY-MM-DD`) so the read path can collapse a date range into a
// straight WHERE clause without re-deriving the boundary per row.
//
// Write path: every MedicationIntakeEvent create / update / delete
// (and the scheduler that mints fresh `scheduledFor` rows) fires
// `recomputeMedicationComplianceForDay` which upserts the
// `(user, medication, day)` row. Worker handles weekly / monthly
// re-fold off the hot path.
model MedicationComplianceRollup {
userId String @map("user_id")
medicationId String @map("medication_id")
day String // YYYY-MM-DD anchored to user.timezone
scheduled Int
taken Int
skipped Int
computedAt DateTime @default(now()) @map("computed_at") @db.Timestamptz(3)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
medication Medication @relation(fields: [medicationId], references: [id], onDelete: Cascade)
@@id([userId, medicationId, day])
@@index([userId, day(sort: Desc)])
@@map("medication_compliance_rollups")
}
// ─── 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 per-kind
// `consecutiveFailuresByKind` JSON bucket feeds the admin-Telegram
// alerting threshold (default 3) via `Math.max(...Object.values(...))`.
// 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" | "parked"
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)
// v1.4.47 W1 — legacy single-column `consecutiveFailures` integer was
// dropped (migration 0077). The per-kind bucket below is now the
// sole source of truth for the alert ladder.
consecutiveFailuresByKind Json? @map("consecutive_failures_by_kind") // v1.4.43 W14 — { transient, reauth_required, persistent }
persistentFailureStartedAt DateTime? @map("persistent_failure_started_at") // v1.4.43 W14 — start of the current persistent streak (drives >24h park)
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")
// v1.4.25 W7 — server-wide default timezone for new accounts that
// don't carry a browser-detected zone in the registration payload.
// The resolver in `src/lib/tz/resolver.ts` falls back to
// "Europe/Berlin" when this column is NULL, so existing instances
// keep behaving identically to v1.4.24 without an admin touching
// anything. Validated against `Intl.supportedValuesOf('timeZone')`
// at the admin write path. Length cap matches IANA's longest
// canonical zone identifier ("America/Argentina/ComodRivadavia" at
// 32 chars) plus headroom.
defaultUserTimezone String? @map("default_user_timezone") @db.VarChar(64)
// v1.4.31 — Assistant-surface operator feature flags. The master
// gates every LLM-driven surface app-wide; five sub-flags carve
// the visibility cut per surface (Coach drawer + chat, Daily
// Briefing + advisor, per-metric status cards, correlation
// narration, Health-Score delta explainer). Defaults preserve the
// v1.4.30 behaviour (every surface visible) so upgrades require
// no admin action. The runtime resolver in
// `src/lib/feature-flags/index.ts` forces every sub-flag false
// when the master is off. Locked per
// `.planning/RESPONSE-TO-IOS-TEAM-2026-05-16.md` §3 R5: the same
// flag matrix gates server-routed AND iOS on-device surfaces, so
// operators can opt out of every assistant surface regardless of
// where the model runs.
assistantEnabled Boolean @default(true) @map("assistant_enabled")
assistantCoachEnabled Boolean @default(true) @map("assistant_coach_enabled")
assistantBriefingEnabled Boolean @default(true) @map("assistant_briefing_enabled")
assistantInsightStatusEnabled Boolean @default(true) @map("assistant_insight_status_enabled")
assistantCorrelationsEnabled Boolean @default(true) @map("assistant_correlations_enabled")
assistantHealthScoreExplainerEnabled Boolean @default(true) @map("assistant_health_score_explainer_enabled")
@@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
// v1.4.27 B3 — resolved at audit-creation time from the bundled
// GeoLite2-ASN MMDB. `asn` is the autonomous-system number,
// `carrier` is the organisation string (e.g. "Deutsche Telekom AG").
// Both nullable so older rows, private-IP rows, and offline-miss
// rows stay valid.
asn Int? // autonomous-system number
carrier String? // autonomous-system organisation
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" | "APNS"
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")
}
// ─── Apple Health export.zip import (v1.4.34) ───────────────
//
// Mirrors a `pg-boss` `apple-health-import` job's lifecycle so the
// polling endpoint can read live per-phase progress without
// round-tripping through pg-boss internals (`output` only populates
// on completion in v12). The `pgBossJobId` link lets the worker
// reconcile a row by the boss id it just received from `boss.send`.
//
// Status state machine:
// queued → unpacking → parsing → upserting → done
// ↘ failed
//
// `progress` carries the live snapshot the worker writes every
// 1 000 records (see `ImportJobProgress` in
// `src/lib/measurements/import-apple-health-export.ts`). `result`
// carries the terminal `ImportJobResult` envelope.
model ImportJob {
id String @id @default(cuid())
userId String @map("user_id")
/// Admin who triggered the import on behalf of `userId` (admin
/// variant only). NULL when the user kicked off their own import.
triggeredByAdminId String? @map("triggered_by_admin_id")
/// `pg-boss` job id assigned by `boss.send`. Allows the worker to
/// look up its mirror row by the boss id rather than passing the
/// HealthLog id through the payload.
pgBossJobId String? @unique @map("pg_boss_job_id")
/// `queued | unpacking | parsing | upserting | done | failed`.
status String @default("queued")
/// Free-text reason on `failed`; null otherwise.
failureReason String? @map("failure_reason")
/// Bytes uploaded — surfaced to the operator audit trail.
uploadBytes Int @map("upload_bytes")
/// SHA-256 hex digest of the uploaded `export.zip`. Lets the
/// kick-off endpoint short-circuit a re-upload of the exact same
/// file back to the existing job id without re-queueing.
uploadSha256 String? @map("upload_sha256")
/// Apple's `ExportDate` attribute string-formatted, parsed during
/// the unpack phase. NULL until the parser sees it.
exportedAt DateTime? @map("exported_at")
startedAt DateTime @default(now()) @map("started_at")
completedAt DateTime? @map("completed_at")
/// JSON envelope mirroring `ImportJobProgress` (`src/lib/measurements/
/// import-apple-health-export.ts`). Updated mid-run.
progress Json @default("{}")
/// JSON envelope mirroring `ImportJobResult`. NULL until terminal
/// state. Carries per-type counts, workout counts, clinical /
/// deferred / unknown buckets.
result Json?
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId, startedAt])
@@index([status])
@@map("import_jobs")
}
// ─── 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 is rejected with
/// 409 by `POST /api/devices` (cross-user-hijack guard).
///
/// `apnsToken` and `apnsEnvironment` are nullable because today's iOS
/// builds register through a generic `token` first; a follow-up call
/// from the iOS app populates the APNs-specific pair once the device
/// receives its first push token from `application:didRegisterForRemoteNotificationsWithDeviceToken:`.
/// Loose-typed `apnsEnvironment` (sandbox|production) leaves headroom
/// for whatever new gateway labels Apple ships next without a forced
/// migration.
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?
apnsToken String? @map("apns_token")
apnsEnvironment String? @map("apns_environment") // "sandbox" | "production"
/// v1.7.0 — per-device medication-delivery override. NULL = inherit the
/// user-level roaming default
/// (`User.notificationPrefs.medication.deliveryDefault`). "server"
/// forces server APNs for this device; "client" forces local. Stored +
/// echoed in v1.7.0; cron suppression stays user-level (APNs fans out to
/// all the user's devices and iOS dedupes locally).
medicationDelivery String? @map("medication_delivery")
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])
// `devices_apns_token_unique` is a partial UNIQUE index over
// `apns_token IS NOT NULL` — see migration 0041. Prisma can't express
// a partial index in the schema language, so it's introduced via raw
// SQL and intentionally omitted here. Do not re-add `@@index([apnsToken])`
// (the non-unique sibling) — the partial unique index supersedes it.
@@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")
// Nullable since v1.4.23 reconcile (Sr-H3): the recommendation
// surface still snapshots the prose here (recommendations are
// unencrypted on the wire, so plaintext is the source-of-truth);
// the Coach surface leaves it NULL and references the message via
// `coachMessageId` so the assistant prose stays encrypted-at-rest
// in `coach_messages.encryptedContent`.
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")
// v1.4.23 H7 — polymorphic target so Coach replies share the table.
// "recommendation" (default) is the v1.4.16 B5e shape; "coach" is a
// Coach assistant message. The aggregator slices on targetType to
// bucket each surface independently. Default value lets every
// pre-v1.4.23 row read as "recommendation" without a backfill.
targetType String @default("recommendation") @map("target_type")
// Optional free-form reason — populated when the user clicks
// "unhelpful" and selects a category from the small chip strip.
// Capped at 200 chars upstream so the table can't bloat from a
// pasted essay.
reason String? @map("reason")
// v1.4.23 reconcile (Sr-H3) — Coach surface references the source
// message instead of snapshotting its prose into
// `recommendationText`. FK + cascade so a deleted message drops the
// rating row too. Always NULL for `targetType="recommendation"`.
coachMessageId String? @map("coach_message_id")
coachMessage CoachMessage? @relation(fields: [coachMessageId], references: [id], onDelete: Cascade)
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])
@@index([targetType, createdAt])
@@index([coachMessageId])
@@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)
// v1.4.23 reconcile (Sr-H3) — back-relation for the FK that lets
// Coach feedback rows reference their source message instead of
// snapshotting plaintext prose into `recommendation_feedback`.
feedback RecommendationFeedback[]
@@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")
}
// ─── Consent Receipts (v1.4.40 SB-10) ─────────────────────────
//
// Per-user audit trail of AI-processing consent grants. The iOS app
// posts a fresh row whenever the user opts in to off-device AI flows
// during onboarding or via Settings; append-only so a revoke + re-grant
// cycle reads chronologically. Persists the raw signed artefact (PDF
// receipt OR signed JWT — caller's choice) the App-Store Guideline
// 5.1.2(i) audit needs to be verifiable. The latest row per user is
// the source of truth for "is AI active right now" decisions; older
// rows are kept for the GDPR Art. 7 burden of proof.
//
// `kind` is a free-text discriminator the iOS / web client uses to
// distinguish "ai_full" / "ai_insights_only" / future scopes without
// requiring a schema migration per new tier.
model ConsentReceipt {
id String @id @default(cuid())
userId String @map("user_id")
kind String
/// Raw consent artefact — either a base64-encoded PDF or a signed
/// JWT. The server does not parse this payload; the audit trail is
/// the storage itself plus the `signedAt` timestamp.
artefact String @db.Text
/// Wall-clock the client recorded the user's tap. UTC ISO-8601.
signedAt DateTime @map("signed_at")
/// Optional revocation marker — set when a follow-up "revoke" row
/// is minted so the latest-per-user lookup can shortcut to "no
/// active consent" without diffing the kind discriminator.
revokedAt DateTime? @map("revoked_at")
createdAt DateTime @default(now()) @map("created_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId, createdAt(sort: Desc)])
@@map("consent_receipts")
}
// ─── Push attempt ledger (v1.4.49) ───────────────────────────
//
// Per-attempt push-delivery history. Every sender on the dispatcher
// cascade (APNS, WEB_PUSH, TELEGRAM, NTFY) writes one fire-and-forget
// row per dispatch so the admin diagnostic endpoint can surface the
// trailing 20 attempts per user without needing DB shell access.
//
// The write is best-effort: a DB hiccup on the insert must not break
// the actual push delivery, so every sender wraps the `create()` in a
// `void … .catch(() => {})` block. There is no retry — a single
// missing row is acceptable.
//
// Retention: a daily cron at 03:35 Europe/Berlin (between the
// audit-log cleanup at 03:15 and the mood-reminder cleanup at 03:25)
// deletes rows older than 90 days. See `handlePushAttemptCleanup` in
// `src/lib/jobs/reminder-worker.ts`.
//
// `eventType` mirrors the dispatcher's `NotificationPayload.eventType`
// string (`MEDICATION_REMINDER`, `MOOD_REMINDER`, `PERSONAL_RECORD`,
// `MEASUREMENT_ANOMALY`, `SYSTEM_ALERT`, …); stored as plain text so
// future event-type additions never require a schema migration.
//
// `channel` is the dispatcher `ChannelType` string (`APNS`,
// `WEB_PUSH`, `TELEGRAM`, `NTFY`).
//
// `result` is one of `"ok"` | `"error"` | `"skipped"` — `skipped`
// covers the "no recipient" / "config missing" soft cases.
//
// `reason` is the failure reason on `error` or `skipped` results, and
// null on `ok`. For APNS this is Apple's reason code
// (`BadDeviceToken`, `Unregistered`, …); for Web Push it carries the
// classified `web_push_*` reason; Telegram / NTFY likewise mirror
// their sender-side `SendOutcome.reason`.
model PushAttempt {
id String @id @default(cuid())
userId String @map("user_id")
channel String // "APNS" | "WEB_PUSH" | "TELEGRAM" | "NTFY"
eventType String @map("event_type") // dispatcher event-type string
result String // "ok" | "error" | "skipped"
reason String? // sender-side reason on error/skip; null on ok
createdAt DateTime @default(now()) @map("created_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId, createdAt(sort: Desc)])
@@map("push_attempts")
}
Reference in new issue View Git Blame Copy Permalink