Patient Graph Data Model

┌─────────────────────────────────────────────────────────────┐
│                    PATIENT GRAPH SCHEMA                      │
│                    (patient_graph.*)                         │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  Core Identity                                               │
│  ├─ customer_profiles (demographics, tier, conditions)      │
│  └─ identity_mappings (Clerk, Rimo, BigCommerce)           │
│                                                               │
│  Clinical Data                                               │
│  ├─ lab_results (parsed biomarkers)                         │
│  ├─ protocols (treatment protocols)                         │
│  ├─ patient_events (clinical timeline)                      │
│  └─ conversation_history (Luna AI logs)                     │
│                                                               │
│  Treatment Lifecycle (Rimo Integration)                     │
│  ├─ patient_treatments (treatment orders)                   │
│  └─ patient_prescriptions (prescription fulfillment)        │
│                                                               │
│  Subscriptions & Billing                                     │
│  └─ subscriptions (recurring products, auto-refill)         │
│                                                               │
│  Wearables & Biometrics                                      │
│  ├─ patient_wearable_readings (raw sensor data)            │
│  ├─ patient_wearable_daily_stats (aggregated stats)        │
│  └─ wearable_data (DEPRECATED - use readings/stats)         │
│                                                               │
│  Security & Compliance                                        │
│  └─ rbac_logs (HIPAA audit trail)                           │
│                                                               │
└─────────────────────────────────────────────────────────────┘

'male' | 'female' | 'other'

'pending'    // Initial state, awaiting approval
'approved'   // Clinician approved (Loop Med only)
'rejected'   // Clinician rejected (Loop Med only)
'active'     // Subscription is active and billing
'paused'     // Temporarily paused by customer
'cancelled'  // Permanently cancelled
'completed'  // Naturally completed (finite protocols)

pending → approved → active → [paused ⇄ active] → completed/cancelled
pending → rejected (terminal)

'weekly' | 'monthly' | 'quarterly' | 'biannual' | 'annual'

'active'     // Currently running
'paused'     // Temporarily on hold
'completed'  // Successfully finished
'cancelled'  // Terminated early

'pending'   // Uploaded, not yet reviewed
'reviewed'  // Clinician reviewed
'archived'  // Archived for compliance

'lab_upload'          // Lab report uploaded
'lab_parsed'          // Biomarkers extracted
'protocol_created'    // Protocol created
'protocol_started'    // Protocol started
'protocol_paused'     // Protocol paused
'protocol_completed'  // Protocol completed
'protocol_cancelled'  // Protocol cancelled
'protocol_check_in'   // Patient check-in completed
'note_added'          // Clinical note added
'profile_updated'     // Profile updated
'consent_granted'     // Consent given
'consent_revoked'     // Consent revoked

'admin'      // Full access, all resources
'staff'      // Internal staff, most resources
'support'    // Customer support, read-only patient data
'provider'   // Clinicians, read patient data + write clinical notes
'customer'   // Patients, own data only (ownOnly=true)

'read'    // View data
'write'   // Create or update
'delete'  // Soft or hard delete
'export'  // Export data (compliance-restricted)

'profile'                // Customer profiles
'lab_results'            // Lab reports and biomarkers
'protocols'              // Treatment protocols
'events'                 // Patient events
'audit_log'              // RBAC logs (admin only)
'conversation_history'   // AI conversation logs
'treatment'              // Rimo treatments (new in v1.2.0)

'allowed' | 'denied'

'pending'       // Created, awaiting transmission
'processing'    // Transmission in progress
'transmitted'   // Sent to pharmacy
'failed'        // Transmission failed
'shipped'       // Pharmacy shipped
'delivered'     // Confirmed delivery
'cancelled'     // Order cancelled

'oura' | 'whoop' | 'garmin' | 'fitbit' | 'apple_health' | 'dexcom' | 'libre' | 'manual'

'sleep' | 'recovery' | 'hrv' | 'glucose' | 'strain' | 'activity' | 'readiness' | 'body'

// Sleep metrics
'sleep_duration' | 'sleep_score' | 'rem_sleep' | 'deep_sleep' | 'light_sleep' | 'awakenings'
 
// Recovery & HRV
'hrv' | 'resting_heart_rate' | 'recovery_score'
 
// Glucose
'glucose' | 'glucose_variability'
 
// Activity
'steps' | 'calories' | 'active_minutes' | 'strain_score'
 
// Body composition
'weight' | 'body_fat' | 'muscle_mass'

'clerk_user_id'              // Clerk authentication ID
'email'                      // Email address
'bigcommerce_customer_id'    // BigCommerce CRM
'stripe_customer_id'         // Stripe billing
'hubspot_contact_id'         // HubSpot CRM (legacy)
'phone'                      // Phone number
'fingerprint_visitor_id'     // Fingerprint.js

