OSCAR EMR — Data Entity Relationships & REST API Requirements¶
Version: 3.1 Last Updated: 2026-02-14
Overview¶
This page documents the complete OSCAR EMR data architecture — every entity, relationship, mandatory field, and REST API constraint that VitaraVox must understand to integrate correctly. OSCAR uses no foreign key constraints; all relationships are application-enforced. This has critical implications for data integrity and API usage.
No FK Constraints
OSCAR's database has zero foreign key constraints. Every relationship described below is enforced only at the application layer. Orphan records, dangling references, and referential integrity violations are possible and must be handled defensively.
Master Entity Relationship Diagram¶
Identity Layer¶
The identity layer establishes who can use the system (providers) and how they authenticate (OAuth).
┌──────────────────────────────────────────────────────────────────────────────┐
│ IDENTITY LAYER │
│ │
│ ┌─────────────────────────────┐ ┌────────────────────────────┐ │
│ │ provider │ │ secUserRole │ │
│ │─────────────────────────────│ │────────────────────────────│ │
│ │ *provider_no VARCHAR(6) PK │◄────────│ provider_no VARCHAR(6) │ │
│ │ first_name VARCHAR(30) │ 1:N │ role_name VARCHAR(60) │ │
│ │ last_name VARCHAR(30) │ │ orgcd VARCHAR(80) │ │
│ │ provider_type VARCHAR(15) │ │ activeyn INT(1) │ │
│ │ specialty VARCHAR(40) │ │────────────────────────────│ │
│ │ status CHAR(1) │ │ MANDATORY FOR REST: │ │
│ │ sex CHAR(1) │ │ • provider_no NOT NULL │ │
│ │ ohip_no VARCHAR(20) │ │ • role_name NOT NULL │ │
│ │ billing_no VARCHAR(20) │ │ • orgcd = 'R0000001' │ │
│ │ practitionerNo VARCHAR(20)│ │ • activeyn = 1 │ │
│ │ email VARCHAR(60) │ │ │ │
│ │ title VARCHAR(20) │ │ ⚠ NULL activeyn or empty │ │
│ │─────────────────────────────│ │ orgcd causes REST NPE │ │
│ │ MANDATORY FOR REST: │ │ in DemographicService │ │
│ │ • provider_no (PK) │ └────────────────────────────┘ │
│ │ • first_name │ │
│ │ • last_name │ ┌────────────────────────────┐ │
│ │ • provider_type = 'doctor' │ │ ServiceClient (OAuth) │ │
│ │ • status = '1' (active) │ │────────────────────────────│ │
│ │ • sex │ │ clientKey VARCHAR(255) │ │
│ │ │ │ clientSecret VARCHAR(255) │ │
│ │ ⚠ provider must have ≥1 │ │ name VARCHAR(255) │ │
│ │ secUserRole with │ │ uri VARCHAR(255) │ │
│ │ role_name='doctor' │ │ lifetime INT(11) │ │
│ │ for REST OAuth to work │ │────────────────────────────│ │
│ └──────────┬──────────────────┘ │ Used for OAuth 1.0a flow │ │
│ │ │ Token TTL = lifetime secs │ │
│ │ └────────────────────────────┘ │
└─────────────┼────────────────────────────────────────────────────────────────┘
│
│ provider_no referenced by 5 entities (no FK enforced)
▼
provider Table¶
| Column | Type | Notes |
|---|---|---|
provider_no |
VARCHAR(6) PK | Primary key |
first_name |
VARCHAR(30) | Required for REST |
last_name |
VARCHAR(30) | Required for REST |
provider_type |
VARCHAR(15) | Must be 'doctor' for REST |
specialty |
VARCHAR(40) | |
status |
CHAR(1) | '1' = active |
sex |
CHAR(1) | Required for REST |
ohip_no |
VARCHAR(20) | Ontario billing number |
billing_no |
VARCHAR(20) | |
practitionerNo |
VARCHAR(20) | |
email |
VARCHAR(60) | |
title |
VARCHAR(20) |
REST OAuth Prerequisite
A provider must have at least one secUserRole entry with role_name='doctor' for the REST OAuth flow to work. Without this, OAuth token exchange fails silently.
secUserRole Table¶
| Column | Type | Notes |
|---|---|---|
provider_no |
VARCHAR(6) | References provider.provider_no |
role_name |
VARCHAR(60) | Must be 'doctor' for REST |
orgcd |
VARCHAR(80) | Must be 'R0000001' |
activeyn |
INT(1) | Must be 1 |
NPE Trap
NULL values in activeyn or empty orgcd cause a NullPointerException in OSCAR's DemographicService. This is a known OSCAR bug that affects REST API demographic lookups.
ServiceClient Table (OAuth)¶
| Column | Type | Notes |
|---|---|---|
clientKey |
VARCHAR(255) | OAuth consumer key |
clientSecret |
VARCHAR(255) | OAuth consumer secret |
name |
VARCHAR(255) | Client application name |
uri |
VARCHAR(255) | Callback URI |
lifetime |
INT(11) | Token TTL in seconds |
Patient Layer¶
The patient layer stores demographics, program admissions, and extended attributes.
┌──────────────────────────────────────────────────────────────────────────────┐
│ PATIENT LAYER │
│ │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ demographic │ │
│ │────────────────────────────────────────────────────────│ │
│ │ *demographic_no INT(10) PK AUTO_INCREMENT │ │
│ │ first_name VARCHAR(30) NOT NULL DEFAULT '' │ │
│ │ last_name VARCHAR(30) NOT NULL DEFAULT '' │ │
│ │ sex CHAR(1) NOT NULL DEFAULT '' │ │
│ │ title VARCHAR(10) │ │
│ │ year_of_birth VARCHAR(4) ┐ │ │
│ │ month_of_birth VARCHAR(2) ├─ DOB as 3 strings │ │
│ │ date_of_birth VARCHAR(2) ┘ │ │
│ │ hin VARCHAR(20) ← health card # │ │
│ │ ver CHAR(3) ← HIN version │ │
│ │ hc_type VARCHAR(20) ← province code │ │
│ │ provider_no VARCHAR(11) ← MRP doctor │ │
│ │ patient_status VARCHAR(20) ← AC/IN/... │ │
│ │ address VARCHAR(60) │ │
│ │ city VARCHAR(50) │ │
│ │ province VARCHAR(20) │ │
│ │ postal VARCHAR(9) │ │
│ │ phone VARCHAR(20) │ │
│ │ phone2 VARCHAR(20) │ │
│ │ email VARCHAR(100) │ │
│ │ official_lang VARCHAR(60) │ │
│ │ spoken_lang VARCHAR(60) │ │
│ │ lastUpdateDate DATETIME NOT NULL │ │
│ │ gender VARCHAR(25) ← gender identity │ │
│ │ pronoun VARCHAR(25) │ │
│ │ middleNames VARCHAR(100) │ │
│ └────────────────────────────────────────────────────────┘ │
│ │
│ Related: admission, program, demographicExt, demographiccust │
└──────────────────────────────────────────────────────────────────────────────┘
demographic Table¶
Mandatory for REST POST /demographics:
| Field | Maps To | Notes |
|---|---|---|
firstName |
first_name |
|
lastName |
last_name |
|
sex |
sex |
M or F |
admissionProgramId |
(not stored in demographic) | Passed to admission creation; use 10034 |
Recommended for a complete record:
| Field | Maps To | Notes |
|---|---|---|
dobYear, dobMonth, dobDay |
year_of_birth, month_of_birth, date_of_birth |
Split across 3 VARCHAR columns |
hin + hcType + ver |
hin, hc_type, ver |
Health card number + province + version |
address.address, .city, .province, .postal |
address, city, province, postal |
|
phone, email |
phone, email |
|
providerNo |
provider_no |
Assigns Most Responsible Provider (MRP) |
patientStatus |
patient_status |
Use 'AC' for active |
DOB Storage Quirk
Date of birth is stored as three separate VARCHAR columns (year_of_birth, month_of_birth, date_of_birth), not as a DATE type. Validation must happen at the application layer.
Provider Reference Trap
provider_no in the demographic table must reference a valid provider with a secUserRole doctor entry. If it references an invalid provider, REST GET calls on that demographic will throw an NPE.
admission Table¶
| Column | Type | Notes |
|---|---|---|
am_id |
BIGINT PK AI | Auto-increment |
client_id |
BIGINT NOT NULL | References demographic.demographic_no |
program_id |
BIGINT NOT NULL | Must be 10034 (OSCAR Service program) |
provider_no |
VARCHAR(6) | |
admission_date |
DATETIME | Auto-set on creation |
admission_status |
VARCHAR(24) | 'current' for active |
discharge_date |
DATETIME |
Mandatory for REST POST:
client_id(from demographic PK)program_id=10034admission_status='current'admission_date(auto-set)
Automatic Creation
Admission records are created automatically by OSCAR's demographicManager when a REST POST /demographics includes admissionProgramId.
program Table¶
| Column | Type | Notes |
|---|---|---|
id |
INT PK | |
name |
VARCHAR(80) | |
type |
VARCHAR(80) |
Key record: id=10034, name='OSCAR', type='Service'
Hardcoded Program ID
All patient admissions must use program_id=10034. This is the default OSCAR Service program. Using any other value will cause patients to not appear in provider rosters.
demographicExt Table (EAV Pattern)¶
| Column | Type | Notes |
|---|---|---|
demographic_no |
INT(10) | References demographic.demographic_no |
key_val |
VARCHAR(64) | Extension key |
value |
TEXT | Extension value |
Common keys: demo_cell, aboriginal, ethnicity, privacyConsent, rxInteractionWarningLevel, hPhoneExt, wPhoneExt
demographiccust Table¶
| Column | Type | Notes |
|---|---|---|
demographic_no |
INT(10) PK | 1:1 with demographic |
nurse |
(provider_no ref) | |
resident |
(provider_no ref) | |
midwife |
(provider_no ref) | |
notes |
(content field) |
Scheduling Layer¶
The scheduling layer defines what time slots are available through a chain of three tables. See also the OSCAR Schedule Deep Dive for detailed analysis of the scheduling algorithm.
┌──────────────────────────────────────────────────────────────────────────────┐
│ SCHEDULING LAYER │
│ │
│ Template Definition Chain (defines WHAT slots are available): │
│ │
│ ┌────────────────────────────┐ ┌──────────────────────────────┐ │
│ │ scheduletemplatecode │ │ scheduletemplate │ │
│ │────────────────────────────│ │──────────────────────────────│ │
│ │ *id INT(10) PK │ │ *provider_no VARCHAR(6) ┐PK│ │
│ │ code CHAR(1) UNIQUE │ │ *name VARCHAR(20) ┘ │ │
│ │ description VARCHAR(80) │ │ summary VARCHAR(80) │ │
│ │ duration CHAR(3) │ │ timecode TEXT │ │
│ │ color VARCHAR(10) │ └──────────────────────────────┘ │
│ │ confirm CHAR(3) │ │
│ │ bookinglimit INT(11) │ Schedule Assignment: │
│ │────────────────────────────│ │
│ │ CRITICAL CODES: │ ┌──────────────────────────────┐ │
│ │ '1' = 15min appointment │ │ scheduledate │ │
│ │ '2' = 30min appointment │ │──────────────────────────────│ │
│ │ '3' = 45min appointment │ │ *id INT(6) PK AI │ │
│ │ '6' = 60min appointment │ │ sdate DATE NOT NULL │ │
│ │ 'C' = On Call Clinic │ │ provider_no VARCHAR(6) │ │
│ │ '_' = Unavailable │ │ available CHAR(1) │ │
│ │ 'L' = Lunch │ │ priority CHAR(1) │ │
│ │ 'P' = Phone time │ │ hour VARCHAR(255) │ │
│ │ 'U' = Urgent │ │ status CHAR(1) │ │
│ │ 's' = Same Day │ │ creator VARCHAR(50) │ │
│ │ 'W' = Same Week │ │──────────────────────────────│ │
│ │ │ │ hour = template NAME ref │ │
│ │ SLOT DURATION FORMULA: │ │ status = 'A' (active) │ │
│ │ min = 1440 / len(timecode) │ │ available = '1' │ │
│ │ 96 chars = 15 min/slot │ └──────────────────────────────┘ │
│ │ │ │
│ │ ⚠ Each code = exactly │ │
│ │ one slot of time │ │
│ └────────────────────────────┘ │
│ │
│ JOIN: scheduletemplate.name = scheduledate.hour │
│ AND (scheduletemplate.provider_no = scheduledate.provider_no │
│ OR scheduletemplate.provider_no = 'Public') │
│ │
│ Resolution priority: │
│ 1. Provider-specific template │
│ 2. 'Public' template │
└──────────────────────────────────────────────────────────────────────────────┘
scheduletemplatecode Table¶
Defines the alphabet of slot types. Each character in a timecode string maps to one of these codes.
| Column | Type | Notes |
|---|---|---|
id |
INT(10) PK | |
code |
CHAR(1) UNIQUE | Single character |
description |
VARCHAR(80) | Human-readable name |
duration |
CHAR(3) | Always 15 min per code |
color |
VARCHAR(10) | UI display color |
confirm |
CHAR(3) | |
bookinglimit |
INT(11) | Advisory only — not server-enforced |
Critical codes:
| Code | Meaning | Duration |
|---|---|---|
1 |
15-min appointment | 15 min |
2 |
30-min appointment | 15 min (2 consecutive = 30 min) |
3 |
45-min appointment | 15 min (3 consecutive = 45 min) |
6 |
60-min appointment | 15 min (4 consecutive = 60 min) |
C |
On Call Clinic | 15 min |
_ |
Unavailable | — |
L |
Lunch | — |
P |
Phone time | — |
U |
Urgent | 15 min |
s |
Same Day | 15 min |
W |
Same Week | 15 min |
scheduletemplate Table¶
Defines named schedule patterns for a provider.
| Column | Type | Notes |
|---|---|---|
provider_no |
VARCHAR(6) | Part of composite PK |
name |
VARCHAR(20) | Part of composite PK; max 20 chars |
summary |
VARCHAR(80) | |
timecode |
TEXT | String where each char = 15-min slot |
Timecode encoding:
Slot duration is calculated by the formula from ScheduleManager.java:154:
int timeSlotDuration = (60 * 24) / timecode.length();
// 96 chars → 1440/96 = 15 min per slot (STANDARD)
- Each character maps to a
scheduletemplatecode.code - 96-char timecodes = 15 min/slot (4 slots per hour, 24 hours)
- Example:
____...111111111111LLLL111111111111...__= 9AM-12 bookable, lunch, 1PM-4PM bookable
96-char position reference (4 positions per hour):
| Positions | Time | Positions | Time | Positions | Time |
|---|---|---|---|---|---|
| 1-4 | 00:00-00:59 | 33-36 | 08:00-08:59 | 65-68 | 16:00-16:59 |
| 5-8 | 01:00-01:59 | 37-40 | 09:00-09:59 | 69-72 | 17:00-17:59 |
| 9-12 | 02:00-02:59 | 41-44 | 10:00-10:59 | 73-76 | 18:00-18:59 |
| 13-16 | 03:00-03:59 | 45-48 | 11:00-11:59 | 77-80 | 19:00-19:59 |
| 17-20 | 04:00-04:59 | 49-52 | 12:00-12:59 | 81-84 | 20:00-20:59 |
| 21-24 | 05:00-05:59 | 53-56 | 13:00-13:59 | 85-88 | 21:00-21:59 |
| 25-28 | 06:00-06:59 | 57-60 | 14:00-14:59 | 89-92 | 22:00-22:59 |
| 29-32 | 07:00-07:59 | 61-64 | 15:00-15:59 | 93-96 | 23:00-23:59 |
provider_no='Public' means the template is shared by all providers.
Name Length Limit
name is VARCHAR(20) max. Raw timecodes can never be stored as names — they exceed this limit.
scheduledate Table¶
Assigns a template to a specific provider on a specific date.
| Column | Type | Notes |
|---|---|---|
id |
INT(6) PK AI | |
sdate |
DATE NOT NULL | Calendar date |
provider_no |
VARCHAR(6) NOT NULL | Which doctor |
available |
CHAR(1) NOT NULL | '1' for available |
priority |
CHAR(1) | |
hour |
VARCHAR(255) | Template NAME reference (NOT a timecode!) |
status |
CHAR(1) NOT NULL | 'A' for active |
creator |
VARCHAR(50) |
Critical: hour is a Template NAME
scheduledate.hour stores a template name (e.g., "Monday", "P:OnCallClinic"), NOT a time value or raw timecode. The JOIN is: scheduletemplate.name = scheduledate.hour. Putting raw timecodes in hour breaks the OSCAR UI because the JOIN fails silently.
Appointment Layer¶
Appointments are independent of schedule slots. There is no foreign key between the appointment table and the scheduling tables.
┌──────────────────────────────────────────────────────────────────────────────┐
│ APPOINTMENT LAYER (independent of schedule slots) │
│ │
│ ┌─────────────────────────────────────────────────┐ │
│ │ appointment │ │
│ │─────────────────────────────────────────────────│ │
│ │ *appointment_no INT(12) PK AI │ │
│ │ provider_no VARCHAR(6) NOT NULL │ │
│ │ appointment_date DATE NOT NULL │ │
│ │ start_time TIME NOT NULL │ │
│ │ end_time TIME NOT NULL │ │
│ │ demographic_no INT(10) │ │
│ │ name VARCHAR(50) ← cached pt name │ │
│ │ program_id INT(11) DEFAULT 0 │ │
│ │ type VARCHAR(50) ← free text! │ │
│ │ status CHAR(2) │ │
│ │ reason VARCHAR(80) │ │
│ │ location VARCHAR(30) │ │
│ │ notes VARCHAR(255) │ │
│ │ resources VARCHAR(255) │ │
│ │ urgency VARCHAR(30) │ │
│ │ bookingSource VARCHAR(32) │ │
│ │ createdatetime DATETIME │ │
│ │ updatedatetime DATETIME │ │
│ │ creator VARCHAR(50) │ │
│ │ lastupdateuser VARCHAR(6) │ │
│ └─────────────────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────┐ │
│ │ appointmentType │ │
│ │────────────────────────────────│ │
│ │ *id INT(12) PK │ │
│ │ name VARCHAR(50) │ │
│ │ notes VARCHAR(80) │ │
│ │ reason VARCHAR(80) │ │
│ │ location VARCHAR(30) │ │
│ │ resources VARCHAR(10) │ │
│ │ duration INT(12) │ │
│ │────────────────────────────────│ │
│ │ ⚠ appointment.type is free │ │
│ │ text, NOT a FK to this table │ │
│ └────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────────────────────┘
appointment Table¶
Mandatory for REST POST /schedule/add:
| Field | Format | Notes |
|---|---|---|
providerNo |
Valid provider_no | Must reference active provider |
appointmentDate |
yyyy-MM-dd |
|
startTime |
HH:mm (24h) |
|
duration |
Integer (minutes) | endTime auto-calculated |
demographicNo |
Valid demographic_no | Must reference existing patient |
Recommended:
| Field | Notes |
|---|---|
type |
From appointmentType.name (by convention only) |
status |
't' for confirmed/to-be-seen |
reason |
Text description |
Auto-set by REST service:
| Field | How Set |
|---|---|
name |
From demographic formatted name |
creator |
From OAuth provider |
endTime |
startTime + (duration - 1) minutes |
createdatetime / updatedatetime |
Current timestamp |
Appointment Status Codes¶
| Code | Meaning |
|---|---|
t |
Confirmed / to-be-seen |
H |
Here / arrived |
C |
Cancelled |
T |
Completed |
b |
Billed |
A |
No-show |
sc |
Signed / completed |
Appointments Are Independent of Slots
Changing scheduledate or scheduletemplate does NOT affect existing appointments. Appointments are stored independently — there is no join or constraint between them and the scheduling tables. This means:
- Deleting a schedule slot does not cancel booked appointments
- Double-booking is possible unless your application checks for conflicts
- True availability = template slots − existing appointments
appointmentType Table¶
| Column | Type | Notes |
|---|---|---|
id |
INT(12) PK | |
name |
VARCHAR(50) | |
notes |
VARCHAR(80) | |
reason |
VARCHAR(80) | |
location |
VARCHAR(30) | |
resources |
VARCHAR(10) | |
duration |
INT(12) | Default duration in minutes |
No Foreign Key
appointment.type is a free text VARCHAR(50) field. It is NOT a foreign key to appointmentType.id. The relationship is by convention only — the application may write any string into appointment.type.
OAuth Layer¶
The OAuth 1.0a flow for REST API authentication.
┌──────────────────────────────────────────────────────────────────────────────┐
│ OAUTH LAYER (for REST API authentication) │
│ │
│ ┌─────────────────────┐ ┌──────────────────────┐ │
│ │ ServiceClient │ │ ServiceRequestToken │ │
│ │─────────────────────│ │──────────────────────│ │
│ │ *id INT PK │◄────│ clientId INT │ │
│ │ clientKey VARCHAR │ │ tokenId VARCHAR │ │
│ │ clientSecret VCHAR │ │ tokenSecret VARCHAR │ │
│ │ name VARCHAR │ │ callback VARCHAR │ │
│ │ uri VARCHAR │ │ verifier VARCHAR │ │
│ │ lifetime INT │ │ providerNo VARCHAR │ │
│ └──────────┬──────────┘ │ scopes VARCHAR │ │
│ │ │──────────────────────│ │
│ │ │ Temp token during │ │
│ │ │ authorization flow │ │
│ │ │ ⚠ Expires after 1hr │ │
│ │ └──────────┬───────────┘ │
│ │ │ │
│ │ ▼ │
│ │ ┌──────────────────────┐ │
│ │ │ ServiceAccessToken │ │
│ │ │──────────────────────│ │
│ └───────────────►│ clientId INT │ │
│ │ tokenId VARCHAR │ │
│ │ tokenSecret VARCHAR │ │
│ │ lifetime INT │ │
│ │ issued BIGINT │ │
│ │ providerNo VARCHAR │ │
│ │ scopes VARCHAR │ │
│ │──────────────────────│ │
│ │ Final token for API │ │
│ │ calls. TTL=lifetime │ │
│ │ providerNo MUST be │ │
│ │ valid in provider │ │
│ │ table with doctor │ │
│ │ secUserRole │ │
│ └──────────────────────┘ │
└──────────────────────────────────────────────────────────────────────────────┘
OAuth 1.0a Flow:
- Register client — Create
ServiceClientwith key/secret pair - Get request token — Temporary token stored in
ServiceRequestToken - Authorize — User logs in via OSCAR UI;
providerNoset on request token - Exchange for access token — Request token exchanged for
ServiceAccessToken - Use access token — All subsequent REST API calls use this token
Token Expiry
- Request tokens expire after 1 hour
- Access token TTL is set by
ServiceClient.lifetime(in seconds) providerNoon the access token must reference a valid provider with adoctorrole insecUserRole
Cross-Entity Dependency Graph¶
This graph shows how all entities relate to each other across layers.
┌──────────────────────────────┐
│ scheduletemplatecode │
│ (defines slot type alphabet) │
└──────────────┬───────────────┘
│ each char in timecode
▼
┌──────────────┐ ┌──────────────────┐ ┌──────────────────┐
│ provider │◄─────────│ scheduletemplate │ │ scheduledate │
│ │ PK: │ PK: (provider_no, │◄─────────│ hour → name │
│ │ provider_ │ name) │ JOIN │ sdate │
│ │ no │ timecode(TEXT) │ │ provider_no │
└──────┬───────┘ └──────────────────┘ └──────────────────┘
│
│ provider_no referenced by:
│
├────────────────────┐
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ secUserRole │ │ demographic │ │ appointment │
│ role='doctor'│ │ provider_no │ │ provider_no │
│ activeyn=1 │ │ = MRP │ │ = appt doc │
│ orgcd filled │ └──────┬───────┘ │ demographic_ │
└──────────────┘ │ │ no = patient │
│ └──────┬───────┘
│ │
▼ │
┌──────────────┐ │
│ admission │ │
│ client_id= │◄──────────┘ (demographic_no)
│ demo PK │
│ program_id= │
│ 10034 │
└──────────────┘
Key relationship rules:
| Source Entity | Target Entity | Relationship | Enforcement |
|---|---|---|---|
secUserRole.provider_no |
provider.provider_no |
N:1 | Application only |
demographic.provider_no |
provider.provider_no |
N:1 (MRP) | Application only |
appointment.provider_no |
provider.provider_no |
N:1 | Application only |
appointment.demographic_no |
demographic.demographic_no |
N:1 | Application only |
admission.client_id |
demographic.demographic_no |
N:1 | Application only |
admission.program_id |
program.id |
N:1 | Application only |
scheduledate.hour |
scheduletemplate.name |
N:1 (JOIN) | Application only |
scheduletemplate.provider_no |
provider.provider_no |
N:1 | Application only |
scheduletemplate.timecode chars |
scheduletemplatecode.code |
Each char → 1 code | Application only |
Entity Creation Order¶
Entities must be created in this sequence — violating the order causes application-level errors:
1. scheduletemplatecode (slot type definitions — usually pre-seeded)
2. provider (doctors)
3. secUserRole (role assignments — MUST have doctor + orgcd + activeyn)
4. program (service program 10034 — usually pre-seeded)
5. program_provider (link providers to program 10034)
6. scheduletemplate (timecode patterns for each provider)
7. demographic (patients — via REST POST with admissionProgramId=10034)
└─► admission (auto-created by REST POST)
8. scheduledate (assign templates to dates for providers)
9. appointment (book patients — via REST POST /schedule/add)
REST API Endpoint Reference¶
Authentication: OAuth 1.0a¶
| Endpoint | Method | Auth |
|---|---|---|
/ws/oauth/initiate |
POST | Consumer Key+Secret (HMAC-SHA1) |
/ws/oauth/authorize |
GET | Browser session (provider login) |
/ws/oauth/token |
POST | Consumer Key+Secret + Request Token |
/ws/services/* |
* | Access Token (HMAC-SHA1 signed) |
Demographics (Patient) CRUD¶
| Endpoint | Method | Mandatory Fields | Optional Fields |
|---|---|---|---|
/ws/services/demographics |
POST | firstName, lastName, sex (M/F), admissionProgramId (10034) |
dobYear, dobMonth, dobDay, hin, hcType, ver, providerNo, patientStatus, address{address,city,province,postal}, phone, email, chartNo, rosterStatus, officialLanguage, spokenLanguage, title, gender, pronoun |
/ws/services/demographics |
PUT | demographicNo (existing ID) |
All fields from POST (updates only provided fields) |
/ws/services/demographics/{id} |
GET | Path: id |
Query: includes[]=allergies, includes[]=measurements, includes[]=notes, includes[]=medications |
/ws/services/demographics/{id} |
DELETE | Path: id |
— |
/ws/services/demographics/quickSearch |
GET | Query: query (name, addr:, chartNo:) |
— |
Schedule / Appointment CRUD¶
| Endpoint | Method | Mandatory Fields | Optional Fields |
|---|---|---|---|
/ws/services/schedule/add |
POST | providerNo, appointmentDate (yyyy-MM-dd), startTime (HH:mm), duration (minutes), demographicNo |
type, status, reason, location, notes, urgency, reasonCode, resources |
/ws/services/schedule/updateAppointment |
POST | id (appointment_no) |
All appointment fields |
/ws/services/schedule/deleteAppointment |
POST | id (appointment_no) |
— |
/ws/services/schedule/appointment/{id}/updateStatus |
POST | Path: id, Body: {status:"t"} |
— |
/ws/services/schedule/day/{date} |
GET | Path: date (yyyy-MM-dd or "today") |
— |
/ws/services/schedule/{providerNo}/day/{date} |
GET | Path: both | — |
/ws/services/schedule/types |
GET | — | — |
/ws/services/schedule/codes |
GET | — | — |
/ws/services/schedule/statuses |
GET | — | — |
Known Bugs & Gotchas¶
Validated against a live OSCAR instance:
| # | Issue | Impact | Workaround |
|---|---|---|---|
| 1 | secUserRole with NULL activeyn or empty orgcd |
REST getDemographicData() NPE at line 289 — "doctor" is null |
Ensure ALL secUserRole rows have activeyn=1 AND orgcd='R0000001' |
| 2 | demographic.provider_no referencing non-existent provider |
OAuth interceptor throws unknown_provider on token validation |
Only assign provider_no values that exist in provider table |
| 3 | DemographicConverter links/rosterTerminationReason bug |
links field always returns rosterTerminationReason value |
Don't rely on links field in REST responses |
| 4 | AppointmentConverter.getAsDomainObject() returns null |
updateAppointment endpoint may not work as expected |
Use status/type/urgency update sub-endpoints instead |
| 5 | scheduledate.hour must be template NAME not timecode |
OSCAR UI shows blank schedule if raw timecode in hour |
Always use template name (max 20 chars) |
| 6 | scheduletemplate.name is VARCHAR(20) |
Template names must be ≤20 chars | Design short names: "Monday", "FullDay", etc. |
| 7 | POST /demographics without admissionProgramId |
Hibernate PropertyValueException: not-null Admission.programId |
Always include "admissionProgramId": 10034 |
| 8 | Access token providerNo must exist in provider table |
unknown_provider OAuth error |
Use real provider_no when authorizing tokens |
| 9 | Appointment endTime = startTime + (duration - 1) min |
15-min appointment: end = start + 14 min (e.g. 09:00→09:14) | Set duration correctly; off-by-one is intentional |
VitaraVox Integration Implications¶
What This Means for Our Adapters¶
- Always validate provider references — Before creating demographics or appointments, verify the provider exists and has the required
secUserRole - Always use program_id 10034 — For admission records; no other value is safe
- Subtract booked appointments from template slots — OSCAR doesn't do this automatically; our BookingEngine must calculate true availability
- Handle DOB as three strings — Never assume a DATE type for birth dates
- Treat appointment.type as free text — Don't assume referential integrity with
appointmentType - Defensive coding for missing data — No FK constraints means orphan records exist in production OSCAR instances
Related Documentation¶
- OSCAR Demo Data & Test Environment — Provider set, patient set, schedule templates, validation checklist, and automation
- OSCAR Schedule Deep Dive — Detailed scheduling algorithm analysis
- Booking Product Architecture (ADR-004) — Why booking logic lives in our platform
- EMR Abstraction Layer (ADR-003) — Multi-EMR adapter pattern
- OSCAR REST Bridge — Bridge implementation details
- SOAP Integration Evidence — Production SOAP consumer proof