Overdue BTS Ticket Alert Automation

Company

An Airflow pipeline that groups unresolved BTS tickets past their SLA by assignee and alerts them on Slack every morning

Period | 2026.09

Around 1,000 of roughly 10,000 unresolved BTS tickets pass their lead time every day
No read access to the ticket table behind the original dashboard SQL, so the query could not simply be scheduled
Overdue counts per assignee were skewed (median 4, max 597), overflowing Slack's text limit in a single message

Rebuilt the pipeline to read tickets via JIRA REST (JQL) and join SLA and store tables in Trino
Counted elapsed time in business days and skipped weekends and holidays to prevent duplicate alerts
Mentioned assignees without any user lookup API by relying on Slack usernames matching JIRA email local parts
Split each alert into a parent message and threaded replies to stay within the text limit

Running daily at 10:00 since 2026-09-30, taking about 1-3 minutes per run
Business-day logic cut the overdue count by 21% versus calendar days (1,239 → 976)
Core logic verified with 21 unit tests that call the deployed code, plus mutation checks

Tickets via JIRA REST (JQL), reference tables via Trino
Column-level permissions blocked the dashboard's ticket table, so only tickets moved to JQL while SLA and store tables stayed in Trino
Absolute dates instead of JQL relative dates
On this JIRA server a month offset (-7M) returned fewer results than a day offset (-7d), so the start date is computed explicitly
Business-day elapsed time and no sends on holidays
CS does not work tickets on weekends or holidays; sending on those days would repeat the previous business day's message
Mentions without a user lookup API
Without the users:read.email scope, mentions rely on Slack usernames matching JIRA email local parts: zero lookup calls, every assignee resolved
Parent message plus threaded replies
One assignee reached 597 tickets (62,787 chars), beyond Slack's 40,000-char limit where messages are silently truncated

Slack 429 rate limits are absorbed by the built-in retry handler; send spacing documented for future growth
Traced the first production failure to a Slack bot swap during review and added a sibling-DAG config check to the deploy checklist
Each run re-scans the full backlog, so nothing is lost on skipped holidays