Sigma Assistant — Deep Architecture

End-to-end system architecture and prompt engineering pipeline for Stripe's NL-to-SQL assistant powered by GPT-4 Turbo

Prepared for OpenAI PM technical depth interview | Sources: Backend Architecture v1, RFA Tech Talk (Dec 2024), Codebase

System Architecture Full request lifecycle: Client → API Server → MongoDB + RAG + OpenAI GPT-4 Turbo → Streaming Response

Client (React) Sigma Dashboard UI GraphQL → Manage API Session caching Streaming SSE consumer API Server (pay-server, Ruby) GET /ajax/sigma/assistant/:id/get_chat_history POST /ajax/sigma/assistant (send_message) mutate_or_create_with_lock on session Prompt Builder / Orchestrator Assembles messages array from: 1. System prompt (instructions + schema) 2. RAG-retrieved example SQL 3. Chat history (multi-turn context) 4. Current user message RAG Pipeline Cosine similarity search user_question → embedding → vector search → top-K relevant SQL examples Techniques: HyDE retrieval | Fine-tuned embeddings Rule-based reranking | Query expansion Embedding Store SQL examples + human-written descriptions Embeddings of descriptions for vector search Covers Billing, Payments, Subscriptions MongoDB QueryAssistantChatSession merchant:String chat_history:AssistantChatMessage[] {role: USER|ASSISTANT, message, sql?} session_id:qacs_* prefix OpenAI GPT-4 Turbo Egress via Secrets framework 128K context window Streaming SSE response Cost: $210K→$37K/yr (80% ↓) Latency: 15s→7s TTFSB (50% ↓) Streaming Architecture Server-Sent Events (SSE) Reduces perceived latency time-to-first-sql-byte: ~7s Prompt Config (Mongo) AssistantInstructionPrompt {merchant, prompt, active} Per-merchant override, no deploy Excelsior tasks for admin access Check / Modify InstructionPrompt Sigma Schema (Dynamic) 7 tables: invoices, customers, charges, plans, prices, subscriptions, products Token limit constraint: must fit in context Foreign keys + column descriptions request stream messages[] SSE chunks
API Layer
RAG + Schema
Persistence
LLM (GPT-4 Turbo)
Streaming

Prompt Engineering Architecture Full prompt composition pipeline: Role → Instructions → Schema → RAG → CoT → Examples → History → User Message

PROMPT COMPOSITION PIPELINE (messages array) 1 Role Assignment "You are an extremely intelligent agent who generates SQL queries for Trino version 334" 2 Core Instructions & Constraints • Produce single SQL in code block, use CTEs, optimize readability • Only use provided table names, column names, relationships • Verify generated SQL is correct TrinoSQL before responding • Respond "|> request invalid" if unanswerable from provided tables 3 Query Hints & SQL Rules • Aggregates must use GROUP BY (positional refs: GROUP BY 1 ORDER BY 2) • No aliases in GROUP BY/ORDER BY/WHERE unless wrapped in sub-query • Group by currency, use Trino date functions, filter reporting_category 4 Schema Injection (Dynamic, Token-Budgeted) TABLE: invoices(id, amount_due, amount_paid, charge_id, customer_id, ...) TABLE: charges(id, amount, customer_id, payment_intent, card_brand, ...) TABLE: subscriptions(id, customer_id, plan_id, price_id, status, ...) + Foreign key relationships between tables + Column descriptions for ambiguous fields token budget 5 Stripe-Specific Domain Context • Emphasize correct tables for Billing scenarios (invoices vs charges) • Explicit metric overrides (MRR, churn, ARPU definitions) • "Don't rely on status column for historical queries" (current status only) 6 RAG-Retrieved Example (top-1 by cosine similarity) "Use this SQL as a starting point:" SELECT ... FROM subscriptions JOIN prices ON ... WHERE cancel_at IS NULL [Matched from: "active subscriptions with pricing details"] 7 Few-shot Examples + Chain-of-Thought Q: "monthly volumes for top 3 customers over past 2 years" A: WITH monthly_vol AS (...) SELECT customer_id, SUM(...) GROUP BY 1 ORDER BY 2 DESC LIMIT 3 CoT: "Give the LLM time to think and validate steps for task generation" 8 Chat History (multi-turn context from MongoDB) [{role:"user", message:"..."}, {role:"assistant", message:"...", sql:"..."}] 9 Current User Message "How many active subscriptions do we have by plan?" → Sent to GPT-4 Turbo as messages[] array RAG SYSTEM DETAIL Retrieval-Augmented Generation 1. User question → generate embedding 2. Vector search against SQL library 3. Return top-1 most relevant SQL example Advanced Techniques Used: • HyDE: generate fake answer, search with that (arxiv.org/abs/2212.10496) • Fine-tuned embeddings on training set • Rule-based reranking by domain • Query expansion (multi-query combine) • Eval: RAGAS (faithfulness, relevancy, context precision, context recall) QUALITY TECHNIQUES Prompt Engineering Best Practices ✓ Assign role (SQL specialist for Trino) ✓ Clear, structured instructions ✓ Break complex tasks into sub-tasks ✓ Chain-of-Thought (think + validate) ✓ Few-shot with GOOD/BAD examples ✓ Limited token usage (schema pruning) ✓ Test changes systematically (benchmark) Key Insight: Prompt + RAG + Schema together drove 10x increase in Billing question accuracy BENCHMARKING FRAMEWORK BIRD-style Evaluation (100 queries) LLM-as-judge for similarity scoring Gold (20) Multi-join, CTEs Edge cases Silver (25) 2+ table joins Real user queries Bronze (55) 1-2 tables Common use cases Iterative pipeline: 1. Analyze user feedback → 2. Categorize failures 3. Curate expected queries → 4. Enhance prompt/RAG/schema 5. Parallel benchmark run → 6. Compare vs baseline CHALLENGES & MITIGATIONS Problem → Solution Schema too large for context → Dynamic table injection PostgreSQL in training data → Detect + convert to Trino Hallucinated tables → SQL compilation + validation Wrong column types → Dynamic type injection Missing foreign keys → Added FK relationships to schema Ambiguous columns → Added column descriptions Billing-specific failures → Domain-specific RAG content Slow responses (15s) → Streaming + GPT-4 Turbo (→7s) retrieved example
Core Prompt (System)
RAG + Schema
Domain Context
LLM + Benchmarking
Few-shot / CoT
Challenges