Build an AI app that answers questions from your own documents

RAG (retrieval-augmented generation) is a way to make an AI model answer from your documents instead of from memory. Your app first finds the pieces of text that match the question, then hands them to the model and asks it to answer from them. In this guide you will build a small version of RAG with PostgreSQL: a Python program that reads four made-up help articles from PostgreSQL and answers questions about them, with the sources listed.

Last tested: 12 October 2026, with PostgreSQL 17, pgvector 0.8, Python 3.14 and Ollama 0.33.0.

What are the steps?

1. Get a database, Python and Ollama ready.
2. Create a table with a vector column and an index.
3. Split your documents into chunks, embed them and save them.
4. Embed a question, fetch the closest chunks and ask the model to answer from them.

You need about 30 minutes, a free WattleDB database, Python, and a free program called Ollama. Ollama runs AI models on your own computer, so the text is never sent to an AI service. It goes only to your database.

What is RAG, and why use it?

If you ask an AI model “What is our refund policy?”, it has never seen your policy. It may invent one that sounds right. RAG fixes this in two steps.

1. Retrieval. Your app searches your own documents and picks the few pieces most relevant to the question.
2. Generation. Your app gives the model those pieces and the question, and asks it to answer using only that text. If the answer is not there, the model should say “I don’t know”.

This reduces guessing and keeps answers tied to text you control. When a policy changes, you update a row in a table. You do not retrain anything.

How do embeddings and pgvector work?

Computers are good at matching exact words and poor at matching meaning. A search for “money back” will not find an article titled “Refunds” if it only compares letters. Embeddings solve this.

An embedding model reads a piece of text and returns a long list of numbers. You can think of the list as a point on a very large map. Texts with similar meanings land close together, and texts with different meanings land far apart. In this tutorial the model is nomic-embed-text, which returns 768 numbers for each piece of text.

PostgreSQL does not store lists like that out of the box. pgvector is an extension, an add-on that gives Postgres new abilities. It adds a vector column type and operators that measure how far apart two vectors are. On WattleDB pgvector is installed on every database, in the public schema, so you do not have to set it up. If you want more detail, the pgvector guide covers indexes, filtering and storage.

What do I need before I start?

1. A WattleDB database. Create a free one in the console. No card is needed.
2. Python 3.10 or newer. Check with:

   python3 --version

   On Windows, use python instead of python3, here and in every later command. If you do not have Python, or it shows 3.9 or older (the Mac’s built-in copy is 3.9), install a current version from python.org, then open a new terminal window and check again.
3. Ollama and two models. Install Ollama from ollama.com (it runs on Windows, Mac and Linux) and keep the Ollama app running, because the scripts talk to it. Then download the two models. This is about 2.3 GB in total and can take a few minutes:

   ollama pull nomic-embed-text
   ollama pull llama3.2

   Both models are free to download. llama3.2 comes with Meta’s licence. nomic-embed-text turns text into embeddings, and llama3.2 writes the answers.
4. Two Python libraries. psycopg is the Python driver for PostgreSQL, and ollama talks to Ollama. In a virtual environment (a private folder of libraries for one project):

   python3 -m venv venv          # on Windows: python -m venv venv
   source venv/bin/activate      # on Windows: venv\Scripts\activate
   pip install "psycopg[binary]" ollama

   If Windows PowerShell refuses to activate the virtual environment, run Set-ExecutionPolicy -Scope Process RemoteSigned and try again.

Your WattleDB console shows two connection strings, DATABASE_URL (pooled, for your app) and DIRECT_URL (direct, for migrations and psql). The two Python scripts below read DATABASE_URL from the environment, so no password is ever written into the code. Keep sslmode=require in the string as the console gives it to you. To verify the certificate too, click Download CA cert in the console and use sslmode=verify-ca&sslrootcert=<file>. Set the variable with a hidden prompt, so the password does not end up in your shell history. On Mac or Linux, run this, paste the pooled string from the console and press Enter:

read -rs DATABASE_URL && export DATABASE_URL

On Windows PowerShell 7.1 or newer:

$env:DATABASE_URL = Read-Host -MaskInput

On Windows PowerShell 5.1, the version Windows ships with:

$env:DATABASE_URL = [Net.NetworkCredential]::new('', (Read-Host -AsSecureString)).Password

On the Windows Command Prompt, include the quotes, because the string can contain an &, which the Command Prompt would treat as a separator. It shows what you type, so be careful on a shared screen:

set "DATABASE_URL=paste the string here"

A variable set this way lasts only for that terminal window, so run the scripts in the same window. Open a new one and you need to set it again.

Step 1: How do I store embeddings in Postgres?

You need one table. You can paste the SQL below into the SQL editor in the WattleDB console: on Databases, click Open on the database’s row, click the SQL editor tab, paste it, and click Run (or press Ctrl + Enter, or ⌘ Return on a Mac). Or, if you prefer a terminal, copy DIRECT_URL from the console and use psql:

read -rs DIRECT_URL && export DIRECT_URL   # paste the direct string, then press Enter
psql "$DIRECT_URL"

On Windows, set DIRECT_URL with the same PowerShell or Command Prompt commands as DATABASE_URL above, changing the name and pasting the direct string.

create table documents (
  id bigserial primary key,
  title text not null,
  content text not null,
  embedding vector(768) not null
);

create index documents_embedding_idx
  on documents using hnsw (embedding vector_cosine_ops);

What this does:

• id gives each row a unique number.
• title and content hold the article title and a chunk of its text.
• embedding holds the 768 numbers. The size must match the model: nomic-embed-text returns 768, and pgvector rejects a vector of any other length.

The create index line builds an index, which is a shortcut the database keeps so it does not have to compare your question with every row. This one is an HNSW index (a graph that links similar vectors). It can be created on an empty table, which is why we create it now. The other type, IVFFlat, should be built after the data is loaded. The vector_cosine_ops part says the index measures cosine distance, a standard way to compare text embeddings. With four articles you will not notice the index. With 100,000 chunks you would.

Step 2: How do I split documents and add them to the database?

Long documents need to be cut into chunks before they are embedded. If you embed a ten-page PDF as one piece, the meaning is muddy: billing, refunds and passwords all blur into one point. Small chunks that each cover one idea are easier to match, and the model gets only the relevant text.

Create a file called add_docs.py. The chunk function splits on blank lines and joins short paragraphs until a chunk is roughly 500 to 800 characters. The articles are for a made-up bike hire shop. Create both Python files in the folder where you made the virtual environment.

import os

import ollama
import psycopg

# Four made-up help articles for a made-up bike hire shop.
ARTICLES = {
    "Hiring a bike": """Hiring a bike

You can hire a bike for a half day (4 hours), a full day (8 hours) or a week. Half-day hire is $25, full-day hire is $40 and a week is $180.

Bring photo ID, such as a driver licence or passport. We keep it at the counter until you return the bike. Helmets are included in the price. Children's bikes and baby seats are available on request.

Our counter is open from 8am to 5pm, seven days a week. Bikes must be back by 5pm on the day your hire ends.

Before you ride, we adjust the seat to fit you and show you how the gears and brakes work. Every bike comes with a lock and a small repair kit. If you want to ride on dirt tracks, tell us when you book and we will give you a bike with wider tyres.

When you bring the bike back, park it in the rack by the counter and hand the lock and repair kit to our staff. We check the bike over while you wait, which takes about five minutes. If everything is fine, we give your photo ID back straight away. If a bike is returned late, we charge an extra $10 for each hour.""",
    "Cancellations and refunds": """Cancellations and refunds

You can cancel a booking for free up to 24 hours before your start time. We refund the full amount to your original payment method within 5 business days.

If you cancel less than 24 hours before, we keep 50 percent of the hire fee. If you do not turn up, we keep the full fee.

If it rains heavily on the day, we move your booking to another day at no cost. Call us before your start time to arrange it.""",
    "Lost or damaged bikes": """Lost or damaged bikes

Please tell us straight away if a bike is damaged or a part is missing. Normal wear, such as a flat tyre, is on us. Call the number on the bike tag and we will bring you a replacement.

If a bike is lost or stolen, report it to the local police and give us the report number. You pay a flat fee of $150 for a lost lock and $600 for a stolen bike.

Damage from a crash or from using the bike on rough tracks is charged at repair cost, up to a maximum of $300.""",
    "Group bookings": """Group bookings

Groups of 6 or more people get 10 percent off the total hire fee. Book at least 3 days ahead so we can set the bikes aside.

One person can pay for the whole group, or each person can pay on the day. Everyone must still show their own photo ID.

For school groups and tours, email groups@example.com with your dates and numbers. We reply within 2 business days with a quote.""",
}


def split_long(paragraph, limit):
    """Cut a paragraph that is longer than the limit, at a sentence end where possible."""
    pieces = []
    while len(paragraph) > limit:
        cut = paragraph.rfind(". ", 0, limit)
        cut = cut + 1 if cut > 0 else limit
        pieces.append(paragraph[:cut].strip())
        paragraph = paragraph[cut:].strip()
    pieces.append(paragraph)
    return pieces


def chunk(text, target=500, limit=800):
    """Split on blank lines, then merge short paragraphs into chunks of about 500-800 characters."""
    chunks, current = [], ""
    paragraphs = [part for p in text.split("\n\n") if p.strip() for part in split_long(p.strip(), limit)]
    for paragraph in paragraphs:
        if current and (len(current) >= target or len(current) + len(paragraph) > limit):
            chunks.append(current)
            current = ""
        current = f"{current}\n\n{paragraph}" if current else paragraph
    if current:
        chunks.append(current)
    return chunks


