Health administration

CREATE ONTOLOGY health_admin;

-- The root. One approved instrument at one version.
CREATE ENTITY health_admin.ControlledDocument (
  document_ref     TEXT NOT NULL EXTRACT 'the controlled document identifier printed in the header or footer, not the filename',
  version          TEXT NOT NULL EXTRACT 'version or revision number of this approved instrument',
  title            TEXT NOT NULL,
  instrument_type  TEXT NOT NULL CHECK (instrument_type IN (
                     'policy','procedure','clinical_guideline','work_instruction',
                     'care_pathway','credentialling_instrument','framework','form')),
  -- The applicability tier. A statewide instrument and a ward-level work
  -- instruction can carry the same document_ref prefix; this is what
  -- separates them when two instruments conflict.
  scope_level      TEXT CHECK (scope_level IN ('organisation','division','service','unit')),
  status           TEXT NOT NULL CHECK (status IN ('draft','current','under_review','superseded','rescinded')),
  effective_from   DATE,
  review_due       DATE EXTRACT 'the scheduled next review or expiry date, not the date of the last revision',
  approved_on      DATE,
  -- Written as it appears on the cover page; the edge resolves it to a node.
  approving_body   TEXT EXTRACT 'name of the committee or executive forum that approved this version',
  custodian_role   TEXT EXTRACT 'the position title of the document owner or custodian, not a person name',
  KEY (document_ref, version)
) ROOT;

-- Committees and executive forums, deduplicated across naming drift.
CREATE ENTITY health_admin.GoverningBody (
  body_name    TEXT KEY NOT NULL,
  body_type    TEXT CHECK (body_type IN ('board','executive','standing_committee','working_group','delegated_officer')),
  meets_every  TEXT EXTRACT 'stated meeting cadence, for example monthly or quarterly'
);

-- Positions, never people. An office outlives its incumbent.
CREATE ENTITY health_admin.AccountableRole (
  role_title    TEXT KEY NOT NULL EXTRACT 'position title only; if a personal name appears, capture the title it holds',
  role_class    TEXT CHECK (role_class IN ('executive','medical','nursing','allied_health','corporate','clinical_lead'))
);

-- An obligation stated in an instrument. Many per document, or none.
CREATE ENTITY health_admin.Directive (
  directive_text     TEXT NOT NULL EXTRACT 'the single obligation as stated, one row per distinct requirement',
  clause_ref         TEXT EXTRACT 'section or clause number carrying this obligation',
  obligation_force   TEXT NOT NULL CHECK (obligation_force IN ('must','should','may','must_not')),
  -- Who has to do it, as written. Resolved to AccountableRole by edge.
  responsible_role   TEXT EXTRACT 'position title carrying this obligation',
  action_category    TEXT CHECK (action_category IN (
                       'notify','escalate','document','review','assess',
                       'authorise','report','train','audit','handover'))
) UNDER health_admin.ControlledDocument;

-- Split from Directive because most obligations carry no clock at all.
CREATE ENTITY health_admin.TimingRule (
  clock_start   TEXT NOT NULL EXTRACT 'the triggering event the clock runs from, for example admission, request, incident, decision',
  limit_value   FLOAT,
  limit_unit    TEXT CHECK (limit_unit IN ('minutes','hours','days','weeks','months','years')),
  boundary      TEXT CHECK (boundary IN ('within','no_later_than','at_least','before','after')),
  recurrence    TEXT EXTRACT 'set only when the requirement repeats, for example annually'
) UNDER health_admin.Directive;

-- A citation as written in the instrument, before resolution succeeds or fails.
CREATE ENTITY health_admin.CrossReference (
  cited_ref     TEXT EXTRACT 'document identifier of the cited instrument, if one is printed',
  cited_title   TEXT NOT NULL EXTRACT 'the title of the cited instrument as written in this document',
  citation_kind TEXT NOT NULL CHECK (citation_kind IN (
                  'supersedes','read_with','implements','replaced_by','external_standard'))
) UNDER health_admin.ControlledDocument;

-- Accreditation criteria the instrument claims to address.
CREATE ENTITY health_admin.AccreditationCriterion (
  standard_code   TEXT NOT NULL EXTRACT 'the accreditation standard identifier, for example the standard number',
  criterion_code  TEXT NOT NULL,
  criterion_label TEXT,
  KEY (standard_code, criterion_code)
);

-- Edges. Note the resolution choice on each.

