api_project_assignments_facts
Description
The api_project_assignments_facts table exposes time-series data about project assignments, including assigned and
registered hours and values per date/month. The assigned hours are derived from spreading the assignment's total or
weekly hours across the assignment's date range.
Each row represents the data for a single assignment on a single date. The distinction between direct and indirect is based on the project's billable setting:
- Direct assigned hours/value — hours from assignments on billable projects.
- Indirect assigned hours/value — hours from assignments on non-billable projects.
Use the project_assignment_id to join with api_project_assignments for assignment details.
Facts are available for a rolling window from 6 months in the past to 2 years in the future, calculated from the first day of the current month. The following assignments are excluded:
- Assignments without a start date or end date.
- Assignments with an end date more than 6 months in the past.
There is no filtering on project status (e.g. active/archived) or assignment status (e.g. done/blocked).
Fields
| Field Name | Type | Description |
|---|---|---|
project_assignments_fact_id | Integer | Unique identifier for the fact |
project_assignment_id | Integer | Identifier of the project assignment |
date | Date | Date of the fact |
month | Date | Month of the fact |
direct_assigned_hours | Numeric | Assigned hours on billable projects for this date |
direct_assigned_value | Numeric | Monetary value of assigned hours on billable projects for this date |
indirect_assigned_hours | Numeric | Assigned hours on non-billable projects for this date |
indirect_assigned_value | Numeric | Monetary value of assigned hours on non-billable projects for this date |
registered_hours | Numeric | Total hours registered (direct + indirect) for this date |
registered_value | Numeric | Monetary value of direct hours registered for this date |
total_assigned_hours | Numeric | Total hours assigned (direct + indirect) for this date |
total_assigned_value | Numeric | Total monetary value assigned (direct + indirect) for this date |
registered_vs_assigned_hours | Numeric | The difference between the registered hours and the assigned hours for this date |
pct_registered_vs_assigned_hours | Numeric | The percentage of the registered hours compared to the assigned hours for this date |
tariff | Numeric | Average tariff calculated as total value divided by total hours for this date |
Related Tables
api_project_assignments- Assignment details viaproject_assignment_id