Transit Times — Developer Technical Guide

Technical reference for engineers working on the transit time subsystem. Complements the higher-level docs:

• TRANSIT_TIME_PROFILES_ARCHITECTURE.md — design rationale and data model overview
• TRANSIT_TIMES_FRONTEND_GUIDE.md — UI pages, modals, and user-facing flows
• ASSIGNING_TRANSIT_PROFILE_TO_RATE_CARD.md — operator walkthrough for linking profiles
• TRANSIT_TIMES_RATE_CARDS_RELATIONSHIP.md — how rate cards consume transit times

This document focuses on the code: models, API, resolution algorithm, extension points, and debugging.

---

1. Code Map

Layer	Path	Purpose
Migration (core)	migrations/create_transit_time_profiles.sql	Creates transit_time_profiles, transit_time_entries, service_level_transit_multipliers, rate_card_transit_overrides; adds transit_time_mode, transit_time_profile_id on rate_cards; creates v_transit_times_calculated view and get_rate_card_transit_time() function
Migration (overrides)	migrations/add_transit_time_entry_overrides.sql	Adds transit_time_entry_overrides — per-service-level overrides at the profile level
ORM models + service	models/transit_time_profiles.py	SQLAlchemy models and TransitTimeService
REST API	api/transit_time_profiles_api.py	Flask blueprint transit_profiles_api (prefix /api/transit-profiles)
Blueprint registration	app_factory.py:664, main.py:106, 177	Registers the API blueprint on both entry points
Portal UI route	portals/operations/routes.py:275	/operations/rate/transit-time-profiles — renders the profiles management page
UI template (profiles)	templates/portals/operations/transit_time_profiles.html	Profile management UI
UI template (rate card tab)	templates/portals/operations/rate_card_details.html	Transit Times tab inside rate card details
Seed data	scripts/seed_transit_time_data.py, import_jat_transit_times.py	Data loading scripts

---

2. Data Model

Tables

transit_time_profiles                 named, reusable collection
  ├── transit_time_entries             base hours per (origin, destination)
  ├── service_level_transit_multipliers  per-service-level multiplier + adjustment
  └── transit_time_entry_overrides     per-(route, service level) hard override

rate_cards
  ├── transit_time_mode                'inherit' | 'profile' | 'custom' | 'none'
  ├── transit_time_profile_id          FK to transit_time_profiles (for mode='profile')
  └── rate_card_transit_overrides      per-rate-card, per-route overrides (for mode='custom')

ORM classes (models/transit_time_profiles.py)

Class	Table	Key fields	Notes
TransitTimeProfile	transit_time_profiles	name (unique), is_default, is_active	get_default(), get_active() class helpers; entries, service_multipliers, rate_cards relationships with cascade='all, delete-orphan' for children
TransitTimeEntry	transit_time_entries	profile_id, origin_zone_id, destination_zone_id, base_transit_hours	Unique on (profile_id, origin, destination). Instance method get_calculated_time(service_level_id) returns (hours, days, source)
ServiceLevelTransitMultiplier	service_level_transit_multipliers	profile_id, service_level_id, transit_multiplier, adjustment_hours, display_priority	Unique on (profile_id, service_level_id). DB CHECK enforces transit_multiplier > 0
TransitTimeEntryOverride	transit_time_entry_overrides	profile_id, origin_zone_id, destination_zone_id, service_level_id, custom_transit_hours	Unique on all four keys. Takes priority over multiplier calc
RateCardTransitOverride	rate_card_transit_overrides	rate_card_id, origin_zone_id, destination_zone_id, service_level_id, custom_transit_hours, price_adjustment_percent	Unique on (rate_card, origin, destination, service_level). Used when rate card mode = 'custom'

Database-side helpers

The core migration also ships:

• View v_transit_times_calculated — cross-joins profiles × entries × service levels, left-joins multipliers, computes calculated_transit_hours and calculated_transit_days via CEIL(base * multiplier + adjustment). Useful for reports and ad-hoc SQL. Note: the view does not apply transit_time_entry_overrides; for override-aware resolution use TransitTimeService.get_transit_time() in Python.
• Function get_rate_card_transit_time(rate_card_id, origin_zone_id, destination_zone_id, service_level_id) — PL/pgSQL replica of the mode-resolution logic. Mirrors TransitTimeService.get_transit_time() but is also override-unaware. Prefer the Python service for application code.

---

3. Resolution Algorithm

TransitTimeService.get_transit_time(rate_card_id, origin_zone_id, destination_zone_id, service_level_id) in models/transit_time_profiles.py:321 is the canonical resolver.

1. Load rate card. If missing → return None.
2. mode = rate_card.transit_time_mode or 'inherit'.

3. If mode == 'none':
     return {transit_hours: None, transit_days: None, source: 'none'}

4. If mode == 'custom':
     look for a RateCardTransitOverride where
       rate_card_id matches AND origin/destination match
       AND (service_level_id matches OR is NULL)
     if found → return it (source='custom').
     Otherwise fall through to profile resolution.

5. Profile selection:
     if mode == 'profile' AND rate_card.transit_time_profile_id is set:
         profile = that profile
     else:
         profile = TransitTimeProfile.get_default()
     if no profile → return None.

6. Find the TransitTimeEntry for (profile, origin, destination).
   If missing → return None.

7. entry.get_calculated_time(service_level_id) — two-step:
     a. If a TransitTimeEntryOverride exists for
        (profile, origin, destination, service_level)
        → hours = override.custom_transit_hours  (source='override')
     b. Else look up ServiceLevelTransitMultiplier by (profile, service_level):
          hours = int(base * multiplier + adjustment_hours)   (source='multiplier')
        If no multiplier row exists → hours = base.
     days = ceil(hours / 24)  [implemented as `-(-hours // 24)`]

8. Return {transit_hours, transit_days, source, calc_source}
   where source ∈ {'profile','inherit','custom'}
   and calc_source ∈ {'override','multiplier'}.

Key invariants:

• custom mode falls back to profile/inherit if no matching override exists — it is additive, not exclusive.
• profile mode falls back to the default profile if transit_time_profile_id is null or the profile has been deleted.
• Entry-level overrides (transit_time_entry_overrides) are evaluated inside TransitTimeEntry.get_calculated_time() — they apply to profile-sourced lookups, not to rate-card custom overrides.
• Day conversion uses ceiling division: 1 hour → 1 day, 25 hours → 2 days.

---

4. API Reference

Blueprint: transit_profiles_api — URL prefix /api/transit-profiles.

Endpoints requiring @login_required are marked with 🔒. All responses follow the project convention { success: bool, data?, error?, message? }.

4.1 Profiles

Method	Path	Auth	Notes
GET	/	–	?include_inactive=true to include inactive
POST	/	🔒	Body: {name, description?, is_default?, is_active?}. Setting is_default=true clears other defaults in the same transaction
GET	/<id>	–	Returns profile with entries, service_multipliers, and entry_overrides (keyed "{origin}_{destination}"). Uses joinedload on entries and zones
PUT	/<id>	🔒	Partial update. Duplicate name → 409
DELETE	/<id>	🔒	Refuses if is_default or if any rate card references the profile
POST	/<id>/copy	🔒	Body: {name, description?}. Delegates to TransitTimeService.copy_profile — clones entries, multipliers, and entry overrides
GET	/default	–	Returns default profile with entries and multipliers
POST	/calculate	🔒	Body: {origin_zone_id, destination_zone_id, service_level_id, profile_id?}. Stand-alone calculation without a rate card

4.2 Entries (base times)

