Bỏ qua

Light Development Stack

Cron: Supabase vs Cloudflare

teguru-labs/light-development

Cron: Supabase vs Cloudflare¶

Tổng quan so sánh¶

┌─────────────────────────────────────────────────────────────────┐
│                      CRON COMPARISON                             │
├────────────────────────────┬────────────────────────────────────┤
│    SUPABASE (pg_cron)      │     CLOUDFLARE CRON TRIGGERS       │
├────────────────────────────┼────────────────────────────────────┤
│  PostgreSQL extension      │  Workers scheduled execution       │
│  Runs SQL/Functions        │  Runs JavaScript/TypeScript        │
│  Direct DB access          │  API access to DB                  │
│  UTC timezone only         │  UTC timezone only                 │
│  Included in plan          │  Included in Workers plan          │
└────────────────────────────┴────────────────────────────────────┘

So sánh chi tiết¶

Tiêu chí	Supabase Cron (pg_cron)	Cloudflare Cron Triggers
Chạy ở đâu	Trong PostgreSQL	Edge Workers (global)
Ngôn ngữ	SQL / PL/pgSQL	JavaScript / TypeScript
Min interval	1 phút	1 phút
Max interval	Unlimited	Unlimited
Timezone	UTC only	UTC only
Timeout	Statement timeout (DB)	30s (free), 15min (paid)
DB Access	Direct (same process)	Via API (network call)
External APIs	Via http extension	Native fetch
Monitoring	job_run_details table	Workers Analytics
Retry	Manual	Manual

Supabase Cron (pg_cron)¶

Ưu điểm¶

✅ Direct Database Access
   - Không cần network call
   - Fastest for DB operations

✅ Transactional
   - ACID compliant
   - Rollback on failure

✅ Simple Setup
   - Just SQL
   - No deployment needed

✅ Data Locality
   - Same region as database
   - No latency

Nhược điểm¶

❌ SQL Only
   - Limited to SQL/PL/pgSQL
   - Complex logic = complex functions

❌ External APIs Difficult
   - Needs http extension
   - Not as flexible as JavaScript

❌ No Global Distribution
   - Single region
   - Latency for global operations

❌ Monitoring Limited
   - Basic logs in table
   - No built-in alerting

Cloudflare Cron Triggers¶

Ưu điểm¶

✅ Full JavaScript/TypeScript
   - Any npm package
   - Complex logic easy

✅ External API Native
   - fetch built-in
   - Webhooks, notifications easy

✅ Global Execution
   - Runs at edge
   - Low latency worldwide

✅ Rich Ecosystem
   - KV, R2, Queue integration
   - Full Workers capabilities

Nhược điểm¶

❌ Network Call to DB
   - Must call Supabase API
   - Extra latency

❌ Timeout Limits
   - 30s free tier
   - 15min paid tier

❌ Not Transactional with DB
   - Separate operations
   - Potential inconsistency

❌ Deployment Required
   - Code changes need deploy
   - Not as instant as SQL

Use Case: Database Maintenance¶

Supabase pg_cron (Recommended)¶

-- Cleanup expired sessions
SELECT cron.schedule(
  'cleanup-sessions',
  '0 3 * * *',  -- 3 AM daily
  $$
    DELETE FROM auth.sessions
    WHERE expires_at < NOW() - INTERVAL '7 days';

    -- Also vacuum the table
    VACUUM ANALYZE auth.sessions;
  $$
);

-- Archive old data
SELECT cron.schedule(
  'archive-old-orders',
  '0 2 * * 0',  -- 2 AM every Sunday
  $$
    WITH moved AS (
      DELETE FROM orders
      WHERE created_at < NOW() - INTERVAL '1 year'
      RETURNING *
    )
    INSERT INTO orders_archive SELECT * FROM moved;
  $$
);

Cloudflare (NOT recommended for this)¶

// ❌ Unnecessary network calls
export default {
  async scheduled(event: ScheduledEvent, env: Env) {
    // Have to call API for each operation
    await fetch(`${env.SUPABASE_URL}/rest/v1/rpc/cleanup_sessions`, {
      method: 'POST',
      headers: { 'Authorization': `Bearer ${env.SERVICE_KEY}` },
    });
    // Slower, more complex
  }
};

Use Case: External API Calls¶

Cloudflare Cron (Recommended)¶

// Send daily digest email
export default {
  async scheduled(event: ScheduledEvent, env: Env) {
    // Get users needing digest
    const { data: users } = await supabase
      .from('users')
      .select('id, email')
      .eq('digest_enabled', true);

    // Send emails via external service
    for (const user of users) {
      await fetch('https://api.sendgrid.com/v3/mail/send', {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${env.SENDGRID_KEY}`,
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({
          to: user.email,
          subject: 'Daily Digest',
          // ...
        }),
      });
    }
  }
};

Supabase (More complex)¶

-- Needs http extension
CREATE EXTENSION IF NOT EXISTS http;

CREATE OR REPLACE FUNCTION send_daily_digest()
RETURNS void AS $$
DECLARE
  user_record RECORD;
  response http_response;
BEGIN
  FOR user_record IN
    SELECT id, email FROM users WHERE digest_enabled
  LOOP
    -- Cumbersome HTTP call from SQL
    SELECT * INTO response FROM http_post(
      'https://api.sendgrid.com/v3/mail/send',
      json_build_object('to', user_record.email)::text,
      'application/json'
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;

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

Use Case: Data Aggregation¶

Supabase pg_cron (Recommended)¶

-- Calculate daily statistics
SELECT cron.schedule(
  'daily-stats',
  '0 0 * * *',  -- Midnight
  $$
    INSERT INTO daily_stats (
      date,
      total_orders,
      total_revenue,
      new_users,
      active_users
    )
    SELECT
      CURRENT_DATE - 1 AS date,
      COUNT(*) FILTER (WHERE type = 'order') AS total_orders,
      SUM(amount) FILTER (WHERE type = 'order') AS total_revenue,
      COUNT(*) FILTER (WHERE type = 'signup') AS new_users,
      COUNT(DISTINCT user_id) AS active_users
    FROM events
    WHERE created_at::date = CURRENT_DATE - 1;
  $$
);

Cloudflare (Less efficient)¶

// ❌ Multiple API calls, slower
export default {
  async scheduled(event: ScheduledEvent, env: Env) {
    const yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - 1);

    // Multiple queries needed
    const [orders, users, events] = await Promise.all([
      supabase.from('orders').select('*').gte('created_at', yesterday),
      supabase.from('users').select('*').gte('created_at', yesterday),
      supabase.from('events').select('*').gte('created_at', yesterday),
    ]);

    // Calculate in JS (less efficient than SQL)
    const stats = {
      total_orders: orders.data.length,
      // ...
    };

    await supabase.from('daily_stats').insert(stats);
  }
};

Use Case: Queue + Cron Hybrid¶

Cloudflare Cron → Supabase Queue¶

// Cloudflare Cron: Trigger batch job
export default {
  async scheduled(event: ScheduledEvent, env: Env) {
    // Get pending tasks
    const { data: tasks } = await supabase
      .from('scheduled_tasks')
      .select('*')
      .eq('status', 'pending')
      .lte('run_at', new Date().toISOString());

    // Enqueue to Supabase pgmq for processing
    for (const task of tasks) {
      await supabase.rpc('pgmq_send', {
        queue_name: 'task_queue',
        message: task,
      });
    }
  }
};

Supabase Cron → Cloudflare Queue¶

-- pg_cron: Enqueue to Cloudflare via Edge Function
CREATE OR REPLACE FUNCTION trigger_cf_processing()
RETURNS void AS $$
BEGIN
  PERFORM http_post(
    'https://xxx.workers.dev/enqueue',
    (SELECT json_agg(t) FROM pending_tasks t),
    'application/json'
  );
END;
$$ LANGUAGE plpgsql;

SELECT cron.schedule(
  'trigger-cf-queue',
  '*/5 * * * *',  -- Every 5 minutes
  'SELECT trigger_cf_processing()'
);

Decision Matrix¶

Chọn Supabase pg_cron khi:¶

┌─────────────────────────────────────────────────────────────┐
│  ✅ Database maintenance (cleanup, vacuum, archive)         │
│  ✅ Data aggregation (reports, statistics)                  │
│  ✅ Simple recurring DB updates                             │
│  ✅ Transactional operations                                │
│  ✅ No external API calls needed                            │
│  ✅ SQL logic is sufficient                                 │
└─────────────────────────────────────────────────────────────┘

Chọn Cloudflare Cron khi:¶

┌─────────────────────────────────────────────────────────────┐
│  ✅ External API calls (email, webhooks, notifications)    │
│  ✅ Complex business logic (easier in JS/TS)               │
│  ✅ Global execution needed                                 │
│  ✅ Integration với Workers ecosystem (KV, Queue, R2)       │
│  ✅ Long-running tasks (with paid tier)                     │
│  ✅ Multiple services orchestration                         │
└─────────────────────────────────────────────────────────────┘

Comparison Summary¶

┌─────────────────────────────────────────────────────────────┐
│                    DECISION FLOWCHART                        │
│                                                              │
│  Task requires direct DB operations?                        │
│         │                                                    │
│    YES ─┴─ NO                                               │
│    │       │                                                │
│    ▼       ▼                                                │
│ pg_cron   Task needs external API?                          │
│                │                                            │
│           YES ─┴─ NO                                        │
│           │       │                                         │
│           ▼       ▼                                         │
│       CF Cron   Complex JS logic?                           │
│                      │                                      │
│                 YES ─┴─ NO                                  │
│                 │       │                                   │
│                 ▼       ▼                                   │
│             CF Cron   Either works                          │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Tổng kết¶

Quick Reference¶

Scenario	Recommendation
Delete old records	pg_cron
Generate reports from DB	pg_cron
Send emails/notifications	Cloudflare
Call external webhooks	Cloudflare
Sync with external systems	Cloudflare
Update statistics	pg_cron
Complex business rules	Cloudflare

Key Takeaways¶

pg_cron = Database-centric, SQL-based, direct access
Cloudflare = API-centric, JS-based, external integrations
Both can work together in hybrid patterns

Q&A¶

Jobs nào trong dự án cần external API?
Có complex logic nào khó viết bằng SQL?
Cần global execution không?