How to Alert on Data Changes in a Database Using Query-Based Rules

Teams often discover critical issues in their data too late: failed payments piling up, inventory dipping below safe levels, error rates spiking, or suspicious logins appearing after business hours. Traditional monitoring tools focus on infrastructure and performance metrics, but many real incidents start as data events. The practical goal is simple: create an alert on data changes in database systems that is timely, accurate, and quiet enough to be trusted.

This guide explains how to design query-based alerts for SQL and NoSQL databases, with patterns you can reuse across PostgreSQL, MySQL, and MongoDB. The emphasis is on building low-noise alerts: signals that indicate meaningful changes, not every write operation.

“The best alert is the one that reliably predicts a decision.”

Step 1: Define what “data change” actually means

Before writing queries, clarify the business meaning of a change. Most high-quality alerts fall into one of these categories:

• New records: “A new high-value order was created.”
• Status transitions: “An order moved to failed.”
• Threshold crossings: “Inventory fell below reorder point.”
• Rate changes: “Failures per minute doubled compared to baseline.”
• Data quality regressions: “Nulls in a required field increased.”
• Security anomalies: “Admin logins from a new region appeared.”

Notice what’s missing: “Any row changed.” That’s almost always too noisy. A useful alert on data changes in database setups focuses on changes that imply action.

Step 2: Choose a detection approach (polling, triggers, or CDC)

There are three common ways to detect changes. Query-based alerting usually relies on polling, but it’s worth understanding the tradeoffs.

Approach	How it works	Pros	Cons	Best for
Polling queries	Run a read-only query on a schedule	Simple, portable, low-risk	Not instant; must handle dedupe	Most alerting use cases
Database triggers	Write logic executed on insert/update/delete	Near-real-time; precise	Operational risk; adds write overhead	Strict workflow enforcement
CDC / oplog / binlog	Stream changes from logs	High fidelity; scalable streams	More complex; infra-heavy	Event pipelines, analytics platforms

If your priority is fast implementation with minimal database risk, polling read-only queries is usually the right starting point. You can still build near-real-time behavior by scheduling frequent checks (for example, every 1–5 minutes) and designing queries that detect only what matters.

Step 3: Design query rules that produce actionable results

A query-based alert should return either:

• A count (e.g., “12 failures in the last 5 minutes”), or
• A small set of rows/documents (e.g., “these 3 orders need review”).

To keep alerts stable, aim for queries that are:

• Deterministic: same inputs produce the same output.
• Indexed: filter on indexed columns/fields (timestamps, status, tenant_id).
• Windowed: evaluate “recent” changes (last N minutes) to avoid scanning history.
• State-aware: track what you already alerted on to prevent repeats.

Pattern A: Time-windowed “new problems” query (SQL)

This detects new failures in a recent window. It’s a classic building block for an alert on data changes in database workflows.

-- Example: failed payments created in the last 5 minutes
SELECT COUNT(*) AS failed_count
FROM payments
WHERE status = 'failed'
  AND created_at >= NOW() - INTERVAL '5 minutes';

Alert rule: notify when failed_count > 0 (or set a higher threshold in high-volume systems).

Make it faster: ensure an index like (status, created_at) or partial index on failed status (PostgreSQL) if appropriate.

Pattern B: Threshold crossing (SQL)

Threshold alerts are powerful because they represent a decision: reorder stock, page on-call, or block a feature flag.

-- Example: SKUs under reorder point
SELECT sku, on_hand, reorder_point
FROM inventory
WHERE on_hand <= reorder_point
ORDER BY (reorder_point - on_hand) DESC
LIMIT 20;

Alert rule: notify when the query returns any rows, and include the top offenders in the message.

Pattern C: Status transition without triggers (SQL “stateful”)

Polling alone doesn’t know what changed since the last check unless you add state. The simplest state is “last seen timestamp” or “last seen ID.”

-- Store :last_checked in your alert system.
-- Example: orders that newly became 'chargeback'
SELECT id, customer_id, amount, updated_at
FROM orders
WHERE status = 'chargeback'
  AND updated_at > :last_checked
ORDER BY updated_at ASC;

Operational note: update :last_checked after a successful run. If you want to be resilient to clock skew or delays, use a small overlap (e.g., subtract 30 seconds) and dedupe by id.

Pattern D: MongoDB aggregation for “new anomalies”

For MongoDB, an aggregation pipeline can compute counts and groupings efficiently when indexes support the match stage.

