Living documentation — last reviewed 2026-05-28

Messages + Comments — Data Model

Schemas: libs/db/src/lib/schema/messaging.ts and libs/db/src/lib/schema/comments.ts.

`messages` (unified DMs + workout-anchored)

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` NOT NULL	FK
`sender_id`	`uuid` NOT NULL	FK `users(id)`
`sender_role`	`sender_role` NOT NULL	`coach \| member \| admin` — snapshot at send (so a later role change doesn’t rewrite history)
`recipient_id`	`uuid` NOT NULL	FK
`workout_assignment_id`	`uuid`	FK CASCADE — NULL = DM, non-NULL = workout-anchored comment
`content`	`text`	Nullable — message may be attachment-only
`created_at`	`timestamptz` NOT NULL
`read_at`	`timestamptz`
`edited_at`	`timestamptz`	Reserved; no API path
`deleted_at`	`timestamptz`	Soft delete

Indexes:

messages_org_created_idx on (organization_id, created_at).
messages_recipient_created_idx, messages_sender_created_idx — conversation reads.
messages_recipient_read_at_idx on (recipient_id, read_at) — unread-count subquery in listConversations.
messages_workout_assignment_idx on (workout_assignment_id, created_at) — workout-anchored thread reads.

`message_attachments`

Column	Type	Notes
`id`	`uuid` PK
`message_id`	`uuid` NOT NULL	FK CASCADE
`type`	`attachment_type` NOT NULL	image \| video
`r2_key`	`text` NOT NULL
`size`	`int` NOT NULL
`mime_type`	`text` NOT NULL
`thumbnail_r2_key`	`text`
`created_at`	`timestamptz` NOT NULL

Index: message_attachments_message_idx.

`exercise_comments` (separate from messages)

Column	Type	Notes
`id`	`uuid` PK
`workout_movement_id`	`uuid` NOT NULL	FK CASCADE
`author_id`	`uuid` NOT NULL	FK `users(id)`
`body`	`text` NOT NULL
`parent_comment_id`	`uuid`	Self-FK for threading
`created_at` / `updated_at` / `deleted_at`	`timestamptz`

Multi-org isolation

messages.organization_id is mandatory + denormalized.
verifyCanMessage requires both participants share the active org.
exercise_comments inherits org scope through the workout_movement → workout → organization chain.

PII handling

Message bodies are user-generated content; plaintext in DB.
Attachments (image/video) are in R2 — presigned reads only.
“A deleted member’s PII must be redacted from messages” is a stated invariant in the README spec but NOT enforced today. Member account deletion leaves sender_id / recipient_id dangling (no ON DELETE clause for these). Future work needed for GDPR-style erasure.

Soft / hard delete

messages.deleted_at soft-delete.
workout_assignments CASCADE → messages with that assignment_id are HARD-deleted with the assignment. Distinct from DM behavior. (Worth noting for QA.)
exercise_comments.deleted_at soft-delete.

Messages + Comments — Code Map Messages + Comments — QA Plan