Slack alert from a SQL query, without cron

The shape of the problem

You have a query that means “someone should look” when its answer is bad. Payments failing, orders stuck, a signup count at zero. You want that in a Slack channel, once, when it starts, and again when it’s over.

The cron-and-curl version works and is worth reading for what goes wrong with it. This page is the other half: the setup that replaces it, with the query pattern and each step spelled out.

Write the query to return one number

An alert checks one column of the first row of your result. That drives how to write the SQL: aggregate down to a single value and let the alert condition decide what’s bad.

SELECT count(*) AS failed_payments
FROM payments
WHERE status = 'failed'
  AND created_at > now() - interval '15 minutes';

This always returns a row, even on a quiet day, where it returns 0. That matters. If you wrote the query to return zero rows when healthy, the alert would sit in an unknown state rather than ok, because there’s no first row to read.

When a raw count is too jumpy, alert on a rate instead. The coalesce and nullif keep an empty window from producing a NULL:

SELECT coalesce(
  round(100.0 * count(*) FILTER (WHERE status = 'failed') / nullif(count(*), 0), 1),
  0
) AS failed_pct
FROM payments
WHERE created_at > now() - interval '15 minutes';

Three failed payments out of fourteen in the window is 21.4, and the condition failed_pct > 10 fires. Three out of three thousand is 0.1 and doesn’t. Pick the form that matches how you’d judge it by eye.

Set it up in arcodash

1. Create the Slack webhook. In Slack, add an app with Incoming Webhooks and bind it to the channel that should hear about this. You get a URL. Treat it like a password: anyone holding it can post to that channel.
2. Add the destination. Create a Slack destination and paste the webhook URL. The URL is stored and never shown again, so you can update it but not read it back. Creating destinations needs the manage_destinations permission. The destination docs cover all nine types.
3. Save and publish the query, then give it a schedule. Every 5 minutes is the shortest preset. An alert is checked when its query refreshes, so a query nobody schedules is only checked when someone opens it.
4. Create the alert. Pick the query, the column (failed_payments), an operator and a value: > and 5, say.
5. Subscribe the Slack destination to the alert.
6. Send a test. You can re-send a notification to a single subscription whatever the current state is, which is the way to confirm the channel and the formatting before a real incident.

Details on the condition and states are in the alerts overview.

What lands in Slack

A card, colored by state, posted as a Slack attachment. For a triggered alert the title is “Failed payments is alerting”, and the title is a link to the monitor in arcodash. Under it sit a one-line description (“The condition on this monitor is met.”), a short statistics line about what changed against the recent history, and two fields: Observed (the value) and Condition (for example failed_payments > 5). The footer carries the arcodash name and the time. If your organization has an AI provider configured, an AI analysis of why it fired is added as a third field, labelled with its confidence; it has a 20 second budget and the alert goes out without it if it runs long.

When the value comes back inside the condition, a second card titled “… is back to OK” goes out, in green. That’s the resolved message you’d otherwise write by hand. A query the alert can’t evaluate gets an amber card with the reason.

Slack, Teams and the other chat destinations share this layout. There’s no card editor, but you can set a custom subject and body on the subscription. They replace the title and the description line, and accept {{name}} and {{state}}. The Observed and Condition fields and the footer stay as they are.

Keep the noise down

Alerts notify on state changes only. Three things shape how often that is:

• Rearm. If the value hovers around the line and flips ok, triggered, ok, triggered, set a rearm period in seconds. Repeat triggers inside the window update the state but skip the message.
• Snooze and mute. Snooze silences notifications for a set number of minutes, then lifts itself. Mute lasts until you undo it. Both are good for a deploy window.
• Escalation. A persistently triggered alert never re-notifies by itself. If you want a nudge when nobody has acknowledged it, escalation sends to a second destination after N minutes. See escalation.

Every delivery attempt is logged per monitor, newest first and capped at 50, so “did Slack actually get it?” has an answer, including the error if a webhook was revoked.

Limits worth knowing

• The message doesn’t list rows. You get a value, the condition and a link to the monitor. If the on-call person needs the ten failed payment IDs, put them in a second saved query and keep it one click away. A cron script that prints rows into the message does that better today.
• One column, first row only. An alert can’t watch five metrics at once. Either build one alert per metric or fold the checks into one query that returns a count of failing checks.
• The shortest scheduled cadence is every 5 minutes. For something that must page within seconds of happening, this is the wrong tool.
• Slack destinations are incoming webhooks, which post to one channel. There’s no Slack app login, threading or buttons.

Where this pays off

The first alert is easy either way. The difference shows up around the fifth: state is stored, deliveries are logged, secrets aren’t sitting in a crontab, and somebody besides the author can open the alert and see what it checks. arcodash is hosted only, with a 14-day trial and no card, and it’s a reasonable place to try this with a query you already have.

The same query pattern works on any connector; Postgres is the one used above, and scheduled SQL checks covers the pattern in general.