Hospital Staffing

Version:

Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Healthcare
Tags: AllocationMILPStaffingMulti-objective
Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Healthcare
Tags: AllocationMILPStaffingMulti-objective
Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Healthcare
Tags: StaffingSchedulingHealthcareMulti-Objective

What this template is for

Hospitals must schedule nurses across shifts while keeping overtime costs low and ensuring patients receive care. This template models assigning 6 nurses to 3 shifts (Morning, Afternoon, Night), where each shift has patient demand that depends on staffing levels.

Nurses can work extra shifts beyond their regular hours, but at a premium rate (1.5x). When staffing falls short and patient demand goes unmet, the hospital incurs an overflow penalty reflecting missed care ratios, throughput shortfalls, and regulatory risk. This template uses RelationalAI’s prescriptive reasoner to find the assignment of nurses to shifts that minimizes the combined cost of overtime and overflow, while respecting availability, safety limits, and skill requirements.

Prescriptive reasoning helps you:

Control overtime cost: Identify the lowest-cost overtime assignments when extra coverage is needed.
Avoid costly overflow: Ensure high-demand shifts get additional staff to prevent unmet patient demand.
Quantify trade-offs: Determine whether overtime cost is justified by the overflow penalty it avoids.
Enforce safety limits: Guarantee no nurse works more than 2 shifts (16 hours).

Who this is for

You want an end-to-end example of prescriptive reasoning (optimization) with RelationalAI.
You’re comfortable with basic Python and mixed-integer optimization concepts.

What you’ll build

A semantic model of nurses, shifts, and availability using concepts and properties.
A MILP model with binary assignment variables, continuous overtime hours, patient throughput, and unmet demand variables.
Nine constraints covering availability, coverage, skill requirements, overtime, demand, capacity, and overflow.
An objective that minimizes overtime cost plus an overflow penalty for unserved patients.
A solver that uses the HiGHS backend and prints overtime assignments, patient throughput, and staff schedules.

What’s included

Model + solve script: hospital_staffing.py
Sample data: data/nurses.csv, data/shifts.csv, data/availability.csv

Prerequisites

Access

A Snowflake account that has the RAI Native App installed.
A Snowflake user with permissions to access the RAI Native App.

Tools

Python >= 3.10

Quickstart

Follow these steps to run the template with the included sample data. You can customize the data and model as needed after you have it running end-to-end.

Download the ZIP file for this template and extract it:
Terminal window
```
curl -O https://private.relational.ai/templates/zips/v0.13/hospital_staffing.zip
unzip hospital_staffing.zip
cd hospital_staffing
```
You can also download the template ZIP using the “Download ZIP” button at the top of this page.

Create and activate a virtual environment

python -m venv .venv
source .venv/bin/activate
python -m pip install -U pip

Install dependencies

From this folder:
Terminal window
```
python -m pip install .
```
Configure Snowflake connection and RAI profile
Terminal window
```
rai init
```
Run the template
Terminal window
```
python hospital_staffing.py
```

Expected output

Status: OPTIMAL
Total cost: $616.00

Overtime assignments:
  nurse  overtime_hours
Nurse_B             8.0
Nurse_C             8.0

Patient throughput by shift:
     shift  patients_served  patient_demand  unmet_demand
   Morning             32.0              45          13.0
 Afternoon             60.0              60           0.0
     Night             24.0              25           1.0

Total patients served: 116 / 130
Total unmet demand: 14 patients

Staff assignments:
  nurse      shift
Nurse_A    Morning
Nurse_A  Afternoon
Nurse_B    Morning
Nurse_B  Afternoon
Nurse_C  Afternoon
Nurse_C      Night
Nurse_D    Morning
Nurse_E    Morning
Nurse_E      Night
Nurse_F      Night

Template structure

.
├─ README.md
├─ pyproject.toml
├─ hospital_staffing.py      # main runner / entrypoint
└─ data/                     # sample input data
   ├─ nurses.csv
   ├─ shifts.csv
   └─ availability.csv

Start here: hospital_staffing.py

Sample data

Data files are in data/.

`nurses.csv`

Staff members with skill ratings, pay rates, and overtime parameters.

Column	Meaning
`id`	Unique nurse identifier
`name`	Nurse name
`skill_level`	Skill rating (1=basic, 2=intermediate, 3=advanced)
`hourly_cost`	Cost per hour ($)
`regular_hours`	Standard hours before overtime kicks in (8)
`overtime_multiplier`	Overtime pay rate multiplier (1.5x)

`shifts.csv`

Time periods with staffing requirements and patient demand.

Column	Meaning
`id`	Unique shift identifier
`name`	Shift name (Morning, Afternoon, Night)
`start_hour`	Shift start time (24-hour format)
`duration`	Shift length in hours
`min_nurses`	Minimum nurses required per shift
`min_skill`	Minimum skill level required for at least one nurse
`patient_demand`	Total patients needing care during this shift
`patients_per_nurse_hour`	Patients one nurse can serve per hour

`availability.csv`

Links nurses to shifts, indicating which assignments are feasible.

Column	Meaning
`nurse_id`	Foreign key to `nurses.csv.id`
`shift_id`	Foreign key to `shifts.csv.id`
`available`	1 if the nurse can work this shift, 0 otherwise

Model overview

The semantic model for this template is built around four concepts.

`Nurse`

A staff member with a skill level, hourly cost, and overtime parameters.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/nurses.csv`
`name`	string	No	Human-readable nurse name
`skill_level`	int	No	Used in skill-requirement constraints
`hourly_cost`	float	No	Cost per hour, used in overtime cost calculation
`regular_hours`	int	No	Hours before overtime applies (8)
`overtime_multiplier`	float	No	Overtime pay multiplier (1.5x)
`overtime_hours`	float	No	Continuous decision variable ()

`Shift`

A time period with coverage requirements and patient demand.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/shifts.csv`
`name`	string	No	Human-readable shift name
`start_hour`	int	No	Shift start time (24-hour format)
`duration`	int	No	Shift length in hours
`min_nurses`	int	No	Minimum nurses required
`min_skill`	int	No	Minimum skill level for at least one assigned nurse
`patient_demand`	int	No	Total patients needing care
`patients_per_nurse_hour`	float	No	Patients one nurse can serve per hour
`patients_served`	float	No	Continuous decision variable ()
`unmet_demand`	float	No	Continuous decision variable (); overflow

