ADR-0015: Column encryption of sensitive data, with logged reveal
- Status: Accepted
- Date: 2026-11-14
Context and problem statement
Section titled “Context and problem statement”The full driver application collects the Social Security number, the date of birth and the driver’s license number. Many users may open a candidate or a driver file, but few need those values in full. A copy of the database should not expose them either.
How are these values stored and shown?
Decision drivers
Section titled “Decision drivers”- The values are unreadable in the database, in its backups and in any export of rows.
- Screens and API responses show them masked by default.
- Seeing a full value is an explicit act, limited by permission and recorded.
- The values never reach logs, error messages, domain events or search text.
Considered options
Section titled “Considered options”- Encryption per column in the application, masked display, and a reveal operation with its own permission and log.
- Encryption at rest of the whole database only. Common baseline, listed for comparison: it does not hide the values from a query.
- Values in clear, restricted only by a permission in the API. Obvious alternative, listed for comparison.
Decision outcome
Section titled “Decision outcome”Chosen option: 1.
SensitiveData, in apps/api/src/core/sensitive, encrypts each value with AES-256-GCM using the key in SECRETS_ENCRYPTION_KEY. The additional authenticated data is the company id and the field name, so a value copied to another company or to another field does not decrypt. Next to the ciphertext the row keeps the last four characters, for the masked form (***-**-1234), and a keyed hash per company for the Social Security number and the license number, used only for exact-match lookups. The date of birth is always fully masked.
revealSensitive returns the full value to a user who has compliance.sensitive_data.reveal. The permission is checked in the database, and the same transaction writes a row in sensitive_data_reveals and a row in the audit log, neither with the value. In a new company only the “Owner” role has this permission. A filter redacts any field with a sensitive name, and anything shaped like a Social Security number, from logs and error bodies. Without the key the API runs and refuses to write sensitive data.
Consequences
Section titled “Consequences”- Good, because reading the table does not give the values, and each reveal names who saw what and when.
- Good, because a mistake in one route cannot return a full value: routes only ever hold the masked form.
- Bad, because these columns cannot be searched or sorted in SQL. Only an exact match through the lookup hash is possible.
- Bad, because one key protects the values of all companies, and it lives in the configuration of the API.
- Bad, because replacing the key is not a function of the product yet. It is a requirement with the state Target in Quality requirements, and the stored format starts with a version byte to allow it.
More information
Section titled “More information”apps/api/src/core/sensitive/sensitive.ts: encryption, masks, lookup hash and reveal.apps/api/src/core/sensitive/redact.ts: the filter for logs and errors.apps/api/test/sensitive.test.tsandapps/api/test/files-sensitive-unit.test.ts: tests of the encryption, the reveal and the masks.- Concept pages: Sensitive data, Audit log.
- Related: ADR-0016.