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 substrate closed 2026-05-15: 1A, 1B, and 1C shipped end-to-end and 1E.3 closed, with 1D admin surfaces in flight; plus the F-tier clinical domains already shipped on top — programs, sessions, protocols, patient subscriptions; see implementation-plan/foundation.md for which sub-phases have closed) and planned schema (the remaining Areas, 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.

Where the line falls today (verified against code 2026-08-02; schema at 000039). Areas 3, 4, 5, 6, 7, 11 are entirely unbuilt — no offerings, specialists, specialties, calendars, appointments, custom_fields, form_templates, forms, pdf_templates, appointment_documents table exists, and no specialists.* / appointments.* / forms.* / documents.* permission row is seeded in any migration. Area 2's patient half is shipped and its specialist half is not. Area 10 is retired. Those Areas were re-scoped and corrected on 2026-08-02 against the settled plan in implementation-plan/platform-completion.md; the evidence sits in leo-port-map.md.

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}
national_id_encrypted	BYTEA NULL	Planned, not shipped (F3). Romanian CNP / equivalent national identifier. pii_regulated → AES-256-GCM via internal/core/crypto (P12); cmd/check-classification enforces the _encrypted suffix + BYTEA type on this class. See "CNP has exactly one home" below.
created_at, updated_at	TIMESTAMPTZ

Shipped shape. Everything above except national_id_encrypted exists in 000006_patient_identity.up.sql.

RLS. Self + caregivers (via current_human_patient_profile_ids()); org staff can SELECT when they hold a patients row against the profile. No field-level masking above that — registering the patient is the disclosure (P8, retired 2026-08-20). DELETE never permitted.

CNP has exactly one home (settled 2026-08-02; opt-in flags removed 2026-08-07). CNP is required on some forms and documents, not all — but the FIELD's presence is what declares that, not a separate flag. Both requires_national_id opt-in controls were removed; the PDF one survives as a derived column. Four consequences that are not negotiable:

