Skip to content

Files and vaults

This page describes how Ochema stores files, who may read them, and how the drug and alcohol file is kept apart from everything else. It is part of section 9 of the arc42 template. The decisions are in ADR-0017 and ADR-0016.

Candidates, drivers, previous employers and office staff all send files: license photos, medical cards, test results, signed contracts. Four things can go wrong with a file that cannot go wrong with a row:

  • its address leaks, and anyone who has it can download the file;
  • it is not what its name says, for example a program renamed to .jpg;
  • it is read by someone inside the company who should not see it, and nobody knows;
  • it is replaced or removed, and the record of what was sent is lost.

Drug and alcohol records add a regulatory rule: they are kept apart from the driver qualification file, with access limited to the people who need it.

  1. A document enters and leaves through one service, FileService. No module holds a storage key or hands out an address that works without the API.
  2. The storage bucket is never public, and there are no direct or pre-signed links. A route decides who may read, and serves the bytes.
  3. What a file is comes from its first bytes, never from its name or the type the sender declares.
  4. A user reads a file only through a record that points at it and that the user is allowed to read.
  5. Every read is recorded before the bytes are handed over.
  6. A stored document is never changed or deleted. Replacing one keeps the previous version.
  7. A file of the drug and alcohol vault does not exist for a session without compliance.drug_alcohol.read, in any query of any module.

ObjectStorage (apps/api/src/core/storage/object-storage.ts) is the abstraction, with an implementation for any S3-compatible service and one in memory for tests. The database keeps one row of files per stored document (packages/db/src/schema/files.ts): company, vault, size, SHA-256, detected type, who sent it and through which channel, and the record it was sent for.

The storage key is built by the server and checked by a constraint of the table:

Vault Key
standard tenants/<tenant id>/files/<file id>
drug_alcohol tenants/<tenant id>/restricted/da/<file id>

The original file name is kept only as text to display. It is cleaned of folders and control characters and never becomes part of a path.

Three kinds of object are kept under the same company prefix by the module that owns them, outside the files table: the logo of a careers page (tenants/<tenant id>/careers/), the spreadsheet of a candidate import (tenants/<tenant id>/imports/) and the result of an audit export (tenants/<tenant id>/exports/). Each of those modules checks the company prefix again before it reads or removes an object.

Channel Who sends Size limit Accepted types
Session A signed-in user 25 MB JPEG, PNG, WebP, PDF, DOCX
Public link A candidate, a driver or a previous employer 10 MB JPEG, PNG, WebP, PDF
System The worker, for a PDF it generated 100 MB The same five types

The limits and types are in apps/api/src/core/storage/file-types.ts, and the size limits are repeated as a constraint of the table.

  • Type by content. sniffFileType reads the signature in the first bytes. A DOCX must be a ZIP that contains the parts of a Word document. Anything else is refused before it reaches the storage.
  • Checks before the body. With a session, the upload is read after the session and the permission are checked and before a database transaction opens. On a public link, the token is resolved first, then uploads are limited per IP address, then the body is read.
  • A ceiling per link. A public link holds at most 10 files, counted under a lock so that parallel uploads cannot pass it. In the candidate and driver area the count is per 24 hours.
  • The row first, the bytes last. FileService writes the row of files with the hash and only then stores the object, so every refusal happens before a byte is stored.

The office system has one address for the files of every module: GET /files/<id> (apps/api/src/core/storage/files.controller.ts). The address carries nothing but the id of the row. FileService.read does the following, in this order, inside the transaction of the request.

%%{init: {"flowchart": {"wrappingWidth": 220}}}%%
flowchart TB
  start["GET /files/&lt;id&gt;<br/>with a session"]
  s1["<b>1. Row visible</b><br/>Company policy and<br/>vault policy"]
  s2["<b>2. Owner</b><br/>A record the user may read<br/>points at the file"]
  s3["<b>3. Access log</b><br/>One row of file_access_log"]
  s4["<b>4. Storage</b><br/>Read the object"]
  s5["<b>5. Integrity</b><br/>Size and SHA-256<br/>match the upload"]
  ok["Answer with the bytes"]
  nf(["404 not_found"])
  err(["500, nothing<br/>handed over"])

  start --> s1
  s1 --> s2
  s2 --> s3
  s3 --> s4
  s4 --> s5
  s5 --> ok
  s1 -.->|"Fails"| nf
  s2 -.->|"Fails"| nf
  s5 -.->|"Fails"| err

Legend. Box: a step, top to bottom. Rounded box: a refusal. Solid arrow: the step passed. Dotted arrow: it failed. All steps run in one database transaction, so the answer leaves only after the access log row is committed.

  • The same 404. An unknown id, a file of another company, a file of the vault without its permission and a file that no readable record points at all answer 404 not_found.
  • Owners decide. files knows no module. Each module registers in FileOwnerResolvers (apps/api/src/core/storage/file-owners.ts) the permission that gives access and a query that says whether one of its rows points at the file. Without a registered owner that answers yes, there is no access.
  • Safe delivery. The answer carries the stored type, X-Content-Type-Options: nosniff, no caching, and a content security policy that lets nothing inside the file run or load anything. A Word document is always a download.
  • Integrity. The bytes read from the storage are compared with the size and the SHA-256 recorded at the upload. A mismatch is an error and nothing is delivered.

