--- updatedAt: 2026-07-06T14:34:25.000Z agentTools: projectIndex: https://readme.mosaicapp.com/llms.txt --- # 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`](#members) | Team members (employees / contractors) | | [`roles`](#roles) | Organization-level roles (positions) | | [`portfolios`](#portfolios) | Project portfolios | | [`projects`](#projects) | Projects | | [`phases`](#phases) | Project phases and milestones | | [`phase_memberships`](#phase-memberships) | Members assigned to phases | | [`work_categories`](#work-categories) | Phase-level work categories | | [`standard_work_categories`](#standard-work-categories) | Organization-level work category templates | | [`project_memberships`](#project-memberships) | Members assigned to projects | | [`project_roles`](#project-roles) | Roles assigned to project members | | [`project_scopes`](#project-scopes) | Scope items on a project | | [`project_scope_assignments`](#project-scope-assignments) | Members assigned to scope items | | [`task_lists`](#task-lists) | Task list groupings | | [`project_tasks`](#project-tasks) | Individual tasks on a project | | [`project_task_assignments`](#project-task-assignments) | Members assigned to tasks | | [`project_notes`](#project-notes) | Notes attached to projects | | [`time_entries`](#time-entries) | Logged time entries | | [`work_plans`](#work-plans) | Resource plans / schedule bars | *** ## Core Entities ### Members Team members represent the people in your organization. **View:** `members` | Column | Type | FK | 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 | FK | 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 | FK | 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 | FK | 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 > **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 ```sql 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 ```sql 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 ```sql 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; ```