Direct Database Schema

🚧
Static IP Required
DDI requires your system to have a static IP address. Without one, the connection may be unstable or interrupted.

This reference documents every view (table) exposed by the Mosaic Direct Database Integration (DDI). Each table section lists its columns, data types, foreign-key relationships, and usage notes.

All views are scoped to your team automatically via the DDI connection credentials.

Quick Reference

View Name	Description
`members`	Team members (employees / contractors)
`roles`	Organization-level roles (positions)
`portfolios`	Project portfolios
`projects`	Projects
`phases`	Project phases and milestones
`phase_memberships`	Members assigned to phases
`work_categories`	Phase-level work categories
`standard_work_categories`	Organization-level work category templates
`project_memberships`	Members assigned to projects
`project_roles`	Roles assigned to project members
`project_scopes`	Scope items on a project
`project_scope_assignments`	Members assigned to scope items
`task_lists`	Task list groupings
`project_tasks`	Individual tasks on a project
`project_task_assignments`	Members assigned to tasks
`project_notes`	Notes attached to projects
`time_entries`	Logged time entries
`work_plans`	Resource plans / schedule bars

Core Entities

Members

Team members represent the people in your organization.

View: members

Column	Type	Nullable	Description
`id`	bigint	no	Primary key.
`first_name`	string	yes	First name.
`last_name`	string	yes	Last name.
`email`	string	yes	Email address.
`employment_type`	string	yes	Employment type. See values below.
`reference_number`	string	yes	External reference / employee number.
`archived_at`	datetime	yes	When this member was archived. `NULL` if active.

employment_type values:

Value	Meaning
`Member`	Standard employee (default).
`Contractor (Internal)`	Internal contractor.
`Contractor (External)`	External contractor.
`Guest`	Guest user.

Roles

Organization-level roles that can be assigned to project members.

View: roles

Column	Type	Nullable	Description
`id`	bigint	no	Primary key.
`name`	string	no	Role name.
`is_default`	boolean	no	Whether this is the default role.
`created_at`	datetime	no	When this role was created.
`updated_at`	datetime	no	When this role was last updated.
`archived_at`	datetime	yes	When this role was archived. `NULL` if active.

Portfolios

Portfolios group projects together.

View: portfolios

Column	Type	Nullable	Description
`id`	bigint	no	Primary key.
`name`	string	no	Portfolio name.
`archived_at`	datetime	yes	When this portfolio was archived. `NULL` if active.
`created_at`	datetime	no	When this portfolio was created.
`updated_at`	datetime	no	When this portfolio was last updated.

Projects

Projects are the primary unit of work in Mosaic.

View: projects

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_number`	string		yes	The project number.
`title`	string		yes	Project title.
`description`	string		yes	Project description.
`billable`	boolean		yes	Whether work on this project is billable.
`portfolio_id`	bigint	`portfolios.id`	yes	The portfolio this project belongs to.
`archived_at`	datetime		yes	When this project was archived. `NULL` if active.
`created_at`	datetime		no	When this project was created.
`updated_at`	datetime		no	When this project was last updated.

Project Structure

Phases

Phases divide a project into budgeted stages or milestones.

View: phases

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_id`	bigint	`projects.id`	no	Parent project.
`name`	string		yes	Phase name.
`phase_number`	string		yes	Phase number identifier.
`is_budget`	boolean		no	`true` = Phase, `false` = Milestone.
`is_default`	boolean		no	Whether this is the default phase.
`is_main`	boolean		no	Whether this is the main phase of the project.
`fee`	numeric		yes	Total budget amount for the phase.
`contract_type`	string		yes	Contract type. See values below.
`bill_rate_type`	string		yes	How billing rates are determined. See values below.
`billable`	boolean		yes	Whether work in this phase is billable.
`hours_budget`	numeric		yes	Estimated hours budget.
`currency_budget`	numeric		yes	Estimated cost budget.
`budget_status`	string		yes	Phase budget status. See values below.
`budget_fixed_fee_with`	string		yes	How the fixed-fee budget is calculated. See values below.
`budget_hourly_with`	string		yes	How the hourly budget is calculated. See values below.
`budget_internal_with`	string		yes	How the internal budget is calculated. See values below.
`budget_phase_by`	string		yes	How the phase is budgeted. See values below.
`ancestors`	integer[]		yes	Array of ancestor phase IDs (parent chain).
`start_date`	datetime		yes	Phase start date.
`end_date`	datetime		yes	Phase end date.
`archived_at`	datetime		yes	When this phase was archived. `NULL` if active.
`created_at`	datetime		no	When this phase was created.
`updated_at`	datetime		no	When this phase was last updated.

