orbitflow-ERD

Tables (15)

attendance

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • employee_id — BIGINT — Foreign key → employee.id — NOT NULL
  • work_date — DATE — NOT NULL
  • commute_at — DATETIME
  • leave_at — DATETIME
  • status — VARCHAR(50)
  • applied_rule_id — BIGINT — Foreign key → attendance_rule.id
  • is_corrected — BOOLEAN — NOT NULL
  • correction_reason — VARCHAR(255)

attendance_event

  • id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employee.id — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • base_role — VARCHAR(20) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL

attendance_record

  • id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employee.id
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • days — DECIMAL(4, 1) — NOT NULL
  • type_id — BIGINT — Foreign key → leave_type.id — NOT NULL
  • reason — VARCHAR(255)
  • status — ENUM( 'DRAFT', 'SUBMITTED', 'IN_PROGRESS', 'APPROVED', 'REJECTED' ) — NOT NULL
  • approved_at — TIMESTAMP

attendance_rule

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • name — VARCHAR(100)
  • default_start_time — TIME — NOT NULL
  • default_end_time — TIME — NOT NULL
  • default_break_minutes — INT
  • late_threshold_min — INT
  • early_leave_threshold_min — INT
  • is_default — BOOLEAN — NOT NULL

chat_conversation

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key — NOT NULL
  • employee_id — BIGINT — Foreign key — NOT NULL
  • manual_category_id — BIGINT — Foreign key → manual_category.id
  • manual_category_name — VARCHAR(255)
  • title — VARCHAR(255)
  • status — VARCHAR(30) — NOT NULL
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

chat_message

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key — NOT NULL
  • conversation_id — BIGINT — Foreign key → chat_conversation.id — NOT NULL
  • role — VARCHAR(30) — NOT NULL
  • content — LONGTEXT — NOT NULL
  • meta_json — JSON
  • created_at — TIMESTAMP — NOT NULL

company

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • business_number — VARCHAR(20) — NOT NULL

employee

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • employee_no — VARCHAR(20) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • role — ENUM('TEAM_ORBIT', 'COMPANY_ADMIN', 'ADMIN', 'EMPLOYEE') — NOT NULL
  • status — ENUM('TEMP', 'ACTIVE', 'SUSPENDED', 'RESIGNED') — NOT NULL
  • work_status — ENUM('WORKING', 'AWAY', 'ON_LEAVE', 'OFF_WORK') — NOT NULL
  • hire_date — DATE — NOT NULL

employee_att_rule

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • employee_id — BIGINT — Foreign key → employee.id — NOT NULL
  • start_time — TIME
  • end_time — TIME
  • break_minutes — INT
  • reason — VARCHAR(255)
  • valid_from — DATE — NOT NULL
  • valid_to — DATE — NOT NULL
  • applied_at — DATETIME
  • is_active — BOOLEAN — NOT NULL

file

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key — NOT NULL
  • object_key — VARCHAR(512) — NOT NULL
  • origin_file — VARCHAR(255)
  • sys_file — VARCHAR(255)
  • content_type — VARCHAR(255)
  • file_size — BIGINT
  • created_by — BIGINT — Foreign key
  • created_at — TIMESTAMP — NOT NULL

grant_history

  • id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employee.id — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • grant_date — DATE — NOT NULL
  • granted_days — DECIMAL(4, 2) — NOT NULL
  • grant_type — VARCHAR(50)
  • expiration_date — DATE
  • is_expired — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

leave_balance

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → company.id — NOT NULL
  • employee_id — BIGINT — Foreign key → employee.id — NOT NULL
  • year — INT — NOT NULL
  • total_granted — DECIMAL(5, 2)
  • remaining_days — DECIMAL(5, 2)
  • last_updated_at — TIMESTAMP — NOT NULL

leave_type

  • id — BIGINT — Primary key — NOT NULL
  • type_name — VARCHAR(50) — NOT NULL
  • is_countable — BOOLEAN — NOT NULL
  • unit_days — DECIMAL(5, 3) — NOT NULL
  • description — VARCHAR(255)

manual_category

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key — NOT NULL
  • category_name — VARCHAR(255) — NOT NULL
  • description — VARCHAR(255)
  • is_active — BOOLEAN — NOT NULL
  • sort_order — INT — NOT NULL
  • created_by — BIGINT — Foreign key
  • modified_by — BIGINT — Foreign key
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

manual_metadata

  • id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key — NOT NULL
  • file_id — BIGINT — Foreign key → file.id — NOT NULL
  • category_id — BIGINT — Foreign key → manual_category.id — NOT NULL
  • status — VARCHAR(50) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key
  • modified_by — BIGINT — Foreign key
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL