Skip to content

SunriseAI.Api — Comprehensive End-to-End Request Flow

Full trace of one chat request, from the HTTP wire to the persisted row, with the exact point where behavior forks depending on AiSettings.ActiveProvider, and how the two forks reconverge. Read top to bottom — it's linear until explicitly marked as a fork.

STAGE 0 — same for every provider: request enters the pipeline

POST https://{merchant}.sunrisecloud.com/api/chat/stream   (or /api/chat)
Body: { sessionId, message, ... }
  1. MerchantResolutionMiddleware runs first, before any endpoint code.
  2. Reads context.Request.Host.Host (e.g. vinetiq.sunrisecloud.com).
  3. Splits on the first . → "vinetiq".
  4. Case-insensitive lookup against SyncSettings.Merchants.
  5. No match, or matched but IsActive == false → hard 404, pipeline stops here, nothing downstream ever runs.

  6. On success: stores the resolved MerchantConfig in HttpContext.Items["Merchant"] and pushes Merchant into the Serilog log context for the rest of the request — every subsequent log line in this request is automatically tagged with which merchant it belongs to.

  7. JWT Bearer auth middleware (ASP.NET built-in, configured in Program.cs) validates the token signature/expiry against a pre-shared symmetric secret. The API never issues tokens itself, only consumes them.

  8. RequestUser is built (scoped DI) from claims on HttpContext.User — ContactId, MerchantId, ChannelId, Username, Name.

  9. ChatEndpoints — validates the request body (request.IsValid), pulls merchant via context.GetMerchant(), resolves ChatRequestHandler from DI.

  10. /api/chat: calls handler.HandleAsync(merchant, requestUser, request), no status callback, returns the whole AgentResponse as one JSON body.
  11. /api/chat/stream: opens an SseWriter first (Content-Type: text/event-stream), calls handler.HandleAsync(..., onStatusUpdate: async status => sseWriter.WriteAsync ("status", status)), then writes a final "result" SSE event with the serialized AgentResponse once the handler returns.
  12. Both endpoints call the exact same handler method — streaming vs. non-streaming is not a structurally different code path, only whether a non-null status callback exists for the agent loop to invoke.

STAGE 1 — same for every provider: ChatRequestHandler.HandleAsync

  1. Builds a fresh MariaDbExecutor (raw connection string, not DI-scoped) and runs EnsureTablesExistAsync() — idempotent CREATE TABLE IF NOT EXISTS for ai_chat_sessions / ai_token_usage, run on every single request.
  2. Loads (or creates) the ChatSession row for request.SessionId.
  3. Loads the last 10 messages for this session from MariaDB, strips each down to just { Role, ContentText } as ChatMessageContent — no raw SQL, no chart JSON, no token metadata carried forward. This is the entire "conversation memory"; anything older than 10 turns back, or anything beyond role+text, simply isn't there.
  4. Calls AgentService.RunAsync(merchant, request.Message, request.SessionId, strippedHistory, onStatusUpdate) — this is where provider branching starts.
  5. (after RunAsync returns — see STAGE 4 below for what happens after the fork reconverges)

STAGE 2 — AgentService.RunAsync: setup, then the fork point

Still common to every provider: 1. Opens a readonly DuckDB connection for this merchant (DuckDbConnectionFactory.OpenReadonlyConnection → ACCESS_MODE=READ_ONLY in the connection string — enforced at the driver level, not just by convention). 2. Builds a fresh QueryExecutor (wraps that connection) and a fresh SunriseSqlPlugin (wraps the executor) — per request, no shared/singleton state between requests.

The fork:

if (_aiSettings.ActiveProvider.ToLower() == "claude")
    return await RunWithAnthropicSdkAsync(merchant, userMessage, sessionId, chatHistory, sqlPlugin, onStatusUpdate);

// only reached for gemini / openai / groq:
var (kernel, executionSettings) = _kernelFactory.Create(sqlPlugin, onStatusUpdate);

STAGE 3A — ActiveProvider == "claude" (the production path)

