Supabase Cron Concepts¶

pg_cron là gì?¶

pg_cron là PostgreSQL extension cho phép schedule và chạy SQL jobs trực tiếp trong database.

┌─────────────────────────────────────────────────────────────┐
│                   SUPABASE + pg_cron                         │
│                                                              │
│  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐    │
│  │  Schedule   │────▶│  pg_cron    │────▶│   Execute   │    │
│  │  (cron)     │     │  Scheduler  │     │ SQL/Function│    │
│  └─────────────┘     └─────────────┘     └─────────────┘    │
│                            │                                 │
│                            ▼                                 │
│                      ┌───────────┐                          │
│                      │PostgreSQL │                          │
│                      │  Tables   │                          │
│                      └───────────┘                          │
└─────────────────────────────────────────────────────────────┘

Tại sao dùng pg_cron?¶

Lợi ích¶

Lợi ích	Chi tiết
Tích hợp sẵn	Không cần external cron service
Direct DB access	Chạy SQL trực tiếp, không cần API call
Reliable	Chạy trong database, không mất jobs
Transactional	ACID compliant
Monitoring	Logs trong database

Use Cases¶

Database maintenance (cleanup, vacuum)
Data aggregation (daily/weekly reports)
Recurring tasks (send reminders)
Sync data between tables
Archive old data

Cron Expression Syntax¶

Format¶

┌───────────── minute (0 - 59)
│ ┌───────────── hour (0 - 23)
│ │ ┌───────────── day of month (1 - 31)
│ │ │ ┌───────────── month (1 - 12)
│ │ │ │ ┌───────────── day of week (0 - 6) (Sunday = 0)
│ │ │ │ │
* * * * *

Common Examples¶

Expression	Description
`* * * * *`	Every minute
`/5 * * *`	Every 5 minutes
`0 * * * *`	Every hour
`0 0 * * *`	Every day at midnight
`0 9 * * 1`	Every Monday at 9 AM
`0 0 1 * *`	First day of month
`0 0 * * 0`	Every Sunday

Enable pg_cron¶

Via Dashboard¶

1. Supabase Dashboard
2. Database → Extensions
3. Search "pg_cron"
4. Enable

Via SQL¶

-- Enable pg_cron extension (usually already enabled)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Grant usage to postgres role
GRANT USAGE ON SCHEMA cron TO postgres;

Create Cron Jobs¶

Basic Syntax¶

SELECT cron.schedule(
  'job-name',           -- unique job name
  '0 * * * *',          -- cron expression
  'SQL COMMAND HERE'    -- SQL to execute
);

Example: Cleanup old sessions¶

SELECT cron.schedule(
  'cleanup-old-sessions',
  '0 2 * * *',  -- Every day at 2 AM
  $$
    DELETE FROM auth.sessions
    WHERE created_at < NOW() - INTERVAL '30 days';
  $$
);

Example: Update statistics¶

SELECT cron.schedule(
  'update-daily-stats',
  '0 0 * * *',  -- Every day at midnight
  $$
    INSERT INTO daily_stats (date, total_users, total_orders)
    SELECT
      CURRENT_DATE - 1,
      (SELECT COUNT(*) FROM users WHERE created_at < CURRENT_DATE),
      (SELECT COUNT(*) FROM orders WHERE created_at::date = CURRENT_DATE - 1);
  $$
);

Call Functions¶

Create function first¶

-- Create a function
CREATE OR REPLACE FUNCTION cleanup_expired_tokens()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  DELETE FROM tokens WHERE expires_at < NOW();

  -- Log the cleanup
  INSERT INTO job_logs (job_name, executed_at, rows_affected)
  VALUES ('cleanup_expired_tokens', NOW(), (SELECT COUNT(*)));
END;
$$;

Schedule function¶

SELECT cron.schedule(
  'cleanup-tokens',
  '0 */6 * * *',  -- Every 6 hours
  'SELECT cleanup_expired_tokens()'
);

Manage Cron Jobs¶

List all jobs¶

SELECT * FROM cron.job;

-- Output:
-- jobid | schedule    | command                          | nodename  | nodeport | database | username
-- 1     | 0 * * * *   | SELECT cleanup_expired_tokens()  | localhost | 5432     | postgres | postgres

View job history¶

SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

-- Output:
-- jobid | runid | job_pid | database | username | command | status    | return_message | start_time | end_time
-- 1     | 123   | 45678   | postgres | postgres | SELECT..| succeeded | 1 row          | 2024-01-01 | 2024-01-01

