Fitness Tracker

Dashboard • Workouts • Nutrition • Progress • Goals
Local-first health tracking app

Quick Daily Log

Calories In vs Out — Last 14 Days

Macro Split Today

Workout Minutes — Last 14 Days

Achievements

Recent Workouts

Today's Meals

Today's Macro Distribution

Weight Trend vs Goal

Measurement History

Goal Progress

``` --- # FILE 2 — `modelname_health_analytics.html` ```html Health Analytics Dashboard

Health Analytics Dashboard

SQL analytics for calorie balance, training trends, weight trajectory, macros, streaks, and goal progress
PostgreSQL-compatible
Current Weight 82.4 kg Goal: 80.0 kg
7-Day Avg Net Calories -310 Deficit trend
Workout Frequency 4.2 / week Target: 4 / week
Logging Streak 11 days Longest: 18 days

Macro Distribution

Protein 30% Carbs 40% Fat 30%

Weight Trend vs Goal

Goal 80.0 kg Start Latest

Calorie Balance

Blue = surplus Green = deficit

Actionable Insights

Weekly calorie deficit is consistent. Current trend projects goal weight in about 6–8 weeks if adherence remains stable.
Fat intake is slightly high relative to targets. Consider shifting 5–8% of calories from fat to protein to improve satiety and muscle retention.
Workout frequency is on target, but intensity variability is moderate. Add one planned high-intensity or progressive overload session per week.

Recommended KPIs

Track: net calories, protein, sleep, step count, workout minutes, body weight, and waist circumference weekly.
Best motivational metrics: streaks, weekly consistency score, goal progress %, and badge completion.
Avoid over-indexing on single-day weight changes; use 7-day rolling average for more stable trend detection.

1. Calorie Deficit / Surplus Analysis Over Time

Compares daily calories consumed with calories burned, calculates net balance, cumulative deficit/surplus, and rolling 7-day average. 


