git.stella-ops.org/docs/db/RULES.md

Database Coding Rules

Version: 1.0.0 Status: APPROVED Last Updated: 2025-11-28

---

Purpose

This document defines mandatory rules and guidelines for all database-related code in StellaOps. These rules ensure consistency, maintainability, determinism, and security across all modules.

Compliance is mandatory. Deviations require explicit approval documented in the relevant sprint file.

---

1. Repository Pattern Rules

1.1 Interface Location

RULE: Repository interfaces MUST be defined in the Core/Domain layer, NOT in the storage layer.

✓ CORRECT:
  src/Scheduler/__Libraries/StellaOps.Scheduler.Core/Repositories/IScheduleRepository.cs

✗ INCORRECT:
  src/Scheduler/__Libraries/StellaOps.Scheduler.Storage.Postgres/IScheduleRepository.cs

1.2 Implementation Naming

RULE: Repository implementations MUST be prefixed with the storage technology.

// ✓ CORRECT
public sealed class PostgresScheduleRepository : IScheduleRepository
public sealed class MongoScheduleRepository : IScheduleRepository

// ✗ INCORRECT
public sealed class ScheduleRepository : IScheduleRepository

1.3 Dependency Injection

RULE: PostgreSQL repositories MUST be registered as Scoped. MongoDB repositories MAY be Singleton.

// PostgreSQL - always scoped (connection per request)
services.AddScoped<IScheduleRepository, PostgresScheduleRepository>();

// MongoDB - singleton is acceptable (stateless)
services.AddSingleton<IScheduleRepository, MongoScheduleRepository>();

1.4 No Direct SQL in Services

RULE: Business logic services MUST NOT contain raw SQL. All database access MUST go through repository interfaces.

// ✓ CORRECT
public class ScheduleService
{
    private readonly IScheduleRepository _repository;

    public Task<Schedule?> GetAsync(string id)
        => _repository.GetAsync(id);
}

// ✗ INCORRECT
public class ScheduleService
{
    private readonly NpgsqlDataSource _dataSource;

    public async Task<Schedule?> GetAsync(string id)
    {
        await using var conn = await _dataSource.OpenConnectionAsync();
        // Direct SQL here - FORBIDDEN
    }
}

---

2. Connection Management Rules

2.1 DataSource Pattern

RULE: Every module MUST have its own DataSource class that configures tenant context.

public sealed class SchedulerDataSource : IAsyncDisposable
{
    private readonly NpgsqlDataSource _dataSource;

    public async Task<NpgsqlConnection> OpenConnectionAsync(
        string tenantId,
        CancellationToken cancellationToken = default)
    {
        var connection = await _dataSource.OpenConnectionAsync(cancellationToken);
        await ConfigureSessionAsync(connection, tenantId, cancellationToken);
        return connection;
    }

    private static async Task ConfigureSessionAsync(
        NpgsqlConnection connection,
        string tenantId,
        CancellationToken cancellationToken)
    {
        // MANDATORY: Set tenant context and UTC timezone
        await using var cmd = connection.CreateCommand();
        cmd.CommandText = $"""
            SET app.tenant_id = '{tenantId}';
            SET timezone = 'UTC';
            SET statement_timeout = '30s';
            """;
        await cmd.ExecuteNonQueryAsync(cancellationToken);
    }
}

2.2 Connection Disposal

RULE: All NpgsqlConnection instances MUST be disposed via await using.

// ✓ CORRECT
await using var connection = await _dataSource.OpenConnectionAsync(tenantId, ct);

// ✗ INCORRECT
var connection = await _dataSource.OpenConnectionAsync(tenantId, ct);
// Missing disposal

2.3 Command Disposal

RULE: All NpgsqlCommand instances MUST be disposed via await using.

// ✓ CORRECT
await using var cmd = connection.CreateCommand();

// ✗ INCORRECT
var cmd = connection.CreateCommand();

2.4 Reader Disposal

RULE: All NpgsqlDataReader instances MUST be disposed via await using.

// ✓ CORRECT
await using var reader = await cmd.ExecuteReaderAsync(ct);

// ✗ INCORRECT
var reader = await cmd.ExecuteReaderAsync(ct);

---

3. Tenant Isolation Rules

3.1 Tenant ID Required

RULE: Every tenant-scoped repository method MUST require tenantId as the first parameter.

// ✓ CORRECT
Task<Schedule?> GetAsync(string tenantId, string scheduleId, CancellationToken ct);
Task<IReadOnlyList<Schedule>> ListAsync(string tenantId, QueryOptions? options, CancellationToken ct);

