OSCAR Schedule System — Complete Architecture Deep Dive¶

Understanding the 4-table schedule chain, availability resolution, and double-booking risks

Version: 1.1.0 Last Updated: February 16, 2026

1. The Core Relationship Chain¶

The OSCAR schedule system is a 4-table chain that resolves "what time slots does this provider have on this date?" It is not a simple calendar — it uses an indirection pattern where dates reference named templates, and templates contain encoded timecode strings.

┌──────────────┐
│   provider   │
│──────────────│
│ provider_no  │─────────┬──────────────┬─────────────────┐
│ (PK, vc 6)  │         │              │                 │
└──────────────┘         │              │                 │
                         ▼              ▼                 ▼
             ┌────────────────┐  ┌──────────────┐  ┌──────────────┐
             │  scheduledate  │  │ schedule     │  │ appointment  │
             │────────────────│  │ template     │  │──────────────│
             │ id (PK)       │  │──────────────│  │ appt_no (PK) │
             │ sdate (date)  │  │ provider_no ─┤  │ provider_no  │
             │ provider_no   │  │ name (vc 20) │  │ demographic_ │
             │ hour (vc 255) ├─►│  (composite  │  │   no         │
             │ status ('A')  │  │   PK)        │  │ appt_date    │
             │ available     │  │ timecode     │  │ start_time   │
             └────────────────┘  │  (text)     │  │ end_time     │
                                 └──────┬───────┘  │ status       │
                JOIN:                    │          └──────────────┘
                sd.hour = st.name        │
                AND provider match       ▼ each char maps to
                                 ┌──────────────────┐
                                 │ scheduletemplate  │
                                 │ code              │
                                 │──────────────────│
                                 │ id (PK)          │
                                 │ code (char 1)    │
                                 │ description      │
                                 │ duration         │
                                 │ color            │
                                 │ bookinglimit     │
                                 │ confirm          │
                                 └──────────────────┘

Resolution Algorithm — Step by Step¶

Step 1: "What template is Dr. Smith using on Feb 14?"

SELECT * FROM scheduledate
WHERE provider_no = '100' AND sdate = '2026-02-14' AND status = 'A'

Result: { provider_no: '100', sdate: '2026-02-14', hour: 'P' }

The hour column is NOT a time

scheduledate.hour is the single most misleading column name in OSCAR. It does NOT contain an hour or time. It contains a template name — a foreign key reference to scheduletemplate.name. This indirection is mandatory because scheduletemplate.name is varchar(20), while raw 48/96-character timecodes can never fit in the hour field.

Step 2: "What timecode does template 'P' contain?"

SELECT * FROM scheduletemplate
WHERE (provider_no = '100' OR provider_no = 'Public') AND name = 'P'

Result: { provider_no: '100', name: 'P', timecode: '____BBBBBBBBBBBB____BBBBBBBBBBBB____BBBBBBBB____' }

Step 3: "What does each character mean?"

SELECT * FROM scheduletemplatecode WHERE code = 'B'

Result: { code: 'B', description: '15 min appt', duration: '15', color: '#BFEBBF', bookinglimit: 1 }

The _ character is hardcoded as unavailable — it never appears in scheduletemplatecode and is never bookable.

Step 4: "Parse the timecode into slots"

48 chars → each = 15 min starting at 8:00 AM
96 chars → each = 15 min starting at midnight
Formula: slot_duration = 1440 / timecode.length

timecode: _ _ _ _ B B B B B B B B B B B B _ _ _ _ B B B B ...
slot:     8:00    9:00                    12:00   13:00
          skip    avail                   lunch   avail

Template Ownership: Private vs Public¶

scheduletemplate (provider_no='100', name='P')    ← Dr. Smith's private template
scheduletemplate (provider_no='Public', name='P')  ← Shared fallback template

Resolution order:

Look for (provider_no='100', name='P') — use if found
Fall back to (provider_no='Public', name='P')

2. The Bookinglimit and Confirm System¶

Each slot type code has capacity controls built into scheduletemplatecode:

