PolyGuide - RAG Chatbot with PostgreSQL

A RAG (Retrieval Augmented Generation) chatbot system for polytechnic institute information with PostgreSQL database and FAISS vector search.

Python Structured RAG (Postgres only)

The folder rag_eng/ contains a two-pass structured RAG pipeline:

• Pass 1: LLM produces a strict JSON Query-Plan (intent + filters)
• Pass 2: Code generates safe parameterized SQL, queries PostgreSQL, then the LLM answers using only returned rows

Run (CLI)

1. Install dependencies:

pip install -r rag_eng/requirements.txt

2. Create environment file:

copy rag_eng\.env.example rag_eng\.env

3. Set values in rag_eng/.env (Postgres + GEMINI_API_KEY + RAG_LLM_MODEL) and run:

python -m rag_eng.main

Voicebot + RAG (Flask-served UI on port 8000)

This repo includes a browser voice UI in poly_voicebot/ (speech-to-text + text-to-speech). The UI is served by a small Flask app which calls the existing rag_eng RAG pipeline.

Prerequisites

• You must have Postgres/Neon credentials and a Gemini key configured in rag_eng/.env.
• If you want admissions/brochure answers, you must have the brochure FAISS index generated in ingestion/faiss_index/brochure_index/ (created via the ingestion scripts).

Setup

1. Create and configure rag_eng/.env:

copy rag_eng\.env.example rag_eng\.env

2. Install Python dependencies (same environment where you run the server):

pip install -r rag_eng/requirements.txt
pip install flask

Run

From the repo root:

python poly_voicebot/app.py

Open in your browser:

• http://localhost:8000/

Notes:

• Use Chrome/Edge for best SpeechRecognition support.
• If the mic doesn’t work, check Windows microphone permissions for your browser.

Project Structure

poly-rag-chatbot/
├── server/                 # Node.js + Express Backend
│   └── src/
│       ├── config/         # Database & environment config
│       ├── models/         # PostgreSQL table models
│       ├── controllers/    # Request handlers
│       ├── routes/         # API routes
│       ├── services/       # Business logic
│       └── utils/          # Helper functions
│
├── ingestion/              # Python Data Processing
│   ├── data/               # Raw data files (Excel, PDF)
│   ├── processed/          # Processed text & chunks
│   ├── faiss_index/        # FAISS vector index
│   └── scripts/            # Data ingestion scripts
│
└── migrations/             # SQL schema files

Setup Instructions

1. Database Setup

Option A: Local PostgreSQL (Development ye local ka hai)

Install PostgreSQL:

• Download from https://www.postgresql.org/download/
• Create database: CREATE DATABASE polyguide;

Configure .env:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=polyguide
DB_USER=postgres
DB_PASSWORD=your_password

Option B: Neon Cloud (Production/Shared Access) ☁️

Sign up for Neon:

• Go to https://neon.tech and create account
• Create a new project
• Copy connection details

Configure .env:

NEON_HOST=ep-xxxxx.aws.neon.tech
NEON_PORT=5432
NEON_DB=neondb
NEON_USER=your_username
NEON_PASSWORD=your_password

Benefits of Neon:

• ✅ Cloud-hosted (access from anywhere)
• ✅ Easy collaboration (share with team)
• ✅ Auto-scaling & backups
• ✅ Free tier available

View Data:

• Local: Use pgAdmin connecting to localhost
• Neon: Use pgAdmin or Neon SQL Editor (https://console.neon.tech)

Run Migrations:

cd ingestion
pip install -r requirements.txt
python scripts/run_migrations.py

Note: Scripts automatically detect Neon when NEON_HOST is set and use SSL connection.

2. Data Ingestion

Load Excel data to PostgreSQL:

python scripts/excel_to_sql.py

Process PDF brochure:

python scripts/pdf_to_text.py
python scripts/chunk_text.py
python scripts/create_embeddings.py
python scripts/sync_sql_faiss.py

3. Server Setup

Install dependencies:

cd server/src
npm install

Configure environment:

cp .env.example .env
# Edit .env with your database credentials

Run server:

npm run dev

Data Flow

1. Excel files → PostgreSQL (institutes, courses, hostels)
2. PDF brochure → Text → Chunks → FAISS embeddings
3. User query → Embedding → FAISS search → SQL filters → LLM response

Where Data Lives

Data Type	Storage
Institutes, Courses, Intake	PostgreSQL
Hostel capacity	PostgreSQL
Rules metadata	PostgreSQL
Brochure text chunks	FAISS
Text embeddings	FAISS
Users & authentication	PostgreSQL

API Endpoints

• GET /health - Health check
• POST /api/chat/query - Process RAG query
• GET /api/institutes - Get institutes
• GET /api/courses - Get courses
• POST /api/auth/login - User authentication

Technology Stack

Backend:

• Node.js + Express
• PostgreSQL (pg driver)
• JWT authentication

Data Processing:

• Python 3.8+
• pandas (Excel processing)
• PyPDF2 (PDF extraction)
• FAISS (vector search)
• Sentence Transformers (embeddings)

Environment Variables

Python Scripts (.env in project root)

# Local PostgreSQL
DB_HOST=localhost
DB_PORT=5432
DB_NAME=polyguide
DB_USER=postgres
DB_PASSWORD=your_password

# OR Neon Cloud (scripts auto-detect and use SSL)
NEON_HOST=ep-xxxxx.aws.neon.tech
NEON_PORT=5432
NEON_DB=neondb
NEON_USER=your_username
NEON_PASSWORD=your_password

Node.js Server (server/.env)

# Database (use same credentials as above)
NEON_HOST=ep-xxxxx.aws.neon.tech
NEON_PORT=5432
NEON_DB=neondb
NEON_USER=your_username
NEON_PASSWORD=your_password

# Server
PORT=5000
NODE_ENV=development
JWT_SECRET=your_secret_key

# Python RAG Service
PYTHON_RAG_URL=http://localhost:8000

Sharing Database Access

For Team Collaboration:

1. Using Neon (Recommended):

   • Share the connection string with team members
   • They can connect via pgAdmin, DBeaver, or scripts
   • Everyone sees the same cloud data

2. Connection String Format:

postgresql://username:password@ep-xxxxx.neon.tech/database?sslmode=require

3. Create Read-Only User (Optional):

CREATE USER readonly WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE neondb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Next Steps

1. Create API route handlers (chat, institutes, auth)
2. Build Python RAG service for query processing
3. Implement frontend client
4. Add authentication middleware
5. Deploy to production