# CountWatch recipe: Supabase orders

Counts paid orders per 5 minutes with a query that runs inside your Supabase database (pg_cron schedules it, pg_net sends it) and alerts when orders spike, drop or stop. Nothing to deploy. Replace `orders`, `created_at` and `status = 'paid'` with your table, its timestamp column and what "an order" means. An index on the timestamp column keeps the query cheap.

The person runs each step in the Supabase SQL editor: every step runs inside the database, so nothing outside it needs the database password. In step 1 they paste a write key they make in the dashboard (Settings, https://staging.countwatch.com/dashboard/settings) where it says `<COUNTWATCH_API_KEY>`; the agent never sees it.

**With the Supabase MCP server** (Supabase dashboard → Connect → MCP; `claude mcp add` with the URL it shows, then authenticate): after the person has run step 1 in the SQL editor (it puts the token in Vault, so the token never passes through the agent), an agent runs steps 2 and 3 with its `execute_sql` tool, one statement block at a time, only when the person's request names this job (for example "install the CountWatch sender in my Supabase database"): the job sends the database's counts out every hour. No connection string or database password is needed. A permission system may still refuse it; then the agent writes the steps to `countwatch/NEXT.md` for the person.

## 1. Extensions and the key

```sql
create extension if not exists pg_cron;
create extension if not exists pg_net;
-- The write key only sends data. Vault keeps it encrypted at rest.
select vault.create_secret('<COUNTWATCH_API_KEY>', 'countwatch_api_key');
```

## 2. Send the last hour's 12 buckets, every hour

```sql
-- Runs as its owner (to read Vault) with a fixed search_path; nobody else may call it (Supabase would expose it at /rest/v1/rpc).
create or replace function public.countwatch_send_orders() returns bigint language sql security definer set search_path = '' as $fn$
  select net.http_post(
    url := 'https://staging.countwatch.com/v1/ingest',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (select decrypted_secret from vault.decrypted_secrets where name = 'countwatch_api_key')),
    body := jsonb_build_object('points', (
      select jsonb_agg(jsonb_build_object(
        'metric', 'orders',
        'ts', to_char(b at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
        'set', (select count(*) from public.orders o where o.created_at >= b and o.created_at < b + interval '5 minutes' and o.status = 'paid')))
      from generate_series(date_trunc('hour', now(), 'UTC') - interval '1 hour', date_trunc('hour', now(), 'UTC') - interval '5 minutes', interval '5 minutes') as b))
  );
$fn$;
revoke execute on function public.countwatch_send_orders() from public, anon, authenticated;

-- A random minute (5–54), so many databases do not all send at the same time.
select cron.schedule('countwatch-orders', (5 + floor(random() * 50))::int || ' * * * *', 'select public.countwatch_send_orders()');
select public.countwatch_send_orders(); -- once now
```

Each point `set`s its bucket, so sending an hour again (a manual run, a cron retry) only rewrites the same totals. Every bucket is sent, zeros included, so the watch (source `explicit`) can tell "no orders" from "the job stopped": a stopped job raises the no-data alert. One request an hour stays inside the Free budget; on Pro you may schedule `*/5 * * * *` and send only the last finished bucket. pg_net sends after the statement commits: a few seconds later, in a separate query, check the answer with `select status_code, content from net._http_response order by id desc limit 1;` (200 and `accepted: 12`).

## 3. Import 89 days with the same count, inside the database

Run once, right after step 2's first send, like steps 1 and 2. Supabase sends the history itself, so no command outside the database holds the database password, and the token stays in Vault. Real 5-minute history, every bucket (an empty one is a `0`), in UTC, ending where the hourly sends begin (the API also stops an import at a watch's first live bucket, so it never overwrites the sends).

