Issues and Incidents

Views for issues, incident management, workflows, custom fields, SAP integration, and root cause analysis links.

These views document the issue and incident handling workflows in Maecos. Issues represent deviations, safety events, quality concerns, or improvement opportunities. They follow configurable workflows, can carry custom fields, and can be linked to SAP PM notifications and root cause analyses.

---

reporting.issues

The main view for issues. Returns all non-deleted issues with their current status, category, assignee, and custom fields.

Field	Type	Description
issue_id	uuid	Unique identifier of the issue
issue_number	integer	Human-readable issue number
title	text	Issue title
description	text	Detailed description of the issue
equipment_id	uuid	Equipment linked to this issue (if any)
priority	text	Priority level
current_status	text	Current workflow status
category_name	text	Issue category
subcategory_name	text	Issue subcategory
due_date	timestamp	Due date
created_at	timestamp	When the issue was created
created_by	uuid	User who created the issue
assignee_type	text	'user' or 'workstation'
assignee_id	uuid	ID of the assigned user or workstation
updated_at	timestamp	Last modification timestamp
updated_by	uuid	User who last modified the issue
resolution	text	Resolution description (when resolved)
custom_fields	jsonb	Custom field key-value pairs
root_cause_analysis_required	boolean	Whether the category requires a why-why analysis

The custom_fields column is jsonb. While you can query it directly with PostgreSQL JSON operators, the issues_fields view provides a much easier, normalized format for working with custom field values in SQL.

Joins: Join with issues_fields to access custom fields in a tabular format. Join with issues_transitions, issues_latest_transitions for workflow history. Join with equipment on equipment_id. Join with issues_why_why_analyses for root cause links.

---

reporting.issues_assignees

Resolves issue assignments to a normalized format.

Field	Type	Description
issue_id	uuid	The issue being assigned
assignee_type	text	'user' or 'workstation'
assignee_id	uuid	ID of the assigned user or workstation

---

reporting.issues_transitions

Full history of status changes for issues.

Field	Type	Description
issue_id	uuid	The issue that transitioned
from	timestamp	When this status period started
until	timestamp	When this status period ended (null if current)
transition	text	The transition category
transitioned_by	uuid	User who triggered the transition

---

reporting.issues_latest_transitions

The most recent (current) status transition for each issue. Subset of issues_transitions where until IS NULL.

Field	Type	Description
issue_id	uuid	The issue
from	timestamp	When the current status started
until	timestamp	Always null
transition	text	Current transition category
transitioned_by	uuid	User who triggered this transition

---

reporting.issues_fields

Unpacks custom_fields from the issues view into a normalized, queryable format. Each row represents one custom field value for one issue.

Field	Type	Description
issue_id	uuid	The issue
key	text	Custom field name
value	text	Custom field value (as text)
type	text	Field type: 'text', 'dateTime', 'number', or select-type
updated_at	timestamp	When the issue was last updated

Multi-select custom fields produce multiple rows for the same issue_id + key combination — one per selected value.

---

reporting.issues_why_why_analyses

Links issues to their root cause analyses.

Field	Type	Description
issue_id	uuid	The issue
analysis_id	uuid	The why-why analysis
updated_at	timestamp	Last update timestamp
is_deleted	boolean	Whether the link has been removed

Joins: Join with why_why_analyses on analysis_id for the analysis details.

---

reporting.issue_sap_notifications

Links issues to SAP PM notification numbers.

This view is only relevant for customers with SAP PM integration enabled. If your site does not use SAP, this view will be empty.

Field	Type	Description
defect_id	uuid	The issue ID (named defect_id for legacy reasons)
notification_number	text	The SAP PM notification number
updated_at	timestamp	When the notification was created

Joins: Join with issues on defect_id = issue_id.

---

Example queries

Issues by category with counts and average resolution time

SELECT
    category_name,
    COUNT(*) AS total_issues,
    COUNT(*) FILTER (WHERE current_status = 'closed') AS closed,
    AVG(
        EXTRACT(EPOCH FROM (updated_at - created_at)) / 3600
    ) FILTER (WHERE current_status = 'closed') AS avg_hours_to_close
FROM reporting.issues
GROUP BY category_name
ORDER BY total_issues DESC;

Issues with a specific custom field value

SELECT
    i.issue_number,
    i.title,
    f.value AS root_cause_type
FROM reporting.issues i
JOIN reporting.issues_fields f
    ON i.issue_id = f.issue_id
    AND f.key = 'Root Cause Type'
WHERE f.value = 'Equipment failure';