Master Data Model

The complete entity-relationship model for the platform, derived from every feature spec in apps/docs/features/. This is the single source of truth for what tables will exist across all 16 Areas, what columns they have, what relationships connect them, and what cross-cutting patterns apply.

Scope and shipped status. This doc covers both shipped schema (Area 1 — Foundation, partially shipped through 1A + 1B.1–1B.10 + 1C partial; see implementation-plan/foundation.md for which sub-phases have closed) and planned schema (Areas 2–16, scoped here so the foundation lands them correctly when their layer ships). For the canonical current schema, the migrations under services/api/migrations/core/ are authoritative; this doc is forward-looking design and may run ahead of code on unshipped Areas.

Why this exists. The phased implementation plan was sequencing features without a holistic view of the schema. That risks rework: every late feature surfaces an entity or column the foundation should have included. This doc is the holistic view — read every feature spec once, model every entity, decide once.

Companion docs. patterns.md catalogs every cross-cutting pattern the model relies on. dependency-map.md sequences the implementation order.

---

Cross-Cutting Conventions (apply to every table)

These are decided once, applied everywhere. See patterns.md for full definitions.

Convention	Rule
Primary keys	UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(). Go-side: UUIDv7 via uuid.NewV7(). (See "Schema reconciliation" below — feature spec docs use BIGSERIAL; that is out of date.)
Tenant column	organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE. Always indexed: CREATE INDEX idx_{table}_org ON {table}(organization_id).
RLS	Enabled on every tenant table. Policy template: WHERE organization_id = current_app_org_id() for SELECT, plus current_app_has_permission(resource, action) for mutations. See P1, P3, P4 in patterns.
Timestamps	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() with trigger_set_updated_at().
Soft delete	deleted_at TIMESTAMPTZ on clinical tables (P13). Repos default-filter WHERE deleted_at IS NULL.
Money	DECIMAL(10,2) + currency TEXT DEFAULT 'RON'. Never floats.
Encryption	Sensitive PII columns are BYTEA, named _encrypted suffix, AES-256-GCM via internal/core/crypto/ (P12).
Translations	Global content tables get translations JSONB NOT NULL DEFAULT '{}'. Org-scoped tables don't. (P21)
JSONB	Snapshots immutable. Query-needed JSONB indexed with GIN + jsonb_path_ops. (P25)

---

Entity Catalog by Area

Area 1: Foundation (Org, Principal, Auth, RBAC)

The actor model is principals as the root identity: every actor in the system — human, AI agent, integration service account, scheduled system job — is a row in principals. Profile data lives in a sibling table per actor type (humans, agents, service_accounts). Audit, RLS, RBAC, and every domain reference principals.id. There is no users table; humans are not privileged in the schema. See decisions.md → Why principals as the root identity for the full rationale.

The platform-level identity and authorization model. Already implemented for the most part; included here for completeness.

organizations

The root of multi-tenancy. Every tenant table FKs to this.

Column	Type	Notes
id	UUID PK
name	TEXT NOT NULL
slug	TEXT NOT NULL UNIQUE	URL-safe, used by domain routing
tagline, description	TEXT
email, phone, website, location	TEXT
logo_url, icon_url	TEXT	S3 keys
language_code	TEXT NOT NULL DEFAULT 'en'	ISO 639-1, drives translations (P21)
portal_self_signup_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Per-clinic toggle for portal walk-up signup (P22).
branding	JSONB NOT NULL DEFAULT '{}'	White-label branding payload (colors, theme, footer_text, etc.). Read as a blob by public-resolve.
tenancy_mode	TEXT NOT NULL DEFAULT 'shared' CHECK (tenancy_mode IN ('shared', 'dedicated'))	Tenancy topology — see Tenant Isolation. shared (default) = pooled platform infrastructure with logical isolation (RLS, prefix scoping, app-layer entitlement checks). dedicated = reserved for future per-tenant Clerk org + addon mechanisms (own S3 bucket, own CMK); not yet provisionable via API — every creation path lands shared today.
activated_at	TIMESTAMPTZ NULL	Lifecycle gate. NULL = draft (not routable from public endpoints); non-NULL = active. Today every creation path sets activated_at = NOW() because dedicated mode is not yet provisionable. Column is preserved as a reservation for when dedicated-mode provisioning ships and creation needs a draft window before infrastructure provisioning completes.
created_at, updated_at	TIMESTAMPTZ

Indexes. idx_organizations_draft (id) WHERE activated_at IS NULL is preserved — keeps the draft-state filter cheap regardless of how many orgs exist; cardinality stays tiny while only the dedicated-provisioning flow produces draft rows.

RLS. Members see their org. Updates gated by organizations.update. Inserts/deletes by superadmin only (AdminPool). Public-resolve. GET /v1/public/organizations/resolve returns 404 for draft orgs (activated_at IS NULL). Owner first-login bind and welcome-email queuing also gate on activated_at IS NOT NULL.

organization_domains

Custom domains per clinic (universal — available on both shared and dedicated tenancy modes as a visual-branding customization).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
domain	TEXT NOT NULL UNIQUE
domain_type	enum domain_type	`clinic
status	enum domain_status	`pending
verification_token	TEXT NOT NULL	DNS-01 token written to TXT record
verified_at, last_check_at	TIMESTAMPTZ NULL	last_check_at is updated on every verify attempt (success or failure)
created_at, updated_at	TIMESTAMPTZ

RLS. Org members see their domains. Mutations gated by organizations.manage_domains. Public-resolve policy reads verified rows when no session vars set.

organization_integrations (Foundation 1C.5 — design pending)

Per-org third-party API credentials for Connected Accounts (Cat B) — clinic-owned external accounts (Google Calendar, Slack, HubSpot, ...) the clinic configures via OAuth or API key. Sensitive. Not yet shipped — no migration creates this table. Conceptually a foundation companion table alongside organization_settings / _billing / _capabilities; the schema design is deferred to a dedicated discussion chat. The first OAuth-based clinic integration will be the first CONSUMER. See foundation.md § 1C.5 (Connected Accounts — Cat B) for the framing and glossary.md → Integration categories for the canonical Cat A vs Cat B distinction. The table sketch below is illustrative — the canonical design lands when 1C.5 implements. The 1C.5 sub-phase additionally introduces the platform-defined integration_services catalog (the list of available Cat B services, their OAuth/API-key shape, scopes, etc.) — schema also pending.

Sibling but distinct concern. Cat A platform-curated providers (SES, Daily.co, Twilio, Anthropic, ...) do NOT use organization_integrations. They resolve through the foundation platform_service_providers table (Foundation 1C.2) — credentials seeded from env / Secrets Manager with optional per-org override rows (e.g., a clinic with its own verified SES sender domain on either tenancy mode). Same encryption posture (P12), different scope (platform-default vs clinic-owned). Daily.co video at § 6.5 is a Cat A consumer of platform_service_providers, not organization_integrations.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
title	TEXT	Human-readable name
integration_service_id	UUID FK → integration_services(id)	Catalog reference (catalog itself ships at 1C.5)
credentials_encrypted	BYTEA NOT NULL	OAuth tokens / API keys, AES-256-GCM (P12)
created_at, updated_at	TIMESTAMPTZ

RLS. Admin only for read + write. Field-level encryption applied at repo layer.

organization_settings

Per-org operational and compliance knobs. 1:1 with organizations (PK = organization_id). Auto-created with defaults via trigger on organizations INSERT. See org-settings.md.

Column	Type	Notes
organization_id	UUID PK FK → organizations(id) ON DELETE CASCADE	1:1
marketing_email_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Org-level kill-switch. Layered on top of per-patient consent (P17).
marketing_sms_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Same shape, SMS channel.
audit_retention_months	INT NULL	Override of platform default (≥ 6 yr per CLAUDE.md). NULL = platform default. CHECK ≥ 72.
default_timezone	TEXT NULL	IANA (e.g., Europe/Bucharest). Org-wide fallback for scheduling display when neither location nor specialist supplies one. NULL = platform default (Europe/Bucharest for the RO launch). See P23 for the full resolution chain.
support_locale	TEXT NULL	ISO 639-1; locale for support emails when different from organizations.language_code.
feature_flags	JSONB NOT NULL DEFAULT '{}'	Internal staged-rollout flags (engineering kill-switches). Not plan-driven entitlements — those live in organization_subscription_entitlements (Area 16).
created_at, updated_at	TIMESTAMPTZ

RLS. SELECT for org members. UPDATE gated by organizations.update_settings. INSERT/DELETE blocked at policy layer (trigger-only).

organization_billing

Per-org billing pointers and contact. 1:1 with organizations. Auto-created on org provisioning. Regulated financial-data class — read access is itself audited.