WITH consumed AS (
  SELECT
    user_id,
    log_date,
    SUM(calories) AS calories
  FROM meals
  WHERE user_id = :user_id
  GROUP BY user_id, log_date
),
burned_base AS (
  SELECT user_id, log_date, calories_burned
  FROM workouts
  WHERE user_id = :user_id

  UNION ALL

  SELECT user_id, log_date, calories_burned
  FROM activities
  WHERE user_id = :user_id
),
burned AS (
  SELECT
    user_id,
    log_date,
    SUM(COALESCE(calories_burned, 0)) AS calories
  FROM burned_base
  GROUP BY user_id, log_date
),
days AS (
  SELECT user_id, log_date
  FROM consumed

  UNION

  SELECT user_id, log_date
  FROM burned
)
SELECT
  d.log_date AS day,
  COALESCE(c.calories, 0) AS consumed_kcal,
  COALESCE(b.calories, 0) AS burned_kcal,
  COALESCE(c.calories, 0) - COALESCE(b.calories, 0) AS net_kcal,
  SUM(
    COALESCE(c.calories, 0) - COALESCE(b.calories, 0)
  ) OVER (ORDER BY d.log_date) AS cumulative_net_kcal,
  ROUND(
    AVG(
      COALESCE(c.calories, 0) - COALESCE(b.calories, 0)
    ) OVER (
      ORDER BY d.log_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
    1
  ) AS avg_7d_net_kcal,
  CASE
    WHEN COALESCE(c.calories, 0) - COALESCE(b.calories, 0) < 0 THEN 'deficit'
    WHEN COALESCE(c.calories, 0) - COALESCE(b.calories, 0) > 0 THEN 'surplus'
    ELSE 'maintenance'
  END AS balance_label
FROM days d
LEFT JOIN consumed c
  ON c.user_id = d.user_id
 AND c.log_date = d.log_date
LEFT JOIN burned b
  ON b.user_id = d.user_id
 AND b.log_date = d.log_date
ORDER BY d.log_date;

2. Workout Frequency and Intensity Trends

Aggregates workouts weekly and scores intensity as low/moderate/high for trend analysis. Useful for detecting stalled progress or overtraining. 


WITH scored AS (
  SELECT
    *,
    CASE intensity
      WHEN 'low' THEN 1
      WHEN 'moderate' THEN 2
      WHEN 'high' THEN 3
      ELSE NULL
    END AS intensity_score
  FROM workouts
  WHERE user_id = :user_id
    AND log_date >= CURRENT_DATE - interval '90 days'
),
weekly AS (
  SELECT
    date_trunc('week', log_date)::date AS week_start,
    COUNT(*) AS sessions,
    SUM(duration_min) AS total_minutes,
    ROUND(AVG(intensity_score), 2) AS avg_intensity_score,
    ROUND(AVG(NULLIF(duration_min, 0)), 1) AS avg_session_minutes,
    SUM(COALESCE(calories_burned, 0)) AS calories_burned
  FROM scored
  GROUP BY 1
)
SELECT
  week_start,
  sessions,
  total_minutes,
  avg_intensity_score,
  avg_session_minutes,
  calories_burned,
  LAG(sessions) OVER (ORDER BY week_start) AS prev_week_sessions,
  sessions - COALESCE(LAG(sessions) OVER (ORDER BY week_start), 0) AS sessions_delta,
  ROUND(
    avg_intensity_score - LAG(avg_intensity_score) OVER (ORDER BY week_start),
    2
  ) AS intensity_delta
FROM weekly
ORDER BY week_start;

3. Weight Loss / Gain Trajectory with Target Date Calculation

Estimates projected goal date using average weekly weight change and compares it to the user's chosen target date. 


WITH logs AS (
  SELECT
    log_date,
    weight_kg
  FROM weight_logs
  WHERE user_id = :user_id
    AND weight_kg IS NOT NULL
),
latest AS (
  SELECT log_date, weight_kg
  FROM logs
  ORDER BY log_date DESC
  LIMIT 1
),
first AS (
  SELECT log_date, weight_kg
  FROM logs
  ORDER BY log_date ASC
  LIMIT 1
),
weekly_changes AS (
  SELECT
    log_date,
    weight_kg - LAG(weight_kg, 7) OVER (ORDER BY log_date) AS weekly_change
  FROM logs
),
trend AS (
  SELECT AVG(weekly_change) AS avg_weekly_change
  FROM weekly_changes
  WHERE weekly_change IS NOT NULL
),
goal AS (
  SELECT
    goal_weight_kg,
    target_date
  FROM health_profiles
  WHERE user_id = :user_id
),
projection AS (
  SELECT
    l.log_date AS latest_date,
    l.weight_kg AS current_weight,
    f.weight_kg AS baseline_weight,
    g.goal_weight_kg,
    g.target_date,
    t.avg_weekly_change,
    CASE
      WHEN g.goal_weight_kg IS NULL THEN NULL
      WHEN t.avg_weekly_change IS NULL OR t.avg_weekly_change = 0 THEN NULL
      WHEN (
        g.goal_weight_kg < l.weight_kg
        AND t.avg_weekly_change < 0
      ) OR (
        g.goal_weight_kg > l.weight_kg
        AND t.avg_weekly_change > 0
      ) THEN ROUND(
        (g.goal_weight_kg - l.weight_kg) / t.avg_weekly_change * 7
      )
      ELSE NULL
    END AS projected_days
  FROM latest l
  CROSS JOIN first f
  CROSS JOIN trend t
  CROSS JOIN goal g
)
SELECT
  latest_date,
  current_weight,
  baseline_weight,
  goal_weight_kg,
  target_date,
  ROUND(avg_weekly_change, 3) AS avg_weekly_weight_change,
  projected_days,
  CASE
    WHEN projected_days IS NOT NULL
      THEN latest_date + projected_days * interval '1 day'
    ELSE NULL
  END AS projected_date,
  CASE
    WHEN projected_days IS NULL THEN 'insufficient_trend'
    WHEN target_date IS NULL THEN 'no_target_date'
    WHEN (target_date - latest_date) >= projected_days THEN 'on_track'
    ELSE 'behind'
  END AS goal_status
FROM projection;

4. Macro Distribution Pie Chart Data

Returns grams and calorie-based percentages for protein, carbs, and fat. Ideal for donut/pie visualizations. 


SELECT
  COALESCE(SUM(protein_g), 0) AS protein_g,
  COALESCE(SUM(carbs_g), 0) AS carbs_g,
  COALESCE(SUM(fat_g), 0) AS fat_g,

  ROUND(
    100 * COALESCE(SUM(protein_g), 0) * 4
    / NULLIF(
      COALESCE(SUM(protein_g), 0) * 4
      + COALESCE(SUM(carbs_g), 0) * 4
      + COALESCE(SUM(fat_g), 0) * 9,
      0
    ),
    1
  ) AS protein_pct,

  ROUND(
    100 * COALESCE(SUM(carbs_g), 0) * 4
    / NULLIF(
      COALESCE(SUM(protein_g), 0) * 4
      + COALESCE(SUM(carbs_g), 0) * 4
      + COALESCE(SUM(fat_g), 0) * 9,
      0
    ),
    1
  ) AS carbs_pct,

  ROUND(
    100 * COALESCE(SUM(fat_g), 0) * 9
    / NULLIF(
      COALESCE(SUM(protein_g), 0) * 4
      + COALESCE(SUM(carbs_g), 0) * 4
      + COALESCE(SUM(fat_g), 0) * 9,
      0
    ),
    1
  ) AS fat_pct
FROM meals
WHERE user_id = :user_id
  AND log_date BETWEEN :start_date AND :end_date;

5. Activity Streaks — Consecutive Days Logged

Uses gaps-and-islands logic to calculate longest streak, most recent streak, and current streak ending today. 


WITH logged AS (
  SELECT DISTINCT
    user_id,
    log_date
  FROM (
    SELECT user_id, log_date
    FROM meals
    WHERE user_id = :user_id

    UNION ALL

    SELECT user_id, log_date
    FROM workouts
    WHERE user_id = :user_id

    UNION ALL

    SELECT user_id, log_date
    FROM activities
    WHERE user_id = :user_id

    UNION ALL

    SELECT user_id, log_date
    FROM weight_logs
    WHERE user_id = :user_id
  ) t
  WHERE log_date <= CURRENT_DATE
),
islands AS (
  SELECT
    user_id,
    log_date,
    log_date - (
      ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY log_date
      ) * interval '1 day'
    )::date AS island
  FROM logged
),
streaks AS (
  SELECT
    user_id,
    island,
    MIN(log_date) AS start_date,
    MAX(log_date) AS end_date,
    COUNT(*) AS streak_length
  FROM islands
  GROUP BY user_id, island
)
SELECT
  l.user_id,
  MAX(s.streak_length) AS longest_streak,

  COALESCE(
    (
      SELECT streak_length
      FROM streaks s2
      WHERE s2.user_id = l.user_id
        AND s2.end_date = (
          SELECT MAX(log_date)
          FROM logged
          WHERE user_id = l.user_id
        )
      ORDER BY streak_length DESC
      LIMIT 1
    ),
    0
  ) AS most_recent_streak,

  CASE
    WHEN (
      SELECT MAX(log_date)
      FROM logged
      WHERE user_id = l.user_id
    ) = CURRENT_DATE
    THEN COALESCE(
      (
        SELECT streak_length
        FROM streaks s3
        WHERE s3.user_id = l.user_id
          AND s3.end_date = CURRENT_DATE
        ORDER BY streak_length DESC
        LIMIT 1
      ),
      0
    )
    ELSE 0
  END AS current_streak