`Availability`

A nurse-shift pair indicating whether the nurse can work that shift.

Property	Type	Identifying?	Notes
`nurse`	`Nurse`	Part of compound key	Joined via `data/availability.csv.nurse_id`
`shift`	`Shift`	Part of compound key	Joined via `data/availability.csv.shift_id`
`available`	int	No	1 if available, 0 otherwise

`Assignment`

A decision concept created for each Availability row; the solver chooses whether to assign.

Property	Type	Identifying?	Notes
`availability`	`Availability`	Yes	One assignment per nurse-shift pair
`assigned`	float	No	Binary decision variable (0/1)

How it works

This section walks through the highlights in hospital_staffing.py.

Import libraries and configure inputs

First, the script imports the Semantics APIs (Model, data, define, where, require, sum) and configures local inputs like DATA_DIR:

from pathlib import Path

import pandas
from pandas import read_csv

from relationalai.semantics import Model, data, define, require, select, sum, where
from relationalai.semantics.reasoners.optimization import Solver, SolverModel

# --------------------------------------------------
# Configure inputs
# --------------------------------------------------

DATA_DIR = Path(__file__).parent / "data"

# Disable pandas inference of string types. This ensures that string columns
# in the CSVs are loaded as object dtype. This is only required when using
# relationalai versions prior to v1.0.
pandas.options.future.infer_string = False

# --------------------------------------------------
# Define semantic model & load data
# --------------------------------------------------

model = Model("hospital_staffing", config=globals().get("config", None), use_lqp=False)

Define concepts and load CSV data

Next, it declares the Nurse and Shift concepts and loads nurses.csv and shifts.csv via data(...).into(...):

# Nurse concept: staff members with skill level, cost, and overtime parameters.
Nurse = model.Concept("Nurse")
Nurse.id = model.Property("{Nurse} has {id:int}")
Nurse.name = model.Property("{Nurse} has {name:string}")
Nurse.skill_level = model.Property("{Nurse} has {skill_level:int}")
Nurse.hourly_cost = model.Property("{Nurse} has {hourly_cost:float}")
Nurse.regular_hours = model.Property("{Nurse} has {regular_hours:int}")
Nurse.overtime_multiplier = model.Property("{Nurse} has {overtime_multiplier:float}")
Nurse.x_overtime_hours = model.Property("{Nurse} has {overtime_hours:float}")

# Load nurse data from CSV and create Nurse entities.
data(read_csv(DATA_DIR / "nurses.csv")).into(Nurse, keys=["id"])

# Shift concept: time periods with coverage requirements and patient demand.
Shift = model.Concept("Shift")
Shift.id = model.Property("{Shift} has {id:int}")
Shift.name = model.Property("{Shift} has {name:string}")
Shift.start_hour = model.Property("{Shift} has {start_hour:int}")
Shift.duration = model.Property("{Shift} has {duration:int}")
Shift.min_nurses = model.Property("{Shift} has {min_nurses:int}")
Shift.min_skill = model.Property("{Shift} has {min_skill:int}")
Shift.patient_demand = model.Property("{Shift} has {patient_demand:int}")
Shift.patients_per_nurse_hour = model.Property("{Shift} has {patients_per_nurse_hour:float}")
Shift.x_patients_served = model.Property("{Shift} has {patients_served:float}")
Shift.x_unmet_demand = model.Property("{Shift} has {unmet_demand:float}")

# Load shift data from CSV and create Shift entities.
data(read_csv(DATA_DIR / "shifts.csv")).into(Shift, keys=["id"])

availability.csv contains foreign keys (nurse_id, shift_id). The template resolves them into Nurse and Shift instances and creates an Availability concept per row using where(...).define(...):

# Availability concept: links nurses to shifts they can work.
Availability = model.Concept("Availability")
Availability.nurse = model.Property("{Availability} for {nurse:Nurse}")
Availability.shift = model.Property("{Availability} in {shift:Shift}")
Availability.available = model.Property("{Availability} is {available:int}")

# Load availability data from CSV.
avail_data = data(read_csv(DATA_DIR / "availability.csv"))

# Define Availability entities by joining nurse/shift IDs from the CSV with the
# Nurse and Shift concepts.
where(
    Nurse.id == avail_data.nurse_id,
    Shift.id == avail_data.shift_id,
).define(
    Availability.new(nurse=Nurse, shift=Shift, available=avail_data.available)
)

Define decision variables, constraints, and objective

An Assignment decision concept is created for every Availability row. The script then declares four decision variables on a SolverModel: binary assignment flags, continuous overtime hours, patients served per shift, and unmet demand per shift:

# Assignment decision concept: represents the decision variables for assigning
# nurses to shifts. Each Assignment is linked to an Availability entity, which
# indicates whether the nurse can work that shift.
Assignment = model.Concept("Assignment")
Assignment.availability = model.Property("{Assignment} uses {availability:Availability}")
Assignment.x_assigned = model.Property("{Assignment} is {assigned:float}")
define(Assignment.new(availability=Availability))

Asn = Assignment.ref()

s = SolverModel(model, "cont")

# Variable: binary assignment (nurse to shift)
s.solve_for(
    Assignment.x_assigned,
    type="bin",
    name=[
        "x",
        Assignment.availability.nurse.name,
        Assignment.availability.shift.name,
    ],
)

# Variable: overtime hours per nurse (continuous >= 0)
s.solve_for(Nurse.x_overtime_hours, type="cont", name=["ot", Nurse.name], lower=0)

# Variable: patients served per shift (continuous >= 0)
s.solve_for(Shift.x_patients_served, type="cont", name=["pt", Shift.name], lower=0)

# Variable: unmet patient demand per shift (continuous >= 0)
s.solve_for(Shift.x_unmet_demand, type="cont", name=["ud", Shift.name], lower=0)

Then it adds nine constraints using require(...) and s.satisfy(...). The constraints enforce availability, minimum and maximum shifts per nurse, coverage and skill requirements, overtime accounting, demand and capacity caps, and the definition of unmet demand:

