RestartiX Platform

English

Română

English

Română

Appearance

Database Overview ​

What this page is

An index of the schema that actually exists — every table created by the 39 migrations in services/api/migrations/core/, plus the cross-cutting conventions (RLS helpers, session variables, extensions, partitioning, pools) a reader needs to navigate it.

For design, /architecture/data-model is authoritative — it derives entity shapes from the holistic spec audit and covers entities that are not built yet. This page is the shipped-state counterpart: what a \dt against the real database returns today. /architecture/glossary wins any naming dispute.

Verified against migrations 000001–000039 on 2026-08-02. Column-level shapes are in the migration files; this page deliberately does not restate them, because restated columns drift.

History

Earlier revisions of this page described a superseded design generation that never shipped — a users table, user_organizations, patient_persons, BIGINT/BIGSERIAL primary keys, app.current_user_id, and a services + calendars scheduling core. None of that exists in any migration. Those sections have been removed rather than renamed, because the shape was wrong, not just the vocabulary. See Actor model for what replaced the identity half.

Scope ​

Two databases, two migration sets:

DatabaseMigrationsOwnerRLS
restartix (API)services/api/migrations/core/API serviceEnforced — every table
Telemetryservices/telemetry/migrations/Telemetry serviceDeliberately not enforced — telemetry has no per-tenant authenticated query path; scoping happens in the API endpoints that read it

Both live on the same Aurora cluster (logical isolation, separate databases). The media service owns no schema. See Telemetry storage below and /telemetry/.

Table inventory (API database) ​

109 tables across 39 migrations. Partition children (audit_log_2026_05, etc.) are not counted — they are managed by make partition-roll, not by hand.

Audit — 000001_init ​

  • audit_log — append-only audit trail, one row per state-changing mutation. Range-partitioned monthly by created_at (P41). No UPDATE/DELETE policy; INSERT/UPDATE/DELETE/TRUNCATE revoked from restartix_app at the grant layer. Written through the audit_log_insert() SECURITY DEFINER function so handler-emitted and trigger-cascaded rows share one request envelope.
  • audit_ai_provenance — AI provenance sidecar (model_id → ai_models, inputs_hash, confidence; there is no model_version column — it was renamed to model_id in 000020). Partitioned on the same monthly window; FK targets the parent's (id, created_at) key, so it carries a denormalised audit_log_created_at partition key.

Identity, tenancy & RBAC — 000002_tenancy_rbac ​

  • principals — root identity registry. Every actor is a row here: human, AI agent, integration service account, system job.
  • humans — human profile. PK is principal_id → principals(id). Carries exactly provider_subject_id, provider_org_id, email, confirmed, blocked, portal_credential_generation, last_activity, preferred_language, timezone. No name column of any kind — not for staff either. Patient names live on patient_profiles.name; staff display names are not in this schema (the row binds to the auth provider by provider_subject_id). Also no cached "current org": default-org-on-first-sign-in is derived from MAX(last_used_at) across organization_memberships and patients. The row carries no authorization state — that lives in organization_memberships / platform_memberships.
  • agents — AI agent profile. Sibling of humans, same PK pattern. Single-org by design (organization_id NOT NULL).
  • service_accounts — Cat F integration principal (clinic-installed connectors, EHR sync, custom webhook senders). Sibling of humans, single-org, holds api_key_hash.
  • organizations — the multi-tenant root.
  • organization_domains — custom clinic/portal hostnames. Uses the only two real enums in the schema: domain_type (clinic | portal), domain_status (pending | verified | failed).
  • organization_memberships — staff membership: (principal_id, organization_id, role_id, is_owner, last_used_at). One role per principal per org. Patients are not in this table — they live in patient_profiles + patients.
  • platform_memberships — platform-level grants. Human-only by CHECK; superadmin is the only role today.
  • roles — per-org named permission bundles. System templates have organization_id IS NULL + is_system = TRUE and are cloned into every new org.
  • permissions — deploy-time catalog of resource.action codes, seeded and extended by migrations.
  • role_permissions — M:M grant of permissions to roles.

Organization configuration — 000003_org_settings ​

  • organization_settings — operational + compliance knobs, one row per org (PK = organization_id). New settings are typed column adds, not JSONB keys.
  • organization_billing — regulated financial data, one row per org. tax_id_encrypted BYTEA.
  • organization_entitlements — the regulated read surface. All flags default FALSE (fail-closed). AppPool has no UPDATE.
  • organization_designations — per-org assignment of a legal / regulatory / governance responsibility. kind is free-form TEXT (not an enum) so a new designation kind is a data change, not a migration; the designee is either an internal principal_id or an external contact, enforced by the designation_has_contact CHECK. Orthogonal to RBAC: roles answer "what can you do", designations answer "what do you hold".

Org plans & subscriptions — 000004_tiers_subscriptions ​

tiers · tier_versions · entitlements · limit_definitions · tier_entitlements · tier_limits · organization_subscriptions · organization_subscription_entitlements · organization_subscription_limits · organization_subscription_overrides

The boolean family (entitlements / tier_entitlements / organization_entitlements) and the quota family (limit_definitions / tier_limits) are both entitlements per the glossary; they are separate tables because their runtime semantics differ. Subscription-side tables are frozen snapshots (P14b) — no FK on entitlement_code, by design. Full model: /architecture/tiers-and-subscriptions.

