Show which apps each user connected on the admin Users page #573

Parent

#230. Related: #357.

Summary

The admin Users page (https://admin.docs.plus/users) does not show which people connected an app to their account over MCP. This issue adds one column for that, and the same field in the CSV export.

The maintainer can then help people who connected Claude or ChatGPT. The maintainer can also check the connector runs in #357 against real accounts.

Terms

• A grant is one row in auth.oauth_consents. A live grant has revoked_at is null, and its client has deleted_at is null.
• A connected app is what the admin sees: one app name plus its redirect origins. It holds the same grants that the person sees in Settings > Connected apps. Settings groups and names them its own way (groupApps and the brand table), so the two lists can look different.

What the admin sees

A new column after Notifications on /users. Its header is Connected apps (Ruling 1). The header tooltip reads "Apps with access: granted and not revoked". DataTable takes a ReactNode header (DataTable.tsx:14), so the header is <span className="tooltip tooltip-bottom" data-tip="Apps with access: granted and not revoked">Connected apps</span>.

• When a refetch fails but earlier data exists, React Query keeps data. The cell then shows that data, not "Could not load". The rule is: Error only when isError && !data.
• Each badge sits in a daisyUI tooltip tooltip-top, like NotificationBadges.tsx:24. The data-tip lists the redirect origins joined with ", ", for example https://claude.ai, https://claude.com.
• Each badge also holds its origins as sr-only text. A screen reader then reads the origin, which is the trust fact. The precedent badge labels its icon with role="img" and aria-label (NotificationBadges.tsx:26-27).
• Two grants with the same cleaned name and the same origins show as one badge. A borrowed name with a different origin shows as its own badge.
• The column covers only the current page of 20 users (USERS_PAGE_SIZE = 20, admin-stats.controller.ts:26). It cannot be sorted or filtered.
• No token, species or rule changes. The cell uses daisyUI and Tailwind classes only.

A badge means a live grant that the person can see and disconnect in Settings. It shows a grant, not use. A grant can stay live after the app stops calling.

Data source

The source is auth.oauth_consents joined to auth.oauth_clients. An MCP connection is a Supabase OAuth 2.1 server grant.

• The deleted_at filter is required. The local image is public.ecr.aws/supabase/gotrue:v2.196.0, and auth.oauth_clients has a deleted_at column. In upstream GoTrue v2.196.0, a client delete sets DeletedAt and does not revoke its consents. UserListOAuthGrants skips a consent whose client lookup filters deleted_at IS NULL. These source citations are upstream, not in this repo: internal/api/oauthserver/service.go:323-341, handlers.go:530-575, models/oauth_client.go:225. Without the filter, the column could show apps that Settings no longer lists.
• A hard delete needs no code. Both foreign keys cascade. In a rolled-back test, a client delete and a user delete each left 0 consents.
• auth.oauth_authorizations is not a connection. It holds 3-minute codes (expires_at default now() + '00:03:00').

Not the admin API. @supabase/auth-js 2.118.0 (bun.lock) auth.admin.oauth manages clients only: list, create, get, update and delete. Grants are listed only with the person's own token. Settings > Connected apps uses that path.

Not Prisma. In production, Prisma's DATABASE_URL is the DigitalOcean cluster, not Supabase (AGENTS.md:396, apps/hocuspocus.server/prisma/schema.prisma:8, docker-compose.prod.yml:170). Locally they are separate containers too. docsy-postgres-local has no auth schema: the pg_namespace query returned none. Do not use $queryRaw on auth.*.

The server calls a Supabase RPC with the service-role client (getSupabaseClient(), apps/hocuspocus.server/src/api/utils/supabase.ts:5). adminAnalytics.service.ts:15 is the supabase.rpc(...) precedent.

The function is SECURITY DEFINER and owned by postgres. On the local database, has_table_privilege is true for postgres on both tables. It is false for service_role, authenticated and anon. postgres gets SELECT through the default ACL postgres=arwdDxt/supabase_auth_admin, and rolsuper is false for postgres. An invoker version called as service_role failed with ERROR: permission denied for table oauth_consents.

This is new ground. No function in the repo reads auth.* rows today (catalog check: 0 rows). Hosted read access for postgres is unverified, so implementation step 1 checks it first.

SQL function contract

• Name: public.admin_get_user_connected_apps(). It follows admin_get_user_notification_subs (07-4-push-notifications-pgmq.sql:489). It sits in public, because PostgREST exposes only public and graphql_public (packages/supabase/config.toml:13).
• Returns: one jsonb value, { "<user_id>": [{ "name": text | null, "redirectUris": text }] }, newest grant first. It returns NULL when either auth table is missing.
• Security: security definer, set search_path = '' (as in 31-connected-app-token-gate.sql:15), stable, read-only.
• Grants: EXECUTE for service_role only.

-- Service role only: the admin route checks the admin. Under service_role
-- auth.uid() is null, so an is_admin(auth.uid()) gate would return zero rows.
-- One jsonb value, so the PostgREST max_rows cap (1000) cannot cut the map.
-- deleted_at: GoTrue soft-deletes a client and leaves its consents live.
create or replace function public.admin_get_user_connected_apps()
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
begin
  if to_regclass('auth.oauth_consents') is null
     or to_regclass('auth.oauth_clients') is null then
    return null;
  end if;
  return (
    select coalesce(jsonb_object_agg(per_user.user_id, per_user.apps), '{}'::jsonb)
    from (
      select oc.user_id,
             jsonb_agg(jsonb_build_object(
               'name', cl.client_name,
               'redirectUris', cl.redirect_uris
             ) order by oc.granted_at desc) as apps
      from auth.oauth_consents oc
      join auth.oauth_clients cl on cl.id = oc.client_id
      where oc.revoked_at is null
        and cl.deleted_at is null
      group by oc.user_id
    ) per_user
  );
end;
$$;

revoke execute on function public.admin_get_user_connected_apps() from public, anon, authenticated;
grant execute on function public.admin_get_user_connected_apps() to service_role;

comment on function public.admin_get_user_connected_apps() is
  'Admin Users page: live OAuth grants per user. service_role only.';

The comment on function line follows the precedent at 07-4-push-notifications-pgmq.sql:508.

Why each choice holds:

• plpgsql, not language sql. check_function_bodies is on locally. A language sql body that names a missing table fails at CREATE (ERROR: relation "auth.no_such_table" does not exist). GoTrue creates the auth.oauth_* tables, not the repo. A plpgsql body is checked only when it runs, so the migration applies on any GoTrue. Whether a fresh seed runs before GoTrue creates the tables is unverified.
• Self-host guard. The to_regclass check exists only for a GoTrue older than the OAuth tables. It returns NULL, and the route answers available: false. GoTrue v2.196.0 creates the tables even when [auth.oauth_server] is off. In that case the map is empty, not NULL.
• One jsonb value. A set-returning RPC loses rows past max_rows = 1000 with no error (config.toml:18). On local PostgREST v12.2.3, get_signups_per_day with p_days = 1500 returned 1000 rows, HTTP 200 and Content-Range: 0-999/1500. A scalar jsonb RPC returned Content-Range: 0-0/1. The production max_rows value is unverified.
• No is_admin(auth.uid()) gate. Under service_role, auth.uid() is null and is_admin is false (local check). admin-stats.controller.ts:427-429 records the same trap.
• Grants. As authenticated and as anon, the call failed with permission denied for function admin_get_user_connected_apps. scripts/check-grants.sql returned 0 rows. The 29-lint-hardening.sql §5 sweep revokes only from public, anon and authenticated, so the service_role grant survives. Keep the name out of the §6 user_facing_names list.
• Verify production grants before the Supabase 2026-10-30 grant change #553 does not apply. That change covers new tables in public, not functions. 20261021120000_default_privileges_close_new_objects.sql already closes default function EXECUTE.
• redirect_uris is one comma-joined text value, not an array (\d auth.oauth_clients). Upstream GoTrue splits it on , (oauth_client.go:123, not in this repo). The route splits it the same way.

Route contract

GET /api/admin/users/connected-apps, registered in admin.router.ts beside /users/notification-subs:

admin.get('/users/connected-apps', adminController.getUserConnectedApps)

• Guard: admin.use('*', adminAuthMiddleware) (admin.router.ts:32). It refuses a connected-app token with 403 (adminAuth.ts:29-31). No other route captures this path. The route takes no input, so it needs no zValidator.
• Envelope: house ok / fail from src/http/envelope.ts, like getMcpUsage.
• Controller: getUserConnectedApps in admin.controller.ts, beside getMcpUsage. It reuses UNKNOWN_APP (admin.controller.ts:182).

Response type, in apps/admin-dashboard/src/types/index.ts beside McpUsage (:542):

export interface UserConnectedApps {
  available: boolean
  users: Record<string, { name: string; origins: string[] }[]>
}

For each grant, the controller:

1. Cleans the name with a new helper cleanClientName beside originOf. It copies the displayClientName rule (apps/webapp/src/utils/displayClientName.ts:1-8): strip \p{Cc} and the bidi controls, trim, and cut at 80 characters with …. It has one deliberate difference: it returns '' for an empty or null name, not 'Unnamed app'. Its comment names the twin file and that difference, as apps/hocuspocus.server/src/modules/document-versions/domain/blockAuthors.ts:6-7 does. The server does not import from apps/webapp.
2. Maps '' to UNKNOWN_APP ('Unknown app'). The MCP page already uses it (admin.controller.ts:233,247). Settings says 'Unnamed app' (displayClientName.ts:6), so the two surfaces differ for now. See Later.
3. Splits redirectUris on ,, trims each part, and turns it into origin text with a new helper originTextOf. Do not reuse originOf here. For a custom scheme such as cursor://anysphere.cursor-mcp/oauth/callback, new URL(uri).origin is the string "null", not null, so originOf would print (null). originTextOf copies the rule in apps/webapp/src/utils/appTrust.ts:48-51: url.origin !== 'null' ? url.origin : \${url.protocol}//${url.host}``. It drops a part that does not parse. Then remove duplicates and sort.
4. Keeps one entry per cleaned name and origin set. The first one seen is the newest grant.

A failure never becomes an empty map, because an empty map paints "None" on every row. The service-role client has no Database type (createClient at apps/hocuspocus.server/src/lib/supabase.ts:50), so rpc() returns any. Check the shape at run time, and wrap the whole call in try/catch.

fetchApi throws on any non-2xx (apps/admin-dashboard/src/services/api.ts:83-95), so React Query sees isError.

UI change

• apps/admin-dashboard/src/types/index.ts: add UserConnectedApps (above).
• apps/admin-dashboard/src/services/api.ts: add fetchUserConnectedApps(): Promise<UserConnectedApps>. Unwrap .data as fetchMcpUsage does (api.ts:300-302).
• apps/admin-dashboard/src/pages/users.tsx:
  • Add a query with key ['admin', 'users', 'connected-apps'] and the default staleTime, like the other maps (users.tsx:63-76). The key has no page number, so a page turn does not refetch it.
  • Add the column after Notifications, with the five states above. Render the cell from the query result by user.id, inside the columns closure, because the cell needs the list and the query state.
  • Make the header Refresh refetch this query too. Today onRefresh refetches only the users list (users.tsx:283). Use onRefresh={() => { refetch(); refetchConnectedApps() }} and refreshing={isRefetching || connectedAppsRefetching}.
  • Add connected_apps: string to UserWithExtras. Only the CSV reads it.
  • Add { key: 'connected_apps', header: 'Connected apps' } to the CSV columns, after Email Notif.
• CSV format. Each entry is the name plus its origins, for example Claude (https://claude.ai, https://claude.com). Origins inside one entry are joined with ", ". Entries are joined with "; ". A borrowed name then reads as Claude (https://evil.test), not as a second Claude. With no grants, the field is None. On an error with no earlier data, it is Could not load. When available is false, it is Not available. While the query is pending, it is an empty string, because handleExport does not wait for the side queries (users.tsx:107-126).
• The CSV field must be a string. exportToCSV neutralizes formulas and escapes quotes only for strings (apps/admin-dashboard/src/utils/export.ts:27-31). In a scratch run, an array cell kept =HYPERLINK(...) live and split the row.
• apps/hocuspocus.server/src/modules/openapi/domain/paths/admin.ts: add the path, id adminGetUserConnectedApps, group Dashboard & users, with a 500 CONNECTED_APPS_FAILED response.
• apps/hocuspocus.server/API.md §Dashboard & users: add one row and one sentence on available.
• apps/hocuspocus.server/CHANGELOG.md §[Unreleased]: one entry for the new route and the new SQL function.

Performance

Every run was on the local database, inside BEGIN; ... ROLLBACK;, on 2026-10-23.

• Production scale. About 8 OAuth clients on 2026-10-20 (memory note, not re-read here).
• Locks. The read takes only AccessShareLock, so it does not block GoTrue's consent writes or revokes. authenticator has statement_timeout=8s. The server's Supabase deadline is SUPABASE_FETCH_TIMEOUT_MS = 10_000 (apps/hocuspocus.server/src/lib/supabase.ts:9).
• Requests. One more HTTP request per page load, in parallel with the four queries that the page already fires. It costs two PostgREST calls: the admin check and the RPC.
• Cadence. _app.tsx:10-17 sets staleTime: 1000 * 60 and refetchOnWindowFocus: false, with no refetchInterval. The map refetches on mount once it is stale, on reconnect, and on Refresh. Nothing polls.
• Size against the page today. The notification-subs map writes about 104 B for every user (local measure). The connected-apps map holds only connected users.
• Not measured. The HTTP response size after grouping, and anything on production.

Conclusion. No new index, no cache and no paging. At 82,832 live grants, the RPC result is about 9.8 MB and the query takes about 109 ms. Payload size is the first limit to watch, long before query time.

Reliability notes

• Freshness. A revoke shows on the next fetch, or on Refresh after this change. An access token that the app already holds stays valid until it expires (lifetime unverified).
• The name is self-declared. client_name comes from Dynamic Client Registration, so anyone can call an app "Claude" (apps/webapp/src/utils/appTrust.ts:5-7). The origin in the tooltip, the sr-only text and the CSV is the trust fact. A rolled-back test had two clients named Claude. One used https://claude.ai/..., and the other used https://evil.test/cb. Both came back, and the grouping rule shows them as two badges. A null name also came back.
• Name length. client_name can hold up to 1024 characters (oauth_clients_client_name_length). The 80-character cut and truncate keep the cell short.
• Deploy order. Ruling 3 puts the migration first. If the route still ships before the migration, the RPC answers PGRST202. The column then shows "Could not load", and nothing else breaks.
• Tests. No committed test meets AGENTS.md §Test Policy. The PR runs one rolled-back SQL probe instead (Acceptance criteria).

Rulings

All three were ruled on 2026-10-23.

Ruling 1. Column and CSV header. Ruled 2026-10-23: B, "Connected apps". The names in this issue already use it.

• A: "MCP", as asked. It matches "MCP Usage" in the admin nav (apps/admin-dashboard/src/constants/navigation.ts:31).
• B: "Connected apps". People see that name in Settings, and the column is the same list. B also renames the code to match: public.admin_get_user_connected_apps(), /users/connected-apps and the CSV key connected_apps.
• Recommendation: B. It keeps one name per thing. Ruled: B.

Ruling 2. Privacy and the <5 mask. Ruled 2026-10-23: B. The MCP page masks 1 to 4 callers as '<5', because a small count could point at one person (admin.controller.ts:216). This column names each person's apps. An admin can then tie a small "By app" count to one person. The privacy page says each tool call records the account id and the app (apps/webapp/src/pages/privacy.tsx:101). It does not say that admins can see connected apps.

• A: Ship. Change no policy text. Reword the maskSmallCount comment so it no longer promises that no per-person view exists.
• B: As A, and add one line under "Connected AI apps" in privacy.tsx: "Our admins can see which apps you connected."
• C: Show only yes or no per user, with no app names. This drops most of the value.
• Recommendation: B. It is one sentence, and it keeps the policy accurate. It does not block the build. Ruled: B.

Ruling 3. Deploy order. Ruled 2026-10-23: A.

• A: Apply the migration first, then deploy back, then front. "Front" means both the admin dashboard and the webapp. The webapp, which holds the privacy sentence, ships no later than the admin dashboard.
• B: Ship everything together and accept a short "Could not load".
• Recommendation: A. The data then exists before the code that reads it. Ruled: A.

Preconditions

• A clean Supabase tree. Steps 3 and 4 regenerate seed.sql and apps/webapp/src/types/supabase.ts from the whole tree and the local database. Uncommitted work for another issue would then leak into this diff. On 2026-10-23, the tree held uncommitted Member faces and roster miss documents whose workspace slug is the human slug #574 work in seed.sql, supabase.ts, scripts/10-8-func-workspace_members.sql and migrations/20261023120000_key_member_rpcs_on_document_id.sql. Start only after that work is committed, or after its owner sets it aside.
• Migration name. 14 digits plus _snake_name.sql. The timestamp must sort after the newest file in packages/supabase/migrations/, for example 20261023130000_admin_user_connected_apps.sql if 20261023120000 is still the newest.

Implementation steps

Steps marked [maintainer] need production access. An implementer does every other step locally.

1. [maintainer] A repo comment says the opposite of this design: "the auth schema is inaccessible to PostgREST / standard RPC calls" (packages/supabase/scripts/28-ghost-accounts-audit.sql:6-8). Locally, a definer function owned by postgres reads the rows. Before the migration goes up, run this read-only check in Dashboard > SQL Editor:

   select current_user,
          has_table_privilege('postgres','auth.oauth_consents','select') as consents,
          has_table_privilege('postgres','auth.oauth_clients','select') as clients;

   Both privilege columns must be true. If either is false, stop and redesign. Never grant on auth.* from the repo.
2. Add packages/supabase/scripts/33-admin-connected-apps.sql and a paired migration with the same body.
3. Regenerate the seed: bun run --filter @docs.plus/supabase_back seed. Apply the migration locally without a reset: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<file>.sql.
4. Regenerate types: bun run --filter @docs.plus/supabase_back types. Then run the gates: bun run --filter @docs.plus/supabase_back check.
5. Add the route, the controller (with cleanClientName and originTextOf), the OpenAPI entry, the API.md row and the back-end CHANGELOG entry.
6. Add the type, the fetch helper, the query, the column, the Refresh change and the CSV field.
7. Apply Ruling 2:
   • Reword the maskSmallCount comment (admin.controller.ts:216) to: // The usage chart hides counts of 1 to 4, so it cannot point at one person. The Users page names each person's connected apps on purpose.
   • In apps/webapp/src/pages/privacy.tsx, add a new <p> right after the </ul> of "Connected AI apps" (:104). It goes before the "To stop an app" paragraph. Its text: "Our admins can see which apps you connected."
   • Set LEGAL_UPDATED in apps/webapp/src/components/pages/legal/legalMetadata.ts (now '12 October 2026') to the ship date. It is shared with /terms.
   • Add an apps/webapp/CHANGELOG.md §[Unreleased] entry for the privacy change. Commit 18c8e19f5 is the precedent for both of these lines.
8. Run only the gates for touched files:
   • bun run --filter @docs.plus/supabase_back check
   • bun run --filter @docs.plus/hocuspocus typecheck
   • bun test specCoverage in apps/hocuspocus.server, so OpenAPI operation ids stay unique. Its route-coverage test does not cover the admin router, so check the OpenAPI entry by hand.
   • bun run --filter @docs.plus/admin-dashboard typecheck
   • bun run --filter @docs.plus/webapp typecheck
   • eslint and prettier on the touched files, including the API.md table alignment.
9. [maintainer] Push the migration to production. Follow packages/supabase/CLAUDE.md §Remote db push: run bunx supabase migration list first, and set [db.migrations] enabled to true only while pushing. If another unpushed migration (such as Member faces and roster miss documents whose workspace slug is the human slug #574's) is in the list, decide first whether it ships in the same push.
10. [maintainer] Run packages/supabase/scripts/check-grants.sql read-only against production (Dashboard SQL Editor, or psql with PGOPTIONS='-c default_transaction_read_only=on'). Zero rows means pass.
11. [maintainer] Deploy in the order of Ruling 3: the back end, then the webapp and the admin dashboard.

Do not run reset for this change. It wipes the shared local state of other worktrees (packages/supabase/CLAUDE.md), and the function has no cross-file SQL dependency.

Acceptance criteria

[maintainer], on production:

• Step 1 returned true for both tables.
• The migration reached production before the back-end deploy (Ruling 3).
• After the migration, this query returns postgres: select pg_get_userbyid(proowner) from pg_proc where proname = 'admin_get_user_connected_apps';
• check-grants.sql returned zero rows after the push (step 10).
• The privacy sentence went live no later than the column.

Implementer, locally:

• public.admin_get_user_connected_apps() exists in the script, the migration, seed.sql and apps/webapp/src/types/supabase.ts. The gates in step 8 pass.
• service_role can call the function. authenticated and anon get permission denied for function admin_get_user_connected_apps.
• A rolled-back SQL probe seeds six cases. They are a revoked consent, a soft-deleted client, a null name, two "Claude" clients with different origins, a custom-scheme redirect URI, and a user with 3 apps. The result leaves out the revoked and deleted grants.
• Browser data that stays: insert rows into auth.oauth_clients and auth.oauth_consents for a local test user. Required client columns are id, registration_type, redirect_uris, grant_types and token_endpoint_auth_method. Use the six cases above, plus one client named =HYPERLINK("x"). When done, delete those clients. The cascade removes their consents.
• With that data, the two "Claude" grants show as two badges. The custom-scheme client shows an origin like cursor://anysphere.cursor-mcp, never null. Each badge has its origins as sr-only text. A long name ends in an ellipsis.
• In a browser, the column shows the Loading, Error, None and Apps states. For Error, block /api/admin/users/connected-apps with DevTools request blocking.
• Refresh refetches the column, and the refresh spinner covers it.
• The CSV connected_apps field reads like Claude (https://claude.ai); Claude (https://evil.test) for a user who holds both probe clients. The =HYPERLINK("x") name comes out with a leading '.
• GET /api/admin/users/connected-apps answers the house envelope. The connected-app refusal comes from admin.use('*', adminAuthMiddleware) (admin.router.ts:32), so confirm in review that the route is registered on that router. A live 403 check needs a local connected-app token (docs/mcp/README.md, "Connect in Claude Code"), and it is optional.
• The maskSmallCount comment, the privacy sentence, LEGAL_UPDATED and both CHANGELOG entries match step 5 and step 7.

Files to touch

• packages/supabase/scripts/33-admin-connected-apps.sql (new)
• packages/supabase/migrations/<timestamp>_admin_user_connected_apps.sql (new)
• packages/supabase/seed.sql (regenerated, never hand-edited)
• apps/webapp/src/types/supabase.ts (regenerated, never hand-edited)
• apps/hocuspocus.server/src/api/routers/admin.router.ts
• apps/hocuspocus.server/src/api/controllers/admin.controller.ts (also the maskSmallCount comment, Ruling 2)
• apps/hocuspocus.server/src/modules/openapi/domain/paths/admin.ts
• apps/hocuspocus.server/API.md
• apps/hocuspocus.server/CHANGELOG.md
• apps/admin-dashboard/src/types/index.ts
• apps/admin-dashboard/src/services/api.ts
• apps/admin-dashboard/src/pages/users.tsx
• apps/webapp/src/pages/privacy.tsx (Ruling 2)
• apps/webapp/src/components/pages/legal/legalMetadata.ts (Ruling 2)
• apps/webapp/CHANGELOG.md (Ruling 2)

Later

• A "last used" time per app from auth.sessions. It is usage data, so it needs its own privacy ruling (apps/webapp/CLAUDE.md §Settings Takeover).
• A count per badge ("Claude ×3"), and a "+N" overflow after two badges.
• Filter or sort the Users table by connected app. This needs a server-side join, because the table pages on the server.
• Scope the map to the visible page's user ids if the payload grows past a few MB.
• Use one name for a nameless app on every surface: 'Unknown app' on the admin pages, 'Unnamed app' in Settings.
• Fix the existing silent errors on the Users page (users.tsx:97-101). A failed document-counts fetch shows 0 docs. A failed notification-subs fetch shows every channel off.
• Clean the raw client_name on the MCP page (apps/admin-dashboard/src/pages/mcp.tsx:77-83) with the same helper.
• Use originTextOf for the MCP page's registeredApps too. It still calls originOf, so a custom-scheme client shows the origin "null" there.
• Revoke OAuth consents in softDeleteUsers, so an erased account keeps no live grants.
• Revoke one person's grant from the admin dashboard. GoTrue's admin API has no endpoint for it today.