RPC functions are called via
supabase.rpc('function_name', params) and respect RLS policies. Most functions use SECURITY INVOKER (runs as the calling user), while a few admin functions use SECURITY DEFINER (runs with elevated privileges).Case management
| Function | Description |
|---|---|
create_case_with_milestones(...) | Creates a case, resolves the milestone template via 3-tier cascade, pre-populates milestone rows |
finalize_draft_case(...) | Converts a draft case to scheduled status with validation |
get_surgeon_day_overview(surgeon_id, facility_id, date) | Returns full day summary as JSON |
Analytics
| Function | Description |
|---|---|
get_milestone_interval_medians(surgeon_id, procedure_type_id, facility_id) | Surgeon vs. facility median times per milestone pair with template cascade |
get_surgeon_median_times(surgeon_id) | Median surgical/total minutes per procedure |
get_phase_medians(facility_id, date_range, procedure_id?, surgeon_id?, template_id) | Phase duration medians with template-aware grouping |
Template resolution
| Function | Description |
|---|---|
resolve_template_phase_boundaries(template_id) | Derives phase boundaries from milestone_template_items |
Scoring and flags
| Function | Description |
|---|---|
record_case_stats() | Trigger that populates case_completion_stats (41 columns) on validation |
seed_facility_flag_rules(facility_id) | Copies global flag rule templates to a facility |
Facility provisioning
| Function | Description |
|---|---|
seed_facility_with_templates(facility_id, config_jsonb) | Seeds a new facility with templates — 13+ template categories via JSONB config |
Cross-case analysis
| Function | Description |
|---|---|
get_previous_case_in_room(or_room_id, case_date, patient_in_time) | Prior case in same room (for turnover) |
get_previous_case_for_surgeon(surgeon_id, case_date, incision_time) | Prior case for surgeon (for flip-room) |
get_facility_median_turnover(facility_id) | Facility-wide median turnover time |
get_facility_median_block_time(facility_id) | Facility-wide median block time |
Auth helpers
| Function | Description |
|---|---|
get_current_user_facility() | Returns facility_id for the authenticated user |
get_my_access_level() | Returns access_level for the authenticated user |
is_facility_accessible(facility_id) | Checks if user can access a facility |
is_global_admin() | Returns true if user has global admin privileges |
Introspection
| Function | Description |
|---|---|
introspect_columns(table_name) | Column metadata for a table |
introspect_triggers(table_name) | Trigger metadata for a table |
introspect_foreign_keys(table_name) | Foreign key metadata for a table |
Calling RPCs from TypeScript
Common gotchas
RPC returns null instead of an error
RPC returns null instead of an error
Many RPC functions return
null for invalid inputs rather than raising errors. Always check for null returns in your TypeScript code and handle them gracefully.get_milestone_interval_medians returns different results
get_milestone_interval_medians returns different results
This function implements the 3-tier cascade internally. If you pass a
surgeon_id, it will use the surgeon’s override template. If not, it falls back to procedure template, then facility default. The template used affects which milestones are included in the median calculation.seed_facility_with_templates is slow
seed_facility_with_templates is slow
This function copies data across 13+ template categories in a single transaction. For large template sets, it can take several seconds. It’s designed to run once during facility provisioning, not repeatedly.