-- =========================================================================== -- Ecore - Project Management System -- PHASE P2 : Logical & Physical Design - Schema Creation (DDL) -- Target : PostgreSQL, schema "project" -- Source : server/prisma/schema.prisma (current, 17 entities + 1 enum) -- -- Type mapping (Prisma -> PostgreSQL): -- Int -> INTEGER (PK: IDENTITY) Float -> DOUBLE PRECISION -- String -> TEXT Boolean -> BOOLEAN -- DateTime -> TIMESTAMP(3) Json -> JSONB -- String[] -> TEXT[] enum -> project.file_review_status -- -- Re-runnable: the schema is dropped and recreated at the top. -- =========================================================================== DROP SCHEMA IF EXISTS project CASCADE; CREATE SCHEMA project; SET search_path TO project; -- --------------------------------------------------------------------------- -- Enumerated type: review workflow status for task files -- --------------------------------------------------------------------------- CREATE TYPE project.file_review_status AS ENUM ( 'PENDING', 'APPROVED', 'NEEDS_REVISION', 'REJECTED' ); -- --------------------------------------------------------------------------- -- users : every person in the system (admins, workers, clients) by role. -- Self-references itself for referrals and company-team membership. -- --------------------------------------------------------------------------- CREATE TABLE project.users ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email TEXT NOT NULL UNIQUE, password TEXT NOT NULL, role TEXT NOT NULL, name TEXT NOT NULL, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, company TEXT, logo TEXT, color_hex TEXT, address TEXT, postal_address TEXT, phone_number TEXT, extra_emails TEXT, brand_pattern TEXT, short_info TEXT, nickname TEXT, avatar_emoji TEXT, skills TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[], profile_status TEXT DEFAULT 'INCOMPLETE', invite_token TEXT UNIQUE, invite_expires TIMESTAMP(3), referred_by_id INTEGER, company_owner_id INTEGER, CONSTRAINT fk_users_referred_by FOREIGN KEY (referred_by_id) REFERENCES project.users (id) ON DELETE SET NULL, CONSTRAINT fk_users_company_owner FOREIGN KEY (company_owner_id) REFERENCES project.users (id) ON DELETE SET NULL ); -- --------------------------------------------------------------------------- -- projects : a deliverable engagement carried out for a client. -- --------------------------------------------------------------------------- CREATE TABLE project.projects ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, description TEXT, client_id INTEGER, status TEXT NOT NULL DEFAULT 'ACTIVE', service_type TEXT NOT NULL DEFAULT 'OTHER', metadata JSONB, start_date TIMESTAMP(3), end_date TIMESTAMP(3), created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_projects_client FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE SET NULL ); CREATE INDEX idx_projects_client_id ON project.projects (client_id); CREATE INDEX idx_projects_status ON project.projects (status); -- --------------------------------------------------------------------------- -- domains : a web domain owned by a client (hosting + renewal lifecycle). -- --------------------------------------------------------------------------- CREATE TABLE project.domains ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, client_id INTEGER NOT NULL, domain_name TEXT NOT NULL, is_primary BOOLEAN NOT NULL DEFAULT FALSE, is_active BOOLEAN NOT NULL DEFAULT TRUE, notes TEXT, activation_date TIMESTAMP(3), expiration_date TIMESTAMP(3), lifespan_years INTEGER, hosting_provider TEXT, hosting_plan TEXT, hosting_expiry TIMESTAMP(3), status TEXT NOT NULL DEFAULT 'PENDING', activation_email_sent_at TIMESTAMP(3), renewal_reminder_sent_at TIMESTAMP(3), created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_domains_client FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE CASCADE ); CREATE INDEX idx_domains_client_id ON project.domains (client_id); CREATE INDEX idx_domains_expiration_date ON project.domains (expiration_date); -- --------------------------------------------------------------------------- -- tasks : a unit of work for a client, optionally grouped under a project. -- --------------------------------------------------------------------------- CREATE TABLE project.tasks ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title TEXT NOT NULL, description TEXT, status TEXT NOT NULL DEFAULT 'PENDING', due_date TIMESTAMP(3), created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, client_id INTEGER NOT NULL, project_id INTEGER, CONSTRAINT fk_tasks_client FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE RESTRICT, CONSTRAINT fk_tasks_project FOREIGN KEY (project_id) REFERENCES project.projects (id) ON DELETE SET NULL ); CREATE INDEX idx_tasks_project_id ON project.tasks (project_id); -- --------------------------------------------------------------------------- -- milestones : an ordered sub-step of a task (completion/deployment tracking). -- --------------------------------------------------------------------------- CREATE TABLE project.milestones ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, task_id INTEGER NOT NULL, title TEXT NOT NULL, description TEXT, image_url TEXT, image_urls TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[], pushed_to_github BOOLEAN NOT NULL DEFAULT FALSE, deployed BOOLEAN NOT NULL DEFAULT FALSE, is_done BOOLEAN NOT NULL DEFAULT FALSE, done_at TIMESTAMP(3), done_by INTEGER, created_by_id INTEGER NOT NULL, "order" INTEGER NOT NULL DEFAULT 0, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_milestones_task FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE ); CREATE INDEX idx_milestones_task_id ON project.milestones (task_id); -- --------------------------------------------------------------------------- -- task_workers : associative (M:N) table assigning workers (users) to tasks. -- --------------------------------------------------------------------------- CREATE TABLE project.task_workers ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, task_id INTEGER NOT NULL, user_id INTEGER NOT NULL, CONSTRAINT uq_task_workers_task_user UNIQUE (task_id, user_id), CONSTRAINT fk_task_workers_task FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE, CONSTRAINT fk_task_workers_user FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE ); CREATE INDEX idx_task_workers_task_id ON project.task_workers (task_id); CREATE INDEX idx_task_workers_user_id ON project.task_workers (user_id); -- --------------------------------------------------------------------------- -- task_files : a file/deliverable on a task, with review workflow + versioning. -- --------------------------------------------------------------------------- CREATE TABLE project.task_files ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, task_id INTEGER NOT NULL, file_name TEXT NOT NULL, file_url TEXT NOT NULL, file_type TEXT NOT NULL, caption TEXT, section TEXT, is_completed BOOLEAN NOT NULL DEFAULT FALSE, completed_at TIMESTAMP(3), completed_by INTEGER, uploaded_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, uploaded_by INTEGER NOT NULL, version INTEGER NOT NULL DEFAULT 1, review_status project.file_review_status NOT NULL DEFAULT 'PENDING', review_comment TEXT, visible_to_client BOOLEAN NOT NULL DEFAULT TRUE, CONSTRAINT fk_task_files_task FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE ); -- --------------------------------------------------------------------------- -- file_comments : a comment on a task file (internal / client-visible). -- --------------------------------------------------------------------------- CREATE TABLE project.file_comments ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, file_id INTEGER NOT NULL, user_id INTEGER NOT NULL, content TEXT NOT NULL, visible_to_client BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_file_comments_file FOREIGN KEY (file_id) REFERENCES project.task_files (id) ON DELETE CASCADE, CONSTRAINT fk_file_comments_user FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE RESTRICT ); -- --------------------------------------------------------------------------- -- task_comments : a comment on a task (internal / client-visible). -- --------------------------------------------------------------------------- CREATE TABLE project.task_comments ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, task_id INTEGER NOT NULL, user_id INTEGER NOT NULL, content TEXT NOT NULL, visible_to_client BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_task_comments_task FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE, CONSTRAINT fk_task_comments_user FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE RESTRICT ); -- --------------------------------------------------------------------------- -- invoices : a bill issued to a client (header for line items + payments). -- --------------------------------------------------------------------------- CREATE TABLE project.invoices ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, invoice_number TEXT NOT NULL UNIQUE, amount DOUBLE PRECISION NOT NULL, due_date TIMESTAMP(3) NOT NULL, status TEXT NOT NULL DEFAULT 'PENDING', description TEXT, paid_at TIMESTAMP(3), created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, file_url TEXT, payment_link TEXT, client_id INTEGER NOT NULL, issue_date TIMESTAMP(3), payment_terms TEXT, notes TEXT, subtotal DOUBLE PRECISION, tax_rate DOUBLE PRECISION, tax_amount DOUBLE PRECISION, CONSTRAINT fk_invoices_client FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE RESTRICT ); -- --------------------------------------------------------------------------- -- payments : a payment recorded against an invoice (supports partials). -- --------------------------------------------------------------------------- CREATE TABLE project.payments ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, invoice_id INTEGER NOT NULL, amount DOUBLE PRECISION NOT NULL, paid_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, note TEXT, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES project.invoices (id) ON DELETE CASCADE ); CREATE INDEX idx_payments_invoice_id ON project.payments (invoice_id); -- --------------------------------------------------------------------------- -- invoice_line_items : a single billed line on an invoice. -- --------------------------------------------------------------------------- CREATE TABLE project.invoice_line_items ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, invoice_id INTEGER NOT NULL, name TEXT NOT NULL, description TEXT, quantity DOUBLE PRECISION NOT NULL, unit_price DOUBLE PRECISION NOT NULL, sort_order INTEGER NOT NULL DEFAULT 0, CONSTRAINT fk_invoice_line_items_invoice FOREIGN KEY (invoice_id) REFERENCES project.invoices (id) ON DELETE CASCADE ); CREATE INDEX idx_invoice_line_items_invoice_id ON project.invoice_line_items (invoice_id); -- --------------------------------------------------------------------------- -- timesheet_projects : a project bucket for time tracking (distinct from -- delivery projects); optionally linked to a client. -- --------------------------------------------------------------------------- CREATE TABLE project.timesheet_projects ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, project_name TEXT NOT NULL, client_id INTEGER, description TEXT, is_paid BOOLEAN NOT NULL DEFAULT FALSE, paid_at TIMESTAMP(3), created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_timesheet_projects_client FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE SET NULL ); CREATE INDEX idx_timesheet_projects_client_id ON project.timesheet_projects (client_id); CREATE INDEX idx_timesheet_projects_is_paid ON project.timesheet_projects (is_paid); -- --------------------------------------------------------------------------- -- timesheet_entries : a logged block of hours under a timesheet project. -- --------------------------------------------------------------------------- CREATE TABLE project.timesheet_entries ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, project_id INTEGER NOT NULL, date TIMESTAMP(3) NOT NULL, hours_worked DOUBLE PRECISION NOT NULL, hourly_rate DOUBLE PRECISION NOT NULL, total_pay DOUBLE PRECISION NOT NULL, notes TEXT, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_timesheet_entries_project FOREIGN KEY (project_id) REFERENCES project.timesheet_projects (id) ON DELETE CASCADE ); CREATE INDEX idx_timesheet_entries_project_id ON project.timesheet_entries (project_id); CREATE INDEX idx_timesheet_entries_date ON project.timesheet_entries (date); -- --------------------------------------------------------------------------- -- notifications : an in-app notification for a user. -- --------------------------------------------------------------------------- CREATE TABLE project.notifications ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, user_id INTEGER NOT NULL, type TEXT NOT NULL, title TEXT NOT NULL, message TEXT NOT NULL, read BOOLEAN NOT NULL DEFAULT FALSE, link TEXT, source_role TEXT, task_id INTEGER, thread_type TEXT, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE ); CREATE INDEX idx_notifications_user_id ON project.notifications (user_id); CREATE INDEX idx_notifications_read ON project.notifications (read); CREATE INDEX idx_notifications_user_created ON project.notifications (user_id, created_at); CREATE INDEX idx_notifications_user_type_source ON project.notifications (user_id, type, source_role); CREATE INDEX idx_notifications_user_task_thread ON project.notifications (user_id, task_id, thread_type); -- --------------------------------------------------------------------------- -- invites : a pending invitation to onboard a new user (token stored hashed). -- --------------------------------------------------------------------------- CREATE TABLE project.invites ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email TEXT NOT NULL, role TEXT NOT NULL, token_hash TEXT NOT NULL UNIQUE, name TEXT NOT NULL, company TEXT, company_owner_id INTEGER, domain_name TEXT, domain_expiry TEXT, hosting_plan TEXT, hosting_expiry TEXT, invited_by_id INTEGER NOT NULL, used BOOLEAN NOT NULL DEFAULT FALSE, cancelled BOOLEAN NOT NULL DEFAULT FALSE, used_at TIMESTAMP(3), created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP(3) NOT NULL, CONSTRAINT fk_invites_invited_by FOREIGN KEY (invited_by_id) REFERENCES project.users (id) ON DELETE RESTRICT ); CREATE INDEX idx_invites_token_hash ON project.invites (token_hash); CREATE INDEX idx_invites_email ON project.invites (email); CREATE INDEX idx_invites_expires_at ON project.invites (expires_at); -- --------------------------------------------------------------------------- -- notification_preferences : per-user, per-event-type channel toggles. -- --------------------------------------------------------------------------- CREATE TABLE project.notification_preferences ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, user_id INTEGER NOT NULL, event_type TEXT NOT NULL, in_app_enabled BOOLEAN NOT NULL DEFAULT TRUE, email_enabled BOOLEAN NOT NULL DEFAULT TRUE, CONSTRAINT uq_notif_pref_user_event UNIQUE (user_id, event_type), CONSTRAINT fk_notif_pref_user FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE ); CREATE INDEX idx_notif_pref_user_id ON project.notification_preferences (user_id); -- =========================================================================== -- End of schema_creation.sql (17 tables, 1 enum, 22 foreign keys) -- ===========================================================================