Removing a connected account silently makes its messages disappear (no FK on messageChannelMessageAssociation.messageChannelId)

Removing a connected account silently makes its messages disappear (no FK on messageChannelMessageAssociation.messageChannelId)

Version: v2.39.5 (self-hosted, Docker) Severity: silent data loss on a routine, documented user action

Summary

Removing and re-adding a connected account deletes its messageChannel rows but leaves every messageChannelMessageAssociation pointing at the now-deleted channel. The server then drops every message whose channel cannot be resolved from API responses. The messages are still in the database; they simply cease to exist as far as the product is concerned. No error, no warning, nothing in the logs.

We lost visibility of 65,886 messages across ~30,000 threads this way in a single day, while asking our team to reconnect their mailboxes — which is the remediation your own UI steers people toward when a sync fails.

Root cause

messageChannelMessageAssociation lives in the workspace schema; messageChannel lives in core. The association's sibling columns have cascades, but the channel column has no constraint at all:

FK on "messageId"        -> workspace_xxx.message(id)       ON DELETE CASCADE
FK on "messageThreadId"  -> workspace_xxx."messageThread"(id) ON DELETE CASCADE
FK on "messageChannelId" -> (none)

Verified with:

select conname, pg_get_constraintdef(oid)
  from pg_constraint
 where conrelid = 'workspace_xxx."messageChannelMessageAssociation"'::regclass
   and contype = 'f';

Because nothing references core."messageChannel", deleting a channel neither cascades to the associations nor is refused. They are left dangling.

The dangling rows are then fatal at read time, in packages/twenty-server/src/modules/messaging/common/query-hooks/message/apply-messages-visibility-restrictions.service.ts:

const messageChannels = associations
  .map((association) => messageChannelMap.get(association.messageChannelId))
  .filter(isDefined);

if (messageChannels.length === 0) {
  messages.splice(i, 1);   // <- the message is removed from the response
  continue;
}

"I cannot resolve this message's channel" is treated as "this message does not exist", rather than as an error or a restricted-content state.

Reproduction

1. Connect a Google account and let it sync some mail.
2. In Settings → Accounts → Emails, remove the account.
3. Add the same account again (this is the standard fix for a failed sync).
4. Open a contact you had corresponded with, or the Emails tab.

Expected: the previously imported history is still visible, or at minimum an error explains why it is not.

Actual: the old messages are gone from every API response. In the database:

select count(*)
  from workspace_xxx."messageChannelMessageAssociation" a
 where not exists (
   select 1 from core."messageChannel" c where c.id = a."messageChannelId");
-- 65886

Why this is worse than it first looks

• It is triggered by the recommended remediation. A mailbox in FAILED_INSUFFICIENT_PERMISSIONS cannot be fixed by retrying; the account has to be removed and re-added. So the fix for one problem silently causes a worse one.
• The new channel does not recover the history. It starts empty and imports only recent mail, so the gap is permanent without manual repair.
• The link is unrecoverable by design. Nothing records which mailbox a channel belonged to. Once the row is deleted, the only way to work out where 65,886 orphaned associations should point is to inspect participant addresses and infer it. We had to do exactly that.
• It is completely silent. No exception, no log line, no empty state. The product just shows less mail than it did yesterday.

Suggested fixes

Any one of these would prevent the data loss; the first two are the real fix.

1. Declare the foreign key. A cross-schema FK is valid in Postgres. With ON DELETE CASCADE the associations are cleaned up honestly; with ON DELETE RESTRICT the deletion is refused until they are handled. Either is better than dangling rows.
2. Repoint on re-add. When an account is reconnected for a handle that previously had a channel, migrate the existing associations to the new channel rather than abandoning them. This is what a user expects "reconnect my mailbox" to mean.
3. Do not treat unresolvable as nonexistent. In applyMessagesVisibilityRestrictions, an association pointing at a missing channel is a data-integrity problem, not a permissions outcome. Log it and either surface the message or mark it restricted — but do not silently splice it out of the response.

Notes

We have worked around this with an external job that records channelId -> handle while channels exist and repoints orphaned associations onto the current channel for the same mailbox. That covers our own instance but cannot fix the underlying integrity gap, and every self-hosted user reconnecting a mailbox is exposed to it.

Related: the same splice/early-return style in the email thread UI makes a permission gap render as a blank panel with no error. Filed separately.

Identifiers and addresses above are placeholders.