messages

Platform messages — inquiries, comments, support. Polymorphic structure (multiple message kinds in one table) with self-relation for threads.

• ETL strategy: merge
• PK: id

Columns

Column	Type	Description
id	BIGINT	PK.
id_tenant	BIGINT	FK → tenants.id.
id_parent	BIGINT	FK → another messages.id. When set, this message is a reply.
id_type	BIGINT	Stable id for the message type.
id_role	BIGINT	Stable id for the author's role (admin, learner, …).
status	TEXT	Message status (preserved unchanged).
type	TEXT	Human-readable type label (preserved unchanged).
role	TEXT	Human-readable role label (preserved unchanged).
message	TEXT	Message body.
created_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ

id+label pairing. id_type / type and id_role / role exist together: use id_* for stable joins and filters, and type / role for human display. The string columns (status, type, role) are stored verbatim and should be treated as opaque labels.

Patterns

-- Full threads (root message + replies)
WITH RECURSIVE thread AS (
    SELECT id, id_parent, message, created_at, 0 AS depth
    FROM lms.messages
    WHERE id_parent IS NULL AND deleted_at IS NULL
  UNION ALL
    SELECT m.id, m.id_parent, m.message, m.created_at, thread.depth + 1
    FROM lms.messages m
    JOIN thread ON thread.id = m.id_parent
    WHERE m.deleted_at IS NULL
)
SELECT * FROM thread ORDER BY created_at, depth;