Background Jobs

Durable newsletter synchronization

The internal sync-newsletter-preferences job is seeded to run every minute. Newsletter forms save the requested choice before any provider call. Mon compte and GET /api/newsletter/status distinguish that saved choice from confirmed delivery. The transactional process-pending-emails queue no longer carries newsletter operations.

Configure Brevo or Mailjet credentials and a positive safe-integer newsletter list ID. Missing list configuration and the noop provider cannot accept newsletter work. For Mailjet, create contact metadata properties before accepting public subscriptions: FIRSTNAME, LASTNAME, LOCALE, SOURCE, CONSENT_SOURCE, CONSENT_TEXT and CONSENT_AT, all string properties. Attribute names follow Mailjet's lowercase metadata names in the adapter.

Newer choices supersede queued work. An operation already sent must settle before a newer one can run. Definite rate-limit rejection receives bounded retry; a timeout, ambiguous response or expired processing lease is quarantined as unknown and must never be automatically replayed. A failed or unknown result makes the job report attention required. Job output contains counts and IDs, never recipient details.

To reconcile, use an authenticated platform-admin session with completed admin escalation step-up. GET /api/admin/newsletter?limit=5 lists a bounded page; use its last id as afterId for the next page. Read the recipient and claimed choice to inspect the operation in the configured provider. Confirm that the operation has finished; a network timeout alone is insufficient evidence. Then POST JSON to the same endpoint with id, token, expectedVersion set to the current row version, outcome set to applied or not_applied, and confirmed: true. Browser mutations use the application's CSRF-protected client. A conflict requires refreshing the row. Reconciliation is audited atomically and schedules only the latest choice. Never copy the private recipient listing into logs or job output.

Resolve outstanding unknown operations before changing provider or list configuration. Existing work keeps its original target; configuration changes never silently move it. Personal erasure waits for uncertain newsletter operations to resolve before removing the contact, then removes the local newsletter record with the canonical identity. Local QA simulates provider HTTP and does not certify a managed provider deployment. Erasure of historical contacts in a previously configured provider remains separate GDPR migration work; this increment does not certify it.

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.

Low-credit alert preflight

check-low-credits-alerts prepares eligible Accounts before starting either Push or bell effects. The shared Prisma repository applies preferences, strict thresholds and both cooldown checks before ordering by balance/id and limiting the page in one actor-free worker transaction. Excluded Accounts do not occupy the first page. Only missing preference rows use defaults, and the bounded receipt validates every projected row before delivery starts.

config/low-credits.ts owns dbTimeoutMs=5000, maxAccountsPerRun=1000 and accountConcurrency=10, with positive-integer ceilings of 120000, 10000 and 100. Existing job options default_threshold and cooldown_hours retain defaults from aiConfig.lowCreditsAlert; max_accounts_per_run can override the page up to 10000. Values are validated without coercion or clamping. Thresholds retain SQL's signed int32 range; cooldown hours must be finite and nonnegative with a valid computed date. The former query-batch and history-row settings are removed. No new environment variable is required.

Install the selected complete composition before deploying the worker. Sources and manifest are the sole DDL authority for fresh installations, and init verifies the worker capability and indexes. No preference/history rows leave PostgreSQL. The database request, Push request and response body consume their configured deadlines and parent cancellation; late success is rejected. Indexes support Account order and exact cooldown probes, but the output limit does not bound all rows scanned.

Existing cooldown rules are unchanged: the exact current-owner/Account pair matters, failed Push attempts still count, and matching bell alerts count whether read or unread. Prior-owner history does not suppress a successor. The lower time boundary is inclusive; matching future-dated rows are not newly excluded. checked now counts only selected eligible Accounts, not opt-outs or cooldown exclusions. Each of the ten Account slots stays occupied until both channels settle, even if one throws; confirmed success from the other channel remains counted. Parent cancellation stops later batches, but does not retract completed sends or cancel an already-started bell write.

The selection snapshot is not a lock or durable claim: overlapping runs can still duplicate alerts and state can change before effects. Cooldown history lets later Accounts enter the page, but repeatedly failing candidates without any history can still monopolize it. There is no cross-channel outbox or atomic erasure-to-send fence. The focused local boundary verifies bounded Prisma reads and selection after cooldown without calling a delivery provider.

Email Queue Claiming

Workspace invitation creation and resend commit a durable email intent in the same transaction as the invitation. Keep process-pending-emails enabled: the Members page shows queued, sent or failed delivery, and its resend action retries a terminal failure. Repeated resends while pending or processing coalesce into one intent. The outbox stores an invitation reference and locale, never its bearer token; the worker resolves that token only for a still-pending, unexpired invitation. Accepted, revoked and expired invitations are skipped. The shared per-recipient delivery quota applies across organizations and retries.

process-pending-emails claims work through the shared Prisma queue repository before sending. The worker transaction uses FOR UPDATE SKIP LOCKED, moves rows to processing, increments attempts, and returns a bounded batch so overlapping workers cannot own the same active lease. Rows stuck in processing for more than 15 minutes are eligible for a later retry. Delivery is at least once: a crash after provider acceptance but before durable settlement can cause a duplicate message on 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

pnpm 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.
• The initialization wizard feature-gates optional jobs. Affiliate integrity migrations also upsert maturation and click-retention jobs for existing installations; once data exists, those financial/privacy obligations continue even while AFFILIATES_ENABLED=false.
• All inserts use INSERT … ON CONFLICT (name) DO NOTHING, so re-running the wizard never overwrites admin-tuned cron expressions or config payloads.
• For either native provider, the AFTER INSERT trigger on public.jobs calls sync_pg_cron_job() per row. supabase_pg_cron schedules an authenticated HTTP call; postgres_pg_cron schedules a fixed, secret-free SQL enqueue command.
• After the seed, the wizard re-syncs all enabled rows. This covers pre-existing schedules and, for the Supabase HTTP topology, URL or Bearer-token drift.

If a feature is enabled after init (e.g. flipping AFFILIATES_ENABLED=true later), either re-run pnpm 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 the 30/min/route admin-write limit. 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 claims or 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. With postgres_pg_cron, run pnpm jobs:worker as a supervised process so the fixed SQL cron enqueue is followed by TypeScript handler execution.

Database Schema

The jobs system uses jobs (definitions with name, cron expression, handler, config, and enabled status), job_runs (durable leased execution history with retry and terminal state), and job_handlers (bounded webhook handler definitions). The email worker claims pending_emails through the same shared Prisma worker boundary. Supabase defaults to supabase_pg_cron; Neon defaults to postgres_pg_cron; both retain external_runner as an explicit fallback.

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                               │
└───────────────┬──────────────────────────────────┬───────────────────┘
                │ supabase_pg_cron                 │ postgres_pg_cron
                │ HTTP POST (pg_net)               │ fixed SQL enqueue
                ▼                                  ▼
┌───────────────────────────────┐   ┌──────────────────────────────────┐
│        /api/jobs/run          │   │ pending job_runs occurrence      │
│ Bearer JOBS_SECRET_KEY        │   │ no URL, secret, pg_net or Vault  │
└───────────────┬───────────────┘   └────────────────┬─────────────────┘
                │                                    │ pnpm jobs:worker
                └──────────────────┬─────────────────┘
                                   ▼
┌─────────────────────────────────────────────────────────────────────┐
│                      JOB RUNNER                                      │
│                  lib/jobs/runner.ts                                  │
│                                                                      │
│   1. Find job by ID or name                                          │
│   2. Check if job is enabled                                         │
│   3. Create or claim 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 scheduler reconfiguration.

What you can do on the page

Security model

• Page + API gated by apiSecurity.admin() (admin role + method/route-scoped limits + CSRF; sensitive job mutations retain 5/min/route).
• 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.
• Successful mutations and audit commit together. SQL writes the canonical administrator, fixed action and safe IDs/counts to admin_logs. An audit failure rolls back native changes. Missing targets do not create audit rows; successful empty purges do.
• Every SQL command is masked in full. The common Prisma snapshot returns [REDACTED] for application and native jobs alike.
• Orphan filters are provider-specific and anchored. Supabase recognizes only its net.http_post job endpoint commands; PostgreSQL/Neon recognizes only the fixed private enqueue command. Unrelated extension or user-defined cron entries are never classified as application orphans.

Shared database commands

Private capabilities execute through shared Prisma after canonical administrator checks. One read-only snapshot returns the list and statistics. Toggle, unschedule and purge use an atomic actor transaction with a five-second deadline, fixed native operations and audit. The runtime cannot access raw cron tables or assume a privileged owner role.

config/cron-admin.ts bounds reads and purge to 200 native entries. Purge validates every candidate before modifying it; a native failure rolls back the whole batch. Successful purge returns { purged, failed: 0 }. Single-entry operations reject ambiguous application references, and unschedule clears a reference only after native removal succeeds.

Missing native capabilities and targets owned by an unavailable native operator return an unsupported outcome. Reads require execution history; a 24-hour filter does not guarantee a bounded scan when the native table has no time index. Managed-provider support and realistic history performance require separate certification.

Prerequisites Setup

If you need to set up the Supabase HTTP scheduler manually, follow these steps. For PostgreSQL/Neon, select postgres_pg_cron, install pg_cron through the canonical composition, and supervise pnpm jobs:worker; do not add pg_net, Vault, a Jobs API URL, or a scheduler Bearer secret.

Step 1: Enable Extensions

For supabase_pg_cron, 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 JOBS_SECRET_KEY. This key authenticates HTTP job execution requests. Store it securely in Supabase Vault for supabase_pg_cron. It is not part of the postgres_pg_cron scheduler path.

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 HTTP execution requests. For supabase_pg_cron, it must match the value stored in Supabase Vault.

Step 4: Configure Jobs API URL

For supabase_pg_cron, 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 Supabase HTTP-scheduled jobs during local development, use manual execution or a public tunnel. With postgres_pg_cron, keep pnpm jobs:worker running locally; no tunnel is needed because pg_cron only inserts the durable occurrence.

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 provider-specific trigger function that automatically syncs job definitions with pg_cron whenever a job is created, updated, or deleted. Supabase entries dispatch authenticated HTTP; PostgreSQL/Neon entries contain only the fixed private enqueue call.

Verification

After setting up, verify everything works:

1. In the selected database SQL console, run SELECT jobname, schedule, active FROM cron.job; — your enabled native-scheduler jobs should be listed with their cron expressions. On PostgreSQL/Neon, confirm commands contain the fixed enqueue call and no URL or credential.
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 JOB_SCHEDULER_PROVIDER to supabase_pg_cron, postgres_pg_cron, or external_runner for a compatible database composition. Set JOBS_SECRET_KEY to a secure random string when Supabase cron, an external scheduler, or an operator calls the HTTP execution endpoint. postgres_pg_cron does not use that secret; it requires a supervised pnpm jobs:worker process. For webhook-based handlers, configure the handler's own authentication method (Bearer token, basic auth, API key, or custom header) in the admin dashboard.