How I Built a Local RAG System to Query NTSB Aviation Accident Reports

#📥 Step 1 Get the Data

NTSB provides a CSV export from their query page. I downloaded all records from 2010–2026: 26,872 rows, 36 columns. Each row has a `NtsbNo` (the report identifier, e.g. `ERA21FA001`) and an `Mkey` (a numeric ID used by their API).
I split the full CSV into 5-year bands to make processing manageable:
NTSB_2010_2014.csv → 8,539 records

NTSB_2015_2019.csv → 8,191 records

NTSB_2020_2024.csv → 8,106 records

NTSB_2025_2026.csv → 2,036 records

Why split? So I could build and test the pipeline on one band (2020–2024) before scaling to all years.

#📄 Step 2 — Download the PDFs (download.py)

Each completed NTSB investigation has a PDF report. The public API URL is:
https://data.ntsb.gov/carol-repgen/api/Aviation/ReportMain/GenerateNewestReport/{mkey}/pdf

Run it using:

#🔪 Step 3 — Ingest: PDF → Chunks (ingest.py)

This is where the real thinking happens. A naive approach would be to split each PDF every 512 tokens. I didn't do that.
Why section-aware chunking?
An NTSB report has predictable sections: History of Flight, Pilot Information, Meteorological Information, Analysis, Probable Cause and Findings. If you chunk blindly at fixed sizes, you get a chunk that says "the pilot failed to..." with no context about who the pilot was or which accident this is. A section-aware chunk is semantically complete on its own.
Here's how I detect sections just regex matching against known headers:

The section splitter walks the PDF line by line. When it hits a header line, it saves the previous section and starts a new one

For long sections (>4,000 chars), I split further with 400-char overlap. so context carries across sub-chunk boundaries.

The metadata strategy
Every chunk gets 29 fields of metadata from the CSV attached to it. This is the critical design decision:

Think of `NtsbNo` as a foreign key in a database. Every chunk links back to its report:
ERA21FA001_chunk_0 ─ntsb_no ─► CSV row (36 columns) ─► ERA21FA001.pdf

ERA21FA001_chunk_1 ─► (same report)

ERA21FA001_chunk_2 ─► (same report)
The metadata is not embedded into the chunk text that would waste embedding dimensions on ID strings. It's stored separately as filterable fields in the vector database.
Output is a JSONL file one chunk per line, ready for the next stage:

Result from my test run: 2 PDFs → 19 chunks, avg ~10 chunks per report, all sections detected correctly.

#🗄️ Step 4 — Set Up ChromaDB (setup_chromadb.py)

ChromaDB is a local, open-source vector database. It persists to disk, so embeddings survive between runs. Setup is one script:

#🧬 Step 5 — Embed and Store (embed_and_store.py)

This stage reads the JSONL, calls Gemini to convert each chunk's text into a vector, and upserts into ChromaDB.

Notice `task_type='RETRIEVAL_DOCUMENT'`. This matters — more on it in a moment.

Then upsert into ChromaDB with metadata:

Result: 19 chunks embedded and stored in 1.3 seconds, zero failures.

#🔍 Step 6 — Query (query.py)

This is where everything comes together. The full query flow:

#💡 The Key Concept: Asymmetric Embeddings

Here's something that trips people up. Queries and documents are linguistically very different:
Query : "What causes engine failure in Cessna?" ← short, question-form

Document : "The carburetor venturi was blocked by ← long, technical narrative ice accumulation at altitude..."

If you embed both with the same "neutral" embedding, they land far apart in vector space even though they're directly related. The query uses everyday language, the document uses NTSB vocabulary.
Gemini's embedding model solves this with a `task_type` parameter:

These two task types are trained to align. The model maps "pilot made a mistake" to match NTSB language like "pilot's failure to maintain altitude" and "inadequate preflight planning."

Metadata Pre-filtering
Before the vector search runs, I can apply filters using ChromaDB's where clause. This is hybrid retrieval narrow the search space with structured data first, then run semantic search within that subset:

So a query like "What caused the Cessna crash in Florida in 2021?" becomes:
1.Metadata filter:`state=Florida AND make=CESSNA`

2.Vector search: finds Analysis + Probable Cause chunks most similar to the question

3.LLM answer:"Based on NTSB report ERA21FA001, the probable cause was..."

What about questions outside the dataset? The system handles it gracefully. When I asked about a helicopter crash in Mumbai (which doesn't exist in the NTSB database since it only covers US civil aviation), the retriever returned the closest helicopter-related chunks it could find but with low similarity scores (~0.40). Gemini correctly identified that no relevant data was available and declined to fabricate an answer.

#🔑 The Three Things That Made This Work

1. Section-aware chunking preserves context. Each chunk is a complete, meaningful unit. "The pilot" in chunk 3 refers to someone described in the same chunk, not page 1 of a different document.
2. Metadata is the filter layer, not the embedding layer.`NtsbNo`, state, make, weather — these are stored as structured fields for precise filtering. The embedding captures only semantic meaning. This is the difference between finding *relevant* chunks and finding the *right* chunks.
3. Asymmetric task types close the vocabulary gap. NTSB reports use formal investigation language. Human questions use plain English. Using `RETRIEVAL_QUERY` at query time and `RETRIEVAL_DOCUMENT` at ingest time lets the embedding model bridge that gap automatically.

The full source code is on GitHub: [NTSB-Insight-RAG]
Like this post? Share it with someone building AI tools on real data.