Background Jobs

The boilerplate includes a robust background job system for scheduled tasks, cleanup operations, and async processing. Jobs can be code-based handlers or webhook-based for external services.

Jobs list with run history and status

Code and webhook handlers management

File Structure

lib/jobs/
├── handlers.ts           # Code-based job handlers registry
└── runner.ts             # Job execution engine

types/
└── jobs.ts               # TypeScript interfaces

app/
├── [locale]/(admin)/admin-dashboard/jobs/
│   ├── page.tsx          # Jobs list & stats
│   ├── new/page.tsx      # Create job
│   ├── [jobId]/page.tsx  # Job detail & runs
│   └── handlers/page.tsx # Handler management
└── api/
    ├── jobs/run/route.ts      # Job execution endpoint
    └── admin/jobs/
        ├── route.ts           # Jobs CRUD
        └── handlers/route.ts  # Handlers CRUD

TypeScript Types

Job-related types are defined in types/jobs.ts and include interfaces for job definitions, run records, handler configurations, and execution results. These types ensure type safety across the job creation, scheduling, and execution pipeline.

Built-in Handlers

13 production handlers + 2 dev-only test fixtures. The Gate column is the feature flag scripts/init-project.js reads to decide whether to seed the row at init time. Test fixtures are skipped by the seed step — admins can create them by hand from /admin-dashboard/jobs if needed.

Email Queue Claiming

process-pending-emails claims work through the claim_pending_emails(p_limit) RPC before sending. The RPC is service-role only, uses FOR UPDATE SKIP LOCKED, moves rows to processing, increments attempts, and returns a bounded batch so overlapping cron runs cannot send the same email twice. Rows stuck in processing for more than 15 minutes are eligible for a later retry.

Handler-sent customer emails are locale-parameterized. Billing notification subjects use email.billingNotif.*, license warnings use email.licenseExpiration.*, and organization deletion notices use email.orgDeleted.*. Job handlers pass a locale from the payload, owner profile, or configured default locale instead of hardcoding a language inside the handler.

Init-Time Seeding

npm run init seeds the jobs table with the production handlers above based on the answers given to the wizard:

• Always-on handlers are inserted unconditionally.
• Feature-gated handlers (purge-error-logs, check-license-expiration, approve-mature-affiliate-conversions) are inserted only when their gate is on at init time.
• All inserts use INSERT … ON CONFLICT (name) DO NOTHING, so re-running the wizard never overwrites admin-tuned cron expressions or config payloads.
• The AFTER INSERT trigger on public.jobs calls sync_pg_cron_job() per row, so newly seeded rows register with pg_cron immediately using the URL + Vault secret the wizard set earlier in the same run.
• After the seed, the wizard re-syncs all enabled rows to cover pre-existing jobs from older runs whose URL / Bearer token may have drifted.

If a feature is enabled after init (e.g. flipping AFFILIATES_ENABLED=true later), either re-run npm run init or create the missing job from /admin-dashboard/jobs — function names must match the registry keys above exactly.

Creating a Code Handler

To create a new code-based handler, add a function to lib/jobs/handlers.ts that accepts a job configuration object and returns a result. Register the handler in the handlers map with a unique key. Your handler can access the database, call external APIs, send emails, or perform any server-side operation. The handler receives the job's metadata (including custom config JSON) and should return a success/error status with optional output data.

Admin Email Notifications

The boilerplate includes a centralized admin notification system that alerts the super-admin by email when critical events occur. Notifications are queued via the email retry system for reliable delivery.

Use sendAdminNotification() from lib/email/admin-notifications.ts to send custom admin alerts:

import { sendAdminNotification } from '@/lib/email/admin-notifications'

await sendAdminNotification({
  subject: '[Alert] Something important',
  title: 'Alert Title',
  body: 'Description of what happened.',
  details: {
    'Key': 'Value',
    'Another Key': 'Another value',
  },
  tags: ['admin-notification', 'custom'],
})

Cron Expressions

Job Execution API

The job execution endpoint at /api/jobs/run accepts job triggers from cron schedules, manual admin actions, or external webhooks. Cron/webhook calls authenticate with Authorization: Bearer JOBS_SECRET_KEY using constant-time comparison, webhook rate limiting, and bounded JSON parsing. Manual dashboard runs authenticate as an admin session and additionally require Origin/CSRF validation plus strict rate limiting. After auth, the route loads the job definition, resolves the handler (code-based or webhook), and executes it with timeout protection and error handling.

