---
apiVersion: data-spec/v1
id: backfill-permissionset-bitmap-to-junction-20260423a
title: "Permission refactor — stage 1/3: backfill bitmap → junction"
description: "Populate _System_PermissionSetPermission with every (permSetID, PermissionsX=1) pair still set on the legacy PermissionSet bitmap columns. Non-destructive, idempotent. Sole code path that transfers historical grant data from the pre-refactor columns onto the new SSoT for upgraded clusters. The legacy column set is discovered per-cluster from INFORMATION_SCHEMA — no hardcoded enumeration. Greenfield clusters are a natural no-op (preflight returns zero rows; no statement referencing a missing column is ever emitted)."
issue: "#1806"
created: 2026-04-23
scope: both
status: pending-staging
tables_modified:
  - _System_PermissionSetPermission
tables: [PermissionSet, _System_PermissionSetPermission]
idempotent: true
risk: low
reviewers: ["@codeowners"]
depends_on: []
blocks: [drop-permissionset-bitmap-columns-20260423c]
---

# Permission refactor — stage 1/3: backfill bitmap → junction

## Summary

Stage 1 of the three-stage operational cleanup for the permission metadata refactor
(PR `work/permission-metadata-refactor`). The refactor dropped the `Permissions<X>
TINYINT(1)` column declarations from `backend/internal/bootstrap/system_tables.json`
(P6) and relocated the grant data to `_System_PermissionSetPermission`. On any
cluster that ran a pre-refactor binary those columns still exist physically and
still carry historical grant values — specifically, values written by Fivetran /
bulk-load ingestion paths that bypassed the `SetSystemPermission` write gate
(see #1806 for staging evidence: the SF-imported admin PermissionSet had 200
`Permissions*=true` bitmap fields but only 24 rows in the junction).

This spec transfers those residual grants into the junction **without touching**
the bitmap columns. After this spec applies successfully, stage 2
(`audit-permissionset-bitmap-backfill-20260423b`) verifies the transfer by
asserting a concrete invariant on real data, and only if the audit passes does
stage 3 (`drop-permissionset-bitmap-columns-20260423c`) drop the columns.

This spec is **non-destructive**: it only upserts into the junction. It never
deletes or modifies bitmap column values. Safe to re-run; idempotent via
`INSERT IGNORE` (additive only — never overwrites existing junction state).

## Precondition

- The cutover binary (containing the P4/P5 read-path changes that read the
  junction exclusively) MUST be deployed to 100% of backend pods before this
  spec runs. Rolling-deploy safety: because this spec does not alter schema, old
  pods continue to read from the junction (post-refactor path) or dual-read (if
  somehow still on an earlier binary) without failure. The precondition is
  defense-in-depth, not a strict requirement.

## Rollback

Rollback is a **truncate-the-delta** operation: removing rows added by this
backfill. Because the junction ID is a UUID generated per-row, there is no
natural key to identify "only rows this backfill wrote" after the fact —
so the rollback strategy is, in order of preference:

1. Take a row-level backup of `_System_PermissionSetPermission` via the
   standard data-spec backups directory before applying (the applier does this
   automatically for specs with `tables_modified`).
2. If a bad backfill is detected (stage 2 audit fails in an unexpected way),
   restore from the pre-apply backup.
3. The junction is never destructive-modified by this spec — `INSERT IGNORE`
   only inserts new rows, never updates or deletes existing ones. Rollback
   is therefore "delete rows inserted by this backfill." Row-level backup
   (step 1) captures pre-apply state so a restore returns the junction to
   exactly that. If the bitmap columns are still present (stage 3 not yet
   applied), an alternative rollback is to re-issue the backfill on a
   cleared junction — but the row-backup restore is strictly simpler and
   preferred.

## Preflight — discover the legacy bitmap column set

This spec is a legacy-remediation runbook for upgraded clusters. The set of
`PermissionSet.Permissions%` bitmap columns that actually exist on a given
cluster is not knowable a priori: it is the union of columns this codebase's
pre-refactor `system_tables.json` declared (26) and columns that ETL / bulk-load
paths (Fivetran against real Salesforce, CSV loaders, admin SQL) created
directly at write time. Salesforce's real `PermissionSet` has ~200 such
columns — a Fivetran-replicated cluster can have any subset of them. The
engineering lesson from #1806 (F1, F5) is that this set must be enumerated
from production reality, not from codebase review.

**Every execution of every stage in this three-spec chain re-runs the preflight
below.** Do not reuse a manifest recorded for an earlier stage or earlier apply
attempt — a new ETL sync or admin column-add between stages must be observed.

