Chapter 19: Interceptors - Auditing AI Prompts

Theoretical Foundations

Interceptors in Entity Framework Core represent a powerful extension point within the database provider's execution pipeline. They allow you to intercept and modify the behavior of database operations as they flow through the context. While traditionally used for query optimization, connection management, or modifying SQL statements before execution, their application in modern AI-integrated systems is profound. Specifically, IDbCommandInterceptor enables the auditing of AI prompts, ensuring that every interaction with an AI model—whether for generating text, creating embeddings, or performing RAG (Retrieval-Augmented Generation)—is logged, traceable, and compliant with governance standards.

The Architectural Role of Interceptors in AI Systems

In a standard EF Core application, the DbContext coordinates the translation of LINQ queries into SQL commands. These commands are then executed against the database. The interceptor acts as a middleware, a "man-in-the-middle" observer that sits between the application logic and the database driver.

When we introduce AI—specifically Large Language Models (LLMs)—into the stack, the data flow becomes bidirectional. We fetch data from the database (using EF Core), send that data to an AI model (via an API or local endpoint), and then potentially store the AI's response back into the database.

The Problem: AI interactions are non-deterministic. Unlike a SQL query which returns the same result for the same input (assuming data consistency), an AI prompt might yield different results due to model updates, temperature settings, or stochastic sampling. If an AI generates a harmful response or a factual error, you need to answer:

1. What was the exact prompt sent?
2. When was it sent?
3. Who requested it (which user/context)?
4. What data was used to construct the prompt (the RAG context)?

Without interception, this data is ephemeral, existing only in memory for the duration of the request. Interceptors provide a persistent hook to capture this state at the exact moment it is serialized for transmission.

Analogy: The Customs Officer

Imagine a high-security border crossing. Goods (data) move constantly between two territories: your Application (Territory A) and the AI Model (Territory B).

1. The Standard Flow: Without oversight, trucks (database commands/AI requests) drive straight through. You have no record of what was transported, only the final manifest (the result).
2. The Interceptor: This is the Customs Officer standing at the gate.
   • Incoming (Query): The officer inspects the truck (SQL query), stamps it, and logs the contents.
   • Outgoing (Command): The officer inspects the cargo (AI prompt data) leaving your territory. They take a photograph (audit log), check for contraband (compliance), and record the destination.

In EF Core, IDbCommandInterceptor is that Customs Officer. It inspects the DbCommand (the truck) containing the SQL or parameters (the cargo) before it leaves the application boundary.

Theoretical Foundations

IDbCommandInterceptor is an interface defined in Microsoft.EntityFrameworkCore.Diagnostics. It contains methods that are invoked at specific stages of the DbCommand lifecycle.

Key Methods:

• ReaderExecuting / ReaderExecutingAsync: Called before a query that returns multiple rows is executed.
• NonQueryExecuting / NonQueryExecutingAsync: Called before an INSERT, UPDATE, or DELETE command.
• ScalarExecuting / ScalarExecutingAsync: Called before a query returning a single value.
• CommandFailed / CommandFailedAsync: Called when a command execution fails.

Why this matters for AI: In a RAG workflow, the flow typically looks like this:

1. User asks a question.
2. EF Core executes a vector search query (e.g., using pgvector or Azure SQL) to find relevant documents.
3. The results are formatted into a prompt string.
4. The prompt is sent to an AI model.
5. The AI response is displayed.

If we rely solely on AI SDK logs, we lose the context of why the AI received that prompt—the specific database records that fueled it. By using an interceptor, we can capture the database query that retrieved the context documents. We can then correlate that database query with the subsequent AI prompt, creating an unbroken chain of custody for the data.

The "What If": Edge Cases and Architectural Implications

What if the AI prompt contains PII (Personally Identifiable Information)? In many RAG systems, the prompt is constructed by injecting database records (e.g., customer support tickets) into the prompt template. If those records contain PII, that PII is transmitted to the AI provider (e.g., OpenAI). Without auditing, you have no record of this transmission.

