Skip to content

Audit log

This page describes the audit log: what is written to it, how the system makes sure no write escapes it, and how a company reads it. It is part of section 9 of the arc42 template.

A carrier must be able to answer, months later, who changed a driver file, who invited a user, who turned a module off and who looked at a Social Security number. An audit trail that depends on each developer remembering to write it has holes, and an audit trail that can be edited proves little.

  1. Every route that writes records at least one audit row, in the same transaction as the write. If the audit row cannot be written, the write does not happen.
  2. An audit row says who, when, what action, on which record and, for a change, the fields before and after. It never holds a password, a token, a hash or a sensitive value.
  3. The application can add audit rows and read those of its company. It cannot change or delete them.
  4. A company reads its own log on the screen Platform > Audit log, with the permission platform.audit.read.
  5. What the platform team does to a company appears in the log of that company.

audit_log is created by packages/db/migrations/0002_foundation_security.sql and declared for queries in packages/db/src/schema/audit-log.ts.

Column Content
occurred_at The instant of the write, taken from clock_timestamp(). It is not the start of the transaction, so ordering by (occurred_at, id) is the real order of concurrent writes.
tenant_id The company. Empty only for events that belong to no company, such as a failed sign-in.
actor_type user, system, anonymous or platform_staff.
actor_user_id, actor_name The user, and a copy of the user’s name at that moment, so the log still names a person who later left the company.
session_id, request_id, ip, user_agent Where the action came from.
action <module>.<resource>.<verb>, for example platform.membership.role_changed. The format is a constraint of the table.
entity_type, entity_id The table and the row the action is about.
changes For a change, the fields before and after. Only the fields that changed.

The table is partitioned by month. See ADR-0028.

One writer, in the transaction of the request

Section titled “One writer, in the transaction of the request”

AuditService.record (apps/api/src/core/audit/audit.ts) is the writer for routes with a session. It takes the action, the record and the changes from the caller, and fills in the rest from the request: user, session, company, request id, IP address and browser. It writes through the transaction of the request, so the audit row and the business write commit together or not at all.

Registers (jobs, candidates, people, vehicles and the like) do not call it by hand. RegisterWriter (apps/api/src/core/registers/write.ts) writes <module>.<entity>.created, .updated, .archived and .reactivated with the changed fields on every write.

Flows without a session call insertAudit with the transaction they manage: a failed sign-in is recorded as anonymous, and what the system does on its own, such as assigning a new lead to a recruiter or creating the candidates of an import, as system.

TransactionInterceptor (apps/api/src/core/http/transaction.interceptor.ts) counts the audit rows written during a request. When a request with a session uses any method other than GET, HEAD or OPTIONS and ends with a count of zero, the interceptor throws, and the transaction rolls back. A route that forgets its audit row therefore fails in the first test that calls it, instead of writing silently.

One exception is built in: an edit that changed nothing, such as saving a form without touching it, may end without an audit row, and only if the transaction wrote nothing else. The interceptor asks the database whether the transaction wrote or locked any row.

The role of the API and the worker has SELECT and INSERT on audit_log, and no UPDATE or DELETE. Its policies let it read only the rows of the company of the transaction, and insert rows of that company or rows with no company. A row with no company is accepted and is never shown to any company.

The routes are in apps/api/src/modules/platform/audit-log, all behind platform.audit.read.

Route What it does
GET /platform/audit-log The events of the company, newest first, with filters by period, user, action and record. The default period is the last 30 days and the longest is 366 days, in the time zone of the user.
GET /platform/audit-log/filters, /actors The choices of the filters. Actions and record types come from a catalog kept in code (audit-log.catalog.ts).
GET /platform/audit-log/export The filtered events as a CSV file, up to 100,000 rows. Cells that a spreadsheet would read as a formula are neutralized. The export is itself an audit row, platform.audit.exported, written before the first byte leaves.

In a new company the Owner and Admin roles have platform.audit.read. The Read only role does not.

The platform team works through database functions of the staff schema. Each one writes its own row to platform_audit_log, which only the panel role can read and nobody can change. When an act changes a company, for example a module turned on from the panel, the function also writes a row to the audit_log of that company with the actor type platform_staff. The company sees it as “Ochema platform team”, with no personal name, and can filter by it. See ADR-0023.

The audit log records actions. Reads of protected content have logs of their own, written in the same transaction as the read. Some also add an audit row, so the read shows up on the Audit log screen.

Log One row per Also an audit row Described in
file_access_log Read of any file, by a user, a public link or the system No Files and vaults
sensitive_data_reveals Reveal of a full Social Security number, date of birth or license number Yes, compliance.sensitive_data.reveal Sensitive data
drug_alcohol_access_log Read of the drug and alcohol file by a user Yes, without any detail of the record Files and vaults
signing_events Step in the life of a signing envelope For steps taken by a signed-in user Lifecycles
messages Message sent, refused or received Only for a message typed by a user, recruit.message.sent, without the text or the address Messaging

All of these are insert-only for the application, except messages, whose status follows the delivery.

audit_log and platform_audit_log have one partition per month and no default partition, so a row for a month without a partition is refused. The worker creates the partitions three months ahead once a day and once at start (PlatformMaintenanceProcessor in apps/api/src/modules/platform/jobs/processors.ts). The health check of the API fails when fewer than two future partitions exist, so a stopped routine is noticed well before it matters.

Test file What it proves
apps/api/test/errors-audit.test.ts A write leaves one audit row with actor, session, request and changes; a write that fails leaves neither data nor audit row.
apps/api/test/audit-log-catalog.test.ts The test reads the source of the whole API: every action and record type the code writes is in the catalog, so the filters offer it.
apps/api/test/audit-log.test.ts Listing, period, filters and paging; the Read only role cannot open the log; a company never sees the events of another; the export has every row up to its start, records itself with its filters, neutralizes formulas and refuses more than 100,000 rows.
packages/db/test/audit-partitions.test.ts Partitions are created ahead with no grants of their own; there is no default partition; rows with no company are accepted and never shown; occurred_at is the instant of each write.
packages/db/test/staff-audit.test.ts Each act of the platform team writes one row to platform_audit_log, and to the company log when it changes a company; nobody updates or deletes platform_audit_log.
apps/api/test/sensitive.test.ts, drug-alcohol.test.ts A reveal records who, of whom and when, never the value; the audit rows of the drug and alcohol file hold no result, note or file name.

The audit rows of each feature are asserted in the API tests of that feature as well.

  • Actions, not page views. The log records writes and a short list of sensitive reads: revealing a value, opening the drug and alcohol file, downloading an audit export, exporting the log. Opening a candidate or a driver file is not recorded.
  • File reads have no screen. Every read of a file is recorded in file_access_log, and no screen of the product shows that log.
  • Automatic messages are not in the audit log. A message sent by an automation, a sequence, a broadcast or a reminder is recorded as the message itself, in the timeline of the candidate, not as an audit row.
  • One year per query. The screen and the export take a period of at most 366 days and 100,000 rows. A longer history is read in several exports.
  • No retention setting. No routine removes old months: the log is kept, and a company cannot choose a shorter period.
  • Platform team rows carry no name. A company sees that the platform team acted, and what it did, not which member.