Add the content_change carrier, the follow column and the fan-out RPC #203

Problem

A person who opens a document never learns that it changed later. There is no follow state, no carrier row and no fan-out.

Nothing in the tree matches content_change, content_email_muted_at, notify_document_content_change, set_document_follow or get_document_follow_state. Confirm with git grep -n "content_change\|content_email_muted_at\|notify_document_content_change\|set_document_follow\|get_document_follow_state" — it returns nothing.

This issue is the database half. Part of #169.

Decided — 2026-09-01

Drop claim 2. Record the gap.

The fan-out RPC returns 0 when the document has no workspaces row, and it does not create one. A document created through the REST API, which no signed-in person has ever opened, notifies nobody, owner included.

The rejected alternative was to insert the row with on conflict (id) do nothing. It was rejected because it puts a write into a read path, and because it would leave workspaces.created_by null forever, against the documented meaning at packages/supabase/scripts/03-0-workspaces.sql:20-21.

This gap must be documented, not left to be discovered. apps/hocuspocus.server/API.md states it in the audience paragraph, in plain words: a document only reaches followers once a signed-in person has opened it at least once.

The ruling is recorded on #169.

What to do

The design is settled. Do not re-derive it.

• The subscription is workspace membership. Every signed-in document open runs join_workspace. So there is no new table.
• The only new state is one nullable column: workspace_members.content_email_muted_at timestamptz. NULL means following.
• The carrier rides public.notifications with a new notification_category value, content_change. The enum has eight values today (packages/supabase/scripts/01-enum.sql:71-80). The digest compiler already groups every pending digest row per user (packages/supabase/scripts/07-5-email-notifications-pgmq.sql:532), so it needs no change.

Six script edits:

• 01-enum.sql — add 'content_change' after 'system_alert' (:79).
• 03-1-workspace_members.sql — add content_email_muted_at, with a comment saying it must never be trusted for privacy.
• 07-4-push-notifications-pgmq.sql — the push gate. See below.
• 07-5-email-notifications-pgmq.sql — the preference case and the force-digest line. The type case there ends in else should_queue := true; (:316-317), so a new type queues email by default. That work is specified in the sibling consumer issue.
• 10-func-notifications.sql — notify_document_content_change.
• 10-8-func-workspace_members.sql — set_document_follow and get_document_follow_state.

Leave 13-RLS.sql and 16-cron-jobs.sql alone. No new policy and no new scheduler.

Grant the two RPCs from the hardening array, not next to the functions. A grant written next to the new functions will not survive a reset.

packages/supabase/scripts/29-lint-hardening.sql §5 (:161-184) loops over every SECURITY DEFINER function in public that the migration role owns. For each one it runs REVOKE EXECUTE ON FUNCTION %I.%I(%s) FROM public, anon, authenticated (:178-181). §6 then re-grants EXECUTE to authenticated from one hardcoded array, user_facing_names (:202-231).

Neither set_document_follow nor get_document_follow_state is in that array. The file is loaded last (:4-5). packages/supabase/generate-seed.ts:76 sorts scripts by numeric prefix, so 29 runs after 10. Without adding both names to the array, both RPCs answer 42501 permission denied to the browser after every local db reset.

A migration cannot cover this locally. packages/supabase/config.toml:58 sets enabled = false under [db.migrations], and :61 sets schema_paths = ["./scripts/*.sql"]. A local reset applies the scripts and skips the migrations. So the array edit belongs in the script, and the migration repeats it for remote.

Keep notify_document_content_change out of the array on purpose. It is service_role-only, the same posture as enqueue_document_view described at 29-lint-hardening.sql:257-259.

Type trap. workspace_members.workspace_id is varchar(36) (packages/supabase/scripts/03-1-workspace_members.sql:6). member_id is uuid (:7). They differ. SQL that compares one to the other raises operator does not exist: character varying = uuid. Compare varchar to varchar and uuid to uuid.

Push gate. enqueue_push_notification (packages/supabase/scripts/07-4-push-notifications-pgmq.sql:186) gates only three types: mention, reply and reaction (:229-237). There is no else. Any other type falls through and pushes. So a content_change carrier would push with no gate at all. Add if v_notify_type = 'content_change' and not coalesce((v_prefs->>'push_content_changes')::boolean, false) then return new; end if;. This gate is load-bearing. No push UI toggle.

Sender guard. The carrier's sender_user_id must be NULL when the actor has no public.users row, and the insert must still run. The column already allows null (packages/supabase/scripts/07-0-notifications.sql:12). Do not copy the precedent at packages/supabase/scripts/10-func-notifications.sql:32-40. That is an early RETURN NEW which aborts the whole fan-out. Every REST write path has a null actor, so a literal copy would notify nobody. The reaction variant at :422-441 skips the row instead. Neither shape fits. Write case when exists(select 1 from public.users where id = p_actor_id) then p_actor_id end and pin it with a test.

set_document_follow is update-only. An upsert would fire notify_user_join_workspace (packages/supabase/scripts/10-8-func-workspace_members.sql:3), which posts a "joined" chat message. It must also leave updated_at untouched. join_workspace writes that column as the last-visit stamp (packages/supabase/scripts/10-functions.sql:867-869), and the roster renders it (packages/supabase/scripts/10-8-func-workspace_members.sql:238).

SQL order is required.

1. Edit packages/supabase/scripts/*.sql.
2. Run bun run --filter @docs.plus/supabase_back reset. It regenerates seed.sql. A fresh-volume seed must come up green.
3. Author the paired migrations.
4. Re-apply each migration to the seeded database to prove idempotence.
5. Run bun run --filter @docs.plus/supabase_back types and ship the regenerated apps/webapp/src/types/supabase.ts in the same change. Never hand-edit it.

Ship two migrations. The enum value gets its own migration that consumes nothing: alter type public.notification_category add value if not exists 'content_change';. Supabase wraps each migration in one transaction, and Postgres forbids using an enum value added in the same transaction. The second, later-timestamped migration holds the column, the functions and the grants.

Acceptance

• apps/hocuspocus.server/API.md states that a document with no signed-in open notifies nobody, owner included.

• select unnest(enum_range(null::public.notification_category)); lists content_change.

• \d public.workspace_members shows content_email_muted_at as a nullable timestamp with time zone.

• After bun run --filter @docs.plus/supabase_back reset, an authenticated caller runs set_document_follow and get_document_follow_state without a 42501.

• An anon caller and an authenticated caller both get 42501 from notify_document_content_change.

• Fan-out matrix, proven in psql. An active member gets one row.

• A member listed in p_editor_ids gets none.

• A member with content_email_muted_at set gets none.

• The p_only_user branch produces exactly one row, for that user.

• Two calls inside 24 hours produce one row in total.

• The fan-out inserts a row with sender_user_id NULL when the actor has no public.users row.

• A content_change notification with push_content_changes unset produces no message on the push_notifications queue.

• set_document_follow leaves workspace_members.updated_at unchanged and posts no chat message.

• Both migrations apply twice to the seeded database with no error.

• apps/webapp/src/types/supabase.ts is regenerated in the same change.

• The decision above is recorded here before the RPC body is written.

Notes

The worker producer seam sits beside the existing fire-and-forget block in apps/hocuspocus.server/src/lib/queue.ts:527-547. That block sits just above the doc:<id>:saved publish at :556. The privacy rules that decide p_only_user are specified in the sibling consumer issue.