Unschedule job¶

-- By job name
SELECT cron.unschedule('cleanup-tokens');

-- By job ID
SELECT cron.unschedule(1);

Advanced Patterns¶

1. Send notifications via Edge Function¶

-- Create function that calls Edge Function
CREATE OR REPLACE FUNCTION send_daily_digest()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  response jsonb;
BEGIN
  SELECT content::jsonb INTO response
  FROM http_post(
    'https://xxx.supabase.co/functions/v1/send-digest',
    '{}',
    'application/json'
  );
END;
$$;

-- Schedule
SELECT cron.schedule(
  'daily-digest',
  '0 9 * * *',  -- 9 AM daily
  'SELECT send_daily_digest()'
);

2. Queue jobs for Cloudflare¶

-- Add to queue, let Cloudflare process
CREATE OR REPLACE FUNCTION queue_weekly_report()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  -- Add job to pgmq queue
  PERFORM pgmq.send(
    'report_queue',
    jsonb_build_object(
      'type', 'weekly_report',
      'week', date_trunc('week', NOW())
    )
  );
END;
$$;

SELECT cron.schedule(
  'queue-weekly-report',
  '0 6 * * 1',  -- Monday 6 AM
  'SELECT queue_weekly_report()'
);

Monitoring & Debugging¶

Check recent runs¶

SELECT
  j.jobname,
  d.status,
  d.start_time,
  d.end_time,
  d.return_message
FROM cron.job j
JOIN cron.job_run_details d ON j.jobid = d.jobid
ORDER BY d.start_time DESC
LIMIT 10;

Check failed jobs¶

SELECT *
FROM cron.job_run_details
WHERE status = 'failed'
ORDER BY start_time DESC;

Create monitoring table¶

CREATE TABLE job_logs (
  id SERIAL PRIMARY KEY,
  job_name TEXT NOT NULL,
  executed_at TIMESTAMPTZ DEFAULT NOW(),
  status TEXT NOT NULL,
  message TEXT,
  rows_affected INTEGER
);

-- Update function to log
CREATE OR REPLACE FUNCTION cleanup_expired_tokens()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  deleted_count INTEGER;
BEGIN
  DELETE FROM tokens WHERE expires_at < NOW();
  GET DIAGNOSTICS deleted_count = ROW_COUNT;

  INSERT INTO job_logs (job_name, status, rows_affected)
  VALUES ('cleanup_expired_tokens', 'success', deleted_count);
EXCEPTION WHEN OTHERS THEN
  INSERT INTO job_logs (job_name, status, message)
  VALUES ('cleanup_expired_tokens', 'error', SQLERRM);
END;
$$;

Best Practices¶

1. Use meaningful job names¶

-- ✅ Good
SELECT cron.schedule('cleanup-expired-sessions-daily', ...);

-- ❌ Bad
SELECT cron.schedule('job1', ...);

2. Idempotent jobs¶

-- ✅ Safe to run multiple times
DELETE FROM sessions WHERE expires_at < NOW();

-- ❌ May cause issues
UPDATE stats SET count = count + 1;

3. Timeout handling¶

-- Set statement timeout for long-running jobs
CREATE OR REPLACE FUNCTION long_running_job()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  SET LOCAL statement_timeout = '5min';
  -- Your long operation here
END;
$$;

4. Avoid overlapping¶

-- Use advisory locks
CREATE OR REPLACE FUNCTION exclusive_job()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  IF NOT pg_try_advisory_lock(12345) THEN
    RAISE NOTICE 'Job already running';
    RETURN;
  END IF;

  -- Your job here

  PERFORM pg_advisory_unlock(12345);
END;
$$;

Tổng kết¶

pg_cron Key Points¶

SQL-based scheduling - chạy trong database
Direct access - không cần API calls
Reliable - logs và monitoring built-in
Limitations - chỉ chạy SQL, UTC timezone

Common Jobs¶

Job Type	Schedule	Example
Cleanup	Daily	Delete expired data
Stats	Hourly	Aggregate metrics
Reports	Weekly	Generate reports
Maintenance	Daily	Vacuum, analyze

Next: So sánh với Cloudflare Cron¶

Khi nào dùng pg_cron
Khi nào dùng Cloudflare Cron Triggers
Best practices cho từng loại

Q&A¶

Có jobs nào cần chạy trong dự án của bạn?
Frequency requirements?
Cần external API calls từ cron jobs?