Alert when signups drop: SQL for signup and payment checks

The problem

A signup form breaks on a Friday release. Nothing throws an error that anyone sees. Traffic is normal, the dashboards keep rendering, and by Monday you’ve lost a weekend of signups. Payments have a quieter version: the webhook that records them times out, or one provider change starts writing null amounts, and revenue looks fine until somebody reconciles it.

Both are the same failure. A number went to a wrong value and nothing was watching it. Four checks cover most of it: freshness, volume, validity, and agreement between tables. This page assumes signups and payments tables in a SQL database arcodash can read.

Volume: did signups drop

Yesterday’s signups as a percent of the median daily count over the previous 14 complete days. Alert when the value is < 50. A day with zero signups returns 0, which also triggers.

WITH daily AS (
  SELECT created_at::date AS d, count(*) AS n
  FROM signups
  WHERE created_at >= current_date - 14 AND created_at < current_date
  GROUP BY 1
), base AS (
  SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY n) AS med FROM daily
)
SELECT
  (SELECT count(*) FROM signups
   WHERE created_at >= current_date - 1 AND created_at < current_date) AS yesterday,
  round(med::numeric, 0) AS median_14d,
  round((100.0 * (
    SELECT count(*) FROM signups
    WHERE created_at >= current_date - 1 AND created_at < current_date
  ) / nullif(med, 0))::numeric, 0) AS pct_of_normal
FROM base;

The monitor watches the pct_of_normal column; the other two columns are there for whoever opens the query. Schedule it once a day shortly after midnight in your database’s time zone; running it more often just repeats the same answer. If you want an earlier warning, count the last 24 hours instead and expect more noise.

Freshness: did the feed stop

Hours since the last signup. This catches a dead form or a stalled sync faster than the daily volume check, since it runs every few minutes. The threshold depends on your traffic: if you get a signup every 20 minutes during the day, > 6 is generous, but a B2B product with 5 a day overnight needs > 24.

SELECT coalesce(
  round((extract(epoch FROM now() - max(created_at)) / 3600)::numeric, 1),
  9999
) AS hours_since_last_signup
FROM signups;

Use the same shape on payments for hours_since_last_payment. For a table written by a pipeline, prefer a load timestamp over an event time; data freshness explains why.

Validity: are payments well-formed

Count nulls and non-positive amounts in one pass. Two columns, two monitors, both > 0. Refunds that you store as negative rows will trigger this, so filter them out first if you do that.

SELECT
  count(*) FILTER (WHERE amount_cents IS NULL) AS null_amounts,
  count(*) FILTER (WHERE amount_cents <= 0)    AS non_positive_amounts
FROM payments;

Duplicates are the other classic. A retried webhook writes the same payment twice and revenue doubles.

SELECT count(*) AS duplicate_payment_ids
FROM (SELECT payment_id FROM payments GROUP BY payment_id HAVING count(*) > 1) t;

Agreement: payments with no signup

A payment for a user id that isn’t in signups means one side of the sync is behind or wrong. Alert on > 0.

SELECT count(*) AS orphan_payments
FROM payments p
LEFT JOIN signups s ON s.id = p.user_id
WHERE s.id IS NULL;

How arcodash sets it up

Each query is a saved query. Put it on a schedule, publish it, then add a monitor: pick the column, an operator and a value. A monitor reads the first row of the published result, which is why every query above returns one row. When the state flips from ok to triggered, subscribers get a notification on email, Slack, Teams, a webhook or PagerDuty. Setup details are in alerts overview and data observability monitors.

To avoid hand-picking the volume threshold, make the monitor a baseline monitor on a daily count instead. It needs 7 observations before it flags anything and has no weekday model, so on a strongly weekly signup pattern the percent-of-median query above is the better tool.

When one fires, the alert carries a statistics line (new value against the recent median). With an AI provider configured, arcodash adds a short explanation built from up to three read-only queries on the same data source. For a signup drop, that’s the thing that says which plan or country fell. For doing that investigation by hand, see why did this metric drop.

See it on a live dashboard

The Payments Pipeline demo is a public dashboard on synthetic data. When I opened it on 12 October 2026 it showed:

• A chart titled signups and active workspaces by day, with a signups series.
• A freshness tile reading OK at 16.7 hours since the last payment, against a limit of 24.
• Two tiles in the alerting state: duplicate payment ids (1) and null payment amounts (1), each with a tooltip showing the crossing and the 30-check median.
• A negative-amounts tile reading OK at 0.
• A payments and revenue by day chart.

The data is made up and deliberately includes a duplicate and a null so the tiles have something to show. It’s a read-only public link, which is a feature any dashboard has; see public links.

Honest limits

• Percent of the median can’t see a drop shaped like your normal low day. Seasonality, holidays and launches move the baseline.
• It tells you signups fell, not why. A real marketing lull and a broken form look identical to the query.
• If signups live in a third-party tool arcodash has no connector for, you need that data copied into a database first.
• A monitor that stays triggered won’t re-notify on its own. Use escalation for a reminder.

Start with the freshness and volume queries on one table. If they stay quiet for two weeks, add the payments checks. arcodash has a 14 day free trial if you want to try them against your own database.

The reconciliation check entry covers comparing two sources, which is the next step for payments.