Learn Supabase Database Functions with Cron & Triggers

Supabase gives you powerful tools like database functions to automate and extend your backend. In this blog, we’ll explore how to use these functions with cron jobs, triggers, and Edge Functions to streamline your app’s workflow.

What Are Supabase Database Functions?

Supabase database functions are custom SQL or PL/pgSQL code blocks that you can run directly inside your Postgres database. You can use them to update data, process logic, or respond to events—all without needing an external server.

1. Automate with Cron: Run Functions on a Schedule

Example: Deactivate Users Who Haven’t Logged In

create or replace function deactivate_old_users()

returns void as $$

begin

update users set is_active = false

where last_login < now() - interval '30 days';

end;

$$ language plpgsql;

Schedule this function using Supabase’s pg_cron extension:

select cron.schedule(

'deactivate_inactive_users',

'0 2 * * *',

$$select deactivate_old_users();$$

);

This will automatically mark users as inactive every day at 2 AM.

2. React to Changes: Use Triggers with Database Functions

Example: Notify Edge Function on New Order

create or replace function notify_on_new_order()

returns trigger as $$

begin

perform net.http_post(

url := 'https://your-edge-url',

headers := '{"Content-Type": "application/json"}',

body := row_to_json(NEW)::text

);

return NEW;

end;

$$ language plpgsql;

Create a trigger that runs the function after a new order:

create trigger on_order_insert

after insert on orders

for each row

execute function notify_on_new_order();

Now your Edge Function will be called automatically whenever a new order is added.

3. Create Custom APIs with RPC (Remote Procedure Call)

Example: Return User Stats

create or replace function get_user_stats(uid uuid)

returns json as $$

select json_build_object(

'total_orders', count(*),

'last_login', max(last_login)

)

from users

where id = uid;

$$ language sql;

You can call this from your frontend using Supabase’s rpc() method:

const { data, error } = await supabase.rpc('get_user_stats', { uid: user.id });

Why Use Supabase Database Functions?

Run background tasks without external servers
Trigger custom logic on data changes
Keep your backend fast, reactive, and efficient

Final Thoughts

Supabase database functions + cron + triggers + Edge Functions = automation power combo. Use them to reduce manual work, react to real-time data changes, and build smarter APIs.

Learn more from the official docs: Supabase Database Functions