Sensitive data
This page describes how Ochema treats the three most sensitive values it holds about a person: the Social Security number, the date of birth and the driver license number. It is part of section 9 of the arc42 template. The decision and its alternatives are in ADR-0015.
The problem
Section titled “The problem”A driver application asks for a Social Security number, a date of birth and license numbers. A carrier needs them a few times in the life of a driver: to order a screening, or to hand a file to an auditor. The rest of the time nobody needs to read them, yet they sit next to data that recruiters open all day.
Three risks follow: a copy of the database gives the values away; a route returns a full value by mistake; a value ends up in a log line or an error message.
The rule
Section titled “The rule”- These values are stored only encrypted. If encryption is not available, they are not stored.
- Every screen and every API answer shows the masked form. This includes the pages the candidate uses.
- The full value is returned by one function only, to a user who holds
compliance.sensitive_data.reveal, and each reveal is recorded with who saw what, of whom and when. - The values never enter logs, error bodies, audit rows, domain events or search text.
How the code applies it
Section titled “How the code applies it”All of it lives in apps/api/src/core/sensitive. The service SensitiveData is the only code that encrypts, decrypts, masks or reveals.
What is stored
Section titled “What is stored”| Value | Encrypted column | Next to it, in the clear | Masked form |
|---|---|---|---|
| Social Security number | ssn_ciphertext |
ssn_last4, and a keyed hash in ssn_lookup_hash |
***-**-1234 |
| Date of birth | date_of_birth_ciphertext |
Nothing | **/**/**** |
| Driver license number | number_ciphertext on each license of an application, license_number_ciphertext on a person |
The last four characters, and a keyed hash | ****1234 |
The values enter through the full application, which the candidate fills in on a public link, and are written to driver_applications and driver_application_licenses (apps/api/src/modules/recruit/full-application/full-application.store.ts). The table person_identity holds the same three fields for a person of the directory. The Hire action writes it, in the transaction of the hire, from the signed full application of the hired application (apps/api/src/modules/people/person-identity.ts): the encrypted values, the last four characters and the keyed hashes are copied inside the database as they are, with the current license and its expiry date, and nothing is decrypted. A candidate hired without a signed full application gets no row. A person that already has a row only receives the fields it does not have; a Social Security number or a license number that differs from the one of the application is kept, and the audit row (people.person.identity_copied or people.person.identity_kept) names the fields, never a value. The audit export reads person_identity for a hired person, and the signed application only for a driver file that has no person yet or for a person without a row.
Encryption
Section titled “Encryption”encryptSensitive(tenantId, field, plaintext) encrypts one value with AES-256-GCM.
- One key, kept outside the database. The key is a setting of the API,
SECRETS_ENCRYPTION_KEY. It is never written to a table. - Bound to the company and the field. The additional authenticated data is
<tenant id>:<field>. A ciphertext copied to another company, or from the license column to the Social Security column, fails to decrypt. - Never the same twice. Each value gets a fresh random 12-byte IV, so two people with the same date of birth have different ciphertexts.
- Versioned format. A stored value is one version byte, the IV, the ciphertext and the 16-byte authentication tag.
- Lookup hash. For the Social Security number and the license number,
lookupHashstores an HMAC-SHA-256 of the company and the normalized value, with a key derived from the main one. The same number gives a different hash in another company. It allows an exact match without decrypting a row.
Without the key, the API still starts and every other feature works. A route that would write a sensitive value answers 503 and writes nothing, so a value is never stored in the clear.
Masking
Section titled “Masking”Routes never hold a full value. They read the last-four column and answer with maskSsn, maskLicense and maskDate. The date of birth has no visible part at all: a screen only learns whether one is on file. The PDF of an application that the candidate signs prints the Social Security number and the license numbers masked, and does not print the date of birth.
Reveal
Section titled “Reveal”The office system shows a “Reveal” button next to a masked value in the full application of a candidate, only to a user with the permission. The button calls POST /recruit/applications/{id}/full-application/reveal.
---
config:
sequence:
width: 110
actorMargin: 20
---
sequenceDiagram
actor U as User<br/>(web app)
participant API
participant DB as PostgreSQL
U->>API: POST .../reveal<br/>(field)
API->>DB: Read the encrypted<br/>column of the record
API->>DB: app.has_permission(<br/>sensitive_data.reveal)
alt Not allowed
API-->>U: 403 forbidden,<br/>nothing written
else Allowed
API->>API: Decrypt for this<br/>company and field
API->>DB: Row in sensitive_<br/>data_reveals
API->>DB: Row in audit_log
API->>DB: COMMIT
API-->>U: The full value
end
- The route reads the encrypted column under the permission of its own module and under row level security, so the record must belong to the company of the session.
revealSensitiveasks the database, not the session data held in memory, whether the user of the transaction hascompliance.sensitive_data.revealin the company of the transaction.- It decrypts, then writes one row of
sensitive_data_reveals(user, name of the user at that moment, subject, field, context, IP address) and one row ofaudit_logwith the actioncompliance.sensitive_data.reveal. Neither holds the value. - Both rows are part of the transaction of the request. If either cannot be written, the request fails and the value is not returned.
sensitive_data_reveals is insert-only: the application role has SELECT and INSERT, and a trigger refuses any update or delete.
The audit export has the same rule. By default the PDF prints masked identifiers. The option “Show full identifiers” needs the permission when the export is requested; the worker then reveals each value as the user who asked, so the permission is checked again at that moment and each value leaves a reveal row with the context export. Downloading that file needs the permission too. See Runtime view.
Today the logged reveal is the only code path that decrypts a value.
Logs and errors
Section titled “Logs and errors”The API and the worker install RedactingLogger (apps/api/src/core/sensitive/redact.ts), and the error filter passes every exception and every error body through the same function.
- A field whose name is a sensitive column, in
snake_caseorcamelCase, becomes[redacted]whatever its value, at any depth of an object. - In free text, the same names followed by a value are redacted, and so are the row and key details PostgreSQL adds to a constraint error.
- Anything shaped like a Social Security number (
123-45-6789) is removed from log lines and from messages shown to the caller.
The last four characters are not treated as sensitive, since every screen shows them.
What guarantees it
Section titled “What guarantees it”| Test file | What it proves |
|---|---|
apps/api/test/files-sensitive-unit.test.ts |
The stored format; no decryption for another company, another field, another key or a changed byte; the lookup hash depends on the company; without the key nothing is encrypted, hashed or decrypted; the masks show the last four and nothing else; the redaction of fields, database errors and the process logger. |
apps/api/test/sensitive.test.ts |
A value is written encrypted and answered masked; it is absent from the answer and from the log of an unexpected error; a reveal without the permission answers 403 and records nothing; a reveal with it records who, of whom and when, never the value; without the key a writing route answers 503. |
apps/api/test/full-application.test.ts |
The public steps keep the sensitive values out of every answer; the candidate record shows them masked and reveals only with the permission. |
apps/api/test/audit-export.test.ts |
“Show full identifiers” needs the permission, to create the export and to read its file. |
packages/db/test/isolation-wave04-files.test.ts |
The application role has no UPDATE or DELETE on the reveal log, and one company never reads the rows of another. |
packages/db/test/wave04-drug-alcohol.test.ts |
The three restricted permissions, this one included, are born on the Owner role only. |
Known limits
Section titled “Known limits”- These values cannot be searched or sorted. A candidate cannot be found by Social Security number or license number from a search box. Lists show the masked form only.
- The date of birth is all or nothing. There is no partially masked date, and no screen computes an age from it.
- One key per installation. The values of every company are encrypted with the same key, bound to each company by the authenticated data. A key per company is not available. Replacing the key is a requirement with the state Target in Quality requirements.
- Reveal starts with the owner. In a new company only the Owner role has
compliance.sensitive_data.reveal. See Authentication and permissions. - Only three fields are covered. Other personal data, such as names, addresses and phone numbers, is protected by tenant isolation and permissions, not by column encryption. Documents such as a license photo are files: see Files and vaults.
Related
Section titled “Related”- ADR-0015: column encryption with logged reveal.
- ADR-0016: the drug and alcohol vault, which protects records rather than single values.
- Audit log: where a reveal appears for the company.
- Files and vaults, Authentication and permissions.
- Runtime view: where the values are written.