group restore and graph counts over time.

from slack:
recently i got bit by a source system providing bad data (10k deletes from a loader job). the team responsible for the data is challenged to get the data back. I did get the data (most of it) restored from PIT queries. But, this got me thinking about an effective UI and capabilities for handling “restore” of group data. Does a document exist that contains such ideas? does anyone want have such a conversation? From playing with some queries and thinking through what i needed to solve my problem (operationally focused) I am thinking an Apple TimeMachine restore approach would probably be best and think we have all the data needed to make it happen. I think we would need 3 capabilities with the appropriate UI on top.
1) a “point in time” restore. restore the membership of a group to what it was at a selected viable time.
2) “play forward” all changes forward from a viable time for the group.
3) “play backward” from now to a viable time for a group.
The play options are needed so downstream changelog consumers reprocess in order. as we know, order matters in identity operations.
oh - one other thing - a useful side effect would be to see a graph of membership counts on a group over time.
example of idea (not to be implemented this way cuz i know this isn't right):
This is done against mysql:
select gpg.name,
FROM_UNIXTIME (round(gpmav.membership_start_time/1000/1000),'%Y/%m/%d') start,
CASE WHEN gpmav.MEMBERSHIP_END_TIME is null THEN ' ACTIVE' ELSE FROM_UNIXTIME (round(gpmav.membership_end_time/1000/1000) ,'%Y/%m/%d') END end,
COUNT(gpg.name) count
from
grouper_pit_memberships_all_v gpmav,
grouper_pit_members gpm,
grouper_pit_fields gpf,
grouper_pit_groups gpg
where
gpmav.member_id = gpm.id
and gpm.subject_source = 'UFperson'
– and gpg.name = 'basis:Identity:Student:Graduating:2020'
and gpg.name = 'App:MFA:service:ref:Student_Emailings:Sent'
and gpmav.owner_group_id = gpg.id
and gpmav.field_id = gpf.id
group by start,end
order by start, end