Discovering and Interpreting API Errors in the Logs

A complimentary guide was made for the Postgres logs

Navigating the API logs:

The Database API is powered by a PostgREST web-server, recording every request to the API Edge Network logs. To precisely navigate them, use the SQL Editor with the query source set to Logs. These logs run on ClickHouse. Every log line is a row in a single logs table, tagged by a source column.

API requests are the rows where source = 'edge_logs'.

Notably, it contains:

Request and response details live in the log_attributes map. Read a field with bracket access, keeping the full dotted key. There are no unnesting joins.

Field access example

select
  -- event_message is a column, so it needs no lookup
  event_message,
  -- response.status_code is a log_attributes key
  log_attributes['response.status_code'] as status_code
from logs
where source = 'edge_logs'
limit 100;

The most useful fields for debugging are:

NOTE: not every field is included below. For a full list, check the API Gateway and Function Edge logs field reference.

Request object

Cloudflare geographic data:

Suggested use cases:

• Detecting abuse from a specific region
• Detecting activity spikes from certain regions

Unnesting example:

select
  log_attributes['request.cf.city'] as city
from logs
where source = 'edge_logs'
limit 100;

IP and browser/environment data:

Suggested use cases:

• Detecting request behavior from IP
• Detecting abuse by IP
• Detecting errors by user_agent

Unnesting example:

select
  log_attributes['request.headers.cf_connecting_ip'] as cf_connecting_ip
from logs
where source = 'edge_logs'
limit 100;

Query type and formatting data:

Suggested use cases:

• identify problematic queries

Unnesting example:

select
  log_attributes['request.method'] as method,
  log_attributes['request.url'] as url
from logs
where source = 'edge_logs'
limit 100;

Response object

Status code:

Suggested use cases:

• detect success/errors

Unnesting example:

select
  log_attributes['response.status_code'] as status_code
from logs
where source = 'edge_logs'
limit 100;

Finding errors

API level errors

The metadata.request.url contains PostgREST formatted queries.

For example, the following call to the JS client:

let { data: countries, error } = await supabase.from('countries').select('name')

translates to calling the following endpoint:

https://<project ref>.supabase.co/rest/v1/countries?select=name

You can use regex (Advanced Regex Guide) to find the objects related to your query. Try isolating by:

• function names
• column names
• table names
• query methods (select, insert, …)

Example:

select
  timestamp,
  log_attributes['response.status_code'] as status_code,
  log_attributes['request.url'] as url,
  event_message
from logs
where
  source = 'edge_logs'
  -- find all errors
  and toInt32OrZero(log_attributes['response.status_code']) >= 400
  -- find queries featuring a specific <table_name> and <column_name>
  and match(log_attributes['request.url'], '<table_name>')
  and match(event_message, '<column_name1>|<column_name2>')
order by timestamp desc
limit 100;

PostgREST has an error reference table that you can use to interpret status codes.

Database-level errors

However, some errors that are reported through the Database API occur at the Postgres level. If it is not clear which error occurred you should reference the timestamp of the error and try to see if you can find it in the Postgres logs.

select
  timestamp,
  log_attributes['parsed.error_severity'] as error_severity,
  log_attributes['parsed.user_name'] as user_name,
  log_attributes['parsed.query'] as query,
  log_attributes['parsed.detail'] as detail,
  log_attributes['parsed.sql_state_code'] as sql_state_code,
  event_message
from logs
where
  source = 'postgres_logs'
  -- filter only for error events
  and log_attributes['parsed.error_severity'] in ('ERROR', 'FATAL', 'PANIC')
  -- All DB API requests are registered as the authenticator role
  and log_attributes['parsed.user_name'] = 'authenticator'
  -- find failed queries featuring the function <function_name>
  and match(log_attributes['parsed.query'], '<function_name>')
  -- limit the time of the search to be around the time of the failed API request
  and timestamp between '2024-04-15 10:50:00' and '2024-04-15 10:50:27'
order by timestamp desc
limit 100;

Like PostgREST, Postgres has a reference table for interpreting error codes.

PostgREST server and Cloudflare errors

In some cases, errors may emerge because of Cloudflare or PostgREST server errors. For 500 and above errors, you may want to check your PostgREST logs and the Cloudflare docs.)

Practical examples:

Find All Errors:

select
  timestamp,
  log_attributes['response.status_code'] as status_code,
  event_message,
  log_attributes['request.path'] as path
from logs
where
  source = 'edge_logs'
  -- find all errors
  and toInt32OrZero(log_attributes['response.status_code']) >= 400
  -- only look at DB API
  and match(log_attributes['request.path'], '^/rest/v1/')
order by timestamp desc
limit 100;

Group errors by path and code:

select
  log_attributes['response.status_code'] as status_code,
  log_attributes['request.path'] as path,
  count() as reoccurrence_per_path
from logs
where
  source = 'edge_logs'
  -- find all errors
  and toInt32OrZero(log_attributes['response.status_code']) >= 400
  and match(log_attributes['request.path'], '^/rest/v1/') -- only look at DB API
group by path, status_code
order by reoccurrence_per_path desc
limit 100;

Find requests by region:

select
  log_attributes['request.path'] as path,
  log_attributes['request.cf.region'] as region,
  count() as region_count
from logs
where
  source = 'edge_logs'
  -- only look at DB API
  and match(log_attributes['request.path'], '^/rest/v1/')
group by region, path
order by region_count desc
limit 100;

Find total requests by IP:

select
  log_attributes['request.headers.cf_connecting_ip'] as ip,
  count() as ip_count
from logs
where
  source = 'edge_logs'
  and match(log_attributes['request.path'], '^/auth/v1/')
group by ip
order by ip_count desc
limit 100;

Go to the GitHub discussion