Authoritative reconciliation of every preset role on deploy — iteration 1¶

1. Problem distillation¶

Runtime authorization resolves from database grants, while the code-defined preset matrix can change independently. Additive seeding leaves stale grants on existing management-role clones and currently requires a bespoke data migration for each narrowing.
The existing authoritative reconciler is restricted to frozen presets. This is no longer the desired ownership boundary: every preset role is platform-owned and must converge to the deployed code matrix in every tenant.
Prisma migrations are one-shot and therefore cannot provide an every-deploy invariant. Deployment needs an idempotent reconciliation step after migrations and Tier-1 seeding.
Historical tenant edits and platform-admin edits to preset grants are no longer durable customization. The next deploy deliberately overwrites them. Custom roles remain tenant-owned and untouched.

Success criteria (observable behavior that proves this works):

Every Railway deploy runs migrations, Tier-1 seed, then the authoritative preset reconciler in development, stage, and production.
On successful completion, every code-declared global preset and every tenant clone has exactly the code-defined scope grants, access levels, and action grants: missing rows are inserted, changed access is updated, and stale rows are deleted.
Every existing tenant has every code-declared preset role; stored tenant role metadata is aligned to the global preset metadata and code-declared parameter dimension.
Custom roles (isPreset = false) and all role assignments are unchanged.
Reconciliation runs in one transaction, verifies the final matrix before commit, rolls back on failure, and fails the pre-deploy command so an unreconciled application revision is not promoted.
A second run against an already-aligned database is idempotent.

Non-goals (in-scope-shaped things this iteration is explicitly not doing):

Automatically deleting a catalogue entity/scope/action removed from code. Preset grants to such catalogue rows are pruned, but catalogue cleanup still requires an explicit migration because custom roles and structural dependants may reference those rows.
Automatically deleting a preset role key removed from code. Assigned obsolete roles require an explicit, product-aware migration rather than a cascading deploy-time delete.
Reconciling or modifying custom roles.
Removing the platform-admin preset-editing API in this iteration. Its writes remain effective immediately but are intentionally reset by the next deploy.
Adding a schema model, permission primitive, or HTTP endpoint.

2. Patterns survey¶

Analogous module/spec	What we'd borrow	What doesn't fit
`tools/reconcile-preset-roles.ts`	Existing privileged, transactional all-preset reconciliation: build the expected code matrix, lock, create missing clones, align metadata and grants, verify, then commit/rollback	It is currently manual and requires a URL argument; deployment must invoke it automatically after the seed
`railway.toml`	One uniform `preDeployCommand` already runs migrations and seed in every environment	It does not yet invoke the all-preset reconciliation step
`src/permissions/role-clone.ts` + `2026-07-22 frozen-preset design`	Existing add/update/prune semantics, missing-clone prerequisite, idempotency, and cross-tenant maintenance placement	The frozen-only boundary deliberately preserves editable presets, which this iteration supersedes
`prisma/seed/helpers/expected-preset-grants.ts` + `test/rbac-grants.db-sync.e2e-spec.ts`	Independent code-vs-DB matrix oracle and bidirectional missing/extra/access mismatch assertions	A test detects drift but cannot repair hosted databases; the deploy reconciler makes the same exactness operational
`prisma/seed.ts` + `docs/15-seeding.md`	Tier-1 runs in every environment and creates catalogue/global/missing preset prerequisites before reconciliation	Its built-in tenant reconcile is frozen-only and non-transactional across the full all-preset matrix

On-axis/off-axis assessment (chapter 16 §§4–5): role and scope additions are on-axis, and a frozen-role deploy reconciler already exists. Making every preset authoritative is an off-axis ownership reversal because it invalidates the prior customization-preservation rule. Reusing the existing transactional CLI keeps that new policy at one explicit deployment seam instead of duplicating a second all-preset algorithm.

3. Architecture mapping¶

Primitive	Apply?	How	Justify
Tenant scope	yes	The privileged maintenance transaction iterates every tenant and only matches tenant preset rows by `tenant_id` plus code-declared role key; it also reconciles the `tenantId IS NULL` global baselines	Cross-tenant reconciliation is platform maintenance and must cover all existing tenants uniformly
Academic-year scope	no	n/a — roles and grants are not academic-year scoped	No affected table carries `academicYearId`
RBAC entity key	none	No new key	This changes lifecycle/ownership, not the authorization vocabulary
Scopes	existing catalogue	Expected scope grants are derived from the code matrix; no new scope is introduced here	Any feature-specific scope delta remains authored in its own change
Actions	existing catalogue	Expected action grants are derived from the code matrix; no new action is introduced here	Same as scopes
Service base	custom deployment tool	Keep the existing standalone privileged reconciler	No request-path CRUD service participates
`queries.ts` shape	n/a — SQL maintenance transaction	The tool performs set-based cross-tenant operations and verification	This is the established implementation in the existing tool
Error codes	none	Throw/process failure aborts pre-deploy	No user-facing API surface
DTO conventions	n/a	No HTTP request/response
File-backed sub-resources	n/a	No files
Custom fields	no direct change	`*.others` grants are ordinary expected scope grants and reconcile like native grants	Custom-field definitions themselves remain untouched
Profile completeness	no	n/a	Unrelated

4. Data model plan¶

Schema deltas¶

None.

