ERD

Tables (24)

artworks

  • id — BIGSERIAL
  • patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • sketch_code — INTEGER
  • image_url — VARCHAR(500) — NOT NULL
  • play_duration_seconds — INTEGER — NOT NULL
  • is_public — BOOLEAN — NOT NULL
  • color_count — INTEGER — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL

daily_usage_stat

  • id — BIGSERIAL
  • stat_date — DATE — NOT NULL
  • content_type — VARCHAR(20) — NOT NULL
  • total_seconds — BIGINT — NOT NULL
  • patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL

dialogue_sessions

  • id — BIGSERIAL
  • patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • npc_name — VARCHAR(32) — NOT NULL
  • status — VARCHAR(16) — NOT NULL
  • step_count — INTEGER — NOT NULL
  • max_steps — INTEGER — NOT NULL
  • finish_reason — VARCHAR(32)
  • script_id — VARCHAR(64)
  • started_at — TIMESTAMP(6) — NOT NULL
  • ended_at — TIMESTAMP(6)

dialogue_turns

  • id — BIGSERIAL
  • session_id — BIGINT — Foreign key → dialogue_sessions.id — NOT NULL
  • step_index — INTEGER — NOT NULL
  • question_text — TEXT — NOT NULL
  • choice_intent_id — VARCHAR(64) — NOT NULL
  • choice_text — TEXT — NOT NULL
  • intensity — SMALLINT — NOT NULL
  • concern_flags — JSONB — NOT NULL
  • protective_factors — JSONB — NOT NULL
  • generated_by — VARCHAR(16) — NOT NULL
  • valence — VARCHAR(16)
  • tone — VARCHAR(16)
  • topic_keywords — JSONB — NOT NULL
  • sentiment_words — JSONB — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL

exercise_motion

  • id — BIGSERIAL
  • exercise_type — VARCHAR(20) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • routine_order — INTEGER — NOT NULL
  • target_reps — INTEGER — NOT NULL
  • description — TEXT — NOT NULL
  • demo_video_url — VARCHAR(500)
  • thumbnail_url — VARCHAR(500)
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL

exercise_session

  • id — BIGSERIAL
  • patient_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • exercise_type — VARCHAR(20) — NOT NULL
  • duration_sec — INTEGER — NOT NULL
  • average_accuracy — DOUBLE — NOT NULL
  • completed_motion_count — INTEGER — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL

exercise_session_motion

  • id — BIGSERIAL
  • session_id — BIGINT — Foreign key → exercise_session.id — NOT NULL
  • exercise_motion_id — BIGINT — Foreign key → exercise_motion.id — NOT NULL
  • performance_video_id — BIGINT — Foreign key → performance_video.id
  • duration_sec — INTEGER — NOT NULL
  • accuracy — DOUBLE — NOT NULL
  • completed_reps — INTEGER — NOT NULL
  • feedback — VARCHAR(255) — NOT NULL
  • pose_replay — TEXT
  • compact_pose_replay — TEXT
  • created_at — TIMESTAMP(6) — NOT NULL

fuel_event

  • id — BIGSERIAL
  • patient_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • sender_id — BIGINT — Foreign key → users.id — NOT NULL
  • amount — INTEGER — NOT NULL
  • message — VARCHAR(100) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • consumed_at — TIMESTAMP(6)

gomoku_chat_messages

  • id — BIGSERIAL
  • match_id — BIGINT — Foreign key → gomoku_matches.id — NOT NULL
  • sender_patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • content — VARCHAR(200) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

gomoku_matches

  • id — BIGSERIAL
  • room_code — VARCHAR(12) — NOT NULL
  • black_patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • white_patient_profile_id — BIGINT — Foreign key → patient_profiles.id
  • winner_patient_profile_id — BIGINT — Foreign key → patient_profiles.id
  • rematch_source_match_id — BIGINT — Foreign key → gomoku_matches.id
  • status — VARCHAR(20) — NOT NULL
  • rule_set — VARCHAR(20) — NOT NULL
  • timer_seconds — INTEGER — NOT NULL
  • current_turn — VARCHAR(10) — NOT NULL
  • result — VARCHAR(20)
  • end_reason — VARCHAR(20)
  • move_count — INTEGER — NOT NULL
  • moves_json — TEXT — NOT NULL
  • ranked — BOOLEAN — NOT NULL
  • black_texture_key — VARCHAR(80) — NOT NULL
  • white_texture_key — VARCHAR(80)
  • started_at — TIMESTAMP
  • finished_at — TIMESTAMP
  • black_last_seen_at — TIMESTAMP
  • white_last_seen_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

