Noteapplied

Multi-Source Retrieval — AlphaFund System Design

Building a finance agent that queries a SQL database and unstructured earnings transcripts in one sentence — a semantic router over tool-calling, self-correcting text-to-SQL, hybrid FAISS+BM25 RAG, plus the threat model and Phase 2 roadmap.

30 min read·verified Content review 2026·reviewed 2026-08-30

Assumes you know

Structured Data Orchestration — Text to SQL

After this you should be able to

Explain the core idea in Multi-Source Retrieval — AlphaFund System Design
Apply Multi-Source Retrieval — AlphaFund System Design in a representative exercise

Semantic RouterHybrid RAGThreat Model

Applied GenAI · Multi-Source Retrieval · Session Study Guide

Building a finance agent that answers a single sentence by querying a structured database and unstructured earnings transcripts at the same time — using LangChain tool-calling as a semantic router.

Stack: Streamlit · LangChain · SQLite · FAISS · BM25 Model: Gemini 2.5 Flash via OpenRouter Scope: as taught in session + system-design briefing deck

“Extract once, judge many, serve beautifully.” — the project's own one-line design thesis.

Hand-copy panel. Terse rules, arrows, symbols. Box the margin markers. Everything here is derived from the Detailed Notes.

The Problem & Goal

Pain → analysts waste hrs cross-ref'ing SQL metrics + press releases/transcripts manually
Goal → 1 sentence → query both DBs simultaneously → 1 polished answer
Story fmt = STAR (Situation-Task-Action-Result) for interviews

3-Layer Architecture

1. Presentation = Streamlit (front end)
→ conversational UI, st.session_state (memory), renders multimodal out
2. Orchestration = brain = LLM (Gemini 2.5 Flash) + LangChain
→ models via OpenRouter (free key); LangChain = semantic router
3. Data Retrieval = 2 engines:
Engine A = SQLite + text→SQL (structured, deterministic math)
Engine B = FAISS + BM25 hybrid (unstructured, semantic + keyword)

DOODLE 3 stacked boxes top↓bottom: [Streamlit UI] → [LLM + LangChain router] → splits into two boxes side-by-side [SQL] [FAISS+BM25]

Why It's Hard ★

Rigid if/else router FAILS → question needs BOTH DBs at once
AI must realize it has 2 tools, split the question, fuse results
Ex: "market cap of MSFT?" (SQL) + "what did CEO say on AI capex?" (vector)

Semantic Router (Tool Calling) ★

Not if/else → native LLM function calling in LangChain
llm.bind_tools(tools) = where ALL tool-selection logic lives
→ tool → JSON schema (name+desc+params) → sent to LLM w/ user query
→ LLM's internal decision: match query to tool description(s)
2 tools: query_sp500_financials · search_earnings_transcript
Fire tools sequentially OR parallel (depends on query)

Engine A — Text-to-SQL ★

LangChain SQL agent w/ reflection
Context injection → LLM strictly prompted w/ exact DDL of DB
Self-correction loop → bad SQL → sqlite3 error caught → fed back to LLM (hidden loop) → rewrite
Circuit breaker = max 3 retries → stop infinite token burn
1 SQL agent = many internal tool calls: list tables → get schema → write query

DOODLE loop arrow: [LLM writes SQL] → [run] → error? → back up (curved arrow) w/ counter "1,2,3 STOP"

Engine B — Hybrid RAG ★

Part	What	For
Dense	FAISS (embeddings)	semantic / meaning
Sparse	BM25	exact keyword
Fuse	RRF, k=60	merge rankings

RRF = Reciprocal Rank Fusion, smoothing const = 60 (industry std)
→ scores by position, not raw incompatible scores
Ensemble retriever → strips raw scores, reranks on rank position
Contextual compression → trim irrelevant sentences in winning chunks before → router (save context, cut hallucination)
Embed: all-MiniLM-L6-v2 (free); chunk 600 / overlap 80

DOODLE query → forks to [FAISS list] + [BM25 list] → both merge into [RRF] → ranked list → [compress scissors] → out

Agentic Loop (run_agent)

while True: LLM responds → tools needed? → execute → feed result back → repeat
No more tools → return final answer
Hybrid Q: iter1 SQL → iter2 transcript → iter3 "have enough" → break
Cost → n tools = n API calls (LangChain handles internally)

