Discovering and Interpreting API Errors in the Logs

[TheOtherBrian1]

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 Log Explorer. These logs are managed through Logflare and can be queried with a subset of BigQuery SQL syntax.

The log table that contains API requests is edge_logs.

Notably, it contains:

field	description
event_message	the log's message
timestamp	time event was recorded
request metadata	metadata about the REST request
response metadata	metadata about the REST response

The request and response columns are arrays in the metadata field and must be unnested. This is done with a cross join.

Unnesting example

select
  -- the event message does not require unnesting
  event_message,
  -- unnested status_code column from metadata.response field
  status_code
from
  edge_logs 
-- Unpack data stored in the 'metadata' field
cross join unnest(metadata) AS metadata
-- After unpacking the 'metadata' field, extract the 'response' field from it
cross join unnest(response) AS response;

The most useful fields for debugging are:

NOTE: not every field is included below. For a full list, check the API Edge field reference in the Log Explorer

Request object

Cloudflare geographic data:

Suggested use cases:

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

Column	Description	Sample value
request.cf.city	Requester's city	Munich
request.cf.country	Requester's country	DE
request.cf.continent	Requester's continent	EU
request.cf.region	Requester's region	Bavaria
request.cf.latitudex	Requester's latitude	48.10840
request.cf.longitude	Requester's longitude	11.61020
request.cf.timezone	Requester's timezone	Europe/Berlin

Unnesting example:

select
  city
from
  edge_logs 
-- Unpack 'metadata' field
cross join unnest(metadata) AS metadata
-- unpack 'request' from 'metadata'
cross join unnest(request) AS request;
-- unpack 'cf' from 'request'
cross join unnest(cf) AS cf;

IP and browser/environment data:

Suggested use cases:

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

Column	Description	Sample value
request.headers.cf_connecting_ip	Requester's IP	80.81.18.138
request.headers.user_agent	Requester's browser or app environment	Mozilla/5.0 (Linux; Android 11; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Mobile Safari/537.36

Unnesting example:

select
  cf_connecting_ip
from
  edge_logs 
-- Unpack 'metadata' field
cross join unnest(metadata) AS metadata
-- unpack 'request' from 'metadata'
cross join unnest(request) AS request;
-- unpack 'headers' from 'request'
cross join unnest(headers) AS headers;

query type and formatting data:

Suggested use cases:

• identify problematic queries
• identify unusual behavior by authenticated users

Column	Description	Sample value
request.method	Request Method (PATCH, GET, PUT...)	GET
request.url	Request URL, which contains the PostgREST formatted query	https://yuhplfrsdxxxtldakizi.supabase.co/rest/v1/users?select=username&id=eq.63b6190e-214f-4b8a-b72d-3af6e1921411&limit=1
request.sb.auth_users	authenticated user's ID	63b6190e-214f-4b8a-b72d-3af6e1921411

Unnesting example:

select
  method,
  url,
  auth_users
from
  edge_logs 
-- Unpack 'metadata' field
cross join unnest(metadata) AS metadata
-- unpack 'request' from 'metadata'
cross join unnest(request) AS request;
-- unpack 'sb' from 'request'
cross join unnest(sb) AS sb;

Response object

Status code:

Suggested use cases:

• detect success/errors

Column	Description	Sample value
response.status_code	Response status code (200, 404, 500...)	404

Unnesting example:

select
  status_code
from
  edge_logs 
-- Unpack 'metadata' field
cross join unnest(metadata) AS metadata
-- unpack 'response' from 'metadata'
cross join unnest(response) AS response;

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
  cast(timestamp as datetime) as timestamp,
  status_code,
  url,
  event_message
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(response) AS request;
cross join unnest(response) AS response;
where
  -- find all errors
  status_code >= 400
    and
  -- find queries featuring the a specific <table_name> and <column_name>
  (
    regexp_contains(url, '<table_name>')
    and 
    regexp_contains(event_message, '<column_name1>|<column_name2>')
  )

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 PostgreSQL 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
  cast(postgres_logs.timestamp as datetime) as timestamp,
  error_severity,
  user_name,
  query,
  detail,
  sql_state_code,
  event_message
from postgres_logs
  cross join unnest(metadata) as metadata
  cross join unnest(metadata.parsed) as parsed
where
  -- filter only for error events
  regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
    and
  -- All DB API requests are registered as the authenticator role
  parsed.user_name = 'authenticator'
    and
  -- find failed queries featuring the function <function_name>
  regexp_contains(parsed.query, '<function_name>')
    and 
  -- limit the time of the search to be around the time of the failed API request
postgres_logs.timestamp between '2024-04-15 10:50:00' AND '2024-04-15 10:50:27'
order by
 timestamp desc
limit 100;

Like PostgREST, PostgreSQL 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
  cast(timestamp as datetime) as timestamp,
  status_code,
  event_message,
  path
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(response) AS response
cross join unnest(request) AS request
where
   -- find all errors
   status_code >= 400
    and
   -- only look at DB API
   regexp_contains(path, '^/rest/v1/');

Group errors by path and code:

select
  status_code,
  path,
  count(path) as reoccurrence_per_path,
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(response) AS response
cross join unnest(request) AS request

where
   -- find all errors
   status_code >= 400
    and
   -- only look at DB API
   regexp_contains(path, '^/rest/v1/')
group by path, status_code
order by reoccurrence_per_path;

Find requests by region:

select
  path,
  region,
  count(region) as region_count
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(request) as request
cross join unnest(cf) as cf
where
  -- only look at DB API
  regexp_contains(path, '^/rest/v1/')
group by region, path
order by requester_region_count;

Find total requests by IP:

select
  cf_connecting_ip as ip,
  count(cf_connecting_ip) as ip_count
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(request) as request
cross join unnest(headers) as headers
cross join unnest(cf) as cf
cross join unnest(response) as response
where
   regexp_contains(path, '^/auth/v1/')
group by ip
order by ip_count;

Search frequented query paths by authenticated user:

select
  -- only available for front-end clients
  auth_users,
  path,
  count(auth_users) as ip_count
from edge_logs
cross join unnest(metadata) as metadata
cross join unnest(request) as request
cross join unnest(sb) as sb
where
   -- only look at DB API
   regexp_contains(path, '^/rest/v1/')
group by auth_users, path;