🧠 Smart RAG Engine

Intelligent Document Retrieval + Natural Language Database Querying

Ask questions in plain English. Get answers from your documents and databases.

---

What is Smart RAG Engine?

Smart RAG Engine is a dual-mode AI backend that lets you interact with both unstructured documents (PDFs, CSVs) and structured PostgreSQL databases using plain English — no SQL knowledge required, no manual document search.

It runs entirely on local AI models via Ollama, meaning your data never leaves your machine.

Two independent FastAPI services:

Service	Entry Point	Purpose
Database Query API	main.py → port 8000	Natural language → SQL → PostgreSQL → summarized answer
Document RAG API	src/Rag-Agent/rag.py → port 8001	Upload PDF/CSV → vector index → semantic Q&A

---

The Problem It Solves

Traditional data interaction forces users to:

• Write SQL queries to interrogate databases
• Manually search through PDF documents
• Handle structured and unstructured data with completely separate tools

Smart RAG Engine provides a unified natural-language interface for both. You ask a question, the system figures out how to retrieve the answer.

---

Architecture

graph TD
    A[User Question] --> B{Which Service?}

    B --> C[Database Query API<br/>main.py :8000]
    B --> D[Document RAG API<br/>rag.py :8001]

    C --> E[schema.py<br/>Table definitions]
    E --> F[services.py<br/>LLM generates SQL]
    F --> G[database.py<br/>psycopg2 executes SQL]
    G --> H[PostgreSQL]
    H --> I[Raw rows returned]
    I --> J[services.py<br/>LLM summarizes results]
    J --> K[Natural language answer]

    D --> L[Upload PDF or CSV]
    L --> M[PyPDF2 / pandas<br/>Text extraction]
    M --> N[RecursiveCharacterTextSplitter<br/>chunk_size=800, overlap=100]
    N --> O[OllamaEmbeddings<br/>nomic-embed-text]
    O --> P[FAISS Vector Store<br/>saved to disk per session]
    P --> Q[User asks question]
    Q --> R[Similarity search<br/>retriever]
    R --> S[RetrievalQA chain<br/>phi3:mini]
    S --> T[Natural language answer]

Loading

---

Core Features

🗄️ Natural Language → SQL

Type a question in English. The LLM reads the database schema, generates a valid PostgreSQL query, executes it, and returns a human-readable summary — not raw rows.

📄 Document Intelligence (RAG)

Upload a PDF or CSV. The system extracts text, splits it into chunks, embeds them with nomic-embed-text, and stores them in a FAISS index. Ask questions and get answers grounded in the document content.

🤖 Fully Local AI

Both the LLM (phi3:mini) and the embedding model (nomic-embed-text) run locally via Ollama. No API keys, no cloud calls, no data leaving your machine.

💬 Session-Based Context

The database API maintains per-session ConversationBufferMemory using cookies. The document API creates isolated FAISS vector stores per upload session identified by UUID.

⚡ FastAPI Backend

Both services expose clean REST endpoints with Pydantic-validated request/response models and proper HTTP error handling.

---

Technology Stack

Layer	Technology	Version	Purpose
Language	Python	3.8+	Core runtime
API Framework	FastAPI	0.117.1	REST API layer
ASGI Server	Uvicorn	0.37.0	Async HTTP server
LLM Framework	LangChain	0.3.27	RAG + chain orchestration
LLM Runtime	Ollama	—	Local model inference
Primary LLM	phi3:mini	—	SQL generation + summarization
Embedding Model	nomic-embed-text	—	Document vector embeddings
Vector Store	FAISS	—	Similarity search + persistence
Database	PostgreSQL	—	Structured data storage
DB Driver	psycopg2	2.9.10	PostgreSQL connection
PDF Parsing	PyPDF2	—	Text extraction from PDFs
CSV Parsing	pandas	—	Tabular data to text
Data Validation	Pydantic	2.11.9	Request/response schemas
Session IDs	uuid	stdlib	Unique session identifiers