Patient tiers — 000005_patient_tiers ​

patient_tiers · patient_tier_versions · patient_tier_entitlements · patient_tier_limits — per-org mirror of the platform tier stack, for what a clinic sells to its own patients. References the shared entitlements(code) / limit_definitions(code) catalogs.

Patient identity — 000006_patient_identity ​

  • patient_profiles — the portable, patient-owned identity: name, DOB, sex, phone, occupation, residence, blood type, allergies, chronic conditions, emergency contact, insurance_entries JSONB. No organization_id — this is the documented RLS exception (see Multi-tenancy). human_id is nullable: account-less patients managed by family have no auth account of their own.
  • patient_caregivers — caregiver/family links (patient_profile_id, caregiver_human_id, relationship). No organization_id — the link is between two human-scoped concepts.
  • patients — the per-org clinical link between an organization and a patient_profile. Existence of a row grants portal access at that org. Carries consumer_id (legacy external ID), last_used_at, deleted_at (soft delete only). This row is the disclosure boundary for the portable profile — a clinic reads patient_profiles because it holds one.

Patient subscriptions — 000007_patient_subscriptions ​

patient_subscriptions · patient_subscription_entitlements · patient_subscription_limits · patient_subscription_overrides — mirror of the org subscription stack, gated by clinic admin (patient_subscriptions.manage) rather than platform admin. At most one live subscription per (patient, org), enforced by a partial unique index over the live lifecycle states.

Consents — 000008_consents ​

  • consent_purposes — platform catalog of every purpose consent is recorded for. withdrawable flags whether the patient-initiated withdraw endpoint applies.
  • consent_purpose_versions — versioned policy text. Platform defaults have organization_id IS NULL; per-org overrides set it.
  • consents — the grant/withdraw record. No updated_at by design — the only allowed mutation is withdrawal, enforced by the enforce_consents_withdrawal_only BEFORE UPDATE trigger.
  • legal_document_templates — platform catalog, one row per (document_type, version, locale).
  • organization_legal_documents — per-org editor state, seeded by the org-create trigger with published_version = NULL.

000009 also redefines create_organization_companion_rows() via CREATE OR REPLACE — edit it there, not in 000003.

Notifications — 000010_notifications ​

  • notifications — one row per logical send; body rendered at enqueue so the audit answer to "what did we send?" is a plain SELECT. Monthly partitioned.
  • notification_deliveries — one row per (notification × channel), with the pending → claimed → sent | failed → … → dead_letter machine. Monthly partitioned on the denormalised notification_created_at.
  • notification_idempotency_keys — producer-side dedup guard.
  • notification_preferences — sparse overrides only; absence means "use the category default".

Access control & elevation — 000011–000013 ​

  • break_glass_sessions (000011) — per-org, time-bound, justified, per-resource-type scope. Break-glass sessions log all actions including reads.
  • organization_invites · share_links (000012) — personal invites (staff or patient) and patient-only code-anchored multi-use links (QR codes, posters).
  • patient_impersonation_sessions (000013) — target_patient_id FKs patients(id) (the per-org row), so the org constraint rides the FK chain.

Locations — 000014_locations ​

  • locations — structured address fields, never freeform. No per-location RLS dimension: current_app_location_ids() was deliberately not added; staff see every location at their org and locations.manage gates only mutations.

Integrations — 000015–000018 ​

Category definitions are in /architecture/glossary.

  • platform_service_providers (000015) — Cat A curated providers, platform-credentialed. credentials_encrypted BYTEA.
  • outbound_webhook_subscriptions · outbound_webhook_deliveries (000016) — Cat C. Deliveries are monthly partitioned per P41; PK is (id, created_at).
  • integration_services · organization_integrations (000017) — Cat B. integration_services is the platform catalog of services we connect TO (seeded empty at foundation; each F-tier consumer adds its row alongside its connector). Not to be confused with service_accounts, which is a principal subtype for actors authenticating INTO us.
  • inbound_webhook_dedup (000018) — Cat D replay guard, monthly partitioned by processed_at.

Metering & AI — 000019–000020 ​

  • usage_records (000019) — append-only event log, one row per metered capability call. Monthly partitioned by occurred_at; PK (id, occurred_at).
  • usage_quotas — live counter per (organization_id, capability, period); limit_units NULL = unlimited.
  • usage_summaries — post-period rollup that survives quota resets, for billing reconstruction.
  • ai_models · ai_model_pricing_history (000020) — model registry the platform is authorised to call, plus pricing history. See /reference/ai-models.

Ownership transfers — 000021_ownership_transfers ​

  • organization_ownership_transfers — the only path by which organization_memberships.is_owner moves between principals; direct demotion is blocked by the protect_owner_membership trigger.