• Solution: An interceptor can inspect the parameters of the DbCommand. If the query retrieves sensitive data, the interceptor can flag the audit log. Furthermore, by intercepting the command before execution, you can theoretically mask or hash parameters if the command is being sent to an untrusted replica, though typically, auditing happens after the data is fetched but before it is sent to the AI.

What if the Vector Database query fails? If the vector search fails, the RAG context is empty, and the AI might hallucinate.

• Solution: The CommandFailed method in the interceptor captures the exception and the command state. This allows you to trace an AI hallucination back to a failed database retrieval, distinguishing between "the AI is broken" and "the database retrieval failed."

Deep Dive: The Lifecycle of an Audited AI Request

To fully understand how interceptors facilitate AI auditing, we must trace the lifecycle of a request involving EF Core and an AI service.

1. Initialization: The application starts, and an IDbCommandInterceptor is registered in the DbContextOptions. This is similar to how we previously discussed Dependency Injection in Book 1, where services are injected into the container. Here, the interceptor is injected into the EF Core infrastructure.

2. The Trigger: A user initiates a chat. The application logic calls a service method that queries the database for relevant context using EF Core.

3. Interception Point 1 (Database):

   • The DbContext generates a DbCommand.
   • Before execution, ReaderExecutingAsync is called.
   • Action: The interceptor captures the Command.CommandText and Command.Parameters.
   • Crucial Distinction: At this stage, we are auditing the retrieval of data. We know exactly which documents were pulled from the database to feed the AI.

4. Data Processing:

   • The query executes, and results are materialized into C# objects (POCOs).
   • The application logic (or a library like Semantic Kernel) formats these objects into a string prompt.
   • Note: The interceptor does not directly see this formatting step. This is a gap we must bridge by correlating the database audit with the AI audit.

5. Interception Point 2 (AI Call - Conceptual Extension):

   • While IDbCommandInterceptor is strictly for database commands, the pattern can be mirrored for AI SDKs. However, for this chapter, we focus on how the database audit supports the AI audit.
   • The application sends the prompt to the AI model.
   • The AI returns a response.

6. Persistence:

   • The captured data (timestamps, SQL, parameters, user ID) is written to an AuditLog table using a separate, dedicated DbContext (to avoid recursion).

Visualizing the Flow

The following diagram illustrates the flow of data and how the interceptor captures the critical "Context Retrieval" phase that fuels the AI.

Hold "Ctrl" to enable pan & zoom

In this diagram, a dedicated `DbContext` isolates and writes captured data—timestamps, SQL, parameters, and user IDs—into an `AuditLog` table to ensure persistence without recursion.

The "Why": Compliance and Debugging

Regulatory Compliance (GDPR, HIPAA): In regulated industries, you cannot simply log "The AI answered the patient's question." You must prove that the data used to generate that answer was accessed legitimately.

• The Interceptor's Role: By logging the exact SQL query and parameters, you prove which patient records were accessed. If the AI generates a response based on Patient A's data, but the audit log shows the query was for Patient B, you have a data leakage or logic error.

Debugging Non-Determinism: AI models are black boxes. If an AI produces an error, debugging is notoriously hard.

• Scenario: An AI claims a product is out of stock when it isn't.
• Debugging without Interceptors: "The AI must be hallucinating."
• Debugging with Interceptors: You check the audit log. You see the SQL query SELECT * FROM Inventory WHERE ProductId = 123. You execute this query manually against the database. If it returns "In Stock," but the AI said "Out of Stock," the error is in the prompt construction or the AI model. If the query returns "Out of Stock," the error is in the database data, not the AI.

Theoretical Foundations

In modern C#, we heavily utilize async/await. EF Core provides asynchronous interceptor methods (e.g., ReaderExecutingAsync).

• Why this matters: AI applications are inherently I/O bound. The database call is I/O bound, and the AI API call is I/O bound.
• The Risk: If you implement a synchronous interceptor (ReaderExecuting) in an async context, you introduce thread blocking. This can lead to thread pool starvation in high-throughput AI applications (e.g., a chatbot serving thousands of concurrent users).
• Best Practice: Always implement the ...Async variants of the interceptor methods to ensure the auditing process does not block the execution pipeline.

