HR surveys

CREATE ONTOLOGY hr;

-- The delivered report. One document, one row: the anchor for everything else.
CREATE ENTITY hr.SurveyReport (
  programme_code    TEXT NOT NULL
    EXTRACT 'short code naming the listening programme, e.g. ENG-ANNUAL or PULSE-Q',
  wave_label        TEXT NOT NULL
    EXTRACT 'the wave or cycle this report covers, e.g. 2026-W1 or Autumn 2025',
  population_scope  TEXT NOT NULL
    EXTRACT 'the audience this copy of the report covers: whole organisation, a division, a site',
  survey_kind       TEXT NOT NULL CHECK (survey_kind IN
    ('engagement','pulse','onboarding','exit_aggregate','culture_audit','three_sixty_aggregate')),
  field_start       DATE,
  field_end         DATE,
  invited_count     INTEGER,
  -- The denominator. NOT NULL because every threshold guard downstream needs it.
  response_count    INTEGER NOT NULL,
  response_rate_pct FLOAT,
  vendor_platform   TEXT
    EXTRACT 'the survey platform that produced the report, if named on the cover or footer',
  benchmark_source  TEXT
    EXTRACT 'the external comparison set cited, e.g. a named industry norm or global norm',
  is_confidential   BOOLEAN
    EXTRACT 'true if the report carries a confidentiality or restricted-circulation marking',
  KEY (programme_code, wave_label, population_scope)
) ROOT SINGULAR PER DOC;

-- The stable identity of a question, independent of how it was worded this wave.
-- item_slug is analyst-assigned and survives reword and vendor migration.
CREATE ENTITY hr.SurveyItem (
  construct_code    TEXT NOT NULL
    EXTRACT 'the driver or construct the item loads onto, e.g. MGR, GROWTH, RECOG, WLB',
  item_slug         TEXT NOT NULL
    EXTRACT 'the stable short identifier for this item across waves, not the vendor question number',
  question_text     TEXT NOT NULL
    EXTRACT 'the exact wording printed in this report; do not normalise or tidy it',
  wording_revision  INTEGER
    EXTRACT 'revision number if the report flags the item as reworded or newly phrased',
  scale_type        TEXT CHECK (scale_type IN
    ('agree_5','agree_7','freq_5','enps_11','binary','open_text')),
  is_benchmarked    BOOLEAN,
  KEY (construct_code, item_slug)
) UNDER hr.SurveyReport;

-- One score for one item for one cut of the population.
-- respondent_base is NOT NULL: no score exists in this ontology without its N.
CREATE ENTITY hr.ItemScore (
  item_slug           TEXT NOT NULL,
  cut_label           TEXT NOT NULL
    EXTRACT 'the population cut this score applies to: overall, a division, a tenure band, a level',
  cut_dimension       TEXT CHECK (cut_dimension IN
    ('overall','business_unit','location','tenure_band','level','function','manager_span')),
  favourable_pct      FLOAT
    EXTRACT 'percentage selecting the top two scale points, sometimes labelled favourable or agreement',
  unfavourable_pct    FLOAT,
  mean_score          FLOAT,
  respondent_base     INTEGER NOT NULL
    EXTRACT 'number of respondents behind this figure, often shown as n= beside the score',
  benchmark_delta     FLOAT
    EXTRACT 'difference against the external benchmark, signed; null if no benchmark shown',
  prior_wave_delta    FLOAT
    EXTRACT 'change against the previous wave as printed, signed; null if not shown or not comparable',
  -- Distinguishes "vendor withheld this cut" from "this cut was never asked".
  suppression_applied BOOLEAN
    EXTRACT 'true if the report shows this cut as withheld, masked or below reporting threshold',
  KEY (item_slug, cut_label)
) UNDER hr.SurveyReport;

-- Rolled-up driver scores. Separate from ItemScore because reports publish
-- driver-level figures that are not simply the mean of their items.
CREATE ENTITY hr.DriverScore (
  construct_code   TEXT NOT NULL,
  construct_label  TEXT
    EXTRACT 'the human-readable driver name as printed, e.g. Manager Effectiveness',
  cut_label        TEXT NOT NULL,
  favourable_pct   FLOAT,
  respondent_base  INTEGER NOT NULL,
  benchmark_delta  FLOAT,
  is_key_driver    BOOLEAN
    EXTRACT 'true if the report flags this construct as a key or priority driver from its impact analysis',
  impact_rank      INTEGER
    EXTRACT 'rank in the driver impact or key driver analysis, 1 being highest impact',
  KEY (construct_code, cut_label)
) UNDER hr.SurveyReport;

