Trustworthy AI · Natural Language

AI Agent Chatbot for Energy Data

A natural language question-answering system that demonstrates how to build trustworthy AI for data queries—with safety checks, parameter binding, and refusal when questions can't be answered.

Python, Claude API, Text-to-SQL, SQLite, Natural Language Processing, Prompt Engineering

GitHub

Overview

This project demonstrates a trustworthy approach to natural language question-answering over structured data. The system converts plain-language questions about U.S. electricity generation into safe, parameterized SQL queries while refusing questions that cannot be answered from the available data.

The chatbot uses public data from the U.S. Energy Information Administration (EIA), covering net electricity generation from 2020 to 2024, broken down by state and energy source (coal, natural gas, nuclear, wind, solar, etc.).

The Challenge

Natural language interfaces to databases face several critical challenges:

  • SQL Injection Risk: Naively inserting user text into SQL queries creates severe security vulnerabilities
  • Schema Misalignment: Users may ask questions the database cannot answer, leading to incorrect results or hallucinated data
  • Access Control: Query systems must respect data permissions and not leak restricted information
  • Accuracy Verification: Generated queries must be checked before execution to ensure they match the question's intent

Solution Approach

1. Checked Query Translation

Instead of directly converting questions to SQL, the system uses a two-step process:

  1. Extract the semantic intent: What aggregations, filters, and dimensions does the question require?
  2. Map that intent to a predefined query template with parameters—never insert user text directly into SQL

Example: Wind Generation in Texas

Question: "How much wind generation did Texas produce in 2023?"

Intent Extracted:

  • Year: 2023
  • State: Texas
  • Energy Source: Wind
  • Metric: Total generation (megawatthours)

Query: A parameterized SELECT with these values bound safely

Result: 119,855,132 megawatthours (verified against EIA data)

2. Refusal Mechanism

When a question asks for data that doesn't exist in the schema, the system refuses and explains why:

Question: "What was customer revenue in Texas in 2023?"

Response: The system explains that the electricity generation table has no revenue column and cannot answer this question. No query is executed.

3. Schema Awareness

The agent has access to the complete database schema, allowing it to:

  • Validate that requested columns and tables exist
  • Check that filters and aggregations are appropriate for the data types
  • Identify when questions require joins or calculations the schema doesn't support

4. Security by Design

Key security principles implemented:

  • Parameter Binding: All user inputs are bound as query parameters, never concatenated into SQL strings
  • Query Validation: Every generated query is checked against allowed patterns before execution
  • Read-Only Access: The system operates with read-only database permissions
  • No Direct SQL: Users cannot provide raw SQL—only natural language questions

Technical Implementation

Architecture

The system consists of several components:

  • Language Model (Claude): Interprets questions and maps them to database operations
  • Query Builder: Constructs parameterized SQL from semantic intent
  • Schema Validator: Checks that requested operations are valid for the schema
  • Execution Engine: Runs validated queries with bound parameters

Data Source

The chatbot uses the EIA's Electric Power Annual dataset, which provides:

  • Historical electricity generation data from 1970–present
  • Breakdown by U.S. state and energy source
  • Generation measured in megawatthours
  • Public domain data suitable for demonstration purposes

Key Takeaways

Principles for Trustworthy Text-to-SQL

  1. Never inject user text into SQL: Use parameterized queries exclusively
  2. Make refusal explicit: When the system can't answer, it should say why, not guess
  3. Validate before execution: Check that generated queries match the question's intent
  4. Expose schema constraints: The AI needs to know what data exists and what doesn't
  5. Design for auditability: Query translation steps should be inspectable and logged

Use Cases

This approach is applicable to domains where data accuracy and security are critical:

  • Energy infrastructure and grid operations data
  • Financial reporting and compliance queries
  • Healthcare data analysis with privacy requirements
  • Government and public sector data transparency

Future Directions

Potential extensions to explore:

  • Multi-table queries with join safety checks
  • Time-series analysis and forecasting questions
  • Row-level access control based on user permissions
  • Query result explanation and provenance tracking
  • Integration with data governance frameworks

Back to all projects · GitHub