# Constraint: can only assign if available
must_be_available = require(Assignment.x_assigned <= Assignment.availability.available)
s.satisfy(must_be_available)

# Constraint: every nurse works at least one shift
nurse_shift_count = sum(Asn.assigned).where(Asn.availability.nurse == Nurse).per(Nurse)
min_one_shift = require(nurse_shift_count >= 1)
s.satisfy(min_one_shift)

# Constraint: max 2 shifts per nurse (safety limit: 16 hours max)
max_two_shifts = require(nurse_shift_count <= 2)
s.satisfy(max_two_shifts)

# Constraint: minimum nurses per shift
shift_staff_count = sum(Asn.assigned).where(Asn.availability.shift == Shift).per(Shift)
min_coverage = require(shift_staff_count >= Shift.min_nurses)
s.satisfy(min_coverage)

# Constraint: at least one nurse with required skill level per shift
skilled_coverage = sum(Asn.assigned).where(
    Asn.availability.shift == Shift,
    Asn.availability.nurse.skill_level >= Shift.min_skill,
).per(Shift)
min_skilled = require(skilled_coverage >= 1)
s.satisfy(min_skilled)

# Constraint: overtime >= total hours worked - regular hours
total_hours_worked = sum(Asn.assigned * Asn.availability.shift.duration).where(
    Asn.availability.nurse == Nurse
).per(Nurse)
overtime_def = require(Nurse.x_overtime_hours >= total_hours_worked - Nurse.regular_hours)
s.satisfy(overtime_def)

# Constraint: patients served <= patient demand per shift
demand_cap = require(Shift.x_patients_served <= Shift.patient_demand)
s.satisfy(demand_cap)

# Constraint: patients served <= nursing capacity per shift
shift_nursing_capacity = shift_staff_count * Shift.patients_per_nurse_hour * Shift.duration
capacity_cap = require(Shift.x_patients_served <= shift_nursing_capacity)
s.satisfy(capacity_cap)

# Constraint: unmet demand >= patient demand - patients served
unmet_def = require(Shift.x_unmet_demand >= Shift.patient_demand - Shift.x_patients_served)
s.satisfy(unmet_def)

With the feasible region defined, the objective minimizes overtime cost plus an overflow penalty for unmet patient demand. The overflow_penalty_per_patient parameter ($20/patient) represents the cost of failing to serve a patient:

# Objective: minimize overtime cost + overflow penalty for unmet patient demand.
# overflow_penalty_per_patient represents the cost of failing to serve a patient
# (missed care ratios, throughput shortfall, regulatory risk).
overflow_penalty_per_patient = 20
overtime_cost = sum(Nurse.x_overtime_hours * Nurse.hourly_cost * Nurse.overtime_multiplier)
total_overflow_penalty = overflow_penalty_per_patient * sum(Shift.x_unmet_demand)
s.minimize(overtime_cost + total_overflow_penalty)

Solve and print results

Finally, the script solves with the HiGHS backend and prints overtime assignments (nurses with more than 0.5 overtime hours), patient throughput by shift, and the staff assignment schedule:

solver = Solver("highs")
s.solve(solver, time_limit_sec=60)

print(f"Status: {s.termination_status}")
print(f"Total cost: ${s.objective_value:.2f}")

# Overtime summary
overtime = select(
    Nurse.name.alias("nurse"),
    Nurse.x_overtime_hours.alias("overtime_hours"),
).where(Nurse.x_overtime_hours > 0.5).to_df()

if not overtime.empty:
    print("\nOvertime assignments:")
    print(overtime.to_string(index=False))
else:
    print("\nNo overtime assigned.")

# Throughput and overflow summary
throughput = select(
    Shift.name.alias("shift"),
    Shift.x_patients_served.alias("patients_served"),
    Shift.patient_demand.alias("patient_demand"),
    Shift.x_unmet_demand.alias("unmet_demand"),
).to_df()

print("\nPatient throughput by shift:")
print(throughput.to_string(index=False))
print(f"Total patients served: {throughput['patients_served'].sum():.0f} / {throughput['patient_demand'].sum()}")
print(f"Total unmet demand: {throughput['unmet_demand'].sum():.0f} patients")

# Staff assignments
assignments = select(
    Assignment.availability.nurse.name.alias("nurse"),
    Assignment.availability.shift.name.alias("shift"),
).where(Assignment.x_assigned > 0.5).to_df()

print("\nStaff assignments:")
print(assignments.to_string(index=False))

Customize this template

Here are some ideas for how to customize and extend this template to fit your specific use case.

Tune parameters

The overflow_penalty_per_patient parameter controls the trade-off between overtime cost and unmet patient demand.

Parameter	Default	Effect
`overflow_penalty_per_patient`	20	Higher values push the optimizer to staff up at overtime cost; lower values tolerate more unmet demand

Use your own data

Replace the CSVs in data/ with your own, keeping the same column names (or update the loading logic in hospital_staffing.py).
Ensure that availability.csv only references valid nurse_id and shift_id values.
Each nurse needs a regular_hours value (standard hours before overtime) and an overtime_multiplier.
Each shift needs patient_demand and patients_per_nurse_hour values for throughput modeling.

Extend the model

Add shift preferences or fairness constraints (e.g., balanced workload across nurses).
Add per-shift overtime caps or total overtime budget limits.
Model multi-day scheduling by adding a date dimension to shifts.
Add break/rest constraints between consecutive shifts.

Scale up and productionize

Replace CSV ingestion with Snowflake sources.
Write staff assignments and overtime plans back to Snowflake after solving.

Troubleshooting

Why does authentication/configuration fail?

Run rai init to create/update raiconfig.toml.
If you have multiple profiles, set RAI_PROFILE or switch profiles in your config.

Why does the script fail to connect to the RAI Native App?

Verify the Snowflake account/role/warehouse and rai_app_name are correct in raiconfig.toml.
Ensure the RAI Native App is installed and you have access.

Why do I get ModuleNotFoundError?

Confirm your virtual environment is activated.
Install the template dependencies from this folder: python -m pip install .

Why do I get Status: INFEASIBLE?

