🌱 JattBot — Varaha Data Assistant

An internal data assistant for Varaha's agroforestry platform. Ask questions in plain English — JattBot generates the correct SQL, runs it against the production database, and gives you a plain-English interpretation with the real numbers.

How it works

1. You type a question ("How many kyaris onboarded in Bhutan last month?")
2. Gemini 2.5 Flash (with thinking mode) generates SQL using the auto-discovered schema
3. The SQL runs against the read-only production DB
4. Real results are fed back to Gemini for an accurate interpretation
5. You see a 5-row preview → confirm → export CSV/JSON

Features

• Auto schema discovery — discovers all 440+ tables on startup, refreshes every 6 hours
• Sampled lookup values — actual country/state/tenant/kyari-type values from the DB are injected into the AI context so filters are always correct
• Two-pass interpretation — AI writes interpretation after seeing real results, so numbers in text always match the table
• Thinking mode — thinking_budget=2048 gives the model space to reason through complex multi-table joins before writing SQL
• Auto SQL retry — if a query errors, the error is fed back to the AI and it self-corrects
• Preview + confirm UX — shows 5 rows first, then "does this look right?" before expanding or exporting
• Image upload — paste or drag-drop a screenshot; Gemini Vision can read it and query accordingly
• Read-only enforced — INSERT/UPDATE/DELETE/DROP blocked at the server level

Quick start

Prerequisites

• Python 3.8+
• Google Gemini API key (free tier) — get one at aistudio.google.com
• VPN access to backenddb-read.varaha.com

Setup

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

pip3 install -r requirements.txt

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

python3 backend/app.py

Then open index.html in your browser.

Environment variables

GEMINI_API_KEY=your_key_here
DB_HOST=backenddb-read.varaha.com
DB_NAME=production
DB_READ_USER_NAME=readonly
DB_READ_PASSWORD=your_password
DB_PORT=5432
GEMINI_MODEL=gemini-2.5-flash

Example questions

How many kyaris onboarded in Bhutan?
Total farmers and area in Jumla, Nepal
Show kyari count by country
Top 10 partners by active farmers
Documentation status by partner
Plantation kyaris created in the last 30 days
RS verification breakdown for plantation kyaris

API endpoints

Endpoint	Method	Description
/api/chat	POST	Main chat endpoint — NL → SQL → results
/api/schema	GET	Schema status and table list
/api/schema/refresh	POST	Trigger immediate schema re-discovery
/api/execute-query	POST	Run raw SQL directly
/api/health	GET	Health check

Project structure

QueryGPT/
├── backend/
│   ├── app.py                # Flask server + Gemini integration
│   ├── example_queries.txt   # SQL patterns injected into AI context
│   └── schema_context.txt    # Static fallback schema
├── index.html                # JattBot chat UI (no build step)
├── requirements.txt
├── env_template.txt
└── start_server.sh

Customising the AI

• backend/example_queries.txt — add worked SQL examples for new query patterns; these are injected verbatim into the system prompt
• get_system_prompt() in app.py — domain glossary, join paths, and location hierarchy rules live here
• Schema sampling queries are in discover_schema() — add more lookup tables there if the AI keeps getting a filter value wrong

---

Built for Varaha's internal data team.