Building a Production RAG App: The Decisions, the Traps, and the Fixes¶

I built a full-stack RAG application — streaming chat, document ingestion, hybrid search, sub-agents, admin-managed model config — and most of the hard parts had nothing to do with calling an LLM. The LLM call is 20 lines. The other 95% is retrieval quality, ingestion correctness, and not blocking your event loop.

This walks through the real decisions and the traps I hit: where naive RAG breaks, and the exact fix that shipped. Every number here is from the running code, not a whitepaper.

All the code is open source: github.com/pkhamdee/rag. Every snippet below links to the file it came from, so you can read the full implementation in context.

Credit: the template's structure and workflow originate from the Claude Code Agentic RAG Masterclass

The Stack (and why)¶

The one rule I held to: no LangChain, no LangGraph. Raw SDK calls and Pydantic for structured outputs. Frameworks hide the retrieval internals, and the retrieval internals are the whole game.

The First Decision: Stop Using Managed RAG¶

The fastest way to ship a RAG application is OpenAI's file_search tool with vector stores (now part of the Responses API — the old Assistants threads are deprecated): upload files, it chunks, embeds, retrieves, and manages conversation state for you. It's also a black box. You can't see the chunks, can't tune retrieval, can't mix in keyword search, and you're locked to one provider.

So I tore it out and owned the retrieval layer. That decision cascades:

• OpenRouter is stateless — it doesn't remember your thread. The backend now loads the full conversation history from Postgres and replays it on every request.
• No managed retrieval means I own chunking, embedding, the vector store, and the search strategy.

That's more code, but it's the only way to get retrieval quality above demo-grade.

Architecture at a Glance¶

Browser (React)
  ├── /           ChatPage      streaming SSE chat with threads
  ├── /documents  DocumentsPage  upload + folder management
  └── /settings   SettingsPage   admin-only model/embedding config

FastAPI (async)
  ├── /messages   POST → SSE stream, tool-calling loop
  ├── /documents  upload → async ingestion (BackgroundTask)
  └── /settings   DB-driven model config, no restart

Supabase (Postgres)
  ├── pgvector      dense vector search
  ├── tsvector+GIN  keyword search
  ├── Storage       raw files
  └── Realtime      ingestion status push to UI

Two pipelines do the real work: ingestion (offline, turns files into searchable chunks) and retrieval (online, turns a question into grounded context). Let's walk both.

Ingestion: Where Correctness Hides¶

Upload looks trivial — accept a file, chunk it, embed it. The traps are deduplication, blocking I/O, and chunks that break your embedding model.

Split upload into two stages¶

A 25 MB PDF can take 30+ seconds to parse and embed. Block the HTTP request on that and your upload endpoint times out. So upload is synchronous and fast; processing is a background job.

Stage 1 — Upload (returns immediately):

• Validate extension (.txt .md .pdf .docx .html) and size (0 < n ≤ 25 MB)
• SHA-256 the file bytes
• If (user_id, content_hash) already exists → return it, duplicate=true, stop
• Store bytes in Supabase Storage, insert documents row with status=pending
• Schedule process_document as a BackgroundTask, return to client

Stage 2 — Process (background): parse → chunk → embed → store, flipping status through processing → completed | failed. The frontend watches that status field over Supabase Realtime, so the user sees a live progress bar with zero polling.

Trap 1: Naive ingestion re-embeds everything¶

Re-upload the same file and a naive pipeline embeds every chunk again — wasted money, duplicate results. The fix is content hashing at two levels:

• Whole file: SHA-256 of the bytes. Identical file → skip the entire pipeline.
• Per chunk: SHA-256 of each chunk's text. Before embedding, batch-look-up which hashes already have a stored vector. Only embed the misses.

One honest caveat: chunk-level dedup is boundary-sensitive. Edit a paragraph and every chunk after the edit can shift its boundaries, so a change on page 1 re-embeds more than a change on page 40. The big wins are identical re-uploads (zero re-embeds), appends, and late edits — not a guaranteed "2 of 200."

Trap 2: Blocking the event loop¶

FastAPI is async, but docling parsing is CPU-bound and the Supabase client is synchronous. Call them directly in an async def and you freeze the entire server for every other request.

Two fixes, used everywhere (retrieval_service.py):

# Offload blocking calls to a worker thread
vector_results = (
    await asyncio.to_thread(lambda: supabase.rpc("match_chunks", rpc_params).execute())
).data or []

And bound concurrency so ten simultaneous uploads don't spawn ten parallel embedding storms — process_document runs under a semaphore capped at 3 concurrent ingestions.

Inside a single document, independent LLM/embedding calls overlap with asyncio.gather (ingestion_service.py):

# metadata extraction, embedding, and entity extraction run concurrently
metadata, embeddings, entities = await asyncio.gather(
    extract_metadata(text),
    embed_uncached_chunks(chunks),   # batched 50 per API call
    extract_entities(text),          # only if knowledge graph enabled
)

Trap 3: A chunk with no separators kills the embed call¶

The recursive splitter chunks on \n\n → \n → ". " → " " at 1000 chars with 200 overlap. But hand it a 50 KB base64 blob or a minified JSON dump — no separators anywhere — and recursion runs out of split points and emits one oversized chunk. That chunk then exceeds the embedding model's token limit and the whole document fails.

The fix is a character-level fallback: when no separators remain and the chunk is still too big, hard-slice it at chunk_size with overlap. Ugly, but it guarantees every chunk fits.

Retrieval: Why Vector Search Alone Isn't Enough¶

Here's the lesson that surprises people: semantic vector search is bad at exact terms. Ask for error code ECONNREFUSED or a part number AIS-2026 and cosine similarity happily returns "conceptually related" chunks that don't contain the string.

So retrieval fuses three signals:

Fuse with Reciprocal Rank Fusion, not score averaging¶

You can't average a cosine score (0–1) against a Postgres FTS rank — different scales, meaningless sum. RRF ignores the scores and uses only the rank position, which is why it's the standard (retrieval_service.py):

def _reciprocal_rank_fusion(result_lists, k=60, top_k=10):
    scores = {}
    for ranked_list in result_lists:
        for rank, r in enumerate(ranked_list):
            rid = str(r["id"])
            scores[rid] = scores.get(rid, 0.0) + 1.0 / (k + rank + 1)
    # ... sort by fused score, return top_k

k=60 is the well-tested constant. A chunk that ranks #1 in keyword and #3 in vector beats one that's #2 in only a single signal.

Over-fetch, fuse, then rerank¶

The retrieval flow that ships:

1. Pull 3× the final top-K candidates from each signal (candidate_k = top_k * 3)
2. Run dense and sparse in parallel (asyncio.to_thread); if the knowledge graph is on, grounded entity extraction runs concurrently with embedding to hide LLM latency
3. Fuse with RRF down to top_k * 2 candidates
4. LLM listwise reranker re-scores those candidates and returns the final top-K

The reranker matters because RRF orders by rank agreement, not by answer relevance. The reranking service is model-aware: if the configured model is a dedicated reranker (Cohere, Jina — anything with "rerank" in the name), it calls the native /rerank endpoint; otherwise it falls back to a listwise chat pass — show the LLM the query plus the candidate chunks, ask for the best ordering. Either way, a cheap rerank meaningfully lifts precision before the context ever hits the main model.

See what retrieval actually did¶

Retrieval bugs are invisible without tracing. Every search emits its signal counts to LangSmith:

run.add_metadata({
    "dense_results": len(vector_results),
    "sparse_results": len(keyword_results),
    "graph_results": len(graph_results),
    "rrf_candidates": len(merged),
})

When an answer is wrong, the first question is "did retrieval even find it?" — and now I can answer that in one trace instead of guessing.

Which Retrieval Technique for Which Query?¶

Hybrid + RRF is the safe default — it covers most queries without you choosing. But knowing which signal carries the weight tells you what to tune when results are bad. Match the query shape to the technique that actually answers it:

The rule of thumb: vectors for meaning, keywords for strings, graph for relationships, SQL for math, sub-agent for whole documents, web for everything you don't have. The agent picks per query — but if retrieval quality drops, this table tells you which signal to look at first.

Why Relational, Not a Graph Database¶

That graph row probably made you ask: shouldn't entity traversal live in Neo4j? No — for a RAG side-signal, Postgres is the better call. The graph here is one of three fused signals, not the product. It needs 1–2 hops, not deep pathfinding.

Adding a dedicated graph DB means two stores that must agree with each other — separate backups, separate auth, and a sync job between your chunks and your edges. Your RAG quality doesn't improve, because traversal depth was never the bottleneck. The rule: reach for a graph DB when traversal is the answer, not when it's one of several retrieval signals you fuse.

Keeping the graph in Postgres also means graph hits and pgvector hits merge in the same RRF query, inside the same transaction, under the same RLS you already wrote.

Why 2 hops, not 4¶

The traversal is a hand-unrolled SQL function — one JOIN against the edge table per hop, not recursion:

seed  → entities matching the query        (depth 0)
hop1  → JOIN entity_relationships on seed   (depth 1)
hop2  → JOIN entity_relationships on hop1   (depth 2)

There's no hop3. The cap is deliberate, and the reason is fan-out. Entity graphs are dense — if each entity averages ~10 edges, the reachable set explodes fast:

By hop 4 the traversal stops filtering and starts returning almost every entity the user owns — slower JOINs for worse chunks. 1–2 hops is where the signal lives. Postgres could do 4 (rewrite as WITH RECURSIVE with a depth guard) — it's just rarely worth it for a fused side-signal.

You don't have to jump straight to Neo4j, either. There's a spectrum inside the relational world:

My order of escalation: fixed joins → recursive CTE → Apache AGE → only then a separate graph engine. Most RAG systems never need to leave the first two rows.

The Agentic Layer: One Model, Four Tools¶

The chat model doesn't just answer — it routes. It runs a tool-calling loop (up to 3 rounds) over four tools:

Trap 4: Some questions need the whole document, not chunks¶

"Summarize this contract" is unanswerable from 5 retrieved chunks — you need all of it. But stuffing a 25 MB document into the main chat context blows the window and poisons every later turn.

The fix is a sub-agent with isolated context. analyze_document spawns a separate agent loop that reads the full document, does its analysis, and returns only the answer. The main thread never sees the raw document — just the conclusion. The UI shows the sub-agent's nested tool calls in a collapsible panel, so it's still transparent.

Trap 5: RAG can't count¶

"How much did I spend last quarter?" is a SQL question, not a retrieval question. Embedding order rows is pointless. So there's a query_structured_data tool that runs SELECT against a Postgres table — through a dedicated read-only role, validated to reject anything but SELECT. Structured questions go to SQL; unstructured go to vectors. The model picks.

Trap 6: The model asks permission instead of just looking¶

Early on the model would answer "I couldn't find that in your documents. Would you like me to search the web?" — useless. The fix lives in the system prompt as a mandatory search protocol (llm_service.py):

Step 1: Call search_documents. Step 2: If results aren't relevant, call search_web immediately — NOT optional, NO confirmation. Step 3: Answer from the combined results, with citations.

Cite the filename for documents, title + URL for web. If everything comes back empty, say so — don't guess.

Trap 7: Provider Flexibility Breaks on OpenAI-Specific Params¶

Letting admins swap models from a settings page is great until a non-OpenAI model chokes on an OpenAI-only argument:

• dimensions= on embeddings → fails on text-embedding-ada-002 and most non-OpenAI models
• response_format={"type": "json_object"} → unsupported on some endpoints
• client.beta.chat.completions.parse → not implemented by most OpenRouter-hosted models

The fix is catch-strip-retry. Try the rich call; on the specific error, drop the offending argument and retry (embedding_service.py):

try:
    response = await client.embeddings.create(model=model, input=texts, dimensions=dimensions)
except Exception as e:
    if "dimensions" in str(e).lower():
        response = await client.embeddings.create(model=model, input=texts)  # retry without it
    else:
        raise

The system prompt already asks for JSON, so the JSON-mode fallback degrades cleanly to a plain completion. If you advertise "bring any model," you have to mean it — and that means graceful degradation, not assumptions.

