Worksheet — Entity Spec

1. Purpose

A Worksheet is the methodology surface — the “show your work” layer of oxFlow where estimators build up costs, declare variables, and make calculations transparent. Every Item and Recipe has exactly one Worksheet (BR-005, BR-006). It’s the bridge between scope and cost, linking inputs (Resources, Recipes, Program Task durations) to the estimate total.

A Worksheet is ownership-determined: Item-owned Worksheets contribute cost to the Item total; Recipe-owned Worksheets produce a cost-per-Output-Unit, evaluated with supplied Input Parameter values.

Empty Worksheet state: An empty Worksheet (containing no Resources, Recipes, or cost-contributing sub-Items) is a valid and meaningful state. It directly drives the parent Item’s Status derivation (see §2 Item Status Derivation below).

Analogy (glossary cooking model): the Worksheet is showing your work — the list of ingredients, the prep steps, the timing notes that explain how the cake got built.

2. Attributes

Attribute	Type	Required	Default	Notes
`id`	UUID	✅	generated	System-managed
`parent_type`	enum	✅	—	`Item` OR `Recipe` (exactly one, enforced by BR-007)
`parent_id`	UUID	✅	—	Points at Item or Recipe depending on `parent_type`
`title`	string	✅	derived	Derived from parent Item description or Recipe name; not independently editable
`notes`	long text	❌	null	Worksheet-level notes — free text
`created_at` / `updated_at`	timestamp	✅	system	Audit
`created_by` / `updated_by`	User ref	✅	system	Audit

Design note: Worksheet has minimal direct attributes. The richness lives in its children (Variables, Calculation Blocks, Content Block instances, Worksheet Resources, Worksheet Recipes).

1a. Item Status Derivation

The parent Item’s Status (Unpriced / Plugged / Priced) is derived automatically from Worksheet content. See Item entity spec §5 for the derivation table.

Key patterns (BR-019a):

Empty Worksheet + no plug_rate → Item Status = Unpriced
Empty Worksheet + plug_rate set → Item Status = Plugged
Worksheet has cost-contributing Resources/Recipes → Item Status = Priced (plug_rate is cleared — BR-019b enforces mutual exclusion)

plug_rate / Worksheet cost mutual exclusion (BR-019b): An Item cannot simultaneously have plug_rate set AND Worksheet cost-contributing children. Adding the first Resource or Recipe to a previously-empty Worksheet auto-clears the parent Item’s plug_rate (and the Status auto-transitions from Unpriced/Plugged to Priced, via BR-019a).

3. Child element types

A Worksheet contains five kinds of ordered child elements:

3.1 Variables (🟡 Working)

Named values usable in calculations. A Variable’s origin is internal metadata but transparent to the estimator.

Origin types:

Manual — entered directly by the estimator
Input Parameter — reference to a Recipe Input Parameter (Recipe-parent Worksheets only)
Program Task — duration of a linked Tender Program task (Item-parent Worksheets only)
Calculation — derived from a Calculation Block result

Attributes: name, value / expression, unit (optional), origin (internal), ordering

Key invariant: Variable names unique within a Worksheet.

3.2 Calculation Blocks (🟡 Working)

Named computations producing numeric results. A Calculation Block takes Variables and literal values, evaluates an expression, and produces a value available to other Calculations or Resource quantities.

Attributes: name, expression (formula), resulting value, ordering

Key feature: expressions can reference Variables and other Calculation Block results by name.

3.3 Content Block instances (🟢)

Labeled free-text sections for structured input. Each instance references a Content Block Definition (admin-managed).

Four Content Blocks ship by default (BR-130):

Inclusions — what this Item includes (scope inclusions)
Exclusions — what this Item explicitly excludes (scope boundaries)
Task Breakdown — key tasks or stages in delivery
Risks — known risks or risk mitigation notes

Admins can define additional Content Block Definitions (each with Name + Explainer guidance).

Attributes: text content, reference to Content Block Definition, ordering

3.4 Worksheet Resources (🟡 Working)

Snapshot-usage joins linking Resources into this Worksheet. Think: Resource = menu item, Worksheet Resource = a line on your order.

Attributes:

resource_id (ref to Resource)
quantity (value or expression referencing Variables)
snapshot_rate (frozen at drag-in time, per BR-040)
snapshot_unit (frozen at drag-in time, per BR-040)
snapshot_flags (frozen at drag-in time, per BR-040)
snapshot_modifier_defaults (Map<ModifierDefinition, numeric value>; frozen at drag-in per BR-040)
modifier_values (Map<ModifierDefinition, numeric value>; inherited from Resource, editable per-instance per BR-028)
wastage_factor (optional, estimator-controlled)
notes (optional)
ordering (within the Worksheet)

Snapshot semantics (critical, BR-040/042/043): Worksheet Resources do NOT auto-sync with the underlying Resource. If the Price Book rate changes, the snapshot stays frozen. Divergence is flagged by Anomaly Review; estimator explicitly “pushes through” to accept new values (preserving quantity and wastage). This ensures estimate stability — silent rate changes would compromise traceability.

