Build a Local RAG Assistant

Build a private local RAG assistant with SQLite search and cited answers.

Introduction

30 Second Summary

Research notes often end up scattered across PDFs and text files. Finding the passage that supports an answer can take longer than reading it.

In this project, you will build a private research assistant that answers questions from documents on your computer. It uses retrieval-augmented generation with Ollama to ground each answer in passages stored in SQLite.

What You'll Build

Your browser shows a cited answer above the supporting passages after you upload PDF or TXT files and enter a question.

By the end of this project, you'll have:

  • A document workspace that accepts multiple PDF or TXT files. You can inspect readable page previews before indexing the documents.
  • A search comparison that exposes a zero-match literal search before semantic retrieval ranks relevant evidence. The indexed chunks remain available in research.db after app restarts.
  • A grounded answer supported by retrieved passages. Each result shows source filenames plus page numbers beside its similarity score.
  • Secret Mission: Add a source selector that limits retrieval to the documents you choose.

Are there any prerequisites?

Basic Python syntax is enough for this project. Start with a Windows computer that already has Python plus Ollama installed.

Before We Start

This first checkpoint locks in the documents your private research assistant will study. It also captures why keeping that work on your own computer matters to you.

Set Up the Windows Workspace

Your research assistant runs entirely on your Windows computer. You do not need to sign in to a cloud service.

A missing command or incompatible Python release can stop every later stage. A dedicated virtual environment prevents package conflicts.

By the end of this step, your pinned packages run inside an isolated workspace. A starter Streamlit page proves that the local setup works.

In this step, get ready to:
  • Confirm that every local tool is reachable.
  • Build an isolated Python environment with pinned dependencies.
  • Launch the starter research assistant with sample documents ready.
Verify your local tools

A reproducible workspace starts with a supported runtime plus reachable local tools. You will use PowerShell as the shared checkpoint for Visual Studio Code plus Ollama.

  • Press the Windows key to open Windows search.
  • Type PowerShell.
  • Press Enter to open PowerShell.
  • Check your installed Python release by running this command:
python --version

What does this check?

This command asks the Python executable on your Windows path to report its release. The result confirms whether PowerShell can reach a supported runtime.

✔️ I see a supported Python version

Your Python release must fall between 3.10 and 3.14. Your runtime is ready for the pinned packages in this project.

ⓧ I see an older Python version

The installed release falls outside this project's supported range. Install a Windows release from 3.10 through 3.14 before continuing.

  • Open the official Streamlit command-line installation guide.
  • Install a supported Python release for Windows.
  • Close PowerShell after the installation finishes.
  • Reopen PowerShell from Windows search.
  • Return to the version check above.

ⓧ Python is unavailable

PowerShell cannot reach Python yet. Install a supported Windows release before creating the project environment.

  • Open the official Streamlit command-line installation guide.
  • Install a Python release from 3.10 through 3.14.
  • Close PowerShell after the installation finishes.
  • Reopen PowerShell from Windows search.
  • Return to the version check above.
  • Check whether the Visual Studio Code command-line tool is available by running this command:
code --version

What does this check?

The command prints the installed Visual Studio Code version when its command-line tool is available through your Windows path. You will use the editor to manage every project file.

✔️ I see a Visual Studio Code version

Visual Studio Code is installed. PowerShell can also reach its command-line tool.

ⓧ The code command is unavailable

The Visual Studio Code User setup adds the command-line tool to your Windows path. Reinstalling with that setup resolves this state.

  • Open the official Visual Studio Code Windows setup guide.
  • Install Visual Studio Code with the User setup.
  • Close PowerShell after the installation finishes.
  • Reopen PowerShell from Windows search.
  • Return to the Visual Studio Code check above.
  • Confirm that PowerShell can reach Ollama by running this command:
ollama ls

What does this check?

This command asks Ollama for the models already stored on your computer. An empty list still confirms that the local service is reachable.

You will see installed model names or an empty list. Either result confirms that Ollama is ready for later model downloads.

Ollama unavailable?

Start Ollama from Windows search. Reopen PowerShell after the app starts.

If the check still fails, help me troubleshoot why PowerShell cannot reach Ollama.

Create the isolated workspace

The project needs one predictable location for its code plus its local environment. Keeping both inside local-rag-assistant makes the workspace easy to find.

  • Press Windows+E to open File Explorer.
  • Select Desktop in the left navigation pane.
  • Press Ctrl+Shift+N to create a folder.
  • Enter local-rag-assistant as the folder name.
  • Press Enter to save the folder name.

Your project now has a permanent home on the Desktop. That folder holds every local artifact you build in this project.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code.
  • Press Enter to open Visual Studio Code.
  • Select File from the menu bar.
  • Select Open Folder....
  • Choose Desktop\local-rag-assistant.
  • Select Select Folder.

You will see local-rag-assistant at the top of the Explorer view. This confirms that Visual Studio Code is working inside the correct folder.

  • Select View from the menu bar.
  • Select Terminal to open the integrated terminal.

The integrated terminal starts inside local-rag-assistant. Its PowerShell prompt keeps setup commands tied to the correct folder.

  • Select Explorer in the Activity Bar.
  • Select the New File... button.
  • Enter requirements.txt.
  • Press Enter to create the file.
  • Add the pinned project dependencies by pasting this content:
streamlit==1.64.0
pypdf==6.18.1
ollama==0.6.2

What do these dependencies provide?

  • Streamlit 1.64.0 provides the browser interface.
  • pypdf 6.18.1 extracts text from PDF pages.
  • Ollama Python 0.6.2 connects the app to your local models.
  • Press Ctrl+S to save requirements.txt.
  • Confirm that requirements.txt is listed under local-rag-assistant in the Explorer view.

Requirements file missing?

Check that requirements.txt sits directly under local-rag-assistant. Move it there if it appears outside the project folder.

If the file name looks different, help me check my requirements file in Visual Studio Code.

  • Create the isolated .venv environment by running this command:
python -m venv .venv

What does this command do?

Python creates a private environment inside .venv. Packages installed after activation stay separate from your other Python projects.

  • Confirm that .venv appears under local-rag-assistant in the Explorer view.

Virtual environment missing?

Confirm that the terminal prompt points to local-rag-assistant. Check the Python version again if the command exits before creating .venv.

If the folder still does not appear, help me troubleshoot Python virtual environment creation on Windows.

  • Activate the project environment by running this command:
.venv\Scripts\Activate.ps1

What does activation change?

Activation points Python package commands at the environment inside .venv. The terminal prompt identifies the active environment.

✔️ I see the active environment

Your PowerShell prompt now starts with (.venv). The isolated environment is active.

ⓧ PowerShell blocked the script

Changing an execution policy can feel sensitive. This documented fallback limits the policy change to your current Windows user.

  • Apply the user-scope activation fallback by running this command:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

What does this fallback change?

The command changes the execution policy at CurrentUser scope. It allows PowerShell to run the virtual environment activation script.

If PowerShell still blocks activation, help me troubleshoot the Windows activation policy.

  • Return to the activation command above.
  • Confirm that the prompt starts with (.venv).
  • Install the pinned dependencies into the active environment by running this command:
python -m pip install -r requirements.txt

What does this install?

Python reads the three exact package pins from requirements.txt. The active environment receives those versions without changing your global Python installation.

The terminal returns to its PowerShell prompt after the packages install. Your local environment can now import every dependency required by the project.

Package installation failed?

Confirm that your terminal prompt starts with (.venv). Check that requirements.txt contains the three pins exactly as shown.

If installation still fails, help me troubleshoot my pinned Python package installation.

Add the starter files

