Open Source · Blueprint + Reference Implementation
Octofy AI Agent is the backend service behind Octofy Professional: it turns natural-language questions into validated T-SQL (and Python, R or SAS) using retrieval-augmented generation, semantic search, and an iterative attempt loop. Discovery runs knowledge-base first, then fuses schema vectors, value-index hits and business data groups with reciprocal rank fusion before hydrating the evidence into the prompt under a token budget. A deterministic front half—preprocessing, PII masking, history folding, coreference rewriting, scenario routing and fast paths—runs before any model call, and what follows is validation-bound: up to five attempts in 120 seconds with a safety interceptor, structural-hash hallucination detection, an advisory LLM critic, and authoritative database parse validation. For governed metrics the model emits a structured SMQ request that a deterministic compiler turns into physical SQL, so business definitions live in the semantic model rather than in the prompt. The model layer is provider-agnostic through LiteLLM (OpenAI, Azure, Anthropic, Google, DeepSeek, Ollama or a local server), and every store read and write is partitioned by source_id so one deployment can host many warehouses without cross-leakage. Around the pipeline sit Server-Sent Event streaming, an execution endpoint with auto-retry, profiling, insights and chart hints, per-user API keys with admin and user roles, conversation history, and admin governance over schema sync, few-shots, value indexes and vector-store backup. The whole project is open source under the MIT licence and ships with the blueprint that makes it reproducible.
Open source under the MIT licence. The whole project is published on GitHub, where it doubles as a build blueprint. docs/SQL_GENERATION_BACKEND_BLUEPRINT.md is a code-derived specification—exact constants, frozen orderings, verbatim prompts, data contracts and an acceptance matrix—that an AI coding agent can execute to build an equivalent agent from an empty repository, so nobody has to reverse-engineer one. The vibe-coding guide covers the standing instructions, the eight build phases, and the tests to require at each step.
Octofy Pro can talk to any backend that implements the same HTTP contract. The Octofy AI Agent backend API in the Documentation Center lists base URL, authentication, SSE generation, execution, conversations, and admin endpoints.
Type to SQL Sandbox
Illustrative preview: this page simulates the token stream in your browser. The service itself returns real SQL, parsed and validated against your database before it is reported as a success.
-- SQL response will stream hereArchitecture Diagram
Click a node to see what it does.
Mode gate, conversational context, KB-first discovery, the bounded attempt loop, the semantic compiler, execution and analysis.
Data flow: request → deterministic front half (routing, coreference, fast paths) → KB-first discovery with reciprocal rank fusion over schema vectors, the value index and data groups → up to five attempts with database parse validation → SSE status · result · done, with execution, profiling and insights on a separate endpoint.
The repository carries docs/SQL_GENERATION_BACKEND_BLUEPRINT.md—a 1.1 MB, 17-section specification derived from the code itself, with exact constants, frozen orderings, verbatim prompts, field-by-field data contracts and an acceptance matrix. It is written to be executed by an AI coding agent rather than skimmed: standing instructions, eight phase prompts, a section map, and a definition of done.
Evidence is retrieved before the model writes anything—schema objects and columns, few-shot examples, real stored values and business data groups—then fused with reciprocal rank fusion (k = 60) so no single signal dominates, and hydrated into the prompt under a token budget.
Curated question-to-code pairs answer recurring questions with zero generation attempts: an exact match short-circuits the pipeline, and a near match becomes a worked example instead of being returned blindly. The value index maps everyday terms to the literal actually stored in schema.table.column, so predicates use real values rather than plausible-looking guesses.
Up to five attempts in 120 seconds: a safety interceptor, structural-hash hallucination detection, an advisory LLM critic, and authoritative database parse validation (SET NOEXEC ON, dry run, sys.dm_exec_describe_first_result_set). Missing objects trigger discovery expansion, and terminal failures return a structured report instead of a confident guess.
The model emits SMQ—logical metrics, dimensions, filters and timeframes—and a deterministic compiler translates it into physical SQL against the active semantic model. Business definitions live in the model, not in the prompt, and errors such as UNKNOWN_METRIC or MISSING_JOIN_PATH are structured and recoverable.
The same discovery, attempt loop and validation serve T-SQL, Python, R and SAS, chosen per request with target_language. When a stored fast path answers the question, the non-SQL targets materialise that SQL into runnable script code.
Any OpenAI-compatible endpoint—OpenAI, Azure, Anthropic, Google, DeepSeek, Ollama or a local server—through a single request layer that decides parameter naming, temperature omission and reasoning budgets centrally. Every store is partitioned by source_id, so schemas, examples, values and semantic models never leak between warehouses.
Generation streams SSE status, result and done frames; a separate endpoint executes SQL or Python with auto-retry, optional profiling, insights and chart recommendations. Per-user API keys carry admin or user roles, alongside conversation history, contribution review, and admin governance over schema sync, few-shots, value indexes and vector-store backup.