Modern teams don’t fail because they lack data—they fail because the right people don’t hear about the right changes in time. Query-based alerting (turning SQL/NoSQL queries into notifications) is one of the most practical ways to close that gap. Instead of monitoring only infrastructure metrics, you monitor business and operational facts: stuck orders, abnormal signups, payment errors, backlog growth, or data freshness problems.
This guide explains how to design low-noise, high-signal alerts using scheduled queries across SQL and NoSQL databases, with patterns you can adapt to PostgreSQL, MySQL, and MongoDB.
What “query-based alerting” actually means
Query-based alerting is simple:
- You write a query that detects a condition (threshold, anomaly, missing data, state transition).
- You run it on a schedule.
- If results match your rule, you send a notification (push, email, Slack-like tooling, etc.).
The advantage is precision: your alert can include context (IDs, counts, recent examples) and map directly to an action. The trade-off is that you must design the query and schedule carefully to avoid alert fatigue.
Principle: Every alert should answer “What changed, why does it matter, and what should I do next?”
Picking alert signals: start with outcomes, not dashboards
Before writing queries, define the outcomes you want to protect. A good alert is tied to a decision or intervention, not curiosity. Common categories:
1) Customer-impacting failures
- Payments failing above baseline
- Orders stuck in a processing state
- Emails not being delivered / queued backlog exploding
2) Data freshness and pipeline health
- No new events ingested in the last N minutes
- ETL job exceeded expected duration
- Key tables stopped updating
3) Guardrails and risk
- Unexpected privilege changes
- Spike in deletions
- Unusual admin actions
Alerting approaches compared
| Approach | Pros | Cons | Best for |
|---|---|---|---|
| Scheduled queries (polling) | Easy to adopt; works with many DBs; rich context in results | Not “instant”; must tune intervals and deduping | Operational and business KPIs |
| DB triggers + notifications | Near-real-time; event-driven | Harder to manage; can add DB load; riskier changes | High-value state transitions |
| Log/CDC-based monitoring | High fidelity; scalable for analytics | More infrastructure; longer setup | Large systems, audit/compliance |
For many teams, scheduled query alerts are the fastest path to useful monitoring—especially when you keep queries read-only and focus on actionability.
Design patterns for low-noise alerts (with examples)
Pattern A: “Stuck state” alerts (PostgreSQL)
Use when entities should transition within an expected time window (orders, jobs, tickets). A robust query returns both a count and sample IDs for triage.
-- Postgres: orders stuck in PROCESSING for > 15 minutes
SELECT
COUNT(*) AS stuck_count,
ARRAY_AGG(id ORDER BY updated_at ASC LIMIT 10) AS example_order_ids
FROM orders
WHERE status = 'PROCESSING'
AND updated_at < NOW() - INTERVAL '15 minutes';Alert rule ideas:
- Warn when
stuck_count >= 5for two consecutive checks. - Escalate when
stuck_count >= 50immediately.
Pattern B: “Missing data” freshness alerts (SQL)
Freshness alerts catch silent failures: ingestion stopped, a cron died, or a pipeline is stuck. The key is to alert on absence of expected writes.
-- Works in many SQL dialects with small changes
SELECT
MAX(created_at) AS last_event_at
FROM events
WHERE created_at > NOW() - INTERVAL '2 hours';Trigger if last_event_at is older than your SLA (for example, 10 minutes). To reduce false alarms during planned downtime, consider a “maintenance window” flag in your alerting layer.
Pattern C: Baseline + threshold (MySQL)
Pure thresholds can be noisy when volume is naturally seasonal. A simple baseline approach compares the current window to a previous window.
-- MySQL: compare last 15 minutes to the previous hour average
SELECT
(SELECT COUNT(*)
FROM payments
WHERE status = 'FAILED'
AND created_at > NOW() - INTERVAL 15 MINUTE) AS failed_15m,
(SELECT COUNT(*) / 4
FROM payments
WHERE status = 'FAILED'
AND created_at BETWEEN NOW() - INTERVAL 75 MINUTE AND NOW() - INTERVAL 15 MINUTE) AS failed_15m_baseline;Trigger when failed_15m is, say, 2× baseline and above a minimum count (for example, at least 20) to avoid alerting on tiny denominators.
Pattern D: “Find the offenders” (top-N grouping)
Alerts that identify the top source of a problem cut time-to-fix. Group by a dimension you can act on (endpoint, customer, region, provider).
-- Postgres: top failing payment providers in the last 30 minutes
SELECT provider, COUNT(*) AS failures
FROM payments
WHERE status = 'FAILED'
AND created_at > NOW() - INTERVAL '30 minutes'
GROUP BY provider
ORDER BY failures DESC
LIMIT 5;Trigger when any single provider exceeds a threshold, and include the top rows in the notification body.
Pattern E: MongoDB “status transition” checks
For document stores, focus on fields that represent lifecycle transitions. If you store timestamps on state changes, you can query for “in state too long.”
// MongoDB: shipments stuck in 'label_created' for more than 2 hours
db.shipments.countDocuments({
status: "label_created",
statusUpdatedAt: { $lt: new Date(Date.now() - 2 * 60 * 60 * 1000) }
});To include examples, fetch a small set of IDs:
db.shipments.find(
{
status: "label_created",
statusUpdatedAt: { $lt: new Date(Date.now() - 2 * 60 * 60 * 1000) }
},
{ _id: 1, statusUpdatedAt: 1 }
).sort({ statusUpdatedAt: 1 }).limit(10);Scheduling: intervals, consecutive failures, and deduping
Most alert noise comes from schedules and repeat notifications, not from the core query. Use these controls:
- Choose an interval that matches the process. If an order normally completes in 2–5 minutes, check every 2–3 minutes. If a pipeline runs hourly, check every 10–15 minutes.
- Require consecutive matches (e.g., “condition true 2 checks in a row”) to filter transient blips.
- Use a cooldown window (e.g., don’t re-notify for 30 minutes unless severity increases).
- Include “state keys” (like a hash of top IDs or the max timestamp) to suppress duplicates when nothing changed.
A practical dedupe trick: alert on “newly bad” only
If your alert runner can store a tiny bit of state, track the last-seen set of offenders. Notify only when new IDs appear or counts rise materially. If you can’t store state, embed a time window in the query (e.g., “created in the last 30 minutes”) so old issues don’t keep re-triggering.
Security: read-only access and least privilege
Query-based monitoring should be safe by default. A secure setup typically includes:
- Read-only database users limited to the minimal schemas/tables required.
- Encrypted connections (TLS) and certificate validation where possible.
- No secrets in queries and no PII in notifications (send IDs and counts, not full customer records).
- Parameterized patterns (when supported) to avoid ad-hoc query edits and reduce risk.
When you need to expose diagnostic context, prefer links to internal tools over sending raw sensitive fields in a push/email.
Operational checklist: make alerts actionable
| Question | What good looks like |
|---|---|
| Is there a clear owner? | Alert routes to a team/on-call rotation that can fix it. |
| Is the condition specific? | Query returns a narrow, interpretable result (counts + examples). |
| Is the threshold justified? | Based on historical volume and customer impact, not guesswork. |
| Will it spam? | Consecutive checks + cooldown + dedupe keys reduce repeats. |
| Can someone act in 5 minutes? | Runbook link + top offenders + suggested next step included. |
Common pitfalls (and how to avoid them)
Over-alerting on small numbers
A 200% increase from 1 to 3 failures isn’t necessarily urgent. Add a minimum volume floor (e.g., “at least 20 events”) alongside percentage rules.
Time zone and clock skew issues
Standardize on UTC timestamps in the database and use DB-native time functions consistently. If you ingest from multiple sources, prefer server-side timestamps.
Queries that are too expensive
Alert queries run repeatedly. Optimize them like production endpoints:
- Index filter columns (
status,created_at,updated_at). - Avoid full table scans for frequent checks.
- Use partial indexes (Postgres) for “status = X” hot paths.
- Limit payload: return IDs, counts, and small samples.
Putting it together: a sample alert spec
Here’s a lightweight format you can adapt for any system (even if you manage alerts manually):
Alert name: Orders stuck in PROCESSING
Database: Postgres
Query: (returns stuck_count and example_order_ids)
Schedule: every 3 minutes
Trigger: stuck_count >= 5 for 2 consecutive runs
Cooldown: 30 minutes unless stuck_count increases by 2x
Notification: push + email to on-call
Runbook: internal link to "Orders stuck" dashboard and mitigation stepsHow an iOS workflow can help (without living in dashboards)
Many teams already have dashboards and logs, but dashboards don’t interrupt you when something breaks. A mobile-first workflow shines when you need quick awareness and fast triage: you get a concise notification, tap into the details (counts + examples), then decide whether to escalate or investigate.
If you’re looking for an iOS tool built around turning database queries into notifications, the query alert app approach is exactly what products like Query Alert are designed for—scheduled checks, multiple databases, and secure read-only monitoring—while keeping the focus on actionable signals rather than noisy metrics.