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.12.7
HealthLog/prisma/schema.prisma
T
Marc-André Bombeck 1a478d3028 feat(whoop): native-scheme connect redirect and Bearer connect ticket 
Two connect-in-app enhancements for the native client.

return_scheme: GET /api/whoop/connect accepts an optional custom scheme,
threaded through the server-side OAuth-state row so it survives the round
trip without ever riding in the URL or cookie. When a valid scheme is
present, the callback's final redirect targets <scheme>://whoop?whoop=
connected|error&reason=... so an in-app web session auto-completes on its
custom-scheme match; absent or invalid, the web settings redirect is
unchanged. Schemes validate against a strict allowlist (custom app scheme
only, ^[a-z][a-z0-9.+-]*$, http/https/javascript/data/file/vbscript
rejected, pinned to dev.healthlog.app). A rejected scheme falls back to
the web redirect and never reflects an arbitrary value.

Bearer connect ticket: a purely Bearer-authenticated client holds no web
session cookie, so it cannot start the handshake through the cookie-gated
connect route. POST /api/whoop/connect/ticket (Bearer) mints a one-time,
~60s, opaque ticket bound to the user; only its HMAC-SHA256 hash is stored
(the raw value never persists). GET /api/whoop/connect?ticket=<opaque>
accepts the ticket in lieu of a cookie, consumes it atomically (single-use,
reuse rejected), sets the nonce cookie, and 302s to WHOOP exactly as the
cookie path. Expired, consumed, or invalid tickets return a typed 401. The
two combine. The daily WHOOP OAuth-state cleanup also sweeps expired ticket
rows.

The ticket query param is added to the egress redaction denylist so the
opaque value never reaches logs or error reports.

Migration 0124 adds whoop_connect_tickets (id, user_id FK cascade,
token_hash unique, expires_at, consumed_at, created_at) and a nullable
return_scheme column on whoop_oauth_states.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
(cherry picked from commit 221b362011d52dda314139e0be7cc195c47d016e)
2026-06-05 08:10:13 +02:00

