Root Cause Analysis

Views for why-why (root cause) analyses and their individual why entries, linked to issues.

These views cover the why-why analysis system in Maecos. It is a structured root cause analysis tool used to investigate issues. Each analysis contains a tree of "why" entries, where each entry can be marked as a root cause and linked to corrective actions.

---

reporting.why_why_analyses

Root cause analysis records.

Field	Type	Description
analysis_id	uuid	Unique identifier
problem_statement	text	The problem being analyzed
conclusion	text	Analysis conclusion (HTML content)
status	text	Current status of the analysis
updated_at	timestamp	Last modification timestamp
is_deleted	boolean	Whether the analysis has been deleted

Joins: Join with issues_why_why_analyses on analysis_id to find the linked issue. Join with why_why_analyses_whys on analysis_id for the individual why entries.

---

reporting.why_why_analyses_whys

Individual "why" entries within a why-why analysis. These form a tree structure where each why can have children (deeper root causes).

Field	Type	Description
analysis_id	uuid	Parent analysis
why_id	uuid	Unique identifier of this why entry
parent_id	uuid	Parent why entry (null for top-level whys)
importance	text	Importance rating
why_content	text	The "why" response text
evidence	text	Supporting evidence (HTML content)
is_root_cause	boolean	Whether this entry has been identified as a root cause
updated_at	timestamp	Last modification timestamp
is_deleted	boolean	Whether the entry has been deleted

Joins: Self-join on parent_id = why_id for tree traversal. Join with linked_actions where parent_type = 'why' and parent_id = why_id to find corrective actions created from root causes.

---

Example queries

Issues with root cause analyses and their status

SELECT
    i.issue_number,
    i.title AS issue_title,
    ww.problem_statement,
    ww.status AS analysis_status,
    COUNT(w.why_id) AS total_whys,
    COUNT(w.why_id) FILTER (WHERE w.is_root_cause = true) AS root_causes_found
FROM reporting.issues_why_why_analyses iww
JOIN reporting.issues i USING (issue_id)
JOIN reporting.why_why_analyses ww USING (analysis_id)
LEFT JOIN reporting.why_why_analyses_whys w
    ON ww.analysis_id = w.analysis_id
    AND w.is_deleted = false
WHERE iww.is_deleted = false
GROUP BY i.issue_number, i.title, ww.problem_statement, ww.status
ORDER BY i.issue_number DESC;

Root causes with corrective actions

SELECT
    ww.problem_statement,
    w.why_content AS root_cause,
    a.action_number,
    a.title AS corrective_action,
    a.current_status
FROM reporting.why_why_analyses_whys w
JOIN reporting.why_why_analyses ww USING (analysis_id)
LEFT JOIN reporting.linked_actions la
    ON la.parent_type = 'why'
    AND la.parent_id = w.why_id
LEFT JOIN reporting.actions a USING (action_id)
WHERE w.is_root_cause = true
    AND w.is_deleted = false
ORDER BY ww.analysis_id, w.why_id;