Aero Pulse

Airline fare and inventory analytics for the Bangladesh market. It collects public fares from airline and OTA websites, stores cycle snapshots in PostgreSQL, builds monitoring reports, and serves results through a FastAPI API and a Next.js dashboard. Forecasting with CatBoost, LightGBM and an MLP is experimental.

Live dashboard: https://aviation-inventory-pricing-intellig.vercel.app

Pipeline:

Airline + OTA collection -> normalization -> PostgreSQL cycle snapshots -> Excel/report outputs -> ML/DL forecasting -> BigQuery hot cache -> FastAPI -> Next.js web app

A personal project, built with AI-assisted development.

What This Project Does

This project captures flight offer data from airline-direct and OTA channels, stores normalized cycle snapshots in local PostgreSQL for operational collection/training, generates operational Excel workbooks, and publishes a bounded recent slice plus ML/DL outputs into BigQuery for hosted reads and current BI.

Current implemented scope:

• cycle-based multi-airline accumulation
• route/flight fare monitoring reports in .xlsx and .xlsm
• OTA benchmarking support for interim carrier coverage
• penalty and tax comparison sheets
• ML/DL prediction pipeline with optional catboost, lightgbm, and mlp
• FastAPI reporting API for the web phase
• BigQuery-backed hosted hot-cache analytics, forecasting, and BI handoff

Core project decisions and operating policy live in:

• PROJECT_CONTEXT.md - Single new-chat handoff, current architecture snapshot, decisions, history, and update log
• PROJECT_DECISIONS.md
• OPERATIONS_RUNBOOK.md
• docs/WEB_PLATFORM_PLAN.md
• warehouse/bigquery/README.md

Main Entry Points

• run_pipeline.py End-to-end accumulation + reports + optional prediction/intelligence steps.

• run_all.py Core collection, normalization, and persistence runner.

• generate_reports.py Standard reporting pack generator.

• generate_route_flight_fare_monitor.py Dedicated route-flight fare monitor workbook, including macro-enabled .xlsm export.

• predict_next_day.py Forecasting layer for event and numeric target prediction.

• apps/api/README.md Reporting API scaffold with BigQuery-first hosted reads and optional PostgreSQL transitional endpoints.

Data Flow

1. Collect airline-direct and OTA offers through channel-specific connectors.
2. Normalize into PostgreSQL tables such as flight_offers and flight_offer_raw_meta.
3. Group parallel airline runs into one shared cycle_id.
4. Compare current vs previous cycle snapshots.
5. Generate operational Excel outputs, API-ready reporting views, and forecasting artifacts.
6. Write extraction health artifacts so source quality is measured separately from process exit.
7. After successful pipeline runs with extraction health not FAIL, export/load a rolling recent capture window into BigQuery for hosted reads and current BI.

Target Platform Split

• Operational collection and training: local PostgreSQL + Python pipeline.

• Hosted application: FastAPI reporting API + Next.js frontend on top of BigQuery curated reads.

• Historical analytics: local PostgreSQL, database backups, and ignored Parquet exports.

• Current hosted BI: bounded BigQuery hot-cache tables + Looker Studio dashboards.

• Excel: Keep as export and delivery format, not as the primary interactive analysis surface.

Environment Setup

Windows / PowerShell oriented setup:

py -3.14 -m venv .venv
.\.venv\Scripts\python.exe -m pip install --upgrade pip setuptools wheel
.\.venv\Scripts\python.exe -m pip install -r requirements.txt

If browser-assisted flows are needed:

.\.venv\Scripts\python.exe -m playwright install chromium

Database configuration is resolved from either:

• AIRLINE_DB_URL
• or DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD

See core/runtime_config.py.

Common Commands

Run the full pipeline:

.\.venv\Scripts\python.exe run_pipeline.py --route-monitor --route-monitor-macro-xlsm

Automatic BigQuery sync now runs after a successful run_pipeline.py execution when:

• BIGQUERY_SYNC_ENABLED=1 is set or --bigquery-sync-enabled is passed
• BIGQUERY_PROJECT_ID is configured
• BIGQUERY_DATASET is configured
• --skip-bigquery-sync is not used
• extraction health is not FAIL

Useful controls:

.\.venv\Scripts\python.exe run_pipeline.py --bigquery-sync-enabled --bigquery-sync-lookback-days 2 --bigquery-load-mode partition-refresh
.\.venv\Scripts\python.exe run_pipeline.py --skip-bigquery-sync
.\.venv\Scripts\python.exe run_pipeline.py --fail-on-bigquery-sync-error
.\.venv\Scripts\python.exe run_pipeline.py --fail-on-extraction-gate

Extraction health and preflight:

.\.venv\Scripts\python.exe tools\pre_flight_session_check.py --dry-run
Get-Content output\reports\extraction_health_latest.md

Temporarily disable a supplier/source by editing config/source_switches.json:

{
  "sources": {
    "sharetrip": { "enabled": false, "reason": "temporary upstream issue" }
  }
}

The source switch file is applied by run_pipeline.py, run_all.py, the parallel airline runner, preflight checks, and nested OTA fallback calls. SHARETRIP_ENABLED=false is still supported as a legacy ShareTrip-only override.

$env:SHARETRIP_ENABLED="false"
.\.venv\Scripts\python.exe run_pipeline.py --skip-bigquery-sync

Manual operational run on a laptop:

cmd /c scheduler\run_ingestion_4h_once.bat

Scheduler timing:

Edit config/schedule.json -> scheduler_timing.

This is the beginning of the README. Read the rest at the original. Read the full README ↗