# Data dictionary (schema 0.12.0, release 2026-10-23)

Every table of the release in a line, with a page each for its columns. The same descriptions are the comments in openair.duckdb, and all of it in one file is [DATA_DICTIONARY.md](https://openairdocs.org/data/latest/DATA_DICTIONARY.md).

## Read it in DuckDB

```sql
ATTACH 'https://openairdocs.org/data/latest/openair.duckdb' AS openair (READ_ONLY);
USE openair;
SELECT table_name, comment FROM duckdb_tables() WHERE database_name = 'openair';
SELECT column_name, data_type, comment FROM duckdb_columns() WHERE table_name = 'ntsb_case_detail';
```

## Joins

- faa_directive.ad_id joins every faa_directive_* table, and the candidate tables faa_aircraft_model_directive and faa_engine_model_directive that match ADs to registry models.
- faa_aircraft_registration (and faa_aircraft_deregistration, where N-numbers repeat) point to faa_aircraft_model.code by aircraft_model_code and to faa_engine_model.code by engine_model_code.
- A model_key is the model in upper case with only letters, digits and + (PA-28-181 is PA28181); faa_aircraft_model, faa_engine_model and the product tables carry one.
- Each NTSB case is one row of ntsb_case_detail, keyed by mKey; every other ntsb_ table joins on case_mkey. ntsb_aircrafts, faa_incident_report and faa_service_difficulty_report carry n_number.
- ntsb_faa_incident_report links NTSB cases (case_mkey, aircrafts_position) to the FAA incident reports about the same aircraft and day (report_number).
- faa_aircraft_model_stc and faa_engine_model_stc list the STCs approved for each registry model (stc_number joins faa_stc): what an aircraft may have, since the registry doesn't record installed STCs.

## Watch for

- Matches between records (an AD to an aircraft, an NTSB case to a registration) are candidates. Check each AD's applicability (serial numbers, part numbers, configuration) before saying it applies, and cite AD numbers.
- NTSB's eventDate is the UTC date from 1982 on, so an evening event shows the next day: use ntsb_case.event_date_local.
- Owner and operator names are spelled as each record reports them: compare upper(name).
- Only the Federal Register PDF on govinfo.gov is the official text of an AD.

## Recipes

Run each after the two lines above.

### Everything on one aircraft

```sql
-- Registrations, cancelled registrations, NTSB cases, FAA and difficulty reports on N222UM
SELECT record, event_date, record_id, summary, page_url
FROM aircraft_history
WHERE n_number = 'N222UM'
ORDER BY event_date;
```

### ADs that may apply to a registered aircraft

```sql
-- Candidates by the aircraft's airframe and engine models: check each AD's applicability
WITH a AS (SELECT aircraft_model_code, engine_model_code FROM faa_aircraft_registration WHERE n_number = 'N333UM')
SELECT d.ad_number, d.status, d.subject, d.effective_date,
       'https://openairdocs.org/ad/' || replace(d.ad_id, 'FAA:', '') AS page_url
FROM faa_directive d
WHERE d.ad_id IN (SELECT ad_id FROM faa_aircraft_model_directive JOIN a USING (aircraft_model_code))
   OR d.ad_id IN (SELECT ad_id FROM faa_engine_model_directive JOIN a USING (engine_model_code))
ORDER BY d.status = 'Current' DESC, d.effective_date DESC;
```

### Accidents and incidents near a point

```sql
-- NTSB cases and FAA reports within 10 miles of Ann Arbor, Michigan (42.28, -83.74), each event once.
-- A point placed at a town (location_precision = 'place') can be off by 10 km or more.
SELECT source, number, event_date, city, state, event_type, location_precision, round(distance_mi, 1) AS miles, page_url
FROM near(42.28, -83.74, 10)
ORDER BY event_date DESC;
```

### Fatal NTSB investigations of a model

```sql
-- Fatal NTSB investigations of Cessna 172S aircraft, with the probable cause
SELECT c.ntsb_number, c.event_date_local, c.city, c.state, c.probable_cause, c.page_url
FROM ntsb_aircrafts a
JOIN ntsb_case c ON c.case_mkey = a.case_mkey
WHERE a.aircraft_model_key = '172S' AND c.highest_injury = 'Fatal'
ORDER BY c.event_date_local DESC;
```

## Tables

| Table | Rows | What it holds |
|---|---|---|
| [faa_directive](https://openairdocs.org/data/dictionary/faa_directive.md) | 20,327 | One row per FAA airworthiness directive (AD). |
| [faa_directive_section](https://openairdocs.org/data/dictionary/faa_directive_section.md) | 285,539 | AD text split into sections: preamble sections and the AD's lettered paragraphs. |
| [faa_directive_product](https://openairdocs.org/data/dictionary/faa_directive_product.md) | 159,864 | Each model an AD names, paired with its maker and type certificate where that can be told. |
| [faa_directive_relation](https://openairdocs.org/data/dictionary/faa_directive_relation.md) | 3,235 | Links between ADs: from_ad_id supersedes or affects (revises, amends) to_ad_id. |
| [faa_proposed_directive](https://openairdocs.org/data/dictionary/faa_proposed_directive.md) | 10,790 | Proposed ADs: notices of proposed rulemaking (NPRMs), supplemental NPRMs and withdrawals. |
| [faa_saib](https://openairdocs.org/data/dictionary/faa_saib.md) | 1,338 | FAA Special Airworthiness Information Bulletins: advisory safety information, not mandatory. |
| [faa_saib_product](https://openairdocs.org/data/dictionary/faa_saib_product.md) | 15,603 | Each model a SAIB names, paired with its maker and type certificate the same way as faa_directive_product. |
| [faa_concern_sheet](https://openairdocs.org/data/dictionary/faa_concern_sheet.md) | 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](https://openairdocs.org/data/dictionary/faa_advisory_circular.md) | 1,687 | FAA advisory circulars: guidance on acceptable means of compliance, one row per version (revision or change) DRS holds. |
| [faa_advisory_circular_page](https://openairdocs.org/data/dictionary/faa_advisory_circular_page.md) | 79,598 | 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](https://openairdocs.org/data/dictionary/faa_directive_ac.md) | 361 | Advisory circulars cited in the text of an AD, by AC family. |
| [faa_type_certificate](https://openairdocs.org/data/dictionary/faa_type_certificate.md) | 2,257 | FAA type certificate data sheets (TCDS), latest revision of each. |
| [faa_type_certificate_model](https://openairdocs.org/data/dictionary/faa_type_certificate_model.md) | 8,536 | Models listed on each type certificate data sheet. |
| [faa_type_certificate_revision](https://openairdocs.org/data/dictionary/faa_type_certificate_revision.md) | 8,118 | Every revision of each type certificate data sheet that DRS holds, current and historical, with its data sheet PDF. |
| [faa_stc](https://openairdocs.org/data/dictionary/faa_stc.md) | 49,107 | FAA supplemental type certificates (STCs): approved design changes to aircraft, engines and propellers, one row per STC number. |
| [faa_stc_model](https://openairdocs.org/data/dictionary/faa_stc_model.md) | 353,844 | Each model an STC applies to, paired with its make and type certificate data sheet as DRS gives them. |
| [faa_stc_file](https://openairdocs.org/data/dictionary/faa_stc_file.md) | 55,451 | 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](https://openairdocs.org/data/dictionary/faa_directive_stc.md) | 1,848 | STCs cited in the text of an AD. |
| [faa_aircraft_registration](https://openairdocs.org/data/dictionary/faa_aircraft_registration.md) | 317,901 | US-registered aircraft from the FAA registry (MASTER), with the registered owner but not their street address. |
| [faa_aircraft_deregistration](https://openairdocs.org/data/dictionary/faa_aircraft_deregistration.md) | 383,639 | 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](https://openairdocs.org/data/dictionary/faa_aircraft_model.md) | 94,134 | FAA registry aircraft model codes (ACFTREF), matched to type certificate data sheets where possible. |
| [faa_engine_model](https://openairdocs.org/data/dictionary/faa_engine_model.md) | 4,752 | FAA registry engine model codes (ENGINE), matched to engine type certificate data sheets where possible. |
| [faa_aircraft_model_directive](https://openairdocs.org/data/dictionary/faa_aircraft_model_directive.md) | 355,197 | Candidate ADs for each registry aircraft model code, matched by airframe model, maker and type certificate. |
| [faa_engine_model_directive](https://openairdocs.org/data/dictionary/faa_engine_model_directive.md) | 46,925 | 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](https://openairdocs.org/data/dictionary/faa_aircraft_model_saib.md) | 16,792 | Candidate SAIBs for each registry aircraft model code, matched like faa_aircraft_model_directive. |
| [faa_engine_model_saib](https://openairdocs.org/data/dictionary/faa_engine_model_saib.md) | 7,255 | Candidate SAIBs for each registry engine model code, matched like faa_engine_model_directive. |
| [faa_aircraft_model_stc](https://openairdocs.org/data/dictionary/faa_aircraft_model_stc.md) | 803,131 | Potential STCs for each registry aircraft model code: STCs whose model list names the model on its data sheet, or a series that includes it. |
| [faa_engine_model_stc](https://openairdocs.org/data/dictionary/faa_engine_model_stc.md) | 37,848 | Potential STCs for each registry engine model code: STCs whose model list names the engine model on its data sheet, or a series that includes it (or, when the registry gives only a series, any model in it). |
| [faa_incident_report](https://openairdocs.org/data/dictionary/faa_incident_report.md) | 234,304 | 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](https://openairdocs.org/data/dictionary/faa_service_difficulty_report.md) | 1,768,312 | FAA Service Difficulty Reports (SDRs), 1995 onward: malfunctions, failures and defects reported by operators, repair stations and others. |
| [faa_jasc_code](https://openairdocs.org/data/dictionary/faa_jasc_code.md) | 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](https://openairdocs.org/data/dictionary/ntsb_case_detail.md) | 180,573 | NTSB aviation investigations: one row per case, every accident and incident NTSB holds from 1962 on, as the NTSB API returns it. |
| [ntsb_aircrafts](https://openairdocs.org/data/dictionary/ntsb_aircrafts.md) | 183,012 | Aircraft in NTSB cases, one row per aircraft (the API's aircrafts list). |
| [ntsb_narratives](https://openairdocs.org/data/dictionary/ntsb_narratives.md) | 177,227 | Narratives of NTSB cases: probable cause, analysis and factual narrative, one row per aircraft. |
| [ntsb_findings](https://openairdocs.org/data/dictionary/ntsb_findings.md) | 73,289 | Findings of NTSB investigations (2008 on): the coded finding, its hierarchy, and whether it is in the probable cause. |
| [ntsb_events](https://openairdocs.org/data/dictionary/ntsb_events.md) | 154,014 | 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](https://openairdocs.org/data/dictionary/ntsb_occurrences.md) | 137,910 | Occurrences in NTSB investigations from 1982 to 2007 (the coding NTSB used before findings). |
| [ntsb_sequenceofevents](https://openairdocs.org/data/dictionary/ntsb_sequenceofevents.md) | 264,151 | The coded sequence of events within each occurrence, 1982 to 2007: subject, modifier, person, cause or factor. |
| [ntsb_injuries](https://openairdocs.org/data/dictionary/ntsb_injuries.md) | 93,948 | Injury counts per aircraft in NTSB cases, by person category. |
| [ntsb_crew_and_occupants](https://openairdocs.org/data/dictionary/ntsb_crew_and_occupants.md) | 96,811 | Crew and occupants of aircraft in NTSB cases (age, sex, injury, certificates, medical). |
| [ntsb_pilots_flight_time_matrix](https://openairdocs.org/data/dictionary/ntsb_pilots_flight_time_matrix.md) | 1,301,305 | Crew members' flight hours by period (total, last 90 days...) and aircraft (all, this make...). |
| [ntsb_flight_time_summary](https://openairdocs.org/data/dictionary/ntsb_flight_time_summary.md) | 96,811 | Whether a crew member's flight times are estimated or incomplete. |
| [ntsb_engines](https://openairdocs.org/data/dictionary/ntsb_engines.md) | 178,524 | Engines and propellers of aircraft in NTSB cases. |
| [ntsb_owner_operators](https://openairdocs.org/data/dictionary/ntsb_owner_operators.md) | 95,989 | Owner and operator of aircraft in NTSB cases, and the rules the flight was conducted under. |
| [ntsb_aircraft_accidents](https://openairdocs.org/data/dictionary/ntsb_aircraft_accidents.md) | 182,548 | Damage, fire and explosion for each aircraft in an NTSB case. |
| [ntsb_airworthiness_certificates](https://openairdocs.org/data/dictionary/ntsb_airworthiness_certificates.md) | 87,898 | Airworthiness certificates of aircraft in NTSB cases. |
| [ntsb_airports](https://openairdocs.org/data/dictionary/ntsb_airports.md) | 190,426 | Airports and runways involved in NTSB cases, per aircraft. |
| [ntsb_approaches](https://openairdocs.org/data/dictionary/ntsb_approaches.md) | 172,150 | Approaches flown by aircraft in NTSB cases. |
| [ntsb_itinerary](https://openairdocs.org/data/dictionary/ntsb_itinerary.md) | 95,525 | Departure and destination of aircraft in NTSB cases. |
| [ntsb_weather_conditions](https://openairdocs.org/data/dictionary/ntsb_weather_conditions.md) | 103,901 | Weather at NTSB events, as the investigation recorded it. |
| [ntsb_weather_precipitations](https://openairdocs.org/data/dictionary/ntsb_weather_precipitations.md) | 13,159 | Precipitation at NTSB events. |
| [ntsb_data_dictionary](https://openairdocs.org/data/dictionary/ntsb_data_dictionary.md) | 343 | The NTSB API's aviation data dictionary (GetAviationDataDictionary) as NTSB publishes it: a description of each field. |
| [ntsb_docket](https://openairdocs.org/data/dictionary/ntsb_docket.md) | 25,662 | 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](https://openairdocs.org/data/dictionary/ntsb_docket_item.md) | 192,521 | 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](https://openairdocs.org/data/dictionary/ntsb_faa_incident_report.md) | 117,629 | 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](https://openairdocs.org/data/dictionary/ntsb_phase_crosswalk.md) | 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](https://openairdocs.org/data/dictionary/ntsb_pre1982_first_half.md) | 87,039 | NTSB investigations from 1962 to 1981, the first half of each record (PRE1982.MDB tblFirstHalf, unchanged since 2003). |
| [ntsb_pre1982_second_half](https://openairdocs.org/data/dictionary/ntsb_pre1982_second_half.md) | 87,039 | The second half of each pre-1982 record (PRE1982.MDB tblSecondHalf), by RecNum: remarks, cause, weather, survival. |
| [ntsb_pre1982_occurrences](https://openairdocs.org/data/dictionary/ntsb_pre1982_occurrences.md) | 130,970 | Occurrences in pre-1982 records (PRE1982.MDB tblOccurrences). |
| [ntsb_pre1982_seq_of_events](https://openairdocs.org/data/dictionary/ntsb_pre1982_seq_of_events.md) | 355,681 | Causes and factors in pre-1982 records (PRE1982.MDB tblSeqOfEvents). |
| [ntsb_pre1982_codes](https://openairdocs.org/data/dictionary/ntsb_pre1982_codes.md) | 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](https://openairdocs.org/data/dictionary/ntsb_access_events.md) | 94,304 | Events (one per investigation) from 1982 on, as NTSB's Access files hold them. |
| [ntsb_access_admin](https://openairdocs.org/data/dictionary/ntsb_access_admin.md) | 94,304 | Record status and approval date of each event, from the Access files' NTSB_Admin table. |
| [ntsb_access_aircraft](https://openairdocs.org/data/dictionary/ntsb_access_aircraft.md) | 95,767 | Aircraft from NTSB's Access files, 1982 on. |
| [ntsb_access_engines](https://openairdocs.org/data/dictionary/ntsb_access_engines.md) | 91,375 | Engines from NTSB's Access files, 1982 on. |
| [ntsb_access_flight_crew](https://openairdocs.org/data/dictionary/ntsb_access_flight_crew.md) | 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](https://openairdocs.org/data/dictionary/ntsb_access_data_dictionary.md) | 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](https://openairdocs.org/data/dictionary/geo_ntsb_case.md) | 168,409 | 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](https://openairdocs.org/data/dictionary/geo_faa_incident_report.md) | 225,898 | 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](https://openairdocs.org/data/dictionary/geo_faa_aircraft_registration.md) | 311,273 | 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](https://openairdocs.org/data/dictionary/geo_faa_aircraft_deregistration.md) | 227,723 | 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](https://openairdocs.org/data/dictionary/source_document.md) | 147,818 | Every upstream document the release was built from: Federal Register documents and FAA DRS records. |
| [source_file](https://openairdocs.org/data/dictionary/source_file.md) | 152,948 | Every mirrored source file (PDF, XML, HTML, plain text), stored once under its SHA-256. |
| [source_file_page](https://openairdocs.org/data/dictionary/source_file_page.md) | 328,274 | The text of each page of the mirrored files we keep text for (DRS PDFs and HTML: STC certificates, TCDS revisions, ACs, SAIBs, concern sheets, emergency ADs and their attachments), one row per page. |

## Views

In openair.duckdb only.

| View | What it holds |
|---|---|
| [ntsb_pre1982_cause](https://openairdocs.org/data/dictionary/ntsb_pre1982_cause.md) | The causes and factors of NTSB cases before 1982, in words: one row per coded cause or factor, decoded the way NTSB's brief prints it (WEATHER - LOW CEILING). |
| [ntsb_case](https://openairdocs.org/data/dictionary/ntsb_case.md) | Every NTSB case in one row, with the facts most questions need: local date, place and point, injuries, aircraft, N-numbers, phase of flight, defining event, probable cause (decoded for cases before 1982) and the case's page. |
| [aircraft_history](https://openairdocs.org/data/dictionary/aircraft_history.md) | Everything recorded about one N-number, a row each: the aircraft registered now, cancelled registrations, NTSB investigations, FAA accident and incident reports, and service difficulty reports, with a one-line summary and the record's page. |
| [geo_location](https://openairdocs.org/data/dictionary/geo_location.md) | Every point of the geo_* tables, with its dataset and record id (as text): one place to map everything. |
| [event](https://openairdocs.org/data/dictionary/event.md) | Every accident and incident once, NTSB's and the FAA's together: each NTSB case, and each FAA incident report no NTSB case covers (ntsb_faa_incident_report links those that are the same event). |

## Macros

In openair.duckdb only; a table macro needs `USE openair` first.

### distance_mi(lat1, lon1, lat2, lon2)

Statute miles between two points given in WGS 84 degrees, over the Earth's surface (haversine on a sphere; within 0.5% of the true distance). For nautical miles multiply by 0.868976.

```sql
SELECT round(distance_mi(42.9655, -83.7436, 42.2124, -83.3534), 1) AS miles  -- Flint to Detroit Metro
```

### near(lat, lon, radius_mi)

The accidents and incidents (the event view) within radius_mi statute miles of a point, nearest first, with a distance_mi column. Points are often an airport or a town's center (see location_precision), so use a radius of a few miles or more. Needs USE on the attached file, as in the directions.

```sql
SELECT number, event_date, aircraft, round(distance_mi, 1) AS mi FROM near(42.9655, -83.7436, 5) LIMIT 10
```