Build Your Own AI FAQ Bot with SQL Server and Python (Step-by-Step Guide)

Are you interested in building an intelligent FAQ bot that responds to user queries using your internal FAQ database? Whether you're new to Python or just starting with AI and embeddings, this guide walks you through building a complete working solution using FastAPI, OpenAI Embeddings, and SQL Server.

🚀 What You Will Build

Uses OpenAI to generate semantic embeddings.
Stores and fetches FAQs from a SQL Server database.
Compares user questions to known FAQs using vector similarity.
Provides intelligent, relevant answers.

🌐 Tech Stack

Backend: Python + FastAPI
Database: SQL Server
Embeddings: OpenAI API
Vector similarity: Scipy + Cosine Distance
Frontend: Simple HTML & JavaScript

✅ Prerequisites

Python 3.8+
A running SQL Server instance
OpenAI API Key
Basic Python + SQL knowledge

⌛ Step-by-Step Setup

① Prepare SQL Server

CREATE DATABASE FAQBot;
USE FAQBot;

CREATE TABLE Faqs (
  id INT PRIMARY KEY IDENTITY(1,1),
  question NVARCHAR(MAX),
  answer NVARCHAR(MAX),
  embedding NVARCHAR(MAX),
  last_updated DATETIME
);

Insert some sample FAQs.

② Backend Setup (Python)

Install dependencies:

pip install fastapi uvicorn openai pyodbc scipy python-dotenv

Folder structure:

backend-python/
|- app.py
|- vector_store.py
|- embedder.py
|- db_config.py
|- .env

.env

OPENAI_API_KEY=your-openai-key

db_config.py

import pyodbc

def get_db_connection():
    return pyodbc.connect(
        'DRIVER={ODBC Driver 17 for SQL Server};'
        'SERVER=your_server;'  # hostname or domain
        'DATABASE=FAQBot;'
        'UID=your_user;PWD=your_password;'
        'TrustServerCertificate=yes;'
    )

embedder.py

import os
import openai
from dotenv import load_dotenv

load_dotenv()
openai.api_key = os.getenv("OPENAI_API_KEY")

def get_embedding(text):
    result = openai.Embedding.create(
        input=text, model="text-embedding-ada-002")
    return result['data'][0]['embedding']

vector_store.py handles loading FAQs and answering user queries using cosine similarity.

Code omitted for brevity. (See GitHub or complete file for full version)

app.py

from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from vector_store import FAQSearch

app = FastAPI()
app.add_middleware(CORSMiddleware, allow_origins=["*"], allow_methods=["*"], allow_headers=["*"])

faq_search = FAQSearch()

class QuestionRequest(BaseModel):
    question: str

@app.post("/ask")
def ask_faq(q: QuestionRequest):
    answer, _ = faq_search.ask(q.question)
    return {"answer": answer}

Run API:

uvicorn app:app --reload

🌐 Frontend (HTML UI)

Create a simple web page to interact with your bot:

<!DOCTYPE html>
<html>
<head><title>AI FAQ Bot</title></head>
<body>
<h2>Ask the FAQ Bot</h2>
<input type="text" id="question" placeholder="Type your question..." onkeypress="handleKey(event)" />
<button onclick="askBot()">Ask</button>
<div id="chat"></div>
<script>
async function askBot() {
  const question = document.getElementById("question").value;
  const res = await fetch("http://localhost:8000/ask", {
    method: "POST", headers: {"Content-Type": "application/json"},
    body: JSON.stringify({ question })
  });
  const data = await res.json();
  document.getElementById("chat").innerHTML += `<p>You: ${question}</p><p>Bot: ${data.answer}</p>`;
  document.getElementById("question").value = "";
}
function handleKey(e) { if (e.key === 'Enter') askBot(); }
</script>
</body>
</html>

📈 Future Enhancements

Add /retrain API endpoint for manual re-embedding
Auto-detect updates in DB and retrain
Add user feedback and logging
Secure API with authentication
Connect to external systems like HRMS or CRM

✉️ Final Thoughts

This bot is a practical, fast, and low-cost way to add AI to your internal tools. You can use the same foundation for HR bots, IT helpdesk bots, or sales query assistants. Explore embeddings, tweak similarity thresholds, and expand based on your own FAQs!

Happy coding! 🚀