The owners registered today:

Owner Permission that gives access Files
compliance.item-files compliance.driver_files.read Documents of the items of a driver file
compliance.person-credentials compliance.driver_files.read Credential documents of a person
compliance.screenings compliance.screenings.read Result files of screening requests, except drug test and Clearinghouse requests
compliance.contract-envelopes compliance.contracts.read Signed contracts
compliance.drug-alcohol compliance.drug_alcohol.read Files of the drug and alcohol vault
recruit.full-application-documents, recruit.full-application-envelopes recruit.full_applications.read Uploads and signed PDF of a full application
recruit.offer-envelopes recruit.offers.read Signed offers

Two other readers exist. The sender of a public link reads only what that same link sent. The system itself reads a file it reached through a row it was allowed to read, for example the worker building an audit export. Both leave an access log row, with the actor type public_link or system.

file_access_log has one row per read: file, vault, actor, purpose (view, download, export, signing or integrity_check), IP address and request id. It is partitioned by month and insert-only: the application role has SELECT and INSERT, and a trigger refuses any update or delete. Two reads of the same file are two rows.

ochema_app may change only the status columns of files, and a trigger refuses any delete. FileService.replace marks the previous row superseded and points it at the new one; both stay readable. No function of the service removes a row or an object.

The drug and alcohol file is a separate area of the Compliance module (apps/api/src/modules/compliance/drug-alcohol), in tables of its own: drug_alcohol_records, drug_alcohol_employer_answers and drug_alcohol_access_log. Its files are rows of files with vault = 'drug_alcohol'.

The separation is enforced by the database, not by the routes:

  • Rows that do not exist. The select policies of the three tables, and the policies over the vault rows of files and of file_access_log, call app.has_permission(...). The function is true only when the user of the transaction has the permission in the company of the transaction through an active membership. Records and files need compliance.drug_alcohol.read. Changing a record needs compliance.drug_alcohol.manage as well, and so does reading drug_alcohol_access_log.
  • No user, no read. The worker and the public routes run with a company and no user, so they read zero rows of the vault. They can insert: this is how the answer of a previous employer to the drug and alcohol questions, and the consent given by signing the full application, reach the file. Such a row is written with an id generated by the API and is not read back.
  • Two facts for the rest of the system. Other modules learn only 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, kept by a trigger. Neither carries a result. Eligibility and the driver file work from those two facts.
  • Results are never edited. A record cannot be deleted, and its result cannot be changed. A wrong record is voided and stays in the file. Employer answers and the access log are insert-only.
  • Every read is logged twice. Each read by a user writes a row of drug_alcohol_access_log (who, what kind of read, of whom) and a row of audit_log that says an access happened, without any detail of the record.
  • Out of exports. The audit export of a driver file contains nothing of the vault.

In a new company only the Owner role has the two permissions.

Test file What it proves
apps/api/test/files.test.ts A file is kept under the folder of its company with its hash, type and sender; a renamed executable, an oversized file and a multipart body that is not one file are refused before the storage; two reads write two log rows; the same 404 without the permission, from another company and with no owner; a changed object fails the integrity check; replacing keeps the previous file; the eleventh file of a link is refused, also when sent in parallel; a link reads only what it sent.
apps/api/test/files-sensitive-unit.test.ts The five types and nothing else, the limits per channel, the cleaning of file names and of the download header.
packages/db/test/isolation-wave04-files.test.ts The storage key is a function of the company, the vault and the id; the application role never changes the content of a file and has no DELETE in this area; the vault exists only with compliance.drug_alcohol.read; a read names its user; a read in a month without a partition fails.
packages/db/test/wave04-drug-alcohol.test.ts Without the read permission a user reads zero rows; with manage alone an update changes zero rows; a transaction without a user reads nothing and can insert; a result is never edited and a record never deleted; the narrow functions answer existence and date and nothing else; the three restricted permissions are born on the Owner role only.
apps/api/test/drug-alcohol.test.ts Every route answers 403 without the read permission and every write without manage; the file goes to the separate vault; the audit log holds no result, note or file name; no domain event or search text carries a result; a company never reads the file of another.
apps/api/test/audit-export.test.ts An export has no byte of the drug and alcohol file.
apps/api/test/import-storage.test.ts An import object key of another company is refused before the storage is reached.
  • Size and type. A file sent through a session is limited to 25 MB and one sent through a public link to 10 MB. Only JPEG, PNG, WebP, PDF and, with a session, DOCX are accepted. Spreadsheets, videos and archives are refused.
  • No direct downloads. Every file is delivered by the API, which reads it whole before answering. There is no link to hand to a third party: a person outside the company gets a document by email attachment or through a page of their own link.
  • A document cannot be removed. A file sent by mistake can be replaced, and the previous version stays stored. When the retention of a drug and alcohol record ends, a user with the manage permission records the decision to keep it or to mark it for deletion; the system does not delete records or files on its own.
  • No background job reads the vault. An automation, a reminder or an export cannot act on the content of a drug and alcohol record.
  • A wrong result is voided, not corrected. The voided record stays visible in the file, and the right one is recorded again.
  • The owner opens the vault. A compliance officer works on the drug and alcohol file only after the owner grants both permissions.