DatabaseCreation: ddl.sql

File ddl.sql, 13.1 KB (added by 235013, 11 days ago)
Line 
1CREATE TABLE Role
2(
3 role_id SERIAL NOT NULL PRIMARY KEY,
4 role_name text NOT NULL UNIQUE,
5 description text
6);
7
8CREATE TABLE Permission
9(
10 permission_id SERIAL NOT NULL PRIMARY KEY,
11 action_name text NOT NULL UNIQUE
12);
13
14CREATE TABLE Industry
15(
16 industry_id SERIAL NOT NULL PRIMARY KEY,
17 industry_name text NOT NULL UNIQUE
18);
19
20CREATE TABLE Project_Status
21(
22 status_id SERIAL NOT NULL PRIMARY KEY,
23 status_name text NOT NULL UNIQUE
24);
25
26CREATE TABLE Technology
27(
28 technology_id SERIAL NOT NULL PRIMARY KEY,
29 technology_name text NOT NULL UNIQUE
30);
31
32CREATE TABLE Rating_Dimension
33(
34 dimension_id SERIAL NOT NULL PRIMARY KEY,
35 dimension_name text NOT NULL UNIQUE,
36 description text NOT NULL
37);
38
39CREATE TABLE Subscription_Tier
40(
41 tier_id SERIAL NOT NULL PRIMARY KEY,
42 tier_name text NOT NULL UNIQUE,
43 list_price numeric(10, 2),
44 allows_custom_pricing bool NOT NULL DEFAULT false,
45
46 CONSTRAINT chk_tier_pricing_exclusivity
47 CHECK (
48 (allows_custom_pricing = true AND list_price IS NULL) OR
49 (allows_custom_pricing = false AND list_price IS NOT NULL)
50 ),
51
52 CONSTRAINT chk_tier_list_price_positive
53 CHECK (list_price IS NULL OR list_price > 0)
54);
55
56CREATE TABLE "User"
57(
58 user_id SERIAL NOT NULL PRIMARY KEY,
59 type text NOT NULL CHECK (type IN ('client', 'vendor', 'management')),
60 first_name text NOT NULL,
61 last_name text NOT NULL,
62 email text NOT NULL UNIQUE,
63 password_hash text NOT NULL,
64 is_active bool NOT NULL DEFAULT false,
65 last_login_at timestamp,
66 created_at timestamp NOT NULL DEFAULT NOW(),
67 updated_at timestamp NOT NULL DEFAULT NOW(),
68
69 CONSTRAINT chk_user_email_format
70 CHECK (email ~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$'),
71
72 CONSTRAINT chk_user_names_nonblank
73 CHECK (btrim(first_name) <> '' AND btrim(last_name) <> ''),
74
75 CONSTRAINT chk_user_password_nonblank
76 CHECK (btrim(password_hash) <> ''),
77
78 CONSTRAINT chk_user_timestamps
79 CHECK (updated_at >= created_at
80 AND (last_login_at IS NULL OR last_login_at >= created_at))
81);
82
83CREATE TABLE Vendor
84(
85 vendor_id SERIAL NOT NULL PRIMARY KEY,
86 agency_name text NOT NULL,
87 website text NOT NULL
88);
89
90CREATE TABLE Client
91(
92 client_id SERIAL NOT NULL PRIMARY KEY,
93 industry_id int4 NOT NULL,
94 company_name text NOT NULL,
95 contact_email text NOT NULL,
96
97 CONSTRAINT fk_client_industry
98 FOREIGN KEY (industry_id) REFERENCES Industry (industry_id)
99 ON DELETE RESTRICT
100);
101
102CREATE TABLE Client_User
103(
104 user_id int4 NOT NULL PRIMARY KEY,
105 client_id int4 NOT NULL,
106
107 CONSTRAINT fk_clientuser_user
108 FOREIGN KEY (user_id) REFERENCES "User" (user_id)
109 ON DELETE RESTRICT,
110
111 CONSTRAINT fk_clientuser_client
112 FOREIGN KEY (client_id) REFERENCES Client (client_id)
113 ON DELETE RESTRICT
114);
115
116CREATE TABLE Vendor_User
117(
118 user_id int4 NOT NULL PRIMARY KEY,
119 vendor_id int4 NOT NULL,
120
121 CONSTRAINT fk_vendoruser_user
122 FOREIGN KEY (user_id) REFERENCES "User" (user_id)
123 ON DELETE RESTRICT,
124
125 CONSTRAINT fk_vendoruser_vendor
126 FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
127 ON DELETE RESTRICT
128);
129
130CREATE TABLE Management_User
131(
132 user_id int4 NOT NULL PRIMARY KEY,
133 role_id int4 NOT NULL,
134
135 CONSTRAINT fk_mgmtuser_user
136 FOREIGN KEY (user_id) REFERENCES "User" (user_id)
137 ON DELETE RESTRICT,
138
139 CONSTRAINT fk_mgmtuser_role
140 FOREIGN KEY (role_id) REFERENCES Role (role_id)
141 ON DELETE RESTRICT
142);
143
144CREATE TABLE Role_Permission
145(
146 role_id int4 NOT NULL,
147 permission_id int4 NOT NULL,
148 PRIMARY KEY (role_id, permission_id),
149
150 CONSTRAINT fk_roleperm_role
151 FOREIGN KEY (role_id) REFERENCES Role (role_id)
152 ON DELETE RESTRICT,
153
154 CONSTRAINT fk_roleperm_permission
155 FOREIGN KEY (permission_id) REFERENCES Permission (permission_id)
156 ON DELETE RESTRICT
157);
158
159CREATE TABLE Vendor_Subscription
160(
161 contract_id SERIAL NOT NULL PRIMARY KEY,
162 vendor_id int4 NOT NULL,
163 tier_id int4 NOT NULL,
164 negotiated_price numeric(10, 2),
165 start_date date NOT NULL DEFAULT CURRENT_DATE,
166 end_date date,
167 is_active bool NOT NULL DEFAULT true,
168 created_at timestamp NOT NULL DEFAULT NOW(),
169 updated_at timestamp NOT NULL DEFAULT NOW(),
170
171 CONSTRAINT chk_vendorsub_dates
172 CHECK (end_date IS NULL OR end_date > start_date),
173
174 CONSTRAINT fk_vendorsub_vendor
175 FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
176 ON DELETE RESTRICT,
177
178 CONSTRAINT fk_vendorsub_tier
179 FOREIGN KEY (tier_id) REFERENCES Subscription_Tier (tier_id)
180 ON DELETE RESTRICT,
181
182 CONSTRAINT chk_vendorsub_price_positive
183 CHECK (negotiated_price IS NULL OR negotiated_price > 0)
184);
185
186CREATE TABLE Client_Vendor_Contract
187(
188 contract_id SERIAL NOT NULL PRIMARY KEY,
189 client_id int4 NOT NULL,
190 vendor_id int4 NOT NULL,
191 contract_number text UNIQUE,
192 contract_title text NOT NULL,
193 start_date date NOT NULL DEFAULT CURRENT_DATE,
194 end_date date,
195 total_value numeric(10, 2),
196 currency_code text,
197 terms_summary text,
198 is_active bool NOT NULL DEFAULT true,
199 created_at timestamp NOT NULL DEFAULT NOW(),
200 updated_at timestamp NOT NULL DEFAULT NOW(),
201
202 CONSTRAINT chk_cvc_dates
203 CHECK (end_date IS NULL OR end_date > start_date),
204
205 CONSTRAINT fk_cvc_client
206 FOREIGN KEY (client_id) REFERENCES Client (client_id)
207 ON DELETE RESTRICT,
208
209 CONSTRAINT fk_cvc_vendor
210 FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
211 ON DELETE RESTRICT,
212
213 CONSTRAINT chk_cvc_total_value_positive
214 CHECK (total_value IS NULL OR total_value > 0),
215
216 CONSTRAINT chk_cvc_currency_code
217 CHECK (currency_code IS NULL OR currency_code ~ '^[A-Z]{3}$'),
218
219 CONSTRAINT chk_cvc_title_nonblank
220 CHECK (btrim(contract_title) <> '')
221);
222
223CREATE TABLE Project
224(
225 project_id SERIAL NOT NULL PRIMARY KEY,
226 contract_id int4 NOT NULL,
227 status_id int4 NOT NULL,
228 project_name text NOT NULL,
229 start_date date NOT NULL DEFAULT CURRENT_DATE,
230 end_date date,
231 budget numeric(10, 2) NOT NULL,
232 created_at timestamp NOT NULL DEFAULT NOW(),
233 updated_at timestamp NOT NULL DEFAULT NOW(),
234
235 CONSTRAINT chk_project_dates
236 CHECK (end_date IS NULL OR end_date > start_date),
237
238 CONSTRAINT fk_project_contract
239 FOREIGN KEY (contract_id) REFERENCES Client_Vendor_Contract (contract_id)
240 ON DELETE RESTRICT,
241
242 CONSTRAINT fk_project_status
243 FOREIGN KEY (status_id) REFERENCES Project_Status (status_id)
244 ON DELETE RESTRICT,
245
246 CONSTRAINT chk_project_budget_positive
247 CHECK (budget > 0),
248
249 CONSTRAINT chk_project_name_nonblank
250 CHECK (btrim(project_name) <> ''),
251
252 CONSTRAINT uq_project_contract_name
253 UNIQUE (contract_id, project_name)
254);
255
256CREATE TABLE Project_Technology
257(
258 project_id int4 NOT NULL,
259 technology_id int4 NOT NULL,
260 PRIMARY KEY (project_id, technology_id),
261
262 CONSTRAINT fk_projtec_project
263 FOREIGN KEY (project_id) REFERENCES Project (project_id)
264 ON DELETE RESTRICT,
265
266 CONSTRAINT fk_projtec_technology
267 FOREIGN KEY (technology_id) REFERENCES Technology (technology_id)
268 ON DELETE RESTRICT
269);
270
271CREATE TABLE Project_Budget_Audit
272(
273 audit_id SERIAL NOT NULL PRIMARY KEY,
274 project_id int4 NOT NULL,
275 old_budget numeric(10, 2) NOT NULL,
276 new_budget numeric(10, 2) NOT NULL,
277 created_at timestamp NOT NULL DEFAULT NOW(),
278 updated_at timestamp NOT NULL DEFAULT NOW(),
279
280 CONSTRAINT fk_budgetaudit_project
281 FOREIGN KEY (project_id) REFERENCES Project (project_id)
282 ON DELETE RESTRICT,
283
284 CONSTRAINT chk_budgetaudit_positive
285 CHECK (old_budget > 0 AND new_budget > 0)
286);
287
288CREATE TABLE Project_Status_History
289(
290 project_status_history_id SERIAL NOT NULL PRIMARY KEY,
291 project_id int4 NOT NULL,
292 vendor_user_id int4,
293 management_user_id int4,
294 status_id int4 NOT NULL,
295 changed_at timestamp NOT NULL DEFAULT NOW(),
296 comment text,
297
298 CONSTRAINT chk_statushistory_single_actor
299 CHECK ((vendor_user_id IS NULL) <> (management_user_id IS NULL)),
300
301 CONSTRAINT fk_statushistory_project
302 FOREIGN KEY (project_id) REFERENCES Project (project_id)
303 ON DELETE RESTRICT,
304
305 CONSTRAINT fk_statushistory_vendoruser
306 FOREIGN KEY (vendor_user_id) REFERENCES Vendor_User (user_id)
307 ON DELETE RESTRICT,
308
309 CONSTRAINT fk_statushistory_mgmtuser
310 FOREIGN KEY (management_user_id) REFERENCES Management_User (user_id)
311 ON DELETE RESTRICT,
312
313 CONSTRAINT fk_statushistory_status
314 FOREIGN KEY (status_id) REFERENCES Project_Status (status_id)
315 ON DELETE RESTRICT
316);
317
318CREATE TABLE Review
319(
320 review_id SERIAL NOT NULL PRIMARY KEY,
321 project_id int4 NOT NULL UNIQUE,
322 client_user_id int4 NOT NULL,
323 review_date date NOT NULL DEFAULT CURRENT_DATE,
324 summary_text text NOT NULL,
325 is_published bool NOT NULL DEFAULT false,
326 created_at timestamp NOT NULL DEFAULT NOW(),
327 updated_at timestamp NOT NULL DEFAULT NOW(),
328
329 CONSTRAINT fk_review_project
330 FOREIGN KEY (project_id) REFERENCES Project (project_id)
331 ON DELETE RESTRICT,
332
333 CONSTRAINT fk_review_clientuser
334 FOREIGN KEY (client_user_id) REFERENCES Client_User (user_id)
335 ON DELETE RESTRICT,
336
337 CONSTRAINT chk_review_summary_nonblank
338 CHECK (btrim(summary_text) <> '')
339);
340
341CREATE TABLE Review_Score
342(
343 review_id int4 NOT NULL,
344 dimension_id int4 NOT NULL,
345 score_value int4 NOT NULL,
346 PRIMARY KEY (review_id, dimension_id),
347
348 CONSTRAINT fk_reviewscore_review
349 FOREIGN KEY (review_id) REFERENCES Review (review_id)
350 ON DELETE RESTRICT,
351
352 CONSTRAINT fk_reviewscore_dimension
353 FOREIGN KEY (dimension_id) REFERENCES Rating_Dimension (dimension_id)
354 ON DELETE RESTRICT,
355
356 CONSTRAINT chk_reviewscore_range
357 CHECK (score_value BETWEEN 1 AND 5)
358);
359
360CREATE TABLE Dispute_Ticket
361(
362 ticket_id SERIAL NOT NULL PRIMARY KEY,
363 assigned_management_user_id int4 DEFAULT NULL,
364 review_id int4 NOT NULL,
365 vendor_user_id int4 NOT NULL,
366 reason text NOT NULL,
367 is_resolved bool NOT NULL DEFAULT false,
368 filed_at date NOT NULL DEFAULT CURRENT_DATE,
369 created_at timestamp NOT NULL DEFAULT NOW(),
370 updated_at timestamp NOT NULL DEFAULT NOW(),
371 resolved_at timestamp,
372 resolution_note text,
373
374 CONSTRAINT fk_ticket_mgmtuser
375 FOREIGN KEY (assigned_management_user_id) REFERENCES Management_User (user_id)
376 ON DELETE RESTRICT,
377
378 CONSTRAINT fk_ticket_review
379 FOREIGN KEY (review_id) REFERENCES Review (review_id)
380 ON DELETE RESTRICT,
381
382 CONSTRAINT fk_ticket_vendoruser
383 FOREIGN KEY (vendor_user_id) REFERENCES Vendor_User (user_id)
384 ON DELETE RESTRICT,
385
386 CONSTRAINT chk_ticket_resolution
387 CHECK (
388 (is_resolved = false AND resolved_at IS NULL) OR
389 (is_resolved = true AND resolved_at IS NOT NULL
390 AND assigned_management_user_id IS NOT NULL
391 AND resolution_note IS NOT NULL)
392 ),
393
394 CONSTRAINT chk_ticket_resolved_after_filed
395 CHECK (resolved_at IS NULL OR resolved_at::date >= filed_at),
396
397 CONSTRAINT chk_ticket_reason_nonblank
398 CHECK (btrim(reason) <> ''),
399
400 CONSTRAINT chk_ticket_note_nonblank
401 CHECK (resolution_note IS NULL OR btrim(resolution_note) <> '')
402);
403
404
405-- Uniqueness applies only to active subscriptions and unresolved disputes.
406CREATE UNIQUE INDEX uq_vendorsub_one_active
407 ON Vendor_Subscription (vendor_id)
408 WHERE is_active;
409
410CREATE UNIQUE INDEX uq_dispute_open_per_vendor_review
411 ON Dispute_Ticket (review_id, vendor_user_id)
412 WHERE is_resolved = false;
413
414
415COMMENT ON COLUMN Vendor_Subscription.contract_id IS
416 'Surrogate key of a platform-vendor subscription period. Unrelated to Client_Vendor_Contract.contract_id, which Project.contract_id references.';
417
418COMMENT ON COLUMN Client_Vendor_Contract.total_value IS
419 'Framework value agreed in the contract document. Project budgets are planned independently and are not capped by it.';
420
421COMMENT ON CONSTRAINT chk_statushistory_single_actor ON Project_Status_History IS
422 'Exactly one actor per status change: either a vendor user or a management user.';