“Data alerts” sound simple: run a query, if something looks wrong, notify someone. In practice, most teams either get no alerts (because nobody finished the setup) or too many alerts (because the rules aren’t tied to actions). This guide shows how to design database-driven alerts that are accurate, low-noise, secure, and easy to operate—whether your source is SQL (PostgreSQL/MySQL) or NoSQL (MongoDB).
The goal is not to monitor everything. It’s to catch the few data conditions that reliably indicate risk: failed payments, stuck workflows, exploding error rates, missing jobs, inventory going negative, or critical tables drifting from expected values.
What “data alerts” are (and what they aren’t)
Data alerts are notifications triggered by query results that represent a meaningful change or condition in your data. They complement infrastructure monitoring by telling you when the business reality in your database has shifted.
- They are not generic dashboard charts or hourly reports.
- They are small, testable rules that return a clear “OK vs. needs attention” signal.
- They work best when every alert maps to an owner and an action.
Rule of thumb: if an alert fires and the responder can’t do anything within 10 minutes, it’s not an alert—it’s telemetry.
Start with alert intent: the action comes first
Before writing any query, write a one-sentence intent statement:
- Condition: What is the data state you want to detect?
- Impact: Why does it matter (customer impact, money, compliance, SLA)?
- Action: What should someone do when it triggers?
- Owner: Who is responsible for fixing or escalating?
Example intent statement:
“If more than 20 orders are stuck in ‘processing’ for over 30 minutes, notify on-call so they can check the worker queue and restart the failed job.”
Choose the right alert pattern
Most query-based alerts fit into a few patterns. Pick the one that matches your data and operational goals.
| Pattern | Best for | Typical query output | Noise risk |
|---|---|---|---|
| Threshold | Counts, error rates, backlog size | Single number | Medium (needs tuning) |
| Change detection | Status transitions, new critical records | Rows (new/changed items) | Low if deduped |
| Missing data / heartbeat | Scheduled jobs, ETL freshness, integrations | 0 vs. expected | Low |
| Anomaly vs. baseline | Seasonal metrics, traffic-dependent KPIs | Deviation score | Medium-high (requires context) |
Design queries that are stable and cheap
The best data alerts have queries that are:
- Read-only (no side effects).
- Deterministic (same inputs, same outputs).
- Index-friendly (avoid full scans on hot tables).
- Time-bounded (filter to a recent window).
- Explicit about timezone and time window boundaries.
SQL example: “stuck orders” threshold alert
This query counts orders that have been in a state too long, scoped to a recent horizon to keep it fast.
-- Postgres / MySQL-style SQL
SELECT COUNT(*) AS stuck_count
FROM orders
WHERE status = 'processing'
AND updated_at < NOW() - INTERVAL '30 minutes'
AND updated_at > NOW() - INTERVAL '24 hours';Alert rule idea: trigger if stuck_count >= 20 for two consecutive runs (see “debouncing” below).
SQL example: “missing heartbeat” for a scheduled job
This detects when a job hasn’t reported success recently.
SELECT 1
FROM job_runs
WHERE job_name = 'billing-sync'
AND status = 'success'
AND finished_at > NOW() - INTERVAL '15 minutes'
LIMIT 1;Alert rule idea: trigger if the query returns no rows.
MongoDB example: “new critical errors” change detection
For MongoDB, an aggregation can find recent high-severity events:
// MongoDB aggregation (conceptual)
db.events.aggregate([
{ $match: {
severity: "critical",
createdAt: { $gte: new Date(Date.now() - 10*60*1000) }
}},
{ $group: {
_id: "$service",
count: { $sum: 1 },
sampleIds: { $addToSet: "$_id" }
}},
{ $match: { count: { $gte: 5 } } }
]);Alert rule idea: notify if any service crosses 5 critical events in 10 minutes.
Reduce noise with four practical techniques
1) Debounce (require persistence)
Single blips cause most false positives. Require the condition to be true for N consecutive checks or for M minutes. Example: “stuck_count ≥ 20” for 2 runs spaced 5 minutes apart.
2) Use severity tiers
Create tiers that match urgency:
- Info: log-only or email digest
- Warning: notify during business hours
- Critical: push + paging channel
3) Deduplicate by entity
When alerts return rows (e.g., stuck orders), don’t notify every run for the same item. Instead, key by a stable identifier (order_id) and only alert when an entity first enters the “bad” set, or when it crosses a “worse” threshold (e.g., 30 minutes → 2 hours).
4) Add context fields to the result
Even for threshold alerts, return small context that speeds triage (top offenders, last updated time, shard/region, etc.). Example:
SELECT status,
COUNT(*) AS cnt,
MIN(updated_at) AS oldest,
MAX(updated_at) AS newest
FROM orders
WHERE status IN ('processing', 'payment_pending')
AND updated_at < NOW() - INTERVAL '30 minutes'
GROUP BY status
ORDER BY cnt DESC;Scheduling: interval, window, and overlap
A common mistake is checking too frequently with too wide a window. Instead:
- Pick an interval based on how fast you can respond (e.g., 1–5 minutes for on-call, 15–60 minutes for ops).
- Pick a lookback window that balances completeness and cost (e.g., “last 10 minutes” or “last 24 hours” depending on the use case).
- Use overlap if the source timestamps can arrive late. For example, check every 5 minutes but look back 15 minutes to tolerate delays—then deduplicate results.
Security and access: keep monitoring safe
Query-based monitoring touches production data, so treat it like production access:
- Use a dedicated read-only user with the narrowest permissions possible.
- Prefer encrypted connections (TLS) and verify certificates where supported.
- Do not store secrets in scripts; use a secret manager or platform keychain.
- Limit data returned: avoid selecting sensitive columns when a count or ID list is sufficient.
- Audit access: log query runs and where notifications are sent.
Testing an alert before you trust it
Use a lightweight checklist:
- Backtest: run the query against historical time ranges to see how often it would have triggered.
- Load test: confirm it uses indexes and doesn’t lock hot tables.
- Failure modes: decide what happens if the database is unreachable. (Often: alert separately on connectivity, and suppress downstream noisy alerts.)
- Runbook: write 3–5 steps for responders: where to look, what to restart, who to escalate to.
A mini playbook: building actionable data alerts in a week
Day 1–2: pick 5 high-signal conditions
- Stuck workflow states (orders, approvals, shipments)
- Payment failures above baseline
- Jobs/ETL freshness (missing heartbeat)
- Inventory or balance going negative
- Error/event spikes per service
Day 3–4: implement queries + thresholds
Start with conservative thresholds (fewer alerts), then tighten after a week of observation. Add debouncing and entity dedupe early.
Day 5: wire routing + runbooks
Route by ownership (payments → finance/checkout; fulfillment → ops). Add a short runbook link or text snippet in the notification payload.
Day 6–7: tune and reduce false positives
Review every trigger: Did it help? Was the action clear? Adjust windows, add context, and lower severity where appropriate.
Common pitfalls (and how to avoid them)
- Alerting on averages only: averages hide outliers. Add “max age” or percentile checks where possible.
- No ownership: if nobody owns it, it will be ignored. Set an owner per alert.
- Hardcoding business hours: instead, use severity tiers and routing schedules.
- Returning too much data: keep payloads small; include IDs + summary fields, not entire records.
- One-size-fits-all thresholds: segment by region, service, or customer tier when behavior differs.
Quick checklist: an alert worth keeping
- Uses a read-only query and minimal permissions
- Runs fast (bounded window, indexed filters)
- Has a defined owner and runbook
- Includes debouncing and deduplication
- Provides context (what changed, how much, since when)
- Triggers at the right severity level
Closing thoughts
Well-designed data alerts turn your database into an early-warning system for real customer and business impact. Start with a handful of high-signal conditions, make them cheap and secure, and iterate based on real incidents—not guesses. If you prefer managing these alerts from a mobile workflow, an iOS app like Query Alert can help you run read-only checks and receive notifications without maintaining custom polling scripts.