Time Data Management

Time Interval Storage

How Time Data is Stored and Organized

The time tracking system uses a centralized time_intervals table structure for all time data storage:

Core Time Interval Fields:

started_at and finished_at timestamps for precise time tracking
user_id links time to specific team members
project_id associates time with projects
task_id (optional) for task-specific time tracking
type field indicating tracking method (START, CONTINUE, FINISH, AUTO)
manual_time flag distinguishing manual entries from desktop app tracking

Database Relationships:

Foreign key relationships ensure data integrity with users and projects
Soft delete capabilities for project and user references
Indexed started_at field for efficient date-based queries
Support for multi-currency time tracking through currency_id field

Data Organization:

Time intervals are stored with timezone-aware timestamps
Notes can be attached to time intervals through polymorphic relationships
Screenshot associations link visual proof to time intervals
Team isolation ensures data security and proper access control

Time Calculation Methods

Duration Calculations and Time Formatting

The system employs multiple sophisticated methods for time calculations:

Duration Calculation:

Primary calculation uses TIMESTAMPDIFF(SECOND, started_at, finished_at) for precision
Automatic conversion between seconds, minutes, hours, and days
Support for both decimal hours and HH:MM:SS time formatting
Time-to-hours conversion: hours + (minutes/60) + (seconds/3600)

Time Formatting Options:

SEC_TO_TIME() converts seconds to HH:MM:SS format
TIME_TO_SEC() converts HH:MM:SS to total seconds
Custom formatting for different display contexts (reports, dashboards, invoices)
ISO 8601 duration support for advanced time calculations

Calculation Accuracy:

Second-level precision for all time calculations
Timezone-aware calculations respect team and user preferences
Automatic handling of daylight saving time transitions
Consistent calculation methods across all system components

Time Movement Between Projects

Moving Tracked Time to Correct Projects with Audit Trail

Corcava provides the ability to move time intervals between projects when time was accidentally tracked to the wrong project. This feature includes comprehensive audit logging to ensure transparency and prevent fraud.

Accessing Time Movement:

Navigate to My Work page
Find the time interval you want to move
Click the Move button (arrow icon) next to the interval

Moving Time Intervals:

Open Move Dialog
- Click the move button on any time interval
- Current project information is displayed
- Duration and time range shown for verification
Select Destination Project
- Choose the correct project from the dropdown
- Only projects you have access to are available
- Projects must be in the same team
Provide Reason (Optional but Recommended)
- Enter why the time is being moved
- Examples: "Tracked to wrong project", "Client changed scope"
- Reason is stored in audit log for accountability
Confirm Move
- Click "Move Time" to complete the action
- Time interval is immediately updated
- Change is logged automatically

Restrictions and Validations:

✅ Cannot move invoiced time - Once time is billed, it cannot be moved
✅ Same team only - Time cannot be moved between different teams
✅ Permission required - Both company and user permissions must allow movement
✅ Active projects only - Cannot move time to archived/deleted projects

Visual Indicators:

When a time interval has been moved, a "Moved" badge appears next to the project name:

Badge is clickable to view complete movement history
Shows who moved it, when, and from which project
Includes reason if one was provided

Movement Audit Log

Comprehensive Tracking of All Time Movements

Every time movement is automatically logged with complete audit trail information to ensure transparency and prevent unauthorized changes.

What Gets Logged:

For each time movement, the system records:

Who made the change (user name and ID)
When the change occurred (timestamp with timezone)
What changed (old project → new project)
Why it changed (optional reason provided by user)
Where it happened (IP address of the user)
How (user agent/browser information)

Viewing Movement History:

For Individual Intervals:

Click the "Moved" badge on any moved interval
Modal opens showing complete movement history
See all moves if interval was moved multiple times
View who made each move and why

For Team-Wide Audit:

Navigate to Reports → Time Movement Log
See all time movements across your team
Paginated list with 50 movements per page
Shows: when, who, from which project, to which project, reason

Audit Log Features:

