AeroTrack AI

A full-stack, predictive maintenance platform that turns live flight data into proactive schedules, preventing costly aircraft delays before they happen.

Comment

✈️ AeroTrack AI

Inspiration

The aviation industry loses billions of dollars annually to unscheduled maintenance and flight delays. Traditional maintenance approaches are predominantly reactive, addressing failures after they occur, or rely on rigid schedules that waste resources.

We saw an opportunity to transform this paradigm. Leveraging Fivetran and Google Cloud's AI suite, we built a platform that goes beyond simple aircraft tracking to forecast future maintenance needs, enabling managers to schedule interventions before problems arise. The result: reduced costs, minimized downtime, and enhanced safety.

What It Does

AeroTrack AI is a comprehensive full-stack solution for airline logistics managers, built on a complete, automated data pipeline.

Core Pipeline

  • A custom Fivetran connector continuously pulls live flight data into BigQuery
  • A Vertex AI model forecasts future flight hours with predictive accuracy
  • Scheduled SQL queries automatically populate maintenance schedules

Manager Interfaces

Web Dashboard (Next.js)
Managers get a unified view of their entire fleet, monitor real-time flight status, and review aircraft predictively scheduled for maintenance.

Conversational AI Agent (Gemini)
Embedded directly in the dashboard, managers can ask natural language questions like "Which planes need an A-Check?" The AI agent queries live BigQuery tables and delivers instant, accurate responses.

How We Built It

AeroTrack AI is architected as a fully automated, end-to-end pipeline with strategic implementation of sponsor technologies.

Data Ingestion (Fivetran)

We developed a custom Fivetran Connector using the Python SDK that pulls live flight data from the OpenSky Network API and upserts it into BigQuery.

Warehousing & Transformation (BigQuery)

BigQuery serves as the project's backbone. Raw data lands in a live_flights table, then a Scheduled Query (sql/1_create_training_data.sql) aggregates it into a clean, time-series daily_flight_hours table.

Predictive AI (Vertex AI)

The daily_flight_hours table feeds into Vertex AI AutoML to train a time-series forecasting model. Daily Vertex AI Batch Prediction jobs forecast future flight hours and write results to new predictions_* tables in BigQuery.

Scheduling Logic (BigQuery)

A second Scheduled Query (sql/2_populate_maintenance_schedule.sql) identifies the latest predictions and intelligently inserts tasks into the maintenance_schedules table. We implemented a robust INSERT ... WHERE NOT EXISTS pattern to ensure demo reliability.

Application Layer (Next.js & Vertex AI)

Managers interact through a Next.js frontend embedding a Vertex AI Agent (Gemini) connected to a BigQuery Data Store. This enables the agent to generate SQL dynamically and answer natural language questions about live_flights and maintenance_schedules tables in real time.

Challenges We Overcame

Our primary challenge was building a reliable scheduling query. The initial MERGE statement failed silently during testing, but upon closer inspection, the logic was actually too effective. It correctly determined that no new maintenance tasks were needed since our demo flight data (e.g., 5 hours) had already exceeded the demo thresholds (e.g., 2 hours).

We pivoted to a more robust, demo-friendly INSERT ... WHERE NOT EXISTS approach, which proved significantly more stable. A classic real-world data engineering challenge.

Accomplishments

We're proud to have delivered a complete, end-to-end, full-stack AI application within a compressed timeframe. This isn't just a prototype — it's a production-ready pipeline. From our custom Fivetran connector ingesting live data, through an AI model generating predictions, to a Gemini-powered conversational agent querying those predictions, every component is seamlessly integrated and automated.

Watching the AI agent dynamically generate SQL and query our maintenance_schedules table was a breakthrough moment for our team.

What's Next

Our roadmap focuses on data enrichment and model sophistication. We plan to integrate additional Fivetran connectors for weather data, flight schedules, and FAA maintenance directives.

This expanded data foundation will enable AeroTrack AI to evolve beyond simple flight-hour predictions to forecast component-level maintenance requirements for engines, landing gear, and avionics based on actual operating conditions and environmental factors.

Built With

Share this project:

Updates