Skip to content

OSCAR Demo Data & Test Environment

Version: 1.0 Last Updated: 2026-02-14

Overview

This page specifies the complete demo data set for VitaraVox's OSCAR EMR test environment — providers, patients, schedule templates, appointments, and OAuth clients. All data follows the entity relationship rules and passes the validation checklist at the bottom of this page.

Automation

The entire data set is loaded by the oscar-align-load.sh script. See Automation at the bottom of this page.

Design Principles

  1. Every provider must have a matching secUserRole with role_name='doctor', orgcd='R0000001', activeyn=1
  2. Every demographic must have a matching admission record with program_id=10034, admission_status='current'
  3. Every demographic with a provider_no must reference a valid, active provider
  4. Every scheduledate.hour must reference an existing scheduletemplate.name for that provider (or 'Public')
  5. Every appointment provider_no and demographic_no must reference valid records
  6. All scheduletemplatecode entries must exist before templates reference them

Reference Data (Pre-seeded)

These records already exist in OSCAR and should not be modified:

Entity Key Notes
program id=10034, name='OSCAR', type='Service' Service program for all patient admissions
scheduletemplatecode 10+ codes '1'=15min, '2'=30min, '3'=45min, '6'=60min, 'C'=OnCall, '_'=Unavail, 'L'=Lunch, 'P'=Phone, 'U'=Urgent, 's'=SameDay, 'W'=SameWeek
appointmentType 15 types Office Visit (15m), Follow-up (15m), Annual Physical (30m), New Patient (30m), Urgent Care (15m), Consult (20m), etc.

Provider Set

4 doctors plus 1 system provider:

provider_no first_name last_name provider_type specialty sex status ohip_no billing_no practitionerNo email
100 Sarah Anderson doctor Family Medicine F 1 123456 123456 CPSO-12345 dr.anderson@clinic.com
101 Michael Chen doctor Family Medicine M 1 234567 234567 CPSO-23456 dr.chen@clinic.com
102 Priya Patel doctor Pediatrics F 1 345678 345678 CPSO-34567 dr.patel@clinic.com
10 Sarah Lee doctor Internal Medicine F 1 456789 456789 CPSO-45678 dr.lee@clinic.com

Required secUserRole Records

Each provider must have this role entry — without it, REST OAuth fails:

provider_no role_name orgcd activeyn
100 doctor R0000001 1
101 doctor R0000001 1
102 doctor R0000001 1
10 doctor R0000001 1

Required program_provider Records

Each provider must be linked to the OSCAR Service program:

provider_no program_id
100 10034
101 10034
102 10034
10 10034

Schedule Templates

All templates use 96-character timecodes (15 min/slot). See slot duration formula for details.

Active Templates on This Instance

provider_no name summary Timecode Decoded
100 FullDay 9AM-5PM w/ lunch _x36 09-12:1x12 12-1:Lx4 13-17:1x16 _x28
100 9-5 9AM-5PM w/ lunch (same as FullDay)
100 clinic 9-12, 2-5 _x36 09-12:1x12 12-1:Lx4 13-14:_x4 14-17:1x12 _x28
100 Monday-Wednesday Mon-Wed schedule (same as FullDay)
100 Thursday-Friday Thu-Fri schedule (same as clinic)
101 FullDay 8:30AM-4:30PM w/ lunch _x34 08:30-12:1x14 12-1:Lx4 13-16:30:1x14 _x30
101 8:30-4:30 8:30AM-4:30PM w/ lunch (same as FullDay)
101 clinic 9-12, 2-5 (same as 100 clinic)
101 Monday-Thursday Mon-Thu schedule (same as FullDay)
101 Friday Fri schedule (same as 100 clinic)
102 FullDay 10AM-5PM w/ lunch _x40 10-12:1x8 12-1:Lx4 13-17:1x16 _x28
102 10-6 10AM-5PM w/ lunch (same as FullDay)
102 clinic 10-12, 1-5 (same as FullDay)
102 Monday-Friday Mon-Fri schedule (same as FullDay)
Public P:OnCallClinic Wknd/Holiday _x40 10-14:Cx16 _x40
Public P:Regular 9 to 5 Standard day _x36 09-12:1x12 12-1:_x4 13-18:1x20 _x24

