🎯 QueryGPT - Natural Language Database Query Interface

A powerful platform that converts natural language queries into SQL and executes them against your PostgreSQL database using Google Gemini AI. Perfect for agricultural and forestry data analysis.

✨ Features

• 🤖 AI-Powered SQL Generation: Uses Google Gemini to convert natural language to SQL
• 🔧 Configurable Database Connection: Click-to-configure database settings with connection testing
• 🔒 Read-Only Safety: Validates all queries to ensure they're read-only
• 📊 Real-Time Execution: Executes queries and displays results instantly
• 📥 Export Options: Export results as CSV or JSON
• 🎨 Beautiful UI: Modern, responsive interface with modal dialogs
• 🔍 Smart Context: Leverages comprehensive database schema and example queries
• 📱 Responsive Design: Works on desktop, tablet, and mobile devices

🚀 Quick Start

Prerequisites

• Python 3.8+
• PostgreSQL database access
• Google Gemini API key (Get one here)

Installation

1. Clone the repository:

git clone https://github.com/yourusername/QueryGPT.git
cd QueryGPT

2. Install dependencies:

pip3 install -r requirements.txt

3. Set up environment variables:

cp env_template.txt .env
# Edit .env and add your GEMINI_API_KEY

4. Start the server:

python3 backend/app.py

5. Open the frontend: Open index.html in your browser or visit http://localhost:5001

🎯 Usage

1. Configure Database Connection: Click on the connection status to configure your database
2. Enter Natural Language Query: Type your question in plain English
3. Generate SQL: Click "Generate & Execute Query" to convert to SQL
4. View Results: See the generated SQL and query results
5. Export Data: Download results as CSV or JSON

📝 Example Queries

Farmer Analytics

• "Show me the total number of farmers"
• "How many farmers are registered by partner?"
• "List farmers with missing identity cards"

Farm Analytics

• "What's the average farm size in acres?"
• "Show farms by verification status"
• "How many kyaris were created this month?"

Plantation Analytics

• "Which plantation models are most popular?"
• "Show species distribution by kyari"
• "What are the most planted tree species?"

Documentation Analytics

• "Show documentation status by partner"
• "Find farms missing land records"
• "List verification status summary"

🔌 API Endpoints

Endpoint	Method	Description
/api/health	GET	Health check
/api/generate-sql	POST	Generate SQL from natural language
/api/execute-query	POST	Execute SQL query
/api/check-connection	GET	Check database connection
/api/db-config	GET	Get database configuration
/api/db-config	POST	Update database configuration
/api/test-connection	POST	Test database connection

🗄️ Database Schema

The system is designed for agricultural and forestry databases with tables for:

• Farmers: Personal information, location, partner associations
• Farms: Farm details, area calculations, ownership
• Kyaris: Farm plots/fields with plantation data
• Species: Tree species and plantation models
• Documentation: Media files and verification status
• Location: Hierarchical location data (block → district → state → country)
• Partners: Tenant organizations and user management

⚙️ Configuration

Database Schema Context

Customize the AI's understanding by editing:

• backend/schema_context.txt - Database schema and relationships
• backend/example_queries.txt - Example queries and patterns

Environment Variables

GEMINI_API_KEY=your_gemini_api_key_here
FLASK_ENV=development
FLASK_DEBUG=True

🔒 Security Features

• ✅ Read-only query validation
• ✅ SQL injection prevention
• ✅ Secure credential storage
• ✅ Environment variable protection
• ✅ Input sanitization

🏗️ Project Structure

QueryGPT/
├── backend/
│   ├── app.py                 # Flask API server
│   ├── schema_context.txt     # Database schema
│   └── example_queries.txt    # Example queries
├── index.html                 # Frontend interface
├── requirements.txt           # Python dependencies
├── .env                       # Environment variables (not in repo)
├── README.md                  # This file
└── start_server.sh           # Startup script

🛠️ Development

Adding New Query Patterns

1. Add examples to backend/example_queries.txt
2. Update schema context in backend/schema_context.txt
3. Test with various natural language inputs

Customizing the UI

• Modify index.html for frontend changes
• Update CSS styles for visual customization
• Add new API endpoints in backend/app.py

📊 Performance

• Query Generation: ~2-3 seconds with Gemini AI
• Database Execution: Depends on query complexity
• Export: Handles large datasets efficiently
• Memory Usage: Optimized for production use

🤝 Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📄 License

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

🙏 Acknowledgments

• Google Gemini AI for natural language processing
• Flask for the web framework
• PostgreSQL for the database engine

📞 Support

If you encounter any issues or have questions:

1. Check the Issues page
2. Create a new issue with detailed information
3. Include error messages and steps to reproduce

---

Made with ❤️ for agricultural data analysis