• One column, on the patient-owned profile. patient_profiles.national_id_encrypted. Never duplicated per-org, never a second copy on a form instance.
• Never in the generic value store. A custom_field_values.value TEXT column can never legally hold a CNP. A custom field of type national_id routes to this column or is rejected outright — it must not fall through to the EAV path (Area 6).
• Egress is explicit. A pii_regulated registry row with an explicit egress target for the PDF renderer; the renderer calls classification.AllowedFor rather than hand-building the field list (P39).
• Reads are permissioned and audited — the value comes from the /national-id reveal endpoint, which requires patients.view_national_id and writes an audit.ActionRead row.

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)
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. Not built — no table, no domain, no routes as of 2026-08-02. Lands with F1.

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. P9.
name, title, description	TEXT	title is the DISPLAY honorific ("Dr.") rendered under the name on the public roster — free text, not the profession.
specialist_title_id	UUID FK NULL → specialist_titles(id) ON DELETE RESTRICT	The PROFESSION. Decides which of an offering's attached forms and document layouts this person receives. NULL = untitled, which receives only the attachments naming no title.
slug	TEXT NOT NULL
minicrm_name	TEXT	External system identifier. Overrides name in outbound CRM payloads (`minicrm_name
signature_url, avatar_url	TEXT	S3 keys. Signature on the signatures surface; avatars need a new SurfaceAvatars registration — do not overload SurfaceLogos, which is org branding.
scheduling_timezone	VARCHAR(64)	IANA tz; NULL = unbookable (P23)
scheduling_active	BOOLEAN DEFAULT TRUE	Removes the specialist from availability computation by construction. Kept strictly separate from humans.blocked — deactivating at Clinic A must not lock the person out of Clinic B.
deleted_at	TIMESTAMPTZ NULL	Soft delete
created_at, updated_at	TIMESTAMPTZ
Unique	(slug, organization_id)
Index	(organization_id) WHERE deleted_at IS NULL; GIN (immutable_unaccent(name) gin_trgm_ops)	Production-scale rule: the roster picker is async typeahead, and Romanian needs unaccent ("Stefan" must match "Ștefan").

Bookability is derived, never a stored flag: scheduling_timezone IS NOT NULL AND scheduling_active AND EXISTS(specialist_weekly_hours). Expose it as a computed field with a machine-readable reason so the roster UI can say why someone is unbookable.

RLS. Staff SELECT via organization_memberships. Plus a patient SELECT policy via current_human_patient_profile_ids() joined to patients.organization_id — without it the portal booking picker returns zero rows (the same patient-side expansion recurs on offerings and calendars; flag it at each table).

humans has no name column ⚠️ — verified in 000002_tenancy_rbac.up.sql: provider_subject_id, provider_org_id, email, confirmed, blocked, portal_credential_generation, last_activity, preferred_language, timezone. specialists.name covers specialists; admin and customer-support staff have nowhere to store a display name. Open — either humans.name TEXT NULL (with a classification row) lands with F1, or the roster shows email as the display name.

specialist_titles

The clinic's PROFESSIONS — "Medic", "Kinetoterapeut". Per-org, soft-delete. Added 2026-08-10.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NOT NULL
key	TEXT NOT NULL	Stable slug, immutable once assigned (app-layer). The title is what a clinic renames.
title	TEXT NOT NULL
description	TEXT NULL
sort_order	INT NOT NULL DEFAULT 0
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13) — the title is the answer to "who was permitted to sign this" for every document produced under it
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, key)
Index	(organization_id, sort_order) WHERE deleted_at IS NULL

Why this exists. A rehab clinic employs two professions. A doctor may issue a medical report and a prescription; a therapist may not. Both conduct the same offerings, and both write a report — but not the same report: Raport medical and Raport de kinetoterapie are two layouts sitting in one document_categories row.

That last fact decides the design. An earlier draft gated paperwork by CATEGORY — a title declaring which categories it could issue — and it fails on the first real example, because both professions issue report and a category gate lets each reach the other's layout. The discriminator is the TEMPLATE, so the rule lives on the attachment: offering_forms.specialist_title_id and offering_documents.specialist_title_id, both nullable, NULL meaning everyone.

Rejected, and recorded so they are not re-proposed. Per-(offering × specialist) rows — ten specialists across fifteen offerings is 150 rows encoding one professional fact, and they drift, so the same therapist ends up able to prescribe on one service and not another. A boolean on the title (can_issue_medical_documents) — the enum document_categories deleted, wearing a different hat; it cannot express "may issue a work-leave certificate but not a prescription" without a second column. A hard ceiling (title → allowed categories, enforced regardless of what an offering attaches) — deliberately not built: these tables are configured by clinic management, a misconfiguration is visible in the offering's paperwork list rather than silent, and adding the ceiling later is a pure addition.

Not authorization. specialists.human_id is nullable — a calendar-only specialist has no account, therefore no role and no permissions — so this rule cannot live in RBAC. Permissions gate who may press generate; the title gates what may be issued in a given specialist's name.

Not a specialty. specialist_specialties is many-to-many, so "which of my specialties decides what I may sign" has no answer. A title is single-valued, which is the only reason it can carry this.

No seed. Unlike document_categories, which had to backfill because form_templates.category_id landed NOT NULL, every reference here is nullable and NULL already means "everyone" — so an unseeded clinic behaves exactly as it did before the concept existed.

RLS. SELECT for any principal of the org plus its patients (a title is a word rendered beside a clinician's name on a booking page). Writes ride specialists.manage — a title is roster configuration and grants no paperwork authority by itself, because which layouts it receives is decided by attaching them to an offering under offerings.manage. Two grants, two hands. No DELETE policy.

specialties

Medical specialty categories. Per-org — settled 2026-08-02 (organization_id NOT NULL). Matches the live leo system and the specialists feature spec; a platform-global catalog was the alternative and was not taken.

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

Configuration data, not clinical — hard delete is permitted (P13's exclusion list), but pre-checked against in-use counts → 409, and audited.

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)

Open: does holding a specialty gate roster assignment (a specialist can only be attached to an offering whose specialty_id they hold), or is the junction taxonomic only? Leo built the enforcing component and left it unimported, so its live behaviour answers "taxonomic". Decide before F2.1 wires offering_specialists — enforce with a 422, or say so in the glossary.

specialist_locations

M:M junction — specialists rotate across physical locations (P40). locations shipped at 1B.14 (000014_locations.up.sql); this junction is the F1 half of that contract.

Column	Type	Notes
specialist_id	UUID FK
location_id	UUID FK → locations(id)
organization_id	UUID FK	denormalized for direct RLS
created_at	TIMESTAMPTZ
PK	(specialist_id, location_id)

Locations label availability; they never partition it. See P40 and Area 4.

---

Area 3: Offerings (Clinical Service Catalog)

An Offering is a clinical service the clinic offers patients — "Initial Assessment", "Follow-up Consultation" (leo calls it serviciu). Pure catalog identity; scheduling lives in Area 4.

Renamed and re-scoped 2026-08-02. This area was titled "Service Catalog" and defined services / service_specialists / service_forms / service_attachments / service_plans / patient_service_plans / products / service_plan_products. Two things changed:

1. The name. glossary.md mandates services → offerings in the clinical domain, deferring the rename "until that area is built." Building it meets the condition, so the tables are offerings / offering_specialists / offering_forms from the first migration. Naming the stand-in services would mean renaming five tables and every FK later, on tables that by then hold production rows under the forward-only freeze.
2. The scope. Only the catalog-identity half (F2.1) is in scope. The commerce half — F2.2 service plans and F2.3 products — is deferred (see Deferred: F2.2 + F2.3). No pricing column, no purchase path, no entitlement binding on offerings. If a task starts reaching for those, it has left scope.

Offering ≠ access-offer. The shipped access_offers family (F14 commerce: shop + campaign access grants, migrations 000035–000038) is a different concept that shares a word. Conflating them already cost one wrong scope decision.

Why the catalog exists at all, given F2 is nominally out of scope: three in-scope features have hard dependencies on it. calendars.offering_id (F4) and appointments.offering_id (F5) are NOT NULL FKs with no other target; offering_forms is the mechanism that answers "which forms does this appointment get" (F3.4); offering_specialists is the roster the assignment engine iterates (F4.3). F3 Forms is not independently shippable in its useful form without it.

offerings

Not built. Lands with the F2.1 stand-in migration.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NOT NULL
title	TEXT NOT NULL	Matches specialties.title; the pre-rename services shape called this name
slug	TEXT NOT NULL
description	TEXT
specialty_id	UUID FK NULL → specialties(id) ON DELETE RESTRICT
default_duration_minutes	INT	Default only — the bookable duration is calendars.slot_duration_minutes (Area 4)
cover_url, video_url	TEXT	S3 keys
minicrm_title	TEXT	External system identifier; overrides title in outbound CRM payloads
is_public	BOOLEAN DEFAULT FALSE	Listed on the public booking browse
published, published_at	BOOLEAN / TIMESTAMPTZ
deleted_at	TIMESTAMPTZ NULL	Soft delete
created_at, updated_at	TIMESTAMPTZ
Unique	(slug, organization_id)
Index	GIN (immutable_unaccent(title) gin_trgm_ops)	Async-typeahead picker; diacritic folding is mandatory for Romanian

Deliberately absent (each was on the old services row): base_price / currency / is_addon — pricing and add-on billing are F2.2; buffer_minutes — belongs to calendars.slot_gap_minutes (Area 4), where the slot lattice is actually computed; category (consultation | therapy | examination | procedure) — no consumer in F1–F6, re-add it when one exists.

RLS. Staff by permission; plus a patient SELECT policy for the booking picker (same pattern as specialists).

offering_specialists

The roster the availability/assignment engine walks.

Column	Type	Notes
offering_id	UUID FK
specialist_id	UUID FK
organization_id	UUID FK	denormalized for direct RLS
priority	INT NOT NULL DEFAULT 0	Lower = preferred. Even distribution is expressed as all priorities equal (0), not as a separate mode — the tiebreaker below then spreads load.
PK	(offering_id, specialist_id)

The assignment tiebreaker among equal-priority candidates is a deterministic hash of (calendar_id, slot_start) — deterministic so two concurrent callers computing candidates independently agree without shared state.

offering_forms

Which form templates auto-attach when an appointment is created for this offering. This is the form-generation mechanism (F3.4), not a convenience join.

Column	Type	Notes
offering_id	UUID FK
form_template_id	UUID FK → form_templates(id)
organization_id	UUID FK	denormalized for direct RLS
category_key	TEXT	The template's category key, denormalised at attach time and never a caller input
category_is_single	BOOLEAN	Mirrors document_categories.cardinality = 'one'; a partial unique index cannot read another table
specialist_title_id	UUID FK NULL → specialist_titles(id) ON DELETE RESTRICT	Restricts this attachment to one profession. NULL = every specialist, which is what every pre-existing row carries.
sort_order	INT
PK	(offering_id, form_template_id)
Unique	(offering_id, category_key, specialist_title_id) NULLS NOT DISTINCT WHERE category_is_single	One per offering per profession

The single-cardinality rule is per TITLE (2026-08-10). report ships cardinality = 'one', and the whole point of specialist_title_id is that a clinic attaches a doctor's report and a therapist's report to the same offering. "One report per offering" was never the rule anyone wanted; "one report per offering per profession" is. NULLS NOT DISTINCT keeps the untitled row bounded to one — without it Postgres treats every NULL as unique and a clinic could attach three untitled reports, losing the guarantee for exactly the rows that are the default.

One title per attached template, because the PK is (offering, template). Two professions sharing one form means attaching it untitled; a clinic that needs "this template for these two titles but not the third" is served by a surrogate key and a widened index — a purely additive change.

Every category may attach, and cardinality is DATA (settled 2026-08-10). An earlier revision of this table carried a slot enum and an app-layer rule: disclaimer and survey multi-valued, parameters / analysis / advice single, report and medical_prescription not attachable at all. That last exclusion was the defect — it left the one document a specialist writes during a consultation with no way to be assigned to an offering, so a clinic generated a PDF from a form nothing had created.

All three properties now live on document_categories: cardinality (enforced by a partial unique index over the denormalised category_is_single flag), sort_order (the render order, formerly a slotOrder array in Go), and filled_by, which is what the exclusion was really expressing — patient categories materialise at booking, staff categories when the appointment enters inprogress. analysis is seeded staff for the reason leo already encodes it: FORM_TYPE_TO_SLOT marks it createValues: false.

Merged with calendar_forms (Area 4) at appointment creation.

offering_documents

Which PDF layouts a consultation for this offering may produce, and which profession may produce each. Added 2026-08-10 (migration 000047).

Column	Type	Notes
offering_id	UUID FK → offerings(id) ON DELETE CASCADE
pdf_template_id	UUID FK → pdf_templates(id) ON DELETE RESTRICT
organization_id	UUID FK	denormalized for direct RLS
specialist_title_id	UUID FK NULL → specialist_titles(id) ON DELETE RESTRICT	NULL = every specialist may generate it
sort_order	INT NOT NULL DEFAULT 0
PK	(offering_id, pdf_template_id)

The mirror of offering_forms, and it exists for the same reason: "which paperwork does this appointment produce" is answered by the OFFERING. Before it, the appointment's Documents tab offered every published layout in the clinic — a specialist could generate a discharge summary on an initial assessment and nothing said otherwise.

Two deliberate differences from offering_forms. No cardinality constraint: a form is MATERIALISED automatically, so two of them means a specialist opens the consultation to duplicate paperwork and the count has to be bounded — whereas a document is GENERATED on demand from a picker, and a clinic legitimately offers a short report and a long one for the same visit. "Only one current report" already lives in appointment_documents, which supersedes within a category. No category_key denormalisation: that column exists on offering_forms only to feed its partial unique index, and there is no such index here.

Enforcement is at the security boundary, not the picker. appointmentdocuments.BuildRenderContext refuses a template that is not attached (409 template_not_on_offering) or that belongs to another profession (403 template_not_for_specialist_title). The picker narrows its dropdown to the same set, and that is a courtesy — a prescription is a legal document, and "the UI did not show it" stops nobody holding an access token.

The title rule has ONE statement, offerings.TitleApplies, shared by form materialisation and document generation: an attachment with no title reaches everyone; an attachment with a title reaches only that profession, and an untitled specialist is not that profession. The opposite reading (untitled sees everything) would make the restriction something each clinician opts into by having a title set, which fails open the first time a clinic forgets one.

RLS. SELECT for any principal of the org plus its patients — deliberately wider on read than pdf_templates itself, which is staff-only, because the junction discloses that a layout is attached and never what it contains. Writes ride offerings.manage.

Deferred: F2.2 service plans, F2.3 products

Out of scope, and the deferral is deliberate rather than an oversight.

Deferred entity	What it was	Why it is not being built now
service_plans	Multi-session packages / subscription plans: plan_type, sessions_total, validity_days, access_months, telerehab_access, library_access, total_price	Overlaps heavily with the shipped patient_tiers / patient_subscriptions / access_offers / patient_content_grants chain (Area 16, migrations 000005, 000007, 000034, 000035). Two competing access models is a foundation problem, not a feature gap.
patient_service_plans	Patient enrollment + session-count progress	Same. Also carries the one genuine gap below.
products + service_plan_products	Reference catalog of physical goods bundled with a plan	Nothing in F1–F6 depends on it.
service_attachments	Files attached to a catalog row	Cosmetic; no in-scope consumer. Re-add with a real need.

The one genuine gap ⚠️ open: "this patient has N sessions of Offering X remaining," decremented as appointments are consumed. protocols.kind='enrollment' (shipped) covers program enrolment only — a patient self-enrolling in a guided exercise program — and carries no session-count semantics against a bookable offering. There is no platform equivalent today. It is F2.2-adjacent and likely deferred with it, but that has not been decided. appointments correspondingly carries no patient_service_plan_id today; see appointments-substrate.md → Reserved for F2.2.

Naming collision to resolve before F2.2 ⚠️: the glossary reserves enrollments as the rename target for service_plans, but enrollment is taken — protocols.kind IN ('prescription','enrollment') is live in production (000023_sessions.up.sql) meaning "patient self-enrolled in a guided program." F2.2 cannot be called enrollments. The glossary row needs a different target; deciding it in a docs PR costs nothing, deciding it after F2.2 ships costs a rename.

---

Area 4: Scheduling (Calendars, Hours, Overrides)

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

Every table in this Area is a tenant table and therefore carries organization_id UUID NOT NULL + RLS. This was stated for the junctions and omitted on specialist_weekly_hours and specialist_schedule_overrides — a doc bug against a CLAUDE.md hard rule, fixed 2026-08-02. Availability rows are tenant data: they say when a named clinician at a named clinic is working. A table reachable only through a FK is still directly queryable, and "the join protects it" is exactly the app-layer reasoning RLS exists to replace.

Both availability tables are state, not events — flat, never partitioned, regardless of row count (P41).

calendars

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NOT NULL
name, slug, description	TEXT
offering_id	UUID FK NOT NULL → offerings(id)	Required — the catalog identity this calendar books. Renamed from service_id with Area 3.
modality	TEXT NOT NULL DEFAULT 'online'	`online
slots_open_at, slots_close_at	TIMESTAMPTZ NULL	Explicit booking window
horizon_days	INT NOT NULL DEFAULT 30	Rolling window — how far ahead patients can book
slot_duration_minutes, slot_gap_minutes	INT	The slot lattice steps by duration + gap, generated on a local-midnight grid (not a UTC grid). Off-grid slot starts are rejected.
cooldown_minutes	INT NOT NULL DEFAULT 1440	Anti-spam; keyed per-calendar, so a patient blocked from rebooking Physio can still book Nutrition
min_lead_time_minutes	INT NOT NULL DEFAULT 1440	24h notice. Violations return a structured error (minLeadTimeMinutes, slotStart, earliestBookableAt), not a boolean — the UI has to say when booking opens
location_id	UUID FK NULL → locations(id)	NULL = remote / telerehab (P40)
assignment_strategy	TEXT NOT NULL DEFAULT 'priority'	`priority
is_public, published	BOOLEAN DEFAULT FALSE
deleted_at	TIMESTAMPTZ NULL	Soft delete
created_at, updated_at	TIMESTAMPTZ
Unique	(slug, organization_id)
CHECK	window XOR horizon	Either slots_open_at/slots_close_at are set and horizon_days = 0, or the reverse. The live system enforces this only in a client-side save handler; it belongs in the DB.

Removed with the Area 3 re-scope: override_duration_minutes / override_buffer_minutes (superseded by slot_duration_minutes / slot_gap_minutes, which are the values the engine actually reads), override_price and is_free (pricing is F2.2).

calendar_specialists

Column	Type	Notes
calendar_id	UUID FK
specialist_id	UUID FK
organization_id	UUID FK
priority	INT NULL	NULL = manual-only. Overrides offering_specialists.priority when set.
PK	(calendar_id, specialist_id)

Relationship to offering_specialists — settled 2026-08-05. Both junctions carry priority, and 000041 already describes its own as "the assignment engine's walk order". They are not competing: the calendar roster is a validated subset of the offering roster. A specialist can only be attached to a calendar if they are already attached to that calendar's offering_id; the attach path returns 422 otherwise. The offering answers who can provide this service at all; the calendar answers who books through this channel, in what order. Independent rosters were the alternative and were rejected — they leave offering_specialists.priority with no reader and let the two disagree about who provides an offering.

⚠️ override_weekly_hours JSONB is struck from this junction. Per-calendar availability overrides as JSONB on a junction row are unqueryable by the slot engine and cannot participate in the overlap constraint below. The replacement is specialist_schedule_overrides.calendar_id UUID NULL (NULL = all calendars) — settled 2026-08-05 (§8.2), matching what the live system converged on without forcing leo's read-only-until-a-calendar-is-selected UI gate.

calendar_forms

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

Column	Type	Notes
calendar_id	UUID FK
form_template_id	UUID FK
organization_id	UUID FK
category_key	TEXT	(same as offering_forms.category_key)
category_is_single	BOOLEAN	(same as offering_forms.category_is_single)
sort_order	INT
PK	(calendar_id, form_template_id)

specialist_weekly_hours

Recurring weekly availability, in the specialist's local wall-clock time.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NOT NULL	Tenant column — CLAUDE.md hard rule. Was missing from this table until 2026-08-02.
specialist_id	UUID FK
day_of_week	enum	`mon
start_time, end_time	TIME	Local wall-clock, resolved against specialists.scheduling_timezone (P23). CHECK (end_time > start_time) — see the overnight note below.
location_id	UUID FK NULL → locations(id)	NULL = remote / telerehab (P40)
created_at, updated_at	TIMESTAMPTZ
Exclusion	EXCLUDE USING gist (specialist_id WITH =, day_of_week WITH =, int4range(minutes(start_time), minutes(end_time)) WITH &&)	Subsumes leo's UNIQUE (specialist_id, day_of_week, start_time, end_time), which only caught exact duplicates.

Overnight rules are forbidden at the row level — settled 2026-08-05. The engine reads end_time <= start_time as an overnight rule and splits it at local midnight, which makes a row's occupied minutes the union [start, 1440) ∪ [0, end). No gist range can express a union, so the single-true-availability invariant below is unimplementable while that shape is storable. A night shift is therefore two rows (fri 20:00–24:00 + sat 00:00–02:00; Postgres TIME accepts 24:00:00), written by one UI action. The CHECK also rejects end_time = start_time, which the engine would read as a 24-hour window and which is almost certainly a data-entry error.

The engine keeps its overnight branch: it is the oracle's behaviour, and the differential fixtures feed it overnight input directly. That is how the transcription is known to be faithful. Nothing in production produces that input.

RLS. ENABLE ROW LEVEL SECURITY; SELECT organization_id = current_app_org_id(), mutations additionally current_app_has_permission('specialists','manage') (or whichever permission F4 seeds for availability). The patient-side booking path reads availability through the derived slot endpoint, not this table.

Bulk edits are a per-day transactional replace — delete only the affected day_of_week's rows and re-insert, inside one transaction.

specialist_schedule_overrides

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

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NOT NULL	Tenant column — CLAUDE.md hard rule. Was missing from this table until 2026-08-02.
specialist_id	UUID FK
start_date, end_date	TIMESTAMPTZ	Absolute UTC instants (contrast with weekly hours' local wall-clock TIME)
availability	BOOLEAN NOT NULL	TRUE = available, FALSE = unavailable
location_id	UUID FK NULL → locations(id)	NULL = remote / telerehab (P40)
calendar_id	UUID FK NULL	Scope of the override — settled 2026-08-05 (§8.2). NULL = every calendar, which is what a vacation is; set = this calendar only, which is how "block Physio next Tuesday but keep Nutrition open" is said.
created_at, updated_at	TIMESTAMPTZ

RLS. Same shape as specialist_weekly_hours.

Overrides REPLACE a day, they never merge. If any override exists for a local date, that date's weekly rules are skipped entirely and only the override's availability = true intervals apply. An override with no intervals — expressed as a single 00:00–23:59 availability = false row — is how "block Tuesday" is said. Getting this wrong silently double-books.

Single-true-availability invariant. Both tables carry a DB-level EXCLUDE USING gist on (specialist_id, time-range) — regardless of location_id. A specialist cannot be in two places at once; locations label availability, never partition it (P40). Requires CREATE EXTENSION btree_gist, which is not enabled today (000001 enables uuid-ossp, pgcrypto, unaccent, pg_trgm, vector, pg_stat_statements only) and must run on DATABASE_DIRECT_URL.

What this actually defends against is not double-entry, it is P40. Two rows — mon 09:00–17:00 @ Clinic Center and mon 14:00–18:00 @ Clinic North — are both legal under a plain UNIQUE, because they differ. The engine merges the windows and offers 14:00–17:00 as bookable at both locations, and the specialist is booked into two buildings at once. Hence the constraint ignoring location_id, and hence the end_time > start_time CHECK on weekly hours: without it the range is a union and the constraint cannot be written at all.

Scope of the invariant: it holds within an org. specialists.organization_id NOT NULL means one person working at two clinics has two independent specialist rows and two schedules, so a cross-org double-booking is not detectable by this constraint. Documented limitation; a cross-org check would key on specialists.human_id and is a cross-tenant read (anonymised or break-glass).

specialist_assignment_tracking

Round-robin counters per calendar.

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

Slot holds are not a table

Transient booking holds live in Redis, not Postgres — P44 forbids session-mode Postgres features in runtime paths, and a hold is a TTL'd lease with a heartbeat, which is exactly what internal/core/locks already does (Lua-atomic, 120s/45s). Hold keys, the client index, cooldown keys, and the SSE stream all namespace through cache.OrgResource(orgID, ...) — a hold key without an org dimension is a cross-tenant leak class (P42/P45).

---

Area 5: Appointments

appointments-substrate.md is authoritative for this table — full DDL, the nine-value status enum, the transition graph, indexes, RLS policies, the AppointmentCounter contract the cadence engine depends on, and the lazy-booking model. It was corrected on 2026-08-02 (four defects, one of which failed make check). The summary below is aligned to it; where they still disagree, the substrate doc wins.

No appointments table exists. Every reference to it in the shipped migrations is a forward-looking comment. It is created for the first time by F5.

appointments

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NOT NULL
patient_profile_id	UUID FK NOT NULL	References patient_profiles (P6). Two-phase identity: set at booked…
patient_id	UUID FK NULL	…and patient_id links at onboarding
specialist_id	UUID FK NOT NULL → specialists(id)	Not a principal FK — a calendar-only specialist (specialists.human_id IS NULL, P9) has no principal and would be unbookable. Whether NOT NULL survives assignment_strategy='manual' is open; see the substrate doc.
offering_id	UUID FK NOT NULL → offerings(id)	Renamed from service_id with Area 3
calendar_id	UUID FK NULL	NULL for direct registrations
location_id	UUID FK NULL → locations(id)	NULL = remote (P40)
contact_email	TEXT	Pre-onboarding notifications; name + phone live on patient_profiles. pii_basic.
booking_client_id	TEXT	Server-signed HttpOnly cookie value, not caller-supplied — it keys the public-booking cooldown
additional_offering_ids	UUID[] DEFAULT '{}'	Add-ons performed during the appointment
protocol_id, session_id	UUID FK NULL	Paired by CHECK — both NULL (stand-alone) or both set (supervised protocol). The adherence denominator reads them.
channel	TEXT NOT NULL DEFAULT 'in_person'	`in_person
scheduled_at	TIMESTAMPTZ NOT NULL
duration_minutes	INT NOT NULL	Preserved verbatim across a reschedule — never re-derived from the offering, which may have changed since booking
started_at, ended_at	TIMESTAMPTZ NULL
status	enum	`booked
cancelled_at, cancellation_reason, cancelled_by_principal_id	TIMESTAMPTZ / TEXT / UUID FK NULL	Set together with any cancelled_* status (CHECK-paired)
created_by_principal_id	UUID FK NOT NULL
created_at, updated_at	TIMESTAMPTZ

Three corrections against the previous version of this table:

1. The single cancelled status is split three ways. cancelled_by_clinic is excluded from the adherence denominator; cancelled_by_patient and cancelled_late count. A patient must not lose adherence because the clinic could not deliver capacity.
2. No deleted_at. Appointments are clinical records, but the status enum already covers every did-not-happen case. GDPR erasure anonymises (contact_email, the profile name, cancellation_reason) and preserves the structural row.
3. No patient_service_plan_id / plan_session_number. Service plans are F2.2, deferred (Area 3). The re-add when F2.2 ships is a nullable UUID FK column, not a reshape.

title and specialty_id are dropped — the title is derived from the offering, and the specialty is reachable through it.

RLS. Three SELECT policies: org staff by appointments.read permission; specialist-sees-own through the specialists join (specialist_id IN (SELECT id FROM specialists WHERE human_id = current_app_principal_id())); patient-sees-own via current_human_patient_profile_ids(). ⚠️ Zero appointments.* permission rows are seeded in any migration today — the F5 migration seeds them, or every policy denies.

appointment_files

Patient- and staff-uploaded files attached to a consultation. Bytes go to the already-registered appointment-files S3 surface.

Column	Type	Notes
id	UUID PK
appointment_id	UUID FK
organization_id	UUID FK
file_url, file_name, file_type	TEXT	file_url stores the S3 key, not a URL. Reads are presigned, 15 min (P27); Block Public Access is on at the bucket.
file_size	BIGINT
uploaded_by_principal_id	UUID FK NOT NULL → principals(id)
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13). These are patient medical documents — hard delete is not available, and there is no DELETE RLS policy.
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 | textarea | number | email | phone | date | select | radio | checkbox | scale | file | signature | national_id
options	JSONB	for select/radio/checkbox; scale uses it for min/max + end labels
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)
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, entity_type, key)
Unique	(organization_id, system_key)

