# Feature COSTS-198 - Store a (mostly) immutable "Initial Work" field on the Work Package that stores the first value of Work to quickly see diversions

Project: Stream Time & Costs
Status: new
URL: https://community.openproject.org/work_packages/COSTS-198

## Planning

| Attribute | Value |
| --- | --- |
| Date |  |
| Priority | Medium |
| Target versions |  |
| Votes |  |
| List |  |
| Roadmap |  |
| Relevance |  |

## OPEN POINTS / FEEDBACK

_No work packages_

## Use cases

_No work packages_

## Features and Chunks

_No work packages_

## Bugs and other work packages

_No work packages_

## Technical Details

| Attribute | Value |
| --- | --- |
| Module |  |
| Feature Flag |  |
| Requires doc change | No |
| Enterprise plan |  |

## Details

| Attribute | Value |
| --- | --- |
| Pull preview |  |
| Mockups |  |
| Build Number |  |
| Found in mobile app version |  |
| Follow-up feedback e-mail |  |
| Accessibility | No |

## People

| Attribute | Value |
| --- | --- |
| Assignee |  |
| Accountable |  |
| Product manager |  |
| Feature Lead |  |
| Developers |  |
| Designer |  |
| Ordered by |  |
| Signed off by |  |
| Interest by |  |

## Estimates and progress

| Attribute | Value |
| --- | --- |
| PD (min) |  |
| PD (max) |  |
| Work |  |
| Labor costs |  |

## Description

**As** a project manager or controller  
**I want to** have an &quot;Initial work&quot; value on each work package that is recorded once, when the work is first estimated, and can&#x27;t be changed afterwards  
**so that** I can always compare the current Work and Remaining work against the original estimate, and see how far estimates have drifted over the life of a work package, a hierarchy and a project

## **Acceptance criteria**

### _Initial work_

*   There is a new built-in work package attribute **Initial work** (hours, same format and duration display as Work).
    
*   The first time Work is set to a value (when the work package is created or in a later update), Initial work is set to that same value. A value of 0h counts as set.
    
*   After that, Initial work doesn&#x27;t change automatically:
    
    *   Changing, clearing or resetting Work doesn&#x27;t change it.
        
    *   Switching between work-based and status-based progress calculation doesn&#x27;t change it.
        
*   Initial work stays empty while Work has never been set.
    
*   When Initial work is set, a journal entry is written, so it shows in the activity tab (&quot;Initial work set to 8h&quot;).
    
*   A copied work package (single copy, bulk copy, project copy, template instantiation) sets initial work to the copied work, not the sources initial work.
    

### _Overwriting Initial work_

_Reasoning here: Mistakes will be made. If a user enters 8h of work, but meant to enter 80h, the immediate edit would keep initial work at 8h. There needs to be some way to correct this. Alternative would be, that the first version is only stored when the journal aggregation time is over, so edits within 5m or so of creation of the WP still allow modifying it._

*   There is a new project permission **Edit initial work**.
    
*   Users with this permission can change Initial work to a different value in the work package details, the table, the progress modal and through API v3.
    
*   Once set, Initial work can&#x27;t be cleared, even by users with this permission.
    
*   Every overwrite is journaled with the old and new value, so corrections show in the activity tab.
    
*   Users without the permission see Initial work as read-only everywhere. API writes from them fail with a permission error.
    

### _Total initial work_

*   There is a derived attribute **Total initial work** on work packages that have children. It is the sum of the work package&#x27;s own Initial work and that of all its descendants. It follows the same rules as Total work: work packages whose status is &quot;excluded from totals&quot; don&#x27;t count, and it is empty when no work package in the hierarchy has Initial work.
    
*   Total initial work is recalculated when the hierarchy changes:
    
    *   children are added, removed or moved
        
    *   a child&#x27;s status changes to or from one that is excluded from totals
        
    *   a child&#x27;s Initial work is overwritten
        
    *   the &quot;excluded from totals&quot; setting of a status changes
        
*   Total initial work is shown the same way Total work is shown next to Work.
    

### _Visibility in the product_

*   Initial work and Total initial work can be:
    
    *   shown as columns in the work package table, with sums in the table footer
        
    *   shown in the &quot;Estimates and progress&quot; section of the work package details
        
    *   used to filter and sort work package queries
        
    *   included in exports (CSV, XLS, PDF)
        
*   Baseline comparisons show Initial work and Total initial work like the other progress values.
    

### _Existing data_

*   After the upgrade, existing work packages get their Initial work from the first value of Work recorded in their history (see technical notes). Work packages whose Work was never set stay empty.
    
*   The backfill does not create activity entries.
    

## **Technical notes**

### _Data model_

*   New columns `initial_estimated_hours` and `derived_initial_estimated_hours` on `work_packages` and `work_package_journals`. They are journaled like `estimated_hours` / `derived_estimated_hours`.
    
*   Initial work is captured when `estimated_hours` goes from empty to a value, inside the same save, so every path that sets _Work_ captures it too: services, bulk edit, imports (e.g. Jira importer), copy.
    
