Status: accepted · Date: 2026-10-20 · Related: ADR-0003, ADR-0009, ADR-0010
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
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
- 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/scoringleaf, 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).