Motir

The persistence layer for a project's own roles: one table, one nullable pointer from ProjectMembership, and the repository leaves every other card in this story goes through. It changes no behaviour on its own — nothing reads the new column until the resolution card lands — which is what makes it safe to ship first.

The model

model ProjectRoleDefinition {
  id          String     @id @default(cuid())
  workspaceId String     @map("workspace_id")
  projectId   String     @map("project_id")
  name        String
  basedOn     MemberRole @map("based_on")
  permissions String[]
  …timestamps, @@unique([projectId, name]), @@index([workspaceId]), @@index([projectId])
  @@map("project_role_definition")
}

Four shape decisions, each argued rather than defaulted:

• workspaceId is a real column, and RLS gates on it directly. ALTER TABLE … ENABLE + FORCE ROW LEVEL SECURITY and a single FOR ALL USING ("workspace_id" = current_setting('app.workspace_id', true)) WITH CHECK (…) policy — the pattern project_membership shipped, copied rather than re-derived. The true is missing_ok, so an unset GUC yields NULL and the row is hidden; FORCE so even the table owner is subject to it. No RLS join through project.
• permissions is a String[], not a join table. The catalog is code-owned and bounded (ROLE_GATED_PERMISSIONS), a role's set is read whole every time it is read at all, and no query asks "which roles hold X". A join table would add a row set with no reader. Values are catalog keys; the service is what validates them.
• There is NO based_on column (Yue, 2026-08-09). An earlier revision stored which built-in the author started from. It was provenance that never re-flowed — a claim about how the role was once authored rather than a fact about it — so nothing records it and nothing draws it. The editor still offers a built-in to START FROM; that seeds the grid in the browser and is not sent.
• ProjectMembership.roleDefinitionId is nullable with onDelete: Restrict, and that is the load-bearing one. Cascade would delete the MEMBERSHIP when a role is deleted; SetNull would silently drop everyone holding it back to whatever their role enum column says, which is the exact "silently promotes or strips them" failure the story exists to prevent. Restrict makes the database refuse, so the only way a role can be deleted is through the service's reassign path. A null pointer keeps meaning "this membership names a built-in through role", so no existing row is migrated and nobody's access changes on deploy. Modelled as a Prisma @relation, per CLAUDE.md.

The two columns move together, and the repository is what guarantees it. role is what levelGrants in lib/permissions/resolve.ts reads and what private gates on; roleDefinitionId decides what the membership actually grants. A membership on a custom role carries role = CUSTOM_ROLE_TIER (member, lib/permissions/builtinRoles.ts) — never a stale leftover value — and the only way to set one column is to set both in the same statement. At that tier the access level subtracts nothing, so a custom role grants exactly what it lists.

Scope boundary

In: the Prisma model, the migration (table + indexes + RLS + the membership column and its FK), projectRoleDefinitionRepository, the three additions to projectMembershipRepository that every writer in this story shares, and their tests. Out: any validation of the permission values, the name or the count — that is the service's, and a repository that validates is a second policy implementation; any READ of the new column by the resolution or the catalog, which are their own cards; the caps constants, which live with the service that enforces them.

Acceptance criteria

• prisma/schema.prisma carries ProjectRoleDefinition as above — with no based_on column — and ProjectMembership.roleDefinitionId as a nullable @relation with onDelete: Restrict; pnpm prisma generate is clean (the generated client is gitignored, which IS this repo's convention).
• The migration creates the table, @@unique([projectId, name]), both indexes, the FK, and ENABLE + FORCE row-level security with the workspace_id FOR ALL policy in both USING and WITH CHECK — verified by a test that drops to the non-bypass app role and shows a row from workspace A is invisible under workspace B's GUC, and that an INSERT naming a foreign workspace_id is refused.
• pnpm prisma migrate deploy against a database holding existing project_membership rows leaves every one of them with role_definition_id IS NULL and their role untouched — asserted, not assumed.
• A test proves the Restrict FK: deleting a ProjectRoleDefinition that a membership points at is refused by the database, and the membership survives.
• lib/repositories/projectRoleDefinitionRepository.ts exposes single-Prisma-op leaves — findManyByProject, findById, countByProject, create, update, delete — with tx required on every write, per the 4-layer convention.
• lib/repositories/projectMembershipRepository.ts gains countByRoleDefinition, setRoleDefinition and reassignRoleDefinition, all tx-required. setRoleDefinition and reassignRoleDefinition write role_definition_id AND role in the SAME statement — assigning a custom role sets role to CUSTOM_ROLE_TIER, and moving a membership to a built-in clears the pointer and sets role to that built-in. Neither column is writable alone.
• Those three methods are the ONLY write paths for role_definition_id in the codebase — the role service and the members service both call them rather than reimplementing either — and a test asserts the paired-column invariant holds after each: a membership with a non-null role_definition_id always has role = CUSTOM_ROLE_TIER.
• A duplicate (projectId, name) insert raises P2002, and the repository lets it through untranslated — translating it into a domain error is the service's job.
• No file outside lib/repositories/ imports the Prisma client for this table.

Context refs

• prisma/schema.prisma — model ProjectMembership, enum MemberRole, and the surrounding project-scoped models.
• prisma/migrations/20260608224711_add_project_membership_and_roles/migration.sql — the RLS block to copy verbatim, including its comments on FORCE, missing_ok and the inherited default grants.
• lib/repositories/projectMembershipRepository.ts — the repository being extended, and countByRole, whose grouped-read shape countByRoleDefinition follows.
• lib/permissions/resolve.ts — levelGrants, whose isProjectMember / hasProjectMembership arms are why role must track basedOn.
• lib/repositories/customFieldOptionRepository.ts — a shipped repository whose in-use guard and P2002 posture are the pattern.
• motir-core/CLAUDE.md — the 4-layer Route → Service → Repository → Prisma split, the tx-required-on-writes rule, and the FK-as-@relation rule.
• lib/permissions/builtinRoles.ts — ROLE_GATED_PERMISSIONS, the value domain the permissions array holds.
• The story's § The shape, which pins the RLS posture and the built-in-only base.