· 6 years ago · Apr 20, 2019, 09:34 AM
1CREATE EXTENSION IF NOT EXISTS "pgcrypto";
2CREATE EXTENSION citext;
3
4
5CREATE TABLE "roles" (
6 "id" UUID DEFAULT gen_random_uuid(),
7 "name" VARCHAR UNIQUE NOT NULL,
8 PRIMARY KEY ("id")
9);
10
11CREATE TABLE "companies" (
12 "id" UUID DEFAULT gen_random_uuid(),
13 "name" TEXT,
14 "description" TEXT,
15 "address" TEXT,
16 "city" TEXT,
17 "zip" TEXT,
18 "phone" TEXT,
19 "website" TEXT,
20 "linkedin" TEXT,
21 PRIMARY KEY ("id")
22);
23
24CREATE TABLE "students" (
25 "id" UUID DEFAULT gen_random_uuid(),
26 "name" TEXT,
27 "course_of_study" TEXT,
28 "semester" text,
29 "website" TEXT,
30 "linkedin" TEXT,
31 "address" TEXT,
32 "city" TEXT,
33 "zip" TEXT,
34 "phone" TEXT,
35 "bio" TEXT,
36 PRIMARY KEY ("id")
37);
38
39CREATE TABLE "managers" (
40 "id" UUID DEFAULT gen_random_uuid(),
41 "company_id" UUID REFERENCES "companies"("id"),
42 "name" TEXT NOT NULL,
43 "phone" TEXT,
44 "linkedin" TEXT,
45 PRIMARY KEY ("id")
46);
47
48CREATE INDEX "managers_company_id_FK" ON "managers" ("company_id");
49
50CREATE TABLE "keywords" (
51 "id" UUID DEFAULT gen_random_uuid(),
52 "key" VARCHAR UNIQUE NOT NULL,
53 "description" TEXT,
54 PRIMARY KEY ("id")
55);
56
57CREATE TABLE "working_hours_types" (
58 "id" UUID DEFAULT gen_random_uuid(),
59 "type" VARCHAR UNIQUE NOT NULL,
60 PRIMARY KEY ("id")
61);
62
63CREATE TABLE "positions" (
64 "id" UUID DEFAULT gen_random_uuid(),
65 "company_id" UUID REFERENCES "companies"("id"),
66 "working_hours_type_id" UUID REFERENCES "working_hours_types"("id"),
67 "title" VARCHAR NOT NULL,
68 "description" TEXT NOT NULL,
69 "benefits" TEXT,
70 PRIMARY KEY ("id")
71);
72
73CREATE INDEX "positions_company_id_working_hours_type_id_FK" ON "positions" ("company_id", "working_hours_type_id");
74
75
76CREATE TABLE "conference_locations" (
77 "id" UUID DEFAULT gen_random_uuid(),
78 "address" TEXT NOT NULL,
79 "city" TEXT NOT NULL,
80 "zip" TEXT NOT NULL,
81 PRIMARY KEY ("id")
82);
83
84
85CREATE TABLE "conferences" (
86 "id" UUID DEFAULT gen_random_uuid(),
87 "conference_location_id" UUID REFERENCES "conference_locations"("id"),
88 "title" TEXT NOT NULL,
89 "description" TEXT,
90 PRIMARY KEY ("id")
91);
92
93CREATE INDEX "conference_conference_location_FK" ON "conferences" ("conference_location_id");
94
95
96CREATE TABLE "conference_contacts" (
97 "id" UUID DEFAULT gen_random_uuid(),
98 "name" TEXT NOT NULL,
99 "title" TEXT,
100 "phone" TEXT,
101 "email" citext,
102 "conference_id" UUID REFERENCES "conferences"("id"),
103 PRIMARY KEY ("id")
104);
105
106create unique index on conference_contacts ((lower(email)));
107
108CREATE INDEX "conference_contacts_conference_id_FK" ON "conference_contacts" ("conference_id");
109
110CREATE TABLE "admins" (
111 "id" UUID DEFAULT gen_random_uuid(),
112 "name" TEXT NOT NULL,
113 PRIMARY KEY ("id")
114);
115
116CREATE TABLE "news_articles" (
117 "id" UUID DEFAULT gen_random_uuid(),
118 "admin_id" UUID NOT NULL REFERENCES "admins"("id"),
119 "headline" TEXT NOT NULL,
120 "body" TEXT NOT NULL,
121 PRIMARY KEY ("id")
122);
123
124CREATE INDEX "news_articles_admins_id_FK" on "news_articles" ("admin_id");
125
126CREATE TABLE "notification_sources" (
127 "id" UUID DEFAULT gen_random_uuid(),
128 "company_id" UUID REFERENCES "companies"("id"),
129 "student_id" UUID REFERENCES "students"("id"),
130 "news_article_id" UUID REFERENCES "news_articles"("id"),
131 "position_id" UUID REFERENCES "positions"("id"),
132 manager_id uuid references managers(id),
133 conference_id uuid references conferences(id),
134 check(
135 (
136 company_id is not null and
137 student_id is null and
138 news_article_id is null and
139 position_id is null and
140 manager_id is null and
141 conference_id is null
142 ) or
143 (
144 student_id is not null and
145 company_id is null and
146 news_article_id is null and
147 position_id is null and
148 manager_id is null and
149 conference_id is null
150 ) or
151 (
152 news_article_id is not null and
153 company_id is null and
154 student_id is null and
155 position_id is null and
156 manager_id is null and
157 conference_id is null
158 ) or
159 (
160 position_id is not null and
161 company_id is null and
162 student_id is null and
163 news_article_id is null and
164 manager_id is null and
165 conference_id is null
166 ) or
167 (
168 manager_id is not null and
169 company_id is null and
170 student_id is null and
171 news_article_id is null and
172 position_id is null and
173 conference_id is null
174 ) or
175 (
176 manager_id is null and
177 company_id is null and
178 student_id is null and
179 news_article_id is null and
180 position_id is null and
181 conference_id is not null
182 )
183 ),
184 PRIMARY KEY ("id")
185);
186
187CREATE INDEX "notification_sources_company_student_news_article_position_FK" ON "notification_sources" ("company_id", "student_id", "news_article_id", "position_id");
188
189CREATE TABLE "activity_types" (
190 "id" UUID DEFAULT gen_random_uuid(),
191 "type" VARCHAR UNIQUE NOT NULL,
192 PRIMARY KEY ("id")
193);
194
195CREATE TABLE "notifications" (
196 "id" UUID DEFAULT gen_random_uuid(),
197 "notification_source_id" UUID REFERENCES "notification_sources"("id"),
198 "activity_type_id" UUID REFERENCES "activity_types"("id"),
199 "timestamp" TIMESTAMP NOT NULL default now(),
200 PRIMARY KEY ("id")
201);
202
203CREATE INDEX "notifications_notification_source_id_activity_type_id_FK" ON "notifications" ("notification_source_id", "activity_type_id");
204
205CREATE TABLE "manager_notifications" (
206 "id" UUID DEFAULT gen_random_uuid(),
207 "notification_id" UUID REFERENCES "notifications"("id"),
208 "manager_id" UUID REFERENCES "managers"("id"),
209 "seen" TIMESTAMP,
210 PRIMARY KEY ("id")
211);
212
213CREATE INDEX "manager_notifications_notification_id_manager_id_FK" ON "manager_notifications" ("notification_id", "manager_id");
214
215CREATE TABLE "company_notifications" (
216 "id" UUID DEFAULT gen_random_uuid(),
217 "notification_id" UUID REFERENCES "notifications"("id"),
218 "company_id" UUID REFERENCES "companies"("id"),
219 "seen" TIMESTAMP,
220 PRIMARY KEY ("id")
221);
222
223CREATE INDEX "company_notifications_notification_id_company_id_FK" ON "company_notifications" ("notification_id", "company_id");
224
225CREATE TABLE "interested_students" (
226 "id" UUID DEFAULT gen_random_uuid(),
227 "company_id" UUID REFERENCES "companies"("id"),
228 "student_id" UUID REFERENCES "students"("id"),
229 PRIMARY KEY ("id")
230);
231
232CREATE INDEX "interested_students_company_id_student_id_FK" ON "interested_students" ("company_id", "student_id");
233
234CREATE TABLE "conference_dates" (
235 "id" UUID DEFAULT gen_random_uuid(),
236 "from_date" DATE NOT NULL,
237 "to_date" DATE NOT NULL,
238 "conference_id" UUID REFERENCES "conferences"("id"),
239 PRIMARY KEY ("id")
240);
241
242CREATE INDEX "conference_dates_conference_id_FK" ON "conference_dates" ("conference_id");
243
244CREATE TABLE "company_contacts" (
245 "id" UUID DEFAULT gen_random_uuid(),
246 "company_id" UUID REFERENCES "companies"("id"),
247 "name" TEXT NOT NULL,
248 "phone" TEXT,
249 "email" citext,
250 PRIMARY KEY ("id")
251);
252
253create unique index on company_contacts ((lower(email)));
254CREATE INDEX "company_contacts_company_id_FK" ON "company_contacts" ("company_id");
255
256CREATE TABLE "right_types" (
257 "id" UUID DEFAULT gen_random_uuid(),
258 "type" VARCHAR UNIQUE NOT NULL,
259 PRIMARY KEY ("id")
260);
261
262CREATE TABLE "student_approved_rights" (
263 "id" UUID DEFAULT gen_random_uuid(),
264 "student_id" UUID NOT NULL REFERENCES "students"("id"),
265 "right_type_id" UUID NOT NULL REFERENCES "right_types"("id"),
266 PRIMARY KEY ("id")
267);
268
269CREATE INDEX "approved_rights_student_id_right_type_id_FK" ON "student_approved_rights" ("student_id", "right_type_id");
270
271create table student_approved_rights_values (
272 id uuid default gen_random_uuid(),
273 value text not null,
274 right_type_id uuid not null references right_types(id)
275);
276
277create index student_approved_rights_values_rights_id_FK on student_approved_rights_values (right_type_id);
278
279CREATE TABLE "work_experiences" (
280 "id" UUID DEFAULT gen_random_uuid(),
281 "student_id" UUID REFERENCES "students"("id"),
282 "company_name" TEXT NOT NULL,
283 "position" TEXT NOT NULL,
284 "years_of_exp" SMALLINT check(years_of_exp > 0) NOT NULL,
285 PRIMARY KEY ("id")
286);
287
288CREATE INDEX "work_experiences_student_id_FK" ON "work_experiences" ("student_id");
289
290CREATE TABLE "industry_categories" (
291 "id" UUID DEFAULT gen_random_uuid(),
292 "name" TEXT UNIQUE NOT NULL,
293 "description" TEXT,
294 PRIMARY KEY ("id")
295);
296
297create table positions_contained_categories (
298 id uuid default gen_random_uuid(),
299 position_id uuid not null references positions(id),
300 industry_category_id uuid not null references industry_categories(id),
301 primary key (id)
302);
303
304create index positions_contained_categories_positions_categories_FK on positions_contained_categories (position_id, industry_category_id);
305
306create table work_experience_contained_categories (
307 id uuid default gen_random_uuid(),
308 work_experience_id uuid not null references work_experiences(id),
309 industry_category_id uuid not null references industry_categories(id),
310 primary key (id)
311);
312
313create index work_experience_categories_work_exp_category_FK on work_experience_contained_categories (work_experience_id, industry_category_id);
314
315create table company_contained_categories (
316 id uuid default gen_random_uuid(),
317 company_id uuid not null references companies(id),
318 industry_category_id uuid not null references industry_categories(id),
319 primary key (id)
320);
321
322create index company_contained_categories_company_id_categories_id_FK on company_contained_categories (company_id, industry_category_id);
323
324
325CREATE TABLE "skills" (
326 "id" UUID DEFAULT gen_random_uuid(),
327 "position_id" UUID REFERENCES "positions"("id"),
328 "student_id" UUID REFERENCES "students"("id"),
329 "skill_level" SMALLINT check (skill_level >= 0 and skill_level <= 5) NOT NULL,
330 check (
331 (
332 position_id is not null and
333 student_id is null
334 ) or
335 (
336 position_id is null and
337 student_id is not null
338 )
339 ),
340 PRIMARY KEY ("id")
341);
342
343CREATE INDEX "skills_position_id_student_id_FK" ON "skills" ("position_id", "student_id");
344
345CREATE TABLE "works_withs" (
346 "id" UUID DEFAULT gen_random_uuid(),
347 "company_id" UUID NOT NULL REFERENCES "companies"("id"),
348 "industry_category_id" UUID NOT NULL REFERENCES "industry_categories"("id"),
349 PRIMARY KEY ("id")
350);
351
352CREATE INDEX "works_withs_company_id_industry_category_id_FK" ON "works_withs" ("company_id", "industry_category_id");
353
354create table skills_contained_keywords (
355 id uuid default gen_random_uuid(),
356 skill_id uuid not null references skills(id),
357 keyword_id uuid not null references keywords(id),
358 primary key(id)
359);
360
361create index skills_contained_keywords_skill_id_keyword_id_FK on skills_contained_keywords (skill_id, keyword_id);
362
363create table works_withs_contained_keywords (
364 id uuid default gen_random_uuid(),
365 works_with_id uuid not null references works_withs(id),
366 keyword_id uuid not null references keywords(id),
367 primary key(id)
368);
369
370create index works_withs_contained_keywords_works_with_id_keyword_id_FK on works_withs_contained_keywords (works_with_id, keyword_id);
371
372create table positions_contained_keywords (
373 id uuid default gen_random_uuid(),
374 position_id uuid not null references positions(id),
375 keyword_id uuid not null references keywords(id),
376 primary key(id)
377);
378
379create index positions_contained_keywords_position_id_keyword_id_FK on positions_contained_keywords (position_id, keyword_id);
380
381
382CREATE TABLE "file_types" (
383 "id" UUID DEFAULT gen_random_uuid(),
384 "type" VARCHAR UNIQUE NOT NULL,
385 PRIMARY KEY ("id")
386);
387
388CREATE TABLE "providers" (
389 "id" UUID DEFAULT gen_random_uuid(),
390 "name" VARCHAR NOT NULL,
391 PRIMARY KEY ("id")
392);
393
394CREATE TABLE "preferred_companies" (
395 "id" UUID DEFAULT gen_random_uuid(),
396 "student_id" UUID NOT NULL REFERENCES "students"("id"),
397 "company_id" UUID NOT NULL REFERENCES "companies"("id"),
398 PRIMARY KEY ("id")
399);
400
401CREATE INDEX "preferred_companies_student_id_company_id_FK" ON "preferred_companies" ("student_id", "company_id");
402
403CREATE TABLE "admin_roles" (
404 "id" UUID DEFAULT gen_random_uuid(),
405 "admin_id" UUID NOT NULL REFERENCES "admins"("id"),
406 "role_id" UUID NOT NULL REFERENCES "roles"("id"),
407 PRIMARY KEY ("id")
408);
409
410CREATE INDEX "admin_roles_admin_id_role_id_FK" ON "admin_roles" ("admin_id", "role_id");
411
412
413
414
415CREATE TABLE "student_notifications" (
416 "id" UUID DEFAULT gen_random_uuid(),
417 "notification_id" UUID NOT NULL REFERENCES "notifications"("id"),
418 "student_id" UUID NOT NULL REFERENCES "students"("id"),
419 "seen" TIMESTAMP,
420 PRIMARY KEY ("id")
421);
422
423CREATE INDEX "student_notifications_notification_id_student_id_FK" ON "student_notifications" ("notification_id", "student_id");
424
425
426CREATE TABLE "files" (
427 "id" UUID DEFAULT gen_random_uuid(),
428 "path" TEXT NOT NULL,
429 "file_type_id" UUID NOT NULL REFERENCES "file_types"("id"),
430 "manager_id" UUID REFERENCES "managers"("id"),
431 "company_id" UUID REFERENCES "companies"("id"),
432 "news_article_id" UUID REFERENCES "news_articles"("id"),
433 "admin_id" UUID REFERENCES "admins"("id"),
434 "position_id" UUID REFERENCES "positions"("id"),
435 "student_id" UUID REFERENCES "students"("id"),
436 check(
437 (
438 manager_id is not null and
439 company_id is null and
440 news_article_id is null and
441 admin_id is null and
442 position_id is null and
443 student_id is null
444 ) or
445 (
446 manager_id is null and
447 company_id is not null and
448 news_article_id is null and
449 admin_id is null and
450 position_id is null and
451 student_id is null
452 ) or
453 (
454 manager_id is null and
455 company_id is null and
456 news_article_id is not null and
457 admin_id is null and
458 position_id is null and
459 student_id is null
460 ) or
461 (
462 manager_id is null and
463 company_id is null and
464 news_article_id is null and
465 admin_id is not null and
466 position_id is null and
467 student_id is null
468 ) or
469 (
470 manager_id is null and
471 company_id is null and
472 news_article_id is null and
473 admin_id is null and
474 position_id is not null and
475 student_id is null
476 ) or
477 (
478 manager_id is null and
479 company_id is null and
480 news_article_id is null and
481 admin_id is null and
482 position_id is null and
483 student_id is not null
484 )
485 ),
486 PRIMARY KEY ("id")
487);
488
489CREATE INDEX "files_type_manager_company_article_admin_position_student_FK" ON "files" ("file_type_id", "manager_id", "company_id", "news_article_id", "admin_id", "position_id", "student_id");
490
491CREATE TABLE "manager_connections" (
492 "id" UUID DEFAULT gen_random_uuid(),
493 "manager_id" UUID NOT NULL REFERENCES "managers"("id"),
494 "student_id" UUID NOT NULL REFERENCES "students"("id"),
495 PRIMARY KEY ("id")
496);
497
498CREATE INDEX "manager_connections_manager_id_student_id_FK" ON "manager_connections" ("manager_id", "student_id");
499
500CREATE TABLE "preferred_positions" (
501 "id" UUID DEFAULT gen_random_uuid(),
502 "student_id" UUID NOT NULL REFERENCES "students"("id"),
503 "position_id" UUID NOT NULL REFERENCES "positions"("id"),
504 PRIMARY KEY ("id")
505);
506
507CREATE INDEX "preferred_positions_student_id_position_id_FK" ON "preferred_positions" ("student_id", "position_id");
508
509
510CREATE SCHEMA "private";
511
512CREATE TYPE "private".jwt_token AS (
513 "student_id" TEXT,
514 "manager_id" TEXT,
515 "admin_id" TEXT,
516 "company_id" TEXT,
517 "exp" INTEGER
518);
519
520CREATE TABLE "users" (
521 "email" citext,
522 "password" TEXT NOT NULL,
523 "student_id" UUID REFERENCES "students"("id") ON DELETE CASCADE,
524 "manager_id" UUID REFERENCES "managers"("id") ON DELETE CASCADE,
525 "admin_id" UUID REFERENCES "admins"("id") ON DELETE CASCADE,
526 "company_id" UUID REFERENCES "companies"("id") ON DELETE CASCADE,
527 check (
528 (
529 student_id is not null and
530 manager_id is null and
531 admin_id is null and
532 company_id is null
533 ) or
534 (
535 student_id is null and
536 manager_id is not null and
537 admin_id is null and
538 company_id is null
539 ) or
540 (
541 student_id is null and
542 manager_id is null and
543 admin_id is not null and
544 company_id is null
545 ) or
546 (
547 student_id is null and
548 manager_id is null and
549 admin_id is null and
550 company_id is not null
551 )
552 ),
553 PRIMARY KEY("email")
554);
555
556CREATE INDEX "users_students_managers_admins_FK" ON "users" ("student_id", "manager_id", "admin_id");
557
558create table attending_conferences (
559 id uuid default gen_random_uuid(),
560 company_id uuid references companies(id),
561 conference_id uuid references conferences(id),
562 "room" TEXT,
563 "floor" TEXT,
564 primary key (id)
565);
566
567create index attending_conferences_company_id_conference_id_FK on attending_conferences (company_id, conference_id);
568
569
570
571create or replace function notify_company_and_managers_and_students_of_new_conference() returns trigger as $$
572 declare
573 v_notification_source_id uuid;
574 v_activity_types_id uuid;
575 v_notification_id uuid;
576 v_company_ids uuid[];
577 v_manager_ids uuid[];
578 v_student_ids uuid[];
579 begin
580
581 select array_agg(id::uuid) into v_company_ids from companies;
582 select array_agg(id::uuid) into v_manager_ids from managers;
583 select array_agg(id::uuid) into v_student_ids from students;
584
585
586 select id into v_activity_types_id
587 from activity_types
588 where activity_types.type = 'new_conference';
589
590 insert into notification_sources(conference_id)
591 values (NEW.id)
592 returning id into v_notification_source_id;
593
594 insert into notifications(notification_source_id, activity_type_id)
595 values (v_notification_source_id, v_activity_types_id)
596 returning id into v_notification_id;
597
598
599 FOR index IN v_company_ids LOOP
600 insert into company_notifications(notification_id, manager_id)
601 values (v_notification_id, v_company_ids[index]);
602 END LOOP;
603
604 FOR index IN v_manager_ids LOOP
605 insert into manager_notifications(notification_id, manager_id)
606 values (v_notification_id, v_manager_ids[index]);
607 END LOOP;
608
609 FOR index IN v_student_ids LOOP
610 insert into student_notifications(notification_id, manager_id)
611 values (v_notification_id, v_student_ids[index]);
612 END LOOP;
613
614 return NEW;
615
616 end;
617&& language plpgsql volatile set search_path from current security definer;
618
619create trigger trigger_company_and_managers_and_students_of_new_conference
620after insert on conferences
621for each row execute procedure notify_company_and_managers_and_students_of_new_conference();
622
623
624
625
626--------------------------------------------------------------
627--------------------------------------------------------------
628
629--imports
630
631\i genesis.sql;
632\i row_level_sec.sql;
633\i helper_functions.sql;
634\i custom_functions.sql;
635\i policies.sql;
636\i table_comments.sql;
637\i column_specific_privileges.sql;
638
639-- triggers
640
641--\i notify_company_and_managers_and_students_of_new_conference.sql;
642--\i triggers/notify_company_and_managers_of_new_interested_student.sql;
643--\i triggers/notify_manager_of_new_company_position.sql;
644--\i triggers/notify_manager_of_new_student_connection.sql;
645--\i triggers/notify_student_of_new_preferred_company_position.sql;
646--\i triggers/notify_company_and_managers_of_upcomming_attending_conference.sql;
647
648--------------------------------------------------------------
649--------------------------------------------------------------