Blame · Line-by-line history

`0037_workflow_engine_v2.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0037_workflow_engine_v2.sqlBlame92 lines · 1 contributor

Normal view Raw

abfa9adClaude	1	-- Workflow engine v2 — Sprint 1 storage additions.
	2	--
	3	-- Strictly additive to Block C1 (drizzle/0008_workflows.sql is LOCKED).
	4	-- The four tables below back new capabilities:
	5	--
	6	-- workflow_secrets encrypted per-repo secrets (AES-256-GCM, base64
	7	-- payload = iv \|\| authTag \|\| ciphertext). The
	8	-- crypto lib lives in src/lib/workflow-crypto.ts;
	9	-- the DB only stores opaque bytes.
	10	-- workflow_dispatch_inputs parameter schema for the `workflow_dispatch`
	11	-- trigger — one row per input on a workflow.
	12	-- workflow_run_cache content-addressable cache, keyed by user-chosen
	13	-- cache_key within a scope (repo / branch / tag).
	14	-- Backs the `gluecron/cache@v1` action.
	15	-- workflow_runner_pool warm-runner worker registry used by the job
	16	-- scheduler to avoid cold-start per run.
	17	--
	18	-- `CREATE TABLE IF NOT EXISTS` + `CREATE INDEX IF NOT EXISTS` throughout so
	19	-- reruns are idempotent. Size/format validation (secret name regex, 100MB
	20	-- cache cap) is enforced at the write-site, not in the DB.
	21
	22	--> statement-breakpoint
	23	CREATE TABLE IF NOT EXISTS "workflow_secrets" (
	24	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	25	"repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE,
	26	"name" text NOT NULL,
	27	"encrypted_value" text NOT NULL,
	28	"created_by" uuid REFERENCES "users"("id") ON DELETE SET NULL,
	29	"created_at" timestamptz NOT NULL DEFAULT now(),
	30	"updated_at" timestamptz NOT NULL DEFAULT now()
	31	);
	32
	33	--> statement-breakpoint
	34	CREATE UNIQUE INDEX IF NOT EXISTS "workflow_secrets_repo_name_uq"
	35	ON "workflow_secrets" ("repository_id", "name");
	36
	37	--> statement-breakpoint
	38	CREATE INDEX IF NOT EXISTS "workflow_secrets_repo_idx"
	39	ON "workflow_secrets" ("repository_id");
	40
	41	--> statement-breakpoint
	42	CREATE TABLE IF NOT EXISTS "workflow_dispatch_inputs" (
	43	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	44	"workflow_id" uuid NOT NULL REFERENCES "workflows"("id") ON DELETE CASCADE,
	45	"name" text NOT NULL,
	46	"type" text NOT NULL CHECK (type IN ('string', 'boolean', 'choice', 'number')),
	47	"required" boolean NOT NULL DEFAULT false,
	48	"default_value" text,
	49	"options" jsonb,
	50	"description" text
	51	);
	52
	53	--> statement-breakpoint
	54	CREATE UNIQUE INDEX IF NOT EXISTS "workflow_dispatch_inputs_wf_name_uq"
	55	ON "workflow_dispatch_inputs" ("workflow_id", "name");
	56
	57	--> statement-breakpoint
	58	CREATE TABLE IF NOT EXISTS "workflow_run_cache" (
	59	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	60	"repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE,
	61	"cache_key" text NOT NULL,
	62	"scope" text NOT NULL DEFAULT 'repo',
	63	"scope_ref" text,
	64	"content_hash" text NOT NULL,
	65	"content" bytea NOT NULL,
	66	"size_bytes" bigint NOT NULL,
	67	"created_at" timestamptz NOT NULL DEFAULT now(),
	68	"last_accessed_at" timestamptz NOT NULL DEFAULT now()
	69	);
	70
	71	--> statement-breakpoint
	72	CREATE UNIQUE INDEX IF NOT EXISTS "workflow_run_cache_repo_key_scope_uq"
	73	ON "workflow_run_cache" ("repository_id", "cache_key", "scope", "scope_ref");
	74
	75	--> statement-breakpoint
	76	CREATE INDEX IF NOT EXISTS "workflow_run_cache_repo_lru_idx"
	77	ON "workflow_run_cache" ("repository_id", "last_accessed_at");
	78
	79	--> statement-breakpoint
	80	CREATE TABLE IF NOT EXISTS "workflow_runner_pool" (
	81	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	82	"worker_id" text NOT NULL UNIQUE,
	83	"status" text NOT NULL CHECK (status IN ('idle', 'busy', 'draining', 'dead')),
	84	"current_run_id" uuid REFERENCES "workflow_runs"("id") ON DELETE SET NULL,
	85	"warmed_at" timestamptz NOT NULL DEFAULT now(),
	86	"last_heartbeat_at" timestamptz NOT NULL DEFAULT now(),
	87	"capacity" integer NOT NULL DEFAULT 1
	88	);
	89
	90	--> statement-breakpoint
	91	CREATE INDEX IF NOT EXISTS "workflow_runner_pool_status_idx"
	92	ON "workflow_runner_pool" ("status");