Alertee LogoAlertee
Back to Blog
monitoringengineering

Your SELECT max(created_at) cron job is not a monitoring system

The DIY freshness cron is the respectable incumbent — it works until it doesn't. Here's the six things it's missing, each with a concrete failure, and better SQL you can keep even if you never sign up.

ByNiallNiall
•

You've written this script. I know you have.

#!/bin/bash
# check_data_freshness.sh

RESULT=$(psql $DATABASE_URL -t -c "SELECT max(created_at) FROM events")
THRESHOLD=$(date -d '30 minutes ago' -u +%Y-%m-%d\ %H:%M:%S)

if [[ "$RESULT" < "$THRESHOLD" ]]; then
  curl -X POST https://hooks.slack.com/services/YOUR/WEBHOOK/HERE \
    -d '{"text":"⚠️ events table stale! Last row: '"$RESULT"'"}'
fi

Then you added it to cron — 0 * * * * /home/deploy/check_data_freshness.sh — and moved on. Twelve lines of shell, no vendor, no bill. It catches the thing you were worried about — the feed stopping — and it catches it for free. We wrote one too. This post is not going to tell you that was a mistake, because it wasn't. The freshness cron is the respectable incumbent. It's the right first move, and for a single feed it can be the right only move for a long time.

But there's a reason it stops being enough, and it's worth being precise about what's actually missing rather than hand-waving about "observability." Below are six things the cron doesn't have, each with a concrete way it bites, and each with SQL you can take regardless of whether you ever pay anyone. You should leave this with better crons even if that's all you do.

1. Memory — it has no idea an incident is open

Your cron runs every five minutes. The feed stops. Which of these did you build?

  • It re-alerts every five minutes. By the third page nobody's reading them, and now you've trained the team to swipe the alert away — including the next real one.
  • It alerts once and never again. Someone acks it in their head, the feed is still down two hours later, and there's nothing re-raising it.

There's no good version of this with a stateless cron, because "is this a new problem or the same problem I already know about" is a question about state, and the cron has none. Every run is born with amnesia. To even approximate an open-incident concept you have to give it somewhere to remember:

-- A tiny state table the cron reads and writes
CREATE TABLE monitor_state (
  check_name  text PRIMARY KEY,
  status      text NOT NULL,          -- 'ok' | 'firing'
  since       timestamptz NOT NULL,
  last_notified timestamptz
);

Now the cron has to compute the current state, compare it to the stored one, only notify on the transition from ok to firing, remember since, and back off re-notification. That's a small state machine you're now maintaining by hand, per check, and getting the edge cases right (flap, recovery, re-fire after N hours) is most of what a monitoring system actually is. The query was never the hard part.

2. Dedupe and ownership — five crons, five messages, nobody assigned

You don't have one cron, you have five: orders freshness, payments freshness, the two ingestion feeds, the nightly export. An upstream database has a bad ten minutes and all five go quiet at once. Your channel gets five Slack messages, none of them reference each other, and the on-call sees a wall of red with no signal about whether this is one root cause or five separate fires.

And then the harder question: who owns it? A Slack message is not assigned to anyone. "Someone will grab it" is how an alert sits unacknowledged for an hour while three people each assume one of the other two has it. The cron can post; it can't route, it can't dedupe correlated failures into one incident, and it can't hold the fact that this specific person has taken this specific problem. You can bolt on a dedupe key by hand:

-- Best you can do statelessly: a stable key per failure so a downstream
-- tool could group re-fires. The cron still can't assign an owner.
SELECT 'freshness:' || table_name AS dedupe_key,
       table_name,
       now() - max(created_at) AS staleness
FROM ( ... ) s
GROUP BY table_name
HAVING now() - max(created_at) > interval '30 minutes';

That gives a downstream system something to group on — but the cron itself has no downstream system, which is the whole point.

3. History — you can't tune a threshold you never recorded

Your freshness threshold is 30 minutes. Is that right? You genuinely don't know, because the cron keeps no record of how the feed actually behaves. Does it normally flap to 25 minutes twice a day and you're one bad afternoon from a false page? Does it never exceed 8 minutes except during a real incident, meaning your 30-minute bar is sleeping through the first 20 minutes of every outage? The cron can't tell you. It evaluates the present and forgets it.

Every threshold you set is a guess you can never check, because the data that would let you tune it — the distribution of normal staleness, how often each feed flaps, what the p99 gap looks like — was thrown away on every run. If you want to improve, you have to first start recording:

-- Append a reading every run; now you can actually study normal
CREATE TABLE freshness_history (
  check_name text NOT NULL,
  observed_at timestamptz NOT NULL DEFAULT now(),
  staleness_seconds double precision NOT NULL
);

-- Later: what does 'normal' actually look like for this feed?
SELECT check_name,
       percentile_cont(0.5)  WITHIN GROUP (ORDER BY staleness_seconds) AS p50,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY staleness_seconds) AS p95,
       percentile_cont(0.99) WITHIN GROUP (ORDER BY staleness_seconds) AS p99
FROM freshness_history
WHERE observed_at > now() - interval '30 days'
GROUP BY check_name;

That p95 is the number your threshold should be derived from. But you only have it if you started writing history before the incident, and a bare cron never does.

4. Schedule context — the cron doesn't know markets close

Here's the one that quietly kills DIY monitoring. Your orders feed is legitimately silent overnight, on weekends, on holidays. Your max(created_at) < now() - interval '30 minutes' check does not know any of that, so it pages you at 3am every night and all weekend, faithfully, for a system that is behaving exactly as designed.

You know what happens next. You add hour guards:

-- The guards metastasize
SELECT max(created_at) AS latest
FROM orders
WHERE created_at > now() - interval '1 day';
-- alert only if:
--   latest < now() - interval '30 minutes'
--   AND extract(dow from now()) NOT IN (0, 6)         -- not weekend
--   AND extract(hour from now() at time zone 'America/New_York')
--       BETWEEN 9 AND 16                              -- market hours
--   AND now()::date NOT IN (SELECT holiday FROM market_holidays)

And now every check has a bespoke calendar baked into its WHERE clause, each one slightly different, none of them tested, all of them going stale when the trading calendar changes or you add a market in another timezone. The failure mode isn't that the guards are hard — it's that the false 3am pages you got before adding them already trained you to ignore the alert. By the time the guards are right, the channel is muted. A monitor that cries wolf on schedule teaches you to distrust it, and a distrusted monitor is worse than none because it costs money and still doesn't get read.

5. The observer problem — the monitor dies with the box

This is the structural one, and it's the reason a cron can never be the whole answer no matter how good the SQL gets. Your freshness cron runs on the same infrastructure it's watching — same host, same database, same Kubernetes cluster, same cloud region. So consider the failure where that infrastructure itself goes down: the box OOMs, the database is unreachable, the region has an outage, the cluster's scheduler wedges.

Your monitor is on that box. When the thing it watches dies, it dies with it, silently, and the silence looks exactly like health — no alert fired, so everything must be fine. The one failure most likely to actually take down your product is the exact failure your monitor is structurally incapable of reporting, because it's a casualty of the same event. A monitor that shares fate with its target isn't a monitor for that class of failure; it's a component of it. The only fix is to run the check from somewhere that doesn't share fate — a different host, a different provider, genuinely outside the pipeline it monitors. That's not a tuning problem you can SQL your way out of; it's about where the scheduler lives.

6. Diagnosis — "0 rows" tells you nothing about which slice stopped

Your cron fires: freshness on events exceeded threshold. Great. Which tenant? Which feed? Which region? A max(created_at) over the whole table gives you a single timestamp and therefore a single bit of information — stale or not — when the actual failure is almost always partial. One tenant's integration broke. One region's edge misrouted. One feed's consumer wedged while its neighbour stayed healthy. The aggregate check either misses that entirely (the other slices keep max(created_at) fresh) or reports it with zero detail about where.

The fix is to group, and to make the alert carry the breakdown:

-- Per-slice freshness, so the alert can name what stopped
SELECT tenant_id AS name,
       EXTRACT(EPOCH FROM now() - max(created_at)) AS value
FROM events
GROUP BY tenant_id
HAVING now() - max(created_at) > interval '30 minutes';

That HAVING returns exactly the slices that stopped, and the responder starts from "tenant 4021 and 8830 went quiet 40 minutes ago" instead of "something about events is stale." But note what you've now signed up for: the grouped query, the per-slice thresholds (a small tenant's normal gap isn't a large tenant's), the state to avoid re-paging on the same slice, the history to know each slice's baseline — every earlier problem on this list, now multiplied per dimension.

Where this leaves the cron

None of the six is a reason to feel bad about the cron. Each one is just work — real, fiddly, stateful work — that the cron doesn't do and that you'd have to build to make it a monitoring system rather than a freshness probe. Add up memory, dedupe, ownership, history, calendars, an out-of-band scheduler, and per-slice diagnosis, and you've written a monitoring product. That's a fine thing to do; it's also a lot of undifferentiated infrastructure to own so that a query can page the right person at the right time with enough context to act.

That gap is exactly the one Alertee fills, and it fills it with the same plain SQL you already wrote — no black-box ML, no learned models, just the row-count and freshness queries you own and can read, running on an independent scheduler outside the pipeline they watch. On top of the query it adds the parts this post is about: an open-incident concept so you're paged on the transition and not every five minutes, dedupe and an inbox where a fire is acknowledged and owned, history so thresholds improve instead of staying guesses, calendars and quiet windows so markets closing doesn't page anyone, and a per-slice grid so the alert names which tenant or feed stopped.

Most monitors tell you something broke. Alertee tells you what, where, and since when.

Keep your cron for the first feed — it's the honest floor. When the six things above start costing you more than the query saves, connect a Postgres or ClickHouse database and turn one check on. It's the SQL you'd have written anyway, minus the monitoring system you'd have had to build around it.