Published on

Part 2 — Full-Stack .NET Observability: a C# API with Postgres or SQL Server

Authors
  • avatar
    Name
    Konrad Bartecki
    Twitter

Part 2 — Full-Stack .NET Observability: a C# API with Postgres or SQL Server

Follow one user click all the way from the browser, through your ASP.NET Core API, down to the exact SQL query -- as a single trace in SigNoz. Plus database metrics, request metrics, and logs that link back to the trace that produced them.

This is Part 2 of 4 in a series on observing a .NET system with OpenTelemetry and SigNoz (free, self-hosted); see the series index. Part 1 covered the Blazor frontend; here we follow the request into the backend API and the database. Every file is included at the end -- there's no repo to clone. (The shared bootstrap, Collector config, and Compose file are in Part 1's appendix; this post adds the backend.)

What you'll learn

  • How to get one distributed trace spanning frontend → API → database (no propagation code)
  • How to add database spans for Postgres and SQL Server, and app-level DB metrics
  • How to get request and runtime metrics for free, then add business metrics
  • How to make a business failure (out of stock) show up as a red span without throwing an exception
  • How logs link to traces automatically

The setup in one minute. Every service calls one helper, AddObservability(...), which sends traces, metrics, and logs over OTLP (OpenTelemetry's wire protocol) to an OpenTelemetry Collector. The Collector forwards to SigNoz. The apps never mention SigNoz, so you can swap backends in collector config alone.

The goal: one trace across the whole stack

The single most useful thing in APM is the distributed trace -- one request drawn as a tree of timed steps across services. Part 1 glimpsed this trace from the Blazor side; here we follow it the whole way, down to the SQL the database actually ran. When the Blazor app calls GET /api/products, we want SigNoz to show:

GET /products            (blazor-frontend)   ← the user's click
└─ frontend.load-products (blazor-frontend)   ← our custom span
   └─ GET                 (blazor-frontend)   ← outbound HttpClient call
      └─ GET /api/products (backend-api)      ← the API receives it
         └─ products.list  (backend-api)      ← our custom span
            └─ postgresql   (backend-api)      ← the actual DB query

That whole tree is one trace. Here it is in SigNoz, captured from the running app:

The same trace open in SigNoz, drawn as a waterfall. One request spans two services, with the postgresql database call nested at the bottom.

It works because the instrumented HttpClient adds a traceparent header (the standard W3C trace-context header) to every outbound call, and ASP.NET Core instrumentation on the receiving side reads it and continues the same trace. Both sides have this on by default through AddObservability, so there is no propagation code to write.

What we're building

ThingWhere
Backend APIhttp://localhost:5081 (/health, /api/products)
Postgreslocalhost:15440
Collector (OTLP)localhost:5317 (gRPC) / 5318 (HTTP)
SigNoz UIhttp://localhost:8080

Step 1 -- Get it running

# Start SigNoz (one-time, self-hosted) -- full install in Part 1
git clone -b main https://github.com/SigNoz/signoz.git
cd signoz/deploy/docker && docker compose up -d        # UI at http://localhost:8080
cd -

# Start the apps + collector (docker-compose.yml + collector config are in Part 1's appendix;
# the backend code is in this post's appendix)
docker compose up -d --build

# Make some traffic
curl http://localhost:5081/api/products
curl -X POST http://localhost:5081/api/products/10000000-0000-0000-0000-000000000001/purchase \
     -H 'content-type: application/json' -d '{"quantity":1}'
curl http://localhost:5081/api/diagnostics/error      # a deliberate 500, to see an error span

That traffic is enough to populate the Services page. Open http://localhost:8080 → Services. From your traces alone, SigNoz derives RED metrics -- Rate, Errors, and Duration -- with zero extra wiring:

SigNoz's Services page for backend-api. The non-zero error rate is real: the deliberate /api/diagnostics/error 500s and the out-of-stock 409s we trigger below.

Step 2 -- The shared setup, and what the API adds

The backend calls the same AddObservability (full source in Part 1) and adds database instrumentation through option hooks -- callbacks the shared bootstrap exposes, so the shared library never references a database driver:

builder.AddObservability("backend-api", options =>
{
    options.ActivitySources.Add(ApiTelemetry.ActivitySourceName);
    options.Meters.Add(ApiTelemetry.MeterName);

    options.ConfigureTracerProvider = tracing =>
    {
        tracing.AddNpgsql();                    // Postgres command spans
        tracing.AddSqlClientInstrumentation(o => o.SetDbStatementForText = true);  // SQL Server spans + SQL text
    };

    options.ConfigureMeterProvider = metrics =>
    {
        metrics.AddNpgsqlInstrumentation();     // Npgsql connection-pool / command metrics
    };
});

Like the frontend in Part 1, ApiTelemetry is a singleton that owns the backend's custom spans and metrics; the two Add lines register its ActivitySource and Meter with OpenTelemetry. Here is its public surface (bodies in the appendix) -- each method bumps a counter or records a histogram value:

// ApiTelemetry -- the backend's custom telemetry (singleton).
public Activity? StartActivity(string name, ActivityKind kind = ActivityKind.Internal);  // a custom span
public void RecordProductOperation(string operation, string provider, bool success);     // -> backend.product.operations
public void RecordDbQuery(string queryName, string provider, TimeSpan duration,
                          bool success, int? resultCount = null);                         // -> backend.db.query.duration / .failures
public void RecordPurchase(string provider, bool success, decimal amount);               // -> backend.product.purchases / .revenue

Why the hooks? The Blazor frontend and the worker have no database. If the shared library referenced Npgsql.OpenTelemetry, every service would drag in that dependency. The hooks let the shared bootstrap stay driver-neutral while each service composes exactly what it needs. The two DB packages live only in the backend project.

Step 3 -- Postgres or SQL Server, same telemetry

Picking the database is a one-line config switch -- the only provider-specific code in the app:

if (provider == DatabaseProviders.Postgres)
    options.UseNpgsql(connectionString, npgsql => npgsql.EnableRetryOnFailure());
else
    options.UseSqlServer(connectionString, sql => sql.EnableRetryOnFailure());

The instrumentation doesn't change, so either database gives you the same db spans -- plus the same backend.db.query.* metrics and custom query spans, tagged with db.provider so you can compare the two in SigNoz.

Step 4 -- Database spans and DB metrics

With AddNpgsql() / AddSqlClientInstrumentation() on, every EF Core query becomes a child db span automatically -- executed through the instrumented Npgsql/SqlClient driver (there's no EF-Core-specific OTel package in play). This query:

var products = await db.Products.AsNoTracking().OrderBy(p => p.CreatedAt)
    .Select(p => p.ToResponse()).ToListAsync(ct);

shows up in SigNoz as the postgresql span you saw in the trace. Open that span and its db.query.text attribute (older packages call it db.statement) holds the exact SQL the driver ran -- the LINQ above, compiled by EF Core to:

SELECT p."Id", p."CreatedAt", p."Description", p."Name", p."Price", p."Quantity", p."UpdatedAt"
FROM products AS p
ORDER BY p."CreatedAt"

Npgsql attaches that statement to every command span automatically -- here's the one from our list query:

A single postgresql span opened in SigNoz, its attributes filtered to query.text. The captured db.query.text is the exact SELECT … FROM products AS p ORDER BY p."CreatedAt" the driver ran; the trace tree on the left is the request it belongs to.

SQL Server needs one opt-in for the same thing -- SetDbStatementForText = true, off by default, which the setup above sets. The list query takes no parameters, so the statement is complete; a query with user input shows the parameter as a placeholder rather than the value, which is what you want in a telemetry store.

Because the span is the driver's raw SQL round-trip, not the LINQ shape, the API also records a named products.list span and its own DB metrics (next block).

Good to know: the exact attribute keys depend on which OpenTelemetry semantic-convention version your packages target. Older packages emit db.system and db.statement; newer ones emit db.system.name and db.query.text.

On top of the driver spans, the API records a DB metric the driver cannot give you: the duration of a named business query, rather than a raw SQL round-trip:

const string queryName = "products.list";
using var activity = telemetry.StartActivity(queryName);
var sw = Stopwatch.StartNew();
try
{
    // ... EF query ...
    telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: true, resultCount: products.Count);
}
catch
{
    telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: false);  // -> backend.db.query.failures
    throw;
}

