MindVoyage

Tables (31)

activity_history

  • activity_history_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • activity_type — ENUM('날씨기록', '충전활동', '소셜', '포인트', '로그인', '퇴근') — NOT NULL
  • activity_description — TEXT
  • related_record_id — BIGINT
  • activity_timestamp — TIMESTAMP
  • INDEX — idx_user_timestamp
  • INDEX (INDEX2) — idx_activity_type
  • Key — Foreign key → users.user_id — NOT NULL

attendance

  • attendance_id — Primary key — NOT NULL
  • attendance_date — DATE — NOT NULL
  • work_start_time — TIMESTAMP
  • work_end_time — TIMESTAMP
  • is_completed — BOOLEAN
  • created_at — TIMESTAMP
  • INDEX — idx_user_date
  • Key — Foreign key → users.user_id — NOT NULL
  • user_id — Foreign key → users.user_id — NOT NULL

charging_activities

  • charging_activity_id (Key) — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • activity_type_id — INT — Foreign key — NOT NULL
  • weather_record_id — BIGINT — Foreign key
  • activity_date — DATE — NOT NULL
  • activity_time — TIMESTAMP — NOT NULL
  • is_completed — BOOLEAN
  • completion_count — INT
  • previous_weather_value — INT
  • new_weather_value — INT
  • weather_change_amount — INT
  • bonus_points_earned — INT
  • created_at — TIMESTAMP
  • completed_at — TIMESTAMP
  • INDEX — idx_user_date
  • INDEX (INDEX2) — idx_completion
  • Key (Key2) — Foreign key → charging_activity_types.activity_type_id — NOT NULL

charging_activity_types

  • activity_type_id — Primary key — NOT NULL
  • activity_name — VARCHAR(100) — NOT NULL
  • activity_description — TEXT
  • interaction_method — VARCHAR(50)
  • recommended_duration_minutes — INT
  • bonus_point_threshold — INT
  • points_per_completion — INT
  • display_order — INT
  • created_at — TIMESTAMP

coupon_types

  • coupon_type_id — Primary key — NOT NULL
  • coupon_name — VARCHAR(100) — NOT NULL
  • coupon_description — TEXT
  • required_points — INT — NOT NULL
  • coupon_image_url — VARCHAR(255)
  • is_active — BOOLEAN
  • created_at — TIMESTAMP

daily_reports

  • daily_report_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • report_date — DATE — NOT NULL
  • total_weather_records — INT
  • total_charging_activities — INT
  • total_obstacles_encountered — INT
  • total_weather_change — INT
  • morning_weather_value — INT
  • evening_weather_value — INT
  • points_earned_today — INT
  • report_generated_at — TIMESTAMP
  • INDEX — idx_user_date
  • Key — Foreign key → users.user_id — NOT NULL

departments

  • department_id — BIGINT — Primary key — NOT NULL
  • organization_id — BIGINT — Foreign key — NOT NULL
  • name — VARCHAR(200) — NOT NULL — 부서명
  • created_at — TIMESTAMP

emotion_statistics

  • emotion_stat_id — Primary key — NOT NULL
  • weekly_report_id — BIGINT — Foreign key — NOT NULL
  • emotion_type_id — INT — Foreign key — NOT NULL
  • occurrence_count — INT
  • occurrence_percentage — DECIMAL(5,2)
  • INDEX — idx_weekly_report
  • Key — Foreign key → weekly_reports.Key — NOT NULL
  • Key (Key2) — Foreign key → emotion_types.emotion_type_id — NOT NULL
  • Key2 (Key22) — Foreign key → weekly_reports.weekly_report_id — NOT NULL

emotion_types

  • emotion_type_id — INT — Primary key — NOT NULL
  • emotion_name — VARCHAR(50) — NOT NULL
  • emotion_category — ENUM('긍정', '부정') — NOT NULL
  • icon — VARCHAR(50)
  • display_order — INT
  • created_at — TIMESTAMP

