Database Schema

PostgreSQL 16 with Prisma ORM -- 15 Models, 9 Enums

Version: v4.3.0 | Last verified: 2026-03-09

---

Database Configuration

Setting	Value
Database	vitara_platform
User	vitara
Port	5432
Version	PostgreSQL 16
ORM	Prisma 5.22
Driver	@prisma/client
Schema	admin-dashboard/server/prisma/schema.prisma

---

Schema Overview

+------------------------------------------------------------------------------+
|                    VITARA DATABASE -- 15 MODELS (Prisma ORM)                 |
+------------------------------------------------------------------------------+
|                                                                              |
|  CORE (8 models)                                                             |
|  +-----------+                                                               |
|  |  Clinic   |--+--------+--------+--------+--------+--------+-------+      |
|  | id, name  |  |        |        |        |        |        |       |      |
|  | slug, tz  |  |        |        |        |        |        |       |      |
|  +-----+-----+  |        |        |        |        |        |       |      |
|        |         |        |        |        |        |        |       |      |
|  1:1   | 1:N     | 1:N    | 1:N    | 1:N    | 1:N    | 1:N    | 1:N  |      |
|  v     v         v        v        v        v        v        v      |      |
| +------+ +------+ +------+ +------+ +------+ +------+ +------+ +--+ |      |
| |Clinic| |Clinic| |Clinic| |Clinic| |Wait- | |Call  | | User | |Fax||      |
| |Config| |Hours | |Holi- | |Prov- | |list  | |Log   | |      | |Doc||      |
| |      | |      | |day   | |ider  | |Entry | |      | |      | |   ||      |
| |80    | |7 days| |date  | |voice | |      | |+SMS  | |Role  | |AI ||      |
| |fields| |open/ | |clos- | |book  | |Status| |fields| |      | |   ||      |
| |      | |close | |ures  | |v3.1  | |      | |      | |      | |   ||      |
| +------+ +------+ +------+ +------+ +------+ +------+ +------+ +--+ |      |
|                                                                              |
|  COMPLIANCE (1 model)                                                        |
|  +-------------------+                                                       |
|  |    AuditLog       |  Immutable trail of all mutations                    |
|  |  userId, action,  |  POST/PUT/DELETE auto-captured                       |
|  |  resource, IP     |  Passwords/secrets redacted                          |
|  +-------------------+                                                       |
|                                                                              |
|  ONBOARDING (1 model)                                                        |
|  +-------------------+                                                       |
|  |OnboardingProgress |  8 boolean flags + currentStep                       |
|  | per clinic_id     |  Pre-launch checklist gate                           |
|  +-------------------+                                                       |
|                                                                              |
|  SUPPORT (3 models)                                                          |
|  +-------------------+                                                       |
|  | SupportTicket     |---- TicketMessage[]                                  |
|  | priority, status  |     content, isInternal, authorId                    |
|  +-------------------+                                                       |
|  +-------------------+                                                       |
|  | Notification      |  Per-user, auto-created on ticket events             |
|  | type, title, read |                                                       |
|  +-------------------+                                                       |
|                                                                              |
|  CACHE (1 model)                                                             |
|  +-------------------+                                                       |
|  |PatientPhoneCache  |  Phone → OSCAR demographicNo lookup                  |
|  | clinicId, phone   |  Repeat caller instant resolution                    |
|  +-------------------+                                                       |
|                                                                              |
+------------------------------------------------------------------------------+

---

Model Definitions

Clinic

Primary clinic registration table. All multi-tenant data branches from this model.

Table: clinics

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key (UUID)
name	String	required	Clinic display name
slug	String	@unique	URL-safe identifier
phone	String?	optional	Main clinic phone number
phones	Json?	@default("[]")	Array of additional phone numbers
vapiPhone	String?	@unique	Vapi phone number assigned to clinic
address	String?	optional	Street address
city	String?	optional	City
province	String	@default("BC")	Province code
postalCode	String?	optional	Postal code
timezone	String	@default("America/Vancouver")	IANA timezone
status	ClinicStatus	@default(active)	Clinic lifecycle status
createdAt	DateTime	@default(now())	Row creation timestamp
updatedAt	DateTime	@updatedAt	Auto-updated on change

Relations:

