Skip to content

fix: Compare idempotency CreatedAt in UTC instead of as offset-dependent strings in NetEvolve.Pulse.SQLite #860

Description

@samtrion

User Story

As a developer who uses IIdempotencyKeyRepository from NetEvolve.Pulse.SQLite directly, I want the time-to-live check to compare points in time, so that a key is treated as expired or valid correctly no matter which UTC offset my timestamps use.


Problem

SQLiteIdempotencyKeyRepository saves CreatedAt and binds validFrom as ISO 8601 strings, keeping whatever offset the caller passed. It then compares the two as TEXT:

  • src/NetEvolve.Pulse.SQLite/Idempotency/SQLiteIdempotencyKeyRepository.cs:80: AND "CreatedAt" >= @validFrom (string comparison)
  • src/NetEvolve.Pulse.SQLite/Idempotency/SQLiteIdempotencyKeyRepository.cs:111: AddWithValue("@validFrom", validFrom.Value.ToString("O"))
  • src/NetEvolve.Pulse.SQLite/Idempotency/SQLiteIdempotencyKeyRepository.cs:136: AddWithValue("@createdAt", createdAt.ToString("O"))

The "O" format keeps the offset, for example 2026-09-27T10:00:00.0000000+02:00. A string comparison only matches the order in time when both values have the same offset.

Failure scenario:

  1. StoreAsync("k", 2026-09-27T10:00:00+02:00) stores the key. That is 08:00Z.
  2. ExistsAsync("k", validFrom: 2026-09-27T09:00:00+00:00) runs.
  3. As text, "...T10:00...+02:00" >= "...T09:00...+00:00" is true, so the method returns true. The key was created before the cutoff, so it should return false.

The same thing happens the other way round: when the offsets are reversed, a valid key is reported as expired.

The other providers compare instants. NetEvolve.Pulse.MySql compares UtcTicks (MySqlIdempotencyKeyRepository.cs:111,136). SqlServer and PostgreSQL bind typed DateTimeOffset parameters. Redis and EF Core compare DateTimeOffset values. The SQLite outbox in the same package already converts to UTC with ToUniversalTime() (src/NetEvolve.Pulse.SQLite/Outbox/SQLiteOutboxRepository.cs:343, :520, :734).

Impact is low and does not show up in normal use. The built-in IdempotencyStore passes TimeProvider.GetUtcNow() for both values (src/NetEvolve.Pulse/Idempotency/IdempotencyStore.cs:52, :63), so they are always +00:00. Only code that resolves the public IIdempotencyKeyRepository and passes timestamps with a non-zero offset is affected, or a custom TimeProvider that breaks the GetUtcNow contract.


Specification

Project contract, IIdempotencyKeyRepository.ExistsAsync (src/NetEvolve.Pulse.Extensibility/Idempotency/IIdempotencyKeyRepository.cs:28-33):

When set, only keys created at or after this timestamp are considered as existing. Keys older than this cutoff are treated as absent.

validFrom is a DateTimeOffset, so "at or after" means comparing points in time. DateTimeOffset comparison uses UtcDateTime (DateTimeOffset.CompareTo): "compares two DateTimeOffset objects based on their UTC date and time values". The round-trip format "O" keeps the original offset (Standard date and time format strings: the round-trip ("O", "o") format specifier), so the strings only sort in time order when every value has the same offset.


Requirements

  • Convert both values to UTC before formatting: createdAt.ToUniversalTime().ToString("O", CultureInfo.InvariantCulture) (line 136) and validFrom.Value.ToUniversalTime().ToString("O", CultureInfo.InvariantCulture) (line 111). "O" is already culture-invariant, so CultureInfo.InvariantCulture is only there for the analyzers.
  • Keep the column type and schema. No data migration is needed, because every row the built-in IdempotencyStore writes already has +00:00.

Acceptance Criteria

  • A failing test is added first. It stores a key with a non-UTC offset (for example 10:00+02:00) and checks that ExistsAsync with validFrom = 09:00+00:00 returns false.
  • A test covers the reverse case: a key created after the cutoff but stored with a smaller offset is reported as existing.
  • ExistsAsync and StoreAsync in SQLiteIdempotencyKeyRepository convert both timestamps to UTC before comparing them.
  • The existing SQLite idempotency unit and integration tests still pass.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    type:bugIndicates an issue or flaw that needs to be fixed.

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions