Skip to main content

PHI encryption at rest and per-tenant DEK rotation

Outcome​

Per-tenant column-level encryption (pgcrypto + Key Vault DEKs) is backfilled, rotated on schedule, and resumable on crash.

Prerequisites​

  • PLATFORM_ADMIN.
  • Tenant has run migration 113 (per-tenant DEK columns).
  • Key Vault admin access.

Scope of encryption​

ColumnNotes
member.member.dobPlaintext is intentionally not zeroized today (used by ix_member_name_dob).
member.member_identifier.identifier_valueOnly when identifier_type IN ('SSN','MEDICARE_ID').
member.coverage_policy.member_number
member.eligibility_result.subscriber_id

Each encrypted row carries an enc_kid SMALLINT tracking which Data Encryption Key (DEK) version encrypted it. Rotation re-encrypts every row to a new DEK and bumps enc_kid.

Key topology​

ElementRole
DEK materialLives in Key Vault under phi-dek-${tenantId}-v${kid}.
Active kidRecorded in identity.tenant.phi_dek_kid (master DB).
TenantDekResolverCaches (tenantId, kid) → dek; provisions a fresh DEK on first use.
Old DEKsRetained in Key Vault — required to decrypt anything ever restored from a backup. Decommissioning is a separate operator step.

Initial backfill (one-time per tenant after migration 113)​

pnpm --filter @rcm/rcm-core backfill-phi --tenant <tenant-id>

The CLI:

  1. Resolves the tenant DB via TenantConnectionResolver.
  2. Resolves the active DEK via TenantDekResolver (provisions one if first use).
  3. For each of the four target tables, finds rows whose *_encrypted column is NULL or whose enc_kid is stale, and updates them in batches of 500.
  4. Logs counts per table.

It is idempotent — re-running encrypts only the rows that the previous run missed. --dry-run forecasts counts without writing.

Rotation drill​

# 1. Forecast.
pnpm --filter @rcm/rcm-core rotate-phi-dek --tenant <id> --dry-run

# 2. Rotate.
pnpm --filter @rcm/rcm-core rotate-phi-dek --tenant <id>

# 3. Verify (sample decrypt).
psql "$TENANT_DB_URL" -c \
  "SELECT pgp_sym_decrypt(dob_encrypted::bytea, '<new-dek>')
     FROM member.member LIMIT 5;"

# 4. Once verified, optionally zeroize legacy plaintext columns.
pnpm --filter @rcm/rcm-core rotate-phi-dek --tenant <id> --zeroize

Rotation:

  1. Provisions a new DEK at kid+1 in Key Vault.
  2. Sweeps every PHI row, decrypts under the old DEK, re-encrypts under the new DEK, sets enc_kid = newKid.
  3. Advances identity.tenant.phi_dek_kid to the new kid so live writes use it.
  4. Invalidates the resolver's cached entries for the tenant.

Resuming a crashed rotation​

The per-row enc_kid makes rotation resumable: each sweep filters on WHERE enc_kid <> newKid, so a rerun picks up only the rows the crashed run never updated.

  1. Confirm the new DEK exists at phi-dek-${tenantId}-v${newKid}.

  2. Confirm identity.tenant.phi_dek_kid is at the OLD kid (the advance happens at the end of rotation).

  3. Re-invoke rotate-phi-dek --tenant <id>. It will detect the already-provisioned new DEK.

  4. If provisionNext collides (new DEK was already written but the master pointer was never advanced), manually advance the pointer then re-run rotation:

    UPDATE identity.tenant SET phi_dek_kid = <newKid>
     WHERE tenant_id = '<tenant-id>';

    After the pointer is correct, the next rotate-phi-dek run will treat <newKid> as the active kid and re-rotate to <newKid+1>. Use that path if any rows are still at enc_kid = oldKid.

Key compromise response 🚨​

If a tenant DEK is suspected compromised:

  1. Immediately rotate — rotate-phi-dek --tenant <id>. New writes go to the new DEK as soon as the master pointer advances.

  2. Audit access — search audit.access_log for accessed_entity_type IN ('MEMBER', 'COVERAGE_POLICY', 'ELIGIBILITY_RESULT') events around the suspected window. See Audit log viewer.

  3. Decommission the compromised DEK — only after confirming no backup restores will rely on it. Hard-purge with keyVault.deleteSecret(name, { purge: true }). This is irreversible; all future restores from older backups become unreadable for the affected columns.

Caveats​

  • Repository write-path was NOT migrated. New INSERTs continue to write plaintext to the legacy columns. Until the follow-up lands, operators must schedule backfill-phi to run nightly.
  • member.dob plaintext is intentionally not zeroized by the --zeroize flag — the dob column is on ix_member_name_dob and is the primary DOB lookup surface. Encrypting AND searching DOB requires the repo write-path migration.
  • Legacy member.ssn_encrypted (encrypted under PHI_ENCRYPTION_KEY env var) is not yet re-keyed under per-tenant DEKs. See PHI key rotation legacy CLI until the migration ships.

Validation​

CheckExpected
backfill-phi completesAll target rows have enc_kid set
rotate-phi-dek completesenc_kid advances on every row
identity.tenant.phi_dek_kidMatches the new kid
Sample decrypt with new DEKReturns plaintext

Cross-references​

Next​

9.1 — pg-boss supervisor operations