Entity-Relationship Model v01

Diagram

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