Detection checks

Use these checks to identify evidence worth investigating. A finding does not establish a cause. The specialist monitoring agents use these same checks.

Before running checks#

Identify the project and database instance. Use project-scoped Supabase MCP with read_only=true.
Run ClickHouse SQL with query_logs; supply an explicit UTC time range using the tool's input schema. Run Postgres SQL with execute_sql. In Explorer, select Run SQL, then query source Logs or Database, respectively.
Record observation time, windows, thresholds, and saved baseline. Defaults below are starting alert policies, not Supabase service guarantees. Record operator overrides before running.
Failed tools, missing permissions or required fields, incomplete windows, and unavailable history make the affected check unable to assess. Continue independent checks. Zero recorded events alone does not prove service health.

Each check returns finding, clear (completed, no threshold crossed), or unable to assess with the missing input. Preserve this result even when a clear run sends no notification.

Health#

Measure API and Auth server errors#

Input: the last complete UTC hour and preceding complete hour, queried separately. Evaluate each source separately; API Gateway and Auth events are different observations, not unique requests to add together.

1
select source,
2
  count() as events,
3
  countIf(status between 100 and 599) as responses,
4
  countIf(status between 500 and 599) as server_errors,
5
  countIf(status in (401, 403)) as access_failures,
6
  countIf(status is null or status < 100 or status > 599) as unknown_status
7
from (
8
  select source,
9
    toInt32OrNull(if(source = 'edge_logs',
10
      log_attributes['response.status_code'], log_attributes['status'])) as status
11
  from logs
12
  where source in ('edge_logs', 'auth_logs')
13
)
14
group by source
15
order by source
16
limit 2;

Signal: compute 100 * server_errors / responses per source. Report at least 20 server errors, a rate of at least 1%, and at least twice the preceding rate. When the preceding rate is zero, use the count and 1% conditions. Both windows need at least 100 responses; otherwise the comparison is unable to assess.

Rates use valid statuses only. Report unknown_status separately; no valid statuses makes the check unable to assess. Auth events without response statuses are not successful requests. A missing source row requires a capture/traffic check, not an assumed zero error rate.

Next: narrow to the source and hour. Collect at most five event IDs with timestamps and status, then follow API error troubleshooting. Redact paths and messages. After a fix, rerun on a comparable window.

Check connection pressure#

Input: a current Postgres snapshot with permission to read all sessions.

1
select
2
  count(*) filter (where backend_type = 'client backend') as client_connections,
3
  count(*) filter (where backend_type = 'client backend' and state = 'active') as active_connections,
4
  current_setting('max_connections')::int as max_connections
5
from pg_stat_activity;

Signal: report client connections at 80% of max_connections. This is an instance-wide pressure indicator. Reserved slots, role limits, and pooler limits can constrain a client sooner; this does not measure slots available to an application.

Next: inspect connection management and role counts. Rerun after the workload or pooling change.

Security#

Review advisor findings#

Action: call get_advisors with type: "security", using the tool's project scope. Report WARN and ERROR findings with the lint name, affected object, and documentation link. Keep INFO as context without alerting by default.

Next: follow the check documentation and verify the intended access model before proposing a change. Rerun the advisor after a fix. No findings does not prove the project is secure. See Advisors for other execution paths.

Measure authentication and authorization failures#

Input/action: run the status-count query for the last complete UTC day and preceding complete day, in separate requests of at most 24 hours. Evaluate each source separately.

Signal: compute 100 * access_failures / responses. Apply the Health minimum of 100 responses in both windows. Report at least 20 failures, a rate of at least 1%, and at least twice the preceding rate. When the preceding rate is zero, use the count and 1% conditions. Apply the same unknown-status and missing-source rules.

Next: group failures by status and sanitized path, not by user, email, or IP. Investigate the client flow and Auth error codes. A spike is a review signal, not proof of an attack. Verify against a comparable window.

Performance#

Find long-running sessions and blockers#

Input: a current Postgres snapshot with permission to read all sessions. This cannot reconstruct sessions that ended between scheduled runs.

1
select pid, usename as role, state,
2
  now() - query_start as query_age,
3
  now() - xact_start as transaction_age,
4
  wait_event_type, wait_event,
5
  pg_blocking_pids(pid) as blocking_pids
6
from pg_stat_activity
7
where datname = current_database()
8
  and pid <> pg_backend_pid()
9
  and (
10
    (state = 'active' and now() - query_start > interval '30 seconds')
11
    or (state like 'idle in transaction%' and now() - xact_start > interval '30 seconds')
12
    or cardinality(pg_blocking_pids(pid)) > 0
13
  )
14
order by query_start
15
limit 20;

Signal: each row needs review. Nonempty blocking_pids identifies blockers; a long query or wait event alone does not. Query age is not lock-wait duration. Twenty returned rows may indicate truncation.