App Flow (8 steps)

1. user enters OpenRouter API key (sidebar)
2. select LLM model (default Gemini Flash)
3. type financial query
4. agent gets query + access to 2 tools
5. router LLM decides which tool(s)
6. tool(s) execute → raw string result
7. LLM synthesizes final answer
8. show answer + routing log (session-only, lost on close)

Run & Deploy

Setup → get OpenRouter key (copy once!) → clone → venv → pip install -r requirements.txt
Run → streamlit run app.py → auto-opens localhost:8501
on launch: pull CSV→SQLite · build FAISS index · UI ready
Deploy → Streamlit Cloud (free) → shareable link
venv = "bubble" isolating deps · don't rename folder mid-setup (breaks path)

Threat Model & Guardrails ★

Read-Only Execution → SQLite conn string forced read-only, write physically refused
Regex Firewalling → middleware blocks DROP/DELETE/UPDATE/ ALTER/GRANT before DB is touched
Prompt Injection Defense → hardcoded system prompt = DDL limits + few-shot examples
1st two = L1/L2 of Text-to-SQL sheet's 4-layer defense in depth

Limitations → Phase 2 Roadmap ★

Relational Blindness → Neo4j GraphRAG (Wk5)
Fragile Prompting → DSPy compiler (Wk6)
Linear Routing → stateful LangGraph Agent (Wk7)

Key Terms (verbatim)

DDL = Data Definition Language (CREATE TABLE schema)
EPS = Earnings Per Share
RRF score = Σ 1 / (k + rank), k = 60
Ticker = short code = primary key (MSFT, NVDA)
commit + close = push data to DB + assert loaded

⚠ PITFALL forgot conn.commit()/conn.close() → DB locked, insert not saved

★ = box this · switch to Detailed Notes for full explanations & figures

The scene

It's 6 a.m. at a hedge fund. An analyst wants to know one thing before the market opens: "What's Microsoft's market cap, and what did their CEO just say about AI infrastructure spending?"

Half that answer lives in a database of numbers. The other half is buried in a 40-page earnings-call transcript. So the analyst opens two tools, runs a SQL query, then scrolls a PDF, then copies both into a doc. Multiply that by fifty companies every morning. Hours gone — before any actual thinking happens.

The goal here: one chatbox. Type that whole sentence, and a single agent figures out that it needs the numbers database and the transcript store, fires both, and hands back one clean answer.

Engine A — structured / SQL Engine B — unstructured / vector Orchestration / LLM

01The Problem This Solves#

In plain English: analysts burn hours by hand-matching numbers from a database against words from company announcements. We want one search box that does both at once.

Alpha Fund is a quantitative hedge fund. Its analysts constantly cross-reference two very different kinds of information. On one side there's structured data — clean rows in a database: market cap, EBITDA, P/E ratio, sector. On the other there's unstructured data — free-flowing text like press releases and earnings-call transcripts from companies like Meta, Google, Amazon, NVIDIA, and Tesla.

The executives' demand was blunt: give analysts a single unified chat interface where they type one sentence and instantly query both databases simultaneously. That word — simultaneously — is the whole engineering challenge, and we'll return to it.

💡 TIP — interview framing

Narrate projects in STAR format: Situation (analysts waste hours), Task (unify the query), Action (build a routed agent), Result (one polished answer). The objective statement is written specifically so it doubles as an interview story.

The two real-world data sources#

Both are pulled live from GitHub (the raw.githubusercontent.com pattern from the awesome-datasets public repo) — nothing is downloaded locally.

	Structured (Engine A)	Unstructured (Engine B)
Format	A `.csv` of S&P 500 financials	A directory of `.md` markdown files
Contents	ticker, name, sector, market cap, EBITDA, price, P/E, EPS	recent Q4 earnings calls / transcripts of major tech firms
Loaded into	SQLite DB (`sp500.db`)	FAISS vector store (chunked + embedded)

S&P 500 is the stock index tracking ~500 large US companies. + EPS = Earnings Per Share; a ticker (e.g. MSFT, NVDA) is the short company code that also serves as the SQL primary key.

02Why a Simple `if/else` Router Fails#

The core insight: a question like "market cap of MSFT and what did the CEO say about AI capex?" is really two questions pointed at two different databases. A hand-written if/else can only pick one branch.

The analogy: a receptionist with a rigid rulebook. "If the caller says billing, transfer to accounting; if they say tech, transfer to support." Now a caller says "my invoice is wrong AND my app keeps crashing." The rulebook breaks — it can only route to one desk. You need a smart receptionist who understands the sentence, splits it, and rings both desks at once.

Fig. 1 — The if/else receptionist can only pick one desk. The semantic router understands the sentence, breaks it in two, and calls both databases in parallel.

⚠ PITFALL

Don't reach for a keyword if/else router just because it's easy. The moment a real user asks a compound question ("the number and the quote"), it silently answers only half. This exact query is "the boss fight" of the project.

03The Three-Layer Architecture#

The shape of the system: three stacked layers — a face (Streamlit), a brain (LLM + LangChain), and hands (two data engines).

The analogy: a restaurant. The presentation layer is the waiter taking your order in plain language. The orchestration layer is the head chef who reads the order and decides which stations must cook. The data-retrieval layer is the two kitchen stations that actually produce the dishes.

Fig. 2 — Waiter → chef → two kitchen stations. Each layer has one job; the brain decides which station(s) cook.

Layer 1 — Presentation (Streamlit)#

Plain English: the web front end the analyst actually sees. Streamlit is a Python framework that turns scripts into web apps with minimal code. Its whole app runs from a single file, app.py.

It does three jobs: manages the conversational UI, maintains session state (the conversation's memory, via the st.session_state keyword — st is the standard alias for streamlit), and dynamically renders the multimodal output in the browser.

💡 TIP

You don't memorize Streamlit syntax — you recall a handful of keywords (st.text_input, st.session_state, etc.) and read the docs. Worth remembering: Streamlit is deployable and shareable: run locally first, then deploy so anyone gets a link.

Layer 2 — Orchestration (the brain)#

Plain English: the decision-maker. The LLM (default Gemini 2.5 Flash) reads the natural-language prompt, and LangChain acts as the semantic router — it ingests the query, decides which tool(s) are needed, takes their outputs, synthesizes an answer, and displays it.

All models are sourced through OpenRouter (a free API key that fronts many models), and the UI lets the user switch models. ! the transcript at one point mis-says the model is "FAISS" — it's Gemini 2.5 Flash; FAISS is a vector store, not an LLM.

Layer 3 — Data Retrieval (two engines)#

Two engines do the actual fetching — detailed in the next two sections.

Engine	Tech	Handles
A — Structured	SQLite + text-to-SQL pipeline	deterministic math, aggregations, financial metrics (total revenue, total debt, EBITDA)
B — Unstructured	FAISS (dense) + BM25 (sparse) hybrid	semantic intent, qualitative quotes, exact keyword matching

04The Semantic Router: Tool Calling in LangChain#

The idea: instead of you writing routing rules, you hand the LLM a menu of tools and let it decide which to call — this is native function calling.

The analogy: a chef handed a menu card for each station ("Grill: cooks meat, takes a doneness argument"; "Salad: assembles cold plates"). The chef reads the menu, reads your order, and decides which stations to fire. You never wrote "if steak, go to grill" — the chef's own judgment does it.

How binding works, step by step#

The magic lives in one line: llm.bind_tools(tools). Here's what happens under it:

Each Python tool function is converted to a JSON schema — its name, description, and parameters (e.g. a sql_query string).
That schema, plus the user's query, is sent to the LLM through the OpenRouter API.
The LLM now knows: "I have these two tools, and here is the user's question."
The LLM's internal decision logic matches the question against each tool's description and returns which tool(s) to call (as structured JSON with the arguments).

Fig. 3 — bind_tools is where all tool-selection logic lives: tools become schema, schema + query go to the LLM, the LLM decides.

The two tools built in this project:

Tool	Triggered by	What it does
`query_sp500_financials`	"market cap", "total EBITDA", metrics	runs a SQL query against the S&P 500 DB
`search_earnings_transcript`	"what did the CEO say", quotes	semantic search over the transcript vector store

★ TRICK

You can't tune the LLM's internal tool-choice decision directly. What you can improve is the tools themselves — sharper descriptions, better parameters, or adding a third, more robust tool. If accuracy is poor, don't fight the model's black-box decision; give it clearer tools to choose from.

💡 TIP

Simple "hi/hello" chit-chat needs no tool — the base LLM handles it. And tool calling itself is free: you build your own tools and let LangChain orchestrate the calls.

05Engine A — Text-to-SQL with Reflection#

Plain English: turn an English question into a SQL query, run it, and if the SQL is broken, let the model read its own error and fix it — up to three tries.

The analogy: a junior analyst writing a database query. They take a first swing, hit a syntax error, read the red message, correct it, and try again. But you cap them at three attempts so they don't burn the whole afternoon on an impossible request.

Context injection#

The architecture is a LangChain SQL agent with reflection. To make it write correct queries, the LLM is strictly prompted with the exact DDL of the database. + DDL = Data Definition Language — the CREATE TABLE statements that define the schema (columns like ticker, market_cap, ebitda, pe_ratio, eps). Feeding the schema in is how the model knows what tables and columns exist.

💡 TIP — how it "knows" the tables

A common question: how does text-to-SQL know the column and table names? You pass the DB in when creating the agent (LangChain's create_sql_agent reads every table inside it automatically). A single SQL agent call actually fires several internal tool calls: list tables → inspect schema → write the query — visible when verbose=True.

The self-correction loop#

If the LLM generates invalid SQLite syntax, the sqlite3 execution error is caught and fed back to the LLM in a hidden background loop, forcing it to rewrite the query. The analyst reads their own error and tries again.

Fig. 4 — Write → run → on error, feed the error back and rewrite. The circuit breaker stops the loop at three retries.

★ TRICK — circuit breaker

Hard-cap the reflection loop at max 3 retries. On an impossible query the model could otherwise loop forever, burning tokens (= money) infinitely. The cap fails gracefully instead. LangChain also exposes max_iterations (set to 30 in the earlier SQL-agent demo) for the same safety reason.

⚠ PITFALL — the DB-locked bug

In the warm-up demo the DB was blocked and inserts wouldn't run because conn.commit() and conn.close() were missing. Commit pushes the data (like a git push so the DB acknowledges the writes); close asserts the data is loaded. Forget them and your inserts silently don't persist and the connection can lock.

06Engine B — Hybrid RAG (FAISS + BM25 + RRF)#

Plain English: to search the transcripts well you need two kinds of search — one that understands meaning and one that catches exact words — then a fair way to merge their two ranked lists.

The analogy: two librarians. One (dense/FAISS) understands the gist of your request and finds thematically related passages. The other (sparse/BM25) is a stickler for the exact words you said. Ask about "AI infrastructure investments" and the first finds a paragraph phrased as "capital expenditure on data centers," while the second nails the literal phrase "AI infrastructure." You want both — hence hybrid.

The two retrievers#

	Dense — FAISS	Sparse — BM25
Type	vector store (embeddings)	keyword store (term frequency)
Good at	semantic / meaning-based search	exact keyword matching
Analogy	"understands the gist"	"matches literal words"

Build settings: embeddings use all-MiniLM-L6-v2 (free, no API key, from Hugging Face), producing 768-dim vectors. ! MiniLM-L6-v2 actually outputs 384-dimensional embeddings, not 768 — a common "768" assumption is off. Chunking uses a recursive character splitter: chunk size 600, overlap 80.

🚩 SOURCE CONFLICT

The system-design briefing deck names the dense embedding model as Google's text-embedding-004 instead of all-MiniLM-L6-v2. That's most likely the deck describing an aspirational/production-grade swap (a hosted API model) versus what the code walkthrough actually wired up (a free local Hugging Face model) — but the two sources disagree, so don't cite one dimension count and the other model name together as if they describe the same running system. The parallel “Alpha Fund” session showed the exact same split — its architecture theory named text-embedding-004, but its code walkthrough wired a free Hugging Face model — so read text-embedding-004 as the intended/production model and the local MiniLM as what actually ran.

Fusion: Reciprocal Rank Fusion (RRF)#

Both retrievers return their own ranked lists — but their raw scores aren't comparable (a FAISS distance and a BM25 score live on different scales). RRF fixes this by ignoring raw scores and ranking purely on position. The formula uses a smoothing constant of 60 (the industry-standard value):

RRF score(doc) = Σ_lists 1 / (k + rank), k = 60

Fig. 5 — Query fans out to two retrievers; RRF merges their lists by rank; the ensemble reranks; compression trims noise before results go back to the router.

Ensemble retriever & contextual compression#

The ensemble retriever is the mechanism that strips the incompatible raw scores from FAISS and BM25 and reranks chunks purely on positional ranking (this is where RRF is applied). Then contextual compression does final cleanup: even a "winning" chunk contains irrelevant sentences, so those are trimmed before the chunk is passed back to the router — to save context space and reduce hallucination probability.

★ TRICK — why hybrid beats either alone

Pure semantic (FAISS) search misses when a user asks for something exact — a specific ticker, a precise phrase. Bolt on BM25 for literal matches and fuse with RRF, and you get the best of both. This one design merges three separate ideas (BM25, text-to-SQL, RRF) into a single robust architecture.

💡 TIP — k = 60

The RRF smoothing constant 60 is the widely-used industry default. The +k term dampens the influence of any single top rank so one retriever can't dominate the fused result.

07The Agentic Reasoning Loop#

Plain English: the agent keeps looping — call a tool, look at the result, decide if it needs more — until it has enough to answer, then stops.

The analogy: a detective. Ask a question, get a clue, decide whether it's enough or if another lead is needed, chase that lead, re-evaluate — and only write the report once the case is solved.

This lives in the run_agent function, which takes the user prompt, the available tools, and the chosen model. It runs a while True loop:

LLM responds — decides if any tool is needed.
No more tools? → return the final answer, break the loop.
Tool needed? → execute it (look up the function in the tool map), then append the result back into the message list (ToolMessage) and loop again.

Fig. 6 — For the compound question: iter 1 fires SQL, iter 2 fires the transcript search, iter 3 sees it has enough and returns one fused answer.

⚠ PITFALL — cost awareness

One agent on the front end hides many API calls behind the scenes: n tools → up to n+ API calls, plus the extra call each loop iteration to re-ask the LLM. The earlier text-to-SQL agent alone made three internal calls (list tables → schema → query). LangChain hides this gracefully — but it's real token spend, which is exactly why the circuit breaker matters.

💡 TIP

The output is returned as a tuple (the final answer string + a list of tool-call logs) so it can't be accidentally mutated downstream. If a tool fails, the loop raises an exception and returns nulls gracefully rather than crashing the app.

08End-to-End App Flow#

Plain English: from the analyst typing to the answer appearing, in eight steps.

Fig. 7 — The full pipeline: key → model → query → agent → router → tools → synthesis → displayed answer with routing log.

★ TRICK — API key in the front end

Why ask the user for the OpenRouter key in the sidebar instead of hard-coding it in the backend? So that when the app is deployed and shared, each random user brings their own key and burns their own tokens — you don't pay for strangers' usage. (A backend default from your .env still works locally as a fallback.)

💡 TIP — the routing log

The UI shows which router/tool fired for each query — a lightweight observability feature so you can see how the answer was fetched. In a production-grade system you'd log these results properly.

⚠ PITFALL — session-only memory

Conversations are saved only in the current session state. Close the server or clear the session and the history is gone — there's no persistence across states. Fine for a demo, but a real deployment needs a database-backed store.

Practical build details (from the code walkthrough)#

Data is fetched at runtime from raw.githubusercontent.com with a 15-second timeout and a raise_for_status() error check; a manual executemany insert is the fallback if the GitHub fetch fails.
The FAISS index build is wrapped in @st.cache_resource so it isn't rebuilt on every interaction (shows a "building index" spinner).
The transcript corpus is markdown files for Microsoft, Apple, Alphabet/Google, NVIDIA, and Amazon Q4 earnings.
Enhancement ideas: plug in a live web-scraping/Trading View API for real-time data — but note FAISS then needs curated, filtered data for a few companies over recent months to stay manageable.

Setup, Run & Deploy#

Plain English: the whole app is one app.py. Get a key, isolate the dependencies, install them, run one command, and (optionally) push it to a free host for a shareable link.

Before that eight-step query flow can happen at all, here's the setup ceremony a Streamlit app needs:

Get an OpenRouter API key — free, no credit card. You see the key once, so copy it somewhere safe immediately; you can't view it again.
Clone the repo (git clone <https-url>) into VS Code / Cursor.
Create a virtual environment: python -m venv .venv, then activate it — .venv\Scripts\activate on Windows, source .venv/bin/activate on Mac.
Install dependencies: pip install -r requirements.txt (Streamlit, LangChain, FAISS, the embedding libs, requests, …).
Set your key in the environment / .env (or paste it into the sidebar at runtime).
Run it: streamlit run app.py — the browser auto-opens the UI on localhost:8501.

💡 TIP — why a virtual environment

Think of a venv as a bubble: every dependency this project installs lives inside it, isolated from your other projects so their package versions can't collide. Recommended, not strictly required — but the norm for any real Python app.

What happens on launch#

The moment streamlit run app.py fires, the app bootstraps its data before showing the query box:

Pulls the S&P 500 financials CSV from GitHub → loads it into SQLite.
Builds the FAISS vector index from the transcript markdown (the @st.cache_resource “building index” spinner shows here).
Renders the terminal UI, ready for a query. You pick a model (Gemini / Claude / OpenAI fallback), ask, and the agent fires Engine A then Engine B and returns one executive summary — with the routing log showing which tool ran.

Deploy: Streamlit Cloud#

Deployment is free on Streamlit Cloud: push the repo, point the service at app.py, and it hosts the app at a public link you can share (drop it on LinkedIn, hand it to a colleague). Combined with the sidebar API-key trick above, each visitor brings their own key and spends their own tokens.

⚠ PITFALL — don't rename the folder mid-setup

A gotcha: renaming the project folder after the terminal had already cd'd into it broke pip install -r requirements.txt with a “cannot find the file” error — the shell's working-directory path no longer existed. If you rename, reopen the folder / restart the terminal so the path resolves.

09Threat Model & Guardrails#

Plain English: an LLM that's allowed to write and run SQL against your database is, by construction, one hallucination away from a catastrophic query. Three independent guardrails keep that from mattering.

The analogy: airport security, not a single locked door. Your ID is checked before you reach the gate (read-only execution — the agent's credentials physically can't write). Your bag goes through a scanner regardless of who you are (the regex firewall — it inspects every query, no exceptions). And the no-fly list runs in the background the whole time (prompt-injection defense — hardening what the model is willing to be told to do). Any one check failing still leaves the other two standing.

Guardrail	Mechanism	Stops
Read-Only Execution	The SQLite connection string is forcefully restricted to strict read-only permissions.	Any write path at all — even a hallucinated `DROP` or `UPDATE` is physically refused by the connection itself.
Regex Firewalling	Middleware blocks any generated string containing `DROP`, `DELETE`, `UPDATE`, `ALTER`, or `GRANT` — checked in Python, before the engine is ever touched.	Destructive SQL, caught cheaply and logged, without spending a network round-trip to the DB just to receive a permission error.
Prompt Injection Defense	System prompts are hardcoded with the DDL schema limits and few-shot examples that anchor the model's output behavior.	The model wandering off-schema, or being steered off-task by adversarial instructions smuggled inside retrieved transcript text.

💡 TIP — this is the same SQL agent, layered further

Read-Only Execution and Regex Firewalling are the same two ideas as Layer 1 (least privilege) and Layer 2 (regex firewall) of the four-layer defense-in-depth model in the Text-to-SQL sheet — because Engine A here is that same text-to-SQL agent. That sheet goes two layers deeper (OLAP replica sandboxing, statement timeouts) that this deck doesn't mention; treat it as the fuller reference for SQL-specific hardening, and this section as the one that adds prompt-injection defense, which is specific to the LLM layer rather than the SQL layer.

⚠ PITFALL

None of these three guardrails cover an expensive but valid read — e.g. an AI-written cross-join over every row in the transcript store. Read-only stops writes, not slow reads. If you extend this project, borrow the OLAP-replica and statement-timeout layers from the Text-to-SQL sheet rather than assuming read-only alone is sufficient protection.

10Limitations & the Phase 2 Roadmap#

Plain English: the deck names three specific architectural ceilings this build hits — and, notably, already has a dated fix planned for each.

Current Limitation	Architectural Description	Phase 2 Upgrade Path
Relational Blindness	FAISS can't understand multi-hop relationship chains across distinct corporate entities — e.g. "which of NVIDIA's suppliers also mentioned margin pressure?" requires walking a graph, not ranking chunks.	Introduce Neo4j GraphRAG
Fragile Prompting	Reliance on manual prompt engineering, which is highly brittle to underlying LLM model updates — a prompt tuned for one model version can silently degrade after a model swap.	Algorithmic prompt compilation via DSPy
Linear Routing	The router can only execute isolated tools and cannot natively reason through cyclical problems — e.g. "check the number, and if it's an outlier, go re-check the transcript for why."	Refactor into a stateful LangGraph Agent

★ TRICK — the pattern behind all three

Every row follows the same shape: this component was built to be simple/deterministic, and that simplicity is exactly what breaks under a harder question. The agentic loop (Section 07) is linear because each iteration reasons about the next tool call, not about revisiting a earlier one — that's precisely what "Linear Routing" names. Good interview framing: name the limitation, then name why the current design chose simplicity anyway (fewer API calls, easier to debug, ships faster) before naming the upgrade.

11Interview & Practice Q&A#

Click to reveal each answer. Read them aloud to rehearse.

Q1Why does a rigid if/else router fail here, and what replaces it?

A compound question ("give me the market cap and what the CEO said") points at two databases at once, but if/else can only take one branch, so it silently answers half. It's replaced by a semantic router using native LLM function calling: the LLM reads the tools' JSON schemas and decides which tool(s) to call — sequentially or in parallel — based on the query.

Q2What actually happens inside llm.bind_tools()?

Each tool function is converted into a JSON schema (name, description, parameters). That schema plus the user query is sent to the LLM via the API. The LLM's internal decision logic matches the query against each tool's description and returns structured tool calls with arguments. All the tool-selection logic effectively lives in this one line.

Q3Explain the text-to-SQL self-correction loop and its circuit breaker.

The LLM writes SQL from the question (using the DB's DDL injected into its prompt) and runs it. If it produces invalid SQLite, the sqlite3 error is caught and fed back to the LLM in a hidden loop that forces a rewrite. A circuit breaker caps this at three retries so an impossible query can't loop forever and burn tokens indefinitely.

Q4Why hybrid retrieval instead of just FAISS? What is each part for?

FAISS (dense/vector) is great at semantic, meaning-based search but weak on exact matches like a specific ticker or literal phrase. BM25 (sparse) handles exact keyword matching. Combining them — hybrid RAG — covers both "gist" queries and "exact" queries, which a single retriever would miss.

Q5What is Reciprocal Rank Fusion and why is the constant 60?

RRF merges multiple ranked lists whose raw scores aren't comparable by scoring each document as the sum of 1/(k + rank) across lists — ranking purely on position, not raw score. The smoothing constant k is set to 60, the industry-standard value; it dampens the pull of any single top rank so one retriever can't dominate.

Q6What do the ensemble retriever and contextual compression each do?

The ensemble retriever strips the incompatible raw scores from FAISS and BM25 and reranks chunks purely on positional ranking (applying RRF). Contextual compression then trims irrelevant sentences out of the winning chunks before they go back to the router — saving context space and reducing hallucination.

Q7Describe the agentic reasoning loop for a two-part question.

In a while True loop the LLM decides which tool to call, the tool executes, and its result is appended back into the message list. For the compound question: iteration 1 fires the SQL tool, iteration 2 fires the transcript tool, iteration 3 sees it has enough information, breaks the loop, and synthesizes one final answer.

Q8Why collect the API key in the front end rather than the backend?

So the app can be deployed and shared: each user supplies their own OpenRouter key and spends their own tokens, so the developer isn't paying for everyone's usage. A backend .env default still works as a local fallback.

Q9What are the main limitations of this demo build?

Conversations persist only within the current session state — closing the server or clearing the session loses history. The whole app lives in one app.py file (fine for Streamlit, not ideal practice). And tool accuracy depends on the LLM's opaque internal decision, which you improve by sharpening tools rather than tuning the model. Beyond these demo-level gaps, the system-design deck names three deeper architectural ceilings — relational blindness, fragile prompting, and linear routing — each with a dated Phase 2 fix (see Section 10).

Q10What three guardrails protect the agent, and what does each one actually stop?

Read-Only Execution restricts the SQLite connection string itself to read-only, so a hallucinated write is physically refused at the connection level. Regex Firewalling is Python middleware that blocks any generated string containing DROP/DELETE/UPDATE/ALTER/GRANT before the query ever reaches the database. Prompt Injection Defense hardcodes the system prompt with DDL schema limits and few-shot examples so the model can't easily be steered off-task by adversarial text hidden inside retrieved transcripts. The first two mirror Layers 1–2 of the Text-to-SQL sheet's defense-in-depth model; prompt-injection defense is the one guardrail specific to this agent's LLM layer.

Q11Name the three Phase 2 upgrades and the limitation each one fixes.

Neo4j GraphRAG (Week 5) fixes relational blindness — FAISS can't trace multi-hop relationships between distinct companies, a graph database can. DSPy fixes fragile prompting — compiling prompts algorithmically instead of hand-tuning them makes the system less brittle to model updates. A stateful LangGraph Agent fixes linear routing — the current while True loop can only reason forward through isolated tool calls, not cycle back to re-check an earlier result.

Q12How do you run this app locally, and how is it shared?

It's a single app.py. Create and activate a virtual environment, pip install -r requirements.txt, set your OpenRouter key, then streamlit run app.py — the UI opens on localhost:8501, pulling the S&P 500 CSV into SQLite and building the FAISS index on launch. To share it, push to Streamlit Cloud (free), which hosts it at a public link; combined with the sidebar API-key entry, each visitor spends their own tokens.

Key Takeaways#

The problem is "simultaneously." One sentence must hit a numbers database and a text store at once — a rigid if/else can't, a semantic router can.
Three layers: Streamlit (face) → LLM + LangChain (brain, semantic router) → two data engines (hands).
Tool calling = the router. bind_tools turns tools into JSON schema; the LLM picks which to fire. Improve accuracy by improving the tools, not the model's internal decision.
Engine A (text-to-SQL) injects the DDL, self-corrects on SQL errors in a hidden loop, and is capped by a 3-retry circuit breaker.
Engine B (hybrid RAG) = FAISS (semantic) + BM25 (keyword), fused by RRF (k=60), reranked by an ensemble retriever, then compressed to cut noise and hallucination.
The agentic loop calls tools, feeds results back, and repeats until it has enough — every extra tool and iteration is a real API call, so guardrails matter.
Deploy-minded touches: user-supplied API key, a visible routing log, and (a known limitation) session-only memory.
Three independent guardrails protect the agent: read-only execution (write path physically refused), a regex firewall (destructive verbs blocked in Python before the DB), and hardcoded prompt-injection defense (DDL limits + few-shot examples). Read-only + regex are the same first two layers as the Text-to-SQL sheet's defense-in-depth model — this project adds prompt injection as a third, LLM-specific layer.
The roadmap names its own ceilings: relational blindness → Neo4j GraphRAG, fragile prompting → DSPy, linear routing → a stateful LangGraph agent — each an upgrade to a component that was deliberately built simple first.

01The Problem This Solves#

The two real-world data sources#

02Why a Simple if/else Router Fails#

03The Three-Layer Architecture#

Layer 1 — Presentation (Streamlit)#

Layer 2 — Orchestration (the brain)#

Layer 3 — Data Retrieval (two engines)#

04The Semantic Router: Tool Calling in LangChain#

How binding works, step by step#

05Engine A — Text-to-SQL with Reflection#

Context injection#

The self-correction loop#

06Engine B — Hybrid RAG (FAISS + BM25 + RRF)#

The two retrievers#

Fusion: Reciprocal Rank Fusion (RRF)#

Ensemble retriever & contextual compression#

07The Agentic Reasoning Loop#

08End-to-End App Flow#

Practical build details (from the code walkthrough)#

Setup, Run & Deploy#

What happens on launch#

Deploy: Streamlit Cloud#

09Threat Model & Guardrails#

10Limitations & the Phase 2 Roadmap#

11Interview & Practice Q&A#

Key Takeaways#

02Why a Simple `if/else` Router Fails#