QueryMind

AI-powered conversational BI platform. Connect a PostgreSQL database, ask questions in plain English, and get generated SQL, interactive charts, and shareable dashboards.

Demo

Try it instantly with the pre-seeded ecommerce database (no setup required):

600 customers · 80 products · 10,500+ orders spanning 12 months · 8 categories · reviews

Example questions the demo DB can answer:

• "Top 5 products by total revenue"
• "Order count by day for the past 30 days"
• "Revenue by category this quarter vs last quarter"
• "Customers with more than 10 orders who never left a review"
• "Average order value by customer segment"

Stack

Layer	Tech
Backend runtime	Bun + Express 5
Database (app)	PostgreSQL via Prisma + PrismaPg
Database (queries)	PostgreSQL via pg with per-user connection pools
AI	Google Gemini 2.5 Flash, OpenAI GPT-4o, OpenRouter
Frontend	React 19, Vite 8, Tailwind CSS 4, Recharts
Auth	JWT in httpOnly cookies (bcryptjs)
Validation	Zod

Local Development

Prerequisites

• Bun >= 1.3
• Two PostgreSQL databases (one for app data, one for the demo ecommerce DB)
• An AI API key (Gemini, OpenAI, or OpenRouter)

Backend

cd backend
cp .env.example .env
bun install
bunx prisma migrate dev --name init
bun run seed
bun run dev

Frontend

cd frontend
cp .env.example .env
bun install
bun run dev

Environment Variables

backend/.env

Variable	Description
DATABASE_URL	PostgreSQL URL for app data (users, dashboards, widgets)
DEMO_DATABASE_URL	PostgreSQL URL where the demo ecommerce DB is seeded
JWT_SECRET	Secret used to sign session JWTs (generate with openssl rand -hex 32)
PORT	API server port (default 3001)
CLIENT_URL	Frontend origin for CORS (default http://localhost:5173)
NODE_ENV	Set to production for deployed environments

frontend/.env

Variable	Description
VITE_API_URL	Backend base URL (default http://localhost:3001)

Architecture

Backend (backend/src/)

index.ts              Express app, route mounting, graceful shutdown
db.ts                 Prisma client with PrismaPg adapter
routes/
  auth.ts             POST /signup /login /logout, GET /me
  connect.ts          POST /api/connect — validate connection, analyze schema
  query.ts            POST /api/query — classify question, generate SQL, execute
  dashboard.ts        CRUD for dashboards + widgets, public /share/:id route
lib/
  pool.ts             PoolManager — per-user pg pools, schema caching (10 min TTL)
  schema-analyzer.ts  Introspects information_schema for tables, columns, FKs, sample values
  queryEngine.ts      Orchestrates: classify → generate SQL → safety check → execute → retry
  gemini.ts           AI provider abstraction (Gemini, OpenAI, OpenRouter) with retry
  safety.ts           Blocks DML/DDL keywords, enforces SELECT-only, caps LIMIT at 500
  jwt.ts / cookie.ts  HS256 JWT with 7-day expiry, httpOnly cookie
  schema.ts           Zod schemas for request validation
middleware/
  authMiddlware.ts    JWT cookie verification → req.userId

Frontend (frontend/src/)

App.tsx               React Router — public and protected routes
context/
  AuthContext.tsx      User state, AI provider/key (localStorage), login/logout
  DbContext.tsx        DB connection config, schema state
pages/
  Connect.tsx         AI provider selector + DB mode (demo vs custom connection string)
  Chat.tsx            Main query interface — message history, charts, SQL viewer, CSV export
  Dashboard.tsx       Dashboard list, widget management, shareable links
  SharedDashboard.tsx Public read-only dashboard view
components/
  ChartRenderer.tsx   Recharts — bar, line, pie, grouped_bar, scatter, text, table
  ChartTypeSwitcher.tsx  Chart type toggle buttons
  SchemaExplorer.tsx  Collapsible sidebar showing tables, columns, types, FKs
  ResultsTable.tsx    Tabular data display with horizontal scroll
  ProtectedRoute.tsx  Auth guard — redirects to /login if unauthenticated
lib/
  api.ts              Axios instance with credential injection
  types.ts            Shared TypeScript interfaces

Database Schema (Prisma)

User
  id, email (unique), name, password, createdAt
  └── has many Dashboard

Dashboard
  id, title, userId, createdAt
  └── has many Widget (cascade delete)

Widget
  id, dashboardId, question, sql, chartType, chartHint, resultData (JSON), position, createdAt

How a Query Works

1. User types a natural language question
2. Backend classifies the question — is it a data query or a conversational message?
3. If conversational, AI responds directly as a chat message (no SQL)
4. If data query, AI generates a PostgreSQL SELECT statement
5. SQL passes through safety validation (blocked keywords, SELECT-only, LIMIT enforcement)
6. Query executes against the user's connected database with a 10s timeout
7. If execution fails, AI is asked to fix the SQL once and retry
8. Result shape is analyzed to auto-detect the best chart type
9. Single-value results (e.g., counts) render as chat bubbles, multi-row results render as charts/tables

SQL Safety

The platform enforces read-only access through multiple layers:

1. LLM prompt instructs the model to generate only SELECT statements
2. safety.ts validates SQL structure before execution (SELECT/WITH only, no DML/DDL, no stacked queries)
3. SET statement_timeout = 10000 is applied per connection
4. LIMIT 500 is automatically injected or capped on every query

AI Provider Support

Users can choose their AI provider on the Connect page:

Provider	Model	Header
Google Gemini	gemini-2.5-flash	x-ai-key
OpenAI	gpt-4o	x-ai-key
OpenRouter	gemini-2.5-flash	x-ai-key

API keys are stored in localStorage only — never sent to or stored on the server.

Deployment

Frontend → Vercel

1. Import repo on vercel.com
2. Set root directory to frontend
3. Add env: VITE_API_URL=https://your-backend-url.onrender.com
4. Deploy

Backend → Render

1. Create a Web Service on render.com
2. Set root directory to backend
3. Build command: bun install && bunx prisma generate
4. Start command: bun src/index.ts
5. Add environment variables (DATABASE_URL, DEMO_DATABASE_URL, JWT_SECRET, CLIENT_URL, PORT, NODE_ENV)
6. Deploy

After both are live, update CLIENT_URL on Render to match the Vercel URL, and VITE_API_URL on Vercel to match the Render URL.