0-to-1: Building & Scaling a RAG System for Social Good

The Problem: A Very Slow Query

The application felt slow. Profiling revealed that a single database call—the match_documents function—was responsible for 68.2% of all query time. This was the critical bottleneck:

• Mean Time: 911 ms (nearly a full second on average)
• Max Time: 2,620 ms (a very noticeable 2.6 seconds)

Even though the end-to-end user experience felt slow (6–8 seconds), we knew that fixing this expensive database call was the critical first step.

What Was Wrong with the Initial Setup

Two primary issues in the initial database design caused the bottleneck:

• Index Mismatch: The vector index was built with vector_l2_ops, which optimizes for L2 (Euclidean) distance. However, for semantic search with modern embedding models like Google's text-embedding-004, Cosine Similarity is the correct metric—it measures the angle between vectors, not the straight-line distance. This mismatch prevented the index from being used effectively, forcing a slow brute-force scan.
• No Pre-filtering: The match_documents function performed a vector search across all 6,000 documents every single time. A keywords column existed but wasn't being used to narrow the search space before the expensive vector comparison.

The Fix: A Two-Stage Filtering Strategy

We implemented a series of coordinated changes to drastically reduce the search space and let indexes do their job:

1. Corrected the Vector Index: Dropped the old index and created a new one using vector_cosine_ops. We tuned the lists parameter to 80 (based on √6000), which is a best practice for IVFFlat index optimization.
2. Added a Keyword Index: Created a GIN index on the keywords column, which is incredibly fast for searching within array columns.
3. Upgraded the SQL Function: Rewrote match_documents to accept a p_keywords array parameter. The new logic first uses the GIN index to filter documents (WHERE keywords @> p_keywords), then performs the vector search only on that much smaller, pre-filtered set.
4. Updated the Edge Function: Modified index.ts to extract keywords from the user's query and pass them to the parameterized SQL function, enabling pre-filtering on every request.

The Results: Massive Performance Gains

Query logs after the changes show dramatic improvement:

• Mean Time: Dropped ~24×, from 911 ms down to ~37 ms
• Max Time: Dropped ~66×, from 2,620 ms down to ~40 ms
• Consistency: The huge variability between minimum and maximum query times disappeared. The function now performs consistently fast, removing the database as the bottleneck.

Key Learnings for the Future

This work reinforced several critical best practices for building high-performance RAG systems:

• Filter First, Search Second: Always reduce rows with relational filters (e.g., WHERE user_id = '...' or WHERE keywords @> '...') before expensive vector operations. This is the single most important optimization you can make.
• Use the Right Index for the Job: A vector index is not one-size-fits-all. Ensure your index type (IVFFlat, HNSW) and distance metric (cosine_ops, l2_ops) match your embeddings and query patterns. For semantic search, cosine similarity is almost always correct.
• Cache is Your Friend (and a Clue): The 100% cache hit rate told us the slowness was compute-bound, not disk-bound—use profiling signals like this when diagnosing.
• Isolate the Bottleneck: Measure each layer. After fixing the database, timing logs on the Edge function revealed the next bottleneck: sequential AI API calls, which is where the remaining 6–8 seconds of latency lies.

The Problem: Crashing on Long Conversations

I identified that the core function was crashing with a 400 Bad Request error. I realized that my most engaged users—those with long, detailed conversations—were the most likely to experience a total app failure, breaking trust and halting their journey.

What Was Wrong

I dove into the logs and saw that the text-embedding-004 model has a small 36,000-byte limit. My code was sending the entire chat history for vectorization, which was inefficient and, for long chats, fatal.

The Fix: A Dual-History Approach

I identified two different needs: RAG only needs recent context to find relevant documents, while the AI needs full context to understand the user's journey. I implemented a solution by creating two history variables:

1. recentHistoryText: A small, truncated history sent to the embedding model for efficient document retrieval.
2. fullHistoryText: The complete history sent to the final chat model (gemini-2.5-flash) to maintain conversational context.

The Problem: Two Critical Bottlenecks

The chatbot application faced two severe issues that directly impacted user trust and experience:

• Stability Failures: The server function frequently crashed with a 500 error when the AI's response didn't match the expected JSON format. This occurred when the AI produced conversational plain text instead—often a symptom of poor RAG results.
• Unacceptable Latency: End-to-end response times exceeded 40 seconds per query, making the tool feel broken and unreliable for real-time user interactions.

What Was Wrong with the Initial Architecture

Root cause analysis revealed two distinct issues:

• No Error Handling: The JSON.parse() call had no try-catch wrapper. When the AI returned plain text (e.g., "Please pro..."), the parser crashed immediately, with no graceful fallback.
• Redundant AI Pre-processing Step: The function spent 27.7 seconds on an initial AI call to generate a searchQuery and p_keywords for hybrid search. However, profiling showed the actual database query (with keywords and vectors) took only 250–1,100 ms. The expensive AI step was the true bottleneck, and it was unclear if it actually improved search quality.

The Fix: Error Handling + Vector-Only Search

We implemented a two-part solution:

1. Wrapped JSON.parse() in Try-Catch: If parsing fails, the function logs the error and returns a valid JSON object with a user-friendly fallback message: "I'm sorry, I had trouble processing that request."
2. Removed Redundant AI Pre-processing: We eliminated the 27.7-second Query & Keyword Generation step. The new logic:
   • Combines the user's query and chat history into a single string
   • Generates one vector embedding from that combined string
   • Calls match_documents using only the vector embedding, passing an empty array [] for keywords

The Results: Stability + Speed Breakthrough

The optimization validated our hypothesis that vector-only search would be fast and accurate:

• Latency Reduction: Eliminated the 27.7-second bottleneck entirely. Total server response time dropped from ~41 seconds to ~3.8 seconds—a 90% reduction.
• Database Efficiency: The new vector-only query remained fast and efficient at ~1.1 seconds (1,072 ms) across 6,000 documents.
• Answer Quality (HITL Evaluation): Side-by-side comparison of hybrid vs. vector-only responses showed no significant quality difference. Both versions provided legally correct, relevant, and easy-to-understand answers. The vector-only method occasionally produced slightly more comprehensive results.
• Reliability: Error handling prevented further crashes, ensuring graceful degradation when the AI produced unexpected output.

Key Learnings for the Future

This optimization reinforced critical lessons about building resilient AI products:

• Always Have a Fallback: When parsing or processing external AI outputs, assume it can fail. Wrap critical calls in error handling and provide sensible user-facing fallbacks.
• Question Every Expensive Step: The AI pre-processing step seemed reasonable in theory but consumed 67% of total latency. Always profile before optimizing; measure impact before adding complexity.
• Simpler is Often Better: Removing a step entirely (instead of trying to optimize it) proved faster and just as effective. Occam's Razor applies to system architecture.
• HITL Validation is Essential: We didn't assume vector-only search would be "good enough"—we tested it manually with real examples to confirm quality parity before deploying at scale.

Caveats & Trade-offs

While vector-only search proved effective, important caveats apply:

• Domain-Specific: This optimization works because RAG is already filtering to the right domain (UK tenancy law). In broader search scenarios, the AI keyword extraction might still add value.
• Query Complexity: Very complex or ambiguous queries might benefit from explicit keyword expansion. We monitor for this and can adjust if needed.
• Not a Universal Pattern: Removing pre-processing works here because our embedding model (text-embedding-004) is strong enough to handle conversational queries directly. Other embedding models or use cases may require explicit keyword extraction.

The Problem: User Frustration from Quickfire Questions

I noticed that users who were asked for details (e.g., "how long has the mould been present?") would try to type a multi-line answer. When they hit Enter for a new line, the UI submitted their partial, incomplete thought, confusing the AI and forcing it to ask the same questions again.

What Was Wrong

The UI was fighting the user's intent. A single-line <input> box that submitted on Enter was preventing users from providing the detailed, multi-line answers the AI needed.

The Fix: Aligning the UI with User Intent

My solution was to re-align the UI to match the user's natural workflow. I implemented this by:

1. Replacing the single-line <input> with a multi-line, auto-resizing <textarea>.
2. Changing the submit event from "Enter" to "Ctrl+Enter" (or "Cmd+Enter").
3. Updating the placeholder text to teach this new, more deliberate interaction.