데이터베이스 설계 (sbdb)

ER 다이어그램 (개요)

[users] ───┬─── [user_profiles]
           ├─── [family_relations] ─── [users]
           ├─── [mood_logs]
           ├─── [medications] ── [med_logs]
           ├─── [health_records]
           ├─── [emergency_contacts]
           ├─── [sos_events]
           ├─── [chat_sessions] ── [chat_messages]
           ├─── [calendar_events]
           ├─── [help_requests] ── [help_providers]
           ├─── [village_events]
           ├─── [job_listings] ── [job_applications]
           ├─── [course_progress]
           ├─── [autobiography]
           ├─── [subscriptions] ── [payments]
           └─── [notifications]
    

핵심 테이블 (DDL)

-- 1. users (회원 기본)
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(150) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('senior','family','helper','admin') DEFAULT 'senior',
  status ENUM('pending','active','suspended') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP NULL,
  INDEX idx_email (email), INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. user_profiles (시니어 상세)
CREATE TABLE user_profiles (
  user_id BIGINT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  birth_year SMALLINT,
  gender ENUM('M','F'),
  region VARCHAR(50),  -- "서울 강북구"
  phone VARCHAR(20),
  avatar_url VARCHAR(255),
  font_size ENUM('normal','large','xlarge') DEFAULT 'large',
  voice_speed DECIMAL(2,1) DEFAULT 0.9,
  interests JSON,      -- ["바둑","요리","역사"]
  chronic_conditions JSON, -- ["고혈압","당뇨"]
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 3. family_relations
CREATE TABLE family_relations (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  senior_id BIGINT NOT NULL,
  family_id BIGINT NOT NULL,
  relation ENUM('son','daughter','spouse','grandchild','sibling','other'),
  is_emergency BOOLEAN DEFAULT FALSE,
  priority TINYINT DEFAULT 5,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_pair (senior_id, family_id),
  FOREIGN KEY (senior_id) REFERENCES users(id),
  FOREIGN KEY (family_id) REFERENCES users(id)
);

-- 4. mood_logs (오늘의 마음)
CREATE TABLE mood_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  score TINYINT NOT NULL,  -- 1~5
  note TEXT,
  audio_url VARCHAR(255),
  ai_sentiment JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_date (user_id, created_at),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 5. medications + med_logs
CREATE TABLE medications (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  name VARCHAR(100) NOT NULL,
  dosage VARCHAR(50),
  times JSON NOT NULL,    -- ["08:00","13:00","19:00"]
  start_date DATE, end_date DATE,
  is_active BOOLEAN DEFAULT TRUE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE med_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  med_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  scheduled_at TIMESTAMP,
  taken_at TIMESTAMP NULL,
  status ENUM('pending','taken','late','skipped'),
  INDEX idx_user_status (user_id, status)
);

-- 6. sos_events
CREATE TABLE sos_events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  trigger_type ENUM('button','voice','fall_detect','no_activity'),
  lat DECIMAL(10,7), lng DECIMAL(10,7),
  battery_level TINYINT,
  resolution ENUM('cancelled','family_resolved','119_called','safe'),
  notified_users JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  resolved_at TIMESTAMP NULL,
  notes TEXT
);

-- 7. health_records (혈압/혈당/체중)
CREATE TABLE health_records (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  metric ENUM('bp_sys','bp_dia','glucose','weight','steps','sleep','temp'),
  value DECIMAL(6,2),
  unit VARCHAR(10),
  recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_metric_date (user_id, metric, recorded_at)
);

-- 8. chat_sessions / chat_messages (AI 말벗)
CREATE TABLE chat_sessions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  bot_type ENUM('companion','secretary','autobiography'),
  summary TEXT,  -- AI 요약 (장기 메모리)
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ended_at TIMESTAMP NULL
);
CREATE TABLE chat_messages (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  session_id BIGINT NOT NULL,
  role ENUM('user','assistant','system'),
  content TEXT,
  audio_url VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_session (session_id, created_at)
);

-- 9. subscriptions + payments (토스페이먼츠)
CREATE TABLE subscriptions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  plan ENUM('free','premium','care_plus','family'),
  status ENUM('active','cancelled','expired'),
  started_at TIMESTAMP, expires_at TIMESTAMP,
  toss_billing_key VARCHAR(255)
);
CREATE TABLE payments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  subscription_id BIGINT,
  amount INT, status ENUM('pending','paid','failed','refunded'),
  toss_payment_key VARCHAR(255),
  paid_at TIMESTAMP NULL
);