Check that the combination of nurse availability and shift requirements is feasible. Every shift needs min_nurses available nurses, and every nurse must have at least one available shift.
Confirm that at least one nurse with the required min_skill level is available for each shift.
If you modified the data, ensure regular_hours and overtime_multiplier are positive.

Why is the overtime summary empty?

The script filters nurses with Nurse.x_overtime_hours > 0.5. If no nurse works beyond their regular hours, the “No overtime assigned” message is printed instead.
This is expected when staffing requirements can be met without overtime.

Why are staff assignments missing or incomplete?

The output filters on Assignment.x_assigned > 0.5 (binary threshold). If values are near zero, inspect the availability data and constraints.
Confirm availability.csv was read correctly and contains rows with available = 1.

CSV loading fails (missing file or column)

Confirm the CSVs exist under data/ and the filenames match.
Ensure the headers match the expected schema:
- nurses.csv: id, name, skill_level, hourly_cost, regular_hours, overtime_multiplier
- shifts.csv: id, name, start_hour, duration, min_nurses, min_skill, patient_demand, patients_per_nurse_hour
- availability.csv: nurse_id, shift_id, available

What this template is for

This template uses RelationalAI’s Prescriptive reasoning capabilities to find the assignment of nurses to shifts that minimizes the combined cost of overtime and overflow, while respecting availability, safety limits, and skill requirements.

Prescriptive reasoning helps you:

Control overtime cost: Identify the lowest-cost overtime assignments when extra coverage is needed.
Avoid costly overflow: Ensure high-demand shifts get additional staff to prevent unmet patient demand.
Quantify trade-offs: Determine whether overtime cost is justified by the overflow penalty it avoids.
Enforce safety limits: Guarantee no nurse works more than 2 shifts (16 hours).

Who this is for

You want an end-to-end example of prescriptive reasoning (optimization) with RelationalAI.
You’re comfortable with basic Python and mixed-integer optimization concepts.

What you’ll build

A semantic model of nurses, shifts, and availability using concepts and properties.
A MILP model with binary assignment variables, continuous overtime hours, patient throughput, and unmet demand variables.
Nine constraints covering availability, coverage, skill requirements, overtime, demand, capacity, and overflow.
An objective that minimizes overtime cost plus an overflow penalty for unserved patients.
A solver that uses the HiGHS backend and prints overtime assignments, patient throughput, and staff schedules.

What’s included

Model + solve script: hospital_staffing.py
Sample data: data/nurses.csv, data/shifts.csv, data/availability.csv

Prerequisites

Access

A Snowflake account that has the RAI Native App installed.
A Snowflake user with permissions to access the RAI Native App.

Tools

Python >= 3.10

Quickstart

Follow these steps to run the template with the included sample data. You can customize the data and model as needed after you have it running end-to-end.

Download the ZIP file for this template and extract it:
Terminal window
```
curl -O https://private.relational.ai/templates/zips/v0.14/hospital_staffing.zip
unzip hospital_staffing.zip
cd hospital_staffing
```
You can also download the template ZIP using the “Download ZIP” button at the top of this page.

Create and activate a virtual environment

python -m venv .venv
source .venv/bin/activate
python -m pip install -U pip

Install dependencies

From this folder:
Terminal window
```
python -m pip install .
```
Configure Snowflake connection and RAI profile
Terminal window
```
rai init
```
Run the template
Terminal window
```
python hospital_staffing.py
```

Expected output

Status: OPTIMAL
Total cost: $616.00

Overtime assignments:
  nurse  overtime_hours
Nurse_B             8.0
Nurse_C             8.0

Patient throughput by shift:
     shift  patients_served  patient_demand  unmet_demand
   Morning             32.0              45          13.0
 Afternoon             60.0              60           0.0
     Night             24.0              25           1.0

Total patients served: 116 / 130
Total unmet demand: 14 patients

Staff assignments:
  nurse      shift
Nurse_A    Morning
Nurse_A  Afternoon
Nurse_B    Morning
Nurse_B  Afternoon
Nurse_C  Afternoon
Nurse_C      Night
Nurse_D    Morning
Nurse_E    Morning
Nurse_E      Night
Nurse_F      Night

Template structure

.
├─ README.md
├─ pyproject.toml
├─ hospital_staffing.py      # main runner / entrypoint
└─ data/                     # sample input data
   ├─ nurses.csv
   ├─ shifts.csv
   └─ availability.csv

Start here: hospital_staffing.py

Sample data

Data files are in data/.

`nurses.csv`

Staff members with skill ratings, pay rates, and overtime parameters.

Column	Meaning
`id`	Unique nurse identifier
`name`	Nurse name
`skill_level`	Skill rating (1=basic, 2=intermediate, 3=advanced)
`hourly_cost`	Cost per hour ($)
`regular_hours`	Standard hours before overtime kicks in (8)
`overtime_multiplier`	Overtime pay rate multiplier (1.5x)

`shifts.csv`

Time periods with staffing requirements and patient demand.

Column	Meaning
`id`	Unique shift identifier
`name`	Shift name (Morning, Afternoon, Night)
`start_hour`	Shift start time (24-hour format)
`duration`	Shift length in hours
`min_nurses`	Minimum nurses required per shift
`min_skill`	Minimum skill level required for at least one nurse
`patient_demand`	Total patients needing care during this shift
`patients_per_nurse_hour`	Patients one nurse can serve per hour

`availability.csv`

Links nurses to shifts, indicating which assignments are feasible.

Column	Meaning
`nurse_id`	Foreign key to `nurses.csv.id`
`shift_id`	Foreign key to `shifts.csv.id`
`available`	1 if the nurse can work this shift, 0 otherwise

Model overview

The semantic model for this template is built around four concepts.

`Nurse`

A staff member with a skill level, hourly cost, and overtime parameters.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/nurses.csv`
`name`	string	No	Human-readable nurse name
`skill_level`	int	No	Used in skill-requirement constraints
`hourly_cost`	float	No	Cost per hour, used in overtime cost calculation
`regular_hours`	int	No	Hours before overtime applies (8)
`overtime_multiplier`	float	No	Overtime pay multiplier (1.5x)
`overtime_hours`	float	No	Continuous decision variable ()

`Shift`

A time period with coverage requirements and patient demand.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/shifts.csv`
`name`	string	No	Human-readable shift name
`start_hour`	int	No	Shift start time (24-hour format)
`duration`	int	No	Shift length in hours
`min_nurses`	int	No	Minimum nurses required
`min_skill`	int	No	Minimum skill level for at least one assigned nurse
`patient_demand`	int	No	Total patients needing care
`patients_per_nurse_hour`	float	No	Patients one nurse can serve per hour
`patients_served`	float	No	Continuous decision variable ()
`unmet_demand`	float	No	Continuous decision variable (); overflow