Relation	Model	Cardinality
users	User[]	1:N
config	ClinicConfig?	1:1
hours	ClinicHours[]	1:N (7 max)
holidays	ClinicHoliday[]	1:N
providers	ClinicProvider[]	1:N
waitlist	WaitlistEntry[]	1:N
callLogs	CallLog[]	1:N

---

ClinicConfig

Per-clinic configuration. The largest model with 80 fields spanning 11 subsections.

Table: clinic_config

Primary key: clinicId (1:1 with Clinic)

┌──────────────────────────────────────────────────────────┐
│                    ClinicConfig (80 fields)               │
├──────────────────────────────────────────────────────────┤
│                                                          │
│  ┌─────────────┐  ┌──────────────┐  ┌──────────────┐    │
│  │ EMR         │  │ Registration │  │ Scheduling   │    │
│  │ Connection  │  │ Settings     │  │ Settings     │    │
│  │ (15 fields) │  │ (3 fields)   │  │ (4 fields)   │    │
│  └─────────────┘  └──────────────┘  └──────────────┘    │
│                                                          │
│  ┌─────────────┐  ┌──────────────┐  ┌──────────────┐    │
│  │ Voice/Vapi  │  │ Privacy      │  │ Data         │    │
│  │ Integration │  │ Officer      │  │ Retention    │    │
│  │ (9 fields)  │  │ (3 fields)   │  │ (2 fields)   │    │
│  └─────────────┘  └──────────────┘  └──────────────┘    │
│                                                          │
│  ┌─────────────┐  ┌──────────────┐  ┌──────────────┐    │
│  │ BAA/DPA     │  │ Clinical     │  │ OSCAR Config │    │
│  │ Status      │  │ Control      │  │ Pull         │    │
│  │ (4 fields)  │  │ (9 fields)   │  │ (7 fields)   │    │
│  └─────────────┘  └──────────────┘  └──────────────┘    │
│                                                          │
│  ┌─────────────┐  ┌──────────────┐                      │
│  │ v3.1 Voice  │  │ SMS Service  │                      │
│  │ Booking     │  │ (9 fields)   │                      │
│  │ (15 fields) │  │              │                      │
│  └─────────────┘  └──────────────┘                      │
│                                                          │
└──────────────────────────────────────────────────────────┘

EMR Connection Fields (15)

Field	Type	Constraints	Description
clinicId	String	@id	FK to Clinic (also PK)
emrType	String	@default("oscar-universal")	EMR type: oscar-universal, oscar-pro, telus-health, juno
oscarUrl	String?	optional	OSCAR instance base URL
oscarConsumerKey	String?	optional	OAuth 1.0a consumer key
oscarConsumerSecretEnc	String?	optional	AES-256-GCM encrypted consumer secret
oscarTokenKey	String?	optional	OAuth 1.0a token key
oscarTokenSecretEnc	String?	optional	AES-256-GCM encrypted token secret
oscarConnectionVerified	Boolean	@default(false)	Whether OSCAR connection has been tested
oscarLastVerifiedAt	DateTime?	optional	Last successful connection test
oscarVersion	String?	optional	Detected OSCAR version
oscarLastSync	DateTime?	optional	Last provider/schedule sync timestamp
oscarSoapUsername	String?	optional	Per-clinic SOAP WS-Security username
oscarSoapPasswordEnc	String?	optional	AES-256-GCM encrypted SOAP password
oscarTlsVerify	Boolean	@default(true)	Whether to verify TLS certificates
oscarAdmissionProgramId	Int?	optional	OSCAR patient registration program ID
oscarOauthTokenExpiresAt	DateTime?	optional	OAuth token expiry (~4-day TTL)
oscarPreferRest	Boolean	@default(false)	Use OAuth REST over SOAP (for Kai/CF instances)

Registration Settings (3)

Field	Type	Constraints	Description
acceptingNewPatients	Boolean	@default(true)	Whether clinic accepts new patients
waitlistEnabled	Boolean	@default(true)	Whether waitlist feature is active
maxNewPatientsPerWeek	Int?	optional	Weekly cap on new patient registrations

Scheduling Settings (4)

Field	Type	Constraints	Description
appointmentDuration	Int	@default(15)	Default appointment duration in minutes
bufferTime	Int	@default(5)	Buffer between appointments in minutes
maxAdvanceBookingDays	Int	@default(30)	How far ahead patients can book
minAdvanceBookingHours	Int	@default(2)	Minimum notice for booking

Voice / Vapi Integration (9)

