Canonical schema (Sprint 02)
Duckbill’s canonical mail schema lives in inbox_agent and follows the data model specification. Provider IDs are alternate keys, never primary keys.
Ownership
| Concern | Table |
|---|---|
| Application user | users (MAIL-73) |
| Connected mailbox | connected_accounts (MAIL-101) |
| Delivered-to / alias identity | mail_identities (MAIL-100) |
| Logical RFC content | mail_messages (MAIL-85) |
| Provider/account occurrence + mailbox flags | message_instances (MAIL-85) |
| Provenance / historical | columns on message_instances (MAIL-174) |
| Conversation attention | conversation_states keyed by thread_id (MAIL-96 / ADR 0009) |
MailMessage 1:N MessageInstance. There is no is_read column on mail_messages. Participants and logical attachments belong to mail_messages. Folder/label membership belongs to message_instances.
Live incremental sync inserts historical = false. Import/backfill helpers set historical = true. Attention workload queries exclude historical rows.
Migration head
Current head: 0012_sprint03_runtime.sql. Apply with npm run db:migrate or npm run migrate:ci. Account health, OAuth state, worker leases, DLQ, and pause tables are in that migration.