oncall-scheduling

On-Call Scheduling

"Who is on call for the database team?" is one question and four tables. Answering it by guessing at table names is how agents end up querying sys_user_group.manager and reporting the wrong person.

The chain

sys_user_group          the team being covered
   └── cmn_rota          a rotation on that group ("Database — primary")
        ├── cmn_schedule the hours the rotation covers, and its TIME ZONE
        └── cmn_rota_roster   a rotation pattern: weekly, 7 days, starting <date>
             └── cmn_rota_member   one person, with a from/to window
                  └── sys_user

Read it as: a group has rotations, a rotation has rosters, a roster has members. The roster is the pattern (rotate weekly, one week each). The member row is one person's turn — it carries from and to as glide_date, and member referencing sys_user.

A group can have several rotations at once — primary, secondary, escalation — and they are unrelated records. "The on-call person" is never a property of the group.

Who is on call right now

snow_oncall_manage({ action: "get_current_oncall", rotation_sys_id }) resolves it in three steps, and knowing them tells you what a wrong answer means:

1. Read cmn_rota_roster where rota=<rotation>^active=true.
2. Find the cmn_rota_member rows belonging to those rosters whose from/to window covers today.
3. Resolve member to a sys_user.

Two consequences worth having in front of you:

• A rotation with no member window covering today returns nothing. That is not an error; it is a rotation whose roster has run past its generated windows. The tool says so explicitly rather than guessing at the next person.
• The dates are dates, and coverage is hours. cmn_rota_member.from/.to are days. Whether the person is on call at 03:00 is decided by the rotation's cmn_schedule — including its time zone, which is a field on the schedule and not on the rotation. A rotation whose schedule is in US/Pacific hands over at a different UTC moment than the same rotation in Europe/Amsterdam. Never convert on-call times yourself without reading cmn_schedule.time_zone first.