Utility DB Chatbot

Chatbot to accurately handle natural language queries against a poorly structured utility database.

Table of content

1. Introduction
2. Solution
3. Architecture
4. Data overview
5. Demo
6. Setup
7. Conclusion

Introduction

Over decades of system migrations, mergers, and operational patches, schemas accumulate inconsistencies such as mixed data types, ambiguous identifiers, redundant tables, and undocumented relationships.

This project demonstrates the design and implementation of an AI-powered chatbot capable of answering natural language queries over a poorly structured utility database. The solution explicitly embraces schema ambiguity and data quality issues, focusing on robustness, explainability, and correctness. It also showcases the advancement in reasoning capabilities of Small Language Models making it possible to run this entire project on device.

Solution

The solution combines intent-aware natural language understanding, schema normalization, and guard-railed SQL execution to ensure high accuracy even when the underlying data is messy.

Key aspects of the solution include:

• Messy schema simulation: A deliberately poorly designed SQLite database was created with inconsistent naming conventions, mixed data types, redundant attributes, and missing foreign keys to reflect real-world utility systems.
• Normalization layer (Not Implemented, Added conceptually in the architecture): Instead of fixing data at query time, the system introduces a normalization layer that converts raw operational tables into clean, analytical views. This isolates schema chaos from the AI layer and ensures consistent query behavior.
• Intent-driven NL-to-SQL: User queries are first classified into high-level intents (e.g., Billing & Consumption, Outage & Service Operations). Each intent restricts the SQL generation space, improving precision and reducing hallucinations.
• Controlled SQL execution: All generated SQL is validated using an rule based validator before execution, enforcing read-only access and preventing unsafe queries.
• Explainable responses: The chatbot clearly communicates assumptions, exclusions, and data quality limitations in its answers, ensuring transparency and auditability.

Tech Stack:

• Python 3.12
• LangGraph & LangChain (ReAct Agent Framework)
• FastMCP Model Context Protocol (MCP) streamable HTTP
• Ollama -> gpt-oss:20b model

Architecture

Reference architecture of the current POC

The system follows a layered architecture designed for scalability and maintainability:

• Data Layer: Raw operational tables (unchanged, messy schema)
• Normalized analytical views: (*_clean) created via SQL (NOT IMPLEMENTED)
• MCP Server:
  • Table discovery tool
  • Schema inspection tool
  • SQL validation tool
  • SQL execution tool
• AI Layer:
  • LangGraph-based ReAct agent
  • Intent classification (rules + embeddings) (NOT IMPLEMENTED)
  • NL-to-SQL generation constrained to normalized views
• Response Layer: Result formatting, Assumption and explanation are generated.

This separation ensures that schema ambiguity is resolved upstream, allowing the LLM to reason over stable, predictable structures.

Important

1. The current approach is for a prototype. At scale, we move normalization and data quality handling to ingestion, store canonical typed fields, and use aggregation logic instead of assuming single readings. The chatbot architecture remains unchanged.

2. Initially, the LLM queried raw operational tables, which doesn’t scale. We introduced a normalization layer and intent routing so the LLM operates on clean, analytical views. This keeps prompts stable, SQL fast, and results auditable.

3. We can scale by isolating schema chaos behind a normalization layer so the LLM reasons over a stable analytical schema, not raw operational data.

As is solution for POC	Scalable production grade solution

Data Overview

The current POC showcases NL-to-SQL on a SQLlite Database with 8 tables.

Table schema for 'customer_accounts':
CREATE TABLE customer_accounts (
    cust_id INTEGER,
    customer_num TEXT,
    consumerName TEXT,
    region TEXT,
    service_address TEXT,
    account_status TEXT,
    solar_flag TEXT,
);

Top 5 rows from table 'customer_accounts':
(101, 'CUST-1001', 'Ravi Kumar', 'East', 'Block A, Sector 5', 'ACTIVE', 'YES')
(102, 'CUST-1002', 'Anita Sharma', 'West', 'Plot 22, Phase 2', 'ACTIVE', 'NO')
(103, 'CUST1003', 'Sunil Verma', 'North', 'House 14, Street 9', 'INACTIVE', 'N')
(104, 'CUST-1004', 'Meena Iyer', 'South', 'Flat 3B, Lake Road', 'ACTIVE', 'Y')
(105, '1005', 'Rajesh Singh', 'East', 'Sector 10, Block D', 'ACTIVE', 'NO')

