Subscription gap metrics: end-of-stream measure for relational stream subscriptions is not scoped to the stream

[alexeyzimarev]

Describe the bug

For relational (SQL Server / PostgreSQL / SQLite) subscriptions of kind SubscriptionKind.Stream, the end-of-stream measure used for the subscription gap metric is not scoped to the subscribed stream. It returns MAX(stream_position) across all streams in the messages table.

The SQL is built as a constant string in each provider's subscription base, with no WHERE clause on the stream:

• src/SqlServer/src/Eventuous.SqlServer/Subscriptions/SqlServerSubscriptionBase.cs:34

  GetEndOfStream = $"SELECT MAX(StreamPosition) FROM {options.Schema}.Messages";

• src/Postgres/src/Eventuous.Postgresql/Subscriptions/PostgresSubscriptionBase.cs:48

  protected override string GetEndOfStream { get; } = $"select max(stream_position) from {options.Schema}.messages";

• src/Sqlite/src/Eventuous.Sqlite/Subscriptions/SqliteSubscriptionBase.cs:34

  GetEndOfStream = $"SELECT MAX(stream_position) FROM {Schema.MessagesTable}";

A stream subscription's checkpoint is a stream position within its own stream (SqlSubscriptionBase.MoveStart returns evt.StreamPosition for SubscriptionKind.Stream), but the measure returns the highest stream position found in any stream in the table. SubscriptionMetrics.ObserveGapValues then computes endOfStream.Position - checkpoint, comparing two unrelated numbers.

Impact

• The eventuous.subscription.gap.count gauge for stream subscriptions is wrong whenever any other stream in the store is longer than the subscribed one — which is the normal case. The reported gap is inflated by (longest stream length − subscribed stream length) and never returns to zero, even when the subscription is fully caught up.
• Unlike Subscription gap metrics: GetSubscriptionEndOfStream issues #548 this fails silently: no exception, no warning, just a plausible-looking wrong number. Gap-based alerting on stream subscriptions is unreliable.
• SqlSubscriptionBase.Connect also uses this measure to seed the checkpoint when Options.StartFrom == InitialPosition.Latest, so a stream subscription starting from Latest can be positioned at an arbitrary position belonging to a different stream.

Expected behaviour

For SubscriptionKind.Stream, the measure should return the last stream position of the subscribed stream, so that endOfStream.Position - checkpoint is the number of events the subscription is behind within that stream.

SubscriptionKind.All is unaffected — MAX(global_position) is correctly global.

Suggested fix

GetEndOfStream / GetEndOfAll are protected abstract string properties holding constant SQL, so there is currently no way to pass a stream parameter. The contract needs to change — e.g. replace the string properties with a protected virtual DbCommand PrepareEndOfStreamCommand(TConnection connection) hook (mirroring the existing PrepareCommand), letting each provider bind the stream.

The stream identity is already resolved per provider:

• SQL Server: SqlServerStreamSubscription resolves _streamId in BeforeSubscribe and passes @stream_id to read_stream_sub; the same value can filter MAX(StreamPosition).
• PostgreSQL / SQLite: equivalent stream lookup in their stream subscription classes.

Note the ordering caveat: BeforeSubscribe runs before the measure in Connect, but the measure is also registered in DI as a GetSubscriptionEndOfStream delegate and can be invoked by the metrics observer before the subscription has ever connected, when the resolved stream id is still unset. That path needs to be handled (return EndOfStream.Invalid rather than a bogus zero).

Tests should cover all three providers: append to two streams of different lengths, subscribe to the shorter one, catch up, and assert the measured gap is zero.

Related

Split out of #548, which covered a different defect in the same method (InvalidCastException from GetInt64 plus swapped switch arms) and was fixed in #551. The stream-scoping problem was not addressed there and is still present on dev.

[linear]

EVE-247