From API Responses to Internal Schemas: Wilvor — Aviation Operations Intelligence Platform

Authors:

Hamza Wajid Paracha LinkedIn | GitHub

Syed Ibrahim Hamza LinkedIn | GitHub

TL;DR

In the previous phases of Wilvor, we explored aviation data sources, defined business requirements, wrote system requirements, and inspected APIs such as OpenSky, SIGMET, METAR, and TAF.

That work helped us answer an important question:

Can we access enough aviation and weather data to build a real-time aviation operations intelligence platform?

The answer was yes.

But after inspecting the APIs, we reached a more practical engineering question:

How should this raw data be shaped inside Wilvor?

External APIs are not designed around our business logic. OpenSky gives aircraft state vectors. SIGMET gives weather hazard polygons. METAR gives current airport weather. TAF gives forecast periods.

Wilvor needs something different.

It needs internal schemas that support aircraft tracking, hazard detection, airport weather status, risk scoring, recommendations, and alerts.

So instead of jumping directly into AWS, streaming pipelines, DynamoDB, and Lambda functions, we first built the internal schema locally in a Jupyter Notebook.

We treated each API DataFrame as if it were a streaming batch. Then we transformed those batches into Wilvor tables such as AircraftCurrentState, ActiveHazards, HazardCells, AircraftProjection, HazardEncounters, RiskResults, AirportStatus, AirportAssessment, Recommendations, and ActiveAlerts.

This article explains how we moved from API exploration to internal schema design, why each group of tables exists, and how this local notebook became the foundation for Wilvor’s future real-time architecture.

Introduction

After completing the API inspection phase of Wilvor, we had a better understanding of what each data source could provide.

OpenSky gave us aircraft positions, altitude, speed, heading, vertical rate, callsign, and timestamps.

SIGMET gave us aviation weather hazards, validity windows, severity, altitude ranges, movement information, raw text, and polygon coordinates.

METAR gave us current weather observations for aviation stations.

TAF gave us forecast information, but in a more complex structure because each TAF contains multiple forecast periods.

At first, it was tempting to think of the next step as simple table creation.

One API could become one table.

OpenSky could become an OpenSky table.

SIGMET could become a SIGMET table.

METAR could become a METAR table.

TAF could become a TAF table.

But that approach would only copy external API structures into our system.

That is not what Wilvor needs.

Wilvor is not just a storage system for aviation APIs. It is an Aviation Operations Intelligence Platform. The platform needs to understand aircraft movement, weather hazards, airport conditions, possible risk, and advisory recommendations.

So the real task was not to copy API responses.

The real task was to design internal schemas that Wilvor can use in real time.

Attach screenshot here: API inspection notebook showing OpenSky and SIGMET DataFrames.

Why We Built the Schemas Locally First

Before building cloud infrastructure, we decided to simulate the internal workflow locally.

This was important for a few reasons.

First, cloud implementation adds complexity. Once AWS services like Kinesis, Lambda, DynamoDB, S3, EventBridge, and SQS are involved, debugging becomes harder. We did not want to discover basic schema mistakes after deploying infrastructure.

Second, the APIs return different shapes of data. OpenSky returns aircraft state data. SIGMET returns hazard products with nested geometry. METAR and TAF require station IDs. TAF also contains nested forecast periods. If we could not normalize these locally, we would struggle even more in the cloud.

Third, internal schemas should be designed around business logic, not around API convenience.

So in the notebook, we treated the raw DataFrames as simulated streaming input.

opensky_df represented an aircraft stream.

sigmet_df represented a weather hazard stream.

METAR and TAF were called later only after we derived the required station IDs from SIGMET geography.

This gave us a local version of the future real-time pipeline.

The Core Idea

The most important idea in this phase was simple:

Raw API data should not flow through the whole platform.

Every source should be normalized into a Wilvor internal schema.

That means the API response is only the starting point.

The internal schema is what the rest of the platform will use.

For example, OpenSky gives altitude in meters and speed in meters per second. Wilvor needs altitude in feet and speed in knots because aviation operations usually reason in those units.

SIGMET gives polygon coordinates. Wilvor needs both the original polygon and H3 spatial cells so it can quickly find aircraft that may intersect a hazard.

TAF gives a raw forecast string and nested forecast periods. Wilvor needs those periods separated so it can evaluate weather around a possible arrival time.

This is why we created two kinds of tables:

API-derived tables Wilvor-generated operational tables

The API-derived tables come directly from external sources.

The Wilvor-generated tables are created by our logic. These are the tables that turn raw data into operational intelligence.

API-Derived Tables

The first group of tables came directly from the inspected APIs.

AircraftCurrentState was created from opensky_df.

This table stores the latest known state of each aircraft. It includes aircraft ID, callsign, country, position, altitude, speed, track, vertical rate, ground status, timestamps, and quality flags.

This table matters because almost everything starts from aircraft state. Projection, hazard lookup, risk scoring, and recommendations all depend on knowing where an aircraft is and how it is moving.

ActiveHazards was created from sigmet_df.

This table stores active aviation weather hazards. Each row represents one hazard product with its type, severity, valid time, altitude bounds, movement, raw text, and polygon coordinates.

HazardCoordinates was created from ActiveHazards.

The SIGMET API returns polygon coordinates as a nested list. We exploded those coordinates into one row per polygon point. This made it easier to inspect, debug, and later visualize the hazard geometry.

StationReference was created from the aviation weather station reference data.

This table is not weather data. It is a lookup table. Its purpose is to connect geography with station IDs. This was necessary because SIGMET gives polygons, but METAR and TAF require station IDs such as KJFK, KBOS, or KORD.

HazardStationCandidates was created by combining ActiveHazards and StationReference.

This table answers a very important question:

Which weather stations are inside or near a SIGMET polygon?

Once we had those station IDs, we could call the METAR and TAF APIs using comma-separated IDs.

This became the bridge between SIGMET and airport weather.

Attach screenshot here: HazardStationCandidates table showing station IDs derived from SIGMET geometry.

Using SIGMET to Fetch METAR and TAF

One issue we faced was that the METAR and TAF APIs require station IDs.

We did not want to manually pick a fixed list of airports at this stage.

Instead, we used SIGMET geography to decide which stations mattered.

The logic was:

SIGMET polygon → find stations inside or near the polygon → collect station IDs → call METAR API with comma-separated IDs → call TAF API with comma-separated IDs

This made the workflow more realistic.

In the actual platform, we should not fetch airport weather randomly. We should fetch it because there is a reason. A SIGMET affecting an area is a good reason. A high-risk aircraft needing diversion evaluation is another reason.

METAR was the raw DataFrame returned from the METAR API.

MetarLatest was the normalized current weather table created from that raw response. It stores station ID, observation time, temperature, dew point, wind, visibility, flight category, weather string, clouds, coordinates, elevation, and raw METAR text.

TAF was the raw DataFrame returned from the TAF API.

TafLatest stored the normalized TAF header, such as station ID, issue time, validity window, remarks, raw TAF, and forecast list.

TafForecastPeriods was created by exploding the nested forecast periods inside each TAF.

This table is especially important because TAF is not a single weather record. It contains multiple future periods. Wilvor needs those periods to answer questions like:

What will the weather be at this airport when the aircraft might arrive?

That makes TAF useful for diversion and airport assessment logic.

Wilvor-Generated Operational Tables

After the API-derived tables were ready, we moved to the second group: operational tables created by Wilvor logic.

These tables do not come directly from APIs.

They are the result of processing.

HazardCells was generated from ActiveHazards.

This table converts each SIGMET polygon into H3 spatial cells. It allows Wilvor to quickly find hazards near an aircraft projection. Instead of checking every aircraft against every polygon, the platform can first compare H3 cells.

This is only a filtering step. It does not replace exact geometry checks.

AircraftProjectionPoints was generated from AircraftCurrentState.

This table projects each aircraft forward using current position, speed, track, and vertical rate. Each row represents a future point on the aircraft path.

AircraftProjection summarizes those points into one projection record per aircraft. It stores the projected path, uncertainty corridor, corridor polygon, H3 cells, and validity period.

This is where raw aircraft position starts becoming operational context.

We are no longer asking only:

Where is the aircraft now?

We are asking:

Where could this aircraft be over the next few minutes?

Attach screenshot here: AircraftProjection or AircraftProjectionPoints sample output.

The First Real Merge: Aircraft and Hazards

The first major intelligence step happens when aircraft projections meet weather hazards.

AircraftHazardCandidates was created by joining aircraft projection H3 cells with hazard H3 cells.

This table finds possible aircraft-hazard matches.

But possible does not mean confirmed.

H3 is useful for speed, but it can produce false positives near cell boundaries. That is why we created the next table.

HazardEncounters performs the exact geometry stage.

It checks whether the projected aircraft corridor actually intersects the SIGMET polygon. It also checks whether the aircraft altitude overlaps the hazard altitude range.

This table turns two separate data streams into a real operational event.

An aircraft is moving.

A hazard exists.

The projection and hazard may intersect.

That is the beginning of decision intelligence.

Turning Encounters Into Risk

Finding an encounter is useful, but Wilvor also needs to explain how serious it is.

That is why we created RiskResults.

This table scores each hazard encounter using rule-based components:

Hazard type and severity Geometry relationship Time to intersection Altitude overlap Confidence

The output is not just a number.

Each risk result includes a risk level, component scores, confidence, and human-readable reasons.

This matters because operational systems should not produce mysterious recommendations.

A user should be able to understand why an aircraft was flagged and what evidence contributed to the result.

For example, a risk result may explain that the projected corridor intersects a hazard polygon, the estimated altitude overlaps the hazard altitude band, and the estimated encounter occurs within a short time window.

That is much more useful than simply showing “High Risk.”

Airport Status and Assessment

Once risk exists, Wilvor needs to evaluate possible airport options.

AirportStatus was created by combining StationReference, MetarLatest, and TafLatest.

This table gives a current weather view of each station or airport. It includes current observation data, forecast metadata, weather risk level, and flags showing whether METAR and TAF are available.

Then we created AirportAssessment.

This table evaluates possible candidate airports for aircraft affected by medium or high risk.

In this local notebook, the scoring is still simplified. It uses distance, weather risk, and placeholder values for route safety, congestion, and runway suitability.

That is acceptable for this phase.

The goal was not to finish the final recommendation engine. The goal was to prove that the internal schemas can support it.

Later, this table can be improved with runway metadata, airport closure status, congestion estimation, route hazard checks, and aircraft-specific operational constraints.

Recommendations and Alerts

The final two tables were Recommendations and ActiveAlerts.

Recommendations combines RiskResults and AirportAssessment.

It creates an advisory decision-support output. It includes the aircraft, hazard, risk level, primary action, preferred airport if available, candidate airports, reasons, limitations, and validity period.

This table is where Wilvor starts looking like a decision intelligence system rather than only a monitoring dashboard.

But the wording matters.

The recommendation is advisory.

Wilvor should not command an aircraft to land, climb, divert, or change route. The system does not know fuel endurance, aircraft performance limits, airline policy, ATC clearance, or active runway configuration.

That is why the recommendation includes limitations.

ActiveAlerts is generated from Recommendations.

This table manages alert state and deduplication. It prevents the platform from creating the same alert repeatedly when nothing important has changed.

In a real-time system, this becomes very important. Without alert deduplication, a streaming pipeline could notify users every few seconds about the same condition.

What We Learned

This phase clarified the difference between API inspection and data modeling.

API inspection tells us what fields are available.

Internal schema design tells us what the platform actually needs.

The APIs gave us raw ingredients.

The internal schemas gave us structure.

We learned that OpenSky data must become current aircraft state.

SIGMET polygons must become active hazards, coordinate rows, and H3 cells.

METAR must become latest airport weather.

TAF must become forecast headers and forecast periods.

Aircraft state must produce projections.

Projections and hazards must produce candidates.

Candidates must produce encounters.

Encounters must produce risk results.

Risk results must produce airport assessments, recommendations, and alerts.

That chain is the real Wilvor workflow.

Attach screenshot here: Final table shape summary from the notebook.

Why This Matters Before AWS

Building this locally was a valuable step because it reduced uncertainty before cloud implementation.

Now we have a clearer idea of what DynamoDB tables may be needed.

We know which tables are current-state tables.

We know which tables are generated from business logic.

We know where H3 is used.

We know where exact geometry checks are required.

We know why METAR and TAF should be fetched only when station IDs are known.

We also know which parts are still placeholders and need improvement later.

This makes the future AWS implementation more controlled.

Instead of building cloud services blindly, we can map each table and transformation to a service.

OpenSky ingestion can feed aircraft state.

SIGMET ingestion can feed active hazards and hazard cells.

METAR and TAF ingestion can be triggered from station candidates.

Projection, encounter detection, risk scoring, airport assessment, recommendation generation, and alert lifecycle can each become clear processing stages.

The notebook became a local blueprint for the real-time system.

Conclusion

This phase of Wilvor moved the project from API exploration to internal schema design.

We stopped thinking in terms of raw API responses and started thinking in terms of operational tables.

That shift matters.

Wilvor is not being designed to simply store aviation data. It is being designed to transform aircraft telemetry, weather hazards, observations, and forecasts into operational intelligence.

The internal schemas created in this notebook now give us a foundation for the next stage.

They show how raw data becomes current state.

How current state becomes projection.

How projection becomes hazard encounter.

How encounter becomes risk.

How risk becomes recommendation.

And how recommendation becomes alert.

There is still a lot to build.

The scoring logic needs refinement. Airport assessment needs stronger data. Congestion needs to be added properly. Runway and route evaluation need more work. The cloud implementation still has to be designed and deployed.

But this was an important milestone.

We now have the internal structure that Wilvor needs before it can become a real-time aviation operations intelligence platform.

The next phase will take these local schemas and map them into production-ready AWS storage, streaming, and processing components.

We began with APIs.

Now we have the data model that can turn those APIs into intelligence.

If you enjoyed this article, feel free to connect with us and follow our Wilvor Journey:

🔗 LinkedIn 💻 GitHub