Clinical content — 000022–000026 ​

  • content_files (000022) — the single registry for consumable media (kind: audio | video | image | document). Descriptive metadata rides in metadata JSONB.
  • exercises — the exercise library. ownership_kind (platform | org) with a matching CHECK: platform rows have organization_id IS NULL, org rows set it (P49). kind is the render model: reps_based | duration_based | static.
  • exercise_renders — the async bake pipeline's per-render row (pending → rendering → transcoding → ready | failed).
  • sessions (000023) — the session template. Publish lifecycle draft → published → archived; kind is exercise | audio.
  • session_exercises — per-exercise dose. mode is reps | hold | video_only, constrained at write time against the source exercise's kind.
  • session_runs — one row per playthrough. in_progress | ended_naturally | ended_explicit | auto_closed. All terminal transitions are server-driven.
  • session_pain_events — append-only mid-session pain reports. Monthly partitioned by reported_at.
  • session_exercise_events — clinical progress milestones per exercise inside a run (started / completed / skipped / abandoned / …). Monthly partitioned by occurred_at. Lives in the API database, not telemetry's.
  • protocols — the single polymorphic patient-side table, kind ∈ {prescription, enrollment}: specialist-driven prescriptions (cadence + adherence) and self-initiated enrollments (course progress). Status active | paused | completed | ended. Two-cadence engine (flexible | scheduled) × orthogonal supervision (unsupervised | supervised) — see /architecture/cadence-and-supervision.
  • session_pairings · session_tv_liveness (000024) — TV companion pairing handshake and the append-only 10s TV heartbeat (monthly partitioned by ts). One of the auto-close cron's two proof-of-life signals.
  • programs · program_phases · program_assets · session_assets · session_audio_items (000025) — the multi-session container substrate. Programs are kind-agnostic; three-tier copy-on-derive (platform → org → patient-specific).
  • protocol_pauses (000026) — patient-side pause windows on a protocol.

Exercise taxonomy & pose tracking — 000027–000031 ​

Taxonomy (000027), 11 tables. Dual-scope (platform + org-private) vocabularies: exercise_categories, exercise_equipment, exercise_conditions. Platform-only vocabularies (locked because cohort analytics and pose heuristics consume them): exercise_body_regions, exercise_movement_patterns, exercise_recovery_phases, exercise_skill_prerequisites. Plus exercise_tags (polymorphic M:M, tag_type discriminates which vocabulary tag_id resolves against), exercise_prerequisites, exercise_instructions, exercise_contraindications.

Pose tracking (000028–000029), 8 tables: pose_engines · pose_landmarks (per-engine catalog, ~543 rows for MediaPipe Holistic) · exercise_pose_configs · exercise_pose_config_history (immutable, required for Class IIa reproducibility) · exercise_pose_landmarks · exercise_pose_metrics · exercise_pose_feedback_rules · pose_data_quality_overrides.

000030 and 000031 are seed-only — vocabulary rows and the 16-exercise content batch. They create no tables. Design record: /architecture/exercise-taxonomy-pose-tracking.

Pose scope

This is the schema for pose tracking. The pose-frame ingest pipeline is deferred past the September launch — client-side preview only, no MDR/IEC-62304 scope today.

