Database Schema

PostgreSQL 16 with Prisma ORM — 13 Models

Version: 4.0.0 Last Updated: 2026-02-15

---

Database Configuration

Setting	Value
Database	vitara_platform
User	vitara
Port	5432
Version	PostgreSQL 16
ORM	Prisma 5.22
Driver	@prisma/client

---

Schema Overview

┌─────────────────────────────────────────────────────────────────────────────┐
│                    VITARA DATABASE — 13 MODELS (Prisma ORM)                  │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  CORE (7 models)                                                            │
│  ┌─────────────────┐                                                        │
│  │     Clinic       │───────┬──────────┬──────────┬──────────┬───────┐     │
│  │  id, name, slug  │       │          │          │          │       │     │
│  │  status, timezone│       │          │          │          │       │     │
│  └────────┬────────┘       │          │          │          │       │     │
│           │ 1:1            │ 1:7      │ 1:N      │ 1:N      │ 1:N   │     │
│           ▼                ▼          ▼          ▼          ▼       ▼     │
│  ┌──────────────┐ ┌──────────┐ ┌─────────┐ ┌────────┐ ┌────────┐ ┌────┐│
│  │ ClinicConfig │ │ Clinic   │ │ Clinic  │ │Waitlist│ │CallLog │ │User││
│  │              │ │ Hours    │ │ Provider│ │ Entry  │ │        │ │    ││
│  │ 40+ fields   │ │ (7 days) │ │ OSCAR   │ │        │ │ Intent │ │Role││
│  │ EMR, Vapi,   │ │ open/    │ │ mapping │ │ Status │ │Outcome │ │    ││
│  │ Privacy, BAA,│ │ close    │ │ sync    │ │        │ │Transcr.│ │    ││
│  │ Retention,   │ │          │ │ status  │ │        │ │        │ │    ││
│  │ Greeting,    │ └──────────┘ └─────────┘ └────────┘ └────────┘ └────┘│
│  │ Pharmacy     │                                                        │
│  └──────────────┘    ┌───────────────┐                                   │
│                       │ClinicHoliday  │                                   │
│                       │ date closures │                                   │
│                       └───────────────┘                                   │
│                                                                              │
│  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│                                                       │
│  └──────────────────┘                                                       │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

---

Model Definitions

Clinic

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

model Clinic {
  id          String       @id @default(uuid())
  name        String
  slug        String       @unique
  phone       String?
  vapiPhone   String?      @unique @map("vapi_phone")
  address     String?
  city        String?
  province    String       @default("BC")
  postalCode  String?      @map("postal_code")
  timezone    String       @default("America/Vancouver")
  status      ClinicStatus @default(active)
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt

  // Relations (1:N)
  users       User[]
  config      ClinicConfig?
  hours       ClinicHours[]
  holidays    ClinicHoliday[]
  providers   ClinicProvider[]
  waitlist    WaitlistEntry[]
  callLogs    CallLog[]
}

Column	Type	Description
id	UUID	Primary key
name	String	Clinic display name
slug	String	URL-safe identifier (unique)
phone	String?	Main clinic phone
vapi_phone	String?	Vapi phone number (unique)
province	String	Default: BC
timezone	String	IANA timezone (default: America/Vancouver)
status	Enum	active, inactive, pending

---

ClinicConfig

Per-clinic configuration — the largest model with 40+ fields spanning EMR, Vapi, compliance, and clinical settings.

model ClinicConfig {
  clinicId              String    @id @map("clinic_id")

  // EMR
  emrType               String    @default("oscar-universal")
  oscarUrl              String?
  oscarConsumerKey      String?
  oscarConsumerSecretEnc String?  // AES-256-GCM encrypted
  oscarTokenKey         String?
  oscarTokenSecretEnc   String?   // AES-256-GCM encrypted
  oscarConnectionVerified Boolean @default(false)
  oscarLastVerifiedAt   DateTime?
  oscarVersion          String?
  oscarLastSync         DateTime?

  // Registration
  acceptingNewPatients  Boolean   @default(true)
  waitlistEnabled       Boolean   @default(true)
  maxNewPatientsPerWeek Int?

  // Scheduling
  appointmentDuration   Int       @default(15)
  bufferTime            Int       @default(5)
  maxAdvanceBookingDays Int       @default(30)
  minAdvanceBookingHours Int      @default(2)

  // Voice / Vapi
  defaultLanguage       String    @default("en")
  supportedLanguages    String[]  @default(["en", "zh"])
  vapiAssistantId       String?   @unique
  vapiSquadId           String?   @unique
  vapiWebhookSecret     String?
  vapiConnectionVerified Boolean  @default(false)
  vapiLastVerifiedAt    DateTime?

  // Privacy Officer (PIPEDA 4.1.1)
  privacyOfficerName    String?
  privacyOfficerEmail   String?
  privacyOfficerPhone   String?

  // Data Retention (PIPEDA 4.5)
  transcriptRetentionDays Int?    // default 90
  callLogRetentionDays  Int?      // default 365

  // BAA/DPA (PIPEDA 4.1.3)
  baaVapiSigned         Boolean   @default(false)
  baaVapiSignedAt       DateTime?
  baaHostingSigned      Boolean   @default(false)
  baaHostingSignedAt    DateTime?

  // Clinical Control (Theme 4)
  customGreeting        String?   // EN greeting for voice agent
  customGreetingZh      String?   // ZH greeting for voice agent
  transferPhoneNumber   String?   // Human handoff destination
  defaultProviderId     String?   // Used when caller says "any doctor"
  preferredPharmacy     String?
  preferredPharmacyAddr String?
  preferredPharmacyFax  String?
  appointmentTypeMappings Json?   // Per-clinic type code mappings
}

Field groups:

Group	Fields	Theme
EMR Connection	oscarUrl, oscarConsumerKey, oscarConsumerSecretEnc, oscarTokenKey, oscarTokenSecretEnc, oscarConnectionVerified	Core
Scheduling	appointmentDuration, bufferTime, maxAdvanceBookingDays, minAdvanceBookingHours	Core
Registration	acceptingNewPatients, waitlistEnabled, maxNewPatientsPerWeek	Core
Vapi Integration	vapiAssistantId, vapiSquadId, vapiWebhookSecret, vapiConnectionVerified	Core
Privacy Officer	privacyOfficerName, privacyOfficerEmail, privacyOfficerPhone	Theme 2
Data Retention	transcriptRetentionDays, callLogRetentionDays	Theme 2
BAA/DPA	baaVapiSigned, baaVapiSignedAt, baaHostingSigned, baaHostingSignedAt	Theme 2
Clinical Control	customGreeting, customGreetingZh, transferPhoneNumber, defaultProviderId, preferredPharmacy*, appointmentTypeMappings	Theme 4

---

ClinicHours

Working hours per day of week.

model ClinicHours {
  id         String   @id @default(uuid())
  clinicId   String   @map("clinic_id")
  dayOfWeek  Int      @map("day_of_week") // 0=Sunday, 6=Saturday
  openTime   String?  @map("open_time")   // "09:00"
  closeTime  String?  @map("close_time")  // "17:00"
  isClosed   Boolean  @default(false)

  @@unique([clinicId, dayOfWeek])
}

---

ClinicHoliday

Holiday closures per clinic.

model ClinicHoliday {
  id        String   @id @default(uuid())
  clinicId  String   @map("clinic_id")
  date      DateTime @db.Date
  name      String?
  createdAt DateTime @default(now())
}

---

ClinicProvider

Provider records with OSCAR mapping and sync capability.

model ClinicProvider {
  id                   String         @id @default(uuid())
  clinicId             String         @map("clinic_id")
  firstName            String
  lastName             String
  title                String         @default("Dr.")
  displayName          String?
  specialty            String?
  email                String?
  phone                String?
  acceptingNewPatients Boolean        @default(true)
  languages            String[]       @default(["English"])
  status               ProviderStatus @default(active)  // active, inactive, on_leave
  appointmentTypes     String[]       @default([])
  color                String         @default("#3B82F6")
  oscarProviderId      String?        // Maps to OSCAR provider number
  lastSyncedAt         DateTime?

  @@unique([clinicId, oscarProviderId])
}

---

WaitlistEntry

New patient waitlist entries.

model WaitlistEntry {
  id           String         @id @default(uuid())
  clinicId     String
  firstName    String
  lastName     String
  phone        String
  email        String?
  notes        String?
  status       WaitlistStatus @default(pending)  // pending, contacted, registered, declined
  createdAt    DateTime       @default(now())
  contactedAt  DateTime?
  registeredAt DateTime?
}

---

CallLog

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

model CallLog {
  id                String      @id @default(uuid())
  clinicId          String
  vapiCallId        String?     // Vapi's call UUID
  callerPhone       String?
  demographicId     Int?        // OSCAR patient ID
  language          String?     // "en", "zh"
  intent            CallIntent? // book, reschedule, cancel, register, inquiry, other
  outcome           CallOutcome? // booked, rescheduled, cancelled, registered, waitlisted, transferred, no_action
  appointmentId     Int?        // OSCAR appointment number
  durationSeconds   Int?
  cost              Float?      // Vapi call cost
  transcript        String?     // Full transcript (retention-managed)
  summary           String?     // AI-generated summary
  transferredToStaff Boolean   @default(false)
  transferReason    String?
  createdAt         DateTime    @default(now())
}

---

User

Dashboard users (both admin and clinic manager).

model User {
  id           String   @id @default(uuid())
  email        String   @unique
  passwordHash String   // bcrypt, cost factor 12
  firstName    String
  lastName     String
  role         UserRole @default(clinic_manager)  // vitara_admin, clinic_manager
  isActive     Boolean  @default(true)
  clinicId     String?  // FK to Clinic (null for vitara_admin)
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
}

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.

model AuditLog {
  id          String   @id @default(uuid())
  userId      String?
  userEmail   String
  action      String   // "POST /api/clinic/providers"
  resource    String   // "clinic/providers"
  resourceId  String?
  clinicId    String?
  details     Json?    // Redacted request body (passwords/secrets removed)
  ipAddress   String?
  userAgent   String?
  createdAt   DateTime @default(now())
}

Compliance: Satisfies PIPEDA 4.1.4 accountability requirement. Passwords and credential fields are automatically redacted before storage.

---

OnboardingProgress

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

model OnboardingProgress {
  clinicId          String    @id @map("clinic_id")
  currentStep       Int       @default(1) @map("current_step")  // 1-4

  // 8 step completion flags
  clinicInfoDone    Boolean   @default(false) @map("clinic_info_done")
  hoursDone         Boolean   @default(false) @map("hours_done")
  providersDone     Boolean   @default(false) @map("providers_done")
  emrConnected      Boolean   @default(false) @map("emr_connected")
  vapiAssigned      Boolean   @default(false) @map("vapi_assigned")
  testCallPassed    Boolean   @default(false) @map("test_call_passed")
  privacyOfficerSet Boolean   @default(false) @map("privacy_officer_set")
  baaSigned         Boolean   @default(false) @map("baa_signed")

  completedAt       DateTime? @map("completed_at")
  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@map("onboarding_progress")
}

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

Step-to-flag mapping: Step 1 sets clinicInfoDone, Step 2 sets emrConnected, Step 3 sets hoursDone, Step 4 runs validation checks. Additional flags (providersDone, vapiAssigned, testCallPassed, privacyOfficerSet, baaSigned) are set by the pre-launch validation endpoint.

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

---

SupportTicket

Clinic support tickets with priority and status workflow.

model SupportTicket {
  id          String         @id @default(uuid())
  clinicId    String
  createdById String         // User who opened the ticket
  assigneeId  String?        // Admin assigned to handle it
  title       String
  description String
  priority    TicketPriority @default(medium)  // low, medium, high, urgent
  status      TicketStatus   @default(open)    // open, in_progress, waiting, resolved, closed
  messages    TicketMessage[]
  createdAt   DateTime       @default(now())
  updatedAt   DateTime       @updatedAt
}

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

---

TicketMessage

Threaded messages on support tickets. Supports internal admin notes.

model TicketMessage {
  id         String        @id @default(uuid())
  ticketId   String
  ticket     SupportTicket @relation(onDelete: Cascade)
  authorId   String        // User who wrote the message
  content    String
  isInternal Boolean       @default(false)  // Internal admin-only notes
  createdAt  DateTime      @default(now())
}

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.

model Notification {
  id        String   @id @default(uuid())
  userId    String   // Target user
  type      String   // "ticket_created", "ticket_reply", "ticket_status_changed"
  title     String
  message   String
  link      String?  // Deep link to relevant page
  read      Boolean  @default(false)
  createdAt DateTime @default(now())
}

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

---

Enums

Enum	Values	Used By
ClinicStatus	active, inactive, pending	Clinic
UserRole	vitara_admin, clinic_manager	User
ProviderStatus	active, inactive, on_leave	ClinicProvider
WaitlistStatus	pending, contacted, registered, declined	WaitlistEntry
CallIntent	book, reschedule, cancel, register, inquiry, other	CallLog
CallOutcome	booked, rescheduled, cancelled, registered, waitlisted, transferred, no_action	CallLog
TicketPriority	low, medium, high, urgent	SupportTicket
TicketStatus	open, in_progress, waiting, resolved, closed	SupportTicket

---

Indexes

Model	Index	Columns
WaitlistEntry	@@index	clinicId
WaitlistEntry	@@index	status
CallLog	@@index	clinicId
CallLog	@@index	createdAt
CallLog	@@index	vapiCallId
AuditLog	@@index	clinicId
AuditLog	@@index	userId
AuditLog	@@index	createdAt
AuditLog	@@index	action
SupportTicket	@@index	clinicId
SupportTicket	@@index	status
SupportTicket	@@index	assigneeId
TicketMessage	@@index	ticketId
Notification	@@index	userId
Notification	@@index	read

---

Migrations

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

# 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 state
npx prisma migrate status

---

Backup & Recovery

Automated Backups

Daily automated backups via scripts/backup-db.sh (cron at 2 AM):

# Manual backup
pg_dump -U vitara -d vitara_platform > 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 full backup
psql -U vitara vitara_platform < backup_20260211.sql

Backups retain 14 daily copies. Located in /home/ubuntu/vitara-platform/backups/.

---

Data Retention

Configurable per clinic via ClinicConfig:

Field	Default	Action
transcriptRetentionDays	90	Nulls transcript column after N days
callLogRetentionDays	365	Deletes CallLog rows after N days

Enforcement: data-retention.ts job runs daily at 3 AM via node-cron.

---

Next Steps

• Platform Architecture
• Voice Agent
• Deployment