코딩 학습 기록 서비스2

테이블 (6)

attachment (첨부/S3 메타)

  • attachment_id — BIGINT — 기본 키 — NOT NULL
  • problem_id — BIGINT — 외래 키 → problem.problem_id — NOT NULL
  • record_id — BIGINT — 외래 키 → study_record.record_id — NOT NULL
  • s3_key — VARCHAR(1024) — NOT NULL
  • mime — VARCHAR(128)
  • size_bytes — BIGINT
  • created_at — TIMESTAMP — NOT NULL
  • attachment_type

monthly_stat (월 집계)

  • month_date — DATE — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • solved_cnt — INT — NOT NULL
  • unsolved_cnt — INT — NOT NULL
  • review_needed_cnt — INT — NOT NULL
  • per_tag_json — JSON
  • updated_at — TIMESTAMP — NOT NULL

notification (알림 멱등/중복 방지)

  • notification_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • problem_id — BIGINT — 외래 키 → problem.problem_id — NOT NULL
  • channel — VARCHAR(16) — NOT NULL
  • due_at — DATETIME — NOT NULL
  • delivered_at — DATETIME
  • payload_json — JSON
  • created_at — TIMESTAMP — NOT NULL

problem (문제)

  • problem_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • title — VARCHAR(300) — NOT NULL
  • source_url — VARCHAR(1000)
  • difficulty — VARCHAR(16) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • memo — TEXT
  • bookmarked — TINYINT(1) — NOT NULL
  • next_review_at — DATETIME
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • tag — VARCHAR(64)

study_record (학습 기록 히스토리)

  • record_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • problem_id — BIGINT — 외래 키 → problem.problem_id — NOT NULL
  • title_opt — VARCHAR(300)
  • content_markdown — MEDIUMTEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

user (회원)

  • user_id — BIGINT — 기본 키 — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL