Audit log

DataSquares records the actions that matter for governance — who shared what, who changed a data source, who issued an API key, who signed in — into a workspace audit log. It is designed for the question an auditor actually asks, which is not “what happened” but “how do I know this record wasn’t edited afterwards”.

Reading the log

Admin → Audit Log lists the workspace’s events, 20 at a time. The current screen filters by action, user ID and date range; click Apply to run the filter and Reset to clear it. Select a row for its detail panel. Access is gated on the admin_audit permission: administrators have it, members need it granted. Reading the audit log is itself governed — you can’t quietly inspect who has been doing what.

Each table row shows time, user, action, resource, and source IP; selecting it opens the current compact detail panel (time, user, and action). The API row also carries resource IDs, user agent, and a details payload.

Tamper-evidence

Every row is linked to the one before it by a SHA-256 hash chain: each entry stores a hash of its own contents plus the hash of its predecessor. Changing a past row changes its hash, which breaks every link after it.

Three properties make that more than a convention:

• The chain is computed by the database, in an insert trigger — so every writer is chained, not just the ones that remembered to be. A caller that supplies its own seq, prev_hash or row_hash has those values discarded.
• UPDATE is refused outright, and so is TRUNCATE. DELETE is refused too, except from the retention sweep described below.
• Attribution survives a workspace deletion. The workspace is recorded as an immutable, hashed value at write time rather than only as a foreign key, so deleting a workspace cannot make the database rewrite its own history.

Verifying it

GET /api/audit-logs/verify walks the global chain and reports whether it is intact — and if not, the exact seq where it first stopped being intact. It distinguishes what went wrong: a row whose contents no longer match its stored hash (edited), a link that doesn’t match its predecessor (cut or re-spliced), a gap in the sequence (a row removed with nothing accounting for it), a head that doesn’t continue the last recorded anchor (a truncation), and a row whose timestamp moves backwards far enough to be a backdated insert rather than clock skew.

The verifier recomputes the hashes in the API process rather than asking the database whether its own rows are valid — a checker that asks the audited system to grade its own homework would report a clean bill of health on a subverted one.

Verification defaults to 100,000 rows and accepts fromSeq plus a maximum limit of 500,000. A partial: true response means the cap stopped before the current head. The endpoint returns positions, hashes, anchors, and findings—not audit event content from other workspaces. Verification is itself appended as audit.chain_verified after the report is calculated.

What this does and does not prove

This is tamper-evidence, not tamper-proofing. Someone with full database superuser rights can disable the triggers and rewrite the chain consistently — the log would then verify clean. What defeats that is a copy of the chain outside the database: the NDJSON feed below, shipped to a system the platform cannot reach, or the periodically anchored head hash. The chain fixes order, not time — created_at is caller-supplied and is why retention deletes by position rather than by age.

Exporting

The Export → CSV control on the Audit Log screen exports the rows currently loaded on that page. For a complete filtered export, automation, or a SIEM cursor, use the API endpoints below.

GET /api/audit-logs/export serves two formats. CSV takes the list filters; NDJSON is a separate sequence-cursor feed.

CSV — for a human reviewer

A download with a stable column order (id, created_at, workspace_id, user_id, action, resource_type, resource_id, ip_address, user_agent, details). New columns append, so a saved import mapping keeps working.

Audit rows carry attacker-influenced text — user agents, resource names — and spreadsheets execute any cell starting with =, +, -, @ or a tab as a formula. An audit export is therefore a delivery vehicle straight to the reviewer’s machine, so those cells are prefixed with an apostrophe: inert as data, unchanged as text. Exporting is itself audited.

NDJSON — for a SIEM

Add format=ndjson for one JSON object per line, with afterSeq as a resume cursor. NDJSON supports only afterSeq and limit; user, action, resource, and date filters are not applied to this format.

curl -H "Authorization: Bearer $TOKEN" \
  "https://your-instance/api/audit-logs/export?format=ndjson&afterSeq=$LAST_SEEN"

seq is global and monotonic. The feed is workspace-scoped, so gaps are normal whenever another workspace wrote the intervening global rows; retention can also remove an old prefix. Use the X-Last-Seq response header (or the last line) as the next afterSeq. The query is strictly seq > afterSeq, so a successful page is not delivered twice.

Every line carries seq, prev_hash, and row_hash, but a workspace feed is not a complete copy of the global chain and therefore cannot independently verify every predecessor link. Preserve the feed off-box as evidence and use the global /verify report and exported hourly head anchors for full-chain verification.

NDJSON defaults to 1,000 rows and is capped at 10,000 per request. CSV defaults to 50,000 and is capped at 100,000. Both export actions are recorded as audit.exported.

Retention

Entries are kept for AUDIT_RETENTION_DAYS — 730 days (two years) by default, the usual SOC 2 / ISO answer. An hourly sweep removes older rows in bounded batches (up to 5,000 per batch and 20 batches per pass) so it cannot hold one unbounded delete lock.

Set it to 0 to disable deletion entirely — the escape hatch for a deployment under legal hold.

The sweep deletes a prefix of the chain rather than a slice by date, and records an anchor as it goes, so verification of the surviving log still succeeds and a truncation is still detectable. It is the only code permitted to delete from the table.

On the same hourly tick, DataSquares witnesses a changed chain head in the anchor table and structured server log. That off-box log copy or backup is what makes a previously witnessed head useful against a database superuser who can otherwise disable triggers and consistently rewrite the chain.

Related

• Log Explore — exploring your own event and log data; a different feature from this platform audit trail.
• Users, roles & invites — where the admin_audit permission is granted.
• Webhooks — pushing events out as they happen, rather than pulling a record afterwards.
• REST API — the full endpoint reference.