*   Contracts: `initial_estimated_hours` is writable only with `edit_initial_work`, and only on work packages where it is already set. Changing it to nil is invalid. When the work package is created or Work is set for the first time, the value comes from Work, so the user can&#x27;t supply it.
    
*   `WorkPackages::UpdateAncestorsService` derives `derived_initial_estimated_hours` with the same rules as `derived_estimated_hours` (`included_in_totals_calculation?`, nil when there are no children or no values). `initial_estimated_hours` triggers re-derivation.
    
*   `WorkPackages::Progress::ApplyStatusesChangeJob` and its SQL commands also recalculate the new total when the &quot;excluded from totals&quot; setting of a status changes.
    
*   API v3: `initialWork` and `derivedInitialWork` (ISO 8601 durations). `initialWork` is writable according to the permission. `derivedInitialWork` is always read-only.
    

### _Backfill_

*   The migration only adds the columns and enqueues a backfill job. It does no data work itself.
    
*   The job subclasses `WorkPackages::Progress::Job`. That keeps it serialized with the other progress jobs, so it can&#x27;t race a status or progress-mode change. It runs set-based SQL in batches of work package ids:
    
    1.  **Find the initial value per work package.** Join `journals` and `work_package_journals` and pick the first journal (lowest `version`) with a non-null `estimated_hours` (`DISTINCT ON (journable_id) … ORDER BY journable_id, version`). That journal&#x27;s `estimated_hours` is the initial value and its version is the capture version.
        
    2.  **Fill the work packages.** Set `work_packages.initial_estimated_hours` to the initial value where it is still NULL. That way, a value captured by a regular save while the job runs isn&#x27;t overwritten.
        
    3.  **Rewrite the journal history.** Set `work_package_journals.initial_estimated_hours` to the initial value on every journal of that work package with `version >= capture version`. No new journals are written. Without this, the next normal edit would compare against a NULL in the previous journal and show a misleading &quot;Initial work set to …&quot; entry, possibly years later. It also makes Baseline comparisons against past dates return the right value.
        
    4.  **Derive totals.** For every work package with descendants, compute `derived_initial_estimated_hours` from `work_package_hierarchies` with the same rules as step-by-step derivation (own value plus descendants, skip statuses with `excluded_from_totals`, NULL if no values). Write it to `work_packages` and to the latest journal of each affected work package only. Older journals keep NULL for the total, because rebuilding the hierarchy as it was at each journal version isn&#x27;t feasible.
        
*   Known limitations to accept and document:
    
    *   **Journal aggregation.** Edits made within `journal_aggregation_time_minutes` of creating a work package are merged into the first journal. If Work was changed within that window, the backfilled value is the merged one, not the very first value typed.
        
    *   **Data from before journals existed.** For data migrated from versions before journals were introduced, the first journal is the migration snapshot, so the backfilled value is the Work at that moment.
        
    *   **Cleared and re-set Work.** If Work was set, cleared and set again, the first non-null value wins, which is consistent with the live behavior.
        
*   Performance: on large instances `journals` can have tens of millions of rows. Batch by work package id and use the existing `(journable_type, journable_id, version)` index. Measure on a large staging dataset before release.
    

## **Permissions and visibility considerations**

*   _To whom is this feature visible?_ Initial work and Total initial work are visible to everyone who can view a work package (`view_work_packages`). Overwriting Initial work requires the new project permission **Edit initial work**. It belongs to the work package tracking module, depends on `edit_work_packages`, and is not granted to any default role except Project admin.
    
*   _When is it not visible?_ When the user can&#x27;t see the work package. Like other attributes, the type form configuration decides whether it appears on the work package form. The edit controls are hidden or disabled for users without **Edit initial work**.
    

## **Translation considerations**

*   EN: &quot;Initial work&quot;, &quot;Total initial work&quot;, permission &quot;Edit initial work&quot;
    
*   DE: &quot;Ursprünglicher Aufwand&quot;, &quot;Ursprünglicher Aufwand insgesamt&quot;, permission &quot;Ursprünglichen Aufwand bearbeiten&quot; (matches &quot;Aufwand&quot; / &quot;Verbleibender Aufwand&quot;)
    
*   Activity entries: &quot;Initial work set to {value}&quot;, &quot;Initial work changed from {old} to {new}&quot;
    
*   Help text: &quot;The first Work estimate entered for this work package. Only users with the &#x27;Edit initial work&#x27; permission can change it.&quot;
    

## **Out of scope**

*   Calculated deviation fields (e.g. &quot;Work − Initial work&quot;, &quot;% deviation&quot;) and charts or widgets built on them
    
*   Re-baselining (setting a new Initial work for a whole project at a planning milestone)
    
*   Historic Total initial work for past journal versions (only the current journal gets the total during the backfill)
    

_Set the_ **To be informed/consulted teams** _field to include all teams necessary to be informed of the changes._