Org-scoped, always (settled 2026-08-03). organization_id is NOT NULL — there is no platform-global tier. Clinics receive a starter set seeded as ordinary org rows at org creation and own them outright. The dual-scope alternative (P20) was considered and rejected: it buys cross-clinic field comparability nothing needs today, at the cost of a nullable organization_id, two partial uniqueness indexes and a fallback resolution on every read.

Not separately versioned (amended 2026-08-03). version / published / published_at are removed, and the custom_field_versions table is not built. Historical rendering is preserved by the forms.fields instance snapshot (Area 7), definition-change history is audit_log's job, and field-level rollback is not a workflow — a template is what gets rolled back. The table had no reader. form_template_versions stays.

Upgrade path if field-level history is ever wanted: an append-only custom_field_versions is a pure addition — no column on custom_fields changes.

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. Never regulated PII — see below.
created_at, updated_at	TIMESTAMPTZ
Unique	(custom_field_id, entity_type, entity_id)

value TEXT can never legally hold a pii_regulated value. A national identifier (CNP, SSN, passport) is column-encrypted BYTEA by classification rule (P12), so a field_type = 'national_id' custom field must route to patient_profiles.national_id_encrypted (Area 2) or be rejected outright at template-publish time. It must not fall through to this EAV path. The legacy system stores CNP as a plaintext EAV value; that is the specific defect this rule forecloses.

