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.
“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.
st.session_state (memory), renders
multimodal outllm.bind_tools(tools) = where ALL tool-selection logic livesquery_sp500_financials · search_earnings_transcriptsqlite3 error
caught → fed back to LLM (hidden loop) → rewrite| Part | What | For |
|---|---|---|
| Dense | FAISS (embeddings) | semantic / meaning |
| Sparse | BM25 | exact keyword |
| Fuse | RRF, k=60 | merge rankings |
all-MiniLM-L6-v2 (free); chunk 600 / overlap 80venv → pip install -r requirements.txtstreamlit run app.py → auto-opens
localhost:8501conn.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.
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.
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.
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.
if/else Router FailsThe 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.
if/else receptionist can only pick one desk. The
semantic router understands the sentence, breaks it in two, and calls both databases in parallel.
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.
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.
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.
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.
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.
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 |
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.
The magic lives in one line: llm.bind_tools(tools). Here's what happens under it:
sql_query string).
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 |
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.
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.
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.
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.
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.
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.
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.
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.
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.
| 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.
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.
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
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.
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.
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.
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:
ToolMessage) and
loop again.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.
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.
Plain English: from the analyst typing to the answer appearing, in eight steps.
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.)
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.
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.
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.@st.cache_resource so it isn't rebuilt on every
interaction (shows a "building index" spinner).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:
git clone <https-url>) into VS Code / Cursor.
python -m venv .venv, then activate
it — .venv\Scripts\activate on Windows,
source .venv/bin/activate on Mac.pip install -r requirements.txt (Streamlit,
LangChain, FAISS, the embedding libs, requests, …)..env (or paste it into the sidebar
at runtime).streamlit run app.py — the browser auto-opens the
UI on localhost:8501.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.
The moment streamlit run app.py fires, the app bootstraps its data before showing the query
box:
@st.cache_resource
“building index” spinner shows here).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.
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.
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. |
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.
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.
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 |
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.
Click to reveal each answer. Read them aloud to rehearse.
llm.bind_tools()?
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.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..env default still works as a local fallback.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).while True loop can only reason forward through isolated tool calls, not
cycle back to re-check an earlier result.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.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.