daily_log2

Tables (25)

calendar_events

  • event_id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • title — TEXT — NOT NULL
  • starts_at — TIMESTAMPTZ — NOT NULL
  • ends_at — TIMESTAMPTZ — NOT NULL
  • all_day — BOOLEAN — NOT NULL
  • location — TEXT
  • description — TEXT
  • source — VARCHAR(20) — NOT NULL
  • external_event_id — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

checkin_session_slots

  • slot_key — VARCHAR(20) — Primary key — NOT NULL
  • session_id — UUID — Foreign key → checkin_sessions.session_id — NOT NULL
  • slot_value — JSONB — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

checkin_sessions

  • session_id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • date_local — DATE — NOT NULL
  • status — VARCHAR(12) — NOT NULL
  • turn_count — INT — NOT NULL
  • turn_limit — INT — NOT NULL
  • constraints — JSONB — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • finished_at — TIMESTAMPTZ

checkin_topics

  • topic_id — UUID
  • session_id — UUID — Foreign key → checkin_sessions.session_id — NOT NULL
  • topic_text — TEXT — NOT NULL
  • user_choice — VARCHAR(10) — NOT NULL
  • score — DOUBLE
  • created_at — TIMESTAMPTZ — NOT NULL

checkin_turns

  • turn_id — UUID
  • session_id — UUID — Foreign key → checkin_sessions.session_id — NOT NULL
  • turn_index — INT — NOT NULL
  • slot — VARCHAR(20) — NOT NULL
  • question_text — TEXT — NOT NULL
  • answer_payload — JSONB — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

diary_entries

  • entry_id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • session_id — UUID — Foreign key → checkin_sessions.session_id — NOT NULL
  • date_local — DATE — NOT NULL
  • title — VARCHAR(50) — NOT NULL
  • content — TEXT — NOT NULL
  • style_code — VARCHAR(30)
  • highlights — JSONB — NOT NULL
  • challenges — JSONB — NOT NULL
  • tomorrow_action — JSONB — NOT NULL
  • tags — TEXT — NOT NULL
  • mood_vector — JSONB — NOT NULL
  • model_version — VARCHAR(30) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

diary_photos

  • photo_id — UUID
  • entry_id — UUID — Foreign key → diary_entries.entry_id — NOT NULL
  • asset_url — TEXT — NOT NULL
  • sort_order — INT — NOT NULL
  • pos_x — DOUBLE
  • pos_y — DOUBLE
  • scale — DOUBLE
  • rotation — DOUBLE
  • created_at — TIMESTAMPTZ — NOT NULL

events

  • event_id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • event_time — TIMESTAMPTZ — NOT NULL
  • event_type — VARCHAR(20) — NOT NULL
  • item_id — VARCHAR(60) — Foreign key → items.item_id
  • rec_id — UUID — Foreign key → recommendations.rec_id
  • session_id — UUID — Foreign key → checkin_sessions.session_id
  • ab_variant — VARCHAR(2)
  • value — DOUBLE
  • payload — JSONB — NOT NULL
  • model_version — VARCHAR(30)

insights_daily

  • id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • date_local — DATE — NOT NULL
  • metric — VARCHAR(30) — NOT NULL
  • percentile — INT — NOT NULL
  • statement — TEXT — NOT NULL
  • sample_n — INT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

interest_options

  • interest_id — SMALLSERIAL
  • code — VARCHAR(30) — NOT NULL
  • label — TEXT — NOT NULL

item_categories

  • category_id — BIGSERIAL
  • name — VARCHAR(50) — NOT NULL

items

  • item_id — VARCHAR(60)
  • category_id — BIGINT — Foreign key → item_categories.category_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • duration_bucket — INT — NOT NULL
  • place — VARCHAR(10) — NOT NULL
  • social — VARCHAR(10) — NOT NULL
  • intensity — VARCHAR(10) — NOT NULL
  • mood_target — VARCHAR(15) — NOT NULL
  • tags — TEXT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