Webhook Handlers

Webhook handlers call external URLs instead of running local code. Useful for integrating with external services or serverless functions.

Handler create/update APIs validate and sanitize all editable fields, require http or https URLs, reject localhost/private network targets, and never return stored webhook auth secrets from list or detail responses.

Job Runner

The job runner in lib/jobs/runner.ts manages the execution lifecycle: it creates a job_runs record, invokes the handler (either a local function from the handlers registry or an HTTP request to a webhook URL), captures the output or error, records the execution duration, and updates the run status. Failed jobs can be configured for automatic retry with configurable delays.

Database Schema

The jobs system uses jobs (definitions with name, cron expression, handler, config, and enabled status), job_runs (execution history with status, output, error, duration, and timestamps), and job_handlers (webhook-based handler definitions with URL, authentication method, and headers). The email worker also uses pending_emails plus the service-role-only claim_pending_emails(p_limit) RPC for concurrency-safe queue claims. Database triggers automatically sync job schedules with pg_cron.

How Jobs Work - Overview

┌─────────────────────────────────────────────────────────────────────┐
│                        ADMIN DASHBOARD                               │
│                    /admin-dashboard/jobs                             │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐                  │
│  │ Create Job  │  │ Update Job  │  │ Delete Job  │                  │
│  └──────┬──────┘  └──────┬──────┘  └──────┬──────┘                  │
└─────────┼────────────────┼────────────────┼─────────────────────────┘
          │                │                │
          ▼                ▼                ▼
┌─────────────────────────────────────────────────────────────────────┐
│                     DATABASE TRIGGER                                 │
│               trigger_sync_pg_cron()                                 │
│                                                                      │
│   • On INSERT/UPDATE: Schedule job in pg_cron                        │
│   • On DELETE: Unschedule job from pg_cron                           │
│   • Checks: is_enabled + cron_expression                             │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
                               ▼
┌─────────────────────────────────────────────────────────────────────┐
│                         pg_cron                                      │
│              PostgreSQL Cron Scheduler                               │
│                                                                      │
│   Automatically executes at scheduled times:                         │
│   • Every minute? ─────────► Triggers job                            │
│   • Every hour? ───────────► Triggers job                            │
│   • Daily at 3am? ─────────► Triggers job                            │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
                               │ HTTP POST (pg_net)
                               ▼
┌─────────────────────────────────────────────────────────────────────┐
│                    /api/jobs/run                                     │
│                                                                      │
│   Request:                                                           │
│   {                                                                  │
│     "job_id": "uuid",                                                │
│     "triggered_by": "cron"                                           │
│   }                                                                  │
│                                                                      │
│   Auth: Bearer JOBS_SECRET_KEY or admin session + CSRF               │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
                               ▼
┌─────────────────────────────────────────────────────────────────────┐
│                      JOB RUNNER                                      │
│                  lib/jobs/runner.ts                                  │
│                                                                      │
│   1. Find job by ID or name                                          │
│   2. Check if job is enabled                                         │
│   3. Create job_runs record                                          │
│   4. Execute handler (code or webhook)                               │
│   5. Update job_runs with result                                     │
│   6. Update job statistics                                           │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
              ┌────────────────┴────────────────┐
              ▼                                 ▼
┌─────────────────────────┐       ┌─────────────────────────┐
│     CODE HANDLER        │       │    WEBHOOK HANDLER      │
│  lib/jobs/handlers.ts   │       │   (External URL)        │
│                         │       │                         │
│  • cleanup-sessions     │       │  • Your API endpoint    │
│  • purge-logs           │       │  • Serverless function  │
│  • sync-stripe          │       │  • External service     │
│  • generate-analytics   │       │                         │
└─────────────────────────┘       └─────────────────────────┘
        

Automatic pg_cron Scheduling

The boilerplate automatically manages pg_cron entries when you create, update, or delete jobs. You don't need to write any SQL - just use the admin dashboard!

Cron Admin Dashboard /admin-dashboard/jobs/cron

A live operations view of every entry in the Postgres cron.job table — sibling to the regular Jobs page, but scoped to raw pg_cron state rather than the public.jobs definitions. Use it to monitor what pg_cron actually has scheduled, pause/resume entries without deleting their jobs row, and clean up orphans left behind after job deletes or URL/secret rotations.

What you can do on the page

Security model

