Motir

Estimate: 18m

The persistence layer for threaded comments and queryable mentions. Pure schema + migration + repository skeleton — no service logic, no UI.

Comment model: id (cuid), workspaceId (the finding-#26 scoping gate every read filters on), workItemId, authorId, parentCommentId (nullable self-relation — the single-level threading decision: a root has null, a reply points at a root; depth >1 is rejected in the service, 5.1.2), bodyMd (@db.Text — Markdown, same substrate as work_item.descriptionMd), editedAt (nullable — set on body edit, drives the "Edited" tag; distinct from updatedAt which any write touches), createdAt, updatedAt. Relations: workItem (onDelete: Cascade — comments die with the issue), author (Restrict — a user with comments cannot be hard-deleted silently), parent/replies self-relation (onDelete: Cascade — deleting a root deletes its thread; the deliberate 5.1.2 decision). Indexes: [workItemId, createdAt] (the paged list read), [parentCommentId].

CommentMention model: id, commentId (Cascade), mentionedUserId, createdAt, with @@unique([commentId, mentionedUserId]) — one row per mention regardless of how many times the token repeats. This is the queryable "mentions me" substrate Epic 6 filters on and 5.1.6 fans notifications out from.

Every FK modelled as a Prisma @relation on BOTH sides (forward field + back-relation: WorkItem.comments, User.authoredComments, User.commentMentions) with explicit onDelete actions — never raw-SQL-only (the CLAUDE.md migration rule; the 2.3.7 attachment-FK drift lesson). prisma migrate dev after the migration reports "No difference detected".

Repository skeleton (lib/repositories/commentRepository.ts + commentMentionRepository.ts): single-Prisma-op methods — create/update/delete requiring tx, findById, the cursor-paged listByWorkItem(workItemId, { cursor, take, order }) + countByWorkItem, createMany/deleteByCommentId for mentions. No business logic, no transactions (services own those — 5.1.2).

Acceptance criteria

• prisma/schema.prisma adds Comment (workspaceId, workItemId, authorId, nullable parentCommentId self-relation, bodyMd @db.Text, nullable editedAt, timestamps) and CommentMention (@@unique([commentId, mentionedUserId])), every FK a two-sided @relation with explicit onDelete (workItem Cascade, author Restrict, parent Cascade, mention→comment Cascade); a follow-up prisma migrate dev reports no drift.
• Indexes exist for the paged list read ([workItemId, createdAt]) and the thread read ([parentCommentId]).
• commentRepository / commentMentionRepository expose single-op methods with required-tx writes per the 4-layer contract; reads support cursor + take + order.
• Vitest (real Postgres): cascades verified — deleting a work item removes its comments; deleting a root removes its replies + mention rows; the unique mention constraint holds; empty-input guards on the new repo methods have direct tests (the coverage gate).

Context refs

• prisma/schema.prisma — WorkItem / User / Workspace models + the existing index/naming conventions; the Attachment model as the recent FK-relation exemplar
• motir-core/CLAUDE.md — the 4-layer contract (required-tx writes; single-op repos) + the FK-as-@relation migration rule (the 2.3.7 drift lesson)
• lib/repositories/workItemRepository.ts — repository shape + cursor/paging precedent
• Story plan: single-level threading + hard delete + the mention substrate are the Jira-verified decisions recorded in the 5.1 story description