..

0	CREATE TABLE tickets.users
1	(
2	uid UUID NOT NULL,
3	name CHARACTER VARYING(32) NOT NULL,
4	email CHARACTER VARYING(128) NOT NULL,
5	created_at TIMESTAMP WITH TIME ZONE NOT NULL,
6	updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
7	CONSTRAINT users_pk PRIMARY KEY (uid),
8	CONSTRAINT users_unique_name UNIQUE (name),
9	CONSTRAINT users_unique_email UNIQUE (email)
10	)
11	WITH (
12	OIDS=FALSE
13	);
14
15	COMMENT ON TABLE tickets.users IS 'All users for the ticket system live within this table.';
16	COMMENT ON COLUMN tickets.users.uid IS 'A globally unique ID for the related user account. It must match the user ID from the hub account.';
17	COMMENT ON COLUMN tickets.users.name IS 'A username between 2 and 32 characters which must be globally unique, contain only lowercase alphanumeric characters and start with a character.';
18	COMMENT ON COLUMN tickets.users.email IS 'A globally unique email address associated with the account.';
19	COMMENT ON COLUMN tickets.users.created_at IS 'The timestamp of when the account was created.';
20	COMMENT ON COLUMN tickets.users.updated_at IS 'A timestamp when the account was last changed.';
21
22	CREATE TABLE tickets.sessions
23	(
24	id VARCHAR(32) NOT NULL,
25	uid UUID NOT NULL,
26	created_at TIMESTAMP WITH TIME ZONE NOT NULL,
27	updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
28	CONSTRAINT sessions_pk PRIMARY KEY (id),
29	CONSTRAINT sessions_fk_uid FOREIGN KEY (uid)
30	REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE CASCADE
31	)
32	WITH (
33	OIDS=FALSE
34	);
35
36	COMMENT ON TABLE tickets.sessions IS 'Keeps the sessions of users.';
37	COMMENT ON COLUMN tickets.sessions.id IS 'A globally unique session ID.';
38	COMMENT ON COLUMN tickets.sessions.uid IS 'The unique ID of the user account to whom the session belongs.';
39	COMMENT ON COLUMN tickets.sessions.created_at IS 'The timestamp of when the session was created.';
40	COMMENT ON COLUMN tickets.sessions.updated_at IS 'The session ID should be re-generated in regular intervals resulting in a copy of the old session entry with a new ID and the corresponding timestamp in this column.';
41
42	CREATE TABLE tickets.projects
43	(
44	id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
45	name CHARACTER VARYING(64) NOT NULL,
46	owner UUID NOT NULL,
47	is_private BOOLEAN NOT NULL DEFAULT FALSE,
48	description CHARACTER VARYING(254),
49	created_at TIMESTAMP WITH TIME ZONE NOT NULL,
50	updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
51	next_ticket_number INTEGER NOT NULL DEFAULT 1,
52	CONSTRAINT projects_unique_owner_name UNIQUE (owner, name),
53	CONSTRAINT projects_fk_uid FOREIGN KEY (owner)
54	REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE CASCADE
55	)
56	WITH (
57	OIDS=FALSE
58	);
59
60	COMMENT ON TABLE tickets.projects IS 'All projects which are basically mirrored repositories from the hub are stored within this table.';
61	COMMENT ON COLUMN tickets.projects.id IS 'An auto generated primary key.';
62	COMMENT ON COLUMN tickets.projects.name IS 'The name of the project. A project name must start with a letter or number and must contain only alphanumeric ASCII characters as well as minus or underscore signs. It must be between 2 and 64 characters long.';
63	COMMENT ON COLUMN tickets.projects.owner IS 'The unique ID of the user account that owns the project.';
64	COMMENT ON COLUMN tickets.projects.is_private IS 'A flag indicating if this project is private i.e. only visible / accessible for users with appropriate permissions.';
65	COMMENT ON COLUMN tickets.projects.description IS 'An optional short text description of the project.';
66	COMMENT ON COLUMN tickets.projects.created_at IS 'The timestamp of when the project was created.';
67	COMMENT ON COLUMN tickets.projects.updated_at IS 'A timestamp when the project was last changed.';
68	COMMENT ON COLUMN tickets.projects.next_ticket_number IS 'Tickets are numbered ascending per project and this field holds the next logical ticket number to be used and must be incremented upon creation of a new ticket.';
69
70	CREATE TABLE tickets.labels
71	(
72	id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
73	project BIGINT NOT NULL,
74	name CHARACTER VARYING(40) NOT NULL,
75	description CHARACTER VARYING(254) DEFAULT NULL,
76	colour CHARACTER VARYING(7) NOT NULL,
77	CONSTRAINT labels_unique_project_label UNIQUE (project, name),
78	CONSTRAINT labels_fk_project FOREIGN KEY (project)
79	REFERENCES tickets.projects (id) ON UPDATE CASCADE ON DELETE CASCADE
80	)
81	WITH (
82	OIDS=FALSE
83	);
84
85	COMMENT ON TABLE tickets.labels IS 'Labels used to add information to tickets.';
86	COMMENT ON COLUMN tickets.labels.id IS 'An auto generated primary key.';
87	COMMENT ON COLUMN tickets.labels.project IS 'The project to which this label belongs.';
88	COMMENT ON COLUMN tickets.labels.name IS 'A short descriptive name for the label which is supposed to be unique in a project context.';
89	COMMENT ON COLUMN tickets.labels.description IS 'An optional description if needed.';
90	COMMENT ON COLUMN tickets.labels.colour IS 'A hexadecimal HTML colour code which can be used to mark the label on a rendered website.';
91
92	CREATE TABLE tickets.milestones
93	(
94	id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
95	project BIGINT NOT NULL,
96	title CHARACTER VARYING(64) NOT NULL,
97	due_date DATE DEFAULT NULL,
98	description TEXT DEFAULT NULL,
99	CONSTRAINT milestones_unique_project_title UNIQUE (project, title),
100	CONSTRAINT milestones_fk_project FOREIGN KEY (project)
101	REFERENCES tickets.projects (id) ON UPDATE CASCADE ON DELETE CASCADE
102	)
103	WITH (
104	OIDS=FALSE
105	);
106
107	COMMENT ON TABLE tickets.milestones IS 'Milestones used to organise tickets';
108	COMMENT ON COLUMN tickets.milestones.project IS 'The project to which this milestone belongs.';
109	COMMENT ON COLUMN tickets.milestones.title IS 'A title for the milestone, usually a version number, a word or a short phrase that is supposed to be unique within a project context.';
110	COMMENT ON COLUMN tickets.milestones.due_date IS 'An optional date on which the milestone is supposed to be reached.';
111	COMMENT ON COLUMN tickets.milestones.description IS 'An optional longer description of the milestone.';
112
113	CREATE TABLE tickets.tickets
114	(
115	id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
116	project BIGINT NOT NULL,
117	number INT NOT NULL,
118	title CHARACTER VARYING(72) NOT NULL,
119	content TEXT DEFAULT NULL,
120	status CHARACTER VARYING(16) NOT NULL,
121	submitter UUID DEFAULT NULL,
122	created_at TIMESTAMP WITH TIME ZONE NOT NULL,
123	updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
124	CONSTRAINT tickets_unique_project_ticket UNIQUE (project, number),
125	CONSTRAINT tickets_fk_project FOREIGN KEY (project)
126	REFERENCES tickets.projects (id) ON UPDATE CASCADE ON DELETE CASCADE,
127	CONSTRAINT tickets_fk_submitter FOREIGN KEY (submitter)
128	REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE SET NULL
129	)
130	WITH (
131	OIDS=FALSE
132	);
133
134	CREATE INDEX tickets_status ON tickets.tickets (status);
135
136	COMMENT ON TABLE tickets.tickets IS 'Information about tickets for projects.';
137	COMMENT ON COLUMN tickets.tickets.id IS 'An auto generated primary key.';
138	COMMENT ON COLUMN tickets.tickets.project IS 'The unique ID of the project which is associated with the ticket.';
139	COMMENT ON COLUMN tickets.tickets.number IS 'The number of the ticket which must be unique within the scope of the project.';
140	COMMENT ON COLUMN tickets.tickets.title IS 'A concise and short description of the ticket which should not exceed 72 characters.';
141	COMMENT ON COLUMN tickets.tickets.content IS 'An optional field to describe the ticket in great detail if needed.';
142	COMMENT ON COLUMN tickets.tickets.status IS 'The current status of the ticket describing its life cycle.';
143	COMMENT ON COLUMN tickets.tickets.submitter IS 'The person who submitted (created) this ticket which is optional because of possible account deletion or other reasons.';
144	COMMENT ON COLUMN tickets.tickets.created_at IS 'The timestamp when the ticket was created / submitted.';
145	COMMENT ON COLUMN tickets.tickets.updated_at IS 'The timestamp when the ticket was last updated. Upon creation the update time equals the creation time.';
146
147	CREATE TABLE tickets.milestone_tickets
148	(
149	milestone BIGINT NOT NULL,
150	ticket BIGINT NOT NULL,
151	CONSTRAINT milestone_tickets_pk PRIMARY KEY (milestone, ticket),
152	CONSTRAINT milestone_tickets_fk_milestone FOREIGN KEY (milestone)
153	REFERENCES tickets.milestones (id) ON UPDATE CASCADE ON DELETE CASCADE,
154	CONSTRAINT milestone_tickets_fk_ticket FOREIGN KEY (ticket)
155	REFERENCES tickets.tickets (id) ON UPDATE CASCADE ON DELETE CASCADE
156	)
157	WITH (
158	OIDS=FALSE
159	);
160
161	COMMENT ON TABLE tickets.milestone_tickets IS 'This table stores the relation between milestones and their tickets.';
162	COMMENT ON COLUMN tickets.milestone_tickets.milestone IS 'The unique ID of the milestone.';
163	COMMENT ON COLUMN tickets.milestone_tickets.ticket IS 'The unique ID of the ticket that is attached to the milestone.';
164
165	CREATE TABLE tickets.ticket_assignees
166	(
167	ticket BIGINT NOT NULL,
168	assignee UUID NOT NULL,
169	CONSTRAINT ticket_assignees_pk PRIMARY KEY (ticket, assignee),
170	CONSTRAINT ticket_assignees_fk_ticket FOREIGN KEY (ticket)
171	REFERENCES tickets.tickets (id) ON UPDATE CASCADE ON DELETE CASCADE,
172	CONSTRAINT ticket_assignees_fk_assignee FOREIGN KEY (assignee)
173	REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE CASCADE
174	)
175	WITH (
176	OIDS=FALSE
177	);
178
179	COMMENT ON TABLE tickets.ticket_assignees IS 'This table stores the relation between tickets and their assignees.';
180	COMMENT ON COLUMN tickets.ticket_assignees.ticket IS 'The unqiue ID of the ticket.';
181	COMMENT ON COLUMN tickets.ticket_assignees.assignee IS 'The unique ID of the user account that is assigned to the ticket.';
182
183	CREATE TABLE tickets.ticket_labels
184	(
185	ticket BIGINT NOT NULL,
186	label BIGINT NOT NULL,
187	CONSTRAINT ticket_labels_pk PRIMARY KEY (ticket, label),
188	CONSTRAINT ticket_labels_fk_ticket FOREIGN KEY (ticket)
189	REFERENCES tickets.tickets (id) ON UPDATE CASCADE ON DELETE CASCADE,
190	CONSTRAINT ticket_labels_fk_label FOREIGN KEY (label)
191	REFERENCES tickets.labels (id) ON UPDATE CASCADE ON DELETE CASCADE
192	)
193	WITH (
194	OIDS=FALSE
195	);
196
197	COMMENT ON TABLE tickets.ticket_labels IS 'This table stores the relation between tickets and their labels.';
198	COMMENT ON COLUMN tickets.ticket_labels.ticket IS 'The unqiue ID of the ticket.';
199	COMMENT ON COLUMN tickets.ticket_labels.label IS 'The unique ID of the label that is attached to the ticket.';