On T176754 we discovered that user_rights table does not update itself when user rights expire and that when temporary user rights expire the user_former_groups ain't updated either until unless another rights change happen in that wiki.
This task aims to request a fix for this second part (user_former_groups not being updated when user rights expire) as suggested in the task linked above.
In my humble opinion, this is the part that is purely MediaWiki-libs-Rdbms (actual mediawiki bug, not DBA related). We could (maybe) help with the other things I mentioned at T177404 (labs view), not here, as this is purely a mediawiki change.
The only solution I can think of to the problem stated in the task title, other than the scheduled task or jobqueue job proposed in T176754, would be to add an ufg_start_time column to the user_former_groups table which indicates when the user's status as a former member of that group takes effect.
That might be overkill though - wouldn't it just be better to alter the single function in MW+extensions that reads from user_former_groups (User::getFormerGroups) to also consider rows in user_groups with ug expiry in the past?
That sounds like a good idea to me. In addition to that, we could consider deleting expired rows/adding to user_former_groups whenever the user makes an edit, instead of waiting for someone to change their groups.
whenever the user makes an edit, instead of waiting for someone to change their groups
That sounds also good to me in addition to what TTO proposed above :)
Why does this need a second task. I see this as one issue.¹ Whenever the entry on user_groups is removed from there (either by another user_groups action or by the cron job), that must add it into user_former_groups (if not already there).
¹ Rather than naming one task purge and the second add, it would have been preferable to call it move, as it would have been clearer.