tbl_user_tracking

-1 rows


Description

Module: HRMS Employee Tracking
Purpose: Captures real-time employee location data, punch events, and device information for attendance tracking, geo-fencing, and movement analysis.
Data: Each row represents a raw tracking event captured from employee mobile devices, including GPS coordinates, timestamps, punch types, and device status information.
Process Usage:
- Records employee location data at regular intervals or on significant events.
- Captures punch-in/punch-out events with geographical context.
- Stores device and network information for data quality assessment.
Key Points:
- Primary source for raw location data and attendance events.
- Supports multiple punch types and tracking scenarios.
- Integrates with expense tracking and site-based attendance.
Business Impact: Provides foundational data for attendance calculation, movement pattern analysis, geo-fencing compliance, and expense validation.

Columns

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

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for each tracking event record.
daily_tracker_id int4 10 √ null

FK: public.tbl_daily_tracker.id (or similar daily summary table)
Purpose: Links individual tracking events to daily summary records for consolidated reporting.
Usage: Enables roll-up of multiple tracking events into daily attendance and movement summaries.
employee_id varchar 500 null

FK: public.tbl_employee.employeeid
Purpose: Identifies the employee associated with this tracking event.
Dependency: Links tracking data to employee master records for personalization and reporting.
date timestamp 29,6 null

Format: Timestamp
Purpose: Exact date and time when the tracking event was captured.
Collection Source: Device system time or server reception time.
Usage: Critical for attendance calculation, event sequencing, and time-based analysis.
latitude varchar 30 √ null

Format: String (Decimal degrees as string)
Purpose: Geographic latitude coordinate of the employee’s location.
Storage: Stored as string to preserve precision and handle various coordinate formats.
Conversion: Typically converted to Double for calculations and distance computations.
Range: -90.0 to +90.0 (stored as string representation).
longitude varchar 30 √ null

Format: String (Decimal degrees as string)
Purpose: Geographic longitude coordinate of the employee’s location.
Storage: Stored as string to preserve precision and handle various coordinate formats.
Conversion: Typically converted to Double for calculations and distance computations.
Range: -180.0 to +180.0 (stored as string representation).
punch_type varchar 10 null

Format: String
Purpose: Classification of the punch event type for attendance processing.
Common Values:
- “PUNCH_IN”: Employee starting work period
- “PUNCH_OUT”: Employee ending work period
- “BREAK_START”: Beginning of break period
- “BREAK_END”: Ending of break period
- “LOCATION_UPDATE”: Regular position update without attendance impact
Business Logic: Determines how the event affects attendance calculations and working hours.
createdby varchar 50 √ null
createddate timestamp 29,6 √ null
modifiedby varchar 50 √ null
modifieddate timestamp 29,6 √ null
networking varchar 100 √ null

Format: String (Time duration or calculated hours)
Purpose: Calculated net working hours up to this tracking event.
Usage: Running total or snapshot of productive time for the day.
Calculation: Typically derived from punch-in/punch-out differential minus break periods.
siteid varchar 100 √ null

Format: String
Purpose: Identifies the specific work site, client location, or project site.
Usage: Contextualizes tracking data for site-based attendance and project tracking.
Examples: “CLIENT_A_OFFICE”, “CONSTRUCTION_SITE_B”, “PROJECT_X_LOCATION”
punchfrom int4 10 √ null

Format: Integer
Purpose: Indicates the source or method of punch recording.
Common Values:
- 1: Mobile App GPS-based punch
- 2: Web portal manual entry
- 3: Biometric device integration
- 4: System-generated auto-punch
- 5: Admin override entry
Audit Purpose: Tracks how the punch event was recorded for data reliability assessment.
batterystatus varchar 50 √ null

Format: String
Purpose: Captures the mobile device battery level and charging status at tracking time.
Values: Percentage (e.g., “85%”), “CHARGING”, “LOW”, “CRITICAL”, “UNKNOWN”
Usage: Data quality indicator - low battery may affect GPS accuracy and tracking reliability.
expensetracking varchar 50 √ null

Format: String (JSON or structured data)
Purpose: Additional data related to expense claims or travel tracking.
Content Examples:
- Distance traveled since last tracking point
- Vehicle type used for travel
- Expense category or project code
- Additional context for expense validation
Integration: Links tracking data with expense management system.
locationname varchar 500 √ null

Indexes

Constraint Name Type Sort Column(s)
tbl_user_tracking_pk__tbl_user_tracking Primary key Asc id
tbl_user_tracking_idx_employee_punchtype_date_tbl_user_tracking Performance Asc/Asc/Asc employee_id + punch_type + date

Relationships

Close relationships within degrees of separation