Uniqueness is per-org, never global. Both (organization_id, entity_type, key) and (organization_id, system_key). A globally-unique field key is what makes cross-tenant template copying silently point at another tenant's field definitions — copy remaps by system_key within the target org, and a dangling cross-org reference fails the copy.

This table holds the CANONICAL org-scoped value, not a form answer. A form instance never reads or writes it live: the resolver copies the value into the instance snapshot at creation, and write-back on save is a separate audited action gated on the template binding's writes_back flag (P19). Sharing one live row across instances — so that answering in one form silently rewrites every other — is the specific legacy defect this two-step forecloses, and it is also what makes a signed form immutable in practice rather than only in policy.

Two scopes of "shared", and the difference is who owns the value. A field bound here is org-scoped and never crosses a clinic boundary. A field bound to a patient_profiles column (Area 2) is patient-owned and portable across every clinic the patient registers at. Choosing the binding chooses whether the answer follows the patient to their next clinic.

---

Area 7: Forms

BUILT IN THE REPO (000042–000044), ON NO ENVIRONMENT. Staging is at 000038 and production at 000039, so none of these tables exists outside a developer's machine. The forms.* / form_templates.manage / document_categories.manage permission rows ARE seeded by those migrations. An earlier revision of this note said nothing here was built and that the permissions existed only in prose; both stopped being true with F3.

document_categories

The clinic's own paperwork taxonomy. Config, org-scoped, soft-delete.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
key	TEXT	Stable slug. Immutable once assigned — denormalised onto forms, offering_forms, calendar_forms and appointment_documents, and embedded in stored F15 filter rules
title	TEXT	What the clinic renames
description	TEXT NULL
filled_by	TEXT	patient | staff. Decides when a form materialises: at booking, or when the appointment enters inprogress
cardinality	TEXT	one | many per offering or calendar
generatable_on_appointment	BOOLEAN	May a PDF template of this category be generated onto an appointment
sort_order	INT	Render and attach order
system_key	TEXT NULL	Set on the seven seeded rows only
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13) — a document generated years ago holds this key
Unique	(organization_id, key), (organization_id, system_key)	Per-org, never global

It replaced a seven-value CHECK on form_templates.type, ported from leo's FORM_TYPE_TO_SLOT map. The entire semantic content of that enum was three properties — an order, a cardinality, and who fills it — and none of them is a property of the word "survey". A clinic wanting an eighth kind of paperwork needed a migration, a Go release and a deploy, for a word.

Behaviour is never keyed off a category NAME. The two rules that read type == 'medical_prescription' now read pdf_templates.requires_signature, derived from the layout. system_key exists for exactly two jobs, both about resolving identity across a rename — the legacy import and cross-org template copy — and nothing in the request path may branch on it. The seeded rows are ordinary org-owned rows a clinic may rename, retype or delete; deletion is bounded by the RESTRICT FK from both template tables rather than by a protected tier.

Seeded per org by an AFTER INSERT ON organizations trigger, the same mechanism custom_fields uses: disclaimer, survey, parameters (patient-filled), analysis, advice, report, medical_prescription (staff-filled).

form_templates

Form designs. Versioned (P18).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
category_id	UUID FK → document_categories(id) ON DELETE RESTRICT	What kind of paperwork this is, in the clinic's own taxonomy. Replaced a seven-value type enum (see document_categories)
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
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13)
created_at, updated_at	TIMESTAMPTZ

Attached to appointments through offering_forms (Area 3) and calendar_forms (Area 4), merged at appointment creation.

The fields JSONB entry — the binding contract

One entry per field on the template. This shape is copied verbatim into forms.fields at instance creation, so it is also the historical rendering record.

Key	Type	Notes
key	string	Generated {type}_{4 alnum}, immutable once assigned — PDFs and exports reference it
label	string	Overrides the library field's label for this template
field_type	string	Denormalised from the library so the snapshot renders standalone
options	array/object	Denormalised likewise
is_required	bool	Checked at pending → completed, never per-field on save
is_private	bool	Staff-only. Omitted from the patient DOM, the submitted payload, the required check, and the patient PDF
custom_field_id	UUID | null	Binding A — canonical value in custom_field_values (org-scoped)
profile_field_key	string | null	Binding B — canonical value in a patient_profiles column (patient-owned, portable)
writes_back	bool, default false	Whether a saved answer propagates back to the canonical store
sort_order	int

