Motir

Estimate: 18m · Depends on: 1.3.1, 1.4.2

Add the two workflow tables to prisma/schema.prisma and ship one Prisma migration that creates both plus their RLS policies, using the same workspace-scoped app.workspace_id GUC pattern Story 1.6.4 established for job_run (see finding #33). Both tables carry an explicit workspaceId column + projectId FK + the Story-1.2 RLS gate, so the typed-status data inherits tenant isolation by structure, not by joins.

Tables and key constraints:

• workflow_status: (id, workspaceId, projectId, key, label, category, color, position, isInitial, createdAt, updatedAt). category is a Prisma enum StatusCategory { todo, in_progress, done } — the durable Jira-style three-bucket taxonomy. key is the machine-stable identifier work_item.status stores (e.g. 'todo', 'in_review'); unique per project (@@unique([projectId, key])). position is a Decimal(20,10) matching Story 1.4's column-shape rule (finding #18) — same fractional-indexing path the work-item ordering uses. isInitial is a boolean; partial unique index @@unique([projectId, isInitial]) where isInitial = true enforces exactly one initial status per project.
• workflow_transition: (id, workspaceId, projectId, fromStatusId, toStatusId, createdAt). @@unique([projectId, fromStatusId, toStatusId]) prevents duplicate transitions. The "any → any" project-policy is NOT stored as N² rows; it's a project-level workflow_policy_mode column on project (added in this same migration) with values restricted / open. open means transitions are unconstrained (the explicit transition rows are ignored at validation time); restricted consults the transition rows. Default restricted. This is the durable shape — Jira and Linear both have an "anything goes" project mode and a "guarded transitions" mode; storing the policy as a project column rather than a flag inside workflow_transition keeps the shape O(transitions) in storage and O(1) to check the policy.

RLS: both tables enable RLS + FORCE ROW LEVEL SECURITY (so even the table owner is gated, per Story 1.4.5's pattern). The policy mirrors work_item's: USING (workspace_id = current_setting('app.workspace_id')::uuid) + the system-admin escape hatch OR current_setting('app.system_admin', true) = 'true' (see finding #33). No FOR SELECT/INSERT/UPDATE/DELETE split — one policy per table covering all four, matching Story 1.6.4.

What this does NOT do: seed default rows (that's 2.2.2's job — done in application code, not a SQL INSERT in the migration, so the seed runs under the prodect_app role and gets the workspace_id GUC set correctly). Also does not change work_item.status's column type — it stays String for v1 portability, with integrity enforced by the service layer (see 2.2.4).

Acceptance criteria

• workflow_status + workflow_transition + project.workflow_policy_mode added in one Prisma migration; prisma migrate dev applies cleanly against a fresh DB.
• RLS policies created and FORCED on both tables; the app.workspace_id + app.system_admin GUC pattern mirrors job_run (finding #33).
• Partial unique index enforces exactly-one-initial-status-per-project; attempting a second initial-status insert fails with a constraint violation.
• @@unique([projectId, key]) enforces stable per-project status keys.
• An RLS proof test (mirroring tests/jobs/rls.test.ts) under SET LOCAL ROLE prodect_app demonstrates: workspace A's session sees only its own statuses; cross-workspace SELECT returns 0 rows; INSERT with a foreign workspaceId in the same row is rejected.
• No SQL INSERT in the migration for default rows (seeding is application-layer work in 2.2.2).

Context refs

• prisma/schema.prisma — Story 1.3 project, Story 1.4 work_item
• Story 1.6.4's migration add_job_run_dlq_and_rls — the canonical RLS + app.system_admin escape-hatch shape
• Story 1.4.5's FORCE ROW LEVEL SECURITY migration on work_item
• tests/jobs/rls.test.ts — the role-switch RLS-proof harness this Subtask mirrors
• motir-core/CLAUDE.md — 4-layer rule, repo-write contract
• Finding #18 — Decimal(20,10) position-column shape; finding #33 — GUC namespace