Direct Database Schema Overview

This guide provides an overview of the various entities included in our Direct Database Integration, detailing their attributes, types, and relationships with other entities, known as joined entities. This schema guide is designed to offer clarity and insight into the structure and interconnections between our database entity views.

Members

Table ID: members

Details employee profiles, including roles, departments, and other HR-related information. Fields include employee ID, name, department ID, role ID, and employment status.

Attribute Field	Attribute Type	Description
id	bigint	ID of the Member.
archived_at	datetime	The date and time this Member was archived.
email	string	The email of the Member.
employment_type	string	The type of employee this Member is.
first_name	string	The first name of the Member.
last_name	string	The last name of the Member.

Phases

Table ID: phases

A distinct stage within a project's lifecycle that is marked by a set of tasks, goals, and milestones. Fields include phase ID, name, start and end dates, budget information, and status.

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Phase.
archived_at	datetime		The date and time this Phase was archived.
bill_rate_type	string		Describes how billing rates are determined for work performed.
billable	boolean		Specifies if the work performed is billable or not.
budget_status	string		Specifies whether the Work Category is Active or not.
contract_type	string		Specifies if the contract is hourly or fixed.
created_at	datetime		The date and time this Work Category was created.
currency_budget	numeric		Estimated amount this Phase will cost.
end_date	datetime		The end date for this Phase.
fee	numeric		The total budget for the Phase.
hours_budget	numeric		The estimated amount of hours this Phase will take.
is_budget	boolean		Specifies if the Phase is a Phase or a Milestone. If `true`, then it’s a Phase . If `false`, then it’s a Milestone.
is_default	boolean		Specifies if the Phase is the default.
is_main	boolean		Specifies if it’s the main Phase of the Project.
name	string		Name of the Phase.
phase_number	string		The number of the phase.
project_id	bigint	projects.id	ID of the Project.
start_date	datetime		The start date for this Phase.
updated_at	datetime		The date and time this Phase was last updated.

Project Memberships

Table ID: project_memberships

Tracks the association of team members with specific projects, crucial for resource planning and allocation. Includes fields for project ID, employee ID, and role.

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Project Membership.
archived_at	datetime		The date and time this Project Membership was archived.
created_at	datetime		The date and time this Project Membership was created.
member_id	bigint	members.id	ID of the Member.
project_id	bigint	projects.id	ID of the Project.
updated_at	datetime		The date and time this Project Membership was last updated.

Project Scopes

Table ID: project_scopes

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Project Scope.
created_at	datetime		The date and time this Project Scope was created.
description	string		A description for the Project Scope.
member_id	bigint	members.id	ID of the Member.
phase_id	bigint	phases.id	ID of the Phase.
project_id	bigint	projects.id	ID of the Project.
standard_work_category_id	bigint	standard_work_categories.id	ID of the Standard Work Category.
updated_at	datetime		The date and time this Project Scope was last updated.
work_category_id	bigint	work_categories.id	ID of the Work Category.

Project Task Assignments

Table ID: project_task_assignments

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Project Task Assignment.
archived_at	datetime		The date and time this Project Task Assignment was archived.
assignee_id	bigint	members.id	ID of the Member who is assigned to this Project Task Assignment.
assigner_id	bigint	members.id	ID of the Member who assigned this Project Task Assignment.
created_at	datetime		The date and time this Project Task Assignment was created.
project_membership_id	bigint	project_memberships.id	ID of the Project Membership.
project_task_id	bigint	project_tasks.id	ID of the Project Task.
updated_at	datetime		The date and time this Project Task Assignment was last updated.

Project Tasks

Table ID: project_tasks

Details individual tasks within projects, including description, assigned members, deadlines, and current status. This table is essential for tracking project progress and task completion.

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Project Task.
activity_phase_sort_rank	bigint		Used to rank the tasks in the Work Category.
archived_at	datetime		The date and time this Project Task was archived.
completed_at	datetime		The date and time this entity was completed.
created_at	datetime		The date and time this Project Task was created.
description	string		A description for the Project Task.
estimated_hours	numeric		The estimated amount of hours this Project Task will take.
member_id	bigint	members.id	ID of the Member.
phase_id	bigint	phases.id	ID of the Phase.
project_id	bigint	projects.id	ID of the Project.
standard_work_category_id	bigint	standard_work_categories.id	ID of the Standard Work Category.
task_group_sort_rank	bigint		Used to rank the task in the Task Group.
task_list_id	bigint	task_lists.id	ID of the Task List.
updated_at	datetime		The date and time this Project Task was last updated.
work_category_id	bigint	work_categories.id	ID of the Work Category.

