Project 03: Personal Health Tracker 🏃‍♀️

-- Core tables with security in mind
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_login TIMESTAMP WITH TIME ZONE,
    is_verified BOOLEAN DEFAULT FALSE,
    mfa_secret VARCHAR(255) -- Optional 2FA
);

-- Health entries table
CREATE TABLE health_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    entry_date DATE NOT NULL,
    mood INTEGER CHECK (mood BETWEEN 1 AND 10),
    energy_level INTEGER CHECK (energy_level BETWEEN 1 AND 10),
    sleep_hours DECIMAL(3,1),
    sleep_quality INTEGER CHECK (sleep_quality BETWEEN 1 AND 5),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, entry_date)
);

-- Symptoms tracking
CREATE TABLE symptoms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    severity INTEGER CHECK (severity BETWEEN 1 AND 10),
    logged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    duration_minutes INTEGER,
    triggers TEXT,
    notes TEXT
);

-- Enable Row Level Security
ALTER TABLE health_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE symptoms ENABLE ROW LEVEL SECURITY;

-- Users can only see their own data
CREATE POLICY "Users can view own health entries" ON health_entries
    FOR ALL USING (auth.uid() = user_id);

CREATE POLICY "Users can view own symptoms" ON symptoms
    FOR ALL USING (auth.uid() = user_id);

// Medication schema with reminders
CREATE TABLE medications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    dosage VARCHAR(100),
    frequency VARCHAR(100), -- "twice daily", "every 8 hours"
    start_date DATE NOT NULL,
    end_date DATE,
    reminder_times TIME[], -- Array of reminder times
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE medication_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    medication_id UUID REFERENCES medications(id) ON DELETE CASCADE,
    taken_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    skipped BOOLEAN DEFAULT FALSE,
    notes TEXT
);

// API endpoint for medication reminders
app.get('/api/medications/due', async (req, res) => {
  const { userId } = req.user;
  const now = new Date();
  
  const dueMedications = await db.query(`
    SELECT m.*, 
           COALESCE(
             (SELECT taken_at FROM medication_logs ml 
              WHERE ml.medication_id = m.id 
              AND DATE(ml.taken_at) = CURRENT_DATE 
              ORDER BY taken_at DESC LIMIT 1), 
             NULL
           ) as last_taken_today
    FROM medications m
    WHERE m.user_id = $1 
      AND m.is_active = true
      AND m.start_date <= CURRENT_DATE
      AND (m.end_date IS NULL OR m.end_date >= CURRENT_DATE)
  `, [userId]);
  
  // Check which medications are due based on frequency
  const due = dueMedications.rows.filter(med => {
    if (!med.last_taken_today) return true;
    // Calculate if enough time has passed based on frequency
    return isTimeDue(med.frequency, med.last_taken_today);
  });
  
  res.json(due);
\});

// Push notifications for reminders
self.addEventListener('push', event => \{
  const data = event.data.json();
  
  self.registration.showNotification('Medication Reminder', \{
    body: `Time to take ${data.medication}`,
    icon: '/icons/pill-icon.png',
    badge: '/icons/badge.png',
    actions: [
          { action: 'taken', title: 'Mark as taken' },
    { action: 'snooze', title: 'Snooze 30 min' }
    ]
  \});
\});

// Aggregated health metrics view
CREATE VIEW health_metrics AS
SELECT 
    user_id,
    DATE_TRUNC('week', entry_date) as week,
    AVG(mood) as avg_mood,
    AVG(energy_level) as avg_energy,
    AVG(sleep_hours) as avg_sleep_hours,
    AVG(sleep_quality) as avg_sleep_quality,
    COUNT(*) as entries_count
FROM health_entries
GROUP BY user_id, DATE_TRUNC('week', entry_date);

// Symptom patterns analysis
CREATE VIEW symptom_patterns AS
SELECT 
    s.user_id,
    s.name as symptom,
    DATE_TRUNC('day', s.logged_at) as day,
    AVG(s.severity) as avg_severity,
    COUNT(*) as occurrences,
    STRING_AGG(DISTINCT s.triggers, ', ') as common_triggers
FROM symptoms s
GROUP BY s.user_id, s.name, DATE_TRUNC('day', s.logged_at);

// Frontend visualization with Chart.js
export async function loadHealthTrends(timeRange = '30d') \{
  const response = await fetch(`/api/health/trends?range=${timeRange}`);
  const data = await response.json();
  
  // Mood trend chart
  new Chart(document.getElementById('moodChart'), \{
    type: 'line',
    data: \{
      labels: data.dates,
      datasets: [\{
        label: 'Mood',
        data: data.moods,
        borderColor: 'rgb(75, 192, 192)',
        tension: 0.4
      \}, \{
        label: 'Energy',
        data: data.energy,
        borderColor: 'rgb(255, 159, 64)',
        tension: 0.4
      \}]
    \},
    options: \{
      responsive: true,
      plugins: \{
        annotation: \{
          annotations: \{
            // Mark medication start dates
            ...data.medicationMarkers
          \}
        \}
      \}
    \}
  \});
  
  // Sleep quality heatmap
  createSleepHeatmap(data.sleepData);
  
  // Symptom correlation matrix
  createSymptomCorrelations(data.symptoms);
\}

