Database Schema

PostgreSQL 16 Multi-Tenant Schema

---

Database Configuration

Setting	Value
Database	vitara
User	vitara
Port	5432 (internal)
Version	PostgreSQL 16 Alpine
Volume	vitara-db-data

---

Schema Overview

┌─────────────────────────────────────────────────────────────────────────────┐
│                          VITARA DATABASE SCHEMA                              │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  ┌─────────────────┐                                                        │
│  │     clinics     │──────────────────┐                                     │
│  │  (UUID PK)      │                  │                                     │
│  └────────┬────────┘                  │                                     │
│           │                           │                                     │
│           │ 1:1                       │ 1:N                                 │
│           ▼                           │                                     │
│  ┌─────────────────┐                  │                                     │
│  │  clinic_config  │                  │                                     │
│  │  (clinic_id FK) │                  │                                     │
│  └─────────────────┘                  │                                     │
│                                       │                                     │
│  ┌─────────────────┐  ┌────────────────┴─────┐  ┌─────────────────┐        │
│  │  clinic_hours   │  │  clinic_providers    │  │ clinic_holidays │        │
│  │  (clinic_id FK) │  │  (clinic_id FK)      │  │ (clinic_id FK)  │        │
│  └─────────────────┘  └──────────────────────┘  └─────────────────┘        │
│                                                                              │
│  ┌─────────────────┐  ┌─────────────────┐                                   │
│  │    waitlist     │  │    call_logs    │                                   │
│  │  (clinic_id FK) │  │  (clinic_id FK) │                                   │
│  └─────────────────┘  └─────────────────┘                                   │
│                                                                              │
│  ┌─────────────────┐  ┌─────────────────┐                                   │
│  │     users       │  │   audit_logs    │                                   │
│  │  (clinic_id FK) │  │  (user_id FK)   │                                   │
│  └─────────────────┘  └─────────────────┘                                   │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

---

Table Definitions

clinics

Primary clinic registration table.

CREATE TABLE clinics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  phone VARCHAR(20),
  vapi_phone VARCHAR(20),
  address TEXT,
  city VARCHAR(100),
  province VARCHAR(50) DEFAULT 'BC',
  postal_code VARCHAR(10),
  timezone VARCHAR(50) DEFAULT 'America/Vancouver',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_clinics_slug ON clinics(slug);
CREATE INDEX idx_clinics_vapi_phone ON clinics(vapi_phone);

Column	Type	Description
id	UUID	Primary key
name	VARCHAR(255)	Clinic display name
slug	VARCHAR(100)	URL-safe identifier
phone	VARCHAR(20)	Main clinic phone
vapi_phone	VARCHAR(20)	Vapi phone number
address	TEXT	Street address
city	VARCHAR(100)	City
province	VARCHAR(50)	Province (default: BC)
postal_code	VARCHAR(10)	Postal code
timezone	VARCHAR(50)	IANA timezone
is_active	BOOLEAN	Active status

---

clinic_config

Per-clinic configuration including OSCAR credentials and EMR live status (enhanced in v1.5.0).

CREATE TABLE clinic_config (
  clinic_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_name VARCHAR(255) NOT NULL,
  clinic_slug VARCHAR(100) UNIQUE NOT NULL,
  clinic_status VARCHAR(50) DEFAULT 'pending_approval',
  clinic_timezone VARCHAR(50) DEFAULT 'America/Vancouver',
  clinic_created_at TIMESTAMP DEFAULT NOW(),
  clinic_updated_at TIMESTAMP DEFAULT NOW(),

  -- Contact Information
  contact_email VARCHAR(255),
  contact_phone VARCHAR(20),
  contact_address TEXT,

  -- Registration Settings
  accepting_new_patients BOOLEAN DEFAULT true,
  waitlist_enabled BOOLEAN DEFAULT true,
  max_new_patients_per_week INTEGER,

  -- EMR Configuration (v1.5.0+)
  emr_type VARCHAR(50) DEFAULT 'oscar',
  emr_live BOOLEAN DEFAULT false,
  emr_config JSONB DEFAULT '{}',

  -- OSCAR Connection (encrypted at application layer)
  oscar_url VARCHAR(500),
  oscar_api_key VARCHAR(255),
  oscar_consumer_key VARCHAR(255),
  oscar_consumer_secret_encrypted TEXT,
  oscar_token_key VARCHAR(255),
  oscar_token_secret_encrypted TEXT,
  oscar_connection_verified BOOLEAN DEFAULT false,
  oscar_last_verified_at TIMESTAMP,
  oscar_default_provider VARCHAR(50),

  -- Voice Settings
  default_language VARCHAR(10) DEFAULT 'en',
  supported_languages TEXT[] DEFAULT ARRAY['en', 'zh'],

  -- Preferences (JSONB)
  preferences JSONB DEFAULT '{}'
);

CREATE INDEX idx_clinic_config_slug ON clinic_config(clinic_slug);
CREATE INDEX idx_clinic_config_status ON clinic_config(clinic_status);

Column	Type	Description
clinic_id	UUID	Primary key
clinic_name	VARCHAR(255)	Clinic display name
clinic_slug	VARCHAR(100)	URL-safe identifier
clinic_status	VARCHAR(50)	active, pending_approval, suspended
emr_type	VARCHAR(50)	EMR type (e.g., oscar, telus)
emr_live	BOOLEAN	When true, uses real EMR; when false, demo mode
emr_config	JSONB	Per-clinic EMR configuration
oscar_url	VARCHAR(500)	OSCAR REST Bridge URL
oscar_api_key	VARCHAR(255)	API key for OSCAR
oscar_default_provider	VARCHAR(50)	Default provider number
preferences	JSONB	Booking rules, reminder hours, etc.

Status Values:

Status	Description
pending_approval	New clinic awaiting setup
active	Active but EMR not live
live	EMR integration enabled
suspended	Temporarily disabled

---

clinic_hours

Working hours per day of week.

CREATE TABLE clinic_hours (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  day_of_week INTEGER NOT NULL CHECK (day_of_week BETWEEN 0 AND 6),
  open_time TIME,
  close_time TIME,
  is_closed BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(clinic_id, day_of_week)
);

CREATE INDEX idx_clinic_hours_clinic ON clinic_hours(clinic_id);

Column	Type	Description
id	UUID	Primary key
clinic_id	UUID (FK)	References clinics
day_of_week	INTEGER	0=Sunday, 6=Saturday
open_time	TIME	Opening time
close_time	TIME	Closing time
is_closed	BOOLEAN	Closed all day

---

clinic_holidays

Holiday closures.

CREATE TABLE clinic_holidays (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  date DATE NOT NULL,
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(clinic_id, date)
);

CREATE INDEX idx_clinic_holidays_date ON clinic_holidays(clinic_id, date);

---

clinic_providers

Provider display names (overrides OSCAR names).

CREATE TABLE clinic_providers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  oscar_provider_id VARCHAR(50) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  specialty VARCHAR(100),
  accepting_new_patients BOOLEAN DEFAULT true,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(clinic_id, oscar_provider_id)
);

---

waitlist

New patient waitlist entries.

CREATE TABLE waitlist (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(255),
  notes TEXT,
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW(),
  contacted_at TIMESTAMP,
  registered_at TIMESTAMP
);

CREATE INDEX idx_waitlist_clinic ON waitlist(clinic_id);
CREATE INDEX idx_waitlist_status ON waitlist(status);

Status Values	Description
pending	Awaiting contact
contacted	Staff has reached out
registered	Now a patient
cancelled	Removed from list

---

call_logs

Call analytics and outcomes.

CREATE TABLE call_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID REFERENCES clinics(id),
  vapi_call_id VARCHAR(255),
  caller_phone VARCHAR(20),
  demographic_id INTEGER,
  language VARCHAR(10),
  intent VARCHAR(50),
  outcome VARCHAR(50),
  appointment_id INTEGER,
  duration_seconds INTEGER,
  transferred_to_staff BOOLEAN DEFAULT false,
  transfer_reason VARCHAR(255),
  error_message TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_call_logs_clinic ON call_logs(clinic_id);
CREATE INDEX idx_call_logs_created ON call_logs(created_at);
CREATE INDEX idx_call_logs_outcome ON call_logs(outcome);

Intent Values	Description
book	Book appointment
reschedule	Reschedule existing
cancel	Cancel appointment
register	New patient registration
info	Clinic information
other	Other/unclear

Outcome Values	Description
booked	Appointment created
rescheduled	Appointment moved
cancelled	Appointment cancelled
registered	Patient registered
waitlisted	Added to waitlist
transferred	Transferred to staff
abandoned	Caller hung up
error	System error

---

admin_users

Admin dashboard users (v1.5.0+).

CREATE TABLE admin_users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  role VARCHAR(50) NOT NULL DEFAULT 'clinic_manager',
  clinic_id UUID REFERENCES clinic_config(clinic_id),
  is_active BOOLEAN DEFAULT true,
  failed_login_attempts INTEGER DEFAULT 0,
  locked_until TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_admin_users_email ON admin_users(email);
CREATE INDEX idx_admin_users_clinic ON admin_users(clinic_id);
CREATE INDEX idx_admin_users_role ON admin_users(role);

Role Values	Description
vitara_admin	Platform administrators with full system access
clinic_manager	Single-clinic administrators for self-service

---

audit_log

Immutable audit trail for admin actions (v1.5.0+).

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES admin_users(id),
  user_email VARCHAR(255),
  user_role VARCHAR(50),
  action VARCHAR(100) NOT NULL,
  resource_type VARCHAR(50),
  resource_id UUID,
  changes JSONB,
  metadata JSONB,
  ip_address INET,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_log_user ON audit_log(user_id);
CREATE INDEX idx_audit_log_action ON audit_log(action);
CREATE INDEX idx_audit_log_resource ON audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_log_created ON audit_log(created_at);

Column	Type	Description
user_id	UUID	Acting user
user_email	VARCHAR(255)	Email at time of action (for display)
user_role	VARCHAR(50)	Role at time of action
action	VARCHAR(100)	e.g., auth.login, clinic.update
resource_type	VARCHAR(50)	e.g., clinic_config, admin_user
resource_id	UUID	Affected resource ID
changes	JSONB	Before/after values
metadata	JSONB	Additional context

Note: JSONB columns are auto-parsed by the PostgreSQL pg driver. The backend uses safeJsonParse() helper for safe handling.

---

Demo Data

Initial seed data for development.

-- Demo clinic
INSERT INTO clinics (id, name, slug, phone, vapi_phone, address, city)
VALUES (
  'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
  'VitaraPlatform Demo Clinic',
  'demo-clinic',
  '604-555-0100',
  '+16045550100',
  '123 Demo Street',
  'Vancouver'
);

-- Demo clinic config
INSERT INTO clinic_config (clinic_id, accepting_new_patients, waitlist_enabled)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', true, true);

-- Demo hours (Mon-Fri 9-5)
INSERT INTO clinic_hours (clinic_id, day_of_week, open_time, close_time, is_closed)
VALUES
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 0, NULL, NULL, true),      -- Sunday
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 1, '09:00', '17:00', false), -- Monday
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 2, '09:00', '17:00', false), -- Tuesday
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 3, '09:00', '17:00', false), -- Wednesday
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 4, '09:00', '17:00', false), -- Thursday
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 5, '09:00', '17:00', false), -- Friday
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 6, NULL, NULL, true);       -- Saturday

---

Migrations

Location

/opt/vitara-platform/voice-agent/migrations/

Files

File	Description
001_add_emr_columns.sql	Add emr_type and emr_config columns (v1.4.0)
001_add_emr_columns_rollback.sql	Rollback script for EMR columns
002_admin_system.sql	Admin users, audit_log, enhanced clinic_config (v1.5.0)
003_seed_data.sql	Demo clinics, users, and audit log entries (v1.5.1)

Running Migrations

# Connect to database
docker exec -it vitara-db psql -U vitara -d vitara

# Run migrations in order
docker exec vitara-db psql -U vitara -d vitara \
  -f /migrations/001_add_emr_columns.sql
docker exec vitara-db psql -U vitara -d vitara \
  -f /migrations/002_admin_system.sql
docker exec vitara-db psql -U vitara -d vitara \
  -f /migrations/003_seed_data.sql

---

Backup & Recovery

Backup

# Full backup
docker exec vitara-db pg_dump -U vitara vitara > backup_$(date +%Y%m%d).sql

# Schema only
docker exec vitara-db pg_dump -U vitara -s vitara > schema.sql

# Data only
docker exec vitara-db pg_dump -U vitara -a vitara > data.sql

Recovery

# Restore full backup
docker exec -i vitara-db psql -U vitara vitara < backup_20260112.sql

---

Next Steps

• Voice Agent →
• Deployment →