Security: Don't Skip the Boring Parts¶

• Row-Level Security on every table. Users only ever see their own threads, documents, and chunks — enforced in Postgres, not application code.
• API keys encrypted at rest. Provider keys live in the DB (admins edit them via UI), encrypted with Fernet symmetric encryption keyed by SETTINGS_ENCRYPTION_KEY. Never plaintext.
• JWT verification on every request against Supabase's JWKS. (Lesson learned: never run the JWKS fetch on the event loop — a bare synchronous httpx.get in an auth dependency freezes the whole server when the endpoint is slow. The fix that shipped: offload it with asyncio.to_thread and cache the keys with a 10-minute TTL, refreshing on an unknown kid to survive key rotation.)

The Challenge → Solution Cheat Sheet¶

Best Practices Checklist¶

Building your own? Tick these off:

• Make ingestion async and idempotent. Two-stage upload, content-hash dedup, background processing.
• Never block the event loop. Offload sync/CPU work to threads; cap concurrency with a semaphore.
• Go hybrid from day one. Dense + sparse + RRF. Vector-only is a demo, not a product.
• Over-fetch, then rerank. Pull 3× candidates per signal, fuse, rerank down to top-K.
• Add a char-slice chunk fallback. Real documents contain unsplittable blobs.
• Route, don't retrieve-everything. Separate tools for documents, full-doc reads, SQL, and web.
• Isolate full-document work in a sub-agent. Keep the main context clean.
• Make the fallback protocol mandatory in the prompt. No "should I search the web?" theater.
• Assume any model. Catch-strip-retry on provider-specific params.
• RLS + encrypt keys + JWKS off the event loop. Security is not a later module.
• Trace retrieval signal counts. You can't fix what you can't see.

What I'd Build Next (and What I'd Skip)¶

The graph signal is the weakest part of the stack today — but the fix isn't a fancier query engine. It's better nodes and better edges. A Cypher dialect over a graph full of duplicate, unweighted entities still returns junk.

Build next — high leverage, pure SQL:

• Entity resolution. "AIS", "AIS plc", and "Advanced Info Service" are three separate nodes right now, which fragments every traversal. Merge aliases at ingestion with embedding-based linking. This single change makes 2-hop outperform any 4-hop on a fragmented graph — and it fixes the composite-key bug (Google as ORG vs PRODUCT) my own code review flagged.
• Weighted, depth-decayed traversal. The weight column exists on every edge and gets ignored at query time. Rank a strong 1-hop link above a weak 2-hop one, decay by distance. No new tech — it's a CASE in the existing function.

Skip — wrong tool for this job:

Keep in the back pocket: a recursive CTE, for the day a real query needs configurable N-hop depth. Until then, deeper traversal just adds noise.

The lesson generalizes past graphs: when a retrieval signal underperforms, fix the data going in before you reach for a bigger engine. Resolution and weighting beat new infrastructure almost every time.

Summary¶

RAG quality is a retrieval problem, not a generation problem. The model is the easy part — the durable engineering is in the pipeline around it.

The five decisions that mattered most:

1. Own your retrieval. Managed RAG is a black box you'll outgrow in a week.
2. Hybrid + RRF + rerank. Three signals fused by rank, then re-scored for relevance. Vector-only loses on exact terms.
3. Async, idempotent ingestion. Content-hash dedup and background processing turn "works on my laptop" into "survives real load."
4. Route with tools, isolate with sub-agents. Documents, SQL, and web are different questions — let the model pick, and keep heavy reads out of the main context.
5. Treat provider flexibility, security, and observability as load-bearing, not nice-to-haves.

Do the boring parts well and the smart parts take care of themselves.

For the theory behind these choices — chunking strategies, hybrid search, reranking, GraphRAG — see Agentic Retrieval: The Complete Guide. For the same pipeline built on Airflow + n8n instead of FastAPI, see the hybrid RAG implementation post.

The full source — backend, frontend, migrations, and the validation suite — is on GitHub: github.com/pkhamdee/rag. Clone it, read the pipelines end to end, and break things.

Discussion

Have thoughts on this post? Share them below — questions, corrections, or your own experience are all welcome.