Source Code (Python)

Backend API (FastAPI)

Below is the core of our application: the api/index.py endpoint. This file receives requests from the Frontend, orchestrates the Text-to-SQL logic alongside the Vector Search (RAG), and handles the final LLM synthesis.

1. Hybrid Routing & Safe Text-to-SQL

We extract the financial 'Entity' via AI prior to executing any SQL commands to prevent Injection attacks. If the query against the Supabase table yields results, the SQL flow is activated.

# 1. Intelligent Entity Extraction (Safe Text-to-SQL)
try:
    ext_prompt = f"Analyze the following question: '{question}'. Extract ONLY the company name or financial ticker... Return ONLY the exact name, without punctuation."
    ext_res = client.chat.completions.create(...)
    entity = ext_res.choices[0].message.content.strip()
    
    if entity != "NONE" and len(entity) > 1:
        # Relational search in Supabase (SQL)
        response = supabase.table("equities").select("*").ilike("name", f"%{entity}%").limit(3).execute()
        
        if response.data:
            context_text += f"\n--- STRUCTURED DATA ---\n{str(response.data)}\n"
            used_sql = True
except Exception as e:
    print("Entity extraction error:", e)

2. Semantic Vector Search (RAG)

Simultaneously, we embed the user's question and execute a mathematical Cosine Similarity procedure directly against the Embeddings database (pgvector).

# 2. RAG Search in PDFs (Macro Context)
try:
    res = client.embeddings.create(input=question, model="openai/text-embedding-3-small")
    query_embedding = res.data[0].embedding
    
    # match_count: 3 retrieves the 3 most similar PDF chunks
    response = supabase.rpc("match_documents", {"query_embedding": query_embedding, "match_count": 3}).execute()
    
    if response.data:
        docs = [doc['content'] for doc in response.data]
        context_text += f"\n--- MACROECONOMIC DOCUMENTS ---\n" + "\n\n".join(docs)
        used_rag = True
except Exception as e:
    print("Vector search error:", e)

3. Synthesis and Dynamic Source Tags

We pass the combined context to the AI with rigorous Guardrails, explicitly forbidding the invention of values that are not present in the extracted texts above.

# Dynamic calculation for interface context tracking:
if used_sql and used_rag:
    source_used = "Hybrid (SQL Data + PDF Vectors)"
elif used_sql:
    source_used = "Relational Database (SQL Equities)"
elif used_rag:
    source_used = "Vector Search (Macro PDFs)"

# 3. Final Answer Generation with Synthesis Agent
prompt = f"""
You are a Senior Investment Research Assistant for GenAI Capital.
Below is the dynamically extracted context from databases and PDFs.

Dynamically Extracted Context: {context_text}
User Query: {question}

Critical Rules:
1. You MUST answer ONLY using the provided Extracted Context. Do not invent financial math.
2. YOUR FINAL RESPONSE MUST ALWAYS BE 100% IN ENGLISH.
"""
completion = client.chat.completions.create(model="openai/gpt-4o-mini", messages=[...])