guardian_device_token

  • id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • device_token — VARCHAR(4096) — NOT NULL
  • platform — VARCHAR(20) — NOT NULL
  • user_agent — VARCHAR(512)
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL
  • deactivated_at — TIMESTAMP(6)

music_chart

  • id — BIGSERIAL
  • chart_id — VARCHAR(100) — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • bpm — INTEGER — NOT NULL
  • duration_ms — INTEGER — NOT NULL
  • audio_url — VARCHAR(500) — NOT NULL
  • cover_url — VARCHAR(500) — NOT NULL
  • total_notes — INTEGER — NOT NULL
  • notes_json — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL

music_result

  • id — BIGSERIAL
  • patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • music_chart_id — BIGINT — Foreign key → music_chart.id — NOT NULL
  • score — INTEGER — NOT NULL
  • max_combo — INTEGER — NOT NULL
  • perfect_count — INTEGER — NOT NULL
  • good_count — INTEGER — NOT NULL
  • great_count — INTEGER — NOT NULL
  • miss_count — INTEGER — NOT NULL
  • total_notes — INTEGER — NOT NULL
  • accuracy — DOUBLE — NOT NULL
  • rank — CHAR(1) — NOT NULL
  • played_duration_ms — INTEGER — NOT NULL
  • played_at — TIMESTAMP(6) — NOT NULL
  • video_key — VARCHAR(1024)
  • thumb_key — VARCHAR(1024)

patient_profiles

  • id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • nickname — VARCHAR(30) — NOT NULL
  • birth_date — DATE — NOT NULL
  • gender — VARCHAR(10) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL

performance_video

  • id — BIGSERIAL
  • patient_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • video_key — VARCHAR(1024) — NOT NULL
  • thumb_key — VARCHAR(1024)
  • created_at — TIMESTAMP(6) — NOT NULL

photo_booth_photos

  • id — BIGSERIAL
  • patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • frame_id — VARCHAR(50) — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • thumbnail_url — VARCHAR(500)
  • is_public — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL

refresh_token

  • id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • token_hash — VARCHAR(64) — NOT NULL
  • expires_at — TIMESTAMP(6) — NOT NULL
  • revoked_at — TIMESTAMP(6)
  • created_at — TIMESTAMP(6) — NOT NULL

taekwondo_belt_history

  • id — BIGSERIAL
  • patient_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • from_belt — VARCHAR(20)
  • to_belt — VARCHAR(20) — NOT NULL
  • trigger_session_id — BIGINT — Foreign key → taekwondo_session.id — NOT NULL
  • promoted_at — TIMESTAMP(6) — NOT NULL

taekwondo_motion

  • id — BIGSERIAL
  • poomsae — VARCHAR(20) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • routine_order — INTEGER — NOT NULL
  • target_reps — INTEGER — NOT NULL
  • description — TEXT — NOT NULL
  • demo_video_url — VARCHAR(500)
  • thumbnail_url — VARCHAR(500)
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL

taekwondo_progress

  • id — BIGSERIAL
  • patient_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • current_belt — VARCHAR(20) — NOT NULL
  • total_monsters_defeated — INTEGER — NOT NULL
  • session_count — INTEGER — NOT NULL
  • last_promoted_at — TIMESTAMP(6)
  • updated_at — TIMESTAMP(6) — NOT NULL

taekwondo_session

  • id — BIGSERIAL
  • patient_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • poomsae — VARCHAR(20) — NOT NULL
  • duration_sec — INTEGER — NOT NULL
  • average_accuracy — DOUBLE — NOT NULL
  • completed_motion_count — INTEGER — NOT NULL
  • monsters_defeated — INTEGER — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL

taekwondo_session_motion

  • id — BIGSERIAL
  • session_id — BIGINT — Foreign key → taekwondo_session.id — NOT NULL
  • motion_id — BIGINT — Foreign key → taekwondo_motion.id — NOT NULL
  • performance_video_id — BIGINT — Foreign key → performance_video.id
  • duration_sec — INTEGER — NOT NULL
  • accuracy — DOUBLE — NOT NULL
  • completed_reps — INTEGER — NOT NULL
  • monsters_defeated — INTEGER — NOT NULL
  • feedback — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL

user_login_session

  • id — BIGSERIAL
  • patient_profile_id — BIGINT — Foreign key → patient_profiles.id — NOT NULL
  • started_at — TIMESTAMP(6) — NOT NULL
  • last_heartbeat_at — TIMESTAMP(6) — NOT NULL
  • ended_at — TIMESTAMP(6)
  • duration_seconds — INTEGER — NOT NULL

users

  • id — BIGSERIAL
  • email — VARCHAR(100) — NOT NULL
  • nickname — VARCHAR(30) — NOT NULL
  • password — VARCHAR(100) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • role — VARCHAR(20) — NOT NULL