Weekly stalled candidate alerts for Lever hiring managers

Every Monday at 8am, send each hiring manager a Slack direct message listing the candidates who have stalled in their hiring pipeline, and log a weekly snapshot to a Google Sheets tracker so stalls can be tracked over time. Run this on a cron trigger.

Start in Lever. Use List Postings to pull every open role. For each posting, use List Opportunities to get the candidates on it, keeping only active, non-archived candidates. Use List Stages once at the start to build a lookup from stage ID to readable stage name, because opportunities reference their stage by ID and the message needs to say "Phone Screen", not a UUID.

Work out how long each candidate has been sitting in their current stage from the lastAdvancedAt timestamp on the opportunity. Lever returns timestamps as Unix milliseconds, so convert before comparing against the current time. If lastAdvancedAt is missing, fall back to createdAt.

Flag any candidate who is past the SLA for their stage. Use three business days for phone screen, seven days for onsite and hiring manager review, and five business days as the default for any other stage. Define these thresholds as a clearly labelled per-stage mapping near the top of the workflow so they are easy to change, and make sure business day counts skip weekends.

Suppress false positives before flagging anyone. Call List Interviews on an Opportunity for each candidate that would otherwise be flagged, and skip them if they already have an interview scheduled in the future. A candidate waiting on a booked interview is not stalled, and surfacing them trains managers to ignore the message.

Work out who to notify for each role. Use List Users with Access to a Posting and pick the hiring manager for that posting. Match that person to Slack with Look Up User by Email using their Lever email address. If no Slack user matches, collect them and report it in the run summary rather than failing the whole run.

Send one Slack Bot direct message per hiring manager, using Open a Conversation to get the DM channel and then Send a Message. If a manager owns several roles, group their message by role rather than sending one message per posting. Within each role, sort candidates most overdue first and cap the list at the ten worst, noting how many more were left off. For each candidate include their name, current stage, and how many days they have been waiting. Write it as a short, readable, prioritized note that leads with the single most urgent candidate, not a raw data dump. Skip managers who have nothing overdue instead of sending them an empty message.

Finally, append one row per posting to the Google Sheets tracker using Append Values, recording the run date, the role title, the number of active candidates, and the number overdue. Append a row for every open posting, including ones with zero overdue candidates, so the trend line stays complete and week-over-week comparisons are honest.

Respect Lever rate limits by keeping requests at or below ten per second, and page through every list response until hasNext is false so large pipelines are not silently truncated.