Discovering and Interpreting API Errors in the Logs
Last edited: 1/18/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
_11select_11 -- the event message does not require unnesting_11 event_message,_11 -- unnested status_code column from metadata.response field_11 status_code_11from_11 edge_logs_11 -- Unpack data stored in the 'metadata' field_11 cross join unnest(metadata) as metadata_11 -- After unpacking the 'metadata' field, extract the 'response' field from it_11 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:
_10select_10 city_10from_10 edge_logs_10-- Unpack 'metadata' field_10cross join unnest(metadata) AS metadata_10-- unpack 'request' from 'metadata'_10cross join unnest(request) AS request;_10-- unpack 'cf' from 'request'_10cross 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:
_10select_10 cf_connecting_ip_10from_10 edge_logs_10-- Unpack 'metadata' field_10cross join unnest(metadata) AS metadata_10-- unpack 'request' from 'metadata'_10cross join unnest(request) AS request;_10-- unpack 'headers' from 'request'_10cross 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:
_12select_12 method,_12 url,_12 auth_users_12from_12 edge_logs_12-- Unpack 'metadata' field_12cross join unnest(metadata) AS metadata_12-- unpack 'request' from 'metadata'_12cross join unnest(request) AS request;_12-- unpack 'sb' from 'request'_12cross 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:
_10select_10 status_code_10from_10 edge_logs_10 -- Unpack 'metadata' field_10 cross join unnest(metadata) as metadata_10 -- unpack 'response' from 'metadata'_10 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:
_10let { data: countries, error } = await supabase.from('countries').select('name')
translates to calling the following endpoint:
_10https://<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:
_19select_19 cast(timestamp as datetime) as timestamp,_19 status_code,_19 url,_19 event_message_19from edge_logs_19cross join unnest(metadata) as metadata_19cross join unnest(response) AS request;_19cross join unnest(response) AS response;_19where_19 -- find all errors_19 status_code >= 400_19 and_19 -- find queries featuring the a specific <table_name> and <column_name>_19 (_19 regexp_contains(url, '<table_name>')_19 and_19 regexp_contains(event_message, '<column_name1>|<column_name2>')_19 )
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.
_26select_26 cast(postgres_logs.timestamp as datetime) as timestamp,_26 error_severity,_26 user_name,_26 query,_26 detail,_26 sql_state_code,_26 event_message_26from postgres_logs_26 cross join unnest(metadata) as metadata_26 cross join unnest(metadata.parsed) as parsed_26where_26 -- filter only for error events_26 regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')_26 and_26 -- All DB API requests are registered as the authenticator role_26 parsed.user_name = 'authenticator'_26 and_26 -- find failed queries featuring the function <function_name>_26 regexp_contains(parsed.query, '<function_name>')_26 and_26 -- limit the time of the search to be around the time of the failed API request_26postgres_logs.timestamp between '2024-04-15 10:50:00' AND '2024-04-15 10:50:27'_26order by_26 timestamp desc_26limit 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:
_15select_15 cast(timestamp as datetime) as timestamp,_15 status_code,_15 event_message,_15 path_15from_15 edge_logs_15 cross join unnest(metadata) as metadata_15 cross join unnest(response) as response_15 cross join unnest(request) as request_15where_15 -- find all errors_15 status_code >= 400_15 and regexp_contains(path, '^/rest/v1/');_15-- only look at DB API
Group errors by path and code:
_15select_15 status_code,_15 path,_15 count(path) as reoccurrence_per_path_15from_15 edge_logs_15 cross join unnest(metadata) as metadata_15 cross join unnest(response) as response_15 cross join unnest(request) as request_15where_15 -- find all errors_15 status_code >= 400_15 and regexp_contains(path, '^/rest/v1/') -- only look at DB API_15group by path, status_code_15order by reoccurrence_per_path;
Find requests by region:
_14select_14 path,_14 region,_14 count(region) as region_count_14from_14 edge_logs_14 cross join unnest(metadata) as metadata_14 cross join unnest(request) as request_14 cross join unnest(cf) as cf_14where_14 -- only look at DB API_14 regexp_contains(path, '^/rest/v1/')_14group by region, path_14order by requester_region_count;
Find total requests by IP:
_13select_13 cf_connecting_ip as ip,_13 count(cf_connecting_ip) as ip_count_13from_13 edge_logs_13 cross join unnest(metadata) as metadata_13 cross join unnest(request) as request_13 cross join unnest(headers) as headers_13 cross join unnest(cf) as cf_13 cross join unnest(response) as response_13where regexp_contains(path, '^/auth/v1/')_13group by ip_13order by ip_count;
Search frequented query paths by authenticated user:
_14select_14 -- only available for front-end clients_14 auth_users,_14 path,_14 count(auth_users) as ip_count_14from_14 edge_logs_14 cross join unnest(metadata) as metadata_14 cross join unnest(request) as request_14 cross join unnest(sb) as sb_14where_14 -- only look at DB API_14 regexp_contains(path, '^/rest/v1/')_14group by auth_users, path;