Column	Type	Notes
organization_id	UUID PK FK	1:1
current_tier_id	UUID FK → plans(id) NULL	Denormalized pointer to the org's current base plan for fast admin-UI lookup. Canonical source is organization_subscriptions (Area 16).
billing_email	TEXT NULL	Where invoices and dunning go. Distinct from organizations.email.
billing_contact_name	TEXT NULL
billing_address_line1, billing_address_line2, billing_city, billing_postal_code	TEXT NULL	Structured fields, not freeform — required for tax invoicing in RO.
billing_country	TEXT NULL	ISO 3166-1 alpha-2.
tax_id_encrypted	BYTEA NULL	CUI for RO clinics. AES-256-GCM (P12) — tax IDs are PII in EU jurisdictions.
currency	TEXT NOT NULL DEFAULT 'RON'	Billing currency for this org's invoices.
external_customer_id	TEXT NULL	Stripe / Chargebee customer ID. NULL until billing system wires up.
payment_provider	TEXT NOT NULL DEFAULT 'manual'	`manual
created_at, updated_at	TIMESTAMPTZ

RLS. SELECT/UPDATE gated by organizations.manage_billing. INSERT/DELETE blocked.

organization_entitlements

Per-org regulated entitlement gates. The only read surface for clinical/regulated code (see middleware-composition.md § Regulated boundary). 1:1 with organizations. Defaults all FALSE — fail-closed regulatory posture.

Column	Type	Notes
organization_id	UUID PK FK	1:1
telerehab_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Unlocks treatment plans, exercise prescription, telerehab patient flows.
treatment_plans_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Subset of telerehab — a clinic can have plans without exercise videos.
video_consultations_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Daily.co / WebRTC integration unlock.
pose_estimation_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Camera-based measurement (likely Class IIa per CLAUDE.md).
created_at, updated_at	TIMESTAMPTZ

RLS. SELECT for org members (read on every clinical request via current_app_has_org_entitlement(entitlement_code)). No UPDATE policy — AppPool has zero write access. Only AdminPool (superadmin) writes. This is the single trust boundary into the regulated read surface. INSERT/DELETE blocked.

Audit. Every UPDATE uses action_context = 'org_entitlement_change' for distinct retention/alerting. New entitlements are column adds (typed, queryable, defaults FALSE).

locations

Physical locations (branches / sites) under an organization. Org owns 1..N locations. Org may have zero locations — a pure-telerehab clinic operates without any physical premises and all appointments carry location_id = NULL. Locations never cross orgs. Per-location entitlements are explicitly out of scope; entitlements stay org-wide. See P40: Locations as Logistics Layer.

Column	Type	Notes
id	UUID PK
organization_id	UUID NOT NULL FK → organizations(id) ON DELETE CASCADE
slug	TEXT NOT NULL	Lowercase, URL-safe (^[a-z0-9]+(-[a-z0-9]+)*$ at the service layer; auto-lowercased + trimmed on input). Unique per (organization_id, slug). Mutable — unlike organizations.slug (which lives in DNS hostnames), location slugs only appear in deep paths like /locations/main-floor; renaming costs at most a 404 on a stale bookmark. FKs use UUIDs.
name	TEXT NOT NULL	Display name (e.g., Centru, Băneasa)
timezone	TEXT NULL	IANA (e.g., Europe/Bucharest). NULL = inherit from organization_settings.default_timezone. See P23.
phone	TEXT NULL	Public contact at this location
email	TEXT NULL	Public contact at this location
address_line1	TEXT NULL	Structured — never freeform single-line.
address_line2	TEXT NULL
city	TEXT NULL
county	TEXT NULL
postal_code	TEXT NULL
country	TEXT NULL	Free TEXT — no ISO 3166-1 enforcement at this layer. Constraint can be added later non-breakingly when a UI form renders a country picker.
status	TEXT NOT NULL DEFAULT 'active'	'active' | 'inactive' | 'closed'. closed is terminal — service rejects transitions out (re-opening means a new row). inactive is reversible (renovation / lease pending / seasonal). Historical appointments referencing closed locations remain queryable.
closed_at	TIMESTAMPTZ NULL	Auto-stamped to clock_timestamp() when status flips to 'closed'. CHECK pins closed_at non-NULL iff status = 'closed'.
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, slug)	per-org slug uniqueness

Indexes. idx_locations_org ON locations(organization_id); partial idx_locations_active ON locations(organization_id) WHERE status = 'active' for booking-time lookups.

RLS. SELECT for org members. Mutations gated by locations.manage permission (granted to admin only by default in 000014 — not specialist, not customer_support). All staff in the org see all locations — no per-location RBAC scoping in v1; deferred until a real customer requires it (see P40).

Audit. Standard P10. No audit_log.location_id column — audit rows reach a location via the appointment / calendar / specialist row they reference.

Lifecycle (DELETE vs. close). Locations are configuration data, not clinical PHI — P13's "never hard-delete" applies to clinical tables, not configuration (see P13's "Tables that do NOT need it" list: services, calendars, form_templates, etc.). The DELETE endpoint is exposed for the rare "created in error, never used" case; the canonical retire-a-location flow is PATCH ... {status: "closed"} which preserves audit-trail clarity for any historical appointments / calendars / specialists already linked. Once Layer 2 ships and specialist_locations / calendars.location_id / appointments.location_id reference this table, DELETE will RESTRICT naturally on dependent rows — that's the intended steady-state behaviour.

Forward references (added in the same migration where each consuming table ships):

• specialist_locations(specialist_id, location_id) — many-to-many; specialists rotate across locations.
• specialist_weekly_hours.location_id NULL — NULL = remote/telerehab availability slot.
• specialist_schedule_overrides.location_id NULL — same convention.
• calendars.location_id NULL — calendars may be pinned to a location ("Centru initial assessment") or org-level virtual ("Telerehab follow-up").
• appointments.location_id NULL — set for in-person; NULL for telerehab/video sessions.

The "one true availability per specialist" invariant — a specialist physically cannot be in two places at once — is enforced at the DB layer when the specialist availability tables ship. See P40.

principals

Root identity registry. Every actor in the system — human, AI agent, integration service account, scheduled system job — has exactly one row here. Doesn't carry profile data; profile data lives in the type-specific sibling table (humans, agents, service_accounts).

Column	Type	Notes
id	UUID PK
principal_type	TEXT NOT NULL	'human' | 'agent' | 'service_account' | 'system' (CHECK)
parent_principal_id	UUID FK NULL → principals(id) ON DELETE RESTRICT	Delegation chain: "this principal acts on behalf of another" (e.g., agents acting under a specialist's standing authorization). Self-referential; no cascade so deleting the parent doesn't silently orphan delegated children. Unused until first delegation feature lights it up.
created_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ NULL	Soft-delete for departed actors; the row stays as a tombstone for audit referential integrity

There is no principals.organization_id. The tenant binding for non-human actors lives on the actor-type child tables (agents.organization_id, service_accounts.organization_id — both NOT NULL). Humans are multi-org via organization_memberships (and patient-side patients); the system singleton is platform-level. Putting the column on the child tables keeps the schema's column shape aligned with the actor's nature: humans never accidentally get an org binding, agents and service accounts always do — no trigger needed to enforce the asymmetry.

RLS. SELECT for self only — cross-tenant visibility of other principals runs through the actor-type child tables (humans / agents / service_accounts), each with its own policy keyed on the relationship that grants visibility. INSERT/UPDATE/DELETE only via AdminPool / trigger fan-out — no AppPool write policy. Same protection as roles / permissions.

Seeded. Migration creates a singleton 'system'-type principal (well-known UUID 00000000-0000-0000-0000-000000000001). Used as actor_id for unauthenticated paths, scheduled jobs, and external webhook handlers (Stripe, Daily.co, Twilio) — so audit rows always have a real actor, never NULL, never a fake row in humans.

humans

Externally-authenticated human profile (Clerk JWT today; the verifier package is provider-agnostic — see auth/doc.go). Replaces the legacy users table. Primary key is principal_id (FK to principals.id, ON DELETE CASCADE). All auth-aware code paths key off humans.

Column	Type	Notes
principal_id	UUID PK FK → principals(id) ON DELETE CASCADE	Same UUID as the principal row
provider_subject_id	TEXT UNIQUE	Nullable until provisioned. Provider-agnostic — JWT sub claim for Clerk / OIDC verifiers, or whatever a future provider surfaces.
provider_org_id	TEXT NULL	Auth-provider organisation identifier. Reservation column — always NULL today; populated only when dedicated mode ships its per-tenant Clerk org provisioner. See features/platform/tenant-isolation.md.
email	TEXT NOT NULL	Unique per (email, provider_org_id) via partial composite unique index humans_email_provider_org_unique (NULLS NOT DISTINCT). Today provider_org_id IS NULL for every row so the effective uniqueness is global on email; the composite shape future-proofs for dedicated mode without a migration.
confirmed, blocked	BOOLEAN
last_activity	TIMESTAMPTZ	Bump on every authenticated request (P35)
created_at, updated_at	TIMESTAMPTZ

Active-org derivation. No cached "current org" column. The "default org on first sign-in with no hostname context" is derived as MAX(last_used_at) across the principal's organization_memberships (staff) and patients (patient) rows. Patients are multi-org via patients, never through organization_memberships; the last_used_at column on patients mirrors the one on organization_memberships. Hostname-based routing ({slug}.clinic.restartix.pro / {slug}.portal.restartix.pro) carries the active-org choice for every authenticated request, so this derivation only matters at first login.

RLS. Self-read; admins read humans in their org via subquery (joining through organization_memberships); superadmin via AdminPool.

Atomic provisioning. The auth-provider webhook handler (Clerk today) inserts principals (type='human') + humans in one transaction. No trigger fan-out for identity — the provider → human path is a single domain operation.

agents

AI agent profile. Sibling table to humans. Single-org by design — agents.organization_id (NOT NULL FK to organizations(id)) is the canonical tenant binding. Per-org role grant lives in organization_memberships like staff humans, with a trigger constraint that non-human principals hold at most one membership and that membership's organization_id matches the corresponding agents.organization_id (or service_accounts.organization_id). Each (org, agent-name) pair is its own principal + agents row — agents are never shared across tenants.

Column	Type	Notes
principal_id	UUID PK FK → principals(id) ON DELETE CASCADE	Same UUID as the principal row
name, description	TEXT
model_provider	TEXT NOT NULL	'anthropic' | 'openai' | ... — denormalized today; reference to SOUP list (1.16+) added later
model_name	TEXT NOT NULL	e.g. 'claude-opus-4-7'
model_version	TEXT NULL	Pinned version, NULL = latest
scope	TEXT NULL	App-interpreted scope marker; first concrete agent feature defines structured shape if needed
system_prompt_ref	TEXT NULL	Pointer (S3 key, row id, git ref) — storage decided per-feature
configuration	JSONB NOT NULL DEFAULT '{}'	Per-feature parameters
enabled	BOOLEAN NOT NULL DEFAULT TRUE	Pause/resume without deleting
deleted_at	TIMESTAMPTZ NULL	Soft delete
created_at, updated_at	TIMESTAMPTZ

RLS. SELECT: visible to org members (joins to principals to check organization_id = current_app_org_id()). INSERT/UPDATE/DELETE: AdminPool only until the first agent-management feature ships its own permission codes + policies.

Indexes. idx_agents_active ON agents(principal_id) WHERE deleted_at IS NULL; idx_agents_model ON agents(model_provider, model_name).

service_accounts

Integration profile (clinic-installed Zapier connectors, EHR sync tools, custom webhook senders). Sibling to humans. Single-org by the same convention as agents.

Column	Type	Notes
principal_id	UUID PK FK → principals(id) ON DELETE CASCADE	Same UUID as the principal row
name, description	TEXT
integration_kind	TEXT NULL	'zapier' | 'ehr_sync' | 'webhook_sender' | ... — loose enum, app-interpreted
api_key_hash	BYTEA NOT NULL UNIQUE	SHA-256 of the high-entropy API key generated server-side at creation. Fast hash is the right algorithm for high-entropy keys; bcrypt/argon2 are for low-entropy passwords.
api_key_prefix	TEXT NULL	Short visible prefix for UI display, e.g. 'sa_live_a1b2'
expires_at	TIMESTAMPTZ NULL	NULL = no expiry
last_used_at, rotated_at, revoked_at	TIMESTAMPTZ NULL	Lifecycle markers
deleted_at	TIMESTAMPTZ NULL	Soft delete
created_at, updated_at	TIMESTAMPTZ

RLS. Same pattern as agents. Per-key scope and rate limits are deferred — the principal's role from organization_memberships is the entire authorization scope today; per-key restrictions ship with the first integration-management feature.

Indexes. idx_service_accounts_active ON service_accounts(principal_id) WHERE deleted_at IS NULL AND revoked_at IS NULL; idx_service_accounts_prefix ON service_accounts(api_key_prefix) WHERE api_key_prefix IS NOT NULL.

Future sibling tables (ship per-feature when a concrete need arrives):

• Platform-level non-human actors — observability agents, cross-org metric aggregators. Today only superadmin humans hold platform-level grants (platform_memberships is human-only by CHECK). When the first observability feature ships, decide between dropping the human-only CHECK + adding non-superadmin platform roles, or a separate platform_actor_grants table. PII access for these is governed by Layer 1.25 column-level data classification, not by where their role lives.

organization_memberships

M:M staff membership with per-org role. Renamed from principal_organizations — staff-only by definition; patients access an org through patient_profiles + patients, not through this table (see decisions.md → Why patients are not memberships, and patient tiers are not roles). principal_id works for any non-patient actor type (humans get role grants today; agents and service accounts get them when they ship). One role per principal per org.

Column	Type	Notes
principal_id	UUID FK → principals(id)
organization_id	UUID FK
role_id	UUID FK → roles	Per-org role assignment
last_used_at	TIMESTAMPTZ NULL	Reserved for P35 — bump on org-scoped requests; mirrored on patients for symmetric default-org derivation
invited_at	TIMESTAMPTZ NULL	Reserved for future invitation flow
invited_by	UUID FK NULL → principals(id)	The principal that issued the invitation
accepted_at	TIMESTAMPTZ NULL	Reserved for future invitation flow
created_at, updated_at	TIMESTAMPTZ
PK	(principal_id, organization_id)

roles

Per-org bundles of permissions, plus system templates. Versioning not needed — roles are mutable but each change is audited. Roles never apply to patients — patient entitlements live in patient_subscription_entitlements / patient_subscription_limits (Area 16).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NULL	NULL for system templates
code	TEXT NOT NULL	e.g., admin, specialist, customer_support, or org-defined. No patient system role — patients are not in the role machinery.
name, description	TEXT
is_system	BOOLEAN NOT NULL	TRUE for templates and their cloned-into-org copies
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, code)	per-org code uniqueness
Unique (partial)	code WHERE organization_id IS NULL	system templates share a flat namespace

permissions

Catalog of every permission the system knows about — for staff actions. Seeded by feature migrations; never UI-edited. The natural key (code) is the PK so RLS policies and role_permissions rows can reference it directly without a UUID join. No app.access_portal permission — portal access is granted by the existence of a patients row at the org, not by a permission grant. Patient-side entitlements (tier entitlements, tier limits) live in the catalog tables entitlements and limit_definitions in Area 16, which are shared with the org-side billing engine.

Column	Type	Notes
code	TEXT PK	resource.action, e.g. appointments.create. Stable identifier referenced from role_permissions and from current_app_has_permission(resource, action) policies.
resource	TEXT NOT NULL	e.g., appointments
action	TEXT NOT NULL	e.g., create, update, delete, manage_members, manage_domains, view, export
description	TEXT
created_at	TIMESTAMPTZ
Index	(resource, action)	non-unique — uniqueness is implicit from the code = resource.action convention

role_permissions

M:M between roles and permissions. References permissions(code) directly, not a UUID id.

Column	Type	Notes
role_id	UUID FK
permission_code	TEXT FK → permissions(code)
created_at	TIMESTAMPTZ
PK	(role_id, permission_code)

platform_memberships

Platform-level grants (superadmin; future support_engineer). Renamed from platform_roles — symmetric with organization_memberships (both are membership tables; only the scope differs). RLS-enabled with no policies → AppPool has zero access; only AdminPool reads. Superadmin stays a human-only concept by constraint, not by table structure — service accounts and agents do not get superadmin grants.

Column	Type	Notes
principal_id	UUID FK → principals(id)	CHECK constraint: the referenced principal must be type='human'
role	TEXT NOT NULL	superadmin initially. Companion platform_role_permissions table lands when a second platform role is added.
granted_at	TIMESTAMPTZ
granted_by_principal_id	UUID FK → principals(id)	Same CHECK constraint: granter must be a human
PK	(principal_id, role)

Trigger. clear_organization_memberships_on_superadmin_grant removes any tenant memberships when a superadmin grant is inserted (one-hat rule).

---

Area 2: People (Patient Profiles, Patients, Caregivers, Specialists, Specialties)

The patient identity model is the most non-obvious part of the platform. Read patterns.md P6/P7/P8 before changing anything here.

Patients are not memberships. Patients access an org through patients, never through organization_memberships. Portal access is implicit from the existence of a patients row; there is no patient system role and no app.access_portal permission grant. See decisions.md → Why patients are not memberships, and patient tiers are not roles.

patient_profiles

Portable patient identity. No organization_id. Renamed from patient_persons. RLS via P4 variant 2.

Column	Type	Notes
id	UUID PK
human_id	UUID FK NULL UNIQUE → humans(principal_id)	Auth account; NULL for account-less patients (managed by family). Patients are humans by domain definition; the FK target enforces this without a CHECK constraint.
name	TEXT NOT NULL
date_of_birth	DATE
sex	TEXT	`Male
phone	TEXT	Plaintext — pii_basic. Phone search is required (caller-ID + partial). See decisions.md → Why most PII is plaintext.
occupation, residence	TEXT
blood_type	TEXT	`A+
allergies	TEXT[]
chronic_conditions	TEXT[]
emergency_contact_name	TEXT
emergency_contact_phone	TEXT	Plaintext — pii_basic, kept consistent with phone.
insurance_entries	JSONB NOT NULL DEFAULT '[]'	Array of {provider, number, type}
created_at, updated_at	TIMESTAMPTZ

RLS. Self + caregivers (via current_human_patient_profile_ids()); org staff can SELECT (with field-level masking unless patients.profile_shared = TRUE). DELETE never permitted.

patient_caregivers

Caregiver / family-account links. No organization_id. Renamed from patient_person_managers — patient_caregivers reads as the actual domain concept; the relationship enum already uses the word "caregiver".

Column	Type	Notes
patient_profile_id	UUID FK → patient_profiles(id)
caregiver_human_id	UUID FK → humans(principal_id)	Caregivers are humans by domain definition
relationship	TEXT NOT NULL	`self
created_at	TIMESTAMPTZ
PK	(patient_profile_id, caregiver_human_id)

