Persist and administer PortfolioRiskPolicy revisions

[carndog]

Outcome

Make PortfolioRiskPolicy Drafts and committed revision history durable, and expose the #31 scheduling and future-edit operations through the authenticated administration API.

Acceptance criteria

• Persist the policy aggregate scope and revision metadata relationally
• Store one validated canonical policy-definition XML document in a SQL Server/Azure SQL xml column for each revision
• Persist stable policy and revision IDs, nullable business revision numbers, creation metadata, optional change reason, optional committed periods and separately stored Draft proposals
• Restore the complete aggregate through the controlled path established in Extend SQL persistence with monitoring-rule revision history #32, preserving IDs, numbering, definitions, metadata and periods without replaying scheduling commands
• Preserve Design reusable revision timeline mechanics and apply them to monitoring rules #31 numbering: Draft numbers are null; committed ranks follow effective order; only entirely future ranks may change
• Map NodaTime Instant values to the documented UTC SQL representation and prove precision behaviour
• Reuse the SQL rowversion and aggregate timeline concurrency approach from Extend SQL persistence with monitoring-rule revision history #32 for Draft and future edits, commits, splits, rescheduling/removal and renumbering
• Add keys, constraints and indexes supporting valid committed periods, unique starts/numbers and at most one applicable policy revision per portfolio and execution environment at any instant
• Persist each Design reusable revision timeline mechanics and apply them to monitoring rules #31 mutation atomically, including splits, adjacent boundary reconciliation and future renumbering; preserve historical definitions and later scheduled periods
• Detect stale edits and competing timeline mutations, including commits of different Draft rows, without losing data or admitting overlapping committed periods
• Create, retrieve, edit and delete multiple independent Drafts, including an initial definition or a copy of an existing revision; allow overlapping tentative proposals outside the committed timeline
• Retrieve Drafts separately, the current applicable policy, the committed timeline and the revision effective at a supplied UTC instant using half-open periods and excluding Drafts
• Provide explicit ApplyNow/Schedule operations and revalidate the complete definition, captured now and current timeline at commit
• Allow definition replacement, rescheduling and removal of entirely future committed revisions only while now < EffectiveFrom
• Reject definition changes or deletion once now >= EffectiveFrom; change the remaining future through a split at T >= now, preserving the original ID/definition before T and the prior end boundary
• Reject backdating, conflicting starts and invalid committed periods; derive applicability from dates without stored Effective/Superseded flags or an activation job
• Expose typed JSON DTOs and never expose EF Core entities or raw XML
• Require opaque resource/timeline concurrency tokens for mutable operations and return HTTP 412 for stale mutations; expose stable revision GUIDs as identity and future numbers as display metadata
• Require Entra-authenticated administration in the deployed environment
• Add non-destructive migration, restoration, persistence and API tests covering multiple Drafts, commit-time validation, exact boundaries, future edits/rescheduling/removal, immediate/future splits, historical preservation, rollback and concurrent timeline mutations
• Keep real policy values out of source, examples and logs

Dependencies

• PortfolioRiskPolicy Domain and XML contract: Define the revision-controlled PortfolioRiskPolicy domain and XML contract #49
• Reusable revision timeline: Design reusable revision timeline mechanics and apply them to monitoring rules #31
• Established SQL/XML persistence approach: Extend SQL persistence with monitoring-rule revision history #32
• Authenticated administration API foundation: Protect the deployed API with Entra ID Easy Auth #38

Clarifications

Reuse the composition and temporal rules from #31 and the persistence approach from #32. The policy remains a separate aggregate with its own definition/XML contract. Draft proposals never reserve time or become applicable automatically. A committed future revision applies at its start without another action and remains editable only until that instant.

Out of scope

• Portfolio performance calculation
• RiskGate decision processing
• User interface