Worksheet Build-up

Detailed flow for constructing an Item’s Worksheet — the “show your work” surface where methodology and cost come together. The highest-risk interaction in oxFlow from a UX standpoint.

Primary view: Worksheet Editor (view 6) Related entities: Worksheet · Item · Resource · Recipe · Price Book

1. Preconditions

• An Item exists inside an Estimate (In Progress). Its Worksheet already exists (created atomically with the Item).
• Worksheet status derives from the parent Item; no independent state.
• The Item’s plug_rate and Worksheet cost-contributing children are mutually exclusive.

Two starting conditions:

Starting point	Initial Item status
Empty Worksheet, no plug_rate	Unpriced
Empty Worksheet, plug_rate set	Plugged
Worksheet has cost-contributing content	Priced

---

2. Flow

 ┌────────────────────┐
 │ Open Item in │
 │ Estimate Editor │
 └─────────┬──────────┘
 │
 ▼
 ┌────────────────────┐
 │ Worksheet │
 │ Editor │
 └─────────┬──────────┘
 │
 ┌─────────────┼─────────────────────────────────┐
 ▼ ▼ ▼ ▼
 ┌───────────┐ ┌────────────┐ ┌────────────┐ ┌────────────────┐
 │ Add │ │ Add │ │ Add │ │ Set plug_rate │
 │ Resource │ │ Recipe │ │ Variables/ │ │ on parent Item │
 │ (drag-in) │ │ (drag-in) │ │ Calcs/ │ │ │
 │ │ │ │ │ Content │ │ │
 └───────────┘ └────────────┘ └────────────┘ └────────────────┘
 │ │ │ │
 ▼ ▼ ▼ ▼
 (snapshot (snapshot (children (Item status:
 freezes freezes carry their Unpriced →
 rate, unit, Input Param own ordering) Plugged)
 flags, values)
 modifier
 defaults)
 │ │ │
 └─────────────┴──────────────┘
 │
 ▼
 Item Status auto-derives → Priced when
 cost-contributing children exist
 │
 ▼
 ┌────────────────────┐
 │ Rate edits, │
 │ snapshot sync, │
 │ anomaly handling │
 └─────────┬──────────┘
 │
 ▼
 Item marked Reviewed
 (manual, Lead Estimator)
 │
 ▼
 When every Item is Reviewed,
 Estimate auto-transitions
 In Progress → Reviewed

---

3. Building blocks in the Worksheet

Five child element types, each with its own ordering — the Worksheet itself has no layout-order attribute; children carry their own.

3.1 Variables

Named values usable in Calculation Blocks and Worksheet Resource quantities.

Origin	Where it comes from
Manual	Estimator types it
Input Parameter	Recipe-parent Worksheets only — surfaced from the Recipe’s declared Input Parameters
Program Task	Item-parent Worksheets only — created when an Item is linked to a Program Task; named Dur_{TaskName}
Calculation	Derived from a Calculation Block’s result

Rules:

• Variable names are unique within a Worksheet (for unambiguous expression evaluation).
• Variables may declare a Unit (optional) for clarity and to drive Anomaly Review unit-reconciliation heuristics.

3.2 Calculation Blocks

Named computations. Expressions reference Variables or other Calculation Block results by name.

Rules:

• Well-formedness validated — expressions must reference only existing Variables / Calculation Blocks in the same Worksheet.
• No circular references.

Production Rate pattern: a common pattern — declare a Variable production_rate with an expected value, then a Calculation Block derived_rate = quantity / task_duration. Anomaly Review flags mismatches. Production Rates are expressed as Variables, not a separate entity type.

3.3 Content Blocks (instances)

Free-text sections anchored to Content Block Definitions (admin-managed). Four ship by default:

• Inclusions — what this Item includes
• Exclusions — what this Item explicitly excludes
• Task Breakdown — delivery stages
• Risks — known risks and mitigation notes

Admins can add more Content Block Definitions. Each instance references a Definition and carries estimator-entered text.

3.4 Worksheet Resources

Snapshot usages of Resources from Price Books. Resource = menu item, Worksheet Resource = a line on your order.

On drag-in: the system freezes a snapshot of the Resource’s:

• rate
• unit
• flag instances (from Flag Definitions applied to the Resource)
• modifier default values (from Modifier Definitions applied to the Resource)

Additional per-instance attributes are estimator-controlled:

• quantity — number or expression referencing Variables
• modifier_values — override per-instance from the snapshot defaults
• wastage_factor — optional
• notes — optional
• ordering within the Worksheet

Modifier math: applied in deterministic order:

1. quantity_multiplier — scales quantity
2. rate_adder — adds to per-unit rate
3. (qty × rate) — base subtotal
4. lump_sum_add — flat addition
5. total_multiplier — final scaler

Snapshot semantics: Worksheet Resources do not auto-sync when the underlying Resource changes. Divergence is surfaced by Anomaly Review; the estimator explicitly chooses whether to push updated rate / flags / modifier defaults through (preserving their per-instance quantity, wastage, and modifier overrides).

Rate-edit actions: three options — per-instance override, “Apply to this Estimate”, “Fork to new Resource”. See rate-edit-mechanics.md.

3.5 Worksheet Recipes

Snapshot usages of Recipes from the Recipe Library.

On drag-in:

• System captures a snapshot reference to the Recipe at drag-in time.
• System prompts for Input Parameter values — one per declared Input Parameter. Each value can be a constant or an expression referencing the host Worksheet’s Variables.
• Recipe’s Worksheet is evaluated with the supplied Input Parameter values to produce a cost per Output Unit.
• The Worksheet Recipe contributes: (recipe cost-per-Output-Unit) × (quantity in Output Units) to the host Item’s total cost.

