builds.sr.ht/schema.sql

CREATE TYPE auth_method AS ENUM (
	'OAUTH_LEGACY',
	'OAUTH2',
	'COOKIE',
	'INTERNAL',
	'WEBHOOK'
);

CREATE TYPE webhook_event AS ENUM (
	'JOB_CREATED'
);

CREATE TYPE visibility AS ENUM (
	'PUBLIC',
	'UNLISTED',
	'PRIVATE'
);

CREATE TABLE "user" (
	id serial PRIMARY KEY,
	username character varying(256) UNIQUE,
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL,
	oauth_token character varying(256),
	oauth_token_expires timestamp without time zone,
	email character varying(256) NOT NULL,
	user_type character varying DEFAULT 'active_non_paying'::character varying NOT NULL,
	url character varying(256),
	location character varying(256),
	bio character varying(4096),
	oauth_token_scopes character varying DEFAULT 'profile'::character varying,
	oauth_revocation_token character varying(256),
	suspension_notice character varying(4096)
);

CREATE TABLE secret (
	id serial PRIMARY KEY,
	user_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL,
	uuid uuid NOT NULL,
	name character varying(512),
	-- Key secrets:
	secret_type character varying NOT NULL,
	secret bytea NOT NULL,
	-- File secrets:
	path character varying(512),
	mode integer
);

CREATE INDEX ix_user_username ON "user" USING btree (username);

CREATE TABLE job_group (
	id serial PRIMARY KEY,
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL,
	owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
	note character varying(4096)
);

CREATE TABLE job (
	id serial PRIMARY KEY,
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL,
	manifest character varying(16384) NOT NULL,
	owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
	job_group_id integer REFERENCES job_group(id) ON DELETE SET NULL,
	note character varying(4096),
	tags character varying,
	runner character varying,
	status character varying NOT NULL,
	secrets boolean DEFAULT true NOT NULL,
	image character varying(128),
	visibility visibility NOT NULL
);

CREATE INDEX ix_job_owner_id ON job USING btree (owner_id);

CREATE INDEX ix_job_job_group_id ON job USING btree (job_group_id);

CREATE TABLE artifact (
	id serial PRIMARY KEY,
	created timestamp without time zone NOT NULL,
	job_id integer NOT NULL REFERENCES job(id) ON DELETE CASCADE,
	name character varying NOT NULL,
	path character varying NOT NULL,
	url character varying NOT NULL,
	size integer NOT NULL
);

CREATE TABLE task (
	id serial PRIMARY KEY,
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL,
	name character varying(256) NOT NULL,
	status character varying NOT NULL,
	job_id integer NOT NULL REFERENCES job(id) ON DELETE CASCADE
);

CREATE INDEX ix_task_job_id ON task USING btree (job_id);

CREATE TABLE trigger (
	id serial PRIMARY KEY,
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL,
	details character varying(4096) NOT NULL,
	condition character varying NOT NULL,
	trigger_type character varying NOT NULL,
	job_id integer REFERENCES job(id) ON DELETE CASCADE,
	job_group_id integer REFERENCES job_group(id) ON DELETE CASCADE
);

-- GraphQL webhooks
CREATE TABLE gql_user_wh_sub (
	id serial PRIMARY KEY,
	created timestamp without time zone NOT NULL,
	events webhook_event[] NOT NULL,
	url character varying NOT NULL,
	query character varying NOT NULL,
	auth_method auth_method NOT NULL,
	token_hash character varying(128),
	grants character varying,
	client_id uuid,
	expires timestamp without time zone,
	node_id character varying,
	user_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
	CONSTRAINT gql_user_wh_sub_auth_method_check
		CHECK ((auth_method = ANY (ARRAY['OAUTH2'::auth_method, 'INTERNAL'::public.auth_method]))),
	CONSTRAINT gql_user_wh_sub_check
		CHECK (((auth_method = 'OAUTH2'::auth_method) = (token_hash IS NOT NULL))),
	CONSTRAINT gql_user_wh_sub_check1
		CHECK (((auth_method = 'OAUTH2'::auth_method) = (expires IS NOT NULL))),
	CONSTRAINT gql_user_wh_sub_check2
		CHECK (((auth_method = 'INTERNAL'::auth_method) = (node_id IS NOT NULL))),
	CONSTRAINT gql_user_wh_sub_events_check
		CHECK ((array_length(events, 1) > 0))
);

CREATE INDEX gql_user_wh_sub_token_hash_idx
	ON gql_user_wh_sub
	USING btree (token_hash);

CREATE TABLE gql_user_wh_delivery (
	id serial PRIMARY KEY,
	uuid uuid NOT NULL,
	date timestamp without time zone NOT NULL,
	event webhook_event NOT NULL,
	subscription_id integer NOT NULL
		REFERENCES gql_user_wh_sub(id) ON DELETE CASCADE,
	request_body character varying NOT NULL,
	response_body character varying,
	response_headers character varying,
	response_status integer
);

-- Legacy OAuth
CREATE TABLE oauthtoken (
	id serial PRIMARY KEY,
	created timestamp without time zone NOT NULL,
	updated timestamp without time zone NOT NULL,
	expires timestamp without time zone NOT NULL,
	user_id integer REFERENCES "user"(id) ON DELETE CASCADE,
	token_hash character varying(128) NOT NULL,
	token_partial character varying(8) NOT NULL,
	scopes character varying(512) NOT NULL
);