Docs / Connect your data

ClickHouse

Count rows in a ClickHouse table on a schedule, and get an alert when the number changes.

In short: a scheduled job counts rows in a ClickHouse table every few minutes and sends the counts to CountWatch, zeros included. If the job stops, you get a "stopped" alert.

The detailed steps below work for you or your coding agent. Your agent can do every step that needs no secret; anything secret, such as a webhook URL or a key, you make in the dashboard and paste yourself.

The general method "scheduled query → ingest" for a count kept in ClickHouse (orders, sign-ups, events). A cron job runs one query over ClickHouse's HTTP interface and sends the result to CountWatch; nothing runs inside ClickHouse. Needs curl, jq, a ClickHouse user that can read the table, and a write key. The example counts paid orders in orders; replace the table, its time column created_at and the status filter. This query runs in CI on the official ClickHouse image.

1. The query

Save as countwatch.sql:

WITH toStartOfFiveMinutes(now('UTC')) AS until
SELECT 'orders' AS metric, formatDateTime(bucket, '%Y-%m-%dT%H:%i:%SZ', 'UTC') AS ts, toUInt32(n) AS set
FROM (
  SELECT toStartOfFiveMinutes(created_at) AS bucket, count() AS n
  FROM orders
  WHERE created_at >= until - INTERVAL 2 HOUR AND created_at < until AND status = 'paid'
  GROUP BY bucket
  ORDER BY bucket WITH FILL FROM until - INTERVAL 2 HOUR TO until STEP INTERVAL 5 MINUTE
)
FORMAT JSONEachRow

• Every bucket, zeros included. WITH FILL adds the empty 5-minute buckets as 0, so the watch (source explicit) can tell "no orders" from "the job stopped" (the no-data alert).
• UTC. formatDateTime(bucket, …, 'UTC') writes each bucket's start in UTC whatever the column's time zone, and now('UTC') sets the window.
• The last 2 hours, complete buckets only. Each point sets its bucket, so the overlap between runs only rewrites the same totals, and rows that arrive late are picked up by the next run.

2. Send it on a schedule

Save as countwatch.sh (bash):

# For a scheduler or a person at a terminal, never an agent's shell: it holds a source secret and calls CountWatch.
( # a subshell: pasted into a terminal, a failure stops only this block
set -euo pipefail
# A failed query stops here (nothing is sent), and the scheduler sees the error.
points=$(curl -sS --fail-with-body "$CLICKHOUSE_URL" -H "X-ClickHouse-User: $CLICKHOUSE_USER" -H "X-ClickHouse-Key: $CLICKHOUSE_PASSWORD" --data-binary @countwatch.sql | jq -sc '{points: .}')
curl -sS --fail-with-body -X POST https://staging.countwatch.com/v1/ingest -H "Authorization: Bearer $COUNTWATCH_API_KEY" -H "Content-Type: application/json" --data-binary "$points"
)

CLICKHOUSE_URL is the HTTP interface (port 8443 over HTTPS on ClickHouse Cloud, 8123 on a server of your own). Schedule it every 60 minutes on Free accounts (crontab 41 * * * *, with your own minute between 5 and 54 so that not every sender runs at once), every 5 minutes on Pro (*/5 * * * *), with a scheduler that lives as long as the database (cron on a server, a CI schedule), and start its first run there (for a CI schedule, gh workflow run), or a person runs bash countwatch.sh once: the first send creates the watch. An agent writes these files and the schedule, and never runs them in its own shell.

3. Import 89 days (once, right after the first send, where the send runs)

Save as countwatch-import.sql:

WITH toStartOfFiveMinutes(now('UTC')) - INTERVAL 2 HOUR AS until, until - INTERVAL 89 DAY AS since
SELECT 'orders' AS metric, '5m' AS resolution, formatDateTime(since, '%Y-%m-%dT%H:%i:%SZ', 'UTC') AS start,
       arrayMap(x -> toUInt32(x.2), arraySort(groupArray((bucket, n)))) AS counts
FROM (
  SELECT toStartOfFiveMinutes(created_at) AS bucket, count() AS n
  FROM orders
  WHERE created_at >= since AND created_at < until AND status = 'paid'
  GROUP BY bucket
  ORDER BY bucket WITH FILL FROM since TO until STEP INTERVAL 5 MINUTE
)
FORMAT JSONEachRow

# For a scheduler or a person at a terminal, never an agent's shell: it holds a source secret and calls CountWatch.
( # a subshell: pasted into a terminal, a failure stops only this block
set -euo pipefail
body=$(curl -sS --fail-with-body "$CLICKHOUSE_URL" -H "X-ClickHouse-User: $CLICKHOUSE_USER" -H "X-ClickHouse-Key: $CLICKHOUSE_PASSWORD" --data-binary @countwatch-import.sql | jq -c '{watches: [.]}')
curl -sS --fail-with-body -X POST https://staging.countwatch.com/v1/import -H "Authorization: Bearer $COUNTWATCH_API_KEY" -H "Content-Type: application/json" --data-binary "$body"
)

The import is real 5-minute history (25,632 buckets), zeros included, and ends where the scheduled sends begin (2 hours back), so it never replaces a bucket the sends own. It answers 202 and an import_id.

Check it works

1. Data arrives. check_setup (or curl -s https://staging.countwatch.com/v1/status -H "Authorization: Bearer $COUNTWATCH_API_KEY"): the orders watch shows last_request_at, and missing_buckets_24h stays 0 (explicit watches). Do what next_actions says.
2. History is in. GET /v1/imports/{import_id} shows status: done and the rules chosen for orders, with the alerts they would have raised. (Skipped the import? The watch learns for about 3 weeks; starter rules apply meanwhile.)
3. An alert reaches you. The person turns on e-mail alerts in the dashboard (https://staging.countwatch.com/dashboard), or you add a webhook (manage add_destination, then verify_destination; the endpoint answers the challenge, see https://staging.countwatch.com/agent). Then manage {"action": "send_test_alert"}: every active destination receives a test alert (test: true) within a few minutes.

For agents: this page as Markdown.