dms-v0.7.1-alpha.0.269

[DMS-1228]      Make CMS request logging structured (#1063)

* [DMS-1228] Make CMS and DMS request logging structured

Squash-rebase of the DMS-1228 structured request logging work onto
origin/main (currently at DMS-1227's 413 handling).

The original pre-rebase history is preserved on the local branch
DMS-1228-pre-rebase-backup. Rebase conflicts were resolved against
DMS-1227's oversized-request-body 413 handling so the LoggingMiddleware
BadHttpRequestException path continues to short-circuit oversized
requests while participating in the structured logging pipeline.

Structured request logging spans both services:
- CMS RequestLoggingMiddleware emits structured start/completion events
  with a per-request BeginScope.
- DMS Core RequestResponseLoggingMiddleware and the DMS frontend
  LoggingMiddleware emit HttpRequestCompleted / HttpRequestFailed
  contract events with sanitized correlation values.
- Shared EventId definitions live in per-service RequestLoggingEventIds
  classes.
- docs/LOGGING.md documents the request-log contract; the docker-compose
  effective-schema-hash module and its Pester tests are included.

* [DMS-1228] Address request-logging review fixes

Correctness:
- Restore context.TraceIdentifier as the 500 error-response body traceId;
  the sanitized correlation value is used for log events only.
- Emit a Debug "Request started" breadcrumb in CMS request logging so a hung
  request still produces a log line.
- Emit the 413 oversized-body path as an HttpRequestCompleted contract event
  (StatusCode 413) instead of a bare Warning, so it is visible to the collector
  contract; the expected control-flow exception is not attached (S6667 is
  suppressed narrowly with justification).
- Allow empty log output in the effective-schema-hash module (@() -> null).
- Fix "after"/"in" DurationMs template drift in DMS core middleware.
- Restore request identity (method/path/traceId) in the rethrow message so
  host-level logging retains correlation after the scope is disposed.

Cleanup:
- Collapse the CMS duplicate LogError to one nullable-exception call, replace
  scope-dictionary readbacks with locals computed once, and simplify
  GetFailureStatusCode in both frontends.
- Remove the unused HasLongStructuredProperty helper, the duplicate DMS-app
  appsettings test and its now-unused helpers, and the dead legacy
  string-formatter branch; modernize the test TestLoggers.

Docs:
- Document the 413 completion path and the outputTemplate/JSON-formatter footgun
  in LOGGING.md and the DMS appsettings example.

Known tradeoff: the per-request logging scope is still constructed
unconditionally (not IsEnabled-gated) to preserve downstream scope enrichment.

* [DMS-1228] Align 500 response traceId with request logs and verify Serilog appsettings binding

Address review findings on the structured request logging work:

- DMS frontend LoggingMiddleware now writes the same sanitized trace id in
  the 500 error response body that the request log events carry as TraceId,
  so a client-reported trace id can be located in the logs when
  CorrelationIdHeader is configured. This also matches the rest of DMS,
  where core FailureResponse bodies already return the correlation-aware
  value. The unit test that previously codified the divergence now asserts
  the body and log values match.

- Add an end-to-end Serilog binding test to both CMS and DMS frontend unit
  suites. Each test loads the application's real appsettings.json, builds
  LoggerConfiguration().ReadFrom.Configuration(...).Enrich.FromLogContext()
  exactly as WebApplicationBuilderExtensions does, captures console output,
  and asserts the emitted line is newline-delimited JSON carrying the
  request-log contract properties, EventId, and RenderedMessage. The file
  sink path is redirected to the test work directory so creating the logger
  does not write into the repository. The existing JSON-shape tests remain
  as fast-failing complements.

- Fix a vacuous-pass hazard in the JSON assertions added on this branch:
  null-conditional chains like properties?["X"]?.GetValue<T>().Should()
  silently skip the assertion when the property is missing. All such chains
  in the two request-logging test files are now parenthesized so a missing
  property flows into the assertion as null and fails.

- Document the request logging EventId values (1228001/1228002) in
  docs/LOGGING.md as the source of truth shared by CMS and DMS, point both
  RequestLoggingEventIds class comments there, and pin the values with unit
  tests in both applications (DMS was missing its pin). EventId equality
  ignores Name, so the tests assert Id and Name separately.

- Document in docs/LOGGING.md that the file sinks intentionally share the
  JSON formatter: file logs carry the same structured properties but are
  not part of the console collector contract and omit RenderedMessage.

Cross-application sharing of the event-id type, test helpers, and log
construction was reviewed and deliberately not done: CMS and DMS build as
separate solutions with no shared assembly, so the documented contract plus
per-application pinned tests are the drift control.

* [DMS-1228] Address merged review findings for request logging

- DMS 413 completions log the status the client actually received when
  the response already started
- CMS GlobalExceptionHandler no longer logs; HttpRequestFailed from the
  request middleware is the canonical (structured) request error event
- CMS request middleware no longer mutates the logging scope after
  BeginScope; StatusCode/DurationMs travel in event state only
- LOGGING.md examples now include EventId and a DMS example with
  RequestLayer; EventId JSON shape pinned in both contract tests
- effective-schema-hash structured-hash regex made explicitly
  case-insensitive for consistency (behavior unchanged; -match was
  already case-insensitive)

* [DMS-1228] Address round-4 merged review findings for request logging

- DMS frontend 500 error body now echoes the raw extracted correlation
  value instead of the log-sanitized form. Sanitizing the body was an API
  contract change unique to frontend-generated 500s: every core
  FailureResponse body already returns the raw traceId.Value. Log events
  still carry the sanitized TraceId; body and logs differ only when a
  client-supplied correlation id contains characters outside the logging
  whitelist, and applying the whitelist to a client-reported id yields
  the logged value. A new unit test pins the raw-body/sanitized-log
  divergence; the round-3 alignment test is renamed to match the settled
  contract.

- docs/LOGGING.md failure-contract clarifications: the Exception field
  carries the exception the logging layer itself observed, so a DMS
  frontend HttpRequestFailed for a downstream 5xx has Exception null and
  correlates to the core-layer event via TraceId + RequestLayer. CMS
  handled 400s (malformed bodies, validation failures) are documented as
  intentional HttpRequestCompleted events with no exception payload,
  replacing the previous Error-level exception-handler logs. Corrected
  the claim that CMS rethrows handled exceptions: only exceptions the
  exception handler does not observe propagate through the request
  logging middleware, which logs and rethrows them. The
  GlobalExceptionHandler comment is updated to match.

- Simplified eng/docker-compose/effective-schema-hash.psm1: removed the
  unreachable string/array recursion and PSObject property lookup. The
  only JSON path is ConvertFrom-Json -AsHashtable on lines starting with
  '{', which always yields dictionaries, so event handling is now inline
  in Get-EffectiveSchemaHashFromLogLine.

- LogSanitizer.ReplaceLineEndings is kept with an explanatory comment:
  it is behaviorally redundant with the whitelist (every character it
  strips is already rejected) but CodeQL models ReplaceLineEndings as a
  log-forging sanitizer and does not recognize the custom whitelist loop.

Verified: DMS frontend unit 254 passed, CMS frontend unit 464 passed,
effective-schema-hash Pester tests 9/9, csharpier formatted.

* [DMS-1228] Arm vacuous JSON contract assertions in DMS core logging tests

The two Serilog JSON contract tests in RequestResponseLoggingMiddlewareTests
used null-conditional chains into .Should()
(properties?["X"]?.GetValue<T>().Should().Be(...)), so a missing Properties
node or property short-circuited past the assertion and the test passed
vacuously, leaving the DMS core request-log collector contract unprotected.

All twelve chains in the two test methods are now parenthesized
((properties?["X"]?.GetValue<T>()).Should().Be(...)) so a missing property
flows into the assertion as null and fails. This applies the same fix commit
827cf225 made to the CMS and DMS frontend request-logging test files, which
had missed this DMS core file.

The remaining occurrences of the unparenthesized pattern in the repository
are in pre-existing test files outside this story's diff and are left as-is.

Verified: Given_RequestResponseLoggingMiddleware fixture 8/8 passed,
csharpier formatted.

* [DMS-1228] Correct Exception-field contract docs and stale core exception naming

Address two review findings on the request logging work:

- docs/LOGGING.md claimed request events carry "Exception": null when no
  exception exists, but Serilog.Formatting.Json.JsonFormatter omits the
  Exception field entirely when the log event has no exception attached.
  Since the document is declared the collector contract, rules written
  against a null-valued field would never match. Removed the
  "Exception": null line from both the CMS and DMS examples and rewrote
  the failure-log prose: the field is present only when the logging layer
  itself observed an exception, and collectors must treat it as optional.
  The omission behavior is pinned with ContainsKey("Exception") == false
  assertions in the CMS and DMS frontend JSON shape and appsettings
  binding tests and the DMS core JSON contract test, so a formatter
  change that starts emitting a null field fails a test instead of
  silently diverging from the documented contract.

- Renamed RequestInfo.UnhandledException (added on this branch) to
  CaughtException: the property holds the exception
  CoreExceptionLoggingMiddleware caught and converted into a 500
  response, and its own XML doc already called it a handled exception.
  Updated the XML doc to describe the capture-for-outer-logger intent.
  CoreExceptionLoggingMiddleware's class summary still said it logs
  requests and responses from before this branch removed its LogError;
  it now describes the actual behavior: convert escaping exceptions to
  error responses (403 for authorization failures, 500 otherwise) and
  capture the 500-path exception for the outer request logging
  middleware without logging it. The class name itself is pre-existing
  and left unchanged.

Verified: DMS core Given_RequestResponseLoggingMiddleware 8/8, DMS
frontend Given_LoggingMiddleware 18/18, CMS RequestLoggingMiddleware
tests 18/18, csharpier formatted.

---------

Co-authored-by: Adam Hopkins <adam.hopkins@simpat.tech>

dms-v0.7.1-alpha.0.268

[METAED-1664] Bump ApiSchema version (#1101)

* [METAED-1664] Bump ApiSchema version

* missed-one

dms-v0.7.1-alpha.0.267

dms-pre-0.7.1-alpha.0.267

[DMS-1227]    Return 413 for oversized DMS request bodies and add OWA…

cs-v0.7.1-alpha.0.269

[DMS-1228]      Make CMS request logging structured (#1063)

* [DMS-1228] Make CMS and DMS request logging structured

Squash-rebase of the DMS-1228 structured request logging work onto
origin/main (currently at DMS-1227's 413 handling).

The original pre-rebase history is preserved on the local branch
DMS-1228-pre-rebase-backup. Rebase conflicts were resolved against
DMS-1227's oversized-request-body 413 handling so the LoggingMiddleware
BadHttpRequestException path continues to short-circuit oversized
requests while participating in the structured logging pipeline.

Structured request logging spans both services:
- CMS RequestLoggingMiddleware emits structured start/completion events
  with a per-request BeginScope.
- DMS Core RequestResponseLoggingMiddleware and the DMS frontend
  LoggingMiddleware emit HttpRequestCompleted / HttpRequestFailed
  contract events with sanitized correlation values.
- Shared EventId definitions live in per-service RequestLoggingEventIds
  classes.
- docs/LOGGING.md documents the request-log contract; the docker-compose
  effective-schema-hash module and its Pester tests are included.

* [DMS-1228] Address request-logging review fixes

Correctness:
- Restore context.TraceIdentifier as the 500 error-response body traceId;
  the sanitized correlation value is used for log events only.
- Emit a Debug "Request started" breadcrumb in CMS request logging so a hung
  request still produces a log line.
- Emit the 413 oversized-body path as an HttpRequestCompleted contract event
  (StatusCode 413) instead of a bare Warning, so it is visible to the collector
  contract; the expected control-flow exception is not attached (S6667 is
  suppressed narrowly with justification).
- Allow empty log output in the effective-schema-hash module (@() -> null).
- Fix "after"/"in" DurationMs template drift in DMS core middleware.
- Restore request identity (method/path/traceId) in the rethrow message so
  host-level logging retains correlation after the scope is disposed.

Cleanup:
- Collapse the CMS duplicate LogError to one nullable-exception call, replace
  scope-dictionary readbacks with locals computed once, and simplify
  GetFailureStatusCode in both frontends.
- Remove the unused HasLongStructuredProperty helper, the duplicate DMS-app
  appsettings test and its now-unused helpers, and the dead legacy
  string-formatter branch; modernize the test TestLoggers.

Docs:
- Document the 413 completion path and the outputTemplate/JSON-formatter footgun
  in LOGGING.md and the DMS appsettings example.

Known tradeoff: the per-request logging scope is still constructed
unconditionally (not IsEnabled-gated) to preserve downstream scope enrichment.

* [DMS-1228] Align 500 response traceId with request logs and verify Serilog appsettings binding

Address review findings on the structured request logging work:

- DMS frontend LoggingMiddleware now writes the same sanitized trace id in
  the 500 error response body that the request log events carry as TraceId,
  so a client-reported trace id can be located in the logs when
  CorrelationIdHeader is configured. This also matches the rest of DMS,
  where core FailureResponse bodies already return the correlation-aware
  value. The unit test that previously codified the divergence now asserts
  the body and log values match.

- Add an end-to-end Serilog binding test to both CMS and DMS frontend unit
  suites. Each test loads the application's real appsettings.json, builds
  LoggerConfiguration().ReadFrom.Configuration(...).Enrich.FromLogContext()
  exactly as WebApplicationBuilderExtensions does, captures console output,
  and asserts the emitted line is newline-delimited JSON carrying the
  request-log contract properties, EventId, and RenderedMessage. The file
  sink path is redirected to the test work directory so creating the logger
  does not write into the repository. The existing JSON-shape tests remain
  as fast-failing complements.

- Fix a vacuous-pass hazard in the JSON assertions added on this branch:
  null-conditional chains like properties?["X"]?.GetValue<T>().Should()
  silently skip the assertion when the property is missing. All such chains
  in the two request-logging test files are now parenthesized so a missing
  property flows into the assertion as null and fails.

- Document the request logging EventId values (1228001/1228002) in
  docs/LOGGING.md as the source of truth shared by CMS and DMS, point both
  RequestLoggingEventIds class comments there, and pin the values with unit
  tests in both applications (DMS was missing its pin). EventId equality
  ignores Name, so the tests assert Id and Name separately.

- Document in docs/LOGGING.md that the file sinks intentionally share the
  JSON formatter: file logs carry the same structured properties but are
  not part of the console collector contract and omit RenderedMessage.

Cross-application sharing of the event-id type, test helpers, and log
construction was reviewed and deliberately not done: CMS and DMS build as
separate solutions with no shared assembly, so the documented contract plus
per-application pinned tests are the drift control.

* [DMS-1228] Address merged review findings for request logging

- DMS 413 completions log the status the client actually received when
  the response already started
- CMS GlobalExceptionHandler no longer logs; HttpRequestFailed from the
  request middleware is the canonical (structured) request error event
- CMS request middleware no longer mutates the logging scope after
  BeginScope; StatusCode/DurationMs travel in event state only
- LOGGING.md examples now include EventId and a DMS example with
  RequestLayer; EventId JSON shape pinned in both contract tests
- effective-schema-hash structured-hash regex made explicitly
  case-insensitive for consistency (behavior unchanged; -match was
  already case-insensitive)

* [DMS-1228] Address round-4 merged review findings for request logging

- DMS frontend 500 error body now echoes the raw extracted correlation
  value instead of the log-sanitized form. Sanitizing the body was an API
  contract change unique to frontend-generated 500s: every core
  FailureResponse body already returns the raw traceId.Value. Log events
  still carry the sanitized TraceId; body and logs differ only when a
  client-supplied correlation id contains characters outside the logging
  whitelist, and applying the whitelist to a client-reported id yields
  the logged value. A new unit test pins the raw-body/sanitized-log
  divergence; the round-3 alignment test is renamed to match the settled
  contract.

- docs/LOGGING.md failure-contract clarifications: the Exception field
  carries the exception the logging layer itself observed, so a DMS
  frontend HttpRequestFailed for a downstream 5xx has Exception null and
  correlates to the core-layer event via TraceId + RequestLayer. CMS
  handled 400s (malformed bodies, validation failures) are documented as
  intentional HttpRequestCompleted events with no exception payload,
  replacing the previous Error-level exception-handler logs. Corrected
  the claim that CMS rethrows handled exceptions: only exceptions the
  exception handler does not observe propagate through the request
  logging middleware, which logs and rethrows them. The
  GlobalExceptionHandler comment is updated to match.

- Simplified eng/docker-compose/effective-schema-hash.psm1: removed the
  unreachable string/array recursion and PSObject property lookup. The
  only JSON path is ConvertFrom-Json -AsHashtable on lines starting with
  '{', which always yields dictionaries, so event handling is now inline
  in Get-EffectiveSchemaHashFromLogLine.

- LogSanitizer.ReplaceLineEndings is kept with an explanatory comment:
  it is behaviorally redundant with the whitelist (every character it
  strips is already rejected) but CodeQL models ReplaceLineEndings as a
  log-forging sanitizer and does not recognize the custom whitelist loop.

Verified: DMS frontend unit 254 passed, CMS frontend unit 464 passed,
effective-schema-hash Pester tests 9/9, csharpier formatted.

* [DMS-1228] Arm vacuous JSON contract assertions in DMS core logging tests

The two Serilog JSON contract tests in RequestResponseLoggingMiddlewareTests
used null-conditional chains into .Should()
(properties?["X"]?.GetValue<T>().Should().Be(...)), so a missing Properties
node or property short-circuited past the assertion and the test passed
vacuously, leaving the DMS core request-log collector contract unprotected.

All twelve chains in the two test methods are now parenthesized
((properties?["X"]?.GetValue<T>()).Should().Be(...)) so a missing property
flows into the assertion as null and fails. This applies the same fix commit
827cf225 made to the CMS and DMS frontend request-logging test files, which
had missed this DMS core file.

The remaining occurrences of the unparenthesized pattern in the repository
are in pre-existing test files outside this story's diff and are left as-is.

Verified: Given_RequestResponseLoggingMiddleware fixture 8/8 passed,
csharpier formatted.

* [DMS-1228] Correct Exception-field contract docs and stale core exception naming

Address two review findings on the request logging work:

- docs/LOGGING.md claimed request events carry "Exception": null when no
  exception exists, but Serilog.Formatting.Json.JsonFormatter omits the
  Exception field entirely when the log event has no exception attached.
  Since the document is declared the collector contract, rules written
  against a null-valued field would never match. Removed the
  "Exception": null line from both the CMS and DMS examples and rewrote
  the failure-log prose: the field is present only when the logging layer
  itself observed an exception, and collectors must treat it as optional.
  The omission behavior is pinned with ContainsKey("Exception") == false
  assertions in the CMS and DMS frontend JSON shape and appsettings
  binding tests and the DMS core JSON contract test, so a formatter
  change that starts emitting a null field fails a test instead of
  silently diverging from the documented contract.

- Renamed RequestInfo.UnhandledException (added on this branch) to
  CaughtException: the property holds the exception
  CoreExceptionLoggingMiddleware caught and converted into a 500
  response, and its own XML doc already called it a handled exception.
  Updated the XML doc to describe the capture-for-outer-logger intent.
  CoreExceptionLoggingMiddleware's class summary still said it logs
  requests and responses from before this branch removed its LogError;
  it now describes the actual behavior: convert escaping exceptions to
  error responses (403 for authorization failures, 500 otherwise) and
  capture the 500-path exception for the outer request logging
  middleware without logging it. The class name itself is pre-existing
  and left unchanged.

Verified: DMS core Given_RequestResponseLoggingMiddleware 8/8, DMS
frontend Given_LoggingMiddleware 18/18, CMS RequestLoggingMiddleware
tests 18/18, csharpier formatted.

---------

Co-authored-by: Adam Hopkins <adam.hopkins@simpat.tech>

cs-v0.7.1-alpha.0.267

cs-pre-0.7.1-alpha.0.267

[DMS-1227]    Return 413 for oversized DMS request bodies and add OWA…

dms-v0.7.1-alpha.0.265

dms-pre-0.7.1-alpha.0.265

[DMS-1256] 401 authentication error contract diverges from design doc…

dms-v0.7.1-alpha.0.262

PostgreSQL 4x Performance Improvement (#1093)

* Add single-document hydration fast path

Add single-document read plan contracts, SQL dialect support, and validation for the hydration fast path.

Compile descriptor and document reference projections for single-document hydration, enable the write hydration fast path, and add regression and integration coverage.

* dms-side-plan

* tuning

* step-3

* cache

* tuneup

* tuneup

* tuneup

* tuning

* tuning

* more-tweaks

* more-tweaks

* fixes

* remove

* update

* update

* final

* small-tweak

* cleaner-but-slightly-slower-cache

* fix1

* fix2

* fix3

* fix4

* fix6

* fix7

* fix3-2

* ok

* ok

* hybrid-check

* JWT-improvements

* phase2

* phase3

* ok

* utf-tweak

* cache-limit

* cache-change

* remove-temp-file

dms-v0.7.1-alpha.0.261

dms-pre-0.7.1-alpha.0.261

[DMS-1238] Add local Docker deployment support for the MSSQL backend …

cs-v0.7.1-alpha.0.266

cs-pre-0.7.1-alpha.0.266

[DMS-1260] Scope CMS Application and ApiClient repositories by tenant…

cs-v0.7.1-alpha.0.260

[DMS-1243] Implement CMS SQL Server backend support (#1084)

* [DMS-1243] Add SQL Server deployer and full dmscs T-SQL schema for CMS

- Real DbUp SqlServer DatabaseDeploy (journal dbo.dmscs_SchemaVersions) replacing the NotImplementedException stub
- T-SQL port of all 27 dmscs scripts with identical numeric prefixes; constraint/index/FK names created lowercase to match the exception-guard literals used by the repositories
- Deviations: tenant FKs are ON DELETE NO ACTION (SQL Server rejects the converging cascade paths; tenant delete is not exposed); 0020 is a placeholder (no crypto extension needed); OpenIddict scripts land now so the schema deploys whole
- New EdFi.DmsConfigurationService.Backend.Mssql.Tests.Integration project (Category=MssqlIntegration, skips locally without ConnectionStrings__MssqlAdmin, fails in CI without it) with deploy/seed/idempotency tests

* [DMS-1243] Move datastore-specific CMS registrations into per-engine DI branches

- PostgreSQL repositories now register only under Datastore=postgresql; the mssql branch registers AddMssqlDatastore + the SQL Server deployer (repositories follow in subsequent commits)
- ITokenManager and IClientSecretHasher are datastore-agnostic; registered for both engines (previously missing on the mssql path)
- Delete MssqlUnsupportedResourceClaimRepository and its unit test: resource-claim endpoints are in scope for SQL Server and get real implementations
- Remove stale identity-provider comment in AppSettings

* [DMS-1243] Add core CMS SQL Server repositories with 1:1 integration coverage

- Vendor, Application, ApiClient, Tenant, Profile, DataStore, DataStoreContext, DataStoreDerivative repositories mirroring the PostgreSQL implementations (Dapper, per-operation SqlConnection, same transaction boundaries and result types); Dapper calls inside transaction scopes pass the transaction explicitly (SqlClient requires it; Npgsql auto-enlists)
- ApiClient foreign-key guard matches the actual fk_apiclient_application constraint so invalid application ids map to FailureApplicationNotFound, with failure-path tests
- Additive PagingQuery.BuildSqlServerPagingClause(): OFFSET/FETCH, emits OFFSET 0 ROWS when unpaged so ORDER BY stays valid in derived tables without a row cap; PostgreSQL BuildPagingClause untouched
- MssqlExceptionExtensions maps SqlException 2627/2601/547 with the same constraint-name literals the PostgreSQL guards use
- Integration tests mirror the PostgreSQL suite file-for-file for these repositories (one OpenIddict-dependent fixture arrives with the OpenIddict commit); dmscs.tenant added to the Respawner reset list for suite re-runnability under SQL Server collation

* [DMS-1243] Add CMS SQL Server claims repositories with 1:1 integration coverage

- ClaimsHierarchy, ClaimsDocument, ClaimSet, ResourceClaim repositories plus ClaimsTableValidator, ResourceClaimMetadataRepository, and ClaimSetDataProvider for SQL Server
- ClaimSet application lists use correlated FOR JSON PATH (jsonb_agg equivalent); import upsert uses an UPDLOCK/HOLDLOCK-guarded insert so concurrent imports converge on one row like ON CONFLICT DO NOTHING
- Resource-claim seeding passes a JSON parameter through OPENJSON in place of UNNEST arrays
- Optimistic-concurrency timestamps bind as DbType.DateTime2: Dapper infers plain DATETIME (~3.33ms) for untyped DateTime parameters, which spuriously fails equality against DATETIME2 columns
- Integration tests mirror the PostgreSQL suite file-for-file, including the import concurrency test

* [DMS-1243] Add self-contained (OpenIddict) identity support on SQL Server

- MSSQL OpenIddictDataRepository/OpenIddictTokenRepository and AddMssqlOpenIddictStores mirroring the PostgreSQL OpenIddict wiring; the self-contained identity branch now selects stores by AppSettings:Datastore
- JSON array columns (Permissions, Requirements, redirect URIs, Scopes, DataStoreIds) are serialized/deserialized explicitly in repository code
- Signing-key at-rest crypto uses ENCRYPTBYPASSPHRASE/DECRYPTBYPASSPHRASE with IdentitySettings:EncryptionKey; passphrase NVARCHAR + cleartext VARCHAR convention enforced on both write (setup scripts) and read (repository) paths, verified by a round-trip integration test
- setup-openiddict.ps1 gains a working MSSQL branch (docker exec sqlcmd) with insert-if-not-exists/JSON_MODIFY equivalents of the PostgreSQL statements
- Fully qualify the PostgreSQL ClaimSetDataProvider registration (CS0104 once both engines' root namespaces are imported); restore the OpenIddict-dependent ApplicationTests fixture deferred from the core-repository commit

* [DMS-1243] Wire SQL Server through CMS runtime, Docker, E2E, and CI

- run.sh skips the pg_isready gate for non-PostgreSQL datastores; new mssql.yml compose service (SQL Server 2022) and .env.config.mssql.e2e; start-local-config.ps1 selects the database compose file from DMS_CONFIG_DATASTORE and threads -DbType/-DbUser into setup-openiddict.ps1
- Dockerfile ships the publish runtimes/ directory and ICU (non-invariant globalization): Microsoft.Data.SqlClient needs its unix runtime assembly and full globalization on the Alpine base
- setup-openiddict.ps1 MSSQL branch invokes sqlcmd directly (SQL as a single argument, -b -I, exit-code guard) instead of Invoke-Expression, which broke on SQL containing double quotes
- E2E SetupHooks cleans up through SqlConnection when DMS_CONFIG_DATASTORE=mssql; representative scenarios across the CMS surfaces tagged @MssqlRepresentative
- build-config.ps1 E2ETest accepts -EnvironmentFile and -E2ETestFilter and exports DMS_CONFIG_DATASTORE for the test process
- CI: MSSQL service container + ConnectionStrings__MssqlAdmin on the integration job; new representative SQL Server E2E job across both identity providers

* [DMS-1243] Resolve PSScriptAnalyzer alerts on OpenIddict setup scripts

Declare [OutputType([string])] on the three SQL/hash-generating functions
in OpenIddict-Crypto.psm1. Suppress with justification in
setup-openiddict.ps1: Write-Host (operator-facing bootstrap script;
Write-Output would pollute Invoke-DbQuery results parsed by
Get-ScalarResult), ReviewUnusedParameter (false positive -- params are
consumed in nested helper functions), and PlainTextForPassword on
DbPassword (ENV: sentinel handed to sqlcmd, which requires plaintext).
Matches existing suppression precedent in configure-local-data-store.ps1,
provision-dms-schema.ps1, and Template-Management.psm1.

* DMS-1243 Address CMS MSSQL review findings

* [DMS-1243] Suppress PSScriptAnalyzer naming-heuristic alerts on OpenIddict scripts

Alert 1496 (PSUseShouldProcessForStateChangingFunctions on
New-ClientSecretUpdateSql) and 1497 (PSUseSingularNouns on
Invoke-InitDbScripts) are pre-existing main-branch findings that GitHub
re-raised as new because this branch shifted their line numbers.

Suppress the ShouldProcess rule on all five New-* functions in
OpenIddict-Crypto.psm1: each is a pure generator returning a
hash/key-pair/SQL string without executing anything, so ShouldProcess
support would advertise -WhatIf semantics that cannot exist. Suppress
the plural-noun rule on Invoke-InitDbScripts (runs multiple init
scripts; required adding an empty param() block to host the attribute).
Matches suppression precedent in env-utility.psm1,
prepare-dms-schema.ps1, and provision-dms-schema.ps1.

* [DMS-1243] Skip multitenant-only CMS E2E scenarios on single-tenant stacks

The regular CMS E2E job runs the suite unfiltered against a single-tenant
stack, where TenantModule is not mapped and POST /v3/tenants returns 404.
The Tenants scenario added for the MSSQL multitenant job therefore failed
there deterministically.

Follow the existing @SelfContainedOnly pattern: tag the scenario
@MultitenantOnly and add a BeforeScenario hook that ignores it unless
DMS_CONFIG_MULTI_TENANCY=true. build-config.ps1 now propagates
DMS_CONFIG_MULTI_TENANCY from the environment file into the test process
(unconditionally, so env files without the key clear stale values) so the
multitenant job still runs the scenario while single-tenant runs skip it.

* [DMS-1243] Address review findings: tenant-scope derivatives, DB readiness, env-driven E2E cleanup

Tenant scoping for DataStoreDerivativeRepository (both engines):
- The derivative repositories never applied the current tenant, so requests
  that knew another tenant's datastore or derivative id could read, update,
  or delete across tenant boundaries, including derivative connection
  strings. The PostgreSQL repository has the identical pre-existing gap
  (unchanged since DMS-1198); the MSSQL port mirrored it 1:1, so both are
  fixed to preserve engine parity.
- Every operation now filters through dmscs.DataStore with the tenant
  clause, modeled on DataStoreContextRepository: queries/gets/deletes only
  see the tenant's rows, and insert/update refuse datastores outside the
  current tenant (FailureForeignKeyViolation). Update distinguishes
  not-found from cross-tenant datastore via a DerivativeExistsForTenant
  probe, mirroring the context repository.
- New Given_derivative_operations_from_another_tenant fixtures (9 tests per
  engine) cover cross-tenant get/query/update/move/delete/list/insert and
  single-tenant-context visibility. Full suites green: MSSQL 256/256,
  PG 244/244.

Database readiness at CMS startup:
- postgresql.yml db service gains a pg_isready healthcheck (mssql.yml
  already had one), and the local-config.yml config service now declares
  depends_on: db: condition: service_healthy. docker compose up therefore
  blocks until the database accepts logins before starting the config
  service (which deploys schema on startup) and before
  start-local-config.ps1 runs the OpenIddict/Keycloak setup scripts; the
  remaining Start-Sleep only covers Keycloak/service warmup.
- src/config/run.sh previously skipped readiness entirely for MSSQL; it now
  waits for the SQL Server TCP endpoint parsed from the connection string
  (bash /dev/tcp probe, since the Alpine runtime image has no sqlcmd).
- Verified by cold-starting both stacks from empty volumes: setup scripts
  succeed against a cold SQL Server and the service reaches healthy.

Env-driven E2E cleanup connection:
- SetupHooks cleanup previously hard-coded host ports, credentials, and the
  database name, so a custom -EnvironmentFile could silently point cleanup
  at the wrong database. It now reads POSTGRES_PASSWORD/PORT/DB_NAME and
  MSSQL_SA_PASSWORD/PORT from the environment, which build-config.ps1
  propagates from the active environment file; fallbacks match the
  checked-in .env.config*.e2e files so a bare dotnet test against a
  standard stack still cleans the right database. Applied symmetrically to
  the PostgreSQL half, and cleanup failures now log a warning instead of
  being swallowed silently.

* [DMS-1243] Adapt MSSQL backend to DMS-1244 OpenIddict and tenant-helper changes

DMS-1244 (database identifier standardization) changed two backend contracts
that this branch's SQL Server mirrors implement:

- IOpenIddictTokenRepository/IOpenIddictDataRepository.StoreTokenAsync lost
  its payload parameter (the raw JWT is no longer handed to storage). Drop
  the parameter from the MSSQL implementations; they never wrote it to
  dmscs.OpenIddictToken, so behavior is unchanged.

- The shared Backend/Services/TenantContextExtensions helper was deleted in
  favor of per-engine variants (PostgresqlTenantContextExtensions emits
  quoted "TenantId"). Add MssqlTenantContextExtensions with the original
  unquoted T-SQL output so the MSSQL repositories keep compiling.

Verified post-rebase: CMS build clean, backend units 331/331, frontend units
443/443, MSSQL integration 256/256, PostgreSQL integration 252/252.

* [DMS-1243] Align MSSQL schema and repositories with DMS-1244 naming conventions

DMS-1244 standardized database identifier naming across the CMS PostgreSQL
schema (PascalCase PK_/UX_/FK_/IX_ constraint and index names). Apply the
same conventions to the SQL Server port so both engines expose identically
named objects:

- All dmscs T-SQL deploy scripts: name every primary key (PK_<Table>;
  previously mostly inline unnamed), rename unique constraints to
  UX_<Table>_<Columns>, foreign keys to FK_<Child>_<Parent>, and non-unique
  lookup indexes to IX_<Table>_<Column>. Idempotency guards updated to the
  new names.
- Where PostgreSQL now enforces uniqueness via a constraint instead of a
  redundant unique index, mirror it: idx_vendor_applicationname and
  idx_claimsetname become UX_ constraints; the redundant ix_profile_name
  index is removed (UX_Profile_ProfileName already covers it).
- MSSQL repositories: constraint-name literals in unique/FK violation
  handling updated to the new names, kept identical to the PostgreSQL twins
  (e.g. uq_company -> UX_Vendor_Company, fk_datastore ->
  FK_ApiClientDataStore_DataStore). MssqlExceptionExtensions doc comment
  updated to match.
- setup-openiddict.ps1: name the bootstrap dmscs.OpenIddictKey primary key
  PK_OpenIddictKey in the MSSQL branch, matching the PostgreSQL branch.

No table/column names, data types, or ON DELETE behavior changed. Tenant
FKs keep ON DELETE NO ACTION (SQL Server converging-cascade-path
restriction). The idempotent guards do not rename objects in existing
databases - fresh deploys pick up the new names (CI always deploys fresh;
long-lived local stacks need a teardown).

Verified: CMS build clean; MSSQL integration 256/256 against a freshly
deployed database (the DbUp journal blocks re-running renamed scripts, so
the old test database must be dropped first).

* [DMS-1243] Suppress PSUseSingularNouns on Add-MssqlOpenIddictKeyParameters

main's new staged PowerShell analyzer (eng/Invoke-StagedPowerShellAnalysis.ps1,
added by #1086 and wired into the husky pre-commit task runner) raises
PSUseSingularNouns on the MSSQL helper Add-MssqlOpenIddictKeyParameters. The
plural noun is intentional: the function adds the full set of OpenIddict key
parameters to a SqlCommand. Suppress with justification, matching the existing
PSUseSingularNouns suppressions on Invoke-InitDbScripts and
Update-OpenIddictApplicationPermissions in this same file.

* [DMS-1243] Gate OpenIddictCrypto.Tests.ps1 in the config PR workflow

Add a "Run OpenIddict Crypto Tests" step to the required
run-parity-pester-tests job in on-config-pullrequest.yml so the
OpenIddict SQL Server signing-key security regression test runs on
every config pull request.

Previously this test was gated by no workflow: the config gate ran
only LockFileParity.Tests.ps1 and the DMS bootstrap Pester list omits
it, so the signing-key fix (encryption key must not leak into the
generated OpenIddict key-insert SQL) could regress while CI stayed
green.

The job name is left unchanged to preserve the required-status-check
reference. The config workflow already triggers on eng/**, so the gate
fires on changes to the OpenIddict crypto module it protects.

* [DMS-1243] Address review findings: dedupe OpenIddict init, drop dead registrations, align null-check style

- start-local-config.ps1: remove the duplicated back-to-back
  `setup-openiddict.ps1 -InitDb` invocation (pre-existing on main since
  a8350cf8) that inserted a second active signing key on every
  self-contained start. The paired -InitDb calls in start-local-dms.ps1
  and start-published-dms.ps1 are mutually exclusive InfraOnly branches,
  not duplicates, and are left unchanged.

- OpenIddict-Crypto.psm1: remove rebase auto-merge artifacts that doubled
  [OutputType]/[SuppressMessage(PSUseShouldProcessForStateChangingFunctions)]
  attributes on New-AspNetPasswordHash, New-OpenIddictKeyPair,
  New-OpenIddictKeyInsertSql, and New-ClientSecretUpdateSql; keep the
  copies that exist on main (#1086).

- MssqlOpenIddictServiceExtensions: delete the unused 4-arg
  AddMssqlOpenIddictStores overload (it never registered
  IOpenIddictDataRepository, so it would fail DI resolution if ever
  called) and the unused IDbConnection registration (reads
  ConnectionStrings:DatabaseConnection, which CMS does not populate, and
  is resolved nowhere). The PostgreSQL twin file carries identical dead
  code; it is left untouched here and tracked as a combined PG+MSSQL
  cleanup follow-up to avoid unrelated PG churn in this PR.

- Backend.Mssql repositories: convert the 32 `== null` / `!= null`
  comparisons across 11 repository files to `is null` / `is not null`,
  matching the PostgreSQL twins (which contain none of the former).

Verified: config solution builds with 0 warnings, csharpier and
PSScriptAnalyzer clean, unit tests 331+443 green, OpenIddictCrypto
Pester tests 6/6, MSSQL integration 256/256, PostgreSQL integration
252/252.

* [DMS-1243] Address review round 3: portable MSSQL readiness probe, PascalCase vendor SQL

src/config/run.sh (MSSQL branch, new in this PR):
- Broaden connection-string parsing to all SqlClient server keyword
  aliases (Server, Data Source, Address, Addr, Network Address), strip an
  optional tcp: protocol prefix, tolerate whitespace, and anchor keys on
  ';' boundaries.
- When no host can be parsed, log and skip the readiness wait instead of
  looping forever, letting .NET surface a real connection error.
- Probe with `nc -z -w 2` instead of bash's /dev/tcp: the published image
  (Nuget.Dockerfile) runs run.sh under BusyBox ash, where /dev/tcp never
  succeeds, so the published image with AppSettings__Datastore=mssql
  would have hung at startup. nc is present in the pinned
  aspnet:10.0.3-alpine3.23 base used by both images. Verified by
  executing the script in that exact image under ash: five
  connection-string forms, the delayed-listener retry loop, and the
  unparseable-skip path.

Backend.Mssql VendorRepository:
- PascalCase the two remaining lowercase SQL queries (UpdateVendor's
  ApiClient UUID lookup and GetVendorApplications' Vendor FROM clause) to
  mirror the PostgreSQL twin; lowercase identifiers fail on
  case-sensitive SQL Server collations. Repo-wide sweep found no other
  lowercase SQL sites. Vendor MSSQL integration tests 16/16.

Findings pushed back as pre-existing cross-backend design or factually
rebutted (see PR discussion): HashingIterations binding gap (shared
Backend.OpenIddict, untouched by this branch), multitenant
auth-strategy visibility, orphan identity client (the repository maps
FK_ApiClientDataStore_DataStore to FailureDataStoreNotFound and the
module compensates with DeleteClientAsync), tenant-scoped Company lookup
vs global unique constraint, OpenIddict DI registration overlap,
migration numbering parity with PostgreSQL, and per-InitDb JWKS key
inserts.