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:

  1. Open Move Dialog

    • Click the move button on any time interval
    • Current project information is displayed
    • Duration and time range shown for verification
  2. 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
  3. 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
  4. 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