Transit Time Profiles Architecture

Overview

This document describes the configurable transit time architecture for iDrv5. The design follows the platform’s core philosophy of making all features configuration-driven and adaptable to different client requirements.

The Problem with Previous Design

The original transit time implementation had these limitations:

Issue	Impact
Tightly coupled to rate cards	Same transit times duplicated across rate cards
Service levels stored as rows	3 rows for each zone pair (Express, Standard, Economy)
No customization options	All rate cards use the same transit times
No opt-out capability	Every rate card had to have transit times

New Architecture

Core Concept: Transit Time Profiles

A Transit Time Profile is a named, reusable collection of transit times that can be:

• Shared across multiple rate cards
• Customized per customer/contract
• Disabled entirely for specific rate cards

┌─────────────────────────────────────────────────────────────────────────┐
│                         TRANSIT TIME PROFILES                           │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  ┌─────────────────────┐    ┌─────────────────────┐                    │
│  │ Standard AU Network │    │ Express Metro Only  │                    │
│  │     (DEFAULT)       │    │                     │                    │
│  └──────────┬──────────┘    └──────────┬──────────┘                    │
│             │                          │                                │
│             ▼                          ▼                                │
│  ┌─────────────────────────────────────────────────────────────┐       │
│  │                   TRANSIT TIME ENTRIES                       │       │
│  │  ┌─────────────────────────────────────────────────────┐    │       │
│  │  │ SYD → MEL : 24 hours base                           │    │       │
│  │  │ MEL → BNE : 48 hours base                           │    │       │
│  │  │ SYD → BNE : 36 hours base                           │    │       │
│  │  └─────────────────────────────────────────────────────┘    │       │
│  └─────────────────────────────────────────────────────────────┘       │
│                              │                                          │
│                              ▼                                          │
│  ┌─────────────────────────────────────────────────────────────┐       │
│  │              SERVICE LEVEL MULTIPLIERS                       │       │
│  │  ┌─────────────────────────────────────────────────────┐    │       │
│  │  │ Express  : 0.50x (50% = faster)                     │    │       │
│  │  │ Standard : 1.00x (100% = base time)                 │    │       │
│  │  │ Economy  : 1.50x (150% = slower)                    │    │       │
│  │  └─────────────────────────────────────────────────────┘    │       │
│  └─────────────────────────────────────────────────────────────┘       │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

Rate Card Transit Time Modes

Each rate card can configure how it handles transit times:

Mode	Description	Use Case
inherit	Use the default global profile	Most rate cards (default)
profile	Use a specific transit time profile	Customer-specific transit commitments
custom	Use rate card-specific overrides	One-off customer requirements
none	No transit times (quote only)	Price quotes without ETA

┌─────────────────────────────────────────────────────────────────────────┐
│                           RATE CARDS                                    │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐         │
│  │ Standard Card   │  │ Premium Client  │  │ Quote Only Card │         │
│  │ mode: inherit   │  │ mode: profile   │  │ mode: none      │         │
│  │                 │  │ profile_id: 2   │  │                 │         │
│  └────────┬────────┘  └────────┬────────┘  └────────┬────────┘         │
│           │                    │                    │                   │
│           ▼                    ▼                    ▼                   │
│    Default Profile      Express Metro       No Transit Times           │
│                            Profile                                      │
│                                                                         │
│  ┌─────────────────┐                                                   │
│  │ Custom Client   │                                                   │
│  │ mode: custom    │──────► Rate Card Transit Overrides                │
│  └─────────────────┘        (SYD→MEL: 18 hours for this client)        │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

Database Schema

New Tables

1. transit_time_profiles

CREATE TABLE transit_time_profiles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id)
);

2. transit_time_entries

CREATE TABLE transit_time_entries (
    id SERIAL PRIMARY KEY,
    profile_id INTEGER NOT NULL REFERENCES transit_time_profiles(id),
    origin_zone_id INTEGER REFERENCES zones(id),
    destination_zone_id INTEGER REFERENCES zones(id),
    base_transit_hours INTEGER NOT NULL,  -- Before service level adjustment
    distance_km DECIMAL(10, 2),
    notes TEXT,
    UNIQUE (profile_id, origin_zone_id, destination_zone_id)
);

3. service_level_transit_multipliers

CREATE TABLE service_level_transit_multipliers (
    id SERIAL PRIMARY KEY,
    profile_id INTEGER NOT NULL REFERENCES transit_time_profiles(id),
    service_level_id INTEGER NOT NULL REFERENCES service_levels(id),
    transit_multiplier DECIMAL(4, 2) NOT NULL DEFAULT 1.00,
    adjustment_hours INTEGER DEFAULT 0,  -- Fixed adjustment after multiplier
    display_priority INTEGER DEFAULT 0,
    UNIQUE (profile_id, service_level_id)
);

