API-Level Sharding

API-level sharding uses a master catalog and explicit application-owned route keys to route each request to one normal CSharpDB database file.

Try it first: run dotnet run --project samples/api-level-sharding/ApiLevelShardingSample.csproj from the repository root. The sample creates a master database plus four shard files and walks through an e-commerce order-history flow where recent orders use the current month route and older history uses the selected month route.

Scenario: E-Commerce Order History

Order history is a good V1 sharding shape because orders are usually write-once/read-many and users normally browse recent orders first. The application can route current orders by month, then route older history only when the user selects an older month.

In this scenario the route context is:

new CSharpDbRouteContext
{
    Keyspace = "orders_by_month",
    Key = "2026-06"
}

The route key is the order month in yyyy-MM form. Normal application code does not choose shard-0 or shard-1; it chooses the business key, and CSharpDB resolves that key through the virtual-bucket map and exact pins.

User Flow

  • Customer opens order history: query page 1 with route key 2026-06.
  • Customer chooses May 2026: query page 1 with route key 2026-05.
  • Customer requests a 10-row page that spans months: read 6 rows from 2026-06, then continue into 2026-05 for the remaining 4 rows.
  • Customer requests a multi-month summary: the caller explicitly iterates the known month route keys and combines the results.

Routing Model

Each shard stays a normal warm CSharpDB database with its own WAL, pager, cache, and commit path. The sharded client sits above those databases and resolves a CSharpDbRouteContext to exactly one shard.

  1. The application supplies Keyspace and Key, such as orders_by_month and 2026-06.
  2. CSharpDB canonicalizes the route text as {keyspaceLength}:{keyspace}|{keyLength}:{key}.
  3. The router hashes the canonical UTF-8 bytes with SHA-256 and reads the first 8 bytes as an unsigned token.
  4. The token maps to a virtual bucket.
  5. The bucket map resolves that bucket to a ShardId.
  6. An exact route-key pin can override bucket ownership for operator-controlled placement of hot or archival keys.
Do not use hash(key) % shardCount for persisted data. Changing the shard count would remap existing keys silently. CSharpDB V1 uses explicit virtual-bucket ownership so adding a shard becomes a controlled migration.
Routing is not row filtering. The route key gets the request to the right database file. SQL should still filter on the route-key column, such as WHERE order_month = '2026-05', because multiple route keys can live on the same physical shard.

Direct Local Client

For embedded or service-internal use, seed the shard map into a master CSharpDB database, open the master database, and bind requests with ForRoute(...). The master database owns sharding metadata; it is not a data shard.

using CSharpDB.Client;

CSharpDbClientOptions masterOptions = new()
{
    ConnectionString = "Data Source=data/master.db",
};

CSharpDbShardingOptions activeMap = new()
{
    Keyspace = "orders_by_month",
    MapVersion = 1,
    VirtualBucketCount = 16,
    Shards =
    [
        new CSharpDbShardDefinition { ShardId = "shard-0", DataSource = "data/shard-0.db" },
        new CSharpDbShardDefinition { ShardId = "shard-1", DataSource = "data/shard-1.db" },
        new CSharpDbShardDefinition { ShardId = "shard-2", DataSource = "data/shard-2.db" },
        new CSharpDbShardDefinition { ShardId = "shard-3", DataSource = "data/shard-3.db" },
    ],
    BucketRanges =
    [
        new CSharpDbShardBucketRange { StartBucketInclusive = 0, EndBucketExclusive = 4, ShardId = "shard-0" },
        new CSharpDbShardBucketRange { StartBucketInclusive = 4, EndBucketExclusive = 8, ShardId = "shard-1" },
        new CSharpDbShardBucketRange { StartBucketInclusive = 8, EndBucketExclusive = 12, ShardId = "shard-2" },
        new CSharpDbShardBucketRange { StartBucketInclusive = 12, EndBucketExclusive = 16, ShardId = "shard-3" },
    ],
    ExactKeyPins = new Dictionary<string, string>(StringComparer.Ordinal)
    {
        ["2026-06"] = "shard-0",
        ["2026-05"] = "shard-1",
        ["2026-04"] = "shard-2",
        ["2025-12"] = "shard-3",
    },
};

await CSharpDbShardedClient.SeedMasterCatalogAsync(masterOptions, activeMap);

await using CSharpDbShardedClient sharded =
    await CSharpDbShardedClient.TryCreateFromMasterCatalogAsync(masterOptions)
    ?? throw new InvalidOperationException("The master database does not contain an active shard map.");

