SQL Agent Chatbot 🤖

An educational project demonstrating how to build a production-ready SQL query chatbot using AWS serverless architecture and Claude 3 Haiku.

🎯 Project Overview

This chatbot allows users to query a MySQL database using natural language in both French and English. It leverages:

• Claude 3 Haiku for natural language understanding
• LangChain for agent orchestration
• AWS Serverless for scalable, cost-effective infrastructure

✨ Features

• SQL Query Agent: Natural language to SQL conversion using Claude 3 Haiku
• French Language Support: Full bilingual support (French/English)
• AWS Serverless: Lambda, API Gateway, DynamoDB, S3/CloudFront architecture
• Security First:
  • Read-only database access
  • AWS Secrets Manager for credentials
  • Cognito authentication
  • VPC isolation (envisioned for company context) 🔭
• Production Ready:
  • CloudWatch structured logging with AWS Lambda Powertools
  • X-Ray tracing support 🔭
  • Comprehensive error handling
  • Chain-of-thought logging for debugging
  • CloudWatch dashboards and alarms ✅
  • ~90% test coverage
• Developer Experience:
  • Modern Python tooling with uv
  • Pre-configured linting and formatting
  • Automated CI/CD pipeline
  • Rich local development logging

🛠️ Tech Stack

AWS Services

• Compute: Lambda (Python 3.12)
• API: API Gateway REST API
• AI/ML: Bedrock (Claude 3 Haiku)
• Database: RDS MySQL + DynamoDB
• Security: Secrets Manager, IAM, Cognito
• Monitoring: CloudWatch, X-Ray 🔭
• Storage: S3 + CloudFront
• Auth: Cognito

Python Stack

• Framework: FastAPI + AWS Lambda Powertools
• AI/ML: LangChain, LangChain-AWS
• Database: PyMySQL, SQLAlchemy, boto3
• Configuration: pydantic-settings
• Testing: pytest, coverage
• Code Quality: ruff
• Package Management: uv

Infrastructure

• IaC: AWS SAM/CloudFormation
• CI/CD: GitHub Actions
• Secrets: AWS Secrets Manager
• Monitoring: CloudWatch Dashboards & Alarms

📁 Project Structure

├── src/                                # Lambda function code
│   ├── app.py                          # Lambda handler with async detection
│   ├── agent/                          # SQL agent implementation
│   │   ├── callbacks.py                # Chain-of-thought & metrics handlers
│   │   ├── prompts.py                  # LangChain prompts
│   │   ├── sql_agent.py                # Main agent logic
│   │   ├── tools.py                    # SQL tools
│   │   ├── translation_data.py         # French translations
│   │   └── translation_utils.py        # Translation helpers
│   ├── api/                            # FastAPI application
│   │   ├── main.py                     # FastAPI app configuration
│   │   ├── models.py                   # Pydantic models
│   │   ├── dependencies/               # FastAPI dependencies
│   │   │   └── auth.py                 # Authentication dependency
│   │   └── routers/                    # API endpoints
│   │       ├── auth.py                 # OAuth endpoints
│   │       ├── chat.py                 # Sync chat endpoint
│   │       ├── chat_async.py           # Async chat endpoint
│   │       ├── feedback.py             # Feedback endpoint
│   │       └── health.py               # Health check
│   └── utils/                          # Shared utilities
│       ├── auth.py                     # JWT validation utilities
│       ├── bedrock.py                  # Bedrock LLM config & token tracking
│       ├── correlation.py              # Request tracking
│       ├── db.py                       # Database connection
│       ├── dynamodb.py                 # DynamoDB operations with metrics
│       ├── logger.py                   # Centralized logging
│       ├── metrics.py                  # CloudWatch metrics
│       └── settings.py                 # Configuration
├── frontend/                           # Static web assets
│   ├── index.html                      # Main page
│   ├── css/
│   │   └── styles.css                  # Styling
│   └── js/
│       ├── config.js                   # Environment detection
│       ├── auth.js                     # Cognito OAuth2 flow
│       └── app.js                      # Main app with async polling
├── tests/                              # Test suite
│   ├── unit/                           # Unit tests (comprehensive)
│   ├── integration/                    # Integration tests
│   └── conftest.py                     # Pytest configuration
├── docs/                               # Documentation
│   ├── 01-setup.md                     # Development setup
│   ├── 02-infrastructure.md            # AWS architecture
│   ├── 03-development.md               # Workflow guide
│   ├── 04-database-security.md         # Phase 2 implementation
│   ├── 05-sql-agent.md                 # Phase 3 implementation
│   ├── 06-api-layer.md                 # Phase 4 implementation
│   ├── 07-authentication.md            # Phase 5 implementation
│   ├── 08-frontend.md                  # Phase 6 implementation
│   ├── 09-monitoring.md                # Phase 7 implementation
│   └── README.md                       # Documentation index