Next: inspect the PIDs using database inspection and establish the transaction's purpose and impact. Do not recommend cancellation from age alone. Rerun to verify resolution.

Compare query execution time#

Input: enabled pg_stat_statements, query-identifier visibility, and three saved snapshots spaced one hour apart. They define the preceding and current hour.

1
select
2
  now() as observed_at,
3
  s.dbid,
4
  s.userid,
5
  s.queryid,
6
  s.toplevel,
7
  s.calls,
8
  s.total_exec_time,
9
  i.stats_reset,
10
  i.dealloc,
11
  to_jsonb(s) ->> 'stats_since' as statement_stats_since
12
from
13
  pg_stat_statements as s
14
  cross join pg_stat_statements_info as i
15
where s.dbid = (select oid from pg_database where datname = current_database())
16
order by s.total_exec_time desc
17
limit 100;

Signal: match (dbid, userid, queryid, toplevel) within the same project instance. For each interval, compute delta(total_exec_time) / delta(calls) in milliseconds. Report a current mean of at least 100 ms and twice the preceding mean, with at least 20 calls in each interval.

Compare rows present in all snapshots with unchanged reset/start markers and counters that have not decreased. Discard comparisons after an upgrade, reset, or change to dealloc (entry eviction). If statement_stats_since is unavailable, require confirmation that no per-statement reset occurred. Missing history or reset provenance means unable to assess; start collecting snapshots. The top 100 rows are a sample, not full query coverage. Do not reset statistics to collect a baseline. See Postgres statistics semantics.

Next: inspect the statement and its query plan. Preserve a comparison window to verify any change.

Review performance advisors#

Call get_advisors with type: "performance". Apply the Security severity policy: report WARN and ERROR; retain INFO as context. Follow the returned documentation, verify relevance to the workload, and rerun after a fix.

Inspect cache misses#

This optional diagnostic is cumulative, not an hourly alert or a measurement of physical disk reads:

1
select
2
  sum(heap_blks_hit) as heap_hits,
3
  sum(heap_blks_read) as heap_reads,
4
  round(
5
    100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0),
6
    2
7
  ) as heap_hit_percent
8
from pg_statio_user_tables;

Use a workload-specific baseline before alerting. A null ratio means no observed accesses. The operating system cache may serve a Postgres buffer miss. See cache inspection.

Capacity #

Collect size and connection measurements#

Input/action: read the same database instance daily at the same UTC time. Save numeric values and timestamps in authorized persistent harness state, or use an authorized historical metrics source. Do not create monitoring tables in the project.

1
select
2
  now() as observed_at,
3
  current_database() as database_name,
4
  pg_database_size(current_database()) as database_bytes;

1
select
2
  now() as observed_at,
3
  schemaname,
4
  relname as table_name,
5
  pg_total_relation_size(relid) as total_bytes
6
from pg_catalog.pg_statio_user_tables
7
order by total_bytes desc
8
limit 20;

1
select now() as observed_at, usename as role, state, count(*) as connections
2
from pg_stat_activity
3
where datname = current_database() and backend_type = 'client backend'
4
group by role, state
5
order by connections desc
6
limit 100;

Interpretation: sizes are bytes, connections are a snapshot count, and table totals include indexes. A relation missing from the top 20 has not necessarily shrunk. Snapshots do not establish peak connection demand; use the Metrics API for a time series.

Forecast a resource limit#

Input: at least seven daily measurements of the same metric and scope, plus a confirmed limit in the same units. Record the limit's source and retrieval time. Database size is not total disk usage: a disk forecast needs disk-used bytes and disk capacity. Never compare table bytes or request counts with an unrelated plan limit.

Signal: when growth is positive, calculate:

1
growth_per_day = (latest_value - earliest_value) / elapsed_days
2
days_remaining = (confirmed_limit - latest_value) / growth_per_day

Report when the current value already meets the confirmed limit, regardless of history. Otherwise, report a supported projection at most 14 days away, labeled as a linear estimate. Missing history, unknown limits, changed scope, or discontinuous measurements make the forecast unable to assess. Flat or falling values do not support an exhaustion date.

Next: carry the metric, units, history, limit source, and calculation to compute and disk guidance. Measure again after a capacity change and update the stored limit.

Compare request volume#

Run the Health query for two separate complete UTC days. Compare API Gateway events; report at least 1,000 events and twice the preceding count. If the preceding count is zero, report new observed traffic without a growth percentage. Apply the missing-source rules. Request growth is workload context, not a capacity limit or billing total.

Turn a detection into a diagnosis#

Report the check, outcome, project, observation time, window or snapshot, threshold, measured values and units, and an evidence identifier. Include one investigation link and a verification step. Separate observations from hypotheses; do not invent a cause or remediation SQL. Use the troubleshooting guides to investigate the evidence.