Clear the text of a chat message when it is deleted #435

Summary

Deleting a chat message only sets deleted_at. The text stays in content and html. The signed-in read policy does not filter deleted_at, and every chat channel is PUBLIC. So any signed-in account can read the text of every deleted message through the Data API.

• Severity: Medium (privacy: "delete" does not delete)
• Area: Supabase messages triggers
• Source: security review of 2026-10-06, finding M7

Production check (2026-10-06, read-only)

• 6 soft-deleted messages still hold text.
• messages_visible_select is USING (internal.can_read_channel(channel_id)) with no deleted_at filter.

Where

• Delete sets only deleted_at: apps/webapp/src/api/messages/deleteMessage.ts:3-10.
• handle_message_soft_delete and its trigger message_soft_delete: packages/supabase/scripts/10-3-func-message.sql:21-107 and :116-120. The trigger is AFTER UPDATE OF deleted_at, so assigning NEW.content inside it has no effect.
• update_message_previews (AFTER UPDATE OF content, medias, type): packages/supabase/scripts/10-3-func-message.sql:161-169. It rewrites reply previews and the channel preview when content is in the SET list.
• Policy: messages_visible_select in packages/supabase/scripts/13-RLS.sql:191-193.
• Nothing purges soft-deleted rows later.
• The UI hides deleted rows because the read RPCs filter them. Anon cannot read them, because messages_public_anon_select filters deleted_at.

Fix plan

1. New BEFORE trigger. In scripts/10-3-func-message.sql, after message_soft_delete, add public.clear_deleted_message_text() and its trigger:
   • BEFORE UPDATE OF deleted_at ON public.messages FOR EACH ROW WHEN (NEW.deleted_at IS NOT NULL).
   • It sets NEW.content, NEW.html and NEW.medias to null, and removes comment from NEW.metadata. The comment anchor quotes document text.
   • It keeps metadata.replied, reply_to_message_id and the row, so threads and reply previews keep their place.
   • handle_message_soft_delete still receives OLD.medias, so media cleanup is unchanged.
2. Backfill the existing rows in the same migration. Run update public.messages set deleted_at = deleted_at where deleted_at is not null and (content is not null or html is not null or medias is not null);.
   • This fires only the new trigger. The message_soft_delete WHEN clause is false, so nothing is re-broadcast or re-counted.
   • Do not set content directly. That fires update_message_previews and overwrites reply and channel previews.
3. Mirror the function and trigger in packages/supabase/scripts/10-3-func-message.sql. The migration must also create them, because production never runs db reset.

Repo rules that apply:

• Regenerate the seed with bun run --filter @docs.plus/supabase_back seed. Never hand-edit packages/supabase/seed.sql.
• Run bun run --filter @docs.plus/supabase_back types. A trigger does not change the generated types, so expect no diff.

Out of scope

• Adding deleted_at is null to messages_visible_select. Once the text is gone, the leak is closed. The filter would also break two things:
  • deleteMessage calls .select(), so its UPDATE … RETURNING would fail RLS on the new row.
  • Signed-in clients would stop getting the postgres_changes delete event. The chatroom "Soft-delete broadcast contract" in apps/webapp/src/components/chatroom/CLAUDE.md says they get both signals.
• Purging soft-deleted rows.

Acceptance criteria

• After a delete, a signed-in read of that row returns null content, html and medias.
• After the migration, the 6 existing soft-deleted rows hold no text.
• Reply previews still read "The message has been deleted", and the channel preview still shows the newest live message.
• Thread counts and the message:deleted broadcast behave as before.
• Media of a deleted message is still removed from storage.

Verify

1. Apply locally: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<new file>.sql.
2. In the local webapp, send a message with text and a picture, reply to it, then delete it.
3. As postgres: select content, html, medias, metadata from public.messages where id = '<message id>';. Expect null text and media.
4. Check the reply preview and the channel preview in the webapp.
5. In production after deploy, read-only: select count(*) from public.messages where deleted_at is not null and (content is not null or html is not null); returns 0.

Related

• Make a Private document's chat readable and writable only by its owner #396 Private document chat.
• Stop clients from writing server-owned columns on chat messages #400 limits the messages columns a client may UPDATE. The new trigger sets columns inside a BEFORE trigger, which needs no column grant.

Generated by Claude Code