Audit Log

Append-only record of org-scoped events. Powers the audit UI and supports compliance review.

Files

• Schema: src/lib/db/schema/audit.ts — audit_logs table.
• Helper: src/lib/api/audit.ts — logAudit(args) fire-and-forget insert, plus signAuditRow / classifyAuditRow / verifyAuditRow / auditSigningEnabled / auditSigningCutover.
• Tests: src/lib/api/audit.test.ts — golden digests over the canonical byte string, plus one case per verification bucket.
• List endpoint: GET /api/internal/audit?limit=50 (developer+) — see Who can read it.
• Verify endpoint: GET /api/internal/audit/verify (owner-only) — see Tamper evidence.

Who can read it

Reads are gated to developer or owner (requireDeveloperSession), and this is a deliberate policy call rather than an oversight: audit rows carry every colleague's IP address and user agent on each sign-in, invitee email addresses, and — with user.login / user.login_failed now emitted — a per-person record of when and from where each teammate signs in. That is surveillance-adjacent data with no read-only use case, so viewer and member do not get it. Writes are unaffected — every role's actions are still recorded.

Enforced in three layers, only the first of which is load-bearing:

• GET /api/internal/audit answers 403 { error: "Developer or owner role required" } below developer. This is the actual gate.
• /overview/settings/audit (page.tsx) checks requireDeveloperSession() server-side and bounces to the settings hub, so a typed-in URL never renders the page shell.
• The sidebar entry and the settings-hub card are hidden below developer (useCanDevelop() client-side, ctx.role server-side) — cosmetic, so nobody is offered a link that 403s.

GET /api/internal/audit/verify stays owner-only, unchanged: a tampering report is an org-security answer.

Actions emitted

Defined in AuditAction (src/lib/api/audit.ts). Grouped by resource:

Users + sessions

The four personal routes above (sessions, two-factor, data-export, and the user DELETE) run on identity alone, because they must work for a user who belongs to no org. logPersonalAudit() (src/lib/auth/personal-audit.ts) files their rows under the user's active org, resolved the way a dashboard request resolves it; for user.account_deleted that is resolved after any owned orgs have been deleted, so the row never lands in an org the erasure just cascaded away. A user with no org left has nowhere to file one (org_id is NOT NULL), so the same facts go to the operational log as audit.personal_no_org instead. The session and 2FA routes emit through schedulePersonalAudit(), which runs it in after() and logs a failure as audit.personal_failed: fired as a floating promise just before the response, the row could be dropped when the platform froze the instance.

API keys

Domains

Webhooks

Templates + audiences + contacts

Tenants (platform mode)

Messaging pools

SMS registration (10DLC + toll-free)

Team

Billing / org

Invite tokens

member.invited rows carry the invited address in resource_id. They used to carry the raw invite token, which is bearer-equivalent — possessing it is enough to accept the invite as the person it was addressed to — and this endpoint was, at the time, readable by every member of the org, viewer included (now developer+).

Historical rows still hold the token in the database. They are deliberately not rewritten: resource_id is one of the fields the row signature covers, so an in-place edit would either invalidate every affected row or require re-signing it, and a lawful cleanup that re-signs is indistinguishable from tampering to whoever reads the next verify report. redactAuditInviteToken (src/lib/api/audit.ts) withholds the value on the way out instead — applied by GET /api/internal/audit, the sync org export and the export cron. Nothing is lost: metadata.email carries the address either way.

Tokens expire 7 days after issue, so rows older than that were inert regardless.

Shape

Each entry:

{
  id: "aud_...",
  orgId: "org_...",
  actorUserId: "usr_..." | null,
  action: "api_key.created",
  resourceType: "api_key" | null,
  resourceId: "key_..." | null,
  metadata: { ... } | null,
  ipAddress: "1.2.3.4" | null,       // from x-forwarded-for
  userAgent: "...",                  // request header
  signature: "<sha256 hex>" | null,  // null when signing is off — see below
  createdAt: Date,
}

GET /api/internal/audit projects these columns explicitly rather than select()-ing the table, so a column added later is not served to readers by default. Two differences from the stored row: signature is omitted (the digest is what /api/internal/audit/verify reports on), and resource_id is withheld on invite rows — see above.

Usage

import { logAudit } from "@/lib/api/audit";

logAudit({
  orgId,
  actorUserId,
  action: "api_key.revoked",
  resourceType: "api_key",
  resourceId: keyId,
  req,
}).catch(() => {});

Always .catch(() => {}) — audit logging should never block the primary action.

Tamper evidence (row signatures)

