Capston2

Tables (13)

achievements

  • achieve_id — SERIAL
  • user_id — INT — Foreign key → users.user_id
  • medal_id — INT — Foreign key → medals.medal_id
  • earned_at — TIMESTAMP
  • is_read — BOOLEAN

activities

  • activity_id — SERIAL
  • act_content — VARCHAR(255)
  • act_category — VARCHAR(20)
  • is_active — BOOLEAN
  • is_outdoor — BOOLEAN
  • is_social — BOOLEAN
  • is_enabled — BOOLEAN
  • source — VARCHAR(20)

attendance

  • att_id — SERIAL
  • user_id — INT — Foreign key → users.user_id
  • att_date — DATE
  • created_at — TIMESTAMP

diaries

  • diary_id — SERIAL
  • user_id — INT — Foreign key → users.user_id
  • content — TEXT
  • keywords — JSON
  • input_type — VARCHAR(10)
  • created_at — TIMESTAMP
  • image_url — VARCHAR(512)

diary_feedbacks

  • feedback_id — SERIAL
  • diary_id — INT — Foreign key → diaries.diary_id
  • ai_message_rating — INT
  • mbi_category_rating — INT
  • is_sent_to_ai — BOOLEAN
  • created_at — TIMESTAMP

email_verifications

  • email — VARCHAR(100)
  • code — VARCHAR(6)
  • is_verified — BOOLEAN
  • created_at — TIMESTAMP

emotion_analysis

  • analysis_id — SERIAL
  • diary_id — INT — Foreign key → diaries.diary_id
  • emotion_probs — JSON
  • primary_emotion — VARCHAR(20)
  • primary_score — FLOAT
  • mbi_category — VARCHAR(30)
  • ai_message — TEXT
  • created_at — TIMESTAMP

medals

  • medal_id — SERIAL
  • medal_code — VARCHAR(50)
  • medal_name — VARCHAR(50)
  • medal_explain — VARCHAR(255)

notification_logs

  • log_id — SERIAL
  • user_id — INT — Foreign key → users.user_id
  • alert_type — VARCHAR(50)
  • message — TEXT
  • sent_at — TIMESTAMP
  • msg_id — INT — Foreign key → push_messages.msg_id

push_messages

  • msg_id — SERIAL
  • msg_content — VARCHAR(255)
  • category — VARCHAR(50)
  • created_at — TIMESTAMP

solution_logs

  • log_id — SERIAL
  • diary_id — INT — Foreign key → diaries.diary_id
  • activity_id — INT — Foreign key → activities.activity_id
  • is_selected — BOOLEAN
  • is_completed — BOOLEAN
  • ai_message — TEXT
  • created_at — TIMESTAMP

user_preferences

  • pref_id — SERIAL
  • user_id — INT — Foreign key → users.user_id
  • is_active — BOOLEAN
  • is_outdoor — BOOLEAN
  • is_social — BOOLEAN
  • preferred_tags — JSON

users

  • user_id — SERIAL
  • email — VARCHAR(100)
  • password — VARCHAR(255)
  • nickname — VARCHAR(20)
  • provider — VARCHAR(20)
  • provider_id — VARCHAR(255)
  • created_at — TIMESTAMP
  • last_att_date — DATE
  • current_streak — INT
  • fcm_token — VARCHAR(512)
  • is_push_enabled — BOOLEAN
  • persona — INT
  • is_daily_alarm_on — BOOLEAN
  • daily_alarm_time — TIME
  • daily_alarm_days — JSON