Method	Path	Auth	Notes
GET	/<profile_id>/entries	–
POST	/<profile_id>/entries	🔒	Body: {origin_zone_id, destination_zone_id, base_transit_hours, distance_km?, notes?}. Duplicate zone pair → 409
PUT	/<profile_id>/entries/<entry_id>	🔒	Updates base_transit_hours, distance_km, notes only
DELETE	/<profile_id>/entries/<entry_id>	🔒
POST	/<profile_id>/entries/bulk	🔒	Body: {entries: [...]}. Per-row errors are collected, not fatal. Commits at end

4.3 Service Level Multipliers

Method	Path	Auth	Notes
GET	/<profile_id>/multipliers	–
POST	/<profile_id>/multipliers	🔒	Upsert — same endpoint creates or updates based on (profile_id, service_level_id). Returns 201 in both cases
DELETE	/<profile_id>/multipliers/<service_level_id>	🔒	Path is service_level_id, not multiplier row id

4.4 Profile Entry Overrides (per-service-level)

Method	Path	Auth	Notes
GET	/<profile_id>/overrides	–	?origin_zone_id=&destination_zone_id= optional filters
POST	/<profile_id>/overrides	🔒	Upsert on (profile, origin, destination, service_level)
PUT	/<profile_id>/overrides/<override_id>	🔒
DELETE	/<profile_id>/overrides/<override_id>	🔒	Route falls back to multiplier calculation
POST	/<profile_id>/overrides/bulk	🔒	Bulk upsert for CSV/data imports

4.5 Rate Card Transit Times

Method	Path	Auth	Notes
GET	/rate-card/<rate_card_id>/transit-times	–	Returns resolved list via TransitTimeService.get_all_transit_times
GET	/rate-card/<rate_card_id>/transit-time	–	Query: ?origin_zone_id=&destination_zone_id=&service_level_id=. All three required
GET	/rate-card/<rate_card_id>/overrides	–	Lists rate_card_transit_overrides rows
POST	/rate-card/<rate_card_id>/overrides	🔒	Body: {origin_zone_id?, destination_zone_id?, service_level_id?, custom_transit_hours, price_adjustment_percent?, notes?}
DELETE	/rate-card/<rate_card_id>/overrides/<override_id>	🔒

Response shape notes

• GET /<id> on a profile returns entry_overrides as an object keyed "{origin_zone_id}_{destination_zone_id}" with arrays of override dicts — not a flat list. Frontend expects this shape when rendering the entry-overrides column.
• POST /<profile_id>/multipliers returns 201 even on update. Callers should not treat 201 as “newly created”.
• TransitTimeService.get_transit_time returns {transit_hours: None, transit_days: None, source: 'none'} (not None) when mode is 'none'. Null-check by inspecting transit_hours rather than the whole object.

---

5. Blueprint Registration

Registered on both entry points:

# main.py:106, 177
from api.transit_time_profiles_api import transit_profiles_api
app.register_blueprint(transit_profiles_api)

# app_factory.py:664
from api.transit_time_profiles_api import transit_profiles_api
app.register_blueprint(transit_profiles_api)

The portal-facing UI is served by portals/operations/routes.py:275:

@operations_portal.route('/rate/transit-time-profiles')
@login_required
def transit_time_profiles():
    return render_template('portals/operations/transit_time_profiles.html', ...)

Any new endpoint added to api/transit_time_profiles_api.py will be auto-registered with the blueprint. No blueprint changes required.

---

6. Programmatic Usage Recipes

6.1 Look up a transit time for a booking/quote

from models.transit_time_profiles import TransitTimeService

result = TransitTimeService.get_transit_time(
    rate_card_id=rate_card.id,
    origin_zone_id=origin.id,
    destination_zone_id=dest.id,
    service_level_id=service_level.id,
)

if result is None or result.get('transit_hours') is None:
    eta_text = 'ETA unavailable'
else:
    eta_text = f"{result['transit_days']} day(s)"

6.2 Clone a profile for a customer-specific variant

from models.transit_time_profiles import TransitTimeService

