Motir

The data model + service core the whole of Epic 4 (Agile planning) rides on. Three things ship here, all backend: (1) the Sprint entity — name, goal, startDate, endDate, state (planned·active·complete), scoped to a project; (2) the issue→sprint association — a nullable work_item.sprint_id FK (null = the issue is in the backlog); and (3) a global backlog rank — work_item.backlog_rank, the single opaque fractional-index ordering (the Jira "Rank") that lets the backlog (Story 4.2) AND a sprint be drag-reordered off one field. On top of the rows, the service-layer state-machine RULES: the sprint transition guard (planned → active → complete, one-way — no skip, no reopen) and the one-active-sprint-per-project guard, both shipped + unit-tested HERE so Story 4.4's start/complete flows just compose them.

Sprint is project-scoped (justified rung-1 deviation — see the module header). A board in this product is a per-project read projection (Story 3.1), not Jira's cross-project filter view, so the sprint hangs off sprint.projectId (denormalized workspaceId too, matching the schema-wide tenancy-denormalization pattern on work_item / board). "One active sprint per board" (Story 4.4 stub / Story 4.5 prose) therefore resolves to one active sprint per PROJECT — enforced by a PARTIAL unique index WHERE state = 'active' on (project_id), exactly the raw-SQL pattern board_one_default_per_project / workflow_status_one_initial_per_project already use (Prisma's DSL can't express a filtered unique index). The backlog is the project's issues with sprint_id IS NULL, in backlog_rank order.

What 4.1 ships vs. what Story 4.4 ships (the clean seam). 4.1 owns the ENTITY and the RULES: sprint CRUD for a planned sprint (create / rename / edit goal+dates / delete), the issue↔sprint association writes (assign to sprint / move to backlog), the backlog rank writes (rank between two neighbours), the bounded backlog + sprint-issue READ queries the 4.2 UI binds to, and the pure transition-guard helper assertSprintTransition(from, to). Story 4.4 owns the lifecycle ORCHESTRATION that uses those rules: the start flow (scope-lock semantics, "board opens"), the complete flow (carry-over of unfinished issues back to the backlog / into the next sprint), and the sprint report — none of which ship here. 4.1 ships assertSprintTransition and the one-active guard so 4.4 composes them; 4.1 does NOT ship startSprint / completeSprint bodies (that would steal 4.4's scope) beyond what the guard tests need. Story 4.3 (estimation) adds the story-point field; 4.1 does not — point roll-ups are 4.3/4.6.

Backlog rank = one global fractional-index field, reusing positioning.ts (no new mechanism). The rank is the SAME opaque base-62 fractional-index String the work_item / board_column / workflow_status / board positions already use (lib/workItems/positioning.ts — keyForAppend / keyForPrepend / keyBetween), so a reorder is a single-row write and there is no Decimal / integer-renumber machinery to add. It is a separate ordering from work_item.position (which orders the issue TREE under its parent) — Jira's Rank is likewise orthogonal to the issue hierarchy. New issues get a backlog_rank appended at creation; the migration backfills every existing issue deterministically by (projectId, createdAt) so the ordering is total from day one.

Completeness / scale (finding #57 — plan the bounded shape now, not load-all). A real team's backlog is thousands of issues, so the backlog READ is cursor-paginated in rank order with an aggregate count (and a per-sprint committed-issue count) — never a "fetch every backlog row" read. The 4.2 backlog UI lazy-loads pages against this; 4.1 ships the bounded query, not a load-everything one. The association + rank writes are O(1) single-row writes (fractional index), never an N-row renumber.

4-layer + tenancy (CLAUDE.md). The Sprint FK and the work_item.sprint_id FK are modelled as Prisma @relations on BOTH sides with explicit onDelete (so prisma migrate dev reports "No difference detected" — the FK-drift lesson, bug-attachment-fk-migration-drift); reads/writes are repository single-ops (writes require tx); the service owns the transactions + DTO mapping + typed errors; the explicit application-layer workspaceId gate (finding #26) covers every sprint/backlog read & write; the sprint table gets the same pure-workspace RLS policy as board / workflow_status (non-null workspace_id, every write under an active workspace context, no system-admin escape hatch). Sprint and association changes record a work_item_revision row (reuse the Story 1.4.6 audit trail) inside the same transaction, so the activity feed (Story 5.5) and reporting (Epic 6) see sprint moves for free.

Out of scope (Epic-4 siblings / later): the backlog + sprint-planning UI, drag-to-reorder, drag-into-sprint, inline estimate (Story 4.2); story-point estimation + roll-ups (Story 4.3); the start/complete lifecycle flows, scope lock, carry-over, sprint report, "board opens" provisioning (Story 4.4); the Scrum board view + sprint header (Story 4.5); velocity + burndown charts (Story 4.6); a multi-value sprint history field on issues for reporting (Jira keeps one — v1 carries the single active sprint_id and lets the 1.4.6 revision trail record sprint changes; multi-sprint history is a later reporting concern); scrum-board CRUD/provisioning (Story 3.7 board CRUD — a view concern, not sprint data).

Verification

• Pull the Story branch, pnpm install, pnpm prisma migrate dev (applies the add_sprint_and_backlog_rank migration), pnpm db:seed, pnpm dev.
• Migration is clean (no drift): a second pnpm prisma migrate dev reports "No difference detected" — the Sprint FK and work_item.sprint_id FK are modelled as @relation on both sides (no spurious DROP CONSTRAINT, per bug-attachment-fk-migration-drift). pnpm prisma migrate status is up to date.
• pnpm test:coverage — Vitest (real Postgres) over the sprint state machine + association + rank + guards stays ≥90% per-file branch/fn/line on the new service/repository files (the CI coverage gate, motir-core-coverage-gate); empty-input guards on any new repo method have a direct test.
• State machine: assertSprintTransition allows planned→active and active→complete; rejects planned→complete (skip), complete→active / active→planned (reopen), and any self-transition, with the typed error.
• One-active guard: creating/activating a second active sprint in the same project fails on the partial-unique index (sprint_one_active_per_project); a different project may have its own active sprint concurrently.
• Association + backlog: assigning an issue to a sprint sets sprint_id and drops it from the backlog read; moving it back to the backlog nulls sprint_id and restores it in backlog_rank order; assigning an issue to a sprint in a DIFFERENT project is rejected (same-project guard).
• Rank: backlog_rank is the positioning.ts base-62 string; ranking an issue between two neighbours is a single-row write that lands it strictly between them; every pre-existing issue has a backfilled rank (the ordering is total).
• Bounded read (finding #57): the backlog query is cursor-paginated in rank order with a total count; it never selects every backlog row. pnpm db:seed:large (a project with thousands of backlog issues) → the backlog read returns one bounded page + the count, the rank writes stay O(1).
• Tenancy: a cross-workspace sprint/backlog read or write is denied by the finding-#26 workspaceId gate; the sprint RLS policy rejects access outside the active workspace context.
• Audit: assigning/removing a sprint or reranking writes a work_item_revision row in the same transaction (visible to the Story 5.5 activity feed).