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