pengdows-crud

name	pengdows-crud
description	Help with pengdows.crud - a SQL-first, strongly-typed data access layer for .NET. Use when implementing CRUD operations, entity mapping, database connections, transactions, or testing with FakeDb. Covers TableGateway, SqlContainer, DatabaseContext, attributes ([Table], [Column], [Id], [PrimaryKey]), and multi-database support.
allowed-tools	Read, Grep, Glob, Bash

pengdows.crud Development Guide

pengdows.crud is a SQL-first, strongly-typed, testable data access layer for .NET 8+. No LINQ, no tracking, no surprises - explicit SQL control with database-agnostic features.

Quick Start

// 1. Define entity with attributes
[Table("orders")]
public class Order
{
    [Id(false)]  // DB-generated surrogate key
    [Column("id", DbType.Int64)]
    public long Id { get; set; }

    [PrimaryKey(1)]  // Business key
    [Column("order_number", DbType.String, 50)]
    public string OrderNumber { get; set; }

    [Column("customer_id", DbType.Int64)]
    public long CustomerId { get; set; }

    [Column("total", DbType.Decimal)]
    public decimal Total { get; set; }
}

// 2. Extend TableGateway with custom methods
public interface IOrderGateway : ITableGateway<Order, long>
{
    ValueTask<Order?> GetByOrderNumberAsync(string orderNumber);
    ValueTask<List<Order>> GetCustomerOrdersAsync(long customerId, DateTime? since = null);
}

public class OrderGateway : TableGateway<Order, long>, IOrderGateway
{
    public OrderGateway(IDatabaseContext context) : base(context)
    {
    }

    public async ValueTask<Order?> GetByOrderNumberAsync(string orderNumber)
    {
        var lookup = new Order { OrderNumber = orderNumber };
        return await RetrieveOneAsync(lookup);
    }

    public async ValueTask<List<Order>> GetCustomerOrdersAsync(long customerId, DateTime? since = null)
    {
        var sc = BuildBaseRetrieve("o");

        sc.Query.Append(" WHERE ");
        sc.Query.Append(sc.WrapObjectName("o.customer_id"));
        sc.Query.Append(" = ");
        var param = sc.AddParameterWithValue("customerId", DbType.Int64, customerId);
        sc.Query.Append(sc.MakeParameterName(param));

        if (since.HasValue)
        {
            sc.Query.Append(" AND ");
            sc.Query.Append(sc.WrapObjectName("o.created_at"));
            sc.Query.Append(" >= ");
            var sinceParam = sc.AddParameterWithValue("since", DbType.DateTime, since.Value);
            sc.Query.Append(sc.MakeParameterName(sinceParam));
        }

        sc.Query.Append(" ORDER BY ");
        sc.Query.Append(sc.WrapObjectName("o.created_at"));
        sc.Query.Append(" DESC");

        return await LoadListAsync(sc);
    }
}

// 3. Register in DI as singletons
services.AddSingleton<IDatabaseContext>(sp =>
    new DatabaseContext(connectionString, SqlClientFactory.Instance));

services.AddSingleton<IOrderGateway>(sp =>
    new OrderGateway(sp.GetRequiredService<IDatabaseContext>()));

// 4. Inject and use
public class OrdersController : ControllerBase
{
    private readonly IOrderGateway _orderGateway;

    public OrdersController(IOrderGateway orderGateway)
    {
        _orderGateway = orderGateway;
    }

    [HttpGet("{orderNumber}")]
    public async Task<IActionResult> Get(string orderNumber)
    {
        var order = await _orderGateway.GetByOrderNumberAsync(orderNumber);
        return order is null ? NotFound() : Ok(order);
    }
}

SQL Building: The Three-Tier API

TableGateway has three tiers of methods. Understanding which tier to use is key:

Tier 1: Build Methods (SQL generation only, no execution)

