The End Time column on the executions and task search pages is marked
sortable, and the date-range picker emits `endTime>`/`endTime<` clauses.
`end_time` was never a column on workflow_index or task_index, so the
query builders snake_cased the field, failed to match it against
VALID_FIELDS, and dropped it -- from the ORDER BY via getSort() and from
the WHERE via Condition.isValid(). Sorting by End Time returned an
unordered page and lost the default `startTime:DESC`; filtering by an
End Time range silently returned unfiltered results.
Add end_time to both index tables on sqlite and postgres, index it,
populate it from the summary on write, and add it to the allow-list.
The millis-to-UTC conversion applied to filter values keys off the
`_time` suffix, so the date range needs no further change.
The column is NOT NULL defaulting to the epoch rather than nullable.
End time is absent until an execution is terminal, and SQLite sorts
NULL lowest while Postgres sorts it highest, so a nullable column would
put still-running executions at opposite ends of an endTime:DESC page
depending on the backend. getSort() emits a bare
`attribute + " " + order` with no room for a NULLS clause, so the
sentinel is what keeps the two engines agreeing: unfinished rows carry
the epoch and sort last on DESC, first on ASC, on both. This follows the
update_time sentinel already in V1 and V13.1.
end_time is added to the ON CONFLICT update clause as well as the
insert, since the row already exists by the time a workflow finishes.
Existing rows are back-filled from json_data, which already holds the
instant as an ISO-8601 string. Terminal executions are never re-indexed,
so without a back-fill every historical execution would sort as
unfinished forever.
Both back-fills are written to survive a malformed value rather than
abort. Sqlite's strftime() returns NULL on one and COALESCE keeps the
epoch. Postgres needs more care than V13.2 took: to_timestamp with a
fixed 'YYYY-MM-DD"T"HH24:MI:SS.MS' raises `invalid value "Z" for "MS"`
on an endTime with no milliseconds, and since applyDataMigrations
defaults to true that would abort the migration and stop the server
booting. V18.1 uses a ::timestamptz cast, which accepts any ISO-8601
form and reads the trailing Z as UTC, behind a regex guard so a
non-timestamp value skips the row instead of failing the statement.
Verified on a 3913-workflow / 45531-task sqlite database: all 3822
finished workflows match json_data exactly with 0 mismatches, the 91
unfinished rows keep the epoch, and `ORDER BY end_time DESC` plans as
`SCAN workflow_index USING INDEX workflow_index_end_time_idx`. On
postgres, V18 then V18.1 against seeded data converts values with and
without milliseconds and with offsets, and leaves empty, malformed and
absent endTimes at the epoch.
No UI change: only `workflowType` was ever remapped on the way out, so
`endTime` already reached the server unchanged.
Manan Bhatt
69c16eae27