`Availability`

A nurse-shift pair indicating whether the nurse can work that shift.

Property	Type	Identifying?	Notes
`nurse`	`Nurse`	Part of compound key	Joined via `data/availability.csv.nurse_id`
`shift`	`Shift`	Part of compound key	Joined via `data/availability.csv.shift_id`
`available`	int	No	1 if available, 0 otherwise

`Assignment`

A decision concept created for each Availability row; the solver chooses whether to assign.

Property	Type	Identifying?	Notes
`availability`	`Availability`	Yes	One assignment per nurse-shift pair
`assigned`	float	No	Binary decision variable (0/1)

How it works

This section walks through the highlights in hospital_staffing.py.

Import libraries and configure inputs

First, the script imports the Semantics APIs (Model, Relationship, data, define, where, require, sum, select) and configures local inputs like DATA_DIR:

from pathlib import Path

import pandas
from pandas import read_csv

from relationalai.semantics import Model, Relationship, data, define, require, select, sum, where
from relationalai.semantics.reasoners.optimization import Solver, SolverModel

# --------------------------------------------------
# Configure inputs
# --------------------------------------------------

DATA_DIR = Path(__file__).parent / "data"

# Disable pandas inference of string types. This ensures that string columns
# in the CSVs are loaded as object dtype. This is only required when using
# relationalai versions prior to v1.0.
pandas.options.future.infer_string = False

# --------------------------------------------------
# Define semantic model & load data
# --------------------------------------------------

model = Model("hospital_staffing", config=globals().get("config", None))

Define concepts and load CSV data

Next, it declares the Nurse and Shift concepts and loads nurses.csv and shifts.csv via data(...).into(...):

# Nurse concept: staff members with skill level, cost, and overtime parameters.
Nurse = model.Concept("Nurse")
Nurse.id = model.Property("{Nurse} has {id:int}")
Nurse.name = model.Property("{Nurse} has {name:string}")
Nurse.skill_level = model.Property("{Nurse} has {skill_level:int}")
Nurse.hourly_cost = model.Property("{Nurse} has {hourly_cost:float}")
Nurse.regular_hours = model.Property("{Nurse} has {regular_hours:int}")
Nurse.overtime_multiplier = model.Property("{Nurse} has {overtime_multiplier:float}")
Nurse.x_overtime_hours = model.Property("{Nurse} has {overtime_hours:float}")

# Load nurse data from CSV and create Nurse entities.
data(read_csv(DATA_DIR / "nurses.csv")).into(Nurse, keys=["id"])

# Shift concept: time periods with coverage requirements and patient demand.
Shift = model.Concept("Shift")
Shift.id = model.Property("{Shift} has {id:int}")
Shift.name = model.Property("{Shift} has {name:string}")
Shift.start_hour = model.Property("{Shift} has {start_hour:int}")
Shift.duration = model.Property("{Shift} has {duration:int}")
Shift.min_nurses = model.Property("{Shift} has {min_nurses:int}")
Shift.min_skill = model.Property("{Shift} has {min_skill:int}")
Shift.patient_demand = model.Property("{Shift} has {patient_demand:int}")
Shift.patients_per_nurse_hour = model.Property("{Shift} has {patients_per_nurse_hour:float}")
Shift.x_patients_served = model.Property("{Shift} has {patients_served:float}")
Shift.x_unmet_demand = model.Property("{Shift} has {unmet_demand:float}")

# Load shift data from CSV and create Shift entities.
data(read_csv(DATA_DIR / "shifts.csv")).into(Shift, keys=["id"])

# Availability concept: links nurses to shifts they can work.
Availability = model.Concept("Availability")
Availability.nurse = model.Relationship("{Availability} for {nurse:Nurse}")
Availability.shift = model.Relationship("{Availability} in {shift:Shift}")
Availability.available = model.Property("{Availability} is {available:int}")

# Load availability data from CSV.
avail_data = data(read_csv(DATA_DIR / "availability.csv"))

# Define Availability entities by joining nurse/shift IDs from the CSV with the
# Nurse and Shift concepts.
where(
    Nurse.id == avail_data.nurse_id,
    Shift.id == avail_data.shift_id,
).define(
    Availability.new(nurse=Nurse, shift=Shift, available=avail_data.available)
)

Define decision variables, constraints, and objective

# Assignment decision concept: represents the decision variables for assigning
# nurses to shifts. Each Assignment is linked to an Availability entity, which
# indicates whether the nurse can work that shift.
Assignment = model.Concept("Assignment")
Assignment.availability = model.Relationship("{Assignment} uses {availability:Availability}")
Assignment.x_assigned = model.Property("{Assignment} is {assigned:float}")
define(Assignment.new(availability=Availability))

AssignmentRef = Assignment.ref()

s = SolverModel(model, "cont")

# Variable: binary assignment (nurse to shift)
s.solve_for(
    Assignment.x_assigned,
    type="bin",
    name=[
        "x",
        Assignment.availability.nurse.name,
        Assignment.availability.shift.name,
    ],
)

# Variable: overtime hours per nurse (continuous >= 0)
s.solve_for(Nurse.x_overtime_hours, type="cont", name=["ot", Nurse.name], lower=0)

# Variable: patients served per shift (continuous >= 0)
s.solve_for(Shift.x_patients_served, type="cont", name=["pt", Shift.name], lower=0)

# Variable: unmet patient demand per shift (continuous >= 0)
s.solve_for(Shift.x_unmet_demand, type="cont", name=["ud", Shift.name], lower=0)

# Constraint: can only assign if available
must_be_available = require(Assignment.x_assigned <= Assignment.availability.available)
s.satisfy(must_be_available)