factor_rankings

  • factor_ranking_id — Primary key — NOT NULL
  • weekly_report_id — BIGINT — Foreign key — NOT NULL
  • factor_type_id — INT — Foreign key — NOT NULL
  • total_count — INT
  • rank_position — INT
  • INDEX — idx_weekly_report
  • INDEX (INDEX2) — idx_rank
  • Key2 (Key22) — Foreign key → weekly_reports.weekly_report_id — NOT NULL

factor_types

  • factor_type_id — INT — Primary key — NOT NULL
  • factor_name — VARCHAR(100) — NOT NULL
  • factor_category — VARCHAR(50)
  • created_at — TIMESTAMP
  • icon

llm_prompt_logs

  • log_id — BIGINT — Primary key — NOT NULL
  • prompt_type — ENUM('summary', 'risk_analysis', 'recommendation') — NOT NULL
  • prompt_text — TEXT — 실제 전송한 프롬프트
  • response_text — TEXT — LLM 응답
  • model_name — VARCHAR(100) — 사용한 모델 (예: gpt-4)
  • tokens_used — INT
  • created_at — TIMESTAMP
  • org_report_id — BIGINT — Foreign key → organizational_weekly_reports.org_report_id — NOT NULL

managers

  • manager_id — Primary key — NOT NULL
  • user_id — BIGINT — NOT NULL — 관리자 계정 (users 테이블 참조)
  • organization_id — BIGINT — Foreign key — NOT NULL — 소속 조직
  • role — ENUM('supervisor', 'team_lead', 'hr_manager', 'executive') — 관리자 역할
  • access_level — ENUM('team', 'department', 'organization') — 접근 권한 범위
  • created_at — TIMESTAMP

notifications

  • notification_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • notification_type — ENUM('응원받음', '포인트받음', '장애물해결', '시스템') — NOT NULL
  • sender_user_id — BIGINT — Foreign key
  • related_support_id — BIGINT — Foreign key
  • message — TEXT — NOT NULL
  • is_read — BOOLEAN
  • created_at — TIMESTAMP
  • read_at — TIMESTAMP
  • INDEX — idx_user_unread
  • INDEX (INDEX2) — idx_created_at
  • Key — Foreign key → social_support.support_id — NOT NULL

obstacles

  • obstacle_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • obstacle_type — VARCHAR(100) — NOT NULL
  • obstacle_description — TEXT
  • created_date — DATE — NOT NULL
  • is_resolved — BOOLEAN
  • resolved_at — TIMESTAMP
  • stress_threshold_value — INT
  • created_at — TIMESTAMP
  • INDEX — idx_user_created
  • INDEX (INDEX2) — idx_is_resolved
  • Key — Foreign key → users.user_id — NOT NULL

organizational_interventions

  • org_intervention_id — BIGINT — Primary key — NOT NULL
  • organization_id — BIGINT — Foreign key — NOT NULL
  • department_id — BIGINT
  • team_id — BIGINT
  • manager_id — BIGINT — Foreign key — NOT NULL — 누가 설계했나
  • intervention_type — ENUM('training', 'policy_change', 'resource_allocation', 'schedule_adjustment', 'wellness_program') — NOT NULL
  • title — VARCHAR(200) — NOT NULL — 인터벤션 제목
  • description — TEXT — 상세 설명
  • target_segment — VARCHAR(100) — 대상 세그먼트 (예: 20대, 야간근무자)
  • start_date — DATE
  • end_date — DATE
  • status — ENUM('planned', 'active', 'completed', 'cancelled')
  • target_metric — VARCHAR(100) — 목표 지표 (예: 스트레스 레벨 10% 감소)
  • baseline_value — DECIMAL(10, 2) — 시작 시점 기준값
  • current_value — DECIMAL(10, 2) — 현재 측정값
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_org_intervention
  • Key — Foreign key → organizations.organization_id — NOT NULL
  • Key (Key2) — Foreign key → managers.manager_id — NOT NULL

