Keep decisions correct when things fail

Let’s begin with the workflow

The domain can make a valid decision in memory, yet the system can still fail while saving it or telling another service. We will follow the same approval through SQL Server and into background processing, stopping at the places where a crash or concurrent update changes the outcome.

Before each experiment, predict what another database connection would see. Then compare that prediction with evidence from a fresh context. Use a dedicated SQL Server test environment with synthetic data. The walkthroughs below describe tests to implement; no live database is connected to this lesson.

How to use this guide: Read one chapter at a time. Predict the result before inspecting an example, then explain the trade-off in your own words. Use the guided exercise at the end to apply the idea.

1. Purpose of Phase 3

Phase 2 created the business core. The Loan aggregate protects approval rules, value objects prevent invalid values, commands express intent, handlers coordinate use cases, and queries return purpose-built DTOs.

Phase 3 makes that model durable and reliable. It answers these practical questions:

• How does EF Core reconstruct a protected domain aggregate from relational columns?
• What exactly is tracked by a DbContext?
• When does SaveChangesAsync() create a transaction?
• How do we prevent two users from silently overwriting one another?
• Why can an application still block even when it uses optimistic concurrency?
• How do we commit a Loan change and the promise to publish an event atomically?
• How does a long-running singleton worker use a short-lived scoped DbContext safely?
• Why may an outbox publisher publish the same message twice?
• How does an inbox prevent a duplicate message from repeating the local business effect?
• Which claims need a real SQL Server integration test rather than a mock?

The central consistency rule is:

The authoritative business update and its outbox record commit in one short SQL transaction. Publishing happens later, and every consumer assumes duplicate delivery is possible.

2. Phase map

Phase 3 is divided into these implementation passages:

1. Operational schema and EF Core mapping.
2. Loading and saving the Loan aggregate.
3. Transactions and SaveChangesAsync().
4. Optimistic concurrency with SQL Server rowversion.
5. Locking, blocking and transaction duration.
6. Transactional outbox creation.
7. The background outbox publisher.
8. Consumer idempotency and the inbox pattern.
9. Integration tests, operational proof and completion criteria.

3. The complete reliable flow

The finished implementation works like this:

1. Angular loads an authorized Loan and receives its current version.
2. The user submits an approval reason and the expected version.
3. The API authenticates, validates and authorizes the request.
4. The application handler loads the authoritative Loan through a tracked EF query.
5. Infrastructure assigns the caller's expected version as the original concurrency value.
6. The aggregate validates the transition and records LoanApproved.
7. Before saving, the persistence pipeline converts the domain event into a versioned outbox record.
8. EF Core updates the Loan and inserts the outbox record in one SQL transaction.
9. SQL Server checks the original rowversion and rejects a stale update.
10. The API returns success only after the database commit.
11. A background worker claims pending outbox rows in a short SQL operation.
12. The worker publishes the stored integration message outside the claim transaction.
13. It marks successful outbox records as processed.
14. A Service Bus consumer receives the message.
15. The consumer checks its inbox using the stable message ID.
16. It performs its local database effect and records inbox completion in one transaction.
17. It completes the Service Bus message after the local commit.
18. A redelivered message is recognised and does not repeat the local business effect.

This flow does not claim exactly-once delivery. It provides atomic local commits, recoverable publication and idempotent local consumption.

4. Passage 3A Operational schema and EF Core mapping

4.1 Domain model versus relational model

The domain uses concepts:

Loan
├── LoanId
├── Money
├── LoanStatus
├── ApprovalReason
├── Approval actor and time
└── Domain events

SQL Server uses tables, rows, columns, keys, constraints and indexes. EF Core maps between the two representations.

A practical Loans schema begins with:

The Money value object becomes two columns because it has no independent identity or lifecycle:

Money.Amount   → PrincipalAmount
Money.Currency → Currency

4.2 Loan mapping

public sealed class LoanConfiguration
    : IEntityTypeConfiguration<Loan>
{
    public void Configure(EntityTypeBuilder<Loan> builder)
    {
        builder.ToTable("Loans");
        builder.HasKey(loan => loan.Id);

        // Convert the strongly typed domain identifier to a SQL Guid.
        builder.Property(loan => loan.Id)
            .HasConversion(
                id => id.Value,
                value => LoanId.From(value));

        builder.Property(loan => loan.Reference)
            .HasMaxLength(30)
            .IsRequired();

        // Money remains a value object in C# but shares the Loans row.
        builder.OwnsOne(
            loan => loan.Principal,
            money =>
            {
                money.Property(value => value.Amount)
                    .HasColumnName("PrincipalAmount")
                    .HasPrecision(18, 2)
                    .IsRequired();

                money.Property(value => value.Currency)
                    .HasColumnName("Currency")
                    .HasMaxLength(3)
                    .IsFixedLength()
                    .IsRequired();
            });

        builder.Property(loan => loan.Status)
            .HasConversion<int>()
            .IsRequired();

        builder.Property(loan => loan.BusinessUnitId)
            .HasMaxLength(50)
            .IsRequired();

        builder.Property(loan => loan.ApprovedBy)
            .HasMaxLength(100);

        builder.Property(loan => loan.ApprovalReason)
            .HasConversion(
                reason => reason == null ? null : reason.Value,
                value => value == null
                    ? null
                    : ApprovalReason.Create(value))
            .HasMaxLength(500);

        // SQL Server generates this value after every update.
        builder.Property(loan => loan.Version)
            .IsRowVersion();

        builder.HasIndex(loan => loan.Reference)
            .IsUnique();
    }
}

