FreightMind
NL→SQL logistics analytics + AI invoice extraction with Planner→Executor→Verifier architecture
Personal
Problem
Problem
Logistics teams work with two kinds of data: historical shipment records locked in databases that require SQL to query, and freight invoices (PDFs/images) with inconsistent formats that require manual data entry. Both are bottlenecks. FreightMind addresses both: a natural language interface for querying historical SCMS shipment data, and an AI extraction pipeline for ingesting invoice documents — with the two feeding into the same database so cross-table queries work seamlessly.
Approach
Approach
Both the analytics and extraction pipelines follow a Planner → Executor → Verifier architecture. The Planner interprets the user's intent and generates a plan (SQL query or extraction schema). The Executor runs it against the database or document. The Verifier independently checks the output for correctness — for analytics, this means a read-only SQL guard; for extraction, it validates field values and per-field confidence scores. All LLM calls go through ModelClient, which handles caching, retry, and fallback between OpenRouter and a local Ollama instance. A statistical judgment layer detects anomalies against self-calibrating baselines.
Architecture
Architecture
FreightMind — system diagram
Key Technical Decisions
Key Technical Decisions
Planner → Executor → Verifier for every agent
A single-step LLM call (query → answer) hallucinates SQL and misreads invoice fields. Separating planning, execution, and verification into distinct stages — each with its own prompt and validation — caught errors before they reached the user. The Verifier's read-only SQL guard prevented any destructive queries from running against the production database.
ModelClient abstraction for LLM portability
Routing all LLM calls through a single ModelClient with caching, retry logic, and OpenRouter/Ollama fallback means the system works both online (cloud models via OpenRouter) and offline (local Ollama). This was critical for demos where internet connectivity was unreliable.
Per-field confidence scoring for invoice extraction
Invoice layouts vary wildly. Rather than returning extracted fields with binary pass/fail, the extractor assigns per-field confidence scores. The UI surfaces low-confidence fields for human review before confirmation to the database. This hybrid human-in-the-loop approach makes the extraction practical even on novel invoice formats.
Results
Results
- ✓NL→SQL analytics over historical SCMS shipment dataset with read-only verification guard
- ✓PDF/image invoice extraction with per-field confidence scores and human-review workflow
- ✓Cross-table queries work seamlessly across historical and extracted data in the same SQLite DB
- ✓Built using the BMad Method — full artifact trail from PRD to architecture to implementation
Tech Stack
Tech Stack
Links
Links