🚀 Quick Start

Prerequisites

• Python 3.12+
• AWS CLI configured
• SAM CLI installed
• Docker Desktop running
• AWS Account with appropriate permissions
• MySQL/MariaDB database (local or RDS)

Local Development

1. Clone the repository

   git clone https://github.com/GMartin-Data/sql-agent-chatbot
   cd sql-agent-chatbot

2. Set up the project

   Choose between standard pip or modern uv:

   Option A: Using pip (universal)

   make setup-pip
   source .venv/bin/activate  # On Windows: .venv\Scripts\activate

   Option B: Using uv (faster, auto-installs if needed on Linux/Mac)

   make setup-uv
   source .venv/bin/activate  # On Windows: .venv\Scripts\activate

3. Configure Environment

   # Edit the generated .env file with your database credentials
   # The setup commands already created it from .env.example
   nano .env  # or your preferred editor

4. Test Database Connection

   make db-test

5. Run Tests

   make test  # Run all tests with coverage
   make cov   # Generate HTML coverage report

6. Start Local API

   make dev   # Starts SAM local API on http://localhost:3000

   OR

   make api   # Starts FastAPI directly on http://localhost:8000

Testing the Agent

Once running locally:

# Test health endpoint
curl http://localhost:3000/health

# Test SQL query (French)
curl -X POST http://localhost:3000/chat \
  -H "Content-Type: application/json" \
  -d '{"question": "Montre-moi toutes les tables"}'

# Test SQL query (English)
curl -X POST http://localhost:3000/chat \
  -H "Content-Type: application/json" \
  -d '{"question": "Show me all tables"}'

For production testing, replace the URL with your API Gateway endpoint:

# Find your API endpoint
aws cloudformation describe-stacks \
  --stack-name sql-agent-chatbot \
  --query "Stacks[0].Outputs[?OutputKey=='ApiUrl'].OutputValue" \
  --output text

💡 Usage Examples

Query Types the Agent Understands

📊 Basic Queries

🚧 Examples with actual database tables and columns coming soon

🔍 Filtering and Conditions

🚧 Examples with real data filters coming soon

📈 Aggregations and Analytics

🚧 Examples based on actual database schema coming soon

🔗 Complex Joins (if your schema supports it)

🚧 Examples using real table relationships coming soon

Example Responses

🚧 Real response examples will be added based on actual database content

Deployment

The project includes a complete CI/CD pipeline:

1. Development: Push to any branch triggers tests
2. Production: Merge to main deploys to AWS

To deploy manually:

make build
make deploy-backend

For frontend deployment:

make deploy-frontend

Required Parameters:

• DBHost: Your RDS MySQL endpoint
• DBPassword: Database password for read-only user
• BedrockModelId: Claude model to use (default: claude-3-haiku)

See Infrastructure Documentation for details.

🚀 Project Progress

Phase 1: Foundation ✅

