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) |
| Reconciliation diagnostics | reconciliation_runs compared/drift/repaired counts (MAIL-28 / ADR 0017) |
| Immutable AI inference | ai_inferences plus current_ai_inferences pointer (MAIL-39 / ADR 0022) |
| Human correction / current Classification | human_corrections, learning_observations, current_classifications (MAIL-54 / ADR 0024) |
| Identity hygiene (app state only) | identity_hygiene_recommendations, identity_hygiene_guidance (MAIL-55 / ADR 0024) |
| Recovery mode and backup inventory | ops_recovery_mode, application_rules, application_corrections, ops_backup_runs (MAIL-75 / MAIL-56 / ADR 0026) |
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.
Canonical identity is merge-conservative (MAIL-32 / ADR 0018): existing mapping, trustworthy Message-ID plus corroboration, unique strict fingerprint, else create new. Threads join from headers; provider conversation IDs are evidence on message_instances only. Attachment rows store metadata; scan_state may be held or unknown and must not fail open to clean.
Migration head
Current head: 0023_icloud_mailbox_writes.sql. Apply with npm run db:migrate or npm run migrate:ci. Account health, OAuth state, worker leases, DLQ, and pause tables are in 0012. Notification and subscription tables for incremental sync are in 0013. Hold-on-scan-failure states are in 0014. Current AI inference pointers and inference immutability are in 0015. Human correction pointers, learning observations, and identity-hygiene guidance are in 0016. MailCommand / ProviderMutation durable write-back is in 0017. Recovery mode, API latency samples, durable rules/corrections, and backup-run records are in 0018. Gated migration plans and dry-run estimates are in 0019. Apple OIDC state/nonce for Sign in with Apple is in 0020 (MAIL-184 / ADR 0028). iCloud app-specific password ciphertext uses token type imap_secret in 0021 (MAIL-250 / ADR 0030). 0022_icloud_move.sql keeps that check and marks iCloud rows read-write for folder and cross-account move (ADR 0031). 0023_icloud_mailbox_writes.sql enables archive, mark read, flag, and delete on those rows (ADR 0032).