Patient catalog & commerce — 000032–000038 ​

  • catalog_sections · catalog_entries (000032) — the patient-facing catalog. An entry is one placement of a content item into a section; content_type is program or session (audio is sessions(kind='audio'), not a separate content type).
  • 000033_content_gating — adds required_entitlement gating columns; creates no tables.
  • patient_content_grants (000034) — per-patient ownership override on the catalog branch (free OR grant OR tierHas(code)). Never applies to prescriptions.
  • access_offers · access_offer_items · access_offer_fulfillments (000035) — F14 bundles: one offer, N items, grant_kind selecting content-grant vs tier-subscription shape. Fulfillments are append-only, admin-pool-written.
  • 000036_shop_integration_services — seeds shop rows into integration_services; creates no tables.
  • access_offer_sku_bindings · access_offer_orders (000037) — external SKU → offer binding and the buyer's claim.
  • access_offer_campaigns (000038) — clinic-authored campaign cards bound to an offer (the free trigger's public face).

Full model: /architecture/patient-catalog-and-access.

000039_restore_safe_principal_is_human ​

Function-only — restores principal_is_human(). No tables.

Count by area ​

AreaMigrationsTables
Audit0000012
Identity, tenancy & RBAC00000211
Organization configuration0000034
Org plans & subscriptions00000410
Patient tiers0000054
Patient identity0000063
Patient subscriptions0000074
Consents0000083
Legal documents0000092
Notifications0000104
Access control & elevation000011–0000134
Locations0000141
Integrations (Cat A–D)000015–0000186
Metering & AI000019–0000205
Ownership transfers0000211
Clinical content000022–00002617
Exercise taxonomy & pose000027–00003119
Patient catalog & commerce000032–0000389
Total000001–000039109

Not built (no tables exist) ​

These appear in feature specs and in older revisions of this page. None of them has a table today. Do not treat spec SQL for them as schema — read the spec for intent, and check /implementation-plan/platform-completion for what is actually in scope.

ConceptStatusWhere the design lives
specialists, specialist_specialtiesNot built (F1)features/specialists/
specialtiesNot built (F1) — per-org when it shipsfeatures/specialties/
offerings (+ junctions)Not built (F2.1)features/services/ — spec still filed under the old services name; the table ships as offerings from day one, no interim rename
form_templates, form_template_versions, formsNot built (F3)features/forms/
custom_fields, custom_field_versions, custom_field_valuesNot built (F3)features/custom-fields/
calendars, availability, specialist_hours, specialist_date_overridesNot built (F4)features/scheduling/
appointments, appointment_filesNot built (F5)/architecture/appointments-substrate
documents, pdf_templatesNot built (F6)features/documents/, features/pdf-templates/
segments, segment_membersOut of scope (F8)features/segments/
appointment_templates (+ junctions)Superseded, never builtReplaced by the offerings design
treatment_plans familySuperseded, never builtReplaced by programs + sessions + protocols — see below
pose_session_metrics, pose_rep_metricsNot builtPose-frame ingest deferred past September
patient_exercise_logsNever builtReplaced by session_exercise_events + telemetry media metrics

The legacy treatment-plan design maps forward as: treatment_plans → programs + protocols; treatment_plan_versions → three-tier copy-on-derive (no snapshots); treatment_plan_sessions → sessions.program_id / phase_id / order_in_phase; treatment_plan_session_exercises → session_exercises; patient_treatment_plans → protocols(kind='prescription'|'enrollment'); patient_session_completions → session_runs + session_exercise_events.

Vocabulary

Bare prescription means the shipped exercise-program sense (protocols.kind = 'prescription'). The F6 PDF document type is medical_prescription. They are different things.

Actor model: principals as root identity ​

There is no users table and no user_id column anywhere. Every actor — human, AI agent, integration service account, system job — is a row in principals. Profile data lives in sibling tables keyed by principal_id: humans today, agents and service_accounts alongside.

  • Any actor type valid → the column is principal_id UUID REFERENCES principals(id). Examples: *.created_by_principal_id, audit_log.actor_id, organization_memberships.principal_id.
  • A human is semantically required (patient identity, caregiver, medical approval) → the column is human_id UUID REFERENCES humans(principal_id). The FK target enforces the constraint; no CHECK needed.
  • Superadmin stays human-only via a CHECK on platform_memberships, not by table structure.

Rationale: /architecture/decisions#why-principals-as-the-root-identity.

Multi-tenancy shapes ​

RLS is enabled on all 109 tables (294 policies). 66 of them carry an organization_id column; 43 do not — "every table has organization_id" is not true and never was. Tenant scoping takes one of five shapes.

1. Org-scoped (the default). organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE, always the long column name (org_id is reserved for wire forms — JWT claims, log keys, query params, S3 path templates).

sql
CREATE TABLE example_table (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
    -- ...
);

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());

2. Dual-scope (platform + org) — organization_id present but nullable. NULL means "platform-owned", non-NULL means "this clinic's". exercises, programs, sessions, and the catalog tables use ownership_kind ∈ {platform, org} with a CHECK tying it to the column (P49); roles uses NULL for system role templates; consent_purpose_versions uses NULL for platform-default policy text; the dual-scope exercise vocabularies (exercise_categories, exercise_equipment, exercise_conditions) let clinics add private entries. Read policies admit organization_id IS NULL OR organization_id = current_app_org_id().

audit_log.organization_id is nullable for a different reason: platform-level actions genuinely have no org context. It is not a dual-scope catalog.

3. Platform-global reference catalogs — no organization_id at all. permissions, tiers, tier_versions, entitlements, limit_definitions, tier_entitlements, tier_limits, consent_purposes, legal_document_templates, integration_services, ai_models, ai_model_pricing_history, pose_engines, pose_landmarks, inbound_webhook_dedup. Several are read on principal-less public paths, so their SELECT policy is USING (TRUE) and mutations are revoked from restartix_app at the grant layer. A principal_id IS NOT NULL guard here would break the public paths that legitimately read them — a real bug class, not a hypothetical one.

4. Scope derived from a parent — no organization_id, reached through an FK. role_permissions, audit_ai_provenance, notification_deliveries, outbound_webhook_deliveries, exercise_renders, exercise_tags, exercise_prerequisites, exercise_instructions, exercise_contraindications, exercise_pose_landmarks, exercise_pose_metrics, exercise_pose_feedback_rules, and the organization_subscription_* / patient_subscription_* / patient_tier_* snapshot + junction tables. Their policy joins the parent rather than duplicating a column.

5. Principal-owned. notification_preferences is keyed (recipient_principal_id, category, channel) with self-only policies — a person's notification choices follow the person, not a clinic.

The documented exception: patient_profiles ​

patient_profiles is patient-owned, not org-owned — a single portable profile powers the patient's relationship with multiple clinics, so an organization_id would be wrong, not merely absent. Its policies are:

sql
-- The patient (or their caregiver) reads and writes their own profile.
CREATE POLICY patient_profiles_select_self_or_caregiver ON patient_profiles FOR SELECT
    USING (id = ANY (current_human_patient_profile_ids()));

-- Org staff read only profiles linked to their org by a live `patients` row.
CREATE POLICY patient_profiles_select_org_staff ON patient_profiles FOR SELECT USING (
    EXISTS (
        SELECT 1 FROM patients p
        WHERE p.patient_profile_id = patient_profiles.id
          AND p.organization_id    = current_app_org_id()
          AND p.deleted_at IS NULL
    )
);

