sql
DECLARE
  entry_count int;
  total_urls integer;
  current_type url_type;
BEGIN

  -- Obtém o subscription_tier do usuário associado à nova URL
  select COUNT(*) - 1
  into entry_count
  from url_data e
  where e.user_id = new.user_id AND e.type = 'free';

  -- Verifica se o total de URLs é menor ou igual a 10
  IF total_urls <= 10 THEN
    current_type = 'free'::url_type;
  ELSE
    current_type = 'payed'::url_type;
  END IF;

  -- raise exception 'total_urls %', total_urls;

  update public.url_data
  set type = current_type
  where id = new.id;

  RETURN NEW;
END;

i checked total_urls value so even that i have less 10 im getting payed on the table

Where did total_urls get set with something?

i fixed