Moodtail

Tables (37)

cocktail_favorites

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • created_at — datetime — NOT NULL

cocktail_ingredients

  • id — bigint — Primary key — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • name — varchar(100) — NOT NULL
  • amount_ml — decimal(6,2)
  • amount_text — varchar(100)
  • display_order — int — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

cocktail_pairing_snacks

  • id — bigint — Primary key — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • name — varchar(100) — NOT NULL
  • display_order — int — NOT NULL
  • created_at — datetime — NOT NULL

cocktail_recipe_steps

  • id — bigint — Primary key — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • step_order — int — NOT NULL
  • description — text — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

cocktails

  • id — bigint — Primary key — NOT NULL
  • name_ko — varchar(100) — NOT NULL
  • name_en — varchar(100) — NOT NULL
  • description — text
  • base_spirit — ENUM('GIN', 'RUM', 'VODKA', 'TEQUILA', 'WHISKEY', 'BRANDY', 'LIQUEUR', 'WINE', 'CHAMPAGNE', 'NON_ALCOHOLIC', 'ETC') — NOT NULL
  • alcohol_degree — decimal(4,1) — NOT NULL
  • alcohol_range — ENUM('LOW', 'MEDIUM', 'HIGH') — NOT NULL
  • alcohol_intensity — decimal(3,2) — NOT NULL
  • sweetness — decimal(3,2) — NOT NULL
  • sourness — decimal(3,2) — NOT NULL
  • bitterness — decimal(3,2) — NOT NULL
  • refreshing — decimal(3,2) — NOT NULL
  • taste_keyword_summary — varchar(255)
  • image_media_file_id — bigint — Foreign key → media_files.id
  • is_iba — boolean — NOT NULL
  • is_active — boolean — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

collection_share_links

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • public_token — varchar(100) — NOT NULL
  • share_title — varchar(100)
  • unlocked_type_count_snapshot — int — NOT NULL
  • view_count — int — NOT NULL
  • expires_at — datetime
  • created_at — datetime — NOT NULL

compromise_participants

  • id — bigint — Primary key — NOT NULL
  • compromise_request_id — bigint — Foreign key → compromise_requests.id — NOT NULL
  • user_id — bigint — Foreign key → users.id
  • mood_test_result_id — bigint — Foreign key → mood_test_results.id
  • share_link_id — bigint — Foreign key → mood_test_result_share_links.id
  • participant_role — ENUM('REQUESTER', 'PARTNER') — NOT NULL
  • alcohol_intensity — decimal(3,2) — NOT NULL
  • sweetness — decimal(3,2) — NOT NULL
  • sourness — decimal(3,2) — NOT NULL
  • bitterness — decimal(3,2) — NOT NULL
  • refreshing — decimal(3,2) — NOT NULL
  • joined_at — datetime — NOT NULL

compromise_requests

  • id — bigint — Primary key — NOT NULL
  • requester_user_id — bigint — Foreign key → users.id — NOT NULL
  • title — varchar(100)
  • status — ENUM('PENDING', 'READY', 'COMPLETED', 'EXPIRED') — NOT NULL
  • public_token — varchar(100) — NOT NULL
  • selected_recommendation_session_id — bigint — Foreign key → recommendation_sessions.id
  • expires_at — datetime
  • created_at — datetime — NOT NULL
  • completed_at — datetime

custom_taste_profiles

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id
  • sweetness_input — int — NOT NULL
  • sourness_input — int — NOT NULL
  • bitterness_input — int — NOT NULL
  • alcohol_intensity_input — int — NOT NULL
  • refreshing_input — int — NOT NULL
  • normalized_sweetness — decimal(3,2) — NOT NULL
  • normalized_sourness — decimal(3,2) — NOT NULL
  • normalized_bitterness — decimal(3,2) — NOT NULL
  • normalized_alcohol_intensity — decimal(3,2) — NOT NULL
  • normalized_refreshing — decimal(3,2) — NOT NULL
  • created_at — datetime — NOT NULL