There is no DELETE policy — deletion is never permitted via AppPool. GDPR erasure is anonymization on patient_profiles, and patients.deleted_at is the soft-delete lever on the per-org link. patient_caregivers follows the same patient-owned shape.

Two-pool architecture ​

RLS is enforced against the AppPool (restartix_app, a restricted role). Superadmin paths use the AdminPool (restartix, the owner role), which bypasses RLS entirely. There is no is_superadmin() function and no policy that checks for one — the bypass is a connection-level property, not a policy branch. Repos must read the RLS-scoped connection via ConnFromContext(ctx), never a raw pool handle.

Roughly 45 tables tighten this further by revoking DML from restartix_app at the SQL grant layer, as defense-in-depth behind the policy — a policy bug then still fails closed. Two broad groups:

  • Append-only / platform-written: audit_log, audit_ai_provenance, usage_records, usage_quotas, usage_summaries, notifications, notification_deliveries, inbound_webhook_dedup, outbound_webhook_deliveries, exercise_pose_config_history, pose_data_quality_overrides.
  • Platform-owned catalogs and authorization state: permissions, roles, role_permissions, principals, humans, agents, service_accounts, platform_memberships, organization_entitlements, the *_subscription_entitlements / *_subscription_limits / *_subscription_overrides snapshot tables, consent_purposes, consent_purpose_versions, consents, legal_document_templates, platform_service_providers, ai_models, ai_model_pricing_history, pose_engines, pose_landmarks, and the platform-only exercise vocabularies.

A leaf partition named directly bypasses both guards. Parent RLS is not enforced on direct-partition access, and a new child inherits the ALTER DEFAULT PRIVILEGES grant from 000001 that hands restartix_app full DML on every new table — so without an explicit revoke, the app role could UPDATE audit_log_2026_07 straight past the parent's append-only guard. Both the migrations (REVOKE ALL ON audit_log_2026_05 …) and the partition roller (internal/core/partitions, REVOKE ALL on every child it creates) close this. The revoke is safe because the app never names a partition directly — parent-routed DML is privilege-checked against the parent.

A few tables achieve the same effect with policy alone rather than grants: access_offer_fulfillments, for instance, simply has no INSERT/UPDATE/DELETE policy, so the AppPool can only read it.

RLS helper functions ​

These exist. Anything not on this list does not.

HelperReturnsReads
current_app_principal_id()UUIDapp.current_principal_id
current_app_principal_type()TEXTapp.current_actor_type
current_app_org_id()UUIDapp.current_org_id
current_app_role()TEXTapp.current_role — informational only, see the note below
current_app_has_permission(p_resource, p_action)BOOLEANmembership → role → permissions
current_app_has_org_entitlement(entitlement_code)BOOLEANorganization_entitlements
current_app_has_patient_access(p_patient_id)BOOLEANlive patients row at the current org and content.read
current_app_is_owner()BOOLEANorganization_memberships.is_owner
current_app_break_glass_id()UUIDapp.current_break_glass_id
current_app_impersonation_id()UUIDapp.current_impersonation_id
current_app_action_context()TEXTapp.current_action_context
current_app_ip()INETapp.current_ip
current_app_request_id()UUIDapp.current_request_id
current_app_request_method()TEXTapp.current_request_method
current_app_request_path()TEXTapp.current_request_path
current_app_user_agent()TEXTapp.current_user_agent
current_human_patient_profile_ids()UUID[]self + caregiver-managed profiles
current_human_is_patient_at(p_org)BOOLEANlive patients row at the org

Deliberately absent, and named here so nobody re-invents them:

  • current_app_user_id() — never existed. Use current_app_principal_id().
  • current_human_patient_person_ids() — never existed. Use current_human_patient_profile_ids().
  • is_superadmin() — see Two-pool architecture.
  • current_app_location_ids() — deliberately not added. Org-scoping is the only RLS dimension for locations; per-location scoping is a future ADR if a customer requires it.

current_app_has_patient_access() is a Phase 1 stub — its own COMMENT ON FUNCTION says so. It currently admits any staff member at the same org holding content.read, and tightens to care-team membership when the care_team table ships post-launch. Do not read it as a care-team check today.

Context is bound by SECURITY DEFINER setters, not by raw SET: set_app_principal, set_app_staff_context, set_app_patient_context, set_app_request_envelope, set_app_break_glass_session_id, set_app_impersonation_session_id. All use transaction-scoped set_config(..., true) — session-scoped state is unusable behind pgbouncer (P44).

Session variables ​

VariableTypePurpose
app.current_principal_idUUIDThe acting principal — the identity dimension of RLS
app.current_actor_typeTEXThuman | agent | service_account | system
app.current_org_idUUIDThe active org — the tenant dimension of RLS
app.current_roleTEXTDenormalised role code. Informational/audit only
app.current_break_glass_idUUIDActive break-glass session, when elevated
app.current_impersonation_idUUIDActive patient-impersonation session, when impersonating
app.current_request_idUUIDRequest envelope — join key across all audit rows from one request
app.current_ipINETRequest envelope
app.current_request_methodTEXTRequest envelope
app.current_request_pathTEXTRequest envelope
app.current_user_agentTEXTRequest envelope
app.current_action_contextTEXTRequest envelope

app.current_role is not an authorization mechanism