At most one of custom_field_id / profile_field_key may be set. Both set is a publish-time rejection; neither means the field is form-only and lives solely in forms.values.

writes_back is meaningless without a binding and is rejected at publish time if set on a form-only field.

Publish-time template validation rejects, at minimum:

1. is_required + is_private together — a private field never reaches the patient payload, so a required private field can never be satisfied and would deadlock the submit. It is an authoring error, caught where the author is.
2. Both bindings set, or writes_back on an unbound field.
3. A national_id field on a template with requires_national_id = FALSE.
4. A custom_field_id resolving outside the template's own organization.

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 the template at first write, not at creation (settled 2026-08-03). Immutable after signed (P14b).

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
appointment_id	UUID FK NULL	NULL = a clinic-wide form that gates every appointment (that is how org-level disclaimers work)
form_template_id	UUID FK NULL
template_version	INT NULL	Version snapshotted. NULL while pending — see the materialization note below
patient_profile_id	UUID FK NULL	Owner (P6)
title, description	TEXT
category_key	TEXT	The template's category key, FROZEN at generation — a form records what was asked and does not follow a later retitling of the taxonomy
fields	JSONB NULL	Snapshot of the template's fields, taken at first write. NULL while pending, when the form renders live from the template's current published version
values	JSONB NOT NULL DEFAULT '{}'	Submission data, GIN-indexed
files	JSONB DEFAULT '{}'	File references keyed by field key; bytes on the shipped forms-upload S3 surface
sort_order	INT DEFAULT 0
status	enum	`pending
completed_at, signed_at	TIMESTAMPTZ
created_by_principal_id	UUID FK NOT NULL → principals(id)
signed_by_principal_id	UUID FK NULL → principals(id)
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13) — clinical records are never hard-deleted, and there is no DELETE RLS policy
created_at, updated_at	TIMESTAMPTZ

fields is the single most important column in this Area. Without the per-instance snapshot, editing a template retroactively rewrites how every historical form renders — the legacy system has no snapshot and its own migration notes call that the primary reason to redesign. Instance creation is one INSERT in one transaction, never an N-row client-side loop.

Materialization: the snapshot is taken at FIRST WRITE

A form has two lives, and the boundary is the freeze point.

	pending	in_progress → completed → signed
fields	NULL	the snapshot, immutable
template_version	NULL	pinned
values	'{}'	the answers
Renders from	the template's current published version	its own snapshot

Why not snapshot at creation. Forms are created when an appointment is booked, which can be weeks ahead. A creation-time snapshot means an untouched form carries a weeks-old copy of both the field set and the prefilled values: a typo the clinic fixed on day 5 still shows on day 21, and an address the patient corrected in the portal never reaches the form. Nothing is corrupted, but a form nobody has touched is stale for no reason, and the only remedy is a "refresh unfilled forms" action a human has to remember.

Materialization happens on the first mutating call — the patient's first answer, or a staff member opening it to fill on the patient's behalf. In one transaction it: snapshots fields from the current published template version, pins template_version, runs the auto-fill resolver to copy canonical values in, and flips status to in_progress. From that instant the field set is frozen.

That places the freeze exactly where the reason to freeze begins — there is now an answer worth protecting. Untouched forms are never stale; touched forms are never rewritten.

Consequences to hold:

• A pending form whose template is later unpublished or soft-deleted has nothing to render. Materialization must fail closed with a clear error rather than snapshotting a withdrawn template; the clinic either republishes or regenerates the form.
• The "which forms does this appointment have" query joins form_templates for pending rows and reads forms.fields for the rest. Two branches, deliberately.
• template_version IS NULL ⟺ fields IS NULL ⟺ status = 'pending'. Enforced by CHECK, so no code path can produce a half-materialized row.

Auto-fill is a copy, not a redirect. At creation, values are copied in from patient_profiles (via profile_field_key) and custom_field_values (via custom_field_id) into the snapshot. On save, write-back is a separate, separately-audited action, and it happens only for bindings whose template entry sets writes_back = true (default false). Sharing one live value row across instances — so that answering in one form silently rewrites every other — is the anti-pattern this two-step exists to prevent. Detaching a form must never delete shared profile-level values.

This is also what makes "signed is immutable" true in practice rather than only in policy: with a live binding, updating a patient's weight next year would retroactively change what a signed consent said, and the stored PDF would disagree with the record it was generated from. The instance records what was true when it was filled; the canonical store records what is true now. They are allowed to differ.

Signed is immutable — any mutation once status = 'signed' returns 409 Conflict, enforced at both the handler and the service layer (P14b).

Link to the shipped consents ledger

consents.source_form_id is already reserved in 000008_consents.up.sql with the comment that the FK lights up when F3 ships the forms table, paired with CHECK ((source='form') = (source_form_id IS NOT NULL)). F3's migration adds the FK; it does not invent a second consent record. The per-clinic boundary and the append-on-grant ledger are non-negotiable (Area 15).

---

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.

F9.1 Phase 2 expansion. This area extends the F9.1 Phase 1 exercises row (already shipped — operational metadata, manifest pointer, Bunny media) with the full clinical/biomechanical taxonomy and a dedicated pose-tracking sub-domain. All design decisions are locked in exercise-taxonomy-pose-tracking.md — that doc is authoritative for why these tables look the way they do; this section reflects the schema. Two cross-cutting patterns are applied uniformly below:

• Class IIa provenance columns (tagged_by_principal_id UUID NOT NULL REFERENCES principals(id), tagged_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), clinical_basis TEXT NULL) appear on every tag-association row and every pose-config sub-row per D3. clinical_basis is nullable today and flips to NOT NULL at the Class IIa elevation; backfill is operationally infeasible post-prod, so the columns ship pre-prod.
• Per-tag deprecation columns (deprecated_at TIMESTAMPTZ NULL, replaced_by_id UUID NULL self-FK) appear on every tag entity per D4, paired with a DB trigger enforcing "tag definitions are never modified in place" — to change a tag's meaning, deprecate the old row and create a new one. This satisfies Class IIa traceability without a separate taxonomy_versions snapshot; the upgrade path to full vocabulary versioning is mechanical (see exercise-taxonomy-pose-tracking.md D4).

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

Dual-scope (P49); hierarchical via parent_id. Per D4: never modified in place — enforced by DB trigger.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NULL	NULL = platform-curated, set = org-private extension (per D5)
name, slug	TEXT
description	TEXT
parent_id	UUID FK NULL → exercise_categories(id)	Hierarchical
sort_order	INT
deprecated_at	TIMESTAMPTZ NULL	Per D4 — when this tag stopped being recommended
replaced_by_id	UUID NULL → exercise_categories(id)	Per D4 — successor tag, if any
translations	JSONB	(P21)
created_at, updated_at	TIMESTAMPTZ

exercise_body_regions

Locked to platform-only per D5 (cohort analytics require comparable vocabulary across clinics). Per D4: never modified in place — enforced by DB trigger.

Column	Type	Notes
id	UUID PK
organization_id	UUID NULL	CHECK (organization_id IS NULL) — platform-only per D5
name, slug	TEXT
body_area	enum	`upper_body
sort_order	INT
deprecated_at	TIMESTAMPTZ NULL	Per D4
replaced_by_id	UUID NULL → exercise_body_regions(id)	Per D4
translations	JSONB	(P21)
created_at, updated_at	TIMESTAMPTZ

exercise_equipment

Dual-scope (P49) — clinics may have proprietary equipment per D5. Per D4: never modified in place — enforced by DB trigger.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NULL	NULL = platform-curated, set = org-private
name, slug	TEXT
icon_url	TEXT
sort_order	INT
deprecated_at	TIMESTAMPTZ NULL	Per D4
replaced_by_id	UUID NULL → exercise_equipment(id)	Per D4
translations	JSONB	(P21)
created_at, updated_at	TIMESTAMPTZ

exercise_movement_patterns

Locked to platform-only per D5 (pose-engine rep-counting heuristics map to these). Per D4: never modified in place — enforced by DB trigger. Concrete values per D2: push | pull | squat | hinge | rotation | lunge | carry | gait | hold.

Column	Type	Notes
id	UUID PK
organization_id	UUID NULL	CHECK (organization_id IS NULL) — platform-only per D5
name, slug	TEXT
description	TEXT
sort_order	INT
deprecated_at	TIMESTAMPTZ NULL	Per D4
replaced_by_id	UUID NULL → exercise_movement_patterns(id)	Per D4
translations	JSONB	(P21)
created_at, updated_at	TIMESTAMPTZ

exercise_recovery_phases

Locked to platform-only per D5 (comparable across clinics for cohort analytics). Per D4: never modified in place — enforced by DB trigger. Concrete values per D2: acute | subacute | strength | return_to_activity | maintenance.

Column	Type	Notes
id	UUID PK
organization_id	UUID NULL	CHECK (organization_id IS NULL) — platform-only per D5
name, slug	TEXT
description	TEXT
sort_order	INT
deprecated_at	TIMESTAMPTZ NULL	Per D4
replaced_by_id	UUID NULL → exercise_recovery_phases(id)	Per D4
translations	JSONB	(P21)
created_at, updated_at	TIMESTAMPTZ

exercise_conditions

Dual-scope (P49) — clinics may track local condition names per D5. Platform-canonical name with optional ICD-10 mapping per B5 (clinicians work with clinical names, not codes; codes added when present for regulatory/insurance interop). Per D4: never modified in place — enforced by DB trigger.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK NULL	NULL = platform-curated, set = org-private per D5
name, slug	TEXT
description	TEXT NULL
icd10_code	TEXT NULL	Optional external mapping per B5
body_region_id	UUID NULL FK → exercise_body_regions(id)	Optional clinical grouping
status	TEXT NOT NULL DEFAULT 'active'	`active
sort_order	INT
deprecated_at	TIMESTAMPTZ NULL	Per D4
replaced_by_id	UUID NULL → exercise_conditions(id)	Per D4
translations	JSONB	(P21) — display_name_translations
created_at, updated_at	TIMESTAMPTZ

exercise_skill_prerequisites

Locked to platform-only per D5 (used in algorithmic program suggestion; vocabulary lock matters). Per D4: never modified in place — enforced by DB trigger. Concrete values per D2: balance_static | balance_dynamic | single_leg_stance | floor_to_stand | grip_strength | bilateral_coordination | weight_bearing_tolerance | core_endurance.

Column	Type	Notes
id	UUID PK
organization_id	UUID NULL	CHECK (organization_id IS NULL) — platform-only per D5
name, slug	TEXT
description	TEXT
sort_order	INT
deprecated_at	TIMESTAMPTZ NULL	Per D4
replaced_by_id	UUID NULL → exercise_skill_prerequisites(id)	Per D4
translations	JSONB	(P21)
created_at, updated_at	TIMESTAMPTZ

exercise_tags

Polymorphic junction (P24). tag_type ENUM extended per D2 to cover all axes. Class IIa cols per D3.

Column	Type	Notes
exercise_id	UUID FK
tag_type	enum	`category
tag_id	UUID FK	Resolved against the appropriate table per tag_type
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3 — flips NOT NULL at Class IIa elevation
PK	(exercise_id, tag_type, tag_id)

exercise_prerequisites

Self-M2M between exercises per D2 — "Bird Dog before Side Plank" chains for program-builder ordering.

Column	Type	Notes
exercise_id	UUID FK → exercises(id)
prerequisite_exercise_id	UUID FK → exercises(id)	The exercise that must be mastered first
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3
PK	(exercise_id, prerequisite_exercise_id)

exercise_instructions

Class IIa cols added per D3.

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
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3
translations	JSONB
created_at, updated_at	TIMESTAMPTZ

exercise_contraindications

Per B5: condition_name freetext replaced with condition_id FK to exercise_conditions. Class IIa cols per D3.

Column	Type	Notes
id	UUID PK
exercise_id	UUID FK
condition_id	UUID NOT NULL FK → exercise_conditions(id)	Per B5 — replaces freetext condition_name
description	TEXT
severity	TEXT	`warning
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3
translations	JSONB
created_at	TIMESTAMPTZ

pose_engines

Reference table — pose-engine vendor catalog. Read-only at the API layer; seeded with mediapipe.holistic at F9.1 Phase 2. Per D15.

Column	Type	Notes
id	UUID PK
code	TEXT NOT NULL UNIQUE	E.g., mediapipe.holistic, mediapipe.pose
display_name	TEXT NOT NULL
vendor	TEXT NOT NULL	E.g., Google MediaPipe
version	TEXT NOT NULL	Engine release version
landmark_catalog_version	INT NOT NULL	Bumps when the engine's landmark vocabulary changes
status	enum	`active
created_at, updated_at	TIMESTAMPTZ

pose_landmarks

Reference table — per-engine landmark catalog (~543 rows for MediaPipe holistic = 33 pose + 21 left hand + 21 right hand + 468 face). Per D17.

Column	Type	Notes
id	UUID PK
engine_id	UUID NOT NULL FK → pose_engines(id)
code	TEXT NOT NULL	E.g., nose, left.shoulder, right.knee
display_name	TEXT NOT NULL
display_name_translations	JSONB NOT NULL DEFAULT '{}'	(P21)
body_part_category	enum	`head
status	enum	`active
deprecated_at	TIMESTAMPTZ NULL	Engine-vocabulary evolution (analogous to D4)
created_at, updated_at	TIMESTAMPTZ
Unique	(engine_id, code)

exercise_pose_configs

1:1 with exercises per D8 (active config per exercise; history in exercise_pose_config_history). Pinned to asset_version per D9 — a DB trigger on exercises.asset_version UPDATE flips this row's status to invalidated and reverts tracking_enabled to FALSE, forcing the clinician to re-author. Cloning an exercise copies this row per D9 (new id, new exercise_id, inherits pinned_asset_version, Class IIa cols reflect the cloning principal). Class IIa cols per D3.

Column	Type	Notes
id	UUID PK
exercise_id	UUID NOT NULL UNIQUE FK → exercises(id)	1:1 per D8
tracking_enabled	BOOLEAN NOT NULL DEFAULT FALSE	Master switch per D15
engine_id	UUID NOT NULL FK → pose_engines(id)	Per D15
camera_angle	enum	`frontal
camera_distance_cm_min	INT NULL	NULL = no minimum (D16)
camera_distance_cm_max	INT NULL	NULL = no maximum (D16)
lighting_requirement	enum	`frontal
in_frame_requirements	TEXT[]	Multi-select from fixed vocabulary, e.g. ['fata_integral_vizibila', 'umeri_in_cadru'] — per D16
rep_success_rule_type	enum	`angle_cycle
rep_success_rule_params	JSONB NOT NULL	Type-specific params validated app-side; composite = nested {operator, children} tree — per B8 / D20
pinned_asset_version	INT NOT NULL	Per D9; DB trigger on exercises.asset_version UPDATE invalidates this row
min_landmark_confidence	NUMERIC NOT NULL DEFAULT 0.5 CHECK (>= 0 AND <= 1)	Per B2 — per-frame aggregate confidence threshold
status	enum	`draft
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3
created_at, updated_at	TIMESTAMPTZ

exercise_pose_config_history

Immutable snapshots per D8 — every edit to exercise_pose_configs (including status transitions and invalidation events) writes a full-row snapshot. Required for Class IIa reproducibility: given a session_run, the exact pose config active at that time is recoverable.

Column	Type	Notes
id	UUID PK
exercise_pose_config_id	UUID NOT NULL FK → exercise_pose_configs(id)
snapshot_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
snapshot_reason	enum	`edit
(full snapshot of every column on exercise_pose_configs at snapshot time)		Append-only — no UPDATE/DELETE policies
created_at	TIMESTAMPTZ

exercise_pose_landmarks

M2M between a pose config and the landmark subset it tracks per D17 ("fewer landmarks = more stable tracking"). Class IIa cols per D3.

Column	Type	Notes
exercise_pose_config_id	UUID FK → exercise_pose_configs(id)
landmark_id	UUID FK → pose_landmarks(id)
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3
PK	(exercise_pose_config_id, landmark_id)

exercise_pose_metrics

Per-config metric definitions per D18. landmark_refs is used for direct-measurement metrics (sources from pose_landmarks); derived_from_metric_ids is used for composite/derived metrics (e.g., symmetry references sibling rotL + rotR metrics). Per-config weight_pct values are validated app-side to sum to 100. Per B4: removing a referenced landmark or metric is blocked at the application layer with a clear error. Class IIa cols per D3.

Column	Type	Notes
id	UUID PK
exercise_pose_config_id	UUID NOT NULL FK → exercise_pose_configs(id)
metric_type	enum	`angle
target_min	NUMERIC	E.g., 60 (degrees)
target_max	NUMERIC	E.g., 80
tolerance	NUMERIC	E.g., ±5
weight_pct	INT CHECK (weight_pct BETWEEN 0 AND 100)	Contribution to overall success score
landmark_refs	UUID[]	Source landmarks for direct-measurement metrics (NULL/empty for derived metrics)
derived_from_metric_ids	UUID[] NULL	Sibling metric IDs for composite/derived metrics; NULL for direct-measurement metrics
axis	enum	`x
label	TEXT	Patient/clinician-facing name
label_translations	JSONB NOT NULL DEFAULT '{}'	(P21)
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3
created_at, updated_at	TIMESTAMPTZ

exercise_pose_feedback_rules

Single table collapsing form-errors and live-warnings into one schema per D10 — semantically identical (condition → patient-facing message), differentiated by severity. Bare version per D10/D19: no rate-limiting columns (min_interval_seconds, trigger_once_per_rep) at F9.1 Phase 2 — added later as additive nullable columns when real "too noisy" complaints surface. condition_expression DSL is deferred per DF1; condition_format discriminates the DSL version (default text_v1). Class IIa cols per D3.

Column	Type	Notes
id	UUID PK
exercise_pose_config_id	UUID NOT NULL FK → exercise_pose_configs(id)
severity	enum	`warning
condition_expression	TEXT NOT NULL	Engine-parseable DSL — grammar deferred per DF1
condition_format	enum NOT NULL DEFAULT 'text_v1'	Discriminator for DSL version per DF1
patient_message	TEXT NOT NULL	What the patient sees
patient_message_translations	JSONB NOT NULL DEFAULT '{}'	(P21)
tagged_by_principal_id	UUID NOT NULL FK → principals(id)	Class IIa per D3
tagged_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Class IIa per D3
clinical_basis	TEXT NULL	Class IIa per D3
created_at, updated_at	TIMESTAMPTZ

