Mission: Semantic Retrieval Audit: Multilingual Vector Edge-Case Analysis & Pipeline Orchestration as a Data Scientist

Mission/open goal Description

My goal was to look under the hood of Zava's multilingual semantic retrieval system (testing English, Spanish, and French). Instead of just running basic queries, I wanted to rigorously audit its quality, figure out exactly where the vector search breaks down (false positives), and calculate how precision drops as the search space expands.

Harness and model

Harness: GitHub Copilot Agent Mode
Model: GPT-4o

Turn-by-turn journey

Turn 1: Understanding the Data Shape (EDA)
Prompt: Use the aggregate_records SQL MCP tool on the Docs table to show me the total count of documents, grouped by SourceType and TagsJson.

Agent Response: I wanted to see the raw numbers before diving in. The agent returned the counts, but the data was highly fragmented. Support chats were neatly grouped, but reviews were split into tiny clusters because of highly specific SKU tags in the JSON array.

My Pivot: Seeing this, I knew SQL alone wouldn't cut it for aggregation. I jumped into a Jupyter Notebook and wrote a custom Python parser to clean and properly group the data.

Turn 2: Pushing the Limits with Support Chats
Prompt: Use the read_records SQL MCP tool to fetch 2 sample rows from the SupportChats table where the customer is complaining about a delayed order.

Agent Response: The agent initially got tripped up by a malformed $filter parameter when querying the SupportChats table.

My Pivot: Instead of getting stuck, I guided the agent to pull the transcripts directly from the Docs table. We successfully extracted a long-form support chat (DocId 49) so I could test if the vector search gets distracted by conversational noise.

Turn 3: Testing Intent Matching
Prompt: Now use the find_similar_docs_by_doc_id SQL MCP tool to find the top 5 similar documents for DocId 49.

Agent Response: The agent queried the precomputed embeddings and returned the raw JSON.

My Pivot: The model actually performed really well here. It matched the seed chat with other order-status chats (Distances: 0.113 to 0.168) without suffering from "Context Collapse."

Turn 4: Finding the Breaking Point & Cross-Lingual Testing
Prompt: Using the read_records tool, fetch 1 short customer review where the text mentions a physical defect like 'broken', 'zipper', or 'hole'. Once you find it, immediately run the find_similar_docs_by_doc_id tool.

Agent Response: The agent found a review about a "small defect" (DocId 32) and ran the similarity search.

My Pivot: Baseline distance scores jumped significantly (starting at 0.272). On the plus side, the model showed great cross-lingual mapping—matching the English word "defect" with the Spanish "pequeño defecto" (Distance: 0.354). However, at this distance, it started exhibiting "Metadata Hallucination," returning completely unrelated product SKUs despite matching the defect theme.

Turn 5: Testing Agent Orchestration
Prompt: Let's test orchestration. First, use aggregate_records to find the most common scenario in SupportChats. Second, use read_records to fetch one exact DocId from that scenario. Third, immediately run find_similar_docs_by_doc_id to verify if it stays within its category. Show me the step-by-step raw outputs.

Agent Response: The agent flawlessly chained three distinct SQL MCP tools in a single autonomous execution without intermediate human prompting. It aggregated the data to identify 'OrderStatus' as the primary scenario (Count: 15), fetched a representative seed document (DocId 49), and executed the vector search.

My Pivot: It showed that the agent isn't just a basic search tool; it can actually orchestrate a data pipeline. Plus, the results backed up what I noticed earlier: the AI successfully kept all the similar documents (DocIds 71, 52, 81, 65) strictly within the 'SupportChat' category. The distance scores stayed very tight (0.113 to 0.174), proving the model didn't get confused and perfectly held onto the 'OrderStatus' intent.

Completion

• Yes, the agent completed the mission or goal.
• No, the agent did not complete the mission or goal.

Bonus work

Precision@K Decay Math: I didn't want to just guess how accurate the model was. I used Pandas to map my custom taxonomy to strict relevance scores, mathematically modeling exactly how precision drops as the distance threshold expands (from Precision@1 down to Precision@3).

Cross-Lingual Verification: I successfully verified that the embedding space maps English sentiment to French and Spanish equivalents seamlessly, bridging the language barrier without needing explicit translation steps.

My Jupyter Notebook (Working Artifact):
https://github.com/Manya23-code/sql-ai-promptathon/blob/main/Submission/Retrieval_Audit.ipynb

My Full Submission File:
https://github.com/Manya23-code/sql-ai-promptathon/blob/main/Submission/Submission.md

Read Submission/Submission.md first, then inspect Submission/Retrieval_Audit.ipynb to review the documented SQL MCP queries, retrieval results, and Precision@K analysis. The issue reports the mission as complete and does not define a follow-up change or acceptance test, so completion criteria for a new contributor are not provided.