An algorithmic freight routing and demurrage penalty engine that tracks container lifecycles via state-machine data modeling, calculates financial exposure from port dwell time, and visualizes results in a Logistics Command Center dashboard.
Shipping containers sitting in ports too long accumulate demurrage (penalty) fees worth millions. This system answers: which routes are failing, and what is the exact financial exposure?
Raw EDI Events → PostgreSQL (raw layer) → dbt (staging → intermediate → marts) → Python Rules Engine (tiered penalty calculation) → Tableau Dashboard (Logistics Command Center)
- State Machine Data Modeling — containers follow a strict lifecycle: EMPTY_DISPATCHED → GATE_IN_FULL → VESSEL_DEPARTED → VESSEL_ARRIVED → DISCHARGED → GATE_OUT_FULL → EMPTY_RETURNED. Invalid transitions are flagged and excluded from financial calculations.
- Window Functions — LAG/LEAD used to sequence events and detect out-of-order EDI messages.
- Tiered Rules Engine — Python engine applies carrier-specific free time and tiered daily penalty rates.
- Strategy Pattern — port region strategies (US, EU, Asia) encapsulate region-specific demurrage rules.
- dbt Pipeline — full staging → intermediate → marts transformation layer with schema tests and custom data quality assertions.
| Layer | Technology |
|---|---|
| Database | PostgreSQL 15 (Docker) |
| Transformation | dbt-postgres |
| Rules Engine | Python 3.11, Pydantic |
| Orchestration | Python subprocess DAG |
| Visualization | Tableau Public |
| Testing | pytest, dbt tests |
Transit-State-Engine/
│
├── ingestion/
│ ├── __init__.py
│ ├── edi_parser.py # Parses raw EDI 315 messages
│ ├── normalizer.py # Standardizes and validates events
│ ├── deduper.py # Removes duplicate events
│ └── synthetic_generator.py # Generates realistic test EDI data
│
├── warehouse/
│ └── sql/
│ ├── 01_schema.sql # Raw, staging, marts, reference schemas
│ ├── 02_seed_ports.sql # Reference ports and containers
│ ├── 03_seed_carrier_rules.sql # Demurrage tariff rules (JSONB)
│ └── 04_indexes.sql # Performance indexes + Tableau view
│
├── dbt/
│ ├── models/
│ │ ├── staging/
│ │ │ ├── stg_events.sql # Cleaned raw events
│ │ │ ├── stg_containers.sql
│ │ │ └── schema.yml
│ │ ├── intermediate/
│ │ │ ├── int_events_ordered.sql # LAG/LEAD window functions
│ │ │ ├── int_state_transitions.sql # State machine validation
│ │ │ └── int_trip_lifecycle.sql # Dwell time calculation
│ │ └── marts/
│ │ ├── fct_trips.sql # Final trip fact table
│ │ └── schema.yml
│ ├── macros/
│ │ └── state_machine.sql # Reusable valid transitions macro
│ ├── tests/
│ │ ├── assert_dwell_positive.sql
│ │ ├── assert_no_orphan_trips.sql
│ │ └── assert_lifecycle_complete.sql
│ ├── dbt_project.yml
│ └── profiles.yml
│
├── rules_engine/
│ ├── __init__.py
│ ├── engine.py # Core tiered demurrage calculator
│ ├── runner.py # Connects engine to database
│ ├── models.py # Pydantic data models
│ ├── tariffs.py # Tariff lookup and resolution
│ ├── currency.py # Currency conversion utilities
│ ├── state_machine.py # Python state machine definition
│ └── strategies/
│ ├── __init__.py # Strategy registry
│ ├── base.py # Abstract base strategy
│ ├── us_ports.py # US port rules (calendar days)
│ ├── eu_ports.py # EU port rules (weekends excluded)
│ └── asia_ports.py # Asia port rules (all calendar days)
│
├── tests/
│ ├── test_rules_engine.py # Unit tests for engine and state machine
│ ├── test_dedup.py
│ ├── test_state_machine.py
│ ├── test_dwell_calc.py
│ └── test_edge_cases.py
│
├── orchestration/
│ └── dag.py # End-to-end pipeline orchestrator
│
├── tableau/
│ ├── logistics_command_center.twb # Tableau dashboard workbook
│ ├── demurrage_export.csv # Exported data for Tableau
│ ├── calculated_fields.md # Tableau calculated field specs
│ └── datasource_design.md # Dashboard design blueprint
│
├── docker-compose.yml
├── pyproject.toml
├── .env.example
└── README.md
- Docker Desktop
- Python 3.11+
- dbt-postgres
- Tableau Public
# Clone the repo
git clone <repo-url>
cd Transit-State-Engine
# Create virtual environment
python -m venv .venv
.venv\Scripts\activate
# Install dependencies
pip install psycopg2-binary python-dotenv pydantic dbt-postgres pytest
# Copy environment file and fill in values
copy .env.example .env
# Start the database
docker-compose up -d
# Run the database schema and seed data
docker exec -i transit_warehouse psql -U freight_admin -d transit_warehouse < warehouse/sql/01_schema.sql
docker exec -i transit_warehouse psql -U freight_admin -d transit_warehouse < warehouse/sql/02_seed_ports.sql
docker exec -i transit_warehouse psql -U freight_admin -d transit_warehouse < warehouse/sql/03_seed_carrier_rules.sql
docker exec -i transit_warehouse psql -U freight_admin -d transit_warehouse < warehouse/sql/04_indexes.sql
python orchestration/dag.py
# Python unit tests
python -m pytest tests/ -v
# dbt data quality tests
dbt test --project-dir dbt --profiles-dir dbt
The Tableau Logistics Command Center shows:
- Total Portfolio Exposure — KPI card showing total demurrage cost across all containers
- Exposure by Port — which ports are generating the most penalties
- Container Status — breakdown of lifecycle states across the fleet
- Carrier Breach Status — which carriers have the most breached containers
- Port Bottleneck Map — geospatial view of exposure concentration by port
- Dwell vs Exposure — scatter plot showing relationship between dwell time and penalty cost
| Container | Port | Dwell Days | Chargeable Days | Exposure |
|---|---|---|---|---|
| MSCU2222222 | Singapore | 14 | 11 | $3,600 |
| MAEU3333333 | Rotterdam | 12 | 8 | $1,100 |
| MSCU1111111 | Singapore | 8 | 5 | $900 |
| MAEU2222222 | Rotterdam | 6 | 2 | $350 |
| Total | $5,950 |