organizational_weekly_reports

  • org_report_id — BIGINT — Primary key — NOT NULL
  • organization_id — BIGINT — NOT NULL
  • department_id — BIGINT — NULL이면 전체 조직
  • team_id — BIGINT — NULL이면 부서 전체
  • week_start_date — DATE — NOT NULL
  • week_end_date — DATE — NOT NULL
  • total_employees — INT — 대상 직원 수
  • avg_stress_level — DECIMAL(5, 2) — 평균 스트레스 레벨
  • high_risk_count — INT — 고위험군 직원 수
  • total_obstacles — INT — 전체 장애물 발생 수
  • avg_weather_value — DECIMAL(5, 2) — 평균 날씨값
  • llm_summary — TEXT — LLM이 생성한 조직 상태 요약
  • risk_patterns — TEXT — LLM이 분석한 리스크 패턴
  • recommendations — TEXT — LLM이 제안한 인터벤션
  • created_at — TIMESTAMP
  • INDEX — idx_org_week
  • organization_id (organization_id2) — BIGINT — Foreign key → organizations.organization_id — NOT NULL
  • department_id (department_id2) — BIGINT — Foreign key → departments.department_id — NOT NULL

organizations

  • organization_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(200) — NOT NULL — 조직명 (예: A콜센터)
  • organization_type — VARCHAR(100) — 업종 (금융, 통신 등)
  • created_at — TIMESTAMP

point_balance

  • user_id — BIGINT — Foreign key
  • total_points — INT
  • points_this_week — INT
  • last_updated — TIMESTAMP
  • Key — Foreign key → users.user_id — NOT NULL

point_transactions

  • transaction_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • transaction_type — ENUM('적립', '사용', '보너스', '응원받음', '응원보냄') — NOT NULL
  • points_amount — INT — NOT NULL
  • balance_after — INT — NOT NULL
  • related_activity_id — BIGINT
  • related_social_action_id — BIGINT
  • description — VARCHAR(255)
  • transaction_date — TIMESTAMP
  • INDEX — idx_user_date
  • INDEX (INDEX2) — idx_transaction_type
  • Key — Foreign key → users.user_id — NOT NULL

segment_statistics

  • segment_stat_id — BIGINT — Primary key — NOT NULL
  • org_report_id — BIGINT — NOT NULL — 어떤 주간 리포트의 세그먼트인가
  • segment_type — ENUM('age_group', 'tenure', 'day_of_week', 'time_of_day', 'job_title') — NOT NULL
  • segment_value — VARCHAR(50) — NOT NULL — 예: 20대, 1년미만, 월요일, 09-12시
  • employee_count — INT
  • avg_stress_level — DECIMAL(5, 2)
  • avg_weather_value — DECIMAL(5, 2)
  • total_obstacles — INT
  • high_risk_count — INT
  • INDEX — idx_segment
  • org_report_id (org_report_id2) — BIGINT — Foreign key → organizational_weekly_reports.org_report_id — NOT NULL

social_support

  • support_id — Primary key — NOT NULL
  • sender_user_id — BIGINT — Foreign key — NOT NULL
  • receiver_user_id — BIGINT — Foreign key — NOT NULL
  • support_type — ENUM('응원', '포인트', '장애물치우기') — NOT NULL
  • points_sent — INT
  • message — TEXT
  • obstacle_id — BIGINT — Foreign key
  • sent_at — TIMESTAMP
  • is_received — BOOLEAN
  • received_at — TIMESTAMP
  • INDEX (INDEX1) — idx_sender
  • INDEX (INDEX2) — idx_sent_at
  • INDEX (INDEX3) — idx_receiver
  • Key2 — Foreign key → obstacles.obstacle_id — NOT NULL
  • Field2