# Constraint: every nurse works at least one shift
nurse_shift_count = sum(AssignmentRef.x_assigned).where(AssignmentRef.availability.nurse == Nurse).per(Nurse)
min_one_shift = require(nurse_shift_count >= 1)
s.satisfy(min_one_shift)

# Constraint: max 2 shifts per nurse (safety limit: 16 hours max)
max_two_shifts = require(nurse_shift_count <= 2)
s.satisfy(max_two_shifts)

# Constraint: minimum nurses per shift
shift_staff_count = sum(AssignmentRef.x_assigned).where(AssignmentRef.availability.shift == Shift).per(Shift)
min_coverage = require(shift_staff_count >= Shift.min_nurses)
s.satisfy(min_coverage)

# Constraint: at least one nurse with required skill level per shift
skilled_coverage = sum(AssignmentRef.x_assigned).where(
    AssignmentRef.availability.shift == Shift,
    AssignmentRef.availability.nurse.skill_level >= Shift.min_skill,
).per(Shift)
min_skilled = require(skilled_coverage >= 1)
s.satisfy(min_skilled)

# Constraint: overtime >= total hours worked - regular hours
total_hours_worked = sum(AssignmentRef.x_assigned * AssignmentRef.availability.shift.duration).where(
    AssignmentRef.availability.nurse == Nurse
).per(Nurse)
overtime_def = require(Nurse.x_overtime_hours >= total_hours_worked - Nurse.regular_hours)
s.satisfy(overtime_def)

# Constraint: patients served <= patient demand per shift
demand_cap = require(Shift.x_patients_served <= Shift.patient_demand)
s.satisfy(demand_cap)

# Constraint: patients served <= nursing capacity per shift
shift_nursing_capacity = shift_staff_count * Shift.patients_per_nurse_hour * Shift.duration
capacity_cap = require(Shift.x_patients_served <= shift_nursing_capacity)
s.satisfy(capacity_cap)

# Constraint: unmet demand >= patient demand - patients served
unmet_def = require(Shift.x_unmet_demand >= Shift.patient_demand - Shift.x_patients_served)
s.satisfy(unmet_def)

# Objective: minimize overtime cost + overflow penalty for unmet patient demand.
# overflow_penalty_per_patient represents the cost of failing to serve a patient
# (missed care ratios, throughput shortfall, regulatory risk).
overflow_penalty_per_patient = 20
overtime_cost = sum(Nurse.x_overtime_hours * Nurse.hourly_cost * Nurse.overtime_multiplier)
total_overflow_penalty = overflow_penalty_per_patient * sum(Shift.x_unmet_demand)
s.minimize(overtime_cost + total_overflow_penalty)

Solve and print results

Finally, the script solves with the HiGHS backend and prints overtime assignments (nurses with more than 0.5 overtime hours), patient throughput by shift, and the staff assignment schedule:

solver = Solver("highs")
s.solve(solver, time_limit_sec=60)

print(f"Status: {s.termination_status}")
print(f"Total cost: ${s.objective_value:.2f}")

# Overtime summary
overtime = select(
    Nurse.name.alias("nurse"),
    Nurse.x_overtime_hours.alias("overtime_hours"),
).where(Nurse.x_overtime_hours > 0.5).to_df()

if not overtime.empty:
    print("\nOvertime assignments:")
    print(overtime.to_string(index=False))
else:
    print("\nNo overtime assigned.")

# Throughput and overflow summary
throughput = select(
    Shift.name.alias("shift"),
    Shift.x_patients_served.alias("patients_served"),
    Shift.patient_demand.alias("patient_demand"),
    Shift.x_unmet_demand.alias("unmet_demand"),
).to_df()

print("\nPatient throughput by shift:")
print(throughput.to_string(index=False))
print(f"Total patients served: {throughput['patients_served'].sum():.0f} / {throughput['patient_demand'].sum()}")
print(f"Total unmet demand: {throughput['unmet_demand'].sum():.0f} patients")

# Staff assignments
assignments = select(
    Assignment.availability.nurse.name.alias("nurse"),
    Assignment.availability.shift.name.alias("shift"),
).where(Assignment.x_assigned > 0.5).to_df()

print("\nStaff assignments:")
print(assignments.to_string(index=False))

Customize this template

Here are some ideas for how to customize and extend this template to fit your specific use case.

Tune parameters

The overflow_penalty_per_patient parameter controls the trade-off between overtime cost and unmet patient demand.

Parameter	Default	Effect
`overflow_penalty_per_patient`	20	Higher values push the optimizer to staff up at overtime cost; lower values tolerate more unmet demand

Use your own data

Replace the CSVs in data/ with your own, keeping the same column names (or update the loading logic in hospital_staffing.py).
Ensure that availability.csv only references valid nurse_id and shift_id values.
Each nurse needs a regular_hours value (standard hours before overtime) and an overtime_multiplier.
Each shift needs patient_demand and patients_per_nurse_hour values for throughput modeling.

Extend the model

Add shift preferences or fairness constraints (e.g., balanced workload across nurses).
Add per-shift overtime caps or total overtime budget limits.
Model multi-day scheduling by adding a date dimension to shifts.
Add break/rest constraints between consecutive shifts.

Scale up and productionize

Replace CSV ingestion with Snowflake sources.
Write staff assignments and overtime plans back to Snowflake after solving.

Troubleshooting

Why does authentication/configuration fail?

Run rai init to create/update raiconfig.toml.
If you have multiple profiles, set RAI_PROFILE or switch profiles in your config.

Why does the script fail to connect to the RAI Native App?

Verify the Snowflake account/role/warehouse and rai_app_name are correct in raiconfig.toml.
Ensure the RAI Native App is installed and you have access.

Why do I get ModuleNotFoundError?

Confirm your virtual environment is activated.
Install the template dependencies from this folder: python -m pip install .

Why do I get Status: INFEASIBLE?

Check that the combination of nurse availability and shift requirements is feasible. Every shift needs min_nurses available nurses, and every nurse must have at least one available shift.
Confirm that at least one nurse with the required min_skill level is available for each shift.
If you modified the data, ensure regular_hours and overtime_multiplier are positive.

Why is the overtime summary empty?

