Estimate: 22m · Depends on: 1.4.2
Ship the WorkItemLink table — the many-to-many join that models dependencies and other inter-issue relationships. Per the "Why work_item_link" section above: a JSON depends_on column on the row was the rejected shortcut; a separate join table is the durable shape every comparable tool uses, and it lands HERE (not Epic 5) because the AI Planning Layer's ready-set engine is unbuildable without indexed dependency queries.
Schema:
model WorkItemLink {
id String @id @default(cuid())
workspaceId String // RLS gate (denormalized from fromItem for RLS speed)
fromId String
toId String
kind WorkItemLinkKind
createdById String
createdAt DateTime @default(now())
fromItem WorkItem @relation("LinksFrom", fields: [fromId], references: [id], onDelete: Cascade)
toItem WorkItem @relation("LinksTo", fields: [toId], references: [id], onDelete: Cascade)
createdBy User @relation(fields: [createdById], references: [id], onDelete: Restrict)
workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
@@unique([fromId, toId, kind]) // no duplicate links of the same kind
@@index([toId, kind]) // reverse lookup: "what does B unblock / what blocks B?"
@@index([fromId, kind]) // forward lookup: "what does A depend on?"
@@index([workspaceId]) // workspace RLS
@@map("work_item_link")
}
enum WorkItemLinkKind {
is_blocked_by // fromItem is_blocked_by toItem ⇒ "A depends on B" → from=A, to=B
relates_to // symmetric soft link
duplicates // fromItem duplicates toItem
clones // fromItem is a clone of toItem
}
Direction convention (Jira-style): a row reads as "fromItem toItem". For "A depends on B, C, D" — three rows are written, all fromId=A, toId IN (B,C,D), kind=is_blocked_by. The forward query ("A's blockers") selects on fromId=A AND kind=is_blocked_by; the reverse ("what's blocked by B") selects on toId=B AND kind=is_blocked_by. Both queries are O(log n) via the dedicated indexes.
Why workspaceId denormalized onto the link row: RLS needs to gate links by workspace. Without the denormalized column, the policy would have to join to work_item on every read — adds a join + makes the policy harder to reason about. Denormalize and enforce consistency at write time (the service layer asserts fromItem.workspaceId === toItem.workspaceId and writes that workspaceId into the link row; a trigger ALSO validates this on INSERT/UPDATE as the structural backstop).
Cross-project links allowed, cross-workspace links forbidden. Real teams have epics whose stories live in sibling projects (e.g., a motir-ai epic blocks a motir-core story — same workspace, different projects). The RLS policy gates by workspace only. The project GUC, when set, narrows reads of work items but not of links — a link query against a project context returns links where either endpoint matches the active project (so the dependency badge can render in a project-scoped board view).
Cycle prevention on is_blocked_by: A is_blocked_by B is_blocked_by A is incoherent. A Postgres trigger fires on INSERT/UPDATE of any is_blocked_by row, walks the blocker chain via recursive CTE, and rejects cycles with SQLSTATE 23514 + WI_LINK_CYCLE marker (translated to WorkItemLinkCycleError at the repository edge). relates_to is symmetric — no cycle check needed; we additionally write the reciprocal row automatically inside the same transaction (so "A relates_to B" produces two rows: A↔B and B↔A, both visible to the both-directions UI). duplicates and clones are directional but not cycle-prone in practice; cycle check is scoped to is_blocked_by only.
What you'll do: Extend prisma/schema.prisma with WorkItemLink + WorkItemLinkKind + the back-relations on WorkItem (linksFrom WorkItemLink[] @relation("LinksFrom") + linksTo WorkItemLink[] @relation("LinksTo")). Generate migration add_work_item_links; append the cycle-prevention trigger SQL and the workspaceId-consistency trigger SQL to the migration file. Add lib/repositories/workItemLinkRepository.ts with single-Prisma-op leaves: create(data, tx), delete(id, tx), findByFromItem(fromId, kind?), findByToItem(toId, kind?), findById(id). All writes require tx. Add lib/workItems/linkErrors.ts with WorkItemLinkCycleError, CrossWorkspaceLinkError, DuplicateLinkError (the unique constraint translates to this), SelfLinkError (fromId === toId is rejected by trigger). Add lib/dto/workItemLinks.ts + lib/mappers/workItemLinkMappers.ts. Service-layer linking methods land in 1.4.4 (alongside the rest of the work-item service surface) — this Subtask stops at the repository edge so 1.4.4 can own all transactional work-item business logic in one place.
WorkItemLink + WorkItemLinkKind in prisma/schema.prisma with the verbatim field set + relations + indexes above. WorkItem gets the two back-relations.add_work_item_links applies cleanly. Includes: the table, the unique + secondary indexes, the cycle-prevention trigger function (scoped to is_blocked_by rows), the workspaceId-consistency trigger (rejects rows where the from/to items belong to different workspaces or where the row's workspaceId mismatches the from item), and the self-link trigger (fromId = toId rejected). Down-migration reverses all of it.is_blocked_by (e.g., A blocks B, B blocks A); cross-workspace links; self-links; workspaceId mismatch between the link row and the from item. Each failure mode uses a distinct SQLSTATE 23514 message marker.workItemLinkRepository exports the methods above; all writes require tx: Prisma.TransactionClient. Trigger errors are translated to the typed errors in lib/workItems/linkErrors.ts. The Prisma P2002 unique-violation is translated to DuplicateLinkError.findByFromItem / findByToItem accept an optional kind filter. With no kind, returns all link kinds for the endpoint. Pagination not required at this layer (links per item are bounded; service layer can paginate if needed).lib/dto/workItemLinks.ts exports WorkItemLinkDto. Mapper converts Prisma rows; never returns raw Prisma rows past the public boundary.create persists; cycle rejection (A→B then B→A on is_blocked_by); cross-workspace rejection; self-link rejection; duplicate-link rejection on the unique constraint; findByFromItem + findByToItem return the right rows with kind filtering.db.* / $transaction calls inside the repository (4-layer rule).pnpm prisma generate && typecheck && lint && format:check && build && test. Existing suite stays green.motir-core/CLAUDE.md — 4-layer rule (auto-loaded)prisma/schema.prisma — current WorkItem model from 1.4.2 (this Subtask adds back-relations)prisma/sql/work_item_triggers.sql from 1.4.2 — the trigger-writing pattern (cycle-check recursive CTE shape, SQLSTATE 23514 + message markers)lib/repositories/workItemRepository.ts + lib/workItems/errors.ts from 1.4.2 — the repository pattern + error-translation pattern to mirror