Immutable Records - Audit logs cannot be edited or deleted
Permanent Trail - Complete history preserved indefinitely
Security Context - IP and user agent logged for forensics
Searchable - Future filtering by date, user, or project
Permission-Based Access - Only authorized users can view audit logs

Who Can View Audit Logs:

SuperAdmins - Can view all movement logs in their team
Organization Managers - Can view all movement logs in their team
Project Managers - Can view all movement logs in their team
Regular Users - Can view logs for their own time intervals only

Audit Log Security:

The audit logging system is designed to prevent fraud and ensure accountability:

All changes tracked automatically - cannot be bypassed
Logs stored separately from time intervals - tamper-proof
Database-level restrictions prevent modification
Complete audit trail for compliance and accountability

Time Aggregation

Rolling Up Task Time to Project and Team Levels

The system provides comprehensive time aggregation across multiple organizational levels:

Task-Level Aggregation:

Individual task time totals calculated from associated time intervals
Task time summaries show total time invested per task
Task completion correlation with time investment tracking
Daily and weekly task time analysis for productivity insights

Project-Level Aggregation:

Daily project time totals with real-time updates during tracking
Weekly project time calculations with configurable limits (40-hour default)
Project time percentage distribution across team members
Historical project time analysis for trend identification

Team-Level Aggregation:

Team daily time summaries across all projects and members
Active member counts based on time tracking activity
Team productivity metrics and time allocation analysis
Cross-project time distribution for resource planning

User-Level Aggregation:

Individual user time totals across all assigned projects
Daily and weekly time limits with automatic enforcement
Personal productivity analytics and time tracking patterns
Historical user time data for performance analysis

Historical Time Data

Accessing and Analyzing Historical Time Records

The system maintains comprehensive historical time data with flexible access methods:

Historical Data Access:

Date range queries support any historical period analysis
Efficient database indexing enables fast historical data retrieval
Time interval data preserved even when projects or users are deleted
Comprehensive audit trail for all time tracking activities

Historical Analysis Capabilities:

Time trend analysis across days, weeks, months, and years
Project time evolution tracking for long-term projects
Team productivity trends and seasonal analysis
Comparative analysis between different time periods

Data Retention:

Permanent storage of all time interval records
Screenshot data maintained with associated time intervals
Notes and context preserved for future reference
Time tracking logs maintained for debugging and analysis

Historical Reporting:

Flexible date range selection for custom historical reports
Time aggregation by various periods (daily, weekly, monthly)
Historical project profitability analysis using time and rate data
Team performance trends over extended periods

Time Data Export

Exporting Time Data for External Analysis

The system supports comprehensive time data export capabilities:

Export Formats:

Time data integrated into invoice generation for client billing
Report generation with customizable time period selection
Chart data export for visual analysis and presentations
Team report generation with automated scheduling

Export Scope Options:

Individual user time data export
Project-specific time data extraction
Team-wide time data compilation
Client-specific time data for transparent billing

Automated Export Features:

Daily team reports automatically generated and distributed
Weekly invoice generation from tracked time data
Scheduled report generation for regular business processes
Email delivery of time reports to stakeholders

Data Integration:

Time data feeds directly into invoicing systems
Integration with expense tracking for complete project costing
Rate calculation integration for accurate billing
Multi-currency support for international operations

Data Integrity and Performance

Ensuring Accurate Time Data Management

The system includes robust data integrity and performance optimization features:

Data Integrity Measures:

Transaction-based time entry prevents partial data corruption
Automatic validation of time interval relationships
Constraint enforcement for logical time sequences
Data consistency checks across related records

Performance Optimization:

Strategic database indexing for fast time queries
Efficient aggregation queries minimize system load
Cached calculations for frequently accessed time totals
Optimized date range queries for historical data access

Data Quality Assurance:

Automatic detection and prevention of overlapping time intervals
Validation of time sequence logic (start before end)
Minimum time interval enforcement (10-minute minimum)
Time limit validation (24-hour daily maximum)

Backup and Recovery:

Complete time data preservation in system backups
Point-in-time recovery capabilities for time data
Data migration support for system upgrades
Export capabilities for data portability and backup