Field	Type	Constraints	Description
defaultLanguage	String	@default("en")	Default voice agent language
supportedLanguages	String[]	@default(["en", "zh"])	Languages available for voice agent
vapiAssistantId	String?	@unique	Vapi assistant UUID
vapiSquadId	String?	optional	Vapi squad UUID
vapiWebhookSecret	String?	optional	HMAC signing secret for Vapi webhooks
vapiConnectionVerified	Boolean	@default(false)	Whether Vapi connection has been tested
vapiLastVerifiedAt	DateTime?	optional	Last successful Vapi connection test
voiceAgentEnabled	Boolean	@default(true)	Master switch to enable/disable voice agent

Privacy Officer -- PIPEDA 4.1.1 (3)

Field	Type	Constraints	Description
privacyOfficerName	String?	optional	Name of designated privacy officer
privacyOfficerEmail	String?	optional	Privacy officer email
privacyOfficerPhone	String?	optional	Privacy officer phone

Data Retention -- PIPEDA 4.5 (2)

Field	Type	Constraints	Description
transcriptRetentionDays	Int?	optional	Days to keep transcripts (default 90)
callLogRetentionDays	Int?	optional	Days to keep call logs (default 365)

BAA/DPA Status -- PIPEDA 4.1.3 (4)

Field	Type	Constraints	Description
baaVapiSigned	Boolean	@default(false)	Whether Vapi BAA is signed
baaVapiSignedAt	DateTime?	optional	Vapi BAA signature date
baaHostingSigned	Boolean	@default(false)	Whether hosting DPA is signed
baaHostingSignedAt	DateTime?	optional	Hosting DPA signature date

Clinical Control (9)

Field	Type	Constraints	Description
customGreeting	String?	optional	English greeting for voice agent
customGreetingZh	String?	optional	Chinese greeting for voice agent
transferPhoneNumber	String?	optional	Human handoff destination number
defaultProviderId	String?	optional	Provider used when caller says "any doctor"
preferredPharmacy	String?	optional	Preferred pharmacy name
preferredPharmacyAddr	String?	optional	Preferred pharmacy address
preferredPharmacyFax	String?	optional	Preferred pharmacy fax number
appointmentTypeMappings	Json?	optional	Per-clinic appointment type code mappings

OSCAR Config Pull (7)

Field	Type	Constraints	Description
scheduleTemplateCodes	Json?	optional	Schedule template codes. Shape: { codes: [{code, description, duration, bookinglimit, confirm, bookable}], pulledAt: ISO }
appointmentStatuses	Json?	optional	Appointment status definitions. Shape: { statuses: [{code, description, active}], pulledAt: ISO }
clinicSites	Json?	optional	Clinic sites/locations. Shape: { sites: [{id, name, address, phone, active}], pulledAt: ISO }
bookableScheduleCodes	String[]	@default([])	Schedule codes that allow voice booking
blockedScheduleCodes	String[]	@default([])	Schedule codes blocked from voice booking
oscarConfigPulledAt	DateTime?	optional	Timestamp of last OSCAR config pull

v3.1 Voice Booking Configuration (15)

Clinic-level defaults for the provider config inheritance chain (defaults → clinic → provider).

Field	Type	Constraints	Description
askReasonBeforeSearch	Boolean	@default(false)	Ask visit reason before searching
defaultAppointmentTypeCode	String	@default("B")	Default OSCAR appointment type code
defaultSlotDuration	Int	@default(15)	Default slot duration in minutes
defaultVisitMode	String	@default("both")	"in_person" / "virtual" / "both"
enabledVisitReasons	String[]	@default(["checkup", "follow_up", "sick_visit", "consultation"])	Allowed visit reasons
providerFallbackBehavior	String	@default("best_available")	"best_available" / "same_specialty" / "ask_patient" / "waitlist_only"
maxFallbackProviders	Int	@default(3)	Max providers to try in fallback
fallbackSearchWindowDays	Int	@default(14)	Days to search for fallback slots
allowSameDayBooking	Boolean	@default(true)	Whether same-day appointments are allowed
allowWeekendBooking	Boolean	@default(false)	Whether weekend appointments are allowed
requireDOBVerification	Boolean	@default(true)	Whether DOB verification is required
maxApptsPerPatientPerDay	Int	@default(1)	Maximum appointments per patient per day
cancellationMinNoticeHours	Int	@default(24)	Minimum hours notice for cancellation
newPatientRequiresLongerSlot	Boolean	@default(true)	Whether new patients need longer slots
newPatientMinDuration	Int	@default(30)	Minimum duration (min) for new patients

