Cron & Scheduling

AnvilBase runs recurring work with pg_cron. One scheduler, in the platform database, runs each project’s job inside that project’s own database — so a cron job can touch only its own project’s data. No external scheduler, no extra container: the database runs the jobs.

There are two ways to drive cron:

• The Cron data-plane API (recommended for apps) — a REST + SDK surface over HTTP, the Upstash-cron / Supabase-cron equivalent. Server-side only: it requires a project service_role key.
• Raw SQL for the platform’s own jobs (operators) — see Platform jobs.

How it works (the per-project model)

pg_cron’s scheduler runs in exactly one database. On the AnvilBase image that database is anvilbase_platform (cron.database_name=anvilbase_platform, set in the Postgres image; the extension is installed there). The control plane — connected to the platform DB — schedules each per-project job with cron.schedule_in_database(name, schedule, command, 'platform_<project_id>'), which tells the single scheduler to run command inside that project’s database. You never run CREATE EXTENSION pg_cron yourself, and project databases do not carry their own pg_cron — they don’t need it.

Each job is namespaced per project (proj_<project_id>_<name>) so two projects’ identically-named jobs never collide, and every job is pinned to its project’s database. Both the prefix and the database pin are derived from the verified API key, never from client input — the tenant-isolation guarantee (see Isolation & privileges).

Cron data-plane API (HTTP + SDK)

The data-plane API lets a backend holding only a project service_role key schedule, list, delete, and inspect jobs over HTTP. It is gated on service_role by design (scheduling is a server-side primitive); an anon or authenticated key gets 403. A key for project A can never see or touch project B’s jobs.

Base URL & shapes

Both URL shapes are mounted, mirroring REST/storage/queues:

• Native: POST /v1/cron/{project_id}/jobs
• SDK-alias: POST /v1/{project_id}/cron/v1/jobs

Endpoints

Method & path	Body	Returns
POST .../jobs	{ "name", "schedule", "command" }	{ "name", "scheduled": true } (201)
GET .../jobs	—	[{ name, schedule, command, active }] (this project’s jobs)
DELETE .../jobs/{name}	—	204 (or 404 if no such job)
GET .../jobs/{name}/runs?limit=	—	[{ status, return_message, start_time, end_time }] (newest first)

# Schedule a nightly cleanup that runs INSIDE your project DB
curl -X POST "$ANVILBASE_URL/v1/cron/$PROJECT_ID/jobs" \
  -H "Authorization: Bearer $SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{
        "name": "purge_sessions",
        "schedule": "0 3 * * *",
        "command": "DELETE FROM sessions WHERE expires_at < now()"
      }'
# → {"name":"purge_sessions","scheduled":true}

Scheduling is idempotent on the name: re-posting the same name replaces the schedule and command.

Schedule syntax

schedule is one of:

• A standard 5-field cron expression — minute hour day month weekday (e.g. 0 3 * * * = 03:00 daily, */5 * * * * = every 5 minutes, 15 3 * * 0 = 03:15 Sundays). Times are UTC.
• A pg_cron interval for sub-minute / simple cadences: 30 seconds, 2 minutes, 1 hour, 1 day (singular or plural).

Job names

Names are lowercase [a-z_][a-z0-9_]*, up to 24 characters. They are project-local: you use the bare name (purge_sessions) everywhere; AnvilBase namespaces it internally to proj_<project_id>_purge_sessions so it can’t collide with another project’s job in the shared schedule table. The list and run-history endpoints return the bare name.

Run history

GET .../jobs/{name}/runs returns recent runs newest-first (default 20, max 100), each with pg_cron’s status (succeeded, failed, running, …), the return_message (e.g. INSERT 0 12, or the error text on failure), and start/end timestamps. Check it when a scheduled task seems to have stopped — a failing job records the error here.

SDK example

Every AnvilBase SDK exposes a cron module (client.cron). It needs the service_role key, so use it from trusted backend code only.

import { createClient } from "@anvilbase/client";

// SERVER-SIDE: the service_role key drives cron.
const client = createClient(ANVILBASE_URL, SERVICE_ROLE_KEY, { projectId });

// Schedule a 5-minute rollup that runs in your project DB.
await client.cron.scheduleJob("rollup", "*/5 * * * *", `
  insert into hourly_metrics (hour, count)
  select date_trunc('hour', created_at), count(*)
  from events
  where created_at >= now() - interval '1 hour'
  group by 1
  on conflict (hour) do update set count = excluded.count
`);

// List this project's jobs.
const { data: jobs } = await client.cron.listJobs();

// Inspect run history.
const { data: runs } = await client.cron.jobRuns("rollup", { limit: 10 });
if (runs?.some((r) => r.status === "failed")) {
  // alert / investigate — r.return_message has the error
}

// Remove it.
await client.cron.deleteJob("rollup");

The Python SDK is identical in spirit: client.cron.schedule_job(...), list_jobs(), job_runs(...), delete_job(...).

Cron inspector (console)

The web console ships a read-only Cron inspector (sidebar → Cron): pick a project, browse its scheduled jobs (name, schedule, active/paused badge), and open a job to see its command, schedule, and a run-history table (status, return message, started, ended — newest first). It is the at-a-glance view for “is this job still running, and did its last runs succeed?”

It is read-only by design — scheduling and unscheduling jobs stay on the service_role data-plane / SDK above (a server-side primitive). The console only inspects.

It is backed by two admin / console-session-gated read endpoints on the management API (the same auth as the rest of /api/v1/projects/{id}/…, not a service_role key — the console holds an admin session):

Method & path	Returns
GET /api/v1/projects/{id}/cron/jobs	[{ name, schedule, command, active }] — this project’s jobs
GET /api/v1/projects/{id}/cron/jobs/{name}/runs?limit=	[{ status, return_message, start_time, end_time }] — newest first (default 20, max 100)

These mirror the service_role data-plane’s list/run-history queries exactly (same per-project name prefix and database filter), so they are equally tenant-scoped: an admin inspecting project A’s jobs can never see project B’s, even though all projects share one cron.job table.

Common recipes

-- Hourly: roll up events into an aggregate table
-- schedule: "0 * * * *"
insert into hourly_metrics (hour, count)
select date_trunc('hour', created_at), count(*)
from events where created_at >= now() - interval '1 hour'
group by 1
on conflict (hour) do update set count = excluded.count;

-- Every minute: drain a PGMQ queue (see the Queues guide)
-- schedule: "* * * * *"
select process_email_batch();   -- a function you wrote that reads + handles messages

-- Weekly: ANALYZE a hot table
-- schedule: "0 4 * * 0"
analyze events;

A cron job can call any function in your project DB — combine it with PGMQ to process background jobs on a cadence, pg_net (Extensions) for scheduled outbound HTTP, or ordinary SQL for cleanups and rollups.

Isolation & privileges

• Tenant isolation. A job is namespaced proj_<project_id>_<name> and pinned to platform_<project_id> — both derived from the verified key, never client input. List/delete/runs filter on that prefix and the project’s database, so a project can only ever see, modify, or inspect its own jobs. A project-A key on project-B’s /v1/cron/{B}/... path is rejected (403) before any handler runs.
• Where the command runs. The database argument pins execution to the caller’s own platform_<project_id> DB. A scheduled command can therefore only touch that one project’s data — it cannot reach another database.
• Who it runs as. Jobs run as the anvilbase role (the control plane’s database role), which owns the project databases but is not a superuser in production. The effective power is exactly the project’s own service_role-equivalent SQL power, scoped to its DB. There is no cross-DB reach and no privilege escalation. Because the caller already holds a service_role key (which can run arbitrary SQL in the project DB anyway), scheduling arbitrary SQL grants nothing beyond what that key already had.

Platform jobs

AnvilBase ships a scheduled job of its own: prune_webhook_dlq_nightly (03:15 UTC), which deletes dead-lettered webhook deliveries older than the retention window (default 30 days). With pg_cron now installed in the platform DB by default, this job is active out of the box on a stock AnvilBase deployment — it runs in the platform database against webhook_deliveries. See Webhooks → Dead-letter queue for the on-demand purge (CLI / admin API).

Operators running their own stock Postgres without pg_cron: the prune_webhook_dlq migration still applies (auto-purge is simply disabled), and the manual purge path remains available. The AnvilBase Postgres image bundles and enables pg_cron, so no action is needed there. The shared_preload_libraries / cron.database_name settings are baked into the image; on an existing volume they take effect on the next container restart.

Tips

• Keep job SQL idempotent and bounded (cap rows per run) so a backlog doesn’t produce one giant transaction.
• Check the run history (.../jobs/{name}/runs) when a job seems to have stopped — failures are recorded there with the error message.
• Times are UTC.

Next: Webhooks.