Airline Delay Dashboard

A full-stack web application for analyzing the cost per minute of delay across an airline's flight network. The dashboard models how a single departure delay propagates through passenger connections, quantifies the financial impact (in Euro) on dissatisfied passengers, and provides monthly KPI summaries for operational decision-making.

Overview

Airlines operate complex networks where a delay on one flight can cascade into dozens of late connections, missed transfers, and dissatisfied passengers. The core question this dashboard answers is:

Given a departure delay of X minutes on flight F, what is the expected cost — in Euro — considering every passenger who will miss their connection or arrive late?

The application models this with a graph-based delay propagation engine and a piecewise-linear soft-cost function calibrated to passenger dissatisfaction at increasing delay thresholds (5 min, 15 min, 30 min, 60 min, …).

Features

KPI Summary Dashboard

Monthly overview with configurable date range
Key metrics: total flights, average delay, % delayed (>15 min), total passengers, estimated delay cost
Charts: daily average delay trend, daily % delayed, delay distribution pie, estimated cost per day
Top-10 most delayed routes table

Flight Ranking

For any selected day, ranks every flight by its expected delay cost (E[Cost] = unit cost × E[dissatisfied passengers])
Configurable unit cost (Euro per dissatisfied passenger)
Horizontal bar chart of top-10 flights
Full sortable/filterable table with: flight code, cities, departure time, passenger count, transit passengers, distance, E[Delay], E[Dissatisfaction], E[Cost]

Delay Propagation Simulator

Select any flight from any day as the starting node
Set initial delay with a slider (0–300 minutes)
BFS propagation through the passenger connection graph
Interactive SVG network graph (blue edges = same aircraft rotation)
Detailed table of all downstream delayed flights with affected passenger counts

Architecture

