0018_project_schedules.sql39 lines · main

1	-- 0018_project_schedules — cron-triggered function invocations.
2	-- The dispatcher worker (apps/api/src/workers/schedule-dispatcher.ts)
3	-- runs every 60s, selects rows where enabled and next_run_at <= now()
4	-- using the partial index below, and bumps next_run_at forward in the
5	-- same UPDATE that records the run outcome — optimistic claim with no
6	-- explicit lock needed.
7
8	CREATE TABLE IF NOT EXISTS "project_schedules" (
9	"id" text PRIMARY KEY NOT NULL,
10	"project_id" text NOT NULL,
11	"name" text NOT NULL,
12	"function_name" text NOT NULL,
13	"cron_expression" text NOT NULL,
14	"args" jsonb DEFAULT '{}'::jsonb NOT NULL,
15	"enabled" boolean DEFAULT true NOT NULL,
16	"next_run_at" timestamp with time zone NOT NULL,
17	"last_run_at" timestamp with time zone,
18	"last_run_status" text,
19	"last_run_error" text,
20	"created_by" text,
21	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
22	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
23	"deleted_at" timestamp with time zone,
24	CONSTRAINT "project_schedules_project_id_fk"
25	FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE,
26	CONSTRAINT "project_schedules_created_by_fk"
27	FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE SET NULL
28	);
29
30	-- Unique per project among non-deleted rows. Soft-deleted schedules
31	-- don't block a customer from reusing a name.
32	CREATE UNIQUE INDEX IF NOT EXISTS "project_schedules_project_name_idx"
33	ON "project_schedules" USING btree ("project_id", "name")
34	WHERE "deleted_at" IS NULL;
35
36	-- Dispatcher hot path: enabled + due rows only.
37	CREATE INDEX IF NOT EXISTS "project_schedules_due_idx"
38	ON "project_schedules" USING btree ("next_run_at")
39	WHERE "enabled" = true AND "deleted_at" IS NULL;