Skip to main content

Database & Migrations

Database Architecture​

Wayd uses a single shared database accessed through WaydDbContext. All domain services share this context.

Entity configurations are organized by domain area in:

Wayd.Infrastructure/src/Wayd.Infrastructure/Persistence/Configuration/

Each area's tables sit in their own SQL schema (Work, Organization, Planning, Ppm, ProductManagement, …), named in SchemaNames.

One context, many interfaces​

Application code never sees WaydDbContext — DatabaseContextTests fails an Application project that references it. Each module reads and writes through its own interface (IWorkDbContext, IOrganizationDbContext, IProjectPortfolioManagementDbContext, …), and shared concerns have theirs (IImportDbContext, IStatusWorkflowDbContext, IActivityLogDbContext, IFeatureManagementDbContext). Twelve such interfaces sit beside IWaydDbContext, which exposes the shared tables (employees, users, scoring models) and which most module interfaces extend.

They are views over one context, not separate contexts: a handler that takes two of them gets one change tracker and one save. Keeping that true takes two things, and either alone breaks it:

  1. AddDomainDbContexts registers each interface as a factory over the scoped WaydDbContext (sp => sp.GetRequiredService<WaydDbContext>()). AddScoped<IFoo, WaydDbContext>() would register a separate context per interface.
  2. Each is on the AlwaysUseServiceLocationFor<T>() allow-list in WolverineConfiguration; without it, Wolverine's codegen constructs its own context for that interface.

DbContextScopeSharingTests checks the result against the booted container. Unit-test fakes pass one object for every role, so they cannot detect a break here.

Entity Framework Core​

Direct DbContext Usage​

Wayd does not use the Repository pattern. Application handlers use their module's context interface directly:

public sealed class GetWorkItemQueryHandler(IWorkDbContext workDbContext)
    : IQueryHandler<GetWorkItemQuery, WorkItemDto?>
{
    private readonly IWorkDbContext _workDbContext = workDbContext;

    public async Task<WorkItemDto?> Handle(GetWorkItemQuery request, CancellationToken cancellationToken)
    {
        return await _workDbContext.WorkItems
            .Where(w => w.Id == request.Id)
            .ProjectToType<WorkItemDto>()
            .FirstOrDefaultAsync(cancellationToken);
    }
}

A query that projects to DTOs, scalars or anonymous types holding no entity instance (Select, ProjectToType) takes no AsNoTracking(): there is nothing to track. A query that returns entities it only reads still takes AsNoTracking().

What a save commits​

SaveChangesAsync commits the tracked rows together with their audit trail, activity log entries and outbox envelopes in one transaction, then delivers the events they raised. Span several saves with BeginUnitOfWork (exposed to Application code on IWorkDbContext), never with Database.BeginTransactionAsync; TransactionScopeTests fails the build on the latter. Because the save owns its transaction, EnableRetryOnFailure cannot be turned on. See What a save commits.

Isolation and timeouts​

Wayd expects the database to have READ_COMMITTED_SNAPSHOT on, as Azure SQL Database has by default: a save's transaction covers its audit and outbox rows, and without row versioning readers block behind it. SQL Server defaults it off, so startup turns it on in Development and logs a warning with the ALTER DATABASE to run everywhere else (DatabaseInitializer).

The command timeout is the context's, DatabaseSettings:CommandTimeoutSeconds (default 30). An operation that legitimately runs longer raises it for its own scope with WithCommandTimeout, which never lowers it; the migration run uses it to allow 180 seconds. Never raise the timeout globally. See Configuration.

Entity Configuration​

Use fluent API in separate configuration classes:

public class WorkItemConfiguration : IEntityTypeConfiguration<WorkItem>
{
    public void Configure(EntityTypeBuilder<WorkItem> builder)
    {
        builder.ToTable("WorkItems", SchemaNames.Work);
        builder.HasKey(w => w.Id);
        // ...
    }
}

Soft Deletes​

An entity that implements ISoftDelete is never physically removed: deleting it marks it as deleted. Soft delete is opt-in per entity — most entities derive from BaseAuditableEntity or BaseEntity and are deleted outright.

Base entity types​

Base typeGives the entity
BaseEntityA version 7 Guid Id and the domain-event and post-persistence-action lists
BaseAuditableEntityThe above plus ISystemAuditable: SystemCreated, SystemCreatedBy, SystemLastModified and SystemLastModifiedBy shadow properties stamped on save, and an audit trail row per change
BaseSoftDeletableEntityThe above plus ISoftDelete

Each has a generic form (BaseEntity<TId>, …) for a non-Guid key.

BaseSoftDeletableEntity​

Entities that support soft delete inherit from BaseSoftDeletableEntity, which implements ISoftDelete:

public abstract class BaseSoftDeletableEntity<TId> : BaseAuditableEntity<TId>, ISoftDelete
{
    public Instant? Deleted { get; set; }
    public string? DeletedBy { get; set; }
    public bool IsDeleted { get; set; } = false;
}

When SaveChangesAsync is called, BaseDbContext intercepts any EntityState.Deleted entry that implements ISoftDelete, sets IsDeleted = true and stamps Deleted and DeletedBy, and changes the state back to Modified — so the row stays in the table.

Global Query Filter​

BaseDbContext.OnModelCreating registers a global EF query filter for all ISoftDelete entities:

modelBuilder.AppendGlobalQueryFilter<ISoftDelete>(s => !s.IsDeleted);

This means all LINQ queries automatically exclude soft-deleted rows — no manual Where(x => !x.IsDeleted) is needed in handlers or validators. Use .IgnoreQueryFilters() only when you explicitly need to include deleted records.

Unique Indexes on Soft-Deletable Entities​

Because soft-deleted rows remain in the table, a plain unique index will block reuse of a deleted value. Always add .WhereNotDeleted() to any unique index on a soft-deletable entity (the filter helpers in IndexBuilderExtensions are the one place that knows the provider's SQL for a filter — never write the string inline):

builder.HasIndex(r => r.Name).IsUnique().WhereNotDeleted();

This keeps the constraint consistent with what the global query filter enforces at the application layer.

Migrations​

Migrations are managed in Wayd.Infrastructure.Migrators.MSSQL.

The dotnet ef commands below come from a local tool manifest, so restore it once per clone:

dotnet tool restore

Creating a Migration​

dotnet ef migrations add <MigrationName> \
  --project "Wayd.Infrastructure/src/Wayd.Infrastructure.Migrators.MSSQL" \
  --startup-project "Wayd.Web/src/Wayd.Web.Api"

Applying Migrations​

dotnet ef database update \
  --project "Wayd.Infrastructure/src/Wayd.Infrastructure.Migrators.MSSQL" \
  --startup-project "Wayd.Web/src/Wayd.Web.Api"

Migrations also run automatically on application startup via app.Services.InitializeDatabases().

A change to an entity configuration needs a migration in the same change. A data-fix migration must not write an evented aggregate's table set-based (UPDATE, DELETE, INSERT, MERGE): EventCoverageTests fails it, because such a write raises no event — see An evented aggregate changes only through events.

Resetting a database

Reset a local database only through Aspire: start Wayd.AppHost, then run Reset Database from the wayd-api resource's commands in the dashboard, which resets the database the API is configured with. Never drop or recreate a Wayd database with dotnet ef database drop, sqlcmd or a script.

Timestamps in Raw SQL​

Migrations cannot inject IDateTimeProvider, so they are the one place DateTime.UtcNow is allowed. When interpolating a timestamp into migrationBuilder.Sql(...), always format it explicitly with CultureInfo.InvariantCulture and an ISO 8601 pattern:

var timestamp = DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss.fff", CultureInfo.InvariantCulture);

The current culture's default DateTime format is not reliably parseable by SQL Server. ICU 72+ emits U+202F (narrow no-break space) before AM/PM for en-US, which fails conversion with error 241 on every non-Windows host, and month/day-ordered formats depend on the server's DATEFORMAT. The ISO pattern is unambiguous under any server language.

Database Initialization​

On startup, app.Services.InitializeDatabases() runs DatabaseInitializer, which:

  1. Applies pending migrations, with the command timeout raised to 180 seconds for the run
  2. Ensures READ_COMMITTED_SNAPSHOT (turned on in Development, a warning elsewhere)
  3. Seeds the built-in roles and their permissions, then runs every ICustomSeeder — feature flags, estimation scales, product types, scoring models, the Product Management status workflows, the workflow alias-name lookup, and the work type level scheme
  4. Re-saves any connection whose secrets are still plaintext so they are encrypted (ConnectionSecretBackfill)

None of this runs when HostIntrospection.SkipsDatabaseInitialization is true — EF tooling, and the Debug build that reads the OpenAPI document (see API Development). Seeders run before Wolverine can route messages, so a seeder that creates an evented aggregate writes its creation events straight into the activity log rather than through SaveChanges; see Backfilling baselines.