4. rate_card_transit_overrides

CREATE TABLE rate_card_transit_overrides (
    id SERIAL PRIMARY KEY,
    rate_card_id INTEGER NOT NULL REFERENCES rate_cards(id),
    origin_zone_id INTEGER REFERENCES zones(id),
    destination_zone_id INTEGER REFERENCES zones(id),
    service_level_id INTEGER REFERENCES service_levels(id),
    custom_transit_hours INTEGER NOT NULL,
    price_adjustment_percent DECIMAL(5, 2),
    notes TEXT,
    UNIQUE (rate_card_id, origin_zone_id, destination_zone_id, service_level_id)
);

Modified Tables

rate_cards (new columns)

ALTER TABLE rate_cards
ADD COLUMN transit_time_mode VARCHAR(20) DEFAULT 'inherit'
    CHECK (transit_time_mode IN ('inherit', 'profile', 'custom', 'none'));

ALTER TABLE rate_cards
ADD COLUMN transit_time_profile_id INTEGER REFERENCES transit_time_profiles(id);

API Endpoints

Transit Time Profiles

Method	Endpoint	Description
GET	/api/transit-profiles/	List all profiles
POST	/api/transit-profiles/	Create new profile
GET	/api/transit-profiles/{id}	Get profile with entries
PUT	/api/transit-profiles/{id}	Update profile
DELETE	/api/transit-profiles/{id}	Delete profile
POST	/api/transit-profiles/{id}/copy	Copy profile
GET	/api/transit-profiles/default	Get default profile

Transit Time Entries

Method	Endpoint	Description
GET	/api/transit-profiles/{id}/entries	List entries for profile
POST	/api/transit-profiles/{id}/entries	Add entry
PUT	/api/transit-profiles/{id}/entries/{entry_id}	Update entry
DELETE	/api/transit-profiles/{id}/entries/{entry_id}	Delete entry
POST	/api/transit-profiles/{id}/entries/bulk	Bulk import entries

Service Level Multipliers

Method	Endpoint	Description
GET	/api/transit-profiles/{id}/multipliers	List multipliers
POST	/api/transit-profiles/{id}/multipliers	Add/update multiplier
DELETE	/api/transit-profiles/{id}/multipliers/{service_level_id}	Delete multiplier

Rate Card Transit Times

Method	Endpoint	Description
GET	/api/transit-profiles/rate-card/{id}/transit-times	Get all transit times for rate card
GET	/api/transit-profiles/rate-card/{id}/transit-time	Get single transit time
GET	/api/transit-profiles/rate-card/{id}/overrides	List custom overrides
POST	/api/transit-profiles/rate-card/{id}/overrides	Add override
DELETE	/api/transit-profiles/rate-card/{id}/overrides/{override_id}	Delete override

Calculation

Method	Endpoint	Description
POST	/api/transit-profiles/calculate	Calculate transit time

Usage Examples

1. Create a Transit Time Profile

// POST /api/transit-profiles/
{
    "name": "Premium Metro Network",
    "description": "Faster transit times for premium customers in metro areas",
    "is_default": false
}

2. Add Transit Time Entries

// POST /api/transit-profiles/1/entries
{
    "origin_zone_id": 1,        // Sydney
    "destination_zone_id": 2,   // Melbourne
    "base_transit_hours": 20,   // Base time (before service level adjustment)
    "distance_km": 878
}

3. Configure Service Level Multipliers

// POST /api/transit-profiles/1/multipliers
{
    "service_level_id": 1,      // Express
    "transit_multiplier": 0.5,  // 50% of base time
    "adjustment_hours": 0,
    "display_priority": 1
}

Alternative Input Modes (UI Feature)

The frontend provides three input modes for entering multipliers, making it easier to configure transit times:

Mode	User Enters	System Calculates
Multiplier	0.5	Stores directly as transit_multiplier
Hours	12 (with base=24)	12 / 24 = 0.5 multiplier
Days	0.5 (with base=24)	(0.5 × 24) / 24 = 0.5 multiplier

Example: Sydney → Melbourne with 24-hour base time

Base Hours Reference: 24 hours (Standard service)