daily_cocktail_curations

  • id — bigint — Primary key — NOT NULL
  • curation_date — date — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • season — ENUM('SPRING', 'SUMMER', 'AUTUMN', 'WINTER')
  • weather_type — ENUM('CLEAR', 'RAIN', 'SNOW', 'CLOUDS', 'HOT', 'COLD', 'ETC')
  • day_type — ENUM('WEEKDAY', 'WEEKEND', 'HOLIDAY') — NOT NULL
  • reason_text — varchar(255)
  • created_at — datetime — NOT NULL

daily_records

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • record_date — date — NOT NULL
  • mood_test_result_id — bigint — Foreign key → mood_test_results.id
  • memo — text
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

drinking_records

  • id — bigint — Primary key — NOT NULL
  • daily_record_id — bigint — Foreign key → daily_records.id — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id
  • cocktail_name_snapshot — varchar(100) — NOT NULL
  • mood_type_id_snapshot — bigint — Foreign key → mood_types.id
  • satisfaction — int
  • memo — text
  • recorded_at — datetime — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

history_photos

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • daily_record_id — bigint — Foreign key → daily_records.id — NOT NULL
  • drinking_record_id — bigint — Foreign key → drinking_records.id
  • media_file_id — bigint — Foreign key → media_files.id — NOT NULL
  • photo_source — ENUM('CAMERA', 'GALLERY', 'SYSTEM_GENERATED', 'EXTERNAL') — NOT NULL
  • caption — varchar(255)
  • display_order — int — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

media_files

  • id — bigint — Primary key — NOT NULL
  • owner_user_id — bigint — Foreign key → users.id
  • media_type — ENUM('IMAGE') — NOT NULL
  • source_type — ENUM('CAMERA', 'GALLERY', 'SYSTEM_GENERATED', 'EXTERNAL') — NOT NULL
  • file_url — varchar(500) — NOT NULL
  • thumbnail_url — varchar(500)
  • original_file_name — varchar(255)
  • content_type — varchar(100)
  • file_size_bytes — bigint
  • width — int
  • height — int
  • created_at — datetime — NOT NULL

monthly_reports

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • report_month — char(7) — NOT NULL
  • dominant_mood_type_id — bigint — Foreign key → mood_types.id
  • previous_mood_type_id — bigint — Foreign key → mood_types.id
  • test_result_count — int — NOT NULL
  • drinking_record_count — int — NOT NULL
  • average_alcohol_intensity — decimal(3,2)
  • average_sweetness — decimal(3,2)
  • average_sourness — decimal(3,2)
  • average_bitterness — decimal(3,2)
  • average_refreshing — decimal(3,2)
  • report_status — ENUM('AVAILABLE', 'INSUFFICIENT_DATA') — NOT NULL
  • generated_at — datetime
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

mood_question_options

  • id — bigint — Primary key — NOT NULL
  • mood_question_id — bigint — Foreign key → mood_questions.id — NOT NULL
  • option_order — int — NOT NULL
  • content — varchar(255) — NOT NULL
  • alcohol_intensity_score — decimal(3,2) — NOT NULL
  • sweetness_score — decimal(3,2) — NOT NULL
  • sourness_score — decimal(3,2) — NOT NULL
  • bitterness_score — decimal(3,2) — NOT NULL
  • refreshing_score — decimal(3,2) — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

mood_questions

  • id — bigint — Primary key — NOT NULL
  • question_type — ENUM('FIXED', 'RANDOM') — NOT NULL
  • content — varchar(500) — NOT NULL
  • display_order — int
  • is_active — boolean — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

mood_test_answers

  • id — bigint — Primary key — NOT NULL
  • mood_test_result_id — bigint — Foreign key → mood_test_results.id — NOT NULL
  • mood_question_id — bigint — Foreign key → mood_questions.id — NOT NULL
  • mood_question_option_id — bigint — Foreign key → mood_question_options.id — NOT NULL
  • question_type — ENUM('FIXED', 'RANDOM') — NOT NULL
  • answer_order — int — NOT NULL
  • created_at — datetime — NOT NULL

mood_test_result_share_links

  • id — bigint — Primary key — NOT NULL
  • mood_test_result_id — bigint — Foreign key → mood_test_results.id — NOT NULL
  • owner_user_id — bigint — Foreign key → users.id — NOT NULL
  • public_token — varchar(100) — NOT NULL
  • view_count — int — NOT NULL
  • expires_at — datetime
  • created_at — datetime — NOT NULL

mood_test_results

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • result_date — date — NOT NULL
  • alcohol_intensity — decimal(3,2) — NOT NULL
  • sweetness — decimal(3,2) — NOT NULL
  • sourness — decimal(3,2) — NOT NULL
  • bitterness — decimal(3,2) — NOT NULL
  • refreshing — decimal(3,2) — NOT NULL
  • type_distance — decimal(8,4) — NOT NULL
  • answer_count — int — NOT NULL
  • fixed_answer_count — int — NOT NULL
  • random_answer_count — int — NOT NULL
  • is_saved — boolean — NOT NULL
  • saved_at — datetime
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

mood_type_cocktail_statistics

  • id — bigint — Primary key — NOT NULL
  • mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • period_type — ENUM('DAILY', 'WEEKLY', 'MONTHLY', 'ALL') — NOT NULL
  • period_key — varchar(20) — NOT NULL
  • drink_count — int — NOT NULL
  • user_count — int — NOT NULL
  • ranking — int — NOT NULL
  • calculated_at — datetime — NOT NULL

mood_type_cocktails

  • id — bigint — Primary key — NOT NULL
  • mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • pool_order — int — NOT NULL
  • created_at — datetime — NOT NULL

mood_type_collection_statistics

  • id — bigint — Primary key — NOT NULL
  • mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • collected_user_count — int — NOT NULL
  • total_user_count — int — NOT NULL
  • collection_rate — decimal(5,2) — NOT NULL
  • calculated_at — datetime — NOT NULL

mood_type_compatibilities

  • id — bigint — Primary key — NOT NULL
  • mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • target_mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • compatibility_type — ENUM('BEST', 'WORST') — NOT NULL
  • distance — decimal(8,4) — NOT NULL
  • created_at — datetime — NOT NULL

mood_types

  • id — bigint — Primary key — NOT NULL
  • code — varchar(50) — NOT NULL
  • name — varchar(50) — NOT NULL
  • description — text — NOT NULL
  • character_name — varchar(50) — NOT NULL
  • character_media_file_id — bigint — Foreign key → media_files.id
  • alcohol_intensity — decimal(3,2) — NOT NULL
  • sweetness — decimal(3,2) — NOT NULL
  • sourness — decimal(3,2) — NOT NULL
  • bitterness — decimal(3,2) — NOT NULL
  • refreshing — decimal(3,2) — NOT NULL
  • sort_order — int — NOT NULL
  • is_active — boolean — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

recommendation_contexts

  • id — bigint — Primary key — NOT NULL
  • recommendation_session_id — bigint — Foreign key → recommendation_sessions.id — NOT NULL
  • season — ENUM('SPRING', 'SUMMER', 'AUTUMN', 'WINTER')
  • weather_type — ENUM('CLEAR', 'RAIN', 'SNOW', 'CLOUDS', 'HOT', 'COLD', 'ETC')
  • day_type — ENUM('WEEKDAY', 'WEEKEND', 'HOLIDAY') — NOT NULL
  • temperature_celsius — decimal(4,1)
  • recent_history_days — int — NOT NULL
  • recent_test_result_id — bigint — Foreign key → mood_test_results.id
  • recent_drinking_record_id — bigint — Foreign key → drinking_records.id
  • context_summary — varchar(255)
  • created_at — datetime — NOT NULL

recommendation_items

  • id — bigint — Primary key — NOT NULL
  • recommendation_session_id — bigint — Foreign key → recommendation_sessions.id — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • ranking — int — NOT NULL
  • match_rate — int — NOT NULL
  • distance — decimal(8,4) — NOT NULL
  • was_previously_recommended — boolean — NOT NULL
  • novelty_boost_applied — boolean — NOT NULL
  • reason_code — varchar(50)
  • reason_text — varchar(255)
  • created_at — datetime — NOT NULL

