# Watch a ClickHouse query

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`:

```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 `set`s 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):

```sh
# 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`:

```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
```

```sh
# 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.