Skip to content

ADR-0015: Column encryption of sensitive data, with logged reveal

  • Status: Accepted
  • Date: 2026-11-14

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?

  • 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.
  1. Encryption per column in the application, masked display, and a reveal operation with its own permission and log.
  2. Encryption at rest of the whole database only. Common baseline, listed for comparison: it does not hide the values from a query.
  3. Values in clear, restricted only by a permission in the API. Obvious alternative, listed for comparison.

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.

  • 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.
  • 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.ts and apps/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.