mood_daily

  • mood_id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • date_local — DATE — NOT NULL
  • session_id — UUID — Foreign key → checkin_sessions.session_id
  • mood_vector — JSONB — NOT NULL
  • dominant_emotion — VARCHAR(20)
  • source — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

mood_emotions

  • emotion — VARCHAR(20) — Primary key — NOT NULL
  • mood_id — UUID — Foreign key → mood_daily.mood_id — NOT NULL
  • score — DOUBLE — NOT NULL

notification_settings

  • user_id — UUID — Foreign key → users.user_id
  • enabled — BOOLEAN — NOT NULL
  • alarm_time_local — TIME — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

purpose_options

  • purpose_id — SMALLSERIAL
  • code — VARCHAR(30) — NOT NULL
  • label — TEXT — NOT NULL

recommendation_items

  • rank — INT — Primary key — NOT NULL
  • rec_id — UUID — Foreign key → recommendations.rec_id — NOT NULL
  • item_id — VARCHAR(60) — Foreign key → items.item_id — NOT NULL
  • score — DOUBLE — NOT NULL
  • reason_code — VARCHAR(30) — NOT NULL
  • source — VARCHAR(255)

recommendations

  • rec_id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • date_local — DATE — NOT NULL
  • session_id — UUID — Foreign key → checkin_sessions.session_id
  • ab_variant — VARCHAR(2) — NOT NULL
  • model_version — VARCHAR(30) — NOT NULL
  • exploration — JSONB — NOT NULL
  • chosen_item_id — VARCHAR(60) — Foreign key → items.item_id
  • created_at — TIMESTAMPTZ — NOT NULL

segment_item_stats

  • age_group_snapshot — VARCHAR(10) — Primary key — NOT NULL
  • gender — VARCHAR(10) — Primary key — NOT NULL
  • window_days — INT — Primary key — NOT NULL
  • item_id — VARCHAR(60) — Foreign key → items.item_id — NOT NULL
  • impression_cnt — INT — NOT NULL
  • complete_cnt — INT — NOT NULL
  • avg_rating — DOUBLE
  • updated_at — TIMESTAMPTZ — NOT NULL

segment_mood_population

  • age_group_snapshot — VARCHAR(10) — Primary key — NOT NULL
  • gender — VARCHAR(10) — Primary key — NOT NULL
  • window_days — INT — Primary key — NOT NULL
  • stability_mean — DOUBLE — NOT NULL
  • vitality_mean — DOUBLE — NOT NULL
  • satisfaction_mean — DOUBLE — NOT NULL
  • sample_n — INT — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

user_category_preferences

  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • category_id — BIGINT — Foreign key → item_categories.category_id — NOT NULL
  • preference_weight — DOUBLE — NOT NULL
  • confidence_score — DOUBLE — NOT NULL
  • last_recommended_at — TIMESTAMPTZ
  • updated_at — TIMESTAMPTZ — NOT NULL

user_devices

  • device_id — UUID
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • fcm_token — TEXT — NOT NULL
  • platform — VARCHAR(20) — NOT NULL
  • device_label — VARCHAR(100)
  • last_seen_at — TIMESTAMPTZ — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • revoked_at — TIMESTAMPTZ

user_interests

  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • interest_id — SMALLINT — Foreign key → interest_options.interest_id — NOT NULL
  • rank — SMALLINT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

user_purposes

  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • purpose_id — SMALLINT — Foreign key → purpose_options.purpose_id — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

users

  • user_id — UUID
  • username — VARCHAR(20) — NOT NULL
  • password_hash — TEXT — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • name — VARCHAR(20) — NOT NULL
  • birth_date — DATE — NOT NULL
  • gender — VARCHAR(10) — NOT NULL
  • job — VARCHAR(30)
  • status — SMALLINT — NOT NULL
  • age_group_snapshot — VARCHAR(10) — NOT NULL — Denormalized
  • data_collection_agreed — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