Design a database schema to support a messaging app with complex relationships.

Official answer

Use a conversation as the destination for both direct and group messages. A core relational design is:

• users(user_id primary key, display_name, ...).
• conversations(conversation_id primary key, kind, created_by referencing users, created_at).
• conversation_members(conversation_id, user_id, joined_at, left_at), with a unique membership key and foreign keys to conversations/users. Keep membership history separately if users can leave and rejoin.
• messages(message_id primary key, conversation_id referencing conversations, sender_id referencing users, body, sent_at, client_message_id). A suitable unique sender/client-message key supports retry deduplication.
• user_blocks(blocker_id, blocked_id, created_at), with both user foreign keys, a unique directed pair and a check against self-blocking.

Every message has an explicit sender and one conversation destination. A foreign key alone does not prove active membership at send time. Validate membership and block rules transactionally with the write; define behavior for concurrent removal/blocking and avoid a check-then-insert race.

Specify whether blocks prevent direct delivery, hide content, or affect shared groups. Direct conversations need an enforced participant/pair policy; the kind field alone does not guarantee exactly two participants. Authorize history reads, edits, attachments and background delivery too.

Page message history by a stable (sent_at, message_id) order with an appropriate conversation index. Add read receipts or delivery state at their actual per-user grain. Plan retention, deletion and recovery before introducing partitioning or sharding; neither solves an incomplete authorization model.