FROM logged l
JOIN streaks s
  ON s.user_id = l.user_id
GROUP BY l.user_id;

6. Goal Progress Percentage Calculations

Compares actual daily/weekly performance against user goals: calories, protein, steps, sleep, workout frequency, and weight trajectory. 


WITH profile AS (
  SELECT *
  FROM health_profiles
  WHERE user_id = :user_id
),
today_meals AS (
  SELECT
    COALESCE(SUM(calories), 0) AS consumed_kcal,
    COALESCE(SUM(protein_g), 0) AS protein_g,
    COALESCE(SUM(carbs_g), 0) AS carbs_g,
    COALESCE(SUM(fat_g), 0) AS fat_g
  FROM meals
  WHERE user_id = :user_id
    AND log_date = CURRENT_DATE
),
today_activity AS (
  SELECT
    COALESCE(SUM(steps), 0) AS steps,
    COALESCE(SUM(sleep_hours), 0) AS sleep_hours
  FROM activities
  WHERE user_id = :user_id
    AND log_date = CURRENT_DATE
),
week_workouts AS (
  SELECT
    COUNT(*) AS sessions,
    COALESCE(SUM(duration_min), 0) AS minutes
  FROM workouts
  WHERE user_id = :user_id
    AND log_date >= CURRENT_DATE - interval '7 days'
),
weights AS (
  SELECT
    (array_agg(weight_kg ORDER BY log_date DESC))[1] AS latest_weight,
    (array_agg(weight_kg ORDER BY log_date ASC))[1] AS baseline_weight
  FROM weight_logs
  WHERE user_id = :user_id
    AND weight_kg IS NOT NULL
),
calculated AS (
  SELECT
    u.name,
    p.daily_calorie_target,
    tm.consumed_kcal,

    ROUND(
      LEAST(
        100,
        GREATEST(
          0,
          100 * tm.consumed_kcal / NULLIF(p.daily_calorie_target, 0)
        )
      ),
      1
    ) AS calorie_progress_pct,

    ROUND(
      LEAST(
        100,
        GREATEST(
          0,
          100 * tm.protein_g / NULLIF(p.protein_target_g, 0)
        )
      ),
      1
    ) AS protein_progress_pct,

    ROUND(
      LEAST(
        100,
        GREATEST(
          0,
          100 * ta.steps / NULLIF(p.step_target, 0)
        )
      ),
      1
    ) AS step_progress_pct,

    ROUND(
      LEAST(
        100,
        GREATEST(
          0,
          100 * ta.sleep_hours / NULLIF(p.sleep_target_hours, 0)
        )
      ),
      1
    ) AS sleep_progress_pct,

    ROUND(
      LEAST(
        100,
        GREATEST(
          0,
          100 * ww.sessions / NULLIF(p.workout_frequency_target, 0)
        )
      ),
      1
    ) AS workout_progress_pct,

    CASE
      WHEN w.latest_weight IS NULL
        OR w.baseline_weight IS NULL
        OR p.goal_weight_kg IS NULL
        OR w.baseline_weight = p.goal_weight_kg
      THEN NULL
      ELSE ROUND(
        LEAST(
          100,
          GREATEST(
            0,
            100 * (w.latest_weight - w.baseline_weight)
            / NULLIF(p.goal_weight_kg - w.baseline_weight, 0)
          )
        ),
        1
      )
    END AS weight_progress_pct,

    6 AS target_count

  FROM users u
  JOIN profile p
    ON p.user_id = u.id
  CROSS JOIN today_meals tm
  CROSS JOIN today_activity ta
  CROSS JOIN week_workouts ww
  CROSS JOIN weights w
)
SELECT
  c.*,
  ROUND(
    (
      COALESCE(c.calorie_progress_pct, 0)
      + COALESCE(c.protein_progress_pct, 0)
      + COALESCE(c.step_progress_pct, 0)
      + COALESCE(c.sleep_progress_pct, 0)
      + COALESCE(c.workout_progress_pct, 0)
      + COALESCE(c.weight_progress_pct, 0)
    ) / NULLIF(c.target_count, 0),
    1
  ) AS overall_goal_progress_pct
