Authoritative reconciliation of frozen preset roles into existing tenants¶

Deployment-scope supersession (2026-08-28): the frozen-only function remains as direct-seed safety, but hosted deployments now finish with the authoritative all-preset transaction defined by 2026-08-28-authoritative-preset-role-reconciliation-iteration-1-design.md. Its earlier editable-preset preservation rule no longer describes post-deploy state.

1. Problem distillation¶

Preset roles are cloned per-tenant at provisioning; seeding thereafter is additive-only (cloneRolesIntoTenant / backfillMissingPresetRoles never delete a RolePermission, and seedGlobalRolePresets only adds/updates). So when a preset's grant set is narrowed in code (e.g. the teacher role losing teachers.employment/documents/health per the self-service spec), tenants provisioned earlier retain the old, broader grants indefinitely.
The reverse gap exists too: a new scope/entity added to the catalogue never reaches an existing tenant's admin (or other frozen) role, because backfill only creates missing roles, not missing grants on roles a tenant already has.
We want baseline changes to propagate to existing tenants without disturbing per-tenant PATCH /roles/:key customizations. This iteration solves that for the roles where the constraint is trivially satisfiable: the frozen roles, which no one can customize.
The frozen set is exactly the roles RoleGrantsService.assertEditable rejects: admin (ADMIN_FROZEN) + PROFILE_COUPLED_ROLE_KEYS = teacher, referent, student (PROFILE_COUPLED). Code is their sole source of truth, so the seed may add, update, and prune their grants freely.

Success criteria (observable behavior that proves this works): - After a deploy/seed, a tenant provisioned before the teacher narrowing no longer has RolePermission rows for teachers.employment/documents/health on its teacher role. - After a deploy/seed, a tenant provisioned before a new scope was added to the catalogue does have the corresponding grant on its admin (and any other baseline-granted frozen) role. - Editable presets (principal, hr, secretary, department_head, curriculum_coordinator) — global and tenant-scoped — are byte-for-byte unchanged by the reconcile; a tenant's PATCH /roles/:key edits survive. - The reconcile is idempotent: a second consecutive run performs zero writes.

Non-goals (in-scope-shaped things this iteration is explicitly not doing): - Propagating baseline changes to editable presets while preserving customizations (needs a baseline snapshot / per-grant provenance — deferred, see §9). - Any new endpoint, standalone CLI, or dry-run inspection mode (chose auto-only in the seed prologue). - Any schema change to Role / RolePermission / RoleActionPermission.

2. Patterns survey¶

Analogous module/spec	What we'd borrow	What doesn't fit
`src/permissions/role-clone.ts` — `cloneRolesIntoTenant`	The "read the global preset row (`tenantId=null`) with its `permissions`/`actionPermissions`, upsert into a tenant" loop is the exact mechanism; the new function is this plus a scoped delete pass, gated to frozen keys.	`cloneRolesIntoTenant` is additive-only and runs over all preset keys; we add pruning and restrict to frozen keys.
`src/permissions/role-clone.ts` — `backfillMissingPresetRoles`	Cross-tenant iteration (`prisma.tenant.findMany`) executed in the Tier-1 prologue in every env incl. prod; the new step slots in right after it and reuses the "runs in prod, touches real tenants" placement.	Backfill only creates missing roles and explicitly never touches existing grants; we do the opposite (sync grants on existing roles). Complementary, non-overlapping.
`prisma/seed/helpers/seed-role.ts` — `seedRoleForTenant`	The authored grant set for a role is already computed here (`visibleNative` ∪ others-scopes ∪ actions). Adding an opt-in prune pass after the upserts makes the global frozen rows authoritative using the set already in hand — no re-derivation.	The helper is shared by editable presets too; the prune must be opt-in (`prune?: boolean`) and passed only for frozen-role calls.
`src/permissions/role-grants.service.ts` — `assertEditable`	Defines the frozen set (`admin` + `PROFILE_COUPLED_ROLE_KEYS`) — the exact predicate that gates which roles the seed may prune. The reconcile's frozen-key list is this predicate, keeping ownership and editability in perfect symmetry.	It's a per-request guard; we reuse only the key set, not the throw.
`prisma/seed/helpers/expected-preset-grants.ts` + `test/rbac-grants.db-sync.e2e-spec.ts`	The independently-derived expected grant map per role is the drift oracle; extend the db-sync spec to assert every tenant's frozen roles match it exactly (no missing, no stale).	Today the oracle is checked against the global presets; we extend coverage to tenant clones.

