81 lines
3.3 KiB
PL/PgSQL
81 lines
3.3 KiB
PL/PgSQL
BEGIN;
|
|
CREATE TABLE canvas_ai_runs (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
board_id uuid NOT NULL,
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
account_id uuid NOT NULL,
|
|
idempotency_key uuid NOT NULL,
|
|
payload_hash text NOT NULL,
|
|
status text NOT NULL CHECK(status IN('queued','running','succeeded','partially_succeeded','failed','cancelled','conflict')),
|
|
plan jsonb NOT NULL,
|
|
create_response jsonb NOT NULL,
|
|
error_code text,
|
|
cancel_requested boolean NOT NULL DEFAULT false,
|
|
lease_id uuid,
|
|
lease_until timestamptz,
|
|
last_seq bigint NOT NULL DEFAULT 0 CHECK(last_seq>=0),
|
|
available_at timestamptz NOT NULL DEFAULT now(),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
completed_at timestamptz,
|
|
expires_at timestamptz NOT NULL DEFAULT now()+interval '30 days',
|
|
PRIMARY KEY(project,workspace_id,id),
|
|
UNIQUE(project,workspace_id,account_id,board_id,idempotency_key),
|
|
FOREIGN KEY(project,workspace_id,board_id) REFERENCES materials ON DELETE RESTRICT,
|
|
FOREIGN KEY(project,account_id) REFERENCES project_accounts
|
|
);
|
|
CREATE INDEX canvas_ai_pending ON canvas_ai_runs(available_at,created_at,id) WHERE status='queued';
|
|
CREATE INDEX canvas_ai_active ON canvas_ai_runs(project,workspace_id,board_id,created_at,id) WHERE status IN('queued','running');
|
|
CREATE TABLE canvas_ai_steps (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
run_id uuid NOT NULL,
|
|
id text NOT NULL,
|
|
board_id uuid NOT NULL,
|
|
node_id text NOT NULL,
|
|
ordinal integer NOT NULL CHECK(ordinal>=0),
|
|
status text NOT NULL CHECK(status IN('queued','running','succeeded','failed','cancelled','conflict','skipped')),
|
|
model_id text NOT NULL,
|
|
frozen_input jsonb NOT NULL,
|
|
target_fingerprint text NOT NULL,
|
|
output jsonb,
|
|
error_code text,
|
|
applied_version bigint,
|
|
started_at timestamptz,
|
|
completed_at timestamptz,
|
|
PRIMARY KEY(project,workspace_id,run_id,id),
|
|
UNIQUE(project,workspace_id,run_id,board_id,node_id),
|
|
FOREIGN KEY(project,workspace_id,run_id) REFERENCES canvas_ai_runs ON DELETE CASCADE,
|
|
FOREIGN KEY(project,workspace_id,board_id) REFERENCES materials ON DELETE RESTRICT
|
|
);
|
|
CREATE UNIQUE INDEX canvas_ai_node_busy ON canvas_ai_steps(project,workspace_id,board_id,node_id) WHERE status IN('queued','running');
|
|
CREATE TABLE canvas_ai_events (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
run_id uuid NOT NULL,
|
|
seq bigint NOT NULL CHECK(seq>0),
|
|
type text NOT NULL,
|
|
data jsonb NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY(project,workspace_id,run_id,seq),
|
|
FOREIGN KEY(project,workspace_id,run_id) REFERENCES canvas_ai_runs ON DELETE CASCADE
|
|
);
|
|
-- Per-run serialization increments last_seq and inserts an event in one statement.
|
|
-- A lease which expires after a worker started is not automatically re-executed:
|
|
-- recovery marks the uncertain step failed, preventing duplicate model invocations.
|
|
CREATE TABLE canvas_ai_mutations (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
run_id uuid NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
action text NOT NULL,
|
|
idempotency_key uuid NOT NULL,
|
|
payload_hash text NOT NULL,
|
|
response_body jsonb NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY(project,workspace_id,run_id,account_id,action,idempotency_key),
|
|
FOREIGN KEY(project,workspace_id,run_id) REFERENCES canvas_ai_runs ON DELETE CASCADE,
|
|
FOREIGN KEY(project,account_id) REFERENCES project_accounts
|
|
);
|
|
COMMIT;
|