Project list: correct order by 'Last updated'

Why are we doing this work

A project list is displayed in various places in GitLab (e.g. dashboard or Explore projects page). For each project in the list, it shows when it was last updated (e.g. "Updated 1 week ago"). This text is based on the maximum of three timestamps of a project: last_activity_at, last_repository_updated_at, and updated_at. This is fine and makes sense.

However, there is a problem with sorting: You can sort the list by "Last updated" (or "Oldest updated"), but unfortunately this sorting option only considers the last_activity_at timestamp. This causes that the project list doesn't seem sorted correctly for the user.

Obviously, the code must be adapted so that the maximum of the three timestamps is taken into account when sorting. But this leads to a database performance challenge. In !75350 (closed), we tried to solve the problem using a GREATEST index. Unfortunately, we came to the conclusion that this is not feasible.

That's why we are now taking an alternative approach: We add a separate column last_updated_at which contains the greatest value of the three timestamps and is also automatically updated when one of the timestamps is updated. (see discussion in !75350 (comment 793267223))

Relevant links

Implementation plan

(Source: !75350 (comment 796919567))

  1. Release %14.7: Migration: add new column last_updated_at. Implement that column is updated automatically when other columns are updated (likely with triggers).
  2. Release %14.8: Background migration: backfill column: greatest value of last_activity_at, last_repository_updated_at, and updated_at.
  3. Release %14.9: Update the code to use the new column.

A few benefits from this order:

  • Steps 1 and 2 can be completed with no effect on the current user experience
  • Implementing the triggers/automatic updates before the background migration ensures there is no chance of records not being correct.
  • We can allow last_updated_at to be NULL, then in the background migration, we only have to update records WHERE last_updated_at IS NULL, so any records already updated can be skipped.