# SOR.Database 12-Factor Refactor Plan Date: 2026-06-10 Scope reviewed: - `Server/src/SOR.Database` - DbContexts for Identity, Catalog, Collection, Bazaar, Chat, Game, and Support - EF migrations under `Server/src/SOR.Database/Migrations` - Database registration in server modules, `SOR.Host`, and `SOR.Admin` - Admin migration startup behavior in `Server/src/SOR.Admin/Program.cs` Reference: https://12factor.net/ ## Executive Summary `SOR.Database` is the shared persistence module for the server. It contains entity models, EF Core DbContexts, schema-specific migrations, design-time factories, and service registration helpers for seven logical schemas: `identity`, `catalog`, `collection`, `bazaar`, `chat`, `game`, and `support`. The module already has strong foundations: - Each domain schema has its own DbContext and migration folder. - Runtime services register only the DbContexts they need. - Connection strings are externalized through `ConnectionStrings:{ContextName}` with fallback to `ConnectionStrings:Default`. - Design-time factories exist for all DbContexts. - PostgreSQL schemas are explicit through `HasDefaultSchema`. - Several important uniqueness and query indexes are already modeled. - Cross-schema IDs are intentionally represented as bare GUIDs where EF foreign keys would couple schemas too tightly. The main 12-Factor gaps are operational: - `SOR.Admin` currently applies every schema migration during normal web startup. - Database connection options are not typed or validated beyond missing connection-string checks. - Design-time factories fall back to a hardcoded local Postgres connection string. - EF registration does not define retry policy, command timeout, migrations assembly conventions, pool sizing, or service-specific read/write intent. - Cross-schema transaction assumptions are implicit. Some application flows rely on all schemas being in the same physical Postgres database. - Schema ownership is documented in comments but not enforced through code boundaries or deployment process. - There are no reusable health/readiness checks per DbContext. - Shared economic logic such as `CardSalvageValues` lives in the database project even though it is domain policy rather than persistence. The plan is to keep `SOR.Database` as the central persistence package, but harden it around 12-Factor operation: externalized validated config, explicit migration/admin processes, schema ownership, readiness checks, transaction policy, query/index governance, and clean separation between persistence models and domain policy. ## Current Structure Observations ### Strong Foundations - `ServiceRegistration.cs` has one registration method per DbContext. - Context-specific connection strings allow production to split schemas later. - Local development can use one default connection string for all schemas. - Migrations are grouped by schema folder. - `SorIdentityDbContext` uses ASP.NET Core Identity but renames tables into the `identity` schema. - `CatalogDbContext` includes content/versioning indexes such as `DefinitionVersion`. - `CollectionDbContext` enforces unique deck-card rows and player cosmetic ownership. - `BazaarDbContext` includes indexes for listings, transactions, purchase records, and bids. - `ChatDbContext` enforces unique channel membership and block pairs. - `GameDbContext` enforces unique action-log sequence numbers per session. - `SupportDbContext` is small and isolated. ### Main Problems - Runtime database registration is minimal and does not apply provider options consistently. - There is no `DatabaseOptions` or `DbContextOptions` validation layer. - Connection strings can silently fall back to `Default`, which is convenient locally but risky when a production schema was expected to be isolated. - Admin startup runs migrations for all contexts, which couples web boot to schema changes. - There is no dedicated migration runner/admin process. - There is no shared health check registration for required schemas. - Cross-schema references are bare GUIDs, but there are no validation jobs to find orphaned IDs. - Some domain policy is located in the database project, such as salvage value calculation. - JSONB payloads are common, but schema/version validation for those payloads lives outside this module or is not centralized. - Several application-level invariants are noted in comments instead of enforced by database constraints or validation jobs. ## Target Database Shape Keep `SOR.Database` as the persistence module, but organize operational concerns explicitly: ```text Server/src/SOR.Database/ Configuration/ DatabaseOptions.cs DbContextConnectionOptions.cs DatabaseOptionsValidator.cs Bootstrap/ DatabaseRegistration.cs DatabaseHealthChecks.cs DatabaseProviderOptions.cs Contexts/ Identity/ Catalog/ Collection/ Bazaar/ Chat/ Game/ Support/ Entities/ ... Migrations/ Identity/ Catalog/ Collection/ Bazaar/ Chat/ Game/ Support/ Operations/ MigrationRunner.cs SchemaValidationJob.cs OrphanReferenceValidationJob.cs IndexAuditJob.cs Testing/ DbContextModelTests.cs MigrationSmokeTests.cs ``` Desired process roles: - Runtime services: register only required DbContexts and never apply migrations. - `db-migrate`: one-off release/admin process that applies selected migrations. - `db-validate`: one-off/admin job that checks schema state, cross-schema references, and data invariants. - Admin web: reads/writes data, but does not own migration execution during normal startup. ## 12-Factor Gap Map ### 1. Codebase Mostly aligned. Database models and migrations are in one package, but domain policy and persistence concerns are mixed in a few places. Plan: - Keep one database package for EF models and migrations. - Move non-persistence domain policy, such as card salvage economics, toward the owning domain module or a shared domain-policy package. - Keep schema-specific migrations grouped and named consistently. ### 2. Dependencies Mostly aligned. EF Core, Npgsql, and Identity EF dependencies are explicit. Plan: - Keep provider dependencies isolated in `SOR.Database`. - Expose registration helpers and health checks, not raw provider setup spread across services. - Standardize provider options in one place: retry policy, command timeout, migrations assembly, and query-splitting defaults where appropriate. ### 3. Config Partially aligned. Connection strings are externalized, but behavior is not typed or validated. Plan: - Add typed database options. - Validate required contexts for active services. - Make fallback-to-default policy configurable and safe by environment. - Add provider options for timeout, retry count, pool size, and migration behavior. Candidate config shape: ```json { "Database": { "AllowDefaultConnectionFallback": true, "CommandTimeoutSeconds": 30, "EnableRetryOnFailure": true, "MaxRetryCount": 3, "MaxRetryDelaySeconds": 5, "RequireContextSpecificConnectionStrings": false, "HealthCheckTimeoutSeconds": 3 }, "ConnectionStrings": { "Default": "", "Identity": "", "Catalog": "", "Collection": "", "Bazaar": "", "Chat": "", "Game": "", "Support": "" } } ``` ### 4. Backing Services Partially aligned. PostgreSQL is the backing service, but the code supports both one physical database and split context databases without making all consequences explicit. Plan: - Document which workflows require same-physical-database transactions. - Add startup validation or warnings when contexts are split but application flows assume shared transactions. - Add readiness checks per context. - Add schema-version checks to readiness for stricter environments. ### 5. Build, Release, Run Weakly aligned today because Admin applies migrations during normal startup. Plan: - Move migrations to a dedicated `db-migrate` process. - Make migration execution explicit in deployment/release pipelines. - Runtime services should fail readiness if schema is incompatible, not mutate schema during boot. - Add migration smoke tests and rollback notes for destructive migrations. ### 6. Processes Partially aligned. DbContexts are registered per service, but migration/admin processes are not separated from Admin runtime. Plan: - Add one-off migration runner. - Add one-off validation jobs for orphan references, data invariants, and index checks. - Keep normal runtime processes web/worker-only. ### 7. Port Binding Aligned. `SOR.Database` binds no ports directly. Plan: - Keep all HTTP/gRPC/WebSocket binding in Host/Admin. - Surface database health through consuming processes. ### 8. Concurrency Partially aligned. Some uniqueness constraints are present, but several important invariants remain application-level. Plan: - Audit application-level invariants and decide which should become database constraints, partial indexes, exclusion constraints, or validation jobs. - Add explicit transaction helpers for same-database multi-context workflows. - Define policy for split-database workflows: no shared transaction, outbox/saga required. - Add tests for known concurrency-sensitive invariants. ### 9. Disposability Partially aligned. DbContexts are scoped through DI, but migration-on-startup reduces disposability. Plan: - Remove runtime startup migrations. - Ensure short command timeouts and cancellation support are consistently applied. - Keep DbContext lifetimes scoped. - Add retry policy only for safe transient failures. ### 10. Dev/Prod Parity Partially aligned. Default connection fallback helps local dev, but can hide production misconfiguration. Plan: - Keep simple local setup with one database and many schemas. - Require explicit connection strings in production. - Document local migration commands per context. - Add containerized local Postgres setup and seed/validation commands if not already present. ### 11. Logs Partially aligned through EF/host logging, but database operations lack consistent operational signals. Plan: - Add structured logs for migration start/end/failure, schema validation, health checks, and cross-schema validation jobs. - Keep SQL command logging disabled or limited in production unless debugging. - Add metrics for migration duration, failed health checks, connection failures, slow queries, and pool exhaustion if available. ### 12. Admin Processes Weakly aligned. Migration and validation are admin processes but are currently tied to Admin startup or absent. Plan: - Add `db-migrate`, `db-validate`, `db-seed`, and `db-audit` commands/jobs. - Make destructive operations dry-run capable. - Add schema and data validation reports for Admin/release workflows. ## Refactor Phases ### Phase 0: Baseline Inventory Goals: - Make the current database shape explicit before changing behavior. Work: - Document every DbContext, schema, owning service, and consumer service. - Document cross-schema bare GUID references. - Document workflows that rely on multiple schemas in one physical database. - Add model tests that verify schema names, key indexes, and uniqueness constraints. Done when: - Database ownership and cross-schema assumptions are visible in docs/tests. ### Phase 1: Typed Database Options Goals: - Make provider and fallback behavior explicit. Work: - Add `DatabaseOptions`. - Add connection-string resolution that can enforce context-specific strings in production. - Add provider configuration for command timeout and transient retry. - Keep current default fallback behavior for local development. Done when: - Runtime services can validate database config before accepting traffic. - Production can opt out of silent `Default` fallback. ### Phase 2: Shared Registration and Health Checks Goals: - Standardize DbContext registration and readiness. Work: - Centralize Npgsql provider options. - Add `AddDatabaseHealthChecks` helpers per context. - Add optional schema-version compatibility checks. - Expose health check tags by service/schema. Done when: - Host/Admin can report readiness for exactly the schemas each process requires. ### Phase 3: Migration Process Separation Goals: - Align build/release/run with 12-Factor. Work: - Create a one-off migration runner or documented `dotnet ef database update` release process. - Remove or gate Admin startup migrations behind a development-only option. - Add migration ordering/runbook documentation for all schemas. - Add migration smoke tests in CI. Done when: - Normal web/worker startup does not apply schema migrations in production. - Releases include an explicit migration step. ### Phase 4: Schema Ownership and Boundaries Goals: - Reduce accidental cross-domain coupling. Work: - Create a schema ownership matrix. - Mark which modules may write each schema. - Identify read-only cross-schema dependencies. - Move domain-policy helpers out of `SOR.Database` where appropriate. Done when: - Each schema has a clear owner and allowed consumers. - Shared database project is not a dumping ground for domain behavior. ### Phase 5: Cross-Schema Transaction Policy Goals: - Make multi-context writes safe and explicit. Work: - Add a transaction helper for same-connection/same-database multi-context operations. - Add runtime validation that participating contexts resolve to compatible connection targets. - Document flows that need outbox/saga if schemas are split across databases. - Add tests for transaction rollback across contexts. Done when: - Bazaar/Collection/Auth-style cross-context mutations have a standard transaction path. - Splitting schemas across physical databases cannot silently break atomic workflows. ### Phase 6: Constraint and Index Audit Goals: - Move critical invariants into the database where practical. Work: - Audit unique constraints, partial indexes, and query indexes across all contexts. - Add missing constraints for application-level invariants where possible. - Use validation jobs where constraints are impractical. - Add index usage/slow-query review docs. Done when: - High-risk invariants are either database-enforced or validated by an explicit job. ### Phase 7: JSONB and Versioned Payload Governance Goals: - Make JSONB payloads maintainable over time. Work: - Inventory all JSONB columns and opaque JSON string columns. - Add payload version fields where needed. - Add validation helpers/tests for game state, board templates, abilities, lobbies, notifications, and bazaar pack slots. - Add migration/backfill strategy for JSON payload changes. Done when: - JSON payload evolution has tests and migration rules. ### Phase 8: Data Validation and Repair Jobs Goals: - Detect cross-schema drift and operational data corruption. Work: - Add orphan-reference validation jobs for bare GUID references. - Add data invariant checks per schema. - Add dry-run repair jobs for safe cases. - Report validation status in Admin or release logs. Done when: - Operators can detect and triage orphaned IDs, invalid JSON, broken content references, and invalid domain states. ### Phase 9: Observability and Runbooks Goals: - Make database operations diagnosable. Work: - Add logs/metrics for health checks, migrations, validation jobs, connection failures, slow queries, and command timeouts. - Add runbooks for migration failure, rollback, lock contention, pool exhaustion, and data repair. - Document backup/restore expectations per schema. Done when: - Database failures have actionable operational guidance. ## Suggested Work Order 1. Create a schema ownership and dependency matrix. 2. Add baseline model tests for schemas, indexes, and uniqueness constraints. 3. Add typed `DatabaseOptions` and shared Npgsql provider setup. 4. Add DbContext-specific health checks. 5. Move production migrations out of Admin startup. 6. Add a migration runner/release process. 7. Standardize multi-context transaction helpers. 8. Audit constraints, indexes, and JSONB payloads. 9. Add validation/repair jobs for cross-schema references. 10. Add database runbooks and operational metrics. ## Completion Criteria The refactor is complete when: - Database config is typed and validated. - Runtime services register only required DbContexts with standard provider options. - Production migrations run through explicit release/admin processes, not normal web startup. - DbContext health checks are available and used by consuming processes. - Cross-schema transaction assumptions are documented, validated, and backed by shared helpers. - Schema ownership is documented and enforced by conventions/tests. - Critical invariants are enforced by constraints or validation jobs. - JSONB payloads have version/validation strategy. - Operators have runbooks for migrations, backups, restores, locks, slow queries, and data repair. ## Immediate Low-Risk Next Steps - Add `DatabaseOptions` with current defaults and no behavior change. - Add a schema ownership matrix to the repo docs. - Add model tests for key uniqueness/index expectations. - Add database health check helpers for each DbContext. - Gate `SOR.Admin` startup migrations behind a development-only option or plan a dedicated migration runner.