Migration shape¶

Additive / destructive / renaming: no new Prisma migration. The reconciler may insert/update/delete preset grant rows as an every-deploy data operation.
Data backfill: tools/reconcile-preset-roles.ts runs after prisma migrate deploy and prisma db seed on every deployment.
Hazards from chapter 12 checklist: no DDL; grant deletions are intentionally destructive but restricted to recognized isPreset = true role keys. Custom roles and assignments remain outside every mutation predicate. The existing transaction, advisory lock, table locks, timeouts, prerequisite assertions, post-check, and rollback behavior remain mandatory.

Indexes and uniqueness¶

No new indexes. Existing role-key and role-grant unique constraints support missing-clone insertion and grant upserts.

5. API surface¶

n/a — no HTTP surface.

Swagger considerations¶

n/a — no controller or contract change.

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 matrix delta in this iteration; the existing code matrix becomes authoritative for every global/tenant preset after every deploy
`*_SCOPES` runtime constant	none

Deployment wiring: extend railway.toml so the uniform pre-deploy command is ordered as prisma migrate deploy → prisma db seed → authoritative RBAC reconcile, passing the privileged deployment DATABASE_URL. The seed must run first because it creates new catalogue rows and global/missing preset roles that the reconciler validates and mirrors.

7. Divergence ledger¶

Pattern	We diverge by	Reason	Tradeoff accepted
Existing editable preset clones preserve historical per-tenant grants	Every recognized preset clone becomes code-authoritative and is add/update/pruned on deploy	There is no longer an editable tenant grant surface, and stale tenant matrices are a larger security/operational risk than preserving historical edits	Yes — user explicitly chose exact code convergence for all existing tenants; custom roles are the customization seam
Platform-admin global preset edits survive redeploys	Deploy reconciliation resets grant and parameter-dimension edits to code	Code cannot be the authoritative matrix while hosted mutations survive indefinitely	Yes — the endpoint remains for now, but edits are deployment-ephemeral and documentation will say so
Prisma migrations carry hosted data changes once	Use an idempotent post-seed reconciliation on every deploy	Applied Prisma migrations never rerun, so they cannot guarantee ongoing convergence	Yes — retain one-off migrations for schema and special data transitions, not steady-state repair
Seed helpers are the normal preset authoring seam	Deployment uses the existing independently-derived expected-matrix reconciler for the final exactness pass	It already provides an atomic all-tenant transaction and post-verification that the seed path lacks	Yes — drift specs continue guarding seed/expected-matrix equivalence

8. Pushback log¶

US says	Conflicts with	Proposed instead	Status
“Run that data migration on each deploy”	Prisma migrations are recorded once and cannot rerun on later deploys	Run the idempotent authoritative reconciler after migrations and seed in the uniform pre-deploy command	Resolved — same requested outcome, correct lifecycle mechanism
“DB state must reflect code matrix for all preset roles”	Prior frozen-only design preserves editable preset/global backoffice changes	Treat all preset grants as code-owned; overwrite such edits on the next deploy and retain custom roles for customization	Resolved by user direction in chat, 2026-08-28

9. Deferrals¶

Removal of obsolete permission catalogue rows — unsafe to automate while custom roles or structural rows may reference them — follow-up: explicit migration per removed/renamed key.
Removal of obsolete preset role keys — assignments need a product-selected replacement/archive policy — follow-up: explicit migration per retired role.
Removal or read-only conversion of the platform-admin preset PATCH endpoint — not required for deploy convergence — revisit if deployment-ephemeral editing is judged misleading.
Consolidating roles.ts and expectedPresetGrants into one declarative source — independent derivation currently provides drift detection — revisit only if maintaining the mirror becomes error-prone.

10. Open questions¶

None. The user explicitly selected all-preset, all-existing-tenant, code-authoritative convergence and accepted the loss of preset customization.

11. Verification plan¶

Unit specs:
Extend the deployment-command/config drift coverage, if present, to assert the ordered migration → seed → reconcile command and environment URL usage.
Keep the expected-matrix derivation tests as the independent code oracle.
E2E specs:
Replace the frozen-only tenant reconciliation cases in test/rbac-grants.db-sync.e2e-spec.ts with all-preset cases: inject one missing grant, one extra grant, and one access mismatch into an editable tenant preset; run reconciliation; assert exact code equality.
Assert a custom role carrying the same injected rows remains unchanged.
Assert a second reconciliation is a no-op and an invalid prerequisite rolls back all mutations.
Manual verification:
Against a disposable database, run the CLI with --dry-run, then apply it, then dry-run again and expect zero deltas.
After deployment to each hosted environment, inspect reconciliation output for the tenant count and successful post-verification.
Documentation sync: update docs/04-rbac.md, docs/15-seeding.md, docs/REFERENCE.md, seed/reconciler docstrings, and the older frozen-only spec's status/pointer as appropriate.
Per repository rules, builds, lint, tests, and migrations run only when the user explicitly requests those commands.

12. Sign-off¶

Approved by: user
Date: 2026-08-28
Chat reference: “go on” in chat after reviewing the all-preset, all-existing-tenant authoritative deployment design.

Until this section is filled, no implementation code is written. When it is approved, set frontmatter status: Approved, fill this section, commit the spec, and add the ordered implementation plan separately under docs/superpowers/plans/ before implementation.