Relational Schema Management

How the schema is created and evolved on PostgreSQL and SQL Server. The backend is chosen by Database:DatabaseType: postgresql (or postgres) or sqlserver (or mssql); mongodb is the default.

Each organisation gets its own tenant store, named Database:TenantDatabasePrefix (default bb-org-) plus the organisation name. On PostgreSQL that is a schema inside the one database in your connection string (hyphens become underscores, so bb_org_acme); on SQL Server it is a separate database. The platform store is named by Database:PlatformDatabaseName (default blazorblueprint-platform) in the same way. Every tenant schema or database keeps its own __EFMigrationsHistory, so tenants are migrated independently.

On MongoDB, only The DbMigrator section applies. Mongo is schemaless and uses neither mode below, but the DbMigrator is still what reconciles its indexes.

Two modes

Both modes use the same entities and the same shared EF model; they differ only in how the DDL reaches the database.

Mode	Database:UseMigrations	How the schema is created	Where it may run
Model-driven	false (the base default)	GenerateCreateScript() lays down the current model’s tables and indexes, creating only those that are missing. No history is kept.	Development only
EF migrations	true	Database.Migrate() applies the committed migrations, tracked in __EFMigrationsHistory.	Everywhere

Model-driven only ever creates missing tables and indexes. It never alters a table that already exists, so a changed column never reaches a live schema. For that reason, outside Development the host refuses to start on a relational backend without UseMigrations=true. The check is “not Development” rather than “Production”, so Staging and Lite are covered too. The shipped PostgreSQL and SQL Server compose files and appsettings.Lite.json already set it.

Where migrations live

Migrations are provider-specific, because Npgsql and SQL Server emit different DDL, and there is one set per DbContext. That makes four folders:

Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Postgres/Migrations/Platform
Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Postgres/Migrations/Tenant
Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Sqlserver/Migrations/Platform
Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Sqlserver/Migrations/Tenant

At design time the contexts are built by the factories in Services/BlazorBlueprint.DbMigrator/Migrations/MigrationsDesignTime.cs. The DbMigrator is the startup project for the EF tooling because it references both provider projects and every plugin listed in Plugins/Plugins.props, so a generated migration includes the plugins’ tables. The BB_MIGRATIONS_PROVIDER environment variable chooses the provider: postgres/postgresql (the default when unset) or sqlserver/mssql. Any other value fails the tooling rather than silently generating for the wrong provider.

Generating migrations

You need EF tools that match EF Core 10: dotnet tool update --global dotnet-ef --version 10.*

After your schema is live, add a named migration for each change, for both providers and both contexts:

PG=Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Postgres
MS=Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Sqlserver
SP=Services/BlazorBlueprint.DbMigrator  # EF design-time startup project

# PostgreSQL
BB_MIGRATIONS_PROVIDER=postgres dotnet ef migrations add <Name> --project $PG --startup-project $SP --context PlatformDbContext -o Migrations/Platform
BB_MIGRATIONS_PROVIDER=postgres dotnet ef migrations add <Name> --project $PG --startup-project $SP --context TenantDbContext -o Migrations/Tenant

# SQL Server
BB_MIGRATIONS_PROVIDER=sqlserver dotnet ef migrations add <Name> --project $MS --startup-project $SP --context PlatformDbContext -o Migrations/Platform
BB_MIGRATIONS_PROVIDER=sqlserver dotnet ef migrations add <Name> --project $MS --startup-project $SP --context TenantDbContext -o Migrations/Tenant

Before launch, scripts/regen-migrations.sh --i-have-never-deployed is the quicker route. It deletes all four folders and generates a fresh InitialCreate for both providers and both contexts. Use it while nothing real depends on the migration history; the script refuses to run without that flag, as a reminder of the danger below.

Do not regenerate once a database has been migrated

A regenerated InitialCreate has a new name. A database that already applied the old one sees the new one as pending and tries to create tables that already exist, so every tenant fails to migrate. Once any real database has applied your migrations, only add migrations.

Two checks keep migrations honest, and both pipelines run them:

• scripts/check-migration-drift.sh fails when the model has changes that no migration captures. Run it locally too: the relational test suites skip themselves when no database server is available, so a forgotten migration can pass a local test run.
• scripts/check-additive-migrations.sh fails when a migration’s Up() drops or renames a column or table, drops a schema, or alters a column without a // additive-migration-guard: reviewed — <reason> comment on the line above it (widening is safe; making a column NOT NULL or narrower breaks the old version’s writes) (see Expand/contract).

Applying migrations

With Database:UseMigrations=true:

• Platform database — the backend activator runs Database.Migrate() when the host starts. On SQL Server it creates the platform database first if it is missing; on PostgreSQL the migration creates the platform schema, but the database named in the connection string must already exist.
• New tenants — the provisioner creates the schema (PostgreSQL) or database (SQL Server) and migrates it to the latest version.
• Existing tenants — each has its own migration history, so tenants are migrated one by one. The DbMigrator does this at deploy time. Hosts don’t do it on every startup, because with many tenants that would be a long unattended sweep. The exception is Lite, which has no DbMigrator service and turns it on with Database:MigrateTenantsOnStartup; see Deployment → Migrations under Lite.

On PostgreSQL in migrations mode, the platform database name is fixed. A migration hard-codes its schema name, so Database:PlatformDatabaseName must stay blazorblueprint-platform (schema blazorblueprint_platform), and the host refuses to start if it doesn’t. Using a different name means regenerating the migrations against it, which is only safe before your first deployment (see Generating migrations). Tenant schemas are unaffected, and so is SQL Server, where the platform name selects a database rather than a schema.

The DbMigrator

Services/BlazorBlueprint.DbMigrator is a one-shot console app that applies schema before the app takes traffic:

• On PostgreSQL and SQL Server, it migrates the platform database, then every existing tenant. When Database:AllowPluginSchemaDropIn is set, it also creates any missing plugin tables (see below) — and it sweeps tenants for that even in model-driven mode.
• On MongoDB, it reconciles indexes across every tenant: it creates missing indexes, drops orphaned ones that it manages (indexes you created by hand are left alone), and alerts rather than skipping when a unique index can’t be built against existing data.
• It exits 0 on success and non-zero if anything failed, so a deploy gate blocks instead of shipping a partly migrated fleet.
• It applies schema only. Data seeding stays in the app. It needs no Data Protection key ring or Redis, because it never reads an encrypted value.
• On a relational backend it holds a lock for the whole run, so a second migrator started at the same time waits instead of racing it.

Its Database:* settings — DatabaseType, ConnectionString, UseMigrations, AllowPluginSchemaDropIn and the two database names — must match the app hosts’. DatabaseType and ConnectionString ship blank and the migrator refuses to run without them, so it can never migrate a database you didn’t name. Its Database:CommandTimeout ships at 180 seconds (the app hosts use 10) because it runs nothing but DDL.

In production

Every shipped full-stack compose file runs it as the one-shot blazorblueprint-dbmigrator service, and every app service waits on it with depends_on: { condition: service_completed_successfully }. The pipelines also run it explicitly before up. In Kubernetes, run it as an init-container or a Job that must complete before the rollout. You can give it a DDL-privileged connection and the app a less-privileged one.

Emitting SQL instead of applying it

If you apply schema through Flyway, DbUp or plain psql, use --emit-sql. It writes idempotent platform.sql and tenant.sql without opening a database connection. Apply tenant.sql once per tenant schema or database. It only works for a relational backend in migrations mode, so pass those settings:

docker run --rm -v ./sql:/sql \
  -e Database__DatabaseType=postgresql \
  -e Database__UseMigrations=true \
  -e Database__ConnectionString="Host=unused;Database=unused" \
  blazorblueprint-dbmigrator:latest --emit-sql --out /sql

--out is required in the shipped image. It runs read-only as a non-root user, so the default ./sql can’t be written; if you leave it out, the migrator explains this and exits 1.

Runtime backstop: the schema gate

On every request, the web host checks the current tenant’s schema against the app’s model. The verdict is cached per tenant (60 seconds when current, 10 seconds when the tenant is held, 5 minutes when a plugin is unavailable). /Account/* stays reachable so users can still sign in. The platform organisation is never held, so you keep the console to fix the problem: a pending migration or a missing host table there is logged as an error and the organisation is served anyway, though a plugin whose tables are missing is still hidden there as below.

• A pending migration (migrations mode only), or a missing host table or column, sends that tenant to a 503 “under maintenance” page instead of a raw SQL error.
• A missing plugin table or column affects only that plugin. It disappears from the launcher and navigation, its routes return 404, and the rest of the workspace serves normally.

The table and column check runs in model-driven mode too. The gate fails open: if the check itself errors, the request goes through. It does not clear by itself: run the DbMigrator. The one exception is missing tables when Database:AllowPluginSchemaDropIn is set — the gate then creates them for that tenant on its first request and serves it. A missing column is never repaired. To turn the gate off, set Database:SchemaVersionGate:Enabled=false.

Expand/contract

Migrations must be additive: no dropping or renaming a column or table in Up(). During a rolling deploy, old app instances keep serving after the migrator has run, and a dropped column would break them. scripts/check-additive-migrations.sh enforces this in CI. Do a drop in a later release, once nothing running still reads the column. Tightening a column is the same kind of break — making it NOT NULL fails every insert the old version makes without it, and narrowing it truncates or rejects what the old version writes — so add the column nullable and wide now, and tighten it in a later release. Widening is safe; mark that AlterColumn with the reviewed comment described in Generating migrations.

What adding a plugin costs

Plugins ship no migrations of their own. PluginEntityModelContributor adds a plugin’s entities to the shared contexts, so its tables appear in the host’s migrations as plugin_{id}_{Entity}. EF Core allows only one migrations assembly per DbContext, so a plugin could only own its migrations by owning a DbContext — and that would mean a separate migration history per plugin per tenant. One shared context with one history per tenant is the simpler trade.

The plugin’s assembly must declare its id with [assembly: PluginAssembly("your-id")]. That id prefixes its tables, and the host refuses to start if it is missing. See Plugins → Assembly identity.

Change	MongoDB	Relational (migrations mode)
Add a plugin	Drop in and restart	Add a migration, then deploy. Or use AllowPluginSchemaDropIn (below)
Upgrade a plugin that changed its schema	Run the DbMigrator, which creates the new indexes	Add a migration, then deploy. The drop-in pass can’t ALTER a table

In migrations mode, a plugin entity that the committed migrations don’t know about makes Database.Migrate() throw PendingModelChangesWarning — the same check that catches a forgotten migration after any model change. Where it bites depends on the entity:

• a platform-scoped plugin entity is in the platform model, so the host will not start;
• a tenant plugin entity fails every tenant migration instead: the DbMigrator exits non-zero, and creating a new organisation fails.

Either way, add a migration (or use the drop-in flag below).

Dropping a plugin into a running deployment

Database:AllowPluginSchemaDropIn=true (default false) lets you add a plugin to a running relational deployment by dropping in its DLL and restarting, with no rebuild and no new migration. When it is set:

• the model-drift check is relaxed, because the model legitimately contains entities the migrations predate;
• after Migrate(), provisioning runs an additive pass that creates any missing tables and indexes and leaves existing ones alone (CREATE … IF NOT EXISTS on PostgreSQL, an existence check per statement on SQL Server). This covers the platform database at host start as well as tenants;
• existing tenants get the tables either on their first request (the schema gate creates them) or when the DbMigrator, or a host with Database:MigrateTenantsOnStartup=true (the BackgroundWorker, or the Web host under Lite), sweeps every tenant.

It creates tables but cannot ALTER them. So it installs a plugin, but it can’t upgrade one whose schema changed; that still needs a migration. Leave the flag off in your own repository and in CI, where the drift check should catch a forgotten migration.

Put the plugin DLL wherever the sweep runs

The DbMigrator and the BackgroundWorker build their model from the plugin assemblies they can load: their own directory, and a Plugins/ folder beneath it. If you drop the DLL only into the Web host’s Plugins/ folder, the migrator’s model has no plugin entities. It creates nothing, reports every tenant as current, and exits 0. Copy the plugin next to every host and the DbMigrator. The migrator logs which plugin assemblies it loaded, so check that line.