Discovering and Interpreting API Errors in the Logs

Last edited: 2/21/2025

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

1
2
3
4
5
6
7
8
9
10
11
select  -- the event message does not require unnesting  event_message,  -- unnested status_code column from metadata.response field  status_codefrom  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:

1
2
3
4
5
6
7
8
9
10
select  cityfrom  edge_logs-- Unpack 'metadata' fieldcross 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:

1
2
3
4
5
6
7
8
9
10
select  cf_connecting_ipfrom  edge_logs-- Unpack 'metadata' fieldcross 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:

1
2
3
4
5
6
7
8
9
10
11
12
select  method,  url,  auth_usersfrom  edge_logs-- Unpack 'metadata' fieldcross 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:

1
2
3
4
5
6
7
8
select  status_codefrom  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:

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

translates to calling the following endpoint:

1
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select  cast(timestamp as datetime) as timestamp,  status_code,  url,  event_messagefrom edge_logscross join unnest(metadata) as metadatacross 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 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select  cast(postgres_logs.timestamp as datetime) as timestamp,  error_severity,  user_name,  query,  detail,  sql_state_code,  event_messagefrom postgres_logs  cross join unnest(metadata) as metadata  cross join unnest(metadata.parsed) as parsedwhere  -- 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 requestpostgres_logs.timestamp between '2024-04-15 10:50:00' AND '2024-04-15 10:50:27'order by timestamp desclimit 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select  cast(timestamp as datetime) as timestamp,  status_code,  event_message,  pathfrom  edge_logs  cross join unnest(metadata) as metadata  cross join unnest(response) as response  cross join unnest(request) as requestwhere  -- find all errors  status_code >= 400  and regexp_contains(path, '^/rest/v1/');-- only look at DB API

Group errors by path and code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select  status_code,  path,  count(path) as reoccurrence_per_pathfrom  edge_logs  cross join unnest(metadata) as metadata  cross join unnest(response) as response  cross join unnest(request) as requestwhere  -- find all errors  status_code >= 400  and regexp_contains(path, '^/rest/v1/') -- only look at DB APIgroup by path, status_codeorder by reoccurrence_per_path;

Find requests by region:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select  path,  region,  count(region) as region_countfrom  edge_logs  cross join unnest(metadata) as metadata  cross join unnest(request) as request  cross join unnest(cf) as cfwhere  -- only look at DB API  regexp_contains(path, '^/rest/v1/')group by region, pathorder by requester_region_count;

Find total requests by IP:

1
2
3
4
5
6
7
8
9
10
11
12
13
select  cf_connecting_ip as ip,  count(cf_connecting_ip) as ip_countfrom  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 responsewhere regexp_contains(path, '^/auth/v1/')group by iporder by ip_count;

Search frequented query paths by authenticated user:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select  -- only available for front-end clients  auth_users,  path,  count(auth_users) as ip_countfrom  edge_logs  cross join unnest(metadata) as metadata  cross join unnest(request) as request  cross join unnest(sb) as sbwhere  -- only look at DB API  regexp_contains(path, '^/rest/v1/')group by auth_users, path;

Discovering and Interpreting API Errors in the Logs

Navigating the API logs:

Request object#

Cloudflare geographic data:#

IP and browser/environment data:#

Query type and formatting data:#

Response object#

Status code:#

Finding errors

API level errors#

Database-level errors#

PostgREST server and Cloudflare errors#

Practical examples:

Request object

Cloudflare geographic data:

IP and browser/environment data:

Query type and formatting data:

Response object

Status code:

API level errors

Database-level errors

PostgREST server and Cloudflare errors