Compare airline reliability before booking a flight route

Build me an app that whoever books our team's travel opens before choosing a flight. It answers one question: on this specific route, which airline is actually reliable? Nobody runs it on a schedule, a person opens it, types a route, and reads the comparison.

The main view starts with two inputs, an origin and a destination. I want to type either a city name or an airport name, so resolve what I type into IATA codes using the AviationStack Get Airports and Get Cities reference catalogs. If what I type matches more than one airport, show me the candidates with their city and country and let me pick, rather than guessing. Once both ends resolve, show the resolved pairing clearly, for example Boston BOS to Chicago ORD, so I know exactly what is being compared.

With the pairing resolved, use AviationStack Get Routes filtered by departure and arrival airport to list every airline that operates that pairing, along with the flight numbers each one runs. This is the candidate set for the comparison.

Then pull recent completed flights for those flight numbers using AviationStack Get Flights, querying across a range of past flight_date values. From those results compute, for each airline and for each individual flight number: on-time rate, median delay in minutes, and cancellation count. Use the actual departure and arrival times against the scheduled times to determine lateness, and treat fifteen minutes past schedule as the default on-time cutoff with the cutoff configurable in settings.

This next rule is the most important part of the app and must not be optimized away. Show delay rate and cancellation rate as two separate columns. Never blend them into a single combined reliability score, star rating, letter grade, or ranking number. A carrier with mild delays but frequent cancellations is the worse booking than a carrier with moderate delays and rare cancellations, and that distinction has to stay visible to whoever is choosing. Do not add a composite score column even as a convenience or a default sort. Sorting is fine, but the user picks which single column to sort by.

Break out morning versus evening departure performance for each airline and flight number, since time of day is usually the real driver of whether a route runs on time. Split on scheduled departure time in the origin airport's local timezone, defaulting to morning before noon and evening from noon onward, with the split point configurable. Show the morning and evening figures side by side so a carrier that is punctual at 7am and unreliable at 6pm is immediately obvious.

Cache each route's computed stats rather than re-querying every time the app opens. Store the computed table per route pairing along with the date range it covers, and show a visible last refreshed timestamp at the top of the comparison. Give me an explicit refresh button that recomputes on demand. This matters because building a single route comparison means many individual flight queries, and AviationStack plans carry a monthly request quota, so re-fetching on every open would burn through it quickly. Before a refresh runs, show roughly how many requests it will take.

From the comparison results I want to shortlist a preferred option into an approved-flights tab. Selecting a row and confirming it appends that choice to a Google Sheet using Google Sheets Append Values, recording the route, airline, flight number, departure time, the delay and cancellation figures at the time of approval, who approved it, and the date. The approved-flights tab is a second view in the app that reads that sheet back with Google Sheets Get Values so the team can see what has already been signed off.

I also want a share button that posts the comparison table to our team channel using the Slack Bot Send a Message operation. Format it as readable text with the airlines, the two separate rate columns, the median delay, and the morning versus evening split, plus the route, the date range covered, and the last refreshed timestamp so nobody acts on stale numbers. Let me pick the channel.

Plan limits are load-bearing here and the app must handle them in plain language rather than failing silently. AviationStack Get Routes requires a paid plan, Basic and above, and returns a 403 function_access_restricted error on the Free plan. Historical flight lookups with a flight_date also require a paid plan. If either request comes back refused for plan reasons, do not show an empty table, show a clear message saying that comparing airlines on a route needs a paid AviationStack plan and naming which part is unavailable. Historical data also only covers a sliding three month window, so cap the comparison range at three months back, and if I ask for a longer range, clamp it and tell me the actual range used instead of returning thin data that looks complete. Where a flight number has too few completed flights in range to be meaningful, label it as low sample rather than showing a confident percentage.