Motir

Estimate: 22m

The persistence layer for in-app notifications. Pure schema + migration + repository skeleton — no fan-in logic (5.7.3), no service (5.7.4), no UI. This is the model the 5.7.3 job WRITES and the 5.7.4 service READS.

Notification model: id (cuid), workspaceId (the finding-#26 scoping gate every read filters on), recipientUserId (the user this notification is FOR — every read is scoped to the session user), type (an enum/string discriminator: mentioned, commented, assigned, transitioned (the 5.4 seam), … — the event-type axis the 5.7.6 preference matrix keys on), category (direct | watching — the Jira Direct/Watching drawer split), workItemId (nullable — the deep-link target; nullable so non-item notifications remain modellable), actorId (nullable — who caused it; rendered as the row avatar), data (Json — the denormalized render payload: the summary nouns the row needs without a join storm — issue key + title, comment excerpt, from→to status — captured at fan-in so the feed read is a single-table scan), readAt (nullable — set on mark-read; the blue-dot / greyed driver), createdAt. Relations: recipient (User, onDelete: Cascade — a user's notifications die with them), actor (User, nullable, onDelete: SetNull), workItem (nullable, onDelete: Cascade — a deleted issue's notifications go with it).

Indexes for scale (finding #57). The feed read is [recipientUserId, createdAt] (cursor-paged from most-recent, per recipient). The unread count must be cheap: a partial index on (recipientUserId) WHERE "readAt" IS NULL (Postgres partial index over the unread predicate) so the badge aggregate is an index-only count, never a sequential scan — the durable shape for a table that grows unbounded per active user. A @@unique on the idempotency key (see 5.7.3) prevents a replayed event double-writing a row.

Every FK modelled as a two-sided Prisma @relation (forward field + back-relation: User.notifications, User.actedNotifications, WorkItem.notifications) with explicit onDelete — 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/notificationRepository.ts): single-Prisma-op methods — createMany requiring tx (the job writes a fan-out batch in one tx), findById, the cursor-paged listByRecipient(userId, { cursor, take, category? }), countUnreadByRecipient(userId) (the partial-index aggregate), markRead(id, tx) and markAllReadByRecipient(userId, tx) (a single bulk updateMany over the unread set — NOT a per-row loop). No business logic, no transactions (services own those — 5.7.4).

Acceptance criteria

• prisma/schema.prisma adds Notification (workspaceId, recipientUserId, type, category, nullable workItemId, nullable actorId, data Json, nullable readAt, createdAt) with every FK a two-sided @relation + explicit onDelete (recipient Cascade, actor SetNull, workItem Cascade); a follow-up prisma migrate dev reports no drift.
• The feed index [recipientUserId, createdAt] exists AND a partial unread index (WHERE "readAt" IS NULL) backs countUnreadByRecipient — verified by an EXPLAIN (or a documented migration note) showing the count uses the partial index, not a seq scan; the idempotency-key @@unique exists.
• notificationRepository exposes single-op methods with required-tx writes per the 4-layer contract; listByRecipient supports cursor + take + category; markAllReadByRecipient is one updateMany, not a loop.
• Vitest (real Postgres): cascade verified (deleting a user / work item removes its notification rows; deleting the actor sets actorId null); the idempotency unique holds; countUnreadByRecipient ignores read rows; empty-input guards on the new repo methods have direct tests (the coverage gate).

Context refs

• prisma/schema.prisma — WorkItem / User / Workspace models + the index/naming conventions; the 5.1 Comment / CommentMention models as the recent two-sided @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/commentRepository.ts — the cursor-paged listBy… + countBy… shape to mirror; finding #57 (paged + cheap count, never load-all)
• Story 5.7 description — the badge-seen-count vs row-readAt distinction + the Direct/Watching category split the schema encodes