3. Architecture mapping¶

Primitive	Apply?	How	Justify
Tenant scope	yes	Iterates `prisma.tenant.findMany()` and writes tenant-scoped `Role`/`RolePermission` rows, exactly like `backfillMissingPresetRoles`. Runs in the seed context (DB owner), which is not RLS-constrained.	Reconciliation is inherently cross-tenant platform maintenance; there is no single caller tenant.
Academic-year scope	no	n/a — roles and their grants are not academic-year-scoped.	Role/permission rows carry no `academicYearId`.
RBAC entity key	none	No new entity key.	This is seed/provisioning machinery, not a domain entity.
Scopes	none	No new scopes; reconcile mirrors the existing frozen-role grant rows (native and others-scopes uniformly — both are `RolePermission` rows keyed by `scopeId`).	Behavior change only; no catalogue delta.
Actions	none	Mirrors existing `RoleActionPermission` rows; no new action keys (`read`/`update` remain implicit).	"
Service base	custom (seed helper)	A plain exported function in `src/permissions/role-clone.ts` typed on `PrismaClient` / `Prisma.TransactionClient`, mirroring its two siblings. Not a NestJS service.	Consistent with the existing provisioning-seam functions in the same file.
`queries.ts` shape	n/a — uses `prisma` directly	The sibling functions in `role-clone.ts` already call `prisma.role.findMany` / `rolePermission.upsert` inline; follow that local convention rather than introducing a `queries.ts`.	Provisioning seam predates the per-module `queries.ts` convention and is not a request-path module.
Error codes	none	Seed-time function; failures throw raw and abort the seed.	No user-facing surface.
DTO conventions	n/a — no HTTP surface		No endpoint.
File-backed sub-resources	n/a		Not file-related.
Custom fields	n/a (handled transparently)	Others-scope (custom-field) grants are ordinary `RolePermission` rows and are mirrored by the same loop; no special-casing.	`pickCustomFields` is a read-path concern, unrelated.
Profile completeness	no	n/a — unrelated to person completeness.

4. Data model plan¶

Schema deltas¶

None. No table/column/FK/enum changes. The only code-level "delta" is an opt-in prune?: boolean field on the existing SeedRoleInput type in seed-role.ts.

Migration shape¶

Additive / destructive / renaming: none — no migration. Row deletions performed by the reconcile are runtime data operations on RolePermission/RoleActionPermission, not schema changes.
Data backfill: the reconcile itself is the data reconciliation; it runs in the seed prologue, not as a Prisma migration.
Hazards from chapter 12 checklist: n/a — no migration.sql generated.

Indexes and uniqueness¶

None added. Relies on existing @@unique compounds roleId_scopeId (RolePermission) and roleId_actionId (RoleActionPermission) for the upsert where clauses and scoped deletes.

5. API surface¶

n/a — no HTTP surface. The feature is a function invoked in the Tier-1 seed prologue (prisma/seed.ts).

Swagger considerations¶

n/a — no controller, no OpenAPI delta.

6. RBAC seed plan¶

Seed file	Delta
`PermissionScope` (rbac-catalogue.ts)	none
`PermissionAction` (rbac-catalogue.ts)	none
`ScopeFieldMapping` (rbac-catalogue.ts)	none
Role grants (roles.ts)	No new (entity, scope) or (entity, action) grants authored. Change is behavioral: the four frozen-role calls in `seedGlobalRolePresets` (`admin`, `teacher`, `referent`, `student` via `seedProfileCoupledPresetsForTenant`) pass `prune: true`, making their global rows authoritative.
`*_SCOPES` runtime constant	none — no scope-field surface changes.

Wiring delta (code, not catalogue): - prisma/seed/helpers/seed-role.ts: add prune?: boolean to SeedRoleInput; after the native + others upsert loops and the action loop, when prune is set, delete any RolePermission/RoleActionPermission row on the role whose scopeId/actionId is not in the authored set just written. - prisma/seed/roles.ts: pass prune: true on the admin, teacher, referent, student global-preset authoring calls (the seedProfileCoupledPresetsForTenant teacher call, and inline for admin; referent/student are authored via inclusion-list loops there — extend those to prune as well). - src/permissions/role-clone.ts: add reconcileFrozenPresetRolesIntoTenants(prisma) — the frozen list is ['admin', ...PROFILE_COUPLED_ROLE_KEYS]; for each tenant × frozen role, upsert every global-preset grant then delete tenant grants absent from it. - prisma/seed.ts: call reconcileFrozenPresetRolesIntoTenants(prisma) immediately after backfillMissingPresetRoles(prisma) (line ~124). Update the stale "prod has no tenants" comment (backoffice onboarding now provisions real prod tenants).

7. Divergence ledger¶

Pattern	We diverge by	Reason	Tradeoff accepted
Seed/provisioning is additive-only; never deletes a `RolePermission` (documented invariant in `role-clone.ts` docstrings)	Introducing deletion of grant rows during seeding	Only deletion propagates a narrowing to existing tenants; additive-only structurally cannot	Yes — deletion is gated strictly to the frozen set (roles no one can hand-edit), so no customization can be destroyed. The `role-clone.ts` docstrings are updated to state the new, narrower invariant: "additive-only for editable presets; authoritative (add/update/prune) for frozen presets."
`seedRoleForTenant` is uniform across all roles	Adding a per-call `prune` flag (frozen calls only)	Editable global presets are hand-editable via backoffice `PATCH` at `tenantId=null`; pruning them would clobber platform-admin edits to the global baseline	Yes — flag defaults to off; only the four frozen calls opt in.

8. Pushback log¶

US says	Conflicts with	Proposed instead	Status
None — engineering-initiated, no US.			Resolved

9. Deferrals¶

Editable-preset propagation (hr, secretary, department_head, curriculum_coordinator, principal) — this iteration does not touch them. Propagating a baseline change while preserving per-tenant PATCH edits requires distinguishing a deliberate customization from an untouched baseline grant, which we cannot do today (no baseline snapshot / provenance). Follow-up: a future spec adding either a per-RolePermission provenance flag (source: PRESET | TENANT) or a per-tenant baselineVersion enabling a three-way merge. Revisit when a live editable-preset baseline change actually needs to ship.
Standalone / dry-run script — chose auto-only in the prologue. Follow-up: if manual inspection of pending deltas is ever wanted, extract the reconcile body behind a thin CLI wrapper (the function is already prod-safe and idempotent). Revisit on demand.

10. Open questions¶

Scope — frozen roles only. (Resolved in chat, 2026-07-22.)
Trigger — automatic in the Tier-1 seed prologue. (Resolved in chat, 2026-07-22.)

None open.

11. Verification plan¶

Unit specs:
New src/permissions/role-clone.spec.ts (or extend if present): reconcileFrozenPresetRolesIntoTenants — (a) prunes a tenant grant absent from the global preset; (b) adds a preset grant missing from the tenant; (c) updates a differing access level; (d) leaves editable-preset roles untouched; (e) idempotent (second run issues no writes / no-op deltas); (f) mirrors others-scope (custom-field) grants like native ones.
New/extended prisma/seed/helpers/seed-role.spec.ts: prune: true deletes a pre-existing global grant not in the authored set; prune absent leaves extra grants in place (editable-preset safety).
E2E specs:
Extend test/rbac-grants.db-sync.e2e-spec.ts: after the full seed, assert every tenant's frozen roles (admin/teacher/referent/student) match the expected-preset-grants.ts baseline exactly — no missing, no stale grant. Add a regression case that seeds a tenant with an injected stale teachers.employment grant on teacher, runs the prologue, and asserts it is gone; and one that injects a hand grant on an editable preset and asserts it survives.
Manual verification: on a local DB, PATCH a stale grant onto a tenant's teacher role via raw SQL, run the prologue (npx prisma db seed), confirm the grant is removed and that a manually-edited hr role is unchanged.

Patterns: chapter 09 (testing), and feedback_e2e_isolation_patterns.md for E2E discipline (E2E DB reset per run, deleteMany cleanup).

12. Sign-off¶

Approved by: Fabio Barbieri
Date: 2026-07-22
Chat reference: design shape approved by Fabio in chat 2026-07-22 ("y"), then written spec approved ("yes") including the §7 additive-only → authoritative-for-frozen-roles invariant change.

Until this section is filled, no implementation code is written. When you fill it, flip the frontmatter status: to Approved in the same edit.