// Encrypted health reports
CREATE TABLE shared_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    share_code VARCHAR(20) UNIQUE NOT NULL,
    encrypted_data TEXT NOT NULL, -- Encrypted JSON
    encryption_key_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    access_count INTEGER DEFAULT 0,
    max_access_count INTEGER DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

// Generate shareable health report
app.post('/api/reports/share', async (req, res) => \{
  const { userId } = req.user;
  const { dateRange, includeSymptoms, includeMedications } = req.body;
  
  // Gather health data
  const healthData = await gatherHealthData(userId, dateRange, {
    includeSymptoms,
    includeMedications
  });
  
  // Generate encryption key
  const encryptionKey = crypto.randomBytes(32);
  const shareCode = generateShareCode();
  
  // Encrypt the report
  const encrypted = await encryptData(healthData, encryptionKey);
  
  // Store encrypted report
  await db.query(`
    INSERT INTO shared_reports 
    (user_id, share_code, encrypted_data, encryption_key_hash, expires_at)
    VALUES ($1, $2, $3, $4, $5)
  `, [
    userId,
    shareCode,
    encrypted,
    hashKey(encryptionKey),
    new Date(Date.now() + 7 * 24 * 60 * 60 * 1000) // 7 days
  ]);
  
  // Return share link with key
  const shareLink = `${process.env.APP_URL}/shared/${shareCode}#${encryptionKey.toString('base64')}`;
  
  res.json({ shareLink, expiresIn: '7 days' });
});

// View shared report (no auth required)
app.get('/shared/:shareCode', async (req, res) => {
  const { shareCode } = req.params;
  const encryptionKey = req.headers['x-encryption-key'];
  
  const report = await db.query(`
    UPDATE shared_reports 
    SET access_count = access_count + 1
    WHERE share_code = $1 
      AND expires_at > NOW() 
      AND access_count < max_access_count
    RETURNING encrypted_data, encryption_key_hash
  `, [shareCode]);
  
  if (!report.rows[0]) \{
    return res.status(404).json({ error: 'Report not found or expired' });
  \}
  
  // Verify encryption key
  if (!verifyKey(encryptionKey, report.rows[0].encryption_key_hash)) \{
    return res.status(403).json({ error: 'Invalid decryption key' });
  \}
  
  // Decrypt and return data
  const decrypted = await decryptData(report.rows[0].encrypted_data, encryptionKey);
  res.json(decrypted);
\});

// Service worker for offline support
self.addEventListener('install', event => \{
  event.waitUntil(
    caches.open('health-tracker-v1').then(cache => \{
      return cache.addAll([
        '/',
        '/dashboard',
        '/medications',
        '/symptoms',
        '/app.js',
        '/app.css',
        '/icons/icon-192.png'
      ]);
    \})
  );
\});

// IndexedDB for offline data
const db = await openDB('HealthTracker', 1, \{
  upgrade(db) \{
    // Offline health entries
    const entries = db.createObjectStore('health_entries', \{
      keyPath: 'id',
      autoIncrement: true
    \});
    entries.createIndex('sync_status', 'sync_status');
    entries.createIndex('entry_date', 'entry_date');
    
    // Offline symptoms
    const symptoms = db.createObjectStore('symptoms', \{
      keyPath: 'id',
      autoIncrement: true
    \});
    symptoms.createIndex('sync_status', 'sync_status');
  \}
\});

// Sync when back online
window.addEventListener('online', async () => \{
  const unsyncedEntries = await db.getAllFromIndex(
    'health_entries', 
    'sync_status', 
    'pending'
  );
  
  for (const entry of unsyncedEntries) \{
    try \{
      await syncEntry(entry);
      entry.sync_status = 'synced';
      await db.put('health_entries', entry);
    \} catch (error) \{
      console.error('Sync failed for entry:', entry.id);
    \}
  \}
  
  showNotification('Data synced successfully!');
\});

# Clone starter template
git clone https://github.com/telebort/health-tracker-starter
cd health-tracker-starter

# Install dependencies
npm install

# Set up environment
cp .env.example .env
# Add Supabase, Redis, and other keys

# Run development
npm run dev

// 1. Never store sensitive data in plain text
const hashPassword = async (password) => \{
  return await argon2.hash(password, \{
    type: argon2.argon2id,
    memoryCost: 2 ** 16,
    timeCost: 3,
    parallelism: 1,
  \});
\};

// 2. Use row-level security in Supabase
CREATE POLICY "Users can only access own data"
ON health_entries
USING (auth.uid() = user_id);

// 3. Implement rate limiting
app.register(rateLimit, \{
  max: 100,
  timeWindow: '1 minute'
\});

// 4. Validate all inputs
const entrySchema = z.object(\{
  mood: z.number().min(1).max(10),
  energy_level: z.number().min(1).max(10),
  sleep_hours: z.number().min(0).max(24),
  notes: z.string().max(1000).optional()
\});

// 5. Use HTTPS everywhere
app.register(fastifySecureSession, \{
  secret: process.env.SESSION_SECRET,
  cookie: \{
    secure: true,
    httpOnly: true,
    sameSite: 'strict'
  \}
\});