= Entity-Relationship Model v01 =

== Diagram ==

[[Image(ERModel_v01.png, width=100%)]]

== Data Requirements ==

=== Users ===
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 (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 ===
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