Database & Models
The FreightFlow storage layer is built on PostgreSQL 17 using SQLAlchemy 2.0+ in fully asynchronous mode (asyncpg).
Entity Relationship
Legacy TAI-Based System
┌──────────────────┐ ┌─────────────────────────┐
│ ShipmentTai │ │ CargoesFlowShipment │
│ (tai_shipments) │ │ (cargoes_flow_shipments) │
│ │ │ │
│ • reference_no │ │ • mbl_number (unique) │
│ • MBL │ │ • booking_number │
│ • carrier │ └────────────┬─────────────┘
│ • shipper │ │
│ • office_name │ │ via mbl_number
│ • sales_reps[] │ │
└────────┬─────────┘ ┌────────────▼────────────┐
│ │ Container │
│ tai_shipment_id │ (containers) │
└─────────────────►│ │
│ • container_number │
│ • mbl_number │
│ • status │
│ • pod_terminal │
│ • terminal_status │
│ • last_free_day │
│ • is_archived │
└──┬─────────────┬─────────┘
│ │
┌────────────────▼──┐ ┌──────▼─────────────┐
│ ContainerShipment │ │ ContainerCustomHold │
│ Event │ │ │
│ │ │ • hold_type │
│ • code │ │ • description │
│ • actual_time │ └─────────────────────┘
│ • location │
│ • terminal_name │
└───────────────────┘Gravitas System (New)
┌─────────────────────────────────────────────────────────────────────────┐
│ Gravitas Data Models │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────┐ ┌─────────────────────────────┐ │
│ │ GravitasShipment │ │ GravitasContainer │ │
│ │ (gravitas_shipments) │ │ (gravitas_containers) │ │
│ │ │ │ │ │
│ │ • id (UUID PK) │◄──────────│ • id (UUID PK) │ │
│ │ • file_no │ 1:N │ • gravitas_shipment_id (FK) │ │
│ │ • mbl_number │ │ • container_number │ │
│ │ • office │ │ • mbl_number │ │
│ │ • consignee │ │ • status │ │
│ │ • oversea_agent │ │ • pod_terminal │ │
│ │ • container_number │ │ • terminal_status │ │
│ │ • shipper │ │ • last_free_day │ │
│ │ • created_at │ │ • last_return_day │ │
│ │ • updated_at │ │ • rail_last_free_day │ │
│ └─────────────────────────┘ │ • rail_last_return_day │ │
│ │ • demurrage_fee │ │
│ │ • detention_fee │ │
│ │ • is_archived │ │
│ │ • shipment_tags[] │ │
│ │ • eta │ │
│ └──────┬──────────────────────┘ │
│ │ │
│ ┌──────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────┐ │
│ │ GravitasContainerEvent │ │
│ │ (gravitas_container_events)│ │
│ │ │ │
│ │ • id (UUID PK) │ │
│ │ • container_id (FK) │ │
│ │ • code │ │
│ │ • name │ │
│ │ • actual_time │ │
│ │ • estimate_time │ │
│ │ • location │ │
│ │ • location_code │ │
│ │ • transport_mode │ │
│ │ • location_role │ │
│ │ • location_terminal_name │ │
│ └─────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘Model Details
Legacy TAI-Based Models
ShipmentTai — TAI Shipments
Source table for shipments ingested from the TAI TMS webhook.
| Column | Type | Description |
|---|---|---|
reference_number | Text | TAI shipment ID (primary identifier). |
master_bill_of_lading | Text | MBL number linking to ocean tracking. |
shipper / consignee | Text | Trade party names. |
carrier / scac_code | Text | Carrier identity. |
office_name | Text | Assigned operations office. |
sales_rep_names | Text[] | Array of assigned sales reps. |
carrier_rep | Text | Assigned carrier representative. |
status | Text | Workflow status (planned, active, etc.). |
source | Text | Origin system (tai, user). |
Container — Container Tracking
The central tracking entity, linked to both TAI and Cargoes Flow data.
| Column | Type | Description |
|---|---|---|
container_number | String(20) | ISO container number. |
mbl_number | String(100) | Master Bill of Lading. |
shipment_number | String(50) | Cargoes Flow shipment reference (TS-xxx). |
status | String(50) | Tracking status from Cargoes Flow. |
pod_terminal | String(50) | Terminal name (set by scraper). |
terminal_status | String(50) | available or not-available (set by scraper). |
last_free_day | DateTime | LFD date (set by scraper). |
is_archived | Boolean | Whether the container cycle is complete. |
Gravitas Models (New)
GravitasShipment — CSV Import Source
Stores raw shipment data imported from CSV files for Gravitas entity operations.
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key, auto-generated. |
file_no | String(100) | Internal file reference number. |
mbl_number | String(100) | Master Bill of Lading (indexed for lookup). |
office | String(100) | Operating office (defaults to "Gravitas"). |
consignee | String(200) | Consignee name from CSV. |
oversea_agent | String(200) | Overseas agent name. |
container_number | String(255) | Primary container number (optional). |
shipper | String(200) | Shipper name. |
created_at / updated_at | DateTime | Audit timestamps. |
Key Features:
- Uniqueness based on
mbl_number(upsert behavior on import) - One-to-many relationship with
GravitasContainer - CSV import via
GravitasService.import_csv()
GravitasContainer — Gravitas Container Tracking
Mirrors the legacy Container model but for Gravitas-specific operations. Includes additional fields for rail shipments and fee tracking.
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key. |
gravitas_shipment_id | UUID | Foreign key to gravitas_shipments. |
container_number | String(20) | ISO container number (indexed). |
mbl_number | String(100) | Master Bill of Lading (indexed). |
status | String(50) | Container status. |
pod_terminal | String(50) | Terminal name (from scraper). |
terminal_status | String(50) | Availability status. |
last_free_day | DateTime | Last Free Day (from scraper). |
last_return_day | DateTime | Last Return Day (from scraper). |
rail_last_free_day | DateTime | Rail-specific LFD. |
rail_last_return_day | DateTime | Rail-specific LRD. |
demurrage_fee | Numeric(10,2) | Calculated demurrage fees. |
detention_fee | Numeric(10,2) | Calculated detention fees. |
daily_fee_rate | Numeric(10,2) | Daily rate for fee calculations (default $150). |
shipment_tags | String[] | Auto-generated tags: LFD, LRD, POD awaiting, etc. |
is_archived | Boolean | Archive status. |
eta | DateTime | Estimated arrival time. |
Computed Properties:
| Property | Logic |
|---|---|
is_pod_awaiting | Discharged at destination, no full gate-out yet. |
is_pod_full_out | Container picked up full from port. |
is_completed | Empty gate-in recorded or status = completed/outdated. |
is_lfd_needed | Awaiting at port but missing LFD. |
is_lrd_needed | Gated out full but missing LRD. |
needs_manual_alert_input | Missing fees after discharge/arrival. |
is_rail_shipment | Has rail dates or rail tracking events. |
is_in_transit | Active shipment, hasn't arrived at destination. |
is_pod_awaiting_full_out_rail | Discharged from rail, not yet picked up. |
GravitasContainerEvent — Event Timeline
Tracks each milestone in a Gravitas container's journey (from Cargoes Flow API).
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key. |
container_id | UUID | Foreign key to gravitas_containers. |
code | String(100) | Event type code (e.g., dischargeFromVessel). |
name | String(200) | Human-readable event name. |
actual_time | String(100) | When event occurred. |
estimate_time | String(100) | Estimated event time. |
location | String(200) | Port/facility name. |
location_code | String(50) | Location code. |
transport_mode | String(100) | Mode of transport (vessel, rail, truck). |
location_role | String(100) | Role in shipment (originPort, destinationPort, etc.). |
location_terminal_name | String(200) | Terminal name. |
carrier_event_name | String(200) | Carrier-specific event description. |
Migrations
We use Alembic to manage schema evolution.
Generate a migration
bash
alembic revision --autogenerate -m "Add gravitas_containers table"Apply migrations
bash
alembic upgrade headIMPORTANT
In Docker, prefix commands with docker exec -it <container_name>.
Seeding
Initialize default admin and operations users:
bash
python seed_users.py