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.

Use the api schema — Query the documented tables using the api schema rather than the public schema.

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
`phase_budgets`	The budget Mosaic displays for each phase
`member_budgets`	Time budgets per member or open role on each work category
`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.
`project_type`	string		no	`regular` for a normal project, `template` for a project template.
`rate_multiplier`	numeric		yes	The project's bill rate multiplier. `NULL` for a team that tracks budgets in hours. See Rate Multipliers.
`cost_rate_multiplier`	numeric		yes	The project's cost rate multiplier. `NULL` for a team that tracks budgets in hours.
`independent_phase_rates`	boolean		no	`true` when the project's Independent Phase Rates setting is on, so its phases and work categories use their own multipliers.
`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, including changes to its multipliers or Independent Phase Rates setting.

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	Hours budget entered on the phase. For the budget Mosaic displays, use `phase_budgets`.
`currency_budget`	numeric		yes	Currency budget entered on the phase. For the budget Mosaic displays, use `phase_budgets`.
`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).
`rate_multiplier`	numeric		yes	The phase's own bill rate multiplier. Used only when the project's `independent_phase_rates` is `true`. `NULL` on a milestone or for a team that tracks budgets in hours. See Rate Multipliers.
`cost_rate_multiplier`	numeric		yes	The phase's own cost rate multiplier, under the same conditions as `rate_multiplier`.
`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	The work category's own bill rate multiplier. Used only when the project's `independent_phase_rates` is `true`. `NULL` for a team that tracks budgets in hours. See Rate Multipliers.
`cost_rate_multiplier`	numeric		yes	The work category's own cost rate multiplier, under the same conditions as `rate_multiplier`.
`is_pto`	boolean		no	Whether the work category is a paid time off category.
`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 or open role's place on 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`	yes	The member. `NULL` for an open (unfilled) role; the role comes from `project_roles`.
`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.

Budgets

All dollar amounts in these views are at bill rate, in the currency they were stored in. No currency code is published.

Phase Budgets

The budget Mosaic displays on the project Budget screen for each phase, in hours and dollars. Unlike phases.hours_budget and phases.currency_budget, which hold the value entered on the phase, this view returns the calculated budget for every Budget With setting.

View: phase_budgets

Column	Type	FK	Nullable	Description
`phase_id`	bigint	`phases.id`	no	The phase.
`project_id`	bigint	`projects.id`	no	The project.
`budget_with`	string		yes	The budget setting in force for the phase. See values below.
`hours_budget`	numeric		yes	The hours budget Mosaic displays.
`currency_budget`	numeric		yes	The dollar budget Mosaic displays, at bill rate. `NULL` for a team that tracks budgets in hours.

Which rows appear: one row per phase, including subphases and archived phases. Milestones (phases.is_budget = false) have no row.

budget_with values:

Value	Setting in Mosaic
`phase_estimate`	Use Entered Budget (Total Hours on a team that tracks budgets in hours)
`member_estimates`	Total Member Time Budget
`spent_and_planned`	Total Spent + Planned Time
`work_category_estimates`	Subphase Budgets
`phase_estimate_percentage`	% of Parent Phase Budget

budget_with is the one column to read for a phase's budget setting. For a team that tracks budgets in currency, it is chosen by the phase's contract type (the Fixed Fee or Hourly setting). For a team that tracks budgets in hours, it is the phase's hours setting, published in the same vocabulary.

Empty vs. zero: A phase on Use Entered Budget with nothing entered returns NULL. A phase on a calculated setting with nothing to add up returns 0.

Hours on a currency team: For a team that tracks budgets in currency, hours_budget follows the dollar setting. The phase's own hours setting and any hours entered on the phase do not apply.

Member Budgets

Time budgets for each member and open role on each work category. These are the figures behind the phase's Member Budget panel, and the detail behind a phase set to Total Member Time Budget in phase_budgets.

View: member_budgets

Column	Type	FK	Nullable	Description
`project_id`	bigint	`projects.id`	no	The project.
`phase_id`	bigint	`phases.id`, `phase_budgets.phase_id`	no	The phase or subphase the work category belongs to.
`work_category_id`	bigint	`work_categories.id`	no	The work category.
`project_membership_id`	bigint	`project_memberships.id`	no	The member's or open role's place on the project. Also joins to `project_membership_id` in `phase_memberships` and `project_roles`.
`member_id`	bigint	`members.id`	yes	The team member. `NULL` for an open role.
`hours_budget`	numeric		no	Budgeted hours. `0` when there is no time budget.
`currency_budget`	numeric		yes	Budgeted dollars at bill rate. `0` when there is no time budget. `NULL` for a team that tracks budgets in hours.
`archived_at`	datetime		yes	When the member's place on this work category, phase or project was archived, whichever came first. `NULL` while active.
`created_at`	datetime		no	When the member or open role was added to the work category.
`updated_at`	datetime		no	When the row last changed. See below.

What a row is: one member or open role on one work category of a phase. Every member and open role on a work category has a row, with 0 when they have no time budget, matching the "0h" the Budget screen shows. To count budgeted members, filter on hours_budget > 0.

Which time budgets are counted: the same ones phase_budgets counts, so the two always agree.

Open roles are included. member_id is NULL; join project_membership_id to project_roles to get the role.
Archived members are included, with archived_at set. The Budget screen hides them in its member rows but counts them in the phase total. If you filter them out, your totals will be lower than the phase budget.
Members removed from the project are excluded. This mostly covers open roles replaced when someone was assigned.
Deleted work categories and milestones have no rows.
Scope estimates follow each project's own setting. If the project uses scope estimates, a member's scope-level time budgets are summed into their work category row. Otherwise only phase-level time budgets count.
When a phase hides its work categories, only its default work category has rows, as on the Budget screen.

Placeholders: a project with no phases budgets on a placeholder phase, shown in Mosaic as "Project Schedule" (phases.is_default / phases.is_main). A phase with no work categories, or one that hides them, budgets on a placeholder work category, shown as "No Work Category" or "Time on Phase" (work_categories.is_default). Rows on placeholders are published like any other.

Reconciling with phase_budgets: on a phase set to Total Member Time Budget (budget_with = 'member_estimates'), the rows on that phase and its subphases add up to the phase's hours_budget and currency_budget in phase_budgets, within rounding. On phases with other settings, the rows still show each member's budget but the phase total comes from elsewhere.

About the dollar figures: Mosaic stores a member's dollar budget when it is saved, using that day's rate, multiplier and (where enabled) exchange rate, and refreshes it in the background when a rate or multiplier changes. A published dollar figure can therefore differ from hours × today's rate. The Budget screen reads the same stored amounts, so the two match.

updated_at and incremental pulls: updated_at moves on every change to a row, including clearing a time budget (the row stays, now with 0), archiving or unarchiving the member at any level, and turning the project's scope estimates on or off. A pull of rows changed since your last pull returns new and edited rows. It cannot return rows that leave the view (for example, a removed member or a deleted work category), so run a periodic full pull to replace your copy.

Rate Multipliers

Mosaic applies one bill rate multiplier and one cost rate multiplier to each piece of work. Which one applies depends on the project's Independent Phase Rates setting (projects.independent_phase_rates):

true: work on a work category uses that work category's multiplier (work_categories.rate_multiplier). Work on a phase uses the phase's multiplier (phases.rate_multiplier).
false: all work on the project uses the project's multiplier (projects.rate_multiplier). The phase and work category values are kept but unused, and apply again if the setting is turned back on.

Multipliers never stack: a phase's multiplier is never applied on top of the project's. The same rule applies to cost_rate_multiplier.

Read the setting before the value. Many projects have Independent Phase Rates off while their phases and work categories still hold different stored multipliers. Reading those values without checking independent_phase_rates gives the wrong multiplier.

Values are published as stored, unrounded. The Budget screen rounds them (for example, 3.2x). Multipliers are already included in the published rates and dollar amounts (time_entries.rate, phase_budgets, member_budgets), and they apply to non-billable work too. Mosaic keeps only the current multiplier: after a change, it reapplies the new value to past figures, so there is no multiplier history.

Multiplier in effect for each phase. This is the badge shown next to Member Budget on the phase (the cost rate multiplier is shown instead when the Budget screen shows cost rates):

SELECT
  ph.id AS phase_id,
  ph.name AS phase_name,
  p.independent_phase_rates,
  CASE WHEN p.independent_phase_rates THEN ph.rate_multiplier ELSE p.rate_multiplier END AS bill_rate_multiplier,
  CASE WHEN p.independent_phase_rates THEN ph.cost_rate_multiplier ELSE p.cost_rate_multiplier END AS cost_rate_multiplier
FROM phases ph
JOIN projects p ON p.id = ph.project_id
WHERE ph.is_budget;

For work on a work category, use work_categories.rate_multiplier in place of phases.rate_multiplier when independent_phase_rates is true.

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)
      │    │    └── member_budgets (via member_budgets.work_category_id)
      │    ├── phase_budgets (via phase_budgets.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)
      │    ├── member_budgets (via member_budgets.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)
 ├── member_budgets (via member_budgets.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

Use the api schema — These queries target the api schema, not public. If you're using psql directly, run SET search_path TO api; before your SELECT queries.

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;

Member budgets by phase, with each member's share of the phase budget

For each phase, sums each member's (or open role's) time budget across the phase's work categories and its subphases, as in the phase's Member Budget panel, and divides by the phase budget to get the % the Budget screen shows. The share uses dollars, or hours for a team that tracks budgets in hours (where the dollar columns are NULL).

SELECT
  ph.id AS phase_id,
  ph.name AS phase_name,
  mb.project_membership_id,
  COALESCE(m.first_name || ' ' || m.last_name, r.name || ' (open role)') AS member_or_role,
  SUM(mb.hours_budget) AS hours_budget,
  SUM(mb.currency_budget) AS currency_budget,
  ROUND(100.0 * COALESCE(
    SUM(mb.currency_budget) / NULLIF(pb.currency_budget, 0),  -- teams that track budgets in currency
    SUM(mb.hours_budget)    / NULLIF(pb.hours_budget, 0)      -- teams that track budgets in hours
  ), 1) AS pct_of_phase_budget
FROM phases ph
JOIN phase_budgets pb ON pb.phase_id = ph.id
-- rows on the phase itself or on any of its subphases
JOIN phases sub ON sub.project_id = ph.project_id
               AND (sub.id = ph.id OR ph.id = ANY(sub.ancestors))
JOIN member_budgets mb ON mb.phase_id = sub.id
LEFT JOIN members m ON m.id = mb.member_id
LEFT JOIN project_roles pr ON pr.project_membership_id = mb.project_membership_id
  AND mb.member_id IS NULL AND pr.archived_at IS NULL  -- open roles only
LEFT JOIN roles r ON r.id = pr.role_id
WHERE ph.project_id = 12345  -- your project
GROUP BY ph.id, ph.name, mb.project_membership_id, m.first_name, m.last_name, r.name,
         pb.currency_budget, pb.hours_budget
ORDER BY ph.name, member_or_role;

Archived members are counted in the phase budget. If you add WHERE mb.archived_at IS NULL, the shares will add up to less than 100%.