student
Tables (94)
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
감사_로그
- audit_log_id — BIGINT — Primary key — NOT NULL — 감사 로그 ID
- term_id — BIGINT — Foreign key → 학생회_기수.term_id
- actor_user_id — BIGINT — Foreign key → 사용자.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
거래처
- counterparty_id — BIGINT — Primary key — NOT NULL — 거래처 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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
결산_기간
- closing_period_id — BIGINT — Primary key — NOT NULL — 결산 기간 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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 → 사용자.user_id
- reopened_at — DATETIME
- reopen_reason — VARCHAR(500)
결산_보고서
- closing_report_id — BIGINT — Primary key — NOT NULL — 결산 보고서 ID
- closing_period_id — BIGINT — Foreign key → 결산_기간.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_request_id
- current_version_no — INT — NOT NULL
- generated_by — BIGINT — Foreign key → 사용자.user_id — NOT NULL
- generated_at — DATETIME
- approved_at — DATETIME
- created_at — DATETIME — NOT NULL
결산_보고서_버전
- closing_report_version_id — BIGINT — Primary key — NOT NULL — 결산 보고서 버전 ID
- closing_report_id — BIGINT — Foreign key → 결산_보고서.closing_report_id — NOT NULL
- version_no — INT — NOT NULL
- report_file_id — BIGINT — Foreign key → 파일.file_id
- report_data_json — JSON — 생성 시점의 결산 데이터 스냅샷
- change_summary — VARCHAR(500)
- generated_by — BIGINT — Foreign key → 사용자.user_id — NOT NULL
- generated_at — DATETIME — NOT NULL
- is_final — TINYINT(1) — NOT NULL
결산_참여자
- settlement_participant_id — BIGINT — Primary key — NOT NULL — 결산 참여자 스냅샷 ID
- event_settlement_id — BIGINT — Foreign key — NOT NULL
- event_application_id — BIGINT — Foreign key — 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
구성원_역할
- membership_id — BIGINT — Foreign key → 학생회_구성원.membership_id — NOT NULL
- role_id — BIGINT — Foreign key → 역할.role_id — NOT NULL
- assigned_at — DATETIME — NOT NULL
- assigned_by — BIGINT — Foreign key → 사용자.user_id
권한
- 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)
단체_송금_대상
- payout_batch_item_id — BIGINT — Primary key — NOT NULL — 단체 송금 대상 ID
- payout_batch_id — BIGINT — Foreign key → 단체_송금_배치.payout_batch_id — NOT NULL
- refund_request_id — BIGINT — Foreign key — 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_transaction_id
- retry_count — INT — NOT NULL
- failure_reason — VARCHAR(500)
- transferred_by — BIGINT — Foreign key → 사용자.user_id
- transferred_at — DATETIME
단체_송금_배치
- payout_batch_id — BIGINT — Primary key — NOT NULL — 단체 송금 배치 ID
- term_id — BIGINT — Foreign key → 학생회_기수.term_id — NOT NULL
- bank_account_id — BIGINT — Foreign key → 학생회_계좌.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_request_id
- export_file_id — BIGINT — Foreign key → 파일.file_id
- bank_result_file_id — BIGINT — Foreign key → 파일.file_id
- created_by — BIGINT — Foreign key → 사용자.user_id — NOT NULL
- created_at — DATETIME — NOT NULL
- exported_at — DATETIME
- completed_at — DATETIME
동기화_작업
- sync_job_id — BIGINT — Primary key — NOT NULL — 동기화 작업 ID
- integration_resource_id — BIGINT — Foreign key → 외부_연동_자원.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 → 사용자.user_id
- created_at — DATETIME — NOT NULL
사용자
- 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_step_id — BIGINT — Primary key — NOT NULL — 승인 단계 ID
- approval_request_id — BIGINT — Foreign key → 승인_요청.approval_request_id — NOT NULL
- step_no — INT — NOT NULL
- required_role_id — BIGINT — Foreign key → 역할.role_id — 승인에 필요한 역할
- approver_user_id — BIGINT — Foreign key → 사용자.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
승인_요청
- approval_request_id — BIGINT — Primary key — NOT NULL — 승인 요청 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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 → 사용자.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
알림
- notification_id — BIGINT — Primary key — NOT NULL — 알림 ID
- user_id — BIGINT — Foreign key → 사용자.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
역할
- 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
역할별_권한
- role_id — BIGINT — Foreign key → 역할.role_id — NOT NULL
- permission_id — BIGINT — Foreign key → 권한.permission_id — NOT NULL
- granted_at — DATETIME — NOT NULL
예산
- budget_id — BIGINT — Primary key — NOT NULL — 예산 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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_request_id
- created_by — BIGINT — Foreign key → 사용자.user_id — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
예산_항목
- budget_item_id — BIGINT — Primary key — NOT NULL — 예산 항목 ID
- budget_id — BIGINT — Foreign key → 예산.budget_id — NOT NULL
- parent_budget_item_id — BIGINT — Foreign key → 예산_항목.budget_item_id
- category_id — BIGINT — Foreign key
- department_id — BIGINT — Foreign key → 학생회_부서.department_id
- item_name — VARCHAR(200) — NOT NULL
- planned_amount — DECIMAL(15, 2) — NOT NULL
- note — VARCHAR(500)
- display_order — INT — NOT NULL
외부_연동_자원
- integration_resource_id — BIGINT — Primary key — NOT NULL — 연동 리소스 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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 → 사용자.user_id — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
은행_거래_장부_대조
- bank_ledger_match_id — BIGINT — Primary key — NOT NULL — 은행 거래와 장부 대조 ID
- bank_transaction_id — BIGINT — Foreign key → 은행_거래내역.bank_transaction_id — NOT NULL
- ledger_entry_id — BIGINT — Foreign key — 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 → 사용자.user_id
- matched_at — DATETIME
- note — VARCHAR(500)
은행_거래내역
- bank_transaction_id — BIGINT — Primary key — NOT NULL — 은행 거래 ID
- bank_account_id — BIGINT — Foreign key → 학생회_계좌.bank_account_id — NOT NULL
- bank_import_id — BIGINT — Foreign key → 은행_거래내역_업로드.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
은행_거래내역_업로드
- bank_import_id — BIGINT — Primary key — NOT NULL — 은행 거래내역 업로드 ID
- bank_account_id — BIGINT — Foreign key → 학생회_계좌.bank_account_id — NOT NULL
- source_file_id — BIGINT — Foreign key → 파일.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 → 사용자.user_id — NOT NULL
- imported_at — DATETIME — NOT NULL
- error_message — TEXT
장부_증빙
- ledger_attachment_id — BIGINT — Primary key — NOT NULL — 장부 증빙 연결 ID
- ledger_entry_id — BIGINT — Foreign key — NOT NULL
- file_id — BIGINT — Foreign key → 파일.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 → 사용자.user_id
- verified_at — DATETIME
- verification_status — ENUM('UPLOADED', 'VERIFIED', 'REJECTED') — NOT NULL
- rejection_reason — VARCHAR(500)
파일
- file_id — BIGINT — Primary key — NOT NULL — 파일 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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 → 사용자.user_id — NOT NULL
- uploaded_at — DATETIME — NOT NULL
- is_deleted — TINYINT(1) — NOT NULL
학생_정보
- student_id — BIGINT — Primary key — NOT NULL — 학생 ID
- user_id — BIGINT — Foreign key → 사용자.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
학생회_계좌
- bank_account_id — BIGINT — Primary key — NOT NULL — 학생회 계좌 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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
학생회_구성원
- membership_id — BIGINT — Primary key — NOT NULL — 학생회 소속 ID
- term_id — BIGINT — Foreign key → 학생회_기수.term_id — NOT NULL
- user_id — BIGINT — Foreign key → 사용자.user_id — NOT NULL
- department_id — BIGINT — Foreign key → 학생회_부서.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
학생회_기수
- 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
학생회_부서
- department_id — BIGINT — Primary key — NOT NULL — 학생회 부서 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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
학생회비_납부_기간
- fee_period_id — BIGINT — Primary key — NOT NULL — 학생회비 납부 기간 ID
- term_id — BIGINT — Foreign key → 학생회_기수.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_account_id — NOT NULL
- status — ENUM('PLANNED', 'OPEN', 'CLOSED', 'ARCHIVED') — NOT NULL
- created_by — BIGINT — Foreign key → 사용자.user_id — NOT NULL
- created_at — DATETIME — NOT NULL
학생회비_납부_상태
- student_fee_record_id — BIGINT — Primary key — NOT NULL — 학생별 현재 학생회비 상태 ID
- fee_period_id — BIGINT — Foreign key → 학생회비_납부_기간.fee_period_id — NOT NULL
- student_id — BIGINT — Foreign key → 학생_정보.student_id
- fee_application_id — BIGINT — Foreign key — 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
학생회비_납부_확인
- eligibility_check_id — BIGINT — Primary key — NOT NULL — 학생회비 자격 확인 ID
- event_application_id — BIGINT — Foreign key — NOT NULL
- student_fee_record_id — BIGINT — Foreign key → 학생회비_납부_상태.student_fee_record_id
- fee_period_id — BIGINT — Foreign key → 학생회비_납부_기간.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 → 사용자.user_id
- checked_at — DATETIME — NOT NULL
- note — VARCHAR(500)
and 14 more tables