patients

Per-org link between an org and a patient_profile. Thin record. Existence of a row here grants the patient (or their caregiver) portal access at the org — no permission lookup, no role assignment.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
patient_profile_id	UUID FK → patient_profiles(id)
profile_shared	BOOLEAN NOT NULL DEFAULT FALSE	Set by signing the profile-sharing consent form (P8)
consumer_id	TEXT	External system ID (legacy/billing)
last_used_at	TIMESTAMPTZ NULL	Bumped on portal requests (P35); mirror of organization_memberships.last_used_at. Used to derive default org on first sign-in.
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13)
created_at, updated_at	TIMESTAMPTZ
Partial unique index	(patient_profile_id, organization_id) WHERE deleted_at IS NULL	At most one active row per (profile, org). Multiple soft-deleted historical rows can coexist — that's how per-clinic re-onboarding works: a withdrawn patient who returns gets a brand-new patients row + brand-new patient_subscriptions chain; the previous (soft-deleted) row stays as audit history. See decisions.md → Why per-clinic re-onboarding creates a fresh patients row.

RLS. Org staff + the patient themselves (via current_human_patient_profile_ids()).

RLS helper rename. current_human_patient_person_ids() → current_human_patient_profile_ids() to match the table rename. Same body, returns the union of patient_profiles.id where the human is the auth-account owner or a registered caregiver.

specialists

Healthcare provider per org.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
human_id	UUID FK NULL UNIQUE → humans(principal_id)	Linked auth account (NULL = calendar-only). Specialists are humans by domain definition.
name, title, description	TEXT
slug	TEXT NOT NULL
minicrm_name	TEXT	External system identifier
signature_url, avatar_url	TEXT	S3 keys
scheduling_timezone	VARCHAR(64)	IANA tz; NULL = unbookable (P23)
scheduling_active	BOOLEAN DEFAULT TRUE
deleted_at	TIMESTAMPTZ NULL	Soft delete
created_at, updated_at	TIMESTAMPTZ

specialties

Medical specialty categories per org.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
title	TEXT NOT NULL
slug	TEXT NOT NULL
created_at, updated_at	TIMESTAMPTZ
Unique	(slug, organization_id)

specialist_specialties

M:M junction.

Column	Type	Notes
specialist_id	UUID FK
specialty_id	UUID FK
organization_id	UUID FK	denormalized for direct RLS
PK	(specialist_id, specialty_id)

---

Area 3: Service Catalog (Services, Plans, Products)

What the org offers. Pure catalog — scheduling lives in Area 4.

services

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, slug, description	TEXT
category	TEXT NOT NULL DEFAULT 'consultation'	`consultation
specialty_id	UUID FK NULL
duration_minutes, buffer_minutes	INT
base_price	DECIMAL(10,2)
currency	TEXT DEFAULT 'RON'
is_addon	BOOLEAN DEFAULT FALSE	Can be added during an existing appointment
is_public, published	BOOLEAN DEFAULT FALSE
cover_url, video_url	TEXT
created_at, updated_at	TIMESTAMPTZ
Unique	(slug, organization_id)

service_specialists

Column	Type	Notes
service_id	UUID FK
specialist_id	UUID FK
organization_id	UUID FK
is_active	BOOLEAN DEFAULT TRUE
custom_price	DECIMAL(10,2) NULL	Overrides service.base_price
PK	(service_id, specialist_id)

service_forms

Forms auto-generated when an appointment is booked for this service.

Column	Type	Notes
service_id	UUID FK
form_template_id	UUID FK
organization_id	UUID FK
form_type	enum	`survey
sort_order	INT
PK	(service_id, form_template_id)

service_attachments

Column	Type	Notes
id	UUID PK
service_id	UUID FK
organization_id	UUID FK
file_url, file_name, file_type	TEXT	S3 key
file_size	BIGINT	bytes
created_at	TIMESTAMPTZ

service_plans

Multi-session packages and subscription plans.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
service_id	UUID FK NULL	NULL for platform-level plans (e.g., "Full Access 1 Year")
name, description	TEXT
plan_type	TEXT NOT NULL DEFAULT 'session_based'	`session_based
sessions_total	INT NULL	Required for session_based + hybrid
treatment_plan_assignments_total	INT NULL	Optional counter for specialist-assigned treatment plans included with this enrollment. Used by tier inclusions (Area 16) for "X treatment plans / period" entitlements. NULL = not applicable.
validity_days	INT NULL	Session-based expiry window
access_months	INT NULL	Required for time_based + hybrid
telerehab_access	BOOLEAN DEFAULT FALSE	Specialist can assign telerehab plans
library_access	BOOLEAN DEFAULT FALSE	Patient can self-browse and self-assign
total_price	DECIMAL(10,2)
currency	TEXT DEFAULT 'RON'
is_published	BOOLEAN DEFAULT FALSE
created_at, updated_at	TIMESTAMPTZ
CHECK	plan_type matches required config

patient_service_plans

Patient enrollment + progress tracking.

Column	Type	Notes
id	UUID PK
patient_id	UUID FK
service_plan_id	UUID FK
organization_id	UUID FK
sessions_total, sessions_completed, sessions_cancelled	INT	Frozen from plan at enrollment
access_starts_at, access_expires_at	TIMESTAMPTZ	For time_based + hybrid
expires_at	TIMESTAMPTZ	For session_based + hybrid (enrolled_at + validity_days)
status	TEXT DEFAULT 'active'	`active
enrolled_at	TIMESTAMPTZ DEFAULT NOW()
source_tier_subscription_id	UUID FK NULL → patient_subscriptions(id)	NULL = patient purchased directly (existing flow). Set = auto-granted by a tier subscription (Area 16). Soft-expired when the source tier subscription ends.
created_at, updated_at	TIMESTAMPTZ

products

Reference catalog of physical goods (no e-commerce).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, slug, description	TEXT
price	DECIMAL(10,2)
currency	TEXT DEFAULT 'RON'
is_active	BOOLEAN DEFAULT TRUE
created_at, updated_at	TIMESTAMPTZ
Unique	(slug, organization_id)

service_plan_products

Junction: which products are bundled with a plan.

Column	Type	Notes
id	UUID PK
service_plan_id	UUID FK
product_id	UUID FK
organization_id	UUID FK
quantity	INT NOT NULL DEFAULT 1
created_at	TIMESTAMPTZ
Unique	(service_plan_id, product_id)

---

Area 4: Scheduling (Calendars, Hours, Overrides)

When and how services can be booked. Calendars are the bookable unit.

calendars

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, slug, description	TEXT
service_id	UUID FK NOT NULL	Required — provides duration/price defaults
modality	TEXT NOT NULL DEFAULT 'online'	`online
slots_open_at, slots_close_at	TIMESTAMPTZ NULL	Booking window
horizon_days	INT NOT NULL DEFAULT 30	How far ahead patients can book
cooldown_minutes	INT NOT NULL DEFAULT 1440	Anti-spam
override_duration_minutes, override_buffer_minutes	INT NULL	Override service defaults
override_price	DECIMAL(10,2) NULL
is_free	BOOLEAN DEFAULT FALSE
assignment_strategy	TEXT NOT NULL DEFAULT 'priority'	`priority
is_public, published	BOOLEAN DEFAULT FALSE
created_at, updated_at	TIMESTAMPTZ
Unique	(slug, organization_id)

calendar_specialists

Column	Type	Notes
calendar_id	UUID FK
specialist_id	UUID FK
organization_id	UUID FK
priority	INT NULL	NULL = manual-only
override_weekly_hours	JSONB NULL	Per-calendar override (e.g., {"wed":[{"start":"10:00","end":"12:00"}]})
PK	(calendar_id, specialist_id)

calendar_forms

