Skip to content

SunriseAI.Api — Agentic Loop Study Notes

Where it lives

AgentService.RunWithAnthropicSdkAsync — the active path (AiSettings.ActiveProvider == "claude"). There's a whole other Semantic Kernel path in RunAsync's main body for OpenAI/Gemini, but per CLAUDE.md it's legacy/alternate, not what production traffic hits. Manual while loop against the native Anthropic SDK, not SK's auto-function-invocation.

The loop, shape

build system prompt (cached) + message history + user message
    ↓
call Claude, tools=[execute_sql]
    ↓
while response.StopReason == "tool_use":
    for each ToolUseContent block in response:
        run SQL (or reuse from sqlResultCache)
        append ToolResultContent
    call Claude again with tool results appended
    ↓
StopReason != "tool_use" → extract JSON → parse into AgentResponse

Single-tool design: the model has exactly one tool, execute_sql. No search, no multi-tool routing — the entire "agentic" surface is "keep calling SQL until you have enough to answer."

Things worth being able to explain in detail

Prompt caching — why the system prompt is built the way it is

SystemMessage gets CacheControl { Type = ephemeral }, and MessageParameters. PromptCaching = PromptCacheType.FineGrained. Caching on Anthropic's side is a byte- for-byte prefix match — anything that changes the system prompt text between requests (even one character) invalidates the cache and you pay full price again.

Two concrete consequences of that constraint, both deliberately engineered around: - SystemPromptBuilder always injects the FULL schema, never conditionally based on the user's question. Tempting "optimization" (only send schema for tables the question needs) would make the prompt variable per-request, breaking the cache — CLAUDE.md explicitly calls this out as a rejected optimization: ~5x more expensive with selective injection + no cache, versus full injection + cache hit. - {{TODAYS_DATE}} is injected as yyyy-MM-dd, not a full timestamp. A full timestamp changes every second, which would invalidate the cache on literally every request. Date-only means the cached prefix only turns over once per calendar day — Anthropic's own caching guidance recommends exactly this: keep volatile content coarse-grained rather than leaving it out or making it maximally precise.

Token accounting reflects this split explicitly: InputTokens (new/uncached), CacheReadTokens (served from cache, ~10% of normal cost), CacheCreationTokens (this call paid to populate the cache). TotalEffectiveInput = InputTokens + CacheReadTokens is logged as the real "how much context did this call actually see" number, separate from what got billed at full price.

sqlResultCache — per-turn, not cross-session

A Dictionary<string,string> keyed on the trimmed, exact SQL string, scoped to one call of RunWithAnthropicSdkAsync (i.e. one user turn, across however many tool-use rounds happen within it). If Claude issues the same SQL twice in one turn — self- correction retrying an identical query, or a follow-up sub-question that happens to repeat an earlier query — the second hit skips DuckDB entirely and reuses the cached CSV text. No staleness risk since it's rebuilt fresh every turn; this is purely about not paying DuckDB execution cost (or, more importantly, not re-sending an identical large result block into a subsequent API call) twice in the same conversation turn.

Why CSV, not JSON, for tool results

