67 lines
3.3 KiB
PL/PgSQL
67 lines
3.3 KiB
PL/PgSQL
BEGIN;
|
|
CREATE TABLE canvas_subgraphs (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
parent_board_id uuid NOT NULL,
|
|
frame_id text NOT NULL CHECK (frame_id ~ '^[A-Za-z0-9_-]{1,128}$'),
|
|
child_board_id uuid NOT NULL,
|
|
archived_at timestamptz,
|
|
migration_source jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY(project,workspace_id,child_board_id),
|
|
FOREIGN KEY(project,workspace_id,parent_board_id) REFERENCES materials ON DELETE RESTRICT,
|
|
FOREIGN KEY(project,workspace_id,child_board_id) REFERENCES materials ON DELETE RESTRICT,
|
|
CHECK(parent_board_id<>child_board_id)
|
|
);
|
|
CREATE UNIQUE INDEX canvas_subgraph_active_frame ON canvas_subgraphs(project,workspace_id,parent_board_id,frame_id) WHERE archived_at IS NULL;
|
|
-- Workspace row locks serialize ownership changes. A database trigger also rejects
|
|
-- cycles and excessive depth for maintenance callers outside the HTTP service.
|
|
CREATE FUNCTION foundry_check_subgraph() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE depth integer;
|
|
BEGIN
|
|
PERFORM 1 FROM workspaces WHERE project=NEW.project AND id=NEW.workspace_id FOR UPDATE;
|
|
IF EXISTS(SELECT 1 FROM materials WHERE project=NEW.project AND workspace_id=NEW.workspace_id AND id IN(NEW.parent_board_id,NEW.child_board_id) AND kind<>'board') THEN
|
|
RAISE EXCEPTION 'subgraph requires boards' USING ERRCODE='23514';
|
|
END IF;
|
|
WITH RECURSIVE ancestors(id,path) AS (
|
|
SELECT NEW.parent_board_id,ARRAY[NEW.child_board_id,NEW.parent_board_id]
|
|
UNION ALL
|
|
SELECT s.parent_board_id,a.path||s.parent_board_id FROM ancestors a JOIN canvas_subgraphs s
|
|
ON s.project=NEW.project AND s.workspace_id=NEW.workspace_id AND s.child_board_id=a.id
|
|
WHERE NOT s.parent_board_id=ANY(a.path) AND s.archived_at IS NULL
|
|
) SELECT max(cardinality(path)-1) INTO depth FROM ancestors;
|
|
IF depth>8 OR EXISTS(WITH RECURSIVE descendants(id,path) AS (
|
|
SELECT NEW.child_board_id,ARRAY[NEW.child_board_id]
|
|
UNION ALL SELECT s.child_board_id,d.path||s.child_board_id FROM descendants d JOIN canvas_subgraphs s
|
|
ON s.project=NEW.project AND s.workspace_id=NEW.workspace_id AND s.parent_board_id=d.id
|
|
WHERE NOT s.child_board_id=ANY(d.path) AND s.archived_at IS NULL
|
|
) SELECT 1 FROM descendants WHERE id=NEW.parent_board_id OR cardinality(path)+depth-1>8) THEN
|
|
RAISE EXCEPTION 'subgraph cycle or depth exceeded' USING ERRCODE='23514';
|
|
END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER canvas_subgraph_integrity BEFORE INSERT OR UPDATE ON canvas_subgraphs FOR EACH ROW EXECUTE FUNCTION foundry_check_subgraph();
|
|
CREATE TABLE canvas_transactions (
|
|
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,
|
|
action text NOT NULL,
|
|
idempotency_key uuid NOT NULL,
|
|
payload_hash text NOT NULL,
|
|
response_status integer NOT NULL,
|
|
response_body jsonb NOT NULL,
|
|
before_state jsonb NOT NULL,
|
|
after_versions jsonb NOT NULL,
|
|
relation_changes jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
expires_at timestamptz NOT NULL DEFAULT now()+interval '7 days',
|
|
undone_at timestamptz,
|
|
PRIMARY KEY(project,workspace_id,id),
|
|
UNIQUE(project,workspace_id,account_id,board_id,action,idempotency_key),
|
|
FOREIGN KEY(project,workspace_id,board_id) REFERENCES materials ON DELETE RESTRICT,
|
|
FOREIGN KEY(project,account_id) REFERENCES project_accounts
|
|
);
|
|
COMMIT;
|