# Watch a Postgres query

The general method "scheduled query → ingest" for a count kept in Postgres: Neon, Amazon RDS or Aurora, Google Cloud SQL, Azure Database for PostgreSQL, or a server of your own. A cron job or a CI schedule you run counts with one query (`psql`) and sends the result to CountWatch; CountWatch never connects to your database and never holds its password. On Supabase, use the [Supabase recipe](https://countwatch.com/docs/recipes/supabase.md) instead: its job runs inside the database. Needs `psql` and `curl`, Postgres 14 or later (`date_bin`), a database user that can only 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. An index on the time column keeps the query cheap. This query runs in CI on a real Postgres.

## 1. A read-only user (once)

The person runs this as the database owner and keeps the password for the scheduler's secrets:

```sql
create role countwatch_reader login password '<a new password>';
grant select on orders to countwatch_reader;
```

`DATABASE_URL` is that user's connection string (`postgresql://countwatch_reader:<password>@<host>/<database>?sslmode=require`; Neon, RDS and Cloud SQL show the host). The scheduler must reach the database: a CI schedule needs a database open to the internet over TLS; for one in a private network, run cron on a machine inside it.

## 2. The query

Save as `countwatch.sql`:

```sql
with span as (
  select date_bin('5 minutes', now(), timestamptz '2000-01-01 00:00:00+00') as until),
counted as (
  -- One pass over the rows: each paid order into its 5-minute window (UTC: the origin is a UTC instant).
  select date_bin('5 minutes', o.created_at, timestamptz '2000-01-01 00:00:00+00') as window_start, count(*) as n
  from orders o, span
  where o.created_at >= span.until - interval '2 hours' and o.created_at < span.until and o.status = 'paid'
  group by 1)
select json_build_object('recipe', 'postgres', 'points', json_agg(json_build_object(
  'metric', 'orders',
  'ts', to_char(b at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
  'set', coalesce(c.n, 0)) order by b))
from span
cross join lateral generate_series(span.until - interval '2 hours', span.until - interval '5 minutes', interval '5 minutes') as b
left join counted c on c.window_start = b;
```

- **Every 5-minute window, zeros included.** `generate_series` lists all 24 windows and an empty one is `0`, so the watch (source `explicit`) can tell "no orders" from "the job stopped" (the no-data alert).
- **UTC.** `date_bin` from a UTC origin and `at time zone 'UTC'` write each 5-minute window's start in UTC whatever the session's time zone. If `created_at` is `timestamp` without a time zone and holds UTC times, write `(o.created_at at time zone 'UTC')` for `o.created_at`.
- **The last 2 hours, complete 5-minute windows only.** Each point `set`s its 5-minute window, so the overlap between runs only rewrites the same totals, and rows that arrive late are picked up by the next run.

## 3. 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.
body=$(psql "$DATABASE_URL" -At -v ON_ERROR_STOP=1 -f countwatch.sql)
curl -sS --fail-with-body -X POST https://countwatch.com/v1/ingest -H "Authorization: Bearer $COUNTWATCH_API_KEY" -H "Content-Type: application/json" --data-binary "$body"
)
```

Schedule it every 5 minutes (crontab `*/5 * * * *`), with a scheduler that lives as long as the database (cron on a server, a CI schedule), `DATABASE_URL` and `COUNTWATCH_API_KEY` in its secrets, 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, or goes to the watch you made first (create_watch). An agent writes these files and the schedule, and never runs them in its own shell.

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

Save as `countwatch-import.sql`:

```sql
with span as (
  select date_bin('5 minutes', now(), timestamptz '2000-01-01 00:00:00+00') - interval '2 hours' as until),
counted as (
  select date_bin('5 minutes', o.created_at, timestamptz '2000-01-01 00:00:00+00') as window_start, count(*) as n
  from orders o, span
  where o.created_at >= span.until - interval '89 days' and o.created_at < span.until and o.status = 'paid'
  group by 1)
select json_build_object('recipe', 'postgres', 'watches', json_build_array(json_build_object(
  'metric', 'orders', 'resolution', '5m',
  'start', to_char((span.until - interval '89 days') at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
  'counts', (select json_agg(coalesce(c.n, 0) order by b)
             from generate_series(span.until - interval '89 days', span.until - interval '5 minutes', interval '5 minutes') as b
             left join counted c on c.window_start = b))))
from span;
```

```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=$(psql "$DATABASE_URL" -At -v ON_ERROR_STOP=1 -f countwatch-import.sql)
curl -sS --fail-with-body -X POST https://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 five-minute windows), zeros included, and ends where the scheduled sends begin (2 hours back), so it never replaces a 5-minute window the sends own. It answers `202` and an `import_id`.

## Check it works

1. **Data arrives.** `check_setup` (or `curl -s https://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 from live data; starter rules apply meanwhile.)
3. **An alert reaches you.** The person turns on e-mail alerts in the dashboard (Destinations (Connect), https://countwatch.com/dashboard/connections?tab=destinations), or you add a webhook (`manage create_destination`, then `verify_destination`; the endpoint answers the challenge, see https://countwatch.com/agent). A write key cannot read alerts: read them with MCP `alerts`, or with `GET /v1/alerts` and an admin key. Then `manage {"action": "send_test_alert"}`: every active destination receives a test alert (`test: true`) within a few minutes. Real alerts go only where the watch's group chose (Alerts go to, or `manage update_group` with `destination_ids`); until then they stay in the app.