---
apiVersion: destructive-runbook/v1
id: drop-permissionset-bitmap-columns-20260423c
title: "Permission refactor — stage 3/3: drop PermissionSet bitmap columns"
description: "Manual destructive runbook to drop the legacy PermissionsX TINYINT(1) columns from PermissionSet now that grant data lives exclusively in _System_PermissionSetPermission. This is intentionally NOT data-spec/v1 and must not be executed by data-spec-apply. The legacy column set is discovered per-cluster from INFORMATION_SCHEMA via the same preflight as stages 1 and 2, with a mandatory cross-check against stage 2's recorded manifest. Requires successful completion of stage 1 (backfill) AND stage 2 (audit) before execution."
issue: "#1806"
created: 2026-04-23
scope: both
status: pending-staging
executable_by: manual-operator-only
requires_codeowner_approval: true
approval_format: "PR body must contain 'Approvers: @a, @b' with two distinct CODEOWNERS"
tables_modified:
  - PermissionSet
tables: [PermissionSet]
idempotent: true
risk: high
reviewers: ["@codeowners"]
depends_on:
  - backfill-permissionset-bitmap-to-junction-20260423a
  - audit-permissionset-bitmap-backfill-20260423b
---

# Permission refactor — stage 3/3: drop PermissionSet bitmap columns

## Summary

Drops the legacy `Permissions<X> TINYINT(1)` columns from `PermissionSet` now
that grant data lives exclusively in `_System_PermissionSetPermission`.
This is the destructive step — **columns cannot be restored without schema
history**. The data-spec split (this is stage 3 of 3) exists specifically to
ensure no destructive change runs without a matching, operator-verified
backfill+audit gate.

This file is intentionally **not** `data-spec/v1`. Ordinary
`data-spec-apply` pre-flight requires `apiVersion: data-spec/v1`, so the
applier must reject this file before running SQL. That rejection is the
contract, not a failure: `DROP COLUMN` is inside the protocol's forbidden
pattern zone and must not be smuggled through prose. Until a general
destructive-spec protocol exists, this stage is a manual operator runbook.

The set of columns to drop is discovered per-cluster via the same preflight
query as stages 1 and 2. There is no hardcoded enumeration. `IF EXISTS` on
every `ALTER TABLE` makes the emitted statements defensive against a column
that disappeared between preflight and apply (e.g. concurrent admin DDL),
but under the normal protocol the preflight manifest IS the definitive
column list.

## Precondition — hard gate

**Both prerequisite specs must be in `status: applied` on the target cluster
before this runbook is executed.** Because this file is not `data-spec/v1`,
the `depends_on` frontmatter is human gate documentation, not an
applier-enforced dependency. The operator must verify manually:

1. `backfill-permissionset-bitmap-to-junction-20260423a` — status: applied
   (grant data transferred from bitmap → junction, OR recorded as a no-op
   on a greenfield cluster).
2. `audit-permissionset-bitmap-backfill-20260423b` — status: applied
   (blocking invariant returned zero per manifest row, OR recorded as a
   no-op on a greenfield cluster).
3. Stage 2's status note includes the recorded preflight manifest.
4. This runbook's PR body contains `Approvers: @a, @b` with two distinct
   CODEOWNERS.

**If any gate is unsatisfied**, stop. Do NOT execute this runbook. Data that
was visible only in the bitmap column will be permanently lost if stage 3 runs
before stage 1 and stage 2 have succeeded.

## Deploy-order preconditions

- The cutover binary (containing the P6 column removal + P2/P4 decorator +
  read-path projection changes) MUST be deployed to 100% of backend pods.
  Mixed rollout is unsafe: old pods still attempt to write to the bitmap
  columns and will fail `INSERT` once the columns are gone.
- Apply order across environments: staging → verify with
  `post-apply verification` queries → prod.

## Preflight — discover the legacy bitmap column set

This runbook is legacy remediation 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 runbook runs. Because no per-column statement is ever emitted, no
  reference to a missing column is constructed. **This runbook 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 runbook's status note
  when `status: applied` is recorded. All per-column statements and any
  stage-specific gates below operate on this manifest.

## Cross-check — this stage's preflight vs stage 2's manifest

Unique to stage 3. Compare this stage's preflight manifest (column names) to
the manifest recorded in stage 2's `status: applied` note.

- **If this stage's preflight contains a column that stage 2's manifest did
  NOT contain, STOP.** A new `Permissions%` column appeared between stage 2
  and stage 3 — a concurrent ETL sync or admin column-add. Re-run stage 2
  against the new column set (its status will revert to `pending-staging`)
  and only then resume stage 3. Dropping an unaudited column would permanently
  lose grant data.
- **If stage 2's manifest contained a column that this stage's preflight does
  NOT contain,** record the discrepancy in the status note. It is likely a
  benign race (a column went away via operator DDL between stages). No drop
  is needed for it; proceed to drop the current manifest.
- **If the two manifests match,** proceed.

## Rollback

The DROP COLUMN is irreversible without schema history (TiDB does not retain
dropped-column data). The junction (`_System_PermissionSetPermission`) is the
authoritative store and is NOT touched by this runbook — so the canonical
rollback is to re-add the columns and repopulate them from the junction on
the pre-refactor binary:

1. Redeploy the pre-refactor binary (`main` before
   `work/permission-metadata-refactor` merge).
2. Run `bootstrap/schema.go ReconcileTableColumns` on that binary to re-ADD
   the 26 columns (still declared in that codebase's `system_tables.json`).
   Columns recreated as NOT NULL DEFAULT 0.
3. Run the pre-refactor seeder's direction-1 reconcile pass (restored in
   the reverted binary) to backfill bitmap columns from the junction.

Note that this rollback path only restores the 26 columns this codebase
historically declared. Any Fivetran-created columns that were in the
preflight manifest (dropped by this runbook) cannot be recreated by rollback
— their grants still exist intact in the junction, so the functional
outcome is preserved; only the physical bitmap shape is not fully restored.

## Changes

### Step 1. DROP COLUMN — one ALTER TABLE per manifest row

**Applies only when the preflight returned ≥ 1 row and the cross-check
against stage 2's manifest passed.** For each row of the preflight manifest,
execute the statement below, substituting `${COLUMN_NAME}` from that row.

```sql
ALTER TABLE `PermissionSet` DROP COLUMN IF EXISTS `${COLUMN_NAME}`;
```

`IF EXISTS` makes each statement idempotent and defensive against a column
that disappeared between preflight and execution, but under this runbook
the preflight manifest IS the definitive column list — each emitted statement
targets a column that existed at preflight time.

Manual per-manifest-row execution keeps the blast radius reviewable: the
operator substitutes `${COLUMN_NAME}` only from the recorded preflight
manifest, executes one `ALTER TABLE ... DROP COLUMN IF EXISTS` per row, and
records the final emitted statement list, operator, timestamp, and two
CODEOWNER approvers in the status note. This is not a data-spec-apply dry-run
or apply phase.

## Post-apply verification

```sql
-- 1. Expect zero rows: no Permissions% columns remain on PermissionSet.
SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME   = 'PermissionSet'
   AND COLUMN_NAME  LIKE 'Permissions%';

-- 2. Sanity — junction still populated; stage 1 and stage 2 did not corrupt
-- the SSoT.
SELECT COUNT(*) AS grant_count FROM `_System_PermissionSetPermission`;
```

Post-apply check 1 is now a true "zero bitmap columns anywhere" invariant,
not "zero of our 26 hardcoded columns" — so Fivetran-created columns that
were in the preflight manifest and correctly dropped by this stage are
also verified absent.