groupware

Tables (15)

admin_policies (시스템정책)

  • policy_key — VARCHAR(50)
  • policy_value — TEXT
  • updated_at — TIMESTAMP

app_assignments (부서/양식별 결재선 매핑)

  • assign_id — INT
  • dept_id — INT — Foreign key → departments.dept_id
  • form_id — INT — Foreign key → app_forms.form_id
  • template_id — INT — Foreign key → app_templates.template_id

app_documents (결재문서함)

  • app_doc_id — INT
  • form_id — INT — Foreign key → app_forms.form_id
  • requester_id — INT — Foreign key → users.user_id
  • title — VARCHAR(200) — NOT NULL
  • content — JSON
  • doc_status — ENUM('DRAFT', 'PROCEEDING', 'APPROVED', 'REJECTED')
  • created_at — TIMESTAMP

app_forms (기안서 양식 관리)

  • form_id — INT
  • form_name — VARCHAR(100) — NOT NULL
  • category — ENUM('HR', 'FINANCE', 'ATTENDANCE', 'GENERAL')
  • is_active — BOOLEAN

app_lines (결재진행이력)

  • line_id — INT
  • app_doc_id — INT — Foreign key → app_documents.app_doc_id
  • approver_id — INT — Foreign key → users.user_id
  • step_order — INT
  • status — ENUM('WAIT', 'APPROVE', 'REJECT')
  • comment — TEXT — EX
  • processed_at — DATETIME

app_template_steps (템플릿상세)

  • step_id — INT
  • template_id — INT — Foreign key → app_templates.template_id
  • step_order — INT — NOT NULL
  • role_type — ENUM('MANAGER', 'DIRECTOR', 'CEO', 'SPECIFIC_USER')
  • target_user_id — INT

app_templates (결재선템플릿)

  • template_id — INT
  • template_name — VARCHAR(100) — NOT NULL
  • is_active — BOOLEAN

attendance (근태기록)

  • att_id — BIGINT
  • user_id — INT — Foreign key → users.user_id
  • work_date — DATE — NOT NULL
  • check_in_at — DATETIME
  • check_out_at — DATETIME
  • is_auto_out — BOOLEAN
  • status — ENUM('NORMAL', 'OUTSIDE', 'TRIP', 'LEAVE')
  • ip_address — VARCHAR(45)
  • user_agent — TEXT
  • overtime_app_id — INT

attendance_monthly_stats (월간 근태기록 통계)

  • stat_id — INT — 통계ID
  • user_id — INT — 사용자ID
  • stat_month — CHAR(7) — NOT NULL — 기준월(YYYY-MM)
  • total_work_minutes — INT — 총근무시간분
  • total_overtime_minutes — INT — 총연장근무분
  • late_count — INT — 지각횟수
  • absent_count — INT — 결근횟수
  • leave_days_used — FLOAT — 월사용연차
  • updated_at — TIMESTAMP — 월간근태통계'; CREATE TABLE attendance_weekly_stats ( stat_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '통계ID
  • user_id — INT — 사용자ID
  • stat_year — INT — 기준연도
  • stat_week — INT — 기준주차
  • start_date — DATE — 주시작일
  • end_date — DATE — 주종료일
  • total_work_minutes — INT — 주간총근무분
  • is_violated_52h — BOOLEAN — 52시간위반여부
  • updated_at — TIMESTAMP — 주간근태통계'; ALTER TABLE attendance_monthly_stats ADD UNIQUE KEY (user_i

attendance_weekly_stats (주간 근태기록 통계)

  • stat_id — INT — 통계ID
  • user_id — INT — 사용자ID
  • stat_year — INT — 기준연도
  • stat_week — INT — 기준주차
  • start_date — DATE — 주시작일
  • end_date — DATE — 주종료일
  • total_work_minutes — INT — 주간총근무분
  • is_violated_52h — BOOLEAN — 52시간위반여부
  • updated_at — TIMESTAMP

audit_logs (감사로그)

  • log_id — BIGINT
  • admin_id — INT
  • action_type — VARCHAR(50)
  • description — TEXT
  • ip_address — VARCHAR(45)
  • created_at — TIMESTAMP

departments (부서정보)

  • dept_id — INT
  • parent_id — INT — Foreign key → departments.dept_id
  • dept_name — VARCHAR(100) — NOT NULL
  • dept_level — INT

user_careers (경력이력)

  • career_id — INT
  • user_id — INT — Foreign key → users.user_id
  • company_name — VARCHAR(100)
  • start_date — DATE
  • end_date — DATE
  • position — VARCHAR(50)
  • job_description — TEXT

user_documents (인사서류)

  • doc_id — INT
  • user_id — INT — Foreign key → users.user_id
  • doc_type — VARCHAR(50)
  • file_url — VARCHAR(255)
  • file_name — VARCHAR(100)

users (회원정보)

  • user_id — INT
  • login_id — VARCHAR(50)
  • password — VARCHAR(255)
  • name_ko — VARCHAR(50) — NOT NULL
  • name_en — VARCHAR(50)
  • phone — VARCHAR(20)
  • email — VARCHAR(100)
  • dept_id — INT — Foreign key → departments.dept_id
  • status — ENUM('DRAFT', 'PENDING', 'ACTIVE', 'RETIRED')
  • work_type — ENUM('A', 'B', 'C')
  • profile_image_url — VARCHAR(255)
  • remained_leave — FLOAT
  • created_at — TIMESTAMP