439 lines
17 KiB
Markdown
439 lines
17 KiB
Markdown
# 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.
|