--- apiVersion: data-spec/v1 id: audit-permissionset-bitmap-backfill-20260423b title: "Permission refactor — stage 2/3: audit bitmap→junction parity" description: "Blocking audit gate between the backfill (stage 1) and the destructive DROP COLUMN (stage 3). One blocking invariant — for every legacy PermissionsX=1 there MUST be a matching row in _System_PermissionSetPermission — plus two informational reports. The legacy column set is discovered per-cluster from INFORMATION_SCHEMA via the same preflight as stages 1 and 3. Operator-executed, read-only. Must pass before stage 3 may be applied." issue: "#1806" created: 2026-04-23 scope: both status: pending-staging tables_modified: [] tables: [PermissionSet, _System_PermissionSetPermission, _System_Permission] idempotent: true risk: low reviewers: ["@codeowners"] depends_on: [backfill-permissionset-bitmap-to-junction-20260423a] blocks: [drop-permissionset-bitmap-columns-20260423c] --- # Permission refactor — stage 2/3: audit bitmap→junction parity ## Summary **Read-only spec. No SQL mutations.** This is the audit gate that stands between the backfill (stage 1) and the destructive DROP COLUMN (stage 3). Stage 3 cannot be applied until the blocking invariant below has been run on the target cluster and returns the expected result (zero rows). This prevents the single most damaging failure mode the data-spec split was designed to eliminate: a backfill that silently missed some rows followed by a DROP that permanently loses them (see `docs/lessons/2026-04-23-permission-ssot-cutover.md`, rule R9 — "Never combine backfill and destructive operations in one migration"). This stage has **one blocking gate** and **two informational reports**. The separation is deliberate: - **Blocking gate — "is the data migrated?"** For every legacy `PermissionsX=1` row on `PermissionSet` there MUST exist a matching row in `_System_PermissionSetPermission`. The `IsEnabled` value on that junction row is out of scope for this gate — it is the current SSoT truth, decided by admins / API writers, and may legally be `0` (see Report A). - **Report A — stale bitmap vs current junction SSoT.** Rows where `bitmap=1 AND junction.IsEnabled=0` are the expected shape of a legal admin revocation that happened during the cutover window (after the cutover binary was deployed but before stage 1 applied). The junction is the SSoT; the bitmap is stale data about to be dropped. Reporting these gives the operator visibility into admin activity during the cutover window; it does not block anything. - **Report B — catalog drift.** `Permissions%` bitmap column suffixes that do not correspond to an active row in `_System_Permission` indicate that some ETL path (likely Fivetran) wrote a permission the platform catalog does not recognize. Stage 1 has already migrated these rows into the junction unconditionally (data preservation > catalog containment; the junction has no FK to the catalog and tolerates unknown keys). Reporting them gives the operator a concrete list to open a catalog follow-up; it does not block the destructive step. ## Precondition - Stage 1 (`backfill-permissionset-bitmap-to-junction-20260423a`) must have `status: applied` with a recorded preflight manifest (or a recorded `no-op` note for greenfield clusters). ## 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. ## Operator protocol 1. Apply stage 1 (`backfill-permissionset-bitmap-to-junction-20260423a`). 2. Run the preflight for this stage. If 0 rows, mark this spec `applied: no-op` and proceed directly to stage 3 (which will see the same empty preflight). 3. If ≥ 1 row, execute the Blocking invariant once per manifest row. **Every execution must return 0 rows.** Any non-zero result means stage 1 did not complete the transfer for that column — re-run stage 1 and then re-run the blocking invariant. 4. Execute Report A and Report B once. Record their output (rows or empty) in this spec's status note. Non-empty output is allowed; it never blocks. 5. Once every blocking-invariant query has returned 0, mark this spec `status: applied` with the recorded manifest and the two report outputs. Only then may stage 3 (`drop-permissionset-bitmap-columns-20260423c`) be applied. ## Changes ### Blocking invariant — per manifest row For each row of the preflight manifest, execute the statement below, substituting `${COLUMN_NAME}` and `${PermissionApiName}`. **Expected: 0 rows.** Any returned row is a parity failure: remediation is to re-run stage 1. ```sql 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; ``` The invariant checks only `j.Id IS NULL`. It does **not** check `IsEnabled=1`. The junction is the post-cutover SSoT; an `IsEnabled=0` row on the junction is a legal admin decision, not a backfill gap. See Report A for visibility into that shape. ### Report A — stale bitmap vs current junction SSoT (informational) For each row of the preflight manifest, execute the statement below. **Any number of returned rows is acceptable.** A returned row means the bitmap column still says "granted" but the junction SSoT currently says "revoked" — the expected shape of an admin revocation that occurred after the cutover binary was deployed but before stage 1 applied. Record the output; do not block. ```sql SELECT ps.`Id` AS permission_set_id, '${PermissionApiName}' AS permission_api_name FROM `PermissionSet` ps JOIN `_System_PermissionSetPermission` j ON j.`PermissionSetId` = ps.`Id` AND j.`PermissionApiName` = '${PermissionApiName}' WHERE ps.`${COLUMN_NAME}` = 1 AND j.`IsEnabled` = 0; ``` ### Report B — catalog drift (informational, single query) Execute once against the cluster. **Any number of returned rows is acceptable.** A returned row means the cluster has a `PermissionSet.PermissionsX` column whose suffix is not an active row in `_System_Permission` — typically an ETL (Fivetran) import of a permission the platform catalog does not yet recognize. Stage 1 has already migrated these rows into the junction unconditionally (data preservation); this report makes them visible so the operator can open a catalog follow-up. ```sql SELECT manifest.COLUMN_NAME, manifest.PermissionApiName FROM ( 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' ) manifest LEFT JOIN `_System_Permission` p ON p.`ApiName` = manifest.PermissionApiName AND p.`IsActive` = 1 WHERE p.`Id` IS NULL ORDER BY manifest.COLUMN_NAME; ``` ## Rollback Not applicable — this spec mutates nothing.