tbl_attendancepolicy_history

1 rows


Description

Module: HRMS Time & Attendance (Audit)
Purpose: Provides a complete, immutable audit trail for all changes to attendance policy records in public.tbl_attendancepolicy.
Data: Stores a historical copy of the policy configuration whenever a change is made to the parent record.
Process Usage:
- Used for auditing, compliance, and viewing the policy configuration effective at a specific historical date.
- Populated by system triggers or application logic on CUD operations of the parent table.
Key Points:
- Ensures data integrity and provides a historical log for policy evolution.
- Every row is a snapshot of the full policy at the time of modification.
Business Impact: Essential for compliance audits and dispute resolution regarding policy changes.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id bigserial 19 √ nextval('tbl_attendancepolicy_history_id_seq'::regclass)

Primary key of the history table
Format: Integer (auto-increment)
Used as: Unique identifier for a single historical policy version.
policyhistoryid int8 19 √ null

FK: public.tbl_attendancepolicy.id
Meaning: The primary key of the parent attendance policy that this historical record belongs to.
Dependency: Links the historical record back to its active policy.
policyname varchar 100 √ null

Meaning: The user-friendly name of the attendance policy (e.g., “Standard Office Hours,” “Flexible WFH Policy”).
Format: Varchar/Text.
description varchar 1000 √ null

Detailed description of the policy, summarizing its scope and key features at the time this history record was created.
Format: Text.
members text 2147483647 √ null

Meaning: Indicates which employees or groups were assigned to this policy at the time of the revision.
Format: Array of IDs or JSONB.
allowfromapp bool 1 √ null

Flag to indicate if attendance (punch-in/out) was allowed via the mobile application in this policy version.
Format: Boolean.
allowfromweb bool 1 √ null

Flag to indicate if attendance was allowed via the web portal (desktop browser) in this policy version.
Format: Boolean.
allowfrombiometric bool 1 √ null

Flag to indicate if attendance was allowed via biometric devices in this policy version.
Format: Boolean.
calculationmethod varchar 255 √ null

Defines the basis for attendance calculation (e.g., Total Working Hours, Net Working hours-based, Single punch) used by this policy version.
Format: Varchar/Text.
multipunchallowed bool 1 √ null

Determines if an employee was allowed multiple punch-in/out pairs in a single day under this policy version.
Format: Boolean.
correctionlimits bool 1 √ null

The maximum number of times an employee could raise a correction request within a specified period in this policy version.
Format: Integer.
correctionrequestraisedays int4 10 √ null

The number of days after an attendance event within which a correction request could be raised under this policy version.
Format: Integer.
correctionmaximumallow int4 10 √ null

The total maximum number of correction requests allowed (Monthly or per policy term) under this policy version.
Format: Integer.
regularizationlimits bool 1 √ null

The maximum number of regularization requests allowed (e.g., for missing a punch) under this policy version.
Format: Integer.
regularizationrequestraisedays int4 10 √ null

The number of days after a missed punch within which a regularization request could be submitted under this policy version.
Format: Integer.
regularizationmaximumallow int4 10 √ null

The total maximum number of regularization requests allowed under this policy version.
Format: Integer.
timeperiodforlatecoming int4 10 √ null

Defines the period within the shift start time that determines a “Late Coming” event for this policy version.
Format: Time/Interval.
timeperiodforearlygoing int4 10 √ null

Defines the period before the shift end time that determines an “Early Going” event for this policy version.
Format: Time/Interval.
timeperiodfortimeoff int4 10 √ null

The time period required to qualify an absence as a “Time Off” event (partial day leave) for this policy version.
Format: Time/Interval.
fixedattendance bool 1 √ null

Flag indicating if this policy version used a fixed shift/attendance schedule.
Format: Boolean.
fixedlatecominggrace int4 10 √ null

The grace time allowed for late-coming in a fixed attendance model for this policy version.
Format: Time/Interval.
fixedearlygoinggrace int4 10 √ null

The grace time allowed for early-going in a fixed attendance model for this policy version.
Format: Time/Interval.
allowlatecomingtype varchar 255 √ null