Nesting cap (Recipe spec): Recipes can nest up to 3 levels (Recipe within Recipe within Recipe).

Snapshot semantics: same principle as Worksheet Resources. If the underlying Recipe changes (new Resources added, rate updates, Input Parameter changes), the Worksheet Recipe holds its snapshot. Anomaly Review surfaces divergence.

---

4. The build-up sequence

Typical happy-path sequence for a non-trivial Item:

1. Open the Worksheet Editor from the Estimate Editor by clicking the Item.
2. Declare Variables — manual values the build-up will use (e.g., production rate, labour hours, crew size). Attach Units where relevant.
3. If Program-linked: link Program Tasks to the Item (from the Item sidebar). Each link adds a Dur_{TaskName} Variable automatically.
4. Add Calculation Blocks for derived quantities (e.g., total_hours = production_rate × quantity).
5. Drag Resources from the Resource picker (opens as a side panel listing Price Books → Resources). Snapshot freezes at drag-in.
6. Set Worksheet Resource quantities — literals or expressions referencing Variables and Calculation Blocks.
7. Adjust modifier values per instance if needed (override).
8. Drag Recipes for reusable methodologies. Fill Input Parameter values (expressions allowed).
9. Fill Content Blocks — Inclusions, Exclusions, Task Breakdown, Risks (and any custom blocks).
10. Sanity check against derived total_cost shown in the right rail.
11. Leave the Worksheet. Item status has auto-derived to Priced.

Not every Item needs every step. Simple Items may be a single Worksheet Resource; complex ones may be dozens of Variables and Calculation Blocks feeding nested Recipes.

---

5. Plug rate shortcut

For rough/preliminary Items, the estimator can skip the build-up and enter a direct rate on the parent Item.

Mechanics:

• Set plug_rate on the Item (Estimate Editor or Worksheet right rail).
• Worksheet remains empty.
• Item status derives to Plugged.
• plug_rate × quantity contributes to Estimate totals.

Mutual exclusion: adding the first cost-contributing child to the Worksheet auto-clears plug_rate and transitions the Item to Priced. The Worksheet and plug_rate cannot coexist.

Submit gate: an Estimate cannot be submitted while any Item is Plugged. Plug rates must either be built up or be converted to Priced before publish.

---

6. Snapshot divergence and push-through

Most Worksheet Resources eventually carry snapshots that diverge from their Resource source — because a Price Book Adjudication updated the rate, or because a per-Estimate bulk override was applied, or because the Resource was otherwise edited.

Detection: Anomaly Review compares each Worksheet Resource’s snapshot against the current Resource state on every run.

Surface in UI:

• Divergence indicator on the Worksheet Resource row (badge / icon)
• Banner at top of Worksheet when divergences exist
• Entry on the Anomaly Dashboard (Layer 1)

Resolution actions (per Worksheet Resource):

• Push through — accept the new Resource rate / unit / flags / modifier defaults. Preserves quantity, wastage, and per-instance modifier overrides.
• Keep snapshot — explicit “this snapshot is intentional” — no action; flag persists but acknowledged.
• Jump to Resource — navigate to the Price Book Editor for context.

Cascade: pushing through is effectively a rate change; affected Items drop from Reviewed back to Priced and must be re-reviewed.

---

7. Recipe-parent Worksheets (variant)

When the Worksheet Editor is opened from the Recipe Builder (view 17), it behaves the same but with three differences:

• The parent is a Recipe, not an Item (XOR enforced).
• Variables with origin Input Parameter are present (one per Recipe-declared Input Parameter).
• Variables with origin Program Task are not allowed (Recipes are Tender-independent).
• No plug_rate concept (Recipes don’t have Item status).

Output is a cost-per-Output-Unit rate that the Recipe declares via its output_unit attribute.

---

8. Validation & invariants

Enforced continuously during editing:

1. Parent exactly one type (Item XOR Recipe).
2. Variable names unique within a Worksheet.
3. No circular references between Variables / Calculation Blocks.
4. Expressions reference only existing Variables / Calculation Blocks in the same Worksheet.
5. Child entities (Variable, Calculation Block, Content Block instance, Worksheet Resource, Worksheet Recipe) belong to exactly one Worksheet.
6. Item-parent Worksheets cannot have Input Parameter-origin Variables.
7. Recipe-parent Worksheets must have ≥1 Input Parameter-origin Variable (enforced via Recipe’s declared Input Parameters).
8. Modifier override values match declared value units.
9. Modifier overrides only for modifiers in scope of the parent Resource’s Type.
10. Snapshot rate is always a valid number ≥ 0.

---

9. Collaboration

Per-Item lock applies to the entire Worksheet. While User A edits any part of the Worksheet:

• Other Users see the parent Item as locked in the Estimate Editor.
• If another User navigates directly to the Worksheet URL, they see a read-only view with “currently edited by [User A]“.
• Lock releases on exit, idle timeout (default 10 min), or Admin override.

---

10. Worked example

Earthwork excavation with Program-linked Production Rate:

Item: "Earthwork excavation" (Unit: m³, Quantity: 1000, linked to Program Task "Excavation Phase" dur=10d)

Worksheet:
 Variables
 task_duration origin=Program Task value=10 days
 production_rate origin=Manual value=100 m³/day
 derived_duration origin=Calculation value = quantity / production_rate = 10 days

 Calculation Blocks
 crew_cost = production_rate × 80 $/m³ = $8,000/day

 Worksheet Resources
 Excavation crew (daily) quantity=derived_duration (=10) snapshot_rate=$8,000/day

Anomaly Review:
 Declared Production Rate: 100 m³/day
 Implied from task + quantity: 1000 / 10 = 100 m³/day ✓ match → no flag

Derived:
 total_cost = 10 × $8,000 = $80,000

Item status: Priced (cost-contributing content exists)