Backlog: improve worker queue polling before action history grows

[ChristianLempa]

Problem

The embedded worker currently uses the actions table as both the queue projection and long-term action history. Queue polling is still simple and acceptable for the current MVP, but as actions accumulate and we add richer action events/history, worker polling can become harder to optimize and reason about.

Current worker queue behavior:

• polls actions every WORKER_POLL_INTERVAL
• selects status = pending
• only picks actions where run_after IS NULL OR run_after <= now
• orders by created_at ASC
• fetches max_concurrent * 2
• execution ownership is established later by lifecycle claim: pending -> running

Goal

Keep the current queue behavior for now, but design an improved worker polling/queue projection before actions become a broad historical/event table.

Suggested direction

• Keep actions as the authoritative action lifecycle row.
• Add richer action history/events separately, e.g. action_events, without making the worker poll the event table.
• Add queue-specific indexes or a dedicated queue projection so polling remains fast as historical data grows.
• Preserve the durable claim boundary: only pending -> running owns execution.
• Consider whether future queue needs include priority, per-kind concurrency, backoff, dedupe keys, and queue partitions.

Candidate indexes if we keep polling actions

CREATE INDEX idx_actions_worker_pending_created
ON actions(created_at)
WHERE status = 'pending' AND run_after IS NULL;

CREATE INDEX idx_actions_worker_pending_due
ON actions(run_after, created_at)
WHERE status = 'pending' AND run_after IS NOT NULL;

CREATE INDEX idx_actions_worker_running_heartbeat
ON actions(last_heartbeat_at, started_at)
WHERE status = 'running' AND completed_at IS NULL;

Acceptance criteria

• Document the intended worker queue model separately from action/event history.
• Decide whether to keep polling actions with partial indexes or introduce a worker_jobs/queue projection table.
• Ensure action events/history do not fragment or slow the worker queue polling path.
• Preserve existing lifecycle semantics, especially pending -> running as the ownership boundary.