Skip to content

About

A powerful web application that transforms natural language into SQL queries and data visualizations. Built with LangGraph, FastAPI, and Streamlit, featuring intelligent agent workflows and secure execution environments.

Topics

Resources

Stars

6 stars

Watchers

0 watching

Forks

Repository files navigation

🧞 Open Genie - Intelligent Text-to-SQL Assistant

A powerful web application that transforms natural language into SQL queries and data visualizations. Built with LangGraph, FastAPI, and Streamlit, featuring intelligent agent workflows and secure execution environments.

✨ Features

  • Natural Language to SQL: Convert plain English questions into optimized SQL queries
  • Intelligent Data Visualization: Automatic chart and plot generation with matplotlib and plotly
  • Multi-table Support: Smart table joins and relationship detection across complex schemas
  • Local & Secure Execution: Run code locally with sandbox protection or use Docker containers
  • Interactive Chat Interface: Conversational AI with context awareness and session memory
  • Real-time Feedback System: User satisfaction tracking with comprehensive admin dashboard
  • Universal Database Support: Works with SQLite, PostgreSQL, MySQL, SQL Server, Oracle, and more

πŸ—οΈ Architecture

High-Level Components

  1. Frontend (Streamlit): Interactive web interface for users and administrators
  2. Backend (FastAPI): RESTful API serving the LangGraph agent
  3. LangGraph Agent: Intelligent workflow orchestration for text-to-SQL conversion
  4. Security Layer: Secure SQL executor and code execution sandbox
  5. Feedback Database: SQLite database for storing user feedback

Security Features

  • Read-only SQL execution with intelligent query validation and injection prevention
  • Flexible execution modes: Local sandbox with FORCE_LOCAL_EXECUTION=true or Docker containers
  • Advanced input sanitization with comprehensive prompt injection protection
  • Resource limits for query execution, memory usage, and runtime constraints
  • Secure code generation with validated matplotlib and plotly visualization libraries

πŸš€ Quick Start

Prerequisites

  • Python 3.11+ with pip
  • Docker (optional - for containerized code execution)
  • OpenRouter API key for LLM access
  • Basic database knowledge (helpful but not required)

Installation

  1. Clone the repository

    git clone <repository-url>
    cd open-genie
  2. Run setup script

    ./scripts/setup.sh
  3. Configure environment

    • Edit .env file with your OpenRouter API key:
    OPENROUTER_API_KEY=your_api_key_here
    
  4. Start the application

    Option A: Local Development (Recommended for development)

    # Start backend with local code execution
    FORCE_LOCAL_EXECUTION=true uvicorn app.main:app --reload --host 0.0.0.0 --port 8000
    
    # In another terminal - Start frontend
    streamlit run app/frontend/main_page.py --server.port 8501
    
    # In another terminal - Start admin dashboard  
    streamlit run app/frontend/admin_page.py --server.port 8502

    Option B: Using Docker Compose (Production)

    docker-compose up -d
  5. Access the applications

πŸ’‘ Usage

Quick Demo with Sample Data

Open Genie comes with sample e-commerce data to get you started immediately:

  1. Start the application following the installation steps above
  2. The app automatically connects to the included demo SQLite database
  3. Try these example queries:
    • "Give me a quantity line plot over the latest 5 records"
    • "Show me a pie chart of products by category"
    • "What are the top selling items?"

Connecting to Your Database

  1. Open the main application
  2. In the sidebar, enter your database connection string:
    # SQLite
    sqlite:///path/to/your/database.db
    
    # PostgreSQL
    postgresql://username:password@host:port/database
    
    # MySQL
    mysql://username:password@host:port/database
    
  3. Click "Connect" to test the connection
  4. Select the tables you want to query

Asking Questions

Once connected, you can ask questions in natural language:

  • "Show me all users from the last month"
  • "What are the top 10 selling products?"
  • "Create a line chart showing sales trends by region"
  • "Give me a pie chart of order quantities by product"
  • "Plot customer distribution across different segments"
  • "Show revenue growth over time with a bar chart"

Security Considerations