// Example: login failures per user in the last 10 minutes
db.auth_events.aggregate([
  { $match: { type: "login_failed", createdAt: { $gte: new Date(Date.now() - 10*60*1000) } } },
  { $group: { _id: "$userId", failures: { $sum: 1 } } },
  { $match: { failures: { $gte: 5 } } },
  { $sort: { failures: -1 } },
  { $limit: 20 }
]);

Alert rule: notify when any user crosses 5 failures in 10 minutes, and include the top users. This avoids alerting on every single failed login while still catching brute-force attempts.

Step 4: Prevent duplicate alerts and reduce noise

Most alert fatigue comes from duplicates: the same issue re-alerting every minute. Add one (or more) of these controls:

1. Deduplicate by entity ID (order_id, payment_id, sku). Only alert once per entity until it changes state again.
2. Use “cooldown” windows: after firing, suppress repeats for N minutes unless severity increases.
3. Alert on deltas, not totals: compare “count in last 5 minutes” vs “count in previous 5 minutes.”
4. Bucket results: group by service, tenant, or region and alert only when a bucket exceeds a threshold.

Here’s an example of a delta-style SQL query that compares adjacent windows:

-- Failures in last 5 minutes vs previous 5 minutes (PostgreSQL)
WITH w AS (
  SELECT
    SUM(CASE WHEN created_at >= NOW() - INTERVAL '5 minutes' THEN 1 ELSE 0 END) AS last_5,
    SUM(CASE WHEN created_at >= NOW() - INTERVAL '10 minutes'
              AND created_at <  NOW() - INTERVAL '5 minutes' THEN 1 ELSE 0 END) AS prev_5
  FROM payments
  WHERE status = 'failed'
    AND created_at >= NOW() - INTERVAL '10 minutes'
)
SELECT last_5, prev_5,
       CASE WHEN prev_5 = 0 THEN NULL ELSE (last_5::float / prev_5) END AS ratio
FROM w;

Alert rule: fire when last_5 exceeds a minimum (to avoid tiny denominators) and ratio is above a threshold (e.g., 2.0).

Step 5: Route alerts to the right place (and include context)

An alert should help someone act quickly. Whether you send push, email, or chat notifications, include:

• What happened: clear condition (“5+ login failures for user 123”).
• When it started: first seen time, last seen time.
• Who/what is impacted: tenant, customer, region, environment.
• Next step: link to a dashboard, runbook, or the query used.

If you have multiple severities, define them consistently (for example: informational, warning, critical). A common anti-pattern is treating every alert as urgent; that guarantees people will ignore them.

Step 6: Security and safety checklist for query-based monitoring

Monitoring should not increase risk. Use this checklist when building an alert on data changes in database process:

• Read-only credentials: grant only SELECT (and ideally only on needed tables/collections).
• Encrypted connections: require TLS; validate certificates where possible.
• Secret handling: store credentials in a secrets manager, not in source control.
• Network controls: restrict database access by IP/VPN, security groups, or private networking.
• Query limits: avoid full table scans; add time windows and LIMIT for row-returning checks.
• Auditability: log executed checks, runtime, and whether they fired (without logging sensitive payloads).

Also consider data minimization: alerts should carry just enough information to act. For example, send an order ID, not full PII fields.

Step 7: Test your alerts like production code

Alerting rules become part of your operational system. Treat them accordingly:

• Backtest: run the query against historical data to estimate firing frequency.
• Load test: ensure the query remains fast at peak scale (check indexes and query plans).
• Failure modes: decide what happens if the database is down—do you alert on “no data,” retry, or both?
• Ownership: assign each alert to a team and attach a runbook link.

A practical habit: maintain a small “alert catalog” documenting each rule’s purpose, threshold, query, expected frequency, and responder.

Putting it all together: a simple blueprint

If you’re starting from scratch, here’s a reliable implementation order:

1. Pick one high-value event (e.g., failed payments).
2. Write a time-windowed query that returns a count.
3. Add a sensible threshold and a cooldown.
4. Include context (top failing categories, IDs, or regions).
5. Harden security (read-only user, TLS, secret storage).
6. Iterate: reduce noise, add state, add deltas.

Over time, you can expand to multiple databases and checks while keeping the same principle: each alert represents a decision you’d be unhappy to miss.

Gentle note: if you prefer managing scheduled, read-only query checks from an iOS workflow with push/email delivery, tools like Query Alert are designed around this exact query-to-notification pattern.