await sharded.ExecuteSqlOnAllShardsAsync("""
    CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        order_month TEXT NOT NULL,
        customer_id TEXT NOT NULL,
        order_number TEXT NOT NULL,
        order_date TEXT NOT NULL,
        amount REAL NOT NULL,
        status TEXT NOT NULL
    );
    """);

var route = new CSharpDbRouteContext { Keyspace = "orders_by_month", Key = "2026-06" };
ICSharpDbClient juneOrders = sharded.ForRoute(route);

await juneOrders.InsertRowAsync("orders", new Dictionary<string, object?>
{
    ["id"] = 1L,
    ["order_month"] = "2026-06",
    ["customer_id"] = "customer-1001",
    ["order_number"] = "SO-202606-1001",
    ["order_date"] = "2026-06-01T10:15:00Z",
    ["amount"] = 149.00,
    ["status"] = "placed",
});

CSharpDbShardResolution resolution = sharded.ResolveRoute(route);
Console.WriteLine($"{route.Key} => bucket {resolution.Bucket}, shard {resolution.ShardId}");

Replica Metadata

Shard definitions can carry Phase 6 replica metadata for operational views. This is metadata only: CSharpDB exposes the role, primary shard, promotion eligibility, and operator-reported lag, but it does not copy data, promote replicas, or reroute traffic based on health in this slice.

new CSharpDbShardDefinition
{
    ShardId = "shard-1-replica",
    DataSource = "data/shard-1-replica.db",
    Role = CSharpDbShardRoles.Replica,
    PrimaryShardId = "shard-1",
    PromotionEligible = true,
    ReplicationLagBytes = 256,
    LastReplicatedUtc = DateTimeOffset.UtcNow,
}

Bucket ranges and exact route-key pins must still point to primary shards. Replicas appear in map snapshots and shard status so Admin and operators can inspect the intended topology before replication and failover mechanics are added.

Recent And Older Pages

A recent order-history page uses the current route key. An older page uses the route key selected by the user. Both queries still filter on order_month.

ICSharpDbClient currentMonth = sharded.ForRoute(new CSharpDbRouteContext
{
    Keyspace = "orders_by_month",
    Key = "2026-06",
});

await currentMonth.ExecuteSqlAsync("""
    SELECT order_number, order_date, amount, status
    FROM orders
    WHERE order_month = '2026-06'
      AND customer_id = 'customer-1001'
    ORDER BY order_date DESC
    LIMIT 25 OFFSET 0;
    """);

ICSharpDbClient olderMonth = sharded.ForRoute(new CSharpDbRouteContext
{
    Keyspace = "orders_by_month",
    Key = "2026-05",
});

await olderMonth.ExecuteSqlAsync("""
    SELECT order_number, order_date, amount, status
    FROM orders
    WHERE order_month = '2026-05'
      AND customer_id = 'customer-1001'
    ORDER BY order_date DESC
    LIMIT 25 OFFSET 0;
    """);

Page Fill Across Route Keys

When a UI page can span more than one route key, the application owns the aggregation loop. For a descending order-history view, keep the route keys ordered newest-to-oldest and keep querying until the page is full.

remaining = 10

query 2026-06 LIMIT 10 OFFSET 0
  -> 6 rows
  -> remaining = 4

query 2026-05 LIMIT 4 OFFSET 0
  -> 4 rows
  -> page is full

For later pages, convert the requested page into a global offset, skip whole route keys until that offset is consumed, then use a route-local OFFSET for the first route that contributes rows.

int remainingToSkip = (pageNumber - 1) * pageSize;
int remainingToTake = pageSize;
var pageRows = new List<OrderRow>(pageSize);

foreach (string month in orderedMonths)
{
    int routeCount = await CountOrdersForRouteAsync(month);
    if (remainingToSkip >= routeCount)
    {
        remainingToSkip -= routeCount;
        continue;
    }

    int routeOffset = remainingToSkip;
    remainingToSkip = 0;

    int routeTake = Math.Min(remainingToTake, routeCount - routeOffset);
    pageRows.AddRange(await LoadOrdersForRouteAsync(month, routeTake, routeOffset));

    remainingToTake = pageSize - pageRows.Count;
    if (remainingToTake == 0)
        break;
}

Alternative Aggregation Patterns

The sample uses precise page fill because it reads only the rows needed for an arbitrary page. If the product experience only spans a small, known route window, simpler patterns can be acceptable.

Bounded over-fetch, then merge and limit

For a recent-orders page, the UI may only need the current month and previous month. Query both routes, accept a small amount of extra data, merge by the same sort order, and trim the result to the requested page size.

pageSize = 10
candidateMonths = ["2026-06", "2026-05"]
rows = []