Now you can chart backend.db.query.duration by query.name and see which operation is slow, independent of the raw SQL.

Step 5 -- Free framework metrics, plus your business metrics

Because AddObservability turns on ASP.NET Core and runtime instrumentation by default, you get these with zero extra code:

  • http.server.request.duration and http.server.active_requests (meter Microsoft.AspNetCore.Hosting)
  • Kestrel connection metrics, including kestrel.rejected_connections
  • System.Runtime -- GC, thread pool, exceptions, process CPU/memory

On top, the API records three business metrics in ApiTelemetry: backend.product.operations, backend.product.purchases, and backend.product.revenue.

The purchase endpoint is the one to study, because it shows how to make a business failure visible without throwing an exception:

if (product.Quantity < request.Quantity)
{
    telemetry.RecordPurchase(provider, success: false, amount: 0m);
    activity?.SetStatus(ActivityStatusCode.Error, "Insufficient stock");  // span goes red
    return Results.Problem(title: "Insufficient stock", statusCode: StatusCodes.Status409Conflict);
}

The span is marked Error even though the 409 is the correct response for a normal business failure -- so you can still find it by filtering the Traces list on error=true.

Unexpected failures are different. /api/diagnostics/error throws an exception. The shared bootstrap sets RecordException = true, so the exception is attached to the span with its full stack trace.

Here is exactly that, as a distributed trace -- a "Buy" click that hit an out-of-stock product:

A single trace of an out-of-stock "Buy" click, in SigNoz. The internal frontend.purchase and products.purchase spans are red (SetStatus(Error, "Insufficient stock")), the HTTP POST spans carry the 409, and the sibling read path stayed green -- all without throwing an exception.

Step 6 -- Logs that link to traces

Logging flows through the same pipeline Part 1 set up, so you get the same automatic correlation here on the backend: a log written during a request carries that request's trace_id and span_id, and structured placeholders stay queryable:

logger.LogInformation("Listed {ProductCount} products from {DatabaseProvider}", products.Count, provider);

In SigNoz, every log carries the trace_id/span_id of the request that produced it, so you can pivot from a trace to its logs and back:

SigNoz's Logs Explorer: logs from across the stack in one place -- backend-api, the worker, and more -- with a severity quick-filter on the left.

Step 7 -- Where the time goes, end to end

A single trace is already a latency breakdown. Read the waterfall top to bottom and each bar is the time spent at that layer:

  • GET /products on blazor-frontend -- the whole round trip the page is waiting on
  • GET /api/products on backend-api -- time inside the API
  • postgresql -- time the query spent in the database

Subtract a child from its parent to get the time spent in a layer rather than below it: the API span minus the postgresql span is the API's own work; the frontend span minus the API span is the network hop plus the frontend's own work. The distributed trace at the top of this post is exactly this.

