design-message.md

# AudivaMax Direct Messages (DB Design) This document specifies the database schema and implementation details for direct messages between authenticated users, including messages addressed to the admin (“superadmin”) account. ## Scope **In scope** - Persisted 1:1 direct messages: user → user, user → admin, admin → user. - Read/unread state per recipient. **Out of scope (future)** - Threads / conversations, attachments, reactions, reporting/moderation workflows. - Soft-delete/archiving per participant. ## “Superadmin” definition There is no separate `superadmin` role in the current schema. The admin/superadmin account is a normal `user` row with `role = 'admin'` (created/updated by `apps/api/src/scripts/create-admin.ts`). ## Table: `message` Created by migration: `packages/db/src/migrations/Migration20260104090000.ts`. ### Columns | Column | Type | Null | Default | Notes | | -------------- | -------------- | ---: | ----------------- | ------------------------------------------------------------------------ | | `id` | `uuid` | no | _(app-generated)_ | UUIDv7 generated in application code. | | `sender_id` | `uuid` | no | | FK → `user.id`. Authenticated user who sent the message. | | `recipient_id` | `uuid` | no | | FK → `user.id`. User who receives the message (including admin account). | | `subject` | `varchar(255)` | yes | | Optional subject line. | | `body` | `text` | no | | Message content. | | `is_read` | `boolean` | no | `false` | Read state for the recipient. | | `created_at` | `timestamptz` | no | _(app-generated)_ | Set by MikroORM `onCreate`. | | `updated_at` | `timestamptz` | no | _(app-generated)_ | Set by MikroORM `onUpdate`. | ### Constraints - Primary key: `message_pkey (id)` - Foreign keys: - `message_sender_id_foreign (sender_id) → user(id) ON UPDATE CASCADE` - `message_recipient_id_foreign (recipient_id) → user(id) ON UPDATE CASCADE` - Note: no explicit `ON DELETE` rule is set; deletions of users referenced by messages are expected to be restricted by default behavior. ### Canonical DDL (as applied by migration) ```sql create table "message" ( "id" uuid not null, "sender_id" uuid not null, "recipient_id" uuid not null, "subject" varchar(255) null, "body" text not null, "is_read" boolean not null default false, "created_at" timestamptz not null, "updated_at" timestamptz not null, constraint "message_pkey" primary key ("id") ); ``` ## ORM mapping Entity: `packages/db/src/entities/Message.ts`. - `sender`: `ManyToOne(User)` → `sender_id` - `recipient`: `ManyToOne(User)` → `recipient_id` - `subject`: nullable string - `body`: `text` - `isRead`: boolean default `false` → `is_read` - `createdAt` / `updatedAt`: managed by MikroORM lifecycle hooks ## Query patterns (and recommended indexes) The migration does **not** currently create indexes besides the primary key. For typical messaging access patterns, these indexes are recommended for a follow-up migration: - Inbox (most common): `recipient_id` + sort by newest - Recommended index: `(recipient_id, created_at desc)` - Sent messages: `sender_id` + sort by newest - Recommended index: `(sender_id, created_at desc)` - Unread count / badge: - Recommended partial index (Postgres): `(recipient_id) WHERE is_read = false` If “conversation view” (messages between two users) is added later, consider a derived conversation key (e.g., `least(sender_id, recipient_id)` / `greatest(...)`) or a separate `conversation` table. ## Authorization rules (API-layer) Database constraints intentionally do not encode application authorization. The API should enforce: - Only authenticated users can send messages. - `sender_id` must equal the authenticated user’s id (no impersonation). - `recipient_id` must exist. - Only the recipient can mark a message as read (`is_read = true`). - Recommended validation: - Disallow `sender_id = recipient_id` (self-message), unless explicitly desired. - Enforce max sizes (e.g., `subject ≤ 255`, `body` size limit) at the API boundary. ## Operational notes - Migrations are applied on API startup (`apps/api/src/index.ts` runs `orm.getMigrator().up()`). - Because ids and timestamps are application-generated, writes should always go through the API/ORM (or replicate the same generation logic in administrative SQL tools).