Table schema for 'meter_assets':
CREATE TABLE meter_assets (
    meter_no TEXT,
    ConsumerID INTEGER,
    install_dt TEXT,
    meter_status TEXT,
    meter_type TEXT,
    load_capacity INTEGER,
);

Top 5 rows from table 'meter_assets':
('MTR-9001', 101, '2020/06/15', 'ACTIVE', 'ELECTRIC', 100)
('MTR-9002', 102, '15-07-2019', 'FAULTY', 'ELECTRIC', 80)
('MTR-9003', 103, '2018-03-12', 'ACTIVE', 'ELECTRIC', 90)
('MTR-9004', 104, '03/11/2021', 'ACTIVE', 'WATER', 50)
('MTR-9005', 105, '2017', 'INACTIVE', 'ELECTRIC', 110)

Table schema for 'meter_readings':
CREATE TABLE meter_readings (
    reading_id INTEGER,
    meter_number TEXT,
    reading_date TEXT,
    units TEXT,
    customer_name TEXT,
    reading_metadata TEXT,
);

Top 5 rows from table 'meter_readings':
(1, 'MTR-9001', '2024-01-31', '350', 'Ravi Kumar', '{"quality":"OK"}')
(2, 'MTR-9002', '31/01/2024', 'N/A', 'Anita Sharma', '{"quality":"ERROR"}')
(3, 'MTR-9003', '2024/01/30', '420', 'Sunil Verma', '{"quality":"OK"}')
(4, 'MTR-9004', '2024-01-29', '120', 'Meena Iyer', '{"quality":"OK"}')
(5, 'MTR-9005', '29-01-2024', '500', 'Rajesh Singh', '{"quality":"ESTIMATED"}')

Table schema for 'billing_info':
CREATE TABLE billing_info (
    bill_id TEXT,
    cust_id INTEGER,
    bill_month TEXT,
    bill_amount TEXT,
    due_dt TEXT,
    billing_address TEXT,
);

Top 5 rows from table 'billing_info':
('BILL-001', 101, 'Jan-2024', '1850.75', '2024-02-15', 'Block A, Sector 5')
('BILL-002', 102, '2024/01', '2100', '15-02-2024', 'Plot 22, Phase 2')
('BILL-003', 103, '01-2024', '1980', '2024/02/18', 'House 14, Street 9')
('BILL-004', 104, 'Jan2024', '950.5', '2024-02-20', 'Flat 3B, Lake Road')
('BILL-005', 105, '2024-Jan', '2500', '2024-02-10', 'Sector 10, Block D')

Table schema for 'service_requests':
CREATE TABLE service_requests (
    request_id INTEGER,
    consumer_id INTEGER,
    issue_category TEXT,
    request_status TEXT,
    area TEXT,
    created_on TEXT,
);

Top 5 rows from table 'service_requests':
(5001, 101, 'Meter Fault', 'OPEN', 'East', '2024-01-20')
(5002, 102, 'Billing Issue', 'PENDING', 'West', '20-01-2024')
(5003, 103, 'Low Voltage', 'CLOSED', 'North', '2024/01/18')
(5004, 104, 'Water Leakage', 'OPEN', 'South', '2024-01-22')
(5005, 105, 'Meter Replacement', 'PENDING', 'East', '2024-01-25')

Table schema for 'outage_events':
CREATE TABLE outage_events (
    outage_id INTEGER,
    affected_area TEXT,
    start_time TEXT,
    end_time TEXT,
    impacted_customers TEXT,
    root_cause TEXT,
);

