Data Change Alerts That Matter: Patterns, SQL, and Mobile Workflows

Published Dec 30, 2025

Design reliable data change alerts with SQL, CDC, and streams. Patterns, code, and mobile workflows to detect and act on critical changes.

Data Change Alerts That Matter: Patterns, SQL, and Mobile Workflows

When high-value records change—an order status flips to refunded, a user’s role escalates, an inventory count hits zero—you need to know fast. Well-designed data change alerts turn those moments into actionable signals for engineering, product, support, and on-call responders. This guide walks through proven detection patterns across SQL and NoSQL databases, trade-offs between polling and event-driven methods, and practical code examples. You’ll also learn how to build mobile-friendly alert flows that reduce noise and improve response time.

What Are Data Change Alerts?

Data change alerts notify you when database state changes meet specific conditions. The goal isn’t just “anything changed”—it’s meaningful change: anomalies, thresholds, SLA breaches, or business events worth human attention. Good alerts are timely, deduplicated, secure, and easy to route.

Core Detection Approaches

There are four common ways to detect changes. Choose based on latency requirements, complexity, and infrastructure constraints.

1) Scheduled Queries (Polling)

  • How it works: Run read-only queries at intervals (e.g., every minute) to find rows that match alert conditions.
  • Pros: Simple, portable across databases; safe with least-privilege read access; easy to reason about.
  • Cons: Can miss events without careful watermarks; has query cost; not sub-second.

2) Database Triggers + Outbox Table

  • How it works: Triggers write critical row changes into an “outbox” table; an alert worker consumes and sends notifications.
  • Pros: Near-real-time; transactional consistency; good audit trail.
  • Cons: Requires write privileges and schema changes; operational complexity.

3) Change Data Capture (CDC)

  • How it works: Stream row-level changes from database logs (e.g., Postgres logical decoding, MySQL binlog, Debezium) into a consumer that evaluates rules.
  • Pros: Low latency; scalable; rich change metadata; decouples alert rules.
  • Cons: Setup overhead; operational tooling; sometimes requires infra you don’t control.

4) Native Change Streams / Notifications

  • How it works: Use built-in features like MongoDB Change Streams or Postgres LISTEN/NOTIFY.
  • Pros: Native, often low-latency; fewer moving parts than full CDC.
  • Cons: Vendor-specific; may need persistent consumers; LISTEN/NOTIFY payloads are small.

Method Comparison

Method Latency Complexity Read-Only Portability Cost Considerations
Scheduled queries Seconds–minutes Low Yes High Query cost; indexing needed
Triggers + outbox Near-real-time Medium No Medium Write path overhead
CDC (e.g., Debezium) Near-real-time High Usually Medium Infra + stream processing
Change Streams / NOTIFY Near-real-time Medium Depends Low–Medium Persistent consumers

Designing Alert Rules That Avoid Noise

  • Use watermarks: Track the last processed timestamp or ID to avoid duplicates and gaps.
  • Windowing: Evaluate conditions over a time window (e.g., last 5 minutes) to reduce flapping.
  • Severity tiers: Info, warning, critical—route each differently.
  • Rate limiting: Suppress repeats within a fixed window and use stateful alerts (send on open and resolve transitions).
  • Enrichment: Include key fields (user, order, region) and runbook links to speed resolution.
  • Business hours vs on-call: Respect quiet times; only critical alerts overnight.

SQL Patterns for Data Change Alerts

These examples assume strongly indexed tables. Always create covering indexes for the filter columns referenced in WHERE clauses and join keys.

1) New High-Value Orders

Goal: Alert when a new order exceeds $500. Use a watermark to avoid re-alerting.

-- Postgres
-- State table to track last seen order id
CREATE TABLE IF NOT EXISTS monitor_state (
  key text PRIMARY KEY,
  last_seen_id bigint NOT NULL
);

-- Query new high-value orders since last_seen_id
WITH last_cursor AS (
  SELECT COALESCE((SELECT last_seen_id FROM monitor_state WHERE key = 'orders_high_value'), 0) AS cursor
)
SELECT id, user_id, total_amount, created_at
FROM orders, last_cursor
WHERE id > cursor AND total_amount >= 500
ORDER BY id ASC
LIMIT 100;