recommendation_sessions

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id
  • session_type — ENUM('TEST_RESULT', 'TODAY_CONTEXT', 'CUSTOM_TASTE', 'COMPROMISE') — NOT NULL
  • mood_test_result_id — bigint — Foreign key → mood_test_results.id
  • custom_taste_profile_id — bigint — Foreign key → custom_taste_profiles.id
  • compromise_request_id — bigint — Foreign key → compromise_requests.id
  • base_mood_type_id — bigint — Foreign key → mood_types.id
  • algorithm_version — varchar(50) — NOT NULL
  • exclude_previous — boolean — NOT NULL
  • requested_at — datetime — NOT NULL
  • created_at — datetime — NOT NULL

refresh_tokens

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • token_hash — varchar(255) — NOT NULL
  • expires_at — datetime — NOT NULL
  • revoked_at — datetime
  • created_at — datetime — NOT NULL

share_cards

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id
  • card_type — ENUM('RESULT', 'REPORT', 'COLLECTION', 'COMPROMISE') — NOT NULL
  • mood_test_result_id — bigint — Foreign key → mood_test_results.id
  • monthly_report_id — bigint — Foreign key → monthly_reports.id
  • collection_share_link_id — bigint — Foreign key → collection_share_links.id
  • compromise_request_id — bigint — Foreign key → compromise_requests.id
  • media_file_id — bigint — Foreign key → media_files.id — NOT NULL
  • public_token — varchar(100) — NOT NULL
  • view_count — int — NOT NULL
  • expires_at — datetime
  • created_at — datetime — NOT NULL

social_accounts

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • provider — ENUM('GOOGLE', 'KAKAO') — NOT NULL
  • provider_user_id — varchar(100) — NOT NULL
  • provider_email — varchar(255) — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

user_cocktail_recommendation_histories

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • cocktail_id — bigint — Foreign key → cocktails.id — NOT NULL
  • recommendation_session_id — bigint — Foreign key → recommendation_sessions.id — NOT NULL
  • recommendation_item_id — bigint — Foreign key → recommendation_items.id — NOT NULL
  • session_type — ENUM('TEST_RESULT', 'TODAY_CONTEXT', 'CUSTOM_TASTE', 'COMPROMISE') — NOT NULL
  • first_recommended_at — datetime — NOT NULL
  • last_recommended_at — datetime — NOT NULL
  • recommended_count — int — NOT NULL

user_mood_type_collections

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • collected_cocktail_count — int — NOT NULL
  • required_cocktail_count — int — NOT NULL
  • status — ENUM('LOCKED', 'UNLOCKED') — NOT NULL
  • unlocked_at — datetime
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

user_term_agreements

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • term_type — ENUM('SERVICE', 'PRIVACY', 'MARKETING') — NOT NULL
  • is_required — boolean — NOT NULL
  • agreed_at — datetime — NOT NULL
  • created_at — datetime — NOT NULL

user_trend_mood_type_counts

  • id — bigint — Primary key — NOT NULL
  • user_trend_snapshot_id — bigint — Foreign key → user_trend_snapshots.id — NOT NULL
  • mood_type_id — bigint — Foreign key → mood_types.id — NOT NULL
  • count — int — NOT NULL
  • ratio — decimal(5,2) — NOT NULL

user_trend_snapshots

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • trigger_mood_test_result_id — bigint — Foreign key → mood_test_results.id — NOT NULL
  • period_type — ENUM('RECENT_7_DAYS', 'RECENT_30_DAYS', 'ALL') — NOT NULL
  • period_start_date — date — NOT NULL
  • period_end_date — date — NOT NULL
  • total_test_count — int — NOT NULL
  • dominant_mood_type_id — bigint — Foreign key → mood_types.id
  • average_alcohol_intensity — decimal(3,2)
  • average_sweetness — decimal(3,2)
  • average_sourness — decimal(3,2)
  • average_bitterness — decimal(3,2)
  • average_refreshing — decimal(3,2)
  • created_at — datetime — NOT NULL

users

  • id — bigint — Primary key — NOT NULL
  • email — varchar(255) — NOT NULL
  • nickname — varchar(50)
  • status — ENUM('ACTIVE', 'WITHDRAWN') — NOT NULL
  • representative_mood_type_id — bigint — Foreign key → mood_types.id
  • last_login_at — datetime
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL
  • deleted_at — datetime