Skip to main content

Scheduled (Cron) jobs

A single Cloudflare Cron trigger drives a sequence of periodic jobs. They are wired through the Worker's scheduled handler in api/src/index.ts:

export default {
  fetch: app.fetch,
  scheduled: (event, env) => handleScheduled(event, env),
  queue: (batch, env) => handleQueueBatch(batch, env),
};

The trigger​

api/wrangler.toml declares one trigger — every 15 minutes:

[triggers]
crons = ["*/15 * * * *"]

The jobs activate once the Worker is deployed with Hyperdrive (they open their own owner DB connection). They are inert locally and in tests. The trigger must also be enabled in the Cloudflare dashboard.

handleScheduled​

api/src/scheduled.ts runs the jobs in sequence. Each is wrapped by recordRun, which times the job, logs but never throws (so one bad run can't crash the schedule or block the others), and writes ONE row to the account-wide ops cron-run log (system.cron_run — owner schema, not tenant-scoped, since cron handlers span every tenant). On success the row records the counts the handler returned; a thrown handler records ok=false plus the error. The log write is itself best-effort:

await recordRun('subject_erasure',   () => runDueSubjectErasures(env), …);
await recordRun('demo_generator',    () => runDemoGenerator(env).then((n) => ({ added: n })), …);
await recordRun('alert_escalation',  () => runAlertEscalation(env), …);
await recordRun('metric_drift',      () => runMetricDrift(env), …);
await recordRun('notifications',     () => runDueNotifications(env), …);
await recordRun('export_schedules',  () => runDueExportSchedules(env), …);
await recordRun('expire_partials',   () => runExpireStalePartials(env), …);
await recordRun('webhook_retry',     () => runPendingWebhooks(env), …);
await recordRun('scheduled_invites', () => runDueScheduledInvites(env), …);
await recordRun('transcription',     () => runPendingTranscriptions(env), …);
await recordRun('file_drops',        () => runDueFileDrops(env), …);
await recordRun('rolling_retention', () => runDueRetention(env), …);
await recordRun('orphan_recordings', () => sweepOrphanRecordings(env), …);

Each job opens an owner (BYPASSRLS) Postgres connection — directly via createPgClient(dbConnectionString(env)), or a full container via buildContainer(env) when it needs the provider ports (e.g. dispatch, transcription) — and joins on explicit client_account_id for tenant scoping, then closes the connection in a finally.

Subject erasure — runDueSubjectErasures​

Runs first as the compliance barrier. It discovers due hash-only erasure jobs, re-enters each owning tenant through RLS and publishes generation-fenced Queue wake-ups. The Queue worker performs bounded Postgres/R2 cleanup; Cron recovers a wake lost after the authoritative job committed.

Demo generator — runDemoGenerator​

Tops up the Black Cat Bank demo workspace with a small random batch of fresh responses each run, so the live demo keeps moving. Scoped by client-account name (DEMO_TENANT, default Black Cat Bank) — identity lives in Clerk now, so there's no neon_auth schema to join; the demo account's name is set by the seed (db/scripts/seed-demo.mjs). A no-op if the workspace doesn't exist or DEMO_TENANT is blank, so real client data is never touched. It mirrors db/scripts/generate-responses.mjs, including computing an overall response sentiment the same way the ingestion rollup does (so the demo shows sentiment without a live queue). Each fresh response also keeps the downstream surfaces ticking the way the live pipeline would: an alert on every detractor, an occasional lead on a promoter, the odd badge award, and an email-send (with an open/click funnel) for email-channel responses — so Alerts / Leads / Badges / Distribution stay live between full re-seeds. The one-off DEEP demo data (org hierarchy, roles, KPIs, dashboards, reports, suppression, file-drop, integrations, ops, …) is seeded separately by db/scripts/seed-demo-extras.mjs (npm run db:seed-demo-full --workspace db).

Alert escalation — runAlertEscalation​

A time-driven SLA scan. Two SQL passes advance open alerts through their tenant's escalation policy:

  • Pass A escalates alerts past the escalation window (stage 0/1 → 2, status → escalated).
  • Pass B sends a first reminder for alerts past the reminder window but not yet reminded/escalated (stage 0 → 1).

It stages recipient deliveries in the transactional notification outbox. Queue wake-ups provide low latency and the later notification sweep recovers missed wakes; the current alert route, target hash and suppression state are rechecked before the email provider call. A no-op for tenants whose policy is inactive.

Metric drift — runMetricDrift​

Evaluates bounded due metric-drift rules, using the same metric semantics and anonymity threshold as the interactive product. A raised alert and its notification intents commit together, so an overlapping Cron tick cannot create duplicate alerts or provider sends.

Notification recovery — runDueNotifications​

Fairly claims a bounded set of due outbox rows across tenants. Each row contains an immutable payload and destination evidence, provider-safe idempotency state, a lease and capped backoff. Definitive failures are terminal; ambiguous provider outcomes remain retryable/recoverable without allowing a second payload.

Scheduled exports — runDueExportSchedules​

Discovers due schedule occurrences and unfinished runs with a global cap plus per-tenant fairness. Under tenant RLS it atomically snapshots the schedule, survey/template scope, rolling date window and verified member audience into an immutable export_run, then publishes a generation-fenced Queue wake. The Queue worker builds bounded CSV parts and a hash/size manifest in R2, delivers an authenticated console link by email or the manifest to an HTTPS webhook, and later verifies object deletion at expiry. Leases, first-write-wins provider payloads, stable idempotency keys and Cron rediscovery make every phase crash-safe. The Cron result reports runs as queued, not delivered; delivery is a later durable phase. SFTP remains an explicit unconfigured seam.

Stale-partial expiry — runExpireStalePartials​

A web/IVR/SMS response that stops building up (no new page or answer for a while) is abandoned. This flips such in_flight transactions to expired, so they show as expired rather than perpetual "partial" and drop out of completed-only aggregations. The window is PARTIAL_EXPIRY_MINUTES (default 120, floored at 5). Staleness is measured from the later of the transaction's created_at and its most recent answer's created_at.

Outbound webhook retry — runPendingWebhooks​

Re-attempts still-pending webhook deliveries across all tenants (limit 200, oldest first) and dead-letters them at the attempt cap (WEBHOOK_MAX_ATTEMPTS = 5). The synchronous attempt at dispatch time covers the happy path; this sweep recovers deliveries whose first attempt failed. Each retry redacts the payload per the owning webhook's content toggles and HMAC-SHA256-signs the exact body via buildSignedDelivery — the same helper the synchronous path uses — and the outbound fetch passes the assertSafeOutboundUrl SSRF guard with redirect: 'manual' and a timeout. A delivered row stamps delivered_at; a failure increments attempts and flips to failed at the cap.

Scheduled invites — runDueScheduledInvites​

Drains due tenant.scheduled_invite rows (limit 100) — invites deferred by the reschedule-instead-of-suppress path (e.g. a frequency-cap collision) until their window opens. It claims a leased batch and replays each through DispatchService.drainScheduledInvite, which sends, drops, retries, or dead-letters per outcome. drainScheduledInvite never throws (it records the retry/dead-letter itself), and the loop guards anyway so one bad tenant can't abort the batch.

Recording transcription — runPendingTranscriptions​

Drives speech-to-text for recorded answers. It atomically claims a leased batch of response.transcription_job rows (limit 25) — pending/failed jobs and lease-expired processing jobs orphaned by a worker eviction — with FOR UPDATE SKIP LOCKED, incrementing attempts at claim so a job that keeps dying still counts toward the cap (TRANSCRIPTION_MAX_ATTEMPTS). For each, it transcribes the R2 audio via the Gemini STT adapter (ports.transcription), writes the transcript into response.answer.value_text with sentiment = null, flips the job to done under the same lease guard, and re-enters the sentiment/auto-code pipeline (analyze → rollupResponse → autoCodeResponse) so a transcript is scored exactly like a typed verbatim. An empty transcript is a retryable failure; backoff is via not_before, dead-lettered at the cap. See Sentiment & coding.

File drops — runDueFileDrops​

Recovers recurring and manual CSV import runs from their Postgres/R2 ledgers, including work whose Queue wake was lost. Each claim advances only a bounded scan/delivery step and retains positive archive/delete evidence before clearing an object reference.

Rolling retention purge — runDueRetention​

Claims a bounded set of tenants with an enabled retention policy and publishes capability-only Queue messages. The consumer verifies related R2 deletion before purging transactions and their children, writes a retained deletion_record, and advances the claim if another batch remains. The ad-hoc /gdpr/retention route uses the same verified service.

Orphan recordings — sweepOrphanRecordings​

Runs after retention so it can remove recordings orphaned in the same cycle. It covers both abandoned brokered uploads and media whose response reference was removed by retention/erasure, deleting and verifying R2 objects before final database cleanup.

Idempotency​

The jobs are safe to re-run on every 15-minute tick:

  • Escalation filters on escalation_stage and status, so an already-escalated alert is skipped on the next pass.
  • Exports materialise one occurrence per schedule due time and snapshot a configuration generation; run phases use leases, generation checks and immutable R2 upload reservations.
  • Partial expiry only matches state = 'in_flight' rows; once flipped to expired they no longer match.
  • Notifications / webhook retry / transcription / scheduled invites lease-claim their rows and advance status (pending → delivered/failed, pending → processing → done/dead, etc.), so a redelivered or concurrently-running tick never double-processes a row.
  • Erasure / file drops / retention / orphan cleanup keep durable cursors and positive deletion evidence, so overlapping recovery attempts resume or become stale no-ops.
  • Demo generator is additive by design (it tops up), so re-runs simply add more demo data.

Production note​

For a real production environment, either remove the [triggers] block (no demo generation) or leave DEMO_TENANT unset so the demo job no-ops; all other handlers are real product, recovery or compliance work and should stay. See Configuration.