This is the complete data model reference. For focused deep-dives, see the dedicated pages on the Milestone system, Scoring methodology, and Flag engine.
Core table groups
Identity and access
| Table | Description |
|---|---|
facilities | Multi-tenant facility definitions (name, city, state, timezone) |
users | All platform users with facility scoping and access levels |
user_roles | Role assignments (surgeon, anesthesiologist, nurse, tech, admin) |
Cases
| Table | Description |
|---|---|
cases | Surgical cases — the central entity. Has 8 triggers. |
case_milestones | One row per expected milestone per case (FK → facility_milestones) |
case_completion_stats | Denormalized stats cache with 41 columns (timing, financial, sequencing) |
case_flags | Flag instances detected on cases |
case_staff | Staff assignments per case |
case_device_companies | Device company assignments with tray tracking |
case_statuses | Status lookup (Draft, Scheduled, In Progress, etc.) |
Milestone system (v2.0)
| Table | Description |
|---|---|
milestone_types | Global milestone definitions (read-only reference) |
facility_milestones | Per-facility instances (FK → milestone_types via source_milestone_type_id) |
milestone_templates | Template containers for grouping milestones |
milestone_template_items | Individual items within templates (milestone + phase + sequence) |
facility_phases | Phase definitions per facility |
surgeon_template_overrides | Surgeon-specific template assignments |
Analytics
| Table | Description |
|---|---|
surgeon_scorecards | Cached ORbit Score results (refreshed nightly) |
flag_rules | Configurable flag detection rules per facility |
financial_targets | Monthly profit targets per facility |
Materialized views
| View | Description |
|---|---|
surgeon_procedure_stats | Per surgeon, per procedure type aggregation |
facility_procedure_stats | Per facility, per procedure type aggregation |
surgeon_overall_stats | Per surgeon across all procedures |
Scheduling
| Table | Description |
|---|---|
or_rooms | Operating rooms per facility |
block_schedules | Surgeon block time reservations |
facility_closures | Facility closure dates |
facility_holidays | Holiday definitions |
Financial
| Table | Description |
|---|---|
cost_categories | Expense and revenue categories (debit/credit) |
payers | Insurance carriers |
procedure_pricing | Per-procedure cost items and reimbursement rates |
surgeon_cost_overrides | Surgeon-specific cost adjustments |
Patient flow
| Table | Description |
|---|---|
patient_checkins | Check-in records with checklist responses (JSONB) |
patient_statuses | Status lookup for check-in workflow |
escort_status_links | Secure family status tracking links |
Soft deletes
20+ tables use thesync_soft_delete_columns() trigger. The rule: never hard delete — set is_active = false instead. The trigger enforces consistency:
- Setting
deleted_at→ automatically setsis_active = false - Clearing
deleted_at→ automatically setsis_active = true - Setting
is_active = false→ automatically setsdeleted_at = NOW() - Setting
is_active = true→ automatically clearsdeleted_at
body_regions, cancellation_reasons, complexities, cost_categories, delay_types, facility_milestones, implant_companies, milestone_types, or_rooms, patients, payers, preop_checklist_fields, procedure_categories, procedure_types, users, and others.
Trigger system
Thecases table has 8 triggers that handle:
- Status transition validation
- Stats pipeline (
record_case_stats) — populates 41 columns incase_completion_stats - Flag detection invocation
- Materialized view refresh
- Audit logging
Row-level security
Every table has RLS policies enforcing facility-scoped access:- Users can only see data within their assigned facility
- Global admins bypass facility filters via the
is_global_admin()helper - Device reps have restricted access to their assigned cases only
Common patterns
Adding a new table
Adding a new table
Create a migration with the table definition, add RLS policies (use existing tables as reference), and add the
sync_soft_delete_columns() trigger if the table should support soft deletes. Update the TypeScript types in types/.Adding a new RPC function
Adding a new RPC function
Create the function in a migration. Use
SECURITY DEFINER sparingly — prefer SECURITY INVOKER so RLS policies apply. Add TypeScript types for the function parameters and return type.Modifying the cases table
Modifying the cases table
Be extremely careful — the
cases table has 8 triggers that fire on INSERT/UPDATE. Test changes thoroughly, especially anything that modifies the validation flow or status transitions.