Transit Times and Rate Cards: Complete Technical Relationship

This document provides a comprehensive breakdown of how transit times relate to rate cards in iDrv5-MyFR8, covering database schema, calculation logic, API endpoints, and frontend interactions.

---

Database Schema (6 Tables)

rate_cards
├── transit_time_mode       VARCHAR(20)  ('inherit'|'profile'|'custom'|'none')
├── transit_time_profile_id INTEGER FK → transit_time_profiles(id)
│
├──→ transit_time_profiles
│    ├── id, name, description, is_default, is_active
│    │
│    ├──→ transit_time_entries  (base times per zone pair)
│    │    ├── profile_id FK → transit_time_profiles
│    │    ├── origin_zone_id FK → zones
│    │    ├── destination_zone_id FK → zones
│    │    ├── base_transit_hours INTEGER
│    │    └── UNIQUE(profile_id, origin_zone_id, destination_zone_id)
│    │
│    ├──→ service_level_transit_multipliers  (speed adjustments)
│    │    ├── profile_id FK → transit_time_profiles
│    │    ├── service_level_id FK → service_levels
│    │    ├── transit_multiplier DECIMAL  (Express=0.50, Standard=1.00, Economy=1.50)
│    │    └── adjustment_hours INTEGER
│    │
│    └──→ transit_time_entry_overrides  (per-route, per-service-level)
│         ├── profile_id, origin_zone_id, destination_zone_id, service_level_id
│         └── custom_transit_hours INTEGER
│
└──→ rate_card_transit_overrides  (rate-card-specific custom times)
     ├── rate_card_id FK → rate_cards
     ├── origin_zone_id FK → zones
     ├── destination_zone_id FK → zones
     ├── service_level_id FK → service_levels (nullable = all levels)
     ├── custom_transit_hours INTEGER
     └── price_adjustment_percent DECIMAL

Additionally, rate_entries has a transit_time_hours column that stores the resolved transit time for each specific rate line.

---

Table Definitions

transit_time_profiles

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(100)	UNIQUE, NOT NULL
description	TEXT
is_default	BOOLEAN	DEFAULT FALSE (only one TRUE at a time)
is_active	BOOLEAN	DEFAULT TRUE
created_at	TIMESTAMP
updated_at	TIMESTAMP
created_by	INTEGER	FK → users(id)

Named, reusable collections of transit times that can be assigned to multiple rate cards.

transit_time_entries

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
profile_id	INTEGER	FK → transit_time_profiles, NOT NULL
origin_zone_id	INTEGER	FK → zones
destination_zone_id	INTEGER	FK → zones
base_transit_hours	INTEGER	NOT NULL
distance_km	DECIMAL(10,2)	Optional metadata
notes	TEXT
created_at	TIMESTAMP
updated_at	TIMESTAMP

Unique constraint: (profile_id, origin_zone_id, destination_zone_id)

Base transit times between zone pairs within a profile.

service_level_transit_multipliers

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
profile_id	INTEGER	FK → transit_time_profiles, NOT NULL
service_level_id	INTEGER	FK → service_levels, NOT NULL
transit_multiplier	DECIMAL(4,2)	DEFAULT 1.00, CHECK > 0
adjustment_hours	INTEGER	DEFAULT 0
display_priority	INTEGER	DEFAULT 0
created_at	TIMESTAMP
updated_at	TIMESTAMP

Unique constraint: (profile_id, service_level_id)

Example multipliers:

• Express: 0.50 (50% of base = faster)
• Standard: 1.00 (100% of base)
• Economy: 1.50 (150% of base = slower)

transit_time_entry_overrides

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
profile_id	INTEGER	FK → transit_time_profiles, NOT NULL
origin_zone_id	INTEGER	FK → zones
destination_zone_id	INTEGER	FK → zones
service_level_id	INTEGER	FK → service_levels, NOT NULL
custom_transit_hours	INTEGER	NOT NULL, CHECK > 0
notes	TEXT
created_at	TIMESTAMP
updated_at	TIMESTAMP

Unique constraint: (profile_id, origin_zone_id, destination_zone_id, service_level_id)

Overrides base_transit_hours x multiplier for specific routes. Takes precedence over multiplier calculations.

rate_card_transit_overrides

Column	Type	Constraints
id	SERIAL	PRIMARY KEY
rate_card_id	INTEGER	FK → rate_cards, NOT NULL
origin_zone_id	INTEGER	FK → zones
destination_zone_id	INTEGER	FK → zones
service_level_id	INTEGER	FK → service_levels (nullable = all levels)
custom_transit_hours	INTEGER	NOT NULL
price_adjustment_percent	DECIMAL(5,2)	Optional
notes	TEXT
created_at	TIMESTAMP
updated_at	TIMESTAMP