-- After sending alerts, advance the watermark in a transaction:
-- UPDATE monitor_state SET last_seen_id = <max_id_returned> WHERE key = 'orders_high_value';

-- Helpful index
CREATE INDEX IF NOT EXISTS idx_orders_total_amount_id ON orders(total_amount, id);

2) Spike in Failed Payments

Compare the last 5 minutes to a 7-day moving average per hour to catch anomalies.

-- Postgres anomaly detection (simplified)
WITH recent AS (
  SELECT COUNT(*) AS c
  FROM payments
  WHERE status = 'FAILED'
    AND created_at >= NOW() - INTERVAL '5 minutes'
),
baseline AS (
  SELECT COALESCE(AVG(c), 0) AS c
  FROM (
    SELECT date_trunc('hour', created_at) AS bucket, COUNT(*) AS c
    FROM payments
    WHERE status = 'FAILED'
      AND created_at >= NOW() - INTERVAL '7 days'
    GROUP BY 1
  ) x
)
SELECT r.c AS recent, b.c AS baseline,
       (r.c > (b.c * 3)) AS is_spike;

-- Index for performance
CREATE INDEX IF NOT EXISTS idx_payments_status_created_at ON payments(status, created_at);

3) MySQL Version (Moving Window)

-- MySQL 8+
SELECT
  SUM(status = 'FAILED' AND created_at >= NOW() - INTERVAL 5 MINUTE) AS recent,
  AVG(cnt) AS baseline,
  SUM(status = 'FAILED' AND created_at >= NOW() - INTERVAL 5 MINUTE) > 3 * AVG(cnt) AS is_spike
FROM (
  SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS bucket,
         SUM(status = 'FAILED') AS cnt
  FROM payments
  WHERE created_at >= NOW() - INTERVAL 7 DAY
  GROUP BY 1
) t;

-- Index
CREATE INDEX idx_payments_status_created_at ON payments(status, created_at);

4) Deduplication and “Resolve” Signals

Persist alert state to only notify on transitions.

-- A minimal alert state table
CREATE TABLE IF NOT EXISTS alert_state (
  key text PRIMARY KEY,
  is_open boolean NOT NULL,
  opened_at timestamptz,
  closed_at timestamptz
);

-- App logic:
-- if condition becomes true and is_open = false, send "OPEN" and set is_open = true
-- if condition becomes false and is_open = true, send "RESOLVED" and set is_open = false

NoSQL Patterns

MongoDB Change Streams

Change Streams provide low-latency alerts without polling.

// Node.js example
const { MongoClient } = require('mongodb');

async function run() {
  const client = new MongoClient(process.env.MONGODB_URI);
  await client.connect();
  const coll = client.db('shop').collection('orders');

  const pipeline = [
    { $match: { 'fullDocument.status': 'REFUNDED' } }
  ];

  const cs = coll.watch(pipeline, { fullDocument: 'updateLookup' });
  cs.on('change', evt => {
    // send alert with evt.fullDocument
  });
}
run();

Indexed Polling Fallback

If Change Streams aren’t available, use an indexed timestamp.

// Find new or modified documents since a stored watermark
const last = new Date(process.env.LAST_WATERMARK);
const cursor = db.orders.find({ updatedAt: { $gt: last }, total: { $gte: 500 } })
                       .sort({ updatedAt: 1 })
                       .limit(200);
// After processing, advance watermark to the max updatedAt seen

// Ensure index
// db.orders.createIndex({ updatedAt: 1, total: 1 })

Delivery: Push, Email, and Webhooks

  • Push notifications: Fast; great for on-call mobile responders; limit payload size and include deep links.
  • Email: Good for less urgent summaries; include screenshots, CSVs, and runbook links.
  • Webhooks: Best for automation (create Jira tickets, Slack threads, or PagerDuty incidents).

