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.
The problem
Section titled “The problem”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.
The rule
Section titled “The rule”- 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.
- 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.
- The application can add audit rows and read those of its company. It cannot change or delete them.
- A company reads its own log on the screen Platform > Audit log, with the permission
platform.audit.read. - What the platform team does to a company appears in the log of that company.
How the code applies it
Section titled “How the code applies it”The table
Section titled “The table”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.
No write without an audit row
Section titled “No write without an audit row”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.
Insert-only
Section titled “Insert-only”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.
Reading the log
Section titled “Reading the log”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 in the log
Section titled “The platform team in the log”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 other logs
Section titled “The other logs”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.
Partitions
Section titled “Partitions”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.
What guarantees it
Section titled “What guarantees it”| 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.
Known limits
Section titled “Known limits”- 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.
Related
Section titled “Related”- ADR-0028: monthly partitions. ADR-0023: the platform team and its log. ADR-0015: logged reveal. ADR-0011: archive instead of delete.
- Building blocks: where the audit check sits in the request pipeline.
- Tenant isolation, Sensitive data, Files and vaults.
- User guide: Audit log.