Mapping belongs in Infrastructure. The Domain does not know column names, precision, indexes, EF conversions or SQL types.

4.3 Apply mapping classes automatically

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfigurationsFromAssembly(
        typeof(LoanDbContext).Assembly);

    base.OnModelCreating(modelBuilder);
}

Separate configuration classes keep OnModelCreating manageable and give each persistence model a clear owner.

4.4 Domain validation and SQL constraints

The Domain remains the primary business-rule boundary, but SQL constraints provide defence in depth. A check such as this protects against support scripts, imports or accidental application code:

ALTER TABLE Loans
ADD CONSTRAINT CK_Loans_PrincipalAmount_NonNegative
CHECK (PrincipalAmount >= 0);

The database constraint does not replace Money.Create(). The Domain gives immediate business meaning; SQL protects stored integrity.

5. Passage 3B Loading and saving aggregates

5.1 LoanDbContext

public sealed class LoanDbContext
    : DbContext, IUnitOfWork
{
    public LoanDbContext(
        DbContextOptions<LoanDbContext> options)
        : base(options)
    {
    }

    public DbSet<Loan> Loans => Set<Loan>();
    public DbSet<OutboxMessage> OutboxMessages => Set<OutboxMessage>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(
            typeof(LoanDbContext).Assembly);

        base.OnModelCreating(modelBuilder);
    }
}

DbContext is both a change tracker and a unit of work. One scoped context represents one short application work boundary.

5.2 Dependency injection

builder.Services.AddDbContext<LoanDbContext>(options =>
{
    var connectionString = builder.Configuration
        .GetConnectionString("LoanDatabase");

    options.UseSqlServer(connectionString);
});

builder.Services.AddScoped<ILoanRepository, EfLoanRepository>();

// Resolve IUnitOfWork to the same scoped DbContext instance.
builder.Services.AddScoped<IUnitOfWork>(provider =>
    provider.GetRequiredService<LoanDbContext>());

The repository and IUnitOfWork must use the same context. If they used different contexts, the context asked to save would not know about the changes tracked by the repository's context.

5.3 Tracked repository query

public sealed class EfLoanRepository : ILoanRepository
{
    private readonly LoanDbContext _dbContext;

    public EfLoanRepository(LoanDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public Task<Loan?> GetForUpdateAsync(
        LoanId loanId,
        CancellationToken cancellationToken)
    {
        // No AsNoTracking: this aggregate will be changed and saved.
        return _dbContext.Loans.SingleOrDefaultAsync(
            loan => loan.Id == loanId,
            cancellationToken);
    }

    public void Add(Loan loan)
    {
        _dbContext.Loans.Add(loan);
    }
}

EF creates the aggregate, stores its original values internally and tracks later domain changes. When the handler calls loan.Approve(...), EF sees the difference during save.

5.4 Why not call Update after a tracked query

This is normally unnecessary:

_dbContext.Loans.Update(loan);

The context already tracks a Loan that it loaded. Calling Update() can mark every mapped property as modified, producing broader SQL than required. The normal flow is:

var loan = await repository.GetForUpdateAsync(id, cancellationToken);
loan!.Approve(reason, actorId, clock.UtcNow);
await unitOfWork.SaveChangesAsync(cancellationToken);

5.5 Do not attach a client-supplied entity

Angular submits intention, not an authoritative entity. The API must not accept arbitrary status, actor or amount values and mark the object modified. That creates mass-assignment and stale-data risks.

The server loads current state, obtains the trusted actor from authentication, calls domain behaviour and saves only the permitted transition.

5.6 Context lifetime

The normal web request is:

Request starts
→ scoped DbContext created
→ aggregate loaded
→ behaviour applied
→ changes saved
→ request ends
→ DbContext disposed

Do not store a context in a singleton, share it across concurrent threads, cache tracked entities globally or keep it alive for hours. DbContext is not thread-safe.

6. Passage 3C Transactions and SaveChangesAsync

6.1 Atomic business outcome

Loan approval requires:

Update Loan
Insert OutboxMessage

The permitted results are:

Both commit
or
both roll back

If both changes are tracked by one DbContext and included in one SaveChangesAsync(), EF Core normally uses a database transaction for the relational save.

6.2 Conceptual SQL

BEGIN TRANSACTION;

UPDATE Loans
SET Status = @Approved,
    ApprovalReason = @Reason,
    ApprovedBy = @Actor,
    ApprovedAtUtc = @OccurredAt
WHERE Id = @LoanId
  AND Version = @ExpectedVersion;

INSERT INTO OutboxMessages
(
    Id, EventType, Payload,
    OccurredAtUtc, CreatedAtUtc, CorrelationId
)
VALUES
(
    @MessageId, @EventType, @Payload,
    @OccurredAt, @CreatedAt, @CorrelationId
);

COMMIT TRANSACTION;

If either statement fails, the transaction rolls back.

6.3 Dangerous split save

loan.Approve(reason, actorId, clock.UtcNow);

// First commit: Loan is now permanently Approved.
await dbContext.SaveChangesAsync(cancellationToken);

dbContext.OutboxMessages.Add(outboxMessage);

// If this fails, the event promise is missing.
await dbContext.SaveChangesAsync(cancellationToken);

The first committed transaction cannot be undone by failure of the second save. Prepare both tracked changes before one commit.

6.4 When an explicit transaction is needed

One SaveChangesAsync() is often sufficient. Use BeginTransactionAsync() only when several database steps genuinely must share a transaction, such as coordinated EF and Dapper operations or unavoidable multiple saves.

await using var transaction =
    await dbContext.Database.BeginTransactionAsync(cancellationToken);

try
{
    await dbContext.SaveChangesAsync(cancellationToken);
    await additionalSql.ExecuteAsync(
        dbContext.Database.CurrentTransaction!,
        cancellationToken);
    await transaction.CommitAsync(cancellationToken);
}
catch
{
    await transaction.RollbackAsync(cancellationToken);
    throw;
}

6.5 Keep external work outside SQL transactions

Do not hold database locks while calling email, Blob Storage, Service Bus or a remote HTTP service. A slow dependency turns into a long SQL transaction, blocking, timeouts and connection pressure.

The safe shape is:

Before the transaction
    Obtain external evidence genuinely required for the decision

Short SQL transaction
    Update Loan
    Insert outbox record
    Commit

After commit
    Background publisher and consumers perform secondary work

Rollback can undo only resources participating in the transaction. It cannot unsend an email or retract a message already accepted by Service Bus.

7. Passage 3D Optimistic concurrency with rowversion

7.1 The stale-screen problem

Two officers may open the same Pending Loan. Officer A changes it first; Officer B later submits an action from an old screen. Without concurrency protection, B may overwrite newer work.

Optimistic concurrency does not lock the record while a user reads the screen. It carries a version from read to write and succeeds only if the stored version is unchanged.

7.2 Rowversion facts

SQL Server rowversion is an automatically generated binary value. It is not a date or time. The application does not increment it.

builder.Property(loan => loan.Version)
    .IsRowVersion();

An API can transport the bytes as Base64:

{
  "loanId": "8df34403-0465-4ed5-99d5-f18943421ec7",
  "status": "Pending",
  "version": "AAAAAAAAB9E="
}

Angular returns the same opaque value with the command.

7.3 Update condition

EF conceptually generates:

UPDATE Loans
SET Status = @Status,
    ApprovedBy = @Actor
OUTPUT INSERTED.Version
WHERE Id = @LoanId
  AND Version = @OriginalVersion;

If the version still matches, one row is updated and SQL generates a new value. If another transaction changed the row, zero rows match and EF throws DbUpdateConcurrencyException.

7.4 Set the caller's expected version

Infrastructure must ensure EF uses the version returned by the caller as the original value:

// The API decodes its Base64 request value into the byte[] command.
// Phase 2 defines ExpectedVersion as byte[].
var expectedVersion = command.ExpectedVersion;

dbContext.Entry(loan)
    .Property(x => x.Version)
    .OriginalValue = expectedVersion;

This EF-specific operation belongs in Infrastructure, potentially behind the repository method.

7.5 Do not silently retry a business decision

An automatic reload-and-retry could overwrite a meaningful intervening decision. Return a conflict, reload the latest state and ask the user to reconsider.

The later API mapping is:

409 Conflict

7.6 Real concurrency test

[Fact]
public async Task Stale_context_cannot_overwrite_newer_state()
{
    await using var contextA = CreateDbContext();
    await using var contextB = CreateDbContext();

    var loanA = await contextA.Loans.SingleAsync();
    var loanB = await contextB.Loans.SingleAsync();

    loanA.MarkRequiredChecksCompleted();
    await contextA.SaveChangesAsync();

    loanB.MarkRequiredChecksCompleted();

    await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
        () => contextB.SaveChangesAsync());

    // A fresh context proves committed database truth.
    await using var verification = CreateDbContext();
    var stored = await verification.Loans.SingleAsync();
    Assert.True(stored.RequiredChecksCompleted);
}

rowversion answers whether the row changed since it was read. Domain invariants answer whether the requested business transition is valid. Both are required.

8. Passage 3E Locking and blocking

8.1 Optimistic concurrency does not remove locks

rowversion detects stale state. SQL Server locks coordinate statements and transactions currently running at the same time.

Normal short locking is healthy:

Begin transaction
Update Loan
Insert outbox
Commit
Release locks

Blocking occurs when one session waits for an incompatible lock held by another session.

8.2 Need-to-know lock types

SQL Server may lock keys, rows, pages or broader structures depending on the plan, affected rows, memory, isolation and escalation.

8.3 How application code creates blocking

The most common application contribution is a long transaction:

await using var transaction =
    await dbContext.Database.BeginTransactionAsync(cancellationToken);

await dbContext.SaveChangesAsync(cancellationToken);

// Dangerous: locks may remain while a remote system responds.
await emailClient.SendAsync(message, cancellationToken);

await transaction.CommitAsync(cancellationToken);

External calls, manual interaction and large unbounded work do not belong inside a short OLTP transaction.

8.4 Indexes and blocking

An appropriate index helps SQL locate a small set of rows. A scan touches more pages, runs longer, can acquire more locks and increases blocking risk. Indexes also cost storage and write work, so they must be justified by query patterns and plans.

8.5 Blocking, timeout and deadlock

• Blocking: one session waits for another and may later continue.
• Command timeout: the caller stops waiting after its configured budget; it does not identify the root cause.
• Deadlock: sessions form a wait cycle; SQL Server selects a victim and rolls it back.

Consistent table-access order reduces one common deadlock source.

8.6 Investigation flow

When users say the application is hanging:

1. Identify the slow endpoint.
2. Check request and dependency duration in telemetry.
3. Confirm whether the API waits on SQL.
4. Inspect active SQL requests and waits.
5. Identify the blocked session.
6. Identify its blocking session.
7. Examine the blocker's open transaction.
8. Find the SQL statement.
9. Inspect its plan and indexes.
10. Find the application transaction boundary.
11. Correct the transaction or query.
12. Test with concurrent load.
13. Monitor after release.

Do not reach immediately for NOLOCK. Dirty, missing, duplicated or rolled-back data is unacceptable for authoritative loan decisions.

8.7 Reducing blocking safely

• Keep transactions short.
• Place external calls outside them.
• Filter and page queries.
• Add evidence-based indexes.
• Update large datasets in controlled batches.
• Access shared tables in a consistent order.
• Dispose, commit or roll back every transaction.
• Use optimistic concurrency for user-edit conflicts.
• Consider row-versioning isolation only after analysis.

9. Passage 3F Transactional outbox creation

9.1 The dual-write problem

Saving SQL then publishing can lose an event if Service Bus fails. Publishing then saving can announce a change that later rolls back. The outbox turns both immediate changes into one SQL transaction.

9.2 Outbox schema

CREATE TABLE OutboxMessages
(
    Id uniqueidentifier NOT NULL,
    EventType nvarchar(200) NOT NULL,
    Payload nvarchar(max) NOT NULL,
    OccurredAtUtc datetimeoffset NOT NULL,
    CreatedAtUtc datetimeoffset NOT NULL,
    CorrelationId nvarchar(100) NULL,
    CausationId nvarchar(100) NULL,
    ProcessedAtUtc datetimeoffset NULL,
    AttemptCount int NOT NULL DEFAULT 0,
    LastAttemptAtUtc datetimeoffset NULL,
    NextAttemptAtUtc datetimeoffset NULL,
    Error nvarchar(2000) NULL,
    LockedBy nvarchar(100) NULL,
    LockedUntilUtc datetimeoffset NULL,
    CONSTRAINT PK_OutboxMessages PRIMARY KEY (Id)
);

Pending means ProcessedAtUtc IS NULL and the record is eligible according to its retry and lease values.

9.3 Domain event and integration contract

The aggregate records an internal fact:

public sealed record LoanApproved(
    Guid EventId,
    LoanId LoanId,
    string ActorId,
    DateTimeOffset OccurredAtUtc) : IDomainEvent;

Infrastructure maps it to a deliberate external contract:

public sealed record LoanApprovedIntegrationEvent(
    Guid MessageId,
    Guid LoanId,
    string ApprovedBy,
    DateTimeOffset ApprovedAtUtc,
    int ContractVersion);

The integration contract is versioned and does not expose the internal aggregate indiscriminately.

9.4 SaveChanges interceptor

An interceptor can collect domain events before SQL is generated:

public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
    DbContextEventData eventData,
    InterceptionResult<int> result,
    CancellationToken cancellationToken = default)
{
    if (eventData.Context is not LoanDbContext dbContext)
    {
        return base.SavingChangesAsync(
            eventData, result, cancellationToken);
    }

    var loansWithEvents = dbContext.ChangeTracker
        .Entries<Loan>()
        .Select(entry => entry.Entity)
        .Where(loan => loan.DomainEvents.Count > 0)
        .ToList();

    foreach (var loan in loansWithEvents)
    {
        foreach (var domainEvent in loan.DomainEvents)
        {
            var outboxMessage = _mapper.ToOutboxMessage(
                domainEvent,
                _correlation.CorrelationId,
                _clock.UtcNow);

            // Same DbContext means same relational save transaction.
            dbContext.OutboxMessages.Add(outboxMessage);
        }
    }

    return base.SavingChangesAsync(
        eventData, result, cancellationToken);
}

The interceptor must be registered on the DbContext options before this hook runs. This excerpt omits that composition code and the mapper implementation.

Production code must define when domain events are cleared. Clearing before a failed save can lose them from the in-memory object. Clear extracted events after a successful save so a second save on the same aggregate does not enqueue them again. Define behaviour for an outer transaction separately: a successful save inside it is not yet a committed business outcome. A safe failure rule is to discard the failed Unit of Work and reload authoritative state unless a specifically designed retry path exists.

9.5 Stable message identity

The outbox ID becomes the Service Bus MessageId. Repeated publishing attempts reuse it. Correlation links the message to the originating request; causation identifies the action or message that produced it.

9.6 Atomicity proof

Force the outbox insert to fail, then query through a fresh context. The Loan must remain Pending and no outbox row may exist. A second successful test must find an Approved Loan and exactly one matching outbox row.

10. Passage 3G Outbox publisher and service lifetimes

10.1 Publisher responsibilities

The publisher:

1. Claims a bounded batch.
2. Commits the claim quickly.
3. Publishes outside the SQL claim transaction.
4. Marks success.
5. Records controlled failure and retry information.
6. Exposes backlog, age, failures and throughput.

10.2 Singleton worker and scoped DbContext

BackgroundService is hosted as a singleton. DbContext is scoped and not thread-safe. The singleton must not retain a scoped context.

Singleton OutboxPublisherWorker
→ creates temporary scope
    → resolves scoped OutboxPublisher
        → receives scoped LoanDbContext
        → processes one batch
    → scope disposal disposes DbContext

public sealed class OutboxPublisherWorker : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;

    public OutboxPublisherWorker(IServiceScopeFactory scopeFactory)
    {
        _scopeFactory = scopeFactory;
    }

    protected override async Task ExecuteAsync(
        CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            await using var scope =
                _scopeFactory.CreateAsyncScope();

            var publisher = scope.ServiceProvider
                .GetRequiredService<IOutboxPublisher>();

            var count = await publisher.PublishBatchAsync(
                stoppingToken);

            if (count == 0)
            {
                await Task.Delay(
                    TimeSpan.FromSeconds(2),
                    stoppingToken);
            }
        }
    }
}

Registrations:

builder.Services.AddHostedService<OutboxPublisherWorker>();
builder.Services.AddScoped<IOutboxPublisher, OutboxPublisher>();
builder.Services.AddDbContext<LoanDbContext>(options =>
    options.UseSqlServer(connectionString));

An alternative is IDbContextFactory<LoanDbContext>, particularly when the worker only needs independent contexts. A scope is clearer when several scoped collaborators belong to one processing cycle.

10.3 Safe claims across several worker instances

A temporary lease prevents multiple publishers deliberately selecting the same records. A SQL Server claim can use a short update with UPDLOCK, READPAST and a lease:

;WITH MessagesToClaim AS
(
    SELECT TOP (@BatchSize) *
    FROM OutboxMessages WITH (UPDLOCK, READPAST, ROWLOCK)
    WHERE ProcessedAtUtc IS NULL
      AND (NextAttemptAtUtc IS NULL OR NextAttemptAtUtc <= @Now)
      AND (LockedUntilUtc IS NULL OR LockedUntilUtc < @Now)
    ORDER BY CreatedAtUtc
)
UPDATE MessagesToClaim
SET LockedBy = @WorkerId,
    LockedUntilUtc = @LockExpiry
OUTPUT INSERTED.*;

This claim statement is illustrative. Verify its locking hints against your SQL Server isolation settings, especially READ_COMMITTED_SNAPSHOT; do not copy it into a differently configured database without an integration test. Select an index for the pending-work query, define tie-breaking order and ensure the lease duration or renewal policy covers the batch.

The claim transaction commits before any Service Bus network wait. A lease, unlike a permanent IsProcessing flag, expires after a worker crash so another instance can recover the record.

10.4 Publishing

foreach (var message in claimedMessages)
{
    try
    {
        await serviceBusPublisher.PublishAsync(
            messageId: message.Id.ToString(),
            subject: message.EventType,
            body: message.Payload,
            correlationId: message.CorrelationId,
            cancellationToken);

        await outbox.MarkProcessedAsync(
            message.Id,
            workerId,
            clock.UtcNow,
            cancellationToken);
    }
    catch (OperationCanceledException)
        when (cancellationToken.IsCancellationRequested)
    {
        throw;
    }
    catch (Exception exception)
    {
        await outbox.RecordFailureAsync(
            message.Id,
            workerId,
            clock.UtcNow,
            SafeError(exception),
            cancellationToken);
    }
}

Do not rebuild the event from the current Loan. Publish the stored payload representing the fact at the time it occurred.

10.5 Crash window and at-least-once delivery

The publisher may successfully send and crash before marking the outbox row processed. The lease later expires and the same stable message is sent again. This is at-least-once delivery, not exactly-once delivery.

10.6 Retry and poison records

Use bounded exponential backoff with jitter rather than a tight loop. A malformed contract, unknown type or permanently oversized payload requires investigation rather than endless retry. Retain the record, mark the failure clearly and make replay authorized and auditable.

10.7 Do not use one context concurrently

Creating a scope does not make DbContext thread-safe. Do not run Task.WhenAll over operations sharing that context. Begin sequentially. If measured throughput demands parallel work, give each concurrent operation an independent scope/context and preserve claim ownership.

11. Passage 3H Consumer idempotency and the inbox

11.1 Inbox schema

CREATE TABLE InboxMessages
(
    ConsumerName nvarchar(200) NOT NULL,
    MessageId nvarchar(200) NOT NULL,
    ProcessedAtUtc datetimeoffset NOT NULL,
    CorrelationId nvarchar(100) NULL,
    CONSTRAINT PK_InboxMessages
        PRIMARY KEY (ConsumerName, MessageId)
);

The composite key allows each independent consumer to process the same event once.

11.2 Local atomic processing

A database consumer commits its local effect and inbox record together:

public async Task HandleAsync(
    ServiceBusReceivedMessage message,
    CancellationToken cancellationToken)
{
    const string consumerName = "Reporting.LoanApproved.v1";
    var messageId = message.MessageId;

    var alreadyProcessed = await dbContext.InboxMessages
        .AnyAsync(
            x => x.ConsumerName == consumerName &&
                 x.MessageId == messageId,
            cancellationToken);

    if (alreadyProcessed)
    {
        return;
    }

    var contract = message.Body
        .ToObjectFromJson<LoanApprovedIntegrationEvent>()
        ?? throw new InvalidMessageException("Invalid payload.");

    dbContext.LoanApprovals.Add(
        LoanApprovalReportRecord.Create(
            contract.LoanId,
            contract.ApprovedBy,
            contract.ApprovedAtUtc));

    dbContext.InboxMessages.Add(
        InboxMessage.Create(
            consumerName,
            messageId,
            clock.UtcNow,
            message.CorrelationId));

    // Local effect and inbox record share one transaction.
    await dbContext.SaveChangesAsync(cancellationToken);
}

The Service Bus message is completed only after this transaction commits.

11.3 Why the unique constraint is essential

Two concurrent handlers can both check and initially see no inbox row. The database primary key is the final race protection. One transaction inserts successfully; the other receives a duplicate-key outcome and verifies that the message is already processed.

11.4 Settlement behaviour

• Complete: local processing committed or duplicate safely recognised.
• Abandon/retry: temporary dependency failure.
• Dead-letter: permanently invalid or repeatedly unprocessable message.
• Defer: deliberate postponement requiring explicit retrieval.

11.5 Consumer crash windows

Before local commit, a crash leaves no local effect or inbox row; redelivery performs the work. After local commit but before Service Bus completion, redelivery occurs, but the inbox prevents a second local effect.

11.6 External effects need extra protection

An email provider and SQL inbox cannot normally share one atomic transaction. The provider may accept the email before the consumer records completion. A local email-intent outbox durably records the intent, but it still has a send/mark crash window. Preventing duplicate external sends also requires provider idempotency keys where supported, naturally idempotent operations, reconciliation, or an explicitly accepted duplicate risk.

The inbox is strongest when both the consumer's business effect and inbox row use the same local database transaction.

11.7 Retention

Inbox records require a retention policy based on Service Bus retention, replay window, audit obligations and incident recovery. Delete in controlled batches only after messages cannot legitimately reappear within the supported window.

12. Failure model

13. Testing strategy

13.1 Do not use mocks for relational claims

Mocks and EF's in-memory provider cannot prove SQL Server transactions, constraints, rowversion, lock behaviour or concurrent uniqueness. Use a real SQL Server test environment or a representative container/database instance.

13.2 Mapping round-trip test

1. Create a Loan with Money, status and business unit.
2. Save it.
3. Dispose the context.
4. Load with a fresh context.
5. Assert that identifiers and value objects reconstruct correctly.

13.3 Atomic outbox failure test

1. Create a Pending Loan.
2. Approve it.
3. Force outbox persistence to fail.
4. Assert save failure.
5. Dispose the context.
6. Load through a fresh context.
7. Assert Loan remains Pending.
8. Assert no outbox record exists.

13.4 Concurrency test

Use two contexts to load the same row. Save through the first, then verify that the second receives DbUpdateConcurrencyException. A third context proves final state.

13.5 Publisher claim competition

Run two claimers concurrently. Verify active leases prevent intentional duplicate claims, an expired lease is recoverable, and each claimed row records ownership.

13.6 Publisher crash-window test

Simulate successful publication without marking processed. Expire the lease and confirm republishing uses the same message ID.

13.7 Sequential duplicate consumer test

Deliver the same message twice. Confirm one business record and one inbox record.

13.8 Concurrent duplicate consumer test

Start two handlers for the same ID. Confirm the unique constraint allows only one local business effect.

13.9 Blocking experiment

Hold one controlled transaction open, start a second conflicting update, observe the wait in telemetry/DMVs, then release the first transaction. This proves the investigation flow without treating production as a laboratory.

14. Operational monitoring

Monitor:

• SQL dependency duration and timeout rate.
• Long-running and open transactions.
• Lock waits, deadlocks and blocked-session duration.
• Outbox pending count.
• Age of the oldest pending outbox record.
• Publication rate and failure rate.
• Lease expirations.
• Permanently failed outbox records.
• Service Bus queue depth and oldest-message age.
• Consumer retries and dead-letter count.
• Inbox duplicate detections.
• End-to-end time from business commit to consumer completion.

Backlog count alone is insufficient. Ten records waiting for two hours can be more serious than a thousand records draining during a brief spike.

Telemetry should contain stable message, correlation, causation, consumer and contract identifiers, but not credentials or unnecessary sensitive payloads.

15. Implementation order for a junior developer

1. Add the SQL Server provider and LoanDbContext in Infrastructure.
2. Create LoanConfiguration and map strongly typed IDs and value objects.
3. Generate and review the migration rather than applying it blindly.
4. Add mapping round-trip integration tests.
5. Implement tracked command-side repository loading.
6. Ensure repository and Unit of Work share one scoped context.
7. Map Version as rowversion.
8. Carry Base64 version through read DTO and command.
9. Add the two-context concurrency test.
10. Define the versioned integration event.
11. Create the outbox table and mapping.
12. Convert domain events into outbox rows before the save.
13. Prove Loan and outbox atomicity with forced failure.
14. Implement the singleton worker plus per-cycle scope.
15. Add bounded claim leases and controlled retry.
16. Publish the stored payload with stable ID and correlation metadata.
17. Add publisher success, failure and crash-window tests.
18. Create consumer inbox tables with a composite unique key.
19. Commit each local effect and inbox record together.
20. Complete the Service Bus message only after local commit.
21. Test sequential and concurrent duplicate delivery.
22. Add dashboards, alerts and a replay runbook.

16. Common mistakes and corrections

Mistake 1 Long-lived DbContext

Problem: Stale tracking, memory growth and unsafe concurrent use. Correction: Short scopes per request or worker cycle.

Mistake 2 Calling Update after a tracked load

Problem: Every mapped property may be marked modified. Correction: Change the tracked aggregate through domain behaviour and save.

Mistake 3 Trusting a client-supplied entity

Problem: Mass assignment and stale protected fields. Correction: Accept intent, load authoritative state and apply controlled methods.

Mistake 4 Two SaveChanges calls for Loan and outbox

Problem: Partial commit can lose the event. Correction: Track both before one atomic save.

Mistake 5 Publishing inside the SQL transaction

Problem: Locks remain while the network responds; rollback cannot retract an accepted message. Correction: Commit an outbox record and publish later.

Mistake 6 Automatically retrying a concurrency conflict

Problem: A newer human decision may be overwritten. Correction: Return conflict, reload and require reconsideration.

Mistake 7 Permanent processing flag

Problem: A crashed worker leaves the row stuck. Correction: Use an expiring ownership lease.

Mistake 8 Singleton worker directly owns DbContext

Problem: Scoped/thread-unsafe context lives for the process lifetime. Correction: Create a scope per cycle or use a context factory.

Mistake 9 Parallel tasks share one context

Problem: DbContext is not thread-safe. Correction: Process sequentially or use independent scopes and contexts.

Mistake 10 Inbox check without unique constraint

Problem: Concurrent handlers both pass the initial check. Correction: Composite primary/unique key provides final protection.

Mistake 11 Claiming exactly-once delivery

Problem: Crash windows remain between independent systems. Correction: State the actual guarantees: atomic local transaction, recoverable at-least-once publication and idempotent local consumption.

Mistake 12 Using NOLOCK as a blocking fix

Problem: Dirty, missing or duplicated data may be read. Correction: Diagnose transaction duration, query plan, indexing and isolation requirements.

17. Senior engineering judgement

17.1 Automatic versus explicit transactions

Prefer one normal EF save when it represents the complete atomic work. Add an explicit transaction only when multiple database operations genuinely require it.

17.2 Interceptor versus explicit outbox mapping

An interceptor centralises conversion but hides work behind SaveChanges. Explicit application mapping is easier to see but can be forgotten. Whichever design is chosen must be tested, observable and consistent.

17.3 Polling frequency and batch size

Small intervals reduce latency but increase SQL activity. Large batches improve throughput but increase lease duration, memory and failure impact. Measure arrival rate, age target, SQL capacity and Service Bus performance.

17.4 Ordering

At-least-once systems do not automatically guarantee useful global ordering. If one aggregate's events require order, use a stable session/partition key and consumer rules, while recognising the throughput trade-off.

17.5 Retry ownership

Avoid stacked retries at every layer. EF execution strategy, publisher loop, Service Bus and consumer may all retry. Define which layer owns which temporary failure and cap the combined delay and load.

17.6 External idempotency

Inbox atomicity covers local SQL effects. External providers need idempotency keys, stable business keys, reconciliation or an explicitly accepted duplicate risk.

18. Phase 3 proof matrix

19. Phase 3 completion gate

Phase 3 is complete when:

• EF Core mappings preserve the Phase 2 domain model.
• Migrations are reviewed and repeatable.
• Command repositories use tracked aggregates deliberately.
• Read queries remain purpose-built and no-tracking where appropriate.
• Repository and Unit of Work share the same scoped context.
• The client carries an opaque expected version.
• rowversion conflicts are detected through a real SQL test.
• Transaction boundaries are short and explicit.
• External calls do not occur inside the Loan commit transaction.
• Loan update and outbox insertion commit atomically.
• Domain events map to deliberate versioned integration contracts.
• The publisher uses scoped dependencies from its singleton worker.
• Competing publishers use bounded claims or equivalent safe coordination.
• Retry, poison-message and lease recovery behaviour is defined.
• Stable message and correlation identifiers flow to Service Bus.
• Each consumer records its own inbox completion.
• Local business effect and inbox row share one transaction.
• Sequential and concurrent duplicates are proven safe.
• Failure windows are documented honestly without exactly-once claims.
• SQL and messaging health have useful metrics and alerts.
• Another engineer can follow the runbook to investigate backlog or blocking.

20. Review questions with answers

1 Why map Money into the Loans row?

Money has no independent identity or lifecycle in this aggregate. It remains a domain value object while its amount and currency are stored as columns in the owning Loan row.

2 Why avoid AsNoTracking for GetForUpdateAsync?

The aggregate will be changed. EF needs the original snapshot, current values and concurrency token to generate the correct update.

3 Why not call Update after loading the Loan?

The same context already tracks it. Calling Update() can mark every mapped property modified unnecessarily.

4 Is SaveChangesAsync always transactional?

For a normal relational save, EF uses a transaction for the generated statements. The exact guarantee must still be verified for the chosen provider and any custom work outside that save.

5 Do we always need BeginTransactionAsync?

No. One complete SaveChangesAsync() is often the clearest transaction. Use an explicit transaction when several database steps genuinely need one commit boundary.

6 Why keep HTTP and Service Bus calls outside the SQL transaction?

They can be slow or fail independently. Holding SQL locks while waiting increases blocking, and SQL rollback cannot undo an external side effect.

7 What does rowversion prove?

It proves whether that row changed since the caller's version was read. It does not prove that the requested domain transition is valid.

8 Why return conflict instead of automatically retrying approval?

The newer state may represent another person's decision. The user must see and reconsider it rather than having the system silently override it.

9 Why are locks still needed with optimistic concurrency?

rowversion detects stale state across time. Locks coordinate statements and transactions currently executing together.

10 What is the main cause of harmful blocking?

Locks held longer than necessary, commonly because of long transactions, slow scans, large batches or external waits inside the transaction.

11 Why can SQL and Service Bus not be treated as one normal transaction?

They are independent systems. An ordinary SQL transaction cannot atomically commit a Service Bus publication, creating a dual-write failure window.

12 What does the outbox guarantee?

If the business transaction commits, a durable record of the message-to-publish commits with it. Publication can then be retried.

13 Does the outbox guarantee one delivery?

No. A publisher may send successfully and crash before marking processed. It may send the same stable message again.

14 Why is the worker singleton allowed to use scoped services?

It does not retain them. It creates a temporary scope for each cycle, resolves a scoped publisher and context, processes one batch and disposes the scope.

15 Why use a lease instead of IsProcessing?

A permanent flag can strand a row after a crash. A lease expires so another worker can recover the work.

16 Why not publish while the claim transaction is open?

Network wait would hold SQL locks and connections. Commit the claim quickly and publish outside that transaction.

17 Why does the consumer need an inbox?

At-least-once delivery means the same message can return. The inbox records that this consumer has already completed its local effect.

18 Why is a read-before-write inbox check insufficient?

Concurrent handlers can both see no record. A database unique key is the final race protection.

19 Can the inbox prevent every duplicate email?

Not by itself. Email acceptance and SQL commit are independent. Use provider idempotency, a local email-intent outbox, reconciliation or an accepted duplicate policy.

20 Why verify state through a fresh context after failure?

The failed context may retain modified or stale in-memory entities. A fresh context reads committed database truth.

21. Interview explanation

For reliable approval, I load the Loan as a tracked aggregate through a scoped EF Core context. SQL Server rowversion prevents stale writes. The Loan update and a versioned outbox message are tracked by the same context and committed by one short transaction. No Service Bus or other network call happens inside that transaction. A background singleton creates a scope per batch so each cycle receives a fresh scoped DbContext. Publishers claim work with expiring leases, publish the stored payload and mark success. Because a crash can occur after send but before marking, delivery is at least once. Each consumer uses a stable message ID, an inbox record and a unique constraint; its local effect and inbox completion commit together. I use real SQL integration tests for mapping, concurrency, transaction atomicity and duplicate races because mocks cannot prove those behaviours.

22. What this phase brings together

Phase 3 connects domain correctness to durable operational consistency.

EF Core maps protected domain types into a relational schema without moving persistence concerns into the Domain. A short-lived DbContext tracks the authoritative aggregate and acts as the Unit of Work. One relational save commits the Loan and outbox record atomically. SQL Server rowversion prevents stale clients from overwriting newer state, while normal SQL locks protect currently executing work. Short transactions and selective queries keep those locks from becoming widespread blocking.

The outbox solves the producer-side dual-write problem by storing the promise to publish alongside the business update. A separate background publisher uses per-cycle scopes, bounded claims, expiring leases, stable identities and controlled retry. The unavoidable crash window means publication is at least once.

The inbox provides consumer-side idempotency for local effects. A unique (ConsumerName, MessageId) key protects concurrent duplicate delivery, and the business effect and inbox record commit together. External effects such as email require additional idempotency support because they cannot normally join the local SQL transaction.

The honest guarantee is:

Local state changes are atomic, committed messages are recoverably published at least once, and consumers are designed so duplicate delivery does not repeat the local business effect.

Phase 4 can now expose these behaviours through a secure, predictable ASP.NET Core HTTP contract.

Appendix A Suggested Infrastructure structure

LoanPlatform.Infrastructure/
    Persistence/
        LoanDbContext.cs
        Configurations/
            LoanConfiguration.cs
            OutboxMessageConfiguration.cs
            InboxMessageConfiguration.cs
        Repositories/
            EfLoanRepository.cs
            EfOutboxRepository.cs
        Interceptors/
            ConvertDomainEventsToOutboxInterceptor.cs
        Migrations/
    Messaging/
        Contracts/
            LoanApprovedIntegrationEvent.cs
        Mapping/
            IntegrationEventMapper.cs
        Publishing/
            OutboxPublisher.cs
            ServiceBusMessagePublisher.cs

LoanPlatform.Workers/
    OutboxPublisherWorker.cs
    Consumers/
        LoanApprovedConsumer.cs
        ServiceBusProcessorHost.cs

LoanPlatform.IntegrationTests/
    Persistence/
        LoanMappingTests.cs
        LoanConcurrencyTests.cs
        OutboxAtomicityTests.cs
    Messaging/
        OutboxClaimTests.cs
        PublisherRecoveryTests.cs
        InboxIdempotencyTests.cs

Appendix B Glossary

Appendix C Immediate next actions

1. Implement and review the EF Core mappings.
2. Add SQL Server integration-test infrastructure.
3. Prove mapping round trips and rowversion conflicts.
4. Implement the outbox entity, mapping and event conversion.
5. Prove atomic rollback when outbox persistence fails.
6. Implement scoped publisher processing behind a singleton worker.
7. Add lease competition and crash-recovery tests.
8. Implement one inbox-protected consumer.
9. Test sequential and concurrent duplicates.
10. Add backlog, age, failure and blocking diagnostics.
11. Record accepted decisions in ADRs and update the Phase 1 proof matrix.
12. Begin Phase 4 with HTTP contracts, endpoints and Problem Details.