```sql
-- The body goes straight into pg_net's queue as compact text: pg_net 0.20.4 sends at most 64 KiB of a request (fixed
-- in 0.20.5), and net.http_post writes JSON with spaces (about 77 KB for 89 days). 5-minute counts for 89 days when
-- they fit, else for 60, 45 or 30 days: the answer's days say which.
with span as (
  select date_trunc('hour', now(), 'UTC') - interval '1 hour' as until),
counted as materialized (
  -- One pass over the rows: each paid order into its 5-minute bucket (UTC: the origin is a UTC instant).
  select date_bin('5 minutes', o.created_at, timestamptz '2000-01-01 00:00:00+00') as bucket, count(*) as n
  from public.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),
choices (pick, days) as (values (1, 89), (2, 60), (3, 45), (4, 30)),
bodies as (
  select c.pick, c.days, '{"watches":[{"metric":"orders","resolution":"5m","start":"'
    || to_char((span.until - c.days * interval '1 day') at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') || '","counts":['
    || (select string_agg(coalesce(k.n, 0)::text, ',' order by b)
        from generate_series(span.until - c.days * interval '1 day', span.until - interval '5 minutes', interval '5 minutes') as b
        left join counted k on k.bucket = b)
    || ']}]}' as body
  from choices c, span),
chosen as (
  -- The first that fits one request with room for the headers; a larger one would stall pg_net for every request.
  select * from bodies where octet_length(body) <= 65000 order by pick limit 1),
sent as (
  insert into net.http_request_queue (method, url, headers, body, timeout_milliseconds)
  select 'POST', 'https://staging.countwatch.com/v1/import',
    jsonb_build_object('Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (select decrypted_secret from vault.decrypted_secrets where name = 'countwatch_api_key')),
    convert_to(chosen.body, 'UTF8'), 30000
  from chosen
  returning id)
select sent.id as request_id, chosen.days, octet_length(chosen.body) as bytes from sent, chosen;
select net.wake(); -- a row added to the queue directly does not wake pg_net's worker
```

A few seconds later, check its answer with the `request_id` the statement returned: `select status_code, content from net._http_response where id = <request_id>;` (`202` and an `import_id`; `GET /v1/imports/{import_id}` then shows the replay).

- **Fewer than 89 days:** the table is busy, so 89 days of 5-minute counts are over one request. The watch learns from the days sent and from live data.
- **No row returned:** nothing was sent; even 30 days are over one request. Add a choice with fewer days (`(5, 21)`) and run it again, or skip the import: the watch learns from live data (about 3 weeks).
- **The insert is refused** (`net.http_request_queue` is pg_net's own queue table, and a pg_net version may change it): an agent stops here and tells the person; the history can wait, and the watch learns from live data meanwhile. A person can use the route below.

`date_bin` needs Postgres 14 or later (Supabase runs 15 or later). 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` here and in step 2.

**From your own machine instead** (a person at a terminal; an agent must not run this: it holds the database password and calls CountWatch in one command, which agent safety checks rightly refuse): `SUPABASE_DB_URL` is the connection string from the Supabase dashboard: **Connect** → **Session pooler** (host `aws-…pooler.supabase.com`, user `postgres.<project-ref>`). The direct connection (`db.<project-ref>.supabase.co`) is IPv6 only unless the project has the IPv4 add-on, and most machines cannot reach it. Keep the string in `.env`, never in chat.

```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 "$SUPABASE_DB_URL" -At -v ON_ERROR_STOP=1 <<'SQL'
with span as (
  select date_trunc('hour', now() - interval '89 days', 'UTC') as since, date_trunc('hour', now(), 'UTC') - interval '1 hour' as until),
counted as (
  -- One pass over the rows: each paid order into its 5-minute bucket (UTC: the origin is a UTC instant).
  select date_bin('5 minutes', o.created_at, timestamptz '2000-01-01 00:00:00+00') as bucket, count(*) as n
  from public.orders o, span
  where o.created_at >= span.since and o.created_at < span.until and o.status = 'paid'
  group by 1)
select jsonb_build_object('watches', jsonb_build_array(jsonb_build_object(
  'metric', 'orders', 'resolution', '5m',
  'start', to_char(span.since at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
  'counts', (select jsonb_agg(coalesce(c.n, 0) order by b)
             from generate_series(span.since, span.until - interval '5 minutes', interval '5 minutes') as b
             left join counted c on c.bucket = b))))
from span;
SQL
)
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"
)
```

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.