Files
sor/deploy/database-migrations.md
fe5cd2d424 Add EF design-time factories; fix Game migration; document DB ops
- DesignTimeFactories.cs: adds IDesignTimeDbContextFactory<T> for all
  6 contexts so dotnet-ef tools work without starting the full ASP.NET
  host (previously failed because Redis/NATS aren't available locally)
- SOR.Host.csproj: adds Microsoft.EntityFrameworkCore.Design (PrivateAssets=all)
  as the EF design-time host still requires it in the startup project
- Game/AddGameRulesAndCustomLobby: emptied Up/Down — migration was generated
  with a stale snapshot duplicating InitialCreate tables; actual GameRuleSet
  and CustomLobby schema is in AddBoardDimensions
- deploy/database-migrations.md: documents how to apply/add migrations
  against any environment; records pg-forge-01 state as of 2026-02-27

Generated with [Claude Code](https://claude.ai/code)
via [Happy](https://happy.engineering)

Co-Authored-By: Claude <noreply@anthropic.com>
Co-Authored-By: Happy <yesreply@happy.engineering>
2026-02-27 09:32:30 -05:00

5.6 KiB

SOR Database Migrations

SOR uses EF Core 8 with 6 independent DbContexts, each owning a separate PostgreSQL schema. All migrations live in Server/src/SOR.Database/Migrations/<Schema>/.


Prerequisites

# Install the dotnet-ef global tool (once per machine)
dotnet tool install --global dotnet-ef

# Verify
dotnet ef --version   # must be >= 8.x

Contexts and Schemas

Context Schema Migration folder
SorIdentityDbContext identity Migrations/Identity/
CatalogDbContext catalog Migrations/Catalog/
CollectionDbContext collection Migrations/Collection/
BazaarDbContext bazaar Migrations/Bazaar/
ChatDbContext chat Migrations/Chat/
GameDbContext game Migrations/Game/

All contexts share the same PostgreSQL database (SOR) but are completely isolated by schema.


Applying Migrations to a Remote Server

Run from Server/ directory. The --connection flag overrides whatever is in appsettings.json and is the correct way to target a specific environment.

cd Server

CONN="Host=<HOST>;Port=5432;Database=SOR;Username=<USER>;Password=<PASSWORD>"

# Apply all 6 contexts (run in order — schemas are independent, no cross-schema FKs)
dotnet ef database update --project src/SOR.Database --context SorIdentityDbContext --connection "$CONN" --no-build
dotnet ef database update --project src/SOR.Database --context CatalogDbContext     --connection "$CONN" --no-build
dotnet ef database update --project src/SOR.Database --context CollectionDbContext  --connection "$CONN" --no-build
dotnet ef database update --project src/SOR.Database --context BazaarDbContext      --connection "$CONN" --no-build
dotnet ef database update --project src/SOR.Database --context ChatDbContext        --connection "$CONN" --no-build
dotnet ef database update --project src/SOR.Database --context GameDbContext        --connection "$CONN" --no-build

Note: --no-build skips rebuilding and uses already-compiled binaries in bin/. Omit it (or run dotnet build first) if you just changed migration files.

pg-forge-01 (production)

HOST=172.10.1.127
USER=SOR-DBA
# password is stored in sor-app-secrets k8s Secret (Cdn__AccessKey isn't it — see deploy/secrets/)

Checking Migration Status

CONN="Host=<HOST>;..."

# Lists all migrations; any marked (Pending) need to be applied
dotnet ef migrations list --project src/SOR.Database --context SorIdentityDbContext --connection "$CONN" --no-build
dotnet ef migrations list --project src/SOR.Database --context CatalogDbContext     --connection "$CONN" --no-build
dotnet ef migrations list --project src/SOR.Database --context CollectionDbContext  --connection "$CONN" --no-build
dotnet ef migrations list --project src/SOR.Database --context BazaarDbContext      --connection "$CONN" --no-build
dotnet ef migrations list --project src/SOR.Database --context ChatDbContext        --connection "$CONN" --no-build
dotnet ef migrations list --project src/SOR.Database --context GameDbContext        --connection "$CONN" --no-build

Adding a New Migration

cd Server

# 1. Make your model changes in SOR.Database/Entities/ or Contexts/

# 2. Add the migration (pick the right context)
dotnet ef migrations add <MigrationName> \
  --project src/SOR.Database \
  --context <ContextName> \
  --output-dir Migrations/<Schema>

# Example:
dotnet ef migrations add AddCardFoilIndex \
  --project src/SOR.Database \
  --context CollectionDbContext \
  --output-dir Migrations/Collection

# 3. Review the generated Up()/Down() in Migrations/<Schema>/<timestamp>_<Name>.cs
#    Make sure it only contains the delta — NOT a full recreation of existing tables.

# 4. Apply to target DB
dotnet ef database update --project src/SOR.Database --context CollectionDbContext --connection "$CONN"

Design-time factories (DesignTimeFactories.cs in SOR.Database) allow the EF tools to instantiate contexts without starting the full ASP.NET Core application. They read the connection string from ConnectionStrings__Default env var, falling back to localhost for local dev.


How EF Tracks Applied Migrations

Each schema gets its own __EFMigrationsHistory table:

identity.__EFMigrationsHistory
catalog.__EFMigrationsHistory
collection.__EFMigrationsHistory
bazaar.__EFMigrationsHistory
chat.__EFMigrationsHistory
game.__EFMigrationsHistory

EF Core compares these tables to the migration files to determine what's pending.


Known Migration History

pg-forge-01 — last applied (2026-02-27)

Context Last migration applied
Identity 20260226111950_AddRankScore
Catalog 20260226012910_AddDefinitionVersionAndRetired
Collection 20260225231700_AddPlayerCosmetics
Bazaar 20260225231658_AddBazaarCosmetics
Chat 20260226012908_AddPresenceAndNotifications
Game 20260226162145_AddBoardDimensions

Notes

  • GameDbContext/20260226125446_AddGameRulesAndCustomLobby is intentionally empty. It was regenerated with a stale snapshot that duplicated InitialCreate tables. The actual game_rule_sets and custom_lobbies tables were added in the following AddBoardDimensions migration.