How We Built an In-Database AI Agent in Pure T-SQL (And Fixed Its Hidden Architectural Blindspots)

An AI agent doesn't need a heavy Python microservice, a complex LangChain orchestrator, or an external background daemon. In our architecture, ask_ai is an autonomous agent that lives entirely inside a SQL Server database schema ({app}_proj) [28, 29].

Its core engine (sp_openai_agent) is a tight 332-line WHILE loop written in pure T-SQL: it fetches an execution step, invokes an HTTP endpoint via sp_ask_ai_http (sp_invoke_external_rest_endpoint), parses JSON tool calls, executes dbo.sp_ask_ai_run_tool, and repeats [28]. There is no external daemon or scheduler—its tools are stored procedures dispatched directly within the caller's session, fenced securely by SQL Server permissions (EXECUTE AS LOGIN = 'grok_agent', ownership of SCHEMA::ai) [28].

However, operating an in-database agent across multi-tenant customer databases revealed subtle architectural blindspots [29, 30]. In this post, we'll walk through how we audited ask_ai, diagnosed its context bloat and cross-database blindness, and executed a precision plan to give it self-monitoring organs [30, 31, 36].

---

The Architecture: Framework vs. Business Database

Every TSQL.APP solution carries the identical {app}_proj framework database schema; only metadata rows differ [29]. The customer business database ({app}) is built individually per customer after the solution ships [29].

Because {app} does not exist when {app}_proj ships, ask_ai ships knowing only the framework architecture, and must discover and learn the business database as it is constructed [29]. All routines live in {app}_proj and are distributed to solutions using distribute-ask-ai.sh [28, 29].

┌─────────────────────────────────────────────────────────────────────────────┐
│                             {app}_proj (Framework)                          │
│   sp_openai_agent (332-line T-SQL WHILE loop)                               │
│   sp_ask_ai_http → sp_invoke_external_rest_endpoint                         │
│   SCHEMA::ai (Permission Fence)                                             │
└──────────────────────────────────────┬──────────────────────────────────────┘
                                       │
                                       ▼ (Dispatches via dbo.main_db())
┌─────────────────────────────────────────────────────────────────────────────┐
│                             {app} (Customer Database)                       │
│   Business Tables, Views, Stored Procedures, and Metadata Cards             │
└─────────────────────────────────────────────────────────────────────────────┘

The Structural Blindspot

During an architectural audit, we discovered that ask_ai's tools were split into two layers, but only one layer was aware of the customer database [30]:

1. Descriptive Layer (schema, describe_table, list_tables, describe_procedure, sql_select): Correctly reached the customer database {app} via dbo.main_db() [30, 31].
2. Structural Layer (app_map mode=find, relate, fn_ask_ai_lineage): Used bare sys.* catalog views without dbo.main_db(), restricting queries to DB_NAME() (which evaluated to {app}_proj only) [30].

As a result, the structural layer was completely blind to customer business tables and procedures in {app} [30].

---

Finding 2: Working Memory Bloat ($0.78 per Turn)

While inspecting memory usage, we uncovered significant context bloat in the prompt generation pipeline [31].

The header for fn_ask_ai_fishing_prompt claimed it was "Short by construction" [31]. However, a live measurement revealed that it rendered 44,972 characters from 69 injected lessons, while fn_ask_ai_system_prompt added another 17,917 characters [31].

-- The Hidden Cost of System Prompts
SELECT 
    LEN(dbo.fn_ask_ai_fishing_prompt()) AS fishing_prompt_chars, -- 44,972 chars
    LEN(dbo.fn_ask_ai_system_prompt())  AS system_prompt_chars;  -- 17,917 chars

Key impact metrics measured over 14 days on master [31]:

• Token Overhead: ~16,000 tokens paid on every single step of every turn before tools or conversation history were added [31].
• Performance Impact: 80 turns averaged 46,514 tokens, 8.0 seconds of latency, and $0.78 in cost per turn [31].
• Context Dilution: Micro-rules (such as "arch_doc_gap_summary uses gap_count column") occupied working memory even on unrelated turns, such as sending emails [32].

The agent had tools to act (remember, forget, merge_lessons), but lacked a perception gauge to measure its own prompt size and token expense [32].

