123456789101112131415

public interface IAuditableEntity
{
  DateTimeOffset CreatedAt { get; set; }
  string? CreatedBy { get; set; }
  DateTimeOffset UpdatedAt { get; set; }
  string? UpdatedBy { get; set; }
}

public interface ISoftDelete
{
  bool IsDeleted { get; set; }
  DateTimeOffset? DeletedAt { get; set; }
  string? DeletedBy { get; set; }
}

123456789101112131415

public sealed class Invoice : IAuditableEntity, ISoftDelete
{
  Guid Id { get; set; }
  Guid TenantId { get; set; }
  decimal Amount { get; set; }
  string Currency { get; set; } = "EUR";
  DateTimeOffset CreatedAt { get; set; }
  string? CreatedBy { get; set; }
  DateTimeOffset UpdatedAt { get; set; }
  string? UpdatedBy { get; set; }
  bool IsDeleted { get; set; }
  DateTimeOffset? DeletedAt { get; set; }
  string? DeletedBy { get; set; }
}

123456789101112131415161718192021222324252627

public interface ICurrentActor
{
    string? UserId { get; }
    Guid? TenantId { get; }
    string CorrelationId { get; }
}

public sealed class CurrentActor : ICurrentActor
{
    public CurrentActor(IHttpContextAccessor accessor)
    {
        var http = accessor.HttpContext;

        UserId = http?.User?.Identity?.Name;
        TenantId = Guid.TryParse(http?.Request.Headers["X-Tenant-Id"], out var tid) ? tid : null;

        CorrelationId =
            http?.TraceIdentifier
            ?? Activity.Current?.Id
            ?? Guid.NewGuid().ToString("N");
    }

    public string? UserId { get; }
    public Guid? TenantId { get; }
    public string CorrelationId { get; }
}

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

public sealed class AuditAndSoftDeleteInterceptor : SaveChangesInterceptor
{
    private readonly ICurrentActor _actor;

    public AuditAndSoftDeleteInterceptor(ICurrentActor actor)
        => _actor = actor;

    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData,
        InterceptionResult<int> result)
    {
        ApplyRules(eventData.Context);
        return base.SavingChanges(eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        ApplyRules(eventData.Context);
        return base.SavingChangesAsync(eventData, result, cancellationToken);
    }

    private void ApplyRules(DbContext? context)
    {
        if (context is null) return;

        var now = DateTimeOffset.UtcNow;
        var user = _actor.UserId ?? "system";

        foreach (var entry in context.ChangeTracker.Entries())
        {
            if (entry.Entity is IAuditableEntity auditable)
            {
                if (entry.State == EntityState.Added)
                {
                    auditable.CreatedAt = now;
                    auditable.CreatedBy = user;
                }

                if (entry.State is EntityState.Added or EntityState.Modified)
                {
                    auditable.UpdatedAt = now;
                    auditable.UpdatedBy = user;
                }
            }

            if (entry.Entity is ISoftDelete softDelete && entry.State == EntityState.Deleted)
            {
                // Convert hard delete into soft delete
                entry.State = EntityState.Modified;

                softDelete.IsDeleted = true;
                softDelete.DeletedAt = now;
                softDelete.DeletedBy = user;

                // Also counts as an update
                if (entry.Entity is IAuditableEntity a)
                {
                    a.UpdatedAt = now;
                    a.UpdatedBy = user;
                }
            }
        }
    }
}

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112

using System.Data.Common;
using System.Diagnostics;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;

public sealed class ObservabilityCommandInterceptor : DbCommandInterceptor
{
    private readonly ICurrentActor _actor;
    private readonly ILogger<ObservabilityCommandInterceptor> _logger;

    // Tune for your system
    private static readonly TimeSpan SlowQueryThreshold = TimeSpan.FromMilliseconds(250);

    public ObservabilityCommandInterceptor(
        ICurrentActor actor,
        ILogger<ObservabilityCommandInterceptor> logger)
    {
        _actor = actor;
        _logger = logger;
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        Tag(command);
        eventData.Context?.Items.TryAdd(eventData.CommandId, Stopwatch.StartNew());
        return base.ReaderExecuting(command, eventData, result);
    }

    public override void ReaderExecuted(
        DbCommand command,
        CommandExecutedEventData eventData,
        DbDataReader result)
    {
        LogIfSlow(eventData, command);
        base.ReaderExecuted(command, eventData, result);
    }

    public override InterceptionResult<int> NonQueryExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<int> result)
    {
        Tag(command);
        eventData.Context?.Items.TryAdd(eventData.CommandId, Stopwatch.StartNew());
        return base.NonQueryExecuting(command, eventData, result);
    }

    public override void NonQueryExecuted(
        DbCommand command,
        CommandExecutedEventData eventData,
        int result)
    {
        LogIfSlow(eventData, command);
        base.NonQueryExecuted(command, eventData, result);
    }

    public override InterceptionResult<object> ScalarExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<object> result)
    {
        Tag(command);
        eventData.Context?.Items.TryAdd(eventData.CommandId, Stopwatch.StartNew());
        return base.ScalarExecuting(command, eventData, result);
    }

    public override void ScalarExecuted(
        DbCommand command,
        CommandExecutedEventData eventData,
        object result)
    {
        LogIfSlow(eventData, command);
        base.ScalarExecuted(command, eventData, result);
    }

    private void Tag(DbCommand command)
    {
        var tenant = _actor.TenantId?.ToString() ?? "none";
        var corr = _actor.CorrelationId;

        // SQL comments are ignored by most engines but show up in logs/traces
        // Keep it short to avoid huge command texts
        command.CommandText = $"/* tenant:{tenant} corr:{corr} */\n{command.CommandText}";
    }

    private void LogIfSlow(CommandExecutedEventData eventData, DbCommand command)
    {
        if (eventData.Context is null) return;

        if (eventData.Context.Items.TryGetValue(eventData.CommandId, out var swObj) &&
            swObj is Stopwatch sw)
        {
            sw.Stop();

            if (sw.Elapsed >= SlowQueryThreshold)
            {
                _logger.LogWarning(
                    "Slow SQL ({ElapsedMs} ms) tenant:{TenantId} corr:{CorrelationId}\n{CommandText}",
                    sw.Elapsed.TotalMilliseconds,
                    _actor.TenantId,
                    _actor.CorrelationId,
                    command.CommandText);
            }

            eventData.Context.Items.Remove(eventData.CommandId);
        }
    }
}

1234567891011121314151617181920212223242526

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ICurrentActor, CurrentActor>();

// Interceptors: usually safe as singleton if they are stateless.
// Ours depends on scoped ICurrentActor, so we register them as scoped.
builder.Services.AddScoped<AuditAndSoftDeleteInterceptor>();
builder.Services.AddScoped<ObservabilityCommandInterceptor>();

builder.Services.AddDbContext<BillingDbContext>((sp, options) =>
{
    options.UseNpgsql(builder.Configuration.GetConnectionString("db"));

    // Add interceptors from DI
    options.AddInterceptors(
        sp.GetRequiredService<AuditAndSoftDeleteInterceptor>(),
        sp.GetRequiredService<ObservabilityCommandInterceptor>());
});

var app = builder.Build();
app.MapGet("/", () => "OK");
app.Run();