for month in candidateMonths:
    rows += query route month
        where customer_id = "customer-1001"
        order by order_date desc, id desc
        limit pageSize

page = rows
    order by order_date desc, id desc
    take pageSize

This avoids route counts and route-local offset math. The tradeoff is that it can read extra rows and only works well when the route window is intentionally small. Use a deterministic tie-breaker such as id with order_date so merged pages are stable.

Date-range window reads

For filters such as "last 90 days" or "orders from April through June", compute the month route keys from the date range and query each route with both the route key and the date predicate.

months = monthsBetween(startDate, endDate)
rows = []

for month in months newest-to-oldest:
    rows += query route month
        where order_month = month
          and order_date >= startDate
          and order_date < endDate
        order by order_date desc, id desc
        limit perRouteLimit

page = merge rows by order_date desc, id desc
page = page take pageSize

This is useful for user-selected windows and reports. Keep perRouteLimit bounded so a broad date filter does not accidentally become an expensive fan-out.

Cursor-based continuation

Instead of counting every route for later pages, the API can return a continuation token that records where the next page should resume. For month-sharded order history, that token can contain the ordered route list plus either per-route offsets or the last (order_date, id) cursor returned from each route.

request:
  pageSize = 10
  token = previous response token, or empty

state = decode token
rows = []

for month in state.remainingMonths:
    rows += query route month
        where row is after the month cursor
        order by order_date desc, id desc
        limit rows still needed

    update month cursor
    if rows has pageSize:
        break

return rows plus updated token

This usually fits infinite scroll better than numbered pages. It avoids global counts, but the token must be treated as part of the API contract.

This is application-level aggregation. CSharpDB V1 does not infer the route list from WHERE order_date BETWEEN ... and does not run a distributed query plan.

Daemon Configuration

For the REST API and gRPC daemon, configure the normal CSharpDB connection string to point at the master database. At startup the daemon opens that database, discovers the active shard map from the master catalog, registers a sharded ICSharpDbClient, and routes existing endpoints through per-request route context.

{
  "ConnectionStrings": {
    "CSharpDB": "Data Source=data/master.db"
  }
}

Seed the master catalog with SeedMasterCatalogAsync(...), Admin's sharding setup flow, or the shard catalog update APIs before starting the daemon. The previous JSON file catalog shape is not part of the shipped v4 implementation.

Remote Route Context

HTTP clients send route context as headers. gRPC clients send the same names as lowercase metadata keys.

X-CSharpDB-Keyspace: orders_by_month
X-CSharpDB-Shard-Key: 2026-06

The .NET client sets those headers or metadata automatically when RouteContext is present.

await using ICSharpDbClient client = CSharpDbClient.Create(new CSharpDbClientOptions
{
    Endpoint = "https://localhost:61818",
    Transport = CSharpDbTransport.Http,
    RouteContext = new CSharpDbRouteContext
    {
        Keyspace = "orders_by_month",
        Key = "2026-06",
    },
});

await client.ExecuteSqlAsync("SELECT COUNT(*) FROM orders WHERE order_month = '2026-06';");

Shard Admin APIs

Shard-admin APIs expose topology and operational state without overloading normal data operations. They are available for direct sharded clients and through both REST and gRPC daemon transports.

await using ICSharpDbShardAdminClient shardAdmin =
    CSharpDbClient.CreateShardAdmin(new CSharpDbClientOptions
    {
        Transport = CSharpDbTransport.Grpc,
        Endpoint = "https://localhost:61818",
    });

CSharpDbShardMapSnapshot map = await shardAdmin.GetShardMapAsync();
CSharpDbShardResolution preview = await shardAdmin.ResolveRouteAsync(new CSharpDbRouteContext
{
    Keyspace = "orders_by_month",
    Key = "2026-06",
});
IReadOnlyList<CSharpDbShardStatus> status = await shardAdmin.GetShardStatusAsync();

The REST endpoints are GET /api/sharding/map, POST /api/sharding/resolve, GET /api/sharding/status, POST /api/sharding/sql/execute-all, and POST /api/sharding/sql/read-all. The gRPC contract exposes matching calls named GetShardMap, ResolveShardRoute, GetShardStatus, ExecuteSqlOnAllShards, and ExecuteReadOnlySqlOnAllShards.

Admin operations are explicit. Execute-on-all-shards is intended for schema setup. Read-only fan-out is intended for diagnostics and Admin views. Both return one result per shard and do not create a distributed SQL planner.

Admin Sharding Workspace

CSharpDB Studio includes a Sharding workspace for operator inspection. It shows shard definitions, bucket ranges, exact route-key pins, shard health, catalog state, migration history, and route resolution previews.

For local Admin hosting, open the master database that contains the active shard map. For remote HTTP or gRPC Admin connections, the workspace calls the daemon shard-admin endpoints.

Route-Aware Admin Tabs

Query, table/view data, and collection tabs include a route selector when shard-admin APIs are available. Enter the keyspace and application route key, such as orders_by_month and 2026-06, then apply the route. The selector resolves and displays the target shard and bucket; the tab stores that route snapshot independently from other open tabs.

After a route is selected, SQL execution, procedure execution, table browsing, row edits, schema actions, collection browsing, document writes, and exact-key document lookup use a route-bound ICSharpDbClient. Direct sharded Admin uses ForRoute(...). Remote HTTP and gRPC Admin clients send the selected route through normal route headers or metadata.

Admin routing is still explicit. The selector does not infer a route from WHERE order_month = ..., a document key, or a table name. Choose the route key first, then run the normal single-shard Admin action. Broader forms, reports, pipelines, and query-designer route binding are planned as follow-up Admin coverage.

Read-Only Fan-Out

Use read-only fan-out when an operator needs a per-shard diagnostic view, such as row counts or simple health queries. CSharpDB validates the SQL before routing and rejects DDL/DML statements.

IReadOnlyList<CSharpDbShardSqlExecutionResult> counts =
    await shardAdmin.ExecuteReadOnlySqlOnAllShardsAsync(
        "SELECT COUNT(*) FROM orders;");

The returned results stay grouped by shard. The caller decides whether to show each shard separately, merge rows for a report, or compute a small explicit rollup. Normal application paging should still use route-aware calls so the app controls the route keys and the amount of data read.

Catalog-Backed Map Updates

Operator-managed catalog mode stores the active shard map, pending map, catalog history, migration checkpoints, and migration history in the master database. Applying a catalog change writes a pending map into the master catalog; it does not silently change the live router.

{
  "ConnectionStrings": {
    "CSharpDB": "Data Source=data/master.db"
  }
}
CSharpDbShardCatalogState catalog = await shardAdmin.GetShardCatalogAsync();

CSharpDbShardCatalogValidationResult validation =
    await shardAdmin.ValidateShardCatalogUpdateAsync(new CSharpDbShardCatalogUpdateRequest
    {
        Options = proposedOptions,
        ExpectedCurrentMapVersion = catalog.ActiveMap.MapVersion,
    });

CSharpDbShardCatalogApplyResult applied =
    await shardAdmin.ApplyShardCatalogUpdateAsync(new CSharpDbShardCatalogUpdateRequest
    {
        Options = proposedOptions,
        ExpectedCurrentMapVersion = catalog.ActiveMap.MapVersion,
        AllowMetadataOnlyOwnershipChange = true,
        Operator = "ops",
        Comment = "data was moved by migration job 2026-06-01",
    });

Catalog update endpoints are GET /api/sharding/catalog, POST /api/sharding/catalog/validate, and POST /api/sharding/catalog/apply. The gRPC contract exposes GetShardCatalog, ValidateShardCatalogUpdate, and ApplyShardCatalogUpdate. A successful apply returns RequiresRestart = true; restart the daemon or recreate the sharded client to activate the new map.

CSharpDB Studio's Sharding workspace includes a JSON shard-map draft editor for master-catalog-backed deployments. Operators can load a sanitized active or pending map template, edit the CSharpDbShardingOptions JSON, validate it, and apply a pending map with operator/comment metadata. Because the editor starts from read-only snapshots, hidden connection strings, API keys, and directory entries must be reviewed and supplied explicitly before applying a draft.

Controlled Exact-Key Migration

Phase 4 starts with exact route-key migration. The operator provides a manifest that identifies the route-owned tables and collections. CSharpDB fences writes for that route key, copies matching rows/documents to the destination shard, verifies counts and checksums, then writes a pending exact-key pin to the catalog.

CSharpDbShardMigrationResult migrated =
    await shardAdmin.MigrateExactRouteKeyAsync(new CSharpDbShardExactKeyMigrationRequest
    {
        Keyspace = "orders_by_month",
        RouteKey = "2026-05",
        DestinationShardId = "archive-1",
        ExpectedCurrentMapVersion = catalog.ActiveMap.MapVersion,
        Operator = "ops",
        Manifest = new CSharpDbShardMigrationManifest
        {
            Tables =
            [
                new CSharpDbShardMigrationTableManifest
                {
                    TableName = "orders",
                    RouteKeyColumn = "order_month",
                    PrimaryKeyColumn = "id",
                },
            ],
            Collections =
            [
                new CSharpDbShardMigrationCollectionManifest
                {
                    CollectionName = "order_documents",
                    RouteKeyPropertyName = "orderMonth",
                },
            ],
        },
    });

The REST endpoint is POST /api/sharding/migrations/exact-route-key. The gRPC contract exposes MigrateExactRouteKey. A successful migration returns Status = "PendingActivation" and RequiresRestart = true; the live router keeps the old map until restart.

Controlled Bucket-Range Movement

Bucket-range movement uses the same manifest shape, but moves every unpinned route key whose hash bucket falls inside an operator-selected virtual-bucket interval. The requested buckets must be wholly owned by the source shard. Exact route-key pins are left in place because pins override bucket ownership.

CSharpDbShardMigrationResult movedBuckets =
    await shardAdmin.MigrateBucketRangeAsync(new CSharpDbShardBucketRangeMigrationRequest
    {
        Keyspace = "orders_by_month",
        SourceShardId = "hot-1",
        DestinationShardId = "archive-1",
        StartBucketInclusive = 1024,
        EndBucketExclusive = 1536,
        ExpectedCurrentMapVersion = catalog.ActiveMap.MapVersion,
        Operator = "ops",
        Manifest = manifest,
    });

The REST endpoint is POST /api/sharding/migrations/bucket-range. The gRPC contract exposes MigrateBucketRange. A successful movement writes a pending bucket map and still requires a daemon restart or sharded-client recreation before live routing changes.

IReadOnlyList<CSharpDbShardMigrationHistoryEntry> history =
    await shardAdmin.GetShardMigrationHistoryAsync();

If shard-directory entries point at moved routes, the pending catalog map updates those entries to the destination shard and pending map version where the movement can be determined safely. Writable catalog mode records rejected, failed, and pending-activation migration outcomes. Failed, verification-failed, and catalog-apply-failed outcomes include RequiresOperatorRecovery and RecoveryAction so Admin and operators can distinguish simple rejection from a recoverable partial movement state. Query history with GET /api/sharding/migrations or gRPC GetShardMigrationHistory. These APIs do not infer ownership from arbitrary SQL predicates.

ADO.NET

ADO.NET callers can bind a connection to one route with connection-string keys. Every command on that connection is sent to the same route.

Endpoint=https://localhost:61818;Transport=Grpc;Shard Keyspace=orders_by_month;Shard Key=2026-06

Transactions

BeginTransactionAsync requires a route context in sharded mode. The returned id is prefixed with the map version and shard id, so later execute, commit, and rollback calls can route from the transaction id alone.

ICSharpDbClient currentMonth = sharded.ForRoute(new CSharpDbRouteContext
{
    Keyspace = "orders_by_month",
    Key = "2026-06",
});

TransactionSessionInfo tx = await currentMonth.BeginTransactionAsync();
// Example: csdbshard:1:shard-0:...

await sharded.ExecuteInTransactionAsync(tx.TransactionId, "INSERT INTO orders VALUES (2, '2026-06', 'customer-1001', 'SO-1002', '2026-06-01T12:00:00Z', 87.50, 'placed');");
await sharded.CommitTransactionAsync(tx.TransactionId);

If a conflicting route header is supplied with a sharded transaction id, CSharpDB rejects the operation instead of sending the transaction to the wrong shard.

V1 Scope

Supported

  • Single-shard SQL execution when a route key is provided.
  • Single-shard row APIs, collection APIs, saved queries, procedures, and transaction sessions.
  • Shard status checks, execute-on-all-shards schema setup helpers, and explicit read-only fan-out for diagnostics.
  • Operator-controlled exact route-key and bucket-range movement with manifest-based copy and verification.
  • Catalog-backed migration history for exact route-key migration outcomes.
  • Replica role metadata in shard maps and status responses.
  • Direct, HTTP, gRPC, and ADO.NET route context propagation.

Not Supported In V1

  • Automatic cross-shard joins, aggregates, arbitrary queries, or transactions.
  • Automatic resharding or data movement.
  • Automatic SQL inference from arbitrary WHERE clauses.
  • Data replication, replica promotion, failover, or shard health based rerouting.

Operator Checklist

  • Keep schemas compatible across all shards in a keyspace.
  • Version every bucket map change with MapVersion.
  • Move data before changing bucket ownership for existing keys.
  • Use ExactKeyPins sparingly for explicit hot-key, archival-key, or migration overrides.
  • Treat route keys as application-owned and trusted, matching the existing trusted in-process API model.

Runnable Sample

Run the four-shard local sample and inspect the generated shard files.

Configuration

Review related daemon and connection-string configuration patterns.