MCP + PostgreSQL + Azure OpenAI + React (Python uv)

End-to-end system with:

• Python backend using uv
• MCP server exposing secure PostgreSQL tools
• Azure OpenAI integration for NL -> SQL and result explanation
• React frontend that renders AI-selected prefab templates

Project Structure

• python-backend/ FastAPI API + MCP tools + PostgreSQL + Azure OpenAI
• frontend/ React UI (component-driven) with adaptive result rendering

Backend Capabilities (Python)

• MCP tools:
  • execute_sql(query, params, allow_non_select)
  • get_schema()
  • validate_query(query, allow_non_select)
• SQL guardrails:
  • Blocks dangerous keywords (DROP, TRUNCATE, etc.)
  • Blocks multi-statement execution
  • Blocks DELETE without WHERE
  • Restricts to SELECT unless explicitly allowed
  • Enforces row limit (LIMIT 100 default)
• Query logging and structured JSON responses

API Contract

POST /query

Request:

{
  "question": "Top 5 highest salaries"
}

Response:

{
  "ok": true,
  "sql": "SELECT ... LIMIT 5",
  "data": [],
  "type": "chart",
  "visualization": {
    "type": "chart",
    "chartKind": "pie",
    "xKey": "expense_category",
    "yKey": "total_expense"
  },
  "prefab": {
    "template": "pie_chart",
    "title": "Expense Distribution",
    "explanation": "Small category composition is best shown as a pie chart.",
    "x_key": "expense_category",
    "y_key": "total_expense"
  },
  "visualization_reason": "Small category composition detected from output; using pie chart.",
  "summary": "...",
  "row_count": 0
}

Query Flow

1. Parse user input
2. Fetch schema via MCP tool layer
3. Generate SQL via Azure OpenAI (temperature=0)
4. Validate SQL via guardrails
5. Execute SQL via MCP tool layer
6. Ask LLM to choose visualization type + chart keys
7. Normalize and validate visualization against real output columns
8. Ask LLM to generate prefab UI plan (template, x_key, y_key, explanation)
9. Return structured result + visualization + prefab

Frontend Adaptive UI

• Input box for natural language question
• Dynamic rendering by backend AI prefab plan:
  • data_table -> DataTable
  • stat_card -> StatCard
  • bar_chart -> ChartView (bar)
  • line_chart -> ChartView (line)
  • pie_chart -> ChartView (pie)
  • area_chart -> ChartView (area)
  • scatter_chart -> ChartView (scatter)
  • radar_chart -> ChartView (radar)
• Includes loading/error states

Setup and Run

1) Python backend (uv)

cd python-backend
copy .env.example .env
# edit .env with database and Azure OpenAI settings
uv sync
uv run run-api

API runs on http://localhost:4000.

Run MCP server separately if needed:

cd python-backend
uv run run-mcp

2) React frontend

cd frontend
npm install
npm run dev

UI runs on http://localhost:5173 and proxies /query to backend.

Example Queries

• Top 5 highest paid employees
• Average salary by department
• Total employees

Security Notes

• Uses parameterized SQL execution path for bound parameters
• Enforces query validation before execution
• Logs all queries and outcomes
• Handles errors with safe structured responses