pose_data_quality_overrides

Per B3 — specialist clinical override of session pose data at session_run or session_exercise_event granularity (NOT per-rep). Stats queries (per-patient, cohort) and the promotion threshold (D13) exclude overridden data via WHERE NOT EXISTS; raw pose data stays in the DB (audit intact) but does not feed scoring. Every insert audit-logged with full principal_id + reason (Class IIa requirement). UI for specialists to invoke the override is deferred per B3 (initial workflow = support request to platform team, same pattern as DF4).

Column	Type	Notes
id	UUID PK
organization_id	UUID NOT NULL FK → organizations(id)	Standard tenant scoping
scope	enum	`session_run
session_run_id	UUID NOT NULL FK → session_runs(id)	The session being overridden
session_exercise_event_id	UUID NULL FK → session_exercise_events(id)	Set when scope = 'session_exercise_event'
override_reason	TEXT NOT NULL	Specialist's clinical rationale
overridden_by_principal_id	UUID NOT NULL FK → principals(id)	Auditable per B3
overridden_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
created_at	TIMESTAMPTZ
CHECK	(scope = 'session_run' AND session_exercise_event_id IS NULL) OR (scope = 'session_exercise_event' AND session_exercise_event_id IS NOT NULL)	Per B3

---

Area 10: Telerehab — Treatment Plans (RETIRED)

Retired — do not build against this Area

Retired 2026-08-02. This Area specified treatment_plans / treatment_plan_versions / treatment_plan_sessions / treatment_plan_session_exercises / patient_treatment_plans / patient_session_completions / patient_exercise_logs. None of those tables was ever created, and none will be. The full schema tables were carried here under a SUPERSEDED banner from 2026-05-23 until now; they are removed rather than left in place, because a reader scrolling into a 140-line schema block does not reliably carry the banner with them. Git history has the original if it is ever needed forensically.

What shipped instead — live in production since 2026-06-05, migrations 000023, 000025, 000026:

Retired entity	Shipped replacement
treatment_plans (global / org / per-patient scopes)	programs + program_phases + program_assets — three ownership tiers (platform / org / patient-specific) under P49, with copy-on-derive deep copies at every transition. programs.derived_from_program_id is the flat lineage pointer.
treatment_plan_versions (snapshot table)	Nothing. Per-prescription isolation comes from the server-side deep copy, not from version snapshots. program_versions and session_versions were designed and dropped for the same reason.
treatment_plan_sessions (+ the program_sessions junction)	sessions themselves, containment-mapped via sessions.program_id + phase_id + order_in_phase. Sessions are source-agnostic (2026-05-17).
treatment_plan_session_exercises	session_exercises (+ session_audio_items, session_assets, content_files)
patient_treatment_plans (enrollment)	protocols — the workflow wrapper around a patient-instance program; renamed from patient_assignments on 2026-05-23 because "assignment" was a relic of the shared-by-reference model. Plus protocol_pauses. protocols.kind IN ('prescription','enrollment') distinguishes specialist-assigned from patient-self-enrolled.
patient_session_completions	session_runs
patient_exercise_logs	session_exercise_events (+ session_pain_events), both monthly-partitioned per P41

Two model decisions worth carrying forward, because they are the reason the retired shape does not map cleanly:

• The session_runs.assignment_id FK was dropped 2026-05-23. The protocol a run satisfies is derivable: session_runs.session_id → sessions.program_id → protocols.program_id, deterministically, because each patient-instance program is owned 1:1 by exactly one protocol.
• session_runs carries both status and completed, and they are orthogonal — auto_closed + completed=TRUE and ended_explicit + completed=FALSE are both normal outcomes.

Canonical docs: features/programs-and-assignments/ (locked 2026-05-21, reshaped 2026-05-22, renamed 2026-05-23), features/sessions/, and cadence-and-supervision.md for the adherence side.

---

Area 11: Documents

Nothing here is built — no pdf_templates, no appointment_documents, no pdf.Renderer. documents.* permission rows are seeded in zero migrations. Lands with F6, which depends on F3 (a document is a rendering of a signed form) and F5.

pdf_templates

Block-based PDF designer output.

Reconciled 2026-08-06 against the settled rendering engine. template_html, template_css ("Go template syntax") and components_used were dropped from this spec before the migration was written. The first two encoded an HTML-rendering assumption that no longer holds: rendering is @react-pdf/renderer server-side (features.md § F6), which consumes the block JSON directly and has no HTML stage — so nothing would ever write them, making them reserved columns with no future writer (P36). components_used was a denormalized cache of what editor_state already states; the "which templates use this component" query is a JSONB containment lookup against editor_state, which cannot drift from its own source.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
category_id	UUID FK → document_categories(id) ON DELETE RESTRICT	The SAME catalog form templates name, so a clinic defines "Rețete medicale" once. Replaced a five-value template_type enum, two of whose values (certificate, invoice) nothing ever read
requires_signature	BOOLEAN NOT NULL DEFAULT FALSE	DERIVED from whether the layout contains a signature block, exactly as requires_national_id is derived from a CNP-selecting block. It replaced template_type = 'medical_prescription' as the trigger for the two signature rules — a rule attached to a type NAME held only for documents somebody remembered to name that way
editor_state	JSONB NOT NULL DEFAULT '{"blocks": []}'	The template itself, not a cache of it. Ordered block list — shape below
layout_config	JSONB NOT NULL DEFAULT '{}'	pageSize, orientation, margins, base font, accent colour
version	INT NOT NULL DEFAULT 1
published	BOOLEAN NOT NULL DEFAULT FALSE
published_at	TIMESTAMPTZ NULL	Constrained in a pair with published, as on form_templates — stamped on first publish, survives republish, so it keeps meaning "first went live at"
requires_national_id	BOOLEAN NOT NULL DEFAULT FALSE	DERIVED, never supplied (2026-08-07). True when any patient_details block selects the cnp field; the service recomputes it on every editor_state write and the API refuses to accept it. Stored rather than computed on read so "which templates print a CNP" is a column scan. The form-side twin on form_templates was REMOVED.
created_by_principal_id, updated_by_principal_id	UUID FK NULL → principals(id)	Actor columns are principal-rooted; the bare created_by / updated_by names predate the actor model
created_at, updated_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ NULL	Soft delete (P13), diverging from the "config tables hard-delete" default. appointment_documents.pdf_template_id points here and an auditor must resolve which template produced a document years after the clinic stopped using it — the identical argument that put deleted_at on form_templates.
Unique	(organization_id, name)

editor_state — the block list

A fixed palette of block types, ordered, not a free-form canvas. Each type maps 1:1 onto a @react-pdf component, which is what keeps pagination correct when a form carries 3 answers or 40 — a free-positioning designer has nowhere for variable-length content to flow, and @react-pdf is a flexbox layout engine with no absolute positioning for flowing content.

{
  "blocks": [
    { "id": "b1", "type": "letterhead",     "config": { ... } },
    { "id": "b2", "type": "patient_details", "config": { "fields": ["name", "age_at_appointment", "cnp"] } },
    { "id": "b3", "type": "form_answers",   "config": { "heading": "Servicii efectuate" } },
    { "id": "b4", "type": "rich_text",      "config": { "variant": "boxed", "content": [ ... ] } },
    { "id": "b5", "type": "signature",      "config": { ... } },
    { "id": "b6", "type": "footer",         "config": { ... } }
  ]
}

Block type	Renders	Ported from
letterhead	Org logo, name, tagline, document title. fixed — repeats on every page	leo's styles.header
patient_details	Two-column label/value identity block; config.fields selects from a server-side allow-list derived from the classification registry	leo's styles.details
form_answers	The signed form's values, groups expanded. Audience pruning is driven by template_type (D3: reports prune is_private, prescriptions show everything), never per-block	leo's fields.map(...) loop
rich_text	Clinician-authored prose. `variant: plain	boxed—boxed` is leo's grey support panel
signature	Specialist signature, base64-embedded at render	leo's signature view
page_break	Forces a new page	—
footer	fixed bottom band with Pagina n / total	leo's styles.footer

Why a palette and not a canvas: all three of leo's hardcoded templates share one layout skeleton and differ almost entirely in prose — nutritional.tsx's 550 lines are clinician-authored Romanian dietary and lab-panel content, not geometry. The variation clinics actually need is content blocks. A canvas would buy layout freedom nobody has asked for at the cost of the pagination guarantee.

The variable namespace inside any block is a server-side allow-list derived from the classification registry, never "whatever is on the record" — the legacy system interpolated a patient's stored password into rendered consent text (G10).

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_principal_id	UUID FK NULL → principals(id)
change_notes	TEXT
created_at	TIMESTAMPTZ
Unique	(template_id, version)

Append-only (P14a): no UPDATE and no DELETE RLS policies. A regenerated document produces a new row, never an in-place file swap.

pdf_template_components

A clinic's saved, pre-configured blocks — "our standard letterhead" reused across every template instead of reconfigured per template.

Reconciled 2026-08-06 alongside pdf_templates. component_html / component_css are gone for the same reason (no HTML stage), replaced by a blocks JSONB holding the same block shape editor_state uses — a component is a fragment of a template, so it must be the same thing a template is made of, or the editor needs two renderers. variables_used is gone as a derivable cache: it restates what the blocks already declare, and a cache of a JSONB document that drifts from that document is worse than the containment query it replaces.

Column	Type	Notes
id	UUID PK
organization_id	UUID FK
name, description	TEXT
blocks	JSONB NOT NULL DEFAULT '[]'	Same block shape as editor_state.blocks
category	TEXT	`header
created_by_principal_id	UUID FK NULL → principals(id)
created_at, updated_at	TIMESTAMPTZ
Unique	(organization_id, name)