Build* methods return an ISqlContainer holding generated SQL and parameters. Nothing is sent to the database. You inspect, modify, or execute the container yourself.

// All synchronous except BuildUpdateAsync
ISqlContainer BuildCreate(entity);            // INSERT statement
ISqlContainer BuildBaseRetrieve("alias");     // SELECT with no WHERE (starting point for custom queries)
ISqlContainer BuildRetrieve(ids, "alias");    // SELECT ... WHERE id IN (...)
ISqlContainer BuildRetrieve(ids);             // SELECT ... WHERE id IN (...) (no alias)
ISqlContainer BuildRetrieve(entities, "a");   // SELECT ... WHERE pk columns match
ISqlContainer BuildRetrieve(entities);        // SELECT ... WHERE pk columns match (no alias)
ISqlContainer BuildDelete(id);                // DELETE ... WHERE id = @id
ISqlContainer BuildUpsert(entity);            // Dialect-specific INSERT-or-UPDATE

// Batch Build methods (new in 2.0)
IReadOnlyList<ISqlContainer> BuildBatchCreate(entities);
IReadOnlyList<ISqlContainer> BuildBatchUpdate(entities);
IReadOnlyList<ISqlContainer> BuildBatchUpsert(entities);
IReadOnlyList<ISqlContainer> BuildBatchDelete(ids);
IReadOnlyList<ISqlContainer> BuildBatchDelete(entities);

// ONLY async Build method (needs DB I/O when loadOriginal: true)
ISqlContainer sc = await BuildUpdateAsync(entity);                     // UPDATE statement
ISqlContainer sc = await BuildUpdateAsync(entity, loadOriginal: true); // Loads current row first

BuildBaseRetrieve vs BuildRetrieve: BuildBaseRetrieve generates SELECT columns FROM table with NO WHERE clause - it's the starting point when you need to add your own custom filtering. BuildRetrieve generates a complete query with a WHERE clause filtering by IDs or primary key values.

Tier 2: Load Methods (execute a pre-built container, map results)

Load* methods take an ISqlContainer you already have (from a Build method or custom SQL) and execute it, mapping rows to entities:

TEntity? result      = await LoadSingleAsync(container);     // First row or null
List<TEntity> list   = await LoadListAsync(container);       // All rows
IAsyncEnumerable<TEntity> stream = LoadStreamAsync(container); // Streamed rows (memory-efficient)

Tier 3: Convenience Methods (Build + Execute in one call)

These combine Tier 1 + Tier 2 into a single call. Use when you don't need to customize the SQL:

// Single-entity retrieval
TEntity? order = await RetrieveOneAsync(id);           // By [Id] column
TEntity? order = await RetrieveOneAsync(entityLookup); // By [PrimaryKey] columns

// Multi-entity retrieval
List<TEntity> orders = await RetrieveAsync(ids);                   // Returns list
IAsyncEnumerable<TEntity> orders = RetrieveStreamAsync(ids);       // Returns stream

// Write operations
bool created  = await CreateAsync(entity, context);    // INSERT, returns true if 1 row
int affected  = await UpdateAsync(entity);             // UPDATE, returns row count
int affected  = await DeleteAsync(id);                 // DELETE single, returns row count
int affected  = await DeleteAsync(ids);                 // DELETE batch, returns row count
int affected  = await UpsertAsync(entity);             // INSERT or UPDATE

// Collection operations (Delegates to Batch methods)
int affected = await CreateAsync(entities);            // Batch INSERT
int affected = await UpdateAsync(entities);            // Batch UPDATE
int affected = await UpsertAsync(entities);            // Batch UPSERT
int affected = await DeleteAsync(entities);            // Batch DELETE by primary key

// Explicit Batch Operations
int affected = await BatchCreateAsync(entities);
int affected = await BatchUpdateAsync(entities);
int affected = await BatchUpsertAsync(entities);
int affected = await BatchDeleteAsync(ids);
int affected = await BatchDeleteAsync(entities);