contract_type values:

Value	Meaning
`Fixed Fee`	Fixed-fee contract.
`Hourly`	Hourly contract.
`Internal`	Internal (non-client) work.

bill_rate_type values:

Value	Meaning
`work_category`	Rates determined by work category.
`member`	Rates determined by team member.
`role`	Rates determined by role.
`NULL`	Not set.

budget_status values (phases):

Value	Meaning
`not started`	Budget has not started (default).
`hold`	Budget is on hold.
`active`	Budget is active.
`complete`	Budget is complete.

budget_fixed_fee_with / budget_hourly_with / budget_internal_with values:

Value	Meaning
`phase_estimate`	Use the phase-level estimate.
`member_estimates`	Use member-level estimates.
`spent_and_planned`	Use spent + planned hours.
`work_category_estimates`	Use work category estimates.
`phase_estimate_percentage`	Use phase estimate as a percentage.

budget_phase_by values:

Value	Meaning
`total hours`	Budget by total hours.
`member estimates`	Budget by member estimates.
`spent and planned`	Budget by spent + planned.

Phase Memberships

Associates team members with specific project phases.

View: phase_memberships

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_membership_id`	bigint	`project_memberships.id`	no	The project membership this belongs to.
`phase_id`	bigint	`phases.id`	no	The phase the member is assigned to.
`member_id`	bigint	`members.id`	yes	The assigned member.
`created_at`	datetime		no	When this assignment was created.
`updated_at`	datetime		no	When this assignment was last updated.
`archived_at`	datetime		yes	When this assignment was archived. `NULL` if active.

ℹ️
Only phase memberships with a valid project_membership_id are included in this view.

Work Categories

Phase-level work categories define billable/budgetable line items within a phase.

View: work_categories

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_id`	bigint	`projects.id`	no	Parent project.
`phase_id`	bigint	`phases.id`	no	Parent phase.
`standard_work_category_id`	bigint	`standard_work_categories.id`	yes	The org-level template this is based on.
`total`	numeric		yes	Total budget amount for this work category.
`is_default`	boolean		no	Whether this is the default work category.
`billable`	boolean		yes	Whether work in this category is billable.
`start_date`	datetime		yes	Start date.
`end_date`	datetime		yes	End date.
`estimated_hours`	numeric		yes	Estimated hours budget.
`estimated_cost`	numeric		yes	Estimated cost budget.
`budget_status`	string		yes	Work category budget status. See values below.
`rate_multiplier`	numeric		yes	Multiplier applied to bill rates.
`cost_rate_multiplier`	numeric		yes	Multiplier applied to cost rates.
`archived_at`	datetime		yes	When this work category was archived. `NULL` if active.
`created_at`	datetime		no	When this work category was created.
`updated_at`	datetime		no	When this work category was last updated.

budget_status values (work categories):

Value	Meaning
`active`	Work category is active (default).
`proposal`	Work category is in proposal stage.
`hold`	Work category is on hold.
`complete`	Work category is complete.

⚠️
Note: Work category budget_status values differ from phase budget_status values. Work categories use proposal where phases use not started.

Standard Work Categories

Organization-level work category templates. Individual work_categories on phases reference these.

View: standard_work_categories

Column	Type	Nullable	Description
`id`	bigint	no	Primary key.
`title`	string	no	Category name.
`archived`	boolean	no	Whether this category is archived.
`billable`	boolean	yes	Whether work in this category is billable.
`is_default`	boolean	no	Whether this is the default category.
`is_custom`	boolean	no	Whether this is a custom (team-created) category.
`created_at`	datetime	no	When this category was created.
`updated_at`	datetime	no	When this category was last updated.

People on Projects