SMS Service (9)

Platform-level Telnyx credentials via environment variables; per-clinic sender numbers and templates.

Field	Type	Constraints	Description
smsEnabled	Boolean	@default(false)	Master SMS switch per clinic
smsSenderNumber	String?	optional	Per-clinic sender phone number
smsTemplateBookEn	String?	optional	Custom English booking template
smsTemplateBookZh	String?	optional	Custom Chinese booking template
smsTemplateCancelEn	String?	optional	Custom English cancellation template
smsTemplateCancelZh	String?	optional	Custom Chinese cancellation template
smsTemplateRescheduleEn	String?	optional	Custom English reschedule template
smsTemplateRescheduleZh	String?	optional	Custom Chinese reschedule template
smsInboundWebhookEnabled	Boolean	@default(false)	Enable inbound SMS webhook processing

Metadata (1)

Field	Type	Constraints	Description
updatedAt	DateTime	@updatedAt	Auto-updated on change

Relation: clinic -> Clinic (FK on clinicId, onDelete: RESTRICT)

---

ClinicHours

Working hours per day of week. Up to 7 rows per clinic (one per weekday).

Table: clinic_hours

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
clinicId	String	FK to Clinic	Owning clinic
dayOfWeek	Int	0-6	Day of week (0=Sunday, 6=Saturday)
openTime	String?	optional	Opening time, e.g. "09:00"
closeTime	String?	optional	Closing time, e.g. "17:00"
isClosed	Boolean	@default(false)	Whether clinic is closed this day

Unique constraint: @@unique([clinicId, dayOfWeek])

Relation: clinic -> Clinic (FK on clinicId, onDelete: RESTRICT)

---

ClinicHoliday

Holiday closures per clinic. One row per closure date.

Table: clinic_holidays

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
clinicId	String	FK to Clinic	Owning clinic
date	DateTime	@db.Date	Closure date (date only, no time)
name	String?	optional	Holiday name (e.g. "Christmas Day")
createdAt	DateTime	@default(now())	Row creation timestamp

Relation: clinic -> Clinic (FK on clinicId, onDelete: RESTRICT)

---

ClinicProvider

Provider records with OSCAR mapping, voice booking control, v3.1 config inheritance, and sync.

Table: clinic_providers

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
clinicId	String	FK to Clinic	Owning clinic
firstName	String	required	Provider first name
lastName	String	required	Provider last name
title	String	@default("Dr.")	Title/honorific
displayName	String?	optional	Override display name
specialty	String?	optional	Medical specialty
email	String?	optional	Contact email
phone	String?	optional	Contact phone
acceptingNewPatients	Boolean	@default(true)	Whether accepting new patients
languages	String[]	@default(["English"])	Languages spoken
status	ProviderStatus	@default(active)	Provider availability status
appointmentTypes	String[]	@default([])	Supported appointment type codes
color	String	@default("#3B82F6")	Calendar display color (hex)
oscarProviderId	String?	optional	Maps to OSCAR provider number
voiceBookable	Boolean	@default(true)	Whether voice agent can book for this provider
maxAdvanceBookingDays	Int?	optional	Provider-level override for max advance booking
lastSyncedAt	DateTime?	optional	Last OSCAR sync timestamp
visitMode	String	@default("both")	Provider visit mode: "in_person" / "virtual" / "both"
acceptsWalkIns	Boolean	@default(false)	Whether provider accepts walk-ins
scheduleCodeConfig	Json?	optional	{codes: ["A","B"]} or null=inherit from clinic
appointmentTypeOverrides	Json?	optional	{visitReason: code} merges with clinic mappings
createdAt	DateTime	@default(now())	Row creation timestamp
updatedAt	DateTime	@updatedAt	Auto-updated on change

Unique constraint: @@unique([clinicId, oscarProviderId])

Relation: clinic -> Clinic (FK on clinicId, onDelete: RESTRICT)

3-Level Config Inheritance

Provider config uses a 3-level inheritance chain: system defaults → clinic config → provider overrides. The effective-config endpoint (GET /api/clinic/providers/:id/effective-config) returns the merged result. Fields like visitMode, scheduleCodeConfig, and appointmentTypeOverrides override clinic-level defaults when set.