{
  "id": "123e4567-e89b-12d3-a456-426614174000",
  "externalId": "user_2a1b3c4d5e6f",
  "email": "patient@example.com",
  "firstName": "Jane",
  "lastName": "Doe",
  "dateOfBirth": "1985-06-15",
  "biologicalSex": "female",
  "phoneNumber": "+12125551234",
  "timezone": "America/Los_Angeles",
  "subscriptionTier": "premium",
  "conditions": ["hypothyroidism", "pcos"],
  "medications": ["levothyroxine 100mcg"],
  "allergies": ["penicillin"],
  "tags": ["high-risk", "monthly-check-in"],
  "metadata": {
    "source": "referral",
    "referrer_id": "provider_123"
  }
}

{
  "id": "sub_123",
  "customerId": "cust_456",
  "productId": "semaglutide-0.25mg",
  "productName": "Semaglutide 0.25mg",
  "offeringId": "rimo_offering_789",
  "offeringName": "GLP-1 Weight Management",
  "clinicianId": "dr_smith_123",
  "clinicianName": "Dr. Sarah Smith, MD",
  "monthlyPriceCents": 29900,
  "notes": "Starting dose, titrate after 4 weeks",
  "status": "approved",
  "approvedAt": "2026-03-20T10:00:00Z",
  "startedAt": "2026-03-21T00:00:00Z",
  "interval": "monthly",
  "nextOrderDate": "2026-04-21T00:00:00Z"
}

{
  "id": "reading_123",
  "customerId": "cust_456",
  "dataType": "glucose",
  "value": 95.5,
  "unit": "mg/dL",
  "deviceId": "dexcom_789",
  "deviceType": "dexcom",
  "source": "dexcom_api",
  "recordedAt": "2026-03-20T14:30:00Z",
  "metadata": {
    "trend": "stable",
    "alert_triggered": false
  }
}

import { createRepositories } from '@loop/patient-graph';
import { db } from './db';
 
const repos = createRepositories(db);
 
const profile = await repos.profiles.findByExternalId('clerk_user_123');
const labs = await repos.labs.list({ customerId: profile.data.id, limit: 10 });
const protocols = await repos.protocols.list({ customerId: profile.data.id });
const events = await repos.events.list({ customerId: profile.data.id, limit: 50 });

const glucoseStats = await repos.wearableData.getDailyStatsForDateRange(
  customerId,
  'glucose',
  '2026-03-01',
  '2026-03-20'
);
 
// Returns array of daily aggregations with avg/min/max

// 1. Create treatment (pending approval)
const treatment = await repos.treatments.create({
  customerId: 'cust_123',
  rimoTreatmentId: 'rimo_456',
  offeringId: 'offering_789',
  offeringName: 'GLP-1 Weight Management',
  status: 'pending',
});
 
// 2. Clinician approves
await repos.treatments.update(treatment.data.id, {
  status: 'approved',
  clinicianId: 'dr_smith',
  clinicianName: 'Dr. Sarah Smith, MD',
  approvedAt: new Date().toISOString(),
  notes: 'Starting dose, titrate after 4 weeks',
});
 
// 3. Create prescription
const rx = await repos.prescriptions.create({
  customerId: 'cust_123',
  treatmentId: treatment.data.id,
  rimoOrderId: 'rimo_order_999',
  medicationName: 'Semaglutide',
  dosage: '0.25mg',
  quantity: 4,
  refills: 3,
  prescribedDate: new Date().toISOString(),
  nextRefillDate: addMonths(new Date(), 1).toISOString(),
});

const repos = createRepositories(db);
const result = await repos.profiles.findById(id);

const result = await db.query('SELECT * FROM customer_profiles WHERE id = $1', [id]);

const result = await repos.profiles.findById(id);
 
if (!result.ok) {
  console.error('Failed to fetch profile:', result.error);
  return;
}
 
const profile = result.data; // Type-safe access

await db.transaction(async (tx) => {
  const repos = createRepositories(tx);
 
  const treatment = await repos.treatments.create({...});
  const event = await repos.events.create({
    type: 'treatment_approved',
    customerId: treatment.data.customerId,
  });
});

await repos.rbacLogs.create({
  actorId: user.id,
  actorRole: 'provider',
  customerId: patientId,
  resource: 'lab_results',
  action: 'read',
  outcome: 'allowed',
});

cd packages/shared
pnpm drizzle-kit studio
# Opens Drizzle Studio at http://localhost:4983

cd packages/shared
pnpm drizzle-kit introspect --out=./docs/schema.png