Bug: store_events_v03.sql uses TEXT MIN() on sequence_number, causing false optimistic locking failures

[victorhazbun]

Bug: store_events_v03.sql uses TEXT MIN() on sequence_number, causing false optimistic locking failures

Summary

The store_events procedure in db/migrate/sequent/store_events_v03.sql (and db/structure.sql) uses MIN() on a JSON text extraction (->>) for sequence_number. Since ->> returns text, MIN() performs lexicographic comparison instead of numeric, returning the wrong minimum when sequence numbers have different digit counts (e.g., crossing from single to double digits).

The Bug

File: db/migrate/sequent/store_events_v03.sql, line 34

SELECT MIN(event->'event_json'->>'sequence_number')
  INTO _next_sequence_number
  FROM jsonb_array_elements(_events) AS event;

The ->> operator returns text. PostgreSQL's MIN() on text uses lexicographic ordering:

SELECT MIN(val) FROM (VALUES ('8'), ('9'), ('10')) AS t(val);
-- Returns: '10'  (because '1' < '8' lexicographically)
-- Expected: '8'  (numerically)

Although _next_sequence_number is declared as events.sequence_number%TYPE (integer), the implicit cast happens after MIN() has already selected the wrong value.

Reproduction

Any aggregate that accumulates 10+ events in a single store_events call will hit this. For example, with events at sequence numbers 8, 9, 10 in one batch:

1. MIN('8', '9', '10') returns '10' (text comparison, '1' < '8')
2. _next_sequence_number = 10
3. If _last_sequence_number = 7, the check _next_sequence_number > _last_sequence_number + 1 evaluates 10 > 8 → true
4. Raises: sequence_number must be consecutive, but last sequence number was 7 and next is 10

This is a false positive — the events are actually consecutive (8, 9, 10), but the text MIN() skipped over 8 and 9.

Impact

• Aggregates with workflows/event chains that produce 10+ events in a single command fail with spurious integrity_constraint_violation errors
• The bug is latent for aggregates with fewer than 10 events per command, which is why it may not have been caught in Sequent's own test suite
• The same buggy SQL is in both db/migrate/sequent/store_events_v03.sql and db/structure.sql

Proposed Fix

Cast to integer before applying MIN():

SELECT MIN((event->'event_json'->>'sequence_number')::integer)
  INTO _next_sequence_number
  FROM jsonb_array_elements(_events) AS event;

The rest of the procedure already handles integer casting correctly — note the INSERT statement uses (event->'event_json'->'sequence_number')::integer (with -> not ->>), which works because -> returns jsonb and ::integer on a jsonb number works fine. But the MIN() line uses ->> which returns text, and MIN on text gives the wrong answer.

Environment

• Sequent version: 8.2.0 (commit 1395495bb59d)
• PostgreSQL 15+
• Ruby 3.3

[erikrozendaal]

Thanks for the report @victorhazbun, the fix has been applied to the master branch. The latest release (sequent 8.2.0) was not affected since it didn't include this check yet.