REBLOOM_2

Tables (36)

ai_insights

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • assessment_id — BIGINT — Foreign key → mental_state_assessments.id
  • risk_event_id — BIGINT — Foreign key → risk_events.id
  • insight_type — VARCHAR(20) — NOT NULL
  • summary_text — TEXT — NOT NULL
  • stress_factor — VARCHAR(255)
  • recommended_focus — VARCHAR(255)
  • generated_at — DATETIME — NOT NULL

analysis_feature_sets

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • window_start_at — DATETIME — NOT NULL
  • window_end_at — DATETIME — NOT NULL
  • source_scope — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL

analysis_feature_values

  • id — BIGINT — Primary key — NOT NULL
  • feature_set_id — BIGINT — Foreign key → analysis_feature_sets.id — NOT NULL
  • feature_name — VARCHAR(100) — NOT NULL
  • feature_value — DECIMAL(18,6)
  • feature_unit — VARCHAR(30)
  • feature_text — VARCHAR(500)
  • created_at — DATETIME — NOT NULL

assessment_evidences

  • id — BIGINT — Primary key — NOT NULL
  • assessment_id — BIGINT — Foreign key → mental_state_assessments.id — NOT NULL
  • evidence_type — VARCHAR(30) — NOT NULL
  • source_table — VARCHAR(80)
  • source_id — BIGINT
  • metric_name — VARCHAR(100)
  • metric_value — VARCHAR(100)
  • baseline_value — VARCHAR(100)
  • contribution_score — DECIMAL(8,4)
  • evidence_text — VARCHAR(500) — NOT NULL
  • created_at — DATETIME — NOT NULL

auth_accounts

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • provider — VARCHAR(30) — NOT NULL
  • provider_user_id — VARCHAR(255) — NOT NULL
  • provider_email — VARCHAR(255)
  • created_at — DATETIME — NOT NULL

biometric_permissions

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • device_id — BIGINT — Foreign key → devices.id — NOT NULL
  • heart_rate_enabled — TINYINT(1) — NOT NULL
  • hrv_enabled — TINYINT(1) — NOT NULL
  • acc_enabled — TINYINT(1) — NOT NULL
  • bvp_enabled — TINYINT(1) — NOT NULL
  • sleep_enabled — TINYINT(1) — NOT NULL
  • stress_enabled — TINYINT(1) — NOT NULL
  • activity_enabled — TINYINT(1) — NOT NULL
  • granted_at — DATETIME — NOT NULL
  • revoked_at — DATETIME

biometric_samples

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • device_id — BIGINT — Foreign key → devices.id — NOT NULL
  • recorded_at — DATETIME — NOT NULL
  • heart_rate_bpm — DECIMAL(10,2)
  • hrv_ms — DECIMAL(10,2)
  • acc_x — DECIMAL(12,6)
  • acc_y — DECIMAL(12,6)
  • acc_z — DECIMAL(12,6)
  • acc_magnitude — DECIMAL(12,6)
  • bvp — DECIMAL(12,6)
  • signal_quality — DECIMAL(5,2)
  • raw_payload — TEXT
  • created_at — DATETIME — NOT NULL

child_counselor_relations

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • counselor_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • started_at — DATETIME
  • ended_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

child_current_statuses

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • latest_assessment_id — BIGINT — Foreign key → mental_state_assessments.id
  • latest_risk_event_id — BIGINT — Foreign key → risk_events.id
  • current_state_label — VARCHAR(50) — NOT NULL
  • current_risk_level — VARCHAR(20) — NOT NULL
  • risk_score — DECIMAL(5,2) — NOT NULL
  • sleep_status — VARCHAR(30)
  • activity_status — VARCHAR(30)
  • stress_status — VARCHAR(30)
  • summary_message — VARCHAR(500)
  • updated_at — DATETIME — NOT NULL

child_devices

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • device_id — BIGINT — Foreign key → devices.id — NOT NULL
  • paired_at — DATETIME — NOT NULL
  • unpaired_at — DATETIME
  • is_active — TINYINT(1) — NOT NULL

child_parent_relations

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • parent_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • relationship_type — VARCHAR(30) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • is_primary — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

child_profiles

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • birth_date — DATE
  • gender — VARCHAR(20)
  • school_name — VARCHAR(100)
  • grade — VARCHAR(30)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

conversation_keywords

  • id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → conversation_sessions.id — NOT NULL
  • message_id — BIGINT — Foreign key → conversation_messages.id
  • keyword — VARCHAR(100) — NOT NULL
  • sentiment_type — VARCHAR(20) — NOT NULL
  • score — DECIMAL(10,2)
  • extracted_at — DATETIME — NOT NULL

conversation_messages

  • id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → conversation_sessions.id — NOT NULL
  • speaker_type — VARCHAR(20) — NOT NULL
  • content — TEXT — NOT NULL
  • sent_at — DATETIME — NOT NULL

conversation_sentiment_summaries

  • id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → conversation_sessions.id — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • positive_score — DECIMAL(5,2)
  • negative_score — DECIMAL(5,2)
  • neutral_score — DECIMAL(5,2)
  • dominant_emotion — VARCHAR(50)
  • summary_text — TEXT
  • generated_at — DATETIME — NOT NULL

conversation_sessions

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • device_id — BIGINT — Foreign key → devices.id
  • started_at — DATETIME — NOT NULL
  • ended_at — DATETIME
  • session_summary — TEXT
  • created_at — DATETIME — NOT NULL

counseling_appointments

  • id — BIGINT — Primary key — NOT NULL
  • counselor_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • parent_user_id — BIGINT — Foreign key → users.id
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • appointment_type — VARCHAR(30)
  • counseling_round — INT
  • is_online — TINYINT(1) — NOT NULL
  • meeting_url — VARCHAR(500)
  • location — VARCHAR(255)
  • start_at — DATETIME — NOT NULL
  • end_at — DATETIME — NOT NULL
  • recurrence_rule — VARCHAR(255)
  • status — VARCHAR(20) — NOT NULL
  • no_show_reason — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

counselor_profiles

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • real_name — VARCHAR(100) — NOT NULL
  • organization_name — VARCHAR(150)
  • phone_number — VARCHAR(30)
  • specialty — VARCHAR(150)
  • bio — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

counselor_todos

  • id — BIGINT — Primary key — NOT NULL
  • counselor_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id
  • appointment_id — BIGINT — Foreign key → counseling_appointments.id
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • due_at — DATETIME
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

daily_emotion_summaries

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • summary_date — DATE — NOT NULL
  • dominant_emotion — VARCHAR(50)
  • positive_score — DECIMAL(5,2)
  • negative_score — DECIMAL(5,2)
  • main_keywords — VARCHAR(500)
  • summary_text — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

daily_health_summaries

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • summary_date — DATE — NOT NULL
  • sleep_minutes — INT
  • sleep_delta_minutes — INT
  • avg_heart_rate — DECIMAL(10,2)
  • avg_hrv_ms — DECIMAL(10,2)
  • avg_bvp — DECIMAL(12,6)
  • activity_steps — INT
  • activity_score — DECIMAL(5,2)
  • stress_score — DECIMAL(5,2)
  • stress_level — VARCHAR(20)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

device_pairings

  • id — BIGINT — Primary key — NOT NULL
  • device_id — BIGINT — Foreign key → devices.id — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • pairing_type — VARCHAR(30) — NOT NULL
  • wifi_ssid — VARCHAR(100)
  • status — VARCHAR(20) — NOT NULL
  • paired_at — DATETIME — NOT NULL
  • disconnected_at — DATETIME

devices

  • id — BIGINT — Primary key — NOT NULL
  • device_type — VARCHAR(30) — NOT NULL
  • device_name — VARCHAR(100) — NOT NULL
  • serial_number — VARCHAR(100)
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

diaries

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • title — VARCHAR(200)
  • content — TEXT — NOT NULL
  • emotion_tag — VARCHAR(50)
  • mood_score — DECIMAL(5,2)
  • written_date — DATE
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

diary_share_settings

  • id — BIGINT — Primary key — NOT NULL
  • diary_id — BIGINT — Foreign key → diaries.id — NOT NULL
  • share_with_counselor — TINYINT(1) — NOT NULL
  • share_biometric_data — TINYINT(1) — NOT NULL
  • share_conversation_metrics — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

mental_state_assessments

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • feature_set_id — BIGINT — Foreign key → analysis_feature_sets.id
  • assessment_type — VARCHAR(30) — NOT NULL
  • state_label — VARCHAR(50) — NOT NULL
  • severity_level — VARCHAR(20) — NOT NULL
  • risk_score — DECIMAL(5,2) — NOT NULL
  • depression_risk_score — DECIMAL(5,2)
  • anxiety_risk_score — DECIMAL(5,2)
  • summary_text — TEXT — NOT NULL
  • recommended_message — TEXT
  • model_version — VARCHAR(50)
  • generated_at — DATETIME — NOT NULL
  • valid_until — DATETIME

observation_replies

  • id — BIGINT — Primary key — NOT NULL
  • observation_log_id — BIGINT — Foreign key → parent_observation_logs.id — NOT NULL
  • counselor_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

parent_observation_logs

  • id — BIGINT — Primary key — NOT NULL
  • parent_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • content — TEXT — NOT NULL
  • category_tag — VARCHAR(50)
  • observed_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

parent_profiles

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • phone_number — VARCHAR(30)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

risk_event_metrics

  • id — BIGINT — Primary key — NOT NULL
  • risk_event_id — BIGINT — Foreign key → risk_events.id — NOT NULL
  • metric_name — VARCHAR(100) — NOT NULL
  • metric_value — VARCHAR(100) — NOT NULL
  • baseline_value — VARCHAR(100)
  • deviation_rate — DECIMAL(10,4)
  • created_at — DATETIME — NOT NULL

risk_events

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • assessment_id — BIGINT — Foreign key → mental_state_assessments.id
  • risk_level — VARCHAR(20) — NOT NULL
  • risk_type — VARCHAR(30) — NOT NULL
  • detected_at — DATETIME — NOT NULL
  • summary_message — VARCHAR(500) — NOT NULL
  • ai_comment — TEXT — EXT NUL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

risk_notifications

  • id — BIGINT — Primary key — NOT NULL
  • risk_event_id — BIGINT — Foreign key → risk_events.id — NOT NULL
  • recipient_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • channel — VARCHAR(20) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • body — TEXT — NOT NULL
  • sent_at — DATETIME
  • read_at — DATETIME
  • status — VARCHAR(20) — NOT NULL

sleep_sessions

  • id — BIGINT — Primary key — NOT NULL
  • child_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • device_id — BIGINT — Foreign key → devices.id
  • sleep_start_at — DATETIME — NOT NULL
  • sleep_end_at — DATETIME — NOT NULL
  • total_sleep_minutes — INT — NOT NULL
  • deep_sleep_minutes — INT
  • rem_sleep_minutes — INT
  • awake_minutes — INT
  • sleep_quality_score — DECIMAL(5,2)
  • created_at — DATETIME — NOT NULL

user_push_tokens

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • platform — VARCHAR(30) — NOT NULL
  • push_token — VARCHAR(500) — NOT NULL
  • device_name — VARCHAR(100)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_roles

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • role — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255)
  • nickname — VARCHAR(100) — NOT NULL
  • profile_image_url — VARCHAR(500)
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL