student

Tables (47)

accounting_categories

  • category_id — BIGINT — Primary key — NOT NULL — 회계 분류 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • parent_category_id — BIGINT — Foreign key → accounting_categories.category_id
  • category_code — VARCHAR(50) — NOT NULL
  • category_name — VARCHAR(150) — NOT NULL
  • category_type — ENUM('INCOME', 'EXPENSE', 'BOTH') — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • display_order — INT — NOT NULL

app_users

  • user_id — BIGINT — Primary key — NOT NULL — 사용자 ID
  • google_account_email — VARCHAR(255) — NOT NULL — Google Workspace 로그인 이메일
  • name — VARCHAR(100) — NOT NULL — 이름
  • phone — VARCHAR(30) — 연락처
  • account_status — ENUM('INVITED', 'ACTIVE', 'SUSPENDED', 'WITHDRAWN') — NOT NULL
  • last_login_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

approval_requests

  • approval_request_id — BIGINT — Primary key — NOT NULL — 승인 요청 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • request_type — ENUM('LEDGER', 'STUDENT_FEE', 'EVENT', 'REFUND', 'SETTLEMENT', 'CLOSING', 'OTHER') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • request_reason — TEXT
  • requested_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • current_step_no — INT — NOT NULL
  • approval_status — ENUM('DRAFT', 'REQUESTED', 'IN_REVIEW', 'APPROVED', 'REJECTED', 'CANCELLED') — NOT NULL
  • requested_at — DATETIME
  • completed_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

approval_steps

  • approval_step_id — BIGINT — Primary key — NOT NULL — 승인 단계 ID
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id — NOT NULL
  • step_no — INT — NOT NULL
  • required_role_id — BIGINT — Foreign key → roles.role_id — 승인에 필요한 역할
  • approver_user_id — BIGINT — Foreign key → app_users.user_id — 실제 승인자
  • step_name — VARCHAR(100) — NOT NULL — 사무국장 검토, 회장단 승인 등
  • step_status — ENUM('WAITING', 'PENDING', 'APPROVED', 'REJECTED', 'SKIPPED', 'CANCELLED') — NOT NULL
  • comment — TEXT
  • acted_at — DATETIME
  • created_at — DATETIME — NOT NULL

audit_logs

  • audit_log_id — BIGINT — Primary key — NOT NULL — 감사 로그 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id
  • actor_user_id — BIGINT — Foreign key → app_users.user_id
  • module_code — VARCHAR(50) — NOT NULL
  • action_type — ENUM('CREATE', 'READ', 'UPDATE', 'DELETE', 'APPROVE', 'REJECT', 'CANCEL', 'EXPORT', 'IMPORT', 'LOGIN', 'OTHER') — NOT NULL
  • target_table — VARCHAR(100)
  • target_id — BIGINT
  • before_data_json — JSON
  • after_data_json — JSON
  • ip_address — VARCHAR(45)
  • user_agent — VARCHAR(500)
  • occurred_at — DATETIME — NOT NULL

bank_accounts

  • bank_account_id — BIGINT — Primary key — NOT NULL — 학생회 계좌 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • bank_name — VARCHAR(100) — NOT NULL
  • account_number_masked — VARCHAR(100) — NOT NULL — 마스킹 계좌번호
  • account_holder — VARCHAR(100) — NOT NULL
  • account_purpose — VARCHAR(200)
  • opening_balance — DECIMAL(15, 2) — NOT NULL
  • is_primary — TINYINT(1) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

bank_imports

  • bank_import_id — BIGINT — Primary key — NOT NULL — 은행 거래내역 업로드 ID
  • bank_account_id — BIGINT — Foreign key → bank_accounts.bank_account_id — NOT NULL
  • source_file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • period_start_date — DATE
  • period_end_date — DATE
  • imported_row_count — INT — NOT NULL
  • duplicate_row_count — INT — NOT NULL
  • import_status — ENUM('UPLOADED', 'PARSING', 'COMPLETED', 'FAILED') — NOT NULL
  • imported_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • imported_at — DATETIME — NOT NULL
  • error_message — TEXT

bank_ledger_matches

  • bank_ledger_match_id — BIGINT — Primary key — NOT NULL — 은행 거래와 장부 대조 ID
  • bank_transaction_id — BIGINT — Foreign key → bank_transactions.bank_transaction_id — NOT NULL
  • ledger_entry_id — BIGINT — Foreign key → ledger_entries.ledger_entry_id — NOT NULL
  • matched_amount — DECIMAL(15, 2) — NOT NULL
  • match_method — ENUM('AUTO_EXACT', 'AUTO_CANDIDATE', 'MANUAL') — NOT NULL
  • confidence_score — DECIMAL(5, 2)
  • match_status — ENUM('SUGGESTED', 'CONFIRMED', 'REJECTED', 'CANCELLED') — NOT NULL
  • matched_by — BIGINT — Foreign key → app_users.user_id
  • matched_at — DATETIME
  • note — VARCHAR(500)

bank_transactions

  • bank_transaction_id — BIGINT — Primary key — NOT NULL — 은행 거래 ID
  • bank_account_id — BIGINT — Foreign key → bank_accounts.bank_account_id — NOT NULL
  • bank_import_id — BIGINT — Foreign key → bank_imports.bank_import_id
  • external_transaction_key — VARCHAR(255) — 은행 파일 내 거래 식별값
  • transaction_at — DATETIME — NOT NULL
  • transaction_type — ENUM('DEPOSIT', 'WITHDRAWAL') — NOT NULL
  • amount — DECIMAL(15, 2) — NOT NULL
  • balance_after — DECIMAL(15, 2)
  • counterparty_name — VARCHAR(200) — 거래처 또는 입금자명
  • transaction_memo — VARCHAR(500)
  • raw_data_json — JSON
  • reconciliation_status — ENUM('UNMATCHED', 'PARTIAL', 'MATCHED', 'IGNORED') — NOT NULL
  • created_at — DATETIME — NOT NULL

budget_items

  • budget_item_id — BIGINT — Primary key — NOT NULL — 예산 항목 ID
  • budget_id — BIGINT — Foreign key → budgets.budget_id — NOT NULL
  • parent_budget_item_id — BIGINT — Foreign key → budget_items.budget_item_id
  • category_id — BIGINT — Foreign key → accounting_categories.category_id
  • department_id — BIGINT — Foreign key → departments.department_id
  • item_name — VARCHAR(200) — NOT NULL
  • planned_amount — DECIMAL(15, 2) — NOT NULL
  • note — VARCHAR(500)
  • display_order — INT — NOT NULL

budgets

  • budget_id — BIGINT — Primary key — NOT NULL — 예산 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • budget_name — VARCHAR(200) — NOT NULL
  • fiscal_year — YEAR — NOT NULL
  • budget_type — ENUM('ANNUAL', 'SEMESTER', 'EVENT', 'DEPARTMENT', 'OTHER') — NOT NULL
  • total_amount — DECIMAL(15, 2) — NOT NULL
  • status — ENUM('DRAFT', 'APPROVED', 'LOCKED', 'CLOSED') — NOT NULL
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

closing_periods

  • closing_period_id — BIGINT — Primary key — NOT NULL — 결산 기간 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • period_type — ENUM('MONTHLY', 'SEMESTER', 'ANNUAL', 'CUSTOM') — NOT NULL
  • period_name — VARCHAR(150) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • closing_status — ENUM('OPEN', 'CALCULATING', 'REVIEWING', 'APPROVED', 'LOCKED', 'REOPENED') — NOT NULL
  • locked_at — DATETIME
  • locked_by — BIGINT — Foreign key → app_users.user_id
  • reopened_at — DATETIME
  • reopen_reason — VARCHAR(500)

closing_report_versions

  • closing_report_version_id — BIGINT — Primary key — NOT NULL — 결산 보고서 버전 ID
  • closing_report_id — BIGINT — Foreign key → closing_reports.closing_report_id — NOT NULL
  • version_no — INT — NOT NULL
  • report_file_id — BIGINT — Foreign key → files.file_id
  • report_data_json — JSON — 생성 시점의 결산 데이터 스냅샷
  • change_summary — VARCHAR(500)
  • generated_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • generated_at — DATETIME — NOT NULL
  • is_final — TINYINT(1) — NOT NULL

closing_reports

  • closing_report_id — BIGINT — Primary key — NOT NULL — 결산 보고서 ID
  • closing_period_id — BIGINT — Foreign key → closing_periods.closing_period_id — NOT NULL
  • report_title — VARCHAR(250) — NOT NULL
  • total_income — DECIMAL(15, 2) — NOT NULL
  • total_expense — DECIMAL(15, 2) — NOT NULL
  • opening_balance — DECIMAL(15, 2) — NOT NULL
  • closing_balance — DECIMAL(15, 2) — NOT NULL
  • unmatched_bank_transaction_count — INT — NOT NULL
  • missing_evidence_count — INT — NOT NULL
  • pending_approval_count — INT — NOT NULL
  • report_status — ENUM('DRAFT', 'GENERATED', 'REVIEW_REQUESTED', 'APPROVED', 'PUBLISHED', 'CANCELLED') — NOT NULL
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • current_version_no — INT — NOT NULL
  • generated_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • generated_at — DATETIME
  • approved_at — DATETIME
  • created_at — DATETIME — NOT NULL

council_memberships

  • membership_id — BIGINT — Primary key — NOT NULL — 학생회 소속 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • user_id — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • department_id — BIGINT — Foreign key → departments.department_id
  • position_name — VARCHAR(100) — NOT NULL — 직책명
  • joined_at — DATE
  • left_at — DATE
  • membership_status — ENUM('ACTIVE', 'LEAVE', 'ENDED') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

council_terms

  • term_id — BIGINT — Primary key — NOT NULL — 학생회 기수 ID
  • term_name — VARCHAR(100) — NOT NULL — 기수명
  • university_name — VARCHAR(150) — NOT NULL — 대학교명
  • college_name — VARCHAR(150) — 단과대명
  • council_name — VARCHAR(150) — NOT NULL — 학생회명
  • start_date — DATE — NOT NULL — 임기 시작일
  • end_date — DATE — NOT NULL — 임기 종료일
  • status — ENUM('PLANNED', 'ACTIVE', 'CLOSED') — NOT NULL — 기수 상태
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

counterparties

  • counterparty_id — BIGINT — Primary key — NOT NULL — 거래처 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • counterparty_name — VARCHAR(200) — NOT NULL
  • business_number — VARCHAR(30)
  • contact_name — VARCHAR(100)
  • phone — VARCHAR(30)
  • bank_name — VARCHAR(100)
  • bank_account_masked — VARCHAR(100)
  • account_holder — VARCHAR(100)
  • note — VARCHAR(500)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

departments

  • department_id — BIGINT — Primary key — NOT NULL — 학생회 부서 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL — 기수 ID
  • department_name — VARCHAR(100) — NOT NULL — 부서명
  • department_type — ENUM('PRESIDENT', 'SECRETARIAT', 'FINANCE', 'EVENT', 'WELFARE', 'PROMOTION', 'OTHER') — NOT NULL
  • description — VARCHAR(500)
  • display_order — INT — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

event_applications

  • event_application_id — BIGINT — Primary key — NOT NULL — 행사 신청 ID
  • event_id — BIGINT — Foreign key → events.event_id — NOT NULL
  • student_id — BIGINT — Foreign key → student_profiles.student_id
  • applicant_name — VARCHAR(100) — NOT NULL
  • student_number — VARCHAR(30) — NOT NULL
  • major_name — VARCHAR(150) — NOT NULL
  • phone — VARCHAR(30)
  • email — VARCHAR(255)
  • payer_name — VARCHAR(100)
  • application_status — ENUM('SUBMITTED', 'WAITLIST', 'APPROVED', 'REJECTED', 'CANCELLED', 'NO_SHOW', 'COMPLETED') — NOT NULL
  • payment_status — ENUM('NOT_REQUIRED', 'UNPAID', 'CHECKING', 'PAID', 'PARTIAL', 'REFUNDED') — NOT NULL
  • submitted_at — DATETIME — NOT NULL
  • cancelled_at — DATETIME
  • cancel_reason — VARCHAR(500)
  • source_resource_id — BIGINT — Foreign key → integration_resources.integration_resource_id
  • source_row_key — VARCHAR(255)

event_attendance

  • attendance_id — BIGINT — Primary key — NOT NULL — 참석 ID
  • event_application_id — BIGINT — Foreign key → event_applications.event_application_id — NOT NULL
  • attendance_status — ENUM('EXPECTED', 'ATTENDED', 'ABSENT', 'PARTIAL', 'CANCELLED') — NOT NULL
  • checked_in_at — DATETIME
  • checked_out_at — DATETIME
  • confirmed_by — BIGINT — Foreign key → app_users.user_id
  • confirmed_at — DATETIME
  • note — VARCHAR(500)

event_fee_eligibility_checks

  • eligibility_check_id — BIGINT — Primary key — NOT NULL — 학생회비 자격 확인 ID
  • event_application_id — BIGINT — Foreign key → event_applications.event_application_id — NOT NULL
  • student_fee_record_id — BIGINT — Foreign key → student_fee_records.student_fee_record_id
  • fee_period_id — BIGINT — Foreign key → fee_periods.fee_period_id
  • eligibility_result — ENUM('ELIGIBLE', 'NOT_ELIGIBLE', 'NOT_FOUND', 'MANUAL_REVIEW', 'NOT_REQUIRED') — NOT NULL
  • checked_student_number — VARCHAR(30) — NOT NULL
  • checked_status — VARCHAR(50)
  • check_method — ENUM('AUTO', 'MANUAL') — NOT NULL
  • checked_by — BIGINT — Foreign key → app_users.user_id
  • checked_at — DATETIME — NOT NULL
  • note — VARCHAR(500)

event_payment_matches

  • event_payment_match_id — BIGINT — Primary key — NOT NULL — 행사 참가비 대조 ID
  • event_application_id — BIGINT — Foreign key → event_applications.event_application_id — NOT NULL
  • event_payment_id — BIGINT — Foreign key → event_payments.event_payment_id — NOT NULL
  • matched_amount — DECIMAL(15, 2) — NOT NULL
  • match_method — ENUM('AUTO_EXACT', 'AUTO_CANDIDATE', 'MANUAL', 'PROXY_PAYER') — NOT NULL
  • confidence_score — DECIMAL(5, 2)
  • match_status — ENUM('SUGGESTED', 'CONFIRMED', 'REJECTED', 'CANCELLED') — NOT NULL
  • matched_by — BIGINT — Foreign key → app_users.user_id
  • matched_at — DATETIME
  • note — VARCHAR(500)

event_payments

  • event_payment_id — BIGINT — Primary key — NOT NULL — 행사 참가비 입금 ID
  • event_id — BIGINT — Foreign key → events.event_id — NOT NULL
  • bank_transaction_id — BIGINT — Foreign key → bank_transactions.bank_transaction_id — NOT NULL
  • payer_name — VARCHAR(200)
  • payment_amount — DECIMAL(15, 2) — NOT NULL
  • payment_at — DATETIME — NOT NULL
  • payment_status — ENUM('UNMATCHED', 'MATCHED', 'PARTIAL', 'DUPLICATE', 'EXCLUDED', 'REFUNDED') — NOT NULL
  • created_at — DATETIME — NOT NULL

event_settlement_items

  • event_settlement_item_id — BIGINT — Primary key — NOT NULL — 행사 결산 집계 항목 ID
  • event_settlement_id — BIGINT — Foreign key → event_settlements.event_settlement_id — NOT NULL
  • category_id — BIGINT — Foreign key → accounting_categories.category_id
  • item_type — ENUM('INCOME', 'EXPENSE', 'ADJUSTMENT') — NOT NULL
  • item_name — VARCHAR(200) — NOT NULL
  • amount — DECIMAL(15, 2) — NOT NULL
  • source_count — INT — NOT NULL
  • display_order — INT — NOT NULL

event_settlement_participants

  • settlement_participant_id — BIGINT — Primary key — NOT NULL — 결산 참여자 스냅샷 ID
  • event_settlement_id — BIGINT — Foreign key → event_settlements.event_settlement_id — NOT NULL
  • event_application_id — BIGINT — Foreign key → event_applications.event_application_id — NOT NULL
  • attendance_status — VARCHAR(30) — NOT NULL
  • final_participant — TINYINT(1) — NOT NULL
  • refund_eligible — TINYINT(1) — NOT NULL
  • allocated_refund_amount — DECIMAL(15, 2) — NOT NULL
  • bank_name — VARCHAR(100)
  • bank_account_number — VARCHAR(100) — 운영 시 암호화 또는 마스킹 필요
  • account_holder — VARCHAR(100)
  • locked_at — DATETIME

event_settlements

  • event_settlement_id — BIGINT — Primary key — NOT NULL — 행사 결산 ID
  • event_id — BIGINT — Foreign key → events.event_id — NOT NULL
  • total_income — DECIMAL(15, 2) — NOT NULL
  • total_expense — DECIMAL(15, 2) — NOT NULL
  • account_balance — DECIMAL(15, 2) — NOT NULL — 수입-지출
  • refundable_balance — DECIMAL(15, 2) — NOT NULL — 환불 가능한 잔액
  • final_participant_count — INT — NOT NULL
  • per_person_refund_amount — DECIMAL(15, 2) — NOT NULL
  • rounding_remainder — DECIMAL(15, 2) — NOT NULL
  • settlement_status — ENUM('DRAFT', 'CALCULATED', 'REVIEW_REQUESTED', 'APPROVED', 'PAYING', 'RECONCILED', 'CLOSED', 'REOPENED') — NOT NULL
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • participants_locked_at — DATETIME
  • calculated_at — DATETIME
  • closed_at — DATETIME
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

events

  • event_id — BIGINT — Primary key — NOT NULL — 행사 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • department_id — BIGINT — Foreign key → departments.department_id
  • event_name — VARCHAR(200) — NOT NULL
  • event_type — ENUM('MT', 'ORIENTATION', 'FESTIVAL', 'WELFARE', 'RENTAL', 'OTHER') — NOT NULL
  • description — TEXT
  • venue — VARCHAR(300)
  • application_start_at — DATETIME
  • application_end_at — DATETIME
  • event_start_at — DATETIME — NOT NULL
  • event_end_at — DATETIME
  • capacity — INT
  • participation_fee — DECIMAL(15, 2) — NOT NULL
  • student_fee_policy — ENUM('NOT_REQUIRED', 'PAID_ONLY', 'DISCOUNT_FOR_PAID', 'MANUAL_CHECK') — NOT NULL
  • bank_account_id — BIGINT — Foreign key → bank_accounts.bank_account_id
  • budget_id — BIGINT — Foreign key → budgets.budget_id
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • event_status — ENUM('DRAFT', 'OPEN', 'CLOSED', 'IN_PROGRESS', 'SETTLING', 'COMPLETED', 'CANCELLED') — NOT NULL
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

fee_applications

  • fee_application_id — BIGINT — Primary key — NOT NULL — 학생회비 신청 ID
  • fee_period_id — BIGINT — Foreign key → fee_periods.fee_period_id — NOT NULL
  • student_id — BIGINT — Foreign key → student_profiles.student_id
  • applicant_name — VARCHAR(100) — NOT NULL
  • student_number — VARCHAR(30) — NOT NULL
  • major_name — VARCHAR(150) — NOT NULL
  • phone — VARCHAR(30)
  • payer_name — VARCHAR(100) — 대리 납부자 포함 실제 입금자명
  • application_status — ENUM('SUBMITTED', 'CHECKING', 'APPROVED', 'REJECTED', 'CANCELLED', 'REFUNDED') — NOT NULL
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • submitted_at — DATETIME — NOT NULL
  • reviewed_by — BIGINT — Foreign key → app_users.user_id
  • reviewed_at — DATETIME
  • rejection_reason — VARCHAR(500)
  • source_resource_id — BIGINT — Foreign key → integration_resources.integration_resource_id
  • source_row_key — VARCHAR(255)

fee_payment_matches

  • fee_payment_match_id — BIGINT — Primary key — NOT NULL — 학생회비 대조 ID
  • fee_application_id — BIGINT — Foreign key → fee_applications.fee_application_id — NOT NULL
  • fee_payment_id — BIGINT — Foreign key → fee_payments.fee_payment_id — NOT NULL
  • matched_amount — DECIMAL(15, 2) — NOT NULL
  • match_method — ENUM('AUTO_EXACT', 'AUTO_CANDIDATE', 'MANUAL', 'PROXY_PAYER') — NOT NULL
  • confidence_score — DECIMAL(5, 2) — 자동 대조 신뢰도 0~100
  • match_status — ENUM('SUGGESTED', 'CONFIRMED', 'REJECTED', 'CANCELLED') — NOT NULL
  • matched_by — BIGINT — Foreign key → app_users.user_id
  • matched_at — DATETIME
  • note — VARCHAR(500)

fee_payments

  • fee_payment_id — BIGINT — Primary key — NOT NULL — 학생회비 실제 입금 ID
  • fee_period_id — BIGINT — Foreign key → fee_periods.fee_period_id — NOT NULL
  • bank_transaction_id — BIGINT — Foreign key → bank_transactions.bank_transaction_id — NOT NULL
  • payer_name — VARCHAR(200)
  • payment_amount — DECIMAL(15, 2) — NOT NULL
  • payment_at — DATETIME — NOT NULL
  • payment_status — ENUM('UNMATCHED', 'MATCHED', 'PARTIAL', 'DUPLICATE', 'EXCLUDED', 'REFUNDED') — NOT NULL
  • created_at — DATETIME — NOT NULL

fee_periods

  • fee_period_id — BIGINT — Primary key — NOT NULL — 학생회비 납부 기간 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • period_name — VARCHAR(150) — NOT NULL — 2026-1학기 등
  • semester_code — VARCHAR(20) — NOT NULL
  • fee_amount — DECIMAL(15, 2) — NOT NULL
  • application_start_at — DATETIME — NOT NULL
  • application_end_at — DATETIME — NOT NULL
  • payment_due_at — DATETIME
  • bank_account_id — BIGINT — Foreign key → bank_accounts.bank_account_id — NOT NULL
  • status — ENUM('PLANNED', 'OPEN', 'CLOSED', 'ARCHIVED') — NOT NULL
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL

fee_status_histories

  • fee_status_history_id — BIGINT — Primary key — NOT NULL — 학생회비 상태 변경 이력 ID
  • student_fee_record_id — BIGINT — Foreign key → student_fee_records.student_fee_record_id — NOT NULL
  • previous_status — VARCHAR(50)
  • new_status — VARCHAR(50) — NOT NULL
  • change_reason — VARCHAR(500)
  • changed_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • changed_at — DATETIME — NOT NULL

files

  • file_id — BIGINT — Primary key — NOT NULL — 파일 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • file_category — ENUM('RECEIPT', 'BANK_STATEMENT', 'TRANSFER_RESULT', 'FORM_RESPONSE', 'REPORT', 'CONTRACT', 'OTHER') — NOT NULL
  • original_file_name — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(100)
  • file_size_bytes — BIGINT
  • google_drive_file_id — VARCHAR(255)
  • google_drive_url — VARCHAR(1000)
  • checksum — VARCHAR(128)
  • uploaded_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • uploaded_at — DATETIME — NOT NULL
  • is_deleted — TINYINT(1) — NOT NULL

integration_resources

  • integration_resource_id — BIGINT — Primary key — NOT NULL — 연동 리소스 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • resource_type — ENUM('GOOGLE_FORM', 'GOOGLE_SHEET', 'GOOGLE_DRIVE_FOLDER', 'APPS_SCRIPT', 'OTHER') — NOT NULL
  • module_code — VARCHAR(50) — NOT NULL — 학생회비, 행사, 회계 등
  • resource_name — VARCHAR(200) — NOT NULL
  • external_resource_id — VARCHAR(255) — NOT NULL
  • resource_url — VARCHAR(1000)
  • config_json — JSON
  • is_active — TINYINT(1) — NOT NULL
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

ledger_attachments

  • ledger_attachment_id — BIGINT — Primary key — NOT NULL — 장부 증빙 연결 ID
  • ledger_entry_id — BIGINT — Foreign key → ledger_entries.ledger_entry_id — NOT NULL
  • file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • attachment_type — ENUM('RECEIPT', 'TRANSFER_CONFIRMATION', 'INVOICE', 'CONTRACT', 'OTHER') — NOT NULL
  • is_primary — TINYINT(1) — NOT NULL
  • verified_by — BIGINT — Foreign key → app_users.user_id
  • verified_at — DATETIME
  • verification_status — ENUM('UPLOADED', 'VERIFIED', 'REJECTED') — NOT NULL
  • rejection_reason — VARCHAR(500)

ledger_entries

  • ledger_entry_id — BIGINT — Primary key — NOT NULL — 회계 장부 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • bank_account_id — BIGINT — Foreign key → bank_accounts.bank_account_id
  • event_id — BIGINT — Foreign key → events.event_id
  • fee_period_id — BIGINT — Foreign key → fee_periods.fee_period_id
  • category_id — BIGINT — Foreign key → accounting_categories.category_id — NOT NULL
  • budget_item_id — BIGINT — Foreign key → budget_items.budget_item_id
  • counterparty_id — BIGINT — Foreign key → counterparties.counterparty_id
  • entry_date — DATE — NOT NULL
  • entry_type — ENUM('INCOME', 'EXPENSE', 'TRANSFER', 'ADJUSTMENT') — NOT NULL
  • amount — DECIMAL(15, 2) — NOT NULL
  • counterparty_text — VARCHAR(200) — 거래처 표시용 스냅샷
  • description — VARCHAR(500) — NOT NULL
  • evidence_status — ENUM('NOT_REQUIRED', 'MISSING', 'UPLOADED', 'VERIFIED', 'REJECTED') — NOT NULL
  • entry_status — ENUM('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED', 'CANCELLED', 'LOCKED') — NOT NULL
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • reviewed_by — BIGINT — Foreign key → app_users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

membership_roles

  • membership_id — BIGINT — Foreign key → council_memberships.membership_id — NOT NULL
  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • assigned_at — DATETIME — NOT NULL
  • assigned_by — BIGINT — Foreign key → app_users.user_id

notifications

  • notification_id — BIGINT — Primary key — NOT NULL — 알림 ID
  • user_id — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • notification_type — ENUM('APPROVAL', 'PAYMENT', 'MATCHING', 'REFUND', 'SETTLEMENT', 'SYSTEM') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • message — VARCHAR(1000) — NOT NULL
  • target_type — VARCHAR(50)
  • target_id — BIGINT
  • is_read — TINYINT(1) — NOT NULL
  • read_at — DATETIME
  • created_at — DATETIME — NOT NULL

payout_batch_items

  • payout_batch_item_id — BIGINT — Primary key — NOT NULL — 단체 송금 대상 ID
  • payout_batch_id — BIGINT — Foreign key → payout_batches.payout_batch_id — NOT NULL
  • refund_request_id — BIGINT — Foreign key → refund_requests.refund_request_id — NOT NULL
  • sequence_no — INT — NOT NULL
  • recipient_name — VARCHAR(100) — NOT NULL
  • bank_name — VARCHAR(100) — NOT NULL
  • bank_account_number — VARCHAR(100) — NOT NULL
  • account_holder — VARCHAR(100) — NOT NULL
  • payout_amount — DECIMAL(15, 2) — NOT NULL
  • transfer_completed — TINYINT(1) — NOT NULL — 화면 송금완료 체크박스
  • transfer_status — ENUM('READY', 'PROCESSING', 'SUCCESS', 'FAILED', 'RETRY') — NOT NULL
  • transfer_reference — VARCHAR(255)
  • bank_transaction_id — BIGINT — Foreign key → bank_transactions.bank_transaction_id
  • retry_count — INT — NOT NULL
  • failure_reason — VARCHAR(500)
  • transferred_by — BIGINT — Foreign key → app_users.user_id
  • transferred_at — DATETIME

payout_batches

  • payout_batch_id — BIGINT — Primary key — NOT NULL — 단체 송금 배치 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • bank_account_id — BIGINT — Foreign key → bank_accounts.bank_account_id — NOT NULL
  • batch_name — VARCHAR(200) — NOT NULL
  • payout_type — ENUM('STUDENT_FEE_REFUND', 'EVENT_FULL_REFUND', 'EVENT_BALANCE_REFUND', 'MIXED') — NOT NULL
  • max_items_per_file — INT — NOT NULL — 은행 일괄송금 파일 최대 건수
  • item_count — INT — NOT NULL
  • total_amount — DECIMAL(15, 2) — NOT NULL
  • batch_status — ENUM('DRAFT', 'APPROVED', 'EXPORTED', 'TRANSFERRING', 'COMPLETED', 'PARTIAL_FAILED', 'FAILED', 'CANCELLED') — NOT NULL
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • export_file_id — BIGINT — Foreign key → files.file_id
  • bank_result_file_id — BIGINT — Foreign key → files.file_id
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • exported_at — DATETIME
  • completed_at — DATETIME

permissions

  • permission_id — BIGINT — Primary key — NOT NULL — 권한 ID
  • permission_code — VARCHAR(100) — NOT NULL — 권한 코드
  • permission_name — VARCHAR(150) — NOT NULL — 권한명
  • module_code — VARCHAR(50) — NOT NULL — 기능 모듈
  • description — VARCHAR(500)

