spec

Tables (18)

activity_reports

  • id — VARCHAR(64) — Primary key — NOT NULL
  • dept_id — VARCHAR(64) — Foreign key → dept_programs.id — NOT NULL
  • activity_date — DATE — NOT NULL
  • time_from — VARCHAR(8) — NOT NULL — HH:mm
  • time_to — VARCHAR(8) — NOT NULL
  • hours — DECIMAL(4, 1) — NOT NULL
  • attendees — TEXT
  • place — VARCHAR(255)
  • topic — VARCHAR(255)
  • content — TEXT
  • improvements — TEXT
  • status — ENUM('draft', 'submitted', 'recognized', 'rejected') — NOT NULL
  • attempt — INT — NOT NULL
  • saved_at — DATETIME — NOT NULL
  • submitted_at — DATETIME
  • rejection_reason — TEXT
  • reviewed_at — DATETIME
  • reviewed_by — VARCHAR(100)

artifact_audits

  • id — VARCHAR(128) — Primary key — NOT NULL — idempotency key
  • dept_id — VARCHAR(64) — Foreign key → dept_programs.id — NOT NULL
  • kind — ENUM('application', 'activity-report', 'poster', 'legacy-report') — NOT NULL
  • artifact_id — VARCHAR(64) — NOT NULL
  • attempt — INT — NOT NULL
  • transition — VARCHAR(32) — NOT NULL — drafted|submitted|staged|promoted|recognized|rejected|...
  • actor_name — VARCHAR(100) — NOT NULL
  • actor_role — ENUM('STUDENT', 'PROFESSOR', 'HEAD', 'STAFF') — NOT NULL
  • actor_student_id — VARCHAR(32) — NOT NULL
  • at_time — DATETIME — NOT NULL
  • reason — TEXT
  • file_snapshot_json — JSON
  • report_snapshot_json — JSON

board_comments

  • id — VARCHAR(64) — Primary key — NOT NULL
  • post_id — VARCHAR(64) — Foreign key → board_posts.id — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • author_id — VARCHAR(64) — NOT NULL

board_posts

  • id — VARCHAR(64) — Primary key — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • author_id — VARCHAR(64) — NOT NULL
  • views — INT — NOT NULL

deadlines

  • id — VARCHAR(64) — Primary key — NOT NULL
  • label — VARCHAR(255) — NOT NULL
  • due_date — DATE — NOT NULL

dept_programs

  • id — VARCHAR(64) — Primary key — NOT NULL
  • student_id — VARCHAR(32) — NOT NULL
  • student_name — VARCHAR(100) — NOT NULL
  • grade — VARCHAR(20) — NOT NULL
  • year — VARCHAR(10) — NOT NULL — 예: 2026
  • semester — VARCHAR(20) — NOT NULL — 예: 1학기
  • professor_name — VARCHAR(100) — NOT NULL — 담당교수 이름(게이팅 키)
  • title — VARCHAR(255) — NOT NULL
  • plan — TEXT
  • goal — TEXT
  • outcome — TEXT
  • study_plan — TEXT
  • project_plan — TEXT
  • schedule_text — TEXT
  • recognized_hours — INT — NOT NULL
  • status — VARCHAR(64) — NOT NULL — DeptStatus
  • application_phase — VARCHAR(32) — draft|submitted|professor-approved|approved|rejected-*
  • historical_final_approved — TINYINT(1) — NOT NULL
  • professor_comment — TEXT
  • admin_comment — TEXT
  • final_approval_date — DATE
  • draft_saved_at — DATETIME
  • last_update — DATETIME — NOT NULL
  • poster_file_id — VARCHAR(64) — Foreign key → files.id
  • poster_draft_file_id — VARCHAR(64) — Foreign key → files.id
  • report_file_id — VARCHAR(64) — Foreign key → files.id — 레거시 보고서
  • poster_reviewed — TINYINT(1) — NOT NULL
  • report_reviewed — TINYINT(1) — NOT NULL
  • poster_review_status — VARCHAR(32) — none|staged|submitted|recognized|rejected
  • poster_last_decision — VARCHAR(32) — none|rejected|recognized
  • poster_attempt — INT — NOT NULL
  • poster_rejection_reason — TEXT
  • schema_version — INT
  • migration_version — INT

dept_team_members

  • id — BIGINT — Primary key — NOT NULL
  • dept_id — VARCHAR(64) — Foreign key → dept_programs.id — NOT NULL
  • student_id — VARCHAR(32) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • member_role — VARCHAR(50)