That is one request. For the trend across all of them, those same durations are metrics you can chart side by side:

  • API: http.server.request.duration, p95 by http.route
  • Database: backend.db.query.duration, p95 by query.name
  • Frontend: the circuit and render metrics from Part 1

One honest gap: the trace starts when the request reaches your frontend. The click-to-first-paint time inside the browser needs browser instrumentation (RUM), which this demo doesn't set up.

Step 8 -- Alert when a query gets slow

A dashboard helps when you are looking at it; an alert tells you when you are not. backend.db.query.duration is a good one to guard. To get paged when a query crosses 500ms:

  1. Alerts → New Alert → Metric-based.
  2. Metric backend.db.query.duration, space aggregation p95, grouped by query.name so each query is judged on its own.
  3. Condition: is above 500 (the metric is in milliseconds), evaluated over the last 5 minutes.
  4. Set a severity and a notification channel, and save.

The alert's detail view in SigNoz, guarding DB query p95 against a 500ms threshold (the dashed line). products.search and products.stats -- full scans over a large table -- spike well past it, while the indexed products.get and products.list stay near zero. Grouping by query.name lets one slow query breach on its own.

The full builder walkthrough is in Part 1's Step 7; only the metric and threshold change.

See it in SigNoz

  1. Services / APM -- backend-api with rate, errors, and latency derived from spans.

  2. Traces -- open a GET /api/products trace to see the full frontend → API → db tree. Filter error=true to isolate the 409s and 500s. (To compare Postgres vs SQL Server, group the backend.db.query.* metrics by db.provider -- that tag is on the custom query spans and metrics, not the raw driver db spans.)

    SigNoz's Traces Explorer, the searchable list of every span, with quick-filters for service, status code, and duration. Here it mixes backend-api database and custom spans with worker-jobs runs.

  3. Logs -- filter service.name = backend-api, severity WARN/ERROR, to surface the "Purchase rejected" warnings and diagnostics errors; pivot to their traces.

  4. Dashboard -- build panels for http.server.request.duration p95 by route, backend.product.revenue by db.provider, and backend.db.query.duration p95 by query.name. The business dashboard in Part 1 already charts backend.product.revenue, backend.product.purchases, and backend.db.query.duration p95 next to the frontend metrics, with a step-by-step on building each panel.

Bonus: the worker joins the same trace

A background worker reuses the identical AddObservability -- with its own WorkerTelemetry class (the worker's equivalent of ApiTelemetry), and ASP.NET Core instrumentation turned off since it isn't a web server:

builder.AddObservability("worker-jobs", options =>
{
    options.InstrumentAspNetCore = false;            // not a web server
    options.ActivitySources.Add(WorkerTelemetry.ActivitySourceName);
    options.Meters.Add(WorkerTelemetry.MeterName);
});

Because its HttpClient is still instrumented, when the worker calls /api/products/stats you get a second trace tree -- worker-jobs → backend-api → db -- exactly mirroring the browser path. (Full worker code is in Part 3.)

Going to production: sampling and scrubbing

Two knobs turn this from a demo into something you can leave running.

Sampling. The demo records 100% of traces, which is fine locally and expensive at scale. Two ways to trim it:

  • Head sampling, in the app. You decide up front, before the trace runs. parentbased means a whole trace is kept or dropped together, so you never get half a distributed trace. Set OTEL_TRACES_SAMPLER=parentbased_traceidratio with OTEL_TRACES_SAMPLER_ARG=0.1 to keep 10%.
  • Tail sampling, in the Collector. The tail_sampling processor decides after seeing the whole trace, so you can keep every error and slow request and sample only the boring successes -- something head sampling cannot do, at the cost of some Collector memory.

Scrubbing. Database command logs and span attributes can carry things you do not want in a telemetry store: a search term, a query parameter, an auth header. Scrub at the Collector so it happens once, for every service, regardless of language:

processors:
  attributes/scrub:
    actions:
      - key: app.search.term # a user-entered value on our search span
        action: hash # one-way hash; keep cardinality, lose the value
      - key: db.statement
        action: update
        value: '[redacted]'
# ...then add attributes/scrub to the traces and logs pipelines

One processor covers every service at once -- no chasing redaction through each codebase.

Development-only: the appendix turns on EF Core's EnableSensitiveDataLogging(). That is fine for the local demo, but it should never ship to production. The Collector scrub above is its production counterpart.

Cheat sheet

You want to know…Look at
Full request path & where time goesthe distributed trace (Traces explorer)
Request rate / errors / latencyServices/APM (RED), http.server.request.duration
Which DB query is slowbackend.db.query.duration grouped by query.name
Failed queriesbackend.db.query.failures
Business outcomesbackend.product.purchases, backend.product.revenue
Runtime healthSystem.Runtime (dotnet.gc.*, thread pool)

Wrapping up

The same AddObservability() from Part 1 carried the trace from the browser into the API and down to the database, with zero per-query code thanks to the Npgsql and SqlClient instrumentation. You added named DB metrics, made a business failure show up red without throwing, and saw logs pivot straight to the trace that produced them -- across both Postgres and SQL Server. Sampling and Collector-side scrubbing make it production-ready.

Next, Part 3 takes the same Collector and brings in everything that lives outside your solution -- a legacy job that only writes .txt, a Python ETL, and a PowerShell task -- so the whole system lands in one self-hosted SigNoz. The full series is on the index page.

The complete code

The backend's files. The shared AddObservability bootstrap, the OpenTelemetry Collector config, the docker-compose.yml, and the SigNoz install are all in Part 1's appendix -- reuse them as-is. The backend is a standard Microsoft.NET.Sdk.Web app targeting net10.0.

NuGet packages (backend)

In addition to a <ProjectReference> to Shared.Telemetry (which brings the core OpenTelemetry packages), the backend references:

<PackageReference Include="Microsoft.AspNetCore.OpenApi"               Version="10.0.2" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL"      Version="10.0.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer"    Version="10.0.2" />
<PackageReference Include="Npgsql.OpenTelemetry"                       Version="10.0.3" />   <!-- AddNpgsql / AddNpgsqlInstrumentation -->
<PackageReference Include="OpenTelemetry.Instrumentation.SqlClient"    Version="1.15.2" />   <!-- AddSqlClientInstrumentation -->

Add the worker-jobs and backend-api services to the docker-compose.yml from Part 1 (Part 1 already shows backend-api; the worker-jobs service is in Part 3).

src/Backend.Api/Program.cs

using Backend.Api.Configuration;
using Backend.Api.Contracts;
using Backend.Api.Data;
using Backend.Api.Endpoints;
using Backend.Api.Telemetry;
using Microsoft.Extensions.Options;
using Npgsql;                 // TracerProviderBuilder.AddNpgsql / MeterProviderBuilder.AddNpgsqlInstrumentation
using OpenTelemetry.Trace;    // TracerProviderBuilder.AddSqlClientInstrumentation
using Shared.Telemetry;       // AddObservability

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddOpenApi();
builder.Services.AddProblemDetails();
builder.Services.AddAppDatabase(builder.Configuration, builder.Environment);
builder.Services.AddSingleton<ApiTelemetry>();

// One shared OpenTelemetry bootstrap. The API adds its own ActivitySource/Meter plus
// database instrumentation (Npgsql + SqlClient) through the option hooks, so the shared
// library never has to reference a specific database driver.
builder.AddObservability("backend-api", options =>
{
    options.ActivitySources.Add(ApiTelemetry.ActivitySourceName);
    options.Meters.Add(ApiTelemetry.MeterName);

    options.ConfigureTracerProvider = tracing =>
    {
        tracing.AddNpgsql();                    // Postgres command spans
        tracing.AddSqlClientInstrumentation(o => o.SetDbStatementForText = true);  // SQL Server spans + SQL text
    };

    options.ConfigureMeterProvider = metrics =>
    {
        metrics.AddNpgsqlInstrumentation();     // Npgsql connection-pool / command metrics
    };
});

var app = builder.Build();

app.UseExceptionHandler();

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
}

