09 Write Model PostgreSQL.md

Write Model (PostgreSQL)

WriteDbContext

public sealed class WriteDbContext(DbContextOptions<WriteDbContext> options, IOutboxMessageFactory outboxMessageFactory)
    : DbContext(options), IUnitOfWork
{
    public DbSet<Room> Rooms { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<OutboxMessage> OutboxMessages { get; set; }

    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        var aggregates = ChangeTracker.Entries<IAggregateRoot>()
            .Select(x => x.Entity)
            .Where(a => a.DomainEvents.Count != 0)
            .ToList();

        var messages = outboxMessageFactory.CreateOutboxMessages(aggregates);
        OutboxMessages.AddRange(messages);

        return await base.SaveChangesAsync(ct);
    }
}

This override is the entire Outbox integration point. Every SaveChangesAsync() call — regardless of which repository/handler triggered it — scans the EF Core change tracker for any tracked IAggregateRoot with pending domain events, converts them to OutboxMessage rows via OutboxMessageFactory, adds them to the same DbContext instance, and calls base.SaveChangesAsync() once. Because EF Core wraps a single SaveChanges call in one implicit transaction, the aggregate's row changes and its outbox rows commit atomically or not at all — this is the entire mechanism that eliminates dual-write risk (see Architecture Overview).

IUnitOfWork is a one-method interface (Task<int> SaveChangesAsync(CancellationToken)), and WriteDbContext is registered directly against it: services.AddScoped<IUnitOfWork>(sp => sp.GetRequiredService<WriteDbContext>()). Handlers depend on IUnitOfWork, never on WriteDbContext — keeping ChatRooms.Application free of any EF Core reference beyond the Microsoft.EntityFrameworkCore package needed for FluentValidation's async validators (the Application.csproj does reference Microsoft.EntityFrameworkCore, but only WriteDbContext in Infrastructure actually uses DbContext types).

OutboxMessageFactory

public IReadOnlyList<OutboxMessage> CreateOutboxMessages(IEnumerable<IAggregateRoot> aggregates)
{
    var messages = new List<OutboxMessage>();
    foreach (var aggregate in aggregates)
    {
        messages.AddRange(aggregate.DomainEvents.Select(e => OutboxMessage.Create(
            type: e.GetType().Name,
            content: JsonSerializer.Serialize(e, e.GetType(), jsonOptions),
            occurredOn: e.OccurredAt)));
        aggregate.ClearDomainEvents();
    }
    return messages;
}

• type is the bare CLR class name ("RoomCreatedDomainEvent"), not a fully-qualified/versioned type identifier — this is exactly the string used as the [FromKeyedServices]-style key for IEventProjector resolution (nameof(RoomCreatedDomainEvent) at registration time; messageType string comparison at dispatch time). This is a fragile coupling: renaming a domain event class renames its wire-format Type discriminator, and any already-persisted-but-unprocessed OutboxMessages rows with the old type name would become permanently unprojectable (no projector registered under the new key) unless a migration/backfill handles it.
• jsonOptions is the shared JsonOptionsFactory.Create() singleton (ValueObjectJsonConverterFactory + DateTimeUtcJsonConverter), registered once via services.AddSingleton(JsonOptionsFactory.Create()) and injected into both the factory and every projector — guaranteeing symmetric serialize/deserialize behavior for value objects and DateTimeUtc.
• aggregate.ClearDomainEvents() is called per aggregate, immediately after harvesting — so if SaveChangesAsync is called multiple times against the same tracked aggregate instance within one logical unit of work (not a pattern used in this codebase's handlers, which call SaveChangesAsync exactly once per request), events wouldn't be double-emitted.

OutboxMessageFactory custom serialization (ChatRooms.Infrastructure.Serialization)

• ValueObjectJsonConverterFactory: reflection-based JsonConverterFactory. CanConvert matches any non-primitive, non-enum, non-System.* type that has a public instance Value property and a public static From method — i.e. it auto-detects the value-object shape (RoomId, Capacity, Name, Email, etc.) without per-type registration. CreateConverter builds a DynamicValueObjectJsonConverter<TValueObject,TPrimitive> via reflection + compiled Expression trees (not raw reflection invocation per call — _getValueMethod/_createMethod are compiled delegates, built once per converter instance) for reasonable serialization performance despite the dynamic dispatch.
• DynamicValueObjectJsonConverter<TVO,TPrim>.Read accepts two wire shapes: a flat primitive ("ABCD1234") or a {"Value": "ABCD1234"} object — this dual-format tolerance exists so the same converter can round-trip values that were serialized by a slightly different serializer configuration (e.g. a plain System.Text.Json default object serializer that emits {Value: ...} for a record, versus this converter's own flat-primitive Write). Write always emits the flat primitive form — so the object-shaped read path exists for backward compatibility with data written before this converter existed, not for anything this converter itself produces going forward.
• DateTimeUtcJsonConverter (two copies exist: ChatRooms.Infrastructure.Serialization.DateTimeUtcJsonConverter and ChatRooms.Domain.Shared.DateTimeUtcJsonConverter, the latter referenced directly via [JsonConverter(typeof(DateTimeUtcJsonConverter))] on the DateTimeUtc struct itself). Both accept ISO-8601 strings or Unix-millisecond numbers on read; both write ISO-8601 ("o" format) strings. Having two independent implementations of the same converter is a duplication smell — they must be kept behaviorally identical by hand; a fix applied to one and not the other would create subtle read/write asymmetries between "objects serialized via the domain-level attribute" and "objects serialized via the infrastructure-level JsonOptionsFactory."

EF Core configurations (Infrastructure/Persistence/Configurations)

RoomConfiguration

• Every value object is column-mapped via HasConversion(vo => vo.Value, value => VO.From(value)) — RoomId, Name, Status (HasConversion<string>() — enum-as-string, not int), Capacity, Code, CreatedAt/UpdatedAt/DeletedAt (via DateTimeUtc.FromUtc/.DateTime), Reason (HasConversion<string>()).
• Code has HasMaxLength(8) and a unique index (HasIndex(r => r.Code).IsUnique()) — matches RoomCode's domain invariant (exactly 8 chars) and the fact that RoomRepository.Exists(RoomCode) / GetByCode depend on uniqueness for correctness, though nothing in CreateRoomCommandHandler actually calls Exists() before creating a room — collision avoidance relies entirely on RoomCodeGenerator's cryptographic randomness (32-character alphabet, 8 chars, RandomNumberGenerator.Fill) being astronomically unlikely to collide, backstopped by the DB's unique constraint throwing a DbUpdateException (not a friendly domain error) if it ever did. This is a non-guarantee worth knowing: a Postgres unique-constraint violation on Code during CreateRoomCommandHandler would propagate as an unhandled exception → generic 500, not a clean Room.CodeAlreadyExists-style Result failure, because there is no such error defined and no try/catch around the insert.
• Version is configured .IsConcurrencyToken() — this is the single line that turns EF Core's normal "last write wins" UPDATE into an optimistic-concurrency-checked one (WHERE Id = @id AND Version = @expectedVersion); a 0-row-affected UPDATE throws DbUpdateConcurrencyException, caught by ConcurrencyBehavior (see CQRS Pipeline).

UserConfiguration

Same shape. Name.HasMaxLength(15) (matches the VO's private MaxLength const), Email.HasMaxLength(254) + unique index, Gender.HasConversion<string>(), Age is explicitly builder.Ignore(u => u.Age) (computed, not persisted), Version.IsConcurrencyToken().

OutboxMessageConfiguration

builder.Property(o => o.Content).HasColumnType("jsonb").IsRequired();
builder.Property(x => x.ProcessingLeaseUntil).HasColumnType("timestamp with time zone");
builder.Property(x => x.ProcessingBy).HasMaxLength(200);
builder.HasIndex(o => new { o.IsProcessed, o.IsDeadLetter, o.ProcessingLeaseUntil, o.OccurredOn });

Content is jsonb (PostgreSQL's binary/indexable JSON type), enabling efficient storage and, if ever needed, JSON path queries against event payloads. See Known Issues — this configuration does not match the latest reviewed migration, which is a genuine schema-drift bug, not just a documentation nuance.

Migrations (chronological)

Migration	Adds
20260225091311_InitialCreate	Rooms, OutboxMessages tables; unique index on Rooms.Code; index on OutboxMessages(IsProcessed, OccurredOn)
20260301090902_AddOutboxDeadLetterQueue	OutboxMessages.IsDeadLetter (bool, default false)
20260512090309_AddIsDeadLetterToOutboxIndex	Drops the old (IsProcessed, OccurredOn) index, adds (IsProcessed, IsDeadLetter, OccurredOn)
20260516101444_AddUserEntity	Users table (unique index on Email)

ChatRooms.Infrastructure/Migrations/WriteDbContextModelSnapshot.cs (the source of truth for "what the last migration believes the schema looks like") still shows the OutboxMessages entity with only Id, Content, ErrorMessage, IsDeadLetter, IsProcessed, OccurredOn, ProcessedOn, RetryCount, Type — no ProcessingLeaseUntil or ProcessingBy columns, despite OutboxMessageConfiguration.cs explicitly configuring them and OutboxRepository.cs writing raw SQL that references "ProcessingLeaseUntil" and "ProcessingBy" columns directly. This is a real, confirmed schema-drift bug — see Known Issues for the operational impact and fix.

int Version, not uint — locked-in decision

Documented project decision: Version is int (not uint) specifically because PostgreSQL has no native unsigned integer type — mapping a C# uint to Postgres would require an artificial workaround (e.g. storing as bigint or a custom converter), adding complexity for a column that will never realistically approach int.MaxValue (~2.1 billion) domain events on a single aggregate. AggregateRoot<TId>.Version's private setter (field >= 0 ? value : throw ...) is the domain-level enforcement that keeps it non-negative despite the signed type.

Development-only auto-migration

if (app.Environment.IsDevelopment())
{
    ...
    using var scope = app.Services.CreateScope();
    var dbContext = scope.ServiceProvider.GetRequiredService<WriteDbContext>();
    await dbContext.Database.MigrateAsync();
}

Non-guarantee: in any environment where ASPNETCORE_ENVIRONMENT != Development, migrations are never applied automatically — a deployment pipeline must run dotnet ef database update (or migrations script + apply) explicitly before starting the API, or the app will fail against a schema-mismatched database. See Deployment.

Design-time factory

WriteDbContextFactory : IDesignTimeDbContextFactory<WriteDbContext> reads connection config from ChatRooms.API/appsettings.json / appsettings.Development.json (relative path ../ChatRooms.API from ChatRooms.Infrastructure's working directory) — this is what makes dotnet ef migrations add ... work without a running host, and is why the migration workflow must be run with --startup-project ChatRooms.API specifically (the connection string key is "postgress" — note the misspelling, consistent between appsettings.json and WriteDbContextFactory, so it's an intentional-if-unfortunate convention, not a bug, as long as both sides keep agreeing on the misspelled key).