test

Tables (20)

badge_progress

  • progress_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • badge_code — VARCHAR(60) — NOT NULL
  • current_streak — INT
  • longest_streak — INT
  • last_date — DATE

badges

  • badge_id — BIGINT
  • code — VARCHAR(60) — NOT NULL
  • title — VARCHAR(120) — NOT NULL
  • category — VARCHAR(40) — NOT NULL
  • description — VARCHAR(500)

exercise_catalog

  • exercise_id — BIGINT
  • category — VARCHAR(20) — NOT NULL
  • name — VARCHAR(120) — NOT NULL
  • met_value — DECIMAL(5,2) — NOT NULL

exercise_records

  • record_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • exercise_id — BIGINT — NOT NULL
  • exercise_date — DATE — NOT NULL
  • category — VARCHAR(20) — NOT NULL
  • duration_min — INT — NOT NULL
  • calories_kcal — DECIMAL(10,2) — NOT NULL
  • points — INT
  • year_month — CHAR(6)
  • %Y%m)) — STORED
  • source — VARCHAR(20)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — ix_ex_user_date
  • INDEX — ix_ex_user_ym

exercise_sets

  • set_id — BIGINT
  • record_id — BIGINT — NOT NULL
  • set_no — INT — NOT NULL
  • reps — INT
  • weight_kg — DECIMAL(6,2)

foods

  • food_id — BIGINT
  • name — VARCHAR(120) — NOT NULL
  • unit — VARCHAR(20)
  • kcal_per_100g — DECIMAL(8,2)
  • carb_g_100g — DECIMAL(8,2)
  • protein_g_100g — DECIMAL(8,2)
  • fat_g_100g — DECIMAL(8,2)
  • sugar_g_100g — DECIMAL(8,2)
  • fiber_g_100g — DECIMAL(8,2)

group_feedback

  • feedback_id — BIGINT
  • group_id — BIGINT — NOT NULL
  • author_id — BIGINT — NOT NULL
  • target_type — VARCHAR(20) — NOT NULL
  • target_id — BIGINT — NOT NULL
  • content — VARCHAR(1000) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — ix_feedback_group

group_invites

  • invite_id — BIGINT
  • group_id — BIGINT — NOT NULL
  • invitee_id — BIGINT
  • invitee_email — VARCHAR(120)
  • status — VARCHAR(20) — NOT NULL
  • sent_at — DATETIME — NOT NULL
  • responded_at — DATETIME

group_members

  • group_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Primary key — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • joined_at — DATETIME — NOT NULL

group_scores_monthly

  • score_id — BIGINT
  • group_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • year_month — CHAR(6) — NOT NULL
  • points — INT — NOT NULL
  • INDEX — ix_group_ym

groups

  • group_id — BIGINT
  • owner_id — BIGINT — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • is_paid_plan — TINYINT(1)
  • created_at — DATETIME — NOT NULL

meal_items

  • meal_item_id — BIGINT
  • meal_id — BIGINT — NOT NULL
  • food_id — BIGINT — NOT NULL
  • amount_g — DECIMAL(10,2) — NOT NULL
  • kcal — DECIMAL(10,2)
  • carb_g — DECIMAL(10,2)
  • protein_g — DECIMAL(10,2)
  • fat_g — DECIMAL(10,2)

meal_recommendations

  • rec_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • meal_date — DATE — NOT NULL
  • meal_type — VARCHAR(20) — NOT NULL
  • source — VARCHAR(20) — NOT NULL
  • payload_json — JSON
  • chosen — TINYINT(1)
  • chosen_at — DATETIME
  • INDEX — ix_rec_user_date

meals

  • meal_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • meal_date — DATE — NOT NULL
  • meal_type — VARCHAR(20) — NOT NULL
  • total_kcal — DECIMAL(10,2)
  • note — VARCHAR(500)
  • source — VARCHAR(20)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — ix_meal_user_date

notifications

  • notification_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • type — VARCHAR(30) — NOT NULL
  • payload_json — JSON
  • is_read — TINYINT(1)
  • created_at — DATETIME — NOT NULL
  • INDEX — ix_notif_user

research_logs

  • research_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • research_date — DATE — NOT NULL
  • condition_score — TINYINT
  • sleep_hours — DECIMAL(4,2)
  • schedule_type — VARCHAR(20)
  • activity_level — VARCHAR(20)
  • caffeine_freq — VARCHAR(20)
  • alcohol_freq — VARCHAR(20)
  • notes — VARCHAR(500)

user_badges

  • user_badge_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • badge_id — BIGINT — NOT NULL
  • achieved_at — DATETIME — NOT NULL
  • INDEX — ix_badge_user

user_profiles

  • user_id — BIGINT
  • nickname — VARCHAR(60)
  • height_cm — DECIMAL(5,2)
  • current_weight_kg — DECIMAL(5,2)
  • goal_type — VARCHAR(20)
  • meals_per_day — TINYINT
  • timezone — VARCHAR(40)
  • weight_public — TINYINT(1)
  • remind_time — TIME

users

  • user_id — BIGINT
  • email — VARCHAR(120)
  • username — VARCHAR(60) — NOT NULL
  • password_hash — VARCHAR(255)
  • auth_provider — VARCHAR(20) — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

weight_logs

  • weight_log_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • log_date — DATE — NOT NULL
  • weight_kg — DECIMAL(5,2) — NOT NULL