Roomfit · 운동 기록

민

김민수

user

최근 90일 세션

—

현재 연속
—

누적 볼륨

—

ton · 90d

Bench 1RM (est.)

—

Squat 1RM (est.)

—

훈련 캘린더 · 최근 13주

각 칸 = 하루 · 색 진하기 = 세트 수 · 황금색 = PR 달성

일월화수목금토

less more PR 달성일

최근 세션 10개

어느 날 · 어떤 운동 · 몇 kg · 몇 번

주요 lift 중량 변화

선택 탭의 작업 세트 무게 — 막대는 RPE 색

Main 4 lift 요약

90일 전 → 지금

주차별 볼륨 · 부위별 분배

주간 총 볼륨 (kg×reps) · 최근 7일 세트 기준 부위 비중

주차별 볼륨

점진적 과부하가 제대로 일어나는지. 플래토 3주 이상이면 deload 신호.

이번 주 부위 분배

Push / Pull / Legs / Core 비중. 한쪽 치우치면 밸런스 경고.

실데이터 주입 SQL · views 적용 완료

migrations 20260419121801 / 121835 / 121936 적용됨 → 아래 쿼리 그대로 Supabase에서 실행

✓ exercise_metadata 65종 카탈로그 + body_part_group 컬럼

✓ exercise_sets_with_pr view (is_weight_pr / is_e1rm_pr / e1rm_kg)

✓ session_exercise_summary view (working_sets jsonb + has_pr)

KPI / 캘린더 / 세션 리스트 / progression / 도넛 SQL (7개)

A · KPI 5개 (프로필 헤더)

WITH me AS (SELECT auth.uid() AS uid),
last90 AS (
  SELECT s.* FROM workout_sessions s, me
   WHERE s.user_id = me.uid AND s.started_at >= now() - interval '90 days'
)
SELECT
  (SELECT count(*) FROM last90)                          AS sessions_90d,
  (SELECT sum(total_volume_load_kg) FROM last90)         AS volume_kg_90d,
  (SELECT max(e1rm_kg) FROM exercise_sets_with_pr
    WHERE user_id = (SELECT uid FROM me) AND exercise_id = 'benchPress'
      AND session_started_at >= now() - interval '90 days') AS bench_e1rm,
  (SELECT max(e1rm_kg) FROM exercise_sets_with_pr
    WHERE user_id = (SELECT uid FROM me) AND exercise_id = 'squat'
      AND session_started_at >= now() - interval '90 days') AS squat_e1rm;

B · 캘린더 히트맵 (일별 세트수 + PR 여부)

SELECT
  date_trunc('day', session_started_at)::date AS day,
  count(*)                                      AS sets,
  bool_or(is_weight_pr OR is_e1rm_pr)         AS has_pr
FROM exercise_sets_with_pr
WHERE user_id = auth.uid()
  AND session_started_at >= now() - interval '90 days'
  AND warmup_detected = false
GROUP BY day ORDER BY day;

C · 최근 세션 10개 (working_sets jsonb 포함)

SELECT session_id, started_at, duration_min, session_rpe, mood,
       total_volume_load_kg, body_part_group,
       exercise_id, exercise_name_ko, working_sets, warmup_set_count, has_pr
FROM session_exercise_summary
WHERE user_id = auth.uid()
ORDER BY started_at DESC, entry_order
LIMIT 50;  -- client groups by session_id, shows top 10 sessions

D · Bench 중량 progression (90일)

SELECT
  session_started_at,
  weight_kg, rep_count, rpe,
  e1rm_kg, is_weight_pr, is_e1rm_pr
FROM exercise_sets_with_pr
WHERE user_id = auth.uid()
  AND exercise_id = 'benchPress'  -- or 'squat' / 'deadlift' / 'militaryPress'
  AND warmup_detected = false
  AND session_started_at >= now() - interval '90 days'
ORDER BY session_started_at, set_index;

E · 이번 주 부위 분배 도넛

SELECT
  em.body_part_group,       -- push / pull / legs / core
  count(es.id) AS sets
FROM exercise_sets_with_pr es
JOIN exercise_metadata em USING (exercise_id)
WHERE es.user_id = auth.uid()
  AND es.session_started_at >= now() - interval '7 days'
  AND es.warmup_detected = false
GROUP BY em.body_part_group;

F · 주차별 볼륨 막대

SELECT
  date_trunc('week', session_started_at)::date AS week,
  sum(volume_load_kg) AS volume_kg,
  count(*)            AS sets
FROM exercise_sets_with_pr
WHERE user_id = auth.uid()
  AND session_started_at >= now() - interval '90 days'
  AND warmup_detected = false
GROUP BY week ORDER BY week;

G · Top 운동 순위 (얼마나 자주 했나)

SELECT em.name_ko, em.body_part_group,
       count(DISTINCT session_id) AS sessions,
       count(*)                    AS working_sets,
       max(e1rm_kg)                AS best_e1rm
FROM exercise_sets_with_pr es
JOIN exercise_metadata em USING (exercise_id)
WHERE es.user_id = auth.uid()
  AND es.warmup_detected = false
  AND es.session_started_at >= now() - interval '90 days'
GROUP BY em.name_ko, em.body_part_group
ORDER BY working_sets DESC LIMIT 10;