WHERE Clause Helpers (modify an existing container)

These append WHERE clauses to a container you already have:

// Appends WHERE column IN (@p0, @p1, ...) to an existing container
BuildWhere("e.Id", ids, existingContainer);

// Appends WHERE clause using [PrimaryKey] columns
BuildWhereByPrimaryKey(entities, existingContainer, "e");

Typical Workflow

// Simple: Use convenience methods (Tier 3)
var order = await RetrieveOneAsync(orderId);

// Custom query: Build (Tier 1) -> modify -> Load (Tier 2)
var sc = BuildBaseRetrieve("o");
sc.Query.Append(" WHERE ");
sc.Query.Append(sc.WrapObjectName("o.status"));
sc.Query.Append(" = ");
var p = sc.AddParameterWithValue("status", DbType.String, "Active");
sc.Query.Append(sc.MakeParameterName(p));
var results = await LoadListAsync(sc);

// Stream large results: Build (Tier 1) -> Stream (Tier 2)
var sc = BuildBaseRetrieve("o");
sc.Query.Append(" ORDER BY ");
sc.Query.Append(sc.WrapObjectName("o.created_at"));
await foreach (var order in LoadStreamAsync(sc))
{
    await ProcessAsync(order);
}

ISqlContainer: Execution Methods

All execution methods return ValueTask (not Task) for reduced allocations:

ValueTask<int>             ExecuteNonQueryAsync(commandType);          // Row count
ValueTask<T>               ExecuteScalarRequiredAsync<T>(commandType); // Single value — throws if no rows or null
ValueTask<T?>              ExecuteScalarOrNullAsync<T>(commandType);   // Single value — null if no rows or DBNull
ValueTask<ScalarResult<T>> TryExecuteScalarAsync<T>(commandType);      // Unambiguous: None/Null/Value
ValueTask<ITrackedReader>  ExecuteReaderAsync(commandType);            // Data reader

All have CancellationToken overloads.

ISqlContainer: Clone for Reuse

Clone a container to reuse its SQL structure with different parameter values or contexts:

var template = BuildCreate(entity);
var clone = template.Clone();                    // Same context, update param values
var clone = template.Clone(transactionContext);  // Different context (e.g., transaction)

DI Lifetime Rules - CRITICAL

Component	Lifetime	Why
DatabaseContext	Singleton	Manages connection pool, metrics, DbMode state
TableGateway<T,TId>	Singleton	Stateless, caches compiled accessors
IAuditValueResolver	Singleton	Must be thread-safe/AsyncLocal-based (e.g. IHttpContextAccessor)
ITenantContextRegistry	Singleton	Manages per-tenant DatabaseContext instances

// Correct DI registration
services.AddSingleton<IDatabaseContext>(sp =>
    new DatabaseContext(connectionString, SqlClientFactory.Instance));

// Extended gateway as singleton
services.AddSingleton<IOrderGateway>(sp =>
    new OrderGateway(sp.GetRequiredService<IDatabaseContext>(), sp.GetRequiredService<IAuditValueResolver>()));

// AuditResolver is SINGLETON - must be thread-safe (e.g. uses IHttpContextAccessor)
services.AddSingleton<IAuditValueResolver, OidcAuditContextProvider>();

// TenantContextRegistry is SINGLETON
services.AddSingleton<ITenantContextRegistry, TenantContextRegistry>();

Extending TableGateway - THE CORRECT PATTERN

Inherit from TableGateway to add custom query methods. Don't wrap it in a separate service class.

public interface ICustomerGateway : ITableGateway<Customer, long>
{
    ValueTask<Customer?> GetByEmailAsync(string email);
    ValueTask<List<Customer>> GetActiveCustomersAsync();
    ValueTask<List<Customer>> SearchByNameAsync(string namePattern);
}

