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