d

Tables (19)

chat_messages

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • role — VARCHAR(20) — NOT NULL — CHECK USER, ASSISTANT
  • content — TEXT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

daily_chat_summaries

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • summary_date — DATE — NOT NULL — UNIQUE(user_id, summary_date)
  • content — TEXT — NOT NULL
  • source_version — BIGINT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

daily_chat_summary_states

  • user_id — BIGINT — Primary key — Foreign key — NOT NULL — PK
  • summary_date — DATE — Primary key — NOT NULL — PK
  • source_version — BIGINT — NOT NULL
  • source_updated_at — TIMESTAMP — NOT NULL
  • status — VARCHAR(20) — NOT NULL — CHECK STALE, CLAIMED, FRESH, FAILED
  • claim_token — VARCHAR(64)
  • claimed_at — TIMESTAMP
  • retry_count — INTEGER — NOT NULL
  • next_retry_at — TIMESTAMP
  • failure_code — VARCHAR(80)
  • changed_sources — TEXT — NOT NULL
  • daily_goal_snapshot_payload — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

daily_goals

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • goal_type — VARCHAR(20) — NOT NULL — CHECK WEIGHT_LOSS, MAINTENANCE, MUSCLE_GAIN
  • calorie_intake_goal — INTEGER — NOT NULL — CHECK > 0
  • exercise_calorie_goal — INTEGER — NOT NULL — CHECK >= 0
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

exercise_activity_options

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • activity_name_ko — VARCHAR(100) — NOT NULL — UNIQUE
  • major_heading — VARCHAR(100) — NOT NULL
  • low_physical_activity_id — BIGINT — Foreign key → physical_activities.id — NOT NULL
  • low_met_value — DECIMAL(4,1) — NOT NULL
  • low_source_description — TEXT — NOT NULL
  • low_met_source — VARCHAR(20) — NOT NULL — CHECK COMPENDIUM, ESTIMATED
  • medium_physical_activity_id — BIGINT — Foreign key → physical_activities.id — NOT NULL
  • medium_met_value — DECIMAL(4,1) — NOT NULL
  • medium_source_description — TEXT — NOT NULL
  • medium_met_source — VARCHAR(20) — NOT NULL — CHECK COMPENDIUM, ESTIMATED
  • high_physical_activity_id — BIGINT — Foreign key → physical_activities.id — NOT NULL
  • high_met_value — DECIMAL(4,1) — NOT NULL
  • high_source_description — TEXT — NOT NULL
  • high_met_source — VARCHAR(20) — NOT NULL — CHECK COMPENDIUM, ESTIMATED
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

exercise_records

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • exercise_activity_option_id — BIGINT — Foreign key → exercise_activity_options.id — NOT NULL
  • intensity_level — VARCHAR(10) — NOT NULL — CHECK LOW, MEDIUM, HIGH
  • exercise_date — DATE — NOT NULL
  • duration_minutes — INTEGER — NOT NULL — CHECK > 0
  • calories_burned — INTEGER — NOT NULL — CHECK >= 0
  • memo — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

food_import_candidates

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • search_miss_id — BIGINT — Foreign key → food_search_misses.id — NOT NULL — UNIQUE(search_miss_id, source_provider, source_key) / UNIQUE(search_miss_id, content_hash)
  • import_run_id — BIGINT — Foreign key → food_import_runs.id
  • status — VARCHAR(20) — NOT NULL — CHECK PENDING, DUPLICATE, APPROVED, REJECTED
  • source_provider — VARCHAR(30) — NOT NULL — UNIQUE(search_miss_id, source_provider, source_key)
  • source_key — VARCHAR(40) — NOT NULL — UNIQUE(search_miss_id, source_provider, source_key)
  • source_url — TEXT
  • source_food_type — VARCHAR(50)
  • name — VARCHAR(255) — NOT NULL
  • brand — VARCHAR(255)
  • food_description — TEXT
  • serving_description — VARCHAR(100)
  • serving_size — NUMERIC(8,2)
  • serving_unit — VARCHAR(20)
  • calories — NUMERIC(8,2) — CHECK >= 0
  • fat — NUMERIC(8,2) — CHECK >= 0
  • carbohydrate — NUMERIC(8,2) — CHECK >= 0
  • protein — NUMERIC(8,2) — CHECK >= 0
  • content_hash — VARCHAR(64) — NOT NULL — UNIQUE(search_miss_id, content_hash)
  • duplicate_food_id — BIGINT — Foreign key → foods.id
  • approved_food_id — BIGINT — Foreign key → foods.id
  • rejection_reason — TEXT
  • reviewed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

