# Health data tenancy and global reference catalogs

The repository's architectural decision, rationale and consequences.

# Health data tenancy and global reference catalogs

**Status:** accepted · **Date:** 2026-10-20 · **Related:** [ADR-0003](0003-organizations-are-the-tenant.md), [ADR-0009](0009-row-level-security.md), [ADR-0010](0010-procedures-run-one-effect-program.md)

**Enforcement:** `packages/db/src/rls-catalog.test.ts` and `db:verify-rls` (grants match `scripts/rls-baseline.mjs`); the lab, booking and order procedure tests (cross-tenant `NOT_FOUND`, release gating, hold privacy); `scripts/check-system-call-sites.mjs` (the `demo.advance` reason).

The aescolab demo adds patient health data (Result Sets, Measurements, Lab Orders, Appointments) and reference data everybody reads (Biomarkers, packages, Partner Practices, postcodes). Both have to fit the existing tenancy model without a second isolation mechanism, and the practice finder has to work for visitors who are not signed in.

## Decision [#decision]

**Patient health data belongs to the User's personal Organization.** Every User already gets `org_<userId>` (`provision-organization.ts`). Every health table carries `organization_id` with `inActiveOrganization` policies, so RLS is the privacy guarantee: patient A never reads patient B, whatever a procedure does.

**Reference catalogs are global and read-only for tenants.** `biomarker`, `biomarker_range`, `biomarker_alias`, `lab_package`, `practice`, `practice_hours` and `postcode_centroid` are `withRLS` tables with the `feature_flag` precedent: one select policy `using (select app.session_trusted())` and `GRANT SELECT` only. A `publicProcedure` (anonymous tenant handle) can read them, so the finder needs no `asSystem`.

**Slot capacity is shared without leaking who booked.** `appointment_hold (practice_id, starts_at)` is the primary key. Tenants insert and delete their own holds (`inActiveOrganization`) and read every hold through a column-level grant on `(practice_id, starts_at)` only; `organization_id` and `appointment_id` are never selectable by `app_tenant`. A booking inserts the hold and the appointment in one tenant transaction; the unique violation is the `SlotTaken` error.

**Release gating is enforced by RLS.** The `lab_result_set` select policy is `inActiveOrganization(organization_id) and released_at is not null`, and measurements are readable only through a released set. A result the lab has not released is invisible to its owner even through a faulty procedure.

**Status changes cross tenants only through reviewed paths.** Tenants read their orders and may pick a practice while an order waits for its sample, but never change its status. The admin pipeline runs through `adminProcedure` (`asSystem(db, "admin", …)`) and sees metadata only: order number, initials, package, status, timestamps and a marker count, never measurement values. In Demo Mode a patient may advance their own `is_demo` order through the same service under the `demo.advance` system reason, after the tenant handle proved the order is theirs.

## Consequences [#consequences]

- Pure lab logic (scoring, label normalisation, the plan rules, deterministic demo values, release) lives in `packages/db/src/lab/`, so the seed and the server share one implementation. Clients reach the scoring through the `@template-cloudflare/api/lab-results/scoring` leaf, which re-exports it.
- No new isolation mechanism and no PostGIS: distance search is Haversine over the 58 practice rows in the service.
- Admin views are deliberately less informative than they could be; that is a data-minimisation choice, stated in the UI.
- A new health table needs the same four steps as any tenant table (policy, `TENANT_GRANTS`, GRANT migration, catalog test).