Tender Program & Program Task — Entity Spec

---

1. Purpose

A Tender Program is an MS Project file uploaded at the Tender level, capturing the project schedule. A Program Task is a parsed task from that file, carrying timing information (start, end, duration).

When a Program Task is linked to an Item, its duration is exposed as a Variable inside the Item’s Worksheet, enabling Production Rate interactions (BR-046). This bridges schedule and cost: an Excavation Item linked to an “Earthworks” task uses the task’s duration to validate quantity against a declared Production Rate.

---

2. Tender Program — Attributes

Attribute	Type	Required	Default	Notes
id	UUID	✅	generated	System-managed
tender_id	Tender ref	✅	—	FK to Tender; M:1 relationship (BR-100)
file_ref	string	✅	—	File storage/path reference (e.g., S3 key, blob URL)
file_version	string	❌	null	Version/name of uploaded file (e.g., “Schedule_v2_Apr15.mpp”)
uploaded_date	timestamp	✅	system	When file was uploaded
parsed_date	timestamp	❌	null	When tasks were last parsed/extracted from the file
parse_status	enum	✅	pending	pending · success · failed. See §3
parse_error	string	❌	null	If parse failed, error message for UI/anomaly review
task_count	integer	✅	0	Count of Program Tasks extracted (informational)
created_at / updated_at	timestamp	✅	system	Audit
created_by / updated_by	User ref	✅	system	Audit

---

3. Tender Program — Lifecycle

Status	Meaning
pending	File uploaded, awaiting parse
success	Tasks extracted successfully; Program Tasks created/updated
failed	Parse error (corrupt file, unsupported format, etc.); flagged in Anomaly Review

Versioning: Re-uploading a newer .mpp file to the same Tender creates a new Tender Program record (immutable history); the old one remains, but only the latest is active for linking.

Task refresh & re-upload conflict resolution: On re-upload, new Program Tasks are parsed. Reconciliation is task-ID-governed (BR-106): same ms_project_id = same task (updated duration/dates); new ms_project_id = new task, even if name matches (creates new entity; old links persist but flag as orphan); missing ms_project_id = removed task (links flag as orphan). Import Wizard (BR-068b) mediates all re-upload logic.

---

4. Program Task — Attributes

Attribute	Type	Required	Default	Notes
id	UUID	✅	generated	System-managed
tender_program_id	Tender Program ref	✅	—	FK; M:1 relationship
ms_project_id	string	✅	—	Task ID from MS Project file (unique per file; used for reconciliation on re-upload)
task_name	string	✅	—	Task name as parsed from the file
start_date	date	✅	—	Task start (ISO 8601)
end_date	date	✅	—	Task end (ISO 8601)
duration	number (decimal)	✅	—	Duration in working days, adopted as-is from MS Project export (BR-104). No recalculation.
parent_task_id	Program Task ref	❌	null	If this is a subtask, FK to parent; null for top-level tasks
is_summary_task	boolean	✅	false	True if this task has children. When linked to Item, expose only the summary task’s duration; do NOT expose subtask durations as separate Variables (BR-105).
created_at / updated_at	timestamp	✅	system	Audit

---

5. Relationships

Tender Program — Inbound & Outbound

Direction	Entity	Cardinality	Notes
Inbound	Tender	1:0..1	Every Tender has at most one active Tender Program (BR-100); prior versions retained in audit history but only the latest is active
Outbound	Program Task	1:M	One Tender Program contains many Program Tasks (BR-101 via domain model)

Program Task — Inbound & Outbound

Direction	Entity	Cardinality	Notes
Inbound	Tender Program	M:1	Every Program Task belongs to one Tender Program
Inbound	Item	M:M	A Task can be linked by multiple Items; an Item can link multiple Tasks (BR-103)
Outbound	Program Task (parent)	M:0..1	Optional self-referential parent for sub-tasks

Item — Program Task Link

When a Program Task is linked to an Item:

• Effect on Item’s Worksheet: a Variable is created named Dur_{TaskName} (e.g., Dur_Earthworks, Dur_Piling_North) with value = task duration.
• Effect on Worksheet Calculations: the Item’s Worksheet can reference this Variable in Production Rate expressions (e.g., Quantity / Dur_Earthworks = Production Rate).
• M:M semantics: one Item can link many Tasks (aggregated duration); one Task can be linked by many Items.

---

6. Validation / Invariants

1. File upload at Tender level. tender_id is required and non-null; Tender Program does not exist without a Tender.
2. Task ID uniqueness per file. Within a single Tender Program, ms_project_id must be unique.
3. Duration >= 0. Duration must be non-negative.
4. Dates ordered. start_date <= end_date.
5. Parent-child consistency. If parent_task_id is set, the parent must exist in the same Tender Program.
6. No circular parent chains. Subtask hierarchy must be acyclic.
7. Parse status enforcement. Only Program Tasks from a Tender Program with parse_status = success are eligible for linking to Items.
8. Active Program only. At any time, only the latest (by uploaded_date) Tender Program is active for UI operations. Prior versions remain in audit history.

---

7. Anomaly Review Checks

When Tasks are linked to Items or when a Tender Program is re-uploaded:

Check	Trigger	Resolution
Production Rate inconsistency	Item declares a Production Rate (Variable); linked Task duration + Item quantity implies a different rate	Flag: “Production Rate mismatch. Declared rate does not match implied rate from task duration and quantity.” Estimator reviews and confirms or adjusts.
Task linked previously, now missing	On re-upload, a Task ID that was previously linked no longer exists in the parsed file	Flag: “Task [id] linked to Item [description] is orphaned in new program version. Confirm scope or re-link.”
Parse failed	Tender Program parse_status = failed	Flag: “MS Project file could not be parsed. [Error details]. Try re-uploading.”
No Program uploaded	Tender has no Tender Program, but Anomaly Review detects Item with Production Rate Variable	Informational flag: “Item has Production Rate logic but no Tender Program. Consider uploading schedule.”

---

8. Worked Examples

Example 1 — Simple Task-to-Item Link (Excavation)

Tender Program: "Project Schedule v1"
  Program Task: "Earthworks Phase"
    ms_project_id: "TASK-042"
    start_date: 2026-05-01
    end_date: 2026-05-22
    duration: 16 days
    is_summary_task: false

Item: "Excavation (top 0.5m)"
  unit: m³
  quantity: 2,400
  Worksheet:
    Worksheet Resource: Excavation crew @ $350/day × 16 days
    Variable: Dur_Earthworks = 16 (linked to Program Task TASK-042)
    Production Rate Variable: rate_excavation = Quantity / Dur_Earthworks
                           = 2400 / 16 = 150 m³/day
    Declared Production Rate: 140 m³/day
  
Anomaly Review flag:
  ⚠️ "Production Rate mismatch: declared 140 m³/day but implied 150 m³/day from task duration. Confirm."

Example 2 — Supervision Item Linked to Multiple Tasks

Tender Program: "Detailed Schedule"
  Program Task: "Project Start" → duration 1 day
  Program Task: "Excavation Phase" → duration 16 days
  Program Task: "Concrete Phase" → duration 30 days
  Program Task: "Finishing" → duration 14 days

Item: "Contractor Supervision (salary)"
  unit: day
  quantity: null (Rate-Only, or calculated in Worksheet)
  Worksheet:
    Variable: Dur_Excavation = 16 (from Program Task)
    Variable: Dur_Concrete = 30 (from Program Task)
    Variable: Dur_Finishing = 14 (from Program Task)
    Calculation: total_supervision_days = Dur_Excavation + Dur_Concrete + Dur_Finishing = 60
    Worksheet Resource: Supervisor @ $600/day × 60 days = $36,000

Example 3 — Item Linked to Two Parallel Tasks (Piling)

Tender Program: "Construction Schedule"
  Program Task: "Piling (North side)" → duration 18 days
  Program Task: "Piling (South side)" → duration 18 days (runs parallel, same schedule)

Item: "Bored Piles (installation labour)"
  unit: no (number of piles)
  quantity: 240
  Worksheet:
    Variable: Dur_Piling_North = 18
    Variable: Dur_Piling_South = 18
    Variable: total_piling_duration = Dur_Piling_North + Dur_Piling_South = 36 days
    Calculation: piles_per_day = Quantity / total_piling_duration = 240 / 36 = 6.67 piles/day
    Production Rate Variable: (explicit or derived) 6.67 piles/day
    Worksheet Resource: Piling crew (4 personnel) @ $4,200/day × (36 / 4) = price for crew covering both fronts