Database Overview

Complete index of all database tables across the system, organized by feature.

Core Features

Authentication & Users

users - User accounts with Clerk integration
user_organizations - Many-to-many relationship (users ↔ organizations)

Organizations

organizations - Multi-tenant root entity
organization_integrations - Third-party API keys (Daily.co, AWS)

Appointment & Scheduling

Appointments

appointments - Appointment instances with lifecycle states
appointment_files - File attachments for appointments

Appointment Templates

appointment_templates - Bookable service type definitions
appointment_template_forms - Form attachments (junction table)
appointment_template_specialists - Specialist priorities (junction table)
appointment_template_attachments - Fixed file attachments

Scheduling

specialist_hours - Weekly availability schedules
specialist_date_overrides - Exception dates (unavailable/available)
hold_events - Redis hold event log (audit trail)

Services

services - Service catalog entries (name, duration, price, category, visibility)
service_specialists - Who can provide each service (capability, custom pricing)
service_forms - Form templates auto-generated on booking (junction table)
service_attachments - Downloadable files (PDFs, consent forms)
service_plans - Multi-session packages and subscriptions (session_based/time_based/hybrid billing models)
patient_service_plans - Patient enrollment, session tracking, access window tracking
products - Physical goods for recommendations (elastic bands, supplements)
service_plan_products - Products bundled with service plans (junction)
patient_product_orders - Product fulfillment tracking (bundled + standalone)

Profile Management

Patients

patient_persons - Portable patient profile (name, DOB, blood type, allergies, insurance) — no org_id, shared across all orgs the patient attends
patient_person_managers - Family/caregiver relationships (e.g. daughter managing elderly father) — junction table linking a manager user to a patient person
patients - Org-patient link (thin record: organization_id + patient_person_id + consumer_id)

Specialists

specialists - Healthcare provider profiles
specialist_specialties - Many-to-many (specialists ↔ specialties)

Specialties

specialties - Medical specialty categories

Forms System

Forms

form_templates - Form template definitions (design time)
form_template_versions - Template version history with snapshots
forms - Form instances (runtime filled forms)

Custom Fields

custom_fields - Versioned field library for all entities
custom_field_versions - Field version history with snapshots
custom_field_values - Per-entity data (patient profiles, specialist profiles, etc.)

Segments

segments - Patient grouping rules
segment_members - Many-to-many (segments ↔ patients)
segment_versions - Version history with rule snapshots

Telerehabilitation

Exercise Library

exercises - Exercise video library (global: org_id NULL, org-scoped: org_id set). UUID PK. Soft delete.
exercise_categories - Taxonomy categories, hierarchical (parent_id). Global + org.
exercise_body_regions - Body part tags grouped by body_area. Global + org.
exercise_equipment - Equipment catalog. Global + org.
exercise_tags - Polymorphic M:M junction (tag_type + tag_id → category/body_region/equipment)
exercise_instructions - Ordered text/image instruction steps per exercise
exercise_contraindications - Clinical warnings/restrictions per exercise

Treatment Plans

treatment_plans - Plan definitions (telerehab/in_clinic type, global/org/custom scope, frequency, duration, versioned)
treatment_plan_versions - Version history with full JSONB sessions_snapshot
treatment_plan_sessions - Template sessions within a plan (ordered blueprints)
treatment_plan_session_exercises - Exercises per session with config (mode, sets, reps, duration, rest)
patient_treatment_plans - Patient enrollment, progress tracking (sessions_completed/skipped)
patient_session_completions - Per-session tracking (timing, pain levels, post-session form)
patient_exercise_logs - Per-exercise tracking (prescribed vs actual, video watch %, pose accuracy)

Supporting Features

PDF Templates

pdf_templates - Visual PDF template definitions with versioning
pdf_template_versions - Template version history
pdf_template_components - Reusable blocks (letterhead, footer, signature)

Documents

appointment_documents - Generated PDFs (reports, prescriptions)
appointment_document_files - File references with signatures

Webhooks

webhook_subscriptions - Organization webhook endpoints
webhook_events - Event delivery log

Audit

audit_log - Local audit trail (synchronous write per request). Async forwarded to Telemetry PostgreSQL audit.audit_logs for enrichment (geo, actor hashing, threat detection). the Core API's table is the HIPAA safety net; Telemetry's is the enriched compliance store (7-year retention). See ../features/audit/README.md for the dual-write architecture.

Table Count by Category

Category	Tables	Features
Core	4	Auth, Organizations
Appointments & Scheduling	17	Appointments, Templates, Scheduling, Services (9)
Profiles	6	Patients (3), Specialists, Specialties
Forms System	8	Forms, Templates, Custom Fields, Segments
Telerehabilitation	14	Exercise Library (7), Treatment Plans (7)
Supporting	8	PDF Templates, Documents, Webhooks, Audit
Total	57	20

Multi-Tenancy Pattern

All tables (except users) have organization_id column:

sql

CREATE TABLE example_table (
    id BIGSERIAL PRIMARY KEY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    -- ... other columns
);

CREATE INDEX idx_example_table_org ON example_table(organization_id);

ALTER TABLE example_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY example_table_org_isolation ON example_table
    USING (organization_id = current_app_org_id());

Two-pool architecture: RLS policies are enforced by the AppPool (restartix_app — restricted role). Superadmins use the AdminPool (restartix — owner role) which bypasses all RLS. No policy uses is_superadmin().

Exceptions:

users — no organization_id; belongs to multiple orgs via user_organizations (sub-query RLS)
patient_persons — no organization_id; portable cross-org profile owned by the patient (RLS via current_user_patient_person_ids())
patient_person_managers — no organization_id; caregiver relationships (RLS via user_id check)

RLS Session Variables

Variable	Type	Purpose
`app.current_user_id`	BIGINT	User-scoped RLS
`app.current_org_id`	BIGINT	Org-scoped RLS
`app.current_role`	TEXT	Role-based RLS

Set by middleware on every request. See RLS Policies for details.

Indexes

Every table with organization_id must have an index:

sql

CREATE INDEX idx_{table}_org ON {table}(organization_id);

Without these indexes, RLS queries become sequential scans (slow).

Cascade Delete Rules

Deleting an organization cascades to all related data:

DELETE FROM organizations WHERE id = 1
  ├── CASCADE: appointments
  ├── CASCADE: patients
  ├── CASCADE: specialists
  ├── CASCADE: forms
  ├── CASCADE: segments
  ├── CASCADE: webhook_subscriptions
  └── ... all other org-scoped tables

Safe: Foreign key constraints prevent orphaned records.

JSONB Columns

Several tables use JSONB for flexible data:

Table	Column	Purpose
treatment_plan_versions	`sessions_snapshot`	Full plan structure snapshot (immutable)
form_templates	`fields`	Field definitions (snapshotted)
forms	`fields`	Field definitions (snapshotted from template)
forms	`values`	Form submission data
forms	`files`	File upload references
segments	`rules`	Segment matching rules
custom_field_values	`value`	Profile field values
appointment_documents	`metadata`	PDF generation metadata
treatment_plans	`condition_tags`	Pain/condition area tags for library browsing

Indexed with GIN:

sql

CREATE INDEX idx_forms_values ON forms USING GIN (values);
CREATE INDEX idx_segments_rules ON segments USING GIN (rules);
CREATE INDEX idx_treatment_plans_condition_tags ON treatment_plans USING GIN (condition_tags);

This enables fast JSONB/array queries for segment evaluation, form filtering, and treatment plan browsing by condition.

Enums

appointment_status

sql

CREATE TYPE appointment_status AS ENUM (
    'booked',      -- Initial state after booking
    'upcoming',    -- After patient onboarding
    'confirmed',   -- Patient confirmed attendance
    'inprogress',  -- Videocall started
    'done',        -- Appointment completed
    'cancelled',   -- Cancelled by patient/specialist/admin
    'noshow'       -- Auto-marked after grace period
);

form_type

sql

CREATE TYPE form_type AS ENUM (
    'consent',     -- Consent forms (typically signed)
    'survey',      -- Questionnaires
    'intake',      -- Patient intake forms
    'followup'     -- Follow-up forms
);

form_status

sql

CREATE TYPE form_status AS ENUM (
    'pending',     -- Not started
    'in_progress', -- Partially filled
    'completed',   -- All required fields filled
    'signed'       -- Immutable (HIPAA compliance)
);

exercise_difficulty

sql

