Phase 6: tamper-evident audit log with DB-trigger immutability

Archibald312 wrapped every LLM call and tool invocation in an append-only audit trail: UPDATE and DELETE on the `audit_log` table raise an exception at the database trigger level, not in application code. Before third-party data connectors arrive, the stated goal is a provable record of who saw what and which model handled it.

compliancesecurity

The migration in backend/migrations/audit_log.sql creates the table and installs two triggers (audit_log_no_update, audit_log_no_delete) that raise audit_log entries are immutable on any modification. REVOKE ALL ON public.audit_log FROM anon, authenticated means only the service role can write rows - a compromised app credential can append but not overwrite.

user_email is stored as a denormalized column alongside the user_id FK. If the user account is deleted and the FK nulls out, the email persists for forensic identification. Check this against your data retention policy before importing.

backend/src/lib/audit.ts defines the AuditEntry shape, a fire-and-forget recordAudit() insert with error swallowing, a hashContent() SHA-256 helper, and an AUDIT_LOG_ENABLED feature flag. The fire-and-forget design means audit failures are transparent to users. Whether that's acceptable depends on your compliance posture.

The tool dispatcher records one row per invocation with duration, hashed input/output payloads, resolved document IDs, and status. The LLM adapter records one row per outer streaming call - per outer call, not per provider iteration inside tool-use loops. Three entry points are instrumented: the main chat, project chat, and tabular review. A GET /audit-log route returns the caller's own rows with filters for project, event type, time range, and pagination. It is self-service, not admin-scoped.

The routing_policy_applied jsonb column is present but unpopulated until the model routing seam lands in the follow-on PR. No retention or rollup story is included.

Unit tests (95 lines) cover hashContent determinism, the insert shape, the feature-flag no-op path, and error swallowing.

So what Worth a look if your fork targets regulated industries, B2B procurement with compliance checklists, or is heading toward paid data connectors. The DB-trigger immutability and hash-not-content storage are the two patterns most worth copying - both are architecture decisions that are much harder to retrofit than to include from the start. The unbounded table growth and the fire-and-forget write semantics are the two things to address before going to production.

Spotted something wrong? Or know the PR text has fresher detail than the writeup above?

Commits in this thread

1 commit from Archibald312/GordonOSS, oldest first. Source extracted verbatim from the harvested git log.

SHA	Subject	Author	Date
`d523a644`	Phase 6: audit logging for LLM + tool invocations (#6)	Archibald312	2026-05-15	↗ GitHub
commit body - New audit_log table with immutability triggers (UPDATE/DELETE blocked at the DB layer); migration in backend/migrations/audit_log.sql and appended to backend/schema.sql. - backend/src/lib/audit.ts: AuditEntry shape, recordAudit() fire-and-forget insert, hashContent() SHA-256 helper, AUDIT_LOG_ENABLED feature flag. - Tool dispatcher (lib/tools/registry.ts) records a tool_call row per invocation with duration, input/output hashes, and resolved document IDs from args + side effects; errors are recorded then re-thrown. - streamChatWithTools wraps the per-provider stream and records an llm_call row on success or error. Audit context flows through runLLMStream and the tabular generate path. - GET /audit-log returns the caller's own entries with filters (project_id, event_type, from, to, limit, offset). - Unit tests cover hashContent determinism, recordAudit insert shape, feature-flag no-op, and error swallowing.

SHA

Subject

Author

Date

d523a644

Phase 6: audit logging for LLM + tool invocations (#6)

Archibald312

2026-05-15

↗ GitHub

commit body

- New audit_log table with immutability triggers (UPDATE/DELETE blocked
  at the DB layer); migration in backend/migrations/audit_log.sql and
  appended to backend/schema.sql.
- backend/src/lib/audit.ts: AuditEntry shape, recordAudit() fire-and-forget
  insert, hashContent() SHA-256 helper, AUDIT_LOG_ENABLED feature flag.
- Tool dispatcher (lib/tools/registry.ts) records a tool_call row per
  invocation with duration, input/output hashes, and resolved document
  IDs from args + side effects; errors are recorded then re-thrown.
- streamChatWithTools wraps the per-provider stream and records an
  llm_call row on success or error. Audit context flows through
  runLLMStream and the tabular generate path.
- GET /audit-log returns the caller's own entries with filters
  (project_id, event_type, from, to, limit, offset).
- Unit tests cover hashContent determinism, recordAudit insert shape,
  feature-flag no-op, and error swallowing.

Capture this thread into my fork

Download a single Markdown prompt that tells Claude how to port every commit above into your working tree — adapting paths and structure to match your repo. Run it via claude -p < capture-thread-425.md from inside the repo you want the changes in.