new_profile = TransitTimeService.copy_profile(
    source_profile_id=default_profile.id,
    new_name='ACME Corp — Express Metro',
    new_description='Per 2026 contract; 18h SYD↔MEL',
)
# Caller should then POST overrides or adjust entries as needed.

copy_profile does its own db.session.commit(). Do not wrap it in an outer transaction you intend to roll back.

6.3 Set a rate card to “quote only” (no ETAs)

rate_card.transit_time_mode = 'none'
rate_card.transit_time_profile_id = None
db.session.commit()

6.4 Define a per-route custom transit for one rate card

from models.transit_time_profiles import RateCardTransitOverride

override = RateCardTransitOverride(
    rate_card_id=rate_card.id,
    origin_zone_id=syd.id,
    destination_zone_id=mel.id,
    service_level_id=express.id,  # or None to match any service level
    custom_transit_hours=18,
    notes='Per contract 2026-04',
)
db.session.add(override)
rate_card.transit_time_mode = 'custom'
db.session.commit()

When service_level_id is NULL, the override acts as a fallback for any service level on that route (see TransitTimeService.get_transit_time step 4).

6.5 Pin a specific profile route to a fixed time

Use an entry override (profile-level) instead of a rate-card override — it applies to every rate card that inherits/links to the profile:

from models.transit_time_profiles import TransitTimeEntryOverride

db.session.add(TransitTimeEntryOverride(
    profile_id=profile.id,
    origin_zone_id=mel.id,
    destination_zone_id=syd.id,
    service_level_id=express.id,
    custom_transit_hours=10,
    notes='Dedicated Mel-Syd Express lane',
))
db.session.commit()

---

7. Extension Points

Adding a new transit_time_mode

1. Add the value to the CHECK constraint:

   ALTER TABLE rate_cards DROP CONSTRAINT rate_cards_transit_time_mode_check;
   ALTER TABLE rate_cards
     ADD CONSTRAINT rate_cards_transit_time_mode_check
     CHECK (transit_time_mode IN ('inherit','profile','custom','none','<new>'));

2. Handle the new branch in TransitTimeService.get_transit_time and get_all_transit_times in models/transit_time_profiles.py.
3. Mirror the logic in the PL/pgSQL function get_rate_card_transit_time if downstream reports depend on it.
4. Update the mode dropdown in templates/portals/operations/rate_card_details.html and the frontend mode-handling JS.
5. Update the mode table in TRANSIT_TIME_PROFILES_ARCHITECTURE.md and the frontend guide.

Adding a new field to a profile entry

1. Add the column via a new migration under migrations/.
2. Add to the SQLAlchemy model in models/transit_time_profiles.py.
3. Extend to_dict() so API responses expose it.
4. Update POST/PUT handlers in api/transit_time_profiles_api.py to accept it.
5. Update the frontend form(s) in transit_time_profiles.html.

Adding a new service-level multiplier concept (e.g., weekend adjustment)

Prefer extending ServiceLevelTransitMultiplier with an optional column and a matching branch in TransitTimeEntry.get_calculated_time. Do not introduce a parallel multiplier table — the resolution algorithm would need to be duplicated.

---

8. Local Development

Apply migrations

psql $DATABASE_URL -f migrations/create_transit_time_profiles.sql
psql $DATABASE_URL -f migrations/add_transit_time_entry_overrides.sql

Both are idempotent (CREATE TABLE IF NOT EXISTS, ON CONFLICT DO NOTHING on seed rows).

Seed data

python scripts/seed_transit_time_data.py     # sample profile + entries
python import_jat_transit_times.py            # JAT production dataset

Quick smoke test

# Assumes app is running on :5002 and you are logged in via cookies.txt
curl -s http://localhost:5002/api/transit-profiles/default | jq .
curl -s "http://localhost:5002/api/transit-profiles/rate-card/1/transit-time?origin_zone_id=1&destination_zone_id=2&service_level_id=1" | jq .