Modifier override mechanic (BR-028): Estimators can override modifier values per instance without affecting the Resource catalog. Example: default wastage 5% on Material Resource; in this confined-space install, override to 8% locally only.

3.4a Rate-edit mechanics

Three distinct actions are available when an estimator edits the rate on a Worksheet Resource (BR-047/048/049/049a):

Action	Scope	Creates new Resource?	Cascade effect
Per-instance override (BR-047) — Default, implicit	This Worksheet Resource only; other uses of same Resource untouched	No	None; divergence flagged by Anomaly Review
”Apply to this Estimate” (BR-048) — Explicit menu option	All Worksheet Resources in current Estimate referencing same underlying Resource	No	Triggers BR-049a: affected Items cascade from Reviewed → Priced
”Fork to new Resource” (BR-049) — Explicit menu option	Creates new Resource in Estimate’s Project-Specific Price Book (auto-created if needed); current Worksheet Resource points to new Resource; other uses unchanged	Yes	Triggers BR-049a: affected Items cascade from Reviewed → Priced

BR-049a cascade: Any rate change via any of the three mechanisms automatically cascades affected Items from Reviewed back to Priced (senior review must be re-done).

Anomaly Review checks: See §6 for bulk-override inconsistency detection (when bulk override has been applied but new drag-ins of same Resource in same Estimate use original Price Book rate).

3.5 Worksheet Recipes (🟡 Working)

Snapshot-usage joins linking Recipes into this Worksheet, with snapshotted Input Parameter values.

Attributes:

recipe_id (ref to Recipe)
input_parameter_values (key → value map; values can be expressions referencing Variables)
snapshot_recipe_version_ref (frozen at drag-in time)
notes (optional)
ordering (within the Worksheet)

Snapshot semantics (BR-041/042): same principle as Worksheet Resource. If the underlying Recipe changes (new Resources added, rate updates), the snapshot keeps its behaviour. Anomaly Review flags divergence; estimator chooses whether to pull the update through.

4. Lifecycle States

A Worksheet has no independent state machine. Its state is inherited from its parent:

Item-parent Worksheet → inherits Item’s state (Priced, Reviewed, Locked)
Recipe-parent Worksheet → Recipes have no state in v1 (immutable once in library; edits create new versions under discussion)

A Worksheet’s state is informational only — state transitions are enforced at the parent level.

5. Relationships

Inbound (things referring to Worksheet)

From	Cardinality	Notes
Item	Item `1:1` Worksheet	Every Item has exactly one (BR-005)
Recipe	Recipe `1:1` Worksheet	Every Recipe has exactly one (BR-006)
Variable	Variable `M:1` Worksheet	Via parent link
Calculation Block	CalcBlock `M:1` Worksheet	Via parent link
Content Block instance	ContentBlock `M:1` Worksheet	Via parent link
Worksheet Resource	WR `M:1` Worksheet	Via parent link
Worksheet Recipe	WRec `M:1` Worksheet	Via parent link

Outbound (things Worksheet references)

To	Cardinality	Required	Notes
Item	Worksheet `M:0..1` Item	conditional	Exactly one Item OR Recipe parent (XOR)
Recipe	Worksheet `M:0..1` Recipe	conditional	Exactly one Item OR Recipe parent (XOR)
Variable	Worksheet `1:M` Variable	❌	Optional; owned by this Worksheet
Calculation Block	Worksheet `1:M` CalcBlock	❌	Optional; owned by this Worksheet
Content Block instance	Worksheet `1:M` ContentBlock	❌	Optional; owned by this Worksheet
Worksheet Resource	Worksheet `1:M` WR	❌	Optional; owned by this Worksheet
Worksheet Recipe	Worksheet `1:M` WRec	❌	Optional; owned by this Worksheet

6. Validation / Invariants

Rules that must hold at all times:

Parent exactly one type. parent_type must be Item or Recipe, never both, never null.
Unique parent ownership. Worksheet 1:1 with exactly one Item OR exactly one Recipe (XOR enforced).
Variable names unique. Within a Worksheet, Variable names must be unique (for unambiguous expression evaluation).
No circular references. Variables and Calculation Blocks cannot reference each other circularly.
Expression well-formedness. Calculation Block expressions and Variable expressions must reference only existing Variables or Calculation Blocks in the same Worksheet.
Child entity ownership. Every Variable, Calculation Block, Content Block instance, Worksheet Resource, and Worksheet Recipe belongs to exactly one Worksheet (no orphans).
Recipe-parent Input Parameters. If parent_type = Recipe, the Worksheet must contain Variables whose origin is Input Parameter (though the definition enforces ≥1 at Recipe level, not Worksheet level per BR-030).
Item-parent no Input Parameters. If parent_type = Item, Variables’ origin must NOT be Input Parameter (Input Parameters only exist in Recipe Worksheets).
Snapshot pointer validity. Worksheet Resources point to valid Resources in Price Books; Worksheet Recipes point to valid Recipes in the Recipe Library. Orphaned references are flagged by Anomaly Review.
Cascade delete. When an Item or Recipe is deleted, its Worksheet and all children are deleted (atomically).
Modifier value unit consistency. Override modifier values must match the modifier’s declared value unit (numeric in expected unit, per BR-028).
Modifier scope constraint. Only modifiers defined in the parent Resource’s Type can have override values in a Worksheet Resource (per BR-028).
snapshot_rate validity. Snapshot_rate must always be a valid number ≥ 0.

7. Derived / Computed Attributes

Attribute	Derivation	Notes
`total_cost` (Item Worksheet)	Sum of: (Worksheet Resources with modifier math applied, per BR-029) + sum of sub-Items recursive + sum of Worksheet Recipes evaluated (each Recipe’s Worksheet computed with supplied input-parameter values)	Recomputed when any child changes
`cost_per_output_unit` (Recipe Worksheet)	Recipe’s Worksheet total cost ÷ quantity of Output Units. Evaluated each time the Recipe is used with supplied Input Parameters.	Recomputed each evaluation
`has_snapshot_divergences`	True if any Worksheet Resource or Recipe snapshot differs from current state (Anomaly Review check)	Boolean badge for UI
`variable_names`	Set of all Variable names in scope (for expression validation and autocomplete)	Computed; used by editor

Worksheet Resource cost calculation (BR-029): When computing the Worksheet Resource’s contribution to Item cost, modifier math is applied in deterministic order: quantity_multiplier → rate_adder → (qty × rate) → lump_sum_add → total_multiplier. See Resource spec §4b for worked example.

8a. Anomaly Review checks

Snapshot divergence (existing):

Worksheet Resource rate differs from current Price Book rate (flagged for estimator action)

Bulk-override inconsistency (v0.4):

Bulk override has been applied (BR-048) to set all Worksheet Resources in Estimate to new rate, but subsequent drag-ins of same Resource snapshot from original Price Book rate. Anomaly surfaces inconsistency; estimator can re-apply bulk override or fork to new Resource.

8. Worked Examples

Example 1 — Item Worksheet with Resources, Variable, and Inclusions

Item: “Concrete supply for bridge pier caps”

Unit: m³
Quantity: 25
Parent Worksheet:

Variables:
  - concrete_rate = 460 $/m³ (manual)
  
Worksheet Resources:
  1. "Concrete — Brown's Supply"
     quantity: 25 m³
     snapshot_rate: $460/m³
     wastage_factor: 5%
     notes: "Price from Brown's 14 Apr quote"
     
Content Blocks:
  - Inclusions: "Ready-mix concrete, 32MPa, delivery"

Derived:

total_cost = 25 × $460 × 1.05 (wastage) = $12,075
has_snapshot_divergences = false

Example 2 — Recipe Worksheet with Input Parameters and Cost-per-Unit

Recipe: “Concrete Pump — 8-hour shift”

Output Unit: day
Parent Worksheet (Recipe-parent):

Variables:
  - concrete_volume = [Input Parameter] m³ (sourced from host Item's Worksheet)
  - num_trips = [Input Parameter] number (sourced from host Item's Worksheet)
  
Calculation Blocks:
  - pump_mobilisation_cost = 2000 × num_trips (each trip costs $2k)
  - labour_cost = 1500 (pump operator, 8 hours)
  
Worksheet Resources:
  1. "Pump rental (daily)" — quantity: 1, snapshot_rate: $800/day

When this Recipe is used in an Item’s Worksheet:

Worksheet Recipes:
  1. "Concrete Pump — 8-hour shift"
     input_parameter_values:
       concrete_volume: 45 (m³, from parent's Variable)
       num_trips: 3
     
     Evaluated cost per output unit = (2000×3) + 1500 + 800 = $8,300/day

Item supplies quantity = 2 days → contribution = 2 × $8,300 = $16,600.

Example 3 — Item Worksheet with Program Task-linked Duration and Production Rate

Item: “Earthwork excavation”

Unit: m³
Quantity: 1000 m³
Linked Program Task: “Excavation Phase” (duration: 10 days)
Parent Worksheet:

Variables:
  - task_duration = [Program Task: Excavation Phase] 10 days (linked)
  - production_rate = 100 m³/day (manual declaration)
  - derived_duration = quantity / production_rate = 1000 / 100 = 10 days
  
Calculation Blocks:
  - crew_cost = production_rate × 80 $/m³ = 100 × 80 = $8,000/day
  
Worksheet Resources:
  1. "Excavation crew (daily)" — quantity: derived_duration (=10), snapshot_rate: $8,000/day

Anomaly Review cross-check:

Declared Production Rate: 100 m³/day
Derived from task + quantity: 1000 m³ ÷ 10 days = 100 m³/day ✓ Match — no divergence flag.