Unique constraint: (rate_card_id, origin_zone_id, destination_zone_id, service_level_id)

Custom transit times scoped to a specific rate card only.

rate_cards (transit-related columns)

Column	Type	Constraints
transit_time_mode	VARCHAR(20)	DEFAULT ‘inherit’, CHECK IN (‘inherit’,‘profile’,‘custom’,‘none’)
transit_time_profile_id	INTEGER	FK → transit_time_profiles(id) ON DELETE SET NULL

rate_entries (transit-related column)

Column	Type	Description
transit_time_hours	INTEGER	Stores the resolved transit time for this specific rate line

---

4 Transit Time Modes

Mode	Behavior
inherit (default)	Uses the system-wide default profile (is_default = TRUE)
profile	Uses a specific profile via transit_time_profile_id
custom	Uses rate_card_transit_overrides for this rate card only
none	Transit times disabled — returns NULL

---

Calculation Priority (Lookup Order)

For a request with (rate_card_id, origin_zone, dest_zone, service_level):

1. If rate_card.transit_time_mode = 'none'
   → Return NULL

2. If rate_card.transit_time_mode = 'custom'
   → Check rate_card_transit_overrides for (rate_card_id, origin, dest, service_level)
   → If found, use custom_transit_hours
   → Else check wildcard (service_level = NULL)
   → If still nothing, fall through to step 3

3. Determine which profile to use:
   - If mode = 'profile' AND transit_time_profile_id is set
     → Use that profile
   - Else (mode = 'inherit')
     → Use default profile (is_default = TRUE)

4. Within the selected profile:
   → Check transit_time_entry_overrides for (profile_id, origin, dest, service_level)
   → If found, use custom_transit_hours (source: 'override')
   → Else:
     - Get base_transit_hours from transit_time_entries
     - Get multiplier from service_level_transit_multipliers
     - Calculate: (base_hours × multiplier) + adjustment_hours (source: 'multiplier')

5. Return: { transit_hours, transit_days, source, calc_source }

---

API Endpoints

Rate Card Transit Times (api/rate_cards_api.py)

Method	Endpoint	Purpose
GET	/api/rate-cards/<id>/transit-times	Fetch merged transit times (custom overrides + profile defaults)
POST	/api/rate-cards/<id>/transit-times	Upsert: {origin_id, destination_id, transit_hours, service_type}
DELETE	/api/rate-cards/<id>/transit-times/<origin>/<dest>	Delete entry (optional ?service_type query param)
GET	/api/rate-cards/<id>/transit-times/export	CSV download
POST	/api/rate-cards/<id>/transit-times/import	CSV/Excel upload

Transit Time Profiles (api/transit_time_profiles_api.py)

Method	Endpoint	Purpose
GET	/api/transit-profiles/	List all profiles
POST	/api/transit-profiles/	Create new profile
GET	/api/transit-profiles/<id>	Get profile with entries and multipliers
PUT	/api/transit-profiles/<id>	Update profile
DELETE	/api/transit-profiles/<id>	Delete profile (checks for rate card usage)

POST Request Body Example

{
  "origin_id": 5,
  "destination_id": 12,
  "transit_hours": 48,
  "service_type": "Standard"
}

GET Response Structure

{
  "success": true,
  "transit_times": [
    {
      "origin_zone_id": 5,
      "origin_zone_name": "Sydney Metro",
      "destination_zone_id": 12,
      "destination_zone_name": "Melbourne Metro",
      "transit_hours": 24,
      "transit_days": 1.0,
      "service_type": "Express",
      "source": "override",
      "updated_at": "2025-03-15T10:30:00"
    }
  ]
}

---

Frontend User Interaction

Location

templates/portals/operations/rate_card_details.html — Transit Times is the 3rd tab in rate card details.

Tab Activation and Data Loading

• Transit times are lazy-loaded — data only fetches when the tab is activated
• Tab event listener calls loadTransitTimeMatrix() → GET /api/rate-cards/<id>/transit-times
• Zones are loaded in parallel via GET /api/zones for the modal dropdowns

Mode Configuration UI

• Dropdown selector at top of tab: Inherit | Use Specific Profile | Custom Overrides | Disabled
• When “Use Specific Profile” is selected, a profile picker appears
• “Save Mode” button persists the selection to the rate card record
• “Manage Profiles” link navigates to the transit time profiles management page

Transit Times Table

Displays all transit times with columns:

