Actions and Improvements

Views for action items, assignments, workflow transitions, and links to parent objects like issues or other actions.

These views cover improvement actions within Maecos. Actions represent tasks created to address issues, drive improvements, or follow up on observations. They can be assigned to users or workstations, and their lifecycle is tracked through status transitions.

Actions are often linked to parent objects — an issue, another action, or a why-why analysis entry — via the linked_actions view.

---

reporting.actions

The main view for action items. Returns all non-deleted actions with their current status and assignee.

Field	Type	Description
action_id	uuid	Unique identifier of the action
action_number	integer	Human-readable action number
title	text	Title of the action
description	text	Detailed description
priority	text	Priority level
current_status	text	Current workflow status
due_date	timestamp	Due date for the action
created_at	timestamp	When the action was created
created_by	uuid	User who created the action
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 action

Joins: Use action_id to join with action_labels, actions_transitions, actions_latest_transitions, and linked_actions. Join assignee_id with users.user_id or workstations.workstation_id depending on assignee_type. Join created_by / updated_by with users.user_id.

This view already includes the assignee via a left join with actions_assignees. You do not need to join actions_assignees separately unless you need actions without any assignee excluded.

---

reporting.actions_assignees

Resolves action assignments to a normalized format. Each row represents one action with an assigned user or workstation.

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

Joins: Join with users on assignee_id = user_id when assignee_type = 'user', or with workstations on assignee_id = workstation_id when assignee_type = 'workstation'.

---

reporting.actions_transitions

Full history of status changes for actions. Each row represents one transition from one status to another.

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

Joins: Join with actions on action_id. Join transitioned_by with users.user_id.

---

reporting.actions_latest_transitions

The most recent (current) status transition for each action. This is the subset of actions_transitions where until IS NULL.

Field	Type	Description
action_id	uuid	The action
from	timestamp	When the current status started
until	timestamp	Always null (this is the current state)
transition	text	The current transition or status
transitioned_by	uuid	User who triggered this transition

---

reporting.action_labels

Labels attached to actions, used for categorization and filtering.

Field	Type	Description
action_id	uuid	The action being labeled
label_category	text	Category of the label (e.g., department, area)
label_value	text	The label value

Joins: Join with actions on action_id.

---

reporting.linked_actions

Maps actions to their parent objects. An action can be linked to an issue, another action, or a why-why analysis entry.

Field	Type	Description
parent_type	text	Type of the parent: 'issue', 'action', or 'why'
parent_id	uuid	ID of the parent object
action_id	uuid	The linked action
linked_at	timestamp	When the link was created
linked_by	uuid	User who created the link

Joins: Join with actions on action_id. When parent_type = 'issue', join parent_id with issues.issue_id. When parent_type = 'action', join parent_id with actions.action_id.

---

Example queries

List all open actions with their assignee name

SELECT
    a.action_number,
    a.title,
    a.current_status,
    a.due_date,
    u.first_name || ' ' || u.last_name AS assignee_name
FROM reporting.actions a
LEFT JOIN reporting.users u
    ON a.assignee_type = 'user'
    AND a.assignee_id = u.user_id
WHERE a.current_status NOT IN ('completed', 'cancelled')
ORDER BY a.due_date ASC NULLS LAST;

Actions created from issues, with the issue number

SELECT
    la.parent_type,
    i.issue_number,
    i.title AS issue_title,
    a.action_number,
    a.title AS action_title,
    a.current_status
FROM reporting.linked_actions la
JOIN reporting.actions a USING (action_id)
JOIN reporting.issues i ON la.parent_id = i.issue_id
WHERE la.parent_type = 'issue'
ORDER BY la.linked_at DESC;