store: split message identity from placements #394

New Issue

2026-08-10T19:28:32Z

KofTwentyTwo commented

2026-08-10 19:28:32 +00:00

(Migrated from github.com)

The largest item. Do not start before #386 has shipped and accumulated identity data.

surrogate_id(account, folder, uidvalidity, uid) makes the same message in a different folder a different message, so a move creates a new row and the old one is never reclaimed.

messages   (message_key PK, body, headers, …)          -- once
placements (message_key, account_id, folder_id,
            uidvalidity, uid, flags,
            PRIMARY KEY(account_id, folder_id, uidvalidity, uid))

Identity precedence: EMAILID (RFC 8474) → X-GM-MSGID → (account_id, normalized Message-ID, content_hash) → (account, folder, uidvalidity, uid).

Non-negotiables

Never key on a Message-ID hash alone. Message-IDs are public, echoed in every References:, and forgeable; with INSERT OR REPLACE a reused Message-ID would silently overwrite the body of a trusted message and evade filters by never being classified new. This hole does not exist today. First-write-wins; differing content mints a new key.
uidvalidity in the placement primary key — UIDs restart at 1 after a bump and would otherwise collide with old rows.
FTS orphan rule. messages_fts holds plaintext bodies keyed on messages.id with a delete-only trigger. Dropping a placement must not delete the FTS row; dropping the last must. Inverted, this leaks plaintext bodies in orphaned rows.
Migration is forward-only. Legacy rows keep their surrogate as message_key, one placement each, no retroactive dedup.
Audit chain namespace boundary. filter_execution_logs.message_id feeds an HMAC chain verified by the live Audit.VerifyChain; historical ids cannot be rewritten. Add a schema-version marker row and return an honest "unresolvable id".

Filter engine changes (larger than a rename)

FilterField::Folder/Account evaluate scalars; WHERE FOLDER = 'INBOX' is undefined for a multi-placement message, so evaluate(&Email) becomes per-placement. The fire-once guard is existing_message_ids, not the execution log — under message_key a new arrival already present in another folder becomes "not new" and silently never fires any filter. The guard must become placement-aware and gain a real (rule_id, message_key) dedup.

Read state becomes per-placement (IMAP \\Seen is per-mailbox), so MARK READ must name one.

Also: there is no folders table — folders are derived from messages GROUP BY folder_id and are currently global across accounts. Placements expose this.

~120–160 edit sites across ~25–30 files. Carries a serialized proto tail; redefining Message.id changes the value of every existing id while staying wire-compatible — invisible to the byte-exact golden, and the token appears at ten other wire positions.

Depends on #386.

Design: ADR 0002 · review evidence (PR #385)

**The largest item. Do not start before #386 has shipped and accumulated identity data.** `surrogate_id(account, folder, uidvalidity, uid)` makes the same message in a different folder a different message, so a move creates a new row and the old one is never reclaimed. ``` messages (message_key PK, body, headers, …) -- once placements (message_key, account_id, folder_id, uidvalidity, uid, flags, PRIMARY KEY(account_id, folder_id, uidvalidity, uid)) ``` Identity precedence: `EMAILID` (RFC 8474) → `X-GM-MSGID` → `(account_id, normalized Message-ID, content_hash)` → `(account, folder, uidvalidity, uid)`. ### Non-negotiables - **Never key on a Message-ID hash alone.** Message-IDs are public, echoed in every `References:`, and forgeable; with `INSERT OR REPLACE` a reused Message-ID would silently overwrite the body of a trusted message and evade filters by never being classified new. **This hole does not exist today.** First-write-wins; differing content mints a new key. - `uidvalidity` **in the placement primary key** — UIDs restart at 1 after a bump and would otherwise collide with old rows. - **FTS orphan rule.** `messages_fts` holds *plaintext* bodies keyed on `messages.id` with a delete-only trigger. Dropping *a* placement must not delete the FTS row; dropping the *last* must. Inverted, this leaks plaintext bodies in orphaned rows. - **Migration is forward-only.** Legacy rows keep their surrogate as `message_key`, one placement each, no retroactive dedup. - **Audit chain namespace boundary.** `filter_execution_logs.message_id` feeds an HMAC chain verified by the live `Audit.VerifyChain`; historical ids **cannot** be rewritten. Add a schema-version marker row and return an honest "unresolvable id". ### Filter engine changes (larger than a rename) `FilterField::Folder`/`Account` evaluate scalars; `WHERE FOLDER = 'INBOX'` is undefined for a multi-placement message, so `evaluate(&Email)` becomes per-placement. **The fire-once guard is `existing_message_ids`, not the execution log** — under `message_key` a new arrival already present in another folder becomes "not new" and *silently never fires any filter*. The guard must become placement-aware **and** gain a real `(rule_id, message_key)` dedup. Read state becomes per-placement (IMAP `\\Seen` is per-mailbox), so `MARK READ` must name one. Also: there is no `folders` table — folders are derived from `messages GROUP BY folder_id` and are currently global across accounts. Placements expose this. ~120–160 edit sites across ~25–30 files. Carries a serialized proto tail; **redefining `Message.id` changes the value of every existing id while staying wire-compatible** — invisible to the byte-exact golden, and the token appears at ten other wire positions. Depends on #386. Design: [ADR 0002](https://github.com/KofTwentyTwo/nuncio/blob/dev/docs/adr/0002-multi-engine-sync-model.md) · [review evidence](https://github.com/KofTwentyTwo/nuncio/blob/dev/docs/reviews/2026-08-10-multi-engine-design-review.md) (PR #385)

KofTwentyTwo commented

2026-08-10 20:30:05 +00:00

(Migrated from github.com)

Unblocked and rescoped by the pre-release posture now recorded in ADR 0002 (PR #385).

The dependency on #386 accumulating identity data is gone. It only existed because history had to be preserved; with a resettable store, capture need only land one merge ahead of the model that consumes it.

The migration section of this issue is superseded. Instead of legacy rows keeping their surrogate as message_key — permanently un-deduped and leaving the store two-tier forever — an identity-bearing schema change bumps user_version, rebuilds the message store, and re-syncs from the server. Account configuration and credentials are preserved; cached mail is not.

The audit-chain namespace boundary is likewise dropped: filter_execution_logs is reinitialised with the store it describes, since a hash chain referencing ids that no longer resolve has no audit value.

Everything else in this issue stands — in particular the FTS plaintext-orphan rule, uidvalidity in the placement primary key, and the fire-once guard becoming placement-aware.

**Unblocked and rescoped** by the pre-release posture now recorded in ADR 0002 (PR #385). The dependency on #386 *accumulating* identity data is gone. It only existed because history had to be preserved; with a resettable store, capture need only land one merge ahead of the model that consumes it. The migration section of this issue is superseded. Instead of legacy rows keeping their surrogate as `message_key` — permanently un-deduped and leaving the store two-tier forever — an identity-bearing schema change bumps `user_version`, rebuilds the message store, and re-syncs from the server. Account configuration and credentials are preserved; cached mail is not. The audit-chain namespace boundary is likewise dropped: `filter_execution_logs` is reinitialised with the store it describes, since a hash chain referencing ids that no longer resolve has no audit value. Everything else in this issue stands — in particular the FTS plaintext-orphan rule, `uidvalidity` in the placement primary key, and the fire-once guard becoming placement-aware.

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: KofTwentyTwo/nuncio#394