CREATE TYPE exercise_difficulty AS ENUM (
    'beginner',
    'intermediate',
    'advanced'
);

exercise_status

sql

CREATE TYPE exercise_status AS ENUM (
    'draft',
    'published',
    'archived'
);

treatment_plan_type

sql

CREATE TYPE treatment_plan_type AS ENUM (
    'telerehab',        -- video exercises at home
    'in_clinic'         -- specialist-scheduled in-clinic sessions
);

treatment_plan_status

sql

CREATE TYPE treatment_plan_status AS ENUM (
    'draft',
    'pending_approval',
    'active',
    'paused',
    'completed',
    'cancelled',
    'expired'
);

exercise_mode

sql

CREATE TYPE exercise_mode AS ENUM (
    'duration',         -- hold for X seconds
    'reps'              -- perform X repetitions
);

user_role

sql

CREATE TYPE user_role AS ENUM (
    'patient',     -- Can view own data only
    'specialist',  -- Can view assigned patients
    'admin',       -- Can manage organization
    'superadmin'   -- Can access all organizations
);

Foreign Key Relationships

Key Junction Tables

appointment_template_forms - Attach forms to appointment types

appointment_template_id → appointment_templates(id)
form_template_id → form_templates(id)

appointment_template_specialists - Specialist priorities

appointment_template_id → appointment_templates(id)
specialist_id → specialists(id)

specialist_specialties - Specialist categorization

specialist_id → specialists(id)
specialty_id → specialties(id)

segment_members - Patient segment membership

segment_id → segments(id)
patient_id → patients(id)

user_organizations - Multi-org membership

user_id → users(id)
organization_id → organizations(id)

Migration Files

Database schema is managed via golang-migrate:

migrations/
├── 000001_create_organizations.up.sql
├── 000002_create_users.up.sql
├── 000003_create_patients.up.sql
├── ...
└── 000029_create_audit_log.up.sql

Apply migrations:

bash

make migrate-core-up

Rollback:

bash

make migrate-core-down

Telemetry Databases (Separate)

Telemetry uses two separate databases within the same AWS VPC, entirely independent from the main Core API PostgreSQL. They are isolated for compliance and performance reasons.

ClickHouse — High-Frequency Analytics

7 tables + 5 materialized views for real-time analytics ingestion:

Table	Purpose
`analytics_events`	General analytics event stream
`media_sessions`	Video/media session tracking
`media_buffering_events`	Buffering quality metrics
`error_events`	Client and server error tracking
`api_metrics`	API latency and throughput metrics
`pose_tracking_frames`	Per-frame pose estimation data
`staff_metrics`	Staff performance metrics

Plus 5 materialized views for pre-aggregated queries.

Telemetry PostgreSQL (TimescaleDB) — Compliance-Grade Audit

6 tables across three schemas for audit, security, and privacy:

Table	Purpose
`audit.audit_logs`	Immutable audit trail
`audit.audit_daily_aggregates`	Daily rollup summaries
`security.security_events`	Security incident tracking
`privacy.privacy_exclusions`	Patient privacy opt-outs
`privacy.privacy_exclusion_sync_log`	Sync state for exclusion propagation
`staff.staff_activity`	Staff activity audit records

Important: These databases are NOT part of the main Core API PostgreSQL. Do not attempt to query Telemetry tables from the core database or vice versa.

For full documentation, see Telemetry.

RLS Policies - Consolidated RLS reference
API Overview - All endpoints
RBAC Permissions - Role-based access control
Features Index - Feature-by-feature documentation

Database Overview ​

Core Features ​

Appointment & Scheduling ​

Profile Management ​

Forms System ​

Telerehabilitation ​

Supporting Features ​

Table Count by Category ​

Multi-Tenancy Pattern ​

RLS Session Variables ​

Indexes ​

Cascade Delete Rules ​

JSONB Columns ​

Enums ​

appointment_status ​

form_type ​

form_status ​

exercise_difficulty ​

exercise_status ​

treatment_plan_type ​

treatment_plan_status ​

exercise_mode ​

user_role ​

Foreign Key Relationships ​

Key Junction Tables ​

Migration Files ​

Telemetry Databases (Separate) ​

ClickHouse — High-Frequency Analytics ​

Telemetry PostgreSQL (TimescaleDB) — Compliance-Grade Audit ​

Related Documentation ​