init.sql

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE projects (
	id SERIAL PRIMARY KEY UNIQUE NOT NULL,
	title TEXT
);

CREATE TABLE groups (
	id SERIAL PRIMARY KEY UNIQUE NOT NULL,
	label VARCHAR (99),
	x DOUBLE PRECISION,
	y DOUBLE PRECISION,
	project INT REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE,
	tree ltree DEFAULT text2ltree('0'),
	matrix_index ltree
);

CREATE TABLE matrixes (
	id SERIAL PRIMARY KEY UNIQUE NOT NULL,
	label VARCHAR (99),
	x DOUBLE PRECISION,
	y DOUBLE PRECISION,
	project INT REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE,
	cols JSONB DEFAULT '["header", "header"]',
	rows JSONB DEFAULT '["index", "index"]',
	tree ltree DEFAULT text2ltree('0')
);

CREATE TABLE notes (
	id SERIAL PRIMARY KEY UNIQUE NOT NULL,
	content TEXT,
	color VARCHAR (7),
	x DOUBLE PRECISION,
	y DOUBLE PRECISION,
	project INT REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE,
	tree ltree DEFAULT text2ltree('0'),
	pipe_from INT REFERENCES notes (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE cards (
	id SERIAL PRIMARY KEY UNIQUE NOT NULL,
	content JSONB,
	x DOUBLE PRECISION,
	y DOUBLE PRECISION,
	project INT REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE,
	tree ltree DEFAULT text2ltree('0'),
	source TEXT
);

CREATE TABLE titles (
	id SERIAL PRIMARY KEY UNIQUE NOT NULL,
	content TEXT,
	x DOUBLE PRECISION,
	y DOUBLE PRECISION,
	project INT REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE,
	tree ltree DEFAULT text2ltree('0')
);

CREATE TABLE datasources (
	id SERIAL PRIMARY KEY UNIQUE NOT NULL,
	query TEXT,
	platform VARCHAR (99),
	loaded INT,
	total INT,
	project INT REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE pipes (
	"from" INT REFERENCES groups (id) ON UPDATE CASCADE ON DELETE CASCADE,
	"to" INT UNIQUE REFERENCES groups (id) ON UPDATE CASCADE ON DELETE CASCADE
);
ALTER TABLE pipes DROP CONSTRAINT pipes_to_key;
ALTER TABLE pipes ADD COLUMN sm_pipe_id INT GENERATED ALWAYS AS
	(CASE WHEN "from" < "to" THEN "from" ELSE "to" END) STORED;
ALTER TABLE pipes ADD COLUMN lg_pipe_id INT GENERATED ALWAYS AS
	(CASE WHEN "from" > "to" THEN "from" ELSE "to" END) STORED;
ALTER TABLE pipes ADD CONSTRAINT unique_flow UNIQUE (sm_pipe_id, lg_pipe_id);


CREATE TABLE session (
    sid varchar NOT NULL COLLATE "default",
    sess json NOT NULL,
    expire timestamp(6) NOT NULL
) WITH (OIDS = FALSE);
ALTER TABLE session ADD CONSTRAINT session_pkey PRIMARY KEY (sid) NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX IDX_session_expire ON session ("expire");