Discovering and Interpreting API Errors in the Logs

1
select
2
  -- event_message is a column, so it needs no lookup
3
  event_message,
4
  -- response.status_code is a log_attributes key
5
  log_attributes['response.status_code'] as status_code
6
from logs
7
where source = 'edge_logs'
8
limit 100;

1
select
2
  log_attributes['request.cf.city'] as city
3
from logs
4
where source = 'edge_logs'
5
limit 100;

1
select
2
  log_attributes['request.headers.cf_connecting_ip'] as cf_connecting_ip
3
from logs
4
where source = 'edge_logs'
5
limit 100;

1
select
2
  log_attributes['request.method'] as method,
3
  log_attributes['request.url'] as url,
4
  log_attributes['request.sb.jwt.authorization.payload.subject'] as auth_user
5
from logs
6
where source = 'edge_logs'
7
limit 100;

1
select
2
  log_attributes['response.status_code'] as status_code
3
from logs
4
where source = 'edge_logs'
5
limit 100;

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

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

1
select
2
  timestamp,
3
  log_attributes['response.status_code'] as status_code,
4
  log_attributes['request.url'] as url,
5
  event_message
6
from logs
7
where
8
  source = 'edge_logs'
9
  -- find all errors
10
  and toInt32OrZero(log_attributes['response.status_code']) >= 400
11
  -- find queries featuring a specific <table_name> and <column_name>
12
  and match(log_attributes['request.url'], '<table_name>')
13
  and match(event_message, '<column_name1>|<column_name2>')
14
order by timestamp desc
15
limit 100;

1
select
2
  timestamp,
3
  log_attributes['parsed.error_severity'] as error_severity,
4
  log_attributes['parsed.user_name'] as user_name,
5
  log_attributes['parsed.query'] as query,
6
  log_attributes['parsed.detail'] as detail,
7
  log_attributes['parsed.sql_state_code'] as sql_state_code,
8
  event_message
9
from logs
10
where
11
  source = 'postgres_logs'
12
  -- filter only for error events
13
  and log_attributes['parsed.error_severity'] in ('ERROR', 'FATAL', 'PANIC')
14
  -- All DB API requests are registered as the authenticator role
15
  and log_attributes['parsed.user_name'] = 'authenticator'
16
  -- find failed queries featuring the function <function_name>
17
  and match(log_attributes['parsed.query'], '<function_name>')
18
  -- limit the time of the search to be around the time of the failed API request
19
  and timestamp between '2024-04-15 10:50:00' and '2024-04-15 10:50:27'
20
order by timestamp desc
21
limit 100;

1
select
2
  timestamp,
3
  log_attributes['response.status_code'] as status_code,
4
  event_message,
5
  log_attributes['request.path'] as path
6
from logs
7
where
8
  source = 'edge_logs'
9
  -- find all errors
10
  and toInt32OrZero(log_attributes['response.status_code']) >= 400
11
  -- only look at DB API
12
  and match(log_attributes['request.path'], '^/rest/v1/')
13
order by timestamp desc
14
limit 100;

1
select
2
  log_attributes['response.status_code'] as status_code,
3
  log_attributes['request.path'] as path,
4
  count() as reoccurrence_per_path
5
from logs
6
where
7
  source = 'edge_logs'
8
  -- find all errors
9
  and toInt32OrZero(log_attributes['response.status_code']) >= 400
10
  and match(log_attributes['request.path'], '^/rest/v1/') -- only look at DB API
11
group by path, status_code
12
order by reoccurrence_per_path desc
13
limit 100;

1
select
2
  log_attributes['request.path'] as path,
3
  log_attributes['request.cf.region'] as region,
4
  count() as region_count
5
from logs
6
where
7
  source = 'edge_logs'
8
  -- only look at DB API
9
  and match(log_attributes['request.path'], '^/rest/v1/')
10
group by region, path
11
order by region_count desc
12
limit 100;

1
select
2
  log_attributes['request.headers.cf_connecting_ip'] as ip,
3
  count() as ip_count
4
from logs
5
where
6
  source = 'edge_logs'
7
  and match(log_attributes['request.path'], '^/auth/v1/')
8
group by ip
9
order by ip_count desc
10
limit 100;

1
select
2
  -- only available for front-end clients
3
  log_attributes['request.sb.jwt.authorization.payload.subject'] as auth_user,
4
  log_attributes['request.path'] as path,
5
  count() as request_count
6
from logs
7
where
8
  source = 'edge_logs'
9
  -- only look at DB API
10
  and match(log_attributes['request.path'], '^/rest/v1/')
11
group by auth_user, path
12
order by request_count desc
13
limit 100;