Correlating Database Context with AI Prompts

The most sophisticated aspect of this theoretical foundation is the correlation mechanism. An interceptor alone only knows about the database. To audit the AI prompt, we need a bridge.

The Correlation ID:

1. Propagation: When a request starts, generate a Guid (CorrelationId).
2. AsyncLocal: Store this ID in an AsyncLocal<string> context. This ensures the ID flows with the execution context across async boundaries (database calls, AI calls).
3. Interceptor Access: The IDbCommandInterceptor methods have access to the EventData object. We can attach the CorrelationId to the audit log entry here.
4. AI Wrapper: When calling the AI service, we wrap the call to also log the prompt using the same CorrelationId.

This creates a "Join" operation in the audit database:

• Table A (DbAudit): CorrelationId, SQL, Timestamp.
• Table B (AiAudit): CorrelationId, Prompt, Response, Timestamp.

By joining these on CorrelationId, you get a complete timeline of the RAG workflow.

Summary of Concepts

• Interception: The act of observing and potentially modifying commands as they pass between the application and the database.
• RAG (Retrieval-Augmented Generation): The pattern of retrieving external data (via EF Core) to inject into an AI prompt.
• Audit Trail: A chronological record of system activities, crucial for security and debugging.
• Correlation: Linking disparate events (database query and AI request) into a single logical transaction.

This theoretical foundation establishes that interceptors are not merely debugging tools; they are governance gateways. In an era where AI systems must be explainable and compliant, the ability to trace an AI's output back to the specific database rows retrieved by EF Core is not just useful—it is essential.

Basic Code Example

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Storage;
using System.Data.Common;
using System.Text.Json;

// ==========================================
// 1. Domain Models
// ==========================================

/// <summary>
/// Represents a user's interaction with an AI model.
/// This is the entity that will be stored in the database for auditing purposes.
/// </summary>
public class AuditLog
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
    public string? UserId { get; set; }
    public string? Prompt { get; set; }
    public string? ContextData { get; set; } // JSON serialized context (e.g., RAG documents)
    public string? ModelName { get; set; }
}

// ==========================================
// 2. The Interceptor
// ==========================================

/// <summary>
/// Intercepts database commands to extract AI-related context and audit them.
/// This implements the <see cref="IDbCommandInterceptor"/> interface.
/// </summary>
public class AiPromptInterceptor : IDbCommandInterceptor
{
    // We use a ThreadLocal to ensure data integrity in async/parallel scenarios.
    // In a real-world web app, you might use IHttpContextAccessor or AsyncLocal.
    private static readonly ThreadLocal<string?> _currentPrompt = new();
    private static readonly ThreadLocal<string?> _currentContext = new();
    private static readonly ThreadLocal<string?> _currentUserId = new();

    /// <summary>
    /// Static method to set the context before executing an AI query.
    /// This is called by your service layer before invoking the AI model.
    /// </summary>
    public static void SetContext(string prompt, string contextData, string userId)
    {
        _currentPrompt.Value = prompt;
        _currentContext.Value = contextData;
        _currentUserId.Value = userId;
    }

    /// <summary>
    /// Static method to clear the context after execution.
    /// </summary>
    public static void ClearContext()
    {
        _currentPrompt.Value = null;
        _currentContext.Value = null;
        _currentUserId.Value = null;
    }

    // We need access to the DbContext to insert the audit log.
    // Since interceptors are registered at the DbContext level, we can inject services here.
    private readonly AuditingDbContext _dbContext;