public class CustomerGateway : TableGateway<Customer, long>, ICustomerGateway
{
    public CustomerGateway(IDatabaseContext context, IAuditValueResolver resolver) : base(context, resolver)
    {
    }

    // Lookup by business key
    public async ValueTask<Customer?> GetByEmailAsync(string email)
    {
        var lookup = new Customer { Email = email };
        return await RetrieveOneAsync(lookup);
    }

    // Custom filtered query
    public async ValueTask<List<Customer>> GetActiveCustomersAsync()
    {
        var sc = BuildBaseRetrieve("c");

        sc.Query.Append(" WHERE ");
        sc.Query.Append(sc.WrapObjectName("c.is_active"));
        sc.Query.Append(" = ");
        var param = sc.AddParameterWithValue("active", DbType.Boolean, true);
        sc.Query.Append(sc.MakeParameterName(param));

        sc.Query.Append(" ORDER BY ");
        sc.Query.Append(sc.WrapObjectName("c.name"));

        return await LoadListAsync(sc);
    }

    // Search with LIKE
    public async ValueTask<List<Customer>> SearchByNameAsync(string namePattern)
    {
        var sc = BuildBaseRetrieve("c");

        sc.Query.Append(" WHERE ");
        sc.Query.Append(sc.WrapObjectName("c.name"));
        sc.Query.Append(" LIKE ");
        var param = sc.AddParameterWithValue("pattern", DbType.String, $"%{namePattern}%");
        sc.Query.Append(sc.MakeParameterName(param));

        return await LoadListAsync(sc);
    }
}

Audit Handling

IAuditValueResolver

Register as singleton and pass to TableGateway for entities with audit fields:

// Register singleton resolver
services.AddHttpContextAccessor();
services.AddSingleton<IAuditValueResolver, OidcAuditContextProvider>();

// OIDC/OAuth implementation
public class OidcAuditContextProvider : IAuditValueResolver
{
    private readonly IHttpContextAccessor _accessor;

    public OidcAuditContextProvider(IHttpContextAccessor accessor)
    {
        _accessor = accessor;
    }

    public IAuditValues Resolve()
    {
        var user = _accessor.HttpContext?.User;
        var id = user?.FindFirst(ClaimTypes.NameIdentifier)?.Value ?? "anonymous";
        return new AuditValues { UserId = id };
    }
}

Audit Fields on Entity

[Table("orders")]
public class Order
{
    [Id] [Column("id")] public long Id { get; set; }

    [CreatedBy] [Column("created_by")] public string CreatedBy { get; set; }
    [CreatedOn] [Column("created_at")] public DateTime CreatedAt { get; set; }
    [LastUpdatedBy] [Column("updated_by")] public string UpdatedBy { get; set; }
    [LastUpdatedOn] [Column("updated_at")] public DateTime UpdatedAt { get; set; }
}

Important: Both CreatedBy/On AND LastUpdatedBy/On are SET on CREATE.

Multi-Tenancy

Pattern 1: TenantContextRegistry

Use TenantContextRegistry as singleton to manage per-tenant DatabaseContext instances:

// Register TenantContextRegistry as singleton
services.AddSingleton<TenantContextRegistry>();

// Pull tenant-specific DatabaseContext from registry
public class TenantService
{
    private readonly TenantContextRegistry _registry;

    public TenantService(TenantContextRegistry registry)
    {
        _registry = registry;
    }

    public IDatabaseContext GetContextForTenant(string tenantId)
    {
        return _registry.GetContext(tenantId);
    }
}

Pattern 2: Per-Tenant Database Contexts (Different Database Types)

How the optional context parameter works:

• Without context parameter: Uses the default context from constructor (simple single-database apps)
• With context parameter: Uses the passed context instead (multi-tenant scenarios)

CRITICAL: Each tenant can use a different database type (SQL Server, PostgreSQL, SQLite, etc.). Pass the tenant's context to CRUD methods to route operations to the tenant's database:

