MariaDB Persistence Layer — Study Notes

The layer, in one line

Every chat turn's audit trail (session, messages, token usage) is written to the live MariaDB ERP database — not DuckDB, not a separate audit store. MariaDbExecutor is the single choke point every repository goes through.

The most important design trait: fail-soft at the lowest level, not just at the call site

Every method on MariaDbExecutor — ExecuteInternalAsync, QueryInternalAsync<T>, QuerySingleOrDefaultInternalAsync<T> — wraps its MySqlConnector call in try/catch, logs a LogWarning on failure, and returns Enumerable.Empty<T>() / default / just returns, never throws upward. This is one level deeper than the fire-and-forget Task.Run wrapper we already traced in 04-end-to-end-request-flow.md — that wrapper catches exceptions from the repository calls, but the repository calls themselves already can't throw, because MariaDbExecutor swallows at the ADO.NET layer first.

Concrete consequence worth knowing: ChatSessionRepository.GetSessionAsync (ChatRequestHandler.cs:50) — called synchronously, before the agent even runs — runs on every message of a conversation, not just the first. There's no session cache anywhere (ChatRequestHandler's constructor only takes AgentService, AiSettings, and loggers — no IMemoryCache, nothing that would remember a session across calls), so message 1, message 2, message 47 of the same conversation all independently re-query ai_chat_sessions WHERE session_id = @SessionId fresh from MariaDB. On a MariaDB outage this silently returns null, same as any other executor failure, and ChatRequestHandler treats null as "no existing session" and creates a fresh one. So if MariaDB is down, the user doesn't get an error — they get a chat that behaves like it forgot everything, silently starting a new session/history every turn, with no error surfaced anywhere except a log line. Same logic applies to loading the last 10 messages for context: a MariaDB blip mid-conversation looks identical to the model simply "forgetting" — there's no way for the caller to distinguish "no history because new session" from "no history because the DB call failed."

Also: if a merchant's MariaDbConnectionString is blank, every executor method no-ops immediately (if (string.IsNullOrWhiteSpace(_connectionString)) return;) — the entire audit trail is effectively optional per merchant. The chat/agent path itself never depends on MariaDB succeeding; only persistence does.

"Migrations" are just idempotent statements — and they run TWICE: once at boot, then redundantly on every single request

There are two independent call sites for EnsureTablesExistAsync(), confirmed by grepping every reference across the API project, not just reading one file:

1. Program.cs:244-264 — at boot, loops every active merchant once, builds a throwaway MariaDbExecutor/MariaDbSchemaInitializer per merchant, awaits EnsureTablesExistAsync(). This is the "normal" once-at-startup initialization.
2. ChatRequestHandler.cs:42-48 — inside HandleAsync, called on every /api/chat and /api/chat/stream request. Builds its own fresh MariaDbExecutor/MariaDbSchemaInitializer with new and awaits EnsureTablesExistAsync() again.

These are not the same call, and nothing links them — there's no shared flag, no cache, no "already initialized" check anywhere in MariaDbSchemaInitializer or MariaDbExecutor. ChatRequestHandler is DI-registered AddScoped, but that's beside the point — the schema-init objects inside it are constructed with new fresh every call, not injected, so scope lifetime doesn't create any reuse here either. The boot-time call handles the case this exists for; the per-request call is pure, confirmed redundancy — every single chat message re-runs CREATE TABLE IF NOT EXISTS × 4 plus the full stack of ALTER TABLE statements below, against MariaDB, before the agent loop even starts. - CREATE TABLE IF NOT EXISTS × 4 tables (ai_chat_sessions, ai_chat_messages, ai_token_usage, ai_sso_tokens) - A stack of ALTER TABLE ... ADD COLUMN / DROP COLUMN statements, run through ExecuteInternalSilentAsync — a variant that swallows all exceptions with a bare catch {}, specifically so "column already exists" (which MariaDB treats as an error, not a no-op, unlike CREATE TABLE IF NOT EXISTS) doesn't spam warnings on every one of the millions of requests after the column was first added.

This is a real, working "poor man's migration" pattern: schema evolution = append a new ALTER TABLE line to this method. It's simple and it works, at a real cost: every request pays for attempting N already-applied ALTER statements against MariaDB, forever, since there's no tracking of "have I already run this migration" — the only signal is "did it error," and errors here are unconditionally discarded. Contrast this with SunriseAI.Sync's sync_state table, which does track what's been done — this table has no equivalent for its own schema history.

Visible evolution in the comments themselves: ai_token_usage originally had just input_tokens/output_tokens, then tool_rounds was added, then — explicitly explained in a comment — cache_read_tokens/cache_creation_tokens/total_api_calls were added later because input_tokens alone was found to understate real billed cost: on a cache-hit request, most of the actual spend is cache_read_tokens (~10% of input rate, but at scale); on a cache-miss/write request, cache_creation_tokens runs 125-200% of normal input rate — more expensive than an uncached token, not cheaper. If asked "why does the token usage table have so many columns," this is the answer: prompt caching doesn't just make things cheaper, it makes "cost" a multi-component number that a single input_tokens column can't represent.

Soft delete, not hard delete

ChatSessionRepository.DeleteSessionAsync never issues a DELETE — it sets is_deleted = 1 on both the session and (cascaded manually, not via FK) its messages. Every read query (GetSessionAsync, GetSessionsAsync) filters WHERE is_deleted = 0. Data isn't actually removed, just hidden from normal reads — recoverable if needed, at the cost of the table growing forever with no purge path visible in this codebase.

Quick recall drill

Q: What happens to a chat request if MariaDB is completely unreachable? A: The chat still works — agent loop, DuckDB queries, and the response to the user are entirely independent of MariaDB. What's lost is: session continuity (looks like a fresh session every time), conversation history (model sees no prior turns), and the entire audit trail (session/message/token rows never get written) — all silently, logged only as warnings, with zero user-facing indication anything went wrong.

Q: How does the schema handle adding a new column to an existing MariaDB audit table? A: Append an ALTER TABLE ... ADD COLUMN call via ExecuteInternalSilentAsync to EnsureTablesExistAsync. It runs once at boot per merchant (Program.cs) AND again on every single chat request (ChatRequestHandler.cs) — two independent, unlinked call sites, confirmed by grep, not just one. Failures (including "column already exists" on every call after the first) are unconditionally swallowed. There's no migration-tracking table for this layer, unlike SunriseAI.Sync's sync_state.

Q: Does the session get re-fetched from MariaDB on every message, or just once per conversation? A: Every message. ChatRequestHandler.HandleAsync calls GetSessionAsync fresh on every request with no caching layer in between — confirmed by checking the constructor (no cache injected) and the call site (unconditional, every call).

Q: Why does ai_token_usage track cache_read_tokens and cache_creation_tokens separately from input_tokens? A: Because Anthropic prompt caching means those three numbers are billed at wildly different rates — cache reads ~10% of input cost, cache creation 125-200% of input cost (more expensive, not less) — so input_tokens alone (which only reflects new/uncached tokens) significantly understates true request cost. This was added after the fact, per the comment in MariaDbSchemaInitializer, once that gap was noticed.

Q: Is a deleted chat session actually removed from the database? A: No — is_deleted = 1 on the session row and every message row for that session. All reads filter it out; nothing purges it.