On-Call assignments page appears broken

[mastercactapus]

Describe the Bug:
User on-call assignments appear broken

Steps to Reproduce:

1. Go to user profile page
2. Click on on-call assignments
3. View output

Expected Behavior:
Each step should be listed once.

Observed Behavior:
Several repeats are listed.

Screenshots/Stack Traces:

Application Version:

    <meta http-equiv="x-goalert-version" content="v0.33.0-24-g8884bbb64" />
    <meta http-equiv="x-goalert-build-date" content="2024-12-18T06:33:44Z" />
    <meta http-equiv="x-goalert-git-commit" content="8884bbb6478823a1819bd83df2dd9e1bfdbc84f9" />
    <meta http-equiv="x-goalert-git-tree-state" content="clean" />

[lucksidedown]

We see the same behavior when a user is added to an escalation policy for any service. This begins duplicating over and over again, and the outcome of that eventually is GoAlert becoming unusable due to excessive amount of data to query when trying to open up a service.

This appears to be the query that is looping:

query keeps looping and duplicating our steps. 
 
 with on_call as (
    select
     step.id step_id,
     coalesce(act.user_id, part.user_id, sched.user_id) user_id
    from escalation_policy_steps step
    join escalation_policy_actions act on act.escalation_policy_step_id = step.id
    left join rotation_state rState on rState.rotation_id = act.rotation_id
    left join rotation_participants part on part.id = rState.rotation_participant_id
    left join schedule_on_call_users sched on sched.schedule_id = act.schedule_id and sched.end_time isnull
    where coalesce(act.user_id, part.user_id, sched.user_id) notnull
   ), ended as (
    select
    ep_step_id step_id,
     user_id
    from ep_step_on_call_users
    where end_time isnull
    except
    select step_id, user_id
    from on_call
   ), _end as (
    update ep_step_on_call_users ep
    set end_time = now()
    from ended
    where
     ep.ep_step_id = ended.step_id and
     ep.user_id = ended.user_id and
     ep.end_time isnull
   ) 
   insert into ep_step_on_call_users (ep_step_id, user_id)
   select step_id, user_id
   from on_call
   on conflict do nothing
   returning ep_step_id, user_id

We see the behavior in 0.30.1, 0.32, and 0.33.

[mastercactapus]

That query is used by the engine to update the set of on-call users. The table should only have 1 unique record for each user per step:

CREATE UNIQUE INDEX idx_ep_step_on_call ON public.ep_step_on_call_users USING btree (user_id, ep_step_id) WHERE (end_time IS NULL);

It wouuldn't be a bad idea to validate that, but I'm thinking it may be in the select statement used for the GraphQL call -- likely a join not properly constrained