📝 Background & Context
Currently, Social Security Numbers (SSNs) are stored in plaintext within the database (specifically in EmploymentForm and Staff entities), with the raw string serving as the uniqueness key. This poses a significant security risk and was flagged during code review (PR #17).
⚠️ The Problem
We must stop storing SSNs in plaintext. However, simply using standard encryption (JPA @convert) creates a challenge: because strong encryption (with random salts) generates different outputs for the same input, the database's UNIQUE constraint will break, and we lose the ability to perform efficient lookups.
💡 Solution: The "Blind Index" Pattern
To address this, we will decouple the identity check from the data storage by using two separate columns:
A Hash Column (ssnLookupKey) for searchability and uniqueness.
An Encrypted Column (encryptedSsn) to allow data recovery for legitimate business needs.
✅ Acceptance Criteria (Tasks)
- Canonicalization (Data Normalization)
Before hashing or encrypting, the SSN must be normalized into a single canonical form (e.g., converting both YYMMDD-NNNN and YYYYMMDD-NNNN into YYYYMMDDNNNN). This ensures the same identity doesn't produce different hashes.
- Implement Deterministic Lookup Key
Implement a new column: ssnLookupKey.
Use a keyed, deterministic hash (HMAC-SHA256) on the canonical SSN to serve as a collision-resistant lookup key.
Update the database schema to move the unique = true constraint from the raw SSN column to the new ssnLookupKey column.
- Implement Field-Level Encryption
Implement a new column: encryptedSsn (or ssnCipherText).
Use a dedicated CryptoService to handle encryption/decryption.
Security Requirement: Secret keys for both HMAC and Encryption must be retrieved from a Secrets Manager; they must never be hardcoded.
- Update Entity & DTO Layers
Entities: Update Staff and EmploymentForm entities. Use JPA AttributeConverter or @PrePersist/@PreUpdate lifecycle hooks to automate encryption and hashing.
Transparency: The Entity getters/setters should continue to work with clear-text values in memory; the conversion should only happen at the persistence layer.
DTOs: Ensure EmploymentFormDTO and other relevant DTOs do not unnecessarily expose or transmit the raw SSN in API responses.
- Logging & Error Handling
Zero Logging: Ensure the raw SSN is never logged, even during exceptions or debugging.
Null Safety: Gracefully handle null values during the encryption and hashing processes.
- Data Migration Job
Provide a one-time migration script/job using the CryptoService.
For existing records: Read the plaintext SSN, canonicalize it, generate the ssnLookupKey, generate the encryptedSsn, and remove/nullify the old plaintext value once the migration is verified.
📝 Background & Context
Currently, Social Security Numbers (SSNs) are stored in plaintext within the database (specifically in EmploymentForm and Staff entities), with the raw string serving as the uniqueness key. This poses a significant security risk and was flagged during code review (PR #17).
We must stop storing SSNs in plaintext. However, simply using standard encryption (JPA @convert) creates a challenge: because strong encryption (with random salts) generates different outputs for the same input, the database's UNIQUE constraint will break, and we lose the ability to perform efficient lookups.
💡 Solution: The "Blind Index" Pattern
To address this, we will decouple the identity check from the data storage by using two separate columns:
A Hash Column (ssnLookupKey) for searchability and uniqueness.
An Encrypted Column (encryptedSsn) to allow data recovery for legitimate business needs.
✅ Acceptance Criteria (Tasks)
Before hashing or encrypting, the SSN must be normalized into a single canonical form (e.g., converting both YYMMDD-NNNN and YYYYMMDD-NNNN into YYYYMMDDNNNN). This ensures the same identity doesn't produce different hashes.
Implement a new column: ssnLookupKey.
Use a keyed, deterministic hash (HMAC-SHA256) on the canonical SSN to serve as a collision-resistant lookup key.
Update the database schema to move the unique = true constraint from the raw SSN column to the new ssnLookupKey column.
Implement a new column: encryptedSsn (or ssnCipherText).
Use a dedicated CryptoService to handle encryption/decryption.
Security Requirement: Secret keys for both HMAC and Encryption must be retrieved from a Secrets Manager; they must never be hardcoded.
Entities: Update Staff and EmploymentForm entities. Use JPA AttributeConverter or @PrePersist/@PreUpdate lifecycle hooks to automate encryption and hashing.
Transparency: The Entity getters/setters should continue to work with clear-text values in memory; the conversion should only happen at the persistence layer.
DTOs: Ensure EmploymentFormDTO and other relevant DTOs do not unnecessarily expose or transmit the raw SSN in API responses.
Zero Logging: Ensure the raw SSN is never logged, even during exceptions or debugging.
Null Safety: Gracefully handle null values during the encryption and hashing processes.
Provide a one-time migration script/job using the CryptoService.
For existing records: Read the plaintext SSN, canonicalize it, generate the ssnLookupKey, generate the encryptedSsn, and remove/nullify the old plaintext value once the migration is verified.