await app.InitializeDatabaseAsync();

app.MapGet("/health", async (
        AppDbContext db, IOptions<DatabaseOptions> dbOptions, CancellationToken ct) =>
    {
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var canConnect = await db.Database.CanConnectAsync(ct);

        return canConnect
            ? Results.Ok(new HealthResponse("ok", provider, DateTimeOffset.UtcNow))
            : Results.Problem(
                title: "Database is unavailable",
                detail: $"The configured {provider} database did not accept a connection.",
                statusCode: StatusCodes.Status503ServiceUnavailable);
    })
    .WithName("Health").WithTags("Health");

app.MapProductEndpoints();
app.MapDiagnosticsEndpoints();

app.Run();

src/Backend.Api/Telemetry/ApiTelemetry.cs

using System.Diagnostics;
using System.Diagnostics.Metrics;

namespace Backend.Api.Telemetry;

/// <summary>Custom traces and metrics for the backend. Registered as a singleton. Its
/// ActivitySource and Meter names are added to the OpenTelemetry pipeline in Program.cs.</summary>
public sealed class ApiTelemetry : IDisposable
{
    public const string ActivitySourceName = "Backend.Api";
    public const string MeterName = "Backend.Api";

    public static readonly ActivitySource ActivitySource = new(ActivitySourceName);

    private readonly Meter _meter = new(MeterName, "1.0.0");
    private readonly Counter<long> _productOperations;
    private readonly Histogram<double> _dbQueryDuration;
    private readonly Counter<long> _dbQueryFailures;
    private readonly Counter<long> _purchases;
    private readonly Counter<double> _revenue;

    public ApiTelemetry()
    {
        _productOperations = _meter.CreateCounter<long>("backend.product.operations", unit: "{operation}",
            description: "Number of product CRUD operations, tagged by operation/provider/success.");
        _dbQueryDuration = _meter.CreateHistogram<double>("backend.db.query.duration", unit: "ms",
            description: "Application-level duration of named database queries.");
        _dbQueryFailures = _meter.CreateCounter<long>("backend.db.query.failures", unit: "{failure}",
            description: "Number of failed database queries.");
        _purchases = _meter.CreateCounter<long>("backend.product.purchases", unit: "{purchase}",
            description: "Number of product purchase operations.");
        _revenue = _meter.CreateCounter<double>("backend.product.revenue", unit: "{currency}",
            description: "Cumulative revenue from successful purchases.");
    }

    public Activity? StartActivity(string name, ActivityKind kind = ActivityKind.Internal)
        => ActivitySource.StartActivity(name, kind);

    public void RecordProductOperation(string operation, string provider, bool success)
        => _productOperations.Add(1, new TagList
        {
            { "operation", operation }, { "db.provider", provider }, { "success", success },
        });

