Build a RAG Chatbot Over Your PDFs and Notes Using Supabase pgvector

What We're Building

A retrieval-augmented generation (RAG) chatbot that runs on your local machine, sucks in your PDFs and Markdown notes, stores vector embeddings in Supabase's free-tier Postgres instance, and answers questions using Google Gemini's free API. No GPU, no credit card charges, no managed vector databases that expire after 14 days.

Feature list:

• Ingests PDF and .md files from a local directory
• Chunks documents with sentence-aware splitting
• Generates embeddings via Gemini's text-embedding-004 model
• Stores vectors in Supabase pgvector (free tier: 500MB database, 2GB bandwidth)
• Retrieves top-k relevant chunks on query
• Synthesizes answers with Gemini Flash (1M free tokens/day)
• Streaming responses in the terminal

If you've already explored vector stores and want a different flavor, we have a parallel guide using Qdrant's free tier at /blog/rag-chatbot-pdfs-notes-qdrant-supabase.

Architecture Overview

The ingestion path (left) runs once or whenever you add files. The query path (right) runs interactively. Both share the same embedding model. Supabase sits in the middle as the persistent vector store—unlike in-memory stores, your embeddings survive restarts.

Prerequisites: Free Tier Accounts

Before writing a single line of code, provision these three services. All remain free indefinitely unless you blast through the limits.

1. Supabase – supabase.com
   Create a new project, note your SUPABASE_URL and SUPABASE_SERVICE_KEY (Project Settings > API). Enable the pgvector extension in the SQL editor:

   create extension if not exists vector;
   

2. Google AI Studio – aistudio.google.com
   Generate an API key. The free tier gives you 1,500 requests/day for Gemini Flash and 1,500 for embeddings. More than enough for personal RAG.

3. Python 3.11+ – You already have this. Create a virtual environment.

That's it. Total cost: $0.

Step 1: Project Setup and Dependencies

mkdir rag-supabase && cd rag-supabase
python -m venv .venv && source .venv/bin/activate
pip install llama-index llama-index-embeddings-gemini llama-index-vector-stores-supabase llama-index-llms-gemini python-dotenv pymupdf

Create .env:

GOOGLE_API_KEY=your_gemini_key
SUPABASE_URL=https://xxxxx.supabase.co
SUPABASE_SERVICE_KEY=eyJhbGciOi...

Create a data/ directory and drop in some PDFs and .md files. For testing, grab a few ArXiv papers or your own notes.

Step 2: Provisioning Supabase pgvector

LlamaIndex can auto-create the table schema, but I prefer explicit control. Run this in the Supabase SQL editor:

create table if not exists documents (
  id uuid primary key default gen_random_uuid(),
  text text not null,
  metadata jsonb default '{}'::jsonb,
  embedding vector(768)
);

create index on documents using ivfflat (embedding vector_cosine_ops) with (lists = 100);

Why 768 dimensions? Gemini's text-embedding-004 outputs 768-dimensional vectors. The ivfflat index gives approximate nearest neighbor search—fast enough for personal-scale collections.

Step 3: The Ingestion Pipeline

Create ingest.py:

import os
from dotenv import load_dotenv
from llama_index.core import SimpleDirectoryReader, Settings, StorageContext
from llama_index.core.node_parser import SentenceSplitter
from llama_index.embeddings.gemini import GeminiEmbedding
from llama_index.vector_stores.supabase import SupabaseVectorStore
from llama_index.core import VectorStoreIndex

load_dotenv()

# Configure global settings
Settings.embed_model = GeminiEmbedding(
    model_name="models/text-embedding-004",
    api_key=os.getenv("GOOGLE_API_KEY")
)
Settings.chunk_size = 512
Settings.chunk_overlap = 64

# Initialize Supabase vector store
vector_store = SupabaseVectorStore(
    postgres_connection_string=(
        f"postgresql://postgres:{os.getenv('SUPABASE_SERVICE_KEY')}"
        f"@{os.getenv('SUPABASE_URL').replace('https://', '')}:5432/postgres"
    ),
    collection_name="documents",
    dimension=768
)

# Load and ingest documents
documents = SimpleDirectoryReader("data").load_data()
parser = SentenceSplitter.from_defaults()
nodes = parser.get_nodes_from_documents(documents)

storage_context = StorageContext.from_defaults(vector_store=vector_store)
index = VectorStoreIndex(nodes, storage_context=storage_context)

print(f"Ingested {len(nodes)} chunks from {len(documents)} documents.")

Run python ingest.py. You'll see Gemini embedding calls in the logs. The first run on a few dozen PDFs takes 30-60 seconds. Subsequent runs with the same files will re-ingest—add deduplication logic if you need it.

What's happening: SimpleDirectoryReader handles PDFs (via PyMuPDF) and Markdown natively. SentenceSplitter respects sentence boundaries so chunks are coherent. Each chunk gets embedded and stored in Supabase with its text and metadata.

Step 4: Building the Query Engine

Create query_engine.py:

import os
from dotenv import load_dotenv
from llama_index.core import Settings, StorageContext, VectorStoreIndex
from llama_index.embeddings.gemini import GeminiEmbedding
from llama_index.llms.gemini import Gemini
from llama_index.vector_stores.supabase import SupabaseVectorStore

load_dotenv()

