Docs / Connect your data

Postgres

Count rows in a Postgres table (Neon, RDS, your own server) on a schedule, and get an alert when the number changes.

In short: a scheduled job you run counts rows in a Postgres table every few minutes and sends the counts to CountWatch, zeros included. CountWatch never connects to your database. If the job stops, you get a Silence alert.

The steps below work for you or your coding agent. Your agent does every step that needs no secret. The key comes from npx countwatch init (you approve it); a Webhook URL you make under Senders (Connect), or approve for your agent.

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

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:

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 sets 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):

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

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;

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

For agents: this page as Markdown.