OSCAR Schedule System — Complete Architecture Deep Dive¶
Understanding the 4-table schedule chain, availability resolution, and double-booking risks
Version: 1.0.0 Last Updated: February 13, 2026
See Also
For the complete OSCAR data model covering all entity layers (Identity, Patient, Scheduling, Appointment, OAuth), see OSCAR Data Architecture.
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?"
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?"
Result: { provider_no: '100', name: 'P', timecode: '____BBBBBBBBBBBB____BBBBBBBBBBBB____BBBBBBBB____' }
Step 3: "What does each character mean?"
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
addAppointmentaccepts 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)¶
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)¶
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. Required Actions — Booking Product Architecture¶
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 Redis/DB, 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: │
│ • getTemplateAvailability(providerId, date) → raw slots[] │
│ • getExistingAppointments(providerId, dateRange) → appts[] │
│ • createAppointment(data) → appointmentId │
│ • cancelAppointment(id) → void │
│ • getTemplateCodes() → 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 │ │
│ └───────────┘ └─────────────┘ └──────────────┘ │
│ │
└──────────────────────────────────────────────────────────────────┘
P0 — Must Fix Before Production¶
-
Build BookingEngine at platform level —
getTrueAvailability()that callsadapter.getTemplateAvailability()andadapter.getExistingAppointments(), subtracts occupied slots, enforces bookinglimit, applies clinic rules. -
Pre-booking conflict check with slot locking — Acquire lock in YOUR database (Redis/PostgreSQL), fresh availability check, create appointment via adapter, post-verify, release lock. Race conditions are handled by YOUR booking queue, not OSCAR's.
-
Build SOAP Adapter — Direct WS-Security connection to OSCAR's SOAP API. Uses
getDayWorkSchedulefor template availability,getAppointmentsForDateRangeAndProvider2for existing appointments,addAppointmentfor booking. This is the universal production connector.
P1 — Should Fix¶
-
Post-booking verification — After
addAppointmentsucceeds, 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). -
Bookinglimit enforcement — Respect the
bookinglimitfield fromscheduletemplatecodewhen computing true availability in the BookingEngine.
P2 — Nice to Have¶
- Template code sync to clinic config — Expose schedule template codes per clinic so the voice agent can map appointment reasons to correct OSCAR codes (future epic — not yet scheduled).
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 |
|---|---|
| Phase 1 (now) | Build BookingEngine with Bridge Adapter for dev speed |
| Phase 2 (next) | Build SOAP Adapter alongside Bridge Adapter |
| Phase 3 (launch) | First customer uses SOAP Adapter — no bridge deployed |
| Phase 4 (mature) | Add REST/Hybrid adapters for Juno-specific features |