---

Finding 4: The Bench Test Instrument Fell Behind

We also audited the agent's automated test bench (bench-ask-ai.sh) [33, 35]:

• Outdated Coverage: The last benchmark run occurred on September 2, 2026, before new documentation layers (document_object, arch_doc) shipped [33]. Only 6 of 26 tools were covered across 14 probes [33].
• Saturated Tests: The test suite's honesty-nonsense test checked if the response contained the letter "o", making it impossible to fail [33]. Core suites were saturated and passed everywhere, making it impossible to detect regressions [33].
• Hardcoded Environments: Test target boxes were hardcoded to master|bikkel|troost, limiting multi-solution test execution [33].
• Opaque Failures: Test logs recorded the agent's output string on a FAIL, but omitted the exact context sent to the model, requiring manual reproduction [33].

---

The Upgrade Plan: Spanning, Repairing, and Self-Awareness

To resolve these findings, we designed a step-by-step, verifiable refactoring plan [34]:

1. Teaching the Structural Layer the Whole Solution

We refactored structural tools (app_map, relate) to resolve dbo.main_db() internally, span both {app}_proj and {app}, label every output row with its database name, and render the databases in distinct blocks [34].

We adopted the principle of "Armour over ghost": adding an opt-in db=both parameter while explicitly labelling every returned row, ensuring the model never presents a business object as a framework object or vice-versa [34].

-- Step 1 Verification: Test cross-database name and lineage resolution
EXEC dbo.agent_app_map @mode='find', @q='<term>';
EXEC dbo.sp_ask_ai_relate @object_name='<business_table>';

2. Repairing the Test Instrument

We updated bench-ask-ai.sh to resolve test environments dynamically from the database rather than the filesystem [35]. We replaced the honesty-nonsense assertion with strict must-contain and must-NOT-contain rules, and updated failure logging to automatically record prompt inputs via sp_ask_ai_review_recall into bench-results.tsv [35].

3. Engineering Missing Organs via sp_ask_ai_self

Rather than introducing new tools that the agent might ignore, we extended sp_ask_ai_self with two new operational modes [36]:

• self mode=health: Computes live diagnostic signals at runtime—verifying read path execution via dbo.api_card impersonation (the error 15406 canary), checking recall status, counting unembedded index rows, testing business database reachability, and confirming SCHEMA::ai ownership [36].
• self mode=footprint: Measures memory usage by returning LEN(fn_ask_ai_fishing_prompt()), LEN(fn_ask_ai_system_prompt()), total injected lesson count, and ranking individual lesson token sizes against vw_ask_ai_spend_turn [36].

---

Strict Refactoring & Verification Discipline

When maintaining an in-database AI agent, small deployment oversights can lead to silent failures [37]. We adhered to strict operational rules during execution:

1. Verify by Behavior, Not Object Counts: Deferred name resolution allows T-SQL procedures to compile successfully even when referencing non-existent tables. Every step was verified by running functional queries on live instances [37].
2. Atomic T-SQL Literal Protection: @tools definitions are stored as T-SQL string literals. A single unescaped single quote (') causes compilation to fail silently, keeping the old definition while shell && script chains report success [37]. Always verify updated definitions using vw_ask_ai_tool [37].
3. Report-First Distribution: Running distribute-ask-ai.sh without --apply generates diff reports first, preventing accidental overwrites of customer-specific database roles [37].
4. Guarded Operations: We intentionally avoided touching active lesson vector embeddings, kept askAiLearningBeats disabled by default, avoided broadening infra/06_own_objects permissions, and barred the agent from executing its own generated action buttons due to sa privilege risks [38].

---

Conclusion

Building an AI agent directly inside SQL Server proves that database-native intelligence is both viable and performant [28]. By replacing external orchestrators with a 332-line T-SQL loop, ask_ai achieves zero-latency access to schema definitions and transactional safety [28, 29].

However, maintaining long-term reliability requires strict observability over context prompt sizes, cross-database tool visibility, and automated self-health diagnostics [30, 31, 36].

Have you experimented with running LLM tool execution loops directly inside relational databases? Let us know your thoughts in the comments below!