No Semantic Kernel involved at all past this point — see 03-user-flow-and-sk-question.md for the full "why," short version: SK's OpenAI-compatible connector had no support for Anthropic's prompt-caching cache_control field, so this path was rewritten against the native Anthropic SDK to get first-class caching support instead of HTTP-layer JSON rewriting hacks.

  1. new AnthropicClient(...) — raw SDK client, built fresh per request.
  2. execute_sql tool schema is defined inline as a Tool/Function JSON schema — hand-written here, not reflected off SunriseSqlPlugin's [KernelFunction] attribute (that attribute is inert on this path).
  3. System prompt built via SystemPromptBuilder.Build(merchant) — full schema always injected, {{TODAYS_DATE}} as date-only — wrapped in a SystemMessage with CacheControl { Type = ephemeral }.
  4. chatHistory (the 10-message DTO list from Stage 1) is converted from ChatMessageContent/AuthorRole into the SDK's own Message/RoleType shape.
  5. First call: client.Messages.GetClaudeMessageAsync(parameters), wrapped in SendWithRetryAsync — retries up to 3× on HTTP 429 with exponential backoff (2s → 4s → 8s), rethrows on final failure.
  6. The loop: while (response.StopReason == "tool_use")
  7. Append the assistant's tool-use response to messages.
  8. For each ToolUseContent block: extract sql from the tool input JSON.
    • onStatusUpdate?.Invoke("Querying analytical store...") (if streaming).
    • Check sqlResultCache (exact-string match, this-turn-only) — hit → reuse, skip DuckDB entirely; miss → sqlPlugin.ExecuteSqlAsync(sql) (plain method call, no SK pipeline), cache the result.
    • onStatusUpdate?.Invoke($"Got {rowCount} rows in {ms}ms — analysing...").
  9. Append all tool results as one ToolResultContent-bearing Message.
  10. onStatusUpdate?.Invoke("Drafting your answer...").
  11. Call Claude again with the extended message list; accumulate inputTokens/outputTokens/cacheReadTokens/cacheCreationTokens across every iteration.
  12. Loop exits when StopReason != "tool_use" (normally "end_turn").
  13. Special case: StopReason == "max_tokens" → skip JSON extraction entirely, return a canned "got cut off" AgentResponse immediately — no amount of extraction logic can recover genuinely truncated JSON.
  14. Otherwise: pull the first TextContent block, run it through ExtractJsonPayload (fence-strip → {"answerText"-key-anchored brace-depth scan → pass-through unchanged as last resort — see 02-agentic-loop.md for the mechanics and the CLAUDE.md correction), then JsonSerializer.Deserialize<AgentResponse>.
  15. On successful parse: stamp token counts + ToolRounds (= loopIteration) + TotalApiCalls (= loopIteration + 1) onto the AgentResponse, return it.
  16. On parse failure: wrap the cleaned text as AnswerText with empty Charts — graceful degradation, never throws.

What SunriseSqlPlugin.ExecuteSqlAsync does on every tool call, either path: - QueryExecutor.ExecuteAsync(sql): - Strips leading --//* */ comments, requires the first real keyword to be SELECT or WITH (CTEs allowed) — anything else throws InvalidOperationException, surfaced back to the model as plain error text so it can self-correct, not a hard failure. - Regex-enforces a LIMIT clause capped at 500 rows — injects one if absent, clamps down an existing one if it's higher. This runs regardless of what the model wrote, so the model cannot bypass the cap by writing its own large LIMIT. - Runs the query via Dapper against the readonly DuckDB connection. - Result serialization (SunriseSqlPlugin.SerialiseResult): - ≤25 rows → full CSV. - >25 rows → first 25 rows as CSV + a server-computed numeric summary (total/min/max/avg/count) per column that's majority-numeric and not _id/_pk/_code-suffixed, so the model can answer aggregate questions without the full result set ever entering context.

STAGE 3B — ActiveProvider ∈ {gemini, openai, groq} (the alternate path)

This is the one where Semantic Kernel is real and load-bearing.

  1. KernelFactory.Create(sqlPlugin, onStatusUpdate):
  2. Registers FunctionStatusFilter (IFunctionInvocationFilter) — before/after every execute_sql invocation, calls onStatusUpdate("Querying analytical store...") then onStatusUpdate("Analysing results..."). This is SK's status-update mechanism — structurally different from the manual onStatusUpdate calls sprinkled through the hand-written Claude loop, but produces similar-looking status text to the client.
  3. Registers AutoInvocationLoggingFilter (IAutoFunctionInvocationFilter) — logs "Model requested tool ... / Tool completed in Nms" around every auto-invoked function call, mirroring the manual logging in RunWithAnthropicSdkAsync so log output looks consistent regardless of which path actually ran.
  4. Registers the real SK connector for whichever provider is active (AddGoogleAIGeminiChatCompletion / AddOpenAIChatCompletion ×2 for openai/groq — groq reuses the OpenAI connector pointed at api.groq.com's OpenAI-compatible endpoint).
  5. Registers SunriseSqlPlugin as a Kernel plugin (AddFromObject) — this is where the [KernelFunction("execute_sql")] attribute actually gets used — SK reflects over it to build the tool schema, unlike the Claude path where that schema is hand-written JSON.
  6. Builds PromptExecutionSettings with FunctionChoiceBehavior.Auto() — SK's own agentic loop (auto tool-calling), not a hand-written while loop.
  7. Builds ChatHistory (SK's own type) — system message + stripped prior turns + new user message.
  8. chatService.GetChatMessageContentAsync(history, executionSettings, kernel) — one call, SK internally handles the entire tool-call loop (auto function invocation), the FunctionStatusFilter/AutoInvocationLoggingFilter firing around each internal execute_sql call.
  9. Token usage extraction is provider-shape-dependent and defensive: tries a generic "Usage" metadata object first (OpenAI/Anthropic-shaped), wrapped in dynamic + try/catch since the shape isn't statically known; falls back to Gemini-specific "PromptTokenCount"/"CandidatesTokenCount" metadata keys if present. ToolRounds is hardcoded to 0 on this path — SK's auto-invocation loop doesn't expose iteration count the way the hand-written Claude loop naturally does, so that field is simply not tracked here.
  10. Same ExtractJsonPayload → JsonSerializer.Deserialize<AgentResponse> → same graceful-degradation-to-raw-text fallback as the Claude path — this part is shared code, not duplicated per provider.

Note on SunriseSqlPlugin re-use across both forks: the exact same plugin instance, built once in Stage 2, is handed to either fork. On 3A it's called directly as a C# method; on 3B it's invoked through SK's reflection-based function-calling pipeline. The 500-row cap, SELECT-only enforcement, and CSV/summary serialization behave identically either way — that logic lives in QueryExecutor/SunriseSqlPlugin, not in either provider-specific branch.

STAGE 4 — reconverges: back in ChatRequestHandler, after RunAsync returns

Identical regardless of which fork ran:

  1. Builds assistantMessage (ContentText = AnswerText, ChartsJson = serialized Charts) — not saved yet.
  2. Builds tokenRecord from whatever AgentResponse populated (InputTokens, OutputTokens, CacheReadTokens, CacheCreationTokens, TotalApiCalls, ToolRounds — the last of which is always 0 if the SK fork ran, real if the Claude fork ran).
  3. Returns agentResponse to the caller immediately — this is what the user actually waits on. Everything below happens after the user already has their answer.
  4. _ = Task.Run(async () => { ... }) — fire-and-forget background persistence:
  5. Builds its own fresh MariaDbExecutor/repos from a raw connection string, deliberately not reusing the scoped ones from steps 1-2 — the HTTP request scope (and anything tied to HttpContext) may already be gone by the time this task actually executes, so it only crosses that boundary with POCOs + a connection string.
  6. SaveSessionAsync, SaveMessageAsync × 2 (user then assistant), LogTokenUsageAsync.
  7. Any exception here is caught, logged as a LogWarning, and silently swallowed — no retry, no surfacing to the user (who's already moved on), meaning a transient MariaDB blip at exactly this moment quietly loses that turn's audit trail with zero visibility beyond a log line.

Back in ChatEndpoints: non-streaming returns Results.Ok(agentResponse) directly; streaming writes a final "result" SSE event with the serialized response, then the SSE connection ends (Results.Empty).

One-paragraph summary, if asked to compress this to an elevator pitch

Every request is merchant-resolved by host header, authenticated, and handed to one handler that loads short conversation history from MariaDB and calls into AgentService. For Claude (production), a hand-written loop drives the native Anthropic SDK directly — manual retries, manual tool-result caching, manual JSON extraction, full control but full responsibility for correctness. For every other provider, Semantic Kernel's own auto-function-invocation loop does the equivalent work, at the cost of losing Anthropic-specific features (prompt caching) and some observability (tool-round counts). Both forks share the same SQL safety layer (QueryExecutor: SELECT-only, 500-row cap) and the same result-shaping (CSV + numeric summaries over 25 rows), and both reconverge on identical graceful-JSON-degradation and fire-and-forget audit persistence — the user gets their answer before the database write for that turn even starts.