# Scheduler Engine Playbook **Portable spec for shipping user-scheduled future actions — cold-email drips, scheduled social posts, scheduled SMS, reminder/notification dispatch — on top of Supabase + Trigger.dev.** Built from the BKE publish-scheduler + email-drip implementations. Reference repo: `reference app`. **Distinct from the worker-task pattern.** That playbook is about "user clicks → worker runs now." This playbook is about "user (or system) schedules a row → it fires at a specific wall-clock time, possibly weeks later, surviving tab close / deploys / restarts." --- ## 0. Use this spec **Inputs:** - `SUPABASE_URL` + `SUPABASE_SERVICE_ROLE_KEY` + Trigger.dev project - Action types you're scheduling (`publish_post`, `send_email`, `send_sms`, `reminder`, etc.) - Per-action provider integration (Postmark / Twilio / Blotato / GHL / etc.) - Per-workspace timezone source (settings.account_timezone, profile.tz, etc.) **Outputs:** - `scheduled_actions` table (or per-domain `scheduled_s`) + state machine - Scheduler-tick cron (1/min) that fans dispatch - Server-side reconciler cron (1/min) that keeps the schedule queue in sync with source-of-truth state - DB-level publish guard trigger - Cancel / reschedule / retry UI hooks - Optional recurring expander for "every Tuesday 9am" patterns --- ## 1. Architecture ``` User UI / autopilot Trigger.dev workers │ │ ▼ ▼ ┌─────────────────┐ every 1m ┌──────────────────┐ │ scheduled_ │ ◄──────────────│ reconciler │ keeps queue in sync with │ actions │ │ (server cron) │ source-of-truth state │ status='queued'│ └──────────────────┘ └────────┬────────┘ │ every 1m ▼ ┌─────────────────┐ ┌──────────────────┐ │ scheduler-tick │ ───fan-out────▶│ dispatch-│ one worker run per row │ (server cron) │ │ provider call │ updates status to └─────────────────┘ │ retry / refund │ submitted → published/failed └──────────────────┘ ``` **Two crons, not one.** Scheduler-tick *dispatches* due rows. Reconciler *materializes / cleans up* rows from upstream state (approved gc rows, drip-enrollment progress, recurring patterns). Keeping these separate prevents one bug from corrupting both responsibilities. --- ## 2. Schema (generic `scheduled_actions`) ```sql create table scheduled_actions ( id uuid primary key default gen_random_uuid(), user_id uuid not null references profiles(id) on delete cascade, workspace_id uuid references workspaces(id) on delete cascade, action_type text not null, -- 'publish_post' | 'send_email' | 'send_sms' | ... target_key text, -- per-action sub-target (platform, channel, phone, etc.) payload jsonb not null, -- action-specific data (gc_id, template_slug, body, ...) publish_at timestamptz not null, -- always UTC status text not null default 'queued' check (status in ('queued','submitted','published','failed','cancelled')), last_error text, attempt_count int not null default 0, next_attempt_at timestamptz, provider_id text, -- external system's id once dispatched submitted_at timestamptz, finished_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); -- Date-range queries (calendar UI) — most important index. create index ix_scheduled_actions_ws_publish_at on scheduled_actions (workspace_id, publish_at); -- Hot-path scheduler-tick query. create index ix_scheduled_actions_queued_due on scheduled_actions (publish_at) where status = 'queued'; -- Prevent duplicate active rows for the same (thing, target). -- BKE uses (generated_content_id, platform); generalize for your domain. create unique index ux_scheduled_actions_active_thing_target on scheduled_actions (action_type, (payload->>'thing_id'), target_key) where status in ('queued','submitted'); ``` Reference: `supabase/migrations/00040_scheduled_posts_dedup.sql`. --- ## 3. Status state machine ``` queued ──▶ submitted ──▶ published (terminal) │ │ │ └─────────▶ failed (terminal, retryable by admin) │ └─────────────────────▶ cancelled (terminal, user-initiated) ``` **Transition rules:** - `queued → submitted` — worker atomically claims the row (see §6). Only the worker writes this. - `submitted → published` — provider returned success. Set `provider_id`, `finished_at`. - `submitted → failed` — provider returned non-retryable error OR retry budget exhausted. Set `last_error`. - `queued → cancelled` — user UI only. Reschedule is `cancel + insert new row`, never UPDATE publish_at. - `submitted → queued` — ONLY if the worker discovers it never reached the provider (network error before fetch, validation error). The worker MUST revert the claim before throwing. (Lab note 2026-05-07.) --- ## 4. Scheduler-tick cron (the fan-out) Reference: `src/trigger/scheduler-tick.ts`. ```ts export const schedulerTick = schedules.task({ id: 'scheduler-tick', cron: '*/1 * * * *', // every minute, NOT faster maxDuration: 120, run: async () => { const supabase = adminClient(); const { data: due } = await supabase .from('scheduled_actions') .select('id, action_type') .eq('status', 'queued') .lte('publish_at', new Date().toISOString()) .order('publish_at', { ascending: true }) .order('id', { ascending: true }) // determinism — multiple rows can share publish_at .limit(200); await Promise.all((due ?? []).map((row) => tasks.trigger(`dispatch-${row.action_type}`, { rowId: row.id }) )); return { dueCount: due?.length ?? 0 }; }, }); ``` **Why fan-out, don't dispatch inline:** a stuck Postmark/Blotato/Twilio call on one row can't block the whole tick. Each dispatched action gets its own Trigger.dev run, its own retries, its own dashboard line. --- ## 5. Reconciler cron (queue ↔ source-of-truth sync) Reference: `src/trigger/scheduled-posts-reconciler.ts`. Reconciliation runs server-side every minute so it doesn't depend on a client tab being open. It (a) inserts missing rows for newly-approved items, (b) updates publish_at when the upstream wall time changes, (c) deletes orphan rows when the upstream item moves out of "scheduled" state. **Critical pattern — the terminal-history gate:** ```ts // Fetch ALL scheduled_actions rows (any status), not just active ones. const { data: allRows } = await supabase .from('scheduled_actions') .select('id, thing_id, target_key, status, publish_at, created_at') .eq('workspace_id', ws.id); const active = allRows.filter(r => r.status === 'queued' || r.status === 'submitted'); const terminalByKey = new Set(); for (const r of allRows) { if (['published','failed','cancelled'].includes(r.status)) { terminalByKey.add(`${r.thing_id}::${r.target_key}`); } } // ...later, when deciding whether to INSERT: if (terminalByKey.has(key)) { skipTerminal++; // NEVER re-insert — sibling already shipped/cancelled } else if (existingActive) { // update publish_at if it drifted } else { inserts.push(newRow); } ``` Without this gate, the reconciler will re-insert a row for a target that already published — and the scheduler-tick will republish it minutes later. **This is the BKE 2026-05-01 carousel triple-publish bug.** Every reconciler must check terminal history before inserting. **Two more reconciler safeties (BKE lab note 2026-05-08):** 1. **Age gate.** Never delete a row younger than 2 min, regardless of source-of-truth state. A worker dispatch + provider call routinely takes 30–90s; an in-flight worker must not have its row yanked out. 2. **Terminal-parent gate.** Before deleting an "orphan" active row, fetch the parent thing's status. If the parent is in a terminal state but has an active row (user clicked Retry or Post Now), it was minted by an explicit user action — don't delete. --- ## 6. Worker dispatch — atomic claim Reference: `src/trigger/publish-post.ts`. ```ts export const dispatchPublishPost = task({ id: 'dispatch-publish-post', run: async ({ rowId }) => { const supabase = adminClient(); // Atomic claim — only proceed if we successfully flipped queued → submitted. const { data: claimed, error } = await supabase .from('scheduled_actions') .update({ status: 'submitted', submitted_at: new Date().toISOString() }) .eq('id', rowId) .eq('status', 'queued') .select('*') .maybeSingle(); if (error) throw error; if (!claimed) return { skipped: 'already-claimed' }; // another tick won the race try { const result = await callProvider(claimed); await supabase.from('scheduled_actions') .update({ status: 'published', provider_id: result.id, finished_at: new Date().toISOString() }) .eq('id', rowId); } catch (err) { const isPreProviderFailure = err instanceof StagingError || err instanceof ValidationError; if (isPreProviderFailure) { // Revert the claim — we never shipped. Trigger.dev's auto-retry can re-claim. await supabase.from('scheduled_actions') .update({ status: 'queued', last_error: err.message }) .eq('id', rowId).eq('status', 'submitted'); throw err; } await supabase.from('scheduled_actions') .update({ status: 'failed', last_error: err.message, attempt_count: claimed.attempt_count + 1 }) .eq('id', rowId); throw err; } }, }); ``` The `.eq('status', 'queued')` on the UPDATE is the atomicity guarantee — concurrent scheduler-tick fans can both fire `tasks.trigger`, but only one will succeed the claim. The loser sees `claimed === null` and returns early. --- ## 7. DB-level publish guard (belt-and-braces) Reference: `supabase/migrations/00062_scheduled_posts_publish_guard.sql`. A BEFORE INSERT/UPDATE trigger raises SQLSTATE `23505` if any code path tries to create an active row for a `(thing, target)` pair that already has a published sibling. This catches races that get past the application-level terminal-history gate. ```sql create function enforce_no_duplicate_publish_active() returns trigger as $$ begin if new.status in ('queued','submitted') then if exists ( select 1 from scheduled_actions where action_type = new.action_type and payload->>'thing_id' = new.payload->>'thing_id' and target_key = new.target_key and status = 'published' and id <> new.id ) then raise exception 'duplicate-publish guard: published sibling exists' using errcode = '23505'; end if; end if; return new; end; $$ language plpgsql; ``` **Why a trigger and NOT a partial unique index:** prod DBs often already contain legitimate duplicate-published history rows (provider redelivery, manual reposts). A unique index would fail to create on existing data; a trigger only inspects NEW rows and leaves history intact. --- ## 8. Cancel / reschedule / retry **Cancel** (user UI): `UPDATE scheduled_actions SET status='cancelled' WHERE id=$1 AND status='queued'`. Idempotent; no-op if already past queued. **Reschedule** = **cancel + insert new row**. Never UPDATE `publish_at` on an existing row. - Preserves "what was scheduled vs what shipped" audit history. - Avoids the publish-guard race where an UPDATE inside the dispatch window could leak a republish. - The new row's `payload` can reference the cancelled row's id for lineage. **Retry** (admin only, on `status='failed'`): - `UPDATE scheduled_actions SET status='queued', last_error=null, attempt_count=attempt_count+1 WHERE id=$1 AND status='failed' AND attempt_count < 5` - Bounded — typically 5 max attempts before manual review. - **Do NOT INSERT a new row for retry** — the existing row's `attempt_count` matters for analytics + admin investigation. --- ## 9. Timezones **Store `publish_at` as UTC, always.** `timestamptz` in Postgres + `.toISOString()` in JS. **User's timezone is per-workspace settings** (`workspaces.settings.account_timezone`, IANA name like `America/New_York`). Compute display offset in the client; never store local time. **Wall-time + timezone → UTC conversion** for "publish Tuesday at 9am in user's timezone": ```ts function resolveScheduledInstant(dateStr: string, timeStr: string, tz: string): Date { const [y, m, d] = dateStr.split('-').map(Number); const [hh, mm] = timeStr.split(':').map(Number); const guess = Date.UTC(y, m - 1, d, hh, mm); // Round-trip through Intl.DateTimeFormat to compute the zone's UTC offset. const parts = new Intl.DateTimeFormat('en-US', { timeZone: tz, hour12: false, year: 'numeric', month: '2-digit', day: '2-digit', hour: '2-digit', minute: '2-digit', second: '2-digit' }).formatToParts(new Date(guess)); const get = (t: string) => parseInt(parts.find(p => p.type === t)!.value, 10); const zoned = Date.UTC(get('year'), get('month') - 1, get('day'), get('hour') % 24, get('minute'), get('second')); return new Date(guess - (zoned - guess)); } ``` Full implementation in `src/trigger/scheduled-posts-reconciler.ts` lines 88–103. **Validate the timezone** with `new Intl.DateTimeFormat('en-US', { timeZone: tz })` in a try/catch before using it — `account_timezone` is a user-controlled JSONB field and a garbage value will crash every per-row resolution. --- ## 10. Recurring schedules (bulk) For "every Tuesday at 9am for the next 12 weeks" patterns, add a `recurring_schedules` table: ```sql create table recurring_schedules ( id uuid primary key default gen_random_uuid(), user_id uuid not null references profiles(id), workspace_id uuid references workspaces(id), action_type text not null, payload_template jsonb not null, -- per-instance overrides applied at expansion rrule text not null, -- RFC 5545 RRULE string starts_at timestamptz not null, ends_at timestamptz, status text default 'active' check (status in ('active','paused','ended')), last_materialized_at timestamptz, created_at timestamptz default now() ); ``` **Daily expander cron** runs once per day, reads active `recurring_schedules` rows, and materializes the next N occurrences (typically 14 days ahead) into `scheduled_actions`. Idempotent — uses a unique index on `(recurring_schedule_id, publish_at)` so re-running doesn't duplicate. Don't pre-materialize the entire RRULE — the user might pause / edit the recurring rule and you'd have to chase down already-queued rows. Roll a sliding window. --- ## 11. Drip-sequence variant For email/SMS drips, the queue shape is different: the row represents *enrollment in a campaign*, not a single dispatch. Reference: `src/trigger/email-drip-tick.ts`. ```sql create table email_enrollments ( id uuid primary key default gen_random_uuid(), user_id uuid not null references profiles(id), campaign_id uuid not null references email_campaigns(id), current_step int not null default 0, next_send_at timestamptz not null, status text default 'active' check (status in ('active','completed','exited')), enrolled_at timestamptz default now(), completed_at timestamptz, exit_reason text ); -- Dedupe key on per-send rows prevents double-send on retried ticks. create table email_sends ( id uuid primary key default gen_random_uuid(), enrollment_id uuid not null references email_enrollments(id), step_index int not null, dedupe_key text not null unique, -- enrollment_id + ':' + step_index postmark_message_id text, sent_at timestamptz default now(), error_code int, error_message text ); ``` **Drip-tick pattern:** cron reads enrollments where `status='active' AND next_send_at <= now()`. For each: render the current step's template, INSERT into `email_sends` (UNIQUE constraint catches double-fires), call provider, advance `current_step` + `next_send_at`. **Optimistic-lock advance:** `UPDATE email_enrollments SET current_step=$new WHERE id=$id AND current_step=$old`. The `.eq('current_step', old)` guard makes the advance a no-op if a parallel tick already moved past — first writer wins. --- ## 12. Common pitfalls ❌ **Don't trust `publish_at` for ordering.** Multiple rows can share a timestamp. Always `ORDER BY publish_at, id` for determinism. ❌ **Don't fire the worker without atomic claim.** Race between two scheduler-tick fans means both will dispatch the worker — only the row-claim UPDATE with `.eq('status','queued')` prevents double-publish. ❌ **Don't INSERT a new row on retry.** UPDATE the existing row back to `status='queued'` and bump `attempt_count`. History matters for analytics, refund audits, and admin investigation. ❌ **Don't run scheduler-tick faster than 1/min.** Postgres connection pressure + Trigger.dev plan limits + the human-readable cadence of a "scheduler" all converge on 1/min. Faster gets you marginal latency and a lot of operational pain. ❌ **Don't skip the terminal-history gate in reconciler logic.** Without it, a (thing, target) that already published gets re-inserted while the parent state machine is mid-flip. BKE shipped this bug on 2026-05-01 (carousel triple-publish). ❌ **Don't store timezone-aware timestamps in user-local time.** UTC + display offset. Storing "2026-05-20T09:00" with no zone information makes DST transitions impossible to handle correctly. ❌ **Don't allow editing `publish_at` on an existing row.** Reschedule must be `cancel + insert`. Editing in place destroys the "what was scheduled vs what shipped" audit trail and opens the door to the publish-guard race. ❌ **Don't bulk-replace the queue on sync.** Reconciler is upsert-only. Per-row inserts so a 23505 dedupe-index hit only drops the colliding row, not the whole batch. ❌ **Don't trust client-side reconciliation.** A tab can close mid-flush. The server-side reconciler cron is the source of truth; client-side sync is a UX accelerator only. ❌ **Don't let `next_attempt_at` / `next_send_at` drift to floats.** PostgREST int columns reject JS floats — `Math.floor()` before write. (BKE memory rule.) ❌ **Don't fetch `scheduled_at` columns without timezone awareness in queries.** `lte('publish_at', now)` requires both sides UTC — if the client passes a non-UTC string the query silently misses due rows. --- ## 13. Kickoff prompt > Execute the Scheduler Engine Playbook at `/templates/b-tier/SCHEDULER-ENGINE-PLAYBOOK.md` for this app. Mirror the BKE scheduler implementation. Reference: `/src/trigger/` (scheduler-tick.ts, scheduled-posts-reconciler.ts, email-drip-tick.ts, publish-post.ts). > > Inputs: > - Action types to support: [list — e.g. publish_post, send_email, send_sms] > - Provider integrations per action type > - Timezone source: workspaces.settings.account_timezone (or override) > - Recurring schedules needed: yes / no > - Retry budget: default 5 attempts > > Pause after the schema migration + scheduler-tick cron land for review before wiring providers. --- **Reference files in BKE:** - `src/trigger/scheduler-tick.ts` — fan-out cron - `src/trigger/scheduled-posts-reconciler.ts` — server-side reconciler with terminal-history gate - `src/trigger/email-drip-tick.ts` — drip-enrollment variant - `src/trigger/publish-post.ts` — atomic-claim dispatch worker - `src/lib/publish/sync-scheduled-posts.ts` — client-side reconciler (UX accelerator) - `supabase/migrations/00040_scheduled_posts_dedup.sql` — partial unique index on active rows - `supabase/migrations/00062_scheduled_posts_publish_guard.sql` — duplicate-publish trigger