The method used to calculate or allow late-coming (e.g., Daily, Monthly, Slab-based) for this policy version.
Format: Varchar/Text.
allowearlygoingtype varchar 255 √ null

The method used to calculate or allow early-going for this policy version.
Format: Varchar/Text.
allowlatecomingminutes int4 10 √ null

Total aggregate late-coming minutes allowed before a penalty was applied in this policy version.
Format: Integer.
allowearlygoingminutes int4 10 √ null

Total aggregate early-going minutes allowed before a penalty was applied in this policy version.
Format: Integer.
allowlatecomingtime int4 10 √ null

Specific time boundary for late-coming for this policy version.
Format: Time.
allowearlygoingtime int4 10 √ null

Specific time boundary for early-going for this policy version.
Format: Time.
firstpunchlateby int4 10 √ null

A flag or value indicating if a penalty was applied based on the first punch-in being late by a certain margin in this policy version.
Format: Time/Interval/Boolean.
applypenalty bool 1 √ null

Master flag indicating if attendance penalties were enabled in this policy version.
Format: Boolean.
latecomingandearlygoingpenalty bool 1 √ null

The type of combined penalty applied for late-coming and early-going events for this policy version.
Format: Varchar/Text.
eventtype varchar 255 √ null

Defines what constituted a penalizable event (e.g., For Every event or after a set number of events) in this policy version.
Format: Varchar/Text.
noofevents int4 10 √ null

The number of late-coming/early-going events allowed before a penalty was triggered in this policy version.
Format: Integer.
fixeddeductfrom varchar 255 √ null

Specifies what the penalty was deducted from (e.g., Leave or attendance deduction) in this policy version.
Format: Varchar/Text.
fixeddeductleavetypes varchar 255 √ null

Comma-separated list of leave types to deduct from (Eg NBL PL) in this policy version.
Format: Varchar/Text.
fixeddeductionslab varchar 255 √ null

JSON or Text describing the penalty slab based on events or shortfall hours for this policy version.
Format: JSONB/Text.
deductdays float8 17,17 √ null

The number of days (or fractional days) deducted as a penalty in this policy version.
Format: Numeric/Float.
fixedwaivepenalty bool 1 √ null

Flag or criteria to automatically waive a penalty in this policy version.
Format: Boolean/Text.
shortfallnotification bool 1 √ null

Flag to enable/disable notifications for attendance shortfalls in this policy version.
Format: Boolean.
latecomingearlygoingnotification bool 1 √ null

Flag to enable/disable immediate notifications for late-coming or early-going in this policy version.
Format: Boolean.
flexibleattendance bool 1 √ null

Flag indicating if this policy version used a flexible shift/attendance schedule.
Format: Boolean.
flexiblegracehours float8 17,17 √ null

Total flexible hours/minutes allowed as a grace before a working hour shortfall was penalized in this policy version.
Format: Time/Interval.
flexibledeductfrom varchar 255 √ null

Specifies what the shortfall penalty was deducted from in a flexible attendance model for this policy version.
Format: Varchar/Text.
flexibledeductleavetypes varchar 255 √ null

Leave types (PL NBL) to deduct from for flexible attendance shortfalls in this policy version.
Format: Varchar/Text.
flexibleshortfallslabhours varchar 255 √ null

Penalty slab definition based on hours of shortfall in flexible attendance for this policy version.
Format: JSONB/Text.
flexibleshortfallnotification bool 1 √ null

Notification flag for flexible attendance shortfalls in this policy version.
Format: Boolean.
penaltywarningnotification bool 1 √ null

Flag to enable/disable a warning notification before a penalty was applied in this policy version.
Format: Boolean.
inpunchoutpunchnotification bool 1 √ null

Flag to enable/disable confirmation notifications for every punch-in and punch-out event in this policy version.
Format: Boolean.
stage varchar 255 √ null

The Policy stage (e.g., Draft, Active, Retired) at the time this history record was created.
Format: Varchar/Text.
combineecrlgrlimit bool 1 √ null

Flag to indicate if late-coming/early-going limits were combined or treated separately in this policy version.
Format: Boolean.
createdby varchar 50 √ null