Projects

Table ID: projects

Central to project management, containing details on individual projects. Fields include project ID, name, budget information, and status.

Attribute Field	Attribute Type	Description
id	bigint	ID of the Project.
archived_at	datetime	The date and time this Project was archived.
billable	boolean	Specifies if the work performed is billable or not.
created_at	datetime	The date and time this Project was created.
description	string	A description for the Project.
project_number	string	The number of the Project.
title	string	Title of the Project.
updated_at	datetime	The date and time this Project was last updated.

Standard Work Categories

Table ID: standard_work_categories

A system used to categorize and track the work performed by employees or contractors. Fields include activity ID, name, start and end dates, budget information, and status.

Attribute Field	Attribute Type	Description
id	bigint	ID of the Standard Work Category.
archived	boolean	Specifies whether this Standard Work Category is archived or not.
billable	boolean	Specifies if the work performed is billable or not.
created_at	datetime	The date and time this Standard Work Category was created.
is_custom	boolean	Specifies whether this Standard Work Category is custom to this team or not.
is_default	boolean	Specifies if the Standard Work Category is the default.
title	string	Title of the Standard Work Category.
updated_at	datetime	The date and time this Standard Work Category was last updated.

Task Lists

Table ID: task_lists

Manages lists of tasks, serving as a breakdown structure for project activities and deliverables. Fields include task list ID, associated project ID, and task list description.

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Task List.
created_at	datetime		The date and time this Task List was created.
name	string		Name of the Task List.
project_id	bigint	projects.id	ID of the Project.
updated_at	datetime		The date and time this Task List was last updated.

Time Entries

Table ID: time_entries

Tracks time spent on work for a project, crucial for billing, productivity analysis, and resource allocation.

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Time Entry.
billable	boolean		Specifies if the work performed is billable or not.
cost_rate	numeric		Specifies the cost rate for the Time Entry.
created_at	datetime		The date and time this Time Entry was created.
date	datetime		Specifies the date for the Time Entry.
description	string		A description for the Project Scope.
hours	numeric		The amount of hours for this Time Entry.
member_id	bigint	members.id	ID of the Member.
phase_id	bigint	phases.id	ID of the Phase.
project_id	bigint	projects.id	ID of the Project.
rate	numeric		The bill rate for this Time Entry.
standard_work_category_id	bigint	standard_work_categories.id	ID of the Standard Work Category.
status	string		The status of the Time Entry.
updated_at	datetime		The date and time this Time Entry was last updated.
work_category_id	bigint	work_categories.id	ID of the Work Category.

Work Categories

Table ID: work_categories

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Work Category.
archived_at	datetime		The date and time this Work Category was archived.
billable	boolean		Specifies if the work performed is billable or not.
budget_status	string		Specifies whether the Work Category is Active or not.
cost_rate_multiplier	numeric		The rate multiplier for the cost rates.
created_at	datetime		The date and time this Work Category was created.
end_date	datetime		The end date for this Work Category.
estimated_cost	numeric		Estimated amount this Work Category will cost.
estimated_hours	numeric		The estimated amount of hours this Work Category will take.
is_default	boolean		Specifies if the Work Category is the default.
phase_id	bigint	phases.id	ID of the Phase.
project_id	bigint	projects.id	ID of the Project.
rate_multiplier	numeric		The rate multiplier for the bill rates.
standard_work_category_id	bigint	standard_work_categories.id	ID of the Standard Work Category.
start_date	datetime		The start date for this Work Category.
total	numeric		The total budget for the Work Category.
updated_at	datetime		The date and time this Work Category was last updated.

Work Plans

Table ID: work_plans

Strategic planning of work for a project, guiding project execution and alignment with objectives.

Attribute Field	Attribute Type	Foreign Key	Description
id	bigint		ID of the Work Plan.
created_at	datetime		The date and time this Work Plan was created.
daily_hours	numeric		The total amount of hours allocated each day for this Work Plan.
description	string		A description for the Work Plan.
end_date	datetime		The end date for this Work Plan.
member_id	bigint	members.id	ID of the Member.
phase_id	bigint	phases.id	ID of the Phase.
project_id	bigint	projects.id	ID of the Project.
standard_work_category_id	bigint	standard_work_categories.id	ID of the Standard Work Category.
start_date	datetime		The start date for this Work Plan.
total_hours	numeric		The total amount of hours for this Work Plan.
updated_at	datetime		The date and time this Work Plan was last updated.
work_category_id	bigint	work_categories.id	ID of the Work Category.