-- Free text, aggregated into themes. The illustrative quote is the single
-- highest re-identification risk in the corpus, so it lives on exactly one
-- entity, on one nullable column, and can be granted and redacted separately.
CREATE ENTITY hr.CommentTheme (
  theme_label        TEXT NOT NULL
    EXTRACT 'the theme name as reported in the open-text analysis section',
  theme_category     TEXT CHECK (theme_category IN
    ('compensation','career_growth','manager','workload','leadership','culture',
     'flexibility','tools_systems','role_clarity','recognition','wellbeing','other')),
  cut_label          TEXT NOT NULL,
  comment_count      INTEGER NOT NULL
    EXTRACT 'number of comments coded to this theme; the threshold guard for free text',
  sentiment          TEXT CHECK (sentiment IN ('positive','mixed','negative')),
  share_of_comments  FLOAT,
  explains_construct TEXT
    EXTRACT 'the driver code this theme is said to explain, if the report links them',
  illustrative_quote TEXT
    EXTRACT 'a verbatim quote if the report prints one; leave null rather than paraphrasing',
  KEY (theme_label, cut_label)
) UNDER hr.SurveyReport;

-- One leaver conversation. Pseudonymous by construction: leaver_reference is a
-- case identifier, and there is deliberately no name, email or manager column.
CREATE ENTITY hr.ExitInterview (
  leaver_reference  TEXT KEY
    EXTRACT 'the case or reference number for this exit conversation; never a person name',
  exit_date         DATE,
  business_unit     TEXT,
  tenure_months     INTEGER,
  level_band        TEXT,
  exit_type         TEXT CHECK (exit_type IN
    ('voluntary','involuntary','end_of_contract','retirement','redundancy')),
  primary_reason    TEXT CHECK (primary_reason IN
    ('compensation','career_growth','manager','workload','culture','relocation',
     'flexibility','role_fit','external_offer','personal','other'))
    EXTRACT 'the single reason the leaver gave as decisive, not the full list of contributing factors',
  would_recommend   BOOLEAN
    EXTRACT 'whether the leaver said they would recommend the organisation as a place to work',
  would_return      BOOLEAN,
  interviewer_role  TEXT
    EXTRACT 'the role of whoever conducted the interview, e.g. HR business partner; not their name'
) ROOT SINGULAR PER DOC;

-- Contributing factors, separate from primary_reason. A leaver relocating may
-- still tell you the workload was unsustainable, and that is the useful part.
CREATE ENTITY hr.ExitFactor (
  factor_category  TEXT NOT NULL CHECK (factor_category IN
    ('compensation','career_growth','manager','workload','leadership','culture',
     'flexibility','tools_systems','role_clarity','recognition','wellbeing','other')),
  factor_note      TEXT,
  severity         TEXT CHECK (severity IN ('mentioned','emphasised','decisive')),
  KEY (factor_category, severity)
) UNDER hr.ExitInterview;

-- The organisational unit a report or exit relates to. An entity rather than a
-- string so the four spellings of one team resolve to one node.
CREATE ENTITY hr.OrgUnit (
  unit_name       TEXT KEY
    EXTRACT 'the organisational unit name as written; do not expand abbreviations',
  unit_type       TEXT CHECK (unit_type IN
    ('division','department','team','site','function','region')),
  headcount       INTEGER
    EXTRACT 'headcount for the unit if stated on the report; this is the eligible population, not the response count',
  parent_unit     TEXT
) ROOT;

-- External edge: a report's score labels are truncated and re-punctuated
-- versions of the item wording, so this must tolerate drift.
CREATE EDGE hr.reports_on_item
  ON hr.SurveyReport COVERS hr.SurveyItem
  LINK BY item_slug RESOLVE FUZZY WITHIN hr.SurveyItem;

-- Unit names in HR documents are a swamp; fuzzy is the only honest choice.
CREATE EDGE hr.belongs_to_unit
  ON hr.SurveyReport SCOPED_TO hr.OrgUnit
  LINK BY population_scope RESOLVE FUZZY WITHIN hr.OrgUnit;

CREATE EDGE hr.exit_from_unit
  ON hr.ExitInterview LEFT_FROM hr.OrgUnit
  LINK BY business_unit RESOLVE FUZZY WITHIN hr.OrgUnit;

-- EXACT: construct codes are assigned, not typed. A fuzzy match here would
-- merge two genuinely distinct drivers, which is worse than no match at all.
CREATE EDGE hr.explains_driver
  ON hr.CommentTheme EXPLAINS hr.DriverScore
  LINK BY explains_construct RESOLVE EXACT;