FK: public.tbl_userlogin.id or public.tbl_employee.employeeid
Meaning: The user who created this policy version record (i.e., the user who last modified the parent policy).
createddate timestamp 29,6 √ null

Timestamp of when this historical policy version was created/recorded.
Stored as: UTC
Format: Timestamp with timezone.
modifiedby varchar 50 √ null

FK: public.tbl_userlogin.id or public.tbl_employee.employeeid
Meaning: The last user who modified the policy before this history record was created.
modifieddate timestamp 29,6 √ null

Timestamp of the last modification to the policy before this history record was created.
Stored as: UTC
Format: Timestamp with timezone.
startingday int4 10 √ 0

Defines the start day of the attendance/payroll cycle for this policy version.
Format: Integer/Text.
mobilepunchinimage int4 10 √ null

Flag requiring an image/photo capture during mobile punch-in in this policy version.
Format: Boolean.
mobilepunchoutimage int4 10 √ null

Flag requiring an image/photo capture during mobile punch-out in this policy version.
Format: Boolean.
weblocation varchar 50 √ null

Flag or setting for geographical location (IP/network) validation during web punch-in/out in this policy version.
Format: Boolean/JSONB.
mobilelocation varchar 50 √ null

Flag or setting for GPS/geo-fencing location validation during mobile punch-in/out in this policy version.
Format: Boolean/JSONB.
allowedmaxtimeinotherhalf int4 10 √ 50

Maximum time allowed to punch in the second half of the day in this policy version.
Format: Time/Interval.
shiftstartnotificationtitle varchar 100 √ null

Custom title for the shift start notification in this policy version.
Format: Varchar/Text.
shiftstartnotificationdescription varchar 500 √ null

Custom body/description for the shift start notification in this policy version.
Format: Text.
shiftendnotificationtitle varchar 100 √ null

Custom title for the shift end notification in this policy version.
Format: Varchar/Text.
shiftendnotificationdescription varchar 500 √ null

Custom body/description for the shift end notification in this policy version.
Format: Text.
fixedshiftgracetime int4 10 √ null

Total non-cumulative grace time for a fixed shift in this policy version.
Format: Time/Interval.
sandwichoffday bool 1 √ null

Flag to determine if an off-day between two leaves/absences was counted as a leave/absence in this policy version.
Format: Boolean.
looseninginout bool 1 √ null

A mechanism to relax punch-in/out time constraints in this policy version.
Format: Boolean/JSONB.
timesheetonpunchin varchar 50 √ '''OFF'''::character varying

Flag to prompt a timesheet or task entry upon punch-in in this policy version.
Format: Boolean.
timesheetonpunchout varchar 50 √ '''OFF'''::character varying

Flag to prompt a timesheet or task entry upon punch-out in this policy version.
Format: Boolean.
salarydeductiontype varchar 50 √ null

Specifies the base for salary deduction (e.g., Basic, Gross) for this policy version.
Format: Varchar/Text.
salarydeduct float8 17,17 √ null

Flag to show if an amount (salary) was cut as a penalty in this policy version.
Format: Boolean.
salaryfulldayhour int4 10 √ null

The number of hours considered a “Full Day” for salary calculation in this policy version.
Format: Numeric.
fixedshiftgracetimehalfday int4 10 √ null

Non-cumulative grace time for a fixed shift when calculating a half-day loss in this policy version.
Format: Time/Interval.
restrictgraceafter int4 10 √ null

Time of day or condition after which grace time was no longer applied in this policy version.
Format: Time.
addgracewhen varchar 50 √ null

Condition or rule for when to apply additional grace time in this policy version.
Format: Varchar/Text.
fixedwaivepenaltyhalfday bool 1 √ null

Flag or criteria to waive a half-day penalty in this policy version.
Format: Boolean/Text.
removeotherpenaltywhenhalfday bool 1 √ null

Flag to indicate if other minor penalties were cleared when a major half-day penalty was applied in this policy version.
Format: Boolean.

Indexes

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

Relationships

Close relationships within degrees of separation