Project Memberships

Represents a member's assignment to a project.

View: project_memberships

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_id`	bigint	`projects.id`	no	The project.
`member_id`	bigint	`members.id`	no	The member.
`archived_at`	datetime		yes	When this membership was archived. `NULL` if active.
`created_at`	datetime		no	When this membership was created.
`updated_at`	datetime		no	When this membership was last updated.

Project Roles

Assigns a role to a project member, with optional date ranges.

View: project_roles

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_membership_id`	bigint	`project_memberships.id`	no	The project membership this role belongs to.
`role_id`	bigint	`roles.id`	yes	The role assigned.
`role_number`	string		yes	The position number for this role assignment.
`start_date`	date		yes	When this role assignment starts.
`end_date`	date		yes	When this role assignment ends.
`created_at`	datetime		no	When this assignment was created.
`updated_at`	datetime		no	When this assignment was last updated.
`archived_at`	datetime		yes	When this assignment was archived. `NULL` if active.

Scope & Tasks

Project Scopes

Scope items represent deliverables or work items at the project level.

View: project_scopes

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_id`	bigint	`projects.id`	no	Parent project.
`phase_id`	bigint	`phases.id`	yes	Associated phase.
`standard_work_category_id`	bigint	`standard_work_categories.id`	yes	Associated standard work category.
`work_category_id`	bigint	`work_categories.id`	yes	Associated work category.
`description`	string		yes	Scope item description.
`created_at`	datetime		no	When this scope item was created.
`updated_at`	datetime		no	When this scope item was last updated.

Project Scope Assignments

Assigns members to scope items.

View: project_scope_assignments

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`member_id`	bigint	`members.id`	yes	The assigned member.
`assigner_id`	bigint	`members.id`	yes	The member who made the assignment.
`project_scope_id`	bigint	`project_scopes.id`	no	The scope item.
`project_membership_id`	bigint	`project_memberships.id`	yes	The project membership.
`created_at`	datetime		no	When this assignment was created.
`updated_at`	datetime		no	When this assignment was last updated.
`archived_at`	datetime		yes	When this assignment was archived. `NULL` if active.

Task Lists

Task lists group tasks within a project.

View: task_lists

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`name`	string		no	Task list name.
`project_id`	bigint	`projects.id`	no	Parent project.
`created_at`	datetime		no	When this task list was created.
`updated_at`	datetime		no	When this task list was last updated.

Project Tasks

Individual tasks within a project.

View: project_tasks

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`description`	string		yes	Task description.
`project_id`	bigint	`projects.id`	no	Parent project.
`phase_id`	bigint	`phases.id`	yes	Associated phase.
`standard_work_category_id`	bigint	`standard_work_categories.id`	yes	Associated standard work category.
`work_category_id`	bigint	`work_categories.id`	yes	Associated work category.
`estimated_hours`	numeric		yes	Estimated hours for this task.
`task_list_id`	bigint	`task_lists.id`	yes	The task list this task belongs to.
`task_list_sort_rank`	integer		yes	Sort order within the task list.
`work_category_sort_rank`	integer		yes	Sort order within the work category.
`completed_at`	datetime		yes	When this task was completed.
`archived_at`	datetime		yes	When this task was archived. `NULL` if active.
`created_at`	datetime		no	When this task was created.
`updated_at`	datetime		no	When this task was last updated.

Project Task Assignments

Assigns members to project tasks.

View: project_task_assignments

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`member_id`	bigint	`members.id`	yes	The assigned member.
`assigner_id`	bigint	`members.id`	yes	The member who made the assignment.
`project_task_id`	bigint	`project_tasks.id`	no	The task.
`project_membership_id`	bigint	`project_memberships.id`	yes	The project membership.
`created_at`	datetime		no	When this assignment was created.
`updated_at`	datetime		no	When this assignment was last updated.
`archived_at`	datetime		yes	When this assignment was archived. `NULL` if active.

Project Notes

Notes attached to projects.

View: project_notes

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`title`	string		yes	Note title.
`body`	string		yes	Note body text.
`project_id`	bigint	`projects.id`	no	Parent project.
`creator_id`	bigint		yes	Identifier of the note creator.
`created_at`	datetime		no	When this note was created.
`updated_at`	datetime		no	When this note was last updated.

⚠️
Important: creator_id is not directly joinable with the members table. It uses a different internal ID space.

Time & Resource Planning

Time Entries

Logged time records.

View: time_entries

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_id`	bigint	`projects.id`	no	Parent project.
`phase_id`	bigint	`phases.id`	yes	Associated phase.
`work_category_id`	bigint	`work_categories.id`	yes	Associated work category.
`standard_work_category_id`	bigint	`standard_work_categories.id`	yes	Associated standard work category.
`description`	string		yes	Entry description.
`billable`	boolean		yes	Whether this entry is billable.
`hours`	numeric		yes	Hours logged.
`date`	date		yes	Date of the time entry.
`rate`	numeric		yes	Bill rate applied.
`cost_rate`	numeric		yes	Cost rate applied.
`member_id`	bigint	`members.id`	yes	The member who logged the time.
`status`	string		yes	Approval status. See values below.
`created_at`	datetime		no	When this entry was created.
`updated_at`	datetime		no	When this entry was last updated.

status values:

Value	Meaning
`not_submitted`	Draft / not yet submitted for approval.
`submitted`	Submitted and awaiting approval.
`approved`	Approved.
`rejected`	Rejected by approver.

Work Plans

Resource allocation schedule bars that define planned work over a date range.

View: work_plans

Column	Type	FK	Nullable	Description
`id`	bigint		no	Primary key.
`project_id`	bigint	`projects.id`	no	Parent project.
`phase_id`	bigint	`phases.id`	yes	Associated phase.
`standard_work_category_id`	bigint	`standard_work_categories.id`	yes	Associated standard work category.
`work_category_id`	bigint	`work_categories.id`	yes	Associated work category.
`description`	string		yes	Work plan description.
`start_date`	date		yes	Start date of the plan.
`end_date`	date		yes	End date of the plan.
`daily_hours`	numeric		yes	Hours allocated per day. `NULL` when weekly planning is enabled.
`weekly_hours`	numeric		yes	Hours allocated per week. `NULL` when daily planning is used.
`total_hours`	numeric		yes	Total planned hours for the full date range.
`member_id`	bigint	`members.id`	yes	The planned member.
`project_membership_id`	bigint	`project_memberships.id`	yes	The project membership.
`effective_hours`	numeric		no	Total planned hours for this work plan, computed as the sum of its per-day hour breakdowns. Should approximately equal `total_hours`. Defaults to `0`.
`created_at`	datetime		no	When this work plan was created.
`updated_at`	datetime		no	When this work plan was last updated.

ℹ️
Daily vs. Weekly Planning: Each work plan uses either daily or weekly hour allocation, never both.

When daily planning is used: daily_hours contains the value, weekly_hours is NULL.

When weekly planning is used: weekly_hours contains the value, daily_hours is NULL.

Relationships Overview

The following describes the key join paths between DDI views:

portfolios
 └── projects (via projects.portfolio_id)
      ├── phases (via phases.project_id)
      │    ├── phases.ancestors[] → other phase IDs (parent chain)
      │    ├── work_categories (via work_categories.phase_id)
      │    └── phase_memberships (via phase_memberships.phase_id)
      ├── project_memberships (via project_memberships.project_id)
      │    ├── project_roles (via project_roles.project_membership_id)
      │    │    └── roles (via project_roles.role_id)
      │    ├── phase_memberships (via phase_memberships.project_membership_id)
      │    ├── project_task_assignments (via project_task_assignments.project_membership_id)
      │    ├── project_scope_assignments (via project_scope_assignments.project_membership_id)
      │    └── work_plans (via work_plans.project_membership_id)
      ├── project_scopes (via project_scopes.project_id)
      │    └── project_scope_assignments (via project_scope_assignments.project_scope_id)
      ├── task_lists (via task_lists.project_id)
      ├── project_tasks (via project_tasks.project_id)
      │    └── project_task_assignments (via project_task_assignments.project_task_id)
      ├── project_notes (via project_notes.project_id)
      ├── time_entries (via time_entries.project_id)
      └── work_plans (via work_plans.project_id)

members
 ├── project_memberships (via project_memberships.member_id)
 ├── phase_memberships (via phase_memberships.member_id)
 ├── project_task_assignments (via project_task_assignments.member_id)
 ├── project_scope_assignments (via project_scope_assignments.member_id)
 ├── time_entries (via time_entries.member_id)
 └── work_plans (via work_plans.member_id)

standard_work_categories
 ├── work_categories (via work_categories.standard_work_category_id)
 ├── project_scopes (via project_scopes.standard_work_category_id)
 ├── project_tasks (via project_tasks.standard_work_category_id)
 ├── time_entries (via time_entries.standard_work_category_id)
 └── work_plans (via work_plans.standard_work_category_id)

Common Query Patterns

Get all active project members with their roles

SELECT
  m.first_name,
  m.last_name,
  p.title AS project_title,
  r.name AS role_name
FROM project_memberships pm
JOIN members m ON m.id = pm.member_id
JOIN projects p ON p.id = pm.project_id
LEFT JOIN project_roles pr ON pr.project_membership_id = pm.id AND pr.archived_at IS NULL
LEFT JOIN roles r ON r.id = pr.role_id
WHERE pm.archived_at IS NULL;

Get time entries with full context

SELECT
  te.date,
  te.hours,
  te.status,
  m.first_name || ' ' || m.last_name AS member_name,
  p.title AS project_title,
  ph.name AS phase_name,
  wc.id AS work_category_id
FROM time_entries te
JOIN members m ON m.id = te.member_id
JOIN projects p ON p.id = te.project_id
LEFT JOIN phases ph ON ph.id = te.phase_id
LEFT JOIN work_categories wc ON wc.id = te.work_category_id;

Compare planned vs. actual hours by project

SELECT
  p.title,
  COALESCE(SUM(wp.total_hours), 0) AS planned_hours,
  COALESCE(SUM(wp.effective_hours), 0) AS effective_hours
FROM work_plans wp
JOIN projects p ON p.id = wp.project_id
GROUP BY p.title
ORDER BY p.title;

Direct Database Schema

Static IP Required

Quick Reference

Core Entities

Members

Roles

Portfolios

Projects

Project Structure

Phases

Phase Memberships

Only phase memberships with a valid `project_membership_id` are included in this view.

Work Categories

Note: Work category `budget_status` values differ from phase `budget_status` values. Work categories use `proposal` where phases use `not started`.

Standard Work Categories

People on Projects

Project Memberships

Project Roles

Scope & Tasks

Project Scopes

Project Scope Assignments

Task Lists

Project Tasks

Project Task Assignments

Project Notes

Important: `creator_id` is not directly joinable with the `members` table. It uses a different internal ID space.

Time & Resource Planning

Time Entries

Work Plans

Daily vs. Weekly Planning: Each work plan uses either daily or weekly hour allocation, never both.

Relationships Overview

Common Query Patterns

Get all active project members with their roles

Get time entries with full context

Compare planned vs. actual hours by project

Static IP Required

Quick Reference

Core Entities

Members

Roles

Portfolios

Projects

Project Structure

Phases

Phase Memberships

Only phase memberships with a valid project_membership_id are included in this view.

Work Categories

Note: Work category budget_status values differ from phase budget_status values. Work categories use proposal where phases use not started.

Standard Work Categories

People on Projects

Project Memberships

Project Roles

Scope & Tasks

Project Scopes

Project Scope Assignments

Task Lists

Project Tasks

Project Task Assignments

Project Notes

Important: creator_id is not directly joinable with the members table. It uses a different internal ID space.

Time & Resource Planning

Time Entries

Work Plans

Daily vs. Weekly Planning: Each work plan uses either daily or weekly hour allocation, never both.

Relationships Overview

Common Query Patterns

Get all active project members with their roles

Get time entries with full context

Compare planned vs. actual hours by project

Only phase memberships with a valid `project_membership_id` are included in this view.

Note: Work category `budget_status` values differ from phase `budget_status` values. Work categories use `proposal` where phases use `not started`.

Important: `creator_id` is not directly joinable with the `members` table. It uses a different internal ID space.