# NHTSA Complaints · DuckDB + RAG Chatbot

Build your **own dataset** from the live [NHTSA](https://www.nhtsa.gov/) vehicle-complaints
API, store it in **DuckDB** (with a vector index), and chat with it in natural language.
An LLM **router** decides per question whether to answer with **SQL** (exact filters,
counts, VIN lookup) or a **semantic vector search** (meaning-based retrieval), then
streams a natural-language answer.

> **Model strategy:** **Primary = HuggingFace**, secondary fallbacks = **OpenAI** or **Google**.
> This applies to both the chat LLM and the embedding model (see [Models](#models)).

---

## Features

- 🚗 **Dynamic cascading dropdowns** — pick a *Make* → its *Models* load automatically → *Model year* (2015–2026). Data from the NHTSA vPIC API.
- 🗄️ **DuckDB storage** — complaints stored in a single embedded DuckDB file (no server). Idempotent upsert by `odiNumber`.
- 🔎 **Vector search (RAG)** — every record is embedded into an `embedding` column; semantic search uses a **HNSW ANN index** (DuckDB `vss` extension) with cosine similarity.
- 🧠 **LangGraph router** — an intent classifier routes each question: `SQL` vs `semantic`.
- 💬 **Conversational chatbot** — persistent history, follow-up memory, streamed answers, tables for list results.
- 📋 **Sample-question templates** — one-click example questions (right panel).
- ⚙️ **Config-driven** — all models, paths, and tuning live in `.env` (no hardcoded values).
- 📊 **Observability** — per-call **timing + token usage** logged to the terminal and shown under each answer.
- 🐳 **Dockerized** — `docker-compose` with a persistent volume for the DuckDB file.

---

## Technology Stack

| Layer | Technology |
|-------|-----------|
| **Language** | Python 3.11+ |
| **UI** | [Streamlit](https://streamlit.io/) |
| **Database** | [DuckDB](https://duckdb.org/) (embedded, columnar) |
| **Vector index** | DuckDB `vss` extension — **HNSW** approximate nearest-neighbor, cosine metric |
| **Orchestration** | [LangGraph](https://langchain-ai.github.io/langgraph/) (router → sql/semantic → answer) |
| **LLM framework** | [LangChain](https://python.langchain.com/) |
| **Chat LLMs** | `langchain-huggingface` (Qwen via novita) · `langchain-openai` (gpt-4o) · `langchain-google-genai` (gemini) |
| **Embeddings** | `sentence-transformers` (local MiniLM) · `langchain-google-genai` (gemini-embedding) |
| **Data** | `pandas`, `requests` |
| **Config** | `python-dotenv` (`.env`) |
| **Container** | Docker + docker-compose |

---

## APIs Used

### External REST APIs (data source)

| API | Endpoint (`.env`) | Purpose |
|-----|-------------------|---------|
| NHTSA **vPIC** — makes | `GET {VPIC_URL}/GetMakesForVehicleType/car?format=json` | List all car makes |
| NHTSA **vPIC** — models | `GET {VPIC_URL}/GetModelsForMake/{make}?format=json` | Models for a selected make |
| NHTSA **Complaints** | `GET {COMPLAINTS_URL}?make=..&model=..&modelYear=..` | Vehicle complaints for make/model/year |

- `VPIC_URL` = `https://vpic.nhtsa.dot.gov/api/vehicles`
- `COMPLAINTS_URL` = `https://api.nhtsa.gov/complaints/complaintsByVehicle`

### Model provider APIs

| Provider | API | Used for | Auth |
|----------|-----|----------|------|
| HuggingFace | Inference Providers (`router.huggingface.co` → novita) | Chat (primary) | `HUGGINGFACEHUB_API_TOKEN` |
| OpenAI | Chat Completions (`api.openai.com`) | Chat (secondary) | `OPENAI_API_KEY` |
| Google | Gemini API (`generativelanguage.googleapis.com`) | Chat + embeddings (secondary) | `GOOGLE_API_KEY` |

---

## Models

The app supports **three interchangeable providers**. HuggingFace is primary; OpenAI / Google are fallbacks (useful when HF Inference credits are depleted).

### Chat LLM (`Provider` dropdown in the sidebar)

| Priority | Provider | Model (`.env`) | Notes |
|----------|----------|----------------|-------|
| **Primary** | HuggingFace | `HF_MODEL=Qwen/Qwen2.5-72B-Instruct` via `HF_PROVIDER=novita` | Uses `HUGGINGFACEHUB_API_TOKEN`. Free monthly credits; may hit `402` when depleted. |
| Secondary | OpenAI | `OPENAI_MODEL=gpt-4o` | Uses `OPENAI_API_KEY`. Billed to your key. Real token streaming. |
| Secondary | Google | `GOOGLE_CHAT_MODEL=gemini-2.0-flash` | Uses `GOOGLE_API_KEY`. Generous free tier (per-day quota). |

### Embedding model (semantic search) — set by `EMBED_PROVIDER` in `.env`

| Priority | `EMBED_PROVIDER` | Model | Dim | Notes |
|----------|------------------|-------|-----|-------|
| **Primary** | `hf` | `sentence-transformers/all-MiniLM-L6-v2` | 384 | Local, **free, offline**. Best for embedding all rows. |
| Secondary | `google` | `models/gemini-embedding-001` | 768 | API-based; free tier ~100 req/min. |

> Switching `EMBED_PROVIDER` changes the vector dimension (384 ↔ 768). The app auto-recreates
> the `embedding` column + HNSW index, then you must **re-embed** (see [Backfill](#4-backfill-embeddings)).

---

## Architecture

![Architecture diagram](architecture.png)

> Editable source: [`architecture.drawio`](architecture.drawio) (open in [draw.io](https://app.diagrams.net) or import into Miro).

```
                       ┌─────────────── Streamlit UI ───────────────┐
   Make/Model/Year ──▶ │  Fetch & store            Chat + templates │
                       └───────┬───────────────────────┬────────────┘
        NHTSA APIs ────────────┘                        │
   (vPIC + complaints)                                  ▼
                                            ┌───── LangGraph ─────┐
   DuckDB (complaints table)  ◀────────────│  router (LLM)       │
     - columns + vin                       │    ├─ sql_node       │──▶ DuckDB SQL
     - embedding FLOAT[dim]                │    └─ semantic_node  │──▶ HNSW vector search
     - HNSW cosine index                   └──────────┬──────────┘
                                                      ▼
                                          stream_answer (LLM) ──▶ NL answer + table
```

- **2 LLM calls per question:** `router` (question → SQL or semantic phrase) + `answer` (rows → NL).
- **RAG:** the semantic path retrieves the top-k similar complaints and grounds the answer on them.

---

## Prerequisites

- **Python 3.11+**
- API keys as needed (at least one chat provider + optionally Google for embeddings):
  - `HUGGINGFACEHUB_API_TOKEN` (primary chat)
  - `OPENAI_API_KEY` (secondary chat)
  - `GOOGLE_API_KEY` (secondary chat + `google` embeddings)

---

## Setup

### 1. Install dependencies

```bash
python -m pip install -r requirements.txt
# For local HuggingFace embeddings (EMBED_PROVIDER=hf):
python -m pip install sentence-transformers
```

### 2. Create `.env`

All configuration lives in `.env` (no hardcoded values in the code). Create it in the
project root:

```env
# ---- Secrets ----
HUGGINGFACEHUB_API_TOKEN=hf_xxx
OPENAI_API_KEY=sk-xxx
GOOGLE_API_KEY=AIza-xxx

# ---- DuckDB ----
DUCKDB_PATH=data/complaints.duckdb
TABLE=complaints

# ---- Embedding backend ----
# hf     -> EMBED_MODEL=sentence-transformers/all-MiniLM-L6-v2  EMBED_DIM=384
# google -> EMBED_MODEL=models/gemini-embedding-001            EMBED_DIM=768
EMBED_PROVIDER=hf
EMBED_MODEL=sentence-transformers/all-MiniLM-L6-v2
EMBED_DIM=384

# ---- Backfill throttling (Gemini free tier ~100 req/min) ----
EMBED_MAX_ROWS=500
EMBED_CHUNK=50
EMBED_SLEEP=1.0

# ---- Chat LLMs ----
HF_MODEL=Qwen/Qwen2.5-72B-Instruct
HF_PROVIDER=novita
OPENAI_MODEL=gpt-4o
GOOGLE_CHAT_MODEL=gemini-2.0-flash

# ---- NHTSA data source APIs ----
VPIC_URL=https://vpic.nhtsa.dot.gov/api/vehicles
COMPLAINTS_URL=https://api.nhtsa.gov/complaints/complaintsByVehicle
```

> `.env` is git-ignored. Missing keys fail loudly (`RuntimeError: Missing required config '...'`).

### 3. Run the app

```bash
python -m streamlit run duckdb_app.py
```

Open http://localhost:8501. Then:

1. In the sidebar, pick a **Make**, **Model**, **Model year** → **Fetch & store**.
2. (First time / after switching embedding provider) run the **backfill** below to embed existing rows.
3. Ask questions in the chat, or click a sample-question template.
4. Pick the chat **Provider** (HuggingFace primary; switch to OpenAI/Google if HF credits are depleted).

### 4. Backfill embeddings

New rows are embedded automatically on **Fetch & store**. To embed rows that predate the
`embedding` column (or after switching `EMBED_PROVIDER`), run the one-off backfill **with
the app and DBeaver closed** (DuckDB allows one writer):

```bash
# Local HF (free) — embed everything at once:
EMBED_MAX_ROWS=100000 python backfill_embeddings.py

# Google (free tier) — process a capped subset per run, resumable:
python backfill_embeddings.py        # embeds next EMBED_MAX_ROWS rows; re-run to continue
```

The status caption in the app shows coverage, e.g.
`… 7331 rows · embedded 7331 (100%) · vector index HNSW ✓ (hf, 384d)`.

---

## Docker

```bash
docker compose up --build     # → http://localhost:8501
```

- Reads keys from `.env` (`env_file`).
- Persists the DuckDB file on the `duckdb-data` volume.
- For local `hf` embeddings inside Docker, uncomment `sentence-transformers` in `requirements.txt`
  (adds torch — a larger image). `google` embeddings need no extra dependency.

---

## Example questions

**Exact (SQL route):**
- How many complaints involved a fire?
- What are the top 10 most common components?
- Give me the details of VIN 5YJ3E1EB8LF
- How many complaints were filed in 2020?

**Meaning-based (semantic / vector route):**
- Find complaints about the car suddenly accelerating on its own
- Anything similar to phantom braking on the highway?
- Complaints describing the battery catching fire

---

## Inspecting the DuckDB file

**DBeaver:** New Connection → DuckDB → point to `data/complaints.duckdb`.
Match the DuckDB JDBC driver to **1.5.x**, and either close the app first or open read-only
(`duckdb.read_only = true`) — only one writer is allowed.

**CLI / Python:**
```python
import duckdb
con = duckdb.connect("data/complaints.duckdb", read_only=True)
print(con.sql("SELECT make, model, COUNT(*) FROM complaints GROUP BY 1,2"))
```

---

## Project files

| File | Purpose |
|------|---------|
| `duckdb_app.py` | **Main app** — dataset builder + DuckDB + LangGraph RAG chatbot |
| `backfill_embeddings.py` | One-off / resumable embedding backfill for existing rows |
| `nhtsa_app.py` | Simpler Streamlit app — build a HF `Dataset` + LLM Q&A (no DuckDB) |
| `nhtsa_dataset.py` | Standalone script — fetch NHTSA data into a HF `Dataset` and query it |
| `app.py` | Minimal LangChain demo (provider dropdown) |
| `LLM_Intro.ipynb` | Bootcamp notebook (HuggingFace + OpenAI intro) |
| `Dockerfile`, `docker-compose.yml` | Containerization |
| `requirements.txt` | Dependencies |
| `.env` | All config + secrets (git-ignored) |

---

## Troubleshooting

| Error | Cause / Fix |
|-------|-------------|
| `402 Payment Required` (novita) | HuggingFace Inference credits depleted → switch Provider to **OpenAI**/**Google**, or go HF PRO. |
| `429 RESOURCE_EXHAUSTED` (Gemini) | Google free-tier quota hit → wait for daily reset, or use another provider. |
| `429 Request too large ... TPM` (OpenAI) | Handled — heavy `embedding`/`embed_text` columns are stripped before the LLM. Reduce result size if it recurs. |
| `IO Error ... used by another process` | DuckDB file locked by the app/DBeaver → close the other writer. |
| `ModuleNotFoundError: duckdb` | Wrong interpreter → install into the one running Streamlit. |
| `Missing required config '...'` | A key is absent from `.env` → add it. |