Forms specific to this calendar (merged with service_forms on appointment creation).

Column	Type	Notes
calendar_id	UUID FK
form_template_id	UUID FK
organization_id	UUID FK
form_type	enum	(same as service_forms.form_type)
sort_order	INT
PK	(calendar_id, form_template_id)

specialist_weekly_hours

Column	Type	Notes
id	UUID PK
specialist_id	UUID FK
day_of_week	enum	`mon
start_time, end_time	TIME
Unique	(specialist_id, day_of_week, start_time, end_time)

specialist_schedule_overrides

Date-specific availability overrides (vacations, extra hours).

Column	Type	Notes
id	UUID PK
specialist_id	UUID FK
start_date, end_date	TIMESTAMPTZ
availability	BOOLEAN NOT NULL	TRUE = available, FALSE = unavailable
created_at, updated_at	TIMESTAMPTZ

specialist_assignment_tracking

Round-robin counters per calendar.

Column	Type	Notes
calendar_id	UUID FK
specialist_id	UUID FK
organization_id	UUID FK
last_assigned_at	TIMESTAMPTZ DEFAULT NOW()
assignment_count	INT DEFAULT 0
Unique	(calendar_id, specialist_id)

---

Area 5: Appointments

appointments

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
calendar_id	UUID FK NULL	NULL for direct registrations
service_id	UUID FK NULL	Always set in practice (from calendar or direct)
patient_profile_id	UUID FK NULL	References patient_profiles (P6)
specialist_id	UUID FK NULL
specialty_id	UUID FK NULL
contact_email	TEXT	For pre-account notifications (status='booked'); name+phone live on patient_profiles
booking_client_id	TEXT	Anonymous session id from public form
additional_service_ids	UUID[] DEFAULT '{}'	Add-ons added during appointment
additional_product_ids	UUID[] DEFAULT '{}'
patient_service_plan_id	UUID FK NULL	When this appointment is part of a multi-session plan
plan_session_number	INT NULL	"Session 3 of 10"
title	TEXT NOT NULL
status	enum	`booked
started_at, ended_at	TIMESTAMPTZ NULL	NULL for service-D-style registrations without time
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13)
created_at, updated_at	TIMESTAMPTZ

RLS. Org staff (admin/specialist/customer_support); specialist sees their own; patient sees their own (via current_human_patient_profile_ids()).

appointment_files

Column	Type	Notes
id	UUID PK
appointment_id	UUID FK
organization_id	UUID FK
file_url, file_name, file_type	TEXT	S3
file_size	BIGINT
created_at	TIMESTAMPTZ

appointment_reviews

Patient feedback after done. Low ratings trigger alerts.

Column	Type	Notes
id	UUID PK
appointment_id	UUID FK UNIQUE	One review per appointment
organization_id	UUID FK
rating	INT NOT NULL CHECK 1-5
comment	TEXT
alert_triggered, alert_acknowledged	BOOLEAN
created_at	TIMESTAMPTZ

---

Area 6: Custom Fields + Profile Fields

See P19 in patterns.

custom_fields

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
entity_type	TEXT NOT NULL	`patient
key	TEXT NOT NULL	Admin-chosen identifier
label	TEXT NOT NULL	Display
field_type	TEXT NOT NULL	`text
options	JSONB	for select/radio/checkbox
description	TEXT	help text
is_private	BOOLEAN DEFAULT FALSE	Specialist-only visibility (excluded from patient PDFs)
sort_order	INT DEFAULT 0
system_key	TEXT NULL	Stable identifier for PDF templates (immutable; enforced at app layer)
version	INT NOT NULL DEFAULT 1	Current version (P18)
published	BOOLEAN NOT NULL DEFAULT FALSE	Publication state
published_at	TIMESTAMPTZ NULL
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, entity_type, key)
Unique	(organization_id, system_key)

Versioning columns (version, published, published_at) are required here, paired with custom_field_versions (next entry). features/custom-fields/versioning.md describes the workflow.

custom_field_versions

Append-only history (P14a, P18).

Column	Type	Notes
id	UUID PK
custom_field_id	UUID FK
version	INT NOT NULL
fields_snapshot	JSONB NOT NULL	Full field state at this version
published_at	TIMESTAMPTZ NOT NULL
changed_by	UUID FK NULL
created_at	TIMESTAMPTZ
Unique	(custom_field_id, version)

custom_field_values

Per-entity value storage.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
custom_field_id	UUID FK
entity_type, entity_id	TEXT, UUID	Polymorphic (P24)
value	TEXT	Plaintext for queryability
created_at, updated_at	TIMESTAMPTZ
Unique	(custom_field_id, entity_type, entity_id)

---

Area 7: Forms

form_templates

Form designs. Versioned (P18).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
type	enum form_template_type	`survey
fields	JSONB NOT NULL DEFAULT '[]'	Field arrangement (references custom_field_id and/or profile_field_key)
version	INT NOT NULL DEFAULT 1
published	BOOLEAN NOT NULL DEFAULT FALSE
published_at	TIMESTAMPTZ NULL
pdf_template_id	UUID FK NULL	Which PDF to use when this form is rendered
created_at, updated_at	TIMESTAMPTZ

form_template_versions

Append-only history.

Column	Type	Notes
id	UUID PK
form_template_id	UUID FK
version	INT NOT NULL
fields_snapshot	JSONB NOT NULL
published_at	TIMESTAMPTZ NOT NULL
Unique	(form_template_id, version)

forms

Form instances. Snapshots template at creation. Immutable after signed (P14b).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
appointment_id	UUID FK NULL
form_template_id	UUID FK NULL
template_version	INT	Version snapshotted
patient_profile_id	UUID FK NULL	Owner (P6)
title, description	TEXT
type	enum form_template_type
fields	JSONB NOT NULL	Snapshot of template fields at creation
values	JSONB NOT NULL DEFAULT '{}'	Submission data, GIN-indexed
files	JSONB DEFAULT '{}'	File references keyed by field key
sort_order	INT DEFAULT 0
status	enum	`pending
completed_at, signed_at	TIMESTAMPTZ
created_at, updated_at	TIMESTAMPTZ

---

Area 8: Segments (Patient Cohorts)

segments

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
rules	JSONB NOT NULL DEFAULT '[]'	Array of {source, ...}; sources: `form
match_mode	TEXT NOT NULL DEFAULT 'all'	`all (AND)
version	INT NOT NULL DEFAULT 1
created_at, updated_at	TIMESTAMPTZ

segment_members

Materialized cache of evaluation results.

Column	Type	Notes
segment_id	UUID FK
patient_id	UUID FK
organization_id	UUID FK
matched_at	TIMESTAMPTZ DEFAULT NOW()
PK	(segment_id, patient_id)

segment_versions

Append-only history.

Column	Type	Notes
id	UUID PK
segment_id	UUID FK
organization_id	UUID FK
version	INT NOT NULL
rules	JSONB NOT NULL
match_mode	TEXT NOT NULL
changed_by	UUID FK NULL
created_at	TIMESTAMPTZ
Unique	(segment_id, version)

---

Area 9: Telerehab — Exercise Library

Dual-scope (P20): organization_id IS NULL for global, set for org-specific.

exercises

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NULL	NULL = global, NOT NULL = org-specific
name, slug, description	TEXT
instructions_summary	TEXT
difficulty	enum	`beginner
estimated_duration_seconds	INT
video_url	TEXT	CDN URL (Bunny Stream / S3)
video_provider	TEXT	`bunny_stream
video_thumbnail_url	TEXT
video_duration_seconds	INT
status	enum	`draft
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13)
cloned_from_id	UUID FK NULL	Clone lineage
created_by_principal_id	UUID FK NULL → principals(id)	Any actor type can create — humans today, agents/service accounts when those ship
translations	JSONB NOT NULL DEFAULT '{}'	(P21b) — only for organization_id IS NULL rows
created_at, updated_at	TIMESTAMPTZ

exercise_categories, exercise_body_regions, exercise_equipment

Same dual-scope pattern. All three need translations JSONB (P21).

Common columns
id	UUID PK
organization_id	UUID FK NULL
name, slug	TEXT
sort_order	INT
translations	JSONB

exercise_categories adds parent_id (hierarchical, description). exercise_body_regions adds body_area (upper_body | lower_body | core | full_body). exercise_equipment adds icon_url.

exercise_tags

Polymorphic junction (P24).

Column	Type	Notes
exercise_id	UUID FK
tag_type	TEXT	`category
tag_id	UUID FK	resolved against the appropriate table
PK	(exercise_id, tag_type, tag_id)

exercise_instructions

Column	Type	Notes
id	UUID PK
exercise_id	UUID FK
sort_order	INT
title, content	TEXT	content is markdown
image_url	TEXT	S3
instruction_type	TEXT	`preparation
translations	JSONB
created_at, updated_at	TIMESTAMPTZ

exercise_contraindications

Column	Type	Notes
id	UUID PK
exercise_id	UUID FK
condition_name, description	TEXT
severity	TEXT	`warning
translations	JSONB
created_at	TIMESTAMPTZ

---

Area 10: Telerehab — Treatment Plans

Three scopes (global / org / custom-per-patient via created_for_patient_id).

treatment_plans

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NULL	NULL = global
name, description	TEXT
type	enum	`telerehab
visibility	TEXT NOT NULL DEFAULT 'internal'	`internal
created_for_patient_id	UUID FK NULL	Custom plan for one patient
created_from_id	UUID FK NULL	Clone source
condition_tags	TEXT[]	GIN-indexed for library browsing
frequency_per_week, duration_weeks	INT
total_sessions	INT GENERATED	frequency_per_week * duration_weeks
specialist_id, specialty_id	UUID FK NULL
requires_approval	BOOLEAN
post_session_form_template_id	UUID FK NULL
service_plan_id	UUID FK NULL
status	enum	`draft
version	INT NOT NULL DEFAULT 1
published	BOOLEAN NOT NULL DEFAULT FALSE
deleted_at	TIMESTAMPTZ NULL
created_by_principal_id	UUID FK NULL → principals(id)	Any actor type can create — humans today, agents/service accounts when those ship
translations	JSONB	(P21) — only for global rows
created_at, updated_at	TIMESTAMPTZ

treatment_plan_versions

Column	Type	Notes
id	UUID PK
treatment_plan_id	UUID FK
organization_id	UUID FK NULL	mirrors plan
version	INT NOT NULL
(snapshotted plan fields: name, description, type, frequency_per_week, duration_weeks, requires_approval, post_session_form_template_id)
sessions_snapshot	JSONB NOT NULL	Full sessions+exercises (immutable)
created_by_principal_id	UUID FK → principals(id)	Any actor type can author the version snapshot
created_at	TIMESTAMPTZ
Unique	(treatment_plan_id, version)

treatment_plan_sessions

Template sessions (live structure).

Column	Type	Notes
id	UUID PK
treatment_plan_id	UUID FK
organization_id	UUID FK NULL
session_number	INT NOT NULL
name, description	TEXT
estimated_duration_minutes	INT
translations	JSONB	for global plans
created_at, updated_at	TIMESTAMPTZ
Unique	(treatment_plan_id, session_number)

treatment_plan_session_exercises

Per-session exercise config.

Column	Type	Notes
id	UUID PK
session_id	UUID FK
exercise_id	UUID FK	ON DELETE RESTRICT (prevents deleting referenced exercises)
organization_id	UUID FK NULL
sort_order	INT
mode	enum	`duration
sets, reps, hold_seconds, duration_seconds	INT
rest_between_sets_seconds, rest_after_exercise_seconds	INT
notes	TEXT
created_at, updated_at	TIMESTAMPTZ

patient_treatment_plans

Patient enrollment.

