Measure how often writers type @username in pad text #348

Parent

#328.

What to build

A pad @ list is designed but waits on evidence. Nobody knows if writers type @username in pad text today. This issue produces one set of counts from production data. The counts decide if the pad @ list (#349) opens. The proposed gate: pad mentions reach 10% of chat mentions over the same pads and the same 90 days.

Acceptance criteria

• A one-off Bun script exists outside the repository. It reads a local export and prints counts only.
• The script ran against the local databases and passed the dry-run checks in Verify.
• The maintainer ran the export and the script against production data. No snapshot was decoded on a production host.
• A comment on this issue holds the totals, the ratio, and the full script text. It holds no slug, title, username, message text or per-pad row.
• The comment states the gate verdict: open or closed, against the threshold the maintainer confirmed.

Blocked by

None — can start now.

Agent brief

Type: HITL — the maintainer must run the production export and the script, because only the maintainer has production access. The maintainer also confirms or changes the 10% threshold. Both the counts and the verdict go in a comment on this issue.

Category: ruling

Current behavior:

• Each saved pad version is a row in the Prisma table Documents (data is the Yjs update, createdAt is the save time). See apps/hocuspocus.server/prisma/schema.prisma, models Documents and DocumentMetadata.
• DocumentMetadata has isPrivate and deletedAt.
• The server decodes a snapshot with Y.applyUpdate(ydoc, bytes) then TiptapTransformer.fromYdoc(ydoc, 'default') (apps/hocuspocus.server/src/lib/documentGridPreview.ts:153-154).
• A pad's chat workspace id is DocumentMetadata.documentId, with the same casing. public.workspaces.id, public.workspace_members.workspace_id and public.channels.workspace_id hold it. Checked on local data: 122 of 122 shared ids match exactly, and 0 match only after lowercasing. Do not lowercase the join key; workspaces.slug is the lowercase copy.
• Active members are workspace_members rows with left_at IS NULL. Usernames live in public.users.username.
• Chat mentions use one token rule in SQL (packages/supabase/scripts/10-func-notifications.sql:69): (?:^|[^A-Za-z0-9_-])@([A-Za-z0-9_-]+). A token names a user only on an exact, case-sensitive username match.
• Copy to Doc writes a paragraph whose first text carries a hyperlink mark with an href containing ?act=ch (apps/webapp/src/components/chatroom/components/MessageCard/hooks/useCopyMessageToDocHandler.ts:17, :31-47). The chat body follows as plain text, so it can hold @username.

Desired behavior: one comment on this issue with these totals, over pads in scope:

• Pads in scope, and pads with at least one pad mention.
• Paragraphs scanned, and paragraphs with at least one pad mention.
• Chat messages in scope, and chat messages with at least one mention.
• The ratio: paragraphs with a pad mention divided by chat messages with a mention.
• The verdict against the confirmed threshold.

Definitions the script must follow:

• Window: the 90 days before the run.
• Pads in scope: isPrivate = false, deletedAt IS NULL, and at least one Documents row in the window. Private pads are out of scope. Filter them in the export query, so their rows never leave production.
• Snapshot: the newest Documents row of each pad in scope.
• Paragraph: every paragraph node at any depth, including those in lists, tables and blockquotes.
• Pad mention: a token, by the rule above, that equals the username of an active member of that pad's workspace. Skip system. Skip a paragraph whose first text node carries a hyperlink mark with ?act=ch in its href.
• Chat mention: the same token rule, over messages.content in channels whose workspace_id is a pad in scope. Count only rows with created_at in the window, deleted_at IS NULL and type IS DISTINCT FROM 'notification'. The username must be an active member of that workspace, not system, and not everyone.

Stop rules:

• If zero pads in scope resolve any active member, the export or the join is wrong. Report that and give no verdict.
• If the chat count is zero, report the raw counts and give no ratio.

Where to start:

• Export from the Prisma database: DocumentMetadata."documentId" and the newest Documents.data per pad in scope.
• Export from the Supabase database: active workspace_members joined to users (workspace_id, username). Also messages.content joined to channels.workspace_id, for pads in scope and the window.
• Write the script in a temporary folder outside the repository. Import yjs and @hocuspocus/transformer by absolute path from apps/hocuspocus.server/node_modules in this checkout. The root node_modules does not hold them. Or install both in that folder with bun add.
• Local data for a dry run: the Prisma database runs in docker container docsy-postgres-local (database docsplus, user from DATABASE_URL in the root .env.local). Query it with docker exec -i docsy-postgres-local psql, and strip the ?connection_limit=… params from the URL. Supabase runs in supabase_db_docsplus_supabase (user postgres). See AGENTS.md §Learned Workspace Facts.

Line numbers are hints as of 2026-09-28; the agent searches by symbol.

Rules that apply:

• Never decode snapshots inside a production container. It shares memory with the live server. Run only small count queries there, and stream the rows out for a local decode.
• The script prints counts only. It never prints or saves text, usernames, titles or slugs. Delete the local export after the run.
• AGENTS.md §Standalone Bun Scripts, and §Package Manager.
• No test file. AGENTS.md §Test Policy: this is a one-off count, not a regression or dense branching logic.
• The repository is public. The comment holds totals only.

Verify:

• Smoke-check the script: bun build <script>.ts --target=bun --outfile=/tmp/out.js.
• Dry run on local data. Start the stack with make dev-local, sign in, and open a new public pad. Opening it signed in makes you an active member of its workspace (useJoinWorkspace). Type @<your username> in one paragraph. Send one chat message with the same token. The script must count one pad mention and one chat mention for that pad.
• Put a Copy to Doc paragraph with @<username> in the same pad. The pad count must not change.

Out of scope

• Building any part of the pad @ list. Add a pad @ list where Members open a Comment and Headings insert a link #349 owns that.
• Notifications from pad text. They are cut; do not propose them.
• Committing the script to the repository.