def to_vector_literal(numbers):
    # pgvector reads a vector from text such as '[0.1,0.2,0.3]'.
    return "[" + ",".join(str(x) for x in numbers) + "]"


# Embed everything first, so the database connection is not held open while Ollama works.
rows = []
for title, text in ARTICLES.items():
    for piece in chunk(text):
        embedding = ollama.embed(model="nomic-embed-text", input=piece)["embeddings"][0]
        rows.append((title, piece, to_vector_literal(embedding)))

# One short transaction. It EMPTIES the documents table first, so you can run this script again.
# prepare_threshold=None turns off prepared statements, which poolers in transaction mode can mishandle.
with psycopg.connect(os.environ["DATABASE_URL"], prepare_threshold=None) as conn:
    conn.execute("delete from documents")
    with conn.cursor() as cur:
        cur.executemany(
            "insert into documents (title, content, embedding) values (%s, %s, %s::vector)",
            rows,
        )

print(f"Saved {len(rows)} chunks from {len(ARTICLES)} articles.")

Run it with python3 add_docs.py. It prints how many chunks it saved. This empties the documents table first, so use a new database for the tutorial. What it does:

1. It cuts each article into chunks. A paragraph longer than 800 characters is cut at a sentence end.
2. It asks Ollama for the embedding of each chunk. It does this before it opens the database connection, so the connection is not held while Ollama works.
3. It connects using DATABASE_URL. This is the pooled connection, which is what an app should use. It runs in transaction mode, so the scripts never use a session-level SET, and they turn off prepared statements with prepare_threshold=None.
4. In one short transaction it deletes the old rows and inserts the new chunks and their embeddings. The SQL uses %s placeholders, so the values travel separately from the query text. Never build SQL by pasting strings together.

pgvector reads a vector from text that looks like [0.1,0.2,0.3], so the script formats the numbers that way and casts them with ::vector. Because the script deletes old rows first, you can run it again after editing the articles.

Step 3: How do I ask a question and get an answer?

Create a file called ask_question.py:

import os
import sys

import ollama
import psycopg

question = " ".join(sys.argv[1:]) or "Can I get a refund if I cancel my booking?"


def to_vector_literal(numbers):
    return "[" + ",".join(str(x) for x in numbers) + "]"


# 1. Turn the question into an embedding.
query_embedding = ollama.embed(model="nomic-embed-text", input=question)["embeddings"][0]

# 2. Find the 3 chunks closest to it. <=> is cosine distance: smaller means more similar.
with psycopg.connect(os.environ["DATABASE_URL"], prepare_threshold=None) as conn:
    rows = conn.execute(
        """
        select title, content, embedding <=> %s::vector as distance
          from documents
         order by embedding <=> %s::vector
         limit 3
        """,
        (to_vector_literal(query_embedding), to_vector_literal(query_embedding)),
    ).fetchall()

# 3. Give those chunks to the chat model and tell it to stick to them.
context = "\n\n---\n\n".join(content for _, content, _ in rows)
prompt = f"""Answer the question using ONLY the context below.
If the context does not contain the answer, reply exactly: I don't know.

Context:
{context}

Question: {question}"""

reply = ollama.chat(
    model="llama3.2",
    messages=[{"role": "user", "content": prompt}],
    options={"temperature": 0},
)

print(f"Question: {question}\n")
print(f"Answer: {reply['message']['content']}\n")
print("Sources:")
for title, _, distance in rows:
    print(f"  - {title} (distance {distance:.2f})")

What it does:

1. It takes your question from the command line, or uses a default one.
2. It embeds the question with the same model used for the documents. This matters: vectors from different models cannot be compared.
3. It asks the database for the 3 closest chunks. The <=> operator is cosine distance, and a smaller number means a closer match. order by ... limit 3 returns the three smallest.
4. It puts those chunks into a prompt that tells the model to answer only from the context, and to say “I don’t know” otherwise. A temperature of 0 asks the model to be as steady as it can.
5. It prints the answer and the titles of the articles the chunks came from.

What will I see when I run it?

Ask a question the articles cover:

python3 ask_question.py "How much is it to hire a bike for a day?"

Then ask one they do not cover:

python3 ask_question.py "Do you hire out e-bikes?"

This is real output from our test run. Your wording will differ, because model output varies from run to run and between machines. The distances will be close but not identical.

Question: How much is it to hire a bike for a day?

Answer: $40.

Sources:
  - Hiring a bike (distance 0.13)
  - Group bookings (distance 0.29)
  - Lost or damaged bikes (distance 0.36)

Question: Do you hire out e-bikes?

Answer: I don't know.

Sources:
  - Hiring a bike (distance 0.27)
  - Group bookings (distance 0.34)
  - Hiring a bike (distance 0.43)

Look at the second answer. The database still returned the 3 closest chunks, because there are always three closest rows, even when none of them is relevant. The model saw that the context did not mention e-bikes and said so. The sources list is the part to check: if the titles look unrelated to the question, do not trust the answer. Once you have more data you can also set a distance cut-off and skip the model when the best match is too far away.

Why can RAG answers still be wrong?

1. Wrong chunks. If search returns the wrong text, the answer will be wrong. Chunks that are too big, too small, or a vague question all cause this.
2. The model ignores the instruction. Small local models especially may add facts that are not in the context. The prompt reduces this and does not stop it.
3. Stale data. If you edit an article, you must embed it again and replace its rows. The numbers in the database do not update themselves.

This is why the script prints its sources. If a customer disagrees with an answer, you can see exactly which text the model was given, then fix the document or the prompt.

Does the data stay in Australia?

In this tutorial, your text is stored in WattleDB in Sydney, with backups in Melbourne. The embeddings and answers are made by Ollama on your own computer, so the text is not sent to a cloud AI service.

If you later switch to a hosted AI service, the text you send it is processed on that provider’s servers, wherever they are. Australian Privacy Principle 8 can apply to sending personal information overseas, so check where the provider processes data and talk to whoever handles privacy for you. This guide is not legal advice.

How do I take this from a tutorial to a real app?

Run the model somewhere that suits you

You would not ask every user to run llama3.2 on their laptop. Either run a model on a server you control, or use a hosted service. WattleDB does not host AI models; it is the database half of the system.

Use the right connection string

Use the pooled DATABASE_URL in your app and DIRECT_URL for setup, psql and migrations. Because the pooled connection is in transaction mode, a plain SET can carry over to the next client. Use SET LOCAL inside a transaction if you need to change a setting for one query.

Keep each customer’s documents separate

If your app serves several customers, each must only see their own documents. Checking customer_id in your Python code works, but one missed WHERE clause shows another customer’s data. Row-level security makes the database enforce the rule every time. See multi-tenant SaaS with row-level security for the full picture. A minimal version for this table looks like this:

alter table documents add column customer_id text check (customer_id <> '');
alter table documents enable row level security;
create policy documents_by_customer on documents
  using (customer_id = nullif(current_setting('app.customer_id', true), ''));

Policies cover vector search like any other query, with one catch: they do not apply to the role that owns the table, and in this tutorial that is the role your scripts use. The fix is to run your app as a different role. In the WattleDB console, open the Roles tab, add a role with the Read & write access level, and put that role’s name and password in your app’s connection string, so the app does not own the tables. The other way is to run alter table documents force row level security;, but forcing also affects the owner’s own scripts, pg_dump and WattleDB’s masked clones, which connect as the owner and fail on a forced table, so the separate Read & write role is the recommended route.

Then set the customer for each transaction, never with a plain SET, because the pooled connection is shared:

conn = psycopg.connect(os.environ["DATABASE_URL"], prepare_threshold=None, autocommit=True)
with conn.transaction():
    conn.execute("select set_config('app.customer_id', %s, true)", (customer_id,))
    rows = conn.execute("select title from documents order by embedding <=> %s::vector limit 3", (vector_literal,)).fetchall()

Take the customer from your server’s verified session or token, never from a request body, header or URL. RLS enforces the customer_id you set; it can’t tell whether you set the right one.

The connection is opened with autocommit=True, so each with conn.transaction(): block is a real transaction that ends when the block ends. Without it, the block would only be a savepoint inside a longer transaction, and the setting would outlive the block. The true at the end makes the setting last only until the transaction ends. Once row-level security applies (a separate role, or force), every row needs a customer_id, and add_docs.py must set customer_id on its inserts and app.customer_id for the transaction. Rows without one become invisible and new inserts are refused. That fails safely: nothing leaks. One more thing: with a policy or a WHERE filter, an HNSW index can return fewer than 3 rows. The pgvector guide explains iterative scans, which fix this.

Treat the documents and the question as untrusted

Text in either can tell the model to ignore your instructions. Do not give the model secrets or tools it could misuse, and do not show its answer as HTML or run it as code.

Plan for storage

Embeddings take more room than most people expect. We measured 5,000 rows of 768-dimension embeddings on PostgreSQL 17 with pgvector 0.8. The table plus its HNSW index came to about 8.3 KB per row, before counting the text you store alongside. Work out how much storage you need by counting chunks, not documents.

What to try next

Add one of your own help articles to ARTICLES, run add_docs.py again and ask a question about it. Then read the pgvector guide for filtering, halfvec and hybrid search with keywords.