Database Overview

This page is being deprecated

The authoritative entity model is /architecture/data-model, which was derived from a holistic audit of every feature spec. This page predates that audit and contains stale details: it references appointment_templates (deprecated — replaced by services + calendars), uses BIGINT foreign-key references that don't match the current UUIDv7 primary-key convention, and shows a table inventory that has shifted.

When implementing a domain, follow /architecture/data-model for entity shapes, /architecture/patterns for cross-cutting concerns, and /architecture/dependency-map for build order. This page is kept for historical context.

SQL fragments below are illustrative shape — the real schema lives in services/api/migrations/core/.

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 plan types)
patient_service_plans - Patient enrollment, session tracking, access window tracking
products - Physical goods catalog (elastic bands, supplements) — reference only, no e-commerce
service_plan_products - Products bundled with service plans (informational junction)

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, monthly partitioned per P41). The single source of truth for compliance audit. Telemetry has no role in audit forwarding — see Why telemetry is PG + S3, not ClickHouse for the redesign rationale and ../features/audit/index.md for the audit 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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID 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`	UUID	User-scoped RLS
`app.current_org_id`	UUID	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
);

Roles (no enum — stored as data)

There is no user_role enum. Role codes are kept as TEXT in the roles table, and each clinic gets its own cloned set of system role rows on creation. This keeps the door open for custom per-org roles without a schema change. The 5 canonical role codes are:

Code	Scope	Description
`patient`	per-org	End user receiving care.
`specialist`	per-org	Healthcare provider.
`customer_support`	per-org	Support staff.
`admin`	per-org	Organization manager.
`superadmin`	platform	Platform operator — granted via `platform_roles`, not via a clinic membership. Bypasses RLS via the owner pool.

A user can hold a different role in each org they belong to (admin at clinic A, customer_support at clinic B). See RBAC Permissions → for the full model.

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 storage (Layer 2)

Telemetry stores its data in the same Postgres cluster as Core API (no separate compliance DB) plus S3 for replay blobs. This is a deliberate change from the earlier "two separate databases" design — see Why telemetry is PG + S3, not ClickHouse for the rationale.

Postgres aggregates (clinical truth)

Written by the Core API events.Bus subscriber when Telemetry API publishes session aggregates. RLS-scoped, audit-logged, classified per data-classification.md.

Table	Cardinality	Partitioning
`pose_session_metrics`	1 row/session	None (state-shaped)
`pose_rep_metrics`	~100 rows/session	Monthly partitioned (P41)
`media_session_metrics`	1 row/session	None
`media_buffering_events`	~5 rows/session	Monthly partitioned (P41)

Plus updates to existing patient_exercise_logs (video_watch_percentage, pose_accuracy_score, etc.).

S3 replay blobs

s3://restartix-telemetry/{org_id}/{session_id}.bin.gz — binary float32 + gzip, ~3 MB per 30-min session. Lifecycled standard → IA → Glacier → expire. Access only via short-lived signed URLs minted by Core API.

No separate ClickHouse, no separate compliance Postgres, no TimescaleDB. All of those are out of scope until Tier 3 scale forces a cross-tenant analytical workload (see ../telemetry/index.md → Scaling roadmap). For the foreseeable horizon, telemetry rides on the same RDS instance as Core API plus an S3 bucket.

For the full design, see ../telemetry/index.md.

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 ​

Roles (no enum — stored as data) ​

Foreign Key Relationships ​

Key Junction Tables ​

Migration Files ​

Telemetry storage (Layer 2) ​

Postgres aggregates (clinical truth) ​

S3 replay blobs ​

Related Documentation ​