Settings.embed_model = GeminiEmbedding(
    model_name="models/text-embedding-004",
    api_key=os.getenv("GOOGLE_API_KEY")
)
Settings.llm = Gemini(
    model="models/gemini-2.0-flash",
    api_key=os.getenv("GOOGLE_API_KEY"),
    temperature=0.1
)

vector_store = SupabaseVectorStore(
    postgres_connection_string=(
        f"postgresql://postgres:{os.getenv('SUPABASE_SERVICE_KEY')}"
        f"@{os.getenv('SUPABASE_URL').replace('https://', '')}:5432/postgres"
    ),
    collection_name="documents",
    dimension=768
)

index = VectorStoreIndex.from_vector_store(vector_store)
query_engine = index.as_query_engine(
    similarity_top_k=5,
    streaming=True
)

def ask(question: str):
    response = query_engine.query(question)
    print("\n--- Answer ---")
    for token in response.response_gen:
        print(token, end="", flush=True)
    print("\n")
    print("--- Sources ---")
    for node in response.source_nodes:
        print(f"- {node.metadata.get('file_name', 'unknown')} (score: {node.score:.3f})")
    return response

This wires up the full retrieval loop: embed the query, cosine-similarity search against pgvector, feed top-5 chunks as context to Gemini Flash, stream the response.

Step 5: Wiring the Chat Loop

Create main.py:

from query_engine import ask

if __name__ == "__main__":
    print("RAG Chatbot ready. Type 'exit' to quit.\n")
    while True:
        query = input("You: ")
        if query.lower() in ("exit", "quit"):
            break
        ask(query)

Run python main.py. Ask questions that require information from your documents. The bot retrieves relevant chunks and synthesizes an answer grounded in your data.

Pro tip: If answers feel hallucinated, increase similarity_top_k to 7-10 and lower temperature to 0.0. If retrieval misses obvious matches, your chunk size might be too large—try 256 with 32 overlap.

Extensions That Actually Matter

Hybrid search. Pure vector search misses exact keyword matches. Add BM25 via Supabase's full-text search and combine scores with reciprocal rank fusion. LlamaIndex supports this natively with QueryFusionRetriever.

Metadata filtering. Tag chunks by source type or date. Add a where clause to your retriever:

from llama_index.core.vector_stores import MetadataFilters, ExactMatchFilter
query_engine = index.as_query_engine(
    filters=MetadataFilters(filters=[
        ExactMatchFilter(key="file_type", value="pdf")
    ])
)

Persistent chat history. Wrap the query engine in a ChatEngine with a memory buffer. This lets you ask follow-up questions that reference previous answers—useful when you're deep in a research session.

Self-hosted embeddings. When you outgrow Gemini's free tier, swap in a Hugging Face model running locally via llama-index-embeddings-huggingface. No API calls, no rate limits.

If you're building similar pipelines for structured data, check out our guide on /blog/personal-finance-categorizer-csv-supabase-sql which uses Supabase for a different kind of data intelligence.

Common Pitfalls

Supabase connection string confusion. The SupabaseVectorStore constructor expects a Postgres connection string, not the REST URL. Use postgresql://postgres:SERVICE_KEY@db.xxxxx.supabase.co:5432/postgres. The service key goes where the password would normally live.

Dimension mismatch. If you get a Postgres error about vector dimensions, your embedding model outputs a different size than the column definition. Gemini text-embedding-004 is 768. If you swap to OpenAI text-embedding-3-small, that's 1536—you'd need to alter the column.

Rate limiting on Gemini free tier. The free tier allows 1,500 requests/day for embeddings. Ingesting 100 PDFs with 50 chunks each is 5,000 embedding calls—you'll hit the limit. Either batch across days or use a local embedding model for bulk ingestion.

Chunks too large for Gemini Flash context. Gemini Flash has a 1M token context window, but LlamaIndex defaults to stuffing all retrieved chunks. If you're retrieving 10 chunks of 512 tokens each, that's only 5K tokens—well within limits. If you increase chunk size to 2048, watch your token consumption.

FAQ

Q: Can I use this with Obsidian or Notion exports? Yes. Export your vault as Markdown, drop it in data/, and re-run ingest.py. Notion exports to Markdown with frontmatter—LlamaIndex preserves metadata like creation date and tags.

Q: How do I avoid re-ingesting unchanged files? Store file hashes in Supabase alongside chunks. On re-ingestion, skip files whose hash matches. This is production-grade deduplication—build it once you have >100 files.

Q: What if I want to deploy this as a web app? Swap the terminal loop for a FastAPI endpoint. The query engine is stateless—just call query_engine.query() inside an async route. Stream the response with server-sent events.

Q: How does this compare to using Qdrant? Supabase pgvector is Postgres-native—you get SQL queries, row-level security, and backups for free. Qdrant is purpose-built for vector search with better performance at scale. Both have generous free tiers. We cover the Qdrant approach in /blog/rag-chatbot-pdfs-notes-qdrant-supabase.

Q: Can I use a different free LLM? Absolutely. Swap Gemini for HuggingFaceInferenceAPI with a free model like mistralai/Mistral-7B-Instruct. Latency will be higher on Hugging Face's free tier, but it works. The architecture stays identical.

Q: How do I become someone who ships this kind of thing for a living? This pattern—ingest unstructured data, embed it, retrieve context, synthesize with an LLM—is exactly what Forward Deployed Engineers build inside customer environments. If you enjoy the mix of systems thinking and rapid prototyping, /blog/what-a-forward-deployed-engineer-actually-does-in-a-week breaks down the day-to-day reality.