Insertion copies, it does not reference. Dropping a component into a template copies its blocks into editor_state; the template does not hold a live pointer. Live binding would make every historical document's layout mutable by editing a component years later — the same defect the forms.fields snapshot exists to prevent (C5/C6), and the reason appointment_documents.pdf_template_version is frozen at generation.

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)
category_key	TEXT	The category key, FROZEN at generation. Which categories may be generated onto an appointment is document_categories.generatable_on_appointment, not a fixed pair
pdf_template_id	UUID FK NULL
pdf_template_version	INT NULL	Frozen at generation — the counterpart of forms.template_version. Without it, editing a template retroactively changes what a historical document claims to say.
title	TEXT NOT NULL
document_url	TEXT	Stores the S3 key, not a URL. Reads are presigned (15 min, P27) and write a document.pdf_accessed audit row.
published	BOOLEAN
metadata	JSONB	PDF generation metadata
created_at, updated_at	TIMESTAMPTZ
Unique	(appointment_id, type)	one per type per appointment

Generation invariants (compliance rules, not preferences):

• PDFs are self-contained — the specialist signature is embedded as base64; no external URLs, no remote image fetches at render time.
• Prescription generation refuses without a signature, returning a typed 422. This is a fail-closed path with its own test, not a UI check.
• Rendering is a server-side pdf.Renderer capability registered through capabilities.WrapInternal — not a client-side render in the staff browser. The engine is settled (2026-08-06, from measurement): @react-pdf/renderer v4 server-side in Node, synchronous, rendered in apps/clinic. No headless browser, no sidecar, no new ECS service — so there is no task-shape or image-size question, and the SOUP row is a package.json dependency. Measurements and the reasoning are in features.md § F6. The Go side owns the contract and remains the only writer of appointment_documents; it does not rasterise.
• A Romanian-capable font must be registered from a real file path, with a test asserting ș/ț/ă/î/â survive a render round-trip. Default fonts drop them silently — no error, and â survives, so the output looks correct at a glance while București renders as Bucureti.
• Audience differs by document type: reports prune is_private fields (a private group hides all its children), prescriptions show everything. Patient age is computed at the appointment date, not at render time.

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.

This resurfaced in 2026-08: the legacy system's own Go-migration design docs specify per-org HTML/CSS document_templates, and a survey of them recommended that shape as the starting point for F6. It is not. Architecture docs beat foreign migration docs; mine those for the template funcmap, the render-data struct, and the error→HTTP mapping only.

prescription is an overloaded word — settled 2026-08-02. protocols.kind = 'prescription' is live in production and means "a specialist assigned an exercise program to a patient." F6's document type means a medical prescription PDF (rețetă). Two different things. The rule (glossary.md → Two senses of prescription): the shipped exercise-program sense keeps the bare word; the F6 document type is always medical_prescription. So the seeded category's key is medical_prescription — carried on appointment_documents.category_key and offering_forms.category_key — and never bare prescription. The shipped sense is not renamed — it is live across a CHECK constraint, a unique index, the protocols.prescribe permission, the content.prescription_play entitlement, a Go constant and patient-facing copy; qualifying the unbuilt side costs one enum value.

---

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/api-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) register at F3.5 and write into the same table with source = 'form'. Those three are the complete Tier B catalog: a purpose exists so code can ask before acting, and a clinic's own treatment consents branch nothing — they are signed documents carried by offering_forms.slot = 'disclaimer'. The treatment_specific_* family earlier docs named was struck 2026-08-05 (features.md → F3.5.1). 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 {{clinic_name}}, {{registered_address}}, {{dpo_email}}, 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. Load-bearing (publish-on-save): the clinic tier editor cuts a new version on every "Save & publish" (patienttiers.PublishVersion), and patientsubscriptions.CreateOnTx snapshots a new subscription's frozen entitlements/limits from the version at patient_tiers.version — not the live junction — so unpublished draft edits never reach new subscribers. patient_tiers.version = 0 (with published = false) is an unpublished Draft: not subscribable (ResolveDefaultTier/ResolveTierVersion require published = TRUE). The org-side tier_versions carries the identical shape but is not yet wired this way (org-side subscriptions.Create still snapshots from the live junction).

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 (deferred with F2.2 — depends on service_plans)

⚠️ Deferred, not next. This table's only FK target is service_plans, which is F2.2 and out of scope (see Area 3 → Deferred). It is not built and does not land with the current feature wave. It is also the shape most likely to be replaced rather than built: the access model it implements — counted grants attached to a subscription — already shipped as patient_tiers / patient_subscriptions / access_offers / patient_content_grants. Re-evaluate whether patient_tier_inclusions is still needed at all when F2.2 is picked up.

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 (Offerings)	Core domain entities: offerings, offering_specialists (F2.1 only — service_plans / products are deferred, see Area 3)
Layer 3.2 (deferred with F2.2)	patient_tier_inclusions; patient_service_plans.source_tier_subscription_id FK column; the treatment_plan_assignments_total counter — all deferred, and the counter additionally points at a model (treatment_plans) that was retired (Area 10)
Layer 4 (Forms)	Core domain entities: custom_fields, form_templates, form_template_versions, forms; offering_forms junction (depends on Layer 3)
Layer 5 (Scheduling)	Core domain entities: calendars, calendar_specialists, calendar_forms, specialist availability tables
Layer 6 (Appointments)	Core domain entities: appointments (depends on patients, specialists, offerings, calendars, forms)

These "Layer N" labels are the plans-and-subscriptions reservation sequence, not the current build order. The live build order is F1 → F2.1 → F3 → F4 → F5 → F6 in platform-completion.md.

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 — SHIPPED, migrations 000022-000026)
        ├─────────────────────────────────────┐
        ▼                                     ▼
 ┌─────────────────────┐             ┌───────────────────────┐
 │     protocols       │────owns────▶│      programs         │
 │ (kind: prescription │  1:1 patient│ (platform / org /     │
 │      | enrollment)  │   instance  │  patient_specific)    │
 └────────┬────────────┘             └────────┬──────────────┘
          │                                   │ program_phases
          │                                   ▼
          │                          ┌───────────────────┐
          │                          │     sessions      │
          │                          │ (program_id +     │
          │                          │  order_in_phase)  │
          │                          └────────┬──────────┘
          ▼                                   │ session_exercises
 ┌─────────────────────┐                      ▼
 │    session_runs     │────refs────▶┌────────────────────┐
 │  (+ _exercise_      │             │     exercises      │
 │     events, pain)   │             │  (global / org)    │
 └─────────────────────┘             └────────────────────┘

[parallel: offerings → 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.

• F2 Offerings — resolved 2026-08-02: the F2.1 catalog-identity stand-in ships, named offerings from day one. F2.2 / F2.3 deferred. Area 3.

• specialties per-org or global — resolved 2026-08-02: per-org, organization_id NOT NULL. Area 2.

• CNP on forms and documents — resolved 2026-08-02, BUILT 2026-08-07: one encrypted home on patient_profiles (+ a national_id_hmac blind index for search); never in custom_field_values.value OR forms.values, each refused by its own trigger; patient-entered only; staff read it through the audited /national-id reveal endpoint. The per-template opt-in flags were removed — the field's presence is the declaration. Areas 2, 6, 7, 11.

• Profile-sharing consent — resolved 2026-08-20 by REMOVAL. patients.profile_shared, the profile_sharing purpose and its flip trigger are gone; registering a patient is the disclosure, and the patients row is the scope. Rationale in patterns.md → P8 (retired). Areas 2, 15.

Still open, and each blocks a specific migration in the current wave:

• Override scoping — is a schedule override per-specialist-global or scoped to one calendar? Blocks the availability migration. Area 4.
• Appointment-package tracking — "N sessions of Offering X remaining"; no platform equivalent, F2.2-adjacent. Area 3.
• humans has no name column — blocks the F1 staff roster UI. Area 2.
• appointments.specialist_id NOT NULL vs. nullable under assignment_strategy = 'manual'. Area 5 / appointments-substrate.md.
• prescription naming collision — settled 2026-08-02: the shipped protocols.kind='prescription' keeps the bare word; the F6 document type is medical_prescription (the seeded document_categories key, carried on appointment_documents.category_key and offering_forms.category_key). See glossary.md → Two senses of prescription. Area 11.
• service_plans → enrollments rename target — blocked; enrollment is taken by the shipped protocols.kind. Area 3.

The full ranked list, including the ones that do not touch schema, is in leo-port-map.md §8.

---

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.