Skip to main content

Duration Tracking Implementation

This implementation adds tracking for transit and storage times for trackers. It includes:

  • New database columns for tracking durations
  • Hourly batch processing to update duration totals
  • GraphQL API for retrieving duration statistics
  • UI components for displaying average transit and storage times

Database Migration​

To apply the database changes, run:

# Install alembic if not already installed
pip install alembic

# Run the migration
alembic upgrade head

Setting Up the Cron Job​

The duration tracking system requires an hourly cron job to update the duration totals. To set this up:

  1. Make sure the update script is executable:
chmod +x app/src/update_durations.py
  1. Add the following line to your crontab (edit with crontab -e):
0 * * * * cd /path/to/tracker-graphql-2 && /usr/bin/python3 app/src/update_durations.py >> /var/log/tracker/durations.log 2>&1

Replace /path/to/tracker-graphql-2 with the actual path to your project directory.

Verification​

After setting up:

  1. Check that the new columns were added to the trackers table:

    • current_state_start
    • total_transit_time
    • total_storage_time

  2. Verify the cron job is running by checking the log file:

tail -f /var/log/tracker/durations.log
  1. Check the UI panels for "Average Transit Time" and "Average Storage Time" are displaying data.

Troubleshooting​

  1. If durations are not updating:

    • Check the cron job logs
    • Verify the database migration completed successfully
    • Ensure the update_durations.py script has execute permissions

  2. If UI panels show no data:

    • Check the browser console for GraphQL errors
    • Verify the durationStats query is working in GraphQL playground
    • Check that the batch processing job is running successfully

Implementation Details​

  • Duration updates happen hourly via cron
  • Times are stored in PostgreSQL interval type
  • UI displays durations in days (converted from hours)
  • Progress bars max out at 30 days (100%)
  • Durations are filtered by the same criteria as the tracker list (brand, campaign, status)

Technical Implementation​

The duration tracking system consists of several components (see Frontend Architecture for UI implementation details):

Database Schema​

The following columns were added to the trackers table:

-- Timestamp when the current state (IN_TRANSIT or IN_STORAGE) began
current_state_start TIMESTAMP WITH TIME ZONE

-- Total time spent in transit (PostgreSQL interval type)
total_transit_time INTERVAL DEFAULT '0' NOT NULL

-- Total time spent in storage (PostgreSQL interval type)
total_storage_time INTERVAL DEFAULT '0' NOT NULL

An index was also created on current_state_start to optimize the batch processing:

CREATE INDEX idx_trackers_current_state_start ON trackers (current_state_start);

Duration Processor​

The core logic for updating durations is in app/src/duration_processor.py. This module:

  1. Queries all trackers with IN_TRANSIT or IN_STORAGE status
  2. For each tracker:
    • Calculates the time since the last update
    • Adds this time to either total_transit_time or total_storage_time based on current status
    • Updates current_state_start to the current time
  3. Commits all updates in a single transaction

Cron Job​

The app/src/update_durations.py script is a simple wrapper around the duration processor that's designed to be run as a cron job. It:

  1. Sets up logging
  2. Creates a database session
  3. Calls the duration processor
  4. Handles any exceptions
  5. Closes the database session

GraphQL API​

The GraphQL API includes:

  1. Fields on the Tracker type for individual duration data:

    • totalTransitTime
    • totalStorageTime
    • currentStateStart

  2. A DurationStats type for aggregated statistics:

    • averageTransitTime
    • averageStorageTime
    • totalTrackers

  3. A durationStats query that accepts filters similar to the tracker list