// 1. Register TableGateway as singleton in DI
services.AddSingleton<ITableGateway<Order, long>>(sp =>
    new OrderGateway(defaultContext));  // Used when context param is omitted

// 2. Non-multi-tenant: Use default context (no parameter needed)
var order = await gateway.RetrieveOneAsync(orderId);  // Uses defaultContext

// 3. Multi-tenant: Resolve tenant context and pass to methods
var registry = services.GetRequiredService<ITenantContextRegistry>();
var tenantCtx = registry.GetContext("enterprise-client");  // Could be PostgreSQL, SQL Server, etc.

// 4. Get gateway from DI and pass tenant context to CRUD methods
var gateway = services.GetRequiredService<ITableGateway<Order, long>>();
var order = await gateway.RetrieveOneAsync(orderId, tenantCtx);  // Uses tenant's database
await gateway.CreateAsync(newOrder, tenantCtx);                  // Inserts to tenant's database
await gateway.UpdateAsync(order, tenantCtx);                     // Updates tenant's database
await gateway.DeleteAsync(orderId, tenantCtx);                   // Deletes from tenant's database

All CRUD methods accept optional context parameter:

• CreateAsync(entity, tenantContext) - Insert to tenant's database
• RetrieveOneAsync(id, tenantContext) - Select from tenant's database
• RetrieveAsync(ids, tenantContext) - Select multiple from tenant's database
• UpdateAsync(entity, tenantContext) - Update in tenant's database
• DeleteAsync(id, tenantContext) - Delete from tenant's database
• UpsertAsync(entity, tenantContext) - Upsert to tenant's database

This pattern enables:

• Single TableGateway instance (singleton) for all tenants
• Each tenant uses different database type (PostgreSQL, SQL Server, MySQL, etc.)
• SQL automatically generated with tenant's dialect (parameter markers, quoting)
• Connection pooling per tenant context
• No tenant_id filtering needed - physical database separation

Example: Multi-Tenant Controller

public class OrdersController : ControllerBase
{
    private readonly ITableGateway<Order, long> _orderGateway;
    private readonly ITenantContextRegistry _tenantRegistry;

    public OrdersController(
        ITableGateway<Order, long> orderGateway,
        ITenantContextRegistry tenantRegistry)
    {
        _orderGateway = orderGateway;
        _tenantRegistry = tenantRegistry;
    }

    [HttpGet("{id}")]
    public async Task<IActionResult> Get(long id)
    {
        // Resolve tenant from request (header, claim, etc.)
        var tenantId = User.FindFirst("tenant_id")?.Value;
        var tenantCtx = _tenantRegistry.GetContext(tenantId);

        // Pass tenant context - retrieves from tenant's database
        var order = await _orderGateway.RetrieveOneAsync(id, tenantCtx);
        return order is null ? NotFound() : Ok(order);
    }

    [HttpPost]
    public async Task<IActionResult> Create([FromBody] Order order)
    {
        var tenantId = User.FindFirst("tenant_id")?.Value;
        var tenantCtx = _tenantRegistry.GetContext(tenantId);

        // Pass tenant context - inserts to tenant's database
        await _orderGateway.CreateAsync(order, tenantCtx);
        return CreatedAtAction(nameof(Get), new { id = order.Id }, order);
    }
}

Core Concepts

[Id] vs [PrimaryKey] - CRITICAL DISTINCTION

[Id] - Surrogate/Pseudo Key:

• Single column only
• Used by TableGateway for CRUD operations
• [Id(false)] = DB-generated (identity/autoincrement)
• [Id(true)] or [Id] = client-provided value

[PrimaryKey] - Business/Natural Key:

• Can be composite (multiple columns with order)
• Enforced as UNIQUE constraint
• Used by RetrieveOneAsync(entity) for lookup
• NEVER put on same column as [Id]

[Table("order_items")]
public class OrderItem
{
    [Id(false)]               // Surrogate for FK references
    [Column("id")]
    public long Id { get; set; }

    [PrimaryKey(1)]           // Business key part 1
    [Column("order_id")]
    public int OrderId { get; set; }

    [PrimaryKey(2)]           // Business key part 2
    [Column("product_id")]
    public int ProductId { get; set; }
}

Custom SQL with SqlContainer

IMPORTANT: Always use WrapObjectName() for column names and aliases to ensure proper quoting per database dialect.

// Inside your extended gateway class
public async ValueTask<List<Order>> GetRecentLargeOrdersAsync(decimal minTotal)
{
    var sc = BuildBaseRetrieve("o");

    sc.Query.Append(" WHERE ");
    sc.Query.Append(sc.WrapObjectName("o.total"));
    sc.Query.Append(" >= ");
    var totalParam = sc.AddParameterWithValue("minTotal", DbType.Decimal, minTotal);
    sc.Query.Append(sc.MakeParameterName(totalParam));

    sc.Query.Append(" AND ");
    sc.Query.Append(sc.WrapObjectName("o.created_at"));
    sc.Query.Append(" >= ");
    var dateParam = sc.AddParameterWithValue("since", DbType.DateTime, DateTime.UtcNow.AddDays(-30));
    sc.Query.Append(sc.MakeParameterName(dateParam));

    sc.Query.Append(" ORDER BY ");
    sc.Query.Append(sc.WrapObjectName("o.total"));
    sc.Query.Append(" DESC");

    return await LoadListAsync(sc);
}

WrapObjectName behavior by database:

• SQL Server: [o].[total]
• PostgreSQL: "o"."total"
• MySQL: `o`.`total`
• Oracle: "o"."total"

Connection Management (DbMode)

Use lowest number possible:

Mode	Use Case
Standard (0)	Production default - pool per operation
KeepAlive (1)	Embedded DBs needing sentinel connection
SingleWriter (2)	File-based SQLite/DuckDB
SingleConnection (4)	In-memory :memory: databases
Best (15)	Auto-select optimal mode for the database

// Using IDatabaseContextConfiguration to specify DbMode with a factory
services.AddSingleton<IDatabaseContext>(sp =>
    new DatabaseContext(
        new DatabaseContextConfiguration { ConnectionString = connStr, DbMode = DbMode.SingleWriter },
        SqlClientFactory.Instance));

// Or using the string-based constructor (also supports DbMode directly)
services.AddSingleton<IDatabaseContext>(sp =>
    new DatabaseContext(connStr, "Microsoft.Data.SqlClient", DbMode.SingleWriter));

Transactions

Transactions are operation-scoped - create inside methods, never store as fields.

// Inside your extended gateway
public async ValueTask<bool> CancelOrderAsync(long orderId)
{
    await using var txn = await Context.BeginTransactionAsync();
    try
    {
        var order = await RetrieveOneAsync(orderId);
        if (order == null || order.Status == OrderStatus.Shipped)
        {
            await txn.RollbackAsync();
            return false;
        }

        order.Status = OrderStatus.Cancelled;
        var affected = await UpdateAsync(order);

        if (affected > 0)
        {
            await txn.CommitAsync();
            return true;
        }

        await txn.RollbackAsync();
        return false;
    }
    catch
    {
        await txn.RollbackAsync();
        throw;
    }
}

// With isolation level
using var txn = Context.BeginTransaction(IsolationLevel.Serializable);

// Async version with profile
await using var txn = await Context.BeginTransactionAsync(IsolationProfile.SafeNonBlockingReads);

Resource Safety: Always use await using for ITransactionContext and ITrackedReader to ensure connections are released correctly.

CRITICAL: Do NOT use TransactionScope

TransactionScope is incompatible with pengdows.crud's connection management. The "open late, close early" philosophy means each operation opens/closes its own connection, which causes:

1. Distributed transaction promotion - Second connection within TransactionScope promotes to MSDTC
2. Performance overhead - MSDTC has significant overhead and may not work in cloud environments
3. Broken semantics - Connections closing between operations lose transactional guarantees

Always use Context.BeginTransaction() which pins the connection for the transaction's lifetime.

Version Column (Optimistic Concurrency)

[Version]
[Column("version")]
public int Version { get; set; }

• Create: Auto-set to 1
• Update: Increments and adds WHERE version = @current
• Conflict: UpdateAsync automatically throws ConcurrencyConflictException

Parameter Naming Convention

Operation	Pattern	Example
INSERT	i{n}	i0, i1, i2
UPDATE SET	s{n}	s0, s1, s2
WHERE	w{n}	w0, w1, w2
VERSION	v{n}	v0, v1

// Reuse container with updated parameters
container.SetParameterValue("w0", newId);
container.SetParameterValue("s0", newValue);

Testing with FakeDb

public class OrderGatewayTests
{
    private readonly fakeDbFactory _factory;
    private readonly IDatabaseContext _context;
    private readonly OrderGateway _gateway;

    public OrderGatewayTests()
    {
        _factory = new fakeDbFactory(SupportedDatabase.Sqlite);
        _context = new DatabaseContext("Data Source=test", _factory);
        _gateway = new OrderGateway(_context);
    }

    [Fact]
    public async Task GetCustomerOrdersAsync_BuildsCorrectSql()
    {
        var orders = await _gateway.GetCustomerOrdersAsync(123);
        Assert.NotNull(orders);
    }

    [Fact]
    public void ConnectionFailure_ThrowsException()
    {
        var factory = fakeDbFactory.CreateFailingFactory(
            SupportedDatabase.PostgreSql,
            ConnectionFailureMode.FailOnOpen);
        var context = new DatabaseContext("test", factory);

        Assert.Throws<InvalidOperationException>(() =>
        {
            using var container = context.CreateSqlContainer("SELECT 1");
            container.ExecuteScalarRequiredAsync<int>().GetAwaiter().GetResult();
        });
    }
}

Supported Databases

SQL Server, PostgreSQL, Oracle, MySQL, MariaDB, SQLite, DuckDB, Firebird, CockroachDB, YugabyteDB, TiDB, Snowflake (+ AuroraMySql and AuroraPostgreSql — auto-detected at runtime, no extra setup)

Each uses optimal SQL syntax (MERGE vs ON CONFLICT vs ON DUPLICATE KEY UPDATE).

TDD Requirements

ALL code changes MUST follow TDD:

1. Write test FIRST
2. Run test - verify it fails
3. Write minimal implementation
4. Refactor while green
5. Repeat

• Minimum 83% coverage (CI enforced)
• Target 95%+ for new features
• NO skipped tests

Core Invariants

1. DatabaseContext is SINGLETON - one per connection string
2. TableGateway is SINGLETON - stateless, caches compiled accessors
3. Extend TableGateway - put custom query methods in inherited class, not wrapper service
4. IAuditValueResolver is SINGLETON - must be thread-safe/AsyncLocal-based to avoid captive dependencies in singleton gateways
5. TenantContextRegistry is SINGLETON - manages per-tenant contexts
6. Transactions are operation-scoped - create inside methods, never store as fields
7. ITrackedReader is a lease - pins connection until disposed, dispose promptly
8. DbMode.Best auto-selects - SQLite/DuckDB :memory: = SingleConnection; file-based SQLite/DuckDB = SingleWriter; LocalDB = KeepAlive; all others = Standard
9. Always use WrapObjectName() - for column names and aliases in custom SQL
10. NEVER use TransactionScope - incompatible with connection management, use Context.BeginTransaction()
11. Execution methods return ValueTask - not Task, for reduced allocations
12. All async methods have CancellationToken overloads - pass tokens through for proper cancellation