ktb-5rth-erd

Tables (20)

cooking_applications

  • cooking_application_id — BIGINT — Primary key — NOT NULL
  • refrigerator_id — BIGINT — Foreign key → refrigerators.refrigerator_id — NOT NULL
  • recipe_id — BIGINT — Foreign key → recipes.recipe_id
  • applied_by_user_id — BIGINT — Foreign key → users.user_id
  • inventory_transaction_id — BIGINT — Foreign key → inventory_transactions.inventory_transaction_id — NOT NULL
  • recipe_name_snapshot — VARCHAR(150) — NOT NULL
  • applied_at — DATETIME — NOT NULL

inventory_items

  • inventory_item_id — BIGINT — Primary key — NOT NULL
  • refrigerator_id — BIGINT — Foreign key → refrigerators.refrigerator_id — NOT NULL
  • created_by_user_id — BIGINT — Foreign key → users.user_id
  • ingredient_name — VARCHAR(100) — NOT NULL
  • category — VARCHAR(30) — NOT NULL
  • storage_type — VARCHAR(20) — NOT NULL
  • quantity — SMALLINT — NOT NULL
  • quantity_unit — VARCHAR(20) — NOT NULL
  • weight_value — DECIMAL(10,3)
  • weight_unit — VARCHAR(10)
  • expiration_date — DATE — NOT NULL
  • item_image_url — VARCHAR(2048)
  • registered_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inventory_transaction_items

  • line_no — SMALLINT — Primary key — NOT NULL
  • inventory_transaction_id — BIGINT — Foreign key → inventory_transactions.inventory_transaction_id — NOT NULL
  • inventory_item_id — BIGINT — Foreign key → inventory_items.inventory_item_id
  • ingredient_name_snapshot — VARCHAR(100) — NOT NULL
  • before_quantity — SMALLINT — NOT NULL
  • changed_quantity — SMALLINT — NOT NULL
  • after_quantity — SMALLINT — NOT NULL

inventory_transactions

  • inventory_transaction_id — BIGINT — Primary key — NOT NULL
  • refrigerator_id — BIGINT — Foreign key → refrigerators.refrigerator_id — NOT NULL
  • actor_user_id — BIGINT — Foreign key → users.user_id
  • transaction_type — VARCHAR(30) — NOT NULL
  • occurred_at — DATETIME — NOT NULL

invite_codes

  • invite_code_id — BIGINT — Primary key — NOT NULL
  • refrigerator_id — BIGINT — Foreign key → refrigerators.refrigerator_id — NOT NULL
  • created_by_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • used_by_user_id — BIGINT — Foreign key → users.user_id
  • code — CHAR(6) — NOT NULL
  • invite_status — VARCHAR(20) — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • used_at — DATETIME

notification_inventory_items

  • line_no — SMALLINT — Primary key — NOT NULL
  • notification_id — BIGINT — Foreign key → notifications.notification_id — NOT NULL
  • inventory_item_id — BIGINT — Foreign key → inventory_items.inventory_item_id
  • ingredient_name_snapshot — VARCHAR(100) — NOT NULL
  • expiration_date_snapshot — DATE — NOT NULL

notification_recipes

  • notification_id — BIGINT — Foreign key → notifications.notification_id — NOT NULL
  • recipe_id — BIGINT — Foreign key → recipes.recipe_id
  • recipe_name_snapshot — VARCHAR(150) — NOT NULL

notification_settings

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • all_enabled — BOOLEAN — NOT NULL
  • expiring_enabled — BOOLEAN — NOT NULL
  • expired_enabled — BOOLEAN — NOT NULL
  • recipe_enabled — BOOLEAN — NOT NULL
  • updated_at — DATETIME — NOT NULL

notifications

  • notification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • notification_type — VARCHAR(20) — NOT NULL
  • title — VARCHAR(150) — NOT NULL
  • body — VARCHAR(500) — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • read_at — DATETIME

receipt_scan_items

  • line_no — SMALLINT — Primary key — NOT NULL
  • receipt_scan_id — BIGINT — Foreign key → receipt_scans.receipt_scan_id — NOT NULL
  • recognized_name — VARCHAR(100)
  • recognized_quantity — DECIMAL(10,3)
  • recognized_unit — VARCHAR(20)
  • suggested_category — VARCHAR(30)
  • suggested_expiration_date — DATE
  • recognition_status — VARCHAR(30) — NOT NULL

receipt_scans

  • receipt_scan_id — BIGINT — Primary key — NOT NULL
  • refrigerator_id — BIGINT — Foreign key → refrigerators.refrigerator_id — NOT NULL
  • requested_by_user_id — BIGINT — Foreign key → users.user_id
  • image_url — VARCHAR(2048) — NOT NULL
  • scan_type — VARCHAR(20) — NOT NULL
  • scan_status — VARCHAR(20) — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • completed_at — DATETIME

recipe_ingredients

  • ingredient_no — SMALLINT — Primary key — NOT NULL
  • recipe_id — BIGINT — Foreign key → recipes.recipe_id — NOT NULL
  • ingredient_name — VARCHAR(100) — NOT NULL
  • required_quantity — DECIMAL(10,3) — NOT NULL
  • required_unit — VARCHAR(20) — NOT NULL

recipe_steps

  • step_no — SMALLINT — Primary key — NOT NULL
  • recipe_id — BIGINT — Foreign key → recipes.recipe_id — NOT NULL
  • instruction — TEXT — NOT NULL

recipes

  • recipe_id — BIGINT — Primary key — NOT NULL
  • recipe_name — VARCHAR(150) — NOT NULL
  • image_url — VARCHAR(2048)
  • cooking_time_minutes — SMALLINT — NOT NULL
  • recipe_status — VARCHAR(20) — NOT NULL

refrigerator_members

  • refrigerator_id — BIGINT — Foreign key → refrigerators.refrigerator_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • member_role — VARCHAR(20) — NOT NULL
  • membership_status — VARCHAR(20) — NOT NULL
  • joined_at — DATETIME — NOT NULL
  • left_at — DATETIME

refrigerators

  • refrigerator_id — BIGINT — Primary key — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • refrigerator_type — VARCHAR(20) — NOT NULL
  • display_name — VARCHAR(30) — NOT NULL
  • refrigerator_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL

social_accounts

  • social_account_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • provider — VARCHAR(20) — NOT NULL
  • provider_user_id — VARCHAR(255) — NOT NULL
  • provider_email — VARCHAR(320)
  • linked_at — DATETIME — NOT NULL

terms

  • term_id — BIGINT — Primary key — NOT NULL
  • term_code — VARCHAR(40) — NOT NULL
  • version — VARCHAR(30) — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • is_required — BOOLEAN — NOT NULL
  • effective_at — DATETIME — NOT NULL

user_term_agreements

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • term_id — BIGINT — Foreign key → terms.term_id — NOT NULL
  • agreed — BOOLEAN — NOT NULL
  • agreed_at — DATETIME — NOT NULL

users

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(320)
  • nickname — VARCHAR(10) — NOT NULL
  • profile_image_url — VARCHAR(2048)
  • user_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL