Changes between Version 4 and Version 5 of ERModel

Timestamp:

07/01/26 14:44:35 (4 days ago)

Author:

221544

Comment:

--

ERModel

@@ -2 +2 @@
 
 == Diagram ==
-[[Image(EcoreErDiagram.png, width=100%)]]
+
+[[Image(ERModel_v01.png, width=100%)]]
 
 == Data Requirements ==
 
 === Users ===
-Represents all system users who may act as project owners or workers.
-
-''Candidate keys:''
- * user_id (primary key)
-
-''Attributes:''
- * user_id – numeric, required
+Represents every person in the system. A single table stores admins, workers, and clients, distinguished by the `role` attribute. This avoids duplicating shared attributes across separate person tables and lets any user participate in relationships uniformly. Self-references twice: a user may be referred by another user, and a team member may belong to a company owner.
+''Candidate keys:'' user_id (primary key), email (unique alternate key). Primary key is user_id — a stable surrogate, preferred over email which can change.
+''Attributes:''
+ * user_id – numeric, required, primary key
  * email – text, required, unique
- * password – text, required
- * role – text, required
- * createdAt – date/time, required
+ * password – text, required (bcrypt hash)
+ * role – text, required (ADMIN | ERASPHERE | WORKER | CLIENT)
+ * name – text, required
+ * company – text, optional
+ * phone_number – text, optional
+ * address – text, optional
+ * profile_status – text, optional (default INCOMPLETE)
+ * created_at – date/time, required
+ * (plus optional profile fields: logo, color_hex, short_info, brand_pattern, nickname, avatar_emoji, skills, extra_emails, invite_token, invite_expires)
 
 === Projects ===