    public AiPromptInterceptor(AuditingDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    /// <summary>
    /// Intercepts the execution of a command. This is the "After" event.
    /// We use this to capture the audit data and save it.
    /// </summary>
    public InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        // 1. Check if we have context data to audit.
        // In a real scenario, we might check a specific tag or command text pattern.
        if (!string.IsNullOrEmpty(_currentPrompt.Value))
        {
            // 2. Create the audit log entry.
            var auditLog = new AuditLog
            {
                Timestamp = DateTime.UtcNow,
                UserId = _currentUserId.Value,
                Prompt = _currentPrompt.Value,
                ContextData = _currentContext.Value, // This contains the RAG context
                ModelName = "GPT-4" // Hardcoded for this example, or parsed from command
            };

            // 3. Insert into the database.
            // IMPORTANT: We must be careful not to create an infinite loop.
            // If we use _dbContext.AuditLogs.Add(auditLog) and SaveChanges(),
            // the interceptor will trigger again for the INSERT command.
            // We use a raw SQL command to bypass the interceptor for the audit write.

            var connection = _dbContext.Database.GetDbConnection();
            using var transaction = connection.BeginTransaction();
            try
            {
                using var cmd = connection.CreateCommand();
                cmd.Transaction = transaction;
                cmd.CommandText = "INSERT INTO AuditLogs (Timestamp, UserId, Prompt, ContextData, ModelName) VALUES (@p0, @p1, @p2, @p3, @p4)";

                AddParameter(cmd, "@p0", auditLog.Timestamp);
                AddParameter(cmd, "@p1", auditLog.UserId);
                AddParameter(cmd, "@p2", auditLog.Prompt);
                AddParameter(cmd, "@p3", auditLog.ContextData);
                AddParameter(cmd, "@p4", auditLog.ModelName);

                cmd.ExecuteNonQuery();
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                // Log error externally, don't swallow it silently in production
                throw;
            }
            finally
            {
                ClearContext();
            }
        }

        return result; // Pass through the original result
    }

    private void AddParameter(DbCommand cmd, string name, object? value)
    {
        var param = cmd.CreateParameter();
        param.ParameterName = name;
        param.Value = value ?? DBNull.Value;
        cmd.Parameters.Add(param);
    }

    // We must implement other members of the interface, but they simply pass through.
    public void NonQueryExecuted(DbCommand command, CommandExecutedEventData eventData, int result) { }
    public void NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result) { }
    public void ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result) { }
    public void ScalarExecuted(DbCommand command, CommandExecutedEventData eventData, object? result) { }
    public void ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object?> result) { }
    public Task<DbDataReader> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default) => Task.FromResult(result);
    public Task<int> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default) => Task.FromResult(result);
    public Task<object?> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object?> result, CancellationToken cancellationToken = default) => Task.FromResult(result);
    public void CommandFailed(DbCommand command, CommandErrorEventData eventData) { }
    public Task CommandFailedAsync(DbCommand command, CommandErrorEventData eventData, CancellationToken cancellationToken = default) => Task.CompletedTask;
    public void CommandSucceeded(DbCommand command, CommandExecutedEventData eventData) { }
    public Task CommandSucceededAsync(DbCommand command, CommandExecutedEventData eventData, CancellationToken cancellationToken = default) => Task.CompletedTask;
}

// ==========================================
// 3. DbContext and Configuration
// ==========================================

public class AuditingDbContext : DbContext
{
    public DbSet<AuditLog> AuditLogs { get; set; }

    public AuditingDbContext(DbContextOptions<AuditingDbContext> options) : base(options) { }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Registering the interceptor
        optionsBuilder.AddInterceptors(new AiPromptInterceptor(this));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AuditLog>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Timestamp).IsRequired();
            entity.Property(e => e.Prompt).HasMaxLength(2000);
            entity.Property(e => e.ContextData).HasColumnType("nvarchar(max)"); // For JSON
        });
    }
}

// ==========================================
// 4. Usage Example (Simulated)
// ==========================================

public class AiService
{
    private readonly AuditingDbContext _context;

    public AiService(AuditingDbContext context)
    {
        _context = context;
    }

