Supabase Cron: Automate Tasks with PostgreSQL

What is the Supabase Cron?

Supabase Cron is built upon the pg_cron PostgreSQL extension, enabling the scheduling of periodic tasks—commonly known as "cron jobs"—to run at specified intervals. These tasks can range from database maintenance and data cleanup to invoking external APIs or Supabase Edge Functions.

Key Features:

Native Integration: Schedule and execute jobs directly within your PostgreSQL database, eliminating the need for external schedulers.
Flexible Scheduling: Define job intervals using standard cron syntax or natural language, accommodating schedules from every second to once a year.
Versatile Execution: Run SQL snippets, call database functions, or make HTTP requests to external services, including Supabase Edge Functions.
Comprehensive Monitoring: Utilize Supabase's observability tools to track and debug scheduled jobs, ensuring smooth operation.

How to Set Up Supabase Cron Jobs:

1. Enable the pg_cron Extension:

Navigate to the Database page in your Supabase Dashboard.

Click on Extensions in the sidebar.

Search for "pg_cron" and enable the extension.

2. Schedule a Cron Job:

You can create jobs via the Supabase Dashboard or using SQL commands. For example, to schedule a job that deletes records older than a week every Sunday at 3:30 AM, execute:

   SELECT cron.schedule(
     'weekly_cleanup',
     '30 3 * * 0',
     $$ 
     DELETE FROM your_table 
     WHERE created_at < NOW() - INTERVAL '1 week' 
     $$ 
   );

Replace your_table with the actual table name.

3. Monitor and Manage Jobs:

Access the Cron interface within the Supabase Dashboard to view job statuses, logs, and manage schedules.

Best Practices:

Concurrency Management: For optimal performance, limit the number of concurrent jobs to no more than eight, and ensure each job runs for no longer than ten minutes.
Security Considerations: Use the SECURITY INVOKER clause when creating functions to ensure they execute with the caller's permissions. Additionally, control function execution by granting specific roles the necessary privileges.
Regular Maintenance: Periodically review and unschedule jobs that are no longer necessary to maintain an efficient task schedule.

By leveraging Supabase Cron, developers can automate routine tasks seamlessly within their PostgreSQL environment, leading to more efficient and maintainable applications.