PII Encryption at Rest

Introduced in: v1.0.38
Security Control: SEC-22
Category: Data Protection

Overview

From v1.0.38, Calmony Pay encrypts all customer personally identifiable information (PII) and sensitive payment fields at the database column level using AES-256-GCM with a KMS-managed encryption key. This means a database compromise — whether through a SQL injection, a leaked backup, or direct storage access — does not expose readable customer data.

Encryption and decryption happen transparently in the application layer (src/db/schema.ts). No changes are required to the public API surface.

Encrypted Fields

`payCustomers`

Column	Type	Notes
`email`	string	Customer email address
`name`	string	Customer full name
`phone`	string	Customer phone number
`line1`	string	Billing address line 1
`line2`	string	Billing address line 2
`city`	string	Billing city
`state`	string	Billing state / region
`postal_code`	string	Billing postal code
`country`	string	Billing country

`payPaymentMethods`

Column	Type	Notes
`billingName`	string	Name on the payment method
`billingEmail`	string	Email associated with the payment method

`users`

Column	Type	Notes
`email`	string	User account email
`name`	string	User display name

`pay_webhook_endpoints`

Column	Type	Notes
`secret`	string	Webhook signing secret — previously stored in plaintext

Encryption Scheme

Algorithm: AES-256-GCM
Key management: KMS-managed key. In production this is an AWS KMS Customer Managed Key (CMK) or an equivalent Vercel-hosted secret. A raw environment variable key is supported as a fallback for local development only.
Implementation: Node.js crypto.createCipheriv() / crypto.createDecipheriv() wrapped in a custom Drizzle column type defined in src/db/schema.ts.
IV handling: A unique, random initialisation vector (IV) is generated per encrypted value and stored alongside the ciphertext.
Authentication tag: GCM authentication tags are verified on decryption, detecting any tampering with ciphertext.

Environment Variables

You must supply the encryption key via environment variable before starting the application:

# A 32-byte (256-bit) hex-encoded key
PII_ENCRYPTION_KEY=<your-kms-managed-key-or-local-secret>

For production deployments, this value should be injected at runtime from AWS KMS, AWS Secrets Manager, or Vercel's encrypted environment secret store — never committed to source control.

⚠️ Key rotation: Rotating PII_ENCRYPTION_KEY requires a backfill migration to re-encrypt all existing rows with the new key. Plan key rotation carefully and keep the previous key available during the transition window.

Migration: Backfilling Existing Data

Rows written before v1.0.38 contain plaintext values. The provided migration script reads each row, encrypts the PII columns with the current key, and writes the ciphertext back.

# Run the backfill migration (idempotent — safe to re-run)
npx tsx scripts/migrate-pii-encrypt.ts

The migration script:

Reads rows in batches to avoid locking the table.
Skips rows where the column value already appears to be ciphertext (prefix check).
Logs progress and any rows that fail to encrypt.

Do not deploy v1.0.38 to production without running this script — until backfilled, pre-existing rows will fail to decrypt at read time.

Operational Considerations

Querying Encrypted Fields

Because values are stored as ciphertext, encrypted columns cannot be used in WHERE clauses or indexed for exact-match queries at the database level. If you need to look up customers by email, the application layer must encrypt the search term with the same key and compare ciphertext, or maintain a separate deterministic HMAC index column.

The internal Calmony Pay API handles this transparently — pass plaintext values to API endpoints as normal.

Logging

Ensure your application and infrastructure logging pipelines do not log decrypted PII. With field-level encryption in place, query logs from the database layer will only surface ciphertext.

Backups

Database backups now contain only encrypted PII. Backup access controls remain important, but a leaked backup no longer constitutes a direct PII breach provided the encryption key is not also compromised.

Security Control Reference

Attribute	Value
Control ID	SEC-22
Threat	Database compromise exposes customer PII and payment data
Mitigation	Field-level AES-256-GCM encryption with KMS key management
Affected file	`src/db/schema.ts`
Introduced	v1.0.38