Application code only ever appends here (the retention cron deletes aged rows; the only rewrite is GDPR erasure, which re-signs what it anonymizes). That says nothing about someone holding a database connection, so each row carries an HMAC and an after-the-fact edit can be detected without trusting the database.

Signing

logAudit computes HMAC-SHA256 over the row's immutable fields and stores the hex digest in signature. The key is AUDIT_SIGNING_SECRET, which lives only in the app env — an attacker with write access to audit_logs can change a row but cannot produce a matching signature for it.

Signed payload, in order: id, org_id, action, actor_user_id, resource_type, resource_id, metadata, created_at (ISO 8601). metadata is canonicalized first — JSON round-trip plus a deep key sort — because jsonb does not preserve key order, so signing the in-memory order would make every multi-key row unverifiable by construction.

The exact signed byte string is pinned by golden digests in src/lib/api/audit.test.ts. Changing the canonicalization re-derives every existing row to a different HMAC, so a change there is a schema-style migration: bump the cutover below in the same commit, and expect the goldens to fail — that failure is the point, not something to re-baseline.

ip_address and user_agent are not signed. They're request-derived rather than part of the recorded action; edits to those two are not detected.

Signing is opt-in and has no fallback secret. With AUDIT_SIGNING_SECRET unset, signAuditRow returns null and rows are written with signature = NULL — audit writes themselves are unaffected. See env.example.

The cutover

AUDIT_SIGNING_CUTOVER (ISO 8601) is the instant from which a row is required to carry a signature that re-derives under the current scheme. It is the boundary between "unverifiable" and "tampered", and the verifier will not call anything older than it a failure:

• rows written before signing landed (or while AUDIT_SIGNING_SECRET was unset) carry no signature at all → unsigned;
• rows signed before the deep key sort hashed metadata in its in-memory key order, which jsonb did not keep. For metadata with two or more keys at any depth that HMAC cannot be reproduced by anyone → legacy.

Unset, it defaults to 2026-08-10T00:00:00Z, the day after the key sort shipped. Set it explicitly when this deployment picked that change up later, or set AUDIT_SIGNING_SECRET later — the correct value is the later of those two instants. An unparseable value logs audit.cutover_unparseable and falls back to the default.

Erring late costs detection (rows land in legacy, which reads as "cannot tell"); erring early manufactures tamper alerts for rows nothing touched. The default rounds up a day for that reason.

The boundary is a timestamp rather than a version marker inside signature because it is a fact about the deployment, not about the row — and the stripped-signature case (signature IS NULL) has no marker to carry. No schema change, no format migration.

Verifying

GET /api/internal/audit/verify

Owner-only (requireOwnerSession) — a tampering report is an org-security answer, and the route reads back every signed field of every row it checks. Scoped to the caller's active org. Without this reader the signature column is write-only.

Newest-first, same (created_at, id) keyset as the list endpoint. A full sweep means walking pages; there is no full-table-scan-in-one-request mode.

{
  "checked": 100,
  "ok": 92,
  "unsigned": 5,
  "legacy": 2,
  "invalid": [
    {
      "id": "aud_...",
      "action": "api_key.revoked",
      "actorUserId": "usr_...",
      "resourceType": "api_key",
      "resourceId": "key_...",
      "createdAt": "2026-08-09T11:04:22.310Z"
    }
  ],
  "stripped": [],
  "cutover": "2026-08-10T00:00:00.000Z",
  "has_more": true,
  "next_cursor": "MjAyNi0wOC0wOVQ..."
}

checked = ok + unsigned + legacy + invalid.length + stripped.length. cutover is echoed because unsigned and legacy only mean "unverifiable" relative to that instant.

Errors:

A 503 here means the feature is inert, not that the log is clean.

Alerting + self-audit

Any mismatch or stripped signature emits log.error("audit.signature_mismatch", { orgId, checked, cutover, invalidCount, invalidIds, strippedCount, strippedIds }) (first 20 ids each) so it reaches Sentry instead of living only in a response the dashboard may discard. A stripped signature raises the same alarm as a mismatch: it is the same attacker making a cheaper edit.

Each run writes its own audit.verified entry — after the page is read, so a pass never has to verify itself — with metadata { checked, ok, unsigned, legacy, invalidCount, invalidIds, strippedCount, strippedIds, cutover, createdAfter, createdBefore }. Only the first 20 failing ids go into metadata; the full list is in the response.

Known benign invalids

Rule out both of these before treating an invalid as a breach:

• Secret rotation — below.

