Automated On-Call Roster Alerting System

Overview

I built an automated on-call alerting system that reads team schedules from a central Google Sheet and sends Slack notifications tagging the appropriate on-call person every weekday. The system supports multiple scheduling models (weekday-based and sprint-based) and handles team-specific channel routing, all without requiring teams to understand the underlying automation.

Problem Statement

The challenge was to provide a flexible on-call notification system that:

• Supports different scheduling preferences across teams (per-day, per-sprint)
• Requires no technical knowledge from teams managing their own rosters
• Avoids alert fatigue by not resending unchanged sprint assignments
• Delivers notifications to both a central channel and team-specific channels
• Operates autonomously with zero maintenance from the DevOps team once a new team onboards

Solution Architecture

The system uses a Google Sheet as the single source of truth and a scheduled Lambda function that reads it, resolves on-call assignments, and delivers Slack alerts.

1. Scheduling: EventBridge rules trigger the Lambda on weekdays at two different times for two modes
2. Data Source: Google Sheets API reads team tabs and reserved system tabs
3. Resolution Logic: Determines the on-call person based on the team's tab structure
4. State Management: A cache tab tracks current assignments to detect changes
5. Notification: Slack API delivers alerts with user mentions to appropriate channels

Implementation Details

Scheduling Modes

The system supports two modes, automatically detected by the column headers in each team's worksheet:

Problem Statement

The challenge was to provide a flexible on-call notification system that:

• Supports different scheduling preferences across teams (per-day, per-sprint)
• Requires no technical knowledge from teams managing their own rosters
• Avoids alert fatigue by not resending unchanged sprint assignments
• Delivers notifications to both a central channel and team-specific channels
• Operates autonomously with zero maintenance from the DevOps team once a new team onboards

Solution Architecture

The system uses a Google Sheet as the single source of truth and a scheduled Lambda function that reads it, resolves on-call assignments, and delivers Slack alerts.

1. Scheduling: EventBridge rules trigger the Lambda on weekdays at two different times for two modes
2. Data Source: Google Sheets API reads team tabs and reserved system tabs
3. Resolution Logic: Determines the on-call person based on the team's tab structure
4. State Management: A cache tab tracks current assignments to detect changes
5. Notification: Slack API delivers alerts with user mentions to appropriate channels

Implementation Details

Scheduling Modes

The system supports two modes, automatically detected by the column headers in each team's worksheet.

Default Mode (12:00 UTC daily)

For teams that assign on-call per weekday. The system checks today's day of the week and looks up the matching person. Every weekday, an alert is sent regardless of whether the person changed.

Detection: Team tab has a "Day of week" column.

Sprint Mode (05:00 UTC daily)

For teams that assign on-call per sprint or date range. Each row has From/To dates. The system finds which range covers today, then checks if the person differs from the last alert sent. Only when the person changes does a new alert go out.

Detection: Team tab has both "From" and "To" columns.

This means teams never need to configure which mode they use — the system infers it from how they structure their sheet.

Technology Stack

• Google Sheets API: Central data source (schedules, user mappings, team config)
• AWS Lambda: Serverless execution of the alerting logic
• EventBridge: Scheduled triggers for both modes
• Slack API: Notification delivery with user mentions
• Python: Core implementation language

Core Components

1. On-Call Job (on_call_job.py)

The main Lambda handler that orchestrates the entire flow:

Event Configuration:

{"id": "Default"}  // or {"id": "Sprint"}

Execution Flow:

1. Validates the job identifier and skips weekends
2. Opens the Google Sheet and reads all team tabs
3. For each team, detects scheduling mode from headers
4. Skips teams that don't match the current mode
5. Resolves the on-call person using mode-specific logic
6. For Sprint mode: compares against cache and skips if unchanged
7. Looks up the person's Slack user ID from the data tab
8. Sends alert to the central channel
9. Sends alert to dedicated team channel if configured
10. Updates the cache with the current assignment

Helper Class (_OnCallJobHelper):
Static methods encapsulating the resolution logic:

• is_sprint_team(headers) — detects mode from column headers
• resolve_default_on_call(...) — day-of-week or cache-based lookup
• resolve_sprint_on_call(...) — date range matching
• has_on_call_person_changed(...) — cache comparison for deduplication

