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. |
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. |
Updated 3 months ago