tbl_workscheduleallocation_history

-1 rows


Description

Module: HRMS Attendance & Work Schedule (Audit)
Purpose: Stores an immutable, historical record of all changes to employee work schedule assignments.
Data: Each row records the state of an employee’s schedule allocation (tbl_workscheduleallocation) at the time of a change.
Process Usage:
- Populated by database triggers on CUD operations on the parent allocation table.
- Used for auditing and dispute resolution regarding when a schedule was changed for an employee.
Key Points:
- Essential for compliance to prove which work schedule was assigned to an employee at any point in time.
- Enables point-in-time reconstruction of an employee’s schedule assignments.
Business Impact: Ensures transparency and compliance in scheduling, vital for accurate payroll and attendance.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id serial 10 √ nextval('tbl_workscheduleallocation_history_id_seq'::regclass)

Primary key for the history record
Format: Integer (auto-increment)
Used as: Unique identifier for this specific historical event.
employeeid varchar 50 √ null

FK: public.tbl_employee.id
Meaning: The unique identifier of the employee whose schedule was changed.
Dependency: Core key linking the history record to the employee.
shiftid int8 19 √ null
tbl_shiftmasterv1.shiftid Implied Constraint R

FK: public.tbl_shift.id
Meaning: The shift ID assigned to the employee at this point in time.
Logic: Nullable if the allocation is not directly to a single shift, but to a schedule.
workscheduleid int8 19 √ null

FK: public.tbl_attendance_workschedule.id
Meaning: The ID of the work schedule that was assigned to the employee.
Dependency: Core foreign key linking to the defined work pattern.
startdate date 13 √ null

The effective date when the work schedule assignment began for the employee.
Format: Date
Logic: Used to determine schedule validity period.
enddate date 13 √ null

The effective date when the work schedule assignment ended for the employee.
Format: Date
Logic: Nullable if the assignment is current/indefinite.
createdby varchar 50 √ null

FK: public.tbl_userlogin.id
Meaning: The user (typically HR or Admin) who performed the action that led to this history record (i.e., changed the schedule allocation).
Usage: Identifies the auditor/administrator responsible for the change.
createddate timestamp 29,6 √ null

Timestamp of when the historical record was generated (i.e., when the change occurred)
Stored as: UTC
Format: Full timestamp, e.g., 2025-10-23T10:00:00Z
modifiedby varchar 50 √ null

FK: public.tbl_userlogin.id
Meaning: The user who last modified the schedule allocation record.
Usage: Identifies the auditor/administrator for the latest change.
modifieddate timestamp 29,6 √ null

Timestamp of when the historical record was last modified.
Stored as: UTC
Format: Full timestamp, e.g., 2025-10-23T10:00:00Z
Logic: Nullable if the record has not been modified since creation.

Indexes

Constraint Name Type Sort Column(s)
tbl_workscheduleallocation_history_pk_tbl_workscheduleallocatio Primary key Asc id

Relationships

Close relationships within degrees of separation