The script filters nurses with Nurse.x_overtime_hours > 0.5. If no nurse works beyond their regular hours, the “No overtime assigned” message is printed instead.
This is expected when staffing requirements can be met without overtime.

Why are staff assignments missing or incomplete?

The output filters on Assignment.x_assigned > 0.5 (binary threshold). If values are near zero, inspect the availability data and constraints.
Confirm availability.csv was read correctly and contains rows with available = 1.

CSV loading fails (missing file or column)

Confirm the CSVs exist under data/ and the filenames match.
Ensure the headers match the expected schema:
- nurses.csv: id, name, skill_level, hourly_cost, regular_hours, overtime_multiplier
- shifts.csv: id, name, start_hour, duration, min_nurses, min_skill, patient_demand, patients_per_nurse_hour
- availability.csv: nurse_id, shift_id, available

What this template is for

This template uses prescriptive reasoning (optimization) to frame hospital nurse scheduling as a bi-objective problem with two competing objectives: minimize overtime cost versus minimize unmet patient demand. The original single-objective formulation bundled both goals into one weighted penalty sum, forcing the modeler to choose a penalty weight up front. This version unbundles them using the epsilon constraint method: it sweeps a range of caps on allowable unmet demand, and at each cap the solver minimizes overtime cost subject to that service-level constraint.

The result is a Pareto frontier that reveals exactly how much overtime cost each level of patient service requires — making the tradeoff explicit and auditable rather than hidden inside a penalty weight.

Who this is for

Healthcare operations managers building nurse scheduling systems
Data engineers integrating optimization into hospital workforce platforms
Developers learning to model staffing problems with mixed binary and continuous variables
Anyone exploring multi-objective optimization with coverage and skill constraints

What you’ll build

A nurse-to-shift assignment model with binary decision variables
Overtime tracking with continuous variables and cost multipliers
Patient throughput and unmet demand calculations per shift
Minimum staffing and skill-level coverage constraints
Epsilon constraint method sweeping unmet demand caps to trace the cost-service frontier
Pareto analysis with marginal cost per patient and knee detection

What’s included

hospital_staffing.py — Main script with model definition, epsilon constraint sweep, and Pareto analysis
data/nurses.csv — Nurse roster with skill levels, hourly costs, and overtime parameters
data/shifts.csv — Shift definitions with timing, staffing requirements, and patient demand
data/availability.csv — Nurse-to-shift availability matrix
pyproject.toml — Python project configuration with dependencies

Prerequisites

Access

A Snowflake account that has the RAI Native App installed.
A Snowflake user with permissions to access the RAI Native App.

Tools

Python >= 3.10
RelationalAI Python SDK (relationalai) >= 1.0.13

Quickstart

Download ZIP:
Terminal window
```
curl -O https://docs.relational.ai/templates/zips/v1/hospital_staffing.zip
unzip hospital_staffing.zip
cd hospital_staffing
```
You can also download the template ZIP using the “Download ZIP” button at the top of this page.

Create venv:

python -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip

Install:
Terminal window
```
python -m pip install .
```
Configure:
Terminal window
```
rai init
```
Run:
Terminal window
```
python hospital_staffing.py
```

Expected output:

======================================================================
ANCHOR SOLVE 1: Minimize overtime cost (no unmet demand constraint)
======================================================================
Overtime cost: $0.00
Unmet demand: 130.0 patients

======================================================================
ANCHOR SOLVE 2: Minimize unmet demand (no overtime cost objective)
======================================================================
Min unmet demand: 0.0 patients

Feasible unmet demand range: [0.0, 130.0]

======================================================================
EPSILON SWEEP: 5 interior points
Unmet demand targets: ['108.3', '86.7', '65.0', '43.3', '21.7']
======================================================================

======================================================================
EFFICIENT FRONTIER: Overtime Cost vs Patient Service
======================================================================
  #          Label   Unmet Demand  Overtime Cost
------------------------------------------------
  1       cheapest          130.0 $         0.00
  2          eps_1          108.3 $         0.00
  3          eps_2           86.7 $         0.00
  4          eps_3           65.0 $         0.00
  5          eps_4           43.3 $         0.00
  6          eps_5           21.7 $       336.00
  7   best_service            0.0 $      1116.00

Overtime Cost
$ 1,116.00 |7                                                 |
           |                                                  |
           |                                                  |
           |                                                  |
           |                                                  |
           |                                                  |
           |                                                  |
           |                                                  |
           |        6                                         |
           |                                                  |
           |                                                  |
$     0.00 |                5       4       3       2        1|
           +--------------------------------------------------+
            0                                              130 patients
                               Unmet Demand

Marginal analysis (cost of reducing unmet demand by 1 patient):
    cheapest -> eps_4         : $0.00/patient (free capacity available)
       eps_4 -> eps_5         : $15.51/patient
       eps_5 -> best_service  : $36.00/patient

  Knee: Point 5 (eps_4) -- marginal cost jumps 15.5x beyond this point
  Recommendation: Target 43 unmet patients at $0.00 overtime cost --
  further service improvement costs significantly more per patient.

  Knee-point assignments:
    A_Afternoon: Nurse
    B_Night: Nurse
    C_Afternoon: Nurse
    D_Morning: Nurse
    E_Morning: Nurse
    F_Night: Nurse

The Pareto frontier reveals a sharp knee at point 5: the first 67% of demand reduction (130 to 43 patients) is free, but reducing the last 43 patients costs 0 to 36.00 per patient.

Template structure

.
├── README.md
├── pyproject.toml
├── hospital_staffing.py
└── data/
    ├── nurses.csv
    ├── shifts.csv
    └── availability.csv

How it works

This section walks through the highlights in hospital_staffing.py.

Define concepts and load CSV data

The model defines three core concepts: nurses with skill levels and cost parameters, shifts with coverage requirements and patient demand, and an availability relationship linking nurses to shifts.

Nurse = Concept("Nurse", identify_by={"id": Integer})
Nurse.name = Property(f"{Nurse} has {String:name}")
Nurse.skill_level = Property(f"{Nurse} has {Integer:skill_level}")
Nurse.hourly_cost = Property(f"{Nurse} has {Float:hourly_cost}")

