---
apiVersion: data-spec/v1
id: migrate-profilepermission-to-junction-20260423d
title: "Permission refactor — one-time migration: _System_ProfilePermission → junction"
description: "Transfer historical grant data from the legacy _System_ProfilePermission table into the junction SSoT _System_PermissionSetPermission, via each profile-owned PermissionSet. Additive-only INSERT IGNORE — stale legacy rows may only fill missing junction rows, never overwrite existing ones. Replaces the long-lived runtime Batch 3 seeder that used ON DUPLICATE KEY UPDATE (removed from backend/internal/controlplane/packs/permissions.go in the same PR); see Summary for the drift mechanism that removal eliminates. Greenfield clusters and clusters where the legacy table is empty or absent are a natural no-op."
issue: "#1806"
created: 2026-04-23
scope: both
status: pending-staging
tables_modified:
  - _System_PermissionSetPermission
tables: [_System_ProfilePermission, _System_PermissionSetPermission, PermissionSet]
idempotent: true
risk: low
reviewers: ["@codeowners"]
depends_on: []
blocks: []
---

# Permission refactor — one-time migration: _System_ProfilePermission → junction

## Summary

`_System_ProfilePermission` is pre-#768 debt. After #768's SSoT cutover, no
runtime code writes to it — it is frozen historical data. But before this
PR, `InitializePermissions` ran Batch 3 on every seed (every server boot
and every `cmd/setup apply`), reading `_System_ProfilePermission` and
writing the junction via `ON DUPLICATE KEY UPDATE IsEnabled = VALUES(IsEnabled)`.
That made the legacy table a live overwriting writer against the junction
SSoT:

1. Admin revokes `ManageRoles` on a profile-owned PermissionSet via the
   new write gate → junction row `IsEnabled = 0`.
2. Next boot / next `setup apply` → Batch 3 reads the stale
   `_System_ProfilePermission` row (`IsEnabled = 1`) → `ON DUPLICATE KEY
   UPDATE` flips junction back to `IsEnabled = 1`.
3. Admin's revocation is silently undone, every time.

This is the same failure class F1 / F5 warn about — a legacy source that
was supposed to be cold kept doubling as a runtime writer, bypassing the
post-cutover SSoT's admin intent. The PR that lands this spec also deletes
Batch 3 from `controlplane/packs/permissions.go`; the junction is then the
sole active writer/source of truth for system grants at runtime.

This spec takes over the one-time transfer job Batch 3 was pretending to
do, with three safety properties Batch 3 did not have:

- **Additive only.** `INSERT IGNORE` with no `UPDATE` clause. A junction
  row that already exists — for any reason, including an admin revocation
  during the cutover window — is left as-is. Frozen legacy rows may only
  fill missing junction rows; they may never override current junction
  state.
- **One-time.** Applied once per cluster on the SSoT cutover upgrade path,
  not on every boot.
- **Preflight-gated.** If `_System_ProfilePermission` is absent or empty,
  the spec is a natural no-op — no SQL referencing a missing table is ever
  emitted, and the applier's strict missing-table-STOP gate stays in force.

## Precondition

- The cutover binary (this PR, which deletes runtime Batch 3) MUST be
  deployed to 100% of backend pods before this spec runs. That removes the
  last active writer from `_System_ProfilePermission` into the junction.
  Applying this spec while a pre-cutover pod is still running is unsafe:
  the pre-cutover pod's Batch 3 will continue to silently overwrite
  junction rows on its next boot, and the drift this spec claims to
  eliminate still exists for as long as any pre-cutover pod is running.

## Rollback

`INSERT IGNORE` is non-destructive — it never updates or deletes existing
rows. Rollback is therefore "delete rows inserted by this migration". The
junction `Id` is a UUID generated per row, so identifying rows this spec
wrote after-the-fact requires a pre-apply row-level backup:

1. The applier auto-backs-up `_System_PermissionSetPermission` before
   apply because this spec has `tables_modified`.
2. If the migration produced an unexpected effect, restore from the
   pre-apply backup.
3. The legacy table `_System_ProfilePermission` is never modified by this
   spec, so re-running after restore is safe.

## Preflight — detect the legacy table state

This spec targets the cutover upgrade path. The set of clusters for which
it does real work is exactly "clusters that held pre-#768 data in
`_System_ProfilePermission`". All others (greenfield, clusters that ran
the earlier runtime Batch 3 to completion at least once, clusters where
the legacy table has been retired) are a natural no-op.