⚠️ Important Security Notes:

  1. Database Permissions: Always use read-only database credentials
  2. Network Security: The application connects to your database - ensure proper firewall rules
  3. API Key Security: Keep your OpenRouter API key secure and never commit it to version control
  4. Code Execution: Generated Python code runs in a Docker sandbox, but always review generated queries before execution

πŸ”§ Configuration

Environment Variables

Variable Description Default
OPENROUTER_API_KEY OpenRouter API key for LLM access Required
DATABASE_URL Feedback database URL sqlite:///./feedback.db
DEBUG Enable debug mode false
LOG_LEVEL Logging level INFO
FORCE_LOCAL_EXECUTION Force local code execution (bypass Docker) false
ALLOW_LOCAL_EXECUTION Allow local execution as fallback false
DOCKER_TIMEOUT Code execution timeout (seconds) 120
DOCKER_MEMORY_LIMIT Memory limit for code execution 512m
DOCKER_CPU_LIMIT CPU limit for code execution 1.0

Supported Databases

  • SQLite
  • PostgreSQL
  • MySQL
  • Microsoft SQL Server
  • Oracle Database
  • Any database supported by SQLAlchemy

πŸ§ͺ Development

Running Tests

# Run all tests
pytest

# Run with coverage
pytest --cov=app tests/

# Run specific test categories
pytest tests/unit/
pytest tests/integration/

Code Quality

# Format code
black .

# Lint code
ruff check .

# Type checking
mypy app/

Development Setup

  1. Install development dependencies:

    pip install -e ".[dev]"
  2. Set up pre-commit hooks:

    pre-commit install

πŸ“Š Admin Dashboard

The admin dashboard provides insights into:

  • Feedback Overview: User satisfaction metrics and trends
  • Query Analysis: Most common queries and performance
  • Session Analytics: User engagement and session patterns
  • System Health: API performance and error rates

Access at: http://localhost:8502

πŸ”’ Security Best Practices

  1. Database Access:

    • Use dedicated read-only database users
    • Restrict network access to database
    • Regularly rotate credentials
  2. API Security:

    • Use HTTPS in production
    • Implement rate limiting
    • Monitor for suspicious activity
  3. Code Execution:

    • Docker sandbox provides isolation
    • Resource limits prevent DoS
    • No network access from sandbox

🚒 Deployment

Docker Deployment

  1. Build and run with Docker Compose:

    docker-compose -f docker-compose.prod.yml up -d
  2. Environment Configuration:

    • Set production environment variables
    • Configure reverse proxy (nginx/traefik)
    • Set up SSL certificates
    • Configure monitoring and logging

Production Considerations

  • Use a production-grade database for feedback storage
  • Implement proper logging and monitoring
  • Set up backup strategies
  • Configure auto-scaling if needed
  • Regular security updates

🀝 Contributing

  1. Fork the repository
  2. Create a feature branch
  3. Make your changes
  4. Add tests for new functionality
  5. Ensure all tests pass
  6. Submit a pull request

πŸ“ License

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

πŸ†˜ Troubleshooting

Common Issues

Docker Connection Issues:

# Check Docker is running
docker ps

# Pull required images
docker pull python:3.11-slim

Database Connection Failures:

  • Verify connection string format
  • Check network connectivity
  • Ensure database user has appropriate permissions
  • Check firewall settings

API Key Issues:

  • Verify OpenRouter API key is valid
  • Check rate limits and usage
  • Ensure environment variable is set correctly

Code Execution Issues:

  • For local execution: Set FORCE_LOCAL_EXECUTION=true environment variable
  • For Docker issues: Ensure Docker is installed and running
  • Check Docker daemon socket permissions
  • Verify Python sandbox image is available
  • For plot generation: Ensure matplotlib and plotly are installed in your environment

Getting Help

  • Check the Issues page for common problems
  • Review the API Documentation when running
  • Check logs in the Docker containers or terminal output

πŸ”— Related Projects


Built with ❀️ using Python, LangGraph, FastAPI, and Streamlit

About

A powerful web application that transforms natural language into SQL queries and data visualizations. Built with LangGraph, FastAPI, and Streamlit, featuring intelligent agent workflows and secure execution environments.

Topics

Resources

Stars

6 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages