Data model
The schema is the spec's §5, unchanged in names, plus one table for SMS sign-up codes (mail/migrations/001_init.sql) and the additions for outside mail (002_outside_mail.sql: users.external_address and the outbox table). Migrations are applied by the mail service at start-up (recorded in schema_migrations). IDs are bigint identities.
Rules the database enforces
| Rule | How |
|---|---|
| One direct chat per pair of people | Unique participant_hash where kind = 'direct' |
| Same members + same name reuses a group; a different name makes a new group | Unique (participant_hash, name) where kind = 'group' |
| Each person replies to a message once | Unique (parent_id, sender_id) on messages (constraint messages_reply_once) |
| Exactly one To | Unique message_id where kind = 'to' on message_recipients |
| A recipient is a person or a group | Check: exactly one of user_id, group_id |
Drafts never enter messages | Separate drafts table |
| A user has a phone number or an outside address | Check users_phone_xor_external |
| A message is sent out once | Unique message_id on outbox |
Why pointers
A message is stored once; each person who can see it gets a mailbox row for each chat it appears in, carrying their own read, star, replied and folder state. So deleting or starring changes only your copy, Bcc privacy is a matter of who has a pointer, and group members added later simply never get pointers to older threads. user_conversations is the home list, kept up to date in the same transaction, so opening the app is one indexed query.
A whole thread in display order is one query: WHERE root_id = $1 ORDER BY path.