Bulk PII Migration—Encrypting & Shredding Legacy Columns

The PII migration tooling backfills legacy plaintext columns into the encryption model the vault uses for new writes. It does three things: report per-column coverage, encrypt rows that still lack an encrypted counterpart, and permanently null the plaintext column once every row is covered.

Reachability. shield-pii-migrate is console only. It is not in the public function allowlist, so https://api.passkeybridge.io/v1/shield-pii-migrate answers 404 with x-pb-reason: unknown-function. It is invoked from the signed-in dashboard through supabase.functions.invoke, and the panel under Settings is the intended way to run it.

Authorization. Three gates in order: a valid user JWT, the platform-level has_role(user, 'admin') check, then is_tenant_admin(user, tenant_id) for the tenant in the body. A tenant admin who is not also a platform admin is refused.

What is left to migrate. Two columns are configured:

While the subscriber entry was still in the map, every query the tooling ran against the dropped column failed at the database, the errors were discarded, the counts defaulted to zero and the panel rendered a Shredded badge from 0/0. The state was correct in substance, the plaintext really is gone, but the number was the absence of a column rather than a measured count, and the same discarded error would have made a genuine outage read as complete. Coverage queries now check their errors and report the pair as unavailable rather than as zero.

profiles has no tenant_id column, so the encrypt and shred passes over it operate on every profile row on the platform, not only the calling tenant's. That is what the platform-admin gate is there for. The run is still recorded against the tenant whose id was in the body.

The panel calls the function with the session's JWT; the function verifies the caller, then does all data work with the service role.

Dashboard panel  →  supabase.functions.invoke("shield-pii-migrate")
                 →  getUser(JWT) → has_role(admin) → is_tenant_admin(tenant_id)
                 →  service-role client → newsletter_subscribers, profiles,
                    shield_pii_migration_runs

Properties that matter when you run it:

• Batched. Encrypt processes at most 500 rows per table per invocation. Shred nulls the plaintext column in batches of 500 and loops until no rows remain.
• Idempotent. Encrypt selects only rows whose encrypted column is null, so re-running it after a partial batch cannot double-encrypt or overwrite an existing ciphertext.
• Isolated per row. A row that fails is counted and its message is kept; the batch continues with the next row.
• Recorded. Every encrypt and shred that touched anything writes a row to shield_pii_migration_runs with the operator's user id.
• Never automatic. Nothing schedules this function. Plaintext is removed only when an operator clicks Shred Plaintext and confirms.

Three actions. action defaults to encrypt when the field is absent; tenant_id is always required.

Status.

const { data } = await supabase.functions.invoke("shield-pii-migrate", {
  body: { action: "status", tenant_id: tenantId },
});

{
  "coverage": {
    "newsletter_subscribers.email": { "total": 0, "encrypted": 0, "plaintext_only": 0, "pct": 100 },
    "profiles.display_name": { "total": 12, "encrypted": 9, "plaintext_only": 3, "pct": 75 }
  },
  "runs": []
}

total counts rows with a non-null plaintext value, encrypted counts rows with a non-null encrypted value, plaintext_only counts rows that have the first and not the second, and pct is encrypted / total rounded, or 100 when total is zero. runs holds the 20 most recent migration runs for the tenant, newest first. Counts for newsletter_subscribers are scoped to the tenant; counts for profiles are platform-wide.

Encrypt.

const { data } = await supabase.functions.invoke("shield-pii-migrate", {
  body: { action: "encrypt", tenant_id: tenantId },
});

{
  "success": true,
  "subscribers_encrypted": 0,
  "profiles_encrypted": 3,
  "errors": []
}

Subscriber rows go through the vault helper that produces both the AES-256-GCM ciphertext and the keyed HMAC-SHA-256 lookup digest, and the row is stamped with the current hash version. Profile rows are encrypted only; a display name is not a lookup key, so no digest is written. Each errors entry names the row it came from, for example profile 3f2a...: the database message.

Shred.

const { data } = await supabase.functions.invoke("shield-pii-migrate", {
  body: { action: "shred", tenant_id: tenantId, table: "profiles" },
});

{
  "success": true,
  "table": "profiles",
  "column": "display_name",
  "rows_shredded": 12
}

Shred first counts rows that still have plaintext and no ciphertext. If any exist it refuses with 409 and changes nothing. Otherwise it nulls the plaintext column in batches and logs the run. table must be one of the two configured names.

Dashboard, Settings, Data governance and compliance, PII Migration Tooling. The section also holds the Data Subject Rights panel; see DSAR workflows.

The panel loads status on mount and after every action.

• Coverage cards. One card per configured column, headed table.column, with encrypted/total encrypted and N plaintext remaining underneath and a progress bar. The badge on the right is Shredded when the column has no plaintext rows at all, a Shred Plaintext button when coverage is 100 percent and plaintext rows remain, and the raw percentage otherwise.
• Encrypt Unprotected Rows. The primary button. Its label changes to Re-run Encryption once every column reads 100 percent, and it is disabled once every column is shredded. The toast reports how many rows were encrypted, or that there was nothing to do.
• Shred Plaintext. Per card, destructive styling, behind a browser confirm that names the table and says the action is irreversible. The toast reports rows_shredded.
• Zero-PII badge. Appears in the panel header when every configured column has zero plaintext rows.
• Migration History. The 20 most recent runs, with the action as an Encrypt or Shred badge, table.column, rows processed with any failed count beside it, a status of Done, Partial or Running, and the date.

To confirm a run landed: refresh the panel, check the card's counts moved, and find the new row at the top of Migration History with the expected rows_processed and zero failures.

Every encrypt or shred that processed or failed a row appends here.

An encrypt invocation writes one row per table it touched, so a single click can produce two rows. An encrypt that found nothing to do and hit no errors writes nothing at all.

Row-level security allows tenant admins to select and to insert rows for their own tenant. The function itself writes with the service role, so the insert policy is not what authorises these rows.

started_at is the insert default rather than a captured start time, so for a long shred it reads as the moment the row landed, close to completed_at.

1. Open Dashboard, Settings, Data governance and compliance, PII Migration Tooling and read the coverage cards.
2. Click Encrypt Unprotected Rows. Each click covers at most 500 rows per table, so repeat until the card reads 100 percent and plaintext remaining is zero.
3. Check Migration History for failed rows. Resolve them before going further: a failure usually means the encryption key is unavailable to the function, which the errors array will say.
4. Confirm your application reads the encrypted column. After shredding, the plaintext is recoverable only from a database backup.
5. Click Shred Plaintext on the card and confirm. The action refuses with 409 if any row still lacks a ciphertext, so the pre-flight is genuine rather than advisory.
6. Refresh. The card should show 0 total with the encrypted count unchanged, and the badge should read Shredded.

Shredding is irreversible and, for profiles, platform-wide.

The panel surfaces these as toasts carrying the function's own message, so the string you see on screen is the string above.

A row that is selected but fails to encrypt does not produce a non-200 response. It is counted in rows_failed and its message is stored on the run record, which is why the history table is the place to check a batch rather than the toast.