Top 5 rows from table 'outage_events':
(900, 'East', '2024-01-10 02:00', '2024-01-10 05:30', '120', 'Transformer overload')
(901, 'West', '2024-01-12 01:00', '2024-01-12 03:00', '80', 'Cable fault')
(902, 'North', '2024-01-15 23:00', '2024-01-16 02:30', '60', 'Storm')
(903, 'South', '2024-01-18 04:00', '2024-01-18 06:00', '40', 'Maintenance')
(904, 'East', '2024-01-20 00:30', '2024-01-20 01:15', '30', 'Unknown')

Table schema for 'infra_assets':
CREATE TABLE infra_assets (
    asset_id TEXT,
    asset_type TEXT,
    location TEXT,
    capacity TEXT,
    install_date TEXT,
    status TEXT,
);

Top 5 rows from table 'infra_assets':
('TR-77', 'TRANSFORMER', 'East Substation', '500KVA', '2015', 'ACTIVE')
('TR-78', 'TRANSFORMER', 'West Yard', '400KVA', '2012', 'ACTIVE')
('SS-12', 'SUBSTATION', 'North Zone', '2MW', '2010', 'ACTIVE')
('PL-9', 'PIPELINE', 'South Sector', 'N/A', '2008', 'OLD')
('TR-79', 'TRANSFORMER', 'East Industrial', '600KVA', '2018', 'FAULTY')

Table schema for 'payments_received':
CREATE TABLE payments_received (
    payment_ref TEXT,
    customer_num TEXT,
    paid_amount TEXT,
    payment_dt TEXT,
    payment_mode TEXT,
);

Top 5 rows from table 'payments_received':
('PAY-789', 'CUST-1001', '1850.75', '2024-02-10', 'UPI')
('PAY-790', 'CUST-1002', '1500', '2024/02/18', 'CASH')
('PAY-791', 'CUST1003', '1980', '18-02-2024', 'CARD')
('PAY-792', 'CUST-1004', '950.5', '2024-02-19', 'UPI')
('PAY-793', '1005', '2000', '2024-02-09', 'CASH')

Demo

Walk-through video:

Setup

Warning

You must have the following system requirements for the project to run smoothly on device:

• Python 3.12+
• SQLite
• Minimum 16 GB RAM, Recommended 32 GB RAM (GPU optional)
• Ollama local LLM runtime

1. Clone the repo:

git clone https://github.com/manojjahgirdar/chatbot-utility-data.git

1. Set the environment variables by creating the .env:

cp example.env .env

1. Update the environment properties. (Optionally setup observability)

DB_PATH=../../databases/utility_data.db
LANGSMITH_PROJECT=utility-chatbot
LANGSMITH_API_KEY= # Get the API key from LangSmith
LANGSMITH_TRACING=true
MCP_AUTH_TOKEN= # A Bearer token

1. Install the MCP server dependencies:

cd tools/mcp_server/
uv sync

1. Start the tools MCP server:

source .venv/bin/activate
uv run mcp_server.py

1. The MCP server will be running on http://0.0.0.0:8000/dbtools/mcp. Keep it running.

2. In another terminal, navigate to the root of the cloned repo, install the agent dependencies:

cd chatbot-utility-data/
uv sync

1. Chat with the agent:

uv run agent/agent.py

1. You can directly interact with the agent through the command line.

   

2. Alternatively you can interact with the Streamlit UI. In terminal, run the following command from the root of the cloned repo.

streamlit run main.py

1. The Streamlit UI will be available on http://localhost:8501/.

Here are few questions that you can try:

INTENT 1: Billing & Consumption Analysis

1. Show high electricity consumers in January 2024
2. What is the average monthly bill for customers in the East region?
3. Compare January 2024 vs December 2023 consumption for Ravi Kumar

INTENT 2: Outage & Service Operations

1. List all active outages in the East region
2. Show pending service requests in the West area
3. Which infrastructure assets in the East have reported faults?

Conclusion

By introducing a normalization layer, intent-aware query generation, and strict SQL validation, the system can produce reliable, explainable answers even in the presence of inconsistent schemas and data quality issues. The architecture mirrors real-world lakehouse best practices and can be extended to large-scale environments with automated profiling and drift detection.

The solution also prioritizes correctness, transparency, and scalability, making it suitable not only as a prototype but as a foundation for production-grade AI systems in regulated utility environments.