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