    public async Task<string> GetRagResponseAsync(string userQuestion, string retrievedDocumentsJson)
    {
        // 1. Set the context BEFORE executing the database query that triggers the AI call
        // (In this simulation, the "AI Call" is just a dummy SQL query, but the interceptor captures the context)
        AiPromptInterceptor.SetContext(
            prompt: userQuestion,
            contextData: retrievedDocumentsJson,
            userId: "user_123"
        );

        // 2. Execute a database command. 
        // The interceptor will catch this, audit the context, and then execute the command.
        // In a real app, this might be a call to an AI vector search or an external API.
        // Here, we simulate it by querying a dummy table.
        var result = await _context.Database.ExecuteSqlRawAsync("SELECT 1 AS FakeAiResponse");

        return "AI Response processed and logged.";
    }
}

// ==========================================
// 5. Main Execution
// ==========================================

class Program
{
    static async Task Main(string[] args)
    {
        // Setup In-Memory Database for the example
        var options = new DbContextOptionsBuilder<AuditingDbContext>()
            .UseInMemoryDatabase(databaseName: "AiAuditDb")
            .Options;

        using var context = new AuditingDbContext(options);

        // Ensure database is created
        await context.Database.EnsureCreatedAsync();

        var aiService = new AiService(context);

        // Simulate a RAG workflow
        string ragContext = JsonSerializer.Serialize(new 
        { 
            Source = "Financial Report 2023", 
            Snippet = "Revenue increased by 15%..." 
        });

        await aiService.GetRagResponseAsync("What was the revenue growth?", ragContext);

        // Verify the audit log was created
        var logs = await context.AuditLogs.ToListAsync();
        Console.WriteLine($"Audit Logs Count: {logs.Count}");
        if (logs.Count > 0)
        {
            var log = logs.First();
            Console.WriteLine($"User: {log.UserId}");
            Console.WriteLine($"Prompt: {log.Prompt}");
            Console.WriteLine($"Context: {log.ContextData}");
        }
    }
}

Detailed Line-by-Line Explanation

1. Domain Models (AuditLog)

public class AuditLog
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
    public string? UserId { get; set; }
    public string? Prompt { get; set; }
    public string? ContextData { get; set; } // JSON serialized context
    public string? ModelName { get; set; }
}

• Line 1-7: We define a standard EF Core entity. This class represents the schema of the table where we will store the audit trail.
• ContextData: This is crucial for RAG (Retrieval-Augmented Generation). It stores the documents retrieved from the vector database that were fed into the AI model. Storing this as JSON allows flexibility in the structure of the retrieved documents.

2. The Interceptor (AiPromptInterceptor)

public class AiPromptInterceptor : IDbCommandInterceptor

• Line 1: We implement IDbCommandInterceptor. This interface allows us to hook into the lifecycle of database commands (Select, Insert, Update, Delete) managed by EF Core.

Static Context Management:

private static readonly ThreadLocal<string?> _currentPrompt = new();
public static void SetContext(string prompt, string contextData, string userId)
{
    _currentPrompt.Value = prompt;
    // ...
}

• Why ThreadLocal? In a web server, multiple requests are handled concurrently. We need to ensure that the prompt from User A isn't accidentally logged with User B's database query. ThreadLocal ensures data isolation per execution thread.
• SetContext: This static method acts as the bridge between your application logic (the AI service) and the infrastructure (the interceptor). You must call this before executing the database command that triggers the AI logic.

Constructor Injection:

private readonly AuditingDbContext _dbContext;
public AiPromptInterceptor(AuditingDbContext dbContext)
{
    _dbContext = dbContext;
}

• Line 1-4: Interceptors are created by the DI container. We inject the AuditingDbContext so we have access to the database connection to write our logs.

The Interception Logic (ReaderExecuting):

public InterceptionResult<DbDataReader> ReaderExecuting(
    DbCommand command,
    CommandEventData eventData,
    InterceptionResult<DbDataReader> result)
{
    if (!string.IsNullOrEmpty(_currentPrompt.Value))
    {
        // ... Create AuditLog ...
    }
    return result;
}

