8. Crosscutting Concepts

8.1 Persistence

Strategy

• ORM: Entity Framework Core (database-first scaffolding initially, code-first migrations for new features)
• Tenant isolation: Database-per-tenant on Azure SQL MI. Connection string resolved from tenant context.
• Schema approach: Preserve legacy schema (pscat_/rmcat_/pxcat_ prefixes) initially. Add audit columns (CreatedAt, UpdatedAt, CreatedBy, UpdatedBy). Add proper foreign keys. Modernize naming in later phases.

Conventions

• All entities inherit from a base AuditableEntity with timestamp and user tracking
• Soft deletes via IsDeleted / DeletedAt columns on entities that require it
• No cascade deletes — all deletions are explicit
• Read-heavy queries use projection (no loading full entity graphs)

Transaction Management

• Unit of Work pattern via EF Core DbContext
• Business operations wrap related changes in a single SaveChangesAsync() call
• Long-running operations (BestEx batch, pooling) use explicit transactions with savepoints
• No distributed transactions — bounded context operations are orchestrated, not choreographed

8.2 Security

Authentication

• Identity provider TBD (ADR-013) — Keycloak, Azure AD, or Odoo as IdP
• JWT-based authentication for API access
• Refresh token rotation for session management

Authorization

• Current legacy model: pxcat_groups table defines role-based access in the desktop app
• Target model: Policy-based authorization in .NET 8 using IAuthorizationHandler
• Migration path: map pxcat_groups roles to modern policy claims during coexistence period
• Tenant context is always validated before any data access

Tenant Isolation

• Every API request resolves a tenant context from the JWT
• Database connection string is selected per-tenant — no shared databases
• Middleware validates tenant access before any controller action executes
• No cross-tenant queries are possible at the data layer

8.3 Error Handling

Strategy

• Domain exceptions for business rule violations (e.g., LoanNotEligibleForTradeException)
• Global exception middleware maps exceptions to appropriate HTTP status codes
• Problem Details (RFC 7807) for structured error responses
• No exception swallowing — all errors are logged and surfaced

Error Categories

Category	HTTP Status	Handling
Validation errors	400	Return field-level errors for UI display
Business rule violations	422	Return business rule description
Not found	404	Standard 404 with entity type
Authorization failures	403	Log and return generic forbidden
Tenant context errors	401	Redirect to authentication
Internal errors	500	Log full stack trace, return generic message

8.4 Logging and Observability

OpenTelemetry

• Structured logging via ILogger<T> with OpenTelemetry exporters
• Distributed tracing for request flows across bounded contexts
• Metrics for business KPIs (BestEx run times, pool creation duration, trade settlement counts)

Log Levels

• Trace: Detailed calculation steps (BestEx per-loan analysis)
• Debug: Query execution, cache hits/misses
• Information: Business events (trade created, pool approved, loan locked)
• Warning: Degraded conditions (cache miss, slow query, retry)
• Error: Failed operations that affect users
• Critical: System-level failures (database unreachable, tenant resolution failure)

Export Targets

• Current: Azure Application Insights (via OpenTelemetry)
• Future: Grafana/Loki/Tempo stack (vendor-agnostic, same OpenTelemetry exporters)

8.5 Audit Trail

Requirements

• All entity changes must be tracked (who, when, what changed)
• Financial calculations must be reproducible (inputs + outputs stored)
• Trade and pool operations require complete audit history for compliance

Implementation

• EF Core interceptors automatically capture change tracking data
• Audit log table stores: entity type, entity ID, action, old values, new values, user, timestamp
• Critical operations (trade settlement, pool approval) write explicit audit events
• Audit data is append-only — never updated or deleted

8.6 Caching

Layers

Layer	Technology	TTL	Use Case
Tenant config	Redis	5 minutes	Connection strings, feature flags, settings
Rate sheets	Redis	Until invalidated	Investor pricing data (invalidated on update)
BestEx results	Redis	Until rate sheet change	Cached analysis results per loan
Static reference data	In-memory	1 hour	Agency codes, product types, state lists

Invalidation

• Rate sheet updates broadcast an invalidation event via Service Bus
• Tenant config changes invalidate on next request (cache-aside pattern)
• No stale data is acceptable for financial calculations — cache miss triggers fresh calculation