Code	Description	Duration	Bookinglimit	Confirm
`1`	15 min appt	15	1	No
`B`	15 min block	15	1	No
`C`	On-call	15	1	Onc
`_`	Not available	15	0	No
`L`	Lunch	15	0	No

Confirm Values¶

Value	Behavior
`No`	No enforcement at all
`Yes`	Warning popup, staff can override
`Day`	Same-day booking limit
`Wk`	Same-week booking limit
`Onc`	On-call/urgent
`Str`	Strict enforcement — defined in UI but commented out as "NOT IMPLEMENTED"

Bookinglimit is advisory only

OSCAR has never implemented strict enforcement of booking limits. The bookinglimit field controls UI display only (showing - when full, | when one over, || when two+ over) but is a warning only. Staff can always override. Our bridge has no access to these UI-level warnings.

3. How Appointments Relate to Schedule Slots¶

Critical Architecture Insight

Appointments and schedule slots are completely independent entities. There is no foreign key between the appointment table and the schedule tables. An appointment is just a row with a date, time, provider, and patient. The schedule template is a separate concept that defines "intended availability." The two are only connected at the UI rendering level.

Schedule Slots (template-derived):        Appointments (separately stored):
┌────────┬──────────┬──────┐              ┌────────┬──────────┬───────────┐
│ Time   │ Code     │ Avail│              │ Time   │ Patient  │ Status    │
├────────┼──────────┼──────┤              ├────────┼──────────┼───────────┤
│ 09:00  │ B (15m)  │ yes  │◄─── books──►│ 09:00  │ #456     │ t (sched) │
│ 09:15  │ B (15m)  │ yes  │              │        │          │           │
│ 09:30  │ B (15m)  │ yes  │              │ 09:30  │ #789     │ t (sched) │
│ 09:45  │ _ (none) │ no   │              │ 09:45  │ #012     │ t (sched) │ ← ALLOWED!
│ 10:00  │ B (15m)  │ yes  │              │        │          │           │
└────────┴──────────┴──────┘              └────────┴──────────┴───────────┘
                                           ▲
                                           Appointment at 09:45 exists even
                                           though the slot is marked '_'.
                                           Nothing prevents this via SOAP API.

Implications for our bridge:

The SOAP addAppointment accepts any time — it does not validate against the template
A booking at an "unavailable" time will succeed silently
Double-bookings at the same time/provider are also accepted silently

4. What's Exposed Through the API¶

OSCAR SOAP API (`ScheduleWs`)¶

Endpoint: http://oscar:8080/oscar/ws/ScheduleWs?wsdl Auth: WS-Security UsernameToken

Availability / Templates¶

Method	Returns	Notes
`getDayWorkSchedule(providerNo, date)`	`DayWorkScheduleTransfer` with `isHoliday`, `timeSlotDurationMin`, `timeSlots: TreeMap<Calendar, Character>`	Returns raw template slots with code chars. Caller must cross-reference with existing appointments to find true availability.
`getScheduleTemplateCodes()`	`ScheduleTemplateCodeTransfer[]` with `code`, `description`, `duration`, `color`, `bookinglimit`, `confirm`	Static reference data for decoding timecode characters.

Appointments¶

Method	Returns	Notes
`addAppointment(transfer)`	`Integer` (appointment ID)	No conflict checking. Accepts any time, even unavailable.
`updateAppointment(transfer)`	`void`	No conflict checking on time changes.
`getAppointmentsForDateRangeAndProvider2(start, end, provNo, gmt)`	`AppointmentTransfer[]`	List appointments for date range.
`getAppointmentsForPatient2(demoId, startIdx, count, gmt)`	`AppointmentTransfer[]`	Patient's appointments.
`getAppointment2(appointmentId, gmt)`	`AppointmentTransfer`	Single appointment lookup.

Also Available (lesser used)¶

getAppointmentTypes() → AppointmentTypeTransfer[]
getAppointmentsUpdatedAfterDate(date, count, gmt)
getAllDemographicIdByProgramProvider(programId, provNo)
getAppointmentsByDemographicIdAfter(date, demoId, gmt)

OSCAR Native REST API (NOT the bridge)¶

Endpoint: /ws/services/schedule/* Auth: OAuth 1.0a

Endpoint	Method	Notes
`/schedule/day/{date}`	GET	Logged-in provider's schedule
`/schedule/{providerNo}/day/{date}`	GET	Specific provider
`/schedule/fetchDays/{sDate}/{eDate}/{providers}`	GET	Bulk date range
`/schedule/fetchMonthly/{providerNo}/{year}/{month}`	GET	Monthly view
`/schedule/add`	POST	Create appointment
`/schedule/updateAppointment`	POST	Update appointment
`/schedule/deleteAppointment`	POST	Delete appointment
`/schedule/getAppointment`	POST	Get appointment
`/schedule/appointment/{id}/updateStatus`	POST	Status change
`/schedule/appointment/{id}/updateType`	POST	Type change
`/schedule/statuses`	GET	Status codes
`/schedule/types`	GET	Appointment types
`/schedule/codes`	GET	Template codes

Critical REST API Gap

The OSCAR native REST API has no getDayWorkSchedule equivalent. It cannot query template-based availability. A checkProviderAvailability endpoint exists in the source code but is commented out (from the "ERO branch", never shipped).

Our REST Bridge¶

Endpoint: http://localhost:3000/api/v1/* Auth: X-API-Key header

Availability (Direct DB — bypasses SOAP)¶

GET /appointments/availability?providerId=100&date=2026-02-14

SELECT sd.*, st.timecode as template_timecode
FROM scheduledate sd
LEFT JOIN scheduletemplate st
  ON sd.hour = st.name
  AND (st.provider_no = sd.provider_no OR st.provider_no = 'Public')
WHERE sd.provider_no = ? AND DATE(sd.sdate) = ?
  AND sd.status = 'A'
LIMIT 1

Template Availability, Not True Availability

Our bridge's /appointments/availability endpoint returns template availability only. It does NOT subtract existing appointments from available slots. A slot will show as "available" even if it already has a booking. This is the root cause of potential double-bookings.

Appointments (via SOAP)¶

Endpoint	SOAP Call	Validation
`GET /appointments?providerId=&startDate=&endDate=`	`getAppointmentsForDateRangeAndProvider2`	N/A (read-only)
`POST /appointments`	`addAppointment`	No validation against availability. No duplicate/conflict detection.
`PUT /appointments/:id`	`updateAppointment`	No conflict checking on time changes.
`DELETE /appointments/:id`	`updateAppointment({ status: 'C' })`	N/A (cancellation)

Template Codes (via SOAP)¶

GET /appointments/types → SOAP: getScheduleTemplateCodes

5. Duplicate Bookings: What They Mean and How They Happen¶

What Is a Duplicate/Double Booking?¶

Two appointments for the same provider that overlap in time:

Scenario	Example	Type
Classic double-book	Two patients at 09:00-09:15 with same provider	Same slot
Partial overlap	09:00-09:30 (30 min) + 09:15-09:30 (15 min)	Overlapping
Patient double-book	Same patient at 09:00 with Dr. Smith AND Dr. Jones	Same patient, different providers

OSCAR's Server-Side Validation (or Lack Thereof)¶

Zero Conflict Checking in ScheduleWs

From the OSCAR Java source code, ScheduleWs.addAppointment() flows directly to ScheduleManager.addAppointment() which does:

Set creator security ID
Set creator username
oscarAppointmentDao.persist(appointment) — straight to DB

Zero conflict checking. Zero availability validation. Zero bookinglimit enforcement.

OSCAR has a checkForConflict() method in OscarAppointmentDao — but it is never called anywhere in the codebase. It also has a bug (only catches appointments fully contained within the new slot, missing partial overlaps).

The BookingWs Path (Self-Booking Only)¶

OSCAR's built-in patient self-booking module (BookingWs.java) is the only path that does post-hoc conflict detection:

BookingWs.bookAppointment()
    │
    ├── 1. scheduleManager.addAppointment()      ← save FIRST (no checks)
    │
    ├── 2. scheduleManager.removeIfDoubleBooked() ← check AFTER saving
    │       │
    │       ├── Query appointments ±6hrs around the slot
    │       ├── Check 4 overlap cases:
    │       │     • existing starts during new
    │       │     • existing ends during new
    │       │     • existing contains new
    │       │     • new contains existing
    │       │
    │       ├── If conflict: DELETE the new appointment → return "DOUBLE_BOOKED"
    │       └── If clear: keep it → return success
    │
    └── 3. Return result to patient

Race Condition

Between step 1 (save) and step 2 (check), another concurrent request can also save — and both may pass the check. This removeIfDoubleBooked is NOT called by ScheduleWs.addAppointment() — only by BookingWs. Our REST bridge calls ScheduleWs, so it gets zero protection.

6. Commercial Implementation Comparative Analysis¶

How Each System Queries Availability¶

System	Method	Availability Source	True Availability?
Cortico	OAuth REST	Reads templates + subtracts appointments	Yes — merges both
Ocean/OceanMD	OAuth REST + Cloud Connect	Shows empty EMR slots; "Do Not Book" blocks	Yes — real-time via message queue
Veribook	Undisclosed (likely SOAP)	Nightly full sync + real-time verify at booking	Yes — double-checked every booking
Porton Health	Undisclosed	Template code reading + real-time sync	Likely yes
Phelix AI	OAuth REST	AI triage layer queries EMR	Likely yes
OSCAR Self-Book	Internal Java	`getDayWorkSchedule()` + `ExistingAppointmentFilter`	Yes — filters occupied slots
Our Bridge	Direct DB query	Template timecode only	NO — does not subtract existing appointments

How Each System Prevents Double Bookings¶

System	Strategy
Cortico	Only exposes slots with template codes. Maintains own slot state — booked slots disappear from patient-facing UI. Uses 5-min base granularity for mixed-duration control.
Ocean	Real-time Cloud Connect middleware. Booking request → message queue → EMR write → confirmation. Shows only truly empty slots. "Do Not Book" appointments physically occupy slots.
Veribook	Two-phase: (1) Nightly full sync (complete schedule rebuild), (2) Real-time verify at EVERY booking ("double checked every single time a patient books"). Optimistic concurrency with verification.
OSCAR Self-Book	Post-hoc deletion via `removeIfDoubleBooked()` — but ONLY for the self-booking module. `ExistingAppointmentFilter` hides occupied slots. Race condition window still exists.
Our Bridge	NONE. No pre-check. No post-check. Passes directly to SOAP `addAppointment` which also does no checking. Double bookings are silently accepted.

Gap: Our Bridge Has No Double-Booking Prevention

We are the only system in this comparison that does not subtract existing appointments from template availability, and has zero conflict detection at booking time. Every production OSCAR integration follows the pattern below — we must implement it.

The Universal Pattern Across Commercial Systems¶

Every production OSCAR integration follows this essential pattern:

Step 1: Read template availability
        (SOAP getDayWorkSchedule or DB query for timecode)
                    │
Step 2: Read existing appointments
        (SOAP getAppointmentsForDateRangeAndProvider2)
                    │
Step 3: Subtract occupied slots from template slots
        available_slots = template_slots - booked_appointments
                    │
Step 4: Present only truly available slots to patient
                    │
Step 5: Patient selects a slot → book via addAppointment
                    │
Step 6: Verify booking succeeded (varies by system):
        • Cortico: removes slot from available pool
        • Ocean: Cloud Connect real-time confirmation
        • Veribook: real-time re-verify against EMR
        • Self-Book: removeIfDoubleBooked() post-hoc check

7. BookingEngine Implementation Status¶

Critical Architecture Decision: Do NOT Build Booking Logic Into the Bridge

The bridge is a dev/testing convenience tool, not a portable production connector. Commercial OSCAR instances (Juno/WELL, KAI, PurpleHub) will never have the bridge. You get OAuth/WS-Security credentials, not SSH access. All booking intelligence must live in the VitaraVox platform layer (your servers), talking to OSCAR through pluggable adapters. See ADR-004: Booking Product Architecture for the full decision record.

Why NOT the Bridge¶

Reason	Detail
Access	Most clinics run managed OSCAR. You get OAuth creds, not SSH. You cannot deploy a sidecar container.
Liability	A component with direct DB write access on a customer's production EMR means you own every data integrity problem. Under PHIPA/PIPA/HIA, that's regulatory exposure you don't want.
Maintenance	Every OSCAR version upgrade, every Juno patch, every MariaDB update could break a bridge. Multiply by N customers.
Precedent	Cortico, Ocean, Veribook — none deploy custom code onto the EMR. They connect via OAuth/SOAP from their own cloud.

What Every OSCAR Instance Exposes¶

Always Available	Usually Available (OSCAR 15+)	Never Available to You
SOAP API (`/oscar/ws/*`): `ScheduleWs`, `DemographicWs`, `ProviderWs`	REST API (`/oscar/ws/services/*`): demographics, some scheduling	Direct database access
WS-Security UsernameToken auth	OAuth 1.0a auth	SSH / filesystem access
`getDayWorkSchedule`, `addAppointment`, `getAppointmentsForDateRangeAndProvider2`	No `getDayWorkSchedule` equivalent on REST	Custom sidecar containers

The SOAP API is the lowest common denominator across every OSCAR deployment since version 12. getDayWorkSchedule is SOAP-only — this is the single most important method for booking and it has no REST equivalent.

Three-Layer Architecture¶

+------------------------------------------------------------------+
|                                                                    |
|  LAYER 1: BOOKING PRODUCT    (your servers, your infrastructure)  |
|  ---------------------                                             |
|  ALL booking intelligence lives here:                              |
|  * Availability calculation (template slots - existing appts)      |
|  * Conflict detection (overlap, double-book prevention)            |
|  * Bookinglimit enforcement                                        |
|  * Booking queue / slot locking (your PostgreSQL, not OSCAR's)    |
|  * Post-booking verification                                       |
|  * Patient-facing UX / Voice Agent                                 |
|                                                                    |
+------------------------------------------------------------------+
|                                                                    |
|  LAYER 2: EMR ADAPTERS       (pluggable, one per EMR type)        |
|  --------------------                                              |
|  Uniform contract hiding HOW you talk to OSCAR:                    |
|  * getScheduleSlots(providerId, date) -> raw slots[]               |
|  * getAppointments(filter) -> appts[]                              |
|  * createAppointment(data) -> appointmentId                        |
|  * cancelAppointment(id) -> boolean                                |
|  * getScheduleTemplateCodes?() -> codes[]                          |
|                                                                    |
|  Implementations:                                                  |
|  +--------------+ +--------------+ +---------------+              |
|  | SOAP Adapter  | | REST Adapter | |Bridge Adapter |              |
|  | (universal    | | (OSCAR 15+,  | |(dev/test ONLY)|              |
|  |  production)  | |  partial)    | |               |              |
|  +--------------+ +--------------+ +---------------+              |
|                                                                    |
+------------------------------------------------------------------+
|                                                                    |
|  LAYER 3: CUSTOMER'S OSCAR   (their infra — hands off)           |
|  ------------------------                                          |
|  You connect to it. You install NOTHING on it.                     |
|  +-----------+  +-------------+  +--------------+                 |
|  | Self-hosted|  | Juno (WELL) |  | KAI / Other  |                |
|  | OSCAR      |  | managed     |  | managed      |                |
|  +-----------+  +-------------+  +--------------+                 |
|                                                                    |
+------------------------------------------------------------------+

Implementation Status¶

P0 — All Complete¶

BookingEngine at platform level — COMPLETE. getTrueAvailability() implemented in booking.service.ts. Calls emr.getScheduleSlots() and emr.getAppointments(), subtracts occupied slots using overlap detection (apptStart - buffer < slotEnd && apptEnd + buffer > slotStart), enforces bookinglimit, applies ScheduleSettings (operating hours, holidays, same-day/weekend restrictions, advance booking limits).
Pre-booking conflict check with slot locking — COMPLETE. BookingEngine acquires a PostgreSQL advisory lock (pg_try_advisory_lock(hashtext(key))) with key "provider:{id}:date:{date}:slot:{startTime}", performs fresh getTrueAvailability() check, creates appointment via adapter, releases lock in finally block. Race conditions handled by platform advisory locking.
SOAP Adapter — COMPLETE. OscarSoapAdapter (~1211 lines). Direct WS-Security connection to OSCAR CXF. Uses getDayWorkSchedule for template slots, getAppointmentsForDateRangeAndProvider2 for existing appointments, addAppointment for booking, updateAppointment with status='C' for cancellation.
Bookinglimit enforcement — COMPLETE. BookingEngine counts overlapping appointments per slot and compares against bookinglimit from scheduletemplatecode. When limit > 0 && overlapping >= limit, the slot is excluded from availability.

P1 — Should Fix¶

Post-booking verification — After addAppointment succeeds, re-query via adapter to confirm no double-booking occurred (handles multi-system coexistence where Cortico/Ocean/front desk also write to the same OSCAR). Not yet implemented.

P2 — Nice to Have¶

Template code sync to clinic config — COMPLETE. OscarConfigService.pullScheduleTemplateCodes() calls adapter.getScheduleTemplateCodes(), persists to ClinicConfig.scheduleTemplateCodes JSON, and derives bookableScheduleCodes / blockedScheduleCodes arrays based on the OSCAR confirm field.

BookingEngine Algorithm: getTrueAvailability¶

getTrueAvailability(providerId, date, settings?):
  settings = settings ?? loadScheduleSettings()  // from ClinicConfig + ClinicHours + ClinicHoliday

  EARLY EXIT CHECKS:
    - Holiday match -> return []
    - Day closed (operatingHours[dow].isOpen = false) -> return []
    - Weekend + allowWeekendBooking=false -> return []
    - Same day + allowSameDayBooking=false -> return []
    - date > today + maxAdvanceBookingDays -> return []

  FETCH DATA:
    templateSlots = emr.getScheduleSlots(providerId, date).slots
    existingAppts = emr.getAppointments({providerId, startDate: date, endDate: date})
                     .filter(a => a.status !== 'cancelled')

  FILTER EACH SLOT:
    for slot in templateSlots:
      slotStart = timeToMinutes(slot.startTime)
      slotEnd = timeToMinutes(slot.endTime)

      // Operating hours filter
      if slotStart < openMinutes OR slotEnd > closeMinutes: SKIP

      // Min advance booking (today only)
      if date == today AND slotStart < nowMinutes + minAdvanceMinutes: SKIP

      // Overlap detection with buffer
      overlapping = existingAppts.filter(appt =>
        (timeToMinutes(appt.startTime) - bufferTime) < slotEnd AND
        (timeToMinutes(appt.endTime) + bufferTime) > slotStart
      )

      // Bookinglimit enforcement (0 = unlimited)
      if slot.bookinglimit > 0 AND overlapping.length >= slot.bookinglimit: SKIP

      ADD to available slots

  return available[]

Bridge Disposition¶

The REST-SOAP bridge does NOT die — it changes roles:

Current Role	Future Role
Integration layer (booking logic candidate)	Dev/testing convenience tool
Only way to talk to OSCAR	Reference implementation for SOAP Adapter
Potential production connector	Useful for YOUR instance's direct-DB features (registration, quick search)
	NOT part of the portable booking product

Migration Path¶

Phase	Action	Status
Phase 1	Build BookingEngine with Bridge Adapter for dev speed	COMPLETE
Phase 2	Build SOAP Adapter alongside Bridge Adapter	COMPLETE
Phase 3 (launch)	First customer uses SOAP Adapter — no bridge deployed	CURRENT
Phase 4 (mature)	Add REST/Hybrid adapters for Juno-specific features	PLANNED