캡스톤디자인

테이블 (13)

fridge_members

  • fridge_id — BIGINT — 외래 키 → fridges.fridge_id — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • role — ENUM('OWNER','MEMBER') — NOT NULL
  • status — ENUM('ACTIVE','INVITED','LEFT') — NOT NULL
  • joined_at — DATETIME — NOT NULL
  • INDEX — idx_fm_user
  • INDEX — idx_fm_fridge_status

fridges

  • fridge_id — BIGINT
  • name — VARCHAR(50) — NOT NULL
  • owner_user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL

ingredient_alias

  • alias_id — BIGINT
  • alias_text — VARCHAR(120) — NOT NULL
  • ingredient_id — BIGINT — 외래 키 → ingredient_master.ingredient_id — NOT NULL
  • confidence — DECIMAL(4,3)
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_alias_text

ingredient_master

  • ingredient_id — BIGINT
  • ingredient_type — ENUM('INGREDIENT','SEASONING','BEVERAGE','ETC') — NOT NULL
  • canonical_name — VARCHAR(120) — NOT NULL
  • default_unit — VARCHAR(10)
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_ing_type

inventory_batches

  • batch_id — BIGINT
  • fridge_id — BIGINT — 외래 키 → fridges.fridge_id — NOT NULL
  • ingredient_id — BIGINT — 외래 키 → ingredient_master.ingredient_id — NOT NULL
  • consume_by_date — DATE
  • remaining_quantity — DECIMAL(10,2) — NOT NULL
  • unit — VARCHAR(10) — NOT NULL
  • status — ENUM('ACTIVE','CONSUMED','DISCARDED') — NOT NULL
  • created_by — BIGINT — 외래 키 → users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_batch_fridge_status_consume
  • INDEX — idx_batch_fridge_ing

meal_consumptions

  • consumption_id — BIGINT
  • meal_event_id — BIGINT — 외래 키 → meal_events.meal_event_id — NOT NULL
  • batch_id — BIGINT — 외래 키 → inventory_batches.batch_id — NOT NULL
  • quantity_used — DECIMAL(10,2) — NOT NULL
  • unit — VARCHAR(10) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_mc_event
  • INDEX — idx_mc_batch

meal_events

  • meal_event_id — BIGINT
  • fridge_id — BIGINT — 외래 키 → fridges.fridge_id — NOT NULL
  • owner_user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • title — VARCHAR(120) — NOT NULL
  • meal_type — ENUM('BREAKFAST','LUNCH','DINNER','SNACK')
  • note — VARCHAR(500)
  • eaten_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_meal_fridge_time
  • INDEX — idx_meal_owner_time

recipe_generations

  • generation_id — BIGINT
  • recipe_id — BIGINT — 외래 키 → recipes.recipe_id — NOT NULL
  • requested_by — BIGINT — 외래 키 → users.user_id — NOT NULL
  • model_name — VARCHAR(80)
  • prompt_json — JSON
  • input_context_json — JSON
  • output_raw_json — JSON
  • score — DECIMAL(6,3)
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_rg_recipe_time
  • INDEX — idx_rg_user_time

recipe_ingredients

  • recipe_ingredient_id — BIGINT
  • recipe_id — BIGINT — 외래 키 → recipes.recipe_id — NOT NULL
  • ingredient_id — BIGINT — 외래 키 → ingredient_master.ingredient_id
  • name_text — VARCHAR(120) — NOT NULL
  • quantity — DECIMAL(10,2)
  • unit — VARCHAR(10)
  • optional — BOOLEAN — NOT NULL
  • INDEX — idx_ri_recipe
  • INDEX — idx_ri_ing

recipes

  • recipe_id — BIGINT
  • owner_user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • recipe_name — VARCHAR(150) — NOT NULL
  • cook_time — INT
  • is_generated — BOOLEAN — NOT NULL
  • description — VARCHAR(500)
  • memo — VARCHAR(500)
  • steps_json — JSON — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_recipe_owner_time

shopping_list_items

  • item_id — BIGINT
  • list_id — BIGINT — 외래 키 → shopping_lists.list_id — NOT NULL
  • ingredient_id — BIGINT — 외래 키 → ingredient_master.ingredient_id
  • item_name — VARCHAR(120) — NOT NULL
  • quantity — DECIMAL(10,2)
  • unit — VARCHAR(10)
  • state — ENUM('NEEDED','BOUGHT','REMOVED') — NOT NULL
  • memo — VARCHAR(200)
  • source_type — ENUM('MANUAL','RECIPE','LOW_STOCK','EXPIRY') — NOT NULL
  • source_ref_id — BIGINT
  • created_by — BIGINT — 외래 키 → users.user_id — NOT NULL
  • bought_by — BIGINT — 외래 키 → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_sli_list_state
  • INDEX — idx_sli_ing

shopping_lists

  • list_id — BIGINT
  • fridge_id — BIGINT — 외래 키 → fridges.fridge_id — NOT NULL
  • owner_user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • status — ENUM('ACTIVE','ARCHIVED') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_sl_fridge_status

users

  • user_id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255)
  • social_provider — ENUM('local','google','kakao','naver','apple') — NOT NULL
  • condition — VARCHAR(255)
  • name — VARCHAR(50)
  • created_at — DATETIME — NOT NULL