---

WaitlistEntry

New patient waitlist entries. Created when a clinic has waitlistEnabled: true and a caller requests registration.

Table: waitlist

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
clinicId	String	FK to Clinic	Owning clinic
firstName	String	required	Patient first name
lastName	String	required	Patient last name
phone	String	required	Patient phone number
email	String?	optional	Patient email
notes	String?	optional	Freeform notes
status	WaitlistStatus	@default(pending)	Waitlist progression status
createdAt	DateTime	@default(now())	Row creation timestamp
contactedAt	DateTime?	optional	When patient was contacted
registeredAt	DateTime?	optional	When patient completed registration

Indexes: @@index([clinicId]), @@index([status])

Relation: clinic -> Clinic (FK on clinicId, onDelete: RESTRICT)

---

CallLog

Voice call analytics and outcomes. Populated from Vapi end-of-call-report webhooks.

Table: call_logs

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
clinicId	String	FK to Clinic	Owning clinic
vapiCallId	String?	optional	Vapi call UUID
callerPhone	String?	optional	Caller phone number
demographicId	Int?	optional	OSCAR patient demographic ID
language	String?	optional	Call language ("en", "zh")
intent	CallIntent?	optional	Detected call intent
outcome	CallOutcome?	optional	Call resolution outcome
appointmentId	Int?	optional	OSCAR appointment number if booked
durationSeconds	Int?	optional	Call duration in seconds
cost	Float?	optional	Vapi call cost in USD
transcript	String?	optional	Full call transcript (retention-managed)
summary	String?	optional	AI-generated call summary
transferredToStaff	Boolean	@default(false)	Whether call was transferred to staff
transferReason	String?	optional	Reason for staff transfer
smsConsent	Boolean?	optional	Whether caller consented to SMS confirmation
smsSentAt	DateTime?	optional	When SMS confirmation was sent
createdAt	DateTime	@default(now())	Row creation timestamp

Indexes: @@index([clinicId]), @@index([createdAt]), @@index([vapiCallId])

Relation: clinic -> Clinic (FK on clinicId, onDelete: RESTRICT)

SMS Consent Tracking

smsConsent records whether the caller said "yes" to SMS during the voice call. smsSentAt records when the actual SMS was delivered via Telnyx. These fields were added as part of the SMS booking confirmation feature (v4.2.0).

---

User

Dashboard users. Supports two roles: platform admin and clinic manager.

Table: users

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
email	String	@unique	Login email
passwordHash	String	required	bcrypt hash (cost factor 12)
firstName	String	required	First name
lastName	String	required	Last name
role	UserRole	@default(clinic_manager)	Authorization role
isActive	Boolean	@default(true)	Whether account is active
clinicId	String?	FK to Clinic (nullable)	Owning clinic (null for vitara_admin)
createdAt	DateTime	@default(now())	Row creation timestamp
updatedAt	DateTime	@updatedAt	Auto-updated on change

Relation: clinic -> Clinic? (FK on clinicId, onDelete: SET NULL)

Role	Access
vitara_admin	All clinics, all endpoints, system settings
clinic_manager	Own clinic only, self-service endpoints

---

AuditLog

Immutable audit trail. Auto-captured by auditMiddleware on all POST/PUT/DELETE requests. Passwords and credential fields are automatically redacted before storage.

Table: audit_logs

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
userId	String?	optional	Acting user ID
userEmail	String	required	Acting user email
action	String	required	HTTP method + path (e.g. "POST /api/clinic/providers")
resource	String	required	Resource path (e.g. "clinic/providers")
resourceId	String?	optional	Affected resource ID
clinicId	String?	optional	Clinic context
details	Json?	optional	Redacted request body, params, status code
ipAddress	String?	optional	Client IP address
userAgent	String?	optional	Client user agent string
createdAt	DateTime	@default(now())	Row creation timestamp

Indexes: @@index([clinicId]), @@index([userId]), @@index([createdAt]), @@index([action])

Compliance

Satisfies PIPEDA 4.1.4 accountability requirement. This table is append-only -- no UPDATE or DELETE operations are permitted at the application layer. Retention: 7 years minimum.

---

OnboardingProgress

Tracks 4-step onboarding wizard completion per clinic. Created automatically when a new clinic is provisioned.

Table: onboarding_progress

Field	Type	Constraints	Description
clinicId	String	@id	FK to Clinic (also PK)
currentStep	Int	@default(1)	Current wizard step (1-4)
clinicInfoDone	Boolean	@default(false)	Step 1: Clinic info completed
hoursDone	Boolean	@default(false)	Step 3: Business hours configured
providersDone	Boolean	@default(false)	Providers added
emrConnected	Boolean	@default(false)	Step 2: EMR connection verified
vapiAssigned	Boolean	@default(false)	Vapi phone/squad assigned
testCallPassed	Boolean	@default(false)	Test call completed
privacyOfficerSet	Boolean	@default(false)	Privacy officer designated
baaSigned	Boolean	@default(false)	BAA/DPA agreements signed
completedAt	DateTime?	optional	When onboarding was completed
createdAt	DateTime	@default(now())	Row creation timestamp
updatedAt	DateTime	@updatedAt	Auto-updated on change

4-step wizard flow: Clinic Details -> EMR Connection -> Business Hours -> Validation

Go-live gate: After completeOnboarding() sets completedAt, admin is notified for provisioning (phone assignment + activation).

---

SupportTicket

Clinic support tickets with priority and status workflow.

Table: support_tickets

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
clinicId	String	required	Clinic that opened the ticket
createdById	String	required	User who created the ticket
assigneeId	String?	optional	Admin assigned to handle it
title	String	required	Ticket subject
description	String	required	Detailed description
priority	TicketPriority	@default(medium)	Ticket priority level
status	TicketStatus	@default(open)	Current ticket status
createdAt	DateTime	@default(now())	Row creation timestamp
updatedAt	DateTime	@updatedAt	Auto-updated on change

Relations: messages -> TicketMessage[] (1:N, cascade delete)

Indexes: @@index([clinicId]), @@index([status]), @@index([assigneeId])

Status workflow: open -> in_progress -> waiting -> resolved -> closed

---

TicketMessage

Threaded messages on support tickets. Supports internal admin-only notes.

Table: ticket_messages

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
ticketId	String	FK to SupportTicket	Parent ticket
authorId	String	required	User who wrote the message
content	String	required	Message body
isInternal	Boolean	@default(false)	Internal admin-only note
createdAt	DateTime	@default(now())	Row creation timestamp

Index: @@index([ticketId])

Relation: ticket -> SupportTicket (FK on ticketId, onDelete: Cascade)

Security

isInternal is enforced server-side. Clinic manager routes always set isInternal: false regardless of client input. Admin routes allow both values. Clinic managers never see internal messages in API responses.

---

Notification

Per-user notifications, auto-created on ticket events.

Table: notifications

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
userId	String	required	Target user
type	String	required	Event type (e.g. "ticket_update", "system")
title	String	required	Notification title
message	String	required	Notification body
link	String?	optional	Deep link to relevant page
read	Boolean	@default(false)	Whether notification has been read
createdAt	DateTime	@default(now())	Row creation timestamp

Indexes: @@index([userId]), @@index([read])

Auto-creation triggers:

Event	Notified Users
Ticket created	All vitara_admin users
Admin replies	Ticket creator
Clinic replies	Ticket assignee (or all admins if unassigned)
Status change	Ticket creator

---

FaxDocument

AI-processed fax documents with extraction results and patient matching.

Table: fax_documents

Field	Type	Constraints	Description
id	String	@id @default(uuid())	Primary key
clinicId	String	FK to Clinic	Owning clinic
status	FaxStatus	@default(pending)	Processing pipeline status
source	String	@default("upload")	Ingestion source: "upload", "poll", "seed"
originalFilename	String?	optional	Original uploaded filename
filePath	String	required	Server filesystem path to stored file
pageCount	Int?	optional	Number of pages in document
extractedData	Json?	optional	Full AI extraction result (structured JSON)
extractionProvider	String?	optional	AI provider: "anthropic", "openai", "google"
extractionModel	String?	optional	AI model used (e.g. claude-sonnet-4-6, gpt-4o-mini)
extractionCostCents	Float?	optional	API cost in cents
matchedPatientId	String?	optional	OSCAR patient demographic ID if matched
matchedPatientName	String?	optional	Matched patient display name
matchConfidence	Float?	optional	Match confidence (0.0-1.0)
matchMethod	String?	optional	How matched: phn_exact, name_dob, name_only
matchVerified	Boolean	@default(false)	Whether match has been verified
matchVerifiedBy	String?	optional	User who verified the match
matchVerifiedAt	DateTime?	optional	When match was verified
senderFaxNumber	String?	optional	Sending fax number
receivedAt	DateTime?	optional	When fax was received
processedAt	DateTime?	optional	When AI processing completed
errorMessage	String?	optional	Error details if status=failed
createdAt	DateTime	@default(now())	Row creation timestamp
updatedAt	DateTime	@updatedAt	Auto-updated on change

Indexes: @@index([clinicId]), @@index([status]), @@index([matchedPatientId])

Relation: clinic -> Clinic (FK on clinicId, onDelete: Restrict)

Status workflow: pending -> processing -> extracted -> matched/verified -> archived (or failed at any step)

---

PatientPhoneCache

Interim workaround for OSCAR quickSearch requiring Lucene index rebuild. Maps caller phone to OSCAR demographicNo so repeat callers get instant lookup via getPatient(id) instead of relying on quickSearch.

Table: patient_phone_cache

Field	Type	Constraints	Description
id	String	@id @default(cuid())	Primary key (CUID)
clinicId	String	required	Owning clinic
phone	String	required	Normalized phone (last 10 digits)
demographicNo	Int	required	OSCAR patient demographic number
patientName	String?	optional	Cached patient display name
lastSeen	DateTime	@default(now())	Last time this caller was seen

Unique constraint: @@unique([clinicId, phone])

Index: @@index([clinicId, phone])

---

Enums

UserRole

Value	Description
vitara_admin	Platform administrator -- access to all clinics and system settings
clinic_manager	Clinic staff -- access limited to own clinic

ClinicStatus

Value	Description
active	Clinic is live and accepting calls
inactive	Clinic is disabled (voice agent offline)
pending	Clinic registered but not yet activated

ProviderStatus

Value	Description
active	Provider is available for booking
inactive	Provider is not available
on_leave	Provider is temporarily unavailable

WaitlistStatus

Value	Description
pending	Entry created, not yet contacted
contacted	Patient has been contacted
registered	Patient completed registration
declined	Patient declined or did not respond

CallIntent

Value	Description
book	Book a new appointment
reschedule	Reschedule an existing appointment
cancel	Cancel an existing appointment
register	Register as a new patient
inquiry	General inquiry
other	Unclassified intent

CallOutcome

Value	Description
booked	Appointment was booked
rescheduled	Appointment was rescheduled
cancelled	Appointment was cancelled
registered	Patient was registered
waitlisted	Patient was added to waitlist
transferred	Call was transferred to staff
no_action	No action taken

FaxStatus

Value	Description
pending	Fax received, not yet processed
processing	AI extraction in progress
extracted	AI extraction complete
matched	Patient matched in OSCAR
verified	MOA confirmed or corrected the match
failed	Processing failed (see errorMessage)
archived	Fax archived after processing

TicketPriority

Value	Description
low	Non-urgent issue
medium	Standard priority (default)
high	Needs prompt attention
urgent	Critical issue requiring immediate response

TicketStatus

Value	Description
open	Ticket created, awaiting triage
in_progress	Being worked on by assigned admin
waiting	Waiting for clinic response
resolved	Issue resolved, pending confirmation
closed	Ticket closed

---

Entity Relationships

Foreign Key Summary

Child Table	FK Column	Parent Table	On Delete
users	clinic_id	clinics	SET NULL
clinic_config	clinic_id	clinics	RESTRICT
clinic_hours	clinic_id	clinics	RESTRICT
clinic_holidays	clinic_id	clinics	RESTRICT
clinic_providers	clinic_id	clinics	RESTRICT
waitlist	clinic_id	clinics	RESTRICT
call_logs	clinic_id	clinics	RESTRICT
fax_documents	clinic_id	clinics	RESTRICT
ticket_messages	ticket_id	support_tickets	CASCADE

Multi-Tenancy

All data is scoped to a clinic via clinicId foreign keys. The Clinic model is the root of the tenant hierarchy. Deleting a clinic is prevented by RESTRICT on all child FKs -- clinics must be set to inactive status instead.

The only exception is User, where clinicId is nullable (platform admins have no clinic) and uses SET NULL on delete.

Unique Constraints

Table	Columns	Purpose
clinics	slug	URL-safe clinic identifier
clinics	vapi_phone	One phone number per clinic
users	email	Login uniqueness
clinic_config	vapi_assistant_id	One assistant per clinic
clinic_hours	(clinic_id, day_of_week)	One hours entry per day per clinic
clinic_providers	(clinic_id, oscar_provider_id)	One OSCAR mapping per provider per clinic
patient_phone_cache	(clinic_id, phone)	One cache entry per phone per clinic

---

Indexes

Table	Index Columns	Type
waitlist	clinic_id	btree
waitlist	status	btree
call_logs	clinic_id	btree
call_logs	created_at	btree
call_logs	vapi_call_id	btree
audit_logs	clinic_id	btree
audit_logs	user_id	btree
audit_logs	created_at	btree
audit_logs	action	btree
support_tickets	clinic_id	btree
support_tickets	status	btree
support_tickets	assignee_id	btree
ticket_messages	ticket_id	btree
notifications	user_id	btree
notifications	read	btree
fax_documents	clinic_id	btree
fax_documents	status	btree
fax_documents	matched_patient_id	btree
patient_phone_cache	(clinic_id, phone)	btree

---

Migration History

Managed by Prisma Migrate. Migration files in admin-dashboard/server/prisma/migrations/.

Migration	Date	Description
20260128212804_init	2026-01-28	Initial schema: 6 enums, 10 tables (users, clinics, clinic_config, clinic_hours, clinic_holidays, clinic_providers, waitlist, call_logs). All FKs, unique constraints, and indexes.
20260206050700_add_unique_vapi_phone	2026-02-06	Added unique index on clinics.vapi_phone.
20260206053000_add_emr_type	2026-02-06	Added emr_type column to clinic_config with default "oscar-universal".
20260212120000_add_voice_agent_enabled_drop_squad_unique	2026-02-12	Added voice_agent_enabled column to clinic_config. Dropped unique constraint on vapi_squad_id.
20260214120000_add_oscar_config_fields	2026-02-14	Added OSCAR config pull fields, booking constraints, provider voice booking fields.

Remaining models

The audit_logs, onboarding_progress, support_tickets, ticket_messages, and notifications tables plus TicketPriority and TicketStatus enums were added in migrations applied before the init was consolidated. They are present in the current schema but their individual migration files were folded into the init migration during a squash.

Migration Commands

# Generate migration from schema changes
cd admin-dashboard/server
npx prisma migrate dev --name description_of_change

# Apply in production
npx prisma migrate deploy

# View current migration status
npx prisma migrate status

# Reset database (DESTROYS ALL DATA)
npx prisma migrate reset

---

Data Retention

Configurable per clinic via ClinicConfig. Enforcement runs daily at 3 AM UTC via node-cron in data-retention.ts.

Data Type	Default Retention	Action	Configurable Field
Call transcripts	90 days	Nulls transcript column on call_logs	transcriptRetentionDays
Call log rows	365 days	Deletes call_logs rows	callLogRetentionDays
Audit logs	7 years	No automatic deletion	N/A (compliance requirement)

Audit Log Retention

Audit logs are retained for 7 years minimum to satisfy PIPEDA 4.1.4 accountability requirements. There is no automatic deletion mechanism for audit logs. Manual purging requires documented compliance approval.

---

Backup & Recovery

Automated Backups

Daily automated backups via scripts/backup-db.sh (cron at 2:00 AM UTC). 14 daily copies are retained.

Backup location: /home/ubuntu/vitara-platform/backups/

# Manual full backup
pg_dump -U vitara -d vitara_platform | gzip > backup_$(date +%Y%m%d).sql.gz

# Schema only
pg_dump -U vitara -s vitara_platform > schema.sql

# Data only
pg_dump -U vitara -a vitara_platform > data.sql

Recovery

# Restore from compressed backup
gunzip -c backup_20260217.sql.gz | psql -U vitara vitara_platform

# After restore, verify Prisma migration state
cd admin-dashboard/server
npx prisma migrate status

Item	Value
Frequency	Daily at 2:00 AM UTC
Method	pg_dump (full logical backup)
Retention	14 daily copies
Location	/home/ubuntu/vitara-platform/backups/
Compression	gzip

---

Next Steps

• Server API Flows -- Request lifecycle, route map, middleware, caches, logging
• API Reference -- Endpoint details, request/response formats
• Platform Architecture -- System-level view
• Compliance Reference -- PIPEDA details