┌──────────────────────────────────────────────────────────────┐
│  Browser                                                     │
│  React 18 + Vite  ──── /api/*  ──────────────────────────┐  │
└──────────────────────────────────────────────────────────│──┘
                                                           │
┌──────────────────────────────────────────────────────────▼──┐
│  nginx (port 80)                                            │
│  • Static React SPA                                         │
│  • Reverse-proxy /api/* → FastAPI                           │
└──────────────────────────────────────────────────────────┬──┘
                                                           │
┌──────────────────────────────────────────────────────────▼──┐
│  FastAPI  (port 8000)                                       │
│  • JWT authentication                                       │
│  • /api/v1/auth        → login / me                        │
│  • /api/v1/flights     → flight ranking                    │
│  • /api/v1/propagation → delay simulation                  │
│  • /api/v1/kpi         → monthly aggregates                │
└──────────────────────────────────────────────────────────┬──┘
                                                           │
┌──────────────────────────────────────────────────────────▼──┐
│  PostgreSQL 16  (port 5432)                                 │
│  airports · aircraft · flight_info · flight_edge · users   │
└─────────────────────────────────────────────────────────────┘

All three services run as Docker containers orchestrated with Docker Compose.

Data Model

`airports`

Column	Type	Description
`iata_code`	VARCHAR(3) PK	IATA airport code
`municipality`	VARCHAR	City name
`country`	VARCHAR	Country
`eu`	BOOLEAN	EU jurisdiction flag (affects regulation)
`latitude`, `longitude`	FLOAT	Coordinates for distance calculations

`aircraft`

Column	Type	Description
`registration`	VARCHAR(20) PK	Aircraft tail number (e.g. `LN-ABC`)
`aircraft_type`	VARCHAR	Model (737-800, A320neo, 787-9)

`flight_info`

One row per flight leg. Core table for all analytics.

Column	Type	Description
`flightcode`	VARCHAR	Flight number (e.g. `DY1234`)
`origin`, `destination`	VARCHAR(3) FK	IATA codes
`flightdate`	DATE	Scheduled date
`std_loc`, `sta_loc`	TIMESTAMP	Scheduled departure / arrival (local)
`atd_loc`, `atd_utc`	TIMESTAMP	Actual departure
`ata_utc`	TIMESTAMP	Actual arrival
`departure_delay`	FLOAT	Minutes late at departure
`buffertime`	FLOAT	Aircraft turnaround time to next scheduled leg (minutes)
`gc_km`	FLOAT	Great-circle distance in km
`blkoff`, `blkon`	FLOAT	Taxi-out / taxi-in time (minutes)
`outdeg`	INT	Number of transit connections departing from this flight
`pax_count`	INT	Transit passengers on board
`pax_number`	INT	Total passengers
`reg_aircraft`	VARCHAR FK	Aircraft registration

`flight_edge`

Represents a transit passenger connection: a group of passengers arriving on flight A at airport T, then departing on flight B from T to a final destination.

Column	Type	Description
`to_tp_flightcode`	VARCHAR	Inbound flight code (leg 1)
`to_tp_iatacode`	VARCHAR(3)	Origin of leg 1
`tp_iata`	VARCHAR(3)	Transfer airport
`dest_iata`	VARCHAR(3)	Final destination
`from_tp_flightcode`	VARCHAR	Outbound flight code (leg 2)
`sta_to_tp_loc`	TIMESTAMP	Arrival of leg 1 (local)
`std_from_tp_loc`	TIMESTAMP	Departure of leg 2 (local)
`time_on_ground`	INT	Minutes between leg 1 arrival and leg 2 departure
`pax_number_to_tp`	INT	Number of transit passengers

`users`

JWT authentication table. Admin account is seeded automatically.

Delay Propagation Algorithm

The propagation engine models the flight schedule as a directed graph where:

Nodes = individual flight legs (identified by flightcode + origin + destination + std_loc)
Edges = transit passenger connections from flight_edge

For a given initial delay d on node s:

BFS traversal from s through successor connections
For each edge (u → v) with time_on_ground = tog and mintime = 30:
- buffer = tog − mintime
- Propagated delay at v = max(delay_v, −(buffer − delay_u))
- If buffer < delay_u, the delay "absorbs" into v
The process terminates when no new downstream delays are generated

The soft-cost function f(x) converts minutes of delay into a fraction of dissatisfied passengers:

Delay threshold	Fraction dissatisfied
5 min	2 %
15 min	9 %
30 min	25 %
60 min	69 %
90 min	91 %
120 min	96 %
≥ 180 min	97–100 %

Total cost = unit_cost_Euro × Σ f(delay_i) × pax_i across all affected flights.

Statistical Prediction Model

The original project used a Keras neural network to predict the probability distribution of delay scenarios for each flight. Since the trained model file was unavailable, it was replaced with a calibrated statistical model (backend/app/core/stats_service.py).

The model reproduces the same interface: for each flight it returns a 26-element probability vector over the same scenario bins [0, 0, 0.1, …, 0.98, 1.0].

Probability of a delay scenario is estimated from:

Hour-of-day effect — Gaussian peak around 17:00 (afternoon build-up)
Buffer time — shorter aircraft turnarounds → higher propagation risk (exponential decay)
Network complexity — outdeg and pax_transit raise the probability of expensive scenarios
Day-of-week — weekends carry a +15 % uplift

Expected delay and expected cost per flight are then computed as:

E[delay] = Σ p_i × f_delay(buffertime × weight_i)
E[cost]  = Σ p_i × f_cost(buffertime × weight_i)

where weight_i = c_i / (1 − c_i) and c_i is the midpoint of bin i.

Synthetic Dataset

The database is populated with synthetic but realistic data for July 2025 via backend/scripts/01_seed_data.py.

Parameter	Value
Period	1–31 July 2025
Airports	30 (20 Europe + 10 US)
Aircraft fleet	120 (60× 737-800, 40× A320neo, 20× 787-9)
Flights per day	300 (60 aircraft × 3 legs + 60 aircraft × 2 legs)
Total flights	~9 300
Passengers per flight	150–250 (uniform random)
Transit connections	~20 % of passengers per leg
Flight speed	900 km/h
Delay distribution	Log-normal, peaks 14–20h, ~18 % of flights delayed >15 min
Long-haul routes	787-9 fleet — European hub ↔ US hub

Airports included

Europe: OSL, CPH, ARN, LHR, CDG, AMS, FRA, MAD, BCN, FCO, MXP, DUB, BRU, VIE, ZRH, ATH, LIS, HEL, WAW, MUC

United States: JFK, LAX, ORD, MIA, BOS, SFO, IAD, EWR, LAS, SEA

Tech Stack

Layer	Technology
Backend	Python 3.12, FastAPI 0.115, SQLAlchemy 2.0 (async), asyncpg
Database	PostgreSQL 16
Authentication	JWT (python-jose), bcrypt (passlib)
Data processing	NumPy, Pandas, NetworkX
Frontend	React 18, Vite 6, React Router 6
Charts	Recharts 2
HTTP client	Axios, TanStack Query 5
Icons	Lucide React
Web server	nginx 1.27 (SPA + API proxy)
Containers	Docker, Docker Compose
CI/CD	GitHub Actions → SSH → Hetzner

Project Structure

airline-web/
├── .env.example                         # Environment variable template
├── .github/
│   └── workflows/main.yml               # Deploy to Hetzner on push to main
├── docker-compose.yml                   # db + api + frontend services
│
├── database/
│   └── migrations/
│       └── 001_initial_schema.sql       # PostgreSQL schema (auto-loaded by Docker)
│
├── backend/
│   ├── Dockerfile
│   ├── requirements.txt
│   ├── scripts/
│   │   └── 01_seed_data.py              # Synthetic data generator for July 2025
│   └── app/
│       ├── main.py                      # FastAPI application entry point
│       ├── config.py                    # Settings via pydantic-settings
│       ├── database.py                  # Async SQLAlchemy engine + session
│       ├── dependencies.py              # JWT auth dependency
│       ├── models/
│       │   ├── flight.py                # ORM: Airport, Aircraft, FlightInfo, FlightEdge
│       │   └── user.py                  # ORM: User
│       ├── schemas/
│       │   ├── auth.py                  # Pydantic: LoginRequest, TokenResponse
│       │   └── flight.py                # Pydantic: ranking, propagation, KPI schemas
│       ├── core/
│       │   ├── network_service.py       # Graph loader + BFS propagation engine
│       │   ├── stats_service.py         # Statistical delay prediction model
│       │   └── utils.py                 # PiecewiseLinear, haversine, soft-cost function
│       └── api/routes/
│           ├── auth.py                  # POST /auth/login, GET /auth/me
│           ├── flights.py               # GET /flights/{day}
│           ├── propagation.py           # POST /propagation/, GET /propagation/flight-options/{day}
│           └── kpi.py                   # GET /kpi/summary
│
└── frontend/
    ├── Dockerfile
    ├── nginx.conf                       # SPA fallback + /api proxy
    ├── package.json
    ├── vite.config.js
    └── src/
        ├── main.jsx
        ├── App.jsx                      # Router: /login, /kpi, /flights, /propagation
        ├── index.css                    # Design tokens + utility classes
        ├── components/
        │   └── Layout.jsx               # Sidebar navigation
        ├── pages/
        │   ├── Login.jsx
        │   ├── KpiDashboard.jsx         # Monthly KPI overview
        │   ├── FlightRanking.jsx        # Daily flight ranking by expected cost
        │   └── DelayPropagation.jsx     # Interactive propagation simulator
        ├── services/
        │   └── api.js                   # Axios instance with JWT interceptor
        └── store/
            └── authStore.js             # React Context auth state

Getting Started

Prerequisites

Docker Desktop (includes Docker Compose)
Git

Local Development (Docker)

# Clone the repository
git clone <your-repo-url> airline-web
cd airline-web

# Copy and review environment variables
cp .env.example .env

# Build and start all services
docker compose up --build -d

The application will be available at:

Service	URL
Frontend	http://localhost:3000
API docs (Swagger)	http://localhost:8000/docs
API docs (ReDoc)	http://localhost:8000/redoc
PostgreSQL	localhost:5432

Seed the Database

On first run, populate the database with the July 2025 synthetic dataset:

# Wait ~15 seconds for PostgreSQL to be ready, then:
docker compose exec api python scripts/01_seed_data.py

The script creates:

30 airports and 120 aircraft
~9 300 flights across July 2025
Transit passenger connections
Admin user: jaoxxx3@gmail.com / pepito

Running without Docker

Backend:

cd backend

# Create and activate virtual environment
python -m venv venv
# Windows:
venv\Scripts\activate
# Linux/Mac:
source venv/bin/activate

pip install -r requirements.txt

# Set environment variables (or create a .env file in backend/)
set DATABASE_URL=postgresql+asyncpg://airline:airline@localhost:5432/airline_web
set SECRET_KEY=your-secret-key

uvicorn app.main:app --reload --port 8000

Frontend:

cd frontend
npm install
npm run dev
# Available at http://localhost:5173

The Vite dev server proxies /api/* to http://localhost:8000 automatically.

API Reference

All endpoints except /auth/login require a Bearer token in the Authorization header.

Authentication

Method	Path	Description
`POST`	`/api/v1/auth/login`	Returns JWT access token
`GET`	`/api/v1/auth/me`	Returns authenticated user info

Flight Ranking

Method	Path	Query params	Description
`GET`	`/api/v1/flights/{day}`	`unit_cost` (int, default 100)	Flights ranked by expected delay cost for a day

Response fields per flight: flight, des_orig, des_dest, std_loc, hour, pax_number, pax_transit, km, Edelay, Edissatisfaction, Ecost

Delay Propagation

Method	Path	Description
`GET`	`/api/v1/propagation/flight-options/{day}`	List all propagation-eligible flights for a day
`POST`	`/api/v1/propagation/`	Run BFS delay simulation

POST body:

{
  "day": "2025-07-15",
  "initial_node": "DY1234OSLCPHTimestamp",
  "initial_delay": 45
}

KPI Summary

Method	Path	Query params	Description
`GET`	`/api/v1/kpi/summary`	`start_date`, `end_date`	Aggregated KPIs for date range

Full interactive docs available at /docs when the API is running.

Deployment to Hetzner

The GitHub Actions workflow (.github/workflows/main.yml) deploys automatically on every push to main.

Required GitHub repository secrets:

Secret	Description
`HETZNER_HOST`	Server IP or hostname
`HETZNER_USER`	SSH username (e.g. `root`)
`HETZNER_SSH_KEY`	Private SSH key (PEM format)

Server setup (one-time, on the Hetzner machine):

# Install Docker
curl -fsSL https://get.docker.com | sh

# Clone the repository
git clone <your-repo-url> /opt/airline-web
cd /opt/airline-web

# Create production .env
cp .env.example .env
nano .env   # set a strong SECRET_KEY and POSTGRES_PASSWORD

# Start
docker compose up --build -d

# Seed data (first time only)
docker compose exec api python scripts/01_seed_data.py

After the first manual setup, every git push origin main triggers an automatic redeploy.

Environment Variables

Variable	Default	Description
`DATABASE_URL`	`postgresql+asyncpg://airline:airline@db:5432/airline_web`	Async SQLAlchemy connection string
`POSTGRES_PASSWORD`	`airline`	PostgreSQL root password
`SECRET_KEY`	(required in production)	JWT signing key — use a long random string
`FRONTEND_URL`	`http://localhost:3000`	Allowed CORS origin
`DEBUG`	`false`	Enables SQLAlchemy query logging

Security note: Change SECRET_KEY and POSTGRES_PASSWORD before deploying to production. Never commit .env to version control (it is in .gitignore).

License

MIT

Name		Name	Last commit message	Last commit date
Latest commit History 8 Commits
.claude		.claude
backend		backend
database/migrations		database/migrations
frontend		frontend
.env.example		.env.example
.gitignore		.gitignore
README.md		README.md
docker-compose.yml		docker-compose.yml

Folders and files

Latest commit

History

Repository files navigation

Airline Delay Dashboard

Table of Contents

Overview

Features

KPI Summary Dashboard

Flight Ranking

Delay Propagation Simulator

Architecture

Data Model

airports

aircraft

flight_info

flight_edge

users

Delay Propagation Algorithm

Statistical Prediction Model

Synthetic Dataset

Airports included

Tech Stack

Project Structure

Getting Started

Prerequisites

Local Development (Docker)

Seed the Database

Running without Docker

API Reference

Authentication

Flight Ranking

Delay Propagation

KPI Summary

Deployment to Hetzner

Environment Variables

License

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

`airports`

`aircraft`

`flight_info`

`flight_edge`

`users`