SeeQL 📊

SeeQL is an AI-powered conversational interface for querying databases using natural language. Built with Next.js and the Model Context Protocol (MCP), it lets users ask complex questions in plain English and receive rich, visual dashboards (Charts, Graphs, Tables, Metrics) instead of raw SQL results.

---

🚀 Key Features

• Natural Language to SQL: Converts conversational questions into SQL queries automatically
• Dynamic Visualizations: Renders appropriate UI based on query results (stats, bar/pie charts, data tables)
• MCP Integration: Uses Model Context Protocol for secure database schema introspection and query execution
• Two-Stage Query Processing: Smart routing determines if a database query is needed before executing
• Large Dataset Handling: Efficiently handles large results with pagination, search, and print functionality
• Persistent Conversations: Thread-based chat history stored in SQLite
• Conversation Starters: Pre-built prompts for common queries

---

📂 Project Structure

seeQL/
├── database/                    # Database files and schema
│   ├── university.db            # Pre-seeded SQLite database with university ERP data
│   └── init.sql                 # Database schema (30+ tables)
├── src/                         # Next.js application source
│   ├── app/
│   │   ├── api/
│   │   │   ├── chat/            # Main chat endpoint (NL to SQL pipeline)
│   │   │   ├── mcp/             # MCP SSE transport endpoints
│   │   │   ├── messages/        # Message persistence
│   │   │   ├── query-results/     # Large dataset retrieval
│   │   │   └── threads/         # Thread CRUD operations
│   │   ├── page.tsx             # Main application page
│   │   └── layout.tsx           # Root layout
│   ├── components/
│   │   ├── CustomAssistantMessage.tsx  # Renders openui-lang components
│   │   └── FullResultsModal.tsx        # Modal for large datasets
│   └── lib/
│       ├── db.ts                # SQLite database operations
│       ├── mcp.ts               # MCP server and tools
│       └── query-cache.ts       # Query result caching (legacy)
├── public/                      # Static assets
│   └── logo.png                 # Application logo
├── package.json                 # Next.js app configuration
└── .env.local                   # Environment variables

---

⚙️ How It Works

Query Pipeline

1. User Input: User submits a natural language question
2. Schema Fetch: Backend retrieves database schema and relationships via MCP
3. Query Decision: LLM determines if a SQL query is needed
4. SQL Generation: If needed, LLM generates a SELECT query
5. Query Execution: MCP executes the query in a secure sandbox
6. UI Generation: LLM generates openui-lang code for visualization
7. Rendering: React components render charts, tables, or metrics

Large Dataset Handling

When query results exceed 50 rows:

1. Full results are saved to the database
2. A 20-row preview is sent to the LLM
3. LLM generates a "View All" button
4. Clicking the button opens FullResultsModal with:
   • Infinite scroll pagination (100 rows/page)
   • Server-side search across all columns
   • Print functionality for all data

---

🛠️ MCP Tools

The application uses 6 MCP tools for database interaction:

Tool	Description
get_schema	Returns complete database schema (tables, columns, foreign keys)
list_tables	Lists all tables with column/row counts
get_table_details	Detailed info for a specific table with sample data
get_database_relationships	All foreign key relationships
get_sample_data	Sample rows from a table
run_query	Executes SELECT/WITH queries with security validation

Security: Only SELECT and WITH queries are allowed. Dangerous patterns (DROP, DELETE, UPDATE, etc.) are blocked.

---

🗄️ Database Schema

The pre-seeded university.db contains a comprehensive University Management System:

Category	Tables
Campus & Organization	campuses, departments, degree_programs, academic_terms, classrooms
Staff Management	staff, staff_positions_history, staff_salary_payouts, staff_attendance
Student Management	students, admissions, student_program_enrollments, student_transfers
Academics	sections, course_catalog, program_curriculum, staff_subject_allocations
Scheduling	timetable_slots, timetable_entries, student_attendance
Assessments	assessments, student_results
Fees & Finance	student_fee_invoices, student_fee_payments
Library	library_books, library_book_copies, library_memberships, library_loans
Transport	transport_routes, transport_stops, student_transport_enrollments
Other	study_materials, support_tickets

Views: vw_student_cgpa, vw_fee_outstanding

---

🧑‍💻 Tech Stack

Layer	Technology
Frontend	React 19, Next.js 16, Tailwind CSS
UI Framework	@openuidev/react-ui, @openuidev/react-lang
AI/LLM	Google Gemini (via OpenAI-compatible API)
Database	SQLite (better-sqlite3)
Protocol	Model Context Protocol (MCP)
Streaming	Server-Sent Events (SSE)

---

⚙️ Setup & Installation

Prerequisites

• Node.js 18+
• Google Gemini API key

Environment Variables

Create .env inside the root directory with:

# LLM API Key
LLM_API_KEY=your_llm_api_key

# LLM API Base URL (OpenAI-compatible)
LLM_BASE_URL=base URL of your LLM

# Path to SQLite database 
DB_PATH=database/university.db

Installation

# Install dependencies
npm install

# Start development server
npm run dev

The application will be available at http://localhost:3000

Production Build

npm run build
npm start

---

💬 Sample Queries

Try these conversation starters:

• "How many students are currently enrolled? Break it down by department."
• "Show me the top 5 departments ranked by the number of students enrolled."
• "What is the current fee collection status? How much is pending vs paid?"
• "Give me an overview of all active courses grouped by department."

---

📊 Storage

The application uses two SQLite databases:

Database	Location	Purpose
university.db	database/	Source data (read-only)
chats.db	Root directory	Chat threads, messages, and cached query results

Tables in chats.db:

• threads: Chat conversation threads
• messages: Individual messages within threads
• query_results: Cached results for large dataset queries

---

🔒 Security

• Query Validation: Only SELECT and WITH queries allowed
• SQL Injection Protection: Blocks dangerous patterns (DROP, DELETE, UPDATE, etc.)
• Read-Only Database: Source database is opened in read-only mode
• No Comment Injection: Blocks SQL comments (--, /* */)

---

🗺️ Roadmap

• Add support for multiple database drivers (PostgreSQL, MySQL, etc.)
• Implement IAM / Auth based access control for Retrieval
• Build a custom UI component library for better UI and more use case coverage
• Enhance text2SQL module while reducing the LLM dependency, so that we can even use smaller LLMs in future
• Introduce full-coverage unit testing setup

---

📝 License

See LICENSE for details.