Table hierarchy
facility_milestone_id is the foreign key everywhere in case_milestones. Never use milestone_type_id directly.
Cascade resolution
When creating a case, the system resolves which template to use via a 3-tier cascade:- Surgeon override — check
surgeon_template_overridesfor this surgeon + procedure - Procedure template — check
procedure_types.milestone_template_id - Facility default — use the
milestone_templatesrecord whereis_default = true
cases.milestone_template_id), so subsequent template changes don’t affect existing cases.
Key function: resolveTemplateForCase
Located inlib/dal/phase-resolver.ts, this TypeScript function implements the cascade:
create_case_with_milestones() and get_milestone_interval_medians().
Required milestones
Every new template must include 4 phases and 7 unique milestones (8 placements including shared boundaries):| Phase | Required milestones |
|---|---|
| Pre-Op | patient_in |
| Surgical | prep_drape_start, prep_drape_complete, incision |
| Closing | closing (shared), closing_complete (shared) |
| Post-Op | patient_out |
lib/template-defaults.ts and enforced in both useTemplateBuilder and useAdminTemplateBuilder hooks.
Grandfather logic: enforcement only applies to templates containing all 8 required milestones. Legacy templates are not retroactively enforced.
Phase boundary resolution
Phase boundaries are derived from template items, not stored separately. The SQL functionresolve_template_phase_boundaries() reads milestone_template_items and returns phase boundaries.
The TypeScript adapter resolvePhaseDefsFromTemplate() returns PhaseDefinitionWithMilestones[], preserving the interface expected by analytics components.
Key functions in lib/dal/phase-resolver.ts
| Function | Description |
|---|---|
resolvePhaseDefsFromTemplate() | Returns phase definitions with milestone arrays |
resolveDefaultPhaseDefsForFacility() | Resolves the facility’s default template |
resolveTemplateForCase() | Implements the 3-tier cascade |
Case creation flow
Whencreate_case_with_milestones() is called:
- Resolve the template via the cascade
- Stamp
milestone_template_idon the case - Read
milestone_template_itemsfor the resolved template - Create one
case_milestonesrow per item withrecorded_at = NULL - Set case status to Scheduled
Analytics integration
- Surgeon analytics — phase breakdowns use each case’s stamped template
- Milestone comparison —
get_milestone_interval_medians()uses the 3-tier cascade for baselines - Phase medians —
get_phase_medians()accepts amilestone_template_idparameter
The analytics pipeline uses the per-case stamped template for phase boundary resolution. This means even if a template is later modified, historical cases keep their original phase boundaries, ensuring analytics remain consistent over time.
Common pitfalls
Using milestone_type_id in queries
Using milestone_type_id in queries
This is the most common mistake. Always join through
facility_milestones to get global type information: case_milestones → facility_milestones.source_milestone_type_id → milestone_types.Forgetting the grandfather rule
Forgetting the grandfather rule
Required milestone enforcement only applies to templates that already contain all 8 required milestones. Legacy templates with fewer milestones are exempt. This prevents breaking existing workflows when the required set was introduced.
Assuming phase boundaries are stored separately
Assuming phase boundaries are stored separately
Phase boundaries are derived from
milestone_template_items, not stored in a separate phase_definitions table (which was removed). Use resolve_template_phase_boundaries() to get boundaries.