Column	Type	Notes
id	UUID PK
patient_id	UUID FK
treatment_plan_id	UUID FK
treatment_plan_version	INT NOT NULL	Frozen at assignment
organization_id	UUID FK
assigned_by_specialist_id	UUID FK NULL	NULL = self-assigned
self_assigned	BOOLEAN NOT NULL DEFAULT FALSE
disclaimer_form_instance_id	UUID FK NULL	Required when self_assigned=TRUE
patient_service_plan_id	UUID FK NULL
start_date, end_date	DATE
frequency_per_week, sessions_total, sessions_completed, sessions_skipped	INT	Frozen from version
status	enum treatment_plan_status
approved_at, approved_by_human_id	TIMESTAMPTZ / UUID FK → humans(principal_id)	Medical approval requires a licensed human; FK target enforces this
created_at, updated_at	TIMESTAMPTZ

patient_session_completions

Per-session execution tracking.

Column	Type	Notes
id	UUID PK
patient_treatment_plan_id	UUID FK
session_number	INT NOT NULL
organization_id	UUID FK
started_at, completed_at	TIMESTAMPTZ
duration_seconds	INT	actual
status	TEXT	`in_progress
exercises_total, exercises_completed, exercises_skipped	INT
post_session_form_id	UUID FK NULL	links to forms
pain_level_before, pain_level_after	INT 0-10
perceived_difficulty	INT 1-5
notes	TEXT
appointment_id	UUID FK NULL	For in_clinic plans
created_at, updated_at	TIMESTAMPTZ
Unique	(patient_treatment_plan_id, session_number, started_at)

patient_exercise_logs

Per-exercise tracking within a session.

Column	Type	Notes
id	UUID PK
session_completion_id	UUID FK
exercise_id	UUID FK NULL	ON DELETE SET NULL
organization_id	UUID FK
sort_order	INT
started_at, completed_at	TIMESTAMPTZ
prescribed_mode	enum	snapshot
prescribed_sets, prescribed_reps, prescribed_hold_seconds, prescribed_duration_seconds	INT
actual_sets, actual_reps, actual_hold_seconds, actual_duration_seconds	INT
video_watched	BOOLEAN
video_watch_seconds	INT
video_watch_percentage	DECIMAL(5,2)
pose_data_captured	BOOLEAN
pose_accuracy_score	DECIMAL(5,2)
skipped	BOOLEAN
skip_reason	TEXT
created_at, updated_at	TIMESTAMPTZ

---

Area 11: Documents

pdf_templates

Block-based PDF designer output.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
template_type	TEXT	`report
template_html, template_css	TEXT	Go template syntax
editor_state	JSONB NOT NULL DEFAULT '{}'	Block editor structure
layout_config	JSONB NOT NULL DEFAULT '{}'	pageSize, orientation, margins, etc.
version	INT NOT NULL DEFAULT 1
published	BOOLEAN NOT NULL DEFAULT FALSE
components_used	JSONB	List of component names
created_by, updated_by	UUID FK NULL
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, name)

pdf_template_versions

Column	Type	Notes
id	UUID PK
template_id	UUID FK
organization_id	UUID FK
version	INT NOT NULL
published_at	TIMESTAMPTZ
(snapshotted template fields)
changed_by	UUID FK NULL
change_notes	TEXT
created_at	TIMESTAMPTZ
Unique	(template_id, version)

pdf_template_components

Reusable blocks (letterhead, footer, signature).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
component_html, component_css	TEXT
category	TEXT	`header
thumbnail_url	TEXT
variables_used	TEXT[]	e.g., ['Organization.LogoURL', 'Patient.Name']
created_by	UUID FK NULL
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, name)

appointment_documents

Generated PDFs (reports + prescriptions unified by type).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
appointment_id	UUID FK
generated_by_principal_id	UUID FK NULL → principals(id)	The actor that generated the document. Humans today; agents acting under specialist delegation in the future. The medical responsibility lives in the form's specialist signature, not here.
form_id	UUID FK NULL	Source form (the report/prescription is a rendering of this form)
type	enum	`report
pdf_template_id	UUID FK NULL
pdf_template_version	INT NULL	Frozen at generation
title	TEXT NOT NULL
document_url	TEXT	S3
published	BOOLEAN
metadata	JSONB	PDF generation metadata
created_at, updated_at	TIMESTAMPTZ
Unique	(appointment_id, type)	one per type per appointment

appointment_document_files

Additional files attached to documents.

Column	Type	Notes
id	UUID PK
document_id	UUID FK
organization_id	UUID FK
file_url, file_name, file_type	TEXT
file_size	BIGINT
created_at	TIMESTAMPTZ

Historical note. Earlier feature specs proposed two competing designs — document_templates (HTML/CSS templates with margins) vs pdf_templates (block-based editor + JSONB state + components library). The block-based design won; document_templates was never implemented and the spec was deleted. appointment_documents is the only document-side table.

---

Area 12: Automations

automation_rules

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
enabled	BOOLEAN DEFAULT TRUE
trigger_event	enum automation_trigger	See P28 catalog
trigger_config	JSONB DEFAULT '{}'	Event-specific config (e.g., {hours_before: 24})
conditions	JSONB DEFAULT '{}'	Rule conditions
actions	JSONB NOT NULL	Ordered action list
execution_count	INT DEFAULT 0
last_executed_at	TIMESTAMPTZ
created_at, updated_at	TIMESTAMPTZ

automation_executions

Append-only audit trail.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
automation_rule_id	UUID FK
trigger_event	enum
trigger_entity_type, trigger_entity_id	TEXT, UUID
status	enum	`success
actions_executed	JSONB	Per-action results
error_message	TEXT
executed_at	TIMESTAMPTZ

---

Area 13: Webhooks

webhook_subscriptions

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
url	TEXT NOT NULL	HTTPS endpoint
description	TEXT
events	TEXT[] NOT NULL	event names or {"*"}
signing_secret	TEXT NOT NULL	server-generated whsec_...
is_active	BOOLEAN DEFAULT TRUE
created_by	UUID FK
created_at, updated_at	TIMESTAMPTZ

webhook_events

Append-only delivery log.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
subscription_id	UUID FK
event_type	TEXT NOT NULL
payload	JSONB NOT NULL	Delivered body
status	TEXT DEFAULT 'pending'	`pending
attempts	INT DEFAULT 0
last_attempt_at	TIMESTAMPTZ
last_status_code, last_error	INT, TEXT
next_retry_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ

---

Area 14: Audit + Break-Glass

See P10, P14a, P15, P16.

audit_log

Append-only mutation history. Actor is a principal — could be a human, AI agent, integration service account, or system job. Range-partitioned monthly on created_at (P41) — initial partition seeded by the migration; cmd/audit-partition-roll extends the runway. PK is composite (id, created_at) because Postgres requires every unique index on a partitioned table to include the partition key; id remains logically unique.

Column	Type	Notes
id	UUID, PK part 1
organization_id	UUID FK NULL	NULL for platform-level events
actor_id	UUID FK NULL → principals(id)	The principal that performed the action. NULL only for the singleton system principal during seeding bootstrap.
actor_type	TEXT NOT NULL	Denormalized from principals.principal_type — 'human' | 'agent' | 'service_account' | 'system'. Saves the join when "what kind of actor was this?" is the only question.
action	TEXT NOT NULL	`CREATE
entity_type	TEXT NOT NULL
entity_id	UUID NULL	(was BIGINT in spec — changed to UUID for v7 PK consistency)
changes	JSONB	before/after diff, sensitive fields redacted (P11)
ip_address	INET
user_agent	TEXT
request_path	TEXT
request_method	TEXT	HTTP verb of the originating request
status_code	INT
request_id	UUID NULL	Correlation with logs (P36)
action_context	TEXT	`normal
break_glass_id	UUID NULL	Set by audit_log_insert from the session GUC current_app_break_glass_id() (bound by the RequireBreakGlass middleware after match). Logical FK to break_glass_sessions(id) — not enforced as a hard FK so audit rows survive break_glass_sessions cascade-deletes (rare). Lit up by Foundation 1B.11.
impersonation_id	UUID NULL	Set by audit_log_insert from the session GUC current_app_impersonation_id() (bound by the RequireImpersonation middleware after match). Logical FK to patient_impersonation_sessions(id) — not enforced as a hard FK so audit rows survive session cascade-deletes (rare). Lit up by Foundation 1B.13.
created_at	TIMESTAMPTZ, PK part 2	Partition key (P41)

AI provenance (model_version, inputs_hash, confidence) lives on the sibling audit_ai_provenance table — see below. The split keeps audit_log's compliance contract stable while AI-features metadata churns on its own table.

Indexes. idx_audit_org, idx_audit_actor (actor_id), idx_audit_entity (entity_type, entity_id), idx_audit_created, idx_audit_org_entity_time, idx_audit_status (status_code, created_at DESC), plus partial indexes on action_context, break_glass_id, impersonation_id, request_id (each WHERE col IS NOT NULL). A partial index on actor_type WHERE actor_type <> 'human' is added when the first non-human actor ships (no value today since every row is 'human').

audit_ai_provenance

Sibling to audit_log. One row per audit event that involved an AI model. Audit rows for purely human actions have no row here. Split out from audit_log so AI-features schema churn (adding prompt versions, tool-call inventories, model-output rationales, etc.) doesn't pollute the core audit table. Range-partitioned monthly on audit_log_created_at, mirroring audit_log's window so both tables hand off the same monthly slice together at archive time (P41). The provenance recorder captures id, created_at from the parent audit_log INSERT (via RETURNING) and passes both into this row.

Column	Type	Notes
audit_log_id	UUID, PK part 1	Composite FK to parent — see below
audit_log_created_at	TIMESTAMPTZ, PK part 2	Partition key (P41); matches parent audit_log.created_at exactly
model_version	TEXT NOT NULL	Model identifier (e.g., claude-opus-4-7)
inputs_hash	BYTEA NOT NULL	SHA-256 of inputs sent to the model
confidence	NUMERIC(4,3) NULL	Model's confidence score (0..1, CHECK-constrained); NULL when the model doesn't expose one
created_at	TIMESTAMPTZ

FK. (audit_log_id, audit_log_created_at) → audit_log(id, created_at) ON DELETE CASCADE. The composite shape is required because Postgres FKs to a partitioned parent must reference the parent's full unique key (P41).

RLS. Same policy as audit_log — gated on audit_log.view_org permission, joined through audit_log.organization_id. INSERT permitted (audit middleware writes the provenance row in the same transaction as the audit row when the action involved an AI model).

break_glass_sessions

Time-bound, audited platform-staff elevation against a target org. One row per (principal × org × scope × time-window). State, not events — partial unique (principal_id, organization_id, scope) WHERE closed_at IS NULL enforces active-session uniqueness; lazy expiry finalize on the admin pool keeps the index honest. AppPool DML REVOKE'd; AdminPool only. SELECT for own + org members with audit_log.view_org. Ships in Foundation 1B.11. Substantive rationale in decisions.md → Why clinic is controller, platform is processor. See P15.

Column	Type	Notes
id	UUID PK
principal_id	UUID FK → principals(id)	The elevating platform staff. Cascade-delete on principal hard-delete (rare).
organization_id	UUID FK → organizations(id)	The target org.
scope	VARCHAR(32) NOT NULL	CHECK in (patient_list, patient_detail, audit_full, cross_org_lookup, org_management). Granular: a session for patient_list does NOT cover patient_detail. org_management covers Console writes against the clinic's HR surface (1B.11.x).
reason_category	VARCHAR(32) NOT NULL	CHECK in (support_ticket, security_incident, dsar_routing, fraud_investigation, platform_engineering).
reason_text	TEXT NOT NULL	Free-text justification, CHECK length(btrim) >= 10.
reason_ref	TEXT NULL	Optional ticket / incident / DSAR reference.
opened_at	TIMESTAMPTZ NOT NULL
expires_at	TIMESTAMPTZ NOT NULL	CHECK expires_at > opened_at AND expires_at <= opened_at + INTERVAL '4 hours'. Default 1h, max 4h.
closed_at	TIMESTAMPTZ NULL	Explicit close stamps NOW(); lazy expiry finalize stamps expires_at (system-closed at natural-end).
closed_by_principal_id	UUID NULL FK → principals(id)	Caller's principal for explicit close (self or break_glass.manage holder); NULL for system-closed by expiry.