---

Project Structure

Smart-Rag-Engine/
├── main.py                    # Database Query API (FastAPI, port 8000)
├── requirements.txt           # Python dependencies
├── sample.txt                 # Example queries for all 4 database tables
├── LICENSE                    # MIT License
├── .gitignore                 # Ignores data/, vectorstores/, .env, __pycache__
│
├── src/
│   ├── config/
│   │   └── database.py        # psycopg2 connection + run_sql_query()
│   ├── Schema/
│   │   └── schema.py          # TABLE_SCHEMAS dict (4 tables, all columns)
│   ├── Services/
│   │   └── services.py        # generate_sql_from_question() + generate_summary_from_results()
│   └── Rag-Agent/
│       └── rag.py             # Document RAG API (FastAPI, port 8001)
│
├── CSV Files/                 # Sample datasets for database import
│   ├── student.csv            # Student academic records
│   ├── user.csv               # User accounts with signup dates
│   ├── pizza.csv              # Pizza restaurant reviews
│   └── products.pdf           # Product catalog (PDF format)
│
├── data/                      # Runtime: uploaded files stored here (gitignored)
└── vectorstores/              # Runtime: FAISS indexes stored here (gitignored)

---

Prerequisites

• Python 3.8 or higher
• PostgreSQL (any recent version) running locally
• Ollama installed and running — ollama.ai
• Git

---

Installation

1. Clone the repository

git clone https://github.com/SadiqCodex/Smart-Rag-Engine.git
cd Smart-Rag-Engine

2. Create and activate a virtual environment

Windows:

python -m venv venv
venv\Scripts\activate

Linux / macOS:

python3 -m venv venv
source venv/bin/activate

3. Install dependencies

pip install -r requirements.txt

---

Ollama Setup

Install Ollama from ollama.ai, then pull the two required models:

# Primary LLM — used for SQL generation and result summarization
ollama pull phi3:mini

# Embedding model — used for document vector search
ollama pull nomic-embed-text

# Start the Ollama server (if not already running)
ollama serve

Verify both models are available:

ollama list

---

Database Setup

1. Create the PostgreSQL database and tables

The application expects four tables. Import the sample data from CSV Files/:

-- students table
CREATE TABLE students (
    id              INTEGER,
    name            TEXT,
    age             INTEGER,
    gender          TEXT,
    class           INTEGER,
    section         TEXT,
    maths           INTEGER,
    science         INTEGER,
    english         INTEGER,
    hindi           INTEGER,
    social_studies  INTEGER,
    grade           TEXT,
    phone           TEXT,
    email           TEXT,
    address         TEXT
);

-- products table
CREATE TABLE products (
    product_id      INTEGER,
    product_name    TEXT,
    category        TEXT,
    price           NUMERIC,
    stock_status    TEXT
);

-- users table
CREATE TABLE users (
    id              INTEGER,
    username        TEXT,
    email           TEXT,
    signup_date     DATE,
    status          TEXT
);

-- pizza_reviews table
CREATE TABLE pizza_reviews (
    id      INTEGER,
    title   TEXT,
    review  TEXT,
    rating  NUMERIC,
    date    DATE
);

2. Configure the database connection

Edit src/config/database.py with your credentials:

DB_PARAMS = {
    "dbname": "your_database",
    "user": "your_username",
    "password": "your_password",
    "host": "localhost",
    "port": "5432"
}

⚠️ Never commit real credentials. Add src/config/database.py to .gitignore or use environment variables for production.

---

Running the Application

The two services run independently on different ports.

Database Query API (port 8000):

uvicorn main:app --reload --host 0.0.0.0 --port 8000

Document RAG API (port 8001):

uvicorn src.Rag-Agent.rag:app --reload --host 0.0.0.0 --port 8001

Or run the RAG service directly from its directory:

cd src/Rag-Agent
uvicorn rag:app --reload --port 8001

---

API Reference

Database Query API — POST /ask