-Represents projects owned by users.
-
-''Candidate keys:''
- * project_id (primary key)
-
-''Attributes:''
- * project_id – numeric
- * client_id – numeric
- * name – text
- * status – text
- * startDate – date
- * endDate – date
-
-=== Relationships ===
- * User owns Project (1:N)
- * Project includes Task (1:N)
- * Task has File (1:N)
- * Task has Comment (1:N)
- * TimesheetProject contains TimesheetEntry (1:N)
-
-== Entity-Relationship Model History ==
- * v01 – Initial ER model created based on project requirements.
+A deliverable engagement carried out for a client. The service_type plus a flexible metadata (JSON) field allow one entity to describe several kinds of work without a separate table per type.
+''Candidate keys:'' project_id (primary key).
+''Attributes:''
+ * project_id – numeric, required, primary key
+ * name – text, required
+ * description – text, optional
+ * client_id – numeric, optional, foreign key → Users
+ * status – text, required (default ACTIVE)
+ * service_type – text, required (default OTHER)
+ * metadata – json, optional
+ * start_date – date, optional
+ * end_date – date, optional
+ * created_at – date/time, required
+ * updated_at – date/time, required
+
+=== Domains ===
+A web domain owned by a client, tracking hosting details and a renewal lifecycle. Cascade-deletes with the client.
+''Candidate keys:'' domain_id (primary key).
+''Attributes:''
+ * domain_id – numeric, required, primary key
+ * client_id – numeric, required, foreign key → Users
+ * domain_name – text, required
+ * is_primary – boolean, required (default false)
+ * is_active – boolean, required (default true)
+ * hosting_provider – text, optional
+ * hosting_expiry – date/time, optional
+ * expiration_date – date/time, optional
+ * status – text, required (default PENDING)
+ * notes – text, optional
+
+=== Tasks ===
+A unit of work carried out for a client, optionally grouped under a project. Always tied to a client; the project link is optional so ad-hoc tasks can exist.
+''Candidate keys:'' task_id (primary key).
+''Attributes:''
+ * task_id – numeric, required, primary key
+ * title – text, required
+ * description – text, optional
+ * status – text, required (default PENDING)
+ * due_date – date/time, optional
+ * client_id – numeric, required, foreign key → Users
+ * project_id – numeric, optional, foreign key → Projects
+ * created_at – date/time, required
+ * updated_at – date/time, required
+
+=== Milestones ===
+An ordered sub-step of a task, tracking completion and deployment state. Cascade-deletes with its task.
+''Candidate keys:'' milestone_id (primary key).
+''Attributes:''
+ * milestone_id – numeric, required, primary key
+ * task_id – numeric, required, foreign key → Tasks
+ * title – text, required
+ * description – text, optional
+ * is_done – boolean, required (default false)
+ * deployed – boolean, required (default false)
+ * order – numeric, required (default 0)
+ * created_at – date/time, required
+
+=== TaskWorkers ===
+Associative (junction) entity resolving the many-to-many between tasks and users (workers). The unique (task_id, user_id) pair prevents duplicate assignments.
+''Candidate keys:'' id (primary key); composite (task_id, user_id) as unique alternate key.
+''Attributes:''
+ * id – numeric, required, primary key
+ * task_id – numeric, required, foreign key → Tasks
+ * user_id – numeric, required, foreign key → Users
+
+=== TaskFiles ===
+A file or deliverable attached to a task, with an approval workflow, versioning, and a client-visibility flag. Cascade-deletes with its task.
+''Candidate keys:'' file_id (primary key).
+''Attributes:''
+ * file_id – numeric, required, primary key
+ * task_id – numeric, required, foreign key → Tasks
+ * file_name – text, required
+ * file_url – text, required
+ * file_type – text, required
+ * version – numeric, required (default 1)
+ * review_status – enum (PENDING | APPROVED | NEEDS_REVISION | REJECTED)
+ * visible_to_client – boolean, required (default true)
+ * uploaded_at – date/time, required
+
+=== FileComments ===
+A comment on a task file, forming a per-file discussion thread. Cascades with the file.
+''Candidate keys:'' file_comment_id (primary key).
+''Attributes:''
+ * file_comment_id – numeric, required, primary key
+ * file_id – numeric, required, foreign key → TaskFiles
+ * user_id – numeric, required, foreign key → Users
+ * content – text, required
+ * visible_to_client – boolean, required (default false)
+ * created_at – date/time, required
+
+=== TaskComments ===
+A comment on a task, forming a per-task discussion thread. Cascades with the task.
+''Candidate keys:'' task_comment_id (primary key).
+''Attributes:''
+ * task_comment_id – numeric, required, primary key
+ * task_id – numeric, required, foreign key → Tasks
+ * user_id – numeric, required, foreign key → Users
+ * content – text, required
+ * visible_to_client – boolean, required (default false)
+ * created_at – date/time, required
+
+=== Invoices ===
+A bill issued to a client. Acts as the header for its line items and payments, storing monetary summaries.
+''Candidate keys:'' invoice_id (primary key); invoice_number (unique alternate key).
+''Attributes:''
+ * invoice_id – numeric, required, primary key
+ * invoice_number – text, required, unique
+ * amount – decimal, required
+ * status – text, required (default PENDING)
+ * due_date – date/time, required
+ * issue_date – date/time, optional
+ * subtotal – decimal, optional
+ * tax_amount – decimal, optional
+ * client_id – numeric, required, foreign key → Users
+ * created_at – date/time, required
+
+=== Payments ===
+A single payment recorded against an invoice. Modeled separately (rather than a paid flag) to support partial and multiple payments. Cascades with the invoice.
+''Candidate keys:'' payment_id (primary key).
+''Attributes:''
+ * payment_id – numeric, required, primary key
+ * invoice_id – numeric, required, foreign key → Invoices
+ * amount – decimal, required
+ * paid_at – date/time, required
+ * note – text, optional
+
+=== InvoiceLineItems ===
+One billed line on an invoice. Cascades with the invoice.
+''Candidate keys:'' line_item_id (primary key).
+''Attributes:''
+ * line_item_id – numeric, required, primary key
+ * invoice_id – numeric, required, foreign key → Invoices
+ * name – text, required
+ * quantity – decimal, required
+ * unit_price – decimal, required
+ * sort_order – numeric, required (default 0)
+
+=== TimesheetProjects ===
+A project bucket used purely for time tracking, separate from delivery projects so hours can be logged against informal or internal work.
+''Candidate keys:'' ts_project_id (primary key).
+''Attributes:''
+ * ts_project_id – numeric, required, primary key
+ * project_name – text, required
+ * client_id – numeric, optional, foreign key → Users
+ * description – text, optional
+ * is_paid – boolean, required (default false)
+ * created_at – date/time, required
+ * updated_at – date/time, required
+
+=== TimesheetEntries ===
+A logged block of hours under a timesheet project. Cascades with its timesheet project.
+''Candidate keys:'' ts_entry_id (primary key).
+''Attributes:''
+ * ts_entry_id – numeric, required, primary key
+ * project_id – numeric, required, foreign key → TimesheetProjects
+ * date – date/time, required
+ * hours_worked – decimal, required
+ * hourly_rate – decimal, required
+ * total_pay – decimal, required
+ * notes – text, optional
+
+=== Notifications ===
+An in-app notification delivered to a user, with type/source metadata used to route and badge messages. Cascades with the user.
+''Candidate keys:'' notification_id (primary key).
+''Attributes:''
+ * notification_id – numeric, required, primary key
+ * user_id – numeric, required, foreign key → Users
+ * type – text, required
+ * title – text, required
+ * message – text, required
+ * read – boolean, required (default false)
+ * created_at – date/time, required
+
+=== Invites ===
+A pending invitation to onboard a new user. The token is stored hashed for security, along with prefilled onboarding data and an expiry.
+''Candida