What it does
You enter an FAA tail number (N-number) and AeroGuard pulls the aircraft registration from the FAA Civil Aviation Registry. From there it searches a database of FAA Service Difficulty Reports — SDRs are reports that mechanics file when they find a problem during maintenance. You can see what issues have been reported for that specific aircraft or its model.
It also cross-references applicable Airworthiness Directives for the aircraft. ADs are FAA-mandated safety inspections or fixes — if one applies to your serial number, you need to comply with it. AeroGuard checks whether the specific serial number is actually affected, not just the model in general.
Based on applicable ADs, failed-part findings, and excess wear reports, the tool gives a risk score from 0 to 100. It is meant to give you a quick sense of an aircraft's maintenance picture before you dig deeper.
The data pipeline
The raw FAA data does not come in a developer-friendly format. I downloaded SDR files and AD spreadsheets directly from FAA sources, then wrote Python scripts to parse and load them into a Supabase (Postgres) database. The main tools were httpx, openpyxl, and pymupdf — for HTTP requests, reading Excel files, and parsing PDFs.
One thing that took time was extracting hyperlinks from the AD spreadsheet. The cell values were just display text — the actual FAA document URLs were stored separately as embedded hyperlinks and had to be pulled using cell.hyperlink.target in openpyxl.
AD applicability — figuring out whether a specific serial number is actually affected — is done with local regex and heuristic pattern matching first. I built six matching strategies covering explicit serial number ranges, fleet-wide applicability, exclusion clauses, and complex hyphenated ranges. If confidence falls below 80%, the analysis falls back to Gemini 2.5 Flash Lite. This keeps it fast for clear-cut cases while still handling ambiguous ones. All scripts are idempotent, so they are safe to re-run without creating duplicate records.
Tech stack
Gemini AIVercelWhat I learned
My first parser parsed zero records
I wrote the SDR parser against a format I assumed the FAA file would have. It was wrong. I had to read the actual file line by line and rewrite the parser from scratch. Assumptions about data formats are usually wrong.
Hyperlinks in Excel are not in cell.value
The FAA AD spreadsheet had clickable links in the cells, but reading cell.value only gave me the display text. The actual URL was stored separately and had to be accessed via cell.hyperlink.target in openpyxl. Not obvious until you look at how it handles hyperlinks.
Column name mismatch between script and database
The Python script was sending camelCase field names, but the Supabase table had snake_case columns. Every insert failed. Fixed it with a schema migration. Good reminder to agree on naming conventions before you start loading data.
Anon keys and service-role keys do different things
My API routes were using the public anon key, which has Row Level Security restrictions. They were silently failing. Fixed it by creating a separate Supabase admin client with the service-role key for server-side calls only.
The Supabase Python SDK did not work
It failed to install on my Python version. I dropped it and called the PostgREST HTTP API directly using httpx instead. Turned out to be cleaner — fewer moving parts.
Calling an LLM for every record was too slow
With 50+ Airworthiness Directives per aircraft, Gemini was being called for every single one. Analysis took up to 8 minutes. I built local regex matching first — it handles the clear-cut cases instantly — and only falls back to Gemini when confidence is below 80%. Brought it down to a few seconds.
The Gemini model I used got deprecated
I had built around gemini-2.0-flash and it stopped working mid-project. Had to switch models. Always check model availability before building a hard dependency on a specific version.
Let's Connect
I’m always open to discussing math, computer science, or aviation logistics. Feel free to reach out if you’d like to collaborate.