3123 lines
159 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.
//
// ─── Timezone storage convention (UTC-only invariant) ───────────────
//
// The database is UTC-only. Almost every `DateTime` column is the
// Prisma default `timestamp(3)` WITHOUT time zone; a handful of rollup
// columns (`MeasurementRollup.bucketStart` / `computedAt`, and the mood
// rollup buckets) carry `@db.Timestamptz(3)`. This is NOT a live bug:
// Prisma always reads and writes UTC over the wire, so both types
// round-trip the same instant. The invariant every reader, writer, and
// raw-SQL query MUST hold is:
//
// * The application server and the Postgres session run in UTC.
// * Wall-clock / calendar-local anchoring lives in an explicit `tz`
// string column (e.g. `MoodEntry.tz`) or the user's `timezone`, NOT
// in the column type.
// * Raw SQL that does date math on a `timestamp` column treats the
// stored value as UTC; never compare a bare `timestamp` against
// `now()`/`current_timestamp` with the expectation of a zone offset.
//
// Do NOT do a `timestamp` → `timestamptz` type sweep ad hoc — the mixed
// types are harmless under the UTC invariant, and a partial sweep is
// riskier than the documented convention. If a server-region move is
// ever on the table, convert the whole schema in one audited migration
// (`ALTER ... TYPE timestamptz USING ... AT TIME ZONE 'UTC'`).
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")
// v1.11.0 — WHOOP OAuth credentials (per-user BYO-keys, encrypted at app
// level). The per-app authorized-user cap makes a single shared WHOOP app
// unworkable for a multi-operator product, so each self-hoster registers
// their own WHOOP dev app and pastes the client id/secret into Settings.
whoopClientIdEncrypted String? @map("whoop_client_id_encrypted")
whoopClientSecretEncrypted String? @map("whoop_client_secret_encrypted")
// v1.12.0 — Fitbit/Pixel OAuth credentials (per-user BYO-keys, encrypted at
// app level) over the Google Health API. The per-app authorized-user cap in
// Google's Testing mode makes a single shared client unworkable for a
// multi-operator product, so each self-hoster registers their own Google
// Cloud OAuth client and pastes the client id/secret into Settings.
fitbitClientIdEncrypted String? @map("fitbit_client_id_encrypted")
fitbitClientSecretEncrypted String? @map("fitbit_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[]
// v1.11.0 — WHOOP integration. Mirrors the Withings connection (1:1) +
// OAuth-state ledger (1:N) relations.
whoopConnection WhoopConnection?
whoopOAuthStates WhoopOAuthState[]
whoopConnectTickets WhoopConnectTicket[]
// v1.12.0 — Fitbit/Pixel integration. Mirrors the WHOOP connection (1:1) +
// OAuth-state ledger (1:N) relations.
fitbitConnection FitbitConnection?
fitbitOAuthStates FitbitOAuthState[]
moodEntries MoodEntry[]
notificationChannels NotificationChannel[]
pushSubscriptions PushSubscription[]
dataBackups DataBackup[]
achievements UserAchievement[]
feedback Feedback[]
recommendationFeedback RecommendationFeedback[]
idempotencyKeys IdempotencyKey[]
devices Device[]
integrationStatuses IntegrationStatus[]
coachConversations CoachConversation[]
coachFacts CoachFact[]
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.10.3 — per-(user, day) Strain personal-anchor cache (day-total
// TRIMP + running EWMA reference) so the nightly Strain engine anchors
// the 0–100 map to the user's own training load without re-integrating
// the 42-day chronic window of HR series each night.
strainTrimpCache StrainTrimpCache[]
// v1.11.0 — owner-minted clinician share links. Each row grants a
// scoped, time-limited, revocable read view of the health record to a
// clinician holding the raw `hls_` token (stored only as an HMAC hash).
clinicianShareLinks ClinicianShareLink[]
// v1.11.0 W1 — durable per-provider AI-health ledger (one row per
// provider chain entry) so a known-bad credential is negatively cached
// across workers instead of re-burning a 401 every generation.
providerHealth ProviderHealth[]
// v1.11.0 W3 — one durable period-narrative row per (period, locale).
// Delete/regenerate-clean; the generated prose is AES-256-GCM at rest.
insightNarratives InsightNarrative[]
// 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.
// v1.10.0 — additive HealthKit signals (WX-A)
// Seven previously-deferred quantity identifiers, wired end-to-end with
// the generic metric-status assessment treatment. Each rides the same
// server-side `convertToDbUnit` + plausibility-range path as the rest of
// the HealthKit surface; see `src/lib/measurements/apple-health-mapping.ts`.
CARDIO_RECOVERY // bpm — HealthKit `heartRateRecoveryOneMinute` (iOS 16+). The drop in heart rate one minute after peak exercise; a larger drop signals fitter autonomic recovery (higher-better).
WRIST_TEMPERATURE // celsius — HealthKit `appleSleepingWristTemperature` (iOS 16+). Overnight wrist temperature; Apple surfaces it as a baseline deviation, we store the absolute reading. DISTINCT from BODY_TEMPERATURE (core) and SKIN_TEMPERATURE (Withings dermal spot).
FALL_COUNT // count — HealthKit `numberOfTimesFallen`. Hard-fall detections; cumulative daily count, lower-better.
SIX_MINUTE_WALK_DISTANCE // metres — HealthKit `sixMinuteWalkTestDistance`. Apple's estimated 6-minute-walk-test distance; mobility + cardiopulmonary endurance signal, higher-better.
STAIR_ASCENT_SPEED // metres per second — HealthKit `stairAscentSpeed`. Mobility section gait metric measured while climbing stairs, higher-better.
STAIR_DESCENT_SPEED // metres per second — HealthKit `stairDescentSpeed`. Mobility section gait metric measured while descending stairs, higher-better.
BREATHING_DISTURBANCES // count — HealthKit `appleSleepingBreathingDisturbances` (iOS 18+). Per-night sleep-breathing-disturbance index Apple classifies as NotElevated / Elevated; lower-better.
// v1.10.0 — categorical events (WX-B)
// Device-flagged EVENT rows. These are NOT continuous measurements —
// each row records a discrete on-device notification the user's wearable
// produced (Apple Watch / Withings ScanWatch). `value` is always 1 (one
// fired event); the device's verdict / severity lands in the
// `rhythmClassification` column (see the `RhythmClassification` enum and
// the CHECK constraint in migration 0105). HealthLog never re-derives or
// re-classifies the underlying signal — it only stores and surfaces the
// result the device's own certified algorithm already emitted.
IRREGULAR_RHYTHM_NOTIFICATION // HealthKit `HKCategoryTypeIdentifierIrregularHeartRhythmEvent` + ScanWatch AFib screening. The certified on-device algorithm flagged a possible irregular rhythm. AWARENESS ONLY — never a HealthLog diagnosis.
HIGH_HEART_RATE_EVENT // HealthKit `HKCategoryTypeIdentifierHighHeartRateEvent`. The watch detected a sustained high heart rate above the user's configured threshold while apparently inactive.
LOW_HEART_RATE_EVENT // HealthKit `HKCategoryTypeIdentifierLowHeartRateEvent`. The watch detected a sustained low heart rate below the user's configured threshold.
WALKING_STEADINESS_EVENT // HealthKit `HKCategoryTypeIdentifierAppleWalkingSteadinessEvent`. The device flagged low / very-low walking steadiness (a falls-risk mobility signal); the severity rides in `rhythmClassification`.
BREATHING_DISTURBANCE_EVENT // HealthKit `HKCategoryTypeIdentifierSleepApneaEvent` + `AppleSleepingBreathingDisturbances`. The device flagged an elevated breathing-disturbance / possible sleep-apnea signal during sleep. SCREENING SIGNAL ONLY — never a HealthLog diagnosis.
// v1.10.0 — computed scores (WX-C)
// Server-derived wellness scores (0–100, unit `score`). Each is minted by a
// nightly engine from the user's already-stored signals and persisted as a
// `COMPUTED`-source Measurement row — never ingested from a client. Only
// RECOVERY_SCORE is computed in v1.10.0; STRESS_SCORE + STRAIN_SCORE are
// defined now so the later engines that compute them need no schema change.
// These are NOT clinical vitals — they never appear in the doctor-report PDF.
RECOVERY_SCORE // Daily recovery / readiness proxy. Higher = more recovered. Blends RHR + HRV (SDNN) + sleep + respiratory-rate deviation from the personal baseline (Plews 2013 / Buchheit 2014 lineage). Descriptive, not a clinical or training-recovery assessment. v1.11.0: WHOOP ships a device-native Recovery into this SAME type, distinguished by `source = WHOOP` vs `COMPUTED` — the two unique keys keep both rows; the cross-source picker decides display.
STRESS_SCORE // Reserved for the later strain/stress engine. Higher = more physiological stress. No engine computes it in v1.10.0.
STRAIN_SCORE // Reserved for the later strain/stress engine. Higher = more cardiovascular strain (workout-load lineage). No engine computes it in v1.10.0. WHOOP day-strain does NOT reuse this — it lands in DAY_STRAIN so the WHOOP-native value and HealthLog's TRIMP proxy never share a bucket.
// ── v1.11.0 — WHOOP integration (additive) ──
// Native WHOOP scores ingest as first-class `source = WHOOP` Measurement
// rows. Where the concept already exists as a COMPUTED type (RECOVERY_SCORE)
// the WHOOP row reuses that type and is distinguished by source; where it
// would collide with a HealthLog engine (STRAIN_SCORE) or a different
// physiological measure (SDNN HEART_RATE_VARIABILITY) it gets a NEW type so
// the native value and the derived proxy never share a bucket. See
// `.planning/v1.11-build/epic-A-whoop-buildspec.md` §3.2 / §5.
HRV_RMSSD // ms — WHOOP `recovery.score.hrv_rmssd_milli`. The RMSSD HRV metric WHOOP reports; kept structurally distinct from the SDNN HEART_RATE_VARIABILITY (different estimator, not interchangeable).
DAY_STRAIN // score (0–21) — WHOOP `cycle.score.strain`. The day's cardiovascular load on WHOOP's 0–21 scale. Distinct from the COMPUTED STRAIN_SCORE (0–100 TRIMP proxy) so the two never share a bucket.
WORKOUT_STRAIN // score (0–21) — WHOOP `workout.score.strain`. Per-workout strain; preferentially stored in `Workout.metadata` (tied to the workout row) rather than as a free-floating Measurement, but the type exists for the rare detached case.
SLEEP_PERFORMANCE // % (0–100) — WHOOP `sleep.score.sleep_performance_percentage`. Sleep achieved vs sleep needed.
SLEEP_EFFICIENCY // % (0–100) — WHOOP `sleep.score.sleep_efficiency_percentage`. Time asleep vs time in bed.
SLEEP_CONSISTENCY // % (0–100) — WHOOP `sleep.score.sleep_consistency_percentage`. Night-to-night bed/wake timing regularity.
SLEEP_NEED // minutes — WHOOP `sleep.score.sleep_needed.baseline_milli` (+ debt / strain / nap components; ms→min). The model's recommended sleep duration for the night.
ENERGY_EXPENDITURE_KJ // kJ — WHOOP `cycle.score.kilojoule`. Day energy expenditure in kilojoules (WHOOP reports kJ natively; kept in kJ rather than converted to kcal so the device-native value round-trips).
@@map("measurement_type")
}
/// v1.10.0 — categorical events (WX-B)
/// The device's own classification RESULT / severity for an EVENT row.
/// Non-NULL only for the categorical EVENT `MeasurementType`s
/// (IRREGULAR_RHYTHM_NOTIFICATION, HIGH/LOW_HEART_RATE_EVENT,
/// WALKING_STEADINESS_EVENT, BREATHING_DISTURBANCE_EVENT), NULL for every
/// continuous measurement — mirroring the `GlucoseContext` / `SleepStage`
/// nullable-context precedent.
///
/// HealthLog stores ONLY the verdict the device's FDA-cleared / CE-marked
/// on-device algorithm already produced. It never re-classifies a raw
/// signal and never emits a diagnosis of its own. `IRREGULAR` means the
/// device flagged a possible irregular rhythm; `NOT_DETECTED` /
/// `INCONCLUSIVE` carry the device's own non-positive verdicts so the
/// awareness timeline can be honest rather than only ever showing alarms.
/// `LOW` / `VERY_LOW` carry the walking-steadiness severity; `FIRED` is the
/// neutral "this event occurred" verdict for the high/low-HR + breathing
/// events that have no finer device-side grading.
enum RhythmClassification {
IRREGULAR // device flagged a possible irregular rhythm (AFib screening)
NOT_DETECTED // device's algorithm ran and did not flag an irregular rhythm
INCONCLUSIVE // device could not classify (poor signal / out-of-range HR)
LOW // walking-steadiness: low
VERY_LOW // walking-steadiness: very low
FIRED // neutral "event occurred" verdict (high/low-HR, breathing disturbance)
@@map("rhythm_classification")
}
/// 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").
// v1.10.0 — computed scores (WX-C)
// Server-derived rows. A `COMPUTED` measurement is minted by a nightly
// engine from the user's already-stored signals (the Recovery score is the
// first); it is NEVER ingested from a client. Server-owned / read-only:
// the client-facing batch + single-POST write surfaces reject it exactly
// like WITHINGS / IMPORT, but it is part of the read enum so iOS can decode
// the rows it queries / charts.
COMPUTED
// v1.11.0 — WHOOP integration. Native WHOOP scores ingest server-side
// (no client write path) as `source = WHOOP` rows. iOS DTO mirrors this
// exact spelling so the `/api/sync/changes` delta feed decodes the rows.
WHOOP
// v1.12.0 — Fitbit/Pixel integration via the Google Health API. Server-owned
// (no client write path); rejected on batch + single POST like WITHINGS/WHOOP.
// iOS DTO mirrors this exact spelling so /api/sync/changes decodes the rows.
FITBIT
@@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.10.0 — categorical events (WX-B). The device's own classification
// verdict / severity for an EVENT row. NULL for every continuous
// measurement; non-NULL only for the categorical EVENT `MeasurementType`s.
// Mirrors the `sleepStage` / `glucoseContext` nullable-context columns. A
// CHECK constraint (migration 0105) enforces the type pairing so a
// continuous row can never carry a classification. NOT part of any unique
// index — dedup stays on `(userId, type, source, externalId)` keyed by the
// device's `HKSample.uuid`, so a re-posted event collapses to a duplicate.
rhythmClassification RhythmClassification? @map("rhythm_classification")
// 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).
// v1.12.1 — these stay FULL uniques (not partial live-row uniques).
// The compound-key write paths (consolidate-daily-mean,
// drain-per-sample-cumulative, the Fitbit / WHOOP / Apple sync +
// import) use `prisma.upsert`, which Prisma 7 compiles to native
// `INSERT ... ON CONFLICT (cols)`; Postgres cannot arbiter an
// `ON CONFLICT` against a partial unique without the predicate (which
// Prisma does not emit), and consolidate-daily-mean deliberately
// RESURRECTS a tombstoned canonical row via `update: { deletedAt:
// null }`, which needs the unique to cover tombstones. So a partial
// unique is unsafe here. The batch-ingest resurrection gap is closed
// instead at the application layer (the existence probe filters
// `deletedAt: null`). Contrast MedicationIntakeEvent, whose write path
// is `create`-only and DID get the partial-unique treatment (0121).
@@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.
// `measurements_dense_intraday_retention_idx` is a partial index over
// `type IN ('HEART_RATE_VARIABILITY','PULSE') AND deleted_at IS NULL`
// — see migration 0108. It accelerates the v1.10.0 dense intra-day
// retention drain's discovery + per-user scan. Same Prisma-can't-
// express-a-partial-predicate reason it is 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)
/// v1.11.1 — source-aware grain. Rollup rows are minted per source so the
/// read path can collapse overlapping sources (e.g. WHOOP + Apple Watch
/// resting heart rate) to the ladder-canonical reading, while cumulative
/// metrics (steps, energy) sum per source. Always non-null; migration 0115
/// purged the legacy source-blind aggregates and the boot backfill re-mints
/// per-source rows.
source MeasurementSource
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, source])
@@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?
samples WorkoutSamples?
/// Ingest dedup. NULL externalId is distinct so two manual
/// entries on the same day never collide here.
///
/// Cross-source dedup (the SAME logical workout arriving from two
/// paired sensors — e.g. Apple Watch + Withings ScanWatch — under
/// different `source`/`externalId` pairs) is handled in two layers,
/// mirroring the measurement-side `pickCanonicalSourceRows()`:
/// * WRITE-time — `dedupeWorkoutBatch()` in
/// src/lib/workouts/canonical-rows.ts groups an inbound batch by
/// (userId, activityType, startedAt ± 90 s) and keeps one
/// canonical row per logical workout BEFORE the insert, so the
/// duplicate never reaches storage.
/// * READ-time — `pickCanonicalWorkoutRows()` in
/// src/lib/measurements/pick-canonical-workout-rows.ts collapses
/// any cross-batch twins (e.g. one source's row from Monday's
/// batch + the other's from Tuesday's backfill) so the dashboard
/// "recent workouts" tile shows one row per logical workout.
/// Both pickers resolve the user's `sourcePriorityJson` ladder.
/// Note (v1.12.1): unlike Measurement / MedicationIntakeEvent, Workout
/// has no `deletedAt` (workout deletes are hard deletes), so this stays
/// a full unique — there is no tombstone to collide with and no
/// resurrection gap to close.
@@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")
}
// ─── Per-workout heart-rate (+ optional speed/power/cadence) series ──
//
// v1.10.0 — a route-independent companion to `WorkoutRoute`.
//
// The route table only exists when a workout has GPS geometry, so an
// INDOOR session (treadmill, bike trainer, strength) had no home for
// its per-sample heart-rate series even though HealthKit ships one.
// That series is the input the training-strain engine (TRIMP /
// time-in-HR-zone) needs, so decoupling it from the route is what
// unblocks indoor strain.
//
// `samples` is a JSONB array `[{ t, hr?, speedMs?, power?, cadence? }]`
// — `t` is an ISO timestamp, `hr` beats/min. Same JSONB-over-PostGIS
// decision as `WorkoutRoute` (no extension dependency on a self-hosted
// Postgres). 1:1 with the workout (FK `workout_id` UNIQUE) so a
// re-ingested batch that wins the workout-row race but loses the
// sample-row race is an idempotent no-op, exactly like the route table.
//
// `sample_count` is denormalised so the strain reader + observability
// can size the series without deserialising the JSONB blob. The ingest
// path caps the count (see MAX_WORKOUT_HR_SAMPLES in
// src/lib/validations/workout.ts) to keep the per-workout payload — and
// the largest new workout-side write stream — bounded.
model WorkoutSamples {
id String @id @default(cuid())
workoutId String @unique @map("workout_id")
/// JSONB array of `{ t: ISO string, hr?: int, speedMs?, power?,
/// cadence? }`. One entry per recorded sample; ordered by `t`
/// ascending at ingest. Capped at MAX_WORKOUT_HR_SAMPLES.
samples Json
/// Denormalised `samples.length` — sized without parsing the blob.
sampleCount Int @map("sample_count")
createdAt DateTime @default(now()) @map("created_at")
workout Workout @relation(fields: [workoutId], references: [id], onDelete: Cascade)
@@map("workout_samples")
}
// ─── Strain personal anchor cache (v1.10.3) ─────────────────
//
// Server-internal per-(user, day) cache that lets the nightly Strain
// engine anchor the 0–100 map to the USER'S OWN recent training-day load
// instead of a fixed population reference. Each row records the scored
// day's day-total Banister TRIMP plus the running EWMA-smoothed personal
// reference and which anchor produced that night's score (personal once
// the cold-start floor is met, population before). Persisting day-total
// TRIMP means the 42-day chronic window reads cheap cached values instead
// of re-integrating 42 days of HR series every night.
//
// NOT a Measurement: it is never read through the derived registry, never
// surfaced on any user-facing surface, never in the doctor PDF or FHIR
// bundle, and never ingested from a client. Reusing the Measurement enum
// would have pulled an internal cache type through every MeasurementType
// completeness wall (chart tokens, categories, PR direction); a dedicated
// table keeps it cleanly server-internal. Idempotent on `(userId, day)` so
// a re-fired nightly tick overwrites the day's cache row in place.
model StrainTrimpCache {
id String @id @default(cuid())
userId String @map("user_id")
/// The scored UTC calendar day (`YYYY-MM-DD`), matching the score row's day key.
day String
/// Day-total Banister TRIMP for the day (0 when no usable HR series).
dayTrimp Float @map("day_trimp")
/// EWMA-smoothed personal reference TRIMP in effect for this day's score.
refPersonal Float @map("ref_personal")
/// Which anchor produced this day's score: `personal` once enough personal
/// training history accrued, `population` during cold start.
anchor String
/// Distinct training days (TRIMP > 0) seen in the chronic window — drives
/// the cold-start gate + the confidence label.
trainingDays Int @map("training_days")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([userId, day])
@@index([userId, day(sort: Desc)])
@@map("strain_trimp_cache")
}
// ─── 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").
/// v1.5 writes go to `rrule` / `rollingIntervalDays` and v1.5.1 flipped
/// the readers to consult the new fields first.
///
/// v1.12.1 — RETAINED, not dropped. The "v1.6.0 drops this column"
/// note here was never executed and is now corrected: the column is
/// still DUAL-WRITTEN on every create/update (the medications POST /
/// PATCH routes serialise the legacy `(daysOfWeek, intervalWeeks)`
/// pair via `serializeScheduleRecurrence`) AND still read by live paths
/// (`gamification/achievements`, `admin/notifications/reminder-check`,
/// the medications list/detail pages parse it through
/// `parseScheduleRecurrence`). Dropping it would break those callers,
/// so it stays until those readers are migrated off the legacy pair.
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.
/// v1.12.1 — this is a partial live-row unique in the DB (`WHERE
/// deleted_at IS NULL`, migration 0121, replacing the 0073 table
/// constraint). A slot the user previously deleted (tombstoned) no
/// longer blocks a re-take: `applyCanonicalSlotWrite` can re-create the
/// row instead of P2002-ing against the tombstone. Schema keeps the
/// full `@@unique` for the `userId_medicationId_scheduledFor_source`
/// compound where-key.
@@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")
}
// ─── WHOOP ─────────────────────────────────────────────────
//
// v1.11.0 — structural copy of the Withings connection/oauth-state pair.
// WHOOP ships solo on copy-of-Withings rails (no provider-adapter
// abstraction yet). Tokens are encrypted at app level via `encrypt()` /
// `decrypt()` — the column names mirror the Withings `access_token` /
// `refresh_token` convention (not `*Encrypted`) so the rotation CLI's
// column registry extends the same way.
model WhoopConnection {
id String @id @default(cuid())
userId String @unique @map("user_id")
whoopUserId String @map("whoop_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")
/// Space-separated OAuth scope string granted by the WHOOP authorisation
/// flow (`offline read:recovery read:sleep read:workout read:cycles
/// read:profile read:body_measurement`). `offline` is required to receive
/// a refresh token. Null = legacy connection that predates scope tracking.
scope String? @map("scope")
lastSyncedAt DateTime? @map("last_synced_at")
/// Set once the self-converging history backfill walks every collection to
/// completion. Null = backfill still in flight (or never started).
backfillCompletedAt DateTime? @map("backfill_completed_at")
/// Max heart rate from the WHOOP body-measurement profile — a profile
/// constant, not a time series, so it lives here rather than as a
/// Measurement row.
maxHeartRate Int? @map("max_heart_rate")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@map("whoop_connections")
}
/// v1.11.0 — short-lived `(nonce → userId)` ledger backing the WHOOP OAuth
/// state check, identical in shape + lifecycle to `WithingsOAuthState`.
///
/// Lifecycle:
/// * `connect` mints a row with `expiresAt = now() + 10 min` and sets the
/// state cookie to JUST the random base64url 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 because
/// the row is gone.
/// * A daily cleanup cron sweeps abandoned rows whose `expiresAt` passed.
model WhoopOAuthState {
nonce String @id
userId String @map("user_id")
createdAt DateTime @default(now()) @map("created_at")
expiresAt DateTime @map("expires_at")
/// v1.12.2 — optional native custom-scheme (e.g. `dev.healthlog.app`) the
/// connect route validated against the strict allowlist. Carried here so it
/// survives the OAuth round-trip server-side (never in the URL/cookie); the
/// callback reads it off the consumed row to drive its FINAL redirect to
/// `<scheme>://whoop?…` instead of the web settings URL. Null = web redirect.
returnScheme String? @map("return_scheme")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([expiresAt])
@@map("whoop_oauth_states")
}
/// v1.12.2 — one-time, Bearer-mintable WHOOP connect ticket. Lets a purely
/// Bearer-authenticated native client (no web-session cookie) start the WHOOP
/// OAuth handshake: it mints a ticket via `POST /api/whoop/connect/ticket`,
/// then opens `GET /api/whoop/connect?ticket=<opaque>` in an in-app web
/// session. The connect route resolves the user from the unconsumed/unexpired
/// ticket IN LIEU of a session cookie.
///
/// Security shape (mirrors the Bearer-token storage pattern):
/// * The raw ticket is returned to the client exactly once and NEVER stored;
/// only its HMAC-SHA256 hash (`tokenHash`, keyed by `API_TOKEN_HMAC_KEY`)
/// lands in the table — a DB read cannot recover a usable ticket.
/// * Single-use: `consumedAt` is stamped by an atomic conditional update at
/// the connect route; a second presentation finds the row already consumed
/// and is rejected.
/// * Short-lived (~60s `expiresAt`); the daily WHOOP OAuth-state cleanup
/// cron also sweeps expired/consumed ticket rows.
model WhoopConnectTicket {
id String @id @default(cuid())
userId String @map("user_id")
/// HMAC-SHA256 of the opaque raw ticket. The raw value never persists.
tokenHash String @unique @map("token_hash")
createdAt DateTime @default(now()) @map("created_at")
expiresAt DateTime @map("expires_at")
/// Stamped on first (and only) successful consumption. Null = still usable.
consumedAt DateTime? @map("consumed_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([expiresAt])
@@map("whoop_connect_tickets")
}
/// v1.12.0 — Fitbit/Pixel connection over the Google Health API. Mirrors
/// `WhoopConnection` 1:1 per user. Tokens are encrypted at the app level
/// before persistence. Unlike WHOOP, Google refresh tokens do not rotate per
/// use, so `refreshToken` is overwritten only when a token response carries a
/// fresh one.
model FitbitConnection {
id String @id @default(cuid())
userId String @unique @map("user_id")
fitbitUserId String @map("fitbit_user_id") // Google/Fitbit external 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")
/// Space-separated OAuth scope string granted by the Google authorisation
/// flow. Null = legacy connection that predates scope tracking.
scope String? @map("scope")
lastSyncedAt DateTime? @map("last_synced_at")
/// Set once the self-converging history backfill walks every collection to
/// completion. Null = backfill still in flight (or never started).
backfillCompletedAt DateTime? @map("backfill_completed_at")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@map("fitbit_connections")
}
/// v1.12.0 — short-lived `(nonce → userId)` ledger backing the Fitbit OAuth
/// state check, identical in shape + lifecycle to `WhoopOAuthState`.
model FitbitOAuthState {
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("fitbit_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
// v1.12.1 — stable per-source dedup id. NULL for native / MANUAL
// entries (those dedup on the legacy `(userId, date, moodLoggedAt)`
// key). When an importer receives a source-stable id (the moodLog
// upstream entry id, a Daylio row id) it writes it here and upserts on
// `@@unique([userId, source, externalId])`, so a re-import is
// idempotent even when `moodLoggedAt` re-rounds / re-zones. Postgres
// treats NULLs as distinct in that unique, so MANUAL rows never
// collide on it. See migration 0122.
externalId String? @map("external_id")
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])
// v1.12.1 — NULL-distinct external dedup key (migration 0122). Importers
// that carry a source-stable id upsert on this; native/MANUAL entries
// (NULL externalId) are distinct here and stay on the legacy key above.
@@unique([userId, source, externalId])
@@index([userId, date])
// v1.12.1 (P1-3) — mood-insights reads filter `{ userId, deletedAt:
// null, moodLoggedAt: { gte } }` ordered by `moodLoggedAt desc`; this
// index turns that into a range scan. See migration 0123.
@@index([userId, moodLoggedAt])
// 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")
/// v1.12.0 — capture-kind discriminator. `BINARY` (default) is a
/// present/absent flag — every tag seeded by 0101 / 0118. `RATED` is a
/// mood *factor* the user scores per entry (e.g. work, social, sleep
/// quality); the score rides on `MoodEntryTagLink.rating`. Keeps one
/// taxonomy + one ingestion path for both flavours.
kind String @default("BINARY") @map("kind") // "BINARY" | "RATED"
/// v1.12.0 — inclusive lower / upper bound of a RATED factor's scale
/// (e.g. 1..5, or 1..2 for a Yes/No factor). Ignored for BINARY tags.
/// The server validates each submitted rating against this range.
scaleMin Int @default(1) @map("scale_min")
scaleMax Int @default(5) @map("scale_max")
/// v1.12.0 — inverse-scaled factor (e.g. stress / conflict) where a
/// HIGHER rating means a WORSE day. Capture stays literal; the (later)
/// analytics layer flips the sign so "up" always reads as "better".
inverse Boolean @default(false) @map("inverse")
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")
/// v1.12.0 — per-entry score for a RATED `MoodTag`. NULL for BINARY
/// links (the present/absent flag carries the signal). Bounded to the
/// tag's `scaleMin..scaleMax` at ingest (server-validated, not a DB
/// CHECK, so a future scale change never strands historical rows).
rating Int? @map("rating")
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])
// v1.12.1 (P1-2) — status-cache reads filter `{ userId, action }`
// ordered by `createdAt desc` on every insight-status card mount;
// this three-column index serves both the findFirst and the take:5 as
// an index range scan over the highest-churn table. See migration 0123.
@@index([userId, action, createdAt(sort: Desc)])
@@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")
}
/// v1.11.0 W1 — durable, multi-instance provider-health ledger.
///
/// Promotes the volatile per-worker `lastWorkingCache`
/// (`provider-runner.ts`) to Postgres, modelled on the atomic-upsert
/// rate-limiter pattern so every worker shares one signal. One row per
/// (user, providerType): the runner records each outcome and reads the
/// row to (a) deprioritise / skip a provider inside its backoff window
/// instead of re-burning a dead round-trip every call, and (b) hold a
/// durable negative-cache for an auth-class failure (401/403 =
/// credential dead) far longer than the old 1h in-memory TTL, surfacing
/// it rather than silently retrying.
///
/// `nextRetryAt` is the backoff gate: while `lastResult = "auth_failed"`
/// and NOW() < `nextRetryAt`, the provider is skipped (negative cache).
/// On a success the row clears to `ok` with a null `nextRetryAt`.
/// Server-internal: never a Measurement, never ingested from a client.
model ProviderHealth {
id String @id @default(cuid())
userId String @map("user_id")
providerType String @map("provider_type") // ProviderChainType tag
lastResult String @map("last_result") // "ok" | "hard_failed" | "auth_failed"
lastStatus Int? @map("last_status") // HTTP status of the last failure; null on ok/network
consecutiveFailures Int @default(0) @map("consecutive_failures")
lastOkAt DateTime? @map("last_ok_at")
lastFailureAt DateTime? @map("last_failure_at")
nextRetryAt DateTime? @map("next_retry_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([userId, providerType])
@@index([userId, lastResult])
@@map("provider_health")
}
// ─── 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")
// v1.11.0 — rolling encrypted summary of the elided older turns, so a long
// conversation keeps memory of what came before without re-sending every
// turn to the provider. AES-256-GCM (same codec as CoachMessage.encrypted).
summaryEncrypted Bytes? @map("summary_encrypted")
summaryUpdatedAt DateTime? @map("summary_updated_at")
summaryTurnCount Int @default(0) @map("summary_turn_count")
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")
}
// v1.11.1 (Epic B, B-W7) — durable, user-scoped personal facts the Coach
// extracts from conversations: stable preferences, conditions the user states
// about themselves, goals, constraints, durable life context. The fact TEXT is
// encrypted at rest (AES-256-GCM, the same codec as CoachMessage). `category`,
// `confidence`, and `sourceConversationId` stay plain so the injection picker
// can rank without paying a per-row decrypt. Descriptive, never diagnostic.
// Soft-delete via `deletedAt` so a user "forget this" action is reversible /
// auditable and re-extraction can avoid resurrecting it.
model CoachFact {
id String @id @default(cuid())
userId String @map("user_id")
factEncrypted Bytes @map("fact_encrypted")
// App-side closed enum (not a DB enum, to keep the migration purely additive
// and avoid an enum-migration per new category): preference | condition |
// goal | constraint | context.
category String
// 0..100 server-assigned extraction confidence.
confidence Int @default(50)
// Provenance — nullable so a future manual-entry surface can leave it null.
sourceConversationId String? @map("source_conversation_id")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
// Soft-delete: hides the fact from injection but keeps the row for audit.
deletedAt DateTime? @map("deleted_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId, deletedAt, confidence])
@@map("coach_facts")
}
// 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")
}
// ─── Clinician share links (scoped, time-limited, revocable read view) ───
//
// v1.11.0 — a user mints a link they hand a clinician (email, paper, QR).
// The clinician opens it in any browser with no account and sees a
// read-only view of the health record. The raw `hls_` token is stored
// ONLY as an HMAC-SHA256 hash (`tokenHash`, same scheme as
// `ApiToken.tokenHash`, keyed by `API_TOKEN_HMAC_KEY`); the plaintext is
// returned to the owner exactly once at creation and never persisted.
//
// The scope is FROZEN at creation: `rangeStart`, `sectionsJson` and
// `resourceTypes` are write-once and a share can NEVER widen later — only
// `revokedAt`, `lastAccessAt` and `accessCount` mutate after mint. This
// mirrors the append-only spirit of `ConsentReceipt`. `expiresAt` is
// mandatory (capped at `SHARE_LINK_MAX_DAYS`, default 90) — there is no
// never-expiring share. Deleting the user cascades every share away so
// the GDPR-erasure path stays clean.
model ClinicianShareLink {
id String @id @default(cuid())
userId String @map("user_id")
/// HMAC-SHA256 of the raw `hls_` token (same scheme as `ApiToken.tokenHash`).
tokenHash String @unique @map("token_hash")
/// Human label the owner sets (e.g. a clinic/specialty note). Plaintext, bounded.
label String
// ── Frozen scope (write-once at creation; never widened) ──
/// Reporting-window start (absolute, not "last N days"). Frozen so a
/// rolling share can never reach data older than the owner chose.
rangeStart DateTime @map("range_start")
/// Window end. Null = rolling ("up to now"); set = frozen snapshot window.
rangeEnd DateTime? @map("range_end")
/// Section toggles (the `DoctorReportPrefs` shape the export uses). JSON.
sectionsJson Json @map("sections_json")
/// FHIR resource types this link may serve (a subset of the catalogue),
/// e.g. `["Observation","MedicationStatement"]`.
resourceTypes String[] @map("resource_types")
/// Whether the FHIR API is reachable via this link at all (vs the view only).
allowFhirApi Boolean @default(false) @map("allow_fhir_api")
/// REQUIRED — no never-expiring share (capped at `SHARE_LINK_MAX_DAYS`).
expiresAt DateTime @map("expires_at")
createdAt DateTime @default(now()) @map("created_at")
// ── Mutable lifecycle/access counters (the only columns that change) ──
revokedAt DateTime? @map("revoked_at")
lastAccessAt DateTime? @map("last_access_at")
accessCount Int @default(0) @map("access_count")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId, createdAt(sort: Desc)])
@@index([expiresAt])
@@map("clinician_share_links")
}
// v1.11.0 W3 — period-narrative cache (Pillar P1).
//
// One durable, typed row per (user, period, locale) carrying the latest
// generated period summary ("how your week/month went"). Delete +
// regenerate clean: the unique constraint enforces a single row per slot,
// so a regeneration upserts in place and there is never a stale duplicate
// to disambiguate. The generated prose is held AES-256-GCM at rest in
// `encryptedContent` (Bytes), following the `CoachMessage.encryptedContent`
// precedent — the body is descriptive health narrative and is treated as
// sensitive even though it carries labels-plus-numbers, not raw rows.
//
// `provenanceJson` is the labels-only provenance envelope the surface
// renders as ⓘ chips (metric names + the read window + the FDR footer);
// it carries no PII so it stays plaintext for a stable, queryable shape,
// matching `CoachMessage.metricSourceJson`. `providerType` + `promptVersion`
// mirror the attribution columns so the cross-feature aggregator can slice
// quality per (provider × prompt). `period` is `week` | `month`; `dateKey`
// is the UTC `YYYY-MM-DD` boundary the row was generated for, so the
// stale-while-revalidate read can tell "today's" narrative from an older
// one without decrypting.
model InsightNarrative {
id String @id @default(cuid())
userId String @map("user_id")
/// "week" | "month".
period String
/// Resolved UI locale of the prose ("de" | "en").
locale String
/// UTC YYYY-MM-DD boundary the narrative was generated for.
dateKey String @map("date_key")
/// AES-256-GCM ciphertext of the generated narrative prose.
encryptedContent Bytes @map("encrypted_content")
/// Labels-only provenance envelope (metric names, window, FDR footer).
provenanceJson String? @map("provenance_json")
providerType String? @map("provider_type")
promptVersion String? @map("prompt_version")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
// One row per (user, period, locale) — regeneration upserts in place.
@@unique([userId, period, locale])
@@index([userId, updatedAt(sort: Desc)])
@@map("insight_narratives")
}
Reference in new issue View Git Blame Copy Permalink