-- Machine-formatted refs on both sides: normalised equality is correct.
CREATE EDGE health_admin.supersedes
  ON health_admin.ControlledDocument SUPERSEDES health_admin.ControlledDocument
  LINK BY document_ref RESOLVE EXACT;

-- Committee names drift across versions and custodians. FUZZY, pool-narrowed.
CREATE EDGE health_admin.approved_by
  ON health_admin.ControlledDocument APPROVED_BY health_admin.GoverningBody
  LINK BY approving_body RESOLVE FUZZY WITHIN health_admin.GoverningBody;

CREATE EDGE health_admin.owned_by
  ON health_admin.ControlledDocument CUSTODIAN_IS health_admin.AccountableRole
  LINK BY custodian_role RESOLVE FUZZY WITHIN health_admin.AccountableRole;

-- Title drift over time is the norm here, so FUZZY again.
CREATE EDGE health_admin.assigned_to
  ON health_admin.Directive ASSIGNED_TO health_admin.AccountableRole
  LINK BY responsible_role RESOLVE FUZZY WITHIN health_admin.AccountableRole;

-- FUZZY on purpose: the near-misses are the dangling references we want.
CREATE EDGE health_admin.cites
  ON health_admin.CrossReference CITES health_admin.ControlledDocument
  LINK BY cited_title RESOLVE FUZZY WITHIN health_admin.ControlledDocument;

CREATE EDGE health_admin.addresses
  ON health_admin.ControlledDocument ADDRESSES health_admin.AccreditationCriterion
  LINK BY document_ref RESOLVE EXACT;

-- Internal edges: structural, resolved by position within the document.
CREATE EDGE health_admin.states
  ON health_admin.ControlledDocument STATES health_admin.Directive;

CREATE EDGE health_admin.timed_by
  ON health_admin.Directive TIMED_BY health_admin.TimingRule;

CREATE CORPUS governance_library
  ON sharepoint.clinical_docs.Controlled.Library,
     sharepoint.clinical_docs.Controlled.Archive
  USING ONTOLOGY health_admin
  PARTITION BY FOLDER LEVEL 2;   -- folder becomes the division dimension

BIND CORPUS governance_library TO ONTOLOGY health_admin;

RUN BINDING governance_library.health_admin;

ALTER BINDING governance_library.health_admin
  SET HINT ON extract_internals WHERE root = ControlledDocument AS
  'Governance metadata sits in the cover-page control table or the document footer.
   Prefer the footer document_ref over any reference typed in the body text; body
   references usually name a different instrument. Treat the header "Review date"
   as review_due and "Approved" as approved_on; where only one date is printed and
   it is in the future, it is review_due. Emit one Directive per distinct
   obligation sentence, not one per paragraph, and keep the modal verb: it drives
   obligation_force. Emit a TimingRule only where an actual duration is stated;
   phrases like "promptly" or "as soon as practicable" are not timing rules and
   must not be given a limit_value. Under CrossReference, capture citations from
   the Related Documents and Supersedes sections and from inline body references;
   set citation_kind from the section heading. For custodian_role and
   responsible_role, record the position title; if a personal name appears, record
   the title held rather than the name.';

RUN BINDING governance_library.health_admin
  EXTRACT (ControlledDocument.scope_level);

SELECT d.document_ref, d.version, d.title, d.review_due, r.role_title
FROM governance_library.ControlledDocument d
JOIN governance_library.AccountableRole r VIA d.owned_by
WHERE d.status = 'current'
  AND d.review_due < DATE '2026-07-01'
ORDER BY d.review_due ASC;

SELECT src.document_ref  AS citing_ref,
       src.title         AS citing_title,
       x.cited_title,
       tgt.document_ref  AS cited_ref,
       tgt.status
FROM governance_library.CrossReference x
JOIN governance_library.ControlledDocument src USING (_doc)
JOIN governance_library.ControlledDocument tgt VIA x.cites
WHERE src.status = 'current'
  AND tgt.status IN ('superseded','rescinded')
  AND x.citation_kind <> 'supersedes'
ORDER BY citing_ref;

SELECT g.body_name,
       g.body_type,
       COUNT(*) AS instruments_approved,
       MIN(d.review_due) AS oldest_review_due
FROM governance_library.ControlledDocument d
JOIN governance_library.GoverningBody g VIA d.approved_by
WHERE d.status = 'current'
  AND d.review_due < DATE '2026-07-01'
GROUP BY g.body_name, g.body_type
ORDER BY instruments_approved DESC;

SELECT d._folder AS division,
       COUNT(*) AS low_confidence_instruments,
       AVG(d._confidence) AS mean_confidence
FROM governance_library.ControlledDocument d
WHERE d._confidence < 0.6
  AND d.status = 'current'
GROUP BY d._folder
ORDER BY low_confidence_instruments DESC;

WITH timed AS (
  SELECT d.document_ref, d.version, d.title, d.scope_level, d.instrument_type,
         dir.action_category, dir.obligation_force, dir.clause_ref,
         t.clock_start, t.limit_value, t.limit_unit, t.boundary
  FROM governance_library.ControlledDocument d
  JOIN governance_library.Directive dir VIA d.states
  JOIN governance_library.TimingRule t VIA dir.timed_by
  WHERE d.status = 'current'
    AND dir.obligation_force IN ('must','must_not')
    AND t.limit_value IS NOT NULL
)
SELECT a.action_category,
       a.clock_start,
       a.document_ref AS instrument_a, a.clause_ref AS clause_a,
       a.limit_value  AS limit_a,      a.limit_unit  AS unit_a,
       b.document_ref AS instrument_b, b.clause_ref AS clause_b,
       b.limit_value  AS limit_b,      b.limit_unit  AS unit_b
FROM timed a
JOIN timed b ON a.action_category = b.action_category
            AND a.clock_start     = b.clock_start
            AND a.limit_unit      = b.limit_unit
            AND a.boundary        = b.boundary
WHERE a.document_ref < b.document_ref     -- one row per pair, never self-matched
  AND a.limit_value <> b.limit_value
ORDER BY a.action_category, a.clock_start;

SELECT c.standard_code, c.criterion_code, c.criterion_label,
       COUNT(*) AS supporting_instruments
FROM governance_library.AccreditationCriterion c
JOIN governance_library.ControlledDocument d VIA c.addresses
WHERE d.status = 'current'
GROUP BY c.standard_code, c.criterion_code, c.criterion_label
HAVING supporting_instruments > 0
   AND c.criterion_code NOT IN (
     SELECT c2.criterion_code
     FROM governance_library.AccreditationCriterion c2
     JOIN governance_library.ControlledDocument d2 VIA c2.addresses
     WHERE d2.status = 'current'
       AND d2.review_due >= DATE '2026-07-01'
   )
ORDER BY supporting_instruments DESC;

-- The review backlog, as one noun.
CREATE OR REPLACE VIEW overdue_register AS
SELECT d.document_ref, d.version, d.title, d.instrument_type, d.scope_level,
       d.review_due, d._folder AS division,
       r.role_title AS custodian, g.body_name AS approving_body
FROM governance_library.ControlledDocument d
LEFT JOIN governance_library.AccountableRole r VIA d.owned_by
LEFT JOIN governance_library.GoverningBody   g VIA d.approved_by
WHERE d.status = 'current'
  AND d.review_due < DATE '2026-07-01';

-- Every stated obligation with its clock flattened alongside it. LEFT JOIN is
-- the point: obligations without a timing rule must still appear.
CREATE OR REPLACE VIEW obligation_register AS
SELECT d.document_ref, d.version, d.instrument_type, d.scope_level,
       dir.clause_ref, dir.obligation_force, dir.action_category,
       dir.directive_text, r.role_title AS responsible_role,
       t.clock_start, t.boundary, t.limit_value, t.limit_unit
FROM governance_library.ControlledDocument d
JOIN governance_library.Directive dir VIA d.states
LEFT JOIN governance_library.TimingRule    t VIA dir.timed_by
LEFT JOIN governance_library.AccountableRole r VIA dir.assigned_to
WHERE d.status = 'current';

-- Citations that resolved to something no longer in force.
CREATE OR REPLACE VIEW broken_citations AS
SELECT src.document_ref AS citing_ref, src.title AS citing_title,
       src._folder      AS division,
       x.citation_kind, x.cited_title,
       tgt.document_ref AS cited_ref, tgt.status AS cited_status
FROM governance_library.CrossReference x
JOIN governance_library.ControlledDocument src USING (_doc)
JOIN governance_library.ControlledDocument tgt VIA x.cites
WHERE src.status = 'current'
  AND tgt.status IN ('superseded','rescinded')
  AND x.citation_kind <> 'supersedes';