Motir

Estimate: 22m · Depends on: 2.5.1, 2.5.11

The backend the lazy Tree consumes (finding #57). Today getProjectTree loads the whole forest in one query and nests in JS — replace that with incremental reads: a listRoots (top-level issues of the project, paged + sorted) and a listChildren(parentId) (a node's direct children, paged + sorted), both workspace-gated (the explicit workspaceId filter, finding #26 — RLS is inert under the dev/CI superuser) and carrying a hasChildren flag per row so the client knows whether to render an expand chevron WITHOUT fetching. Sorting carries through as the ORDER BY of each read (siblings sort within their parent). Pure reads through Repository → Service → DTO; the legacy whole-forest read stays only if a non-lazy caller still needs it (else removed). NO schema change.

(PR #111 merged 2026-06-05, merge commit 6bee620 — the lazy-tree backend (finding #57). workItemRepository.findProjectTreeLevel(projectId, workspaceId, parentId|null, sort, {take, offset}) reads ONE level — roots (parentId IS NULL) or a parent's direct children — sorted via the whitelisted ISSUE_SORT_SQL + key-asc tiebreaker (total order → paging never skips/repeats), each row carrying an EXISTS hasChildren flag (chevron without loading the subtree). Explicit workspace+project gate (finding #26); fetches take+1 → hasMore (no COUNT). workItemsService.listRootIssues/listChildIssues → TreeLevelDto{rows,hasMore}; roots gate the project (ProjectNotFoundError), children gate the parent by workspace (WorkItemNotFoundError — no empty-list leak); clampTreePage caps a forged ?take (default 50, max 200). New WorkItemTreeRow/WorkItemTreeRowDto/TreeLevelDto + toWorkItemTreeRowDto. Two flagged deviations (rung-1 justified): (a) OFFSET paging + take+1 hasMore (consistent with the List 2.5.12) rather than the design-notes' "cursor" hint — keyset over an arbitrary multi-column sort is disproportionate; cursor → Epic 6; (b) lazy reads are the UNfiltered tree — a FILTERED tree keeps the context-preserving getProjectTree over the already-bounded result (lazy+context-preserving filter → Epic 6). Integration tests (real PG): roots/children paging+hasMore, hasChildren, sort-within-parent, empty leaf, project + parent workspace isolation. tsc/eslint/prettier clean; CI runs the real-PG suite. Gates 2.5.14.)

Acceptance criteria

• workItemRepository gains listRoots + listChildren (each: limit/offset, the whitelisted ORDER BY sort, the workspaceId gate, a per-row hasChildren); workItemsService exposes them returning DTOs.
• Cross-workspace project/parent id → not-found, never a leak (finding #26); an empty level → []; sort axes match the List's whitelist.
• Integration tests (real PG): roots paging, children paging, hasChildren correctness, sort within a parent, workspace isolation. No raw Prisma outside the repo; tsc/eslint/prettier clean.

Context refs

• lib/services/workItemsService.ts getProjectTree + lib/repositories/workItemRepository.ts (the forest read to replace; findProjectIssuesFlat as the sort/gate reference)
• lib/issues/issueListView.ts sort whitelist; motir-core/CLAUDE.md 4-layer + finding #26 gate
• The 2.5.11 design — the per-node page size + which columns sort