Sensitive data columns use encryption (at-rest encryption beyond platform defaults)

Platform-level encryption at rest protects the physical disk from theft but does not protect sensitive columns from a compromised database user or a SQL injection attack that reads data directly from tables. SSNs, government IDs, health diagnoses, and biometrics stored as plaintext TEXT columns are fully readable to any session with SELECT access. CWE-311 (missing encryption of sensitive data) and OWASP A02 both require that sensitive fields are protected at the application layer — the database encryption key and the application encryption key must be separately managed so that compromising one does not expose the other.

Encrypt sensitive fields (SSNs, government IDs, health data) using AES-256-GCM before writing to the database. Store the encryption key in a secrets manager, not in source code. For payments, store only Stripe token references — never raw card numbers.

// lib/encryption.ts — AES-256-GCM field encryption
import { createCipheriv, createDecipheriv, randomBytes } from 'crypto'
const KEY = Buffer.from(process.env.FIELD_ENCRYPTION_KEY!, 'hex') // 32-byte hex key

export function encrypt(plaintext: string): string {
  const iv = randomBytes(12)
  const cipher = createCipheriv('aes-256-gcm', KEY, iv)
  const enc = Buffer.concat([cipher.update(plaintext, 'utf8'), cipher.final()])
  return `${iv.toString('hex')}:${cipher.getAuthTag().toString('hex')}:${enc.toString('hex')}`
}

export function decrypt(ciphertext: string): string {
  const [ivHex, tagHex, dataHex] = ciphertext.split(':')
  const d = createDecipheriv('aes-256-gcm', KEY, Buffer.from(ivHex!, 'hex'))
  d.setAuthTag(Buffer.from(tagHex!, 'hex'))
  return d.update(Buffer.from(dataHex!, 'hex')) + d.final('utf8')
}
// Generate key: openssl rand -hex 32