• Page + API gated by apiSecurity.admin() (admin role + 5/min rate limit + CSRF).
• Destructive actions (unschedule, purge-orphans) require recent auth via appConfig.security.requireRecentAuthForAdminEscalationMinutes. A session-hijacked admin cannot tear down platform infrastructure (GDPR job, email queue, log purge) in one POST without re-authenticating.
• Every mutation writes to admin_logs with {admin_user_id, action, target_type:'cron_job', details:{jobid, ...}} for forensic trail.
• Bearer tokens are redacted at the SQL layer. The admin_list_cron_jobs() RPC runs regexp_replace(cj.command, 'Bearer\\s+[A-Za-z0-9._\\-]+', 'Bearer ***', 'g') so JOBS_SECRET_KEY never crosses the API/UI boundary even when the command field is rendered.
• Orphan filter is anchored on command ILIKE 'select net.http_post(%' AND command LIKE '%/api/jobs/run%' so unrelated extension cron jobs (other Supabase extensions, user-defined entries) are never classified as orphans nor purged.

Underlying RPCs

Five SECURITY DEFINER functions, all REVOKE EXECUTE FROM authenticated, anon, public + GRANT EXECUTE TO service_role, with an auth.role() = 'service_role' guard inside the body for defense-in-depth:

• admin_list_cron_jobs() — left-joins cron.job to public.jobs, marks orphans, redacts Bearer tokens.
• admin_get_cron_stats() — single-call aggregator (counts + 24h cron.job_run_details roll-up). cron.job_run_details is wrapped in EXCEPTION when undefined_table so the query degrades gracefully on older pg_cron.
• admin_set_cron_job_active(p_jobid bigint, p_active boolean)
• admin_unschedule_cron_job(p_jobid bigint)
• admin_purge_orphan_cron_jobs() returns jsonb — returns { purged, failed } so per-row swallowed errors stay visible.

Prerequisites Setup

If you need to set up manually, follow these steps:

Step 1: Enable Extensions

Enable the pg_cron and pg_net extensions in your Supabase project. Go to Database → Extensions in the Supabase Dashboard and enable both. These allow scheduled tasks and HTTP requests from within PostgreSQL.

Step 2: Generate and Store Secret Key

Generate a secure random string for the JOBS_SECRET_KEY. This key authenticates all job execution requests. Store it securely in Supabase Vault for use by pg_cron functions.

Step 3: Add to Environment

Add the same JOBS_SECRET_KEY value to your .env.local file so the Next.js application can authenticate incoming job execution requests. The key must match the value stored in Supabase Vault for pg_cron triggers to work correctly.

Step 4: Configure Jobs API URL

Go to Admin Dashboard → Settings and set the Jobs API URL to your deployed origin plus the job endpoint:

# Production — must be a publicly reachable URL (pg_cron runs on Supabase)
https://your-domain.com/api/jobs/run

Local Development Options

For testing scheduled jobs during local development, you have two options:

curl -X POST http://localhost:3777/api/jobs/run \
  -H "Authorization: Bearer $JOBS_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{ "job_id": "<uuid-from-admin-dashboard-jobs>" }'

ngrok http 3777
# Copy the https://<id>.ngrok-free.app URL, then set
# Admin → Settings → Jobs API URL = https://<id>.ngrok-free.app/api/jobs/run

Additional Schema (pg_cron tracking)

The jobs system adds a trigger function that automatically syncs job definitions with pg_cron whenever a job is created, updated, or deleted. When a job's cron expression or enabled status changes, the trigger updates the pg_cron schedule accordingly.

Verification

After setting up, verify everything works:

1. In Supabase SQL editor, run SELECT jobname, schedule, active FROM cron.job; — your enabled jobs should be listed with their cron expressions.
2. From /admin-dashboard/jobs, click Run Now on a safe job (e.g. cleanup-sessions) and confirm a new row appears in job_runs with status = 'success'.
3. Check SELECT * FROM job_runs ORDER BY created_at DESC LIMIT 5; for recent executions and durations.
4. If LOGS_ENABLED=true, watch /admin-dashboard/logs for any jobs-category errors after the first scheduled tick.

Troubleshooting

Quick Start Example

Here's how to create a working scheduled job in 3 steps:

Environment Variables

Set JOBS_SECRET_KEY in your .env.local file to a secure random string. This key authenticates all job execution requests from cron triggers, manual runs, and webhook calls. Without this key, the job execution endpoint rejects all requests. For webhook-based handlers, also configure the handler's authentication method (bearer token, basic auth, API key, or custom header) in the admin dashboard.