// ✗ INCORRECT
Task<Schedule?> GetAsync(string scheduleId, CancellationToken ct);

3.2 Tenant Filtering

RULE: All queries MUST include tenant_id in the WHERE clause for tenant-scoped tables.

// ✓ CORRECT
cmd.CommandText = """
    SELECT * FROM scheduler.schedules
    WHERE tenant_id = @tenant_id AND id = @id
    """;

// ✗ INCORRECT - Missing tenant filter
cmd.CommandText = """
    SELECT * FROM scheduler.schedules
    WHERE id = @id
    """;

3.3 Session Context Verification

RULE: DataSource MUST set app.tenant_id on every connection before executing any queries.

// ✓ CORRECT - Connection opened via DataSource sets tenant context
await using var connection = await _dataSource.OpenConnectionAsync(tenantId, ct);

// ✗ INCORRECT - Direct connection without tenant context
await using var connection = await _rawDataSource.OpenConnectionAsync(ct);

---

4. SQL Writing Rules

4.1 Parameterized Queries Only

RULE: All user-provided values MUST be passed as parameters. String interpolation is FORBIDDEN for values.

// ✓ CORRECT
cmd.CommandText = "SELECT * FROM users WHERE id = @id";
cmd.Parameters.AddWithValue("id", userId);

// ✗ INCORRECT - SQL INJECTION VULNERABILITY
cmd.CommandText = $"SELECT * FROM users WHERE id = '{userId}'";

4.2 SQL String Constants

RULE: SQL strings MUST be defined as const or static readonly fields, or as raw string literals in methods.

// ✓ CORRECT - Raw string literal
cmd.CommandText = """
    SELECT id, name, created_at
    FROM scheduler.schedules
    WHERE tenant_id = @tenant_id
    ORDER BY created_at DESC
    """;

// ✓ CORRECT - Constant
private const string SelectScheduleSql = """
    SELECT id, name, created_at
    FROM scheduler.schedules
    WHERE tenant_id = @tenant_id
    """;

// ✗ INCORRECT - Dynamic string building without reason
cmd.CommandText = "SELECT " + columns + " FROM " + table;

4.3 Schema Qualification

RULE: All table references MUST include the schema name.

// ✓ CORRECT
cmd.CommandText = "SELECT * FROM scheduler.schedules";

// ✗ INCORRECT - Missing schema
cmd.CommandText = "SELECT * FROM schedules";

4.4 Column Listing

RULE: SELECT statements MUST list columns explicitly. SELECT * is FORBIDDEN in production code.

// ✓ CORRECT
cmd.CommandText = """
    SELECT id, tenant_id, name, enabled, created_at
    FROM scheduler.schedules
    """;

// ✗ INCORRECT
cmd.CommandText = "SELECT * FROM scheduler.schedules";

4.5 Consistent Casing

RULE: SQL keywords MUST be lowercase for consistency with PostgreSQL conventions.

// ✓ CORRECT
cmd.CommandText = """
    select id, name
    from scheduler.schedules
    where tenant_id = @tenant_id
    order by created_at desc
    """;

// ✗ INCORRECT - Mixed casing
cmd.CommandText = """
    SELECT id, name
    FROM scheduler.schedules
    WHERE tenant_id = @tenant_id
    """;

---

5. Data Type Rules

5.1 UUID Handling

RULE: UUIDs MUST be passed as Guid type to Npgsql, NOT as strings.

// ✓ CORRECT
cmd.Parameters.AddWithValue("id", Guid.Parse(scheduleId));

// ✗ INCORRECT
cmd.Parameters.AddWithValue("id", scheduleId); // String

5.2 Timestamp Handling

RULE: All timestamps MUST be DateTimeOffset or DateTime with Kind = Utc.

// ✓ CORRECT
cmd.Parameters.AddWithValue("created_at", DateTimeOffset.UtcNow);
cmd.Parameters.AddWithValue("created_at", DateTime.UtcNow);

// ✗ INCORRECT - Local time
cmd.Parameters.AddWithValue("created_at", DateTime.Now);

5.3 JSONB Serialization

RULE: JSONB columns MUST be serialized using System.Text.Json.JsonSerializer with consistent options.

// ✓ CORRECT
var json = JsonSerializer.Serialize(obj, JsonSerializerOptions.Default);
cmd.Parameters.AddWithValue("config", json);

// ✗ INCORRECT - Newtonsoft or inconsistent serialization
var json = Newtonsoft.Json.JsonConvert.SerializeObject(obj);

5.3.1 Generated Columns for JSONB Hot Keys

RULE: Frequently-queried JSONB fields (>10% of queries) SHOULD be extracted as generated columns.

When to use generated columns:

• Field is used in WHERE clauses frequently
• Field is used in JOIN conditions
• Field is used in GROUP BY or ORDER BY
• Query planner needs cardinality statistics

-- ✓ CORRECT: Generated column for hot JSONB field
ALTER TABLE scheduler.runs
ADD COLUMN finding_count INT GENERATED ALWAYS AS ((stats->>'findingCount')::int) STORED;

CREATE INDEX idx_runs_finding_count ON scheduler.runs(tenant_id, finding_count);

RULE: Generated column names MUST follow snake_case convention matching the JSON path.

-- ✓ CORRECT naming
doc->>'bomFormat'      → bom_format
stats->>'findingCount' → finding_count
raw->>'schemaVersion'  → schema_version

-- ✗ INCORRECT naming
doc->>'bomFormat' → bomFormat, format, bf

RULE: Generated columns MUST be added with concurrent index creation in production.

-- ✓ CORRECT: Non-blocking migration
ALTER TABLE scheduler.runs ADD COLUMN finding_count INT GENERATED ALWAYS AS (...) STORED;
CREATE INDEX CONCURRENTLY idx_runs_finding_count ON scheduler.runs(finding_count);
ANALYZE scheduler.runs;

-- ✗ INCORRECT: Blocking migration
CREATE INDEX idx_runs_finding_count ON scheduler.runs(finding_count); -- Blocks table

Reference: See SPECIFICATION.md Section 6.4 for detailed guidelines.

5.4 Null Handling

RULE: Nullable values MUST use DBNull.Value when null.

// ✓ CORRECT
cmd.Parameters.AddWithValue("description", (object?)schedule.Description ?? DBNull.Value);

// ✗ INCORRECT - Will fail or behave unexpectedly
cmd.Parameters.AddWithValue("description", schedule.Description); // If null

5.5 Array Handling

RULE: PostgreSQL arrays MUST be passed as .NET arrays with explicit type.

// ✓ CORRECT
cmd.Parameters.AddWithValue("tags", schedule.Tags.ToArray());

// ✗ INCORRECT - List won't map correctly
cmd.Parameters.AddWithValue("tags", schedule.Tags);

---

6. Transaction Rules

6.1 Explicit Transactions

RULE: Operations affecting multiple tables MUST use explicit transactions.

// ✓ CORRECT
await using var transaction = await connection.BeginTransactionAsync(ct);
try
{
    // Multiple operations
    await cmd1.ExecuteNonQueryAsync(ct);
    await cmd2.ExecuteNonQueryAsync(ct);
    await transaction.CommitAsync(ct);
}
catch
{
    await transaction.RollbackAsync(ct);
    throw;
}

6.2 Transaction Isolation

RULE: Default isolation level is ReadCommitted. Stricter levels MUST be documented.

// ✓ CORRECT - Default
await using var transaction = await connection.BeginTransactionAsync(ct);

// ✓ CORRECT - Explicit stricter level with documentation
// Using Serializable for financial consistency requirement
await using var transaction = await connection.BeginTransactionAsync(
    IsolationLevel.Serializable, ct);

6.3 No Nested Transactions

RULE: Nested transactions are NOT supported. Use savepoints if needed.

// ✗ INCORRECT - Nested transaction
await using var tx1 = await connection.BeginTransactionAsync(ct);
await using var tx2 = await connection.BeginTransactionAsync(ct); // FAILS

// ✓ CORRECT - Savepoint for partial rollback
await using var transaction = await connection.BeginTransactionAsync(ct);
await transaction.SaveAsync("savepoint1", ct);
// ... operations ...
await transaction.RollbackAsync("savepoint1", ct); // Partial rollback
await transaction.CommitAsync(ct);

---

7. Error Handling Rules

7.1 PostgreSQL Exception Handling

RULE: Catch PostgresException for database-specific errors, not generic exceptions.

// ✓ CORRECT
try
{
    await cmd.ExecuteNonQueryAsync(ct);
}
catch (PostgresException ex) when (ex.SqlState == "23505") // Unique violation
{
    throw new DuplicateEntityException($"Entity already exists: {ex.ConstraintName}");
}

// ✗ INCORRECT - Too broad
catch (Exception ex)
{
    // Can't distinguish database errors from other errors
}

7.2 Constraint Violation Handling

RULE: Unique constraint violations MUST be translated to domain exceptions.

SQL State	Meaning	Domain Exception
23505	Unique violation	DuplicateEntityException
23503	Foreign key violation	ReferenceNotFoundException
23502	Not null violation	ValidationException
23514	Check constraint	ValidationException

7.3 Timeout Handling

RULE: Query timeouts MUST be caught and logged with context.

try
{
    await cmd.ExecuteNonQueryAsync(ct);
}
catch (NpgsqlException ex) when (ex.InnerException is TimeoutException)
{
    _logger.LogWarning(ex, "Query timeout for schedule {ScheduleId}", scheduleId);
    throw new QueryTimeoutException("Database query timed out", ex);
}

---

8. Pagination Rules

8.1 Keyset Pagination

RULE: Use keyset pagination, NOT offset pagination for large result sets.

// ✓ CORRECT - Keyset pagination
cmd.CommandText = """
    select id, name, created_at
    from scheduler.schedules
    where tenant_id = @tenant_id
      and (created_at, id) < (@cursor_created_at, @cursor_id)
    order by created_at desc, id desc
    limit @page_size
    """;

// ✗ INCORRECT - Offset pagination (slow for large offsets)
cmd.CommandText = """
    select id, name, created_at
    from scheduler.schedules
    where tenant_id = @tenant_id
    order by created_at desc
    limit @page_size offset @offset
    """;

8.2 Default Page Size

RULE: Default page size MUST be 50. Maximum page size MUST be 1000.

public class QueryOptions
{
    public int PageSize { get; init; } = 50;

    public int GetValidatedPageSize()
        => Math.Clamp(PageSize, 1, 1000);
}

8.3 Continuation Tokens

RULE: Pagination cursors MUST be opaque, encoded tokens containing sort key values.

public record PaginationCursor(DateTimeOffset CreatedAt, Guid Id)
{
    public string Encode()
        => Convert.ToBase64String(
            JsonSerializer.SerializeToUtf8Bytes(this));

    public static PaginationCursor? Decode(string? token)
        => string.IsNullOrEmpty(token)
            ? null
            : JsonSerializer.Deserialize<PaginationCursor>(
                Convert.FromBase64String(token));
}

---

9. Ordering Rules

9.1 Deterministic Ordering

RULE: All queries returning multiple rows MUST have an ORDER BY clause that produces deterministic results.

// ✓ CORRECT - Deterministic (includes unique column)
cmd.CommandText = """
    select * from scheduler.runs
    order by created_at desc, id asc
    """;

// ✗ INCORRECT - Non-deterministic (created_at may have ties)
cmd.CommandText = """
    select * from scheduler.runs
    order by created_at desc
    """;

9.2 Stable Ordering for JSONB Arrays

RULE: When serializing arrays to JSONB, ensure consistent ordering.

// ✓ CORRECT - Sorted before serialization
var sortedTags = schedule.Tags.OrderBy(t => t).ToList();
cmd.Parameters.AddWithValue("tags", sortedTags.ToArray());

// ✗ INCORRECT - Order may vary
cmd.Parameters.AddWithValue("tags", schedule.Tags.ToArray());

---

10. Audit Rules

10.1 Timestamp Columns

RULE: All mutable tables MUST have created_at and updated_at columns.

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

10.2 Update Timestamp

RULE: updated_at MUST be set on every UPDATE operation.

// ✓ CORRECT
cmd.CommandText = """
    update scheduler.schedules
    set name = @name, updated_at = @updated_at
    where id = @id
    """;
cmd.Parameters.AddWithValue("updated_at", DateTimeOffset.UtcNow);

// ✗ INCORRECT - Missing updated_at
cmd.CommandText = """
    update scheduler.schedules
    set name = @name
    where id = @id
    """;

10.3 Soft Delete Pattern

RULE: For audit-required entities, use soft delete with deleted_at and deleted_by.

cmd.CommandText = """
    update scheduler.schedules
    set deleted_at = @deleted_at, deleted_by = @deleted_by
    where tenant_id = @tenant_id and id = @id and deleted_at is null
    """;

---

11. Testing Rules

11.1 Integration Test Database

RULE: Integration tests MUST use Testcontainers with PostgreSQL.

public class PostgresFixture : IAsyncLifetime
{
    private readonly PostgreSqlContainer _container = new PostgreSqlBuilder()
        .WithImage("postgres:16")
        .Build();

    public string ConnectionString => _container.GetConnectionString();

    public Task InitializeAsync() => _container.StartAsync();
    public Task DisposeAsync() => _container.DisposeAsync().AsTask();
}

11.2 Test Isolation

RULE: Each test MUST run in a transaction that is rolled back after the test.

public class ScheduleRepositoryTests : IClassFixture<PostgresFixture>
{
    [Fact]
    public async Task GetAsync_ReturnsSchedule_WhenExists()
    {
        await using var connection = await _fixture.OpenConnectionAsync();
        await using var transaction = await connection.BeginTransactionAsync();

        try
        {
            // Arrange, Act, Assert
        }
        finally
        {
            await transaction.RollbackAsync();
        }
    }
}

11.3 Determinism Tests

RULE: Every repository MUST have tests verifying deterministic output ordering.

[Fact]
public async Task ListAsync_ReturnsDeterministicOrder()
{
    // Insert records with same created_at
    // Verify order is consistent across multiple calls
    var result1 = await _repository.ListAsync(tenantId);
    var result2 = await _repository.ListAsync(tenantId);

    result1.Should().BeEquivalentTo(result2, options =>
        options.WithStrictOrdering());
}

---

12. Migration Rules

12.1 Idempotent Migrations

RULE: All migrations MUST be idempotent using IF NOT EXISTS / IF EXISTS.

-- ✓ CORRECT
CREATE TABLE IF NOT EXISTS scheduler.schedules (...);
CREATE INDEX IF NOT EXISTS idx_schedules_tenant ON scheduler.schedules(tenant_id);

-- ✗ INCORRECT
CREATE TABLE scheduler.schedules (...);  -- Fails if exists

12.2 No Breaking Changes

RULE: Migrations MUST NOT break existing code. Use expand-contract pattern.

Expand Phase:
1. Add new column as nullable
2. Deploy code that writes to both old and new columns
3. Backfill new column

Contract Phase:
4. Deploy code that reads from new column only
5. Add NOT NULL constraint
6. Drop old column

12.3 Index Creation

RULE: Large table indexes MUST be created with CONCURRENTLY.

-- ✓ CORRECT - Won't lock table
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_large_table_col
    ON schema.large_table(column);

-- ✗ INCORRECT - Locks table during creation
CREATE INDEX idx_large_table_col ON schema.large_table(column);

---

13. Configuration Rules

13.1 Backend Selection

RULE: Storage backend MUST be configurable per module.

{
  "Persistence": {
    "Authority": "Postgres",
    "Scheduler": "Postgres",
    "Concelier": "Mongo"
  }
}

13.2 Connection String Security

RULE: Connection strings MUST NOT be logged or included in exception messages.

// ✓ CORRECT
catch (NpgsqlException ex)
{
    _logger.LogError(ex, "Database connection failed for module {Module}", moduleName);
    throw;
}

// ✗ INCORRECT
catch (NpgsqlException ex)
{
    _logger.LogError("Failed to connect: {ConnectionString}", connectionString);
}

13.3 Timeout Configuration

RULE: Command timeout MUST be configurable with sensible defaults.

public class PostgresOptions
{
    public int CommandTimeoutSeconds { get; set; } = 30;
    public int ConnectionTimeoutSeconds { get; set; } = 15;
}

---

14. Documentation Rules

14.1 Repository Method Documentation

RULE: All public repository methods MUST have XML documentation.

/// <summary>
/// Retrieves a schedule by its unique identifier.
/// </summary>
/// <param name="tenantId">The tenant identifier for isolation.</param>
/// <param name="scheduleId">The schedule's unique identifier.</param>
/// <param name="cancellationToken">Cancellation token.</param>
/// <returns>The schedule if found; otherwise, null.</returns>
Task<Schedule?> GetAsync(string tenantId, string scheduleId, CancellationToken cancellationToken);

14.2 SQL Comment Headers

RULE: Complex SQL queries SHOULD have a comment explaining the purpose.

cmd.CommandText = """
    -- Find schedules due to fire within the next minute
    -- Uses compound index (tenant_id, next_fire_time) for efficiency
    select s.id, s.name, t.next_fire_time
    from scheduler.schedules s
    join scheduler.triggers t on t.schedule_id = s.id
    where s.tenant_id = @tenant_id
      and s.enabled = true
      and t.next_fire_time <= @window_end
    order by t.next_fire_time asc
    """;

---

Enforcement

Code Review Checklist

• Repository interfaces in Core layer
• PostgreSQL repositories prefixed with Postgres
• All connections disposed with await using
• Tenant ID required and used in all queries
• Parameterized queries (no string interpolation for values)
• Schema-qualified table names
• Explicit column lists (no SELECT *)
• Deterministic ORDER BY clauses
• Timestamps are UTC
• JSONB serialized with System.Text.Json
• PostgresException caught for constraint violations
• Integration tests use Testcontainers

Automated Checks

These rules are enforced by:

• Roslyn analyzers in StellaOps.Analyzers
• SQL linting in CI pipeline
• Integration test requirements

---

Document Version: 1.0.0 Last Updated: 2025-11-28