┌─────────────┬─────────────┬─────────────┬─────────────┐
│ Service     │ Multiplier  │ Hours Mode  │ Days Mode   │
│ Level       │ Mode        │             │             │
├─────────────┼─────────────┼─────────────┼─────────────┤
│ Express     │ 0.50x       │ 12 hrs      │ 0.5 days    │
│ Standard    │ 1.00x       │ 24 hrs      │ 1.0 days    │
│ Economy     │ 1.50x       │ 36 hrs      │ 1.5 days    │
└─────────────┴─────────────┴─────────────┴─────────────┘

All modes save the same multiplier value to the database.

Note: The API always receives and stores the transit_multiplier value. The Hours/Days input modes are a frontend convenience feature that auto-calculates the multiplier before saving.

4. Assign Profile to Rate Card

// PUT /api/rate-cards/123
{
    "transit_time_mode": "profile",
    "transit_time_profile_id": 1
}

5. Get Transit Time for Quote

// GET /api/transit-profiles/rate-card/123/transit-time?origin_zone_id=1&destination_zone_id=2&service_level_id=1

// Response:
{
    "success": true,
    "data": {
        "transit_hours": 10,    // 20 base × 0.5 Express multiplier
        "transit_days": 1,
        "source": "profile"
    }
}

Calculation Logic

Transit Time Formula

calculated_hours = (base_transit_hours × transit_multiplier) + adjustment_hours
calculated_days = CEIL(calculated_hours / 24)

Example Calculation

For SYD → MEL with Express service:

• Base transit: 24 hours
• Express multiplier: 0.5
• Adjustment: 0 hours

calculated_hours = (24 × 0.5) + 0 = 12 hours
calculated_days = CEIL(12 / 24) = 1 day

Resolution Priority

When getting transit time for a rate card:

1. If mode = 'none' → Return null (no transit times)
2. If mode = 'custom' → Check rate_card_transit_overrides first
3. If mode = 'profile' → Use linked transit_time_profile
4. If mode = 'inherit' or fallback → Use default profile

Migration Guide

Step 1: Run Migration Script

psql $DATABASE_URL -f migrations/create_transit_time_profiles.sql

Step 2: Create Default Profile

The migration automatically creates a “Standard Australian Network” default profile.

Step 3: Migrate Existing Transit Times

-- Insert existing zone_transit_times into the new structure
INSERT INTO transit_time_entries (profile_id, origin_zone_id, destination_zone_id, base_transit_hours)
SELECT
    (SELECT id FROM transit_time_profiles WHERE is_default = TRUE),
    origin_zone_id,
    destination_zone_id,
    transit_time * 24  -- Convert days to hours if needed
FROM zone_transit_times;

Step 4: Update Rate Cards

Existing rate cards will default to transit_time_mode = 'inherit', which uses the default profile.

Benefits

Benefit	Description
Reduced Redundancy	One profile serves multiple rate cards
Service Level Efficiency	Multipliers instead of duplicate rows
Customer Flexibility	Custom profiles or overrides per contract
Optional Feature	Rate cards can opt out of transit times
Easy Maintenance	Update one profile, affects all linked rate cards
Scalability	Add new service levels without updating every zone pair
Flexible Input	Enter multipliers as ratios, hours, or days - system auto-converts

Configuration Options Summary

Rate Card Transit Time Configuration:
  inherit:
    description: "Use the system default transit time profile"
    use_case: "Most standard rate cards"
    setup: "No additional configuration needed"

  profile:
    description: "Use a specific transit time profile"
    use_case: "Customer-specific transit commitments"
    setup: "Set transit_time_profile_id to the profile ID"

  custom:
    description: "Use rate card-specific transit time overrides"
    use_case: "One-off customer requirements"
    setup: "Add entries to rate_card_transit_overrides table"

  none:
    description: "Disable transit times for this rate card"
    use_case: "Quote-only pricing without delivery estimates"
    setup: "No additional configuration needed"

Files Reference

File	Purpose
migrations/create_transit_time_profiles.sql	Database migration
models/transit_time_profiles.py	SQLAlchemy models
api/transit_time_profiles_api.py	REST API endpoints
models/rate_cards.py	Updated rate card model

Related Documentation

• AGENTS.md - Main project documentation
• TRANSIT_TIMES_FRONTEND_GUIDE.md - Frontend UI documentation
• Rate Cards API - Rate card endpoints
• Service Levels - Service level model

---

Changelog

December 2024

• Added Input Mode Selector feature for service level multipliers
  • Multiplier mode: Direct decimal input (0.1 - 5.0)
  • Hours mode: Enter actual hours, auto-calculates multiplier
  • Days mode: Enter actual days, auto-calculates multiplier
• Added Base Hours Reference field for Hours/Days mode calculations
• Enhanced effect preview to show calculated hours alongside speed badges