Skip to main content
Case management is handled through a combination of direct Supabase table operations and RPC functions that orchestrate multi-step workflows.
Always use the DAL functions in lib/dal/cases.ts rather than querying the cases table directly. The DAL ensures consistent select clauses, proper filtering, and type safety.

​
Data access layer

All case queries are centralized in lib/dal/cases.ts. This prevents query duplication and ensures consistent patterns.

​
Core functions

​
Listing cases

FunctionDescription
casesDAL.listByDate(supabase, facilityId, date)Cases for a facility on a specific date
casesDAL.listByDateRange(supabase, facilityId, dateRange, pagination?)Cases within a date range
casesDAL.listForCasesPage(...)Full-featured list with tab filtering, sorting, pagination
casesDAL.listForAnalytics(supabase, facilityId, dateRange, surgeonId?)Optimized select for analytics
casesDAL.search(supabase, facilityId, searchTerm, limit)Search by case number

​
Single case

FunctionDescription
casesDAL.getById(supabase, caseId)Full detail with milestones, flags, staff, devices

​
Mutations

FunctionDescription
casesDAL.recordMilestone(supabase, caseId, facilityMilestoneId, timestamp)Record or update a milestone timestamp (upsert)

​
Aggregations

FunctionDescription
casesDAL.countByTab(...)Tab badge counts (all, today, scheduled, in_progress, completed, data_quality)
casesDAL.flagsByCase(supabase, caseIds[])Flag summaries for a batch of cases

​
Type system

​
List view (minimal)

interface CaseListItem {
  id: string
  case_number: string
  scheduled_date: string
  start_time: string | null
  status_id: string | null
  data_validated: boolean
  is_excluded_from_metrics: boolean
  surgeon?: { first_name, last_name }
  or_room?: { name }
  case_status?: { name }
  procedure_type?: { id, name, expected_duration_minutes }
}

​
Detail view (full)

Extends CaseListItem with patient info, case_milestones[], case_flags[], case_staff[], and case_implant_companies[].

​
Analytics view (optimized)

interface CaseForAnalytics {
  id, surgeon_id, procedure_type_id, scheduled_date,
  start_time, or_room_id, patient_in_at, patient_out_at,
  incision_at, prep_drape_complete_at, closing_at
}

​
Key RPCs

FunctionDescription
create_case_with_milestones(...)Creates case + populates milestones from resolved template
finalize_draft_case(...)Converts draft to scheduled status
get_surgeon_day_overview(...)Returns a surgeon’s full day summary as JSON

​
Data fetching pattern

Always use the useSupabaseQuery hook: 
const { data, isLoading, error } = useSupabaseQuery(
  ['cases', facilityId, date],
  async (supabase) => casesDAL.listByDate(supabase, facilityId, date)
)
The cache key array (first argument) determines when the query re-fetches. Include all variables that affect the query result — facilityId, date, surgeonId, etc. When any value changes, the hook automatically re-fetches.

​
Common patterns

Use casesDAL.getById() which includes milestones, flags, staff, and device companies in a single query. The return type is the full CaseDetail interface.
Use casesDAL.recordMilestone() which performs an upsert — it creates the milestone record if it doesn’t exist or updates the timestamp if it does. This prevents duplicate milestone entries.
Use casesDAL.listForAnalytics() which returns a minimal select optimized for chart rendering. It excludes expensive joins (staff, devices) that analytics views don’t need.

​
Next steps

RPC functions

Full RPC reference.

Case management

User-facing case documentation.