Skip to content

OscarPro Schedule Template Schema — Source Findings

Status: Provisional — findings marked from code are definitive; those marked needs DB query are not settled, and the queries that would settle them are listed under DB queries still wanted. Source repo: oscar-pro/src/main/java/ca/kai/schedule/ FHIR converter (existing): oscar-pro/src/main/java/ca/kai/fhir/converter/r4/{ScheduleConverter,SlotConverter,ScheduleTemplateCodeConverter}.java

This note answers the six assumptions the schema investigation was scoped to verify. Each finding flags whether it's from code (definitive) or needs DB query (still pending).

TL;DR — the picture changed materially

OscarPro already has partial FHIR Slot/Schedule converter code in production. This was not flagged in the original plan. Three components exist:

  • ScheduleConverter.java — emits Schedule + bundled Slot[] per provider
  • SlotConverter.java — walks timecode and produces List<Slot> for a given date
  • ScheduleTemplateCodeConverter.java — emits a FHIR ValueSet from the scheduletemplatecode table

This significantly reshapes the converter contract's role: it is no longer a greenfield handoff. It's a migration spec from the existing partial converter (which has several base-FHIR violations — see §"Existing converter bugs" below) to the new profile-compliant emission.

Per-assumption findings

Assumption 1: timecode length is always 96

Code finding (definitive): FALSE — length is variable, treated as such by the existing converter.

SlotConverter.toSlot() line 40 computes int slotDuration = minutesInADay / timeCode.length();. The slot duration is derived from the length, not assumed. Common deployments may use 96 (15-min) or 48 (30-min) or 24 (60-min), but the converter handles any divisor of 1440.

Impact on the Slot profile: Do not constrain Slot.start/.end to a 15-minute grid. Keep them as base FHIR instant. Document the convention that slot duration = 1440 / len(timecode) minutes in oscar-to-fhir.md.

DB query still wanted: distribution of observed timecode lengths in production (96 vs 48 vs other) — informs example fixtures but doesn't change the profile.

Assumption 2: scheduletemplatecode.duration is reliably populated

Code finding (partial): Field is String (not int) with default "15". Default value means the field is never null in new rows. The existing ScheduleTemplateCodeConverter line 77 wraps it in new IntegerType(duration) — which throws if the string is non-numeric. So if a legacy row has e.g. "15min" or "", the existing converter crashes.

Impact on the Slot profile: Acceptable to require duration in FSH bindings IF the DB query (below) confirms no rows violate. Otherwise add defensive handling note in converter-contracts.md.

DB query still wanted: SELECT COUNT(*) FROM scheduletemplatecode WHERE duration IS NULL OR duration !~ '^[0-9]+$';

Assumption 3: Providers share templates across locations

Code finding (definitive): ScheduleTemplate has NO location relationship. Primary key is (provider_no, name). Templates are provider-scoped; location is attached only at Schedule construction time (and the existing converter doesn't attach Location at all — see §"Existing converter bugs" #3 below).

Impact on the Schedule profile: Confirms Schedule.actor[Location] should be 0..1 MS (or 0..* if a provider works at multiple locations under one Schedule). Existing converter emits no Location, so any Schedule design that requires one is a change to current behavior. Document in fhir-to-oscar.md.

DB query still wanted: count of distinct templates per provider (do providers maintain many templates or just one?) — informs whether one Schedule per provider or per (provider, template) is more accurate.

Assumption 4: Every timecode char has a matching scheduletemplatecode row

Code finding (partial): SlotConverter line 47-49 calls findByCode(timeCodeChar). If the lookup returns null, the slot is still emitted but without the template-code extension — no exception thrown. So partial coverage is tolerated by current code.

Also: '_' is treated specially (line 43) — when timecode[i] == '_', no slot is emitted at all (continue). This is the "no slot here" convention.

Impact on the Slot profile: rawCode slice on Slot.serviceType.coding should be 0..* MS, not 1..* MS. The existing converter already produces Slots without rawCode mapping for unmatched chars. Tightening to 1..* would break the existing converter.

DB query still wanted: SELECT DISTINCT char FROM (unnested timecode chars across all scheduletemplate rows) WHERE char NOT IN (SELECT code FROM scheduletemplatecode); — quantifies the gap.

Assumption 5: Slots starting after 23:30 cross midnight

Code finding (definitive): No — midnight overflow is prevented by construction. SlotConverter.createSlot() lines 79-80 compute LocalTime starting from 00:00:00 plus index offsets. LocalTime is 24-hour-wrapped: the last slot index (e.g. 95 in a 96-char timecode) produces start 23:45 and end 00:00 (next day boundary, but LocalTime.plusMinutes(slotDuration * (index+1)) may wrap silently).

Potential bug: the existing code uses LocalTime.plusMinutes(...) which wraps at midnight (a known JDK behavior). The end time of the last slot could be incorrectly 00:00 (same date) instead of 00:00 next day. Worth a follow-up unit test, not blocking.

Impact on the Slot profile: Profile is unaffected; Slot.end is instant which doesn't have this issue at the FHIR level. Flag the converter behavior in converter-contracts.md.

Assumption 6: How blackout/holiday days are represented

Code finding (definitive): Two-layer model.

  1. scheduleholiday table (entity ScheduleHoliday.java) — keyed by sdate, with holiday_name. Existing converter checks this and emits a https://apps.health/List/schedule-date-is-holiday boolean extension on the day's Bundle entry (line 162-163).
  2. ScheduleDate.available char — default '1' (IS_AVAILABLE). Other values presumably indicate within-day blockouts. Existing converter does not currently emit this signal into the FHIR output.

Impact on the Slot profile and the booking flow: The IG should map holiday days to Slot.status = busy-unavailable (or omit slot emission entirely). The existing converter does neither — it emits an out-of-band extension. Document the new contract: holidays → no slots, or slots with busy-unavailable. Decision for booking-flow-design.md.

DB query still wanted: SELECT COUNT(*) FROM scheduledate WHERE available != '1'; — quantifies within-day blockout usage. If zero, ignore the available field; if non-zero, decide its FHIR mapping.

Existing converter bugs / divergences (new — not in original spike scope)

Discovered while reading the existing converter; surfacing here because they affect the converter contract's scope.

  1. Slot.schedule is never set. Base FHIR R4 requires Slot.schedule 1..1. SlotConverter.toSlot() returns slots without setting .schedule. Any consumer running base-FHIR validation against current OscarPro output gets a validation error.

  2. Slot.status is never set. Also 1..1 required in base FHIR. Same problem.

  3. Schedule.actor references Practitioner/, not PractitionerRole/. Line 82 of ScheduleConverter: schedule.addActor().setReference(Practitioner.class.getSimpleName() + "/" + providerNo);. Our NexusEmrCoreSchedule profile constrains actor to NexusEmrCorePractitionerRole. This is a direct conflict — the existing converter must be updated.

  4. Extension URLs use https://apps.health/ instead of https://fhir.apps.health/. Inconsistent with the canonical IG base. Examples: EXTENSION_URL_SCHEDULE_TEMPLATE_CODE = "https://apps.health/Slot/schedule-template-code", EXTENSION_URL_SCHEDULE_DATE = "https://apps.health/List/schedule-date". These should be migrated or aliased.

  5. No appointment-overlay logic. Plan §3 calls for overlaying the appointment table to set Slot.status=busy on slots that have a booked appointment. Existing converter does not do this — all slots are emitted with no busy/free distinction.

  6. Nested Bundle-of-Bundles structure. toSchedule() returns a Bundle containing per-provider Bundles containing Schedule + per-day Bundles containing Slots. Unusual; downstream consumers may struggle with the nesting. Consider flattening in the migration.

Suggested before the Schedule profile work starts:

  • Slot profile: relax Slot.serviceType.coding[rawCode] to 0..* (not 1..*). Confirmed by code; DB query will refine the rationale.
  • Slot profile: do not assume 96-char timecode in any example fixture. Use whatever the existing converter produces.
  • Schedule profile: keep Schedule.actor[PractitionerRole] 0..1 MS as designed — but note the existing converter uses Practitioner, not PractitionerRole. The converter contract must explicitly require the converter migrate to PractitionerRole.
  • Converter contract: rescope from "greenfield handoff" to "migration spec for existing converter," covering at minimum: setting Slot.status + Slot.schedule, changing actor type, aligning extension URLs, adding appointment-overlay for busy/free status, deciding holiday → busy-unavailable vs omit.
  • booking-flow-design.md: document the holiday + within-day-blockout (ScheduleDate.available) decision.

DB queries still wanted

Minimum query set to close the spike:

-- Q1: timecode length distribution
SELECT LENGTH(timecode) AS len, COUNT(*) FROM scheduletemplate GROUP BY LENGTH(timecode);

-- Q2: scheduletemplatecode duration sanity
SELECT COUNT(*) AS bad_duration FROM scheduletemplatecode
WHERE duration IS NULL OR duration !~ '^[0-9]+$';

-- Q3: distinct templates per provider
SELECT provider_no, COUNT(*) AS template_count FROM scheduletemplate
GROUP BY provider_no ORDER BY template_count DESC LIMIT 20;

-- Q4: timecode chars without scheduletemplatecode coverage
-- (DB-specific; example for Postgres using string_to_array + unnest;
--  for MySQL, use a temp table walking the string char-by-char)
WITH chars AS (
  SELECT DISTINCT SUBSTRING(timecode FROM g FOR 1) AS c
  FROM scheduletemplate, generate_series(1, LENGTH(timecode)) AS g
  WHERE timecode IS NOT NULL
)
SELECT c FROM chars WHERE c != '_' AND c NOT IN (SELECT code FROM scheduletemplatecode);

-- Q5: within-day blockout usage
SELECT available, COUNT(*) FROM scheduledate GROUP BY available;

-- Q6: scheduleholiday population
SELECT COUNT(*) FROM scheduleholiday;
SELECT MIN(sdate), MAX(sdate) FROM scheduleholiday;

These run read-only against any OscarPro dev/staging DB. Results paste into this file under a "DB findings" section, and the spike closes.

Status checklist

  • [x] Source-code investigation complete (this file)
  • [ ] DB queries Q1–Q6 run; results captured below
  • [ ] Recommended plan adjustments accepted or rejected
  • [ ] Schedule and Slot profile work unblocked