Origin Zone	Destination Zone	Transit Time	Service Type	Last Updated	Actions
Sydney Metro	Melbourne Metro	1 day	Express	15/03/2025	Edit / Delete

• Filter toggle: “This rate card only” checkbox filters to show only routes that have matching rate_entries
• Format display: Hours are formatted as human-readable (“1 day”, “2h 30m”, “45 minutes”)

Add Manual Entry Modal

1. Origin Zone — searchable dropdown (real-time filtering by name/code)
2. Destination Zone — searchable dropdown (validation: origin ≠ destination)
3. Service Type — Express | Standard | Economy
4. Transit Time — numeric input with unit toggle (Hours / Days)
5. Days are auto-converted to hours (× 24) before API submission

CSV Import/Export

• Download CSV: GET /api/rate-cards/<id>/transit-times/export triggers browser download
• Upload CSV/Excel: POST /api/rate-cards/<id>/transit-times/import with FormData
• Response includes imported_count for user feedback

Key JavaScript Variables

transitTimeMatrix = [];           // Loaded transit times array
zonesMap = {};                    // { zoneId: { id, code, name } }
transitTimesFilterEnabled = true; // Filter to rate card routes only
selectedOriginZone = null;        // Current modal selection
selectedDestinationZone = null;   // Current modal selection

---

Connection to Rate Computation

When computing a rate via POST /api/rate-entries/compute-rate:

1. The engine looks up the rate_entry matching (rate_card, origin_zone, dest_zone, service_level)
2. If rate_entry.transit_time_hours is populated, that value is used directly
3. Otherwise, TransitTimeService.get_transit_time() resolves it using the priority chain above
4. The resolved transit time is returned alongside the computed price, allowing the frontend to show estimated delivery time with the quote

---

Models and Service Classes

File: models/transit_time_profiles.py

Class	Purpose
TransitTimeProfile	ORM model for profiles. Methods: get_default(), get_active(), to_dict()
TransitTimeEntry	ORM model for base times. Method: get_calculated_time(service_level_id) → (hours, days, source)
ServiceLevelTransitMultiplier	ORM model for service level speed adjustments
TransitTimeEntryOverride	ORM model for profile-level route overrides
RateCardTransitOverride	ORM model for rate-card-specific overrides
TransitTimeService	Static methods: get_transit_time(), get_all_transit_times(), copy_profile()

---

Database Helper Objects

View: v_transit_times_calculated

Pre-calculated view combining profiles, entries, zones, service levels, and multipliers.

Columns: profile_id, profile_name, origin_zone_id, origin_zone_name, destination_zone_id, destination_zone_name, service_level_id, service_level_name, base_transit_hours, transit_multiplier, adjustment_hours, calculated_transit_hours, calculated_transit_days, distance_km, is_default, is_active

Function: get_rate_card_transit_time()

PostgreSQL function that returns transit time based on rate card’s transit_time_mode.

Parameters: rate_card_id, origin_zone_id, destination_zone_id, service_level_id

Returns: TABLE(transit_hours, transit_days, source)

---

Complete Entity Relationship

service_levels ─────────────────────────────┐
    │                                        │
    │ service_level_id                       │ service_level_id
    ▼                                        ▼
service_level_transit_multipliers    transit_time_entry_overrides
    │ profile_id                         │ profile_id
    ▼                                    ▼
transit_time_profiles ◄── transit_time_entries
    ▲                         │ origin_zone_id, destination_zone_id
    │ transit_time_profile_id ▼
    │                       zones
rate_cards ──────────────────▲
    │ rate_card_id           │ origin_zone_id, destination_zone_id
    ▼                        │
rate_card_transit_overrides ─┘
    │
    ▼
rate_entries (transit_time_hours stores resolved value)
    │ rate_card_id → rate_cards
    │ origin_zone_id → zones
    │ destination_zone_id → zones
    │ service_level_id → service_levels

---

File Reference

Component	File Path
DB Schema	exports/02_transport_schema.sql
Migration: Profiles	migrations/create_transit_time_profiles.sql
Migration: Overrides	migrations/add_transit_time_entry_overrides.sql
Models	models/transit_time_profiles.py
Rate Cards API	api/rate_cards_api.py (lines ~820-1040)
Profiles API	api/transit_time_profiles_api.py
Frontend Template	templates/portals/operations/rate_card_details.html
Frontend: Tab	Lines ~1406-1596 (Transit Times tab pane)
Frontend: Modal	Lines ~2413-2472 (Add Transit Time modal)
Frontend: JS	Lines ~5550-6350 (Transit time JavaScript functions)
DB Helper View	v_transit_times_calculated
DB Helper Function	get_rate_card_transit_time()