Recipes

Org Hierarchies

Subtree-scoped rules that follow the organization tree.

Situation. Groupe Hospitalier Valmont wants every physician in the North hospital, including its departments, to read patient records. New departments must be covered automatically the day they are created.

Setup

Every recipe in this section shares this idempotent Valmont base. Run it once (or again, harmlessly):

INSERT INTO morbac.orgs (name) VALUES ('Groupe Hospitalier Valmont')
ON CONFLICT (name) DO NOTHING;
INSERT INTO morbac.orgs (name, parent_id)
SELECT v.n, (SELECT id FROM morbac.orgs WHERE name = 'Groupe Hospitalier Valmont')
FROM (VALUES ('Clinique Valmont Nord'), ('Clinique Valmont Sud')) v(n)
ON CONFLICT (name) DO NOTHING;
INSERT INTO morbac.orgs (name, parent_id)
SELECT 'Pharmacie Valmont Nord', (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord')
ON CONFLICT (name) DO NOTHING;
 
INSERT INTO morbac.views (name) VALUES
    ('patients'), ('prescriptions'), ('lab-results'), ('invoices')
ON CONFLICT (name) DO NOTHING;
INSERT INTO morbac.activities (name) VALUES ('prescribe'), ('dispense')
ON CONFLICT (name) DO NOTHING;
 
INSERT INTO morbac.roles (org_id, name)
SELECT o.id, r.n
FROM morbac.orgs o,
     (VALUES ('physician'), ('nurse'), ('pharmacist'), ('billing-clerk')) r(n)
WHERE o.name IN ('Clinique Valmont Nord', 'Clinique Valmont Sud')
ON CONFLICT (org_id, name) DO NOTHING;
INSERT INTO morbac.roles (org_id, name)
SELECT id, 'auditor' FROM morbac.orgs WHERE name = 'Groupe Hospitalier Valmont'
ON CONFLICT (org_id, name) DO NOTHING;

Dr. Camille Rousseau is our North physician:

INSERT INTO morbac.user_roles (user_id, role_id, org_id)
SELECT '11111111-1111-4111-8111-111111111111', r.id, r.org_id
FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Clinique Valmont Nord' AND r.name = 'physician'
ON CONFLICT DO NOTHING;

One rule, whole subtree

Write the rule once, at Clinique Valmont Nord, with scope subtree:

INSERT INTO morbac.rules (org_id, role_id, activity, view, context_id, modality, scope)
SELECT o.id, r.id, 'read', 'patients',
       (SELECT id FROM morbac.contexts WHERE name = 'always'),
       'permission', 'subtree'
FROM morbac.orgs o
JOIN morbac.roles r ON r.org_id = o.id AND r.name = 'physician'
WHERE o.name = 'Clinique Valmont Nord'
ON CONFLICT DO NOTHING;

Proof

SELECT morbac.is_allowed('11111111-1111-4111-8111-111111111111',
    (SELECT id FROM morbac.orgs WHERE name = 'Pharmacie Valmont Nord'),
    'read', 'patients');                                   -- true
 
SELECT morbac.is_allowed('11111111-1111-4111-8111-111111111111',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Sud'),
    'read', 'patients');                                   -- false

Create a brand-new department and check again, with no rule writes:

INSERT INTO morbac.orgs (name, parent_id)
SELECT 'Cardiologie Valmont Nord', (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord')
ON CONFLICT (name) DO NOTHING;
 
SELECT morbac.is_allowed('11111111-1111-4111-8111-111111111111',
    (SELECT id FROM morbac.orgs WHERE name = 'Cardiologie Valmont Nord'),
    'read', 'patients');                                   -- true

Scope is evaluated at decision time against the live tree, so growth is free. The role check also follows the rule's org: Camille holds physician at Clinique Valmont Nord, which is exactly where the subtree rule looks.

Variations

Group-wide policy: write the rule at Groupe Hospitalier Valmont with scope subtree against a group-level role.
Departments only: scope descendants excludes the hospital itself.
Escalation upward: scope lineage lets a department role act on its own org and every ancestor.