Motir

Estimate: 20m

The extensible-schema substrate. Schema + migration + repo skeletons only.

CustomFieldDefinition — id, workspaceId, projectId, key (machine slug, @@unique([projectId, key]) — the stable handle revision diffs and Epic-6 predicates reference; generated from the label, immutable after create), label, fieldType ('text' | 'number' | 'date' | 'select' | 'user'), description?, position (fractional-index String @db.Text — the WorkflowStatus convention), timestamps. Index [projectId, position]. The project-scoped-config precedent is WorkflowStatus (key+label split, fractional position) — mirror it.

CustomFieldOption — id, fieldId (Cascade), label, position (fractional), archived (default false), timestamps; @@index([fieldId, position]). Options are ROWS, not a JSON config blob: orderable, renameable, FK-able from value rows (the archive/delete rules need referential integrity).

CustomFieldValue — typed-EAV, the Jira customfieldvalue shape: id, workspaceId, workItemId (Cascade — values die with the issue), fieldId (Cascade — team-managed field delete destroys values), and ONE populated per-type column: valueText String? @db.Text, valueNumber Decimal?, valueDate DateTime? @db.Date, valueUserId String? (FK → User, SetNull — a deleted user clears the value, never blocks), valueOptionId String? (FK → CustomFieldOption, Restrict — the service must clear/migrate values before an option hard-deletes; the DB backstops the only-when-unused rule). @@unique([workItemId, fieldId]) (one value per pair — upsert target). The Epic-6 contract: indexes [fieldId, valueOptionId], [fieldId, valueNumber], [fieldId, valueDate], [fieldId, valueUserId] (+ the unique covers by-item reads), and a schema-comment block documenting the JOIN-predicate sketch 6.1 compiles (JOIN custom_field_value v ON v.work_item_id = w.id AND v.field_id = ? WHERE v.value_option_id = ?). Every FK a two-sided @relation (CLAUDE.md rule).

Repo skeletons (single-op, writes require tx): definition CRUD + listByProject, countByProject; option CRUD + listByField, countByField, countValuesByOption; value upsert, deleteByWorkItemAndField, listByWorkItem(workItemId) (bounded by the 50-field cap), countByField (the delete-confirm number).

Acceptance criteria

• The three models exist with the exact scoping/uniques/indexes above; every FK is a two-sided @relation with the stated onDelete actions (value→option Restrict, value→user SetNull, value→workItem + value→field + option→field Cascade); prisma migrate dev re-run reports no drift.
• The Epic-6 predicate contract is documented (schema comment + a lib/dto or doc note 6.1 can cite): typed columns + the four [fieldId, value*] indexes + the JOIN sketch.
• Repo methods exist as single ops; Vitest (real Postgres) verifies the cascades (field delete removes values; issue delete removes values; option delete with values is DB-rejected), the one-value-per-pair unique, and empty-input guards (coverage gate).

Context refs

• prisma/schema.prisma — WorkflowStatus (the project-scoped-config precedent: key/label/position) + WorkItem/User; motir-core/CLAUDE.md (FK rule, required-tx)
• Jira's customfieldvalue typed-EAV schema (the verified storage precedent in the Story 5.3 description)
• lib/repositories/workflowStatusRepository.ts (or equivalent) — repo conventions for project-scoped config
• Story 6.1 stub — the downstream consumer of the predicate contract