Member faces and roster miss documents whose workspace slug is the human slug #574

Problem

Settings → Documents and Home show a row of member faces on each document. On production the faces are missing on most documents, the /demo pad among them. The roster popover and the admin dashboard member count fail the same way.

Nothing on screen says anything is wrong. The list reads only the data from the faces request, so a missing row looks the same as a document with one member.

Evidence

Captured on production on 2026-10-23, signed in as a normal account, on Home:

• POST /rest/v1/rpc/get_document_member_previews answered 200.
• The request named 8 documents. Six of them sit in that account's Joined list, so the account is an active member of each.
• The response held one row: a document made on 2026-09-26, with 20 members.
• It held no row for the demo (p3672b330b8af90pvi2) or the other five Joined documents. A document the caller has joined should come back with at least member_count: 1.

Cause

All three functions find the workspace by workspaces.slug. The client sends lower(documentId) as that slug.

• get_document_member_previews(p_slugs): the faces (packages/supabase/scripts/10-8-func-workspace_members.sql, where w.slug = any(p_slugs))
• get_document_members(p_slug): the roster popover (where w.slug = p_slug)
• admin_get_document_member_counts(p_slugs): admin dashboard counts (where w.slug = any(p_slugs))

But workspaces.slug does not always hold lower(documentId):

• From 2024-02 until 2026-08-09, the pad created the workspace from the browser with slug: docMetadata.slug, the human slug (for example demo). Commit eb47e7c3d (2024-02-08) first wrote that call, 1e7c265d9 (2024-11-14) kept it, and 7d56a29d4 (2026-08-09) removed it.
• That upsert used ignoreDuplicates. So a workspace made this way keeps its human slug for good.
• join_workspace writes lower(_workspace_id) only when no workspace exists yet. Only newer workspaces have that value.

So every workspace made before 2026-08-09 is invisible to these three functions. packages/supabase/CLAUDE.md says the column "always" holds lower(documentId). Production shows that is wrong.

The slug value of the demo workspace on production is read from code, not measured. This read-only query confirms it:

select id, slug from public.workspaces where id = 'p3672b330b8AF90PvI2';
select count(*) as total, count(*) filter (where slug <> lower(id)) as mismatched from public.workspaces;

Fix

Key all three functions on the documentId, which is workspaces.id, verbatim and in exact case. Never key them on the slug.

• Migration: drop and re-create the three functions.
  • Take p_document_ids text[] (or p_document_id text for the roster).
  • Match w.id.
  • Return document_id instead of slug.
  • Re-grant explicitly, because a drop loses grants: authenticated and service_role for the two user functions, and service_role only for the admin one.
• Mirror the change in scripts/10-8-func-workspace_members.sql, and fix the comments that call the slug the key.
• Regenerate apps/webapp/src/types/supabase.ts.
• Webapp: useDocumentMembers, useDocumentRoster, DocumentListRow and DocumentGridTile send and key on doc.documentId as is, with no .toLowerCase().
• Backend: adminStats.service.ts sends exact documentIds to admin_get_document_member_counts and maps by document_id.
• Correct the rule in packages/supabase/CLAUDE.md: key workspace and member lookups on workspaces.id.

The pad URL keeps the human slug. This change is about data keys only.

Decisions

1. Rename the parameters, or keep the old names?
   Recommended: rename them, and cut over in one go. Renaming is a drop and re-create, so an old client fails until the webapp and the backend deploy. That failure is what users see today anyway: hidden faces, plus one admin log line. Keeping p_slugs while passing ids would be a name that lies.
2. Backfill workspaces.slug to lower(id) as well?
   Recommended: no. The column is unique and has a check constraint, and other readers may still expect the human slug (see below). Keying on w.id makes the slug value irrelevant to these functions.

Out of scope (follow-up)

These readers also use workspaces.slug. Old workspaces give them the human slug and new ones give lower(documentId), so they can disagree. List them, but leave them for a separate change:

• Digest email links: workspace_slug in scripts/07-5-email-notifications-pgmq.sql and apps/hocuspocus.server/src/lib/email/digestDocuments.ts.
• Bookmarks: workspace_slug in scripts/07-bookmark-functions.sql.
• Chat media stats: w.slug in scripts/10-4-func-chat-media-features.sql.
• User retention: w.slug in scripts/22-user-retention.sql.

document_view_stats is keyed by lower(documentId) from the room name, not from workspaces.slug, so it is correct as it is.

Related: a failed faces request shows no error on the list (DocumentsList.tsx reads only data). Signed-out visitors never become members, so the faces show signed-in visitors only. That is by design.