The starter page gives the environment a visible job. Two sample documents also prepare the exact evidence used in the retrieval steps ahead.

  • Select local-rag-assistant in the Explorer view.
  • Select the New Folder button.
  • Enter sample_docs.
  • Press Enter to create the folder.
  • Select sample_docs in the Explorer view.
  • Select the New File... button.
  • Enter remote_work.txt.
  • Press Enter to create the file.
  • Add the remote-work sample by pasting this content:
Remote teams perform better when expectations are explicit. Written decision logs, overlapping communication hours, and clear ownership reduce delays. Teams that document decisions spend less time repeating discussions and can keep projects moving across time zones.

Why use this sample?

This passage answers a productivity question without repeating the question's exact wording. That difference creates the literal-search obstacle you will test later.

  • Press Ctrl+S to save remote_work.txt.
  • Confirm that remote_work.txt appears under sample_docs in the Explorer view.

Remote-work sample missing?

Check that remote_work.txt sits inside sample_docs. Confirm that the file ends with the .txt extension.

If the file appears elsewhere, help me fix my sample document structure.

  • Select sample_docs in the Explorer view.
  • Select the New File... button.
  • Enter security_notes.txt.
  • Press Enter to create the file.
  • Add the local-security sample by pasting this content:
Sensitive research material should remain on a controlled machine when external processing is not approved. Local inference can keep document text away from third-party APIs, but the operator must still protect the device, review model licenses, and remove stored indexes when the work ends.

Why use a second sample?

This passage gives the assistant a separate research topic. Later retrieval checks can compare evidence across multiple filenames.

  • Press Ctrl+S to save security_notes.txt.
  • Confirm that security_notes.txt appears under sample_docs in the Explorer view.

Security sample missing?

Check that security_notes.txt sits beside remote_work.txt inside sample_docs.

If the folder structure differs, help me place both sample documents correctly.

  • Select local-rag-assistant in the Explorer view.
  • Select the New File... button.
  • Enter app.py.
  • Press Enter to create the file.
  • Build the starter interface by pasting this code:
import streamlit as st

st.title("Local RAG Research Assistant")
st.write("Upload PDF or TXT files, compare literal search with semantic retrieval, and generate a cited answer using local Ollama models.")

What does this code do?

  • The import makes Streamlit's interface functions available through st.
  • The title names the local research assistant in the browser.
  • The supporting text previews the upload-to-answer workflow.
  • Press Ctrl+S to save app.py.
  • Confirm that app.py appears directly under local-rag-assistant in the Explorer view.

Starter file missing?

Check that app.py sits beside requirements.txt. Confirm that Visual Studio Code did not save it inside sample_docs.

If the file location is unclear, help me verify my local RAG project structure.

✔️ Awesome, I've got everything!

Your environment plus starter files are ready. Save every open editor tab before launching the app.

ⓧ I'd like to double check the full code

.venv

The .venv folder was created with the documented virtual environment command. Its activation script is running in the PowerShell terminal.

requirements.txt
streamlit==1.64.0
pypdf==6.18.1
ollama==0.6.2

What should match?

Your dependency file contains these three lines in this order. Each package has an exact version pin.

app.py
import streamlit as st

st.title("Local RAG Research Assistant")
st.write("Upload PDF or TXT files, compare literal search with semantic retrieval, and generate a cited answer using local Ollama models.")

What should match?

Your starter app contains one import plus two interface statements. The title text must match exactly.

sample_docs/remote_work.txt
Remote teams perform better when expectations are explicit. Written decision logs, overlapping communication hours, and clear ownership reduce delays. Teams that document decisions spend less time repeating discussions and can keep projects moving across time zones.

What should match?

The remote-work sample is one paragraph. It describes productive distributed teams without repeating the default research question.

sample_docs/security_notes.txt
Sensitive research material should remain on a controlled machine when external processing is not approved. Local inference can keep document text away from third-party APIs, but the operator must still protect the device, review model licenses, and remove stored indexes when the work ends.

What should match?

The security sample is one paragraph. It describes the responsibilities that remain when research processing stays local.

Streamlit starts a local web server for the starter interface. The command keeps this PowerShell terminal busy until you stop it with Ctrl+C.

Before you launch it, what title do you expect to see at the top of the browser page?

  • Launch the starter research assistant by running this command:
python -m streamlit run app.py

What does this command do?

Python runs Streamlit from the active virtual environment. Streamlit serves app.py through a local browser page.

Your browser displays Local RAG Research Assistant above the workflow description. That page is visible proof that the Windows workspace can run your local app.

Starter page not visible?

Check the terminal for the local address printed by Streamlit. Open that address in your browser if no tab opened automatically.

Confirm that the prompt showed (.venv) before launch. If the app still fails, help me troubleshoot my Streamlit starter page.

Your local workspace is running from an isolated environment. Next, you will turn uploaded PDF plus TXT files into page-aware text that you can inspect.

Extract and Preview Documents

Your Streamlit starter page proves that your local workspace can serve an app. Your research assistant now needs a way to turn uploaded files into text it can inspect.

Retrieval depends on clean text that preserves its original filename and page number. In this step, you will extract PDF and TXT content before anything is indexed.

In this step, get ready to:
  • Extract readable text with source and page metadata.
  • Split extracted pages into overlapping chunks.
  • Upload documents and preview their extracted text.
Extract page-aware text

An uploaded file arrives as bytes. The extraction layer decodes TXT files. It reads each PDF page separately so every result keeps its source and page number.

  • Switch back to app.py in Visual Studio Code.
  • Find the existing import streamlit as st line.
  • Build the extraction foundation by replacing that line with the code below:
import sqlite3

import streamlit as st
from pypdf import PdfReader

DB_PATH = "research.db"
CHUNK_SIZE = 900
CHUNK_OVERLAP = 150


def extract_documents(uploaded_files):
    pages = []
    for uploaded_file in uploaded_files:
        uploaded_file.seek(0)
        source = uploaded_file.name
        if source.lower().endswith(".pdf"):
            reader = PdfReader(uploaded_file)
            for page_number, page in enumerate(reader.pages, start=1):
                text = (page.extract_text() or "").strip()
                if text:
                    pages.append({"source": source, "page": page_number, "text": text})
        else:
            text = uploaded_file.getvalue().decode("utf-8", errors="replace").strip()
            if text:
                pages.append({"source": source, "page": 1, "text": text})
    return pages

What does this code do?

  • The PdfReader import provides text extraction for readable PDF pages.
  • The extract_documents() function turns each file into page records with source and page metadata.
  • The TXT path decodes file bytes as utf-8 while replacing unreadable characters.
  • The DB_PATH constant records the filename that later SQLite storage code will use. No database connection is opened in this step.
  • Save app.py.
  • Return to the browser tab from the previous step.

Good progress. You should see the starter title after Streamlit reruns the file.

Does the page show an import error?

Confirm that from pypdf import PdfReader matches the code above. Also confirm that your existing virtual environment remains activated in PowerShell.

If the page still fails to load, help me debug the document extraction imports.

Split pages into overlapping chunks

A chunk is a smaller passage that retrieval can compare with a question. A 150-character overlap carries nearby context across each 900-character boundary.

  • Find the return pages line at the end of extract_documents().
  • Add the chunking function below extract_documents() by pasting this code:
def chunk_pages(pages):
    chunks = []
    for page in pages:
        text = page["text"]
        start = 0
        chunk_index = 0
        while start < len(text):
            end = min(start + CHUNK_SIZE, len(text))
            content = text[start:end].strip()
            if content:
                chunks.append({
                    "id": f"{page['source']}|{page['page']}|{chunk_index}",
                    "source": page["source"],
                    "page": page["page"],
                    "chunk_index": chunk_index,
                    "content": content,
                })
            if end == len(text):
                break
            start = end - CHUNK_OVERLAP
            chunk_index += 1
    return chunks

How does chunking work?

  • The start and end positions select one passage at a time.
  • Each chunk receives an id built from its filename, page number, and position.
  • The next chunk starts 150 characters before the previous chunk ended. This preserves context near a boundary.
  • The loop stops when it reaches the end of the page. This prevents a repeated final chunk.
  • Save app.py.
  • Return to the running app in your browser.

You should see the starter title load again. This confirms that the chunking function is valid Python.

Did the app stop reloading?

Check the indentation inside chunk_pages(). The break line belongs inside the length check.

For targeted help, help me fix my chunk_pages function.

Connect uploads and previews

The extraction functions now need a visible entry point. The upload widget passes selected files into those functions before the app displays counts and previews.

  • Find the existing st.write() line below the app title.
  • Connect the upload interface below that line by pasting this code:
uploaded_files = st.file_uploader("Upload research documents", type=["pdf", "txt"], accept_multiple_files=True)
pages = extract_documents(uploaded_files)
chunks = chunk_pages(pages)

if uploaded_files:
    st.write(f"Extracted pages: {len(pages)}")
    st.write(f"Prepared chunks: {len(chunks)}")
    if not pages:
        st.write("No readable text was extracted. Scanned PDFs need OCR, which is outside this project.")
    for page in pages[:3]:
        with st.expander(f"Preview: {page['source']}, page {page['page']}"):
            st.write(page["text"][:1000])

What does the interface show?

  • The st.file_uploader() widget accepts multiple PDF and TXT files.
  • The page and chunk totals expose how much content the app prepared.
  • The first three page records appear inside preview expanders.
  • A scanned PDF can produce no readable text because pypdf does not perform OCR.
  • Save app.py.

Before the check, consider whether each short sample file will become one page and one chunk.

  • Return to the running Streamlit app in your browser.
  • Upload both sample_docs/remote_work.txt and sample_docs/security_notes.txt through Upload research documents.

You should see Extracted pages: 2 followed by Prepared chunks: 2. You should also see one preview expander for each sample file.

  • Upload a text-based PDF through the same uploader if you have one available.

The page count should increase by the number of readable PDF pages. The first three extracted pages remain available as previews.

Are the counts or previews missing?

Confirm that the uploader allows both pdf and txt file types. Check that pages is passed into chunk_pages().

A scanned PDF may contain images without extractable text. If your sample TXT files also fail, help me debug my upload and preview flow.

✔️ Awesome, I've got everything!

  • Confirm that app.py is saved.
  • Keep the Streamlit app running for the next step.

ⓧ I'd like to double check the full code

import sqlite3

import streamlit as st
from pypdf import PdfReader

DB_PATH = "research.db"
CHUNK_SIZE = 900
CHUNK_OVERLAP = 150


def extract_documents(uploaded_files):
    pages = []
    for uploaded_file in uploaded_files:
        uploaded_file.seek(0)
        source = uploaded_file.name
        if source.lower().endswith(".pdf"):
            reader = PdfReader(uploaded_file)
            for page_number, page in enumerate(reader.pages, start=1):
                text = (page.extract_text() or "").strip()
                if text:
                    pages.append({"source": source, "page": page_number, "text": text})
        else:
            text = uploaded_file.getvalue().decode("utf-8", errors="replace").strip()
            if text:
                pages.append({"source": source, "page": 1, "text": text})
    return pages


def chunk_pages(pages):
    chunks = []
    for page in pages:
        text = page["text"]
        start = 0
        chunk_index = 0
        while start < len(text):
            end = min(start + CHUNK_SIZE, len(text))
            content = text[start:end].strip()
            if content:
                chunks.append({
                    "id": f"{page['source']}|{page['page']}|{chunk_index}",
                    "source": page["source"],
                    "page": page["page"],
                    "chunk_index": chunk_index,
                    "content": content,
                })
            if end == len(text):
                break
            start = end - CHUNK_OVERLAP
            chunk_index += 1
    return chunks


st.title("Local RAG Research Assistant")
st.write("Upload PDF or TXT files, compare literal search with semantic retrieval, and generate a cited answer using local Ollama models.")

uploaded_files = st.file_uploader("Upload research documents", type=["pdf", "txt"], accept_multiple_files=True)
pages = extract_documents(uploaded_files)
chunks = chunk_pages(pages)

if uploaded_files:
    st.write(f"Extracted pages: {len(pages)}")
    st.write(f"Prepared chunks: {len(chunks)}")
    if not pages:
        st.write("No readable text was extracted. Scanned PDFs need OCR, which is outside this project.")
    for page in pages[:3]:
        with st.expander(f"Preview: {page['source']}, page {page['page']}"):
            st.write(page["text"][:1000])

Your assistant can now turn local documents into page-aware chunks that you can inspect. Next, you will test why literal phrase matching misses relevant evidence.

Watch Literal Search Fail

Your Streamlit app can now turn uploaded documents into page-aware chunks. Those chunks give your research assistant evidence it can search.

Documents often answer a question using different wording. This step tests how a literal phrase search handles that gap.

In this step, get ready to:
  • Add a normalized literal phrase search to app.py.
  • Connect the default research question to a search button.
  • Run the supplied test against both sample documents.
Define the literal search rule

A literal search checks whether the complete question appears inside a chunk. Lowercasing removes capitalization differences. Trimming removes stray spaces.

  • In app.py, find CHUNK_OVERLAP = 150.
  • Add the default research question directly below that line by copying this code:
DEFAULT_QUESTION = "What helps distributed teams stay productive?"

What does this line do?

The DEFAULT_QUESTION constant stores the supplied test question in one place. The interface uses this value to prefill the research field.

  • In app.py, find the end of chunk_pages() directly above st.title.
  • Add the literal search function in that location by copying this code:
def literal_phrase_search(chunks, question):
    normalized_question = question.strip().lower()
    if not normalized_question:
        return []
    return [chunk for chunk in chunks if normalized_question in chunk["content"].lower()]

What does this function do?

  • The normalized_question value removes surrounding spaces from the complete question.
  • The empty-question check prevents a blank search from matching every chunk.
  • The final list keeps only chunks containing the complete normalized question.
  • Save app.py.
  • Return to the browser tab running the app.

Your existing title and upload interface should still load. This confirms the new constant and function contain valid Python syntax.

Did the app stop refreshing?

  • Check that literal_phrase_search starts at the left edge of app.py.
  • Check that its indented lines use the same spacing as the other functions.

If the app still fails to load, help me debug the literal search function.

Connect the search controls

The function needs a question from the interface before it can search the chunks. A text field captures that question. A button starts the comparison.

  • In app.py, find st.write(page["text"][:1000]) at the end of the preview loop.
  • Add the question field and literal-search button below the preview loop by copying this code:
question = st.text_input("Research question", value=DEFAULT_QUESTION)

if st.button("1. Test literal phrase search", disabled=not chunks):
    matches = literal_phrase_search(chunks, question)
    st.write(f"Exact phrase matches: {len(matches)}")
    if not matches:
        st.write("The full question is absent even though one document discusses the idea.")

What do these controls do?

  • The text field starts with DEFAULT_QUESTION as its value.
  • The button stays disabled until the app has at least one document chunk.
  • The result displays the number of complete phrase matches.
  • A zero-match message identifies the gap between relevant meaning and exact wording.
  • Save app.py.
  • Return to the browser tab from earlier.

Good progress. Your app now shows a Research question field plus a 1. Test literal phrase search button below the document previews.

Don't see the search controls?

  • Check that the new code sits below the complete document preview loop.
  • Check that question = st.text_input starts at the left edge of app.py.

If the controls remain hidden, help me find the placement problem.

✔️ Awesome, I've got everything!

Your literal-search controls are ready for the supplied test.

ⓧ I'd like to double check the full code

import sqlite3

import streamlit as st
from pypdf import PdfReader

DB_PATH = "research.db"
CHUNK_SIZE = 900
CHUNK_OVERLAP = 150
DEFAULT_QUESTION = "What helps distributed teams stay productive?"


def extract_documents(uploaded_files):
    pages = []
    for uploaded_file in uploaded_files:
        uploaded_file.seek(0)
        source = uploaded_file.name
        if source.lower().endswith(".pdf"):
            reader = PdfReader(uploaded_file)
            for page_number, page in enumerate(reader.pages, start=1):
                text = (page.extract_text() or "").strip()
                if text:
                    pages.append({"source": source, "page": page_number, "text": text})
        else:
            text = uploaded_file.getvalue().decode("utf-8", errors="replace").strip()
            if text:
                pages.append({"source": source, "page": 1, "text": text})
    return pages


def chunk_pages(pages):
    chunks = []
    for page in pages:
        text = page["text"]
        start = 0
        chunk_index = 0
        while start < len(text):
            end = min(start + CHUNK_SIZE, len(text))
            content = text[start:end].strip()
            if content:
                chunks.append({
                    "id": f"{page['source']}|{page['page']}|{chunk_index}",
                    "source": page["source"],
                    "page": page["page"],
                    "chunk_index": chunk_index,
                    "content": content,
                })
            if end == len(text):
                break
            start = end - CHUNK_OVERLAP
            chunk_index += 1
    return chunks


def literal_phrase_search(chunks, question):
    normalized_question = question.strip().lower()
    if not normalized_question:
        return []
    return [chunk for chunk in chunks if normalized_question in chunk["content"].lower()]


st.title("Local RAG Research Assistant")
st.write("Upload PDF or TXT files, compare literal search with semantic retrieval, and generate a cited answer using local Ollama models.")

uploaded_files = st.file_uploader("Upload research documents", type=["pdf", "txt"], accept_multiple_files=True)
pages = extract_documents(uploaded_files)
chunks = chunk_pages(pages)

if uploaded_files:
    st.write(f"Extracted pages: {len(pages)}")
    st.write(f"Prepared chunks: {len(chunks)}")
    if not pages:
        st.write("No readable text was extracted. Scanned PDFs need OCR, which is outside this project.")
    for page in pages[:3]:
        with st.expander(f"Preview: {page['source']}, page {page['page']}"):
            st.write(page["text"][:1000])

question = st.text_input("Research question", value=DEFAULT_QUESTION)

if st.button("1. Test literal phrase search", disabled=not chunks):
    matches = literal_phrase_search(chunks, question)
    st.write(f"Exact phrase matches: {len(matches)}")
    if not matches:
        st.write("The full question is absent even though one document discusses the idea.")

How to use this reference

This is the complete app.py file for this point in the project. Compare its placement and indentation with your saved file.

Test the default question

The supplied documents cover remote teamwork plus local research security. The literal test compares the complete question with every prepared chunk.

  • Use Upload research documents to select remote_work.txt plus security_notes.txt from the sample_docs folder.
  • Check that the preview area reports two extracted pages.
  • Leave What helps distributed teams stay productive? in the Research question field.

Before you click the button, predict whether a complete phrase comparison can recognize a passage that answers the idea with different words.

  • Click 1. Test literal phrase search.

You'll see Exact phrase matches: 0. You'll also see a message explaining that one document still discusses the idea.

Why zero matches matters

The question asks what helps distributed teams stay productive. The remote_work.txt passage uses phrases such as clear ownership and written decision logs.

Literal matching only recognizes the complete question text. Semantic retrieval can rank passages by meaning when the wording differs.

You have exposed the weakness in exact phrase matching with a repeatable test. Next, you'll build semantic retrieval so relevant evidence can surface through meaning.

Store and Retrieve Semantic Evidence

Your Streamlit app found zero exact matches even though the remote-work passage contains relevant evidence. You now have concrete proof that different wording blocks literal retrieval.

An embedding represents the meaning of a passage as a vector of numbers. Ollama creates these vectors locally from your document chunks.

SQLite stores each vector beside its source details. Cosine similarity then ranks the stored chunks by how closely their meaning matches the question.

Why SQLite for vectors?

A vector database can hide the storage details behind a framework. SQLite keeps each chunk, source label, page number, and serialized vector visible in one local file.

This approach suits a small learning corpus. You can inspect every stage of retrieval without adding another service.

In this step, get ready to:
  • Prepare the local embedding model.
  • Persist document chunks in a local knowledge base.
  • Rank evidence by semantic similarity.
Download the embedding model and create the database

The embedding model turns both document passages and research questions into comparable vectors. The compact granite-embedding:30m model performs this work on your computer.

  • Create a second PowerShell session using the new-terminal control in the VS Code terminal panel.
  • Download the local embedding model by running this command:
ollama pull granite-embedding:30m

What does this command do?

The command downloads the 63 MB embedding model into Ollama's local model store. Your app can then request vectors without sending document text to a hosted API.

The terminal shows the model download completing. The embedding model is now available to your local app.

Model download not completing?

Confirm that Ollama is running on your Windows computer. A stopped Ollama process cannot download or serve the model.

If the download still fails, help me debug the local embedding model download.

Your Python file now needs access to Ollama plus JSON serialization. It also needs constants that keep the model choice and retrieval limit consistent.

  • In app.py, replace the imports and constants at the top of the file with this code:
import json
import sqlite3

import ollama
import streamlit as st
from pypdf import PdfReader

DB_PATH = "research.db"
EMBED_MODEL = "granite-embedding:30m"
CHUNK_SIZE = 900
CHUNK_OVERLAP = 150
TOP_K = 4
DEFAULT_QUESTION = "What helps distributed teams stay productive?"

What does this setup add?

  • The json module converts embedding vectors into text that SQLite can store.
  • The ollama package connects your Python code to the local embedding model.
  • EMBED_MODEL keeps the model identifier in one place.
  • TOP_K limits each retrieval result to the four highest-scoring chunks.
  • Save app.py.
  • Return to the browser tab running your app.

Streamlit reruns the file. You should still see the Local RAG Research Assistant title plus the upload controls without a Python traceback.

Seeing an import error?

Confirm that the Streamlit process is running from the PowerShell session where .venv is active. The Ollama Python package was installed inside that environment.

If the package still cannot be imported, help me check the active virtual environment.

The local knowledge base needs a stable table before it can receive document vectors. The initializer creates that table whenever the app starts.

  • Add initialize_database() below the constants in app.py by copying this function:
def initialize_database():
    connection = sqlite3.connect(DB_PATH)
    try:
        connection.execute(
            """
            CREATE TABLE IF NOT EXISTS chunks (
                id TEXT PRIMARY KEY,
                source TEXT NOT NULL,
                page INTEGER NOT NULL,
                chunk_index INTEGER NOT NULL,
                content TEXT NOT NULL,
                embedding TEXT NOT NULL
            )
            """
        )
        connection.commit()
    finally:
        connection.close()

What does this function create?

  • DB_PATH points SQLite to the local research.db file.
  • CREATE TABLE IF NOT EXISTS chunks makes repeated Streamlit reruns safe.
  • The source fields preserve the filename, page number, chunk position, and passage text.
  • The embedding column holds each serialized vector.
  • Find the existing st.title("Local RAG Research Assistant") line near the bottom of app.py.
  • Place the database initializer directly above that line so the two lines look like this:
initialize_database()
st.title("Local RAG Research Assistant")

Why run this before the interface?

Streamlit executes the file from top to bottom after each rerun. Calling initialize_database() first guarantees that the table exists before any button tries to use it.

  • Save app.py.
  • Confirm that research.db now appears in the VS Code Explorer sidebar.

That is the storage foundation in place. Your app now recreates the connection safely while preserving the database file between reruns.

Database file not appearing?

Check that initialize_database() sits above the title line without indentation. An indented call may remain trapped inside the preceding function.

If the app shows a database error, help me debug the SQLite initializer.

Embed and store document chunks

Each uploaded chunk needs its own vector plus the metadata required for later citations. Batch embedding processes all chunk texts in one Ollama request.

  • Add store_chunks() below literal_phrase_search() in app.py by copying this function:
def store_chunks(chunks):
    texts = [chunk["content"] for chunk in chunks]
    embeddings = ollama.embed(model=EMBED_MODEL, input=texts).embeddings
    rows = [
        (chunk["id"], chunk["source"], chunk["page"], chunk["chunk_index"], chunk["content"], json.dumps(embedding))
        for chunk, embedding in zip(chunks, embeddings)
    ]
    sources = sorted({chunk["source"] for chunk in chunks})
    connection = sqlite3.connect(DB_PATH)
    try:
        connection.executemany("DELETE FROM chunks WHERE source = ?", [(source,) for source in sources])
        connection.executemany(
            "INSERT INTO chunks (id, source, page, chunk_index, content, embedding) VALUES (?, ?, ?, ?, ?, ?)",
            rows,
        )
        connection.commit()
    finally:
        connection.close()

How are chunks stored?

  • texts collects the passage content for one batch embedding request.
  • json.dumps() serializes each numeric vector into a JSON string.
  • rows pairs each vector with the chunk ID plus its source metadata.
  • The question-mark placeholders keep SQL values separate from the statement.
  • Save app.py.
  • Return to the running app in your browser.

The page should rerun with the upload controls intact. A clean page confirms that the storage function parses successfully.

Seeing a syntax error in storage?

Check the indentation inside the try and finally blocks. Confirm that the closing parenthesis after rows aligns with the matching database call.

For targeted help, help me debug the store_chunks function.

The interface needs a concrete success signal after indexing. A small count helper reads the number of rows currently stored in the knowledge base.

  • Add count_stored_chunks() directly below store_chunks() by copying this function:
def count_stored_chunks():
    connection = sqlite3.connect(DB_PATH)
    try:
        return connection.execute("SELECT COUNT(*) FROM chunks").fetchone()[0]
    finally:
        connection.close()

What does the count confirm?

The query returns one row containing the table total. Selecting its first value gives the app a number it can display after indexing.

  • Save app.py.
  • Return to the running browser tab.

You should see the existing document interface without a traceback. The count helper is ready for the indexing button.

Count query failing?

Confirm that the table name is chunks in both database functions. Also check that fetchone()[0] remains inside the try block.

If the query still fails, help me inspect the chunk count query.

The indexing button connects the uploaded chunks to the storage functions. Clicking it replaces existing rows for the same sources before inserting fresh vectors.

  • At the bottom of app.py, add this block below the existing literal phrase search button block:
if st.button("2. Index documents", disabled=not chunks):
    store_chunks(chunks)
    st.write(f"Stored chunks in {DB_PATH}: {count_stored_chunks()}")

What happens when you index?

The button stays disabled until the app has prepared at least one chunk. Once enabled, it embeds the uploaded passages before writing their vectors and metadata to research.db.

  • Save app.py.
  • Confirm that both sample text files remain in the uploader.

Before you click the indexing button, how many stored chunks do you expect from the two short sample files? Keep your prediction in mind.

  • Click 2. Index documents.

You should see Stored chunks in research.db: 2. Each sample file fits inside one 900-character chunk.

Indexing not completing?

Confirm that both uploaded files still show extracted pages and prepared chunks. An empty uploader keeps the indexing button disabled.

If Ollama cannot create the vectors, confirm that granite-embedding:30m finished downloading. You can also ask for help with the indexing failure.

Rank and preview semantic evidence

Semantic retrieval embeds the question using the same model as the stored passages. It compares that question vector with every stored chunk before returning the strongest matches.

  • Add load_rows() below count_stored_chunks() by copying this function:
def load_rows():
    connection = sqlite3.connect(DB_PATH)
    try:
        return connection.execute(
            "SELECT source, page, chunk_index, content, embedding FROM chunks"
        ).fetchall()
    finally:
        connection.close()

What does this function load?

The query loads the source metadata, passage text, and serialized vector for every stored chunk. Retrieval uses these rows without changing the database.

  • Save app.py.
  • Return to the browser tab running the app.

The interface should rerun without a traceback. This confirms that the row-loading query is valid Python.

Rows not loading?

Check that the query uses the same five column names created in the chunks table. Confirm that fetchall() remains attached to the database query.

If needed, help me debug the load_rows query.

Cosine similarity measures the angle between two vectors. A higher score indicates that the question and passage point in a more similar semantic direction.

  • Add cosine_similarity() directly below load_rows() by copying this function:
def cosine_similarity(left, right):
    dot_product = sum(a * b for a, b in zip(left, right))
    left_magnitude = sum(value * value for value in left) ** 0.5
    right_magnitude = sum(value * value for value in right) ** 0.5
    if left_magnitude == 0 or right_magnitude == 0:
        return 0.0
    return dot_product / (left_magnitude * right_magnitude)

How is similarity calculated?

  • dot_product measures how strongly the vector values align.
  • left_magnitude and right_magnitude measure the length of each vector.
  • The zero check prevents division by zero if either vector has no magnitude.
  • The final division normalizes the score so vector length does not dominate the ranking.
  • Save app.py.
  • Return to the running app.

The page should load without a traceback. Your app can now compare two embedding vectors with inspectable Python math.

Similarity function showing an error?

Confirm that both sum() expressions include their closing parentheses. Check that the final return remains outside the zero-magnitude condition.

For more help, help me debug the cosine calculation.

The retrieval function brings the storage and similarity pieces together. It embeds the research question before scoring every stored passage.

  • Add retrieve() below cosine_similarity() by copying this function:
def retrieve(question):
    rows = load_rows()
    if not rows:
        return []
    query_vector = ollama.embed(model=EMBED_MODEL, input=question).embeddings[0]
    scored = []
    for source, page, chunk_index, content, embedding_json in rows:
        scored.append({
            "source": source,
            "page": page,
            "chunk_index": chunk_index,
            "content": content,
            "score": cosine_similarity(query_vector, json.loads(embedding_json)),
        })
    scored.sort(key=lambda item: item["score"], reverse=True)
    return scored[:TOP_K]

How does retrieval rank passages?

  • load_rows() retrieves the persisted chunks and vectors.
  • ollama.embed() creates one vector for the research question.
  • json.loads() restores each stored vector to a Python list.
  • The descending sort places the highest similarity scores first before TOP_K limits the result.
  • Save app.py.
  • Return to the running browser tab.

The app should rerun without a traceback. The semantic ranking pipeline is now ready for a visible preview.

Retrieval function not loading?

Check that embedding_json is passed to json.loads() before similarity is calculated. Confirm that the sort uses reverse=True.

If the function still fails, help me trace the semantic retrieval pipeline.

A retrieval score becomes useful when you can inspect the evidence behind it. Expanders keep each filename, page number, score, and passage together.

  • Add render_evidence() directly below retrieve() by copying this function:
def render_evidence(evidence):
    for index, item in enumerate(evidence, start=1):
        with st.expander(f"Source {index}: {item['source']}, page {item['page']}"):
            st.write(f"Cosine similarity: {item['score']:.3f}")
            st.write(item["content"])

What does the evidence view show?

Each expander labels the retrieved passage with its ranking position, filename, and page number. The body shows the rounded similarity score above the original chunk text.

  • Save app.py.
  • Return to the running app.

The app should rerun without a traceback. The evidence renderer is ready to display the ranked results.

Evidence renderer showing a syntax error?

Check the nested quotation marks inside the expander label. The outer formatted string uses double quotes while the dictionary keys use single quotes.

If the label still fails, help me debug the evidence expander.

The final preview button lets you test semantic retrieval before generating any answer. This separation makes the ranked evidence visible as its own result.

  • At the bottom of app.py, add this block below the indexing button block:
if st.button("3. Preview semantic retrieval", disabled=not question):
    if count_stored_chunks() == 0:
        st.write("Index at least one document first.")
    else:
        evidence = retrieve(question)
        st.write("Semantic retrieval results")
        render_evidence(evidence)

What does the preview button check?

The button first checks whether the knowledge base contains any chunks. A populated database triggers retrieval before the app renders the ranked evidence.

  • Save app.py.
  • Confirm that the default research question remains in the question field.

Before you click the preview button, which file do you expect to rank first? Keep your prediction in mind.

  • Click 3. Preview semantic retrieval.

You should see Semantic retrieval results followed by expandable sources. The passage from remote_work.txt should rank among the retrieved evidence with its page number and cosine similarity score.

Remote-work evidence missing?

Click 2. Index documents again with both sample files uploaded. This refreshes the stored vectors for both sources.

Confirm that the question still reads What helps distributed teams stay productive?. For deeper help, help me diagnose the semantic ranking.

✔️ Awesome, I've got everything!

Your indexing and semantic retrieval code is complete. The app now stores local vectors and exposes the evidence ranking.

ⓧ I'd like to double check the full code

import json
import sqlite3

import ollama
import streamlit as st
from pypdf import PdfReader

DB_PATH = "research.db"
EMBED_MODEL = "granite-embedding:30m"
CHUNK_SIZE = 900
CHUNK_OVERLAP = 150
TOP_K = 4
DEFAULT_QUESTION = "What helps distributed teams stay productive?"


def initialize_database():
    connection = sqlite3.connect(DB_PATH)
    try:
        connection.execute(
            """
            CREATE TABLE IF NOT EXISTS chunks (
                id TEXT PRIMARY KEY,
                source TEXT NOT NULL,
                page INTEGER NOT NULL,
                chunk_index INTEGER NOT NULL,
                content TEXT NOT NULL,
                embedding TEXT NOT NULL
            )
            """
        )
        connection.commit()
    finally:
        connection.close()


def extract_documents(uploaded_files):
    pages = []
    for uploaded_file in uploaded_files:
        uploaded_file.seek(0)
        source = uploaded_file.name
        if source.lower().endswith(".pdf"):
            reader = PdfReader(uploaded_file)
            for page_number, page in enumerate(reader.pages, start=1):
                text = (page.extract_text() or "").strip()
                if text:
                    pages.append({"source": source, "page": page_number, "text": text})
        else:
            text = uploaded_file.getvalue().decode("utf-8", errors="replace").strip()
            if text:
                pages.append({"source": source, "page": 1, "text": text})
    return pages


def chunk_pages(pages):
    chunks = []
    for page in pages:
        text = page["text"]
        start = 0
        chunk_index = 0
        while start < len(text):
            end = min(start + CHUNK_SIZE, len(text))
            content = text[start:end].strip()
            if content:
                chunks.append({
                    "id": f"{page['source']}|{page['page']}|{chunk_index}",
                    "source": page["source"],
                    "page": page["page"],
                    "chunk_index": chunk_index,
                    "content": content,
                })
            if end == len(text):
                break
            start = end - CHUNK_OVERLAP
            chunk_index += 1
    return chunks


def literal_phrase_search(chunks, question):
    normalized_question = question.strip().lower()
    if not normalized_question:
        return []
    return [chunk for chunk in chunks if normalized_question in chunk["content"].lower()]


def store_chunks(chunks):
    texts = [chunk["content"] for chunk in chunks]
    embeddings = ollama.embed(model=EMBED_MODEL, input=texts).embeddings
    rows = [
        (chunk["id"], chunk["source"], chunk["page"], chunk["chunk_index"], chunk["content"], json.dumps(embedding))
        for chunk, embedding in zip(chunks, embeddings)
    ]
    sources = sorted({chunk["source"] for chunk in chunks})
    connection = sqlite3.connect(DB_PATH)
    try:
        connection.executemany("DELETE FROM chunks WHERE source = ?", [(source,) for source in sources])
        connection.executemany(
            "INSERT INTO chunks (id, source, page, chunk_index, content, embedding) VALUES (?, ?, ?, ?, ?, ?)",
            rows,
        )
        connection.commit()
    finally:
        connection.close()


def count_stored_chunks():
    connection = sqlite3.connect(DB_PATH)
    try:
        return connection.execute("SELECT COUNT(*) FROM chunks").fetchone()[0]
    finally:
        connection.close()


def load_rows():
    connection = sqlite3.connect(DB_PATH)
    try:
        return connection.execute(
            "SELECT source, page, chunk_index, content, embedding FROM chunks"
        ).fetchall()
    finally:
        connection.close()


def cosine_similarity(left, right):
    dot_product = sum(a * b for a, b in zip(left, right))
    left_magnitude = sum(value * value for value in left) ** 0.5
    right_magnitude = sum(value * value for value in right) ** 0.5
    if left_magnitude == 0 or right_magnitude == 0:
        return 0.0
    return dot_product / (left_magnitude * right_magnitude)


def retrieve(question):
    rows = load_rows()
    if not rows:
        return []
    query_vector = ollama.embed(model=EMBED_MODEL, input=question).embeddings[0]
    scored = []
    for source, page, chunk_index, content, embedding_json in rows:
        scored.append({
            "source": source,
            "page": page,
            "chunk_index": chunk_index,
            "content": content,
            "score": cosine_similarity(query_vector, json.loads(embedding_json)),
        })
    scored.sort(key=lambda item: item["score"], reverse=True)
    return scored[:TOP_K]


def render_evidence(evidence):
    for index, item in enumerate(evidence, start=1):
        with st.expander(f"Source {index}: {item['source']}, page {item['page']}"):
            st.write(f"Cosine similarity: {item['score']:.3f}")
            st.write(item["content"])


initialize_database()
st.title("Local RAG Research Assistant")
st.write("Upload PDF or TXT files, compare literal search with semantic retrieval, and generate a cited answer using local Ollama models.")

uploaded_files = st.file_uploader("Upload research documents", type=["pdf", "txt"], accept_multiple_files=True)
pages = extract_documents(uploaded_files)
chunks = chunk_pages(pages)

if uploaded_files:
    st.write(f"Extracted pages: {len(pages)}")
    st.write(f"Prepared chunks: {len(chunks)}")
    if not pages:
        st.write("No readable text was extracted. Scanned PDFs need OCR, which is outside this project.")
    for page in pages[:3]:
        with st.expander(f"Preview: {page['source']}, page {page['page']}"):
            st.write(page["text"][:1000])

question = st.text_input("Research question", value=DEFAULT_QUESTION)

if st.button("1. Test literal phrase search", disabled=not chunks):
    matches = literal_phrase_search(chunks, question)
    st.write(f"Exact phrase matches: {len(matches)}")
    if not matches:
        st.write("The full question is absent even though one document discusses the idea.")

if st.button("2. Index documents", disabled=not chunks):
    store_chunks(chunks)
    st.write(f"Stored chunks in {DB_PATH}: {count_stored_chunks()}")

if st.button("3. Preview semantic retrieval", disabled=not question):
    if count_stored_chunks() == 0:
        st.write("Index at least one document first.")
    else:
        evidence = retrieve(question)
        st.write("Semantic retrieval results")
        render_evidence(evidence)

Your local knowledge base can now retrieve passages by meaning and show exactly why they ranked. Next, you will turn that evidence into grounded answers with visible source citations.

Generate Grounded Answers with Citations

Your SQLite knowledge base now ranks passages by semantic similarity. You can inspect the strongest evidence before generating an answer.

Now you will use Ollama to turn those retrieved passages into a cited response. The generation model stays on your computer.

The final RAG assistant must answer only from the supplied evidence. Its source labels expose the filename and page behind each claim.

In this step, get ready to:
  • Download the local generation model.
  • Build a grounded answer function with labelled evidence.
  • Connect the final Streamlit button to retrieval and answer generation.
Prepare local answer generation

The gemma3:1b model generates the final response from your retrieved passages. Its listed download is 815 MB.

  • Create a second PowerShell terminal in the Visual Studio Code window from earlier.
  • Download the generation model by running this command:
ollama pull gemma3:1b

What does this command do?

The ollama command asks your local Ollama service to download gemma3:1b. The model remains available for later runs after the download finishes.

Once the command completes, the generation model is stored locally for ollama.chat. That is the final model download for your research assistant.

Model download interrupted?

Confirm Ollama is still running on your computer. Run the pull command again because Ollama can reuse model data that already downloaded.

If the download still fails, help me diagnose my Ollama model download.

Your app needs a constant that identifies the model used for answer generation. The existing embedding model line gives you the placement anchor.

  • In app.py, find this existing line:
EMBED_MODEL = "granite-embedding:30m"

Why use this line as the anchor?

The model constants belong together near the top of app.py. This keeps the embedding model separate from the generation model.

  • Add the chat model constant directly below the existing line.
  • Compare the two model constants with this reference:
EMBED_MODEL = "granite-embedding:30m"
CHAT_MODEL = "gemma3:1b"

What does the new constant control?

The CHAT_MODEL constant gives the answer function one place to select gemma3:1b. Your embedding calls continue using EMBED_MODEL.

  • Save app.py.
  • Return to the browser tab from earlier.
  • Click 3. Preview semantic retrieval.

You should still see Semantic retrieval results followed by ranked evidence. This confirms the new constant loads without disrupting retrieval.

App stopped after adding the constant?

Check that CHAT_MODEL sits on its own line beneath EMBED_MODEL. Confirm the model identifier remains inside matching quotation marks.

If the app still does not load, help me check my model constants.

Build the grounded answer function

The retrieved chunks need stable labels before the model sees them. The answer_question() function pairs every passage with its source filename and page number.

A system instruction limits the response to this labelled evidence. It also requires exact citations or a clear admission that the evidence is insufficient.

  • In app.py, insert this function between retrieve() and render_evidence():
def answer_question(question, evidence):
    evidence_text = "\n\n".join(
        f"[Source {index}: {item['source']}, page {item['page']}]\n{item['content']}"
        for index, item in enumerate(evidence, start=1)
    )
    response = ollama.chat(
        model=CHAT_MODEL,
        messages=[
            {"role": "system", "content": "You are a careful research assistant. Answer only from the provided evidence. Cite claims with the exact bracketed source labels. If the evidence is insufficient, say so clearly."},
            {"role": "user", "content": f"Question:\n{question}\n\nEvidence:\n{evidence_text}"},
        ],
    )
    return response.message.content

What does this function do?

  • The evidence_text value combines each retrieved passage with a numbered source label.
  • The system message limits the model to the supplied evidence.
  • The user message supplies the research question plus the labelled passages.
  • The final line returns the generated assistant text from response.message.content.
  • Save app.py.
  • Return to the browser tab from earlier.
  • Click 3. Preview semantic retrieval.

You should see the ranked evidence load again with filenames and cosine similarity scores. This confirms the new function parses while the existing retrieval path continues to work.

App no longer loads?

Compare the indentation inside answer_question() with the reference block. Check the closing brackets around messages because each message is a dictionary inside a list.

For help tracing the syntax, help me debug my answer function.

Connect the final answer flow

The final button completes the ingest, retrieve, ground, and cite workflow. It retrieves semantic evidence before passing the same question and passages to answer_question().

The answer appears first so the result is easy to read. Expandable evidence follows so you can audit the passages and similarity scores.

  • In app.py, place this block after the existing semantic retrieval button block:
if st.button("4. Ask the research assistant", disabled=not question):
    if count_stored_chunks() == 0:
        st.write("Index at least one document first.")
    else:
        evidence = retrieve(question)
        st.write("Grounded answer")
        st.write(answer_question(question, evidence))
        st.write("Retrieved evidence")
        render_evidence(evidence)

How does the final flow work?

  • The empty-database check prevents generation without indexed evidence.
  • The retrieve() call selects the strongest semantic matches for the current question.
  • The answer_question() call generates the grounded response from those matches.
  • The final render_evidence() call keeps the supporting passages visible beneath the answer.
  • Save app.py.
  • Return to the browser tab from earlier.
  • Confirm the Research question field contains What helps distributed teams stay productive?.

Before you click the final button, which retrieved filename do you expect the model to cite?

  • Click 4. Ask the research assistant.

You should see Grounded answer followed by a response with bracketed labels such as [Source 1: remote_work.txt, page 1]. Below it, you should see Retrieved evidence with expandable passages and cosine similarity scores.

Your assistant now shows where its answer came from. The retrieved evidence gives you a direct way to check every citation.

Answer missing citations?

Confirm the system message tells the model to cite exact bracketed source labels. Check that evidence_text includes each filename and page before its passage.

If the model call fails, confirm gemma3:1b finished downloading. You can also help me debug my grounded answer flow.

✔️ Awesome, I've got everything!

Your app.py file now contains the complete grounded-answer flow.

ⓧ I'd like to double check the full code

import json
import sqlite3

import ollama
import streamlit as st
from pypdf import PdfReader

DB_PATH = "research.db"
EMBED_MODEL = "granite-embedding:30m"
CHAT_MODEL = "gemma3:1b"
CHUNK_SIZE = 900
CHUNK_OVERLAP = 150
TOP_K = 4
DEFAULT_QUESTION = "What helps distributed teams stay productive?"


def initialize_database():
    connection = sqlite3.connect(DB_PATH)
    try:
        connection.execute(
            """
            CREATE TABLE IF NOT EXISTS chunks (
                id TEXT PRIMARY KEY,
                source TEXT NOT NULL,
                page INTEGER NOT NULL,
                chunk_index INTEGER NOT NULL,
                content TEXT NOT NULL,
                embedding TEXT NOT NULL
            )
            """
        )
        connection.commit()
    finally:
        connection.close()


def extract_documents(uploaded_files):
    pages = []
    for uploaded_file in uploaded_files:
        uploaded_file.seek(0)
        source = uploaded_file.name
        if source.lower().endswith(".pdf"):
            reader = PdfReader(uploaded_file)
            for page_number, page in enumerate(reader.pages, start=1):
                text = (page.extract_text() or "").strip()
                if text:
                    pages.append({"source": source, "page": page_number, "text": text})
        else:
            text = uploaded_file.getvalue().decode("utf-8", errors="replace").strip()
            if text:
                pages.append({"source": source, "page": 1, "text": text})
    return pages


def chunk_pages(pages):
    chunks = []
    for page in pages:
        text = page["text"]
        start = 0
        chunk_index = 0
        while start < len(text):
            end = min(start + CHUNK_SIZE, len(text))
            content = text[start:end].strip()
            if content:
                chunks.append({
                    "id": f"{page['source']}|{page['page']}|{chunk_index}",
                    "source": page["source"],
                    "page": page["page"],
                    "chunk_index": chunk_index,
                    "content": content,
                })
            if end == len(text):
                break
            start = end - CHUNK_OVERLAP
            chunk_index += 1
    return chunks


def literal_phrase_search(chunks, question):
    normalized_question = question.strip().lower()
    if not normalized_question:
        return []
    return [chunk for chunk in chunks if normalized_question in chunk["content"].lower()]


def store_chunks(chunks):
    texts = [chunk["content"] for chunk in chunks]
    embeddings = ollama.embed(model=EMBED_MODEL, input=texts).embeddings
    rows = [
        (chunk["id"], chunk["source"], chunk["page"], chunk["chunk_index"], chunk["content"], json.dumps(embedding))
        for chunk, embedding in zip(chunks, embeddings)
    ]
    sources = sorted({chunk["source"] for chunk in chunks})
    connection = sqlite3.connect(DB_PATH)
    try:
        connection.executemany("DELETE FROM chunks WHERE source = ?", [(source,) for source in sources])
        connection.executemany(
            "INSERT INTO chunks (id, source, page, chunk_index, content, embedding) VALUES (?, ?, ?, ?, ?, ?)",
            rows,
        )
        connection.commit()
    finally:
        connection.close()


def count_stored_chunks():
    connection = sqlite3.connect(DB_PATH)
    try:
        return connection.execute("SELECT COUNT(*) FROM chunks").fetchone()[0]
    finally:
        connection.close()


def load_rows():
    connection = sqlite3.connect(DB_PATH)
    try:
        return connection.execute(
            "SELECT source, page, chunk_index, content, embedding FROM chunks"
        ).fetchall()
    finally:
        connection.close()


def cosine_similarity(left, right):
    dot_product = sum(a * b for a, b in zip(left, right))
    left_magnitude = sum(value * value for value in left) ** 0.5
    right_magnitude = sum(value * value for value in right) ** 0.5
    if left_magnitude == 0 or right_magnitude == 0:
        return 0.0
    return dot_product / (left_magnitude * right_magnitude)


def retrieve(question):
    rows = load_rows()
    if not rows:
        return []
    query_vector = ollama.embed(model=EMBED_MODEL, input=question).embeddings[0]
    scored = []
    for source, page, chunk_index, content, embedding_json in rows:
        scored.append({
            "source": source,
            "page": page,
            "chunk_index": chunk_index,
            "content": content,
            "score": cosine_similarity(query_vector, json.loads(embedding_json)),
        })
    scored.sort(key=lambda item: item["score"], reverse=True)
    return scored[:TOP_K]


def answer_question(question, evidence):
    evidence_text = "\n\n".join(
        f"[Source {index}: {item['source']}, page {item['page']}]\n{item['content']}"
        for index, item in enumerate(evidence, start=1)
    )
    response = ollama.chat(
        model=CHAT_MODEL,
        messages=[
            {"role": "system", "content": "You are a careful research assistant. Answer only from the provided evidence. Cite claims with the exact bracketed source labels. If the evidence is insufficient, say so clearly."},
            {"role": "user", "content": f"Question:\n{question}\n\nEvidence:\n{evidence_text}"},
        ],
    )
    return response.message.content


def render_evidence(evidence):
    for index, item in enumerate(evidence, start=1):
        with st.expander(f"Source {index}: {item['source']}, page {item['page']}"):
            st.write(f"Cosine similarity: {item['score']:.3f}")
            st.write(item["content"])


initialize_database()
st.title("Local RAG Research Assistant")
st.write("Upload PDF or TXT files, compare literal search with semantic retrieval, and generate a cited answer using local Ollama models.")

uploaded_files = st.file_uploader("Upload research documents", type=["pdf", "txt"], accept_multiple_files=True)
pages = extract_documents(uploaded_files)
chunks = chunk_pages(pages)

if uploaded_files:
    st.write(f"Extracted pages: {len(pages)}")
    st.write(f"Prepared chunks: {len(chunks)}")
    if not pages:
        st.write("No readable text was extracted. Scanned PDFs need OCR, which is outside this project.")
    for page in pages[:3]:
        with st.expander(f"Preview: {page['source']}, page {page['page']}"):
            st.write(page["text"][:1000])

question = st.text_input("Research question", value=DEFAULT_QUESTION)

if st.button("1. Test literal phrase search", disabled=not chunks):
    matches = literal_phrase_search(chunks, question)
    st.write(f"Exact phrase matches: {len(matches)}")
    if not matches:
        st.write("The full question is absent even though one document discusses the idea.")

if st.button("2. Index documents", disabled=not chunks):
    store_chunks(chunks)
    st.write(f"Stored chunks in {DB_PATH}: {count_stored_chunks()}")

if st.button("3. Preview semantic retrieval", disabled=not question):
    if count_stored_chunks() == 0:
        st.write("Index at least one document first.")
    else:
        evidence = retrieve(question)
        st.write("Semantic retrieval results")
        render_evidence(evidence)

if st.button("4. Ask the research assistant", disabled=not question):
    if count_stored_chunks() == 0:
        st.write("Index at least one document first.")
    else:
        evidence = retrieve(question)
        st.write("Grounded answer")
        st.write(answer_question(question, evidence))
        st.write("Retrieved evidence")
        render_evidence(evidence)

You now have the complete local research loop. Your assistant retrieves relevant passages before generating an answer with evidence you can inspect.

Secret mission

Filter Retrieval by Source

Your research assistant currently searches every indexed document. Add a source selector that limits retrieval to chosen files while keeping selected filenames separate from the SQL query.

Clean Up Your Resources

Clean Up Your Resources

Everything in this project runs on your Windows computer, so there are no ongoing cloud costs. Choose whether to keep your resources available, pause the app for later, or delete the local files and models.

Resources you used:

  • The local Streamlit process running in PowerShell.
  • The local-rag-assistant workspace opened in Visual Studio Code, including app.py, requirements.txt, sample_docs, and .venv.
  • The research.db SQLite knowledge base containing document chunks and serialized embeddings.
  • The local Ollama models granite-embedding:30m and gemma3:1b.

Keep everything running

Leave everything in place if you plan to keep researching or extend the assistant. Your app, database, environment, and models remain available without ongoing cloud costs.

  • Keep the Streamlit process running while you continue using the assistant.
  • Keep research.db to preserve your indexed chunks and embeddings between sessions.
  • Keep granite-embedding:30m available for document retrieval.
  • Keep gemma3:1b available for grounded answer generation.

Pause - I'll come back to this later

Stop the running app to free its local resources while keeping your work ready for another session. Your project files, database, virtual environment, and models remain on your computer.

  • Switch back to the PowerShell session running Streamlit.
  • Press Ctrl+C to stop Streamlit.
  • Leave the local-rag-assistant folder in place.
  • Leave both Ollama models in place.

Delete - I don't want to use this again

Choose a clean start if you no longer need the assistant. Everything removed here is local, so this does not affect a cloud account.

  • Switch back to the PowerShell session running Streamlit.
  • Press Ctrl+C to stop Streamlit.
  • Close Visual Studio Code.
  • Use File Explorer to locate the local-rag-assistant folder.
  • Delete the local-rag-assistant folder to remove the project files, .venv, and research.db.
  • Remove both local Ollama models by running these commands:
ollama rm granite-embedding:30m
ollama rm gemma3:1b

What Do These Commands Remove?

Each command removes one downloaded model from Ollama's local storage. Your local-rag-assistant folder is unaffected.

Removing the models frees their disk space. Ollama must download them again before the assistant can embed documents or generate answers.

Models Still Stored Locally?

Check that each model identifier matches granite-embedding:30m or gemma3:1b exactly.

Get help removing a local Ollama model.

Nice Work!

Nice Work!

You did it! Your private, local RAG research assistant now turns your documents into grounded answers with visible citations.

You've learned how to:

  • Build a browser-based Streamlit interface for PDF files plus text files. Turn uploaded content into page-aware previews with overlapping chunks.
  • Experience the literal search gap through a guaranteed zero-match test. Replace exact matching with semantic retrieval that ranks local Ollama embeddings by cosine similarity.
  • Persist document chunks plus serialized vectors in a local SQLite knowledge base. Generate grounded answers from retrieved evidence. Visible citations connect each answer to its filename plus page number. Similarity scores expose why each passage was selected.
  • Complete the optional Secret Mission by adding a source selector for document-specific retrieval. Parameterized SQL keeps selected filenames separate from the query.

Ready to quiz yourself?