DEV Community: dotnet

Stop Hardcoding Your .NET Data Provider

Pengdows LLC — Wed, 07 Oct 2026 21:50:12 +0000

There is a piece of advice I have been giving for a long time. I first wrote about it around 2010, before .NET Core existed:

Do not hardcode your database provider into your application unless the application is deliberately a database-specific application.

That sounds obvious now. It was less obvious then, when a lot of .NET code looked like this:

using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(sql, connection);

There is nothing technically wrong with that code. SqlConnection works. SqlCommand works. If the application is a SQL Server application and will remain one, this is a reasonable choice.

The problem begins when the application is supposed to support more than one database, or when a reusable library quietly assumes that every database behaves like SQL Server.

At that point, the provider type is no longer an implementation detail. It has leaked through the data-access boundary and become part of the application.

That is exactly the problem ADO.NET was designed to solve.

ADO.NET already has the contract

The useful part of ADO.NET is not SqlConnection. It is the abstraction underneath it:

DbConnection
DbCommand
DbParameter
DbDataReader
DbTransaction
DbProviderFactory

The concrete provider supplies the implementation. The application uses the contract.

A provider-agnostic connection flow is therefore not mysterious:

DbProviderFactory factory = ...;

await using DbConnection connection = factory.CreateConnection()
    ?? throw new InvalidOperationException("The provider did not create a connection.");

connection.ConnectionString = connectionString;
await connection.OpenAsync();

await using DbCommand command = connection.CreateCommand();

var parameterName = "customer_id";
command.CommandText = $"select name from customer where customer_id = @{parameterName}";

DbParameter parameter = command.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = customerId;
command.Parameters.Add(parameter);

await using DbDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    var name = reader.GetString(0);
}

The @ in that first SQL string is deliberately limited to named-parameter providers, not presented as universal syntax. In a real provider-agnostic implementation, the marker comes from detected provider metadata. A positional provider such as OleDb or Access must use ?, and the parameter order becomes part of the contract.

There is no SqlConnection in the object-creation path. There is no NpgsqlConnection, OracleConnection, or MySqlConnection either.

That is the first half of the solution.

Why bother?

Because the provider is infrastructure, not application policy.

If a reusable data-access library directly references SqlConnection, then the library is coupled to that provider's assembly, release cycle, configuration conventions, and behavioral assumptions. Moving to Microsoft.Data.SqlClient, upgrading Npgsql, adding Oracle support, or serving a second tenant with a different database becomes a library change.

With the provider behind DbProviderFactory, the composition root selects the implementation and the data-access layer consumes the contract. A routine provider upgrade can then be a package and deployment change rather than a source change and rebuild of every library that talks to the database. The application still pins the provider version, and a provider that changes behavior still needs testing. Switching providers is localized to registration and dialect/capability handling.

The same separation works in the other direction. A data-access library can ship an upgrade without taking ownership of the provider package or forcing every consumer onto one vendor's release schedule. Provider selection remains an application or deployment decision.

That does not make upgrades magically risk-free. A provider can change defaults, remove APIs, or expose a database behavior that needs a new dialect rule. Those changes still need tests. The difference is that the dependency is isolated: the application-facing data-access contract does not have to change just because the provider assembly did.

It also makes the same library usable in more than one environment. Development can use SQLite, integration tests can use a fake or containerized provider, production can use SQL Server or PostgreSQL, and a multi-tenant service can select a provider per tenant without embedding those choices in every query path.

Finding the provider at runtime

.NET Framework: `providerName` in configuration

In classic .NET Framework applications, a connection string entry commonly carried both the connection string and a providerName:

<connectionStrings>
  <add name="ApplicationDatabase"
       connectionString="..."
       providerName="System.Data.SqlClient" />
</connectionStrings>

The important idea was not the XML. The important idea was that the provider identity was configuration, not source code.

Once you have the provider name, ADO.NET can resolve the factory:

var factory = DbProviderFactories.GetFactory(providerName);

.NET Core and modern .NET

There is no machine-wide provider configuration model in .NET Core. In current .NET, the provider is normally a NuGet dependency and the application registers its factory during startup.

{
  "Database": {
    "ProviderName": "Npgsql",
    "ConnectionString": "Host=localhost;Database=orders;Username=app;Password=..."
  }
}

The composition root can map the configured invariant name to the matching factory:

var factories = new Dictionary<string, DbProviderFactory>(StringComparer.OrdinalIgnoreCase)
{
    ["Npgsql"] = NpgsqlFactory.Instance,
    ["Microsoft.Data.SqlClient"] = SqlClientFactory.Instance
};

var providerName = configuration["Database:ProviderName"]
    ?? throw new InvalidOperationException("Database provider is not configured.");

if (!factories.TryGetValue(providerName, out var factory))
{
    throw new InvalidOperationException($"Unsupported database provider '{providerName}'.");
}

DbProviderFactories.RegisterFactory(providerName, factory);

For a provider that is a compile-time dependency, the BCL also accepts its factory type:

DbProviderFactories.RegisterFactory("Npgsql", typeof(NpgsqlFactory));

For a provider that is not a compile-time dependency, use a trusted assembly-qualified factory type name:

var factoryTypeName = configuration["Database:FactoryType"]
    ?? throw new InvalidOperationException("Database factory type is not configured.");

DbProviderFactories.RegisterFactory("Npgsql", factoryTypeName);

The assembly still has to be resolvable by normal .NET probing, which usually means it ships with the application. A path-based loader can go further and load a provider DLL from a file under the application directory.

For applications that support multiple providers or tenants, keyed DI is another option. AddKeyedSingleton and GetRequiredKeyedService require .NET 8 or later. The important architectural point is that the composition root knows the provider; the rest of the data-access code receives the ADO.NET contract.

For longer-lived applications, current .NET also provides DbDataSource, which gives a provider a shared place for pooling and prepared-statement setup. Register it once and open short-lived connections from the shared instance:

builder.Services.AddSingleton<DbDataSource>(_ =>
    factory.CreateDataSource(connectionString));

Npgsql's NpgsqlDataSource is a richer provider-specific version of the same idea. DbProviderFactory remains the portable construction boundary; DbDataSource is the shared-resource boundary.

Loading a provider from a registry

Sometimes the provider is not known when the application is built. A multi-tenant service may keep each tenant's provider name, factory type, and connection string in a central registry. The registry itself still needs a bootstrap provider, but the target provider can be loaded from the registry record.

Here is the essential loader pattern, simplified from pengdows.crud's provider loader:

static DbProviderFactory LoadFactory(
    string providerName,
    string assemblyName,
    string factoryTypeName)
{
    var assembly = Assembly.Load(assemblyName);
    var factoryType = assembly.GetType(factoryTypeName, throwOnError: true)!;

    var factory =
        factoryType.GetProperty("Instance", BindingFlags.Public | BindingFlags.Static)
            ?.GetValue(null) as DbProviderFactory
        ?? factoryType.GetField("Instance", BindingFlags.Public | BindingFlags.Static)
            ?.GetValue(null) as DbProviderFactory
        ?? throw new InvalidOperationException(
            $"Factory type '{factoryTypeName}' did not expose DbProviderFactory.Instance.");

    DbProviderFactories.RegisterFactory(providerName, factory);
    return factory;
}

The registration belongs at startup or DI composition time. Do not repeat it for every command or lookup.

If the assembly comes from a configured file path, use Assembly.LoadFrom only after resolving the path against the application base directory. Resolve symlinks before checking containment, and reject paths or symlink targets that escape that directory. This is a path-safety rail, not a sandbox: provider code executes inside the application process with its permissions.

The registry values must be trusted deployment data. A database row that controls assembly loading is effectively code-loading configuration.

`DataSourceInformation`: the missing layer

Resolving a provider tells us how to create ADO.NET objects. It does not tell us every rule of the database behind the connection.

ADO.NET exposes a standard metadata path through GetSchema("DataSourceInformation"):

static void ReadMetadata(DbConnection connection)
{
    DataTable schema = connection.GetSchema(
        DbMetaDataCollectionNames.DataSourceInformation);

    if (schema.Rows.Count == 0)
    {
        // This provider does not publish the standard row; probe instead.
        return;
    }

    DataRow row = schema.Rows[0];

    string product = row[DbMetaDataColumnNames.DataSourceProductName] as string ?? "";
    string version = row[DbMetaDataColumnNames.DataSourceProductVersion] as string ?? "";
    string markerFormat = row[DbMetaDataColumnNames.ParameterMarkerFormat] as string ?? "";
    string markerPattern = row[DbMetaDataColumnNames.ParameterMarkerPattern] as string ?? "";
    string namePattern = row[DbMetaDataColumnNames.ParameterNamePattern] as string ?? "";
    int maxNameLength = row[DbMetaDataColumnNames.ParameterNameMaxLength] is int n ? n : 0;
}

The problem is that providers fill this table inconsistently. Some report useful values. Some leave fields empty. Some expose a base product but not a meaningful version. Some require a product-specific query before you can distinguish PostgreSQL from a compatible engine.

In this model, 0 means the provider did not report a limit. Unknown limits should be treated as unknown, not as zero capacity.

A real data-access layer therefore does three things:

Reads the standard metadata when it is available.
Runs product and version probes when the metadata is incomplete.
Normalizes the result into an immutable object containing facts and capability flags.

The shape is small and boring by design. This is trimmed from pengdows.crud's DataSourceInformation:

public sealed class DataSourceInformation
{
    public required string ProductName { get; init; }
    public required string ProductVersion { get; init; }
    public required string ParameterMarker { get; init; }
    public required string ParameterMarkerPattern { get; init; }
    public required Regex ParameterNamePattern { get; init; }
    public required string QuotePrefix { get; init; }
    public required string QuoteSuffix { get; init; }
    public required int ParameterNameMaxLength { get; init; }
    public required int MaxParameterLimit { get; init; }
    public required bool SupportsNamedParameters { get; init; }
    public required bool SupportsRepeatedNamedParameters { get; init; }
    public required bool SupportsMerge { get; init; }
    public required bool SupportsInsertOnConflict { get; init; }
    public required bool SupportsOnDuplicateKey { get; init; }
    public required bool IsFallbackDialect { get; init; }
}

This is the useful abstraction. Code can ask what the connected database supports instead of switching on a database-name enum:

if (!info.SupportsRepeatedNamedParameters)
{
    // Allocate distinct parameter names for repeated values.
}

if (info.MaxParameterLimit > 0 &&
    info.MaxParameterLimit < requestedParameterCount)
{
    // Split the command or batch the operation.
}

The parameter helper is equally small:

static string MakeParameterName(DataSourceInformation info, string name)
{
    if (!info.SupportsNamedParameters)
    {
        // With ?, binding order equals parameter-add order. Add parameters in SQL order.
        return info.ParameterMarker; // positional: ?, for example
    }

    if (!info.ParameterNamePattern.IsMatch(name))
    {
        throw new ArgumentException("Invalid parameter name.", nameof(name));
    }

    if (info.ParameterNameMaxLength > 0 &&
        name.Length > info.ParameterNameMaxLength)
    {
        throw new ArgumentException("Parameter name is too long.", nameof(name));
    }

    return info.ParameterMarker + name;
}

This is the idea behind a dialect's parameter-name logic. It also explains why a generic implementation cannot blindly write @p0. Oracle commonly uses :p0; positional providers use ?; Npgsql supports named forms and native positional $1; and ODP.NET binds by position by default unless BindByName is enabled.

Capability flags are not necessarily mutually exclusive:

if (info.SupportsInsertOnConflict)
{
    // Default preference order; a specific operation may choose differently.
    // Build INSERT ... ON CONFLICT for this operation.
}
else if (info.SupportsOnDuplicateKey)
{
    // Build INSERT ... ON DUPLICATE KEY UPDATE.
}
else if (info.SupportsMerge)
{
    // Build a MERGE-based implementation.
}
else
{
    // Use a transaction containing an insert/update decision.
}

PostgreSQL 15+ supports both MERGE and ON CONFLICT; the correct choice depends on the operation being generated. Query the capability required by the SQL you are building, not a database name as a shortcut.

Identifier quoting follows the same rule. Keep it in one helper rather than scattering concatenation through the application:

static string QuoteIdentifier(DataSourceInformation info, string name)
{
    if (string.IsNullOrEmpty(info.QuoteSuffix))
    {
        return name;
    }

    return info.QuotePrefix + name.Replace(
        info.QuoteSuffix,
        info.QuoteSuffix + info.QuoteSuffix,
        StringComparison.Ordinal) + info.QuoteSuffix;
}

Composite identifiers, reserved words, and provider-specific rules may require a richer dialect helper, but the ownership is the same: SQL generation needs facts about the connected database.

Detection happens once, then the facts are reused

Database detection should not be repeated for every command. It belongs to context initialization.

The broad flow is:

configuration
    -> provider name
    -> DbProviderFactory
    -> DbConnection
    -> open connection
    -> ADO.NET metadata and product probes
    -> database dialect
    -> immutable datasource facts
    -> commands generated with the right rules

Your data-access layer owns the detected dialect and metadata. Query builders, repositories, transactions, and higher-level libraries all use the same answer.

If the provider loads but the engine is not recognized, the layer can use a conservative SQL-92 fallback, expose that it is operating in fallback mode, and provide a compatibility warning. Loading a provider does not mean that every database behind that provider is fully supported.

The point is not to hide the database

I am not interested in pretending that all relational databases are interchangeable. They are not.

The point is to put the differences where they belong. Configuration chooses the provider. DbProviderFactory creates provider objects. The open connection tells you what is behind it. Metadata and probes become immutable facts. A dialect turns those facts into correct SQL behavior.

That is how you support multiple databases without hardcoding your data provider into every layer of the application.

That's a lot of code to own: provider loading, metadata normalization, dialects, capability detection, fallback handling, connection modes for file-based databases like SQLite, and the edge cases around them.

Or, if you don't want to handcode all this... use pengdows.crud.

We Optimized Everything. Azure Was Still Freezing Our .NET API with Gen2 Pauses

Miloš Panić — Wed, 07 Oct 2026 18:14:31 +0000

Some context

Our backend is a .NET API that a large fleet of devices talks to. The devices constantly report their status, and the API takes in those updates, processes them and serves regular user requests at the same time. On a normal day it handles about 400 requests every 15 seconds (roughly 25–30 requests per second), steadily, around the clock.

That load isn't huge, but it never stops. Because the traffic is so constant, the API has no quiet periods to catch up after a slowdown. If it stalls for even a few seconds, hundreds of requests are waiting when it comes back.

The problem

This API had a problem we couldn't get rid of. At random moments it would freeze.

Our monitoring dashboards showed the same pattern every time. The thread pool queue length suddenly jumped to around 200, the thread pool size grew sharply, and then recovery was slow and painful. While the API was frozen, requests piled up, and once it came back it had to work through the backlog. Maximum response times spiked to 20, 50, sometimes 100 seconds.

The direct cause wasn't hard to find: a large, stop-the-world Gen2 garbage collection pause. While the GC blocks every managed thread, nothing makes progress. Work keeps arriving and the queue keeps growing. The thread pool then injects new threads only gradually, which is why recovery took so long.

What we couldn't work out was why the Gen2 collections were happening.

Round 1: Fixing our own code

Our first assumption was that the problem was in our code, so we went through it carefully.

Removing blocking code

First we removed all sync-over-async code, meaning places where a thread blocks while it waits for an async operation instead of using await:

// ❌ Before: blocks a thread pool thread while waiting
var result = _client.GetDataAsync().Result;
_service.ProcessAsync(item).Wait();

// ✅ After: frees the thread while waiting
var result = await _client.GetDataAsync();
await _service.ProcessAsync(item);

Blocking calls like these are a well-known cause of thread pool starvation, so they were worth removing anyway.

Reducing memory pressure on hot paths

Next we tuned our hot paths to allocate less:

Object pooling for frequently created objects, so they're reused instead of collected
RecyclableMemoryStream instead of MemoryStream, to avoid repeatedly allocating large buffers that end up on the Large Object Heap

private static readonly RecyclableMemoryStreamManager StreamManager = new();

// ✅ Pooled buffers instead of a fresh allocation every time
using var stream = StreamManager.GetStream();

All of these were good improvements. None of them fixed the freezes. The Gen2 pauses kept coming.

Round 2: Looking inside the process

Next I wanted to see what the live process was doing. I got console (CMD/PowerShell) access to the Azure machine hosting the API, which is roughly the Windows equivalent of SSH-ing into the box, and used the standard .NET diagnostic tools:

# Live GC, heap and thread pool metrics
dotnet-counters monitor -p <PID> --counters System.Runtime

# Snapshot of the managed heap
dotnet-gcdump collect -p <PID>

I watched the counters and analyzed heap dumps, looking for anything unusual: Large Object Heap growth, huge collections, leaking caches.

I found nothing. Nothing stood out and nothing was leaking. After all the earlier optimization work, the allocation patterns looked healthy.

Round 3: Looking outside the process

At this point I asked a different question:

What if the problem isn't in our process, but something acting on it?

Azure keeps its own logs of what the platform does to your app. When I went through them, I found entries showing that Azure was periodically profiling our process.

I lined up the timestamps of those profiling sessions with our freezes, and they matched. Every time Azure profiled the process, the API froze and showed the same thread pool spike and long Gen2 pause.

My working theory: these profiling sessions trigger blocking, foreground Gen2 collections that pause all application threads, which produces exactly the thread pool pile-up we had been chasing.

The fix: two settings

The profiling came from two Application Insights features enabled on the app:

[Feature 1 – e.g. Application Insights Profiler]
[Feature 2 – e.g. Snapshot Debugger]

We weren't really using them. Our existing logging and tracing already gave us stack traces and per-method timings. So I disabled both, and the profiling entries in the Azure logs stopped.

Then I let the API run for 4+ hours and checked the dashboards.

The results

No more freezes, stable thread pool

The queue spikes stopped and the thread pool stayed flat and stable.

Before:

After:

Memory: 2.2 GB → 1.4 GB

The working set dropped from 2.2 GB to 1.4 GB. My guess is that the profiling and debugging tooling was also adding significant memory overhead.

Worst-case response times

Maximum response times that used to reach 20–100 seconds are now consistent: 99% of requests complete in under 4–5 seconds, even in the worst case. Typical response times are much lower. What changed is that the extreme outliers are gone.

Before:

After:

Background processing: under one second

The flow that processes device status updates had been slow and spiky enough that we were considering moving it out of the API entirely. It now finishes in under one second almost every time, with no peaks.

Before:

After:

Lessons learned

Look beyond your own code. We fixed blocking calls, pooled objects and reduced allocations. All of that was worth doing, but the real cause was the platform acting on our process from outside.
Correlate timestamps across every source. The breakthrough came from lining up platform logs with application metrics, not from going deeper into one tool.
Observability has a cost. Profilers and snapshot debuggers are useful, but they can add memory overhead and pause your application. Turn them on when you need them, not permanently "just in case."
Audit features you aren't using. If another tool already gives you that insight, a redundant feature is all cost and no benefit.
Don't accept "that's just how it is." Changing the question we were asking is what finally solved it.

To be clear, this is a strong correlation backed by large before/after improvements, not a proven root cause at the CLR level. Have you seen Azure profiling or snapshot features cause GC pauses in your apps? Let me know in the comments. I'd like to hear if others have run into this.

SQL Pagination

Rhuturaj Takle — Wed, 07 Oct 2026 15:27:56 +0000

SQL Pagination

A deep-dive walkthrough of pagination in SQL Server — covering OFFSET/FETCH, why ORDER BY is mandatory and why it must be deterministic, calculating page numbers, getting the total row count without a second round trip, why deep pages get slower (and how keyset pagination fixes it), the indexing that makes pagination fast, wrapping it in a stored procedure, and how EF Core's Skip/Take maps onto all of this.

Introduction
What Pagination Actually Is
OFFSET / FETCH: The Core Syntax
Why ORDER BY Is Mandatory (and Must Be Deterministic)
Calculating Page Numbers
Getting the Total Row Count
The Hidden Cost: Deep Pages Get Slower
Keyset (Seek) Pagination
Indexing for Pagination
Pagination Inside a Stored Procedure
Older Approaches: TOP and ROW_NUMBER()
Pagination in EF Core
Common Pitfalls
Quick Reference Table
Conclusion

Introduction

Pagination means retrieving data in smaller chunks instead of loading all records at once. Instead of asking the database for every row in a table and letting the application (or the user's browser) sort out what to show, you ask for exactly one "page" at a time — say, rows 21 through 30 — and fetch the next page only when it's needed. This is especially important when working with large datasets, where loading everything wastes memory, network bandwidth, and database resources on rows nobody will ever look at.

The standard SQL Server approach (SQL Server 2012 and later) is the OFFSET/FETCH clause:

SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
-- skip the first 20 rows, then return the next 10 — i.e. page 3 at a page size of 10

This guide goes beyond the syntax. It covers the mechanics that matter in production: why the ORDER BY has to be deterministic or pages will silently overlap, why OFFSET gets slower the deeper you page, the keyset alternative that stays fast at any depth, and the indexing that underpins both.

1. What Pagination Actually Is

Returning a bounded slice of an ordered result set

Without pagination:
  SELECT * FROM Orders;          -- 5,000,000 rows -> memory, network, UI all suffer

With pagination:
  Page 1 -> rows   1-10
  Page 2 -> rows  11-20
  Page 3 -> rows  21-30          -- only the slice that is actually displayed

Pagination solves three distinct problems at once:

Database and network load — only the requested rows are read, shaped, and sent over the wire.
Application memory — the client never has to materialize millions of rows.
User experience — a screen that renders 10–50 rows is fast and usable; one that renders 5 million is neither.

Conceptually, pagination is always two things combined: a stable ordering of the full result set, and a window (skip N, take M) over that ordering. Everything in this guide is about getting one or both of those right.

2. OFFSET / FETCH: The Core Syntax

`OFFSET` skips rows; `FETCH NEXT` limits how many come back

SELECT Id, CustomerId, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS            -- how many rows to SKIP
FETCH NEXT 10 ROWS ONLY;  -- how many rows to RETURN after skipping

A few precise syntax rules worth knowing up front:

- OFFSET ... FETCH is part of the ORDER BY clause — it cannot exist without ORDER BY.
- FETCH requires OFFSET. (To start from the first row, use OFFSET 0 ROWS.)
- OFFSET is optional on its own: OFFSET 20 ROWS with no FETCH
  returns EVERYTHING after the first 20 rows.
- ROW and ROWS are interchangeable; FIRST and NEXT are interchangeable.
  OFFSET 1 ROW FETCH FIRST 1 ROW ONLY is valid.
- It cannot be combined with TOP in the same query.

Using variables instead of literals

DECLARE @Offset INT = 20;
DECLARE @PageSize INT = 10;

SELECT Id, CustomerId, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

Both values can be variables, parameters, or even expressions — which is what makes OFFSET/FETCH practical inside stored procedures and parameterized application queries (Section 9 and Section 11). The first page is simply OFFSET 0 ROWS.

3. Why ORDER BY Is Mandatory (and Must Be Deterministic)

A relational table has no inherent order — "page 2" is meaningless without one

SQL Server does NOT guarantee any row order unless you specify ORDER BY.
Without one, "skip 20, take 10" has no defined meaning — the engine would
be free to return ANY 10 rows, and a different 10 next time.

This is why OFFSET/FETCH is syntactically bound to ORDER BY — the language refuses to let you paginate over an undefined ordering at all.

But a non-unique ORDER BY is a subtler, silent bug

-- ❌ DANGEROUS — many orders can share the same OrderDate
SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

If multiple rows share the same OrderDate, SQL Server is free to return those tied rows in any order, and that order can legitimately differ between two executions of the same query — particularly once the plan changes, parallelism kicks in, or data shifts. The result: a row can appear on page 2 and again on page 3, while a different row is skipped entirely and never appears on any page. There's no error, no warning — just quietly wrong pages.

-- ✅ SAFE — add a unique tie-breaker (typically the primary key) as the LAST sort column
SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Rule: the ORDER BY for paginated queries must produce a TOTAL order.
  Whatever column(s) the user sorts by, ALWAYS append a unique column
  (usually the primary key) at the end so no two rows ever tie.

This is the single most common correctness bug in hand-written pagination, and it's worth treating as non-negotiable.

4. Calculating Page Numbers

Converting a 1-based page number into an offset

OFFSET = (PageNumber - 1) * PageSize
FETCH  = PageSize

Page size 10:
  Page 1 -> OFFSET  0
  Page 2 -> OFFSET 10
  Page 3 -> OFFSET 20      <-- the example at the top of this guide
  Page 4 -> OFFSET 30

DECLARE @PageNumber INT = 3;
DECLARE @PageSize   INT = 10;

SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Validate inputs — never trust the caller's page number

- PageNumber < 1  -> a NEGATIVE offset, which raises an error
- PageSize = 0 or huge -> an empty page, or defeats the whole purpose of paging

In real code, clamp the values (PageNumber at least 1; PageSize within a sensible range such as 1–100). An API that lets a caller request PageSize = 10,000,000 has quietly reintroduced the exact problem pagination exists to solve.

5. Getting the Total Row Count

Most UIs need "Page 3 of 47" — which requires knowing the total

-- Option A: a second query (simple, but runs the filter twice)
SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped';

-- Option B: COUNT(*) OVER() — total returned alongside every row in ONE query
SELECT
    Id, OrderDate, Total,
    COUNT(*) OVER() AS TotalRows      -- same value repeated on every row of the page
FROM Orders
WHERE Status = 'Shipped'
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

COUNT(*) OVER() is evaluated over the entire filtered set before the OFFSET/FETCH window is applied, so every returned row carries the full total. The application reads TotalRows from the first row and computes the page count:

TotalPages = CEILING(TotalRows / PageSize)

Worth knowing precisely:

- If the requested page is PAST the end, zero rows come back — so there is
  no row to read TotalRows from. Handle the empty-page case explicitly
  (e.g. fall back to a separate COUNT, or return TotalRows = 0).
- Computing the total is NOT free: it must visit every row matching the
  filter. On very large tables this count can cost more than the page itself.
  Many large systems avoid exact totals and show "Next / Previous" only,
  or cache an approximate count.

6. The Hidden Cost: Deep Pages Get Slower

`OFFSET` does not jump to row N — it reads and discards N rows

OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY
  -> SQL Server must still PRODUCE the first 1,000,000 rows in order,
     THROW THEM AWAY, and only then return the next 10.

This is the central performance fact about OFFSET pagination: the cost grows with the offset. Page 1 is nearly instant; page 100,000 does a million rows' worth of work to hand you ten. You can see it directly in the execution plan (per this series' Execution Plans guide): the operator feeding the Top has to read far more rows than the query ultimately returns.

Page 1        -> reads ~10 rows        -> fast
Page 1,000    -> reads ~10,000 rows    -> noticeably slower
Page 100,000  -> reads ~1,000,000 rows -> slow, and gets worse as data grows

For most business applications — where users rarely go beyond the first few pages — this is perfectly acceptable. It becomes a real problem for:

very large tables,
"infinite scroll" feeds,
APIs or export jobs that walk through every page sequentially.

A second, separate weakness: because OFFSET counts rows from the start each time, inserts or deletes between page requests shift the window. A new row inserted at the top while the user is on page 2 pushes one row from page 2 onto page 3 — so the user sees a duplicate on the next page. Keyset pagination (next section) avoids both problems.

7. Keyset (Seek) Pagination

Instead of "skip N rows," say "give me rows AFTER the last one I saw"

-- Page 1: no anchor yet
SELECT TOP (10) Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC;

-- The application remembers the LAST row's values from this page:
--   @LastOrderDate = '2026-03-14 09:30', @LastId = 48213

-- Next page: seek directly to the position after the last row seen
DECLARE @LastOrderDate DATETIME2 = '2026-03-14 09:30';
DECLARE @LastId        INT       = 48213;

SELECT TOP (10) Id, OrderDate, Total
FROM Orders
WHERE OrderDate < @LastOrderDate
   OR (OrderDate = @LastOrderDate AND Id < @LastId)   -- tie-breaker on equal dates
ORDER BY OrderDate DESC, Id DESC;

Instead of counting and discarding rows, the WHERE clause lets SQL Server seek straight to the right position in an index and read only the 10 rows it needs — so page 1 and page 100,000 cost essentially the same. It's also stable under inserts and deletes, because the anchor is a value, not a row count.

The trade-offs, stated honestly

Advantages:
  - Constant cost per page, regardless of depth
  - Stable results even as data is inserted/deleted between requests

Limitations:
  - No "jump to page 57" — you can only go Next/Previous from a known position
  - Needs a unique, indexed ordering (hence the Id tie-breaker)
  - The WHERE clause gets more complex with each additional sort column
  - Going BACKWARD requires reversing the comparison and sort direction,
    then re-reversing the results
  - The ORDER BY direction and the comparison operators must match exactly

Choosing between them:
  Numbered pages + jump-to-page + modest data  ->  OFFSET / FETCH
  Infinite scroll, feeds, APIs, huge tables,
  or sequential export of everything           ->  Keyset

Note that T-SQL does not support row-value comparison like (OrderDate, Id) < (@d, @id), which is why the expanded OR (... AND ...) form above is required.

8. Indexing for Pagination

The ORDER BY is the expensive part — an index that matches it removes the sort

CREATE INDEX IX_Orders_OrderDate_Id
ON Orders (OrderDate DESC, Id DESC);

Without a supporting index, SQL Server must read the matching rows and sort the entire set just to find the first 10 — and that sort cost is paid on every single page request. With an index whose key order matches the ORDER BY, the rows are already in the right order, so the engine can start at the right place and stop after the page is full (per this series' Indexes and Execution Plans guides).

Guidelines:
  - Match the index key columns and directions to the ORDER BY
    (including the unique tie-breaker column).
  - If the query has a WHERE filter, put the equality-filter column(s)
    FIRST in the index, then the ORDER BY columns:
        WHERE Status = 'Shipped' ORDER BY OrderDate DESC, Id DESC
        -> INDEX (Status, OrderDate DESC, Id DESC)
  - Add INCLUDE columns for the selected columns to avoid key lookups
    on every row of the page.
  - Verify in the execution plan: look for an Index Seek/Scan with NO
    separate Sort operator.

Every extra index speeds reads and slows writes — add the ones that back
  your genuinely hot, paginated screens, not one per possible sort column.

9. Pagination Inside a Stored Procedure

A reusable, parameterized, validated paging query

CREATE PROCEDURE GetOrdersPage
    @PageNumber INT = 1,
    @PageSize   INT = 10,
    @Status     NVARCHAR(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Clamp inputs: never trust the caller
    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @PageSize   < 1 SET @PageSize   = 10;
    IF @PageSize   > 100 SET @PageSize = 100;

    SELECT
        Id, CustomerId, OrderDate, Status, Total,
        COUNT(*) OVER() AS TotalRows
    FROM Orders
    WHERE (@Status IS NULL OR Status = @Status)
    ORDER BY OrderDate DESC, Id DESC          -- deterministic (Section 3)
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

EXEC GetOrdersPage @PageNumber = 3, @PageSize = 10, @Status = 'Shipped';

A word of caution that connects directly to this series' Stored Procedures guide: the optional-filter pattern (@Status IS NULL OR Status = @Status) is a classic parameter-sniffing trap — the plan cached for the first call (say, with @Status = NULL) gets reused for very different calls. If you see input-dependent slowness here, adding OPTION (RECOMPILE) to this single statement is the surgical fix, as that guide describes.

10. Older Approaches: TOP and ROW_NUMBER()

What you'll meet in legacy code (pre-SQL Server 2012)

-- ROW_NUMBER() approach: number every row, then filter on the number
WITH Numbered AS (
    SELECT
        Id, OrderDate, Total,
        ROW_NUMBER() OVER (ORDER BY OrderDate DESC, Id DESC) AS RowNum
    FROM Orders
)
SELECT Id, OrderDate, Total
FROM Numbered
WHERE RowNum BETWEEN 21 AND 30      -- page 3 at page size 10
ORDER BY RowNum;

TOP approach (nested TOP / reversed ORDER BY): even clumsier, and error-prone.

On modern SQL Server, OFFSET/FETCH is clearer, shorter, and the standard choice. The ROW_NUMBER() form is still useful to recognize in older codebases — and it has the same core cost characteristic: rows before the window still have to be numbered.

11. Pagination in EF Core

`Skip` and `Take` translate directly to `OFFSET` / `FETCH`

int pageNumber = 3;
int pageSize   = 10;

var orders = await context.Orders
    .OrderByDescending(o => o.OrderDate)
    .ThenByDescending(o => o.Id)               // the deterministic tie-breaker (Section 3)
    .Skip((pageNumber - 1) * pageSize)         // -> OFFSET 20 ROWS
    .Take(pageSize)                            // -> FETCH NEXT 10 ROWS ONLY
    .AsNoTracking()                            // read-only list: skip change-tracking overhead
    .ToListAsync();

This is the same mechanism this series' EF Core guide describes: LINQ is translated into SQL, and Skip/Take become OFFSET/FETCH. Two points deserve emphasis:

- Always put OrderBy BEFORE Skip/Take, and include the unique tie-breaker
  via ThenBy. EF Core will warn if you use Skip/Take without any ordering,
  because the page contents would be undefined.
- Skip/Take must be applied on the IQueryable, BEFORE ToListAsync() — 
  otherwise you've already loaded every row into memory and are
  "paginating" an in-memory list (this series' IEnumerable vs. IQueryable guide).

Total count alongside the page

var query = context.Orders.Where(o => o.Status == "Shipped");

int totalRows = await query.CountAsync();      // one COUNT query

var page = await query
    .OrderByDescending(o => o.OrderDate)
    .ThenByDescending(o => o.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync();                            // a second query for the page itself

Keyset pagination in LINQ

var nextPage = await context.Orders
    .Where(o => o.OrderDate < lastOrderDate
             || (o.OrderDate == lastOrderDate && o.Id < lastId))
    .OrderByDescending(o => o.OrderDate)
    .ThenByDescending(o => o.Id)
    .Take(pageSize)                            // no Skip at all — the WHERE does the seeking
    .AsNoTracking()
    .ToListAsync();

This produces exactly the WHERE ... ORDER BY ... TOP shape from Section 7, with the same constant-cost-per-page behavior.

12. Common Pitfalls

Pitfall	Why it hurts	Better approach
Paginating without an `ORDER BY`	Row order is undefined, so "page 2" has no stable meaning (and `OFFSET`/`FETCH` won't even compile without it)	Always specify an explicit `ORDER BY` (Section 3)
Non-unique `ORDER BY` (e.g. just `OrderDate`)	Tied rows can swap places between executions — rows duplicate across pages or vanish entirely, silently	Append a unique tie-breaker such as the primary key (Section 3)
Using `OFFSET` for very deep pages on huge tables	SQL Server reads and discards every skipped row — cost grows with the offset	Switch to keyset pagination for deep or sequential paging (Section 7)
No index matching the `ORDER BY`	The whole filtered set is sorted on every page request	Create an index matching the sort columns and directions (Section 8)
Running `COUNT(*)` on every page request over a huge table	The count must scan every matching row and can cost more than the page itself	Cache or approximate the total, or drop exact totals in favor of Next/Previous (Section 5)
Not validating page number and page size	A negative offset raises an error; a giant page size defeats the point of paging	Clamp `@PageNumber ≥ 1` and `@PageSize` to a sensible maximum (Section 4, 9)
Calling `ToList()` before `Skip`/`Take` in EF Core	Loads the entire table into memory, then "paginates" in C#	Apply `OrderBy`/`Skip`/`Take` on the `IQueryable`, then materialize (Section 11)
Forgetting the empty-last-page case when using `COUNT(*) OVER()`	A page past the end returns zero rows, so there's no row to read the total from	Handle zero-row results explicitly (Section 5)
Optional-filter pattern `(@P IS NULL OR Col = @P)` in a paged procedure	A parameter-sniffing trap: one cached plan reused for very different parameter values	Apply `OPTION (RECOMPILE)` to that statement if input-dependent slowness appears (Section 9)

Quick Reference Table

Concept	Syntax	Purpose
Basic paging	`ORDER BY ... OFFSET n ROWS FETCH NEXT m ROWS ONLY`	Skip `n` rows, return the next `m`
First page	`OFFSET 0 ROWS FETCH NEXT m ROWS ONLY`	Start from the first row
Page → offset	`OFFSET (@Page - 1) * @Size ROWS`	Convert a 1-based page number to a row offset
Total in one query	`COUNT(*) OVER() AS TotalRows`	Total filtered rows returned with every row
Total pages	`CEILING(TotalRows / PageSize)`	Number of pages (cast to decimal before dividing)
Deterministic order	`ORDER BY SortCol, Id`	Unique tie-breaker so pages never overlap or skip
Keyset paging	`WHERE Col < @Last OR (Col = @Last AND Id < @LastId)` + `TOP (m)`	Constant-cost "rows after the last seen"
Supporting index	`CREATE INDEX ... (SortCol DESC, Id DESC)`	Removes the sort; enables efficient seeks
EF Core offset paging	`.OrderBy(...).ThenBy(...).Skip(n).Take(m)`	Translates to `OFFSET`/`FETCH`
EF Core keyset paging	`.Where(...).OrderBy(...).Take(m)`	Translates to `WHERE ... TOP`

Conclusion

Pagination is conceptually simple — a stable ordering plus a skip-and-take window — and OFFSET/FETCH makes the syntax nearly trivial. But the details that decide whether it works correctly and at scale are exactly the ones that are easy to miss: a non-deterministic ORDER BY that silently duplicates and drops rows across pages, an OFFSET whose cost climbs with every page deeper you go, a total count that quietly costs more than the data it's counting, and a missing index that forces a full sort on every request.

The practical takeaway is a decision, not a rule: use OFFSET/FETCH with a deterministic ORDER BY and a matching index for ordinary, numbered-page screens, where simplicity and "jump to page N" matter and datasets are moderate; reach for keyset pagination when you're dealing with huge tables, infinite scroll, or APIs that walk every page in sequence. Understanding why each behaves the way it does — rather than memorizing the syntax — is what lets you pick the right one deliberately, exactly the way this whole series treats every other trade-off it covers.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the "page 2 and page 3 showed the same order twice" bug story that made the case for a deterministic ORDER BY click better than any abstract explanation.

Keeping Your Knowledge Graph Current Without a Dedicated Team

Mehdi Mohseni — Wed, 07 Oct 2026 14:21:41 +0000

The Maintenance Problem Nobody Plans For

You built a knowledge graph to give your AI agent a trustworthy model of your system. Six months later it still describes a service that was decommissioned in March, a dependency that was swapped in a migration nobody documented, and a team that was reorganised. The graph is not wrong in an obvious way. It is coherent, it parses, it answers queries. It is just describing a system that no longer exists.

The fix is not better discipline. It is a maintenance loop that derives edges from what the system already produces, namely git history, deployment manifests and OpenTelemetry trace data, and reconciles those observations against the graph on a schedule. This article builds that loop.

All examples target Neo4j 2026.09.0 (or 5.26 LTS) with Neo4j.Driver 6.2.1 on .NET 8+.

Where the Edges Come From

Three sources give you most of what you need without asking anyone to write documentation:

Git history. CODEOWNERS, directory structure, and cross-repository references encode ownership and coupling. A weekly parse of git log --follow and git blame tells you which team touched which module last, and whether a module has been silent for a year.

Deployment manifests. Kubernetes Deployment and Service manifests, Helm values, and Terraform state files enumerate what is actually running, which images are deployed, and which secrets each workload mounts. These are ground truth. If it is not in the manifest, it is not in the cluster.

OpenTelemetry service maps. The OTel Collector contrib service_graph connector pairs client and server spans, extracting service-to-service call edges as metrics. Configuring dimensions to include http.request.method, http.response.status_code, and rpc.service gives you typed, observable dependency edges that reflect real traffic rather than stale architecture diagrams.

One tuning note: the default store.ttl: 2s means the connector will miss spans where client and server arrive more than two seconds apart. If you have long-running gRPC calls, raise this to at least 10s.

The Reconciliation Worker

The worker runs as a .NET BackgroundService. On each cycle it pulls edges from each source, compares them to the current graph state, and applies only what has changed: adding new edges, marking stale ones, and routing conflicts to a review queue rather than resolving them unilaterally.

public sealed class GraphReconciliationWorker : BackgroundService
{
    private readonly IGraphSourceAggregator _sources;
    private readonly IGraphRepository _graph;
    private readonly IReviewQueue _reviewQueue;
    private readonly ILogger<GraphReconciliationWorker> _logger;
    private static readonly TimeSpan Interval = TimeSpan.FromHours(4);

    public GraphReconciliationWorker(
        IGraphSourceAggregator sources,
        IGraphRepository graph,
        IReviewQueue reviewQueue,
        ILogger<GraphReconciliationWorker> logger)
    {
        _sources = sources;
        _graph = graph;
        _reviewQueue = reviewQueue;
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        while (!ct.IsCancellationRequested)
        {
            try
            {
                var observed = await _sources.CollectAsync(ct);
                var current  = await _graph.LoadEdgeSnapshotAsync(ct);

                var diff = EdgeDiff.Compute(current, observed);

                await _graph.UpsertEdgesAsync(diff.New, ct);
                await _graph.MarkStaleAsync(diff.Missing, ct);

                foreach (var conflict in diff.Conflicts)
                    await _reviewQueue.EnqueueAsync(conflict, ct);

                _logger.LogInformation(
                    "Reconciliation complete. New={New}, Stale={Stale}, Conflicts={Conflicts}",
                    diff.New.Count, diff.Missing.Count, diff.Conflicts.Count);
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                _logger.LogError(ex, "Reconciliation cycle failed");
            }

            await Task.Delay(Interval, ct);
        }
    }
}

The critical design choice is MarkStaleAsync rather than delete. Deletion is irreversible and loses provenance. A stale node or edge can be queried, reviewed, and either reinstated or permanently removed by a human. Agents are told to treat stale nodes as unverified, not as absent.

Provenance on Every Edge

Every edge written by the reconciliation worker carries provenance properties so you can answer: who said this, when, and how confident were they?

The AuthentiCity knowledge graph (Zenodo, August 2026), spanning 176.8 GiB of Neo4j store across five cities, models this using confidence-weighted ENRICHED_BY edges and explicit source identifiers on each relationship. That pattern scales.

In Cypher:

private const string UpsertEdgeCypher = """
    MERGE (a:Service {id: $sourceId})
    MERGE (b:Service {id: $targetId})
    MERGE (a)-[r:CALLS]->(b)
    ON CREATE SET
        r.firstSeen       = datetime(),
        r.lastConfirmed   = datetime(),
        r.source          = $source,
        r.confidence      = $confidence,
        r.stale           = false,
        r.provenanceRunId = $runId
    ON MATCH SET
        r.lastConfirmed   = datetime(),
        r.source          = $source,
        r.confidence      = $confidence,
        r.stale           = false,
        r.provenanceRunId = $runId
    """;

public async Task UpsertEdgesAsync(IReadOnlyList<ObservedEdge> edges, CancellationToken ct)
{
    await using var session = _driver.AsyncSession();
    foreach (var edge in edges)
    {
        await session.RunAsync(UpsertEdgeCypher, new
        {
            sourceId   = edge.SourceId,
            targetId   = edge.TargetId,
            source     = edge.SourceSystem,   // e.g. "otel", "git", "manifest"
            confidence = edge.Confidence,     // 0.0 to 1.0
            runId      = edge.RunId
        });
    }
}

Note the version pinning. If you are on any Neo4j release between 2025.11 and 2026.01.3, a verified bug in dynamic relationship types with composite multi-property indexes causes MERGE to silently write to the wrong node or issue CREATE operations that fail silently. Fixed in 2026.01.4 (released 10 February 2026). Run the staleness query below and audit your relationship counts if you are upgrading from that window.

Conflict Handling: When Two Sources Disagree

Sources will contradict each other. OTel says Service A calls Service B. The Kubernetes manifest shows Service B was removed last sprint. Git history shows no recent activity in Service B's repository.

The rule is: the automated pass is not allowed to resolve source disagreements alone. It can log, score, and enqueue. It cannot pick a winner.

The conflict score is a weighted combination of source recency and confidence. OTel evidence from the last 24 hours scores higher than a manifest that has not changed in 30 days. But even a high-confidence automated score is not sufficient. Conflicting evidence about a dependency that affects security boundaries or SLA calculations belongs in the review queue, not silently overwritten.

The review queue is a table in your own store (or a simple Azure Service Bus topic). It records the two competing claims, the sources, the timestamps, and the computed scores. A weekly rotation, one engineer for thirty minutes, clears most of it.

Querying for Stale Nodes

The staleness query is the heartbeat check for graph health. Run it as part of every reconciliation cycle and expose it to the agent so it can self-report uncertainty:

private const string StaleEdgeCypher = """
    MATCH (a)-[r]->(b)
    WHERE r.stale = true
       OR r.lastConfirmed < datetime() - duration('P7D')
    RETURN
        a.id          AS source,
        b.id          AS target,
        type(r)       AS relationship,
        r.lastConfirmed AS lastConfirmed,
        r.source      AS evidenceSource
    ORDER BY r.lastConfirmed ASC
    LIMIT 200
    """;

public async Task<IReadOnlyList<StaleEdge>> QueryStaleAsync(CancellationToken ct)
{
    await using var session = _driver.AsyncSession();
    var result = await session.RunAsync(StaleEdgeCypher);
    return await result.ToListAsync(r => new StaleEdge(
        Source:         r["source"].As<string>(),
        Target:         r["target"].As<string>(),
        Relationship:   r["relationship"].As<string>(),
        LastConfirmed:  r["lastConfirmed"].As<LocalDateTime>(),
        EvidenceSource: r["evidenceSource"].As<string>()
    ), ct);
}

Seven days is a reasonable staleness threshold for most systems, long enough to survive a quiet weekend and short enough to catch a decommissioned service before an agent routes a customer query through it.

Honest Limits

The approach works well for what is running and how it is connected. It works poorly for:

Intent and rationale. Git commit messages are inconsistent. OTel can tell you Service A calls Service B 400 times per minute, but not why. That knowledge lives in design documents, ADRs, and conversations. An agent that only sees the graph will confidently answer structural questions and silently miss the "why never do this" annotations that experienced engineers carry in their heads.

Ownership during reorgs. CODEOWNERS files lag by weeks. The window between an org change and the manifest reflecting it is a period where the graph is structurally correct but politically wrong. Agents that route escalations using ownership edges during that window will get it wrong.

Security-sensitive edges. Which service has read access to which secret store is derivable from manifests, but the intended access policy versus the actual policy can diverge. Automated reconciliation will reflect reality; it will not flag the divergence from intent. A human has to compare the graph against the intended IAM policy periodically.

The reconciliation worker is not a replacement for architectural review. It is a floor, a guarantee that the graph is no worse than what your observable signals show. The ceiling still requires human judgment, and the review queue is the mechanism that ensures human judgment gets applied to the cases that need it most.

Practical Checklist Before You Ship

Confirm Neo4j version is 2026.01.4 or later if you use composite indexes on relationship types.
Use Neo4j.Driver 6.2.1. The 4.4 driver is security-only with no new feature backports.
Tune service_graph connector store.ttl above 2s if you have long-running RPC calls.
Verify which APOC procedures you depend on are in Core vs Extended, since they are separate downloads since Neo4j 5.0.
Every automated edge write must carry source, confidence, lastConfirmed, and provenanceRunId.
The review queue is not optional. If you skip it, conflicts get silently resolved by whichever source ran last.

Hybrid Retrieval in C#: Combining Vector Search, Graph Traversal, and Reranking for Agent Memory

Mehdi Mohseni — Wed, 07 Oct 2026 14:18:04 +0000

The Problem with Pure Vector Retrieval

Vector search gives you semantic proximity: chunks that are statistically close to the query in embedding space. What it doesn't give you is relational context: the entities those chunks mention, what those entities connect to, and whether the supporting facts you actually need live two hops away from the seed result. For a stateless Q&A system, that gap is tolerable. For an agent that accumulates memory across sessions, it's a critical flaw.

Part 1 of this series built the graph memory store using Neo4j 2026.06.0 and the Neo4j .NET driver (v5.28.4). This part wires in the vector arm, fuses the two ranked lists with Reciprocal Rank Fusion, expands context via graph traversal, and applies a cross-encoder reranker before assembling a hard-token-budgeted context block. The implementation targets Semantic Kernel v1.80.1 stable APIs, specifically IVectorStore and ITextEmbeddingGenerator, because new feature development has moved to Agent Framework 1.0 (GA April 3, 2026) and you want a migration path.

Pipeline Architecture

The canonical order, validated on real C# codebase retrieval tasks, is:

BM25 retrieval + embedding retrieval run in parallel
Reciprocal Rank Fusion (RRF) to produce a single fused ranked list
Content-hash deduplication before the reranker
Graph expansion from the top-N fused seeds (1 to 2 hops)
Cross-encoder reranking on the deduplicated candidate pool
Token-budget-gated context assembly → answer generation

Steps 1 to 3 keep the graph traversal focused: you're not expanding from all vector results, only from the seeds that survived RRF. This matters because graph traversal fans out exponentially. At 2 hops from 10 seeds on a reasonably connected knowledge graph, you can easily retrieve 400+ nodes. Containing that is the job of the hop budget and the reranker.

Reciprocal Rank Fusion and Deduplication

RRF fuses ranked lists without needing calibrated scores. The formula is:

score(d) = Σ 1 / (k + rank_i(d))

where k = 60 by convention and rank_i is the document's rank in list i. A chunk appearing in both the BM25 list and the vector list gets a naturally boosted fused score, which is the correct behaviour. But before handing anything to the cross-encoder, deduplicate on content hash. Without this you pay the cross-encoder's linear-cost full forward pass twice for the same text, and you burn token budget on duplicate context.

public sealed class HybridRetrievalService
{
    private const int RrfK = 60;
    private const int MaxHops = 2;
    private const int RerankerCandidateCap = 40;
    private const int FinalResultCount = 8;
    private const int MaxContextTokens = 3_200;

    private readonly IVectorStore _vectorStore;
    private readonly ITextEmbeddingGenerator _embedder;
    private readonly IDriver _neo4jDriver;
    private readonly ICrossEncoderReranker _reranker;

    public HybridRetrievalService(
        IVectorStore vectorStore,
        ITextEmbeddingGenerator embedder,
        IDriver neo4jDriver,
        ICrossEncoderReranker reranker)
    {
        _vectorStore = vectorStore;
        _embedder = embedder;
        _neo4jDriver = neo4jDriver;
        _reranker = reranker;
    }

    public async Task<RetrievedContext> RetrieveAsync(
        string query, CancellationToken ct = default)
    {
        // 1. Parallel retrieval
        var embeddingTask = _embedder.GenerateEmbeddingAsync(query, ct);
        var bm25Task = RunBm25Async(query, topK: 20, ct);
        await Task.WhenAll(embeddingTask, bm25Task);

        var vectorResults = await RunVectorSearchAsync(
            await embeddingTask, topK: 20, ct);
        var bm25Results = await bm25Task;

        // 2. RRF fusion
        var fused = ComputeRrf(vectorResults, bm25Results);

        // 3. Deduplication on content hash
        var deduplicated = DeduplicateByHash(fused);

        // 4. Graph expansion from top seeds
        var seeds = deduplicated.Take(10).Select(r => r.NodeId).ToList();
        var expanded = await ExpandGraphAsync(seeds, MaxHops, ct);

        // Merge expanded nodes into candidate pool, re-dedup
        var allCandidates = DeduplicateByHash(
            deduplicated.Concat(expanded)
                        .OrderByDescending(r => r.FusedScore)
                        .Take(RerankerCandidateCap)
                        .ToList());

        // 5. Cross-encoder reranking
        var reranked = await _reranker.RerankAsync(
            query, allCandidates, FinalResultCount, ct);

        // 6. Token-budget assembly
        return AssembleContext(reranked, MaxContextTokens);
    }

    private static List<CandidateChunk> ComputeRrf(
        IList<CandidateChunk> listA,
        IList<CandidateChunk> listB)
    {
        var scores = new Dictionary<string, double>();
        var chunks = new Dictionary<string, CandidateChunk>();

        void Accumulate(IList<CandidateChunk> list)
        {
            for (int i = 0; i < list.Count; i++)
            {
                var chunk = list[i];
                scores.TryGetValue(chunk.ContentHash, out double current);
                scores[chunk.ContentHash] = current + 1.0 / (RrfK + i + 1);
                chunks.TryAdd(chunk.ContentHash, chunk);
            }
        }

        Accumulate(listA);
        Accumulate(listB);

        return scores
            .OrderByDescending(kv => kv.Value)
            .Select(kv =>
            {
                var c = chunks[kv.Key];
                return c with { FusedScore = kv.Value };
            })
            .ToList();
    }

    private static List<CandidateChunk> DeduplicateByHash(
        IEnumerable<CandidateChunk> candidates) =>
        candidates
            .GroupBy(c => c.ContentHash)
            .Select(g => g.OrderByDescending(c => c.FusedScore).First())
            .OrderByDescending(c => c.FusedScore)
            .ToList();
}

Graph Expansion: Hop Budget and Cypher

The ExpandGraphAsync method issues a parameterised Cypher query that walks at most MaxHops relationships from each seed node. Beyond 2 hops, traversal noise reliably outweighs signal for most agent memory question types. Keep MaxHops as a named constant and resist the temptation to make it a user-tunable parameter without also gating it behind the reranker.

private async Task<List<CandidateChunk>> ExpandGraphAsync(
    IList<string> seedNodeIds, int maxHops, CancellationToken ct)
{
    await using var session = _neo4jDriver.AsyncSession();

    // Note: Neo4j 2026.01+ required for filterable_properties in WITH clause.
    // Version-gate any feature that generates server-side WITH predicates.
    var cypher = $"""
        UNWIND $seedIds AS seedId
        MATCH path = (seed {{nodeId: seedId}})-[*1..{maxHops}]-(neighbour)
        WHERE neighbour.embedding IS NOT NULL
          AND neighbour.nodeId <> seedId
        WITH neighbour,
             min(length(path)) AS hopDistance,
             collect(DISTINCT seed.nodeId) AS reachedFrom
        RETURN neighbour.nodeId       AS nodeId,
               neighbour.text         AS text,
               neighbour.contentHash  AS contentHash,
               hopDistance,
               reachedFrom
        ORDER BY hopDistance
        LIMIT 200
        """;

    var result = await session.RunAsync(cypher,
        new { seedIds = seedNodeIds });

    var chunks = new List<CandidateChunk>();
    await foreach (var record in result.ToAsyncEnumerable(ct))
    {
        // Discount score by hop distance so seeds still rank above neighbours
        double hopPenalty = 1.0 / (1 + record["hopDistance"].As<int>());
        chunks.Add(new CandidateChunk(
            NodeId: record["nodeId"].As<string>(),
            Text: record["text"].As<string>(),
            ContentHash: record["contentHash"].As<string>(),
            FusedScore: hopPenalty));
    }

    return chunks;
}

The hop-distance penalty ensures that a direct seed still outranks a 2-hop neighbour with the same textual content when fused scores are close. RRF will then further modulate this before the reranker makes the final call.

Context Assembly with a Hard Token Budget

Simple truncation (take results until you exceed the budget) is fine for homogeneous retrieval. After graph expansion you often have clusters of near-identical chunks (sibling entity nodes, co-authored facts) that eat budget without adding information. A greedy diversity pass is worth the few microseconds it costs.

The AssembleContext method sorts reranked results by score, accumulates tokens, and skips any chunk whose text has more than 0.85 Jaccard overlap with already-included text. It stops early if the score drops below half the top score, which typically cuts 20 to 30 percent of token spend on graph-expanded result sets without meaningful quality loss.

Practical ratios from the field: feed 40 candidates into the cross-encoder, return 8 after reranking (a 5:1 ratio), then budget-gate those 8 down to however many fit in MaxContextTokens. On bge-reranker-v2-m3 with a candidate cap of 25, expect roughly 45 ms p50 / 110 ms p95 added latency over the fused first stage (nDCG@10 ≈ 0.69 vs 0.58 without reranking).

What the Reranker Actually Fixes

The cross-encoder's biggest practical value after graph expansion is sibling-entity pollution. Graph traversal from a seed node about CreateUser reliably surfaces UpdateUser and DeleteUser. They share schema nodes, audit-log nodes, and permission edges. They're semantically adjacent but incorrect for a question specifically about user creation. A cross-encoder running a full forward pass on the query paired with each candidate chunk scores UpdateUser fragments appropriately low. A vector similarity score cannot make this distinction because the embeddings for these functions cluster tightly.

Critical warning: do not assume your cross-encoder will help. The MemArena benchmark found that an MS-MARCO MiniLM reranker applied on top of BM25 RAG dropped answer accuracy below the BM25 baseline by a mean of 20.6 percentage points across five reader models. The damage was reader-family-specific: Llama-3.2-3B dropped 21.8 pp, multiple Qwen readers dropped 17 to 33 pp, while Mistral-7B was essentially unaffected. Measure the reranker against your specific reader model on your domain before shipping it. bge-reranker-v2-m3 and Cohere Rerank are the current recommended defaults, but they are not universally safe.

Measuring the Difference

On a 120-question evaluation set drawn from a real agent memory workload (session recall, entity disambiguation, multi-hop fact retrieval), the pipeline stages compared as follows:

Stage	nDCG@10	Avg tokens consumed	Notes
Vector-only	0.54	1,840	Baseline
BM25 + Vector (RRF)	0.58	1,920	+7% quality, +4% tokens
+ Graph expansion (2 hops)	0.61	2,650	+13% quality, +44% tokens
+ Cross-encoder (top-25)	0.69	2,210	+28% quality, +20% tokens vs baseline
+ Diversity token budget	0.69	1,780	Quality held, tokens below baseline

Graph expansion without reranking costs tokens without the proportional quality gain. The reranker and the diversity budget together reclaim most of that token spend while holding the nDCG improvement. That last row, quality above pure vector at fewer tokens, is the target state.

Migration Path to Agent Framework

Semantic Kernel 1.80.1 receives security patches through at least April 2027, but new retrieval features ship in Agent Framework 1.0. The Neo4j.AgentFramework.GraphRAG package (v0.1.0-preview.2) exposes a HybridCypherRetriever that implements roughly this same pipeline natively as an Agent Framework context provider. If you write your hybrid retrieval service against SK's IVectorStore and ITextEmbeddingGenerator interfaces rather than any SK-specific implementation type, the migration to Agent Framework's equivalent abstractions is a constructor-injection swap, not a rewrite.

Keep MaxHops, RerankerCandidateCap, FinalResultCount, and MaxContextTokens as named constants in a single configuration record. Those four numbers are the primary tuning levers; you will revisit them when you switch reader models or domains, and you want them discoverable.

Edge AI with .NET, Part 4: Time Series That Outlives the Hardware

Mehdi Mohseni — Wed, 07 Oct 2026 14:10:42 +0000

The Data Outlasts the Device

You have a fleet of water and power meters, each running a small .NET edge agent that sends readings every minute. The hardware will fail, be replaced, or be upgraded. The readings must not. If your schema ties a reading to a device serial number rather than a logical meter identity, you lose history the moment you swap hardware. If you store readings in a plain PostgreSQL table, you pay ten times the storage cost and wait ten times as long for rolling-window queries.

TimescaleDB (now shipped by TigerData, renamed from Timescale Inc. on June 17, 2025) solves both the storage and the query problem. Version 2.29.0, released July 28, 2026, adds incremental and concurrent continuous-aggregate refresh, vectorised time_bucket() from 2.26 onward, and and the Hypercore columnstore engine introduced in 2.18. All of them matter for the patterns below.

This article covers the schema, the EF Core mapping, and the anomaly worker. Parts 1 to 3 covered the edge agent; here we focus on what happens after the reading lands in the cloud database.

Schema Design: Survive the Meter Replacement

The first rule: readings reference a logical meter, not a device serial. Keep a meters lookup table with a surrogate UUID, the physical serial, and install/remove timestamps. When hardware is swapped, insert a new meters row, and the old readings keep their original meter_id.

-- Locations and meters
CREATE TABLE locations (
    location_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    address     TEXT NOT NULL
);

CREATE TABLE meters (
    meter_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id  UUID NOT NULL REFERENCES locations(location_id),
    device_serial TEXT NOT NULL,
    meter_type   TEXT NOT NULL CHECK (meter_type IN ('water','power')),
    installed_at TIMESTAMPTZ NOT NULL,
    removed_at   TIMESTAMPTZ
);

-- Raw readings hypertable
CREATE TABLE readings (
    time        TIMESTAMPTZ     NOT NULL,
    meter_id    UUID            NOT NULL,
    value       DOUBLE PRECISION NOT NULL  -- litres or watt-hours
);

SELECT create_hypertable('readings', 'time',
    chunk_time_interval => INTERVAL '7 days');

-- Hypercore columnstore compression after 7 days
SELECT add_columnstore_policy('readings',
    after => INTERVAL '7 days',
    segmentby => ARRAY['meter_id']);

-- Tariff periods for cost calculation
CREATE TABLE tariff_periods (
    tariff_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valid_from  TIMESTAMPTZ NOT NULL,
    valid_until TIMESTAMPTZ,          -- NULL means current tariff
    eur_per_kwh NUMERIC(10,6) NOT NULL
);

A 7-day chunk interval suits medium-frequency meter data (one reading per minute is 1,440 rows per day per meter). Hypercore's columnstore compresses cold chunks by 90 to 95 percent, so a year of data from a hundred meters costs roughly what a week would in plain PostgreSQL.

Continuous Aggregates: Daily and Monthly Rollups

Stack the aggregates. An hourly rollup feeds a daily rollup, which feeds a monthly rollup. Each is its own hypertable, refreshed incrementally. The WITH (timescaledb.continuous) flag and real_time_aggregate option mean your anomaly queries always see the latest raw data without a forced refresh.

-- Hourly rollup
CREATE MATERIALIZED VIEW readings_hourly
WITH (timescaledb.continuous,
      timescaledb.materialized_only = false) AS
SELECT
    time_bucket('1 hour', time)  AS bucket,
    meter_id,
    SUM(value)                   AS total,
    MIN(value)                   AS min_val,
    MAX(value)                   AS max_val,
    COUNT(*)                     AS sample_count
FROM readings
GROUP BY bucket, meter_id
WITH NO DATA;

SELECT add_continuous_aggregate_policy('readings_hourly',
    start_offset  => INTERVAL '2 hours',
    end_offset    => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

-- Daily rollup sourced from hourly
CREATE MATERIALIZED VIEW readings_daily
WITH (timescaledb.continuous,
      timescaledb.materialized_only = false) AS
SELECT
    time_bucket('1 day', bucket)  AS bucket,
    meter_id,
    SUM(total)                    AS total,
    MIN(min_val)                  AS min_val,
    MAX(max_val)                  AS max_val,
    SUM(sample_count)             AS sample_count
FROM readings_hourly
GROUP BY time_bucket('1 day', bucket), meter_id
WITH NO DATA;

SELECT add_continuous_aggregate_policy('readings_daily',
    start_offset  => INTERVAL '2 days',
    end_offset    => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day');

Monthly rollups follow the same pattern using '1 month' and sourcing readings_daily.

EF Core 10 Mapping

EF Core has no native understanding of hypertable DDL. The YC.EntityFrameworkCore.TigerData.TimescaleDB package (targeting EF Core 10 / Npgsql 10, requires TimescaleDB 2.23+) exposes a Fluent API that injects the correct migrationBuilder.Sql(...) calls into generated migrations. For bare projects, execute raw SQL directly in Up().

The Reading entity is straightforward; the important thing is that MeterId is the foreign key, not a serial string:

// Reading.cs
public class Reading
{
    public DateTimeOffset Time     { get; set; }
    public Guid           MeterId  { get; set; }
    public double         Value    { get; set; }

    public Meter Meter { get; set; } = null!;
}

// Meter.cs
public class Meter
{
    public Guid            MeterId      { get; set; }
    public Guid            LocationId   { get; set; }
    public string          DeviceSerial { get; set; } = string.Empty;
    public string          MeterType    { get; set; } = string.Empty;
    public DateTimeOffset  InstalledAt  { get; set; }
    public DateTimeOffset? RemovedAt    { get; set; }

    public Location          Location { get; set; } = null!;
    public ICollection<Reading> Readings { get; set; } = [];
}

// MeterDbContext.cs (relevant excerpt)
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Reading>(e =>
    {
        e.HasKey(r => new { r.Time, r.MeterId });
        e.Property(r => r.Time).HasColumnType("timestamptz");
        e.HasOne(r => r.Meter)
         .WithMany(m => m.Readings)
         .HasForeignKey(r => r.MeterId);
    });

    modelBuilder.Entity<Meter>(e =>
    {
        e.HasKey(m => m.MeterId);
        e.Property(m => m.RemovedAt).HasColumnType("timestamptz");
    });

    // If not using the community package, call the hypertable
    // DDL from the migration's Up() method via migrationBuilder.Sql()
}

Important gotcha: TimescaleDB cannot apply certain schema changes in place on an existing hypertable, it has to recreate the table. Design your schema before production data volumes grow large; adding a column after millions of rows have been ingested is a heavy operation.

Also: the 2.27.x bloom-filter bug caused compressed int2/SMALLINT columns to silently miss matching rows in SELECT queries. The workaround is to drop the affected sparse indexes manually before upgrading. On 2.28+ this is resolved, but avoid SMALLINT on heavily compressed columns until you have confirmed your version.

The Anomaly Worker

A single BackgroundService polls every minute and runs three independent checks:

Leak detection. A water meter with non zero flow for six consecutive hours. Use readings_hourly with real_time_aggregate so no explicit refresh is needed:

public class AnomalyWorker(MeterDbContext db,
                           ILogger<AnomalyWorker> logger,
                           IAlertService alerts)
    : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        using var timer = new PeriodicTimer(TimeSpan.FromMinutes(1));
        while (await timer.WaitForNextTickAsync(ct))
        {
            await CheckLeaksAsync(ct);
            await CheckPowerSpikesAsync(ct);
            await CheckSilentMetersAsync(ct);
        }
    }

    private async Task CheckLeaksAsync(CancellationToken ct)
    {
        var cutoff = DateTimeOffset.UtcNow.AddHours(-6);

        // readings_hourly is a cagg, queried like a normal table
        var leaking = await db.Database
            .SqlQuery<Guid>($"""
                SELECT h.meter_id AS "Value"
                FROM   readings_hourly h
                JOIN   meters m ON m.meter_id = h.meter_id
                WHERE  m.meter_type = 'water'
                  AND  m.removed_at IS NULL
                  AND  h.bucket >= {cutoff}
                  AND  h.min_val > 0
                GROUP  BY h.meter_id
                HAVING COUNT(*) >= 6
                """)
            .ToListAsync(ct);

        foreach (var meterId in leaking)
            await alerts.RaiseAsync(meterId, "LeakDetected", ct);
    }

    private async Task CheckPowerSpikesAsync(CancellationToken ct)
    {
        // 7-day baseline: flag today if today's total > avg + 2*stddev
        var spikes = await db.Database
            .SqlQuery<Guid>($"""
                WITH baseline AS (
                    SELECT meter_id,
                           AVG(total)    AS avg_total,
                           STDDEV(total) AS std_total
                    FROM   readings_daily
                    WHERE  bucket >= NOW() - INTERVAL '8 days'
                      AND  bucket <  NOW() - INTERVAL '1 day'
                    GROUP  BY meter_id
                ),
                today AS (
                    SELECT meter_id, SUM(total) AS day_total
                    FROM   readings_hourly
                    WHERE  bucket >= date_trunc('day', NOW())
                    GROUP  BY meter_id
                )
                SELECT t.meter_id AS "Value"
                FROM   today t
                JOIN   baseline b ON b.meter_id = t.meter_id
                WHERE  t.day_total > b.avg_total + 2 * COALESCE(b.std_total, 0)
                """)
            .ToListAsync(ct);

        foreach (var meterId in spikes)
            await alerts.RaiseAsync(meterId, "PowerSpike", ct);
    }

    private async Task CheckSilentMetersAsync(CancellationToken ct)
    {
        var threshold = DateTimeOffset.UtcNow.AddMinutes(-15);

        var silent = await db.Database
            .SqlQuery<Guid>($"""
                SELECT m.meter_id AS "Value"
                FROM   meters m
                WHERE  m.removed_at IS NULL
                  AND  NOT EXISTS (
                    SELECT 1 FROM readings r
                    WHERE  r.meter_id = m.meter_id
                      AND  r.time    >= {threshold}
                  )
                """)
            .ToListAsync(ct);

        foreach (var meterId in silent)
            await alerts.RaiseAsync(meterId, "SilentMeter", ct);
    }
}

The silent-meter check queries the raw hypertable rather than an aggregate, because chunk pruning on r.time >= threshold limits the scan to one or two recent chunks regardless of how many years of history exist.

Cost Calculation With Tariff History

Tariffs change. A reading from 2023 must be costed at the 2023 rate, not today's. Join through tariff_periods using a lateral range condition:

SELECT
    d.meter_id,
    d.bucket,
    d.total / 1000.0                   AS kwh,
    tp.eur_per_kwh,
    (d.total / 1000.0) * tp.eur_per_kwh AS eur_cost
FROM readings_daily d
JOIN tariff_periods tp
  ON  d.bucket >= tp.valid_from
  AND (tp.valid_until IS NULL OR d.bucket < tp.valid_until)
WHERE d.meter_id = @meterId
ORDER BY d.bucket;

Store valid_until = NULL for the active tariff. When a new tariff starts, set valid_until on the previous row to the change timestamp; insert the new row. That gives you an append only audit trail, so you never lose what a customer paid in an earlier period.

What to Watch

TimescaleDB 2.29 removed PostgreSQL 15 support; if your managed database is on PG15, you are on the 2.28.x train until you upgrade the engine. The Hypercore columnstore is now the recommended default for new installs, so enable it from day one. Retrofitting compression onto a large existing hypertable is possible but slow.

The community EF Core packages (both YC.EntityFrameworkCore.TigerData.TimescaleDB and CmdScale.EntityFrameworkCore.TimescaleDB) are thin wrappers that emit raw SQL migrations. They save repetitive boilerplate, but you should read the generated migration SQL before applying it, especially the compress_segmentby and chunk_time_interval settings, which are hard to change once data is flowing.

Edge AI with .NET, Part 3: Vision OCR You Can Actually Trust

Mehdi Mohseni — Wed, 07 Oct 2026 14:03:28 +0000

The Gap Between Demo and Production

Reading an analog power meter with a vision model takes about twenty lines of C#. Getting a reading you can actually write to a time-series database takes considerably more. The demo is easy because you pick a clean, well-lit image and the model returns the right number. Production is hard because real meters have glare, dirty lenses and half turned digits, and the model never says "I am not sure." It confidently returns a plausible number instead.

Research confirms this is not a theoretical concern. A May 2026 study (arxiv:2605.16409) documented that VLMs including GPT-class models frequently hallucinate text, omit tokens, or autocomplete partially visible text under degraded visual conditions. The specific failure mode for meter reading is autoregressive completion: the model sees enough of a digit shape to predict what it statistically should be and returns that prediction without flagging uncertainty. A July 2025 EPFL benchmark (arxiv:2507.01955) covering GPT-4o, Gemini 2.0 Flash, Claude 3.5 Sonnet, and others found hallucinated objects and input-output misalignment across all tested models.

The engineering response is not to abandon vision OCR. It is to build a validation layer that catches the failures before they corrupt your data.

Prompt Design and Image Detail

What the Prompt Needs to Say

For digit reading, vague prompts produce vague results. The prompt needs to name the display type, specify the expected digit count, and explicitly instruct the model to report failure rather than guess:

You are reading a utility meter display. The display shows exactly 5 numeric digits 
with no decimal point. Return ONLY the 5-digit reading as a JSON object with a single 
field \"reading\". If any digit is unclear, partially obscured, or you are not confident, 
return {\"reading\": null} instead of guessing. Do not infer or complete digits.

The null-on-uncertainty instruction matters. Without it, the model defaults to completion mode. With it, you still get hallucinations under severe degradation, but the rate drops meaningfully.

Image Detail Level and What It Costs

This is the cost decision that matters most before you write any code. The OpenAI vision API exposes two explicit detail levels:

detail: low. Always 85 tokens flat, image downsampled to 512×512. Usable for dominant colour or shape detection. Insufficient for small numerals on a meter display.
detail: high. 85 base tokens plus 170 tokens per 512×512 tile required to tile the image. For gpt-6-astra, the tokenizer applies a 2,500-patch budget with a 1.2× multiplier. A 1024×1024 image costs ~1,229 tokens; a 2048×2048 image is capped at 1,600×1,600 and costs ~3,000 tokens.

At April 2026 pricing ($0.015/1K input tokens), a 2048×2048 meter image costs roughly $0.045 per read. At 96 reads per day per meter, that is about $4.32 per meter per day, which matters at scale. The practical answer is to resize images server-side to 1024×1024 before sending, cutting per-read token cost by more than half while retaining enough resolution for 5-digit OCR.

Critically, the vision_detail parameter defaults to auto if omitted. Do not rely on auto in production. Pin it explicitly or the model selects detail level based on image size and you lose cost predictability.

Note: GPT-4.5 costs $75/M input tokens and is not a viable option for a high-volume pipeline. o4-mini was retired from the API on February 13, 2026. Use gpt-6-astra, which is the reference model in the official OpenAI .NET docs as of June 30, 2026.

The Vision Call in C

The OpenAI .NET SDK v2 uses the OpenAI.Chat namespace. The pattern is: construct a ChatClient, build a UserChatMessage from a ChatMessageContentPart[] array, call CompleteChatAsync.

using OpenAI.Chat;
using System.Text.Json;

public sealed class MeterVisionClient
{
    private readonly ChatClient _client;

    public MeterVisionClient(string apiKey)
    {
        _client = new ChatClient("gpt-6-astra", apiKey);
    }

    public async Task<string?> ReadMeterAsync(
        string imagePath,
        CancellationToken ct = default)
    {
        byte[] imageBytes = await File.ReadAllBytesAsync(imagePath, ct);
        var imageData = BinaryData.FromBytes(imageBytes);

        var systemPrompt = ChatMessageContentPart.CreateTextPart(
            "You are reading a utility meter display. The display shows exactly 5 " +
            "numeric digits with no decimal point. Return ONLY a JSON object with a " +
            "single field \"reading\". If any digit is unclear or you are not confident, " +
            "return {\"reading\": null} instead of guessing. Do not infer or complete digits.");

        var imagePart = ChatMessageContentPart.CreateImagePart(
            imageData,
            "image/png",
            ChatImageDetailLevel.High);  // explicit, never rely on auto

        var message = new UserChatMessage(
            new[] { systemPrompt, imagePart });

        ChatCompletion response = await _client.CompleteChatAsync(
            new[] { message },
            cancellationToken: ct);

        string rawContent = response.Content[0].Text;

        // Parse the JSON response safely
        using JsonDocument doc = JsonDocument.Parse(rawContent);
        if (doc.RootElement.TryGetProperty("reading", out JsonElement readingEl)
            && readingEl.ValueKind != JsonValueKind.Null)
        {
            return readingEl.GetString();
        }

        return null; // model reported uncertainty
    }
}

A few things worth noting: ChatImageDetailLevel.High is the enum member, do not pass a raw string. The image MIME type must match the actual encoding; sending a JPEG with "image/png" causes silent model degradation. If you are fetching images from a camera endpoint over HTTP, replace File.ReadAllBytesAsync with HttpClient.GetByteArrayAsync.

The Validation Layer

A raw model response, even a non null one, never goes straight into your data store. Every reading passes through four gates. Any failure routes to quarantine.

Gate 1: Digit Count

The model returns a string. Validate its length against the known display width, check it is all numeric, and reject leading zeros where the meter format prohibits them.

Gate 2: Monotonicity

Utility meters are cumulative. A reading lower than the last stored value is a hard failure. No exceptions. This single rule eliminates a large class of confident hallucinations.

Gate 3: Rate-of-Change Bounds

Compare the delta against a physical maximum. A domestic power meter cannot accumulate more than, say, 10 kWh between two 15-minute reads. Anything outside the plausible envelope is flagged regardless of direction.

Gate 4: Quarantine and Human Review

Any reading that fails one or more gates is written to a quarantine store, not the time-series database. The quarantine record includes the original image path, the raw model response string, the previously accepted reading, and the name of the rule that failed.

public sealed record MeterReading(string RawValue, decimal ParsedValue, DateTimeOffset Timestamp);

public sealed record QuarantineRecord(
    string ImagePath,
    string RawModelResponse,
    MeterReading? PreviousReading,
    string FailedRule,
    DateTimeOffset QuarantinedAt);

public sealed class MeterReadingValidator
{
    private const int ExpectedDigitCount = 5;
    private const decimal MaxDeltaPerInterval = 10m; // kWh per 15-minute window

    public ValidationResult Validate(
        string? rawReading,
        MeterReading? previous,
        DateTimeOffset readingTime)
    {
        if (rawReading is null)
            return ValidationResult.Quarantine("ModelReportedUncertainty");

        // Gate 1: digit count and format
        if (rawReading.Length != ExpectedDigitCount || !rawReading.All(char.IsAsciiDigit))
            return ValidationResult.Quarantine("DigitFormatInvalid");

        if (!decimal.TryParse(rawReading, out decimal parsed))
            return ValidationResult.Quarantine("ParseFailure");

        if (previous is null)
            return ValidationResult.Accept(parsed); // first reading, no history to compare

        // Gate 2: monotonicity
        if (parsed < previous.ParsedValue)
            return ValidationResult.Quarantine("MonotonicityViolation");

        // Gate 3: rate-of-change
        decimal delta = parsed - previous.ParsedValue;
        if (delta > MaxDeltaPerInterval)
            return ValidationResult.Quarantine("RateOfChangeTooHigh");

        return ValidationResult.Accept(parsed);
    }
}

public sealed class ValidationResult
{
    public bool IsAccepted { get; private init; }
    public decimal? AcceptedValue { get; private init; }
    public string? FailedRule { get; private init; }

    public static ValidationResult Accept(decimal value) =>
        new() { IsAccepted = true, AcceptedValue = value };

    public static ValidationResult Quarantine(string rule) =>
        new() { IsAccepted = false, FailedRule = rule };
}

The FailedRule string is what operators see in the review queue. Specific names like MonotonicityViolation make triage fast. A review record with the original image attached is what makes the review useful. Without it an operator cannot tell whether the failure was model error, a lens problem, or a genuine meter fault.

Operational Notes

Resize before sending. A 4096×512 panoramic image costs approximately 2,458 tokens at detail: high. Most meter displays photograph well at 1024×1024 and that halves your cost versus sending full-resolution camera output.

Log raw model responses. Even accepted readings should retain the raw JSON string in an audit column. When a hallucination slips through (it will, eventually), you need the evidence to distinguish model error from a genuine meter anomaly.

Tune rate bounds per meter class. A 15-minute window and 10 kWh ceiling suits a domestic installation. Industrial meters warrant different bounds, and the validation layer should accept those as configuration rather than constants.

Quarantine rate is a signal. If more than a few percent of reads hit quarantine for a specific meter, the problem is usually physical, a dirty lens, water ingress, or a mounting angle that creates constant glare, not model quality. Surface the quarantine rate as a health metric per device.

Vision OCR for meter reading is viable in production. The failure modes are real and documented, but they are bounded and catchable. The validation layer is not optional overhead; it is the feature that turns a demo into a system you can operate.

NGB Platform v3.1.0: Attachments & Notes

NGB Platform — Wed, 07 Oct 2026 13:50:21 +0000

NGB Platform v3.1.0 is out, introducing Attachments & Notes — a new platform-level capability for managing files and notes associated with business objects.

The implementation follows an important architectural principle in NGB: adding a file or editing a note should not modify the underlying business document.

Why independent lifecycle matters

In document-driven business applications, posting a document can produce operational, reference, or accounting effects.

But what happens when someone needs to attach a supporting PDF or add a note to an already posted document?

Those operations shouldn't require unposting the document, modifying its version, or recalculating its business effects.

In NGB, Attachments and Notes have their own lifecycle, completely independent of the parent object's posting state and business effects.

What's included in v3.1.0

Attachments: Upload, download, and mark files for deletion.
Notes: Create, edit, and logically delete plain-text notes.
Audit Log integration: Track changes, review previous note content, and access retained attachments, even after logical deletion.
Permissions: Separate access controls for attachments and notes.
Platform-wide support: Available for catalogs, documents, and General Journal Entries.

The same capabilities work across all four NGB verticals: Property Management, Trade, Agency Billing, and CRM.

A few implementation details

Attachments use MinIO object storage, with short-lived presigned URLs for direct file transfers.

Notes are stored separately and use their own versioning for concurrency control.

Deletion is logical. Content disappears from active lists, but the audit history is preserved. Completed attachments remain available through the Audit Log with appropriate permissions.

Neither capability changes the parent object's version, posting state, accounting entries, or register movements.

Short demo

Here's a quick walkthrough of the functionality in action:

NGB is an open-source platform built with .NET 10 and PostgreSQL for developing document-driven business applications.

Source code: https://github.com/ngbplatform/NGB

Website: https://ngbplatform.com

Documentation: https://docs.ngbplatform.com

What Happens After the Commit?

Rodrigo de Oliveira — Wed, 07 Oct 2026 13:01:00 +0000

Saving a record to a database is relatively easy.

We have mature tools for that problem. We open a transaction, change some state, commit it, and roll everything back if something fails before the commit.

Things become much more interesting when the database commit is not the end of the operation.

Imagine a financial transaction. Once it is persisted, another service needs to know about it so it can update a balance projection. Maybe additional consumers will react to the same event later.

The first implementation that comes to mind seems perfectly reasonable:

save to database
publish event

But there is an uncomfortable question between those two lines:

What if the database commits successfully and the message broker becomes unavailable immediately afterward?

The transaction exists.

The event does not.

And now two parts of the system are telling different stories.

This is one of the problems I wanted to explore in poc-arquitetura, a repository I use as an executable software architecture laboratory.

The project uses .NET, PostgreSQL, Kafka, Keycloak, OpenTelemetry, k6, and a few other tools. But the goal has never been to collect technologies or architectural patterns.

What interests me is what happens when these patterns have to work together.

Outbox solves one problem and introduces new responsibilities. At-least-once delivery forces us to think about idempotency. Eventual consistency changes how we reason about reads. A Saga requires compensation. Retry needs boundaries. Messaging requires observability beyond HTTP.

That simple question — what happens after the commit? — opens a much broader discussion about distributed systems.

rodri-oliveira-dev / poc-arquitetura

.NET architecture PoC for distributed services with CQRS, Kafka, PostgreSQL, Outbox, DLQ, observability and CI quality gates.

poc-arquitetura

POC educacional de microserviços em .NET para estudar arquitetura de software com código real: Clean Architecture, DDD, PostgreSQL, Kafka, Outbox, Inbox, JWT/JWKS com Keycloak, observabilidade, segurança, contratos e testes automatizados.

Ela demonstra um problema comum em sistemas financeiros: registrar fatos de forma transacional, publicar eventos com confiabilidade, projetar saldos em outro serviço e operar falhas sem esconder consistência eventual. O repositório também mostra contextos de identidade, transferência, pagamento externo e auditoria funcional para exercitar trade-offs de integração.

Este projeto é útil para:

quem está aprendendo arquitetura e quer ver os conceitos aplicados;
desenvolvedores .NET que querem executar, testar e alterar uma stack local;
arquitetos que querem avaliar decisões, limites e riscos;
avaliadores técnicos que querem entender a proposta rapidamente.

Visão geral

flowchart LR
    Client[Cliente ou teste] --> Keycloak[Keycloak OIDC]
    Client --> LedgerApi[LedgerService.Api]
    Client --> BalanceApi[BalanceService.Api]
    Client --> TransferApi[TransferService.Api]
    Client --> PaymentApi[PaymentService.Api]
    Client --> IdentityApi[IdentityService.Api]
    Client --> AuditApi[AuditService.Api]
    LedgerApi -->

…

View on GitHub

Start by separating facts from projections

Two bounded contexts are especially important in this example.

LedgerService owns financial facts.

If a financial transaction happened, the Ledger is where that fact should be recorded.

BalanceService has a different responsibility. It maintains a projection optimized for balance queries.

At a high level, the architecture looks like this:

flowchart LR
    Client[Client] --> LedgerApi[Ledger API]

    LedgerApi --> LedgerDb[(PostgreSQL<br/>Ledger)]

    LedgerDb --> LedgerWorker[Ledger Worker]
    LedgerWorker --> Kafka[(Kafka)]

    Kafka --> BalanceWorker[Balance Worker]
    BalanceWorker --> BalanceDb[(PostgreSQL<br/>Balance)]

    Client --> BalanceApi[Balance API]
    BalanceApi --> BalanceDb

A transaction and a balance are not the same thing.

The transaction is a fact: something happened.

The balance is a model derived from those facts.

Could everything live in a single application and database? Absolutely. Depending on the system, that may even be the better architecture.

But this laboratory deliberately separates those responsibilities so that the consequences of the decision become visible.

One consequence appears immediately.

The balance is no longer updated inside the same database transaction as the Ledger.

We have entered the world of eventual consistency.

That does not mean the system is simply inconsistent or incorrect. It means there is a period during which the Ledger already knows about a transaction while the Balance projection has not processed it yet.

That window is not an accident.

It is part of the architecture.

The real problem is not Kafka. It is the dual write

Suppose the Ledger does something like this:

sequenceDiagram
    participant C as Client
    participant L as Ledger API
    participant DB as PostgreSQL
    participant K as Kafka

    C->>L: Create transaction
    L->>DB: INSERT LedgerEntry
    DB-->>L: COMMIT
    L->>K: Publish event
    K-->>L: Acknowledged
    L-->>C: 201 Created

    Note over L,K: What happens if the process<br/>fails after COMMIT but before publish?

When everything works, this looks fine.

Now imagine that the database commit succeeds but the process crashes before Kafka acknowledges the event.

The Ledger contains the transaction.

The event may never be published.

Publishing first and saving afterward does not solve the problem either. It simply reverses it: we can now publish an event for a transaction that later fails to commit.

This is the dual-write problem.

We need to update two independent resources — the database and the broker — and would like them to behave as if they belonged to one atomic transaction.

One option would be some form of distributed transaction.

For this project, I chose a different approach: accept that PostgreSQL and Kafka have independent lifecycles and make the intent to publish durable.

That is where the Transactional Outbox pattern becomes useful.

Outbox: do not publish now, persist the intent to publish

Instead of saving a transaction and immediately depending on Kafka, the Ledger stores both the financial fact and an Outbox message inside the same PostgreSQL transaction.

sequenceDiagram
    participant C as Client
    participant L as Ledger API
    participant DB as PostgreSQL
    participant W as Ledger Worker
    participant K as Kafka

    C->>L: Create transaction

    rect rgb(235, 235, 235)
        L->>DB: BEGIN
        L->>DB: INSERT LedgerEntry
        L->>DB: INSERT OutboxMessage
        L->>DB: COMMIT
    end

    L-->>C: Transaction confirmed

    W->>DB: Fetch pending messages
    DB-->>W: OutboxMessage
    W->>K: Publish LedgerEntryCreated
    K-->>W: Acknowledged
    W->>DB: Mark as processed

Either both records are persisted or neither is.

The HTTP request no longer needs Kafka to be available in order to preserve the intent to publish the event.

A separate worker polls pending Outbox messages and publishes them.

After Kafka confirms publication, the worker updates the Outbox entry.

If Kafka is unavailable, the financial transaction still exists together with durable information that an event still needs to be published.

If the worker crashes, processing can resume later.

This small architectural change has an important effect.

The failure is no longer a tiny invisible window between two independent writes.

It becomes state that the system can observe and recover from.

That, to me, is one of the most useful ways to understand Outbox.

It does not magically turn Kafka and PostgreSQL into a single transaction.

It does not guarantee exactly-once processing.

It turns an otherwise difficult-to-recover failure into something explicitly represented in the system.

Recoverable systems are usually far more interesting than systems designed around the assumption that failures will not happen.

Preventing message loss creates another problem: duplicates

There is a consequence to this design.

Imagine the worker publishes an event successfully, but crashes before marking the Outbox message as processed.

When it restarts, the same message may be published again.

sequenceDiagram
    participant O as Outbox
    participant W as Ledger Worker
    participant K as Kafka
    participant B as Balance Worker
    participant DB as Balance DB

    O->>W: Pending message
    W->>K: Publish event
    K-->>W: Success

    Note over W: Worker crashes before<br/>marking the message as processed

    O->>W: Same message again
    W->>K: Republish event

    K->>B: Event
    B->>DB: Was this event_id processed?
    DB-->>B: No
    B->>DB: Update projection and store event_id

    K->>B: Duplicate event
    B->>DB: Was this event_id processed?
    DB-->>B: Yes

    Note over B: Duplicate safely ignored

This is not necessarily a bug.

It is a normal consequence of at-least-once delivery.

The responsibility now moves to the consumer.

BalanceService records processed event identifiers so the same event does not modify the projection twice.

That is idempotency becoming part of the architecture.

Retries are often presented as generic resilience configuration: retry three times, use exponential backoff, done.

But retry is also a business decision.

If executing the same operation twice produces two different side effects, a retry may cause more damage than the original failure.

Before asking:

How many times should we retry?

I think a better question is:

What happens if we execute this operation again?

That question tends to uncover much more important design problems.

Eventual consistency has to exist outside the diagram too

Once Ledger and Balance are separated, this state becomes perfectly valid:

Ledger:
Transaction exists.

Balance:
Transaction has not been projected yet.

There is nothing inherently wrong with that.

The problem appears when the architecture is asynchronous but the rest of the product behaves as though every read must immediately reflect every write.

Different domains solve this in different ways.

Some operations can expose a processing state. Some critical queries may need to consult the source of truth. Some applications can simply tolerate a small delay before projections become visible.

There is no universal answer.

The important point is that eventual consistency should be a known business and architectural property, not an accidental side effect of adding Kafka.

Events are APIs too

Once services depend on events, another problem eventually appears:

contracts change.

The project includes an evolution from events such as:

LedgerEntryCreated.v1
LedgerEntryCreated.v2

During a migration period, consumers may need to understand both versions.

That forces us to treat event schemas as real integration contracts.

We already think carefully about HTTP APIs: OpenAPI definitions, breaking changes, versioning, client compatibility.

Events deserve similar care, perhaps even more.

An HTTP response usually exists only during a request.

An event may remain in a Kafka topic, a dead-letter queue, or a replay mechanism long after the application version that originally created it has disappeared.

That changes how we think about compatibility.

Ordering deserves attention too.

Kafka guarantees ordering within a partition, not universal ordering across the entire system.

The message key therefore becomes an architectural decision because it influences which events share a partition and which operations can be processed in parallel.

Details that look small in producer code can have significant consequences for correctness and throughput.

Retry cannot be the final answer

Some failures are temporary.

A broker can become unavailable. A network request can time out. A dependency may take longer than expected to respond.

Retry with backoff makes sense in those cases.

But a structurally invalid event will still be invalid ten seconds later.

An incompatible schema will not suddenly become compatible on attempt number 47.

Retrying forever simply turns one bad message into a permanent consumer of CPU, logs, and operational attention.

That is where a Dead Letter Queue, or DLQ, becomes useful.

A useful DLQ should preserve enough information for investigation and recovery: the original payload, event type, failure classification, source information, and correlation metadata.

But putting something in a DLQ is only half the solution.

The more important question is:

What happens next?

Can the message be safely replayed?

Was the root cause fixed?

Can a projection be rebuilt?

Should the message be discarded?

Does the operation require manual review?

The project explores requeue, replay, and projection rebuild scenarios because recovery is part of the architecture too.

A phrase I keep coming back to is:

A DLQ without a recovery strategy is just a distributed archive of unresolved problems.

Transfers make distributed failures much easier to see

Updating a read projection is relatively straightforward compared with an operation that produces multiple distributed side effects.

Consider a transfer.

At a simplified level, we need to create a debit and then a credit.

Inside one local database transaction, ACID properties solve a lot of problems for us.

Across independent components, those guarantees disappear.

The project models this flow as an orchestrated Saga:

flowchart TD
    A[Transfer Requested] --> B[Create Debit]

    B -->|Success| C[Create Credit]
    B -->|Failure| F[Transfer Failed]

    C -->|Success| D[Transfer Completed]
    C -->|Failure| E[Compensate Debit]

    E --> G[Record Reversal]
    G --> F

This makes something very explicit:

when we distribute an operation, we also distribute its failure modes.

If the debit succeeds and the credit fails, there is no global ROLLBACK statement capable of reversing time across independent services.

We have to define what compensation means.

In financial domains, compensation often should not erase the original fact. A reversal can instead create a new fact that neutralizes the previous one while preserving the history of what happened.

A Saga does not recreate ACID across services.

It models the states and compensating actions required because that local transaction boundary no longer exists.

That distinction is important.

Circuit breakers do not hide failures. They control them.

Another scenario in the laboratory intentionally takes LedgerService offline while the transfer worker is still running.

Without protection, the worker can keep calling a dependency that is already known to be unavailable.

Retries can make this even worse by multiplying calls against an unhealthy service.

A Circuit Breaker changes that behavior.

stateDiagram-v2
    [*] --> Closed

    Closed: Requests flow normally
    Open: Requests fail fast
    HalfOpen: Limited probe requests

    Closed --> Open: Consecutive failures
    Open --> HalfOpen: Wait period expires
    HalfOpen --> Closed: Dependency recovered
    HalfOpen --> Open: Dependency still unhealthy

When the circuit is open, the system has not somehow recovered.

It has acknowledged the failure and chosen not to waste resources repeatedly exercising the same broken dependency.

After a configured interval, the breaker allows a controlled probe through the half-open state.

If that succeeds, traffic can resume.

This leads to another principle that I think is worth emphasizing:

Resilience does not mean making failures invisible.

Resilience means making failure behavior predictable.

Observability becomes much more important after leaving HTTP

Following a single HTTP request through logs is usually manageable.

Following an asynchronous workflow across several processes is very different.

A single operation may travel through:

flowchart LR
    Request[HTTP Request]
        --> API[Ledger API]
        --> DB[(PostgreSQL)]
        --> Outbox[Outbox]
        --> Worker[Ledger Worker]
        --> Kafka[(Kafka)]
        --> Consumer[Balance Worker]
        --> Projection[(Balance DB)]

    Request -. Correlation ID .-> API
    API -. Trace Context .-> Outbox
    Outbox -. traceparent .-> Worker
    Worker -. Trace Context .-> Kafka
    Kafka -. traceparent .-> Consumer

If every component produces unrelated logs, diagnosing a problem becomes timestamp archaeology.

The project propagates correlation_id, and when OpenTelemetry is enabled, W3C tracing context such as traceparent and tracestate can also travel through the Outbox and Kafka messages.

The observability side can be viewed separately:

flowchart LR
    Client[Client] --> API[Ledger API]
    API --> DB[(PostgreSQL)]
    DB --> Outbox[Outbox]
    Outbox --> Worker[Ledger Worker]
    Worker --> Kafka[(Kafka)]
    Kafka --> Consumer[Balance Worker]
    Consumer --> Balance[(Balance DB)]

    API -.-> OTEL[OpenTelemetry]
    Worker -.-> OTEL
    Consumer -.-> OTEL

    OTEL --> Traces[Traces]
    OTEL --> Metrics[Metrics]

    API -. Logs .-> Logs[Centralized Logs]
    Worker -. Logs .-> Logs
    Consumer -. Logs .-> Logs

The local stack includes OpenTelemetry, Jaeger, Prometheus, Grafana, Loki, and supporting components.

But the tools are not the important part.

The important part is the questions they allow us to answer.

The API is healthy, but is the Outbox backlog growing?

Is the producer failing?

Is consumer processing slowing down?

Are duplicates increasing?

Has the DLQ started receiving messages?

Which original HTTP request produced the event that failed several minutes later in another process?

Once asynchronous processing becomes central to the system, an HTTP health endpoint tells only a very small part of the story.

Test the architecture, not only the classes

A system like this can have excellent unit-test coverage and still fail exactly where the interesting risks are: between components.

That is why the laboratory also contains integration tests and k6 scenarios that exercise complete workflows.

One scenario validates:

Ledger
  -> Outbox
  -> Kafka
  -> Balance

Another exercises the complete Transfer Saga.

There is also a resilience scenario in which the Ledger dependency is deliberately stopped so the Circuit Breaker's behavior can be observed during failure and recovery.

I am careful not to describe these tests as production-scale benchmarks.

They run in a controlled local environment.

Their latency thresholds are regression guardrails, not production SLOs.

Running 50 requests per second on Docker Compose does not prove that an architecture can operate at banking scale.

Real capacity depends on infrastructure, partitions, database behavior, network topology, autoscaling, workload characteristics, failure modes, and many other variables.

The tests prove something narrower, but still valuable:

the architectural behavior remains testable under concurrency and controlled failure conditions.

Sometimes the best architecture test is not checking whether a certain method was called.

It is turning a dependency off and observing what the system actually does.

This is a laboratory, not a production reference architecture

Projects that demonstrate microservices, Kafka, Outbox, Sagas, and observability can easily give the impression that they represent a universal production blueprint.

This one does not.

poc-arquitetura is intentionally a laboratory for experimenting with decisions.

A real production environment would still need serious work around secrets management, workload identity, high availability, Kafka capacity and replication, disaster recovery, network security, production SLOs, infrastructure topology, deployment strategy, and many domain-specific controls.

That distinction matters.

Software architecture is not the process of fitting as many patterns as possible into a diagram.

It is the process of choosing mechanisms that are proportional to the problems the system actually has.

Quick takeaways

The database commit is often only the beginning of a distributed workflow.

Transactional Outbox does not make PostgreSQL and Kafka a single transaction; it makes the intent to publish durable.

At-least-once delivery means duplicates are expected, which makes idempotency a correctness requirement rather than a nice optimization.

Eventual consistency has to be understood by the product, not hidden behind a message broker.

Retries require operations that are safe to repeat. DLQs require recovery procedures. Sagas make states and compensations explicit when a local transaction is no longer available.

Circuit Breakers control failures instead of pretending those failures disappeared.

And observability has to cross the same boundaries that events cross.

If I had to reduce the whole experiment to one idea, it would be this:

In distributed systems, we do not design only the successful path. We also design how failures are detected, understood, and recovered.

Where to go next

For a broader view of .NET microservice architecture, Microsoft's “.NET Microservices: Architecture for Containerized .NET Applications” is a useful starting point.

For Transactional Outbox, Saga, Idempotent Consumer, and related distributed-system patterns, Chris Richardson's Microservices Patterns and the Microservices.io pattern catalog are excellent references.

For Kafka, it is worth going beyond basic producer/consumer tutorials and studying the official material on partitions, consumer groups, offsets, ordering, delivery semantics, and transactions.

For distributed tracing and context propagation, the OpenTelemetry documentation provides a vendor-neutral mental model for traces, spans, metrics, baggage, and propagation.

For load and resilience testing, Grafana k6 is approachable enough to start small while still supporting serious workloads.

And if you want to understand the deeper ideas behind transactions, replication, streams, partitioning, and distributed data, Designing Data-Intensive Applications, by Martin Kleppmann, is still one of the books I would put near the top of the list.

The repository behind this article contains the source code, ADRs, event contracts, architecture documentation, tests, and failure scenarios discussed here:

rodri-oliveira-dev / poc-arquitetura

.NET architecture PoC for distributed services with CQRS, Kafka, PostgreSQL, Outbox, DLQ, observability and CI quality gates.

poc-arquitetura

Este projeto é útil para:

quem está aprendendo arquitetura e quer ver os conceitos aplicados;
desenvolvedores .NET que querem executar, testar e alterar uma stack local;
arquitetos que querem avaliar decisões, limites e riscos;
avaliadores técnicos que querem entender a proposta rapidamente.

Visão geral

flowchart LR
    Client[Cliente ou teste] --> Keycloak[Keycloak OIDC]
    Client --> LedgerApi[LedgerService.Api]
    Client --> BalanceApi[BalanceService.Api]
    Client --> TransferApi[TransferService.Api]
    Client --> PaymentApi[PaymentService.Api]
    Client --> IdentityApi[IdentityService.Api]
    Client --> AuditApi[AuditService.Api]
    LedgerApi -->

…

View on GitHub

There is still plenty I want to experiment with.

And that is probably the part I enjoy most about this kind of architecture work: once failure stops being treated as an exceptional event and becomes part of the design, the questions get much more interesting.

Edge AI with .NET, Part 1: Integrating a 1975 Power Meter That Has No API

Mehdi Mohseni — Wed, 07 Oct 2026 12:52:29 +0000

The Problem: A Meter That Predates APIs by Half a Century

The power meter in the basement counts 98,697 kWh. It has done so since 1975, behind a plastic cover, with two rotating dials and a red Ferraris disc spinning at a rate proportional to current draw. It is accurate. It is reliable. And it offers absolutely nothing a software system can talk to.

This is not an unusual situation in German housing stock. The Marktstammdatenregister rollout of smart meters has been slow, legally contested, and largely limited to new construction or high-consumption sites. Older residential meters, the mechanical Ferraris type, have no optical interface in their standard consumer form, no pulse output, and no infrared port. The landlord prohibits modification. You cannot clamp on a pulse counter. You cannot replace the meter without utility approval.

The integration layer, therefore, becomes a camera.

This article walks through the decision and the architecture: why camera OCR is the right call, how the M5Stack Timer Camera X handles the sensing side, and specifically how the .NET 10 ingestion service is designed so that a battery-powered device with a hard timeout never loses a reading to server-side latency.

Why OCR Won the Design Decision

Before building anything, the alternatives deserve a real evaluation:

Smart meter swap: requires utility coordination, landlord approval, and a wait time measured in years in most German municipalities.
Ferraris disc pulse counting: the disc has a reflective mark, but consumer-grade Ferraris meters don't expose an optical sensor port. Clamping a photodiode to the outside of the plastic cover is mechanically unreliable and landlord-prohibited.
Current clamp (CT sensor): measures power draw in real time but gives you watts, not the cumulative kWh figure the meter itself tracks. Integrating over time introduces drift, and the clamp still needs physical installation.
Manual logging: a human opens an app and types numbers. Error-prone, forgotten, useless for sub-hourly resolution.

Camera OCR is the only path that is non-invasive, non-destructive, and completely reversible. You mount a camera with a suction cup or a 3D-printed bracket, point it at the digit display, and let the server handle the interpretation. The meter itself is untouched.

Architecture Overview

The stack has four components:

M5Stack Timer Camera X. An ESP32 based camera module with a 3-megapixel OV3660 sensor, 8 MB PSRAM, a 140 mAh internal battery, and a BM8563 RTC that generates a wake signal on a schedule. It sleeps at under 10 μA, wakes, captures a JPEG, POSTs it over Wi-Fi, then returns to deep sleep.
.NET 10 minimal API ingestion service. Receives the multipart upload, authenticates, stores the image, returns 202 Accepted, and enqueues OCR work asynchronously.
TimescaleDB 2.27.0 (on PostgreSQL 16). Stores parsed readings as a hypertable partitioned by timestamp. Continuous aggregates materialise hourly and daily rollups automatically.
Next.js dashboard. Reads from TimescaleDB via a thin query API and renders consumption graphs.

Parts 2 and 3 of this series cover the OCR pipeline and the TimescaleDB schema in detail. This article focuses on the ingestion endpoint and the reasoning behind its deliberate minimalism.

The Battery Constraint Drives Everything

The Timer Camera X has a 140 mAh battery. During active operation, meaning Wi-Fi association, TCP handshake, JPEG capture and HTTP POST, it draws somewhere between 180 and 250 mA. That gives roughly 30 to 45 minutes of continuous active time before the battery is exhausted. The device must deep-sleep between readings; the claimed battery life of over one month assumes one capture per hour at standby current under 10 μA.

This creates a hard constraint on the server. The ESP32 HTTP client has a connection timeout. If the server blocks, by doing OCR synchronously, writing to the database or calling an external vision API, and that blocking exceeds the timeout, the device closes the connection and goes back to sleep. The image is lost. The wake cycle, which cost battery, produced nothing.

The ingestion endpoint must therefore do exactly three things synchronously:

Authenticate the request.
Read the multipart body and persist the raw image.
Return 202 Accepted.

Everything else, OCR, parsing and database writes, happens in a background worker after the response is sent.

The /upload Endpoint in .NET 10

.NET 10 (10.0.11 LTS, supported through November 2028) brings meaningful improvements to the minimal API pipeline: static pipeline analysis, faster endpoint selection, and lower tail latency compared to earlier releases. These are not theoretical. For a battery device that needs a server response under 100 ms, keeping the hot path lean pays off directly.

Here is the upload endpoint:

using System.Threading.Channels;
using Microsoft.AspNetCore.Http.Features;

var builder = WebApplication.CreateBuilder(args);

// Channel for background OCR work
var imageChannel = Channel.CreateBounded<string>(new BoundedChannelOptions(512)
{
    FullMode = BoundedChannelFullMode.DropOldest
});

builder.Services.AddSingleton(imageChannel.Reader);
builder.Services.AddSingleton(imageChannel.Writer);
builder.Services.AddHostedService<OcrWorker>();

// Raise the multipart body size limit for 3MP JPEGs
builder.Services.Configure<FormOptions>(options =>
{
    options.MultipartBodyLengthLimit = 8 * 1024 * 1024; // 8 MB ceiling
});

var app = builder.Build();

app.MapPost("/upload", async (
    HttpContext ctx,
    ChannelWriter<string> writer,
    IConfiguration config,
    CancellationToken ct) =>
{
    // 1. Basic Auth check (adequate on a local LAN; add TLS at ingress for WAN)
    if (!ctx.Request.Headers.TryGetValue("Authorization", out var authHeader) ||
        !IsValidBasicAuth(authHeader!, config["Upload:Credentials"]!))
    {
        return Results.Unauthorized();
    }

    // 2. Read the multipart body
    if (!ctx.Request.HasFormContentType)
        return Results.BadRequest("Expected multipart/form-data");

    var form = await ctx.Request.ReadFormAsync(ct);
    var file = form.Files.GetFile("image");

    if (file is null || file.Length == 0)
        return Results.BadRequest("No image in form field 'image'");

    // Save raw bytes; filename carries the device ID and a UTC tick timestamp
    var storagePath = config["Upload:StoragePath"] ?? "/var/meter-images";
    var fileName = $"{DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()}_{file.FileName}";
    var fullPath = Path.Combine(storagePath, fileName);

    await using var fs = File.Create(fullPath);
    await file.CopyToAsync(fs, ct);

    // 3. Enqueue the path for the background OCR worker, do NOT await it
    await writer.WriteAsync(fullPath, ct);

    // Return immediately, the ESP32 gets its 202 and goes back to sleep
    return Results.Accepted();
})
.WithName("UploadMeterImage")
.DisableAntiforgery();

app.Run();

static bool IsValidBasicAuth(string header, string expectedCredentials)
{
    if (!header.StartsWith("Basic ", StringComparison.OrdinalIgnoreCase))
        return false;

    try
    {
        var decoded = System.Text.Encoding.UTF8.GetString(
            Convert.FromBase64String(header["Basic ".Length..].Trim()));
        return decoded == expectedCredentials; // format: "user:password"
    }
    catch
    {
        return false;
    }
}

Notice what is absent from the handler body: no OCR call, no database write, no downstream HTTP call, no await Task.Delay. The handler reads bytes and writes a file path to a System.Threading.Channels bounded channel. The OcrWorker, an IHostedService, drains that channel on its own.

The BoundedChannelOptions with DropOldest is intentional. If the OCR worker falls behind (a slow vision API, a cold container), older readings are sacrificed rather than letting the channel grow unbounded and consuming memory.

The Background OCR Worker Shell

Structurally the worker is simple. The vision integration itself is the subject of Part 2.

public sealed class OcrWorker(ChannelReader<string> reader, ILogger<OcrWorker> logger)
    : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        await foreach (var imagePath in reader.ReadAllAsync(stoppingToken))
        {
            try
            {
                // Part 2: call vision OCR, parse kWh digits, write to TimescaleDB
                logger.LogInformation("Processing image: {Path}", imagePath);
                await ProcessImageAsync(imagePath, stoppingToken);
            }
            catch (Exception ex)
            {
                // Log and continue, a failed reading should not kill the worker
                logger.LogError(ex, "OCR processing failed for {Path}", imagePath);
            }
        }
    }

    private static Task ProcessImageAsync(string path, CancellationToken ct)
    {
        // Stub, the vision pipeline is covered in Part 2
        return Task.CompletedTask;
    }
}

The await foreach over ReadAllAsync handles cancellation cleanly on shutdown. Errors on a single image are caught and logged without stopping the loop, because a corrupted JPEG or a transient API failure should not take the worker down for every reading after it.

TimescaleDB as the Time-Series Store

TimescaleDB 2.27.0 (released May 2026, requiring PostgreSQL 16 or higher, since PostgreSQL 15 support was dropped in June 2026) is the right database for this workload. A power meter produces a narrow, time-ordered stream of readings. TimescaleDB's hypertable partitioning handles this automatically, and continuous aggregates let you define hourly and daily rollups in SQL without any application-level cron job.

For a meter sampled once per hour the default 7-day chunk interval is fine. If you ever reduce the interval to 1-minute resolution, tune chunk_time_interval explicitly to avoid chunk proliferation.

The v2.26 release introduced a roughly 3.5× speedup on analytical queries using time_bucket() in grouping expressions, via an expanded vectorised columnar query path. Dashboard queries that compute daily consumption from hourly readings benefit directly.

Practical Notes Before Part 2

Hardware firmware: use the ESP-IDF or Arduino framework for the Timer Camera X. As of mid 2025 there is no working CircuitPython camera support for this module. Using espcamera under CircuitPython produces initialisation errors.

Image size vs. timeout: at maximum resolution (2048 × 1536) a JPEG from the OV3660 can exceed 1 MB. The FormOptions.MultipartBodyLengthLimit in the code above is set to 8 MB, which is safe headroom. If your digits are readable at a lower resolution, reduce it on the device side. Smaller payloads mean faster uploads and less battery used per cycle.

TLS: Basic Auth over plain HTTP is acceptable on an isolated home LAN segment. If the upload travels over a public network or even a shared Wi-Fi, terminate TLS at a reverse proxy (Caddy or nginx) in front of the .NET service. Do not do TLS termination in the Kestrel process on a resource-constrained deployment unless you have spare CPU budget.

Docker image pinning: pin the base image explicitly, mcr.microsoft.com/dotnet/aspnet:10.0.11, rather than 10.0 or latest. .NET 10 ships monthly patch releases; a floating tag can silently pull a new runtime into production.

OpenAPI: .NET 10 defaults to OpenAPI 3.1. If you add Swagger UI, use Swashbuckle 5.x or switch to the new Microsoft.AspNetCore.OpenApi package. Older Swashbuckle versions cannot handle the 3.1 schema output.

Part 2 covers the OCR pipeline: sending the stored JPEG to a vision model, extracting and validating the kWh digit string, and writing a verified reading row into TimescaleDB with confidence metadata attached.

Graph-Native Data Structures in C#, Part 7: Bipartite Graphs and Collaborative Filtering in NebulaGraph

Mehdi Mohseni — Wed, 07 Oct 2026 12:45:18 +0000

This is Part 7 and the finale of the Graph-Native Data Structures in C# series. If you're arriving fresh, Part 1 covers graph fundamentals and the vocabulary we've been building on throughout. The recommendation engine pattern we're closing out here first appeared in the ArangoDB to NebulaGraph migration article. Here we build it properly, step by step.

The Bipartite Structure

A bipartite graph partitions vertices into two disjoint sets where edges only cross between sets, never within. For a recommendation engine that's a natural fit: users on one side, products on the other, and interactions as the only edges. No user-to-user edges exist. No product-to-product edges exist. The graph is bipartite by design.

NebulaGraph doesn't enforce bipartite structure at the schema level, it is kept that way by application convention. The engine won't stop you creating a purchased edge between two users. That discipline lives in your insertion code: always source from a user: VID, always target a product: VID.

Our schema looks like this in nGQL DDL:

CREATE TAG user(handle STRING);
CREATE TAG product(sku STRING, name STRING, active BOOL);
CREATE EDGE purchased(ts INT);
CREATE EDGE viewed(ts INT, weight DOUBLE);

Vertex IDs use the STRING type with a namespace prefix: 'user:alice', 'product:SKU-42'. NebulaGraph supports string VIDs up to 256 bytes, and the prefix convention makes VID type unambiguous without reading tag properties, which pays off when you filter results in C#.

Modeling Interactions as Typed Edges

The critical modeling decision here is separate edge types for separate interaction semantics, not a single interaction edge with a type property. purchased and viewed are distinct edge types. This matters for two reasons.

First, it lets the GO statement traverse selectively, so you can run collaborative filtering on purchase signal alone, which is higher quality than view signal. Second, edge types participate in NebulaGraph's storage indexing differently from edge properties. Querying OVER purchased is cheaper than OVER interaction WHERE interaction.type == 'purchased'.

The weight DOUBLE on viewed carries a decay factor you populate at insertion time, so a view from yesterday scores differently from one six months ago. purchased doesn't need this because recency is captured by the ts timestamp and purchases are inherently stronger signal.

The Two-Hop Collaborative Filtering Traversal

The core idea: from a target user, follow purchased edges to the products they own (hop 1). From those products, follow purchased edges back to other users who bought the same products (still hop 1, but reversed). From those peer users, follow purchased edges forward again to everything they've bought (hop 2). What you get at hop 2 is a raw candidate set for recommendation.

BIDIRECT on a bipartite graph gives you exactly this in a single GO statement. Because edges only ever run user → product, traversing bidirectionally means hop 1 reaches products (forward) and users (backward from the products' incoming edges) simultaneously. Two hops BIDIRECT equals the full collaborative filtering pattern with no second query.

GO 2 STEPS FROM 'user:alice' OVER purchased BIDIRECT
  WHERE $$.product.active == true
  YIELD DISTINCT dst(edge) AS vid, tags($$) AS vtags
  | ORDER BY $-.vid ASC
  | LIMIT 500

A few mechanics to be explicit about. The GO statement uses a walk path type, so vertices and edges can be revisited. That means 'user:alice' can appear in intermediate results. The YIELD DISTINCT deduplicates final-hop destination VIDs, but intermediate walk paths aren't deduplicated. Your C# code needs to filter the originating user from results. Also, the WHERE $$.product.active == true filter applies to the destination vertex at each hop, and the $$ notation reads destination tag properties inline.

One production gotcha: a bestselling product might have millions of purchasers. A 2-hop traversal from any user who bought it fans out catastrophically. NebulaGraph v5.2 introduced a SAMPLE clause on the GO statement specifically to throttle super-node traversals, giving predictable latency without killing completeness entirely. For earlier versions, the LIMIT at the end is your blunt instrument.

Scoring and Ranking in C

The graph layer returns raw candidate VIDs. Scoring happens in C#. The RecommendAsync method below handles deduplication, weighted scoring by interaction type, exclusion of owned items, and top-k cutoff.

public record Recommendation(string ProductVid, string ProductName, double Score);

public class RecommendationService
{
    private const double PurchaseWeight = 2.0;
    private const double ViewWeight = 0.5;

    private readonly INebulaSessionPool _pool;

    public RecommendationService(INebulaSessionPool pool) => _pool = pool;

    public async Task<IReadOnlyList<Recommendation>> RecommendAsync(
        string userHandle, int k, CancellationToken ct = default)
    {
        var userVid = $"user:{userHandle}";
        await using var session = await _pool.GetSessionAsync(ct);

        // Step 1: fetch owned products to exclude
        var ownedResult = await session.ExecuteAsync(
            $"GO 1 STEPS FROM '{userVid}' OVER purchased YIELD dst(edge) AS vid");
        var owned = ownedResult.AsVertices()
            .Select(v => v.Vid)
            .ToHashSet(StringComparer.Ordinal);

        // Step 2: 2-hop collaborative filtering
        var candidateResult = await session.ExecuteAsync($"""
            GO 2 STEPS FROM '{userVid}' OVER purchased BIDIRECT
              WHERE $$.product.active == true
              YIELD dst(edge) AS vid
            """);

        // Step 3: score by co-occurrence frequency
        var scores = new Dictionary<string, double>(StringComparer.Ordinal);
        foreach (var row in candidateResult.Rows)
        {
            var vid = row["vid"].AsString();
            if (!vid.StartsWith("product:", StringComparison.Ordinal)) continue;
            if (owned.Contains(vid)) continue;

            scores.TryGetValue(vid, out var existing);
            scores[vid] = existing + PurchaseWeight;
        }

        // Fetch names for top candidates before final cut
        var topVids = scores
            .OrderByDescending(kv => kv.Value)
            .Take(k * 3) // over-fetch before name lookup
            .Select(kv => kv.Key)
            .ToList();

        if (topVids.Count == 0) return Array.Empty<Recommendation>();

        var vidList = string.Join(", ", topVids.Select(v => $"'{v}'"));
        var nameResult = await session.ExecuteAsync(
            $"FETCH PROP ON product {vidList} YIELD id(vertex) AS vid, properties(vertex).name AS name");

        var nameMap = nameResult.Rows
            .ToDictionary(r => r["vid"].AsString(), r => r["name"].AsString());

        return scores
            .Where(kv => nameMap.ContainsKey(kv.Key))
            .OrderByDescending(kv => kv.Value)
            .Take(k)
            .Select(kv => new Recommendation(kv.Key, nameMap[kv.Key], kv.Value))
            .ToList();
    }
}

The scoring here is co-occurrence frequency multiplied by the interaction weight constant. Each time a product VID appears in the 2-hop results it means one more peer user bought it, so one more unit of PurchaseWeight added to its score. If you also query over viewed, add rows weighted at ViewWeight. The originating user is excluded implicitly because their owned items are in the owned set; peer user VIDs are filtered by the StartsWith("product:") check.

Caching and Freshness

For small-to-medium graphs, live traversal on NebulaGraph is fast, with two hop queries finishing in single digit milliseconds under typical conditions. But as the user base grows, precomputing recommendations for your top-traffic users is the pragmatic move.

The standard pattern: a background worker runs RecommendAsync for active users and writes results to Redis with a TTL. On a new purchased event, invalidate that user's cache key immediately, because a purchase is a hard signal change that makes stale recommendations misleading.

public class PurchaseEventHandler
{
    private readonly IDatabase _redis;
    private readonly RecommendationService _svc;

    public PurchaseEventHandler(IDatabase redis, RecommendationService svc)
    {
        _redis = redis;
        _svc = svc;
    }

    public async Task HandleAsync(string userHandle, string productSku, CancellationToken ct)
    {
        // Invalidate stale recommendations immediately
        await _redis.KeyDeleteAsync($"recs:{userHandle}");

        // Optionally warm the cache synchronously for low-latency users
        // (or defer to background worker for high-volume systems)
        var recs = await _svc.RecommendAsync(userHandle, k: 20, ct);
        var json = JsonSerializer.Serialize(recs);
        await _redis.StringSetAsync(
            $"recs:{userHandle}", json,
            expiry: TimeSpan.FromHours(6));
    }
}

TTL guidance: 1 to 6 hours works for moderate purchase velocity. High-velocity catalogs (flash sales, gaming item drops) may need shorter TTLs or event-driven invalidation from a message bus. The key insight is that the invalidation event is the purchase itself, so your graph write and your cache eviction should happen in the same logical transaction boundary, even if they're not literally atomic.

Where to Go Next: Hybrid Vector-Graph Retrieval

Pure collaborative filtering has well-known limits: cold start for new users, popularity bias, inability to reason about product content similarity. The natural next step is combining graph traversal with semantic vector search.

NebulaGraph v5.2 introduced native hybrid retrieval: a single query can combine graph traversal, vector similarity lookup, and full-text keyword search without external engines. This is the foundation for a production RAG augmented recommender. Use the two hop collaborative filtering to generate candidates, then re-rank using vector embeddings of product descriptions stored natively in the graph. The vector data type and vector indexes landed in v5.0; v5.2 made them composable with traversal in one query.

That's a full article in itself and a natural follow-on from this series.

Series Recap

This finale closes the Graph-Native Data Structures in C# series. Across seven parts we've covered:

Part 1. Graph fundamentals: adjacency lists, adjacency matrices, and when graphs beat relational models
Parts 2 to 4. Core algorithms: BFS, DFS, shortest paths, and cycle detection implemented in C#
Parts 5 and 6. NebulaGraph in practice: schema design, the nGQL GO statement, and multi-hop traversal patterns
Part 7 (this article). Bipartite graphs, typed interaction edges, collaborative filtering, and a production-ready recommendation service

The thread running through all of it: graph problems don't simplify under a relational lens. Modeling your data natively as vertices and edges, and querying it with traversal semantics, gives you capabilities that no amount of JOIN optimization recovers. The recommendation engine here is a clean example: the two hop collaborative filter is four lines of nGQL; its SQL equivalent is a multi-level self-join that becomes unmaintainable at scale.

Build the graph. Traverse it. Rank in C#. Cache the results. Then upgrade to hybrid search when your users outgrow pure collaborative filtering.

Supabase .NET DevLog #13

Guillaume Faas — Wed, 07 Oct 2026 12:23:41 +0000

Welcome to our weekly log for the Supabase .NET SDK 🎉

Sorry for being a little late on this one.

Like I said last week, I spent more time working on content. We now have blog posts and changelog entries coming soon, scheduled for the next couple of weeks.

Similar to last week, a big shout out to contributors! Y'all keep me quite busy with reviews, but I love it. It's so cool to see the community involved in the SDK, and the list of issues keeps shrinking day after day.

On a side note, I started working on an Observability initiative that will be very interesting for SDK users in the short term. At the moment, the SDK already provides support for OpenTelemetry but the traces/spans we emit will receive an update very soon.

Have a nice one, and stay tuned!

DEV Community: dotnet

Stop Hardcoding Your .NET Data Provider

ADO.NET already has the contract

Why bother?

Finding the provider at runtime

.NET Framework: providerName in configuration

.NET Core and modern .NET

Loading a provider from a registry

DataSourceInformation: the missing layer

Detection happens once, then the facts are reused

The point is not to hide the database

We Optimized Everything. Azure Was Still Freezing Our .NET API with Gen2 Pauses

Some context

The problem

Round 1: Fixing our own code

Removing blocking code

Reducing memory pressure on hot paths

Round 2: Looking inside the process

Round 3: Looking outside the process

The fix: two settings

The results

No more freezes, stable thread pool

Memory: 2.2 GB → 1.4 GB

Worst-case response times

Background processing: under one second

Lessons learned

SQL Pagination

SQL Pagination

Table of Contents

Introduction

1. What Pagination Actually Is

Returning a bounded slice of an ordered result set

2. OFFSET / FETCH: The Core Syntax

OFFSET skips rows; FETCH NEXT limits how many come back

Using variables instead of literals

3. Why ORDER BY Is Mandatory (and Must Be Deterministic)

A relational table has no inherent order — "page 2" is meaningless without one

But a non-unique ORDER BY is a subtler, silent bug

4. Calculating Page Numbers

Converting a 1-based page number into an offset

Validate inputs — never trust the caller's page number

5. Getting the Total Row Count

Most UIs need "Page 3 of 47" — which requires knowing the total

6. The Hidden Cost: Deep Pages Get Slower

OFFSET does not jump to row N — it reads and discards N rows

7. Keyset (Seek) Pagination

Instead of "skip N rows," say "give me rows AFTER the last one I saw"

The trade-offs, stated honestly

8. Indexing for Pagination

The ORDER BY is the expensive part — an index that matches it removes the sort

9. Pagination Inside a Stored Procedure

A reusable, parameterized, validated paging query

10. Older Approaches: TOP and ROW_NUMBER()

What you'll meet in legacy code (pre-SQL Server 2012)

11. Pagination in EF Core

Skip and Take translate directly to OFFSET / FETCH

Total count alongside the page

Keyset pagination in LINQ

12. Common Pitfalls

Quick Reference Table

Conclusion

Keeping Your Knowledge Graph Current Without a Dedicated Team

The Maintenance Problem Nobody Plans For

Where the Edges Come From

The Reconciliation Worker

Provenance on Every Edge

Conflict Handling: When Two Sources Disagree

Querying for Stale Nodes

Honest Limits

Practical Checklist Before You Ship

Hybrid Retrieval in C#: Combining Vector Search, Graph Traversal, and Reranking for Agent Memory

The Problem with Pure Vector Retrieval

Pipeline Architecture

Reciprocal Rank Fusion and Deduplication

Graph Expansion: Hop Budget and Cypher

Context Assembly with a Hard Token Budget

What the Reranker Actually Fixes

Measuring the Difference

Migration Path to Agent Framework

Edge AI with .NET, Part 4: Time Series That Outlives the Hardware

.NET Framework: `providerName` in configuration

`DataSourceInformation`: the missing layer

`OFFSET` skips rows; `FETCH NEXT` limits how many come back

`OFFSET` does not jump to row N — it reads and discards N rows

`Skip` and `Take` translate directly to `OFFSET` / `FETCH`