Route natural-language questions through an agent that can inspect schema context without exposing private warehouse data on the portfolio.
Global health analytics
Last Mile Health KPI Agent
LangGraph multi-agent text-to-SQL system for querying global health KPI data in natural language.
Background
Program teams needed a faster way to retrieve community health worker training and operations metrics without hand-writing Redshift SQL for each analytical question.
This project turns operational public-health questions into structured database queries. The core challenge is not only generating SQL, but generating it with enough schema context, guardrails, and retry behavior that a program team can trust repeated KPI lookups.
Read this page as a systems project: the visualizations show where the agent spends time, how a question flows through the pipeline, and how the backend returns a chart-ready contract instead of a raw notebook output.
Project Workflow
Retrieve relevant schema, validate generated SQL, and use bounded correction when warehouse errors indicate a fixable query issue.
Cache schema and repeated query context so common KPI questions can move from expensive discovery to faster answer synthesis.
When the user asks for a chart, emit a small chart_spec object that any portal charting library can render.
Planning Artifacts
Architecture moved from hand sketch to workflow mockup.
The sketch captures the original control-flow thinking: a master agent routes the request, schema retrieval decides whether cached metadata is enough, SQL generation and validation are separated, and bounded correction loops prevent uncontrolled retries.
Architecture
Natural language to guarded SQL.
The workflow retrieves relevant schema context before generation, then validates and retries SQL in a bounded loop.
Portal sends message and optional dashboard page context.
Classifies whether database access and visualization are needed.
Searches cached Redshift schema metadata for relevant tables and columns.
Generates read-only SQL using only verified schema context.
Checks SQL safety and retries bounded corrections when appropriate.
Summarizes query rows and prepares preview-safe response metadata.
Builds a minimal chart_spec when the query result is plottable.
Benchmarks
Latency exposes where the agent does real work.
Bars show average warm-request latency in seconds for representative prompts.
| Query | Avg | Cold | Rows |
|---|---|---|---|
| What is a KPI? | 4.93s | 5.43s | - |
| Show KPI metrics | 16.66s | 21.61s | 6 |
| Show the training KPI trend for Malawi | 12.35s | 12.39s | 0 |
Chart Spec
Visualization is returned as a small frontend contract.
The backend returns a compact chart specification so a web app can render structured answers consistently.
{
"chart_type": "line",
"x_field": "fiscal_year",
"y_field": "value",
"series_field": null,
"title": "Training KPI trend over time"
}Associated Presentation
Last Mile Health: LMD 2.0 Agent
Final TGHI presentation covering the LMD 2.0 agent concept, implementation narrative, and project handoff.
