Motir

Estimate: 32m

Add the persistence layer for sprints + the backlog rank, as ONE Prisma migration, modelled so prisma migrate dev is drift-free.

Sprint model (@@map("sprint")): id (cuid), workspaceId @map("workspace_id") + projectId @map("project_id") (the project the sprint belongs to — see the module-header scope decision; denormalized workspaceId matches board / work_item), name (String, required — default-named "Sprint N" by the service via sequence), goal (String? @db.Text, the sprint goal), state SprintState @default(planned), startDate DateTime? + endDate DateTime? (the sprint window — nullable on a planned sprint, set/edited by the service; Story 4.4 stamps them on start), completedAt DateTime? (set by 4.4 on complete), sequence Int (per-project monotonic ordinal for the default name + stable listing), createdAt / updatedAt. Relations: workspace/project (onDelete: Cascade, like board); a workItems WorkItem[] back-relation. Indexes: @@index([workspaceId]), @@index([projectId, state]) (resolve the active sprint + list a project's sprints).

SprintState enum (@@map("sprint_state")): planned, active, complete — matching the plan's vocabulary (Jira API's future/active/closed). Values exist now so Story 4.4 adds no enum ALTER.

WorkItem additions: sprintId String? @map("sprint_id") + a sprint Sprint? @relation(fields: [sprintId], references: [id], onDelete: SetNull) — SetNull so deleting/clearing a sprint NEVER deletes issues (they fall back to the backlog); and backlogRank String? @db.Text @map("backlog_rank") — the global fractional-index rank (the same positioning.ts base-62 string the other position columns use; SEPARATE from work_item.position, which orders the tree). Index @@index([projectId, sprintId, backlogRank]) to serve both the backlog read (sprintId IS NULL, rank order) and a sprint's ranked issues off one composite.

Both FK sides modelled (CLAUDE.md FK rule + bug-attachment-fk-migration-drift): the Sprint.workItems ↔ WorkItem.sprint relation is declared on BOTH sides with the explicit onDelete: SetNull, so the generated migration matches schema.prisma and a re-run reports "No difference detected" (no raw-SQL-only FK, no spurious DROP CONSTRAINT on the next migrate).

Raw-SQL tail of the migration (Prisma DSL can't express these — same pattern as the 3.1 / 3.7 board migrations):

• One-active-per-project partial-unique index sprint_one_active_per_project: CREATE UNIQUE INDEX … ON sprint (project_id) WHERE state = 'active' — the data-layer guard behind "one active sprint per project" (mirrors board_one_default_per_project).
• RLS policy on sprint: the pure-workspace gate (non-null workspace_id, every read/write under the active workspace context), copied from the board / workflow_status policy — NO system-admin escape hatch.
• Backlog-rank backfill: assign every existing work_item a backlog_rank deterministically in (project_id, created_at, id) order (monotonic base-62 keys, the same scheme positioning.ts emits) so the ordering is total immediately. Leave the column nullable (new issues get a rank at creation in 4.1.4); the backfill makes the existing set total.

Acceptance criteria

• schema.prisma gains the Sprint model + SprintState enum + the work_item.sprint_id / backlog_rank columns and indexes described above; the Sprint.workItems ↔ WorkItem.sprint FK is modelled on BOTH sides with onDelete: SetNull.
• One migration add_sprint_and_backlog_rank creates the table, enum, columns, indexes, the sprint_one_active_per_project PARTIAL unique index, the sprint RLS policy, and the backlog_rank backfill. pnpm prisma migrate dev applies cleanly and a SECOND run reports "No difference detected" (no FK drift).
• The partial-unique index rejects a second active sprint in the same project but allows one active sprint per project across different projects; the backfill leaves every pre-existing issue with a non-null backlog_rank.
• pnpm prisma generate + pnpm typecheck + pnpm build pass; no other model changes.

Context refs

• prisma/schema.prisma model WorkItem (lines ~299), model Board + enum BoardType (~607) — the tenancy-denormalization + partial-unique (board_one_default_per_project) + RLS patterns to mirror
• prisma/sql/* board/workflow_status migrations — the raw-SQL partial-unique-index + RLS-policy precedents to copy
• lib/workItems/positioning.ts — the base-62 fractional-index scheme the backfill emits (so new ranks interleave with backfilled ones)
• motir-core/CLAUDE.md (FK-as-@relation on both sides; the migration FK-drift rule) + the bug-attachment-fk-migration-drift precedent
• Jira sprint states (future/active/closed) as the mirror for planned/active/complete