### Preflight query

```sql
-- Does the legacy table exist on this cluster?
SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME   = '_System_ProfilePermission';
```

### Preflight result handling

- **0 rows returned.** The legacy table is not present on this cluster
  (already dropped, or never created). This spec is a no-op. Mark
  `status: applied` with status note:
  `no-op: _System_ProfilePermission table not present on this cluster`.
  No further SQL runs. Because no subsequent statement references the
  missing table, the applier's strict missing-table-STOP gate is never
  triggered — no exemption needed.
- **1 row returned.** Proceed to the row-count check below.

```sql
-- Is there anything to migrate?
SELECT COUNT(*) AS legacy_row_count FROM `_System_ProfilePermission`;
```

- **`legacy_row_count = 0`.** The legacy table exists but is empty. This
  spec is a no-op. Mark `status: applied` with status note:
  `no-op: _System_ProfilePermission table is empty on this cluster`.
- **`legacy_row_count > 0`.** Proceed to the migration SQL.

## Changes

### Step 1. One-shot backfill — _System_ProfilePermission → junction, INSERT IGNORE

**Applies only when the preflight reported a present, non-empty legacy
table.**

```sql
INSERT IGNORE INTO `_System_PermissionSetPermission`
  (`Id`, `PermissionSetId`, `PermissionApiName`, `IsEnabled`)
  SELECT UUID(), ps.`Id`, pp.`PermissionApiName`, pp.`IsEnabled`
    FROM `_System_ProfilePermission` pp
    JOIN `PermissionSet` ps
      ON ps.`ProfileId`         = pp.`ProfileId`
     AND ps.`IsOwnedByProfile`  = 1;
```

Semantics:

- `INSERT IGNORE` — primary/unique key collision on `(PermissionSetId,
  PermissionApiName)` silently no-ops. A row that already exists in the
  junction — whether from native seeding, prior runs of this spec, an
  admin edit through the new write gate, or any other source — is left
  untouched. Frozen legacy rows cannot re-grant a revoked permission.
- No `ON DUPLICATE KEY UPDATE` clause. This is load-bearing. Review
  #1831's original runtime Batch 3 had `ON DUPLICATE KEY UPDATE IsEnabled
  = VALUES(IsEnabled)`, which made the legacy source an overwriting
  writer against the junction SSoT — the exact drift scenario
  Summary describes. `INSERT IGNORE` with no update clause is the only
  form that preserves "junction is the SSoT; legacy source can fill
  gaps but not decide state".
- The JOIN projects each legacy `(ProfileId, PermissionApiName,
  IsEnabled)` tuple onto the PermissionSet owned by that profile
  (`IsOwnedByProfile = 1`), which is how the post-#768 data model
  represents profile-scoped permissions.

## Post-apply verification

```sql
-- 1. Junction grew by at most the legacy row count (may be strictly less
--    if some pairs were already present). Informational baseline.
SELECT COUNT(*) AS junction_rows FROM `_System_PermissionSetPermission`;

-- 2. Parity check: for every profile-owned PermissionSet, every legacy
--    (ProfileId, PermissionApiName) tuple where IsEnabled=1 is now
--    represented in the junction by at least a row (with whatever
--    IsEnabled value the junction SSoT currently holds — admin intent
--    wins). Expected: 0 rows. Non-zero means the migration did not
--    create a junction row for a legacy grant — investigate.
SELECT pp.`ProfileId`, pp.`PermissionApiName`
  FROM `_System_ProfilePermission` pp
  JOIN `PermissionSet` ps
    ON ps.`ProfileId`        = pp.`ProfileId`
   AND ps.`IsOwnedByProfile` = 1
  LEFT JOIN `_System_PermissionSetPermission` j
    ON j.`PermissionSetId`  = ps.`Id`
   AND j.`PermissionApiName` = pp.`PermissionApiName`
 WHERE pp.`IsEnabled` = 1
   AND j.`Id` IS NULL;
```

Post-apply check 2 is the stage-2-equivalent invariant for this migration:
it flags only **missing** junction rows (`j.Id IS NULL`), not `IsEnabled`
disagreements. A legacy `pp.IsEnabled = 1` row paired with a junction
`j.IsEnabled = 0` row is a legal admin revocation, not a parity failure —
see the stage 2 audit spec's blocking-gate discussion for the same
principle.