Note what is not on this list. DELETE /api/internal/user anonymizes the leaving user's entries with UPDATE audit_logs SET actor_user_id = NULL, keeping the trail without the PII link — and actor_user_id is a signed field, so that write used to flip every entry the user ever authored to invalid, permanently. The erasure now re-signs each anonymized row against its new contents, so a lawful erasure leaves the trail verifiable and an invalid still means what it says. A re-sign that fails is logged as internal.user.audit_resign_failed; those rows report invalid with "actorUserId": null, which is the shape to look for before calling it a breach.

It re-signs only the rows that classified ok first. This is the load-bearing half: signing whatever a row currently contains would make erasure a signing oracle. Someone with database write access but no AUDIT_SIGNING_SECRET — a leaked read-write DATABASE_URL is the realistic shape — could edit rows to erase their tracks, repoint actor_user_id at an account they control, delete that account, and have the forged contents come back ok under a real signature. A row that was invalid, stripped, or legacy before the erasure keeps its original signature and its original verdict; the count of rows skipped for that reason is logged as internal.user.audit_resign_skipped. A non-zero preserved there means the trail was already broken before the erasure touched it, which is worth reading as a finding in its own right.

Note what is not on this list any more: rows signed before the canonicalization cutover. Those land in legacy, never in invalid, so a deployment that has had AUDIT_SIGNING_SECRET set since signing shipped no longer sees its whole history reported as tampered.

Anything left over is a row that changed outside the app. Compare the invalid ids against the audit.verified metadata of earlier runs to date the change.

Reading a report

1. stripped non-empty → treat as a breach signal, same as invalid. Nothing in the app clears a signature.
2. invalid non-empty → rule out the two benign causes above, then investigate.
3. legacy / unsigned are unverifiable, not clean. If either is large, scope the sweep with created_after=<cutover> to get a report made only of rows that can actually be checked — the same trick the rotation section describes.
4. legacy should only ever shrink as retention prunes old rows. If it grows, either the cutover is set later than this deployment actually cut over, or something is writing rows the current signer did not produce.

The blind spot to know about: for a pre-cutover row whose metadata has two or more keys, "edited" and "old key order" are the same observation, so a genuine edit to such a row reports as legacy. That window is closed only by time — every row written after the cutover is fully checkable.

Rotating the secret

Rotating AUDIT_SIGNING_SECRET makes every previously-signed row report as invalid — their HMACs were derived from the old key and cannot be reproduced. There is no re-sign path, and adding one would defeat the purpose: anything that can re-sign rows can also launder an edit.

So: rotate only when you accept that everything written before the rotation becomes unverifiable, and record the rotation timestamp. created_after=<rotation time> then scopes a verify run to the rows that can still be checked, and a later invalid spike can be read against that date instead of being confused with the rotation.

Moving AUDIT_SIGNING_CUTOVER to the rotation instant is still worth doing — it stops a row written after the rotation with a NULL signature from reading as stripped. But it does not keep the pre-rotation rows out of invalid, and it is important not to expect that it will. The legacy bucket excuses one specific thing: a signature the old canonicalization cannot reproduce, which legacyOrderLost detects by finding an object with two or more keys in the row's metadata. A row orphaned by a secret rotation whose metadata is NULL or single-key — user.sessions_revoked, anything with just { via } — re-derives cleanly under the new key, mismatches, fails the legacyOrderLost test, and lands in invalid no matter where the cutover sits. (org.deleted used to be in that list and no longer is: since the billing-aware delete it carries eight metadata keys, so post-rotation it takes the legacyOrderLost path into legacy instead. Rows written before that change still classify as invalid.) That is by design: legacy is a per-row test precisely so it cannot become a blanket hiding place for everything old.

The practical consequence: after a rotation, expect a permanent invalid population over pre-rotation history, and scope verify runs with created_after rather than trying to tune it away. Only created_after actually separates the two.

Time zones

The route re-reads created_at as UTC text (to_char) and never selects the driver's Date at all. created_at is timestamp (no time zone); parsed in a process running off UTC it would shift every row and the entire page would read as tampered.

The same UTC text is what the response reports and what next_cursor is built from. It has to be: the cursor seeks the next page against the unshifted column, so a cursor built from a shifted Date would step an offset's worth of rows past where page 2 should start — silently, while checked/ok still tallied as though the sweep were complete.

Indexes

• (org_id, created_at) — powers the dashboard timeline query.
• (action) — filter by action type.

Retention

audit_logs rows older than 365 days are pruned nightly by /api/cron/retention. Tune AUDIT_LOG_RETENTION_DAYS (or edit the cron) if compliance requires longer retention. There's no archive-to-blob path yet — open follow-up for high-volume orgs.