Updates

Views for internal communication posts, multilingual content, and read confirmations.

These views power the "Updates" section in Maecos. These are internal posts or announcements targeted to specific audiences within the plant. Updates can require read confirmations and support multilingual content.

---

reporting.updates

Internal communication posts with their metadata and targeting information.

Field	Type	Description
update_id	uuid	Unique identifier of the update
category_names	text	Comma-separated list of target categories or audience groups
internal_title	text	Internal title or subject
publish_date	date	When the update was published
publish_until	date	When the update expires
requires_read_confirmation	boolean	Whether recipients must confirm they read the update
author	uuid	User who authored the update
created_at	timestamp	When the update was created
created_by	uuid	User who created the record
updated_at	timestamp	Last modification timestamp
updated_by	uuid	User who last modified the record
deleted_at	timestamp	Soft delete timestamp
deleted_by	uuid	User who deleted the record

Joins: Join with updates_content on update_id for the actual text. Join with updates_read_confirmations on update_id for read tracking. Join author with users.user_id.

---

reporting.updates_content

The actual content of updates, supporting multiple languages.

An update can have multiple rows in this view — one per language. When joining with updates, filter on language to avoid duplicating rows.

Field	Type	Description
update_id	uuid	The update
language	text	Language code of the content
content	text	The update text
created_at	timestamp	When the content was created
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.updates_read_confirmations

Tracks which users have read which updates.

Field	Type	Description
update_id	uuid	The update
user_id	uuid	The user who read the update
read_at	timestamp	When the update was read

---

Example queries

Read confirmation rate for updates that require it

SELECT
    u.internal_title,
    u.publish_date,
    COUNT(DISTINCT rc.user_id) AS confirmed_readers
FROM reporting.updates u
LEFT JOIN reporting.updates_read_confirmations rc USING (update_id)
WHERE u.requires_read_confirmation = true
    AND u.deleted_at IS NULL
GROUP BY u.update_id, u.internal_title, u.publish_date
ORDER BY u.publish_date DESC;