It is set by set_app_staff_context (which validates the membership first) purely so audit rows and diagnostics carry the role code. Authorization is per-org permission codes. Policies call current_app_has_permission(resource, action); handlers gate with RequirePermission. Comparing role strings — in SQL or in Go — is a bug. Add a permission instead. See /reference/rbac-permissions.

The request-envelope variables are all nullable. Outside an HTTP request (cron jobs, migration-time seeds) they read as SQL NULL, and audit_log stores NULL rather than a fabricated default.

Primary keys ​

Surrogate primary keys are id UUID PRIMARY KEY DEFAULT gen_random_uuid() (P26) — 71 tables use that exact declaration. Go generates UUIDv7 via uuid.NewV7() for time-ordered inserts and better b-tree locality; the gen_random_uuid() default produces v4 and exists only as a safety net for ad-hoc SQL. Never BIGSERIAL, never BIGINT, never integer IDs — feature-spec schemas under apps/docs/features/ that show BIGSERIAL predate this convention and are out of date.

The rest use a natural key rather than a surrogate:

  • Sibling-of-principals tables key on the parent: humans, agents, service_accounts all have principal_id UUID PRIMARY KEY REFERENCES principals(id).
  • One-row-per-org tables key on the org: organization_settings, organization_billing, organization_entitlements.
  • Join tables use the composite tuple: role_permissions(role_id, permission_code), patient_caregivers(patient_profile_id, caregiver_human_id), exercise_pose_landmarks(config_id, landmark_id), notification_preferences(recipient_principal_id, category, channel).
  • Partitioned tables must include the partition key in every unique constraint, so their PK is composite: audit_log(id, created_at), usage_records(id, occurred_at), outbound_webhook_deliveries(id, created_at).

Extensions ​

All six are created in 000001_init (1A.16), at the foundation layer, so a feature migration can use one without coordinating an extension-enable plus image-swap.

ExtensionUsed forIn use today
uuid-osspUUID primitivesNo — gen_random_uuid() is core PG13+
pgcryptoCrypto primitivesNo SQL-side call sites; encryption is done in Go
unaccentDiacritic folding — Romanian search without it breaks UX ("Stefan" ↛ "Ștefan")Yes, via immutable_unaccent()
pg_trgmTrigram GIN indexes for server-side typeaheadYes — 10 indexes
vectorpgvector embedding columns for AI featuresNot yet — enabled preemptively so the first AI feature is a column add
pg_stat_statementsTop-N slow-query observabilityTracks nothing unless shared_preload_libraries includes it

btree_gist is not enabled. It is what a GIST exclusion constraint on overlapping time ranges would need — the classic appointment double-booking guard. That is a future migration alongside F5, not a current fact.

Because unaccent() is STABLE, it cannot appear in an index expression directly. 000001 defines an immutable_unaccent() wrapper, and every searchable-name index uses it:

sql
CREATE INDEX idx_patient_profiles_name_trgm
    ON patient_profiles USING GIN (immutable_unaccent(name) gin_trgm_ops);

Ten such indexes exist today. Queries must fold both sides: unaccent(col) ILIKE unaccent('%' || $1 || '%').

Partitioned tables ​

Ten tables are range-partitioned monthly per P41: Range-Partitioned Event Tables. The rule: a table that records occurrences (append-only, time-ordered, multi-year retention) is partitioned from day one; a table that records state (mutable rows queried by entity ID) stays flat regardless of row count.

TablePartition keyMigration
audit_logcreated_at000001
audit_ai_provenanceaudit_log_created_at000001
notificationscreated_at000010
notification_deliveriesnotification_created_at000010
outbound_webhook_deliveriescreated_at000016
inbound_webhook_dedupprocessed_at000018
usage_recordsoccurred_at000019
session_pain_eventsreported_at000023
session_exercise_eventsoccurred_at000023
session_tv_livenessts000024

Two more live in the telemetry database: media_buffering_events and media_library_views, both on started_at.

Each migration seeds only the current month (_2026_05) per the minimal-seed pattern. Future months are rolled ahead by cmd/api-partition-roll -ahead=3 and cmd/telemetry-partition-roll -ahead=3, both wired as daily EventBridge crons at 02:00 UTC (staging and production) with a 3-day CloudWatch dead-man's-switch alarm on the success heartbeat. The binaries are idempotent, connect as the schema owner (DDL needs the owner role), and REVOKE ALL on every child they create.

There is no DEFAULT partition — a missed rollover surfaces as outright INSERT failures rather than silently piling rows into a catch-all. That loud failure is deliberate: a gap in audit_log must be visible immediately.

Retention: hot 0–12 months in PostgreSQL, warm 12mo–6yr in S3 archives, then purge. DROP PARTITION → S3 archive → purge is an O(1) hand-off. Break-glass logs, GDPR operation entries (7yr), and key-rotation events are never deleted.

Enums vs. CHECK constraints ​

The schema has exactly two PostgreSQL ENUM types, both in 000002:

sql
CREATE TYPE domain_type   AS ENUM ('clinic', 'portal');
CREATE TYPE domain_status AS ENUM ('pending', 'verified', 'failed');

