3

Tables (24)

AiRecommendation

  • ai_rec_id — BIGINT — Primary key — NOT NULL
  • ai_log_id — BIGINT — Foreign key → AiRecommendationLog.ai_log_id — NOT NULL
  • quest_id — BIGINT — Foreign key → Quest.quest_id
  • score — FLOAT
  • reason — TEXT
  • rank — INTEGER
  • created_at — DATETIME

AiRecommendationLog

  • ai_log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • request_context — JSON
  • reason — TEXT
  • created_at — DATETIME

BackgroundMusic

  • bgm_id — INT — Primary key — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • mood_tag — VARCHAR(50)
  • file_url — VARCHAR(512) — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

Badge

  • badge_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • description — VARCHAR(255)
  • icon_url — VARCHAR(500)
  • badge_type — VARCHAR(30)
  • unlock_condition — VARCHAR(255)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

Category

  • category_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • icon_url — VARCHAR(500)
  • color — VARCHAR(20)
  • sort_order — INTEGER
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

Comment

  • comment_id — BIGINT — Primary key — NOT NULL
  • post_id — BIGINT — Foreign key → CommunityPost.post_id — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • content — VARCHAR(500)
  • created_at — DATETIME
  • updated_at — DATETIME

CommunityPost

  • post_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • submission_id — BIGINT — Foreign key → QuestSubmission.submission_id
  • shorts_id — INT
  • media_url — VARCHAR(500)
  • media_type — VARCHAR(20)
  • caption — TEXT
  • view_count — INTEGER
  • like_count — INTEGER
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

Competition

  • competition_id — BIGINT — Primary key — NOT NULL
  • title — VARCHAR(200)
  • description — TEXT
  • start_at — TIMESTAMP
  • end_at — TIMESTAMP
  • status — ENUM('scheduled','ongoing','ended')
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

CompetitionContribution

  • contribution_id — BIGINT — Primary key — NOT NULL
  • competition_id — BIGINT — Foreign key → Competition.competition_id — NOT NULL
  • region_id — BIGINT — Foreign key → Region.region_id — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • submission_id — BIGINT — Foreign key → QuestSubmission.submission_id
  • points — INTEGER
  • created_at — TIMESTAMP

CompetitionRegion

  • comp_region_id — BIGINT — Primary key — NOT NULL
  • competition_id — BIGINT — Foreign key → Competition.competition_id — NOT NULL
  • region_id — BIGINT — Foreign key → Region.region_id — NOT NULL
  • total_score — INTEGER
  • rank — INTEGER
  • joined_at — TIMESTAMP
  • updated_at — TIMESTAMP

Item

  • item_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100)
  • description — TEXT
  • price_point — INTEGER
  • item_type — VARCHAR(30)
  • image_url — VARCHAR(500)
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

PointTransaction

  • transaction_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • amount — INTEGER
  • type — VARCHAR(20)
  • source_type — VARCHAR(30)
  • source_id — BIGINT
  • balance_after — INTEGER
  • created_at — TIMESTAMP

PostLike

  • post_id — BIGINT — Foreign key → CommunityPost.post_id — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • created_at — TIMESTAMP

Purchase

  • purchase_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • item_id — BIGINT — Foreign key → Item.item_id — NOT NULL
  • quantity — INTEGER
  • total_point — INTEGER
  • status — VARCHAR(20)
  • purchased_at — TIMESTAMP
  • updated_at — TIMESTAMP

Quest

  • quest_id — BIGINT — Primary key — NOT NULL
  • title — VARCHAR(200)
  • description — TEXT
  • category_id — BIGINT — Foreign key → Category.category_id
  • quest_type — ENUM('team','solo')
  • verification_method — ENUM('photo','video','gps','code')
  • creator_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • location — VARCHAR(255)
  • reward_point — INTEGER
  • difficulty — VARCHAR(20)
  • quest_embedding — JSON
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

QuestSubmission

  • submission_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • quest_id — BIGINT — Foreign key → Quest.quest_id — NOT NULL
  • attempt_number — INTEGER
  • media_url — VARCHAR(500)
  • media_type — VARCHAR(20)
  • media_hash — VARCHAR(255)
  • media_embedding — JSON
  • challenge_code — VARCHAR(50)
  • gemini_verdict — JSON
  • ai_generated_suspicion — BOOLEAN
  • final_status — VARCHAR(20)
  • reviewed_by — BIGINT — Foreign key → User.user_id
  • submitted_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • reviewed_at — TIMESTAMP

Region

  • region_id — BIGINT — Primary key — NOT NULL
  • region_name — VARCHAR(100)
  • sigungu — VARCHAR(50)
  • mid_latlan — DECIMAL(10,7)
  • kakao_region_code — VARCHAR(50)
  • updated_at — TIMESTAMP

Report

  • report_id — BIGINT — Primary key — NOT NULL
  • reporter_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • target_type — VARCHAR(20)
  • target_id — BIGINT
  • reason — TEXT
  • status — VARCHAR(20)
  • reviewed_by — BIGINT — Foreign key → User.user_id
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • reviewed_at — TIMESTAMP

ShortForm

  • shorts_id — INT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id
  • bgm_id — INT — Foreign key → BackgroundMusic.bgm_id
  • title — VARCHAR(255)
  • ai_generated_captions — JSON
  • final_video_url — VARCHAR(512)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

Team

  • team_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100)
  • notification — TEXT
  • leader_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • region — VARCHAR(100)
  • max_members — INTEGER
  • status — ENUM('recruiting','active','expired','disbanded')
  • expires_at — TIMESTAMP
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

TeamInvite

  • invite_id — BIGINT — Primary key — NOT NULL
  • team_id — BIGINT — Foreign key → Team.team_id — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • status — ENUM('pending','accepted','rejected','expired')
  • expires_at — TIMESTAMP
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

TeamMember

  • team_member_id — BIGINT — Primary key — NOT NULL
  • team_id — BIGINT — Foreign key → Team.team_id — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • role_in_team — ENUM('member','leader')
  • join_status — ENUM('pending','accepted','expired')
  • joined_at — TIMESTAMP
  • updated_at — TIMESTAMP

User

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • provider — VARCHAR(50)
  • provider_user_id — VARCHAR(255)
  • password_hash — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • interests — JSON
  • goal — VARCHAR(255)
  • active_time — VARCHAR(50)
  • region — VARCHAR(100)
  • preferred_difficulty — VARCHAR(20)
  • profile_embedding — JSON
  • trust_score — INTEGER
  • point_balance — INTEGER
  • current_xp — INT
  • current_level — INT
  • daily_streak — INT
  • role — VARCHAR(20)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • last_login_at — TIMESTAMP

UserBadge

  • user_badge_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → User.user_id — NOT NULL
  • badge_id — BIGINT — Foreign key → Badge.badge_id — NOT NULL
  • is_equipped — BOOLEAN
  • awarded_at — TIMESTAMP
  • updated_at — TIMESTAMP