Skip to content

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.

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.

  1. These values are stored only encrypted. If encryption is not available, they are not stored.
  2. Every screen and every API answer shows the masked form. This includes the pages the candidate uses.
  3. 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.
  4. The values never enter logs, error bodies, audit rows, domain events or search text.

All of it lives in apps/api/src/core/sensitive. The service SensitiveData is the only code that encrypts, decrypts, masks or reveals.

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.

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, lookupHash stores 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.

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.

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
  1. 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.
  2. revealSensitive asks the database, not the session data held in memory, whether the user of the transaction has compliance.sensitive_data.reveal in the company of the transaction.
  3. 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 of audit_log with the action compliance.sensitive_data.reveal. Neither holds the value.
  4. 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.

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_case or camelCase, 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.

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.
  • 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.