    public void RecordDbQuery(string queryName, string provider, TimeSpan duration, bool success, int? resultCount = null)
    {
        var tags = new TagList { { "query.name", queryName }, { "db.provider", provider }, { "success", success } };
        if (resultCount is not null) tags.Add("result.count", resultCount.Value);

        _dbQueryDuration.Record(duration.TotalMilliseconds, tags);
        if (!success) _dbQueryFailures.Add(1, tags);
    }

    public void RecordPurchase(string provider, bool success, decimal amount)
    {
        _purchases.Add(1, new TagList { { "db.provider", provider }, { "success", success } });
        if (success) _revenue.Add((double)amount, new TagList { { "db.provider", provider } });
    }

    public void Dispose() => _meter.Dispose();
}

src/Backend.Api/Configuration/DatabaseOptions.cs

namespace Backend.Api.Configuration;

public sealed class DatabaseOptions
{
    public const string SectionName = "Database";
    public string Provider { get; set; } = DatabaseProviders.Postgres;
}

public static class DatabaseProviders
{
    public const string Postgres = "Postgres";
    public const string SqlServer = "SqlServer";

    public static string Normalize(string? provider) => provider?.Trim().ToLowerInvariant() switch
    {
        null or "" or "postgres" or "postgresql" or "npgsql" => Postgres,
        "sqlserver" or "sql-server" or "mssql" or "microsoftsqlserver" => SqlServer,
        _ => throw new InvalidOperationException(
            $"Unsupported database provider '{provider}'. Use '{Postgres}' or '{SqlServer}'."),
    };

    public static string ConnectionStringName(string provider) => Normalize(provider) switch
    {
        Postgres => Postgres,
        SqlServer => SqlServer,
        _ => throw new InvalidOperationException($"Unsupported database provider '{provider}'."),
    };
}

src/Backend.Api/Data/DatabaseServiceCollectionExtensions.cs

using Backend.Api.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace Backend.Api.Data;

public static class DatabaseServiceCollectionExtensions
{
    public static IServiceCollection AddAppDatabase(
        this IServiceCollection services, IConfiguration configuration, IHostEnvironment environment)
    {
        services.Configure<DatabaseOptions>(configuration.GetSection(DatabaseOptions.SectionName));

        services.AddDbContext<AppDbContext>((serviceProvider, options) =>
        {
            var databaseOptions = serviceProvider.GetRequiredService<IOptions<DatabaseOptions>>().Value;
            var provider = DatabaseProviders.Normalize(databaseOptions.Provider);
            var connectionStringName = DatabaseProviders.ConnectionStringName(provider);
            var connectionString = configuration.GetConnectionString(connectionStringName);

            if (string.IsNullOrWhiteSpace(connectionString))
                throw new InvalidOperationException(
                    $"Connection string '{connectionStringName}' is required for provider '{provider}'.");

            if (provider == DatabaseProviders.Postgres)
                options.UseNpgsql(connectionString, npgsql => npgsql.EnableRetryOnFailure());
            else
                options.UseSqlServer(connectionString, sqlServer => sqlServer.EnableRetryOnFailure());

            if (environment.IsDevelopment())
            {
                options.EnableDetailedErrors();
                options.EnableSensitiveDataLogging();
            }
        });

        return services;
    }
}

src/Backend.Api/Models/Product.cs

namespace Backend.Api.Models;

public sealed class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Description { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
    public DateTimeOffset UpdatedAt { get; set; }
}

src/Backend.Api/Data/AppDbContext.cs

using Backend.Api.Models;
using Microsoft.EntityFrameworkCore;

namespace Backend.Api.Data;

public sealed class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var product = modelBuilder.Entity<Product>();
        product.ToTable("products");
        product.HasKey(x => x.Id);
        product.Property(x => x.Name).HasMaxLength(120).IsRequired();
        product.Property(x => x.Description).HasMaxLength(500);
        product.Property(x => x.Price).HasPrecision(12, 2);
        product.Property(x => x.CreatedAt).IsRequired();
        product.Property(x => x.UpdatedAt).IsRequired();
        product.HasIndex(x => x.Name);
    }
}

src/Backend.Api/Data/DatabaseInitializer.cs

using Backend.Api.Configuration;
using Backend.Api.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace Backend.Api.Data;

public static class DatabaseInitializer
{
    public static async Task InitializeDatabaseAsync(this WebApplication app)
    {
        using var scope = app.Services.CreateScope();
        var services = scope.ServiceProvider;

        var logger = services.GetRequiredService<ILoggerFactory>().CreateLogger("DatabaseInitializer");
        var provider = DatabaseProviders.Normalize(services.GetRequiredService<IOptions<DatabaseOptions>>().Value.Provider);
        var db = services.GetRequiredService<AppDbContext>();

        logger.LogInformation("Ensuring {DatabaseProvider} database exists", provider);
        await db.Database.EnsureCreatedAsync(app.Lifetime.ApplicationStopping);   // demo only -- use migrations in production

        if (await db.Products.AnyAsync(app.Lifetime.ApplicationStopping)) return;

        db.Products.AddRange(SeedProducts());
        await db.SaveChangesAsync(app.Lifetime.ApplicationStopping);
        logger.LogInformation("Seeded {DatabaseProvider} database with starter products", provider);
    }

    private static Product[] SeedProducts()
    {
        var now = new DateTimeOffset(2026, 1, 1, 12, 0, 0, TimeSpan.Zero);
        return
        [
            new Product { Id = Guid.Parse("10000000-0000-0000-0000-000000000001"), Name = "Mechanical Keyboard", Description = "Hot-swappable, 75% layout", Quantity = 12, Price = 149.99m, CreatedAt = now, UpdatedAt = now },
            new Product { Id = Guid.Parse("10000000-0000-0000-0000-000000000002"), Name = "27\" 4K Monitor", Description = "USB-C, 144 Hz", Quantity = 8, Price = 349.00m, CreatedAt = now.AddMinutes(1), UpdatedAt = now.AddMinutes(1) },
            new Product { Id = Guid.Parse("10000000-0000-0000-0000-000000000003"), Name = "USB-C Dock", Description = "Dual display, 2.5GbE", Quantity = 5, Price = 219.50m, CreatedAt = now.AddMinutes(2), UpdatedAt = now.AddMinutes(2) },
            new Product { Id = Guid.Parse("10000000-0000-0000-0000-000000000004"), Name = "Laptop Stand", Description = "Aluminium, adjustable", Quantity = 15, Price = 79.95m, CreatedAt = now.AddMinutes(3), UpdatedAt = now.AddMinutes(3) },
        ];
    }
}

src/Backend.Api/Contracts/ProductContracts.cs

using Backend.Api.Models;

namespace Backend.Api.Contracts;

public sealed record ProductResponse(Guid Id, string Name, string? Description, int Quantity, decimal Price, DateTimeOffset CreatedAt, DateTimeOffset UpdatedAt);
public sealed record CreateProductRequest(string? Name, string? Description, int Quantity, decimal Price);
public sealed record UpdateProductRequest(string? Name, string? Description, int Quantity, decimal Price);
public sealed record PurchaseRequest(int Quantity);
public sealed record ProductStatsResponse(int TotalCount, int TotalQuantity, decimal InventoryValue, decimal AveragePrice);
public sealed record HealthResponse(string Status, string Provider, DateTimeOffset Timestamp);

public static class ProductMappingExtensions
{
    public static ProductResponse ToResponse(this Product p) =>
        new(p.Id, p.Name, p.Description, p.Quantity, p.Price, p.CreatedAt, p.UpdatedAt);
}

src/Backend.Api/Endpoints/DiagnosticsEndpoints.cs

namespace Backend.Api.Endpoints;

/// <summary>A tunable-latency endpoint (for the Blazor load test) and an always-fails endpoint
/// (to demonstrate error spans).</summary>
public static class DiagnosticsEndpoints
{
    public static IEndpointRouteBuilder MapDiagnosticsEndpoints(this IEndpointRouteBuilder endpoints)
    {
        var group = endpoints.MapGroup("/api/diagnostics").WithTags("Diagnostics");

        group.MapGet("/slow", async (int? ms, ILoggerFactory loggerFactory, CancellationToken ct) =>
        {
            var delay = Math.Clamp(ms ?? 500, 0, 10_000);
            await Task.Delay(delay, ct);
            loggerFactory.CreateLogger("Diagnostics").LogInformation("Slow endpoint returned after {DelayMs}ms", delay);
            return Results.Ok(new { delayedMs = delay, at = DateTimeOffset.UtcNow });
        }).WithName("SlowEndpoint");

        group.MapGet("/error", (ILoggerFactory loggerFactory) =>
        {
            loggerFactory.CreateLogger("Diagnostics").LogError("Diagnostics error endpoint invoked");
            throw new InvalidOperationException("Intentional failure from /api/diagnostics/error.");
        }).WithName("ErrorEndpoint");

        return endpoints;
    }
}

src/Backend.Api/Endpoints/ProductEndpoints.cs

The full CRUD + search + stats + purchase. Every handler follows the same pattern: open a named custom span, time the work, record a backend.db.query.* or backend.product.* metric, and log a structured line.

using System.Diagnostics;
using Backend.Api.Configuration;
using Backend.Api.Contracts;
using Backend.Api.Data;
using Backend.Api.Models;
using Backend.Api.Telemetry;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace Backend.Api.Endpoints;

public static class ProductEndpoints
{
    public static IEndpointRouteBuilder MapProductEndpoints(this IEndpointRouteBuilder endpoints)
    {
        var group = endpoints.MapGroup("/api/products").WithTags("Products");

        group.MapGet("/", ListAsync).WithName("ListProducts");
        group.MapGet("/search", SearchAsync).WithName("SearchProducts");
        group.MapGet("/stats", StatsAsync).WithName("GetProductStats");
        group.MapGet("/{id:guid}", GetAsync).WithName("GetProduct");
        group.MapPost("/", CreateAsync).WithName("CreateProduct");
        group.MapPut("/{id:guid}", UpdateAsync).WithName("UpdateProduct");
        group.MapDelete("/{id:guid}", DeleteAsync).WithName("DeleteProduct");
        group.MapPost("/{id:guid}/purchase", PurchaseAsync).WithName("PurchaseProduct");

        return endpoints;
    }

    private static async Task<IResult> ListAsync(
        AppDbContext db, ApiTelemetry telemetry, IOptions<DatabaseOptions> dbOptions,
        ILoggerFactory loggerFactory, CancellationToken ct)
    {
        const string queryName = "products.list";
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var logger = loggerFactory.CreateLogger("Products");
        using var activity = telemetry.StartActivity(queryName);
        var sw = Stopwatch.StartNew();

        try
        {
            activity?.SetTag("db.provider", provider);
            var products = await db.Products.AsNoTracking()
                .OrderBy(p => p.CreatedAt).Select(p => p.ToResponse()).ToListAsync(ct);

            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: true, resultCount: products.Count);
            logger.LogInformation("Listed {ProductCount} products from {DatabaseProvider}", products.Count, provider);
            return Results.Ok(products);
        }
        catch (Exception)
        {
            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: false);
            throw;
        }
    }

    private static async Task<IResult> SearchAsync(
        string? term, AppDbContext db, ApiTelemetry telemetry, IOptions<DatabaseOptions> dbOptions,
        ILoggerFactory loggerFactory, CancellationToken ct)
    {
        const string queryName = "products.search";
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var logger = loggerFactory.CreateLogger("Products");
        using var activity = telemetry.StartActivity(queryName);
        var sw = Stopwatch.StartNew();

        if (string.IsNullOrWhiteSpace(term))
        {
            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: false);
            return Results.ValidationProblem(new Dictionary<string, string[]> { ["term"] = ["Search term is required."] });
        }

        var normalized = term.Trim().ToLowerInvariant();
        try
        {
            activity?.SetTag("db.provider", provider);
            activity?.SetTag("app.search.term", normalized);

            var products = await db.Products.AsNoTracking()
                .Where(p => p.Name.ToLower().Contains(normalized)
                         || (p.Description != null && p.Description.ToLower().Contains(normalized)))
                .OrderBy(p => p.Name).Select(p => p.ToResponse()).ToListAsync(ct);

            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: true, resultCount: products.Count);
            logger.LogInformation("Search '{Term}' returned {Count} products", normalized, products.Count);
            return Results.Ok(products);
        }
        catch (Exception)
        {
            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: false);
            throw;
        }
    }

    private static async Task<IResult> StatsAsync(
        AppDbContext db, ApiTelemetry telemetry, IOptions<DatabaseOptions> dbOptions,
        ILoggerFactory loggerFactory, CancellationToken ct)
    {
        const string queryName = "products.stats";
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var logger = loggerFactory.CreateLogger("Products");
        using var activity = telemetry.StartActivity(queryName);
        var sw = Stopwatch.StartNew();

        try
        {
            activity?.SetTag("db.provider", provider);
            var total = await db.Products.CountAsync(ct);
            var stats = total == 0
                ? new ProductStatsResponse(0, 0, 0m, 0m)
                : new ProductStatsResponse(
                    total,
                    await db.Products.SumAsync(p => p.Quantity, ct),
                    await db.Products.SumAsync(p => p.Price * p.Quantity, ct),
                    await db.Products.AverageAsync(p => p.Price, ct));

            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: true);
            logger.LogInformation("Computed stats over {Count} products", stats.TotalCount);
            return Results.Ok(stats);
        }
        catch (Exception)
        {
            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: false);
            throw;
        }
    }

    private static async Task<IResult> GetAsync(
        Guid id, AppDbContext db, ApiTelemetry telemetry, IOptions<DatabaseOptions> dbOptions, CancellationToken ct)
    {
        const string queryName = "products.get";
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        using var activity = telemetry.StartActivity(queryName);
        var sw = Stopwatch.StartNew();

        try
        {
            activity?.SetTag("db.provider", provider);
            activity?.SetTag("app.product.id", id);
            var product = await db.Products.AsNoTracking()
                .Where(p => p.Id == id).Select(p => p.ToResponse()).FirstOrDefaultAsync(ct);

            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: product is not null);
            return product is null ? Results.NotFound() : Results.Ok(product);
        }
        catch (Exception)
        {
            telemetry.RecordDbQuery(queryName, provider, sw.Elapsed, success: false);
            throw;
        }
    }

    private static async Task<IResult> CreateAsync(
        CreateProductRequest request, AppDbContext db, ApiTelemetry telemetry, IOptions<DatabaseOptions> dbOptions,
        ILoggerFactory loggerFactory, CancellationToken ct)
    {
        const string operation = "create";
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var logger = loggerFactory.CreateLogger("Products");
        using var activity = telemetry.StartActivity("products.create");

        var errors = Validate(request.Name, request.Quantity, request.Price);
        if (errors.Count > 0)
        {
            telemetry.RecordProductOperation(operation, provider, success: false);
            return Results.ValidationProblem(errors);
        }

        var now = DateTimeOffset.UtcNow;
        var product = new Product
        {
            Id = Guid.NewGuid(), Name = request.Name!.Trim(), Description = Normalize(request.Description),
            Quantity = request.Quantity, Price = request.Price, CreatedAt = now, UpdatedAt = now,
        };

        activity?.SetTag("db.provider", provider);
        activity?.SetTag("app.product.id", product.Id);

        db.Products.Add(product);
        await db.SaveChangesAsync(ct);

        telemetry.RecordProductOperation(operation, provider, success: true);
        logger.LogInformation("Created product {ProductId} ({ProductName})", product.Id, product.Name);
        return Results.Created($"/api/products/{product.Id}", product.ToResponse());
    }

    private static async Task<IResult> UpdateAsync(
        Guid id, UpdateProductRequest request, AppDbContext db, ApiTelemetry telemetry,
        IOptions<DatabaseOptions> dbOptions, ILoggerFactory loggerFactory, CancellationToken ct)
    {
        const string operation = "update";
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var logger = loggerFactory.CreateLogger("Products");
        using var activity = telemetry.StartActivity("products.update");

        var errors = Validate(request.Name, request.Quantity, request.Price);
        if (errors.Count > 0)
        {
            telemetry.RecordProductOperation(operation, provider, success: false);
            return Results.ValidationProblem(errors);
        }

        var product = await db.Products.FirstOrDefaultAsync(p => p.Id == id, ct);
        if (product is null)
        {
            telemetry.RecordProductOperation(operation, provider, success: false);
            return Results.NotFound();
        }

        product.Name = request.Name!.Trim();
        product.Description = Normalize(request.Description);
        product.Quantity = request.Quantity;
        product.Price = request.Price;
        product.UpdatedAt = DateTimeOffset.UtcNow;

        activity?.SetTag("db.provider", provider);
        activity?.SetTag("app.product.id", product.Id);

        await db.SaveChangesAsync(ct);
        telemetry.RecordProductOperation(operation, provider, success: true);
        logger.LogInformation("Updated product {ProductId}", product.Id);
        return Results.Ok(product.ToResponse());
    }

    private static async Task<IResult> DeleteAsync(
        Guid id, AppDbContext db, ApiTelemetry telemetry, IOptions<DatabaseOptions> dbOptions,
        ILoggerFactory loggerFactory, CancellationToken ct)
    {
        const string operation = "delete";
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var logger = loggerFactory.CreateLogger("Products");
        using var activity = telemetry.StartActivity("products.delete");

        var product = await db.Products.FirstOrDefaultAsync(p => p.Id == id, ct);
        if (product is null)
        {
            telemetry.RecordProductOperation(operation, provider, success: false);
            return Results.NotFound();
        }

        activity?.SetTag("db.provider", provider);
        activity?.SetTag("app.product.id", product.Id);

        db.Products.Remove(product);
        await db.SaveChangesAsync(ct);
        telemetry.RecordProductOperation(operation, provider, success: true);
        logger.LogInformation("Deleted product {ProductId}", id);
        return Results.NoContent();
    }

    // The business operation worth its own span + metrics: decrement stock and record revenue,
    // and surface an out-of-stock failure on the trace WITHOUT throwing.
    private static async Task<IResult> PurchaseAsync(
        Guid id, PurchaseRequest request, AppDbContext db, ApiTelemetry telemetry,
        IOptions<DatabaseOptions> dbOptions, ILoggerFactory loggerFactory, CancellationToken ct)
    {
        var provider = DatabaseProviders.Normalize(dbOptions.Value.Provider);
        var logger = loggerFactory.CreateLogger("Products");
        using var activity = telemetry.StartActivity("products.purchase", ActivityKind.Internal);
        activity?.SetTag("db.provider", provider);
        activity?.SetTag("app.product.id", id);
        activity?.SetTag("app.purchase.quantity", request.Quantity);

        if (request.Quantity <= 0)
        {
            telemetry.RecordPurchase(provider, success: false, amount: 0m);
            return Results.ValidationProblem(new Dictionary<string, string[]> { ["quantity"] = ["Quantity must be greater than zero."] });
        }

        var product = await db.Products.FirstOrDefaultAsync(p => p.Id == id, ct);
        if (product is null)
        {
            telemetry.RecordPurchase(provider, success: false, amount: 0m);
            return Results.NotFound();
        }

        if (product.Quantity < request.Quantity)
        {
            telemetry.RecordPurchase(provider, success: false, amount: 0m);
            activity?.SetStatus(ActivityStatusCode.Error, "Insufficient stock");
            logger.LogWarning("Purchase rejected for {ProductId}: requested {Requested} but only {Available} in stock",
                id, request.Quantity, product.Quantity);
            return Results.Problem(
                title: "Insufficient stock",
                detail: $"Requested {request.Quantity} but only {product.Quantity} of '{product.Name}' available.",
                statusCode: StatusCodes.Status409Conflict);
        }

        var amount = product.Price * request.Quantity;
        product.Quantity -= request.Quantity;
        product.UpdatedAt = DateTimeOffset.UtcNow;
        await db.SaveChangesAsync(ct);

        telemetry.RecordPurchase(provider, success: true, amount: amount);
        activity?.SetTag("app.purchase.amount", (double)amount);
        logger.LogInformation("Purchased {Quantity} x {ProductName} for {Amount}; {Remaining} remaining",
            request.Quantity, product.Name, amount, product.Quantity);

        return Results.Ok(product.ToResponse());
    }

    private static Dictionary<string, string[]> Validate(string? name, int quantity, decimal price)
    {
        var errors = new Dictionary<string, string[]>();
        if (string.IsNullOrWhiteSpace(name)) errors["name"] = ["Name is required."];
        else if (name.Trim().Length > 120) errors["name"] = ["Name must be 120 characters or fewer."];
        if (quantity < 0) errors["quantity"] = ["Quantity cannot be negative."];
        if (price < 0) errors["price"] = ["Price cannot be negative."];
        return errors;
    }

    private static string? Normalize(string? description)
        => string.IsNullOrWhiteSpace(description) ? null : description.Trim();
}

Next

You've now followed a request from the browser to the database as one trace. Part 1 -- Blazor Server observability covers the frontend, circuits, and sockets (and carries the shared foundation code). Part 3 -- On-prem observability for background jobs brings jobs you don't control -- a legacy text-log job, PowerShell, and Python -- into the same SigNoz. Part 4 -- Instrument now, collect later covers the BCL instrumentation primitives this post builds on.

💼Open for consulting

I take on consulting and delivery work across .NET and React — on my own or alongside a trusted group of senior engineers I work with. Together we can build, untangle and modernize your software:

  • Building ASP.NET / Blazor / C# / WPF apps with Postgres / ClickHouse
  • Untangling, refactoring & modernizing legacy ASP.NET, C#, Blazor and WPF into a modern stack (modular monolith C# + React)
  • Cloud & on-premise DevOps: Azure DevOps, CI/CD pipelines and automation
  • Observability & analytics — in the cloud and on-premise
  • On-premise migrations
  • Scaling up delivery with experienced .NET, backend and React engineers, plus technical leadership
Email meLinkedIn →About me →
View on GitHub