425 lines
15 KiB
SQL
425 lines
15 KiB
SQL
CREATE TYPE auth_method AS ENUM (
|
|
'OAUTH_LEGACY',
|
|
'OAUTH2',
|
|
'COOKIE',
|
|
'INTERNAL',
|
|
'WEBHOOK'
|
|
);
|
|
|
|
CREATE TYPE ticket_webhook_event AS ENUM (
|
|
'TICKET_UPDATE',
|
|
'EVENT_CREATED',
|
|
'TICKET_DELETED'
|
|
);
|
|
|
|
CREATE TYPE tracker_webhook_event AS ENUM (
|
|
'TRACKER_UPDATE',
|
|
'TRACKER_DELETED',
|
|
'TICKET_CREATED',
|
|
'TICKET_UPDATE',
|
|
'LABEL_CREATED',
|
|
'LABEL_UPDATE',
|
|
'LABEL_DELETED',
|
|
'EVENT_CREATED',
|
|
'TICKET_DELETED'
|
|
);
|
|
|
|
CREATE TYPE visibility AS ENUM (
|
|
'PUBLIC',
|
|
'UNLISTED',
|
|
'PRIVATE'
|
|
);
|
|
|
|
CREATE TYPE webhook_event AS ENUM (
|
|
'TRACKER_CREATED',
|
|
'TRACKER_UPDATE',
|
|
'TRACKER_DELETED',
|
|
'TICKET_CREATED'
|
|
);
|
|
|
|
CREATE TABLE "user" (
|
|
id integer 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,
|
|
oauth_token_scopes character varying,
|
|
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_revocation_token character varying(256),
|
|
suspension_notice character varying(4096),
|
|
notify_self boolean DEFAULT false NOT NULL
|
|
);
|
|
|
|
CREATE INDEX ix_user_username ON "user" USING btree (username);
|
|
|
|
CREATE TABLE participant (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
participant_type character varying NOT NULL,
|
|
user_id integer UNIQUE REFERENCES "user"(id) ON DELETE CASCADE,
|
|
email character varying UNIQUE,
|
|
email_name character varying,
|
|
external_id character varying UNIQUE,
|
|
external_url character varying
|
|
);
|
|
|
|
CREATE TABLE tracker (
|
|
id serial PRIMARY KEY,
|
|
owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
name character varying(1024),
|
|
description character varying(8192),
|
|
default_access integer DEFAULT 7 NOT NULL,
|
|
next_ticket_id integer DEFAULT 1 NOT NULL,
|
|
import_in_progress boolean DEFAULT false NOT NULL,
|
|
visibility visibility NOT NULL,
|
|
CONSTRAINT tracker_owner_id_name_unique UNIQUE (owner_id, name)
|
|
);
|
|
|
|
CREATE TABLE user_access (
|
|
id serial PRIMARY KEY,
|
|
tracker_id integer NOT NULL REFERENCES tracker(id) ON DELETE CASCADE,
|
|
user_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
permissions integer NOT NULL,
|
|
created timestamp without time zone NOT NULL,
|
|
CONSTRAINT idx_useraccess_tracker_user_unique UNIQUE (tracker_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE label (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
tracker_id integer NOT NULL REFERENCES tracker(id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
color text NOT NULL,
|
|
text_color text NOT NULL,
|
|
CONSTRAINT idx_tracker_name_unique UNIQUE (tracker_id, name)
|
|
);
|
|
|
|
CREATE TABLE ticket (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
tracker_id integer NOT NULL REFERENCES tracker(id) ON DELETE CASCADE,
|
|
dupe_of_id integer REFERENCES ticket(id) ON DELETE SET NULL,
|
|
title character varying(2048) NOT NULL,
|
|
description character varying(16384),
|
|
status integer DEFAULT 0 NOT NULL,
|
|
resolution integer DEFAULT 0 NOT NULL,
|
|
scoped_id integer NOT NULL,
|
|
submitter_id integer NOT NULL REFERENCES participant(id) ON DELETE CASCADE,
|
|
authenticity integer DEFAULT 0 NOT NULL,
|
|
comment_count integer DEFAULT 0 NOT NULL,
|
|
CONSTRAINT uq_ticket_scoped_id_tracker_id UNIQUE (scoped_id, tracker_id),
|
|
CONSTRAINT uq_ticket_tracker_id_scoped_id UNIQUE (tracker_id, scoped_id)
|
|
);
|
|
|
|
CREATE INDEX ix_ticket_comment_count ON ticket USING btree (comment_count);
|
|
|
|
CREATE INDEX ix_ticket_scoped_id ON ticket USING btree (scoped_id);
|
|
|
|
CREATE INDEX ticket_scoped_id ON ticket USING btree (scoped_id);
|
|
|
|
CREATE INDEX ticket_tracker_id ON ticket USING btree (tracker_id);
|
|
|
|
CREATE INDEX ticket_dupe_of_id ON ticket USING btree (dupe_of_id);
|
|
|
|
CREATE TABLE ticket_assignee (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
ticket_id integer NOT NULL REFERENCES ticket(id) ON DELETE CASCADE,
|
|
assignee_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
assigner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
CONSTRAINT idx_ticket_assignee_unique UNIQUE (ticket_id, assignee_id)
|
|
);
|
|
|
|
CREATE INDEX ticket_assignee_ticket_id ON ticket_assignee USING btree (ticket_id);
|
|
|
|
CREATE TABLE ticket_comment (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
ticket_id integer NOT NULL REFERENCES ticket(id) ON DELETE CASCADE,
|
|
text character varying(16384),
|
|
submitter_id integer NOT NULL REFERENCES participant(id),
|
|
authenticity integer DEFAULT 0 NOT NULL,
|
|
superceeded_by_id integer REFERENCES ticket_comment(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX ticket_comment_submitter_id ON ticket_comment USING btree (submitter_id);
|
|
|
|
CREATE INDEX ticket_comment_superceeded_by_id ON ticket_comment USING btree (superceeded_by_id);
|
|
|
|
CREATE INDEX ticket_comment_ticket_id ON ticket_comment USING btree (ticket_id);
|
|
|
|
CREATE TABLE ticket_label (
|
|
ticket_id integer NOT NULL REFERENCES ticket(id) ON DELETE CASCADE,
|
|
label_id integer NOT NULL REFERENCES label(id) ON DELETE CASCADE,
|
|
user_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
created timestamp without time zone NOT NULL,
|
|
PRIMARY KEY (ticket_id, label_id)
|
|
);
|
|
|
|
CREATE INDEX ticket_label_ticket_id ON ticket_label USING btree (ticket_id);
|
|
|
|
CREATE TABLE ticket_subscription (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
ticket_id integer REFERENCES ticket(id) ON DELETE CASCADE,
|
|
tracker_id integer REFERENCES tracker(id) ON DELETE CASCADE,
|
|
participant_id integer REFERENCES participant(id) ON DELETE CASCADE,
|
|
CONSTRAINT subscription_ticket_participant_uq UNIQUE (ticket_id, participant_id),
|
|
CONSTRAINT subscription_tracker_participant_uq UNIQUE (tracker_id, participant_id)
|
|
);
|
|
|
|
CREATE TABLE event (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
event_type integer NOT NULL,
|
|
old_status integer,
|
|
old_resolution integer,
|
|
new_status integer,
|
|
new_resolution integer,
|
|
ticket_id integer REFERENCES ticket(id) ON DELETE CASCADE,
|
|
comment_id integer REFERENCES ticket_comment(id) ON DELETE CASCADE,
|
|
label_id integer REFERENCES label(id) ON DELETE CASCADE,
|
|
from_ticket_id integer REFERENCES ticket(id) ON DELETE CASCADE,
|
|
participant_id integer REFERENCES participant(id) ON DELETE CASCADE,
|
|
by_participant_id integer REFERENCES participant(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX event_comment_id ON event USING btree (comment_id);
|
|
|
|
CREATE INDEX event_from_ticket_id ON event USING btree (from_ticket_id);
|
|
|
|
CREATE INDEX event_label_id ON event USING btree (label_id);
|
|
|
|
CREATE INDEX event_participant_id ON event USING btree (participant_id);
|
|
|
|
CREATE INDEX event_ticket_id ON event USING btree (ticket_id);
|
|
|
|
CREATE TABLE event_notification (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
event_id integer NOT NULL REFERENCES event(id) ON DELETE CASCADE,
|
|
user_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX event_notification_event_id ON event_notification USING btree (event_id);
|
|
|
|
-- 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'::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
|
|
);
|
|
|
|
CREATE TABLE gql_tracker_wh_sub (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
events tracker_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,
|
|
tracker_id integer NOT NULL REFERENCES tracker(id) ON DELETE CASCADE,
|
|
CONSTRAINT gql_tracker_wh_sub_auth_method_check
|
|
CHECK ((auth_method = ANY (ARRAY['OAUTH2'::auth_method, 'INTERNAL'::auth_method]))),
|
|
CONSTRAINT gql_tracker_wh_sub_check
|
|
CHECK (((auth_method = 'OAUTH2'::auth_method) = (token_hash IS NOT NULL))),
|
|
CONSTRAINT gql_tracker_wh_sub_check1
|
|
CHECK (((auth_method = 'OAUTH2'::auth_method) = (expires IS NOT NULL))),
|
|
CONSTRAINT gql_tracker_wh_sub_check2
|
|
CHECK (((auth_method = 'INTERNAL'::auth_method) = (node_id IS NOT NULL))),
|
|
CONSTRAINT gql_tracker_wh_sub_events_check
|
|
CHECK ((array_length(events, 1) > 0))
|
|
);
|
|
|
|
CREATE INDEX gql_tracker_wh_sub_token_hash_idx ON gql_tracker_wh_sub USING btree (token_hash);
|
|
|
|
CREATE TABLE gql_tracker_wh_delivery (
|
|
id serial PRIMARY KEY,
|
|
uuid uuid NOT NULL,
|
|
date timestamp without time zone NOT NULL,
|
|
event tracker_webhook_event NOT NULL,
|
|
subscription_id integer NOT NULL REFERENCES gql_tracker_wh_sub(id) ON DELETE CASCADE,
|
|
request_body character varying NOT NULL,
|
|
response_body character varying,
|
|
response_headers character varying,
|
|
response_status integer
|
|
);
|
|
|
|
CREATE TABLE gql_ticket_wh_sub (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
events ticket_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,
|
|
tracker_id integer NOT NULL REFERENCES tracker(id) ON DELETE CASCADE,
|
|
ticket_id integer NOT NULL REFERENCES ticket(id) ON DELETE CASCADE,
|
|
scoped_id integer NOT NULL,
|
|
CONSTRAINT gql_ticket_wh_sub_auth_method_check
|
|
CHECK ((auth_method = ANY (ARRAY['OAUTH2'::auth_method, 'INTERNAL'::auth_method]))),
|
|
CONSTRAINT gql_ticket_wh_sub_check
|
|
CHECK (((auth_method = 'OAUTH2'::auth_method) = (token_hash IS NOT NULL))),
|
|
CONSTRAINT gql_ticket_wh_sub_check1
|
|
CHECK (((auth_method = 'OAUTH2'::auth_method) = (expires IS NOT NULL))),
|
|
CONSTRAINT gql_ticket_wh_sub_check2
|
|
CHECK (((auth_method = 'INTERNAL'::auth_method) = (node_id IS NOT NULL))),
|
|
CONSTRAINT gql_ticket_wh_sub_events_check
|
|
CHECK ((array_length(events, 1) > 0))
|
|
);
|
|
|
|
CREATE INDEX gql_ticket_wh_sub_token_hash_idx ON gql_ticket_wh_sub USING btree (token_hash);
|
|
|
|
CREATE TABLE gql_ticket_wh_delivery (
|
|
id serial PRIMARY KEY,
|
|
uuid uuid NOT NULL,
|
|
date timestamp without time zone NOT NULL,
|
|
event ticket_webhook_event NOT NULL,
|
|
subscription_id integer NOT NULL REFERENCES gql_ticket_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 (TODO: Remove)
|
|
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,
|
|
token_hash character varying(128) NOT NULL,
|
|
token_partial character varying(8) NOT NULL,
|
|
scopes character varying(512) NOT NULL,
|
|
user_id integer REFERENCES "user"(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Legacy webhooks (TODO: Remove)
|
|
CREATE TABLE user_webhook_subscription (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
url character varying(2048) NOT NULL,
|
|
events character varying NOT NULL,
|
|
user_id integer REFERENCES "user"(id) ON DELETE CASCADE,
|
|
token_id integer REFERENCES oauthtoken(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE user_webhook_delivery (
|
|
id serial PRIMARY KEY,
|
|
uuid uuid NOT NULL,
|
|
created timestamp without time zone NOT NULL,
|
|
event character varying(256) NOT NULL,
|
|
url character varying(2048) NOT NULL,
|
|
payload character varying(65536) NOT NULL,
|
|
payload_headers character varying(16384) NOT NULL,
|
|
response character varying(65536),
|
|
response_status integer NOT NULL,
|
|
response_headers character varying(16384),
|
|
subscription_id integer NOT NULL REFERENCES user_webhook_subscription(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE tracker_webhook_subscription (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
url character varying(2048) NOT NULL,
|
|
events character varying NOT NULL,
|
|
user_id integer REFERENCES "user"(id) ON DELETE CASCADE,
|
|
token_id integer REFERENCES oauthtoken(id) ON DELETE CASCADE,
|
|
tracker_id integer NOT NULL REFERENCES tracker(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE tracker_webhook_delivery (
|
|
id serial PRIMARY KEY,
|
|
uuid uuid NOT NULL,
|
|
created timestamp without time zone NOT NULL,
|
|
event character varying(256) NOT NULL,
|
|
url character varying(2048) NOT NULL,
|
|
payload character varying(65536) NOT NULL,
|
|
payload_headers character varying(16384) NOT NULL,
|
|
response character varying(65536),
|
|
response_status integer NOT NULL,
|
|
response_headers character varying(16384),
|
|
subscription_id integer NOT NULL REFERENCES tracker_webhook_subscription(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE ticket_webhook_subscription (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
url character varying(2048) NOT NULL,
|
|
events character varying NOT NULL,
|
|
user_id integer REFERENCES "user"(id) ON DELETE CASCADE,
|
|
token_id integer REFERENCES oauthtoken(id) ON DELETE CASCADE,
|
|
ticket_id integer NOT NULL REFERENCES ticket(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE ticket_webhook_delivery (
|
|
id serial PRIMARY KEY,
|
|
uuid uuid NOT NULL,
|
|
created timestamp without time zone NOT NULL,
|
|
event character varying(256) NOT NULL,
|
|
url character varying(2048) NOT NULL,
|
|
payload character varying(65536) NOT NULL,
|
|
payload_headers character varying(16384) NOT NULL,
|
|
response character varying(65536),
|
|
response_status integer NOT NULL,
|
|
response_headers character varying(16384),
|
|
subscription_id integer NOT NULL REFERENCES ticket_webhook_subscription(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX ticket_webhook_subscription_ticket_id ON ticket_webhook_subscription USING btree (ticket_id);
|