Ask a natural-language question about the PostgreSQL database. The system generates SQL, executes it, and returns a summarized answer.

Request

{
  "question": "How many active users are there?"
}

Response

{
  "answer": "There are 60 active users in the database."
}

Error Response (SQL execution failure)

{
  "error": "SQL execution error: column 'xyz' does not exist"
}

Session state is maintained via a session_id cookie set automatically on the first request.

---

Document RAG API — POST /upload/

Upload a PDF or CSV file. Returns a session_id used for subsequent questions.

Request — multipart/form-data

file: [your .pdf or .csv file]

Response

{
  "session_id": "351ae27c-0355-416f-9fcd-c5d24342f7b4",
  "message": "PDF uploaded & processed successfully"
}

Unsupported format response

{
  "error": "Only PDF and CSV supported"
}

---

Document RAG API — POST /ask/

Ask a question about a previously uploaded document.

Request — multipart/form-data

session_id: 351ae27c-0355-416f-9fcd-c5d24342f7b4
question:   What products are listed in this catalog?

Response

{
  "session_id": "351ae27c-0355-416f-9fcd-c5d24342f7b4",
  "question": "What products are listed in this catalog?",
  "answer": "The catalog includes electronics, clothing, and home goods..."
}

Invalid session response

{
  "error": "Invalid or expired session_id"
}

---

Document RAG API — GET /reset/

Clears all active sessions and deletes all uploaded files and vector stores from disk.

Response

{
  "message": "All sessions cleared"
}

---

Example Queries

Database API — curl

# Count students in class 10
curl -X POST "http://localhost:8000/ask" \
  -H "Content-Type: application/json" \
  -d '{"question": "How many students are in class 10?"}'

# Filter by score
curl -X POST "http://localhost:8000/ask" \
  -H "Content-Type: application/json" \
  -d '{"question": "Show students who scored more than 90 in maths"}'

# Product query
curl -X POST "http://localhost:8000/ask" \
  -H "Content-Type: application/json" \
  -d '{"question": "List all out-of-stock products"}'

# Review aggregation
curl -X POST "http://localhost:8000/ask" \
  -H "Content-Type: application/json" \
  -d '{"question": "Show pizza reviews with rating above 4"}'

# User query
curl -X POST "http://localhost:8000/ask" \
  -H "Content-Type: application/json" \
  -d '{"question": "How many users signed up in 2023?"}'

Document RAG API — curl

# Step 1: Upload a file
curl -X POST "http://localhost:8001/upload/" \
  -F "file=@CSV Files/student.csv"

# Step 2: Ask a question (use the session_id from the upload response)
curl -X POST "http://localhost:8001/ask/" \
  -F "session_id=351ae27c-0355-416f-9fcd-c5d24342f7b4" \
  -F "question=What grades do most students have?"

Python Client

import requests

BASE_DB  = "http://localhost:8000"
BASE_RAG = "http://localhost:8001"

# --- Database query ---
def ask_database(question: str) -> str:
    resp = requests.post(f"{BASE_DB}/ask", json={"question": question})
    return resp.json().get("answer")

# --- Document RAG ---
def upload_document(file_path: str) -> str:
    with open(file_path, "rb") as f:
        resp = requests.post(f"{BASE_RAG}/upload/", files={"file": f})
    return resp.json()["session_id"]

def ask_document(session_id: str, question: str) -> str:
    resp = requests.post(f"{BASE_RAG}/ask/",
                         data={"session_id": session_id, "question": question})
    return resp.json().get("answer")

# Usage
print(ask_database("List all female students in section A"))

sid = upload_document("CSV Files/pizza.csv")
print(ask_document(sid, "What are the most common complaints in the reviews?"))

JavaScript Client

// Database query
async function askDatabase(question) {
  const res = await fetch("http://localhost:8000/ask", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ question }),
    credentials: "include",   // sends session cookie
  });
  return res.json();
}