patient_impersonation_sessions

Time-bound, audited clinic-internal session where staff acts on a patient's behalf. Per-clinic counterpart to break_glass_sessions. State, not events — partial unique (staff_principal_id, organization_id) WHERE closed_at IS NULL enforces "one impersonation at a time per staff per clinic"; lazy expiry finalize on the admin pool keeps the index honest. AppPool + RLS WITH CHECK (not the AdminPool-with-REVOKE shape break-glass uses) — the opening principal is an authenticated org member with patients.impersonate and full RLS context. SELECT for own (staff_principal_id self-match), org members with patients.manage, and target patient (via current_human_patient_profile_ids() join through patients). Cross-context exclusion with break-glass (one elevated session at a time per principal × org, bidirectional). Ships in Foundation 1B.13. See P16.

Column	Type	Notes
id	UUID PK
staff_principal_id	UUID FK → principals(id)	The clinic staff member opening the session.
target_patient_id	UUID FK → patients(id)	Per-org patient row (carries the org constraint via the FK chain). Cascade-delete on patient hard-delete (rare; soft-delete is the normal path).
organization_id	UUID FK → organizations(id)	Denormalized for RLS efficiency, mirrors patient_subscriptions. Must match target_patient_id's org via the WITH CHECK clause + FK chain.
reason	TEXT NOT NULL	Free-text justification, CHECK length(btrim) >= 10. Rich vocabulary by design (vs. break-glass's closed enum) — clinic-internal reasons span more than support categories ("patient called in confused", "elderly patient needs help completing intake", etc.).
opened_at	TIMESTAMPTZ NOT NULL
expires_at	TIMESTAMPTZ NOT NULL	CHECK expires_at > opened_at AND expires_at <= opened_at + INTERVAL '4 hours'. Default 1h, max 4h.
closed_at	TIMESTAMPTZ NULL	Explicit close stamps clock_timestamp() (not NOW() — same-tx Open+Close paths would violate the CHECK if NOW() returned tx-start); lazy expiry finalize stamps expires_at (system-closed at natural-end).
closed_by_principal_id	UUID NULL FK → principals(id)	Caller's principal for explicit close (self via update_self policy or patients.manage holder via update_manage policy); NULL for system-closed by expiry.

---

Area 15: Consents (Foundation 1B.9)

Single ledger spanning platform-scope (platform_terms, platform_privacy_notice) and org-scope (org_terms, org_privacy_notice, marketing_email, marketing_sms, analytics, ai_processing) purposes. Tier B form-driven medical consents (telemedicine, video_recording, biometric_capture, treatment_specific_*) register at F3.5 and write into the same table with source = 'form'. Ships in Foundation 1B.9. Substantive design rationale in decisions.md → Why clinic is controller, platform is processor. See P17.

consent_purposes

Catalog of purpose codes. Platform-managed (AdminPool writes via migration; SELECT for everyone — purpose text is by definition public).

Column	Type	Notes
code	TEXT PK	e.g. platform_terms, org_privacy_notice, marketing_email, ai_processing, video_recording
scope	TEXT NOT NULL	platform | org. Platform-scope rows are accepted once per principal and apply across all orgs; org-scope rows are accepted per clinic.
name	TEXT NOT NULL	Human-readable label
description	TEXT
legal_basis	TEXT NOT NULL	contract | legitimate_interest | consent | legal_obligation | vital_interest (GDPR Art. 6)
withdrawable	BOOLEAN NOT NULL	Whether the patient-initiated withdraw endpoint accepts a flip-off for this purpose. Mostly tracks legal_basis = 'consent', with one deliberate exception: org_terms (legal_basis = contract) is withdrawable because its withdrawal is the "leave clinic" action — the cascade trigger soft-deletes the per-org patients row, cancels the active subscription, and cascade-withdraws every other org-scope consent. platform_terms (also contract) stays non-withdrawable: account deletion (F11.1) is the only revocation path.
created_at	TIMESTAMPTZ

consent_purpose_versions

Versioned policy text per purpose. Org-scope purposes can have org-specific overrides; platform-scope purposes always use the platform-default text.

Column	Type	Notes
id	UUID PK
purpose_code	TEXT FK → consent_purposes(code)
organization_id	UUID FK NULL	NULL = platform-default text. Set = org override (only valid when the purpose's scope = 'org').
version	INT NOT NULL	Bumped per publish
body_translations	JSONB	{ "en": "...", "ro": "..." }
published_at	TIMESTAMPTZ NOT NULL
published_by_principal_id	UUID FK
Unique	(purpose_code, organization_id, version)

For org_privacy_notice specifically, the version row is generated at publish time from the org's organization_privacy_notices row (template + placeholder values + toggleable sections → assembled markdown).

consents

The append-on-grant ledger. One row per grant; withdrawal is the only mutation (UPDATE sets withdrawn_at + withdrawn_by_principal_id). Re-grant after withdrawal = new row.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NULL	NULL = platform-scope grant; non-NULL = org-scope grant at that clinic
patient_profile_id	UUID FK	The subject (patient identity, not the per-org patients row)
purpose_code	TEXT FK → consent_purposes(code)
purpose_version	INT NOT NULL	The consent_purpose_versions.version accepted at grant time
source	TEXT NOT NULL	signup_checkbox | self_toggle | form | staff_action | api
source_form_id	UUID FK NULL	NULL except when source = 'form' (FK to F3 forms; provenance for Tier B medical consents)
granted_at	TIMESTAMPTZ NOT NULL
granted_by_principal_id	UUID FK	The grantor — usually the patient principal (self-toggle, signup) but may be a staff principal (source = 'staff_action')
granted_via_ip	INET
withdrawn_at	TIMESTAMPTZ NULL	NULL = currently granted
withdrawn_by_principal_id	UUID FK NULL
withdrawal_reason	TEXT NULL
created_at	TIMESTAMPTZ
Index	(patient_profile_id, organization_id, purpose_code, granted_at DESC)	history-by-subject lookups
Index	(organization_id, purpose_code) WHERE withdrawn_at IS NULL	active consents per org

RLS. Org staff with consents.view_org sees consents in their org for patients registered there. Patient sees their own across all orgs (via current_human_patient_profile_ids()). Platform-scope rows (organization_id IS NULL) are visible to the patient and to break-glass-elevated staff via Foundation 1B.11.

Withdrawal semantics. withdrawable is derived from the purpose's legal_basis: only legal_basis = 'consent' purposes accept patient-initiated withdrawal. platform_terms (contract basis) cannot be withdrawn except by account deletion (triggers GDPR erasure in F11.1). org_terms withdrawal at clinic A triggers patients.deleted_at at clinic A and cascades withdrawal of every org-scope consent at that org.

Area 15a: Privacy Notice Templates (Foundation 1B.10)

Platform provides a versioned template; the clinic fills placeholders + selects toggleable sections; the assembled markdown is published as the org's org_privacy_notice purpose-version. The clinic owns the legal artefact (controller); the platform provides the scaffolding (processor).

privacy_notice_templates

Platform catalog. AdminPool writes; SELECT for everyone.

Column	Type	Notes
id	UUID PK
version	INT NOT NULL
locale	TEXT NOT NULL	en, ro
body_with_placeholders	TEXT NOT NULL	Markdown with , , , etc.
toggleable_sections	JSONB NOT NULL	[{key, default, body}, ...] — e.g. video_recording, biometric_capture, cross_border_transfer
published_at	TIMESTAMPTZ

organization_privacy_notices

Per-org assembled notice. One row per org; updated via clinic-admin editor (1C.2).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
source_template_id	UUID FK
source_template_version	INT NOT NULL	Snapshot of template version at last publish
placeholder_values	JSONB NOT NULL	{ clinic_name: "...", dpo_email: "..." }
included_sections	JSONB NOT NULL	["video_recording", "cross_border_transfer"]
assembled_body	TEXT	Final markdown — what the patient accepts
published_version	INT NULL	FK target on consent_purpose_versions for the org_privacy_notice row generated at publish; NULL until first publish
reviewed_by_principal_id	UUID FK NULL	Clinic admin who published
reviewed_at	TIMESTAMPTZ NULL

Template version bumps. When the platform publishes a new privacy_notice_templates version, every organization_privacy_notices row whose source_template_version is older surfaces a "Review template update" prompt to that clinic's admin in 1C.2. The previously-assembled body keeps serving (no break in legality) until the clinic re-publishes.

---

Area 16: Plans, Subscriptions & Patient Tiers

The platform's commercial model has two surfaces — both ride on the same engine shape with shared atomic catalogs and parallel higher-level tables.

• B2B (platform → clinic). Platform-defined tiers (no organization_id) sold to clinics. Subscription state in organization_subscriptions + organization_subscription_entitlements / _limits / _overrides (snapshots). Managed by superadmin.
• B2C (clinic → patient). Clinic-defined patient_tiers (per-org) sold to patients. Subscription state in patient_subscriptions + patient_subscription_entitlements / _limits / _overrides (snapshots). Managed by clinic admin.
• Shared atomic catalogs. entitlements and limit_definitions are platform-wide and used by both surfaces — an entitlement code or limit code means the same thing whether it appears on a platform plan or a patient tier. The shared catalog prevents definition drift.

Patient tiers do not ride RBAC. There is no patient_tiers.role_id; tier entitlements live in patient_tier_entitlements / patient_tier_limits (mirrors of tier_entitlements / tier_limits), snapshotted onto the subscription on subscribe (P37). See decisions.md → Why patients are not memberships, and patient tiers are not roles.

Entitlement projection for regulated entitlements (P38) applies to the org-side only. Per-patient regulated entitlement is gated by the org-side organization_entitlements (the clinic must be certified) plus the patient's own subscription entitlements (the patient must be on a tier that includes it).

Companion docs: plans-and-subscriptions.md (full design — resolution rules, entitlement projection, lifecycle); middleware-composition.md (how RequirePlanEntitlement / RequireOrgEntitlement / EnforceLimit compose); org-settings.md (where current_tier_id and the entitlement surface live).

Catalog tables (platform-wide, no organization_id)

Catalog tables are managed via migrations, not at runtime. RLS allows everyone to SELECT; only superadmin (AdminPool) writes.

tiers

The product catalog. Versioned for snapshot-on-subscribe (P37).

Column	Type	Notes
id	UUID PK
code	TEXT NOT NULL UNIQUE	e.g. free, pro, dedicated, addon_telerehab, pack_video_minutes_1000. Used by external billing.
name	TEXT NOT NULL
description	TEXT
kind	TEXT NOT NULL	`base
billing_cycle	TEXT NULL	`monthly
base_price	DECIMAL(10,2) NULL	Informational; canonical price comes from external billing. (P22)
currency	TEXT NOT NULL DEFAULT 'RON'
is_public	BOOLEAN NOT NULL DEFAULT FALSE	TRUE ⇒ appears in self-service signup.
version	INT NOT NULL DEFAULT 1	Bumped on any entitlement/limit edit.
published	BOOLEAN NOT NULL DEFAULT FALSE	Only published versions can be subscribed to.
published_at	TIMESTAMPTZ NULL
deprecated_at	TIMESTAMPTZ NULL	When set, prevents new signups; existing subscribers continue.
translations	JSONB NOT NULL DEFAULT '{}'	(P21) for name/description localization.
created_at, updated_at	TIMESTAMPTZ

tier_versions

Append-only history (P14a). Snapshotted onto subscriptions at subscribe time.

Column	Type	Notes
id	UUID PK
tier_id	UUID FK
version	INT NOT NULL	Matches plans.version at the moment of publish.
published_at	TIMESTAMPTZ NOT NULL
entitlements_snapshot	JSONB NOT NULL	Array of entitlement codes enabled at this version.
limits_snapshot	JSONB NOT NULL	Array of {code, cap_value, behavior}.
metadata_snapshot	JSONB NOT NULL	Frozen {name, description, base_price, currency, billing_cycle}.
changed_by_principal_id	UUID FK NULL → principals(id)	Superadmin (human) who published this version. The human-only constraint is enforced by platform_memberships, not by a CHECK here.
created_at	TIMESTAMPTZ
Unique	(tier_id, version)

entitlements

Catalog of every plan-gated entitlement. Mirrors permissions in shape — every code is documented here. Natural-key PK.

Column	Type	Notes
code	TEXT PK	e.g. custom_domain, automations, webhooks, treatment_plans, video_consultations.
name	TEXT NOT NULL	Display name for billing UI.
description	TEXT
regulated	BOOLEAN NOT NULL DEFAULT FALSE	TRUE ⇒ entitlement must be projected onto organization_entitlements (P38).
created_at, updated_at	TIMESTAMPTZ

limit_definitions

Catalog of every metered or capped resource.

Column	Type	Notes
code	TEXT PK	e.g. max_patients, max_storage_bytes, video_minutes_per_month.
name	TEXT NOT NULL
description	TEXT
unit	TEXT NOT NULL	`count
default_behavior	TEXT NOT NULL	`hard_block
period_kind	TEXT NOT NULL	`lifetime
created_at, updated_at	TIMESTAMPTZ

tier_entitlements

Which entitlements a plan unlocks. Junction.

Column	Type	Notes
tier_id	UUID FK
entitlement_code	TEXT FK → entitlements(code)
enabled	BOOLEAN NOT NULL DEFAULT TRUE	Allows a plan version to disable an entitlement without removing the row (audit trail across versions).
created_at	TIMESTAMPTZ
PK	(tier_id, entitlement_code)

tier_limits

What caps and meter behaviors a plan sets. Junction.

Column	Type	Notes
tier_id	UUID FK
limit_code	TEXT FK → limit_definitions(code)
cap_value	BIGINT NULL	NULL ⇒ unlimited.
behavior_override	TEXT NULL	Override limit_definitions.default_behavior. NULL ⇒ inherit.
created_at	TIMESTAMPTZ
PK	(tier_id, limit_code)

Per-org subscription tables

organization_subscriptions

N:M between an org and the plans it holds. One row per active plan attachment (base + each add-on + each usage pack are separate rows).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK	(P1)
tier_id	UUID FK	Pointer to the catalog plan.
tier_version	INT NOT NULL	The tier_versions.version snapshotted at subscribe time.
status	TEXT NOT NULL	`trialing
started_at	TIMESTAMPTZ NOT NULL
current_period_starts_at, current_period_ends_at	TIMESTAMPTZ NULL	NULL for usage_pack (no period).
cancel_at, canceled_at	TIMESTAMPTZ NULL
payment_provider	TEXT NOT NULL DEFAULT 'manual'	`manual
external_subscription_id	TEXT NULL	NULL until billing wires up.
created_at, updated_at	TIMESTAMPTZ
Index	(organization_id, status)	Hot path on every gated request.

RLS. SELECT gated by subscriptions.view_org. INSERT/UPDATE/DELETE by subscriptions.manage. Superadmin via AdminPool can write any org.

organization_subscription_entitlements (snapshot, P37)

Frozen at subscribe time. Plan edits never modify these rows. Renamed twice: subscription_features → organization_subscription_features (when patient subscriptions arrived and the organization_ prefix became load-bearing) → organization_subscription_entitlements (foundation 1C.9, 2026-05-06; resolves the architectural-vs-billing word collision per glossary.md → Entitlement).

Column	Type	Notes
subscription_id	UUID FK → organization_subscriptions(id)
entitlement_code	TEXT NOT NULL	References the shared entitlements(code) catalog.
enabled	BOOLEAN NOT NULL
created_at	TIMESTAMPTZ
PK	(subscription_id, entitlement_code)

organization_subscription_limits (snapshot, P37)

Frozen at subscribe time. Renamed from subscription_limits.

Column	Type	Notes
subscription_id	UUID FK → organization_subscriptions(id)
limit_code	TEXT NOT NULL	References the shared limit_definitions(code) catalog.
cap_value	BIGINT NULL	NULL ⇒ unlimited.
behavior	TEXT NOT NULL	Resolved from tier_limits at subscribe time.
created_at	TIMESTAMPTZ
PK	(subscription_id, limit_code)

organization_subscription_overrides

Sales-granted exceptions on top of the snapshot. Audited. Renamed from subscription_overrides.

Column	Type	Notes
id	UUID PK
subscription_id	UUID FK → organization_subscriptions(id)
override_kind	TEXT NOT NULL	`entitlement
entitlement_code, entitlement_enabled	TEXT, BOOLEAN NULL	Required when override_kind = 'entitlement'.
limit_code, cap_value, behavior_override	TEXT, BIGINT, TEXT NULL	Required when override_kind = 'limit'.
granted_by_principal_id	UUID FK → principals(id)	Superadmin (human) who granted. Human-only constraint enforced by platform_memberships.
reason	TEXT NOT NULL	Audit trail.
effective_from	TIMESTAMPTZ NOT NULL DEFAULT NOW()
expires_at	TIMESTAMPTZ NULL	NULL ⇒ until subscription ends.
revoked_at, revoked_by_principal_id	TIMESTAMPTZ, UUID FK NULL → principals(id)
created_at	TIMESTAMPTZ
CHECK	one of (entitlement_code, limit_code) is set per override_kind

RLS. SELECT for subscriptions.view_org. INSERT/UPDATE/DELETE: superadmin only via AdminPool — clinic admins cannot grant their own overrides on the platform-tier subscription. The patient-side mirror (patient_subscription_overrides) has different gating because clinic admins DO grant patient-side overrides.

Patient tier tables (B2C — clinic-defined, parallel shape to org-side)

The patient tier engine mirrors the org-side plan engine (patient_tiers ↔ tiers, patient_tier_versions ↔ tier_versions, etc.) with two structural differences: (1) per-org scope (organization_id NOT NULL); (2) clinic admin manages instead of superadmin. Tier entitlements are entitlements and limits, not roles — there is no patient_tiers.role_id.

patient_tiers

Per-org catalog of tiers a clinic offers patients. Versioned for snapshot-on-subscribe (P37). Mirror of tiers.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK	(P1)
code	TEXT NOT NULL	Org-defined, e.g. basic, premium.
name	TEXT NOT NULL
description	TEXT
is_active	BOOLEAN NOT NULL DEFAULT TRUE
is_default	BOOLEAN NOT NULL DEFAULT FALSE	Exactly one tier per org should have this TRUE; auto-assigned to new portal sign-ups. Partial unique index WHERE is_default = TRUE is the brute-force guarantee; service-layer atomic-swap is the user-friendly path.
sort_order	INT NOT NULL DEFAULT 0
version	INT NOT NULL DEFAULT 1	Bumped on any entitlement/limit edit.
published	BOOLEAN NOT NULL DEFAULT FALSE	Only published versions can be subscribed to.
published_at	TIMESTAMPTZ NULL
external_price_hint	DECIMAL(10,2) NULL	Informational only. Source of truth is the clinic's external billing system.
currency	TEXT NOT NULL DEFAULT 'RON'
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, code)

RLS. SELECT for org members + portal patients (so the sign-up screen can list active tiers). INSERT/UPDATE/DELETE gated by patient_tiers.manage (granted to admin template).

patient_tier_versions

Append-only history of published tier versions. Mirror of tier_versions.

Column	Type	Notes
id	UUID PK
tier_id	UUID FK → patient_tiers(id)
organization_id	UUID FK	(P1; denormalized for RLS)
version	INT NOT NULL	Matches patient_tiers.version at the moment of publish.
published_at	TIMESTAMPTZ NOT NULL
entitlements_snapshot	JSONB NOT NULL	Array of entitlement codes enabled at this version.
limits_snapshot	JSONB NOT NULL	Array of {code, cap_value, behavior}.
metadata_snapshot	JSONB NOT NULL	Frozen {name, description, external_price_hint, currency}.
changed_by_principal_id	UUID FK NULL → principals(id)	Clinic admin (human) who published this version.
created_at	TIMESTAMPTZ
Unique	(tier_id, version)

patient_tier_entitlements

Which entitlements a patient tier unlocks. Junction. Mirror of tier_entitlements. References the shared entitlements(code) catalog.

Column	Type	Notes
tier_id	UUID FK → patient_tiers(id)
entitlement_code	TEXT FK → entitlements(code)	Same catalog as the org-side plan engine.
enabled	BOOLEAN NOT NULL DEFAULT TRUE	Allows a tier version to disable an entitlement without removing the row.
created_at	TIMESTAMPTZ
PK	(tier_id, entitlement_code)

patient_tier_limits

What caps and meter behaviors a patient tier sets. Junction. Mirror of tier_limits. References the shared limit_definitions(code) catalog.

Column	Type	Notes
tier_id	UUID FK → patient_tiers(id)
limit_code	TEXT FK → limit_definitions(code)	Same catalog as the org-side plan engine.
cap_value	BIGINT NULL	NULL ⇒ unlimited.
behavior_override	TEXT NULL	Override limit_definitions.default_behavior. NULL ⇒ inherit.
created_at	TIMESTAMPTZ
PK	(tier_id, limit_code)

patient_subscriptions (lands at Layer 2.5 — depends on patients)

Per-patient subscription to a tier. Mirror of organization_subscriptions.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK	(P1)
patient_id	UUID FK → patients(id)
tier_id	UUID FK → patient_tiers(id)
tier_version	INT NOT NULL	The patient_tier_versions.version snapshotted at subscribe time.
status	TEXT NOT NULL	`trialing
started_at	TIMESTAMPTZ NOT NULL	When the subscription first began (does not change across renewals).
current_period_starts_at, current_period_ends_at	TIMESTAMPTZ NULL	Drives the tier-inclusion rollover hook.
cancel_at, canceled_at	TIMESTAMPTZ NULL
payment_provider	TEXT NOT NULL DEFAULT 'external'	`external
external_subscription_id	TEXT NULL
created_at, updated_at	TIMESTAMPTZ
Index	(patient_id, status)	One active subscription per patient is the service-layer invariant.

RLS. SELECT gated by patient_subscriptions.view_org. Patients see their own via current_human_patient_profile_ids() join through patients.patient_profile_id. Mutations gated by patient_subscriptions.manage.

No role-flip hook. Subscribing to a tier does not mutate any role assignment — patients have no role at the org. Tier perks are read out of the snapshot tables below; portal access is implicit from the existence of the patients row.

patient_subscription_entitlements (snapshot, P37)

Frozen at subscribe time. Tier edits never modify these rows. Mirror of organization_subscription_entitlements.

Column	Type	Notes
subscription_id	UUID FK → patient_subscriptions(id)
entitlement_code	TEXT NOT NULL	Shared entitlements(code) catalog.
enabled	BOOLEAN NOT NULL
created_at	TIMESTAMPTZ
PK	(subscription_id, entitlement_code)

patient_subscription_limits (snapshot, P37)

Frozen at subscribe time. Mirror of organization_subscription_limits.

Column	Type	Notes
subscription_id	UUID FK → patient_subscriptions(id)
limit_code	TEXT NOT NULL	Shared limit_definitions(code) catalog.
cap_value	BIGINT NULL	NULL ⇒ unlimited.
behavior	TEXT NOT NULL	Resolved from patient_tier_limits at subscribe time.
created_at	TIMESTAMPTZ
PK	(subscription_id, limit_code)

patient_subscription_overrides

Clinic-granted exceptions on top of the snapshot. Audited. Mirror of organization_subscription_overrides with one structural difference: gated by clinic admin (patient_subscriptions.manage), not platform admin. Common case: the clinic admin overrides a Basic-tier patient's monthly-appointments limit because of a clinical exception.

Column	Type	Notes
id	UUID PK
subscription_id	UUID FK → patient_subscriptions(id)
override_kind	TEXT NOT NULL	`entitlement
entitlement_code, entitlement_enabled	TEXT, BOOLEAN NULL	Required when override_kind = 'entitlement'.
limit_code, cap_value, behavior_override	TEXT, BIGINT, TEXT NULL	Required when override_kind = 'limit'.
granted_by_principal_id	UUID FK → principals(id)	Clinic admin who granted.
reason	TEXT NOT NULL	Audit trail.
effective_from	TIMESTAMPTZ NOT NULL DEFAULT NOW()
expires_at	TIMESTAMPTZ NULL	NULL ⇒ until subscription ends.
revoked_at, revoked_by_principal_id	TIMESTAMPTZ, UUID FK NULL → principals(id)
created_at	TIMESTAMPTZ
CHECK	one of (entitlement_code, limit_code) is set per override_kind

RLS. SELECT and INSERT/UPDATE/DELETE gated by patient_subscriptions.manage (granted to admin + customer_support templates). The patient sees their own active overrides via current_human_patient_profile_ids() join through patients.patient_profile_id.

patient_tier_inclusions (lands at Layer 3.2 — depends on service_plans)

Counted entitlements bundled with a tier — specifically, service-plan templates auto-cloned into the patient's patient_service_plans when they subscribe. Distinct from patient_tier_entitlements / patient_tier_limits: tier entitlements and limits are abstract codes ("priority_support", "max_monthly_appointments"); inclusions bind to specific bookable service templates ("5 sessions of Service X per period"). Both shapes coexist on a tier.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK	(P1)
tier_id	UUID FK → patient_tiers(id)
service_plan_id	UUID FK → service_plans(id)	The template to clone into patient_service_plans when the tier subscription becomes active.
grant_period	TEXT NOT NULL	`per_subscription_period
grant_quantity	INT NOT NULL DEFAULT 1
carry_over_unused	BOOLEAN NOT NULL DEFAULT FALSE	Default FALSE ("use it or lose it"); per-row override allowed.
prorate_on_upgrade	BOOLEAN NOT NULL DEFAULT FALSE	Default FALSE (full grant on upgrade); per-row override allowed.
created_at, updated_at	TIMESTAMPTZ
Index	(tier_id)	Hot path on subscription state changes.

RLS. SELECT for org members. INSERT/UPDATE/DELETE gated by patient_tiers.manage (same permission gating the tier itself).

Hook. On patient_subscriptions state change, a domain service projects inclusions onto patient_service_plans (provision on activate/trialing, soft-expire on cancel/expire/past_due, regrant on period rollover, full grant or prorate on tier change). See plans-and-subscriptions.md § Tier → entitlement provisioning.

Layer dependencies

The schema lands layer by layer based on FK dependencies. Plans/subscriptions split across Layer 1, 2.5, and 3.2; the rest of the platform's core domain entities anchor each subsequent layer (cross-reference dependency-map.md for the canonical layer-by-area build order):

Layer	Tables
Layer 1 (foundation)	Shared catalog tables (entitlements, limit_definitions); org-side catalog (tiers, tier_versions, tier_entitlements, tier_limits); org-side subscription tables (organization_subscriptions, organization_subscription_entitlements, organization_subscription_limits, organization_subscription_overrides); patient-side catalog (patient_tiers, patient_tier_versions, patient_tier_entitlements, patient_tier_limits)
Layer 2 (People)	Core domain entities: humans, patient_profiles, patient_caregivers, patients, specialists, specialties
Layer 2.5 (after patients)	patient_subscriptions, patient_subscription_entitlements, patient_subscription_limits, patient_subscription_overrides
Layer 3 (Service Catalog)	Core domain entities: services, service_plans, service_specialists
Layer 3.2 (after service_plans)	patient_tier_inclusions; patient_service_plans.source_tier_subscription_id FK column; service_plans.treatment_plan_assignments_total counter extension
Layer 4 (Forms)	Core domain entities: custom_fields, form_templates, form_template_versions, forms; service_forms junction (depends on Layer 3 services)
Layer 5 (Scheduling)	Core domain entities: calendars, calendar_specialists, specialist availability tables
Layer 6 (Appointments)	Core domain entities: appointments (depends on patients, specialists, services, calendars, forms)

See plans-and-subscriptions.md § Layer 1 reservation for the full phase-by-phase reservation list.

---

ER Diagram (high-level)

                        ┌──────────────────┐
                        │  organizations   │◄────────────┐
                        └────────┬─────────┘             │
                                 │                       │
                ┌────────────────┼────────────────┐      │
                ▼                ▼                ▼      │
      ┌──────────────────────┐ ┌─────────────────┐ ┌──────────────────────┐
      │     principals       │ │  org_domains    │ │ org_integrations     │
      │ (root identity:      │ └─────────────────┘ └──────────────────────┘
      │  id, principal_type) │
      └──────────┬───────────┘
                 │ principal_id (PK = FK, ON DELETE CASCADE)
                 │
        ┌────────┼────────────────────────────────┐
        ▼        ▼                                ▼
 ┌──────────────┐ ┌─────────────────────┐ ┌──────────────────────┐
 │   humans     │ │  agents (sibling,   │ │ service_accounts     │
 │ (shipped —   │ │   future actor —    │ │ (sibling, future     │
 │  human       │ │   table exists, no  │ │  actor — table       │
 │  profiles)   │ │   features yet)     │ │  exists, no features │
 └──────┬───────┘ └─────────────────────┘ │  yet)                │
        │                                 └──────────────────────┘
        │ organization_memberships (staff M:M with role)
        │ — principals.id, NOT humans-only; agents +
        │   service_accounts join here when their first
        │   feature ships
        │
        ┌────┴────────────────────────────────────────────┐
        │                                                 │
        ▼                                                 ▼
 ┌──────────────┐                              ┌────────────────────┐
 │  patient_    │ ◄─── caregivers ─────────────┤ patient_caregivers │
 │  profiles    │                              └────────────────────┘
 └──────┬───────┘
        │
        │ (per-org link — patients NOT in organization_memberships)
        ▼
 ┌──────────────┐         ┌───────────────┐         ┌─────────────────┐
 │   patients   │────────▶│ appointments  │◄────────│   specialists   │
 └──────┬───────┘         └───────┬───────┘         └────────┬────────┘
        │                         │                          │
        │                         │                          │
        │                ┌────────┴──────┐                   │
        │                ▼               ▼                   ▼
        │         ┌────────────┐  ┌────────────┐    ┌────────────────┐
        │         │   forms    │  │ documents  │    │ specialties    │
        │         └────────────┘  └────────────┘    └────────────────┘
        │
        │ (telerehab path)
        ├─────────────────────────────────────┐
        ▼                                     ▼
 ┌─────────────────────┐             ┌───────────────────────┐
 │ patient_treatment_  │────refs────▶│   treatment_plans     │
 │      plans          │             │  (global / org /      │
 └────────┬────────────┘             │      custom)          │
          │                          └────────┬──────────────┘
          ▼                                   │
 ┌─────────────────────┐                      ▼
 │ patient_session_    │             ┌───────────────────┐
 │   completions       │             │  treatment_plan_  │
 └────────┬────────────┘             │     sessions      │
          │                          └────────┬──────────┘
          ▼                                   │
 ┌─────────────────────┐                      ▼
 │ patient_exercise_   │────refs────▶┌────────────────────┐
 │      logs           │             │     exercises      │
 └─────────────────────┘             │  (global / org)    │
                                     └────────────────────┘

[parallel: services → calendars → appointments]
[parallel: custom_fields → form_templates → forms]
[parallel: automations + webhooks consume the event bus]
[parallel: audit_log captures everything]
[parallel: consents track per-clinic per-purpose]

---

Schema Reconciliation: Spec vs Current Implementation (CLOSED)

The per-feature schema.sql / schema.md files in apps/docs/features/*/ were the original schema source of truth. Layer 1.24 (principal model rename) and P26 (UUIDv7 PKs) changed them so heavily that they drifted from the migrations, and CLAUDE.md adopted the rule "architecture wins over feature specs." Once that rule landed, this document became canonical and the per-feature spec files were no longer load-bearing.

Resolution (executed):

1. The 19 per-feature schema.{sql,md} files were deleted. Every "Resolution: spec is stale" entry below was made moot by deletion. Parent feature docs now link to the relevant Area N in this file.
2. services/api/cmd/check-migrations lints new migrations for the same stale patterns the spec files used to carry: BIGSERIAL, BIGINT REFERENCES, users(id), has_role(, current_app_user_id, app.current_user_id, form_instances. Wired into make check. A copy-paste from a hypothetical surviving stale spec would now fail the build.
3. The schema decisions originally captured here remain authoritative — they are documented in their natural homes:
   • UUIDv7 PKs: P26
   • Principal model (no users table): decisions.md → Why principals as the root identity, Area 1 of this file
   • pdf_templates wins over document_templates: Area 11 of this file
   • forms (not form_instances): Area 7 of this file
   • appointment_status defined upfront, no ALTER TYPE later: Area 5 of this file
   • No appointment_template_id legacy column: Area 5 (omitted by design)
   • Per-org permissions (current_app_has_permission, no role-string compares): P3 and rbac-permissions.md
   • audit_log.request_id, organization_memberships.last_used_at / invited_at / invited_by: reserved-columns.md (Layer 1.11, 1.12)
   • consents table: Area 15 of this file (Foundation 1B.9 owns implementation; F3.5 layers Tier B medical consents)
   • specialists.human_id UNIQUE, patient_profiles.human_id UNIQUE: Area 2 of this file (Layer 2.1, 2.3)
   • appointments.patient_profile_id nullable until the booking flow links it: Area 5 of this file

If a future contributor finds a missing constraint that used to live in the deleted spec files, add it to the relevant Area in this document — there is no other canonical home.

---

Open Decisions

The canonical list lives in implementation-plan.md → Open Decisions — kept there so each open item is attached to the layer that has to resolve it. Don't duplicate that table here; update it in the implementation plan and link back from any data-model entries that depend on the answer.

Already-resolved items relevant to this doc (so the schema lookups stop reading "still open"):

• OpenAPI spec generation — resolved (Layer 1.7): spec-first via oapi-codegen (Go) + openapi-typescript (frontends), source at apps/docs/openapi.yaml. See decisions.md.
• humans.last_activity write strategy — resolved (Layer 1.11): middleware-side throttled bump (60s in-process cache → admin-pool UPDATE on miss). See reference/activity-tracking.md.
• organizations.last_activity_at column — resolved (Layer 1.11): not stored; derive from MAX(organization_memberships.last_used_at) WHERE organization_id = ? when needed.
• Daily.co for video — resolved: see decisions.md.

---

How to Use This Doc

When starting work on a new feature:

1. Find the area it belongs to and review the entities involved.
2. Cross-check against patterns.md for every pattern those entities depend on.
3. Confirm the build order in dependency-map.md — does anything blocking this feature still need to land?
4. Update this doc when adding tables or columns. There is no per-feature schema doc anymore — this file is the canonical schema; services/api/cmd/check-migrations keeps new migrations in line with it.
5. When in doubt, re-read data-isolation.md, audit-trail.md, and gdpr.md — those constraints are non-negotiable.

The implementation plan in apps/docs/implementation-plan.md references entities from this doc by name. Keep names stable across both docs.