FROM calculated c;

Recommended PostgreSQL Schema

This schema supports longitudinal tracking, nutrition analytics, workout history, goal progress, achievements, and streak calculation. 


CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE health_profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  height_cm NUMERIC,
  baseline_weight_kg NUMERIC,
  goal_weight_kg NUMERIC,
  target_date DATE,
  daily_calorie_target INTEGER,
  protein_target_g INTEGER,
  carb_target_g INTEGER,
  fat_target_g INTEGER,
  step_target INTEGER,
  sleep_target_hours NUMERIC,
  workout_frequency_target INTEGER,
  weekly_weight_target_kg NUMERIC
);

CREATE TABLE activities (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  log_date DATE NOT NULL,
  steps INTEGER,
  sleep_hours NUMERIC,
  resting_heart_rate INTEGER,
  calories_burned NUMERIC,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE workouts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  log_date DATE NOT NULL,
  workout_type TEXT NOT NULL,
  intensity TEXT CHECK (intensity IN ('low', 'moderate', 'high')),
  duration_min INTEGER,
  calories_burned NUMERIC,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE meals (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  log_date DATE NOT NULL,
  meal_slot TEXT CHECK (meal_slot IN ('Breakfast', 'Lunch', 'Dinner', 'Snack')),
  meal_name TEXT NOT NULL,
  calories INTEGER,
  protein_g NUMERIC,
  carbs_g NUMERIC,
  fat_g NUMERIC,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE weight_logs (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  log_date DATE NOT NULL,
  weight_kg NUMERIC,
  body_fat_pct NUMERIC,
  waist_cm NUMERIC,
  chest_cm NUMERIC,
  hips_cm NUMERIC,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE goals (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  label TEXT NOT NULL,
  metric TEXT NOT NULL,
  target_value NUMERIC,
  period TEXT CHECK (period IN ('daily', 'weekly', 'monthly', 'total')),
  start_date DATE,
  end_date DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE achievements (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  achievement_key TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  earned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, achievement_key)
);

CREATE INDEX idx_meals_user_date
  ON meals(user_id, log_date);

CREATE INDEX idx_workouts_user_date
  ON workouts(user_id, log_date);

CREATE INDEX idx_activities_user_date
  ON activities(user_id, log_date);

CREATE INDEX idx_weight_logs_user_date
  ON weight_logs(user_id, log_date);

CREATE INDEX idx_goals_user_metric
  ON goals(user_id, metric);