Catch social posts stuck in approval before they miss their slot

Every weekday at 8:30am, check Hootsuite for social posts that are still sitting in pending approval, and chase the ones that are about to miss their scheduled slot.

Use Hootsuite's Retrieve Outbound Messages to pull messages across a window running from 7 days in the past to 14 days in the future. Hootsuite requires both a start time and an end time on this call and the range must not exceed 4 weeks, so a 21 day window is safe. The backward half of the window matters: without it you cannot see posts whose publish time has already gone by. Page through all results rather than reading only the first page. Keep only the messages in the pending review or approval state, and discard anything already approved, sent, or rejected.

For each remaining message, compare its scheduled publish time to the current time and sort it into one of three buckets. Missed: the publish time has already passed while the post is still unapproved, which means it silently failed to go live. Urgent: the publish time falls within the next 48 hours. Upcoming: everything else still pending further out.

Use Retrieve Social Profiles to build a lookup from social profile id to a readable account name and network, so the digest names the real account, for example "Acme on LinkedIn", instead of a raw id. Note that scheduling one post to several profiles creates a separate Hootsuite message per profile, so group messages that share the same copy and publish time into a single digest entry and list their target accounts together.

If the Missed bucket and the Urgent bucket are both empty, do not post anything at all. Staying quiet on a calm morning matters more than a daily heartbeat, so never post an empty digest.

Otherwise post one message to the social team's Slack channel using Slack Bot's Send a Message. Lead with a Missed section when it is non-empty, since those posts have already failed to publish, then an Urgent section for anything going out within 48 hours, then a short Upcoming tail listing the rest. For every entry show the post copy trimmed to roughly 200 characters, the target account name, and the scheduled publish time in the team's local timezone with a relative hint such as "in 6 hours" or "2 days ago". Include the Hootsuite message id so someone can find the post quickly.

Open the digest with a one line summary such as "3 posts need sign-off, 1 already missed its slot". Send a single message rather than one message per item.