Skip to content

ADR-0016: Separate vault for the drug and alcohol file

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

A carrier keeps drug and alcohol test results, Clearinghouse queries and what previous employers answer about drugs and alcohol. These records need a much smaller audience than the rest of the driver file: a recruiter or a compliance clerk who works on the driver file every day should not see them.

How is this file kept apart from everything else about the same person?

  • The restriction must hold even if a new route or a new query forgets to check a permission.
  • Background routines and public links must never read these records.
  • The rest of the product still needs two facts: whether a required record is on file, and whether the person is on hold.
  • Every read is recorded.
  1. Separate tables and a separate storage area, with the read permission checked inside the row level security policy.
  2. The same tables as the driver file, with a flag on the row and the permission checked in the API. Obvious alternative, listed for comparison.

Chosen option: 1.

The records are in drug_alcohol_records, drug_alcohol_employer_answers and drug_alcohol_access_log. Their files are stored with vault = 'drug_alcohol', under a storage prefix of their own. The select policies of drug_alcohol_records and drug_alcohol_employer_answers, and a restrictive policy on the vault rows of files, call app.has_permission('compliance.drug_alcohol.read'). A session without the permission reads zero rows, in any query of any module. Reading drug_alcohol_access_log asks for compliance.drug_alcohol.manage instead. The worker and the routes without a session have no user, so they never read the vault: they can only insert, for example when a previous employer answers by link.

Other modules learn two things through narrow database functions that return no result data: whether a record that fulfils a rule is on file, with its date (gate.drug_alcohol_on_file), and the boolean driver_files.compliance_hold. The records cannot be deleted, the answers and the access log are insert-only, and each read by a user writes a row in the access log. The audit export of a driver file contains nothing from the vault. In a new company only the “Owner” role has compliance.drug_alcohol.read and compliance.drug_alcohol.manage.

  • Good, because the restriction is in the database: application code cannot leak a row the session is not allowed to see.
  • Good, because eligibility and the driver file work from two derived facts, without access to results.
  • Bad, because no automation or background routine can act on the content of a record. Anything they need has to be exposed as another narrow function.
  • Bad, because a user with manage but without read changes zero rows and gets no error, since an update also passes the select policy. The two permissions only make sense together.
  • Bad, because a wrong result cannot be edited: it is voided and recorded again.
  • Bad, because the owner of a new company has to grant the permissions before a compliance officer can work on this file.
  • packages/db/src/schema/drug-alcohol.ts and packages/db/migrations/0050_wave04_drug_alcohol_security.sql: tables, policies and privileges.
  • packages/db/migrations/0043_wave04_files_security.sql: app.has_permission and the vault policy of files.
  • packages/db/test/wave04-drug-alcohol.test.ts and apps/api/test/drug-alcohol.test.ts: the tests.
  • Concept pages: Files and vaults, Authentication and permissions.
  • User guide: Drug and alcohol file.