---

9. Debugging

”Transit time not found” on a valid-looking route

Walk the resolution algorithm manually:

-- 1. Which mode is the rate card in?
SELECT id, name, transit_time_mode, transit_time_profile_id
FROM rate_cards WHERE id = <rate_card_id>;

-- 2. If mode='custom', is there a matching override?
SELECT * FROM rate_card_transit_overrides
WHERE rate_card_id = <rate_card_id>
  AND origin_zone_id = <o> AND destination_zone_id = <d>
  AND (service_level_id = <sl> OR service_level_id IS NULL);

-- 3. Which profile will be used?
--    mode='profile' → transit_time_profile_id
--    else          → the default profile
SELECT * FROM transit_time_profiles WHERE is_default = TRUE AND is_active = TRUE;

-- 4. Is there a base entry for this zone pair in that profile?
SELECT * FROM transit_time_entries
WHERE profile_id = <pid>
  AND origin_zone_id = <o> AND destination_zone_id = <d>;

-- 5. Entry-level override for this service level?
SELECT * FROM transit_time_entry_overrides
WHERE profile_id = <pid> AND origin_zone_id = <o>
  AND destination_zone_id = <d> AND service_level_id = <sl>;

-- 6. Multiplier for this service level?
SELECT * FROM service_level_transit_multipliers
WHERE profile_id = <pid> AND service_level_id = <sl>;

If steps 4 and 6 both return rows but the API still 404s, verify you are not hitting the 'none' mode branch — it returns a non-null dict with transit_hours = None, which some callers render as “not found”.

Cross-check calculation

SELECT * FROM v_transit_times_calculated
WHERE profile_id = <pid>
  AND origin_zone_id = <o> AND destination_zone_id = <d>
  AND service_level_id = <sl>;

If this view disagrees with the API: the view is override-unaware. Check transit_time_entry_overrides — the API applies them, the view does not.

Deleting a profile fails

DELETE /api/transit-profiles/<id> refuses when:

• profile.is_default is TRUE
• profile.rate_cards is non-empty

Either unset default/reassign rate cards first, or delete in SQL (cascades will drop entries, multipliers, and entry overrides).

Multiplier upsert returns 201 twice

Expected — POST /<profile_id>/multipliers always returns 201, whether it inserted or updated. Use the response message field ("Multiplier created successfully" vs "Multiplier updated successfully") to distinguish.

---

10. Gotchas

• Ceiling vs. floor days. TransitTimeEntry.to_dict() returns base_transit_days = base_transit_hours / 24 (float, no ceiling). get_calculated_time returns days = ceil(hours / 24) (int). Don’t mix them in the same display.
• calc_source vs. source. source tells you where the time came from at the rate-card level (profile / inherit / custom / none); calc_source tells you how the number was produced at the entry level (override / multiplier). Both are present on resolved results and are not interchangeable.
• Cascades. Deleting a profile cascades to entries, multipliers, and entry overrides (ON DELETE CASCADE). Zones are ON DELETE SET NULL — a deleted zone leaves orphan entries with NULL zone ids rather than disappearing rows. Watch for this in reports.
• custom mode fall-through. Setting mode='custom' without adding overrides silently behaves like inherit for missing routes. This is intentional (partial custom configs) but can surprise testers.
• SQL function drift. get_rate_card_transit_time() (PL/pgSQL) predates transit_time_entry_overrides and does not apply them. Treat the Python service as the source of truth.
• copy_profile commits. It finalizes its own transaction — don’t call it inside a block you plan to roll back.

---

11. Related Reading

• AGENTS.md — project conventions, DB connection, date handling
• models/rate_cards.py — RateCard model with transit_time_mode, transit_time_profile_id, and transit_overrides back-ref
• models/service_level.py — ServiceLevel used in multipliers and overrides
• api/rate_cards_api.py — rate card CRUD (accepts transit mode fields on update)