food_import_runs

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • status — VARCHAR(20) — NOT NULL — CHECK RUNNING, SUCCESS, PARTIAL_FAILED, FAILED
  • picked_count — INTEGER — NOT NULL
  • candidate_count — INTEGER — NOT NULL
  • failure_count — INTEGER — NOT NULL
  • failure_reason — TEXT
  • started_at — TIMESTAMPTZ — NOT NULL
  • finished_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

food_search_misses

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • query — VARCHAR(255) — NOT NULL
  • normalized_query — VARCHAR(255) — NOT NULL — UNIQUE
  • status — VARCHAR(20) — NOT NULL — CHECK PENDING, PROCESSING, PENDING_REVIEW, APPROVED, REJECTED, NO_RESULT, FAILED
  • miss_count — INTEGER — NOT NULL — CHECK > 0
  • last_requested_at — TIMESTAMPTZ — NOT NULL
  • processing_started_at — TIMESTAMPTZ
  • processed_at — TIMESTAMPTZ
  • retry_count — INTEGER — NOT NULL — CHECK >= 0
  • last_failure_reason — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

food_submission_requests

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • status — VARCHAR(20) — NOT NULL — CHECK PENDING, APPROVED, REJECTED
  • name — VARCHAR(255) — NOT NULL
  • brand — VARCHAR(255)
  • serving_description — VARCHAR(100)
  • serving_size — NUMERIC(8,2)
  • serving_unit — VARCHAR(20)
  • calories — NUMERIC(8,2) — CHECK >= 0
  • carbohydrate — NUMERIC(8,2) — CHECK >= 0
  • protein — NUMERIC(8,2) — CHECK >= 0
  • fat — NUMERIC(8,2) — CHECK >= 0
  • admin_note — TEXT
  • rejection_reason — TEXT
  • approved_food_id — BIGINT — Foreign key → foods.id
  • submitted_at — TIMESTAMPTZ — NOT NULL
  • reviewed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

foods

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • source_key — VARCHAR(40) — NOT NULL — UNIQUE(source_key, serving_key)
  • source_url — TEXT — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • brand — VARCHAR(255)
  • serving_description — VARCHAR(100)
  • serving_key — VARCHAR(100) — UNIQUE(source_key, serving_key)
  • serving_size — NUMERIC(8,2)
  • serving_unit — VARCHAR(20)
  • calories — NUMERIC(8,2) — CHECK >= 0
  • fat — NUMERIC(8,2) — CHECK >= 0
  • carbohydrate — NUMERIC(8,2) — CHECK >= 0
  • protein — NUMERIC(8,2) — CHECK >= 0
  • content_hash — VARCHAR(64) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

meal_items

  • meal_id — BIGINT — Primary key — Foreign key → meals.id — NOT NULL — PK
  • food_id — BIGINT — Primary key — Foreign key → foods.id — NOT NULL — PK
  • quantity — NUMERIC(8,2) — NOT NULL — CHECK > 0

meals

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • meal_type — VARCHAR(10) — NOT NULL — CHECK BREAKFAST, LUNCH, DINNER, SNACK
  • meal_date — DATE — NOT NULL — UNIQUE(user_id, meal_type, meal_date)

oauth_accounts

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • provider — VARCHAR(20) — NOT NULL — UNIQUE(provider, provider_user_id)
  • provider_user_id — VARCHAR(100) — NOT NULL — UNIQUE(provider, provider_user_id)
  • email — VARCHAR(255)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

physical_activities

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • compendium_code — VARCHAR(20) — NOT NULL — UNIQUE
  • compendium_version — VARCHAR(20) — NOT NULL
  • major_heading — VARCHAR(100) — NOT NULL
  • met_value — DECIMAL(4,1) — NOT NULL
  • description — TEXT — NOT NULL

user_memories

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • content — TEXT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user_profiles

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • height_cm — DECIMAL(5,2)
  • current_weight_kg — DECIMAL(5,2)
  • target_weight_kg — DECIMAL(5,2)
  • goal_type — VARCHAR(20)
  • gender — VARCHAR(20) — CHECK MALE, FEMALE
  • age — INTEGER — CHECK age > 0
  • updated_at — TIMESTAMP — NOT NULL

users

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • 이메일 (email) — VARCHAR(255) — NOT NULL — UNIQUE
  • 비밀번호 (password) — VARCHAR(255)
  • nickname — VARCHAR(50) — NOT NULL
  • role — VARCHAR(20) — NOT NULL — CHECK USER, ADMIN
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

weight_records

  • id — BIGSERIAL — Primary key — NOT NULL — PK
  • record_date — DATE — NOT NULL — UNIQUE(user_id, record_date)
  • weight_kg — NUMERIC(5,2) — NOT NULL — 0 < weight <= 500
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL