Files
sor/SOR.Database.12FactorPlan.md
2026-07-02 13:09:05 -04:00

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.