Living documentation — last reviewed 2026-05-28

Announcements — Data Model

Schema: libs/db/src/lib/schema/announcements.ts.

`announcements`

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` NOT NULL	FK
`author_id`	`uuid` NOT NULL	FK `users(id)`
`title`	`text` NOT NULL
`content`	`text` NOT NULL
`priority`	`bool` NOT NULL DEFAULT false	UI sort + badge
`created_at`	`timestamptz` NOT NULL
`deleted_at`	`timestamptz`	Soft delete

Indexes:

announcements_org_created_idx on (organization_id, created_at) — list query.
announcements_author_idx on author_id.

`announcement_reads`

Column	Type	Notes
`id`	`uuid` PK
`announcement_id`	`uuid` NOT NULL	FK CASCADE
`user_id`	`uuid` NOT NULL	FK `users(id)`
`read_at`	`timestamptz` NOT NULL DEFAULT now

Constraints + indexes:

Unique (announcement_id, user_id).
announcement_reads_user_idx, announcement_reads_announcement_idx.

Multi-org isolation

announcements.organization_id is required.
Reads are not org-scoped at the table level but the route is — markRead should verify org membership (verify this in QA #3.1).

PII handling

Title / content are coach-authored broadcasts — generally non-PII, but no special sanitization (e.g. raw user-supplied content in <p> rendering — XSS-protect at the UI layer).

Soft / hard delete

announcements.deleted_at for archive (no UI surface today).
Hard delete on cascade only.
announcement_reads CASCADE’d when announcement is hard-deleted.

Announcements — Code Map Announcements — QA Plan