biz
Tables (9)
attendances
- attendance_id — number(12) — NOT NULL
- employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
- record_date — date — NOT NULL
- check_in_time — timestamp
- check_out_time — timestamp
- work_hours — number(5,2)
- status — varchar(20) — NOT NULL
- source — varchar(20) — NOT NULL
- reason — varchar(1000)
- created_by — number(10)
- created_at — timestamp
- updated_by — number(10)
- updated_at — timestamp
departments
- dept_id — number(6) — NOT NULL
- dept_code — varchar(30) — NOT NULL
- dept_name — varchar(200) — NOT NULL
- parent_dept_id — number(6) — Foreign key → departments.dept_id
- manager_emp_id — number(10) — Foreign key → employees.employee_id
- created_at — timestamp
- updated_at — timestamp
employees
- employee_id — number(10) — NOT NULL
- emp_no — varchar(30)
- emp_name — varchar(100) — NOT NULL
- gender — char(1)
- birth_date — date
- phone — varchar(30)
- email — varchar(150)
- address — varchar(400)
- dept_id — number(6) — Foreign key → departments.dept_id
- position — varchar(100)
- rank — varchar(50)
- start_date — date — NOT NULL
- termination_date — date
- status — varchar(20) — NOT NULL
- career_years — number(5,1)
- ssn_mask — varchar(20)
- created_by — number(10)
- created_at — timestamp
- updated_by — number(10)
- updated_at — timestamp
leave_requests
- leave_id — number(12) — NOT NULL
- employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
- leave_type — varchar(30) — NOT NULL
- start_date — date — NOT NULL
- end_date — date — NOT NULL
- days — number(6,2)
- reason — varchar(2000)
- status — varchar(20) — NOT NULL
- approver_id — number(10) — Foreign key → employees.employee_id
- requested_at — timestamp
- approved_at — timestamp
- comments — varchar(2000)
- created_by — number(10)
- created_at — timestamp
personnel_assignments
- assignment_id — number(12) — NOT NULL
- employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
- assignment_date — date — NOT NULL
- previous_dept_id — number(6) — Foreign key → departments.dept_id
- new_dept_id — number(6) — Foreign key → departments.dept_id
- previous_position — varchar(100)
- new_position — varchar(100)
- previous_rank — varchar(50)
- new_rank — varchar(50)
- reason — varchar(1000)
- created_by — number(10)
- created_at — timestamp — NOT NULL
roles
- role_id — number(10) — NOT NULL
- role_name — varchar(50) — NOT NULL
salaries
- salary_id — number(12) — NOT NULL
- employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
- pay_period_start — date — NOT NULL
- pay_period_end — date — NOT NULL
- payment_date — date — NOT NULL
- base_salary — number(12,2) — NOT NULL
- additional_allowance — number(12,2)
- commission — number(12,2)
- other_pay — number(12,2)
- gross_pay — number(14,2)
- deductions — number(14,2)
- net_pay — number(14,2)
- remarks — varchar(2000)
- created_by — number(10)
- created_at — timestamp
user_roles
- user_id — number(10) — Foreign key → users.user_id — NOT NULL
- role_id — number(10) — Foreign key → roles.role_id — NOT NULL
users
- user_id — number(10) — NOT NULL
- employee_id — number(10) — Foreign key → employees.employee_id
- username — varchar(100) — NOT NULL
- password_hash — varchar(255) — NOT NULL
- salt — varchar(255)
- is_active — char(1) — NOT NULL
- is_locked — char(1) — NOT NULL
- last_login_at — timestamp
- failed_login_count — number(4)
- created_at — timestamp
- updated_at — timestamp