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 during host boot for the
  standalone server and for service-slice containers, after schema
  bootstrap completes 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 legacy storage backfill 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 standalone or service-slice
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
standalone image or microservices compose stack.

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

```sql
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:

```pwsh
# 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.