SunriseSqlPlugin.SerialiseResult returns CSV, explicitly called out as "significantly more token-efficient than JSON for tabular data" — no repeated {"col": ...} key names per row, just one header row. Every tool result gets re-sent as input on every subsequent call in the same loop (that's how the SDK conversation model works — full message history goes with every request), so this compounds: a verbose serialization format costs you once per remaining round-trip in that turn, not once total.

The 25-row summarization threshold

Above SummaryThreshold = 25 rows, the plugin doesn't send the full result — it sends the first 25 rows as CSV plus a computed numeric summary (total, min, max, average, count) for every column where more than half the values parse as numeric, skipping columns ending in _id/_pk/_code (their sums are meaningless). This lets Claude answer "what's the total revenue" for a 10,000-row result without the full 10,000 rows ever entering context — the aggregate math is precomputed in C#, not left for the model to eyeball from truncated data. Good design instinct: don't trust an LLM to "add up" a truncated column correctly, compute the real aggregate server-side and hand it over as a fact.

max_tokens truncation — the one un-recoverable failure mode

If StopReason == "max_tokens", the response is caught before attempting JSON extraction at all — a truncated response can't produce valid JSON no matter how the extraction logic is written, so this is special-cased into a canned user-facing message ("got cut off, ask something more specific") rather than falling through to the normal parse-then-degrade path.

ExtractJsonPayload — read this section, it corrects something CLAUDE.md gets wrong

CLAUDE.md (as of whenever it was last edited) says: "leading prose before { is not stripped (by design: chart tokens {{chart:N}} contain braces, so blind brace-scanning from the first { anywhere in the text is unsafe and was deliberately rejected)" — implying a "Perfect! Now let me..." preamble before real JSON is unrecoverable by code, prompt-only fix required.

That's stale relative to the actual current code. ExtractJsonPayload (AgentService.cs ~L555) does NOT anchor on the first { — it anchors on the literal pattern \{\s*"answerText" via regex, searched anywhere in the text, not just position 0. Once found, it does the escape/string-aware brace-depth walk from that point to the true matching }. Since {{chart:N}} never has "answerText" immediately following an opening brace, anchoring on the key specifically — not on any { — sidesteps exactly the ambiguity CLAUDE.md describes as the reason mid-text scanning was rejected.

Net effect: a "Perfect, now let me summarise the results:\n{\"answerText\": ...}" response IS now correctly recovered by code — both the leading prose and any trailing prose get logged as warnings (so recurring phrasings can still be harvested into new few-shot examples) and stripped, and the clean JSON is what actually gets parsed.

There's also a fenced-code-block path checked first (```json ... ```, found anywhere via regex, not just at position 0) — same prose-logged-then-stripped treatment.

Practical implication: the current code handles more failure shapes than CLAUDE.md credits it for. If asked "can code fix a leading-prose failure," the honest current answer is "only if the JSON itself starts with {"answerText" somewhere findable in the text" — which covers the actual observed "Perfect! Now let me..." pattern CLAUDE.md cites as its motivating example. The one shape that would still defeat this: prose that never contains the literal "answerText" key at all (e.g. the model abandons JSON entirely and just writes an answer in prose) — that's caught by branch 3 (nothing recognisable found) and falls through to the raw-text-wrap degradation, same as always. Worth flagging to whoever maintains CLAUDE.md that this section needs a re-read.

Graceful degradation, the actual philosophy

Never throws on a malformed response. Chain of fallbacks: 1. Try direct JSON parse of the raw response. 2. If that fails, run it through ExtractJsonPayload (fence-strip / key-anchor-strip) and try again. 3. If still not valid JSON, wrap the (cleaned) raw text as AnswerText with empty Charts — "ugly but visible" beats a hard failure the user sees as a broken app. 4. The only case that skips straight to a canned message instead of attempting any of the above is max_tokens truncation, because no extraction can recover from that.

Who manages the growing message list within one request — NOT Semantic Kernel

For the Claude path, the answer is: a single, plain C# List<Message> local variable inside RunWithAnthropicSdkAsync, built and mutated by hand. No framework owns it — the loop appends to it directly with messages.Add(...) at each step. Nothing manages this "for" the code; the code manages it explicitly, in-process, for the lifetime of that one method call.

The shape of messages as it grows across one HTTP request, concretely:

  1. Before the first API call (AgentService.cs ~L252-271): messages = [ ...stripped prior turns from MariaDB (User/Assistant, text only)..., new Message(User, userMessage) ] The system prompt is NOT part of messages — Anthropic's Messages API models it as a separate top-level System field on MessageParameters (parameters.System = systemMessages), set once, sent unchanged on every call in the loop (this is exactly what makes the ephemeral cache hit repeatedly within one turn, not just across turns).

  2. First call: SendWithRetryAsync(parameters) sends System + Messages as-is.

  3. If response.StopReason == "tool_use" (loop body, AgentService.cs ~L334-404): messages.Add( Message(Assistant, response.Content) ) // the model's tool_use block(s) // ...execute each requested SQL, build ToolResultContent per tool_use_id... messages.Add( Message(User, toolResultContents) ) // tool results, modeled as a "user" message parameters.Messages = messages // same list reference, reassignment is a no-op but harmless response = await SendWithRetryAsync(parameters) // next call, full history so far Anthropic's API convention: tool results are sent back as a User-role message containing ToolResultContent blocks keyed by ToolUseId — not a dedicated "tool" role the way OpenAI's API shapes it.

  4. Loop repeats for as many tool-use rounds as the model needs — each iteration appends one Assistant (tool_use) message and one User (tool_result) message to the same growing list, and every subsequent call resends the entire accumulated list plus the unchanged cached system prompt.

  5. Loop exits once StopReason != "tool_use" — the final Assistant text response is what gets extracted (ExtractJsonPayload) and parsed into AgentResponse.

Where this state lives, and where it doesn't: messages is a method-local variable. It exists only in memory for the duration of that one RunWithAnthropicSdkAsync call — one HTTP request, one user message, however many tool rounds it took. Nothing about the tool-calling trace (which SQL ran, what came back) is ever persisted anywhere. Once the method returns, that list and everything in it is garbage. What actually survives to the next message is only what ChatRequestHandler saves afterward — the final AnswerText + Charts, stripped to plain role+text (see 05-persistence-layer.md) — which is what gets loaded back as "prior history" for the next user message, which then starts an entirely fresh, empty messages list of its own. The model has zero memory of what SQL it ran in a previous turn — only of what it, in the end, said.

Contrast with the SK path (Gemini/OpenAI/Groq): SK's own ChatHistory object plays the equivalent role, but the growth/mutation happens inside SK's GetChatMessageContentAsync call via FunctionChoiceBehavior.Auto() — invisible to AgentService, which only sees the single call it made and the final result. This is exactly why ToolRounds is hardcoded to 0 on that path (noted earlier in this file) — there's no equivalent to loopIteration to count, because the loop isn't hand-written there, it's internal to SK.

Quick recall drill

Q: Why does the system prompt inject the entire schema on every request instead of only what's relevant to the question? A: Prompt caching is a byte-for-byte prefix match. Any per-question variability in the system prompt breaks the cache and makes every request pay full input-token price instead of ~10% for cached tokens — full injection + cache hit is cheaper than selective injection + cache miss, even though it "wastes" tokens on unused schema.

Q: Why is today's date injected as just yyyy-MM-dd instead of a full timestamp? A: Same cache-prefix-match constraint — a timestamp changes every second and would invalidate the cache on every single request. Date-only means the cache only turns over once a day.

Q: What happens if Claude issues the exact same SQL query twice in one turn? A: Second call is served from sqlResultCache (an in-memory dict scoped to that one turn) — no second DuckDB round trip, and the cached CSV text is reused rather than re-executing.

Q: Why CSV instead of JSON for tool results? A: Token efficiency — no repeated key names per row — and it compounds because every tool result gets resent as context on every subsequent call within the same loop.

Q: How does the plugin avoid dumping 10,000 rows into context for a big aggregate query? A: Above 25 rows it sends only the first 25 as CSV, plus a server-computed numeric summary (total/min/max/avg/count) per numeric-looking column, so aggregate answers don't depend on the model eyeballing a truncated sample.

Q: Can a "Perfect! Now let me look at that..." preamble before real JSON be recovered without a prompt change? A: Yes, as of the current ExtractJsonPayload implementation — it searches the whole response text for the {"answerText" key pattern (not just position 0) and extracts from there via a brace-depth walk, logging the stripped preamble as a warning. This is narrower than CLAUDE.md's description suggests; worth a doc update.