• Line 1: This method runs before EF Core executes a query that returns a result set.
• Line 4: We check if SetContext was called. If not, we do nothing (pass-through).
• Line 6-15: We populate the AuditLog entity using the data stored in the ThreadLocal variables.

The Infinite Loop Problem (Critical Concept):

using var transaction = connection.BeginTransaction();
// ... using raw ADO.NET to insert ...
transaction.Commit();

• The Trap: If we used _dbContext.AuditLogs.Add(auditLog) and _dbContext.SaveChanges(), EF Core would generate an INSERT SQL command. This command would trigger the ReaderExecuting (or NonQueryExecuting) interceptor again. Since we have context data in the static variable, it would try to insert another audit log, leading to a stack overflow or infinite loop.
• The Solution: We bypass EF Core's change tracking for the audit log insertion. We use raw ADO.NET (DbCommand) directly on the underlying connection. This allows us to write to the database without triggering the interceptor again.

3. DbContext Configuration

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(new AiPromptInterceptor(this));
}

• Line 3: This registers our interceptor with the DbContext. Every time this context executes a command, our interceptor will be invoked.

4. Usage (AiService)

AiPromptInterceptor.SetContext(
    prompt: userQuestion,
    contextData: retrievedDocumentsJson,
    userId: "user_123"
);

• Line 1-4: Before the business logic runs, we "arm" the interceptor with the specific context of this operation. This decouples the auditing concern from the business logic.

Common Pitfalls

1. Recursive Interception (The Infinite Loop):

   • Mistake: Calling SaveChanges() or executing a query inside an interceptor using the same DbContext instance that registered the interceptor.
   • Consequence: The interceptor triggers itself repeatedly until the stack overflows.
   • Fix: Always use raw ADO.NET (DbCommand) or a separate, dedicated logging DbContext instance (scoped differently) to write audit logs from within an interceptor.

2. Async/Await Context Loss:

   • Mistake: Relying on AsyncLocal or ThreadLocal without understanding execution flow. If you use Task.Run or background threads, the thread ID might change, and the context might be lost.
   • Consequence: Audit logs are created with missing data (null prompts, wrong user IDs).
   • Fix: Ensure SetContext is called on the same logical execution path as the database command. In ASP.NET Core, AsyncLocal is often safer than ThreadLocal for async flows, but ThreadLocal works for synchronous or strictly sequential async operations on the same thread.

3. Transaction Deadlocks:

   • Mistake: Starting a transaction inside the interceptor while the outer operation is also transactional.
   • Consequence: If the outer transaction locks a resource and the inner interceptor transaction tries to lock the same resource (or vice versa), it can cause deadlocks.
   • Fix: In the example, we explicitly start a new transaction using BeginTransaction() on the connection. We commit it immediately. This is generally safe for logging. However, if the outer transaction rolls back, the audit log (which was committed) will remain. This is usually desired (audit logs should exist even if the operation failed), but it is a behavior to be aware of.

4. Memory Leaks with ThreadLocal:

   • Mistake: Storing large objects (like the full JSON context) in ThreadLocal and not clearing them.
   • Consequence: In long-running threads (like background services), these values remain in memory indefinitely.
   • Fix: Always call ClearContext() (or set values to null) in a finally block or immediately after the operation completes.

Visualizing the Flow

Hold "Ctrl" to enable pan & zoom

This diagram illustrates the lifecycle of a background thread where a context value persists indefinitely if not explicitly cleared, contrasting this memory retention with the recommended pattern of using a `finally` block to ensure the context is reset immediately after the operation completes.

The chapter continues with advanced code, exercises and solutions with analysis, you can find them on the ebook on Leanpub.com or Amazon

---

Loading knowledge check...

---

---

Code License: All code examples are released under the MIT License. Github repo.

Content Copyright: Copyright © 2026 Edgar Milvus | Privacy & Cookie Policy. All rights reserved.

All textual explanations, original diagrams, and illustrations are the intellectual property of the author. To support the maintenance of this site via AdSense, please read this content exclusively online. Copying, redistribution, or reproduction is strictly prohibited.