All notebooks

OrgBAC: Users & Patients by Organization

Reference notebook for apps/practitioner-app's OrgBAC sharing model — shows every Aidbox User and every Patient alongside the Organization their tenant-organization-id extension points at (LEFT JOIN, so untagged/pre-OrgBAC records show up too, with a null org).

3 cells · updated Aug 20, 2026

Run in Aidbox

This is a read-only view. Responses shown were saved when the notebook was published — open it in your own Aidbox to run the cells against live data.

OrgBAC: Users & Patients by Organization

Both queries below read the https://aidbox.app/tenant-organization-id meta.extension OrgBAC stamps onto any resource created through Organization/{id}/fhir/* (see apps/practitioner-app). A null org means the resource predates OrgBAC or was created outside the org-scoped API.

SQL Send

SELECT u.id AS user_id,
       u.resource->>'email' AS email,
       o.id AS org_id,
       o.resource->>'name' AS org_name
FROM "user" u
LEFT JOIN LATERAL jsonb_array_elements(u.resource#>'{meta,extension}') AS ext
  ON ext->>'url' = 'https://aidbox.app/tenant-organization-id'
LEFT JOIN organization o
  ON o.id = split_part(ext#>>'{valueReference,reference}', '/', 2)
ORDER BY org_name NULLS LAST, email;

SQL Send

SELECT p.id AS patient_id,
       coalesce(p.resource#>>'{name,0,family}', '') || ', ' || coalesce(p.resource#>>'{name,0,given,0}', '') AS patient_name,
       o.id AS org_id,
       o.resource->>'name' AS org_name
FROM patient p
LEFT JOIN LATERAL jsonb_array_elements(p.resource#>'{meta,extension}') AS ext
  ON ext->>'url' = 'https://aidbox.app/tenant-organization-id'
LEFT JOIN organization o
  ON o.id = split_part(ext#>>'{valueReference,reference}', '/', 2)
ORDER BY org_name NULLS LAST, patient_name;