Background
This issue tracks the planned refactoring of SSN encryption, identified during review of PR #80 (comment: #80 (comment)).
Requested by: @codebyNorthsteep
Problem
The current implementation uses Encryptors.text(encryptionKey, salt) (Spring Security) with a single static salt stored as an environment variable. Because Encryptors.text() generates a fresh random 16-byte IV per encryption call:
- Uniqueness checks are unreliable:
staffRepository.existsBySocialSecurityNumber(encryptionService.encrypt(ssn)) will almost never match an existing row because each encryption of the same SSN yields a different ciphertext.
- Update comparison is broken: Comparing the stored encrypted SSN against a newly encrypted value will always differ.
The current workaround (findAll() + decrypt-and-compare) is O(n) and will degrade as the dataset grows. It is marked with TODO comments in EmploymentFormService.
Planned Solution (as agreed in PR #80)
- Per-record random salt: Generate a unique salt for each SSN at encryption time and store it alongside the encrypted value in the database, so decryption remains possible without a shared static salt.
- Deterministic HMAC lookup column: Add a
ssn_lookup column to the Staff and EmploymentForm entities containing a keyed HMAC-SHA256 of the plaintext SSN (using a separate, dedicated HMAC key from env vars). This restores O(1) existence checks.
- Service updates: Refactor
EmploymentFormService.validateSsnNotExists() and updateFormBeforeApproval() to query/compare using the HMAC lookup value instead of the full encrypted ciphertext.
- Key rotation strategy: Document how the HMAC key and encryption key can be rotated.
Acceptance Criteria
Background
This issue tracks the planned refactoring of SSN encryption, identified during review of PR #80 (comment: #80 (comment)).
Requested by: @codebyNorthsteep
Problem
The current implementation uses
Encryptors.text(encryptionKey, salt)(Spring Security) with a single static salt stored as an environment variable. BecauseEncryptors.text()generates a fresh random 16-byte IV per encryption call:staffRepository.existsBySocialSecurityNumber(encryptionService.encrypt(ssn))will almost never match an existing row because each encryption of the same SSN yields a different ciphertext.The current workaround (
findAll()+ decrypt-and-compare) is O(n) and will degrade as the dataset grows. It is marked withTODOcomments inEmploymentFormService.Planned Solution (as agreed in PR #80)
ssn_lookupcolumn to theStaffandEmploymentFormentities containing a keyed HMAC-SHA256 of the plaintext SSN (using a separate, dedicated HMAC key from env vars). This restores O(1) existence checks.EmploymentFormService.validateSsnNotExists()andupdateFormBeforeApproval()to query/compare using the HMAC lookup value instead of the full encrypted ciphertext.Acceptance Criteria
ssn_lookup(HMAC-SHA256) column is added and populated on create/updatevalidateSsnNotExists()usesexistsBySsnLookup(computeHmac(ssn))updateFormBeforeApproval()compares HMAC values for change detectionencrypt()for uniqueness checks)