Scheduledate Assignments

4 weeks rolling from today. For each provider, assign day-of-week templates to weekdays:

sdate provider_no hour (template name) available status creator
Mon-Fri dates 100 Day name (e.g. "Monday-Wednesday") 1 A oscaralignload
Mon-Fri dates 101 Day name (e.g. "Monday-Thursday") 1 A oscaralignload
Mon-Fri dates 102 Day name (e.g. "Monday-Friday") 1 A oscaralignload
Sat-Sun dates — (no entry = no schedule) — — —

Dynamic Dates

Schedule dates are generated relative to the current date. The automation script creates 4 weeks of weekday entries, always looking forward.

Demographic Set

12 patients with diverse Canadian demographics:

# demographic_no firstName lastName sex DOB hin hcType providerNo phone city province
1 15 John Smith M 1980-05-15 9876543210 BC 100 604-555-0101 Vancouver BC
2 16 Sarah Johnson F 1975-11-22 9876543211 BC 100 604-555-0102 Burnaby BC
3 17 Emma Williams F 1992-03-08 9876543212 BC 102 604-555-0103 Surrey BC
4 18 Robert Brown M 1965-09-30 9876543213 BC 100 604-555-0104 Richmond BC
5 19 Maria Garcia F 1978-07-18 9876543214 BC 101 604-555-0501 Surrey BC
6 20 David Thompson M 1988-01-25 9876543215 BC 100 604-555-0106 Vancouver BC
7 21 Sofia Martinez F 1995-12-03 9876543216 BC 101 604-555-0107 Coquitlam BC
8 22 William Li M 1972-06-14 9876543217 BC 100 604-555-0108 Vancouver BC
9 23 Lisa Nguyen F 1985-08-27 9876543218 BC 101 604-555-0109 Burnaby BC
10 24 Robert Wilson M 1960-04-11 9876543219 BC 102 604-555-0110 New Westminster BC
11 25 Aisha Khan F 1990-10-05 9876543220 BC 101 604-555-0111 Vancouver BC
12 26 James O'Brien M 1983-02-19 9876543221 BC 100 604-555-0112 Victoria BC

Each patient must have:

  • patientStatus = 'AC'
  • rosterStatus = 'RO'
  • Valid address with BC postal code (V**)
  • Matching admission record with program_id=10034, admission_status='current'

Appointment Set

12 sample bookings, rolling weekly:

appointmentDate startTime duration providerNo demographicNo type reason status
Next Monday 09:00 15 100 15 (John Smith) Office Visit General consultation t
Next Monday 09:30 30 100 18 (Robert Brown) Annual Physical Preventive care t
Next Monday 10:00 20 101 19 (Maria Garcia) Consult Consultation t
Next Monday 14:00 15 101 23 (Lisa Nguyen) Follow-up Follow-up care t
Next Tuesday 10:00 30 102 17 (Emma Williams) Well-Child Preventive care t
Next Tuesday 10:00 15 100 20 (David Thompson) Medication Review Medication management t
Next Wednesday 09:00 20 101 21 (Sofia Martinez) Prenatal Prenatal care t
Next Wednesday 11:00 30 100 26 (James O'Brien) New Patient New patient registration t
Next Thursday 10:00 15 102 24 (Robert Wilson) Chronic Disease Chronic care t
Next Thursday 14:00 30 101 25 (Aisha Khan) Mental Health Mental health consultation t
Next Friday 09:00 10 100 16 (Sarah Johnson) Lab Review Follow-up t
Next Friday 10:00 15 100 22 (William Li) Urgent Care Acute care t

All appointments tagged with creator='oscaralignload' for idempotent reload.

OAuth Clients

Production Client

Field Value
name VitaraPlatform
clientKey vitara_c359e856e68211f09e4d4e96584a2d2c
uri https://vitara.digiconsult.ca
lifetime 3600 (1 hour)

Test Client

Field Value
name vitaraPostman
clientKey 9qk5p7145leds3b1
uri https://oscar.digiconsult.ca:8443
lifetime 36000 (10 hours)

Validation Checklist

These 8 checks must all pass after data load:

CHECK 1: Provider-Role Integrity

Every active provider has secUserRole(doctor, R0000001, activeyn=1):

SELECT p.provider_no, p.first_name, p.last_name,
       s.role_name, s.orgcd, s.activeyn
FROM provider p
LEFT JOIN secUserRole s
  ON p.provider_no = s.provider_no AND s.role_name = 'doctor'
WHERE p.status = '1' AND p.provider_type = 'doctor'
-- EXPECT: No NULL role_name, orgcd, or activeyn

CHECK 2: Patient-Admission Integrity

Every active demographic has admission(program_id=10034, status='current'):

SELECT d.demographic_no, d.first_name, d.last_name,
       a.program_id, a.admission_status
FROM demographic d
LEFT JOIN admission a
  ON d.demographic_no = a.client_id AND a.admission_status = 'current'
WHERE d.patient_status = 'AC'
-- EXPECT: No NULL program_id or admission_status

CHECK 3: Demographic-Provider Reference

Every demographic.provider_no references a valid provider:

SELECT d.demographic_no, d.provider_no
FROM demographic d
LEFT JOIN provider p ON d.provider_no = p.provider_no
WHERE d.provider_no IS NOT NULL
  AND d.provider_no != ''
  AND p.provider_no IS NULL
-- EXPECT: 0 rows

CHECK 4: Schedule-Template Reference

Every scheduledate.hour references a valid scheduletemplate.name:

SELECT sd.id, sd.sdate, sd.provider_no, sd.hour
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.status = 'A' AND st.name IS NULL
-- EXPECT: 0 rows

CHECK 5: Appointment Reference Integrity

Every appointment references valid provider AND demographic:

SELECT a.appointment_no, a.provider_no, a.demographic_no
FROM appointment a
LEFT JOIN provider p ON a.provider_no = p.provider_no
LEFT JOIN demographic d ON a.demographic_no = d.demographic_no
WHERE (p.provider_no IS NULL OR d.demographic_no IS NULL)
  AND a.demographic_no != 0
-- EXPECT: 0 rows

CHECK 6: secUserRole Data Quality

No secUserRole rows with NULL activeyn or empty orgcd:

SELECT * FROM secUserRole
WHERE activeyn IS NULL OR orgcd IS NULL OR orgcd = ''
-- EXPECT: 0 rows

CHECK 7: REST Demographics Smoke Test

REST GET /demographics/{id} returns 200 for all active patients:

For each AC demographic, OAuth GET must return HTTP 200, not 500

CHECK 8: REST Appointment Creation Smoke Test

REST POST /schedule/add succeeds for valid provider+demo+date:

Test with known good provider, demographic, future date within schedule

Automation: oscar-align-load

The oscar-align-load.sh script at /home/ubuntu/scripts/oscar-align-load.sh automates the full data alignment lifecycle:

Phase What It Does
Phase 1: Pre-flight Validates DB connectivity, program 10034, template codes; reports current counts
Phase 2: Alignment Ensures providers 100/101/102/10 exist with correct secUserRole and program_provider links; fixes orphan demographics and appointments referencing non-existent providers; fixes NULL activeyn / empty orgcd globally
Phase 3: Load Loads 26 schedule templates (96-char), 4 weeks of schedule dates, 12 patients (demographic_no 15-26) with admissions, 12 sample appointments
Phase 4: Verification Runs all 8 validation checks above; reports PASS/FAIL for each

Usage:

bash /home/ubuntu/scripts/oscar-align-load.sh

Key properties:

  • Fully idempotent — safe to re-run at any time
  • Appointments tagged creator='oscaralignload' are deleted and re-created on each run (no duplicates)
  • Schedule dates use INSERT IGNORE (no duplicates)
  • Demographics use ON DUPLICATE KEY UPDATE (updates existing records)
  • Automatically creates missing admission records for all active patients, not just the 12 demo ones
  • Dynamic dates: schedules and appointments generated relative to current date