MSSQL outbox generated round-trip tests fail with DateTimeOffset offset mismatch (timezone-sensitive)

[iancooper]

Summary

Twelve MSSQL outbox generated round-trip tests fail with a one-hour offset mismatch on Header.TimeStamp (DateTimeOffset). The failures look like a DateTime.Kind ambiguity in the MSSQL datetimeoffset mapping — not a timezone clock skew — because the offset-direction of the mismatch varies across the 12 cases (sometimes expected +00:00, actual +01:00, sometimes the reverse).

Repro

Local (BST, +01:00) environment, host 2026-05-31. MSSQL container at localhost:11433. Run either TFM separately (parallel-TFM deadlock convention):

dotnet test tests/Paramore.Brighter.MSSQL.Tests/Paramore.Brighter.MSSQL.Tests.csproj -f net9.0
# same 12 fail on -f net10.0 too

Result: Failed: 12, Passed: 185, Total: 197.

Failing tests (all 12, all in tests/Paramore.Brighter.MSSQL.Tests/Outbox/{Binary,Text}/Generated/{Sync,Async}/)

• WhenAddingAMessageItShouldBeStoredWithAllProperties / Async (Binary + Text)
• WhenAddingAMessageWithinTransactionItShouldBeStoredAfterCommit / Async (Binary + Text)
• WhenRetrievingAMessageByIdItShouldReturnTheCorrectMessage / Async (Binary + Text)

The failing assertion in every case is line ~79:

Assert.Equal(message.Header.TimeStamp, dispatched.Header.TimeStamp, TimeSpan.FromSeconds(1));

Sample failures (varying offset direction)

Expected: 2026-05-31T13:36:57.0755270+00:00
Actual:   2026-05-31T13:36:57.0770000+01:00 (difference 00:59:59.9985270 > 00:00:01)

Expected: 2026-05-31T13:37:10.2600000+01:00
Actual:   2026-05-31T13:37:10.2591510+00:00 (difference 00:59:59.9991510 > 00:00:01)

Note the wall-clock parts match within ~10ms (well under the 1-second tolerance) — the discrepancy is purely the offset. Assert.Equal(DateTimeOffset, DateTimeOffset, TimeSpan) compares instants, so a one-hour offset mismatch crosses the threshold.

Why this is almost certainly a real bug, not "BST environment"

• PostgreSQL outbox tests (Paramore.Brighter.PostgresSQL.Tests) pass 190/190 in the same BST environment → the generated test template itself is fine.
• The mismatch direction is inconsistent across the 12 failures. If it were "we're in BST and MSSQL only stores UTC", the direction would always be the same. The bidirectional pattern indicates a DateTime.Kind-sensitive code path in either the writer or the reader (likely new DateTimeOffset(DateTime) on a value whose Kind is Unspecified, which silently substitutes the local offset).

Suspected surfaces (worth tracing)

• src/Paramore.Brighter.Outbox.MsSql/MsSqlOutbox.cs (or async sibling) — the DataReader.GetDateTime(...) → DateTimeOffset projection for the Timestamp column.
• The generated test factory builds dispatched in-memory with a DateTimeOffset whose Kind may differ from what outbox.Add round-trips through MSSQL's datetimeoffset(7) column.
• The 12 affected files were last touched by:
  • b61a786c1 — Migrate outbox test to generated (Migrate outbox test to generated #3943)
  • b42887af4 — Universal scheduler delay support for all transports (feat: Universal scheduler delay support for all transports #3995)

Reproducibility note

These failures may be timezone-dependent (BST-only reproducer). A CI run in UTC may pass even with the underlying bug present. Recommend a CI matrix with TZ=Europe/London to surface this for MSSQL.

Out-of-scope context

This was uncovered by the VERIFY phase of spec 0029 (multi-tenancy migration-history scope) on branch multi-tenancy (PR #4155). That spec touches only src/Paramore.Brighter.BoxProvisioning*/ — zero outbox code — so the failures are pre-existing and orthogonal. The MSSQL BoxProvisioning subset (--filter "FullyQualifiedName~BoxProvisioning") is 84/84 on the same branch.

[iancooper]

@lillo42 One for you on the generated tests