Slack alert from a SQL query: DIY, then done right

You have a query that, when it returns something, means somebody should look. Orders stuck in pending, a signup webhook that stopped writing rows, a refund over some limit. You want a Slack message when that happens, and silence otherwise.

You can build this in an afternoon with tools already on your server. I’ll show that version first, because it’s a good way to learn what the problem actually is. Then I’ll show where it bites you.

The DIY version

The query returns rows only when something is wrong. Here it’s orders that have been pending for over an hour:

SELECT id,
       total,
       round(extract(epoch FROM now() - created_at) / 60) AS minutes_waiting
FROM orders
WHERE status = 'pending'
  AND created_at < now() - interval '1 hour'
ORDER BY created_at
LIMIT 10;

Healthy database, zero rows. Say a bad deploy leaves a batch of orders hanging: you get one row per order, oldest first, with how long each has waited. The LIMIT keeps the Slack message readable.

Create an incoming webhook in Slack (an app with the Incoming Webhooks feature gives you a URL bound to one channel). Then a script:

#!/usr/bin/env bash
set -euo pipefail

STATE=/var/tmp/stuck_orders.firing
rows=$(psql "$DATABASE_URL" -At -F ' | ' -f /opt/alerts/stuck_orders.sql)

post() {
  jq -n --arg text "$1" '{text: $text}' |
    curl -fsS -X POST -H 'Content-Type: application/json' -d @- "$SLACK_WEBHOOK_URL" >/dev/null
}

if [ -n "$rows" ]; then
  if [ ! -f "$STATE" ]; then
    post "Orders stuck in pending (id | total | minutes waiting):
$rows"
    touch "$STATE"
  fi
elif [ -f "$STATE" ]; then
  post "Resolved: no orders stuck in pending."
  rm "$STATE"
fi

And a crontab line:

*/5 * * * * DATABASE_URL=postgres://alerts:...@db/app SLACK_WEBHOOK_URL=https://hooks.slack.com/services/... /opt/alerts/stuck_orders.sh

-At makes psql print bare rows with no header or alignment, jq handles JSON escaping so a quote in your data can’t break the payload, and curl -f makes a bad webhook response a non-zero exit.

That’s already better than the five-line version most people write first, which is psql ... | curl ... and spams the channel every five minutes. I added a state file so it only posts when things flip from fine to broken, and once more when they flip back.

It works. I’d still not run it for anything you care about.

Where it breaks

Cron fails silently. If the database is down, the password rotated, or jq isn’t on cron’s PATH, the script exits non-zero and nothing tells you. Cron mails the output to a local mailbox nobody reads. Your alert’s own failure looks exactly like “no problems today,” which is the worst possible failure mode for an alert. You can add a dead-man’s switch (ping a heartbeat URL on every successful run), but now you’re running a second monitoring system to monitor the first.

The state file only tracks “any rows.” If two orders are stuck and a third gets stuck an hour later, nobody hears about it, because the alert is already firing. Fixing that means storing a fingerprint of the rows and diffing it, and now your 20-line script has a data model.

State lives in /var/tmp. Reboot the box, or redeploy to a fresh container, and the file is gone. The next run re-announces everything that was already known. Or worse, a “resolved” message never arrives.

Secrets sit in the crontab. The database password and the webhook URL are in plaintext in a file that’s readable by anyone with shell access, and probably in a backup. A Slack webhook URL is a bearer credential: anyone holding it can post to your channel.

Nobody owns it. It’s on one server, written by one person, and it lives in /opt, not in a repo. Six months later someone asks why Slack pinged at 3am and the answer is “ssh in and read the script.” Then the person who wrote it leaves.

None of that makes cron wrong. For one alert on a side project, it’s fine. By the fifth alert you’re maintaining an alerting system by accident.

Rules for any SQL-to-Slack setup

Whatever you build it with (this script, a scheduled job in your app, a managed tool), these are the things I’d hold it to.

1. The query returns rows only when something is wrong. Put the condition in the WHERE, not in the script. A query that always returns a number forces the alerting layer to know what “bad” means, and that logic ends up scattered. Zero rows means healthy, and anyone can read the query and know what the alert is about.

2. The alert body is the rows. “Something is wrong with orders” starts a scavenger hunt. “Orders 4182, 4190 and 4203 have waited 61, 74 and 80 minutes” starts a fix. Select the columns someone needs to act: the ID, the amount, how long. Cap it with LIMIT so a million bad rows doesn’t become a million-line message. Then say how many there were in total if it matters.

3. Fire on state change, not on every run. One message when it goes bad, one when it recovers. A check that runs every five minutes and pings every five minutes teaches your team to mute the channel, and a muted channel catches nothing. If you also want a reminder for incidents that nobody picks up, make that an explicit second mechanism, like a repeat after an hour or a page to a second person.

4. Send a resolved message. Without it, people can’t tell whether the thing fixed itself or is still burning. It also closes the loop for whoever saw the first alert and went to lunch. It’s the cheapest rule on this list and the one DIY setups skip most.

5. Link back to the query. The person reading the alert at 7am on a phone shouldn’t have to find the right repo or the right dashboard. Put a URL in the message that runs the query. Even in the cron version, a link to a saved query in your SQL client beats nothing.

Add a sixth for the failure mode above: the check itself should be monitored. If the query errors out or the job doesn’t run, that has to produce a signal, not silence.

How arcodash does it

This is the exact job arcodash does, so I’ll be brief. You save the query, create a monitor on it, and pick a condition on a column of the first result row. Since a monitor compares one column to a threshold, the stuck-orders monitor uses a count:

SELECT count(*) AS stuck_orders
FROM orders
WHERE status = 'pending'
  AND created_at < now() - interval '1 hour';

The condition is stuck_orders > 0. Notifications fire only on a state change, ok to triggered and back, which covers rules 3 and 4. Destinations include Slack (an incoming webhook URL, stored without ever being shown again), email, PagerDuty, Teams, Discord and a few more. Each triggered alert goes with a short line describing what changed. For a metric that should alert on a drop rather than on a count of bad rows, see how to write the week over week and small-number logic.

For the incident part, someone can acknowledge it, snooze it for a fixed time or mute it, and you can set up escalation: if nobody acknowledges within N minutes, notify a second destination. Every delivery attempt is logged per monitor, so “did Slack actually get it?” has an answer.

One difference from the DIY script above: the monitor reads a single column from the first row, so the Slack message tells you the monitor changed state rather than listing the offending orders. Keep the row-listing query next to it as a saved query, and you’re one click from the rows. If a message that lists every row is the thing you need, the script version does that today.

If you want to try it, there’s a 14-day trial with no card.

Pick by how many alerts you have

One alert, one server, low stakes: use the script, add a heartbeat ping, and move on. Several alerts, or alerts other people depend on: use something that stores state, logs deliveries and has an owner. What you build or buy matters less than whether the rules hold.