Docs / Connect your data

Supabase

Get an alert when orders in your Supabase database spike, drop or stop. The database sends its own counts.

In short: your Supabase database counts its own orders on a schedule and sends them to CountWatch. Nothing else runs on your side. If you like, it also sends the last 89 days so alerts start today.

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.

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

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

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

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

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

For agents: this page as Markdown.