Security and Performance Essentials

  • Read-only roles: Grant the minimal SELECT on specific tables or views; avoid superuser credentials.
  • Network encryption: Enforce TLS for database connections; verify server certificates.
  • Parameterized queries: Avoid string concatenation to prevent injection in dynamic filters.
  • Index wisely: Composite indexes on filter columns plus time/ID; validate with EXPLAIN/EXPLAIN ANALYZE.
  • Bounded scans: Use LIMIT and watermarks; prefer monotonic IDs or timestamps.
  • Backoff and jitter: If a query is slow, back off to protect production workloads.
  • Audit & rotation: Rotate credentials; store secrets in a vault.

Mobile-Friendly Workflows

  • Actionable payloads: Include IDs, status, severity, and a deep link into an internal dashboard or mobile app route.
  • One-tap triage: Buttons for “Acknowledge”, “Create Incident”, and “Mute 1h” reduce friction.
  • Quiet hours: Respect user preferences; only critical alerts break through at night.
  • Summaries: Batch low-severity signals into hourly digests to cut noise.
  • Offline resilience: Cache the last N alerts locally; show status while reconnecting.

Putting It Together: A Lightweight Polling Worker

Here’s a minimal Python example that checks Postgres for high-value orders and sends a webhook. It uses watermarks and parameterized queries.

# pip install psycopg2-binary requests
import os, requests, psycopg2

DSN = os.environ['PG_DSN']
WEBHOOK_URL = os.environ['WEBHOOK_URL']

SQL_SELECT = '''
WITH last_cursor AS (
  SELECT COALESCE(
    (SELECT last_seen_id FROM monitor_state WHERE key = %s), 0
  ) AS cursor
)
SELECT id, user_id, total_amount, created_at
FROM orders, last_cursor
WHERE id > cursor AND total_amount >= %s
ORDER BY id ASC
LIMIT 200;'''

SQL_UPSERT = '''
INSERT INTO monitor_state(key, last_seen_id)
VALUES(%s, %s)
ON CONFLICT (key) DO UPDATE SET last_seen_id = EXCLUDED.last_seen_id;'''

KEY = 'orders_high_value'
THRESHOLD = 500

with psycopg2.connect(DSN) as conn:
    with conn.cursor() as cur:
        cur.execute(SQL_SELECT, (KEY, THRESHOLD))
        rows = cur.fetchall()
        if rows:
            max_id = 0
            for r in rows:
                payload = {
                    'type': 'ORDER_HIGH_VALUE',
                    'id': r[0], 'user_id': r[1], 'total': float(r[2]),
                    'created_at': r[3].isoformat()
                }
                requests.post(WEBHOOK_URL, json=payload, timeout=5)
                max_id = max(max_id, r[0])
            cur.execute(SQL_UPSERT, (KEY, max_id))

Edge Cases to Consider

  • Clock skew: If multiple systems generate timestamps, prefer database server time (e.g., NOW()) and IDs as watermarks.
  • Long transactions: Rows may appear with old timestamps; a cursor by ID is more reliable than time-based only.
  • Reprocessing: Build idempotency into your delivery (e.g., de-dup keys in your alert sink).
  • Schema changes: Version your queries or use views; monitor for query errors.
  • Backfills: Prevent floods during backfills by temporarily raising thresholds or disabling certain alerts.

Operational Tips for Maintainable Alerts

  • Runbooks: Every alert should link to a clear diagnosis and action plan.
  • SLOs for alerts: Measure time-to-detect and time-to-notify; track false positive rate.
  • Integration tests: Seed synthetic changes that must produce alerts; verify end-to-end delivery.
  • Ownership: Assign a team and rotation; stale alerts create noise debt.
  • Review cadence: Quarterly tune thresholds, windows, and routing rules based on postmortems.

Choosing the Right Approach

If you need portability and low setup cost, start with scheduled queries plus strong indexing and stateful deduplication. For higher throughput and near-real-time latency, adopt CDC or Change Streams. For critical transactional integrity, triggers with an outbox table can work well when you control the schema. Across all methods, emphasize idempotency, security, and actionability to keep your data change alerts useful instead of noisy.

Note: If you prefer a lightweight mobile-first approach, Query Alert is an iOS app that connects read-only to Postgres, MongoDB, and MySQL, schedules checks, and delivers push or email, helping you turn queries into notifications without building infrastructure.