// Upload document
async function uploadDocument(file) {
  const form = new FormData();
  form.append("file", file);
  const res = await fetch("http://localhost:8001/upload/", {
    method: "POST",
    body: form,
  });
  return res.json();  // { session_id, message }
}

// Ask about document
async function askDocument(sessionId, question) {
  const form = new FormData();
  form.append("session_id", sessionId);
  form.append("question", question);
  const res = await fetch("http://localhost:8001/ask/", {
    method: "POST",
    body: form,
  });
  return res.json();
}

---

RAG Pipeline — How It Works

When you upload a document and ask a question, the system follows these steps:

Step 1 — Upload & Save The file is saved to data/ with a UUID filename to avoid collisions.

Step 2 — Text Extraction

• PDF files → PyPDF2.PdfReader, iterates all pages
• CSV files → pandas.read_csv(), converts the DataFrame to a string representation

Step 3 — Chunking RecursiveCharacterTextSplitter splits the text into overlapping chunks:

• chunk_size = 800 characters
• chunk_overlap = 100 characters

Each chunk becomes a langchain.docstore.document.Document object.

Step 4 — Embedding OllamaEmbeddings(model="nomic-embed-text") converts each chunk into a dense vector using the locally running nomic-embed-text model.

Step 5 — Vector Store Creation FAISS.from_documents() builds an in-memory index, then saves it to vectorstores/<session_id>.faiss/ for persistence across requests.

Step 6 — Retrieval On each question, the FAISS index is loaded from disk, and vectorstore.as_retriever() performs similarity search to find the most relevant chunks.

Step 7 — Answer Generation RetrievalQA.from_chain_type() with chain_type="stuff" passes the retrieved chunks as context to OllamaLLM(model="phi3:mini"), which generates the final answer.

---

Natural Language Database Querying — How It Works

Step 1 — Schema injection schema.py defines TABLE_SCHEMAS, a dictionary of all four tables and their columns with types. This is formatted into a string and injected into the LLM prompt.

Step 2 — SQL generation generate_sql_from_question() in services.py sends a structured prompt to phi3:mini instructing it to return only a valid SQL query — no explanation, no markdown, just SQL.

Step 3 — SQL cleaning The raw LLM output is cleaned with a regex split on ; to extract the first complete statement.

Step 4 — Execution run_sql_query() in database.py opens a psycopg2 connection using RealDictCursor, executes the SQL, and returns results as a list of dictionaries.

Step 5 — Serialization Decimal and datetime types are serialized to JSON-safe formats before being stored in memory or passed to the summarizer.

Step 6 — Summarization generate_summary_from_results() sends the raw result rows to phi3:mini with a prompt asking for a concise summary (≤100 words).

Step 7 — Session memory ConversationBufferMemory stores the question/answer pair per session, keyed by a UUID stored in a browser cookie.

---

Sample Datasets

The CSV Files/ directory contains sample data ready to import into PostgreSQL:

File	Table	Description
student.csv	students	~100 student records with academic scores across 5 subjects, grades, and contact info
user.csv	users	100 user accounts with signup dates (2023) and active/inactive status
pizza.csv	pizza_reviews	200 pizza restaurant reviews with ratings (1–5) and review text
products.pdf	products	Product catalog in PDF format (also usable with the Document RAG API)

These files can also be uploaded directly to the Document RAG API for semantic Q&A without a database.

---

Session Management

Database API sessions (main.py):

• A UUID session_id is generated on the first request if no cookie is present
• ConversationBufferMemory is stored in an in-memory conversations dict keyed by session_id
• The session_id is set as an HTTP cookie in the response
• Sessions persist only for the lifetime of the running process

Document RAG sessions (rag.py):

• A UUID session_id is generated at upload time
• The FAISS vector store is saved to vectorstores/<session_id>.faiss/ on disk
• The sessions dict maps session_id → vector store path
• Sessions persist only for the lifetime of the running process; vector stores persist on disk until /reset/ is called

---

Error Handling