system_settings

  • setting_id — INT — Primary key — NOT NULL
  • setting_key — VARCHAR(100) — NOT NULL
  • setting_value — TEXT
  • setting_description — TEXT
  • updated_at — TIMESTAMP

team_members

  • team_member_id — Primary key — NOT NULL
  • team_id — BIGINT — Foreign key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • joined_at — TIMESTAMP
  • is_active — BOOLEAN
  • INDEX — idx_team
  • INDEX (INDEX2) — idx_user
  • Key2 (Key22) — Foreign key → teams.team_id — NOT NULL

team_obstacle_status

  • team_obstacle_id — Primary key — NOT NULL
  • team_id — BIGINT — Foreign key — NOT NULL
  • obstacle_type — VARCHAR(100) — NOT NULL
  • affected_member_count — INT
  • status_date — DATE — NOT NULL
  • last_updated — TIMESTAMP
  • INDEX — idx_team_date
  • Key — Foreign key → teams.Key — NOT NULL
  • Key2 — Foreign key → teams.team_id — NOT NULL

teams

  • team_id — Primary key — NOT NULL
  • team_name — VARCHAR(100) — NOT NULL
  • team_description — TEXT
  • created_at — TIMESTAMP
  • is_active — BOOLEAN
  • department_id — BIGINT — Foreign key → departments.department_id — NOT NULL
  • Key — Foreign key — NOT NULL

user_coupons

  • user_coupon_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • coupon_type_id — INT — Foreign key — NOT NULL
  • purchase_date — TIMESTAMP
  • barcode_number — VARCHAR(100)
  • is_used — BOOLEAN
  • used_date — TIMESTAMP
  • expiration_date — DATE
  • INDEX — idx_user_purchase
  • INDEX (INDEX2) — idx_barcode
  • Key (Key2) — Foreign key → coupon_types.coupon_type_id — NOT NULL

users

  • user_id — Primary key — NOT NULL
  • username — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • phone_number — VARCHAR(20)
  • age — INT
  • gender — ENUM('남성', '여성', '기타')
  • call_center_type — VARCHAR(100)
  • work_location — VARCHAR(100)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • last_login_at — TIMESTAMP
  • is_active — BOOLEAN
  • INDEX — idx_username
  • INDEX (INDEX_2) — idx_created_at
  • hire_date — DATE
  • job_title — VARCHAR(100)
  • employment_type — ENUM('full_time', 'part_time', 'contract')
  • Field

weather_record_factors

  • record_factor_id — Primary key — NOT NULL
  • weather_record_id — BIGINT — Foreign key — NOT NULL
  • factor_type_id — INT — Foreign key — NOT NULL
  • created_at — TIMESTAMP

weather_records

  • weather_record_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • record_date — DATE — NOT NULL
  • record_time — TIMESTAMP — NOT NULL
  • emotion_type_id — INT — Foreign key — NOT NULL
  • weather_value — INT
  • weather_text — TEXT
  • previous_weather_value — INT
  • weather_change — INT
  • is_first_record_of_day — BOOLEAN
  • created_at — TIMESTAMP
  • self_report — JSON — 업무 시작하기에서 작성한 설문지
  • written_text — 알아보기에서 사용자가 선택사항으로 작성한 마음표현 글

weekly_reports

  • weekly_report_id — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • week_start_date — DATE — NOT NULL
  • week_end_date — DATE — NOT NULL
  • year_number — INT — NOT NULL
  • week_number — INT — NOT NULL
  • total_attendance_days — INT
  • total_weather_records — INT
  • total_charging_activities — INT
  • total_social_interactions — INT
  • points_earned_this_week — INT
  • positive_emotion_percentage — DECIMAL(5,2)
  • negative_emotion_percentage — DECIMAL(5,2)
  • report_generated_at — TIMESTAMP
  • INDEX — idx_user_week
  • Key — Foreign key → users.user_id — NOT NULL