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