Edge function is only working with supabase functions API and not with cron jobs. #21537

Question

I'm trying to run my edge function every minute (with my cron job) for my web app. Unfortunately, the function doesn't seem to be working as intended. When I use my useEffect hook, the edge function works as intended. However, when I trigger the same function with a cron job, it doesn't work.

When I call the edge function inside my 'test' page using the useEffect hook, I get a response with status 200, but the method is OPTIONS. However, when I run my cron job, it also returns status 200, but the method is POST.

useEffect(() => {
    const fetchData = async () => {
      const { data, error } = await supabase.functions.invoke(
        "setPostToPosted",
        {
          body: JSON.stringify({ data: "this is a test" }),
        }
      );
      console.log(data, error);
      ``;

      setPosts(data);
    };

    // Call the fetch data function when the component mounts
    fetchData();
  }, []);

I assume there might be something wrong with my edge function.

Here's my cron job:

select
  cron.schedule(
    'invoke-function-every-minute',
    '* * * * *', -- every minute
    $$
    select
      net.http_post(
          url:='https://vopvzwbjhdsvhnwvluwb.supabase.co/functions/v1/setPostToPosted', 
          headers:='{"Content-Type": "application/json", "Authorization": "Bearer  {my token was here}"}'::jsonb,
          body:=concat('{"time": "', now(), '"}')::jsonb
      ) as request_id;
    $$
  );

In short, if this is relevant: the edge function should update certain rows to another status.

See below for my edge function code:

import { createClient } from 'npm:@supabase/supabase-js@2';
import {corsHeaders} from "../_shared/cors.ts"


Deno.serve(async (req) => {
  // This is needed if you're planning to invoke your function from a browser.
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }

  const authHeader = req.headers.get('Authorization')!;
  const supabaseClient = createClient(
    Deno.env.get('_SUPABASE_URL') ?? '',
    Deno.env.get('_SUPABASE_ANON_KEY') ?? '',
    { global: { headers: { Authorization: authHeader } } }
  );

  try {
    // Get post data from the request
    const { data, error } = await req.json();
  
    const currentTime = new Date();
    const responseData = await supabaseClient
      .from("posts")
      .select("*")
      .eq("status", "Scheduled");


      const updatedRowsData = [];
      const updatedRowError = [];
      const fetchUpdatedRows = [];

      for (const post of responseData.data) {
        const postDateTime = new Date(post.post_date_time);
  
        // Check if post_date_time is equal to or past the current time
        if (postDateTime <= currentTime) {
          // Update the status to "Posted"
          const { data: updatedRowData, error:updatedRowError } = await supabaseClient
            .from('posts')
            .update({ status: 'Posted' })
            .eq('id', post.id);
  
          // If the update was successful, add the updated row to the list
          if (!updatedRowError) {
            updatedRowsData.push(updatedRowData);
            
            const {data, error} = await supabaseClient.from('posts').select('*').eq('status', 'Posted').eq('id', post.id);
        if (!error) {
          fetchUpdatedRows.push(data);
        } 
          } else {
            updatedRowError.push(updatedRowError);
          
          }
        }
        
      }

  
      const object = {
        updatedRowsData: updatedRowsData,
        updatedRowsError: updatedRowError,
        fetchUpdatedRows: fetchUpdatedRows,
      }
  
      return new Response(
        JSON.stringify(object),
        { headers: { 'Content-Type': 'application/json', ...corsHeaders } },
      );
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { headers: { "Content-Type": "application/json", ...corsHeaders }, status: 400 },
    );
  }
})

I hope someone can help me figure this out.

Answer 1

So it sounds like you just want to execute the function every minute. If you are seeing the function invocation in the logs, that means the function is properly being called. I assume you are not seeing the status being updated, so from here on you just need to add console logs in different places to debug and find where the root cause is. Here is a sample code that I cleaned up based on yours. You can start with this and see if you see the X posts queried message show up on your logs.

Deno.serve(async (req) => {
  // This is needed if you're planning to invoke your function from a browser.
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }

  const authHeader = req.headers.get('Authorization')!;
  const supabaseClient = createClient(
    Deno.env.get('_SUPABASE_URL') ?? '',
    Deno.env.get('_SUPABASE_ANON_KEY') ?? '',
    { global: { headers: { Authorization: authHeader } } }
  );

  try {  
    const currentTime = new Date();
    
    // Get posts that have `Scheduled` status and the `post_date_time` is passed.
    const { data: postsData, error: postsError} = await supabaseClient
      .from("posts")
      .select("*")
      .eq("status", "Scheduled")
      .lte('post_date_time', currentTime.toISOString())

      console.log(`${postsData.length} posts queried`)

      const updatedRowsData = [];
      const updatedRowError = [];

      for (const post of responseData.data) {
          // Update the status to "Posted" and get the new data
          const { data: updatedRowData, error:updatedRowError } = await supabaseClient
            .from('posts')
            .update({ status: 'Posted' })
            .eq('id', post.id)
            .select()
            .single()
  
          // If the update was successful, add the updated row to the list
          if (!updatedRowError) {
            updatedRowsData.push(updatedRowData)
          } else {
            updatedRowError.push(updatedRowError)
          }
      }

  
      const object = {
        updatedRowsData: updatedRowsData,
        updatedRowsError: updatedRowError,
      }
  
      return new Response(
        JSON.stringify(object),
        { headers: { 'Content-Type': 'application/json', ...corsHeaders } },
      );
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { headers: { "Content-Type": "application/json", ...corsHeaders }, status: 400 },
    );
  }
})