Shift = Concept("Shift", identify_by={"id": Integer})
Shift.min_nurses = Property(f"{Shift} has {Integer:min_nurses}")
Shift.min_skill = Property(f"{Shift} has {Integer:min_skill}")
Shift.patient_demand = Property(f"{Shift} has {Integer:patient_demand}")

Define decision variables, constraints, and objective

The solve_staffing helper encapsulates the full formulation. It registers four variable types and applies all constraints, then switches between objectives and an optional epsilon bound on unmet demand.

The original single-objective template bundled overtime cost and unmet demand into one weighted penalty sum (problem.minimize(overtime_cost + PENALTY * sum(Shift.x_unmet_demand))). The bi-objective version splits them: the primary objective minimizes overtime cost, while unmet demand is bounded by an epsilon constraint. This eliminates the arbitrary penalty weight and reveals the true tradeoff.

def solve_staffing(objective="min_overtime", eps_unmet=None):
    problem = Problem(model, Float)

    problem.solve_for(Assignment.x_assigned, type="bin", populate=False,
                name=["assigned", Assignment.availability.nurse.name,
                      Assignment.availability.shift.name])
    problem.solve_for(Nurse.x_overtime_hours, type="cont", populate=False,
                name=["ot", Nurse.name], lower=0)
    problem.solve_for(Shift.x_patients_served, type="cont", populate=False,
                name=["pt", Shift.name], lower=0)
    problem.solve_for(Shift.x_unmet_demand, type="cont", populate=False,
                name=["ud", Shift.name], lower=0)

Constraints enforce availability, minimum staffing, skill coverage, overtime tracking, and patient demand accounting.

    # Each nurse works 1-2 shifts
    nurse_shift_count = sum(AssignmentRef.x_assigned).where(
        AssignmentRef.availability.nurse == Nurse).per(Nurse)
    problem.satisfy(model.require(nurse_shift_count >= 1))
    problem.satisfy(model.require(nurse_shift_count <= 2))

    # Minimum nurses per shift with skill requirements
    shift_staff_count = sum(AssignmentRef.x_assigned).where(
        AssignmentRef.availability.shift == Shift).per(Shift)
    problem.satisfy(model.require(shift_staff_count >= Shift.min_nurses))

When eps_unmet is provided, a constraint caps total unmet demand across all shifts.

    if eps_unmet is not None:
        problem.satisfy(model.require(sum(Shift.x_unmet_demand) <= eps_unmet))

The objective switches between minimizing overtime cost (primary) and minimizing unmet demand (used for anchor solve 2).

    overtime_cost = sum(Nurse.x_overtime_hours * Nurse.hourly_cost * Nurse.overtime_multiplier)

    if objective == "min_overtime":
        problem.minimize(overtime_cost)
    elif objective == "min_unmet":
        problem.minimize(sum(Shift.x_unmet_demand))

    problem.solve("highs", time_limit_sec=60)

Solve anchor points and run the epsilon sweep

Two anchor solves establish the feasible unmet demand range. Anchor 1 minimizes overtime with no demand constraint (finding the cheapest schedule, which may leave patients unserved). Anchor 2 minimizes unmet demand (finding the best achievable service level).

result1 = solve_staffing("min_overtime", eps_unmet=None)
result2 = solve_staffing("min_unmet", eps_unmet=None)

The epsilon sweep then traces interior points between the anchors. Each solve minimizes overtime cost subject to a progressively tighter cap on unmet demand.

n_interior = 5
epsilon_values = [
    unmet_max - i * (unmet_max - unmet_min) / (n_interior + 1)
    for i in range(1, n_interior + 1)
]

for i, eps in enumerate(epsilon_values):
    result = solve_staffing("min_overtime", eps_unmet=eps)

Pareto analysis output

The script prints the efficient frontier showing how overtime cost increases as the unmet demand cap tightens. Marginal analysis computes the cost of reducing unmet demand by one patient, and a knee detector identifies the point where the marginal cost jumps sharply — recommending the best cost-service balance.

print(f"{'#':>3} {'Label':>14} {'Unmet Demand':>14} {'Overtime Cost':>14}")
for j, pt in enumerate(pareto):
    print(f"{j+1:>3} {pt['label']:>14} {pt['unmet_demand']:>14.1f} ${pt['overtime_cost']:>13.2f}")

# Marginal analysis (cost of reducing unmet demand by 1 patient)
for j in range(len(pareto) - 1):
    d_cost = pareto[j+1]['overtime_cost'] - pareto[j]['overtime_cost']
    d_unmet = pareto[j]['unmet_demand'] - pareto[j+1]['unmet_demand']
    if abs(d_unmet) > 1e-6:
        rate = d_cost / d_unmet
        # ...

# Knee detection
print(f"\n  Knee: Point {knee_idx + 1} ({pareto[knee_idx]['label']}) "
      f"— marginal cost jumps {max_jump:.1f}x beyond this point")
print(f"  Recommendation: Target {pareto[knee_idx]['unmet_demand']:.0f} unmet patients "
      f"at ${pareto[knee_idx]['overtime_cost']:.2f} overtime cost — "
      f"further service improvement costs significantly more per patient.")

Customize this template

Add more nurses or shifts by extending the CSV files with additional rows.
Adjust frontier resolution: Increase n_interior for a finer-grained Pareto frontier.
Add shift preferences by introducing a preference weight per nurse-shift pair and including it in the objective.
Model consecutive shift restrictions by adding constraints that prevent nurses from working back-to-back shifts without rest.
Introduce part-time nurses with different regular hour limits and availability patterns.

Troubleshooting

Solver returns INFEASIBLE

Check that nurse availability in availability.csv provides enough coverage to meet the minimum staffing requirements in shifts.csv. With the current data, each shift requires at least 2 nurses, so ensure enough nurses are available per shift.

High unmet demand in the solution

Tighten the epsilon constraint by reducing the unmet demand cap (or increase n_interior to explore finer gradations). If even the best-service anchor shows high unmet demand, the nurse roster may need more staff or broader availability.

Connection or authentication errors

Run rai init to configure your Snowflake connection. Verify that the RAI Native App is installed and your user has the required permissions.

ModuleNotFoundError for relationalai

Ensure you activated the virtual environment and ran python -m pip install . to install all dependencies listed in pyproject.toml.