Delegation

Situation. Dr. Camille Rousseau goes on leave for a week. Her colleague Julien Lefevre, normally a nurse, must hold her physician role at Clinique Valmont Nord for exactly that week, then lose it automatically.

Assumes the shared Valmont setup.

Delegate

INSERT INTO morbac.delegations
    (delegator_id, delegatee_id, role_id, org_id, valid_from, valid_until)
SELECT '11111111-1111-4111-8111-111111111111',   -- Camille Rousseau
       '22222222-2222-4222-8222-222222222222',   -- Julien Lefevre
       r.id, r.org_id,
       now(), now() + interval '7 days'
FROM morbac.roles r
JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Clinique Valmont Nord' AND r.name = 'physician';

Nothing else changes: no rule is copied, no role is assigned. Julien simply appears to hold physician while the delegation is live.

SELECT r.name, cr.source
FROM morbac.get_comprehensive_roles(
    '22222222-2222-4222-8222-222222222222',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord')) cr
JOIN morbac.roles r ON r.id = cr.role_id;
-- nurse  | direct
-- physician  | delegation

Guarantees

• Time-bounded. valid_from and valid_until are enforced at decision time; expiry needs no job and cannot be forgotten.
• Backed by the delegator. The delegation only counts while Camille still holds the role herself. Revoke her physician role and Julien's delegated copy evaporates in the same instant.
• Not transitive. Julien cannot delegate onward what he only holds by delegation: the engine checks user_roles for the delegator, not the effective set.
• Subject to negative assignments. A row in negative_role_assignments for Julien beats the delegation.
• Self-delegation is rejected by a check constraint, as is a window that ends before it starts.

Early revocation

UPDATE morbac.delegations SET revoked = TRUE
WHERE delegatee_id = '22222222-2222-4222-8222-222222222222'
  AND revoked = FALSE;

The row is kept for audit; revoked takes effect immediately, and the decision cache is flushed by trigger.

Reviewing live delegations

SELECT d.delegator_id, d.delegatee_id, r.name AS role, o.name AS org, d.valid_until
FROM morbac.delegations d
JOIN morbac.roles r ON r.id = d.role_id
JOIN morbac.orgs  o ON o.id = d.org_id
WHERE NOT d.revoked AND now() BETWEEN d.valid_from AND d.valid_until
ORDER BY d.valid_until;

Delegations involving system principals are rejected by trigger in both directions.