RelationalDesign: schema_creation.sql

File schema_creation.sql, 19.2 KB (added by 221544, 4 days ago)
Line 
1-- ===========================================================================
2-- Ecore - Project Management System
3-- PHASE P2 : Logical & Physical Design - Schema Creation (DDL)
4-- Target : PostgreSQL, schema "project"
5-- Source : server/prisma/schema.prisma (current, 17 entities + 1 enum)
6--
7-- Type mapping (Prisma -> PostgreSQL):
8-- Int -> INTEGER (PK: IDENTITY) Float -> DOUBLE PRECISION
9-- String -> TEXT Boolean -> BOOLEAN
10-- DateTime -> TIMESTAMP(3) Json -> JSONB
11-- String[] -> TEXT[] enum -> project.file_review_status
12--
13-- Re-runnable: the schema is dropped and recreated at the top.
14-- ===========================================================================
15
16DROP SCHEMA IF EXISTS project CASCADE;
17CREATE SCHEMA project;
18
19SET search_path TO project;
20
21-- ---------------------------------------------------------------------------
22-- Enumerated type: review workflow status for task files
23-- ---------------------------------------------------------------------------
24CREATE TYPE project.file_review_status AS ENUM (
25 'PENDING',
26 'APPROVED',
27 'NEEDS_REVISION',
28 'REJECTED'
29);
30
31-- ---------------------------------------------------------------------------
32-- users : every person in the system (admins, workers, clients) by role.
33-- Self-references itself for referrals and company-team membership.
34-- ---------------------------------------------------------------------------
35CREATE TABLE project.users (
36 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
37 email TEXT NOT NULL UNIQUE,
38 password TEXT NOT NULL,
39 role TEXT NOT NULL,
40 name TEXT NOT NULL,
41 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
42 company TEXT,
43 logo TEXT,
44 color_hex TEXT,
45 address TEXT,
46 postal_address TEXT,
47 phone_number TEXT,
48 extra_emails TEXT,
49 brand_pattern TEXT,
50 short_info TEXT,
51 nickname TEXT,
52 avatar_emoji TEXT,
53 skills TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
54 profile_status TEXT DEFAULT 'INCOMPLETE',
55 invite_token TEXT UNIQUE,
56 invite_expires TIMESTAMP(3),
57 referred_by_id INTEGER,
58 company_owner_id INTEGER,
59 CONSTRAINT fk_users_referred_by
60 FOREIGN KEY (referred_by_id) REFERENCES project.users (id) ON DELETE SET NULL,
61 CONSTRAINT fk_users_company_owner
62 FOREIGN KEY (company_owner_id) REFERENCES project.users (id) ON DELETE SET NULL
63);
64
65-- ---------------------------------------------------------------------------
66-- projects : a deliverable engagement carried out for a client.
67-- ---------------------------------------------------------------------------
68CREATE TABLE project.projects (
69 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
70 name TEXT NOT NULL,
71 description TEXT,
72 client_id INTEGER,
73 status TEXT NOT NULL DEFAULT 'ACTIVE',
74 service_type TEXT NOT NULL DEFAULT 'OTHER',
75 metadata JSONB,
76 start_date TIMESTAMP(3),
77 end_date TIMESTAMP(3),
78 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
79 updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
80 CONSTRAINT fk_projects_client
81 FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE SET NULL
82);
83CREATE INDEX idx_projects_client_id ON project.projects (client_id);
84CREATE INDEX idx_projects_status ON project.projects (status);
85
86-- ---------------------------------------------------------------------------
87-- domains : a web domain owned by a client (hosting + renewal lifecycle).
88-- ---------------------------------------------------------------------------
89CREATE TABLE project.domains (
90 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
91 client_id INTEGER NOT NULL,
92 domain_name TEXT NOT NULL,
93 is_primary BOOLEAN NOT NULL DEFAULT FALSE,
94 is_active BOOLEAN NOT NULL DEFAULT TRUE,
95 notes TEXT,
96 activation_date TIMESTAMP(3),
97 expiration_date TIMESTAMP(3),
98 lifespan_years INTEGER,
99 hosting_provider TEXT,
100 hosting_plan TEXT,
101 hosting_expiry TIMESTAMP(3),
102 status TEXT NOT NULL DEFAULT 'PENDING',
103 activation_email_sent_at TIMESTAMP(3),
104 renewal_reminder_sent_at TIMESTAMP(3),
105 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
106 updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
107 CONSTRAINT fk_domains_client
108 FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE CASCADE
109);
110CREATE INDEX idx_domains_client_id ON project.domains (client_id);
111CREATE INDEX idx_domains_expiration_date ON project.domains (expiration_date);
112
113-- ---------------------------------------------------------------------------
114-- tasks : a unit of work for a client, optionally grouped under a project.
115-- ---------------------------------------------------------------------------
116CREATE TABLE project.tasks (
117 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
118 title TEXT NOT NULL,
119 description TEXT,
120 status TEXT NOT NULL DEFAULT 'PENDING',
121 due_date TIMESTAMP(3),
122 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
123 updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
124 client_id INTEGER NOT NULL,
125 project_id INTEGER,
126 CONSTRAINT fk_tasks_client
127 FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE RESTRICT,
128 CONSTRAINT fk_tasks_project
129 FOREIGN KEY (project_id) REFERENCES project.projects (id) ON DELETE SET NULL
130);
131CREATE INDEX idx_tasks_project_id ON project.tasks (project_id);
132
133-- ---------------------------------------------------------------------------
134-- milestones : an ordered sub-step of a task (completion/deployment tracking).
135-- ---------------------------------------------------------------------------
136CREATE TABLE project.milestones (
137 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
138 task_id INTEGER NOT NULL,
139 title TEXT NOT NULL,
140 description TEXT,
141 image_url TEXT,
142 image_urls TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
143 pushed_to_github BOOLEAN NOT NULL DEFAULT FALSE,
144 deployed BOOLEAN NOT NULL DEFAULT FALSE,
145 is_done BOOLEAN NOT NULL DEFAULT FALSE,
146 done_at TIMESTAMP(3),
147 done_by INTEGER,
148 created_by_id INTEGER NOT NULL,
149 "order" INTEGER NOT NULL DEFAULT 0,
150 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
151 CONSTRAINT fk_milestones_task
152 FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE
153);
154CREATE INDEX idx_milestones_task_id ON project.milestones (task_id);
155
156-- ---------------------------------------------------------------------------
157-- task_workers : associative (M:N) table assigning workers (users) to tasks.
158-- ---------------------------------------------------------------------------
159CREATE TABLE project.task_workers (
160 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
161 task_id INTEGER NOT NULL,
162 user_id INTEGER NOT NULL,
163 CONSTRAINT uq_task_workers_task_user UNIQUE (task_id, user_id),
164 CONSTRAINT fk_task_workers_task
165 FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE,
166 CONSTRAINT fk_task_workers_user
167 FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE
168);
169CREATE INDEX idx_task_workers_task_id ON project.task_workers (task_id);
170CREATE INDEX idx_task_workers_user_id ON project.task_workers (user_id);
171
172-- ---------------------------------------------------------------------------
173-- task_files : a file/deliverable on a task, with review workflow + versioning.
174-- ---------------------------------------------------------------------------
175CREATE TABLE project.task_files (
176 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
177 task_id INTEGER NOT NULL,
178 file_name TEXT NOT NULL,
179 file_url TEXT NOT NULL,
180 file_type TEXT NOT NULL,
181 caption TEXT,
182 section TEXT,
183 is_completed BOOLEAN NOT NULL DEFAULT FALSE,
184 completed_at TIMESTAMP(3),
185 completed_by INTEGER,
186 uploaded_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
187 uploaded_by INTEGER NOT NULL,
188 version INTEGER NOT NULL DEFAULT 1,
189 review_status project.file_review_status NOT NULL DEFAULT 'PENDING',
190 review_comment TEXT,
191 visible_to_client BOOLEAN NOT NULL DEFAULT TRUE,
192 CONSTRAINT fk_task_files_task
193 FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE
194);
195
196-- ---------------------------------------------------------------------------
197-- file_comments : a comment on a task file (internal / client-visible).
198-- ---------------------------------------------------------------------------
199CREATE TABLE project.file_comments (
200 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
201 file_id INTEGER NOT NULL,
202 user_id INTEGER NOT NULL,
203 content TEXT NOT NULL,
204 visible_to_client BOOLEAN NOT NULL DEFAULT FALSE,
205 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
206 CONSTRAINT fk_file_comments_file
207 FOREIGN KEY (file_id) REFERENCES project.task_files (id) ON DELETE CASCADE,
208 CONSTRAINT fk_file_comments_user
209 FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE RESTRICT
210);
211
212-- ---------------------------------------------------------------------------
213-- task_comments : a comment on a task (internal / client-visible).
214-- ---------------------------------------------------------------------------
215CREATE TABLE project.task_comments (
216 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
217 task_id INTEGER NOT NULL,
218 user_id INTEGER NOT NULL,
219 content TEXT NOT NULL,
220 visible_to_client BOOLEAN NOT NULL DEFAULT FALSE,
221 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
222 CONSTRAINT fk_task_comments_task
223 FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE,
224 CONSTRAINT fk_task_comments_user
225 FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE RESTRICT
226);
227
228-- ---------------------------------------------------------------------------
229-- invoices : a bill issued to a client (header for line items + payments).
230-- ---------------------------------------------------------------------------
231CREATE TABLE project.invoices (
232 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
233 invoice_number TEXT NOT NULL UNIQUE,
234 amount DOUBLE PRECISION NOT NULL,
235 due_date TIMESTAMP(3) NOT NULL,
236 status TEXT NOT NULL DEFAULT 'PENDING',
237 description TEXT,
238 paid_at TIMESTAMP(3),
239 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
240 file_url TEXT,
241 payment_link TEXT,
242 client_id INTEGER NOT NULL,
243 issue_date TIMESTAMP(3),
244 payment_terms TEXT,
245 notes TEXT,
246 subtotal DOUBLE PRECISION,
247 tax_rate DOUBLE PRECISION,
248 tax_amount DOUBLE PRECISION,
249 CONSTRAINT fk_invoices_client
250 FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE RESTRICT
251);
252
253-- ---------------------------------------------------------------------------
254-- payments : a payment recorded against an invoice (supports partials).
255-- ---------------------------------------------------------------------------
256CREATE TABLE project.payments (
257 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
258 invoice_id INTEGER NOT NULL,
259 amount DOUBLE PRECISION NOT NULL,
260 paid_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
261 note TEXT,
262 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
263 CONSTRAINT fk_payments_invoice
264 FOREIGN KEY (invoice_id) REFERENCES project.invoices (id) ON DELETE CASCADE
265);
266CREATE INDEX idx_payments_invoice_id ON project.payments (invoice_id);
267
268-- ---------------------------------------------------------------------------
269-- invoice_line_items : a single billed line on an invoice.
270-- ---------------------------------------------------------------------------
271CREATE TABLE project.invoice_line_items (
272 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
273 invoice_id INTEGER NOT NULL,
274 name TEXT NOT NULL,
275 description TEXT,
276 quantity DOUBLE PRECISION NOT NULL,
277 unit_price DOUBLE PRECISION NOT NULL,
278 sort_order INTEGER NOT NULL DEFAULT 0,
279 CONSTRAINT fk_invoice_line_items_invoice
280 FOREIGN KEY (invoice_id) REFERENCES project.invoices (id) ON DELETE CASCADE
281);
282CREATE INDEX idx_invoice_line_items_invoice_id ON project.invoice_line_items (invoice_id);
283
284-- ---------------------------------------------------------------------------
285-- timesheet_projects : a project bucket for time tracking (distinct from
286-- delivery projects); optionally linked to a client.
287-- ---------------------------------------------------------------------------
288CREATE TABLE project.timesheet_projects (
289 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
290 project_name TEXT NOT NULL,
291 client_id INTEGER,
292 description TEXT,
293 is_paid BOOLEAN NOT NULL DEFAULT FALSE,
294 paid_at TIMESTAMP(3),
295 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
296 updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
297 CONSTRAINT fk_timesheet_projects_client
298 FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE SET NULL
299);
300CREATE INDEX idx_timesheet_projects_client_id ON project.timesheet_projects (client_id);
301CREATE INDEX idx_timesheet_projects_is_paid ON project.timesheet_projects (is_paid);
302
303-- ---------------------------------------------------------------------------
304-- timesheet_entries : a logged block of hours under a timesheet project.
305-- ---------------------------------------------------------------------------
306CREATE TABLE project.timesheet_entries (
307 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
308 project_id INTEGER NOT NULL,
309 date TIMESTAMP(3) NOT NULL,
310 hours_worked DOUBLE PRECISION NOT NULL,
311 hourly_rate DOUBLE PRECISION NOT NULL,
312 total_pay DOUBLE PRECISION NOT NULL,
313 notes TEXT,
314 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
315 updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
316 CONSTRAINT fk_timesheet_entries_project
317 FOREIGN KEY (project_id) REFERENCES project.timesheet_projects (id) ON DELETE CASCADE
318);
319CREATE INDEX idx_timesheet_entries_project_id ON project.timesheet_entries (project_id);
320CREATE INDEX idx_timesheet_entries_date ON project.timesheet_entries (date);
321
322-- ---------------------------------------------------------------------------
323-- notifications : an in-app notification for a user.
324-- ---------------------------------------------------------------------------
325CREATE TABLE project.notifications (
326 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
327 user_id INTEGER NOT NULL,
328 type TEXT NOT NULL,
329 title TEXT NOT NULL,
330 message TEXT NOT NULL,
331 read BOOLEAN NOT NULL DEFAULT FALSE,
332 link TEXT,
333 source_role TEXT,
334 task_id INTEGER,
335 thread_type TEXT,
336 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
337 CONSTRAINT fk_notifications_user
338 FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE
339);
340CREATE INDEX idx_notifications_user_id ON project.notifications (user_id);
341CREATE INDEX idx_notifications_read ON project.notifications (read);
342CREATE INDEX idx_notifications_user_created ON project.notifications (user_id, created_at);
343CREATE INDEX idx_notifications_user_type_source ON project.notifications (user_id, type, source_role);
344CREATE INDEX idx_notifications_user_task_thread ON project.notifications (user_id, task_id, thread_type);
345
346-- ---------------------------------------------------------------------------
347-- invites : a pending invitation to onboard a new user (token stored hashed).
348-- ---------------------------------------------------------------------------
349CREATE TABLE project.invites (
350 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
351 email TEXT NOT NULL,
352 role TEXT NOT NULL,
353 token_hash TEXT NOT NULL UNIQUE,
354 name TEXT NOT NULL,
355 company TEXT,
356 company_owner_id INTEGER,
357 domain_name TEXT,
358 domain_expiry TEXT,
359 hosting_plan TEXT,
360 hosting_expiry TEXT,
361 invited_by_id INTEGER NOT NULL,
362 used BOOLEAN NOT NULL DEFAULT FALSE,
363 cancelled BOOLEAN NOT NULL DEFAULT FALSE,
364 used_at TIMESTAMP(3),
365 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
366 expires_at TIMESTAMP(3) NOT NULL,
367 CONSTRAINT fk_invites_invited_by
368 FOREIGN KEY (invited_by_id) REFERENCES project.users (id) ON DELETE RESTRICT
369);
370CREATE INDEX idx_invites_token_hash ON project.invites (token_hash);
371CREATE INDEX idx_invites_email ON project.invites (email);
372CREATE INDEX idx_invites_expires_at ON project.invites (expires_at);
373
374-- ---------------------------------------------------------------------------
375-- notification_preferences : per-user, per-event-type channel toggles.
376-- ---------------------------------------------------------------------------
377CREATE TABLE project.notification_preferences (
378 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
379 user_id INTEGER NOT NULL,
380 event_type TEXT NOT NULL,
381 in_app_enabled BOOLEAN NOT NULL DEFAULT TRUE,
382 email_enabled BOOLEAN NOT NULL DEFAULT TRUE,
383 CONSTRAINT uq_notif_pref_user_event UNIQUE (user_id, event_type),
384 CONSTRAINT fk_notif_pref_user
385 FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE
386);
387CREATE INDEX idx_notif_pref_user_id ON project.notification_preferences (user_id);
388
389-- ===========================================================================
390-- End of schema_creation.sql (17 tables, 1 enum, 22 foreign keys)
391-- ===========================================================================