Inbox Agent — Canonical Data Model & Entity Relationship Specification

Status: Authoritative Data Model Baseline
Audience: Cursor, Charter, engineering, data architecture
Critical correction: MailMessage and MessageInstance are distinct entities and MUST NOT be collapsed.

1. Modeling Principles

The canonical model separates logical mail content from the provider/account-specific occurrence of that content. This prevents mailbox state, provider identifiers, and synchronization concerns from contaminating message identity.

Primary principles:

stable internal identifiers;
provider-neutral domain entities;
explicit source/provenance;
normalized relationships;
replay-safe provider mappings;
immutable/history-oriented records for AI and audit;
provider-specific extensions only where canonical semantics are insufficient.

2. MailMessage vs MessageInstance — Authoritative Rule

MailMessage

MailMessage represents the logical RFC-style message/content object.

It owns content-level attributes such as:

canonical message identifier;
Internet/RFC Message-ID when present;
subject and normalized subject;
sent/origin timestamp;
sender/from semantics;
body/content references and normalized content fingerprint;
header-derived threading references;
canonical participant relationships;
attachment logical relationships;
content-level AI processing target.

There should normally be one MailMessage for the same logical message even when it appears in multiple accounts, folders, or provider representations.

MessageInstance

MessageInstance represents a specific occurrence of a MailMessage within a connected account/provider context.

It owns instance/provider state such as:

connected account;
provider message/resource ID;
provider thread/conversation ID where applicable;
provider change/version metadata;
mailbox/folder/label membership;
read/unread;
flagged/starred;
draft/sent/trash/spam/archive-like state;
received/discovered timestamp in that account;
deletion/tombstone state;
synchronization timestamps;
provider-specific state extensions.

Cardinality: MailMessage 1 -> N MessageInstance.

A MessageInstance MUST reference exactly one MailMessage. A MailMessage may exist with one or many instances.

This correction supersedes any earlier interpretation in which MailMessage represented a mailbox-specific provider row.

3. Core Entity Catalog

User

Application principal/owner.

Key attributes: user_id, identity-provider subject, status, created/updated timestamps.

ConnectedAccount

A mail account authorized for synchronization.

Key attributes: account_id, user_id, provider type, provider account identity, display address, capability profile, auth-secret reference, sync status, created/updated timestamps.

Unique logical constraint: provider + provider account identity per user.

MailboxContainer

Canonical representation of provider folders/labels/mailboxes.

Key attributes: container_id, account_id, provider container ID, name, canonical semantic type, parent ID where hierarchical, provider metadata.

MailThread

Canonical conversation/thread abstraction.

Key attributes: thread_id, normalized subject/key, created/latest timestamps.

Provider conversation IDs belong in mappings/instances and are not assumed globally canonical.

MailMessage

Logical message/content entity as defined above.

Suggested attributes:

mail_message_id PK
internet_message_id nullable
subject
normalized_subject
sent_at
content_fingerprint
body_text_ref / content storage reference
body_html_ref / content storage reference
in_reply_to_message_id nullable source header value
references_header or normalized reference relation
created_at
updated_at

Deduplication must not rely solely on Internet Message-ID because it may be absent or malformed. Canonicalization uses provider/source evidence plus content/header fingerprints under deterministic rules.

MessageInstance

Provider/account-specific occurrence.

Suggested attributes:

message_instance_id PK
mail_message_id FK NOT NULL
account_id FK NOT NULL
provider_message_id NOT NULL
provider_thread_id nullable
provider_version / change key nullable
received_at nullable
is_read
is_flagged
is_draft
is_sent
is_trashed
is_spam
is_deleted
provider_state_json only for unmapped extension state
first_seen_at
last_seen_at
last_synced_at

Unique constraint: (account_id, provider_message_id).

MessageContainerMembership

Many-to-many relationship between MessageInstance and MailboxContainer, supporting label-based providers.

Key: (message_instance_id, container_id) plus provider membership metadata if required.

Party

A person/organization identity concept used for mail participation and future contact intelligence.

Key attributes: party_id, type, display name, organization relationship where known.

EmailAddress

Normalized email address identity.

Key attributes: email_address_id, normalized address, display/local metadata.

Email addresses are not assumed one-to-one with a person.

PartyEmailAddress

Association between Party and EmailAddress with source/provenance, confidence, type, validity dates.

This supports future contact consolidation without forcing it into MVP.

MessageParticipant

Associates a MailMessage with an email address/party and role.

Roles include: from, sender, reply-to, to, cc, bcc, resent variants as needed.

Key attributes: mail_message_id, ordinal, role, email_address_id, optional party_id, source display name.

Participants are content-level and therefore belong to MailMessage, not MessageInstance, unless a provider exposes an instance-specific envelope distinction that must be separately modeled.

Attachment

Logical attachment associated with MailMessage.

Key attributes: attachment_id, mail_message_id, content ID, filename, MIME type, size, content hash, inline flag, storage reference, scan/processing state.

Provider-specific attachment resource IDs may be held in AttachmentInstance.

AttachmentInstance

Optional mapping from logical attachment to a specific MessageInstance provider resource.

Key attributes: attachment_instance_id, attachment_id, message_instance_id, provider attachment ID, provider metadata.

ThreadMessage

Associates MailMessage to MailThread, with sequence/relationship metadata.

A message may normally belong to one canonical thread, but use an association entity to avoid coupling provider threading to the canonical message row.

AIInference

Immutable record of an AI task execution.

Key attributes: inference ID, mail_message_id, optional message_instance_id, task, model, prompt version, schema/taxonomy version, input fingerprint, output, confidence, validation status, timestamps, cost/latency metadata.

Classification

Normalized/current user-facing classification derived from inference or human override.

Key attributes: classification ID, mail_message_id, dimension, value, source type, source inference ID nullable, confidence, effective/current timestamps.

ExtractedEntity

Structured extraction from a message.

Key attributes: extracted entity ID, mail_message_id, inference ID, type, normalized value, evidence reference, confidence.

ActionItem

Structured requested/committed work identified in mail.

Key attributes: action item ID, mail_message_id, inference ID/source, description, responsible party, due date/time, status, confidence, user override metadata.

ProviderMutation

Durable requested change to provider state.

Key attributes: mutation ID, message_instance_id, account ID, mutation type, desired state, idempotency key, status, requested/sent/confirmed timestamps, provider response metadata, failure details.

SyncCheckpoint

Per-account/provider synchronization cursor/checkpoint.

Key attributes: checkpoint ID, account ID, scope, cursor/token reference, status, last success, recovery metadata.

SyncWorkItem

Durable unit of synchronization/background work.

Key attributes: work ID, account ID, type, payload reference, status, attempts, next attempt, correlation ID, error metadata.

DomainEventOutbox

Transactional event publication record.

Key attributes: event ID, event type, aggregate type/ID, payload, schema version, correlation/causation IDs, created/published timestamps.

ReconciliationRun

Tracks reconciliation execution.

Key attributes: run ID, account ID, scope/window, start/end, status, compared counts, drift counts, repaired counts, diagnostics.

AuditEvent

Append-oriented material action/decision record.

Key attributes: audit ID, actor, action, target type/ID, timestamp, correlation ID, sanitized metadata.

4. Relationship Summary

User
  1 ─── N ConnectedAccount
             1 ─── N MailboxContainer
             1 ─── N MessageInstance ─── N MessageContainerMembership ─── 1 MailboxContainer
                         N
                         │
                         1
                    MailMessage
                     │  │  │
                     │  │  ├── N MessageParticipant ─── 1 EmailAddress
                     │  ├──── N Attachment ─── N AttachmentInstance ─── 1 MessageInstance
                     ├─────── N AIInference
                     ├─────── N Classification
                     ├─────── N ExtractedEntity
                     └─────── N ActionItem

MailThread 1 ─── N ThreadMessage N ─── 1 MailMessage

Party N ─── N EmailAddress (via PartyEmailAddress)

MessageInstance 1 ─── N ProviderMutation
ConnectedAccount 1 ─── N SyncCheckpoint
ConnectedAccount 1 ─── N SyncWorkItem
ConnectedAccount 1 ─── N ReconciliationRun

5. Identity and Deduplication

Instance Identity

Provider instance identity is deterministic:

ConnectedAccount + provider_message_id -> MessageInstance

Canonical Message Identity

Canonical message resolution uses a ranked deterministic strategy, such as:

known provider mapping to an existing canonical message;
trustworthy normalized Internet Message-ID plus corroborating metadata;
content/header fingerprint under strict matching rules;
create new MailMessage when ambiguity remains.

False merges are more damaging than duplicate canonical messages. Ambiguous cases should remain separate and be eligible for later reconciliation.

6. Threading

Canonical threading must not blindly equate provider conversation IDs across accounts/providers.

Use:

In-Reply-To;
References;
normalized subject as supporting evidence;
chronology;
provider thread identifiers as source evidence.

Thread repair/recalculation must not change MailMessage identity.

7. State Ownership Matrix

Data	Owner
Subject/body/core headers	MailMessage
Participants	MailMessage
Logical attachments	MailMessage
AI content classification	MailMessage
Provider message ID	MessageInstance
Provider thread ID	MessageInstance/source mapping
Read/unread	MessageInstance
Flag/star	MessageInstance
Folder/label membership	MessageInstance
Trash/spam/archive state	MessageInstance
Provider change key/version	MessageInstance
Sync timestamps	MessageInstance/account sync entities

This matrix is authoritative for implementation decisions.

8. Persistence Rules

Use UUID/ULID-style internal keys or another implementation-standard opaque identifier.
Provider IDs are alternate keys, never primary domain keys.
Timestamps use timezone-aware representations.
JSON is permitted for provider extension payloads and versioned AI output, not as a substitute for core relational entities.
Frequently queried canonical attributes must be first-class columns.
Foreign keys and unique constraints enforce identity invariants.
Soft/tombstone state is preferred where synchronization/reconciliation requires historical knowledge.
Large bodies/attachments may use object storage with database metadata references.

9. Contact Future-Proofing

Contacts are currently a future consolidation concern. The model therefore distinguishes:

observed email address;
inferred/known party;
source/provenance association;
future authoritative contact record.

Do not make EmailAddress itself the canonical person. Do not overwrite a person/contact based on signature extraction without governed merge logic.

10. Migration Rule for Earlier Model

If an earlier schema used MailMessage as the provider-specific mailbox row:

introduce the new canonical MailMessage;
introduce/rename provider-specific rows as MessageInstance;
move provider/account/folder/read/flag/sync attributes to MessageInstance;
group instances into canonical messages conservatively;
move participants/content-level attachments and AI classification to MailMessage;
preserve provider identifiers and history;
add constraints only after data migration validation;
run deduplication/reconciliation reports.

No destructive merge should occur solely from matching subject or provider thread ID.

11. Data Model Acceptance Criteria

The model is correctly implemented when:

one MailMessage can have multiple MessageInstances;
provider state never requires duplicating message content;
read/folder/label state is instance-specific;
AI content inference targets MailMessage by default;
provider IDs are scoped to ConnectedAccount;
participants and logical attachments are content-level;
replayed synchronization cannot create duplicate MessageInstances;
contact intelligence retains provenance;
reconciliation can identify orphaned/duplicate mappings;
earlier MailMessage-as-instance assumptions have been removed from code and migrations.