ktb13

Tables (15)

auth_sessions

  • id — CHAR(36) — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • refresh_token_hash — VARCHAR(255)
  • user_agent — VARCHAR(500)
  • ip_address — VARCHAR(45)
  • expires_at — DATETIME — NOT NULL
  • revoked_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • last_used_at — DATETIME

batch_jobs

  • id — BIGINT — Primary key — NOT NULL
  • job_type — ENUM('DAILY_METRICS','RANKING','AI_RESCHEDULE') — NOT NULL
  • target_date — DATE — NOT NULL
  • status — ENUM('PENDING','RUNNING','SUCCEEDED','PARTIALLY_SUCCEEDED','FAILED') — NOT NULL
  • started_at — DATETIME
  • finished_at — DATETIME
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • failure_count — INT — NOT NULL
  • error_message — TEXT
  • created_at — DATETIME — NOT NULL

categories

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(80) — NOT NULL
  • description — VARCHAR(500)
  • icon_url — VARCHAR(1000)
  • display_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

conversation_messages

  • id — CHAR(36) — Primary key — NOT NULL
  • conversation_id — CHAR(36) — Foreign key → conversations.id — NOT NULL
  • role — ENUM('SYSTEM', 'USER', 'ASSISTANT', 'TOOL') — NOT NULL
  • content — LONGTEXT
  • external_document_id — VARCHAR(100) — MongoDB document id when body is stored externally
  • replaces_message_id — CHAR(36) — Original message retained when user edits a message
  • model_name — VARCHAR(100)
  • prompt_tokens — INT
  • completion_tokens — INT
  • created_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

conversations

  • id — CHAR(36) — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • schedule_id — BIGINT
  • title — VARCHAR(200)
  • status — ENUM('ACTIVE','ARCHIVED') — NOT NULL
  • last_message_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

images

  • id — BIGINT — Primary key — NOT NULL
  • uploader_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • owner_type — ENUM('USER', 'MESSAGE', 'SCHEDULE', 'SCHEDULE_ITEM') — NOT NULL
  • owner_id — VARCHAR(100) — NOT NULL — Polymorphic owner id; application validates target
  • storage_key — VARCHAR(1000) — NOT NULL — S3-compatible object storage key
  • original_filename — VARCHAR(255)
  • content_type — VARCHAR(100) — NOT NULL
  • byte_size — BIGINT — NOT NULL
  • width — INT
  • height — INT
  • checksum — VARCHAR(128)
  • created_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

ranking_snapshots

  • id — BIGINT — Primary key — NOT NULL
  • ranking_date — DATE — NOT NULL
  • scope — ENUM('OVERALL','CATEGORY') — NOT NULL
  • category_id — BIGINT
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • rank_no — INT — NOT NULL
  • score — DECIMAL(14,2) — NOT NULL
  • puzzle_count — INT — NOT NULL
  • active_days — INT — NOT NULL
  • achievement_rate — DECIMAL(5,2) — NOT NULL
  • created_at — DATETIME — NOT NULL

reschedule_results

  • id — BIGINT — Primary key — NOT NULL
  • batch_job_id — BIGINT — Foreign key → batch_jobs.id — NOT NULL
  • user_id — BIGINT — NOT NULL
  • schedule_id — BIGINT — NOT NULL
  • achievement_rate — DECIMAL(5,2)
  • status — ENUM('PENDING','RUNNING','SUCCEEDED','PARTIALLY_SUCCEEDED','FAILED') — NOT NULL
  • ai_request_id — VARCHAR(100)
  • result_summary — TEXT
  • error_message — TEXT
  • started_at — DATETIME
  • finished_at — DATETIME
  • created_at — DATETIME — NOT NULL

schedule_change_logs

  • id — BIGINT — Primary key — NOT NULL
  • schedule_id — BIGINT — Foreign key → schedules.id — NOT NULL
  • schedule_item_id — BIGINT
  • actor_user_id — BIGINT
  • action — ENUM('CREATE','UPDATE','DELETE','RESTORE','RESCHEDULE') — NOT NULL
  • source — ENUM('USER','AI','RESCHEDULE_BATCH') — NOT NULL
  • version — INT — NOT NULL
  • before_data — JSON
  • after_data — JSON
  • reason — TEXT
  • batch_job_id — BIGINT
  • created_at — DATETIME — NOT NULL

schedule_items

  • id — BIGINT — Primary key — NOT NULL
  • schedule_id — BIGINT — Foreign key → schedules.id — NOT NULL
  • category_id — BIGINT
  • parent_item_id — BIGINT
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • scheduled_date — DATE — NOT NULL — Date on which AI assigned the task
  • workload — INT — NOT NULL — Relative task weight
  • priority — TINYINT — NOT NULL — 1 highest, 5 lowest
  • puzzle_count — INT — NOT NULL
  • status — ENUM('TODO', 'IN_PROGRESS', 'COMPLETED', 'SKIPPED', 'CANCELLED') — NOT NULL
  • source — ENUM('USER', 'AI', 'RESCHEDULE_BATCH') — NOT NULL
  • position — INT — NOT NULL
  • completed_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

schedules

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • status — ENUM('DRAFT','ACTIVE','COMPLETED','ARCHIVED') — NOT NULL
  • source — ENUM('USER','AI','RESCHEDULE_BATCH') — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • current_version — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

user_auth_accounts

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • provider — ENUM('LOCAL','GOOGLE','KAKAO','NAVER') — NOT NULL
  • provider_user_id — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_daily_metrics

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • category_id — BIGINT — NULL means overall metrics
  • metric_date — DATE — NOT NULL
  • planned_item_count — INT — NOT NULL
  • completed_item_count — INT — NOT NULL
  • planned_minutes — INT — NOT NULL
  • completed_minutes — INT — NOT NULL
  • puzzle_count — INT — NOT NULL
  • achievement_rate — DECIMAL(5, 2) — NOT NULL
  • consecutive_days — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_preferences

  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • max_daily_tasks — INT — NOT NULL — Maximum tasks AI may assign to one date
  • daily_puzzle_goal — INT — Optional daily puzzle target
  • weekend_schedule_enabled — BOOLEAN — NOT NULL
  • ai_reschedule_enabled — BOOLEAN — NOT NULL
  • notification_enabled — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255)
  • nickname — VARCHAR(50) — NOT NULL
  • profile_image_id — BIGINT — Foreign key → images.id
  • timezone — VARCHAR(50) — NOT NULL
  • status — ENUM('ACTIVE','DORMANT','WITHDRAWN','SUSPENDED') — NOT NULL
  • last_login_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • withdrawn_at — DATETIME