Behavioural Observations (BOS)

Views for Behavioural Observation System (BOS) entries and their categories/labels.

These views support the Behavioural Observation System in Maecos. BOS entries represent safety observations made by team leaders or observers on the shop floor. Each observation can be categorized, labeled, and marked with a positive or negative reaction.

---

reporting.bos_entries

Safety observations recorded in the BOS module.

Field	Type	Description
bos_id	uuid	Unique identifier of the BOS entry
bos_number	integer	Human-readable entry number
description	text	Description of the observation
type	text	Entry type
discussed	boolean	Whether the observation was discussed with the observed person
created_at	timestamp	When the observation was recorded
created_by	uuid	User who recorded the observation (the observer)
team	text	Team name associated with this observation
updated_at	timestamp	Last modification timestamp
updated_by	uuid	User who last modified the entry
contractor	text	Contractor name (if the observation involves a contractor)
reacted_positive_or_negative	text	'positive', 'negative', or null
external_id	text	External reference ID (for integrations)

Joins: Join with bos_entries_labels on bos_id for category and label filtering. Join created_by with users.user_id for observer details.

---

reporting.bos_entries_labels

Categories and labels attached to BOS entries. This view combines both the BOS-specific category/subcategory system and the general labeling system.

Field	Type	Description
bos_id	uuid	The BOS entry
label_category	text	Category name
label_value	text	Subcategory or label value

Joins: Join with bos_entries on bos_id.

---

Example queries

BOS observations per team, positive vs. negative (last 90 days)

SELECT
    team,
    COUNT(*) AS total_observations,
    COUNT(*) FILTER (WHERE reacted_positive_or_negative = 'positive') AS positive,
    COUNT(*) FILTER (WHERE reacted_positive_or_negative = 'negative') AS negative
FROM reporting.bos_entries
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY team
ORDER BY total_observations DESC;

BOS entries by category

SELECT
    bl.label_category,
    bl.label_value,
    COUNT(*) AS observations
FROM reporting.bos_entries_labels bl
JOIN reporting.bos_entries b USING (bos_id)
WHERE b.created_at >= NOW() - INTERVAL '30 days'
GROUP BY bl.label_category, bl.label_value
ORDER BY observations DESC;