Google Sheet Structure

Reserved System Tabs

Tab	Purpose
_Data	Maps team member names to Slack user IDs
_Cache	Tracks current on-call person per team (managed by automation)
_Teams	Team configuration (dedicated Slack channel)

Team Tabs — Default Mode

Day of week	Name	Backup
Monday	Alice Smith	Bob Jones
Tuesday	Charlie Brown

Team Tabs — Sprint Mode

From	To	Name	Backup
2026-07-14	2026-07-25	Alice Smith
2026-07-28	2026-08-08	Bob Jones

Alert Deduplication (Sprint Mode)

The key design challenge for sprint-based teams was avoiding daily alert spam. The solution:

1. On each run, resolve today's on-call person from the date ranges
2. Compare against the _Cache tab which stores the last alerted person
3. If unchanged, silently skip — no alert sent
4. If different (new sprint started), send alert and update cache

This means sprint teams only see a notification at the boundary between sprints.

Multi-Channel Alerting

Any team can configure a Dedicated Channel in the _Teams tab. When set, the alert is sent to both the central channel (for cross-team visibility) and the team's own channel (for focused notification).

Self-Service Onboarding

Teams onboard themselves without DevOps involvement:

1. Add members to _Data tab with Slack user IDs
2. Add team config to _Teams tab
3. Create a worksheet tab with (tbd) suffix while setting up
4. Populate the schedule in their preferred format
5. Remove (tbd) from the tab name — alerts begin automatically

The (tbd) convention acts as a feature flag at the data layer.

Design Decisions

Why Google Sheets?

• Non-technical users can manage schedules without learning new tools
• Different teams across the organization already use Sheets daily — no adoption friction
• Built-in tab-level protection allows locking down each team's tab so only authorized members can edit their own roster, preventing accidental cross-team changes
• Teams can self-service without submitting tickets to DevOps
• Immediate visibility into who is on call (just open the sheet)
• No deployment required to update schedules

Why Detect Mode from Headers Instead of Configuration?

• Zero configuration burden on teams
• Impossible to misconfigure — the structure IS the configuration
• Adding a "From" and "To" column is the only step to switch modes
• No separate config tab entry to keep in sync

Why Cache-Based Deduplication?

• Sprint-based teams would get the same alert every single day otherwise
• Polling the sheet daily is cheap; sending alerts daily is noisy
• Cache comparison is simple (string equality on name) and reliable
• Cache also serves as a "last known state" for operational visibility

Why Two Separate Schedules?

• Sprint alerts at 05:00 UTC so teams know about changes before workday begins
• Default alerts at 12:00 UTC (noon) as a mid-day reminder of who is on call
• Separating them makes it easy to adjust timing per mode independently
• Each invocation only processes relevant teams, keeping execution fast

Why Skip Weekends?

• On-call coverage during weekends follows a different process (PagerDuty)
• Sending alerts to empty offices creates noise and degrades trust in the system
• Weekend coverage for critical systems uses separate escalation paths

Operational Considerations

Common Failure Modes

1. Name mismatch: Names in team tabs must exactly match _Data (case-sensitive)
2. Missing date range: Sprint teams with no row covering today get silently skipped
3. Empty Slack ID: Person gets mentioned as empty string in Slack (no notification)

Maintenance Requirements

• Sprint teams must add future date ranges before current ones expire
• New team members need entries in _Data with correct Slack user IDs
• The _Cache tab should never be manually edited

Monitoring

• Lambda execution logs capture which teams were processed and which were skipped
• Warning-level log emitted when no sprint range covers today for a team
• Error-level log for unknown job identifiers

Lessons Learned

1. Let the Data Be the Config: Detecting mode from sheet structure eliminated an entire category of misconfiguration errors
2. Alert Fatigue is Real: The cache-based deduplication for sprint mode was critical — without it, teams would have muted the channel within a week
3. Self-Service Scales: The (tbd) convention for in-progress teams meant zero DevOps tickets for onboarding after the first month
4. Non-Technical Users Need Guardrails, Not Documentation: Case-sensitive name matching is the #1 support issue — explicit validation in the code would be a worthwhile future addition
5. Separate Concerns by Time: Running Default and Sprint at different times simplified debugging and made it possible to adjust cadence per mode without code changes