email_notifications

  • id — VARCHAR(64) — Primary key — NOT NULL
  • to_student — VARCHAR(32) — NOT NULL — 수신 학번
  • to_name — VARCHAR(100) — NOT NULL
  • subject — VARCHAR(255) — NOT NULL
  • body — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • is_read — TINYINT(1) — NOT NULL
  • href — VARCHAR(512) — 더블클릭 이동 경로

files

  • id — VARCHAR(64) — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • size_bytes — BIGINT — NOT NULL
  • content_type — VARCHAR(128)
  • storage_key — VARCHAR(512) — 객체스토리지 키
  • uploaded_at — DATETIME — NOT NULL

history_entries

  • id — BIGINT — Primary key — NOT NULL
  • parent_type — ENUM('dept', 'toeic', 'volunteer') — NOT NULL
  • parent_id — VARCHAR(64) — NOT NULL
  • step — VARCHAR(128) — NOT NULL — 전이 결과 상태 또는 액션명
  • actor_name — VARCHAR(100) — NOT NULL
  • actor_role — ENUM('STUDENT', 'PROFESSOR', 'HEAD', 'STAFF') — NOT NULL
  • at_time — DATETIME — NOT NULL
  • reason — TEXT

messages

  • id — VARCHAR(64) — Primary key — NOT NULL
  • from_student — VARCHAR(32) — NOT NULL
  • from_name — VARCHAR(100) — NOT NULL
  • to_professor — VARCHAR(100) — NOT NULL
  • body — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • is_read — TINYINT(1) — NOT NULL

notices

  • id — VARCHAR(64) — Primary key — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • body — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL

records_meta

  • id — TINYINT — Primary key — NOT NULL
  • schema_version — INT — NOT NULL
  • revision — BIGINT — NOT NULL
  • last_mutation_id — VARCHAR(64)

signature_images

  • professor_name — VARCHAR(100) — Primary key — NOT NULL
  • data_url — MEDIUMTEXT — NOT NULL — base64 data URL (목업)
  • file_name — VARCHAR(255)
  • uploaded_at — DATETIME

student_overrides

  • student_id — VARCHAR(32) — Primary key — NOT NULL
  • grade — VARCHAR(20) — NOT NULL
  • status — ENUM('재학','유급','휴학') — NOT NULL
  • attendance_memo — TEXT

toeic_scores

  • id — VARCHAR(64) — Primary key — NOT NULL
  • student_id — VARCHAR(32) — NOT NULL
  • student_name — VARCHAR(100) — NOT NULL
  • grade — VARCHAR(20) — NOT NULL
  • year — VARCHAR(10) — NOT NULL
  • semester — VARCHAR(20) — NOT NULL
  • professor_name — VARCHAR(100) — NOT NULL
  • birth_date — VARCHAR(8) — YYYYMMDD 8자리
  • test_date — DATE — NOT NULL
  • test_number — VARCHAR(32) — 수험번호 앞 6자리 등
  • total_score — INT — NOT NULL
  • issue_number — VARCHAR(64)
  • valid_until — DATE — 응시일+2년
  • status — VARCHAR(32) — NOT NULL — SimpleStatus: 접수|1차 승인|최종 승인|검토중|승인|반려
  • admin_comment — TEXT
  • final_approval_date — DATE
  • last_update — DATETIME — NOT NULL

users

  • id — VARCHAR(64) — Primary key — NOT NULL — 로그인 ID
  • password_hash — VARCHAR(255) — NOT NULL — 목업은 평문 pw, 실서비스는 hash
  • role — ENUM('STUDENT', 'PROFESSOR', 'HEAD', 'STAFF') — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • student_id — VARCHAR(32) — 학생만
  • grade — VARCHAR(20) — 예: 3학년
  • created_at — DATETIME — NOT NULL

volunteers

  • id — VARCHAR(64) — Primary key — NOT NULL
  • student_id — VARCHAR(32) — NOT NULL
  • student_name — VARCHAR(100) — NOT NULL
  • grade — VARCHAR(20) — NOT NULL
  • year — VARCHAR(10) — NOT NULL
  • semester — VARCHAR(20) — NOT NULL
  • professor_name — VARCHAR(100) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • recognized_hours — DECIMAL(6, 1) — NOT NULL
  • accumulated_hours — DECIMAL(6, 1) — NOT NULL
  • cert_file_id — VARCHAR(64) — Foreign key → files.id
  • status — VARCHAR(32) — NOT NULL — SimpleStatus
  • admin_comment — TEXT
  • final_approval_date — DATE
  • last_update — DATETIME — NOT NULL