Natural-language → safe-SQL pipeline
The heart of Reporting is a guarded orchestrator. A question flows through app/ai/orchestrator.py, which (1) short-circuits conversational/meta queries before spending tokens, (2) calls the AI provider with structured_completion to produce a typed DashboardPlan — { title, chartType, sql, x_axis, y_axis, rationale }, (3) runs that SQL through the validator, (4) executes it under a per-session Postgres statement_timeout, and (5) makes a second AI pass to generate an officer-level insight in the requested language.
The validator as trust boundary
app/services/sql_validator.py parses every AI-generated string with sqlglot at the AST level and rejects anything that isn't a single SELECT. It blocks mutations, multi-statement payloads, dangerous server-side functions, and enforces a table allowlist — only the seven domain tables are readable. The AI never touches database credentials.
Self-correction
When the first plan fails validation or Postgres rejects it, the orchestrator feeds the error back to the model for one corrected retry. Structural failures (off-topic questions, allowlist violations) are not retried; the user gets a clear scope message instead.
URBI assistant and dashboards
The frontend is a two-pane Command Center — the URBI chat assistant alongside a dynamic dashboard renderer that mounts ECharts charts, KPI cards, sortable data tables, heatmaps, and maps from the AI's structured plan.
Live Operations
A WebSocket feed drives a real-time ops view with KPI tiles that tick on a ~12-second cadence — pending complaints, resolved-in-24h, garbage tons today, faulty streetlights, overdue tax — plus severity-graded incident alerts. Every tile is click-to-drill, firing a natural-language prompt back into the assistant.
Role-based access
Authentication spans ADMIN, COMMISSIONER, ADMINISTRATOR, ENGINEER, DEPARTMENT_HEAD, and OFFICER roles. The login screen offers one-click demo presets — Commissioner (Gurugram), Engineer (Faridabad), Department Head (Panipat), and read-only Officer (Hisar) — each backed by a seeded municipal persona.
