mirror of
https://github.com/block/buzz.git
synced 2026-10-28 13:01:10 +00:00
31 lines
1.9 KiB
SQL
31 lines
1.9 KiB
SQL
-- ── Scheduled workflow fires (cron claim) ─────────────────────────────────────
|
|
-- Plan §5: the at-most-once cron fire claim. UNIQUE (community_id, workflow_id,
|
|
-- scheduled_for) — only the pod that wins the claim insert creates the run.
|
|
-- Restart-safe (DB-durable). community is server provenance: the scheduler passes
|
|
-- workflow.community_id from list_all_enabled_workflows(), never a client input.
|
|
-- workflow_id is NOT globally unique under the (community_id, id) workflow key, so
|
|
-- the claim binds both community and id explicitly rather than resolving from id.
|
|
-- workflow_run_id links the won claim to the run it created (audit; NULL until the
|
|
-- post-insert attach, and stays NULL if run creation failed after a won claim).
|
|
-- The FK to workflow_runs uses NO ACTION (not SET NULL): community_id is shared
|
|
-- with the claim PK and is NOT NULL, so SET NULL is unimplementable here; a future
|
|
-- delete of a still-linked run is blocked rather than orphaning the at-most-once
|
|
-- claim row. workflow_runs are not pruned today, so this is a guardrail, not a path.
|
|
|
|
CREATE TABLE scheduled_workflow_fires (
|
|
community_id UUID NOT NULL REFERENCES communities(id),
|
|
workflow_id UUID NOT NULL,
|
|
scheduled_for TIMESTAMPTZ NOT NULL,
|
|
claimed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
workflow_run_id UUID,
|
|
PRIMARY KEY (community_id, workflow_id, scheduled_for),
|
|
FOREIGN KEY (community_id, workflow_id)
|
|
REFERENCES workflows (community_id, id) ON DELETE CASCADE,
|
|
FOREIGN KEY (community_id, workflow_run_id)
|
|
REFERENCES workflow_runs (community_id, id) ON DELETE NO ACTION
|
|
);
|
|
|
|
-- The interval anchor reads MAX(scheduled_for) per workflow; the janitor prunes
|
|
-- by claimed_at globally (operator concern). See plan §5 retention coupling.
|
|
CREATE INDEX idx_scheduled_fires_claimed_at ON scheduled_workflow_fires (claimed_at);
|