CREATE EDGE hr.scores_item
  ON hr.ItemScore MEASURES hr.SurveyItem
  LINK BY item_slug RESOLVE EXACT;

-- Internal edges: structural containment within one document.
CREATE EDGE hr.report_has_driver
  ON hr.SurveyReport CONTAINS hr.DriverScore;

CREATE EDGE hr.exit_has_factor
  ON hr.ExitInterview CITES hr.ExitFactor;

CREATE CORPUS listening
  ON sharepoint.people_analytics.'Engagement Reports',
     sharepoint.people_analytics.'Pulse Waves',
     sharepoint.people_analytics.'Exit Conversations'
  USING ONTOLOGY hr
  PARTITION BY FOLDER LEVEL 2;      -- level 2 is the division folder

BIND CORPUS listening TO ONTOLOGY hr;

RUN BINDING listening.hr;

ALTER BINDING listening.hr
  SET HINT ON extract_internals WHERE root = SurveyReport AS
  'Reports in this corpus put driver scores on a summary page as a horizontal
   bar chart with the favourable percentage printed at the bar end and the
   benchmark delta in a small signed box beside it: capture both, preserving the
   sign. Item-level scores appear in a heat-map grid later in the deck, one row
   per item and one column per cut; the n= figure sits in the column header or in
   a footnote, and it must be captured for every score, never inferred from the
   overall response count. Where a cell is shown as a dash, a grey block or the
   text below threshold, set suppression_applied true and leave the percentage
   null rather than reading zero. item_slug is the short mnemonic in the leftmost
   column or the appendix item map; if the deck shows only a vendor question
   number, use that verbatim as the slug rather than inventing one. Copy
   question_text exactly as printed, including any asterisk or note marking the
   item as reworded this wave, and set wording_revision when such a marker is
   present. Open-text themes come from the comments section: capture the theme
   name, its comment count and its sentiment, and extract illustrative_quote only
   where a quote is printed verbatim in quotation marks. Never construct a quote
   from a summary sentence.';

RUN BINDING listening.hr
  EXTRACT (ItemScore.suppression_applied, CommentTheme.explains_construct);

-- People analytics owns the corpus and can see everything in it.
GRANT SELECT ON CORPUS listening TO GROUP people_analytics;

-- HR business partners hold the same corpus grant. What differs is the
-- per-document ACL on the division folders, which intersects with it: the
-- grant opens the corpus, the ACL decides which division's reports are inside
-- it for this caller. Nobody writes a WHERE clause to enforce that.
GRANT SELECT ON CORPUS listening TO GROUP hr_business_partners;

-- The executive group is granted the derived corpus of rolled-up figures, not
-- the source corpus. Verbatim comments and unit-level cuts are not in it, so
-- there is nothing to accidentally expose in a screenshot of a board pack.
GRANT SELECT ON CORPUS listening_rollup TO GROUP executive_committee;

-- Exit conversations are a narrower circle again, by folder ACL within the
-- same corpus grant.
GRANT SELECT ON CORPUS listening TO USER employee_relations_lead@examplecorp.test;

GRANT USE ON PIPELINE comment_search TO GROUP people_analytics;

SELECT u.unit_name,
       d.construct_label,
       d.favourable_pct,
       d.benchmark_delta,
       d.respondent_base
FROM listening.DriverScore d
JOIN listening.SurveyReport r VIA d.report_has_driver
JOIN listening.OrgUnit u VIA r.belongs_to_unit
WHERE d.respondent_base >= 5          -- minimum reporting threshold, never optional
  AND d.suppression_applied IS NOT NULL
  AND d.benchmark_delta < -5
  AND r.survey_kind = 'engagement'
ORDER BY d.benchmark_delta ASC;

SELECT r.population_scope,
       d.construct_label,
       AVG(d.favourable_pct)   AS avg_favourable,
       SUM(d.respondent_base)  AS total_base,
       COUNT(*)                AS cuts_reported
FROM listening.SurveyReport r
JOIN listening.DriverScore d VIA r.report_has_driver
WHERE d.respondent_base >= 5
  AND r.field_end >= DATE '2026-01-01'
GROUP BY r.population_scope, d.construct_label
HAVING cuts_reported >= 3            -- alias the aggregate; HAVING COUNT(*) does not parse
ORDER BY avg_favourable ASC
LIMIT 25;

SELECT s._doc,
       s._confidence,
       s._folder,
       s.item_slug,
       s.cut_label,
       s.favourable_pct,
       s.respondent_base
FROM listening.ItemScore s
WHERE s._confidence < 0.75
  AND s.respondent_base >= 5
ORDER BY s._confidence ASC
LIMIT 40;

WITH series AS (
  SELECT i.construct_code,
         i.item_slug,
         r.wave_label,
         i.question_text,
         i.wording_revision,
         s.favourable_pct,
         s.respondent_base
  FROM listening.ItemScore s
  JOIN listening.SurveyItem i VIA s.scores_item
  JOIN listening.SurveyReport r USING (_doc)
  WHERE s.cut_label = 'overall'
    AND s.respondent_base >= 5
)
SELECT construct_code,
       item_slug,
       COUNT(*)                     AS waves_observed,
       COUNT(DISTINCT question_text) AS distinct_wordings,
       MAX(wording_revision)        AS latest_revision,
       MIN(favourable_pct)          AS lowest,
       MAX(favourable_pct)          AS highest,
       MAX(favourable_pct) - MIN(favourable_pct) AS swing
FROM series
GROUP BY construct_code, item_slug
HAVING waves_observed >= 3
ORDER BY swing DESC;

SELECT u.unit_name,
       d.construct_label,
       d.favourable_pct,
       t.theme_label,
       t.theme_category,
       t.sentiment,
       t.comment_count
FROM listening.CommentTheme t
JOIN listening.DriverScore d VIA t.explains_driver
JOIN listening.SurveyReport r USING (_doc)
JOIN listening.OrgUnit u VIA r.belongs_to_unit
WHERE d.respondent_base >= 5
  AND t.comment_count >= 5          -- free text needs its own threshold, and a stricter one
  AND t.sentiment = 'negative'
  AND d.benchmark_delta < 0
ORDER BY t.comment_count DESC;

SELECT f.factor_category,
       COUNT(DISTINCT e.leaver_reference) AS leavers_citing,
       AVG(e.tenure_months)               AS avg_tenure_months
FROM listening.ExitInterview e
JOIN listening.ExitFactor f VIA e.exit_has_factor
WHERE e.exit_type = 'voluntary'
  AND e.exit_date >= DATE '2026-01-01'
  AND f.severity IN ('emphasised','decisive')
GROUP BY f.factor_category
ORDER BY leavers_citing DESC;

SELECT r.programme_code, r.wave_label, r.population_scope, r.response_count
FROM listening.SurveyReport r
WHERE r MATCHES ('psychological safety speaking up without fear of retribution'
                 USING comment_search TOP 30)
  AND r.response_count >= 5;

-- The only entry point analysts should use for driver scores. The minimum
-- reporting threshold is inside the view, so a downstream query cannot omit it
-- by writing SELECT * and forgetting why the n column was there.
CREATE OR REPLACE VIEW reportable_scores AS
SELECT r.programme_code,
       r.wave_label,
       r.population_scope,
       r.survey_kind,
       r.field_end,
       u.unit_name,
       d.construct_code,
       d.construct_label,
       d.cut_label,
       d.favourable_pct,
       d.benchmark_delta,
       d.respondent_base,
       d.is_key_driver,
       d.impact_rank
FROM listening.DriverScore d
JOIN listening.SurveyReport r VIA d.report_has_driver
LEFT JOIN listening.OrgUnit u VIA r.belongs_to_unit
WHERE d.respondent_base >= 5;

-- Free text at a stricter threshold and with the verbatim column deliberately
-- absent. Themes are safe to circulate; the quote that illustrates them is not,
-- so this view does not offer it and no consumer has to remember to drop it.
CREATE OR REPLACE VIEW safe_themes AS
SELECT r.programme_code,
       r.wave_label,
       r.population_scope,
       t.theme_label,
       t.theme_category,
       t.sentiment,
       t.comment_count,
       t.share_of_comments,
       t.explains_construct
FROM listening.CommentTheme t
JOIN listening.SurveyReport r USING (_doc)
WHERE t.comment_count >= 5
  AND r.response_count >= 20;      -- a theme from a small survey is identifying even at n=5

-- One row per item per wave, wording drift flagged. This is the trend table:
-- read favourable_pct as a series only where wording_stable is true.
CREATE OR REPLACE VIEW item_trend AS
SELECT i.construct_code,
       i.item_slug,
       r.wave_label,
       r.field_end,
       i.question_text,
       i.wording_revision,
       s.cut_label,
       s.favourable_pct,
       s.prior_wave_delta,
       s.respondent_base
FROM listening.ItemScore s
JOIN listening.SurveyItem i VIA s.scores_item
JOIN listening.SurveyReport r USING (_doc)
WHERE s.respondent_base >= 5
  AND s.suppression_applied IS NOT NULL;