Everything else is TEXT + a CHECK constraint. This is deliberate: adding a value to a PG enum is a schema change with awkward transactional semantics, while widening a CHECK is an ordinary ALTER. Representative constraint sets:

TableColumnAllowed values
exerciseskindreps_based, duration_based, static
exercisesstatusdraft, publishing, published, archived
exercisesownership_kindplatform, org
exercise_rendersstatuspending, rendering, transcoding, ready, failed
content_fileskindaudio, video, image, document
sessionskindexercise, audio
sessions / programsstatusdraft, published, archived
session_exercisesmodereps, hold, video_only
session_runsstatusin_progress, ended_naturally, ended_explicit, auto_closed
protocolskindprescription, enrollment
protocolsstatusactive, paused, completed, ended
patient_caregiversrelationshipself, parent, child, spouse, sibling, caregiver, other
patient_profilessexMale, Female, Other, Prefer not to say
patient_profilesblood_typeA+, A-, B+, B-, O+, O-, AB+, AB-

The appointment_status, form_type, form_status, exercise_difficulty, exercise_mode, treatment_plan_type, and treatment_plan_status enums that older revisions of this page listed do not exist in any form — their tables were never built. The authoritative appointment status machine, when F5 ships, is in /architecture/appointments-substrate; do not restate its value list elsewhere.

Roles are data, not an enum ​

There is no user_role type. Role codes are TEXT in the roles table, and every org gets its own cloned set of system role rows at creation — so custom per-org roles never need a schema change. The three seeded system role templates are:

CodeScopeDescription
specialistper-orgHealthcare provider. Own appointments + assigned patients.
customer_supportper-orgSupport staff. Reads most org data; limited writes.
adminper-orgOrganization manager. Full management within the org.

Two things older revisions got wrong here:

  • patient is not a role. Patients live in patient_profiles + patients; portal access follows from the existence of a patients row, not from a role grant. See /architecture/decisions → "Why patients are not memberships, and patient tiers are not roles."
  • superadmin is not in roles. It is a row in platform_memberships (human-only by CHECK), and its bypass is the AdminPool connection, not a policy. There is no platform_roles table.

A principal can hold a different role in each org they belong to. Full model: /reference/rbac-permissions.

Indexes ​

Every org-scoped table indexes organization_id — without it, RLS predicates degrade to sequential scans:

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

Beyond that, the production-scale rules in CLAUDE.md apply: the platform replaces a legacy product with 20k+ users, 11k+ treatment plans, and 5k+ active subscriptions, migrating on launch day. There is no small-dataset phase.

  • Any column the API filters or sorts on at scale needs an index. Adding a WHERE created_by_principal_id = ANY(...) filter without confirming coverage is a bug.
  • Searchable text columns get a trigram GIN over immutable_unaccent(col).
  • Partial indexes carry the soft-delete predicate where the query does: CREATE INDEX idx_patients_org_active ON patients(organization_id) WHERE deleted_at IS NULL.
  • Uniqueness that must tolerate soft-deleted history is partial: at most one live (patient_profile_id, organization_id) pair, with deleted rows accumulating freely.

Cascade deletes ​

Org-scoped tables declare ON DELETE CASCADE to organizations(id), so deleting an organization row removes its tenant data:

DELETE FROM organizations WHERE id = '018f...'::uuid
  ├── CASCADE: patients            (org link only — patient_profiles survives)
  ├── CASCADE: organization_memberships
  ├── CASCADE: programs, sessions, protocols, session_runs
  ├── CASCADE: outbound_webhook_subscriptions
  └── ... every other org-scoped table

Two things this does not mean:

  1. It is not the deletion path for a live clinic. Patient records are soft-delete-only; GDPR erasure is anonymization, not DELETE. A hard org delete is an operational action on an empty or abandoned tenant.
  2. patient_profiles does not cascade. The profile is portable and patient-owned; deleting one org removes that org's patients link and leaves the profile intact for the other clinics the patient attends.

Child tables inside a domain use ON DELETE CASCADE to their parent (session_exercises → sessions); reference tables that other rows depend on use ON DELETE RESTRICT (exercise_pose_landmarks → pose_landmarks) so a catalog row cannot vanish out from under a live config.

JSONB columns ​

JSONB is used where the shape is genuinely open-ended or where the alternative is a schema change per UI iteration — never as a substitute for a typed column that compliance will need to query. organization_settings is the explicit counter-example: new settings are typed column adds precisely so "show every clinic with marketing_email_enabled" stays a plain SQL query.

Representative uses:

PurposeWhere
i18n catalogstranslations on 17 tables (exercise names, instructions, catalog copy, campaign text)
Snapshots (P14b)entitlements_snapshot, limits_snapshot, metadata_snapshot on tier_versions / patient_tier_versions
Audit field-level diffaudit_log.changes
Open metadatacontent_files.metadata, usage_records.metadata
Engineering flagsorganization_settings.feature_flags — staged-rollout flags, not entitlements
Brandingorganizations.branding
Pose configexercise_pose_configs.rep_success_rule_params (+ the history mirror)
Cadenceprotocols.cadence_config
Forward-compat slotsprogram_phases.entry_criteria