Scenario	Behavior
SQL execution fails	Returns HTTP 400 with {"error": "SQL execution error: ..."}
Unsupported file type uploaded	Returns {"error": "Only PDF and CSV supported"}
Question asked with invalid session_id	Returns {"error": "Invalid or expired session_id"}
No rows returned from SQL	Returns "No relevant data found to summarize."
Ollama not running	ConnectionRefusedError raised at request time

---

Troubleshooting

PostgreSQL connection error

• Verify PostgreSQL is running: pg_isready or check your system services
• Double-check credentials in src/config/database.py
• Ensure the database and all four tables exist

Ollama model not found

ollama pull phi3:mini
ollama pull nomic-embed-text
ollama serve

ModuleNotFoundError for Schema.schema The services.py file imports from Schema.schema import TABLE_SCHEMAS. Run the database API from the project root so Python resolves the path correctly:

# From project root
uvicorn main:app --reload

Vector store errors on /ask/ The sessions dict is in-memory. If the server restarts, session state is lost even though .faiss files remain on disk. Re-upload the document to get a new session_id.

Clear all vector stores manually

# Windows
rmdir /s /q vectorstores
mkdir vectorstores

# Linux/macOS
rm -rf vectorstores/*

Dependency conflicts

pip install --upgrade -r requirements.txt

---

Security Notes

Implemented:

• Database credentials are isolated in src/config/database.py
• data/ and vectorstores/ are gitignored — uploaded files and indexes are not committed
• .env is gitignored

Recommended for production:

• Move DB_PARAMS to environment variables or a .env file — never hardcode credentials in source files
• Validate uploaded file types and sizes before processing
• Restrict PostgreSQL user permissions to SELECT only for the query service
• Run behind HTTPS with a reverse proxy (nginx, Caddy)
• Add rate limiting to the /ask endpoints
• Scope CORS origins explicitly

---

Limitations

• SQL generation accuracy depends on the quality of phi3:mini. Complex multi-join queries or ambiguous questions may produce incorrect SQL.
• Document extraction quality depends on the input. Scanned PDFs without OCR will produce poor or empty text.
• Session state is in-memory — restarting the server loses all active sessions.
• No authentication — the APIs are open by default.
• Local inference speed depends on your hardware. phi3:mini is lightweight but still requires a capable CPU or GPU for reasonable latency.
• CSV-to-text conversion uses df.to_string(), which may not be optimal for very large CSV files.

---

Roadmap

• Document ingestion (PDF + CSV)
• FAISS vector store with session isolation
• Natural language → SQL → PostgreSQL pipeline
• LLM-powered result summarization
• Conversation memory per session
• Fully local inference via Ollama
• Environment variable configuration (.env support)
• Streaming responses for long answers
• Background document processing (async upload)
• Automated test suite
• Authentication / API key middleware
• Rate limiting
• Hybrid retrieval (keyword + semantic)
• Observability / request logging
• Docker Compose setup

---

Contributing

Contributions are welcome.

# Fork the repo, then:
git clone https://github.com/SadiqCodex/Smart-Rag-Engine.git
cd Smart-Rag-Engine
git checkout -b feature/your-feature-name

1. Make your changes
2. Test manually against both APIs
3. Update this README if your change affects setup or usage
4. Commit with a clear message
5. Push and open a Pull Request against main

Please follow PEP 8 and add type hints to new functions.

---

License

MIT License — see LICENSE for details.

Copyright (c) 2024 Sadik Mohammad

---

Author

Sadik Mohammad GitHub: SadiqCodex Repository: SadiqCodex/Smart-Rag-Engine

---

Acknowledgments

• FastAPI — the API layer
• LangChain — RAG and chain orchestration
• Ollama — local LLM inference
• FAISS — vector similarity search
• PostgreSQL — structured data storage

---

Smart RAG Engine demonstrates how a modern LLM application can bridge unstructured documents and structured relational databases through retrieval-augmented generation, FAISS vector search, schema-aware SQL generation, and fully local model inference — without sending a single byte of your data to an external service.