• Project structure with SAM
• CI/CD pipeline with GitHub Actions
• Basic Lambda function with health check
• Development environment setup
• Documentation structure

Phase 2: Database & Security ✅

• RDS MySQL integration with read-only user
• AWS Secrets Manager for credentials
• Database connection utilities
• CloudWatch structured logging with Powertools
• 100% test coverage maintained

Phase 3: Core Agent ✅

• LangChain SQL agent implementation
• Claude 3 Haiku integration via Bedrock
• Query safety and validation (markdown SQL patch)
• Chain-of-thought logging
• French language support
• Lambda integration with connection reuse
• Comprehensive error handling
• ~100% test coverage maintained

Phase 4: API Layer ✅

• FastAPI integration with Lambda
• Request/response validation with Pydantic
• Comprehensive error handling
• API documentation (OpenAPI/Swagger)
• Maintained ~100% test coverage

Phase 5: Authentication ✅

• Cognito user pools setup (SAM template)
• JWT token validation utilities
• FastAPI auth dependency integration
• Chat endpoint auth protection
• User session management integration (DynamoDB)
• Integration tests for auth flow
• ~95% test coverage maintained

Phase 6: Frontend & Feedback ✅

• Static site infrastructure (S3/CloudFront)
• Q&A chat interface with character counters
• OAuth2 integration with Cognito
• Backend auth endpoints (/auth/callback, /auth/config)
• User feedback system with DynamoDB storage
• Frontend deployment via CloudFront
• 3-environment configuration system
• ~95% test coverage maintained

Phase 7: Monitoring & Observability ✅ 95%

• CloudWatch dashboards (AI Performance & Application Health)
• CloudWatch alarms with email notifications
• Custom metrics for AI performance
• Token usage tracking per LLM call
• Chain-of-thought step metrics
• DynamoDB operation metrics
• Comprehensive monitoring documentation
• Operational runbook (in progress)
• X-Ray tracing integration (future)
• ~90% test coverage

Phase 8: Production Hardening

• Polishing and minor debugging (fix known issues first)
• Enhancing security (critical for production)
• Improving coverage (safety net before refactoring)
• Refactoring (with confidence from tests)
• Updating documentation (continuous process)

📚 Documentation

Comprehensive guides are available in the docs/ directory:

• Development Setup - Get your environment ready
• Infrastructure Guide - Understand the AWS architecture
• Development Workflow - Git workflow, 3-environment system, and CI/CD
• Database & Security - RDS, Secrets Manager, and logging
• SQL Agent Implementation - LangChain and Claude 3 integration
• API Layer - FastAPI integration and REST API
• Authentication - JWT auth with AWS Cognito
• Frontend - SPA architecture, OAuth2 flow, and deployment
• Monitoring & Observability - CloudWatch dashboards, metrics, and alarms

🧪 Testing

The project maintains test coverage as close as possible to 100%:

make test              # Run all tests
make cov              # Generate coverage report
make db-test          # Test database connection
make agent-test       # Interactive agent testing

📊 Monitoring

The application includes comprehensive monitoring:

Dashboards

• AI Performance Dashboard: Token usage, response times, reasoning complexity
• Application Health Dashboard: Success rates, latencies, database performance

Alarms

• High error rate (>5%)
• High response time (>60s)
• No traffic detection
• High token usage (>100k/hour)

Access dashboards:

# Get dashboard URLs
make dashboard-urls

# Or find them manually
aws cloudwatch list-dashboards \
  --query "DashboardEntries[?contains(DashboardName, 'sql-agent-chatbot')].DashboardName"

🤝 Contributing

This is an educational project, but contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Follow the commit conventions
4. Ensure tests pass
5. Submit a pull request

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• AWS for the serverless platform
• Anthropic for Claude 3 Haiku
• The Python and AWS communities

---

Note: This is an educational project demonstrating best practices for building production-ready serverless applications. Always follow your organization's security and compliance requirements when building production systems.