Starflow queue substrate

ADR: 047 §2 Status (2026-06-01): Postgres SKIP LOCKED shipping. SpacetimeDB substrate scaffolded (env-var knob; adapter TBD).

This doc is the runbook for the queue-substrate boundary — what’s abstracted, what’s pluggable, and how Phase 7’s SpacetimeDB cutover will work without touching the engine.

What WorkflowStateAdapter already does

The substrate boundary is the WorkflowStateAdapter port in @celestial/starflow-engine. Two methods carry the queue:

interface WorkflowStateAdapter {
  // Edge inserts. Single row, no race.
  enqueueRun?(spec: { runId, templateId, workspaceId, env, runnerPool?, ... }): Promise<void>;

  // Runner claims. Implementation guarantees atomicity — only one runner
  // sees any given row as "newly claimed."
  claimNextQueuedRun?(args: { runnerId, leaseUntil, runnerPool? }): Promise<ClaimedRun | null>;
}

The engine never touches the substrate directly. It calls these two methods. Anything that implements WorkflowStateAdapter can be a substrate.

Implementations today

Adapter	Substrate	Atomicity primitive	Used when
SupabaseWorkflowStateAdapter	Postgres (via PostgREST)	Conditional UPDATE … WHERE status='queued'	SUPABASE_URL is set
LocalWorkflowStateAdapter	SQLite	Conditional UPDATE … WHERE status='queued' (same shape)	Local dev, single-host self-host
NullWorkflowStateAdapter	None	n/a (no queue methods)	Tests, embedded harnesses

Conditional UPDATE is the universal trick: select the oldest pending row, then UPDATE it filtered on status='queued'. The DB enforces “at most one winner per row.” A racing claimer sees zero rows updated and re-polls. Works in Postgres, SQLite, anything with row-level locking. No SELECT FOR UPDATE needed — the conditional UPDATE is the lock.

The Phase 7 SpacetimeDB substrate

ADR-047 §2 Phase 2 calls for a SpacetimeWorkflowStateAdapter that:

• Replaces the polling claim loop with a subscription. The runner subscribes to queued_runs WHERE runner_pool = mine; ST pushes new rows as they arrive. CPU drops to near-zero between events.
• Uses a reducer for the atomic claim: claim_run(run_id, runner_id, lease_until) runs inside ST’s transaction model and either succeeds or fails — no race window.
• Same interface (enqueueRun + claimNextQueuedRun). The runner module’s claim loop becomes “wait for subscription event, then call claim reducer.”

What we already did to make this trivial: the engine, runner, and edge all talk to WorkflowStateAdapter. They don’t import SupabaseWorkflowStateAdapter directly anywhere outside the boot-time selector in server.ts. Swap the adapter at the selector and the rest is unchanged.

The selector

STARFLOW_QUEUE_SUBSTRATE env var, validated in config.ts:

Value	Behavior
auto	Default. Supabase when SUPABASE_URL is set; else SQLite.
supabase	Force Supabase. Errors at boot if SUPABASE_URL missing.
sqlite	Force SQLite even when Supabase env is present.
spacetime	Reserved. Errors at boot today (#179 prerequisite).

Pre-wiring the enum + validation means the eventual cutover is a config-file flip, not a code change. The error message on spacetime today is explicit so an operator who sets it sees exactly why it doesn’t work yet.

Migration plan (Phase 7 proper)

When #179 lands and SpacetimeDB is producting-ready:

1. Build SpacetimeWorkflowStateAdapter in @celestial/starflow-engine. Implement the same WorkflowStateAdapter interface. Subscription-based claim path replaces polling.
2. Add to the substrate selector in server._initStorage(). The "spacetime" case constructs the new adapter; remove the boot-time error from config.ts.
3. Dual-write for cutover. For a brief window, run both substrates side by side — Postgres as system-of-record, ST as the active queue. The engine writes terminal state to both. This gives us a rollback path during the validation window.
4. Flip prod. Set STARFLOW_QUEUE_SUBSTRATE=spacetime on the runner unit. Edge keeps writing to Postgres for the dashboard’s read-side (until that also migrates).
5. Decommission Postgres queue. After a few weeks of clean operation, drop the dual-write and rely on ST + the dashboard projection.

The interface is what makes step 1 a self-contained 1–2 week task and steps 2–5 mostly configuration. No engine, runner, or edge changes beyond the selector.

What’s not a substrate concern

• The workflow_runs table itself — that’s the system of record (cold tier). Substrate is purely about queue dispatch (claim_state lifecycle).
• worker_jobs — same story. The worker SDK protocol (HTTP long-polling) is independent of how runs are dispatched. ST won’t touch worker_jobs.
• workflow_definitions, phase events, telemetry — none of this is substrate-pluggable. The queue is the only thing that switches.

Keeping the substrate scope tight to “claim” is what makes ADR-047 Phase 7 a small change. Anything broader becomes a multi-quarter rewrite.