refund_requests

  • refund_request_id — BIGINT — Primary key — NOT NULL — 환불 요청 ID
  • term_id — BIGINT — Foreign key → council_terms.term_id — NOT NULL
  • refund_type — ENUM('STUDENT_FEE_FULL', 'EVENT_FULL', 'EVENT_BALANCE', 'OTHER') — NOT NULL
  • student_id — BIGINT — Foreign key → student_profiles.student_id
  • fee_application_id — BIGINT — Foreign key → fee_applications.fee_application_id
  • event_application_id — BIGINT — Foreign key → event_applications.event_application_id
  • settlement_participant_id — BIGINT — Foreign key → event_settlement_participants.settlement_participant_id
  • requester_name — VARCHAR(100) — NOT NULL
  • refund_amount — DECIMAL(15, 2) — NOT NULL
  • refund_reason — VARCHAR(500)
  • bank_name — VARCHAR(100) — NOT NULL
  • bank_account_number — VARCHAR(100) — NOT NULL — 운영 시 암호화 또는 마스킹 필요
  • account_holder — VARCHAR(100) — NOT NULL
  • refund_status — ENUM('REQUESTED', 'CHECKING', 'APPROVAL_REQUESTED', 'APPROVED', 'BATCHED', 'TRANSFERRED', 'FAILED', 'CANCELLED', 'RECONCILED') — NOT NULL
  • approval_request_id — BIGINT — Foreign key → approval_requests.approval_request_id
  • requested_at — DATETIME — NOT NULL
  • approved_at — DATETIME
  • transferred_at — DATETIME
  • cancelled_at — DATETIME
  • failure_reason — VARCHAR(500)
  • created_by — BIGINT — Foreign key → app_users.user_id — NOT NULL

role_permissions

  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • permission_id — BIGINT — Foreign key → permissions.permission_id — NOT NULL
  • granted_at — DATETIME — NOT NULL

roles

  • role_id — BIGINT — Primary key — NOT NULL — 역할 ID
  • role_code — VARCHAR(50) — NOT NULL — 역할 코드
  • role_name — VARCHAR(100) — NOT NULL — 역할명
  • role_level — INT — NOT NULL — 권한 수준
  • description — VARCHAR(500)
  • is_system_role — TINYINT(1) — NOT NULL

student_fee_records

  • student_fee_record_id — BIGINT — Primary key — NOT NULL — 학생별 현재 학생회비 상태 ID
  • fee_period_id — BIGINT — Foreign key → fee_periods.fee_period_id — NOT NULL
  • student_id — BIGINT — Foreign key → student_profiles.student_id
  • fee_application_id — BIGINT — Foreign key → fee_applications.fee_application_id — NOT NULL
  • current_status — ENUM('NOT_APPLIED', 'PENDING_PAYMENT', 'PAYMENT_CHECK', 'PAID', 'APPROVED', 'CANCELLED', 'REFUNDED') — NOT NULL
  • approved_amount — DECIMAL(15, 2) — NOT NULL
  • paid_at — DATETIME
  • approved_at — DATETIME
  • effective_from — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

student_profiles

  • student_id — BIGINT — Primary key — NOT NULL — 학생 ID
  • user_id — BIGINT — Foreign key → app_users.user_id — 로그인 사용자 연결 ID
  • student_number — VARCHAR(30) — NOT NULL — 학번
  • name — VARCHAR(100) — NOT NULL — 학생 이름
  • major_name — VARCHAR(150) — NOT NULL — 학과명
  • grade_level — TINYINT — 학년
  • enrollment_status — ENUM('ENROLLED', 'LEAVE', 'GRADUATED', 'OTHER') — NOT NULL
  • email — VARCHAR(255)
  • phone — VARCHAR(30)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sync_jobs

  • sync_job_id — BIGINT — Primary key — NOT NULL — 동기화 작업 ID
  • integration_resource_id — BIGINT — Foreign key → integration_resources.integration_resource_id — NOT NULL
  • job_type — ENUM('IMPORT', 'EXPORT', 'RECONCILE', 'GENERATE_REPORT', 'OTHER') — NOT NULL
  • job_status — ENUM('QUEUED', 'RUNNING', 'SUCCESS', 'PARTIAL_SUCCESS', 'FAILED', 'CANCELLED') — NOT NULL
  • started_at — DATETIME
  • finished_at — DATETIME
  • total_count — INT — NOT NULL
  • success_count — INT — NOT NULL
  • failure_count — INT — NOT NULL
  • error_message — TEXT
  • executed_by — BIGINT — Foreign key → app_users.user_id
  • created_at — DATETIME — NOT NULL