GIN indexes exist where JSONB or array containment is actually queried — outbound_webhook_subscriptions.event_filters (JSONB) and programs.tags (array) today. Do not add a GIN index speculatively; add it with the query that needs it. Note that exercise_pose_metrics.landmark_refs is UUID[], not JSONB: PostgreSQL cannot put a real FK on an array element, so validation lives in the Go service layer.

Column classification & encryption ​

Every column carries a class and an allowed-egress list in /architecture/data-classification. Default is block — a column missing from the registry cannot leave the tenant. make check runs cmd/check-classification and fails the build when a migration adds a column without a registry entry in the same PR.

Column-level encryption is reserved for two classes:

  • auth_secret — credentials, API keys, signing secrets. Today: platform_service_providers.credentials_encrypted, organization_integrations.credentials_encrypted, organization_integrations.inbound_signing_secret_encrypted, outbound_webhook_subscriptions.signing_secret_encrypted (+ signing_secret_previous_encrypted).
  • pii_regulated — national IDs (CUI, CNP, SSN, passport). Today: organization_billing.tax_id_encrypted. Patient CNP lands as an encrypted BYTEA column on patient_profiles (national_id_encrypted, with a national_id_hmac blind index so a clinic can still search by it) — never in a generic key/value store, and never in forms.values; both routes are refused by database triggers.

Everything else — names, emails, phones, addresses, allergies, diagnoses — is plaintext plus layered defense (RLS + audit + at-rest disk encryption + encrypted backups + restricted DB access). patient_profiles.phone is the worked example: random-nonce AES-GCM makes partial / last-N-digit caller-ID lookup impossible, and that lookup is a required clinic feature. Rationale: /architecture/decisions#why-most-pii-is-plaintext-and-what-isn-t.

Wire format for the columns that do encrypt is [1-byte version][12-byte nonce][ciphertext+tag] in BYTEA, via internal/core/crypto/. Keys live in AWS KMS — never in code, config, or environment variables.

Not every BYTEA column is ciphertext. service_accounts.api_key_hash and audit_ai_provenance.inputs_hash are one-way digests. organization_ownership_transfers.accept_token is 32 raw crypto/rand bytes stored unhashed and unencrypted — a documented, deliberate choice (the migration comment says foundation defense-in-depth does not yet treat it as auth-grade; the upgrade path is SHA-256-at-rest with the raw token sent only in the email body).

Migrations ​

Managed by golang-migrate, sequential, in services/api/migrations/core/ (000001–000039 today; the next new migration is 000040). Telemetry has its own set under services/telemetry/migrations/.

bash
make migrate-up      # apply all pending (uses DATABASE_DIRECT_URL, port 5432)
make migrate-down    # roll back one
make migrate-reset   # wipe dev containers + volumes, re-migrate from scratch
make migrate-create name=add_foo
make partition-roll  # roll partitions 3 months ahead

Migrations run against DATABASE_DIRECT_URL on port 5432, not through pgbouncer on 6432 — golang-migrate takes a session-scoped pg_advisory_lock, which transaction pooling cannot honour (P44).

Production is live

Production has been serving real patients since 2026-06-05. A migration already applied to production can no longer be edited in place — a change to an applied migration needs a manual catch-up DDL script per environment. The canonical pattern is infra/scripts/000023-skip-note-prod.sql. Migrations not yet applied to any environment may still be edited or reordered freely. The forward-only freeze binds formally at the September launch gate, when the irreplaceable ~20k-user legacy migration lands.

Scaffold new work with /new-migration and /new-domain — they encode the full checklist (permission seeding, role-template grants, RLS policies, route gating, classification registry entry, doc updates).

Telemetry storage ​

Telemetry stores its data in the same Aurora cluster as the API (separate database, logical isolation) plus S3 for replay blobs. Deliberate change from an earlier "two separate databases / ClickHouse" design — see Why telemetry is PG + S3, not ClickHouse.

Tables that exist today (services/telemetry/migrations/, 2 migrations):

TableCardinalityPartitioning
media_session_metrics1 row/(run, media)None (state-shaped)
media_buffering_events~5 rows/sessionMonthly on started_at
media_library_views1 row/viewMonthly on started_at

Written by the in-memory aggregator when the terminal session_end event arrives — one media_session_metrics row per (run_id, media_id) plus N buffering rows in a single transaction. 000002_video_errors adds video_error rollup columns (error_count, etc.) rather than a new table.

pose_session_metrics and pose_rep_metrics do not exist. The pose-frame ingest pipeline is deferred past the September launch — the portal does client-side preview only. The clinical progress record for a run lives in the API database as session_exercise_events and session_pain_events, not in telemetry.

RLS is not enforced on telemetry tables. There is no per-tenant authenticated query path into that database; every row carries organization_id + patient_id, and tenant filtering is the responsibility of the API endpoints that read them. This is documented at the top of 000001_media_metrics.up.sql.

S3 replay blobs live at s3://restartix-telemetry/{org_id}/{session_id}.bin.gz — binary float32 + gzip, ~3 MB per 30-min session, lifecycled standard → IA → Glacier → expire, reachable only via short-lived signed URLs minted by the API.

No separate ClickHouse, no separate compliance Postgres, no TimescaleDB — all out of scope until a cross-tenant analytical workload forces the question. See /telemetry/#scaling-roadmap.