### Preflight query — text-identical across stage 1, 2, and 3

```sql
SELECT
  COLUMN_NAME,
  SUBSTRING(COLUMN_NAME, CHAR_LENGTH('Permissions') + 1) AS PermissionApiName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'PermissionSet'
  AND COLUMN_NAME  LIKE 'Permissions%'
  AND DATA_TYPE    = 'tinyint'
ORDER BY COLUMN_NAME;
```

### Preflight result handling

- **0 rows returned.** The cluster has no legacy bitmap columns — either it is
  greenfield (never ran a pre-refactor binary and was never Fivetran-populated)
  or stage 3 has already executed against it. This stage is a no-op. Mark this
  spec's `status: applied` with a status note:
  `no-op: no legacy bitmap columns present on this cluster`. No further SQL in
  this spec runs. Because no per-column statement is ever emitted, no reference
  to a missing column ever reaches the applier — `data-spec-apply`'s strict
  "missing column = STOP" gate stays in force and is never triggered. **This
  spec uses no 1054 / missing-column exemption.**
- **≥ 1 row returned.** Capture the full result set as the **preflight manifest
  for this stage execution**. Record the manifest in this spec's status note
  when `status: applied` is recorded. All per-column statements and any
  stage-specific gates below operate on this manifest.

## Changes

### Step 1. Backfill — one INSERT IGNORE per manifest row

**Applies only when the preflight returned ≥ 1 row.** For each row of the
preflight manifest, execute the statement below, substituting `${COLUMN_NAME}`
and `${PermissionApiName}` from that row.

```sql
INSERT IGNORE INTO `_System_PermissionSetPermission`
  (`Id`, `PermissionSetId`, `PermissionApiName`, `IsEnabled`)
  SELECT UUID(), `Id`, '${PermissionApiName}', 1
    FROM `PermissionSet`
   WHERE `${COLUMN_NAME}` = 1;
```

**Why additive-only (`INSERT IGNORE`, no `ON DUPLICATE KEY UPDATE`) is
load-bearing:** the cutover binary is deployed before this spec is applied.
In the window between the two, admin / API writes go exclusively to the
junction while the legacy bitmap columns stay stale. If an admin revokes a
grant in that window — junction row `IsEnabled=0`, bitmap column still `=1` —
an `ON DUPLICATE KEY UPDATE IsEnabled=VALUES(IsEnabled)` form would flip the
junction back to `1`, silently re-granting the permission. `INSERT IGNORE`
with no update clause can never re-grant: the junction is the SSoT
post-cutover, and stale bitmap state cannot override admin intent on that
SSoT. Stage 2's blocking invariant is aligned with this semantics: it
flags **missing** junction rows (`j.Id IS NULL`) only — never `IsEnabled=0`
rows, which are a legal post-cutover admin decision (see stage 2 Report A).

For the #1806 scenario (Fivetran / bulk-load writers that wrote directly to
the bitmap column pre-cutover, never touching the junction), there is no
existing junction row — so `INSERT IGNORE` inserts it with `IsEnabled=1`,
which is exactly the backfill intent.

Per-manifest-row execution (rather than a single dynamically-composed SQL
batch) preserves the data-spec-apply protocol's Phase 1.3 forbidden-pattern
scan, Phase 2 per-statement dry-run row-count, and Phase 4 per-statement
apply-time row-count verification — each emitted statement is static SQL
at the point the applier sees it. No protocol extension is required.

## Post-apply verification

Re-run the preflight. For each manifest row, execute the stage 2 blocking
invariant as a self-check. **Expected: 0 rows per manifest row.** Any
non-zero result means some `PermissionSet` row has `${COLUMN_NAME}=1` but
no matching junction row — this stage did not complete the transfer and
must be re-run before handing off to stage 2.

```sql
-- Per-manifest-row self-check (same shape as stage 2's blocking invariant).
SELECT ps.`Id` AS permission_set_id, '${PermissionApiName}' AS permission_api_name
  FROM `PermissionSet` ps
  LEFT JOIN `_System_PermissionSetPermission` j
    ON j.`PermissionSetId` = ps.`Id`
   AND j.`PermissionApiName` = '${PermissionApiName}'
 WHERE ps.`${COLUMN_NAME}` = 1
   AND j.`Id` IS NULL;
```

Informational (not a gate):

```sql
-- Total junction row count — informational baseline; no clean before/after
-- number because the junction may have been populated by native seeding.
SELECT COUNT(*) AS junction_rows FROM `_System_PermissionSetPermission`;
```