--- license: cc0-1.0 pretty_name: "OpenAirDocs: FAA and NTSB aviation data" language: - en tags: - aviation - faa - ntsb - airworthiness-directives - aircraft-registry - aviation-safety - aircraft-accidents size_categories: - 10M Unofficial compilation of public FAA and NTSB data. Only the Federal Register edition published on govinfo.gov is the official legal text of an airworthiness directive. Extracted fields (applicability, product pairing, registry matches) are aids for finding ADs, not compliance determinations. The geo_ tables' points are openair's, placed from public-domain place lists; the agencies' own columns are unchanged. ## Where to get it - **[openairdocs.org](https://openairdocs.org)** has a page for every record, built from these files. - **Every file of this release**, the SQLite and DuckDB databases included, is at `https://openairdocs.org/data/releases/2026-10-22/`. `https://openairdocs.org/data/latest/` always serves the newest release. The site keeps recent releases only. - **[Hugging Face](https://huggingface.co/datasets/openairdocs/openairdocs)** holds the Parquet tables, this README, `manifest.json` and `DATA_DICTIONARY.md` for every release: each release is a tag (`2026-10-22`), and `main` is the newest. ## Files | File | Rows | Contents | |---|---|---| | `faa_directive.parquet` | 20,326 | One row per FAA airworthiness directive (AD). | | `faa_directive_section.parquet` | 285,501 | AD text split into sections: preamble sections and the AD's lettered paragraphs. | | `faa_directive_product.parquet` | 159,851 | Each model an AD names, paired with its maker and type certificate where that can be told. | | `faa_directive_relation.parquet` | 3,235 | Links between ADs: from_ad_id supersedes or affects (revises, amends) to_ad_id. | | `faa_proposed_directive.parquet` | 10,790 | Proposed ADs: notices of proposed rulemaking (NPRMs), supplemental NPRMs and withdrawals. | | `faa_saib.parquet` | 1,338 | FAA Special Airworthiness Information Bulletins: advisory safety information, not mandatory. | | `faa_saib_product.parquet` | 15,603 | Each model a SAIB names, paired with its maker and type certificate the same way as faa_directive_product. | | `faa_concern_sheet.parquet` | 95 | FAA airworthiness concern sheets (ACS): requests for information on a safety concern, issued before the FAA decides whether an AD is needed. | | `faa_advisory_circular.parquet` | 1,687 | FAA advisory circulars: guidance on acceptable means of compliance, one row per version (revision or change) DRS holds. | | `faa_advisory_circular_page.parquet` | 74,212 | The text of each AC, one row per PDF page: the unit to search, quote and link to (open the mirrored PDF at #page=N). | | `faa_directive_ac.parquet` | 361 | Advisory circulars cited in the text of an AD, by AC family. | | `faa_type_certificate.parquet` | 2,257 | FAA type certificate data sheets (TCDS), latest revision of each. | | `faa_type_certificate_model.parquet` | 8,536 | Models listed on each type certificate data sheet. | | `faa_stc.parquet` | 49,234 | FAA supplemental type certificates (STCs): approved design changes to aircraft, engines and propellers, one row per STC number. | | `faa_stc_model.parquet` | 368,692 | Each model an STC applies to, paired with its make and type certificate data sheet as DRS gives them. | | `faa_stc_file.parquet` | 55,434 | Files of every STC record in DRS: the certificate PDF of each issue (current and historical) and its attachments, mostly approved model lists (AMLs). | | `faa_directive_stc.parquet` | 1,846 | STCs cited in the text of an AD. | | `faa_aircraft_registration.parquet` | 317,868 | US-registered aircraft from the FAA registry (MASTER), with the registered owner but not their street address. | | `faa_aircraft_deregistration.parquet` | 383,636 | Cancelled US aircraft registrations from the FAA registry (DEREG): registrations that expired, were cancelled after a sale, export or revocation, and so on, back to the 1920s. | | `faa_aircraft_model.parquet` | 94,132 | FAA registry aircraft model codes (ACFTREF), matched to type certificate data sheets where possible. | | `faa_engine_model.parquet` | 4,752 | FAA registry engine model codes (ENGINE), matched to engine type certificate data sheets where possible. | | `faa_aircraft_model_directive.parquet` | 355,197 | Candidate ADs for each registry aircraft model code, matched by airframe model, maker and type certificate. | | `faa_engine_model_directive.parquet` | 46,912 | Candidate ADs for each registry engine model code: the exact model, or the whole series when the registry gives only a series (O-320 SERIES). | | `faa_aircraft_model_saib.parquet` | 16,792 | Candidate SAIBs for each registry aircraft model code, matched like faa_aircraft_model_directive. | | `faa_engine_model_saib.parquet` | 7,255 | Candidate SAIBs for each registry engine model code, matched like faa_engine_model_directive. | | `faa_incident_report.parquet` | 234,303 | FAA Accident/Incident Data System (AIDS) reports: accidents from 1973 and incidents from 1978 (and a few older accidents), one report per aircraft, with the FAA's narrative. | | `faa_service_difficulty_report.parquet` | 1,768,082 | FAA Service Difficulty Reports (SDRs), 1995 onward: malfunctions, failures and defects reported by operators, repair stations and others. | | `faa_jasc_code.parquet` | 547 | FAA Joint Aircraft System/Component (JASC) codes: the four-digit system codes in service difficulty reports (faa_service_difficulty_report.jasc_code) and airworthiness concern sheets (faa_concern_sheet.jasc_codes), with titles and the FAA's definition of what each covers. | | `ntsb_case_detail.parquet` | 180,561 | NTSB aviation investigations: one row per case, every accident and incident NTSB holds from 1962 on, as the NTSB API returns it. | | `ntsb_aircrafts.parquet` | 183,000 | Aircraft in NTSB cases, one row per aircraft (the API's aircrafts list). | | `ntsb_narratives.parquet` | 177,223 | Narratives of NTSB cases: probable cause, analysis and factual narrative, one row per aircraft. | | `ntsb_findings.parquet` | 73,276 | Findings of NTSB investigations (2008 on): the coded finding, its hierarchy, and whether it is in the probable cause. | | `ntsb_events.parquet` | 153,996 | The sequence of events of each aircraft in NTSB cases (2008 on, and a phase for older cases), with NTSB's event codes. | | `ntsb_occurrences.parquet` | 137,910 | Occurrences in NTSB investigations from 1982 to 2007 (the coding NTSB used before findings). | | `ntsb_sequenceofevents.parquet` | 264,151 | The coded sequence of events within each occurrence, 1982 to 2007: subject, modifier, person, cause or factor. | | `ntsb_injuries.parquet` | 93,939 | Injury counts per aircraft in NTSB cases, by person category. | | `ntsb_crew_and_occupants.parquet` | 96,801 | Crew and occupants of aircraft in NTSB cases (age, sex, injury, certificates, medical). | | `ntsb_pilots_flight_time_matrix.parquet` | 1,301,135 | Crew members' flight hours by period (total, last 90 days...) and aircraft (all, this make...). | | `ntsb_flight_time_summary.parquet` | 96,801 | Whether a crew member's flight times are estimated or incomplete. | | `ntsb_engines.parquet` | 178,514 | Engines and propellers of aircraft in NTSB cases. | | `ntsb_owner_operators.parquet` | 95,977 | Owner and operator of aircraft in NTSB cases, and the rules the flight was conducted under. | | `ntsb_aircraft_accidents.parquet` | 182,543 | Damage, fire and explosion for each aircraft in an NTSB case. | | `ntsb_airworthiness_certificates.parquet` | 87,893 | Airworthiness certificates of aircraft in NTSB cases. | | `ntsb_airports.parquet` | 190,421 | Airports and runways involved in NTSB cases, per aircraft. | | `ntsb_approaches.parquet` | 172,145 | Approaches flown by aircraft in NTSB cases. | | `ntsb_itinerary.parquet` | 95,520 | Departure and destination of aircraft in NTSB cases. | | `ntsb_weather_conditions.parquet` | 103,889 | Weather at NTSB events, as the investigation recorded it. | | `ntsb_weather_precipitations.parquet` | 13,158 | Precipitation at NTSB events. | | `ntsb_data_dictionary.parquet` | 343 | The NTSB API's aviation data dictionary (GetAviationDataDictionary) as NTSB publishes it: a description of each field. | | `ntsb_docket.parquet` | 25,656 | Public dockets of NTSB aviation investigations, from the NTSB docket system (data.ntsb.gov/Docket): one row per docket in the docket search, with the docket page's summary and dates. | | `ntsb_docket_item.parquet` | 192,455 | Files in NTSB dockets: one row per item of each docket page, with its title, pages, type and a link to the file on data.ntsb.gov. | | `ntsb_faa_incident_report.parquet` | 117,628 | openair's links between NTSB cases and FAA incident reports about the same aircraft and day: one row per NTSB aircraft and FAA report. | | `ntsb_phase_crosswalk.parquet` | 66 | Phases of flight: the CICTT main phases NTSB codes events in from 2008 on (and in its cases from 1962 to 1981), and openair's crosswalk from NTSB's older phase codes (its Aviation Coding Manual, 1982 to 2007) to them. | | `ntsb_pre1982_first_half.parquet` | 87,039 | NTSB investigations from 1962 to 1981, the first half of each record (PRE1982.MDB tblFirstHalf, unchanged since 2003). | | `ntsb_pre1982_second_half.parquet` | 87,039 | The second half of each pre-1982 record (PRE1982.MDB tblSecondHalf), by RecNum: remarks, cause, weather, survival. | | `ntsb_pre1982_occurrences.parquet` | 130,970 | Occurrences in pre-1982 records (PRE1982.MDB tblOccurrences). | | `ntsb_pre1982_seq_of_events.parquet` | 355,681 | Causes and factors in pre-1982 records (PRE1982.MDB tblSeqOfEvents). | | `ntsb_pre1982_codes.parquet` | 2,910 | The codes of the pre-1982 records (PRE1982.MDB ct_Pre1982): Name is the column (or group of columns: CAUSE_FACTOR for CAUSE_FACTOR_1P...), Code the value, Meang its meaning. | | `ntsb_access_events.parquet` | 94,304 | Events (one per investigation) from 1982 on, as NTSB's Access files hold them. | | `ntsb_access_admin.parquet` | 94,304 | Record status and approval date of each event, from the Access files' NTSB_Admin table. | | `ntsb_access_aircraft.parquet` | 95,767 | Aircraft from NTSB's Access files, 1982 on. | | `ntsb_access_engines.parquet` | 91,375 | Engines from NTSB's Access files, 1982 on. | | `ntsb_access_flight_crew.parquet` | 96,702 | Flight crew from NTSB's Access files, 1982 on, including city and state of residence, which the API does not return. | | `ntsb_access_data_dictionary.parquet` | 4,574 | NTSB's data dictionary for the Access files (eADMSPUB_DataDictionary, from avall.mdb): one row per column and code, with the code's meaning and the column's definition. | | `geo_ntsb_case.parquet` | 168,398 | A point for each NTSB case that can be placed: NTSB's own coordinate when it checks out, else the airport NTSB names (moved by NTSB's distance and direction from it), else the named town. | | `geo_faa_incident_report.parquet` | 225,897 | A point for each FAA incident report that can be placed: the linked NTSB case's site, else the named airport, else the named town. | | `geo_faa_aircraft_registration.parquet` | 311,225 | A point for each registered aircraft's owner: the center of the owner's 5-digit ZIP area, else their city. | | `geo_faa_aircraft_deregistration.parquet` | 227,719 | A point for each cancelled registration's last owner: the center of the 5-digit ZIP of their mailing address, else their city. | | `source_document.parquet` | 147,801 | Every upstream document the release was built from: Federal Register documents and FAA DRS records. | | `source_file.parquet` | 152,923 | Every mirrored source file (PDF, XML, HTML, plain text), stored once under its SHA-256. | The same tables are in two SQLite databases, indexed, with full-text search: - `openair.sqlite` (3,662 MB): every table except service difficulty reports. Published as [`openair.sqlite.zst`](https://openairdocs.org/data/releases/2026-10-22/openair.sqlite.zst) (665 MB); unpack it with `zstd -d openair.sqlite.zst` - `openair-sdr.sqlite` (1,590 MB): service difficulty reports. Published as [`openair-sdr.sqlite.zst`](https://openairdocs.org/data/releases/2026-10-22/openair-sdr.sqlite.zst) (299 MB); unpack it with `zstd -d openair-sdr.sqlite.zst` And every table in one DuckDB database, for analysis. Download it, or ATTACH it over HTTPS and DuckDB reads only what each query needs: - [`openair.duckdb`](https://openairdocs.org/data/releases/2026-10-22/openair.duckdb) (813 MB): every table, with the column descriptions as comments, each record's web page (`openair_page`) and views for common questions; no full-text indexes. It needs DuckDB 1.2 or later `manifest.json` lists row counts, SHA-256 checksums, every column and what each upstream source contributed. `DATA_DICTIONARY.md` documents every column. Tables built from FAA data are named `faa_*` and from NTSB data `ntsb_*`; `source_document` and `source_file` record provenance for the FAA sources. NTSB tables keep NTSB's own table and column names (the API's `aircraftMake`, the Access files' `ev_id`); the columns openair adds are in snake case. On Hugging Face, each table is a subset of the dataset. Every column has an origin, so a match openair made is not mistaken for an FAA or NTSB finding: - `source`: The agency's own words or numbers, at most converted to a date, number or boolean. - `normalized`: The agency's value under a fixed rule: a code spelled out, a prefix added, a join key. - `derived`: Chosen or inferred by openair (an extracted paragraph, a match between records). It can be wrong. - `openair`: Identifiers, positions, links and fetch details openair adds. Not a claim about the world. The origin is in `DATA_DICTIONARY.md`, `manifest.json`, `openair_column` in each database and the `openair.origin` metadata of each Parquet field: ```sql SELECT name, origin, description FROM openair_column WHERE table_name = 'faa_incident_report' AND origin <> 'source'; ``` The `geo_*` tables give NTSB cases, FAA incident reports and registry owners a point (latitude, longitude, how it was placed and roughly how far off it can be), computed by openair from public-domain place lists; the agencies' own columns are unchanged. In the SQLite and DuckDB files, the `geo_location` view puts every point in one table (`dataset`, `record_id`, `latitude`, `longitude`, `precision`, `radius_m`, `method`), and `openair.sqlite` has R*Tree spatial indexes for NTSB cases and FAA reports (`geo_ntsb_case_rtree`, `geo_faa_incident_report_rtree`, joined on rowid). Registry points are the owner's mailing ZIP area or town, not where the aircraft is based. Upstream data in this release: | Source | Dataset | Records | As of | |---|---|---|---| | FAA DRS | AD final rules | 20,181 | fetched 2026-10-22 | | FAA DRS | Emergency ADs | 291 | fetched 2026-10-22 | | FAA DRS | AD notices of proposed rulemaking | 8,289 | fetched 2026-10-22 | | FAA DRS | Special Airworthiness Information Bulletins | 1,338 | fetched 2026-10-22 | | FAA DRS | Type Certificate Data Sheets | 8,118 | fetched 2026-10-22 | | FAA DRS | Supplemental Type Certificates | 83,486 | fetched 2026-10-22 | | FAA DRS | Advisory Circulars | 1,687 | fetched 2026-10-22 | | FAA DRS | Airworthiness Concern Sheets | 95 | fetched 2026-10-22 | | Federal Register | Part 39 documents | 24,316 | issues through 2026-10-19 | | FAA registry | Registered aircraft | 317,868 | file of 2026-10-21 | | FAA registry | Deregistered aircraft (cancelled registrations) | 383,636 | file of 2026-10-21 | | FAA SDR | Service difficulty reports (32 yearly files) | 1,768,082 | reports through 2026-10-21 | | FAA AIDS | Accident and incident reports (ASIP search API) | 234,303 | events through 2026-10-11 | | FAA JASC | JASC code table | 547 | file of 2008-10-27 | | NTSB API | Aviation investigations, from NTSB's public API | 180,561 | events through 2026-10-20 | | NTSB Access files | Aviation database, 1962 to 1981 (PRE1982.zip, Microsoft Access) | 87,039 | fetched 2026-10-20 | | NTSB Access files | Aviation database, 1982 to 2007 (Pre2008.zip, Microsoft Access) | 63,002 | fetched 2026-10-20 | | NTSB Access files | Aviation database, 2008 on (avall.zip, Microsoft Access) | 31,303 | fetched 2026-10-20 | | NTSB dockets | Docket index: 192,455 files, linked but not copied | 25,656 | fetched 2026-10-22 | | Place lists | Census places (U.S. Census Bureau), for geocoding | 32,363 | edition 2026 | | Place lists | Census state boundaries (U.S. Census Bureau), for geocoding | 56 | edition 2025 | | Place lists | Census ZIP areas (U.S. Census Bureau), for geocoding | 33,791 | edition 2026 | | Place lists | FAA airports (NASR) (FAA), for geocoding | 19,427 | edition 2026-10-01 | | Place lists | GNIS domestic names (USGS), for geocoding | 284,162 | edition 2026-09-29 | | Place lists | GNIS historical features (USGS), for geocoding | 15,334 | edition 2026-09-02 | | Place lists | OurAirports airports (OurAirports), for geocoding | 86,258 | edition 2026-10-16 | ## Quick start DuckDB (no server; also works over HTTPS): ```sql SELECT ad_number, status, subject, effective_date FROM 'faa_directive.parquet' WHERE list_contains(models, '172S') AND status = 'Current' ORDER BY effective_date DESC; ``` DuckDB also reads the tables straight from Hugging Face, at this release or the newest (drop `@2026-10-22`): ```sql SELECT ad_number, subject, effective_date FROM 'hf://datasets/openairdocs/openairdocs@2026-10-22/faa_directive.parquet' ORDER BY effective_date DESC LIMIT 10; ``` Hugging Face `datasets`, one table (subset) at a time: ```python from datasets import load_dataset directives = load_dataset("openairdocs/openairdocs", "faa_directive", split="train", revision="2026-10-22") ``` The whole release is also one DuckDB file. Open it with `duckdb openair.duckdb`, or attach it where it is published without downloading it: ```sql ATTACH 'https://openairdocs.org/data/releases/2026-10-22/openair.duckdb' AS openair (READ_ONLY); USE openair; FROM openair_column WHERE table_name = 'faa_directive'; -- the data dictionary is in the file FROM aircraft_history WHERE n_number = 'N222UM' ORDER BY event_date; -- one aircraft's records ``` Besides the tables, it holds `openair_page`, the address of every record's page on openairdocs.org (cite those), `openair_source` (each upstream dataset with when it was fetched and the date of its newest record), and views for common questions: `aircraft_history` (an N-number's registrations, NTSB cases and FAA reports), `ntsb_case` (a case per row, with its local date, phase of flight, defining event and probable cause in words), `event` (every accident and incident once, NTSB's and the FAA's together, with a point to map), `geo_location` (every point) and `ntsb_pre1982_cause` (the coded causes of cases before 1982, decoded). Two macros come with it: `distance_mi(lat1, lon1, lat2, lon2)`, statute miles between two points, and `near(lat, lon, radius_mi)`, the events within a radius, nearest first (`FROM near(42.97, -83.74, 5)`). It has no full-text indexes. For search from DuckDB, attach the SQLite file and run its FTS5 queries (below) through `sqlite_query`: `ATTACH 'openair.sqlite' AS lite (TYPE sqlite, READ_ONLY);` then `SELECT * FROM sqlite_query('lite', '...')`. Python: `pandas.read_parquet("faa_directive.parquet")` or `polars.read_parquet(...)`. SQLite (`sqlite3 openair.sqlite`, or any SQLite library): the same tables, with lists stored as JSON text, dates as ISO 8601 text and booleans as 0/1. Full-text search uses the `*_fts` tables (FTS5): ```sql -- ADs mentioning cracked wing spars, best match first SELECT d.ad_number, d.status, d.subject, snippet(faa_directive_fts, -1, '[', ']', '...', 12) AS excerpt FROM faa_directive_fts JOIN faa_directive d ON d.rowid = faa_directive_fts.rowid WHERE faa_directive_fts MATCH 'crack* AND "wing spar"' ORDER BY rank LIMIT 20; -- The same search over every paragraph of every AD, with the page to open in the official PDF SELECT s.ad_id, s.heading, s.pdf_page, snippet(faa_directive_section_fts, 1, '[', ']', '...', 12) AS excerpt FROM faa_directive_section_fts JOIN faa_directive_section s ON s.rowid = faa_directive_section_fts.rowid WHERE faa_directive_section_fts MATCH 'crack* AND "wing spar"' ORDER BY rank LIMIT 20; -- FAA accident and incident reports for one model (matched through the registry), newest first SELECT i.event_date, i.event_type, i.n_number, i.aircraft_damage, i.narrative FROM faa_incident_report i JOIN faa_aircraft_model m ON m.code = i.aircraft_model_code WHERE m.model_key = 'PA28181' ORDER BY i.event_date DESC LIMIT 20; -- NTSB investigations of the same model, with the probable cause and a link to NTSB's case page SELECT c."eventDate", c."ntsbNumber", c."highestInjuryLevel", n."probableCause", c.case_url FROM ntsb_aircrafts a JOIN ntsb_case_detail c ON c."mKey" = a.case_mkey JOIN faa_aircraft_model m ON m.code = a.aircraft_model_code LEFT JOIN ntsb_narratives n ON n.case_mkey = a.case_mkey AND n."aircraftNumber" = a."aircraftNumber" WHERE m.model_key = 'PA28181' ORDER BY c."eventDate" DESC LIMIT 20; -- NTSB narratives about carburetor icing, and the docket files of those cases SELECT n.case_mkey, snippet(ntsb_narratives_fts, -1, '[', ']', '...', 12) AS excerpt FROM ntsb_narratives_fts JOIN ntsb_narratives n ON n.rowid = ntsb_narratives_fts.rowid WHERE ntsb_narratives_fts MATCH '"carburetor ice"' ORDER BY rank LIMIT 20; SELECT title, pages, bytes, url FROM ntsb_docket_item WHERE project_id = 100748 ORDER BY item_number; ``` Quote search terms that contain punctuation: `"PA-28-181"`, `"2026-20-09"`. `openair_table` and `openair_column` hold the data dictionary inside each database, with each table's and column's origin. Service difficulty reports are in `openair-sdr.sqlite`. Attach it to query them alongside the other tables; table names work unqualified once attached: ```sql ATTACH 'openair-sdr.sqlite' AS sdr; -- What has been reported about one aircraft SELECT difficulty_date, part_name, discrepancy FROM faa_service_difficulty_report WHERE n_number = 'N12345' ORDER BY difficulty_date DESC; -- Reports mentioning a cracked fuel selector valve SELECT r.difficulty_date, r.aircraft_model, r.part_name, snippet(faa_service_difficulty_report_fts, 0, '[', ']', '...', 12) AS excerpt FROM faa_service_difficulty_report_fts JOIN faa_service_difficulty_report r ON r.rowid = faa_service_difficulty_report_fts.rowid WHERE faa_service_difficulty_report_fts MATCH 'crack* AND "fuel selector valve"' ORDER BY rank LIMIT 20; -- What is reported on one model, by system (faa_jasc_code, in openair.sqlite, names the codes) SELECT r.jasc_code, j.title, count(*) AS reports FROM faa_service_difficulty_report r LEFT JOIN faa_jasc_code j USING (jasc_code) WHERE r.aircraft_model_key = 'PA28181' GROUP BY r.jasc_code, j.title ORDER BY reports DESC LIMIT 20; ``` ## Finding the ADs that may affect an aircraft This is the job the dataset is built for, whether a person or an LLM is doing it. 1. **List the products.** An aircraft is several products, each with its own ADs: the airframe (make, model, serial number), each engine (make, model, serial), each propeller, and appliances (magnetos, ELTs, seats, instruments...). For a US aircraft, `faa_aircraft_registration` gives the airframe model and serial and the registered engine model, but often only the engine *series* (O-320, not O-320-E2D), and nothing about propellers or appliances. Ask the owner for those. 2. **Find candidate ADs by model.** Match each product's model against `faa_directive_product.model_key` (uppercase letters, digits and `+` only: PA-28-181 becomes PA28181). For registered aircraft this is done already: `faa_aircraft_model_directive` and `faa_engine_model_directive` list the candidate ADs for every registry airframe and engine model code (an engine series covers every model in it). Add the STCs installed on the aircraft (from its records): `faa_directive_stc` lists the ADs that cite each STC, and `in_applicability` marks ADs that apply because the STC is installed. `faa_stc_file` has the text of each STC's certificate and approved model list, and `file_sha256` finds our copy of the PDF in `source_file`. 3. **Read the applicability.** `faa_directive.applicability` holds the AD's applicability paragraph: serial number ranges, part numbers, installed equipment, modification state. Decide each candidate from that text (and from `faa_directive_section` when it refers to other paragraphs). About one in five recent ADs define applicability by pointing to a foreign AD (EASA, Transport Canada...), which is not in this dataset. 4. **Drop what no longer applies.** `status = 'Historical'` and `superseded_by` show ADs replaced by later ones. 5. **Cite the source.** Give the AD number and link `govinfo_pdf_url`, the official edition; for a specific paragraph add `#page=` with `faa_directive_section.pdf_page`. For context beyond ADs: `faa_service_difficulty_report` (in `openair-sdr.sqlite`) holds what operators reported going wrong (by `n_number`, `aircraft_model_key`, `part_number` and `ata_chapter`; `faa_jasc_code` names each `jasc_code`), `faa_incident_report` the FAA's accident and incident reports (by `n_number`, and by registry model through `aircraft_model_code`), `ntsb_aircrafts` and `ntsb_case_detail` the NTSB's investigations (matched to the registry the same way; `ntsb_findings` and `ntsb_narratives` say what caused them), `faa_concern_sheet` the concerns the FAA asked about before deciding on an AD, and `faa_saib` advisory bulletins (matched to registry models in `faa_aircraft_model_saib` and `faa_engine_model_saib`, like ADs). `faa_advisory_circular_page` has the text of the advisory circulars ADs point to as acceptable methods (`faa_directive_ac`). None of these is mandatory unless an AD requires it. The query below does steps 1 and 2 for a registered aircraft. In DuckDB, open `openair.duckdb` (or attach it as above) and set the N-number: ```sql SET VARIABLE n_number = 'N12345'; ``` To run it on the Parquet files instead, start DuckDB in the release folder, set the N-number the same way and create a view for each file first: ```sql CREATE VIEW faa_directive AS FROM 'faa_directive.parquet'; CREATE VIEW faa_directive_section AS FROM 'faa_directive_section.parquet'; CREATE VIEW faa_directive_product AS FROM 'faa_directive_product.parquet'; CREATE VIEW faa_directive_relation AS FROM 'faa_directive_relation.parquet'; CREATE VIEW faa_proposed_directive AS FROM 'faa_proposed_directive.parquet'; CREATE VIEW faa_saib AS FROM 'faa_saib.parquet'; CREATE VIEW faa_saib_product AS FROM 'faa_saib_product.parquet'; CREATE VIEW faa_concern_sheet AS FROM 'faa_concern_sheet.parquet'; CREATE VIEW faa_advisory_circular AS FROM 'faa_advisory_circular.parquet'; CREATE VIEW faa_advisory_circular_page AS FROM 'faa_advisory_circular_page.parquet'; CREATE VIEW faa_directive_ac AS FROM 'faa_directive_ac.parquet'; CREATE VIEW faa_type_certificate AS FROM 'faa_type_certificate.parquet'; CREATE VIEW faa_type_certificate_model AS FROM 'faa_type_certificate_model.parquet'; CREATE VIEW faa_stc AS FROM 'faa_stc.parquet'; CREATE VIEW faa_stc_model AS FROM 'faa_stc_model.parquet'; CREATE VIEW faa_stc_file AS FROM 'faa_stc_file.parquet'; CREATE VIEW faa_directive_stc AS FROM 'faa_directive_stc.parquet'; CREATE VIEW faa_aircraft_registration AS FROM 'faa_aircraft_registration.parquet'; CREATE VIEW faa_aircraft_deregistration AS FROM 'faa_aircraft_deregistration.parquet'; CREATE VIEW faa_aircraft_model AS FROM 'faa_aircraft_model.parquet'; CREATE VIEW faa_engine_model AS FROM 'faa_engine_model.parquet'; CREATE VIEW faa_aircraft_model_directive AS FROM 'faa_aircraft_model_directive.parquet'; CREATE VIEW faa_engine_model_directive AS FROM 'faa_engine_model_directive.parquet'; CREATE VIEW faa_aircraft_model_saib AS FROM 'faa_aircraft_model_saib.parquet'; CREATE VIEW faa_engine_model_saib AS FROM 'faa_engine_model_saib.parquet'; CREATE VIEW faa_incident_report AS FROM 'faa_incident_report.parquet'; CREATE VIEW faa_service_difficulty_report AS FROM 'faa_service_difficulty_report.parquet'; CREATE VIEW faa_jasc_code AS FROM 'faa_jasc_code.parquet'; CREATE VIEW ntsb_case_detail AS FROM 'ntsb_case_detail.parquet'; CREATE VIEW ntsb_aircrafts AS FROM 'ntsb_aircrafts.parquet'; CREATE VIEW ntsb_narratives AS FROM 'ntsb_narratives.parquet'; CREATE VIEW ntsb_findings AS FROM 'ntsb_findings.parquet'; CREATE VIEW ntsb_events AS FROM 'ntsb_events.parquet'; CREATE VIEW ntsb_occurrences AS FROM 'ntsb_occurrences.parquet'; CREATE VIEW ntsb_sequenceofevents AS FROM 'ntsb_sequenceofevents.parquet'; CREATE VIEW ntsb_injuries AS FROM 'ntsb_injuries.parquet'; CREATE VIEW ntsb_crew_and_occupants AS FROM 'ntsb_crew_and_occupants.parquet'; CREATE VIEW ntsb_pilots_flight_time_matrix AS FROM 'ntsb_pilots_flight_time_matrix.parquet'; CREATE VIEW ntsb_flight_time_summary AS FROM 'ntsb_flight_time_summary.parquet'; CREATE VIEW ntsb_engines AS FROM 'ntsb_engines.parquet'; CREATE VIEW ntsb_owner_operators AS FROM 'ntsb_owner_operators.parquet'; CREATE VIEW ntsb_aircraft_accidents AS FROM 'ntsb_aircraft_accidents.parquet'; CREATE VIEW ntsb_airworthiness_certificates AS FROM 'ntsb_airworthiness_certificates.parquet'; CREATE VIEW ntsb_airports AS FROM 'ntsb_airports.parquet'; CREATE VIEW ntsb_approaches AS FROM 'ntsb_approaches.parquet'; CREATE VIEW ntsb_itinerary AS FROM 'ntsb_itinerary.parquet'; CREATE VIEW ntsb_weather_conditions AS FROM 'ntsb_weather_conditions.parquet'; CREATE VIEW ntsb_weather_precipitations AS FROM 'ntsb_weather_precipitations.parquet'; CREATE VIEW ntsb_data_dictionary AS FROM 'ntsb_data_dictionary.parquet'; CREATE VIEW ntsb_docket AS FROM 'ntsb_docket.parquet'; CREATE VIEW ntsb_docket_item AS FROM 'ntsb_docket_item.parquet'; CREATE VIEW ntsb_faa_incident_report AS FROM 'ntsb_faa_incident_report.parquet'; CREATE VIEW ntsb_phase_crosswalk AS FROM 'ntsb_phase_crosswalk.parquet'; CREATE VIEW ntsb_pre1982_first_half AS FROM 'ntsb_pre1982_first_half.parquet'; CREATE VIEW ntsb_pre1982_second_half AS FROM 'ntsb_pre1982_second_half.parquet'; CREATE VIEW ntsb_pre1982_occurrences AS FROM 'ntsb_pre1982_occurrences.parquet'; CREATE VIEW ntsb_pre1982_seq_of_events AS FROM 'ntsb_pre1982_seq_of_events.parquet'; CREATE VIEW ntsb_pre1982_codes AS FROM 'ntsb_pre1982_codes.parquet'; CREATE VIEW ntsb_access_events AS FROM 'ntsb_access_events.parquet'; CREATE VIEW ntsb_access_admin AS FROM 'ntsb_access_admin.parquet'; CREATE VIEW ntsb_access_aircraft AS FROM 'ntsb_access_aircraft.parquet'; CREATE VIEW ntsb_access_engines AS FROM 'ntsb_access_engines.parquet'; CREATE VIEW ntsb_access_flight_crew AS FROM 'ntsb_access_flight_crew.parquet'; CREATE VIEW ntsb_access_data_dictionary AS FROM 'ntsb_access_data_dictionary.parquet'; CREATE VIEW geo_ntsb_case AS FROM 'geo_ntsb_case.parquet'; CREATE VIEW geo_faa_incident_report AS FROM 'geo_faa_incident_report.parquet'; CREATE VIEW geo_faa_aircraft_registration AS FROM 'geo_faa_aircraft_registration.parquet'; CREATE VIEW geo_faa_aircraft_deregistration AS FROM 'geo_faa_aircraft_deregistration.parquet'; CREATE VIEW source_document AS FROM 'source_document.parquet'; CREATE VIEW source_file AS FROM 'source_file.parquet'; ``` ```sql -- Candidate ADs for a US-registered aircraft, by airframe model and registered engine model. -- -- This narrows the list; it does not decide applicability. Check each AD's applicability text against the -- aircraft's serial number, installed equipment and modification state. matched_on = 'equipment' marks ADs -- about equipment that may be installed on this model (they apply only if it is). Propeller and most -- appliance ADs (magnetos, ELTs, seats...) name no aircraft model and need the owner's equipment list. WITH aircraft AS ( SELECT aircraft_model_code, engine_model_code FROM faa_aircraft_registration WHERE n_number = upper(getvariable('n_number')) ), candidates AS ( SELECT 1 AS side, m.ad_id, m.matched_on, m.matched_model, m.pairing FROM aircraft a JOIN faa_aircraft_model_directive m ON m.aircraft_model_code = a.aircraft_model_code UNION ALL SELECT 2 AS side, m.ad_id, m.matched_on, m.matched_model, m.pairing FROM aircraft a JOIN faa_engine_model_directive m ON m.engine_model_code = a.engine_model_code ), -- An AD that matches both the airframe and the engine is listed once, as an airframe match. ranked AS ( SELECT c.*, row_number() OVER (PARTITION BY c.ad_id ORDER BY c.side) AS n FROM candidates c ) SELECT d.ad_number, d.status, r.matched_on, r.matched_model, r.pairing, d.subject, d.effective_date, d.applicability, d.superseded_by, d.govinfo_pdf_url FROM ranked r JOIN faa_directive d ON d.ad_id = r.ad_id WHERE r.n = 1 ORDER BY d.status = 'Current' DESC, d.effective_date DESC NULLS LAST, d.ad_number; ``` In SQLite (`sqlite3 openair.sqlite`), run `.param set $n_number 'N12345'` and use the same query with `$n_number` in place of `getvariable('n_number')`. ## Sources and provenance - **FAA Dynamic Regulatory System (DRS)**: the AD index, status, products, supersession links, emergency ADs, proposed ADs, SAIBs, type certificate data sheets, and the text of ADs from before 2000. - **Federal Register / govinfo**: the published legal text of ADs and proposed ADs from 1994 onward (XML from 2000, plain text before; `faa_directive.text_source` says which text an AD uses). - **FAA aircraft registry** (Releasable Aircraft Database): registered aircraft, their owners and model codes, and cancelled registrations (`faa_aircraft_deregistration`: expired, exported, sold, revoked; one row per cancellation, so an N-number can appear many times). Owners' street addresses are left out. The FAA itself omits owners who asked it to withhold their name and address. - **DRS** also supplies supplemental type certificates (`faa_stc`, holder addresses left out), the certificate PDF of every STC issue and its attachments (`faa_stc_file`: mirrored, with each PDF's text as printed; about a fifth are scans with no text layer), airworthiness concern sheets and advisory circulars (every version DRS holds, PDFs mirrored). - **FAA service difficulty reports**: the FAA's yearly SDR downloads, 1995 onward. Reports are the submitter's account. Air carriers must report; general aviation reporting is voluntary. Code fields are published as the FAA gives them. - **FAA JASC code table**: the Joint Aircraft System/Component code table and definitions (last updated October 2008), which names the four-digit system codes on service difficulty reports and concern sheets (`faa_jasc_code`). - **FAA Accident/Incident Data System (AIDS)**: accident and incident reports from the FAA's Aviation Safety Information Portal (asip.faa.gov), one report per aircraft, 1973 onward, with the FAA's narrative (the FAA replaces private details with `^PRIVACY DATA OMITTED^`). Incidents fall below the NTSB's accident thresholds; for accidents the NTSB's database is the fuller record. Reports are matched to registry aircraft by N-number and event date (`registry_match`), which can be wrong for re-used marks. - **NTSB**: aviation investigations from 1962 on, from NTSB's public API (`ntsb_case_detail` and the tables of its case record); the 1962-1981 records and the 1982-on event, aircraft, engine, crew and record-status tables of NTSB's Access database (`ntsb_pre1982_*`, `ntsb_access_*`), which NTSB stops publishing in April 2027; and an index of every file in NTSB's public dockets (`ntsb_docket*`; the files are linked, not copied). Street addresses are left out. - **Place lists**, for the `geo_*` tables only: FAA airports (NASR), OurAirports, Census places, ZIP areas and states, and USGS GNIS names, all public domain. The table above gives each edition. Every upstream document is listed in `source_document`, and every mirrored file (PDF, XML, HTML, text) in `source_file` with its SHA-256 and original URL, so a copy can be checked byte for byte against the original. DRS file URLs need an FAA API key; use the mirrored copies. ## Personal information These are public records, published by the FAA and the NTSB, and they name people: - The FAA registry names each aircraft's registered owner, with city, state and ZIP code. Street addresses are left out. The FAA omits owners who asked it to withhold their name and address, but a release made before an owner asked keeps their name, and so does every earlier version on Hugging Face. - FAA incident narratives are published as the FAA redacted them, and its redaction misses some names. - NTSB records give pilots' age, certificates and flight hours, and crew members' home city and state. The NTSB withholds individual owners' names itself. - STC certificate text is as printed, holder's address included. ## Versions Releases are named by date and never change once published. On Hugging Face, each release is a git tag: pass `revision=""` (or `@` in an `hf://` path) for a fixed one. The schema version follows semantic versioning: a new major version means renamed or removed columns. Rows are always written in the same order (`sorted_by` in `manifest.json`): tables that grow put the oldest rows first, so each release adds rows at the end. Unchanged tables are byte-identical from one release to the next, and the Parquet files use content-defined chunking, so storage that deduplicates (Hugging Face's Xet) keeps only what changed. ## License FAA and Federal Register content is a work of the US government and in the public domain. This compilation is dedicated to the public domain under CC0 1.0. The code that builds it is Apache-2.0.