DorkNet/DorkNet.Server/Data/MIGRATIONS.md

Migrations & legacy data upgrades

Current state (2026-05-25 — post-consolidation)

• One migration under Migrations/: 20260525181055_Initial.cs (generated by dotnet ef migrations add Initial against the current entity model — captures all 67 DbSet<> tables).
• LegacyUpgrades.cs holds idempotent data transforms that can't be derived from the entity model. Runs once per server boot, after EF migrations complete and before RoomService.SeedAsync().

What changed in the consolidation

Before this pass, the codebase had two parallel schema-management mechanisms accumulating per-feature noise:

1. 34 EF migrations under Migrations/ — applied to SQLite via db.Database.Migrate().
2. 65 RunPatchAsync calls in Program.cs:574-1011 — idempotent Postgres ALTER/CREATE statements layered on top of EnsureCreated(), because the Postgres path skipped EF migrations.

Both were collapsed in 2026-05-25:

• The 34 migrations were deleted and regenerated as a single 20260525181055_Initial. The model snapshot was regenerated to match.
• The RunPatchAsync block was deleted entirely. Postgres still uses EnsureCreated (which now reads the same up-to-date entity model); no per-feature patches needed.
• The PhotonRegion coerce (the only data transform in the block) was moved into LegacyUpgrades.CoercePhotonRegionAsync.
• The Storage admin page (admin-ui/src/pages/Storage.tsx) and StorageBackfillController.cs were deleted — they were the UI for draining the legacy RoomDataBlobs.Bytes column into S3, which is no longer needed.

Production cutover (one-time SQL)

Existing prod databases need a one-time fixup to tell EF "the new Initial is already applied here" so the next boot doesn't try to CREATE TABLE over existing data.

Postgres path

The Postgres production schema was built via EnsureCreated() plus the (now-deleted) RunPatchAsync block. No __EFMigrationsHistory table exists because EF Core only creates it when Migrate() runs. Postgres continues to use EnsureCreated() post-consolidation, so there's nothing to do — EnsureCreated() reads the entity model, sees the schema already matches, and no-ops. Just deploy the new build.

If you ever switch the Postgres path to Migrate(), run this once before the cutover deploy:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20260525181055_Initial', '10.0.0');

SQLite path (dev DBs)

Handled automatically by BaselineExistingSchemaIfNeeded() in Program.cs. On boot it detects two cutover scenarios and reconciles the migration-history table to match the assembly's current migration list:

• Legacy EnsureCreated — schema present, history table absent. Creates the history table and stamps the Initial.
• Stale history post-consolidation — schema present, history has entries from the old 34 per-feature migrations. Wipes them and stamps the Initial.

Migrate() then sees the reconciled history and no-ops. No manual SQL needed.

Gotcha: partial-migration dev DBs

BaselineExistingSchemaIfNeeded assumes the on-disk schema matches the current entity model. If a dev DB was created mid-cycle and missed some of the later migrations (e.g. AddPlaylists, AddClubAnnouncementsAndCategories, AddWeeklyChallengeSettings), the reconciled boot will succeed but the missing tables will cause queries to fail at runtime (no such table: Playlists).

Fix: either delete the dev DB and re-seed, or apply the missing migrations against your DB before upgrading to the consolidated build. The latter requires checking out the pre-consolidation commit, running dotnet ef database update, then checking back out.

Adding migrations after consolidation

One release = one migration. Don't accumulate per-feature micros.

The post-consolidation discipline:

1. Iterate freely between releases — edit entities, run the server against your dev DB (sqlite under bin/Debug/.../data/). EF will reject the schema mismatch and you fix it locally however you like. Local DBs are disposable.
2. At release cut, run dotnet ef migrations add Release_v{N}_{M} once to bundle all the schema deltas from the iteration window into a single migration.
3. Data transforms (renames of seed rows, backfills, etc.) go in LegacyUpgrades.cs as new private methods, NOT as new migrations. They run on every boot and are idempotent.

Regenerating Initial from scratch

If the model drifts far enough that you want to re-collapse to one Initial again:

# 1. Delete all migration files + the model snapshot.
Remove-Item DorkNet.Server\Migrations\2026*.cs
Remove-Item DorkNet.Server\Migrations\DorkNetDbContextModelSnapshot.cs

# 2. Regenerate Initial from the current entity model.
dotnet ef migrations add Initial `
    --project DorkNet.Server `
    --context DorkNetDbContext

# 3. Eyeball the generated Up() body before committing — verify it
#    captures every DbSet<> in DorkNetDbContext plus every
#    relationship and index.

Existing DBs auto-reconcile via BaselineExistingSchemaIfNeeded (SQLite) or no-op via EnsureCreated (Postgres). No manual cutover.